技术数据库

Mysql

Mysql, 数据库

基础

基本命令

  • 备份mysql

    备份一个库

    mysqldump -u sa -p newspaper | gzip > /usr/local/backups/mysql/newspaper_20140216.sql.gz

    备份全部库

    mysqldump -u root -p --all-databases | gzip > /home/allbk-2017-12-06.sql.gz

  • 恢复mysql

    cd /usr/local/backups/mysql
gzip -d newspaper_20140211.sql.gz
mysql -u sa -p newspaper < /usr/local/backups/mysql/newspaper_20140211.sql

  • grant 普通 DBA 管理某个 MySQL 数据库的权限

    mysql -u root -p
CREATE SCHEMA `newspaper` DEFAULT CHARACTER SET utf8mb4;
grant all on newspaper.* to sa@%;

centos7 mariadb

yum -y install mariadb*

systemctl start mariadb.service

systemctl enable mariadb.service

mysql_secure_installation

mysql -uroot -p

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'IDENTIFIED BY 'passward' WITH GRANT OPTION;

flush privileges;

数据库授权

GRANT ALL PRIVILEGES ON *.* TO 'sa'@'%' IDENTIFIED BY 'mypassword';
FLUSH PRIVILEGES;

启动MySQL操作日志

  1. 前提
  • 设置general log保存路径
  • 注意在Linux中只能设置到 /tmp 或 /var 文件夹下，设置其他路径出错
  • 需要root用户才有访问此文件的权限
  1. 操作
1
2
3
mysql>set global general_log_file='/tmp/general.lg';
// 开启general log模式
mysql>set global general_log=on;
  1. 或者改my.cnf配置
1
2
3
4
5
// 查找my.cnf位置
find / -name my.cnf
// 在[mysqld]后面加入下面内容：
general_log_file=/tmp/general.lg
general_log=1

然后重启mysql服务

mysql在linux下启动停止的合集

  1. 通过rpm包安装的MySQL

    service mysqld restart
service mysql restart

  2. 从源码包安装的MySQL

    // linux关闭MySQL的命令
$mysql_dir/bin/mysqladmin -uroot -p shutdown
// linux启动MySQL的命令
$mysql_dir/bin/mysqld_safe &

infobright安装

  1. infobright是开源的MySQL数据仓库解决方案

    wget --no-check-certificat https://www.infobright.org/downloads/ice/infobright-4.0.7-0-x86_64-ice.rpm

rpm -ivh infobright-4.0.7-0-x86_64-ice.rpm --prefix=/zewspaper/

  2. To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system

  3. PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !

    To do so, start the server, then issue the following commands:

    /zewspaper/infobright-4.0.7-x86_64/bin/mysqladmin -u root password 'new-password'
/zewspaper/infobright-4.0.7-x86_64/bin/mysqladmin -u root -h hadoop1 password 'new-password'

    Alternatively you can run:

    /zewspaper/infobright-4.0.7-x86_64/bin/mysql_secure_installation

    which will also give you the option of removing the test
    databases and anonymous user created by default. This is
    strongly recommended for production servers.

    See the manual for more instructions.

  4. You can start the MySQL daemon with:

    cd /zewspaper/infobright-4.0.7-x86_64
/zewspaper/infobright-4.0.7-x86_64/bin/mysqld_safe &

  5. You can test the MySQL daemon with mysql-test-run.pl

    cd /zewspaper/infobright-4.0.7-x86_64/mysql-test
perl mysql-test-run.pl

System Physical memory: 1873(MB)
System memory too low!
Infobright optimal ServerMainHeapSize is set to 400(MB)
Infobright optimal LoaderMainHeapSize is set to 300(MB)
Infobright server installed into folder /zewspaper/infobright
Installation log file /tmp/ib4.0.7-0-install.log

  1. To activate infobright server, please run

    ./postconfig.sh

    script from /zewspaper/infobright-4.0.7-x86_64.

MySQL Master Slave同步配置

  1. 首先修改mysql配置文件：my.cnf
    master配置

    log-bin = testserver-bin
log-bin-index = testserver-bin.index
server-id = 1
expire_logs_days = 7

    slave配置

    server-id = 2
relay-log-index = slave1-relay-bin.index
relay-log = slave1-relay-bin
expire_logs_days = 7

  2. 将master中的数据库 导入到slave中，

    先锁表，禁止写入操作

    mysql -uroot -p
flush tables with read lock;

    先从master导出：

    mysqldump -uroot -p tipoff > /tmp/tipoff.sql

scp /tmp/tipoff.sql 10.99.113.33:/tmp/

    再导入到slave：

    mysql -uroot -p tipoff < /tmp/tipoff.sql

    记得需要先建库test

  3. 进入master上的mysql,查看master状态

    show master status;

  4. 进入slave上的Mysql

    change master to  master_host='10.99.113.32', master_user='root', master_password='_tipoff18', master_log_file='xsb32-m1-bin.000002', master_log_pos=50974;

