全面 MySQL 性能优化实际

线上有些性能的列表查问工夫很久。起因是新性能用到旧性能的接口，而这些旧接口的 SQL 查问语句关联 5,6 张表且编写不够标准，导致 MySQL 在执行 SQL 语句时索引生效，进行全表扫描。

查阅网上 SQL 优化的材料后胜利解决了问题，在此从全局角度，记录和总结 MySQL 查问优化相干技巧。

一、优化思路

数据查问慢，不代表 SQL 语句写法有问题。首先，咱们须要找到问题的源头能力“隔靴搔痒”。上面用一张流程图展现 MySQL 优化的思路：

从图中能够分明地看出，导致数据查问慢的起因有多种，如：

• 缓存生效，在此一段时间内因为高并发拜访导致 MySQL 服务器解体。
• SQL 语句编写问题。
• MySQL 服务器参数问题。
• 硬件配置限度 MySQL 服务性能问题等。

二、查看 MySQL 运行状态的值

如果零碎的并发申请数不高，且查问速度慢，能够疏忽该步骤间接进行 SQL 语句调优步骤。

执行命令：

show status

因为返回后果太多，此处不贴出后果。其中，再返回的后果中，咱们次要关注“Queries”、“Threadsconnected”和“Threadsrunning”的值，即查问次数、线程连接数和线程运行数。

咱们能够通过执行如下脚本监控 MySQL 服务器运行的状态值

!/bin/bashwhile truedomysqladmin -uroot -p” 明码 ” ext | awk ‘/Queries/{q=$4}/Threads_connected/{c=$4}/Threads_running/{r=$4}END{printf(“%d %d %dn”,q,c,r)}’ >> status.txtsleep 1done

执行该脚本 24 小时，获取 status.txt 里的内容，再次通过 awk 计算 == 每秒申请 MySQL 服务的次数 ==

awk ‘{q=$1-last;last=$1}{printf(“%d %d %dn”,q,$2,$3)}’ status.txt

复制计算好的内容到 Excel 中生成图表察看数据周期性。

如果察看的数据有周期性的变动，如上图的解释，须要批改缓存生效策略。

例如：

通过随机数在 [3,6,9] 区间获取其中一个值作为缓存生效工夫，这样扩散了缓存生效工夫，从而节俭了一部分内存的耗费。

当拜访高峰期时，一部分申请分流到未生效的缓存，另一部分则拜访 MySQL 数据库，这样缩小了 MySQL 服务器的压力。

三、获取须要优化的 SQL 语句

3.1 形式一：查看运行的线程

执行命令：

show processlist

返回后果：

mysql> show processlist;+—-+——+———–+——+———+——+———-+——————+| Id | User | Host     | db   | Command | Time | State   | Info             |+—-+——+———–+——+———+——+———-+——————+| 9 | root | localhost | test | Query   |   0 | starting | show processlist |+—-+——+———–+——+———+——+———-+——————+1 row in set (0.00 sec)

从返回后果中咱们能够理解该线程执行了什么命令 /SQL 语句以及执行的工夫。理论利用中，查问的返回后果会有 N 条记录。

其中，返回的 State 的值是咱们判断性能好坏的要害，其值呈现如下内容，则该行记录的 SQL 语句须要优化：

Converting HEAP to MyISAM # 查问后果太大时，把后果放到磁盘，重大 Create tmp table #创立长期表，重大 Copying to tmp table on disk #把内存长期表复制到磁盘，重大 locked #被其余查问锁住，重大 loggin slow query #记录慢查问 Sorting result #排序

4.2 形式二：开启慢查问日志

在配置文件 my.cnf 中的 [mysqld] 一行下边增加两个参数：

slow_query_log = 1slow_query_log_file=/var/lib/mysql/slow-query.loglong_query_time = 2log_queries_not_using_indexes = 1

