数据库系列MySQL慢查询分析和性能优化

　　from:cnblogs.com/wzh2010/p/17091055.htm1 背景
　　我们的业务服务随着功能规模扩大，用户量扩增，流量的不断的增长，经常会遇到一个问题，就是数据存储服务响应变慢。
　　导致数据库服务变慢的诱因很多，而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脚本合理性、建立索引或优化索引、读写分离、垂直+水平分区）、多读少写/冷数据 做缓存、优化数据库的锁竞争、数据库配置调优、硬件资源升级 等等，后面几篇我们慢慢说。