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

　　from：cnblogs。comwzh2010p17091055。htm1背景
　　我们的业务服务随着功能规模扩大，用户量扩增，流量的不断的增长，经常会遇到一个问题，就是数据存储服务响应变慢。
　　导致数据库服务变慢的诱因很多，而RD最重要的工作之一就是找到问题并解决问题。
　　下面以MySQL为例子，我们从几个角度分析可能产生原因，并讨论解决的方案。2定位慢查询的原因并优化2。1慢查询的分析
　　开启SlowLog，默认是关闭的，由参数slowquerylog决定，在MySQL命令终端中输入下面的命令：是否开启，这边为开启，默认情况下是设置慢查询阈值，单位是s，默认为10s，这边的意思是查询耗时超过0。5s，便会记录到慢查询日志里面setgloballongquerytime0。5；确定慢查询日志的文件名和路径VariablenameValueslowquerylogfileusrlocalmysqldataMacintoshdeMacBookProslow。log1rowinset（0。00sec）检查慢查询的详细指标，可以看到下面slowquerylogON，longquerytime0。5，都是因为我们调整过的VariablenameValuebinlogrowsquerylogeventsOFFftqueryexpansionlimit20havequerycacheNOlogqueriesnotusingindexesOFFlogthrottlequeriesnotusingindexes0longquerytime0。500000queryallocblocksize8192querypreallocsize8192slowquerylogONslowquerylogfileusrlocalmysqldataMacintoshdeMacBookProslow。log10rowsinset（0。01sec）
　　配置好之后，就会按照阈值默认把慢查询日志收集下来，可以到对应的目录下分析具体的慢请求原因。2。2使用Explain进行查询语句分析2。2。1分析过程举例
　　很多时候我们在评审RD同学代码和SQL脚本的时候，上下文和使用环境不了解，不能做出很准确的判断。
　　这时候使用Explain分析SQL的执行计划就显得非常有用，拿到具体环境中Run一下就能看出很多问题。
　　举个例子：
　　模拟一个千万级别的雇员表，我们在没有做索引的字段上做一下查询看看，在500W数据中查询一个名叫LsHfFJA的员工，消耗2。239S，获取到一条id为4582071的数据。
　　再看看他的执行计划，扫描了4952492条数据才找到该行数据：mysqlexplainselectfromempwhereempnameLsHfFJA；idselecttypetabletypepossiblekeyskeykeylenrefrowsExtra1SIMPLEempALLNULLNULLNULLNULL4952492Usingwhere1rowinset
　　这就是无索引或者索引不合理的结果，这个时候我们就可以根据实际情况进行查询优化了。2。2。2Explain需要关注的指标
　　比较核心要关注的字段一般有selecttype、type、possiblekeys、key、rows、Extra等
　　我们来一个个说明：selecttype：代表表示查询中每个select子句的类型，是简单查询还是联合查询还是子查询，一目了然。咱们上面的例子是SIMPLE，代表简单查询，其他枚举参考下列表格：
　　selecttype的值
　　解释
　　SIMPLE
　　简单查询（不使用关联查询或子查询）
　　PRIMARY
　　如果包含关联查询或者子查询，则最外层的查询部分标记primary
　　UNION
　　联合查询（UNION）中第二个及后面的查询
　　DEPENDENTUNION
　　UNION中的第二个或后面的SELECT语句，取决于外面的查询
　　UNIONRESULT
　　UNION的结果，union语句中第二个select开始后面所有select
　　SUBQUERY
　　字查询中的第一个擦讯
　　DEPENDENTSUBQUERY
　　子查询中的第一个查询，并且依赖外部查询
　　DERIVED
　　派生表的SELECT，FROM子句的子查询
　　MATERIALIZED
　　被物化的子查询
　　UNCACHEABLESUBQUERY
　　一个子查询的结果不能被缓存，必须重新评估外链接的第一行type：表示MySQL在表中查找所需数据的方式，也称访问类型，咱们上面的例子是All，代表全表扫描，是非常差的模式，其他枚举参考下列表格：
　　type的值
　　解释
　　system
　　查询对象表只有一行数据，且只能用于MyISAM和Memory引擎的表，这是最好的情况
　　const
　　基于主键或唯一索引查询，最多返回一条结果
　　eqref
　　类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primarykey或者uniquekey作为关联条件
　　ref
　　表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
　　fulltext
　　全文检索
　　refornull
　　表连接类型是ref，但进行扫描的索引列中可能包含NULL值
　　indexmerge
　　利用多个索引
　　uniquesubquery
　　子查询中使用唯一索引
　　indexsubquery
　　子查询中使用普通索引
　　range
　　只检索给定范围的行，使用一个索引来选择行
　　index
　　FullIndexScan，index与ALL区别为index类型只遍历索引树
　　ALL
　　FullTableScan，MySQL将遍历全表以找到匹配的行possiblekeys：应该或建议使用的索引
　　表示MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。这个趋向于指导性作用。key：实际使用的索引，没有的情况下为NULL
　　显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULLrows：预估扫描了了多少行，咱们上面的例子4952492，非常不合理。
　　表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数。基本表现为实际扫描过的行数。3一些使用上的规范3。1分析是否有不合理的查询
　　以下是我们团队的准入规范，也是CodeReview标准。尽量避免使用select，join语句使用select可能导致只需要访问索引即可完成的查询需要回表取数。
　　一种是可能取出很多不需要的数据，对于宽表来说，这是灾难；一种是尽可能避免回表，因为取一些根本不需要的数据而回表导致性能低下，是很不合算。严禁使用selectfromtname，不加任何where条件，道理一样，这样会变成全表全字段扫描。MySQL中的text类型字段存储：不与其他普通字段存放在一起，因为读取效率低，也会影响其他轻量字段存取效率。大宽表、大字段表，整体性能也不好。如果不需要text类型字段，又使用了select，会让该执行消耗大量io，效率也很低下在取出字段上可以使用相关函数，但应尽可能避免出现now（），rand（），sysdate（）等不确定结果的函数，在Where条件中的过滤条件字段上严禁使用任何函数，包括数据类型转换函数。大量的计算和转换会造成效率低下，这个在索引那边也描述过了。分页查询语句全部都需要带有排序条件，否则很容易引起乱序用in（）union替换or，效率会好一些，并注意in的个数小于300严禁使用前缀进行模糊前缀查询。如下，这种查询会导致扫描表：selecta，b，可以使用模糊后缀查询如：selecta，尽量避免使用子查询，可以把子查询优化为join操作，通常子查询在in子句中，且子查询中为简单SQL（不包含union、groupby、orderby、limit从句）时，才可以把子查询转化为关联查询进行优化。子查询性能差的原因：子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响；特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大；由于子查询会产生大量的临时表也没有索引，所以会消耗过多的CPU和IO资源，产生大量的慢查询。在多表join中，尽量选取结果集较小的表作为驱动表，来join其他表。分页查询，当limit起点较高时，可先用过滤条件进行过滤，如下。原理参考这篇如selecta，b，cfromt1limit10000，20；优化为：selecta，b，cfromt1whereid10000limit20；3。2检查是否有不合理的索引使用
　　建议参考笔者这篇《构建高性能索引（策略篇）》，比较完整索引区分度（0。2）
　　索引必须创建在索引选择性（区分度）较高的列上，选择性的计算方式为：selecttivitycount（distinctcname）count（）；
　　如果区分度结果小于0。2，则不建议在此列上创建索引，否则大概率会拖慢SQL执行遵循最左前缀，将索引区分度最高的放在左边
　　对于确定需要组成组合索引的多个字段，设计时建议将选择性高的字段靠前放。使用时，组合索引的首字段，必须在where条件中，且需要按照最左前缀规则去匹配。
　　正确理解和计算索引字段的区分度，文中有计算规则，区分度高的索引，可以快速得定位数据，区分度太低，无法有效的利用索引，可能需要扫描大量数据页，和不使用索引没什么差别。禁止使用外键，可以在程序级别来约束完整性varchar、text类型字段如果需要创建索引，必须使用前缀索引。
　　前缀索引计算公式如下，calcullen是数字，长度为1cname字段的最长值，可以逐一比较，对比区分度最高的出来
　　正确理解和计算前缀索引的字段长度，文中有判断规则，合适的长度要保证高的区分度和最恰当的索引存储容量，只有达到最佳状态，才是保证高效率的索引。selectcount（distinctleft（cname，calcullen））count（）单张表的索引数量理论上应控制在5个以内。经常有大批量插入、更新操作表，应尽量少建索引，索引建立的原则理论上是多读少写的场景。ORDERBY，GROUPBY，DISTINCT的字段需要添加在索引的后面，形成覆盖索引联合索引注意最左匹配原则：查询时必须按照从左到右的顺序匹配，MySQL会一直向右匹配索引直到遇到范围查询（、、between、like）然后停止匹配。如：如果建立（depno，empname，job）顺序的索引，job是用不到索引的。depno1andempnameandjob1应需而取策略，查询记录的时候，不要一上来就使用，只取需要的数据，可能的话尽量只利用索引覆盖，可以减少回表操作，提升效率。正确判断是否使用联合索引，应避免索引下推（IPC），减少回表操作，提升效率。避免索引失效的原则：禁止对索引字段使用函数、运算符操作，会使索引失效。这是实际上就是需要保证索引所对应字段的干净度。避免非必要的类型转换，字符串字段使用数值进行比较的时候会导致索引无效。模糊查询value会使索引无效，变为全表扫描，因为无法判断扫描的区间，但是value是可以有效利用索引。索引覆盖排序字段，这样可以减少排序步骤，提升查询效率尽量的扩展索引，非必要不新建索引。比如表中已经有a的索引，现在要加（a，b）的索引，那么只需要修改原来的索引即可。
　　举例子：比如一个品牌表，建立的的索引如下，一个主键索引，一个唯一索引PRIMARYKEY（id），UNIQUEKEYunibranddefine（appid，defineid）
　　实际场景中，建议代码交叉评审，当你同事业务代码中的检索语句如下的时候，应建议调整：selectbrandid，brandnamefromdsbrandsystemwherestatus？anddefineid？andappid？
　　建议改成如下：selectbrandid，brandnamefromdsbrandsystemwhereappid？anddefineid？andstatus？
　　虽然说MySQL的查询优化器会根据实际索引情况进行顺序优化，所以这边不做强制。但是同等条件下还是按照顺序进行排列，比较清晰，并且节省查询优化器的处理。4总结
　　这边仅仅是从查询语句的角度进行分析，实际上缓存服务变慢的可能性很多，不仅仅是慢查询怎么分析（SlowLog、Explain命令）。还应该全面的分析原因，并给出处理方案，如分析SQL脚本合理性、建立索引或优化索引、读写分离、垂直水平分区）、多读少写冷数据做缓存、优化数据库的锁竞争、数据库配置调优、硬件资源升级等等，后面几篇我们慢慢说。