• 其中，slowquerylog = 1 示意开启慢查问。
• slowquerylogfile 示意慢查问日志寄存的地位。
• longquerytime = 2 示意查问 >=2 秒才记录日志。
• logqueriesnotusing_indexes = 1 记录没有应用索引的 SQL 语句。

留神：slowquerylog_file 的门路不能轻易写，否则 MySQL 服务器可能没有权限将日志文件写到指定的目录中。倡议间接复制上文的门路。

批改保留文件后，重启 MySQL 服务。在 /var/lib/mysql/ 目录下会创立 slow-query.log 日志文件。连贯 MySQL 服务端执行如下命令能够查看配置状况。

show variables like ‘slow_query%’;show variables like ‘long_query_time’;

测试慢查问日志：

mysql> select sleep(2);+———-+| sleep(2) |+———-+|       0 |+———-+1 row in set (2.00 sec)

关上慢查问日志文件

[root@localhost mysql]# vim /var/lib/mysql/slow-query.log/usr/sbin/mysqld, Version: 5.7.19-log (MySQL Community Server (GPL)). started with:Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sockTime                 Id Command   Argument# Time: 2017-10-05T04:39:11.408964Z# User@Host: root[root] @ localhost [] Id:     3# Query_time: 2.001395 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0use test;SET timestamp=1507178351;select sleep(2);

咱们能够看到方才执行了 2 秒的 SQL 语句被记录下来了。

尽管在慢查问日志中记录查问慢的 SQL 信息，然而日志记录的内容密集且不易查阅。因而，咱们须要通过工具将 SQL 筛选进去。

MySQL 提供 mysqldumpslow 工具对日志进行剖析。咱们能够应用 mysqldumpslow --help 查看命令相干用法。

罕用参数如下：

-s：排序形式，后边接着如下参数       c：拜访次数       l：锁定工夫       r：返回记录       t：查问工夫   al：均匀锁定工夫   ar：均匀返回记录书   at：均匀查问工夫   -t：返回后面多少条的数据   -g：翻遍搭配一个正则表达式，大小写不敏感

案例：

获取返回记录集最多的 10 个 sql

mysqldumpslow -s r -t 10 /var/lib/mysql/slow-query.log

获取拜访次数最多的 10 个 sql

mysqldumpslow -s c -t 10 /var/lib/mysql/slow-query.log

获取依照工夫排序的前 10 条外面含有左连贯的查问语句

mysqldumpslow -s t -t 10 -g “left join” /var/lib/mysql/slow-query.log

四、剖析 SQL 语句

4.1 形式一：explain

筛选出有问题的 SQL，咱们能够应用 MySQL 提供的 explain 查看 SQL 执行打算状况（关联表，表查问程序、索引应用状况等）。

用法：

explain select * from category;

返回后果：

mysql> explain select * from category;+—-+————-+———-+————+——+—————+——+———+——+——+———-+——-+| id | select_type | table   | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+—-+————-+———-+————+——+—————+——+———+——+——+———-+——-+| 1 | SIMPLE     | category | NULL       | ALL | NULL         | NULL | NULL   | NULL |   1 |   100.00 | NULL |+—-+————-+———-+————+——+—————+——+———+——+——+———-+——-+1 row in set, 1 warning (0.00 sec)

字段名称

字段解释

id

select 查问序列号。id 雷同，执行程序由上至下；id 不同，id 值越大优先级越高，越先被执行

select_type

查问数据的操作类型，其值如下：1、simple：简略查问，不蕴含子查问或 union；2、primary: 蕴含简单的子查问，最外层查问标记为该值；3、subquery：在 select 或 where 蕴含子查问被标记为该值；4、derived：在 from 列表中蕴含的子查问被标记为该值，MySQL 会递归执行这些子查问，把后果放在长期表；5、union：若第二个 select 呈现在 union 之后，则被标记为该值。若 union 蕴含在 from 的子查问中，外层 select 被标记为 derived；6、union result：从 union 表获取后果的 select；

table

显示该行数据是对于哪张表

partitions

匹配的分区

type

