MySQL Slow Sql优化（面向研发）

一、获取慢日志

1、即时慢日志：只发送一次

2、订阅慢日志：按周期定时发送，添加定时任务

3、报警触发推送慢日志邮件

二、准备工作

1、慢日志切割脚本，取出指定时间段的慢日志

2、监控报警触发脚本，取报警前一段时间慢日志

3、结合资产信息获取，发送给对应研发

三、邮件内容分为两个文件

1、slow.log切割

文件名称格式：1.1.1.1_slow_2019-06-09_01_06_33.txt

通过慢日志可以看出以下信息：

Sql开始时间：190609 0:35:32

应用用户：appuser

应用IP：192.168.10.120

Sql执行时间：9.078241s

查询结果行数：38148

扫描行数：4836413

具体Sql：select …

2、pt-query-digest工具对slow.log的分析汇总

文件名称：1.1.1.1_pt_slow_2019-06-09_01_06_33.txt

通过pt分析的日志可以看出：

第一部分：总体统计结果

Overall：总共有多少条查询

Time range：查询执行的时间范围

unique：唯一查询数量，即对查询条件进行参数化以后，总共有多少个不同的查询

total：总计 min：最小 max：最大 avg：平均

95%：把所有值从小到大排列，位置位于95%的那个数，这个数一般最具有参考价值

stddev：标准偏差，表示所有值偏离平均值的波动程度 （计算这个是从百分之95来算出的，排除了特别大的数）

median：中位数，把所有值从小到大排列，位置位于中间那个数

第二部分：查询分组统计结果 Rank：所有语句的排名，默认按查询时间降序排列，通过--order-by指定 Query ID：语句的ID，（去掉多余空格和文本字符，计算hash值） Response：总的响应时间 time：该查询在本次分析中总的时间占比 calls：执行次数，即本次分析总共有多少条这种类型的查询语句 R/Call：平均每次执行的响应时间 V/M：响应时间Variance-to-mean的比率 Item：查询对象

第三部分：每一种查询的详细统计结果 由下面查询的详细统计结果，最上面的表格列出了执行次数、最大、最小、平均、95%等各项目的统计。 ID：查询的ID号，和上图的Query ID对应 Databases：数据库名 Users：各个用户执行的次数（占比） Query_time distribution ：查询时间分布, 长短体现区间占比

Tables：查询中涉及到的表 Explain：SQL语句

四、SQL优化利器—explain使用

explain模拟优化器执行SQL语句，在5.6以及以后的版本中，除过select，其他比如insert，update和delete均可以使用explain查看执行计划。 1）select_type：表示SELECT的类型，常见的取值有： SIMPLE(简单表，即不使用表连接或者子查询) PRIMARY(主查询，即外层的查询) UNION(UNION中的第二个或者后面的查询语句) SUBQUERY(子查询中的第一个SELECT)等。 2）table：输出结果的表名 3）type：表示MySQL在表中找到所需行的方式，或者叫访问类型，常见的有：ALL、index、range、ref、eq_ref、const/system、NULL，从左到右，性能由最差到最好。 --type=ALL：全表扫描。 --type=index：索引全扫描，MySQL遍历整个索引来查询。 --type=range：索引范围扫描，常见于<、<=、>、 >=、 between。 --type=ref：使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值的记录。 --type=eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者unique index作为关联条件。 --type=const/system：单表中最多有一个匹配行，查询起来非常迅速，一般主键primary key或者唯一索引unique index进行的查询，通过唯一索引uk_email访问的时候，类型type为const；而从我们构造的仅有一条记录的a表中检索时，类型type为system。 --type=NULL：MySQL不用访问表或者索引，就能直接得到结果。 --类型type还有其他值，如ref_or_null(与ref类似，区别在于条件中包含对NULL的查询)、index_merge(索引合并优化)、unique_subquery(in的后面是一个查询主键字段的子查询)、index_subquery(与unique_subquery类似，区别在于in的后面是查询非唯一索引字段的子查询) 4）possible_keys：表示查询时可能使用的索引。 5）key：表示实际使用的索引。 6）key_len：使用到索引字段的长度。 7）rows：扫描行的数量 8）Extra：执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息。 --Using filesort：说明mysql会对数据适用一个外部的索引排序。而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作称为“文件排序” --Using temporary:使用了临时表保存中间结果，mysql在查询结果排序时使用临时表。常见于排序order by和分组查询group by。 --Using index:表示相应的select操作用使用覆盖索引，避免访问了表的数据行。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查询动作。 --Using where :表明使用where过滤 --Using join buffer:使用了连接缓存 --Impossible WHERE noticedafter reading const tables:5.6之前版本，where子句的值总是false，不能用来获取任何数据。 --no matching row in consttable: 5.7版本 --select tables optimizedaway：在没有group by子句的情况下，基于索引优化min、max操作或者对于MyISAM存储引擎优化count（*），不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。 --distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

五、根据explain执行计划添加索引

索引是数据库优化中最常用也是最重要的手段之一，通过索引通常可以帮助用户解决大多数的SQL性能问题。

1、常用索引分类：

主键：建表时强烈建议带上，且采用整型类型

唯一索引：字段值必须唯一，否则会报冲突

辅助索引：不要每个字段都创建独立的索引，会降低性能

联合索引：选择性高的放左边（唯一度高）

2、添加索引方法： alter table test add constraint primarykey(id); alter table test add unique key uniq_name(name); alter table test add index idx_column(col1,col2,col3);

3、Profiler使用：

MySQL 的 QueryProfiler 是一个使用非常方便的 Query 诊断分析工具，通过该工具可以获取一条Query 在整个执行过程中多种资源的消耗情况。

set profiling = 1;

show profiles;

show profile cpu, block io for query 2;

4、SQL优化：

1）避免左匹配%

2）尽量避免使用子查询，如果有子查询，优先查看子查询执行计划

3）尽可能减少JOIN中Nested Loop的循环次数，采用小结果集的表驱动大结果集的表

4）in后面跟的是小表，exists后面跟的是大表。

5）尽量减少多表关联，同一个SQL多关联（join）一个表，就会多分配一个关联缓存，如果在一个SQL中关联的表越多，所占用的内存也就越大。如果程序中大量的使用了多表关联的操作，同时join_buffer_size设置的也不合理的情况下，就容易造成服务器内存溢出的情况

6）避免使用select *，会消耗更多cpu、io和网络带宽

5、索引优化：

1）函数不支持索引，尽量避免DATE_SUB()等函数使用

2）隐式转换问题

3）如果字段类型较长，如col varchar(300)，建议创建部分匹配索引index(col(20))

4）更新删除操作尽量根据主键操作，减少间隙锁产生，高发下避免死锁

5）禁止给表中的每一列都建立单独的索引

6）限制每张表上的索引数量，建议单张表索引不超过5个

7）出现在SELECT、UPDATE、DELETE语句的WHERE条件的列，和包含在ORDER BY、GROUP BY、DISTINCT中的列，通常建立联合索引效果更好

8）区分度最高的放在联合索引的最左侧（区分度=列中不同值的数量/列的总行数）

9）尽量把字段长度小的列放在联合索引的最左侧（因为字段长度越小，一页能存储的数据量越大，IO性能也就越好）

10）使用最频繁的列放到联合索引的左侧（这样可以比较少的建立一些索引）

11）避免建立冗余和重复索引，（有联合索引，就不用建立最左列的独立索引）。