start slave;

  5. 查看slave状态

    show slave status\G;

  6. 最后将master上的表解锁

    unlock tables;

MySQL 5.7并行复制时代

MySQL 5.7并行复制的思想简单易懂，一言以蔽之：

一个组提交的事务都是可以并行回放，因为这些事务都已进入到事务的prepare阶段，则说明事务之间没有任何冲突（否则就不可能提交）。

开启MTS功能后，务必将参数master_info_repostitory设置为TABLE，这样性能可以有50%~80%的提升

要开启enhanced multi-threaded slave其实很简单，只需根据如下设置：

# slave
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
  • MySQL 5.7推出的Enhanced Multi-Threaded Slave解决了困扰MySQL长达数十年的复制延迟问题

MySQL5.7中使用JSON

实战

关系数据库冗余设计

  1. 存储查询复杂的静态数据

    例如网易新闻评论的盖楼模式，父评论还有父评论：

    在评论表中使用reply_id_chain表示所有祖宗评论id列表，用逗号分隔，按照id大小顺序排列(及回复先后顺序)，例如2,4,5,7,89。这样在获取一条评论的回复链的时候，只需要执行一次查询即可得到所有祖宗评论列表，并且排序按照楼层拍好。

有一点是需要注意的，冗余存储的必须是静态数据，即，一旦发生永不改变。例如发布一条回复，这个事件一旦发生不可能改变的，用户不可能再去修改已经发布的评论内容、评论时间或者彻底删除这条评论

  2. 存储计算复杂的动态数据

    例如论坛中用户等级一般是根据用户行为得分计算而来的，例如发文得10分、评论得1分，被赞得1分等等，数据库会记录用户发文、评论和被赞的详细流水。

    如果对每一个用户获取其发文数、评论数、被赞数，在进行一次公式计算，显然是很耗时耗资源的。为了提升读的速度，就要在考虑在写的时候优化，我们可以将计算过程放到发布文章、写入评论时。

    首先，在用户表增加一个字段score，表示用户行为得分
每次获取用户等级时，只需要拿到这个得分，做一个简单的映射即可，1000分一下LV1、1000-2000LV2等等
而写入本身是一个相对数据操作压力较小的工作，还可以借助消息机制实现异步写入

  3. 存储难以标准化的数据

    例如站内系统消息会提示用户“你的XXX文章被XX赞了”，“XX回复了你的文章XXX”，“XX关注了你”等等。

    不能标准化的字段有：行为类型可能是回复也可能是点赞、回复对象的类型可能是文章也可能是评论，另外还引用的一些附加信息，例如文章id、文章标题等等。

    引入一个content字段，把非标准化的数据拼凑为可用的数据。为了让拼凑的content数据更加灵活，我们采用了json格式表示这些数据。
这样的冗余存储使得查询用户消息的时候无须关联其他用户资料、文章、评论表，虽然有些数据是动态的，例如用户昵称、文章标题，但是用户可以理解这种消息只是一个快照，这一快照之后，用户昵称发生过改变。

  4. 总结

    总结下来就是一句话，如果数据是静态的，利用冗余存储去提升查询性能是一件不错的事情。
    当下NoSQL的发展，使得冗余存储的设计更加重要，设计好冗余储存对于业务的性能将会有很大的提升。

mysql的cpu占用过高

/etc/my.cnf
/usr/my.cnf
  1. 修改my.cnf增加
innodb_thread_concurrency=8 #你的服务器CPU有几个就设置为几
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES #去除
max_connections=1000
tmp_table_size=200M

  1. 查看SQL 语句经常在 process list 中出现

    mysql> show processlist;

    然后优化sql语句，或者增加索引

mysql日志太多

  1. 修改my.cnf增加

    expire_logs_days = 7

在mysql命令窗口获取正在执行的sql语句

use information_sschema;

select * from PROCESSLIST where info is not null;