表的连贯类型，其值，性能由高到底排列如下：1、system：表只有一行记录，相当于零碎表；2、const：通过索引一次就找到，只匹配一行数据；3、eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。罕用于主键或惟一索引扫描；4、ref：非唯一性索引扫描，返回匹配某个独自值的所有行。用于 =、< 或 > 操作符带索引的列；5、range：只检索给定范畴的行，应用一个索引来抉择行。个别应用 between、>、< 状况；6、index：只遍历索引树；7、ALL：全表扫描，性能最差；注：前 5 种状况都是现实状况的索引应用状况。通常优化至多到 range 级别，最好能优化到 ref

possible_keys

指出 MySQL 应用哪个索引在该表找到行记录。如果该值为 NULL，阐明没有应用索引，能够建设索引进步性能

key

显示 MySQL 理论应用的索引。如果为 NULL，则没有应用索引查问

key_len

示意索引中应用的字节数，通过该列计算查问中应用的索引的长度。在不损失精确性的状况下，长度越短越好 显示的是索引字段的最大长度，并非理论应用长度

ref

显示该表的索引字段关联了哪张表的哪个字段

rows

依据表统计信息及选用状况，大抵估算出找到所需的记录或所需读取的行数，数值越小越好

filtered

返回后果的行数占读取行数的百分比，值越大越好

extra

蕴含不适合在其余列中显示但非常重要的额定信息，常见的值如下：1、using filesort：阐明 MySQL 会对数据应用一个内部的索引排序，而不是依照表内的索引程序进行读取。呈现该值，应该优化 SQL；2、using temporary：应用了长期表保留两头后果，MySQL 在对查问后果排序时应用长期表。常见于排序 order by 和分组查问 group by。呈现该值，应该优化 SQL；4、using index：示意相应的 select 操作应用了笼罩索引，防止了拜访表的数据行，效率不错；5、using where：where 子句用于限度哪一行；6、using join buffer：应用连贯缓存；distinct：发现第一个匹配后，进行为以后的行组合搜寻更多的行；留神：呈现前 2 个值，SQL 语句必须要优化。

4.2 形式二：profiling

应用 profiling 命令能够理解 SQL 语句耗费资源的详细信息（每个执行步骤的开销）。

4.2.1 查看 profile 开启状况

select @@profiling;

返回后果：

mysql> select @@profiling;+————-+| @@profiling |+————-+|           0 |+————-+1 row in set, 1 warning (0.00 sec)

0 示意敞开状态,1 示意开启

4.2.2 启用 profile

set profiling = 1;

返回后果：

mysql> set profiling = 1;Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select @@profiling;+————-+| @@profiling |+————-+|           1 |+————-+1 row in set, 1 warning (0.00 sec)

在连贯敞开后，profiling 状态主动设置为敞开状态。

4.2.3 查看执行的 SQL 列表

show profiles;

返回后果：

mysql> show profiles;+———-+————+——————————+| Query_ID | Duration   | Query                       |+———-+————+——————————+|       1 | 0.00062925 | select @@profiling           ||       2 | 0.00094150 | show tables                 ||       3 | 0.00119125 | show databases               ||       4 | 0.00029750 | SELECT DATABASE()           ||       5 | 0.00025975 | show databases               ||       6 | 0.00023050 | show tables                 ||       7 | 0.00042000 | show tables                 ||       8 | 0.00260675 | desc role                   ||       9 | 0.00074900 | select name,is_key from role |+———-+————+——————————+9 rows in set, 1 warning (0.00 sec)

该命令执行之前，须要执行其余 SQL 语句才有记录。

4.2.4 查问指定 ID 的执行详细信息

show profile for query Query_ID;

返回后果：

