MySQL Slow Sql优化（面向研发）
MySQL Slow Sql优化（面向研发）
一、获取慢日志
1、即时慢日志：只发送一次
2、订阅慢日志：按周期定时发送，添加定时任务
3、报警触发推送慢日志邮件
二、准备工作
1、慢日志切割脚本，取出指定时间段的慢日志
2、监控报警触发脚本，取报警前一段时间慢日志
3、结合资产信息获取，发送给对应研发
三、邮件内容分为两个文件
1、slow.log切割
文件名称格式：1.1.1.1_slow_2019-06-09_01_06_33.txt
# Time: 190609 0:35:32# User@Host: appuser[appuser] @ [192.168.10.120]# Query_time: 9.078241 Lock_time: 0.000086 Rows_sent: 38148 Rows_examined: 4836413SET timestamp=1560011732;select Id as Id,Type as Type,ExtType as ExtType,RangeType as RangeType,TimeBegin as TimeBegin,TimeEnd as TimeEnd,CheckState as CheckState,DeleteState as DeleteState,PromoState as PromoState,Created as Created,Modified as Modified,Source as Source,Rsn as Rsn,exttags as exttags from info7 where Modified >= date_format(adddate(now(), -1), '%Y%m%d') or Created >= date_format(adddate(now(), -1), '%Y%m%d'); |
|---|
通过慢日志可以看出以下信息：
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
# 220ms user time, 10ms system time, 24.68M rss, 205.27M vsz# Current date: Sun Jun 9 01:06:34 2019# Hostname: HOSTNAME# Files: /data/log/192.168.10.120_slow_2019-06-09_01_06_33.txt# Overall: 512 total, 2 unique, 0.56 QPS, 10.51x concurrency _____________# Time range: 2019-06-09 00:35:16 to 00:50:33# Attribute total min max avg 95% stddev median# ============ ======= ======= ======= ======= ======= ======= =======# Exec time 9638s 7s 29s 19s 27s 6s 18s# Lock time 56ms 50us 1ms 108us 185us 60us 93us# Rows sent 42.01M 36.57k 136.42k 84.03k 130.04k 46.69k 123.85k# Rows examine 2.66G 4.61M 6.61M 5.32M 5.99M 666.79k 5.71M# Query size 201.75k 375 432 403.50 420.77 28.50 420.77 # Profile# Rank Query ID Response time Calls R/Call V/M Item# ==== ================== =============== ===== ======= ===== ============# 1 0x6BF0169C881F223C 5335.8995 55.4% 256 20.8434 0.85 SELECT operateinfo_?# 2 0x6E463DB0962A2012 4302.3784 44.6% 256 16.8062 3.36 SELECT promotioninfo_? # Query 1: 0.40 QPS, 8.26x concurrency, ID 0x6BF0169C881F223C at byte 110094# This item is included in the report because it matches --limit.# Scores: V/M = 0.85# Time range: 2019-06-09 00:39:47 to 00:50:33# Attribute pct total min max avg 95% stddev median# ============ === ======= ======= ======= ======= ======= ======= =======# Count 50 256# Exec time 55 5336s 13s 29s 21s 27s 4s 19s# Lock time 46 26ms 50us 253us 101us 176us 38us 84us# Rows sent 77 32.74M 129.51k 136.42k 130.94k 130.04k 786.42 130.04k# Rows examine 55 1.49G 5.78M 6.61M 5.94M 5.99M 155.45k 5.99M# Query size 46 93.75k 375 375 375 375 0 375# String:# Hosts 192.168.10.120# Users appuser# Query_time distribution# 1us# 10us# 100us# 1ms# 10ms# 100ms# 1s# 10s+ ################################################################# Tables# SHOW TABLE STATUS LIKE 'operateinfo_521'\G# SHOW CREATE TABLE `operateinfo_521`\G# EXPLAIN /*!50100 PARTITIONS*/select Id as Id,PromoId as PromoId,OperateType as OperateType,OperateState as OperateState,Operator as Operator,OperateName as OperateName,OperateTime as OperateTime,Opinion as Opinion,Created as Created,Modified as Modified,ClientIP as ClientIP,Source as Source,Remark as Remark from info7 where Modified >= date_format(adddate(now(), -3), '%Y%m%d')\G |
|---|
通过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）避免建立冗余和重复索引，（有联合索引，就不用建立最左列的独立索引）。