【全网首发】数据库系列：MySQL慢查询分析和性能优化原创
1 背景
我们的业务服务随着功能规模扩大，用户量扩增，流量的不断的增长，经常会遇到一个问题，就是数据存储服务响应变慢。
导致数据库服务变慢的诱因很多，而RD最重要的工作之一就是找到问题并解决问题。
下面以MySQL为例子，我们从几个角度分析可能产生原因，并讨论解决的方案。
2 定位慢查询的原因并优化
2.1 慢查询的分析
开启SlowLog，默认是关闭的，由参数slow_query_log决定，在MySQL命令终端中输入下面的命令：
# 是否开启，这边为开启，默认情况下是off
set global slow_query_log=on;
# 设置慢查询阈值，单位是 s，默认为10s，这边的意思是查询耗时超过0.5s，便会记录到慢查询日志里面
set global long_query_time=0.5;
# 确定慢查询日志的文件名和路径
mysql> show global variables like 'slow_query_log_file';
+---------------------+-------------------------------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------------------------------+
| slow_query_log_file | /usr/local/mysql/data/MacintoshdeMacBook-Pro-slow.log |
+---------------------+-------------------------------------------------------+
1 row in set (0.00 sec)
# 检查慢查询的详细指标，可以看到下面 slow_query_log = ON，long_query_time = 0.5 ，都是因为我们调整过的
mysql> show global variables like '%quer%';
+----------------------------------------+-------------------------------------------------------+
| Variable_name | Value |
+----------------------------------------+-------------------------------------------------------+
| binlog_rows_query_log_events | OFF |
| ft_query_expansion_limit | 20 |
| have_query_cache | NO |
| log_queries_not_using_indexes | OFF |
| log_throttle_queries_not_using_indexes | 0 |
| long_query_time | 0.500000 |
| query_alloc_block_size | 8192 |
| query_prealloc_size | 8192 |
| slow_query_log | ON |
| slow_query_log_file | /usr/local/mysql/data/MacintoshdeMacBook-Pro-slow.log |
+----------------------------------------+-------------------------------------------------------+
10 rows in set (0.01 sec)
配置好之后，就会按照阈值默认把慢查询日志收集下来，可以到对应的目录下分析具体的慢请求原因。
2.2 使用Explain进行查询语句分析
2.2.1 分析过程举例
很多时候我们在评审RD同学代码和SQL脚本的时候，上下文和使用环境不了解，不能做出很准确的判断。
这时候使用Explain分析SQL的执行计划就显得非常有用，拿到具体环境中Run一下就能看出很多问题。
举个例子：
模拟一个千万级别的雇员表，我们在没有做索引的字段上做一下查询看看，在500W数据中查询一个名叫LsHfFJA的员工，消耗 2.239S ，获取到一条id为4582071的数据。
再看看他的执行计划，扫描了4952492 条数据才找到该行数据：
mysql> explain select * from emp where empname='LsHfFJA';
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 4952492 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set
这就是无索引或者索引不合理的结果，这个时候我们就可以根据实际情况进行查询优化了。
2.2.2 Explain需要关注的指标
比较核心要关注的字段一般有 select_type、type、possible_keys、key、rows、Extra等
我们来一个个说明：
- select_type：代表表示查询中每个select子句的类型，是简单查询还是联合查询还是子查询，一目了然。咱们上面的例子是SIMPLE，代表简单查询，其他枚举参考下列表格：
| select_type的值 | 解释 |
|---|---|
| SIMPLE | 简单查询（不使用关联查询或子查询） |
| PRIMARY | 如果包含关联查询或者子查询，则最外层的查询部分标记primary |
| UNION | 联合查询（UNION）中第二个及后面的查询 |
| DEPENDENT UNION | UNION中的第二个或后面的SELECT语句，取决于外面的查询 |
| UNION RESULT | UNION的结果，union语句中第二个select开始后面所有select |
| SUBQUERY | 字查询中的第一个擦讯 |
| DEPENDENT SUBQUERY | 子查询中的第一个查询，并且依赖外部查询 |
| DERIVED | 派生表的SELECT, FROM子句的子查询 |
| MATERIALIZED | 被物化的子查询 |
| UNCACHEABLE SUBQUERY | 一个子查询的结果不能被缓存，必须重新评估外链接的第一行 |
- type：表示MySQL在表中查找所需数据的方式，也称“访问类型”，咱们上面的例子是All，代表全表扫描，是非常差的模式，其他枚举参考下列表格：
| type的值 | 解释 |
|---|---|
| system | 查询对象表只有一行数据，且只能用于MyISAM和Memory引擎的表，这是最好的情况 |
| const | 基于主键或唯一索引查询，最多返回一条结果 |
| eq_ref | 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件 |
| ref | 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值 |
| fulltext | 全文检索 |
| ref_or_null | 表连接类型是ref，但进行扫描的索引列中可能包含NULL值 |
| index_merge | 利用多个索引 |
| unique_subquery | 子查询中使用唯一索引 |
| index_subquery | 子查询中使用普通索引 |
| range | 只检索给定范围的行，使用一个索引来选择行 |
| index | Full Index Scan，index与ALL区别为index类型只遍历索引树 |
| ALL | Full Table Scan， MySQL将遍历全表以找到匹配的行 |
- possible_keys：应该或建议使用的索引
表示MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。这个趋向于指导性作用。
- key：实际使用的索引，没有的情况下为NULL
显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL
- rows：预估扫描了了多少行，咱们上面的例子 4952492 ，非常不合理。
表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数。基本表现为实际扫描过的行数。
3 一些使用上的规范
3.1 分析是否有不合理的查询
★ 以下是我们团队的准入规范，也是CodeReview 标准。
- 尽量避免使用select *，join语句使用select * 可能导致只需要访问索引即可完成的查询需要回表取数。
一种是可能取出很多不需要的数据，对于宽表来说，这是灾难；一种是尽可能避免回表，因为取一些根本不需要的数据而回表导致性能低下，是很不合算。 - 严禁使用select * from t_name，不加任何where条件，道理一样，这样会变成全表全字段扫描。
- MySQL中的text类型字段存储：
- 不与其他普通字段存放在一起,因为读取效率低，也会影响其他轻量字段存取效率。大宽表、大字段表，整体性能也不好。
- 如果不需要text类型字段，又使用了select *，会让该执行消耗大量io，效率也很低下
- 在取出字段上可以使用相关函数，但应尽可能避免出现 now() , rand() , sysdate() 等不确定结果的函数，在Where条件中的过滤条件字段上严禁使用任何函数，包括数据类型转换函数。大量的计算和转换会造成效率低下，这个在索引那边也描述过了。
- 分页查询语句全部都需要带有排序条件 , 否则很容易引起乱序
- 用in()/union替换or，效率会好一些，并注意in的个数小于300
- 严禁使用%前缀进行模糊前缀查询。
-- 如下，这种查询会导致扫描表：
select a,b,c from t_name where a like '%name';
-- 可以使用%模糊后缀查询如：
select a,b from t_name where a like 'name%';
- 尽量避免使用子查询，可以把子查询优化为join操作，通常子查询在in子句中，且子查询中为简单SQL(不包含union、group by、order by、limit从句)时，才可以把子查询转化为关联查询进行优化。子查询性能差的原因：
- 子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响；
- 特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大；
- 由于子查询会产生大量的临时表也没有索引，所以会消耗过多的CPU和IO资源，产生大量的慢查询。
- 在多表join中，尽量选取结果集较小的表作为驱动表，来join其他表。
- 分页查询，当limit起点较高时，可先用过滤条件进行过滤，如下。原理参考这篇
-- 如
select a,b,c from t1 limit 10000,20;
-- 优化为:
select a,b,c from t1 where id>10000 limit 20;
3.2 检查是否有不合理的索引使用
建议参考笔者这篇《构建高性能索引（策略篇）》，比较完整
- 索引区分度（> 0.2）
索引必须创建在索引选择性（区分度）较高的列上，选择性的计算方式为:
selecttivity = count(distinct c_name)/count(*) ;
如果区分度结果小于0.2，则不建议在此列上创建索引，否则大概率会拖慢SQL执行
- 遵循最左前缀，将索引区分度最高的放在左边
对于确定需要组成组合索引的多个字段，设计时建议将选择性高的字段靠前放。使用时，组合索引的首字段，必须在where条件中，且需要按照最左前缀规则去匹配。
正确理解和计算索引字段的区分度，文中有计算规则，区分度高的索引，可以快速得定位数据，区分度太低，无法有效的利用索引，可能需要扫描大量数据页，和不使用索引没什么差别。
-
禁止使用外键，可以在程序级别来约束完整性
-
varchar、text类型字段如果需要创建索引，必须使用前缀索引。
前缀索引计算公式如下，calcul_len 是数字，长度为1 ~ c_name字段的最长值，可以逐一比较，对比区分度最高的出来
正确理解和计算前缀索引的字段长度，文中有判断规则，合适的长度要保证高的区分度和最恰当的索引存储容量，只有达到最佳状态，才是保证高效率的索引。
select count(distinct left(`c_name`,calcul_len))/count(*) from t_name;
- 单张表的索引数量理论上应控制在5个以内。经常有大批量插入、更新操作表，应尽量少建索引，索引建立的原则理论上是多读少写的场景。
- ORDER BY，GROUP BY，DISTINCT的字段需要添加在索引的后面，形成覆盖索引
- 联合索引注意最左匹配原则：查询时必须按照从左到右的顺序匹配，MySQL会一直向右匹配索引直到遇到范围查询(>、<、between、like)然后停止匹配。如：
-- 如果建立(depno,empname,job)顺序的索引，job是用不到索引的。
depno=1 and empname>'' and job=1
- 应需而取策略，查询记录的时候，不要一上来就使用*，只取需要的数据，可能的话尽量只利用索引覆盖，可以减少回表操作，提升效率。
- 正确判断是否使用联合索引，应避免索引下推（IPC），减少回表操作，提升效率。
- 避免索引失效的原则：禁止对索引字段使用函数、运算符操作，会使索引失效。这是实际上就是需要保证索引所对应字段的”干净度“。
- 避免非必要的类型转换，字符串字段使用数值进行比较的时候会导致索引无效。
- 模糊查询’%value%'会使索引无效，变为全表扫描，因为无法判断扫描的区间，但是’value%'是可以有效利用索引。
- 索引覆盖排序字段，这样可以减少排序步骤，提升查询效率
- 尽量的扩展索引，非必要不新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
举例子：比如一个品牌表，建立的的索引如下，一个主键索引，一个唯一索引
PRIMARYKEY (`id`),
UNIQUEKEY `uni_brand_define` (`app_id`,`define_id`)
实际场景中，建议代码交叉评审，当你同事业务代码中的检索语句如下的时候，应建议调整：
select brand_id,brand_name from ds_brand_system where status=? and define_id=? and app_id=?
建议改成如下：
select brand_id,brand_name from ds_brand_system where app_id=? and define_id=? and status=?
虽然说 MySQL的查询优化器会根据实际索引情况进行顺序优化，所以这边不做强制。但是同等条件下还是按照顺序进行排列，比较清晰，并且节省查询优化器的处理。
4 总结
这边仅仅是从查询语句的角度进行分析，实际上缓存服务变慢的可能性很多，不仅仅是慢查询怎么分析（Slow Log、Explain命令）。还应该全面的分析原因，并给出处理方案，如 分析SQL脚本合理性、建立索引或优化索引、读写分离、垂直+水平分区）、多读少写/冷数据 做缓存、优化数据库的锁竞争、数据库配置调优、硬件资源升级 等等，后面几篇我们慢慢说。
欢迎关注公众号【架构与思维】，一群bat一线资深研发、架构师的技术分享！