mysql> show profile for query 9;+———————-+———-+| Status               | Duration |+———————-+———-+| starting             | 0.000207 || checking permissions | 0.000010 || Opening tables       | 0.000042 || init                 | 0.000050 || System lock         | 0.000012 || optimizing           | 0.000003 || statistics           | 0.000011 || preparing           | 0.000011 || executing           | 0.000002 || Sending data         | 0.000362 || end                 | 0.000006 || query end           | 0.000006 || closing tables       | 0.000006 || freeing items       | 0.000011 || cleaning up         | 0.000013 |+———————-+———-+15 rows in set, 1 warning (0.00 sec)

每行都是状态变动的过程以及它们继续的工夫。Status 这一列和 show processlist 的 State 是统一的。因而，须要优化的留神点与上文形容的一样。

4.2.5 获取 CPU、Block IO 等信息

show profile block io,cpu for query Query_ID;show profile cpu,block io,memory,swaps,context switches,source for query Query_ID;show profile all for query Query_ID;

五、优化伎俩

次要以查问优化、索引应用和表结构设计方面进行解说。

5.1 查问优化

防止 SELECT *，须要什么数据，就查问对应的字段。

小表驱动大表，即小的数据集驱动大的数据集。如：以 A，B 两表为例，两表通过 id 字段进行关联。

当 B 表的数据集小于 A 表时，用 in 优化 exist；应用 in，两表执行程序是先查 B 表，再查 A 表

select * from A where id in (select id from B)

当 A 表的数据集小于 B 表时，用 exist 优化 in；应用 exists，两表执行程序是先查 A 表，再查 B 表

select * from A where exists (select 1 from B where B.id = A.id)

一些状况下，能够应用连贯代替子查问，因为应用 join，MySQL 不会在内存中创立长期表。

适当增加冗余字段，缩小表关联。

正当应用索引（下文介绍）。如：为排序、分组字段建设索引，防止 filesort 的呈现。更多：来一份 MySQL 索引数据结构及优化清单

5.2 索引应用

5.2.1 适宜应用索引的场景

1、主键主动创立惟一索引 2、频繁作为查问条件的字段 3、查问中与其余表关联的字段 4、查问中排序的字段 5、查问中统计或分组字段

5.2.2 不适宜应用索引的场景

1、频繁更新的字段 2、where 条件中用不到的字段 3、表记录太少 4、常常增删改的表 5、字段的值的差异性不大或重复性高

5.2.3 索引创立和应用准则

• 单表查问：哪个列作查问条件，就在该列创立索引
• 多表查问：left join 时，索引增加到右表关联字段；right join 时，索引增加到左表关联字段
• 不要对索引列进行任何操作（计算、函数、类型转换）
• 索引列中不要应用 !=，<> 非等于
• 索引列不要为空，且不要应用 is null 或 is not null 判断
• 索引字段是字符串类型，查问条件的值要加’’单引号, 防止底层类型主动转换
• 违反上述准则可能会导致索引生效，具体情况须要应用 explain 命令进行查看

5.2.4 索引生效状况

除了违反索引创立和应用准则外，如下状况也会导致索引生效：

1. 含糊查问时，以 % 结尾
2. 应用 or 时，如：字段 1（非索引）or 字段 2（索引）会导致索引生效。
3. 应用复合索引时，不应用第一个索引列。
4. index(a,b,c)，以字段 a,b,c 作为复合索引为例：

5.3.1 抉择适合的数据类型 6.3 数据库表结构设计

应用能够存下数据最小的数据类型

• 应用简略的数据类型。int 要比 varchar 类型在 mysql 解决简略
• 尽量应用 tinyint、smallint、mediumint 作为整数类型而非 int
• 尽可能应用 not null 定义字段，因为 null 占用 4 字节空间
• 尽量少用 text 类型, 非用不可时最好思考分表
• 尽量应用 timestamp 而非 datetime
• 单表不要有太多字段，倡议在 20 以内

5.3.2 表的拆分

当数据库中的数据十分大时，查问优化计划也不能解决查问速度慢的问题时，咱们能够思考拆分表，让每张表的数据质变小，从而进步查问效率。

垂直拆分：将表中多个列离开放到不同的表中。例如用户表中一些字段常常被拜访，将这些字段放在一张表中，另外一些不罕用的字段放在另一张表中。插入数据时，应用事务确保两张表的数据一致性。