linux下mysql的root密码忘记解决方

  1. 修改MySQL的登录设置：

    vi /etc/my.cnf

    在[mysqld]的段中加上一句：skip-grant-tables

    例如：

    [mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-grant-tables

    保存并且退出vi。

  2. 重新启动mysqld

    service mysql restart

  3. 登录并修改MySQL的root密码

    /usr/bin/mysql

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 3.23.56
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> USE mysql ;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> UPDATE user SET Password = password ( 'new-password' ) WHERE User = 'root' ;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2 Changed: 0 Warnings: 0
mysql> flush privileges ;
Query OK, 0 rows affected (0.01 sec)
mysql> quit
Bye

  4. 将MySQL的登录设置修改回来

    vi /etc/my.cnf

    将刚才在[mysqld]的段中加上的skip-grant-tables删除
    保存并且退出vi。

  5. 重新启动mysql

特殊命令

  • 特殊选择

    SELECT id,imsi FROM xxxx.user where imsi is not null and imsi!='' and (instr(imsi,'-')=0) and id>10255;

insert into xxxx.news_2015 select * from xxxx.news where creattime<'2015-04-01';
Delete FROM xxxx.news where creattime<'2015-04-01';

DELETE FROM `xxxx`.`content` WHERE `node_id` in (SELECT id FROM xxxx.node where `issue_id`='6');

  • 替换字符串

    UPDATE `xxxx`.`news` SET `picurl`=replace(`picurl`,'192.168.1.1','r.stt.dda.com');

  • 清理历史表

    Delete FROM newspaper.content where node_id in (SELECT id FROM newspaper.node where issue_id in (SELECT id FROM newspaper.issue where name < '2014-02-01'));

  • 查看最大连接数:

    show variables like '%max_connections%';

  • 修改最大连接数

    方法一：修改配置文件。推荐方法一

    进入MySQL安装目录 打开MySQL配置文件 my.ini 或 my.cnf查找 max_connections=100 修改为 max_connections=1000 服务里重起MySQL即可.

    方法二：命令行修改。不推荐方法二

    命令行登录MySQL后。设置新的MySQL最大连接数为200：

    MySQL> set global max_connections=200。

shell跨服务器导数据

#!/bin/sh

mysql -h10.10.4.104 -uroot -p123456 --skip-column-names --default-character-set=gbk -e"SELECT id,advertiserID,name,startDate,endDate,budget,dailyBudget,groupID FROM campaign" hdtcms > /services/db-bak/t_campaign_info.txt

mysql -uroot -p123456 -e"

delete from dspreport.t_campaign_info;

load data local infile '/services/db-bak/t_campaign_info.txt' into table dspreport.t_campaign_info character set gbk (id,advertiserID,name,startDate,endDate,budget,dailyBudget,groupID);


"
exit  0

优化

「mysql优化专题」

https://www.toutiao.com/i6483352424965734925/

让数据库跑的更快的7个MySQL优化建议

http://www.sohu.com/a/210048187_463994

  1. 学习如何使用EXPLAIN

    设计决策

  • 应用实体之间如何被映射到各个数据表（数据库模式架构）上。

  • 应用程序如何获取（查询）到它们所需格式类型的数据。

    重点查看的部分是：查询成本。查询成本是指基于查询执行的总体成本和许多不同的因素考虑，MySQL 判定一次查询所付出的花销。

    一般简单查询的成本会小于 1000。介于 1000 到 100,000 的成本值被视为中等成本的查询。
    因此，如果您每秒只是运行上百个（并非几万个）此类查询的话，一般速度应该比较快。
    查询成本如果是超过 100,000 的话，那么开销就比较大了。

    explain format=json selectavg(k)fromsbtest1 whereid between 1000and2000G

    决定查询成本的一个首要因素是：查询是否正确地使用了各种索引。如果您没有使用索引进行查询，那么会被 EXPLAIN 命令所指出来

  1. 创建正确的索引

    索引被用于加快对数据库的访问，并有助于遵循数据库的各种约束（例如 UNIQUE 和 FOREIGN KEY）

    需要使用 EXPLAIN 来查找缺失的索引，并将其添加上去

    需要注意的是：不要添加您所不需要的索引，因为不必要的索引会反过来拖慢数据库。

    Percona查找未使用的索引

  2. 拒绝默认设置

    有三个有关 MySQL 性能优化的设置

    * innodb_buffer_pool_size
数据和索引被用作缓存的缓冲池。当数据库服务器有着大量的系统内存时，可以用到该设置。
如果只运行 InnoDB 存储引擎，通常可以分配 80％ 左右的内存给该缓冲池
而如果运行非常复杂的查询或者有大量的并发数据库连接，亦或有非常大的数据表的情况，那么就可能需要将此值下调一个等级，以便为其他的调用分配更多的内存。

“Percona 监控和管理”

* innodb_log_file_size
可以通过查看未实际使用的重做日志空间大小来判定
查看“Percona 监控和管理”的 InnoDB 指标仪表板
您的日志文件应该至少比使用量大 20％，从而保持系统处于最佳的性能状态

* max_connections
MySQL 能够在峰值操作时轻易地获悉所用到的连接数量
需要确保在应用程序所使用到的最大连接数和可用的最大连接数之间至少有 30％ 的差额。
在“Percona 监控和管理”的系统概述界面中查看使用 MySQL 连接图。
应用程序所创建的连接数量过多，通常会导致数据库运行缓慢。

  1. 将数据库载入内存中

    只需要将最频繁访问的数据集放入内存便可。

    检查数据库达到稳定运行状态时的 I/O（通常是在它开始运行的几个小时之后）。
请查看一下数据的读取，因为如果您的数据库已载入到内存里的话，那么读取会完全结束；而只要有内存可用，写入操作总是会发生的。

“Percona 监控和管理”的 InnoDB 指标仪表板中的 InnoDB I/O

image

如上图所示，那些峰值高达每秒 2,000 的 I/O 操作表明（至少是流量负载的一部分）它们与载入内存中数据库的数据集并不相配

  1. 使用SSD存储

    需要使用快速存储来处理写入操作，并且避免在数据库启动后（重启之后）出现性能问题。这里的快速存储就是指固态硬盘。

    应该选用那些专供服务器工作负载、且能精心呵护数据的 SSD。防止断电损坏的，而避免使用那些专为台式和笔记本电脑设计的商用固态硬盘。
通过 NVMe 或英特尔 Optane 技术来直接连接的 SSD 往往能够提供最佳的性能。

  1. 横向扩展

    横向扩展，在处理负载方面有如下几点优势：

    可以从更小型、成本更低的系统中获益。
横向扩展使得系统的线性扩展更方便、更快捷。
由于数据库会横跨增长到多个物理机上，横向扩展在保护数据库的同时，消除了硬件单点故障。

    局限性

    需要数据复制，例如基本的 MySQL Replication 或是用于数据同步的 Percona XtraDB 群集

    如果需要更高级的扩展性，那么请考虑使用 MySQL 分片（sharding）。

    需要确保连接到群集架构的应用程序可以找到它们所需的数据。这通常是通过诸如 ProxySQL 或 HAProxy 的一些代理服务器和负载平衡器来实现的。

    当然，过早地规划横向扩展，会增加分布式数据库的复杂性。最近发布的 MySQL 8 候选版本已声称自己能够在单一的系统上处理超过 200 万个简单查询。

  2. 追求可视性

    理想情况就是在系统出现问题或是被用户所察觉之前就做到防范于未然

    常用的监测工具有：

    * MySQL企业监控器（Enterprise Monitor）。
* Monyog。
* 具有免费与开源版本的 Percona 监控和管理（PMM）。

mysql性能监控qps,tps,iops

  1. qps 每秒处理的查询数

    Questions = SHOW GLOBAL STATUS LIKE ‘Questions’;

    Uptime = SHOW GLOBAL STATUS LIKE ‘Uptime’;

    QPS=Questions/Uptime

  1. tps 每秒处理的事务数

    Com_commit = SHOW GLOBAL STATUS LIKE ‘Com_commit’;

    Com_rollback = SHOW GLOBAL STATUS LIKE ‘Com_rollback’;

    Uptime = SHOW GLOBAL STATUS LIKE ‘Uptime’;

    TPS=(Com_commit + Com_rollback)/Uptime

  2. IOPS，每秒磁盘进行的I/O操作次数

性能优化之覆盖索引（covering index）

http://blog.itpub.net/35489/viewspace-2055391/

覆盖索引（covering index），MySQL只需要通过索引就可以返回查询所需要的数据，而不必在查到索引之后再去查询数据，所以相当快！！但是同时也要求所查询的字段必须被索引所覆盖到，在Explain的时候，输出的Extra信息中如果有“Using Index”，就表示这条查询使用了覆盖索引。

例如：

    CREATE TABLE `user_group` (
      `id` int(11) NOT NULL auto_increment,
      `uid` int(11) NOT NULL,
      `group_id` int(11) NOT NULL,
      PRIMARY KEY  (`id`),
      KEY `uid` (`uid`),
      KEY `group_id` (`group_id`),
    ) ENGINE=InnoDB AUTO_INCREMENT=750366 DEFAULT CHARSET=utf8

75万条数据。简单的查询：

    SELECT SQL_NO_CACHE uid FROM user_group WHERE group_id = 245;
    -- SQL_NO_CACHE 不使用缓存提示

Explain的结果是：

    +----+-------------+------------+------+---------------+----------+---------+-------+------+-------+
    | id | select_type | table      | type | possible_keys | key      | key_len | ref   | rows | Extra |
    +----+-------------+------------+------+---------------+----------+---------+-------+------+-------+
    |  1 | SIMPLE      | user_group | ref  | group_id      | group_id | 4       | const | 5544 |       |
    +----+-------------+------------+------+---------------+----------+---------+-------+------+-------+

加索引的效果一般，试着加了一个多列索引：

    ALTER TABLE user_group ADD INDEX group_id_uid (group_id, uid);

经过优化的SQL再结合真实的业务需求，也从之前2.2s下降到0.05s

再Explain一次：

    +----+-------------+------------+------+-----------------------+--------------+---------+-------+------+-------------+
    | id | select_type | table      | type | possible_keys         | key          | key_len | ref   | rows | Extra       |
    +----+-------------+------------+------+-----------------------+--------------+---------+-------+------+-------------+
    |  1 | SIMPLE      | user_group | ref  | group_id,group_id_uid | group_id_uid | 4       | const | 5378 | Using index |
    +----+-------------+------------+------+-----------------------+--------------+---------+-------+------+-------------+

在Explain的时候，输出的Extra信息中如果有“Using Index”，就表示这条查询使用了覆盖索引

查询优化技巧

  1. 查询的开销指标：
  • 执行时间
  • 检查的行数
  • 返回的行数
  1. 建立索引的几个准则：

(1)、合理的建立索引能够加速数据读取效率，不合理的建立索引反而会拖慢数据库的响应速度。

(2)、索引越多，更新数据的速度越慢。

(3)、尽量在采用MyIsam作为引擎的时候使用索引（因为MySQL以BTree存储索引），而不是InnoDB。但MyISAM不支持Transcation。

(4)、当你的程序和数据库结构/SQL语句已经优化到无法优化的程度，而程序瓶颈并不能顺利解决，那就是应该考虑使用诸如memcached这样的分布式缓存系统的时候了。

(5)、习惯和强迫自己用EXPLAIN来分析你SQL语句的性能。

  1. count的优化

    比如：计算id大于5的城市

(1). select count(*) from world.city where id > 5;
(2). select (select count() from world.city) – count() from world.city where id <= 5;

(1)语句当行数超过11行的时候需要扫描的行数比(2)语句要多，
(2)语句扫描了6行，此种情况下，(2)语句比(1)语句更有效率。
当没有where语句的时候直接select count(*) from world.city这样会更快，因为mysql总是知道表的行数。

  1. 避免使用不兼容的数据类型

    例如float和int、char和varchar、binary和varbinary是不兼容的。

    数据类型的不兼容可能使优化器无法执行一些本来可以进行的优化操作。

  2. 保证在实现功能的基础上，尽量减少对数据库的访问次数

  3. 通过搜索参数，尽量减少对表的访问行数,最小化结果集，从而减轻网络负担
  4. 能够分开的操作尽量分开处理，提高每次的响应速度
  5. 在数据窗口使用SQL时，尽量把使用的索引放在选择的首列
  6. 算法的结构尽量简单
  7. 在查询时，不要过多地使用通配符如 SELECT * FROM T1语句，要用到几列就选择几列如：SELECT COL1,COL2 FROM T1
  8. 在可能的情况下尽量限制尽量结果集行数如：SELECT TOP 300 COL1,COL2,COL3 FROM T1,因为某些情况下用户是不需要那么多的数据的
  9. 不要在应用中使用数据库游标，游标是非常有用的工具，但比使用常规的、面向集的SQL语句需要更大的开销
  10. 按照特定顺序提取数据的查找

  11. 索引字段上进行运算会使索引失效

    尽量避免在WHERE子句中对字段进行函数或表达式操作

    SELECT * FROM T1 WHERE F1/2=100

    应改为:

    SELECT * FROM T1 WHERE F1=100*2

  12. 避免使用!=或＜＞、IS NULL或IS NOT NULL、IN ，NOT IN等这样的操作符

    优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。

    在in语句中能用exists语句代替的就用exists.

  13. 尽量使用数字型字段

    包含数值信息的字段设计为字符型，这会降低查询和连接的性能，并会增加存储开销。

    这是因为引擎在处理查询和连接会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

  14. 合理使用EXISTS,NOT EXISTS子句

(1). SELECT SUM(T1.C1) FROM T1 WHERE (SELECT COUNT(*)FROM T2 WHERE T2.C2=T1.C2 > 0)

(2). SELECT SUM(T1.C1) FROM T1 WHERE EXISTS(SELECT * FROM T2 WHERE T2.C2=T1.C2)

后者的效率显然要高于前者，因为后者不会产生大量锁定的表扫描或是索引扫描。如果你想校验表里是否存在某条纪录，不要用count(*)那样效率很低，而且浪费服务器资源。可以用EXISTS代替。
  1. 能够用BETWEEN的就不要用IN
  2. 能够用DISTINCT的就不用GROUP BY
  3. 尽量不要用SELECT INTO语句。SELECT INTO 语句会导致表锁定，阻止其他用户访问该表

  4. 必要时强制查询优化器使用某个索引

    SELECT * FROM T1 WHERE nextprocess = 1 AND processid IN (8,32,45)

    改成：

    SELECT * FROM T1 (INDEX = IX_ProcessID) WHERE nextprocess = 1 AND processid IN (8,32,45)

    则查询优化器将会强行利用索引IX_ProcessID 执行查询。

  5. 消除对大型表行数据的顺序存取

    某些形式的WHERE子句强迫优化器使用顺序存取。如：

    SELECT * FROM orders WHERE (customer_num=104 AND order_num>1001) OR order_num=1008

    解决办法可以使用并集来避免顺序存取：

    SELECT * FROM orders WHERE customer_num=104 AND order_num>1001 UNION SELECT * FROM orders WHERE order_num=1008

    这样就能利用索引路径处理查询。

  6. 尽量避免在索引过的字符数据中，使用非打头字母搜索。这也使得引擎无法利用索引

    如下例子：

    SELECT * FROM T1 WHERE NAME LIKE ‘%L%’

SELECT * FROM T1 WHERE SUBSTING(NAME,2,1)=’L’

SELECT * FROM T1 WHERE NAME LIKE ‘L%’

    第三个查询能够使用索引来加快操作，不要习惯性的使用 ‘%L%’这种方式(会导致全表扫描)，如果可以使用`L%’相对来说更好

  7. 虽然UPDATE、DELETE语句的写法基本固定，但是还是对UPDATE语句给点建议

(1). 尽量不要修改主键字段。

(2). 当修改VARCHAR型字段时，尽量使用相同长度内容的值代替。

(3). 尽量最小化对于含有UPDATE触发器的表的UPDATE操作。

(4). 避免UPDATE将要复制到其他数据库的列。

(5). 避免UPDATE建有很多索引的列。

(6). 避免UPDATE在WHERE子句条件中的列。

  1. 能用UNION ALL就不要用UNION

    UNION ALL不执行SELECT DISTINCT函数，这样就会减少很多不必要的资源

    UNION从两个互不关联的表中返回数据，这就意味着不会出现重复的行，同时也必须对数据进行排序，我们知道排序是非常耗费资源的，特别是对大表的排序。

    UNION ALL可以大大加快速度，如果你已经知道你的数据不会包括重复行，或者你不在乎是否会出现重复的行，在这两种情况下使用UNION ALL更适合

    此外，还可以在应用程序逻辑中采用某些方法避免出现重复的行

  2. 字段数据类型优化

(1). 避免使用NULL类型：NULL对于大多数数据库都需要特殊处理，MySQL也不例外，它需要更多的代码，更多的检查和特殊的索引逻辑，有些开发人员完全没有意识到，创建表时NULL是默认值，但大多数时候应该使用NOT NULL，或者使用一个特殊的值，如0，-1作为默认值。

(2). 尽可能使用更小的字段，MySQL从磁盘读取数据后是存储到内存中的，然后使用cpu周期和磁盘I/O读取它，这意味着越小的数据类型占用的空间越小，从磁盘读或打包到内存的效率都更好，但也不要太过执着减小数据类型，要是以后应用程序发生什么变化就没有空间了。修改表将需要重构，间接地可能引起代码的改变，这是很头疼的问题，因此需要找到一个平衡点。

(3). 优先使用定长型

  1. 关于大数据量limit分布的优化（当偏移量特别大时，limit效率会非常低）

    附上一个提高limit效率的简单技巧，在覆盖索引(覆盖索引用通俗的话讲就是在select的时候只用去读取索引而取得数据，无需进行二次select相关表)上进行偏移，而不是对全行数据进行偏移。可以将从覆盖索引上提取出来的数据和全行数据进行联接，然后取得需要的列，会更有效率，看看下面的查询：

    select film_id, description from sakila.film order by title limit 50, 5;

    如果表非常大，这个查询最好写成下面的样子：

    select film.film_id, film.description from sakila.film inner join(select film_id from sakila.film order by title liimit 50,5) as film usinig(film_id);

  2. 程序中如果一次性对同一个表插入多条数据

    比如以下语句：

    insert into person(name,age) values(‘xboy’, 14);

insert into person(name,age) values(‘xgirl’, 15);

insert into person(name,age) values(‘nia’, 19);

    把它拼成一条语句执行效率会更高.

    insert into person(name,age) values(‘xboy’, 14), (‘xgirl’, 15),(‘nia’, 19);

  3. 不要在选择的栏位上放置索引，这是无意义的。应该在条件选择的语句上合理的放置索引，比如where，order by

    例如：

    SELECT id,title,content,cat_id FROM article WHERE cat_id = 1;

    上面这个语句，你在id/title/content上放置索引是毫无意义的，对这个语句没有任何优化作用。但是如果你在外键cat_id上放置一个索引，那作用就相当大了。

  4. ORDER BY语句的MySQL优化

(1). ORDER BY + LIMIT组合的索引优化。如果一个SQL语句形如：

    SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort] LIMIT [offset],[LIMIT];

这个SQL语句优化比较简单，在[sort]这个栏位上建立索引即可。

(2). WHERE + ORDER BY + LIMIT组合的索引优化，形如：

    SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [VALUE] ORDER BY [sort] LIMIT [offset],[LIMIT];

这个语句，如果你仍然采用第一个例子中建立索引的方法，虽然可以用到索引，但是效率不高。更高效的方法是建立一个联合索引(columnX,sort)

(3). WHERE + IN + ORDER BY + LIMIT组合的索引优化，形如：

    SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] IN ([value1],[value2],…) ORDER BY [sort] LIMIT [offset],[LIMIT];

这个语句如果你采用第二个例子中建立索引的方法，会得不到预期的效果（仅在[sort]上是using index，WHERE那里是using where;using filesort），理由是这里对应columnX的值对应多个。

(4). WHERE+ORDER BY多个栏位+LIMIT，比如:

    SELECT * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10;

对于这个语句，大家可能是加一个这样的索引:(x,y,uid)。但实际上更好的效果是(uid,x,y)。这是由MySQL处理排序的机制造成的。

其他

工具

  1. 分析工具

    性能，结构和数据分析工具

    Anemometer – 一个 SQL 慢查询监控器。
innodb-ruby – 一个对 InooDB 格式文件的解析器，用于 Ruby 语言。
innotop – 一个具备多种特性和可扩展性的 MySQL 版 ‘top’ 工具。
pstop – 一个针对 MySQL 的类 top 程序，用于收集，汇总以及展示来自 performance_schema 的信息。
mysql-statsd – 一个收集 MySQL 信息的 Python 守护进程，并通过 StatsD 发送到 Graphite。

  2. 备份

    备份/存储/恢复 工具

    MyDumper – 逻辑的，并行的 MySQL 备份/转储工具。
MySQLDumper – 基于 web 的开源备份工具-对于共享虚拟主机非常有用。
mysqldump-secure – 将加密，压缩，日志，黑名单和 Nagios 监控一体化的 mysqldump 安全脚本。
Percona Xtrabackup – 针对 MySQL 的一个开源热备份实用程序——在服务器的备份期间不会锁定你的数据库。

  3. 性能测试

    给你的服务器进行压测的工具

    iibench-mysql -基于 Java 的 MySQL/Percona/MariaDB 索引进行插入性能测试工具。
Sysbench – 一个模块化，跨平台以及多线程的性能测试工具。

  4. 聊天应用

    集成进聊天室的脚本

    Hubot MySQL ChatOps

  5. 配置

    MySQL 配置实例及指导

    mysql-compatibility-config – 使 MySQL 配置起来更像新的（或先前）的 MySQL 版本。

  6. 连接器

    多种编程语言的 MySQL 连接器

    Connector/Python – 一个对于 Python 平台和开发的标准化数据库驱动程序。
go-sql-driver – 一个 Go 语言的轻量级、极速的 MySQL 驱动程序。
libAttachSQL – libAttachSQL 是 MySQL 服务器的一个轻量级，非阻塞的 C 语言 API。
MariaDB Java Client – 针对 Java 应用且经过 LGPL 许可的 MariaDB 客户端库。
MySQL-Python – 一个 Python 语言的 MySQL 数据库连接器。
PHP mysqlnd – 针对 MySQL 的 MySQL 本地驱动，弃用过时的 libmysql 基础驱动。

  7. 开发

    支持 MySQL 相关开发的工具

    Flywaydb – 数据库迁移;任意情况下轻松可靠地演变你的数据库版本。
Liquibase – 对你的数据库进行源代码控制。
Propagator – 集中模式和数据部署在一个多维拓扑上。

  8. GUI

    前端和应用的 GUI

    Adminer – 一个 PHP 编写的数据库管理工具。
HeidiSQL – Windows 下的 MySQL 图形化管理工具。
MySQL Workbench – 提供给数据库管理员和开发人员进行数据库设计和建模的集成工具环境;SQL 开发;数据库管理。
phpMyAdmin – 一个 PHP 写成的开源软件，意图对 web 上的 MySQL 进行管理。
SequelPro – 一个 mac 下运行 MySQL 的数据库管理应用程序。
mycli – 一个带自动补全和语法高亮的终端版 MySQL 客户端

  9. HA

    高可用解决方案

    Galera Cluster – 一个基于同步复制的多主机集群方案。
MHA – 针对 MySQL 的优秀高可用管理器及工具
MySQL Fabric – 一个用于管理 MySQL 服务器场（Server Farms）的可扩展框架。
Percona Replication Manager – 针对 MySQL 的异步复制管理代理。支持以文件和 GTID 为基础的复制，使用 booth 实现的地理分布式集群。

  10. 代理

    MySQL 代理

    MaxScale – 开源，以数据库为中心的代理。
Mixer – Go 实现的一个 MySQL 代理，目的为 MySQL 分片提供一个简单的解决方案。
MySQL Proxy – 一个处于你的客户端和 MySQL 服务端之间的简单程序，它可以检测、分析或者改变它们的通信。
ProxySQL – 高性能的 MySQL 代理。

  11. 复制

    复制相关的软件

    orchestrator – 对 MySQL 复制拓扑管理并可视化的工具。
Tungsten Replicator – MySQL 的一个高性能、开源、数据复制引擎。

  12. 模式

    附加模式

    common_schema – MySQL DBA 的框架， 提供一个具有函数库、视图库和查询脚本的解释器。
sys – 一个视图、函数和过程的集合，以帮助 MySQL 管理人员更加深入理解 MySQL 数据库的使用。

  13. 服务器

    MySQL server flavors
MariaDB – MySQL server 的一个由社区开发的分支。
MySQL Server & MySQL Cluster – Oracle 官方的 MySQL server 和 MySQL 集群分布。
Percona Server – 一个加强版的 MySQL 替代品
WebScaleSQL – WebScaleSQL，5.6 版本，基于 MySQL 5.6 社区版本。

  14. 分片

    分片解决方案/框架

    vitess – 对于大规模的 web 服务，vitess 提供服务和工具以便于 MySQL 数据库的缩放。
jetpants – 一个自动化套件，用于管理大规模分片集群，由 Tumblr 开发。

  15. 工具包

    工具包，通用脚本

    go-mysql – 一个纯 go 的库，用于处理 MySQL 的网络协议和复制。
MySQL Utilities – 一个命令行实用程序的集合，Python 语言编写，用于维护和管理单一或多层的 MySQL。
Percona Toolkit – 一个先进的命令行工具集，用于执行对于 MySQL 服务器和系统过于困难或复杂的任务。
openark kit – 一组实用的工具，解决日常的维护工作，包括一些复杂的或需徒手操作的，用 Python 语言编写。
UnDROP – 一个用来恢复删除或损坏的 InnoDB 表中数据的工具。

  16. 资源

    在这个阶段，“资源”不包括网站，博客，幻灯片，演示视频等。这些资源列表的大小令人恐惧。

  17. 会议

    围绕 MySQL 进行公开，经常性的大会。

    FOSDEM – 一个软件开发人员见面、交流思想与协作的活动。每年在 Brussels 举行。提供 “MySQL & friends” 开发工作室。
MySQL Central – Oracle 年度 MySQL 大会，是 Oracle Open World 的一部分。
Percona Live – MySQL 和 OpenStack 的重要会议。
SCALE – 一个每年在南加州举办，由社区组织的 Linux 和 开源软件大会。由当地 MySQL社区组织并以MySQL社区日的名义举办。

  18. 电子书

    MySQL 电子书以及相关材料。

    SQL-exercise – 包含几个 SQL 练习，包括模式描述，用 SQL 语句去建立模式，SQL 的问题及解决方案。以 wikibook SQL 练习为基础。

  19. 媒体

    公开，持续的视频和音频转播。这不包括会议演讲那令人恐惧的资源列表大小。

    DBHangOps – 两周一次由各种各样的 MySQL 社区人员参加的 google 聚集大会，大会的日常就是谈论一切关于 MySQL 的东西。
OurSQL Podcast – MySQL 数据库社区播客。

  20. 新闻周刊

    顾名思义，新闻周刊需要一个 email 地址。下面列出周刊只需要一个 email 地址。

    Weekly MySQL News – 非官方的 MySQL新闻摘要，包含关于MySQL的各类信息。

安全汇总

  1. 三招搞挂MySQL

    产生大量undo日志撑满磁盘空间导致MySQL不可用

    开启一个事务，反复地更新一条记录而不提交，将会产生大量的undo日志，使得磁盘空间爆满，导致MySQL不可用

    错误信息如下：

    No space left on device

>定义大量用户变量连接耗尽MySQL的内存

会导致MySQL占用的内存急剧上涨，最后被操作系统kill掉。而且，不再需要有更新记录的权限，只需要有登录数据库的权限即可

通过pidstat观察MySQL占用的内存：

    yum install sysstat

    pidstat

    pidstat 2 -r -p 6879

这个问题是完全可以避免的。MySQL支持在创建用户的时候，限制用户使用的资源。

可以限制的资源包括：

    每小时的查询次数
    每小时的更新次数
    每小时的连接次数
    同时建立的连接数

使用方式如下所示：

    CREATE USER 'john'@'localhost' IDENTIFIED BY 'john';
    GRANT ALL ON customer.* TO 'john'@'localhost'
        WITH MAX_QUERIES_PER_HOUR 20
            MAX_UPDATES_PER_HOUR 10
            MAX_CONNECTIONS_PER_HOUR 5
            MAX_USER_CONNECTIONS 2;


>触发MySQL的bug。

几点建议：

    特别大的事务会占用特别多的资源，甚至出现占满磁盘空间的情况，要避免特别大的事务；
    限制用户使用的资源，避免不良用户恶意破坏；
    紧随社区的脚步，关注社区报告和修复的bug，必要时升级数据库版本，以免遇到已知bug；
    新功能一般bug较多，不要上得太快，避免踩到未知bug。

错误处理汇总

  1. com.mysql.jdbc.PacketTooBigException: Packet for query is too large

    找到/etc/my.cnf文件，在[mysqld]后面，修改max_allowed_packet=50M，重启mysql，问题解决。

  1. 连接Mysql巨慢

    附一： 摘自http://gcoder.blogbus.com/logs/31907502.html
    两台在同一网段的机器, 连接同一个数据库服务器, 一台的速度是另一台的100倍, 数据库主机是域名表示的. 怎么都想不明白?
    后来查看两台机器的 /etc/resolv.conf, 发现配置不一样, 改成速度快的那台机器的配置, 速度那上就上来了.
    域名解析也可以导致网络程序慢, 又一收获. 另外, mysql还有DNS反向解析的问题, 也可能导致速度慢, 可以在mysql的配置文件中, 把DNS反向解析关掉.

    附二： 摘自
    当远程访问mysql时，mysql会解析域名，会导致访问速度很慢，加上下面这个配置可解决此问题
    禁止mysql做域名解析
    [mysqld]
    skip-name-resolve

    PHP远程连接MYSQL速度慢,有时远程连接到MYSQL用时4-20秒不等,本地连接MYSQL正常,出现这种问题的主要原因是,默认安装的MYSQL开启了DNS的反向解析,在MY.INI(WINDOWS系统下)或MY.CNF(UNIX或LINUX系统下)文件的[mysqld]下加入skip-name-resolve这一句。

a