程度拆分：依照行进行拆分。例如用户表中，应用用户 ID，对用户 ID 取 10 的余数，将用户数据平均的调配到 0~9 的 10 个用户表中。查找时也依照这个规定查问数据。

5.3.3 读写拆散

个别状况下对数据库而言都是“读多写少”。换言之，数据库的压力少数是因为大量的读取数据的操作造成的。咱们能够采纳数据库集群的计划，应用一个库作为主库，负责写入数据；其余库为从库，负责读取数据。这样能够缓解对数据库的拜访压力。

六、服务器参数调优

6.1 内存相干

• sortbuffersize 排序缓冲区内存大小
• joinbuffersize 应用连贯缓冲区大小
• readbuffersize 全表扫描时调配的缓冲区大小

6.2 IO 相干

1、Innodblogfile_size 事务日志大小 2、Innodblogfilesingroup 事务日志个数 3、Innodblogbuffer_size 事务日志缓冲区大小 4、Innodbflushlogattrx_commit 事务日志刷新策略，其值如下：

• 0：每秒进行一次 log 写入 cache，并 flush log 到磁盘
• 1：在每次事务提交执行 log 写入 cache，并 flush log 到磁盘
• 2：每次事务提交，执行 log 数据写到 cache，每秒执行一次 flush log 到磁盘

6.3 平安相干

1. expirelogsdays 指定主动清理 binlog 的天数
2. maxallowedpacket 管制 MySQL 能够接管的包的大小
3. skipnameresolve 禁用 DNS 查找
4. read_only 禁止非 super 权限用户写权限
5. skipslavestart 级你用 slave 主动复原

6.4 其余

• max_connections 管制容许的最大连接数
• tmptablesize 长期表大小
• maxheaptable_size 最大内存表大小

笔者并没有应用这些参数对 MySQL 服务器进行调优，具体详情介绍和性能成果请参考文章开端的材料或另行百度。

七、硬件选购和参数优化

硬件的性能间接决定 MySQL 数据库的性能。硬件的性能瓶颈，间接决定 MySQL 数据库的运行数据和效率。

作为软件开发程序员，咱们次要关注软件方面的优化内容，以下硬件方面的优化作为理解即可

7.1 内存相干

内存的 IO 比硬盘的速度快很多，能够减少零碎的缓冲区容量，使数据在内存停留的工夫更长，以缩小磁盘的 IO

7.2 磁盘 I/O 相干

应用 SSD 或 PCle SSD 设施，至多取得数百倍甚至万倍的 IOPS 晋升

购买阵列卡同时装备 CACHE 及 BBU 模块，能够显著晋升 IOPS

尽可能选用 RAID-10，而非 RAID-5

7.3 配置 CUP 相干

在服务器的 BIOS 设置中，调整如下配置：

1、抉择 Performance Per Watt Optimized（DAPC）模式，施展 CPU 最大性能 2、敞开 C1E 和 C States 等选项，晋升 CPU 效率 3、Memory Frequency（内存频率）抉择 Maximum Performance

八、参考资料

1、https://dev.mysql.com/doc/refman/5.7/en/show-status.html show status 语法 2、https://dev.mysql.com/doc/refman/5.7/en/show-processlist.html show processlist 语法 3、https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html 线程状态 4、https://dev.mysql.com/doc/refman/5.7/en/explain-output.html explain 语法 5、https://dev.mysql.com/doc/refman/5.7/en/show-profile.html show profile 语法 6、http://blog.csdn.net/nightelve/article/details/17393631 MySQL 服务器参数调优 7、http://blog.csdn.net/qq_22929803/article/details/51237056 MySQL 服务器参数调优 8、http://blog.chinaunix.net/uid-11640640-id-3426908.html 9、https://segmentfault.com/a/1190000006158186 10、http://blog.csdn.net/gzh0222/article/details/7976127