MySQL进阶(三)：索引失效、SQL定位及调优（慢查询日志、mysql profile、全日志）

目录一、索引失效问如果就要使用like%关键字%而且索引不失效二、explain三、定位sql0.查询优化1.慢查询的开启并捕获2.explain慢sql分析3.mysql profiles4.全日志不推荐尤其是线上环境一、索引失效关于索引跳转链接在使用索引时如果避免索引失效下面综合各种情况来总结1.全值匹配最好即复合索引的每个列都被作为条件使用了2.遵循最佳左前缀法则若创建的多个列的复合索引在sql中使用时若仅使用该复合索引的非第一列索引会失效即必须包含第一列且中间的列不能丢失顺序不可颠倒否则会从断裂点后面的索引列失效3.不在索引列上做任何操作计算、函数\(自动or手动)类型转换),会导致索引失效而转向全表扫描4.存储引擎不能使用索引中范围条件右边的列即范围条件右边的索引列失效5.尽量使用覆盖索引只访问索引列的的查询索引列与查询列一致减少使用select *6.mysql在使用不等于!或的时候无法使用索引会导致全表扫描7.is null,is not null也无法使用索引8.like以通配符开头%asdmysql索引失效会变成全表扫描的操作但是like通配符结尾adc%索引还是有效的9.字符串不加单引号索引失效原因自动类型转换10少用or,用它来连接时会索引失效关于组合索引如A表中组合索引idxa1,a2,a3,a4,有以下几种情况 0.SELECT * from A WHERE a1a1and a2a2and a4a4 and a3a3; 索引都会被使用 1.SELECT * from A WHERE a1a1and a2a2and a3a3 ORDER BY a4; 索引列都会被使用只是排序的列没有被explain统计 2.SELECT * from A WHERE a1a1and a2a2and a3a3 ORDER BY a4; 排序a4索引失效而是using filesort原因范围后面索引失效 3.SELECT * from A WHERE a1a1and a2a2and a4a4 ORDER BY a3; 索引使用了3个explain统计2个排序其实也使用了索引a4失效 4.SELECT * from A WHERE a1a1and a2a2 ORDER BY a4; 索引使用了2个explain统计2个排序a4失效原因索引使用顺序与组合索引创建列的顺序不可断 5.SELECT * from A WHERE a1a1,a4a4 ORDER BY a2,a3; 使用了a1,a2,a3explain统计1个a4失效无useing filesort 6.SELECT * from A WHERE a1a1,a4a4 ORDER BY a3,a2; 使用了a1explain统计1个a3,a2,a4失效extra显示useing filesort 7.SELECT * from A WHERE a1a1,a2a2 ORDER BY a2,a3; 索引不会失效 8.SELECT * from A WHERE a1a1,a2a2 ORDER BY a3,a2; 索引不会失效 9.SELECT * from A WHERE a1a1 ORDER BY a1 索引不会失效不会有using filesort问如果就要使用like%关键字%而且索引不失效答使用覆盖索引即查询的列都是索引或组合索引10.关于like范围的特例后面的不失效 SELECT * from A where a13 and a2 like aa% and a3a3 a1,a2,a3的索引都不失效 11. SELECT * from A where a13 and a2 like %aa and a3a3 a2与a3失效 12. SELECT * from A where a13 and a2 like %aa% and a3a3 a2与a3失效 13. SELECT * from A where a13 and a2 like a%aa% and a3a3 a1,a2,a3的索引都不失效覆盖索引简单理解sql查询列被所创建的索引字段完全覆盖这样性能好的原因sql直接从索引中读取所查数据不需要读取数据行。另order by与group by相较于索引情况类似。group by基本上都需要进行排序会有临时表。二、explain使用EXPLAIN关键字可以模拟优化器执行SQL查询语句从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈格式explain sql语句2.能干什么表的读取顺序数据读取操作的操作类型哪些索引可以使用哪些索引被实际使用表之间的引用每张表有多少行被优化器查询结果------------------------------------------------------------------------------------------------------------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ------------------------------------------------------------------------------------------------------------------- | 1 | PRIMARY | e | NULL | ref | dept_id_fk | dept_id_fk | 5 | const | 1 | 100.00 | Using where | | 2 | SUBQUERY | d | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index | | 3 | SUBQUERY | dc | NULL | ref | loc_id_fk | loc_id_fk | 5 | const | 21 | 100.00 | Using index | -------------------------------------------------------------------------------------------------------------------注释1.id的意思三种情况1.1 如果是平行返回结果中id相同执行顺序从上到下1.2 如果是子查询返回结果中id会递增:执行顺序是id越大越先执行1.3 id相同\不同同时存在:id越大越先执行,id相同的从上到下执行2.select_type即查询类型常见的值simple:普通查询,sql中不包含子查询或unionprimary:查询中若包含任何的子查询最外层查询则为primarysubquery:在select或where中子查询部分derived:在from中子查询被标记为derived(衍生)临时表union:联合查询union关键后的sql类型为union,外层sql标记derivedunion result:联合查询结果table显示访问表type:与sql是否优化息息相关all:全表扫描indexindex与all区别为index类型只遍历索引树也就是虽然all和index都是读全表但index是索引中读取的而all是从硬盘中读取的range检索给定范围的行使用一个索引来选择行。key列显示使用了哪个索引一般就是出现在where后的between,,,in;ref非唯一性索引扫描返回匹配某个单独值的所有行本质上是一种索引访问。属于查找和扫描的混合体eq_ref唯一性索引扫描对于每个索引键表中只有一条记录与之匹配。常见于主键或唯一索引扫描const表示通过索引一次就找到了const用于比较primary key或unique索引system表只有一行数据可以忽略NULL从最好到最差依次是systemconsteq_refrefrangeindexall一般来说得保证查询至少达到range级别最好能达到refpossible_keys:可能应用在这张表中的索引一个或多个sql中涉及的字段存在索引就会被列出。keys实际使用到的索引可能与possible_keys不同,null即没有使用索引若查询中使用了覆盖索引则该索引仅出现在key列中key_len显示的值为索引字段的最大可能长度并非实际使用长度一般越小越好这点和keys相冲但还是以keys为最优。ref:表示哪个库中的哪个索引被使用最优是constrows根据表统计信息及索引的选用情况大致估算出找到所需记录所需要读取的行数extra:可以有很多值参考性高using filesort说明mysql对数据使用一个外部的索引排序而不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作称为“文件排序”显示内容包含该值表示sql排序性能不好。using temporary:性能比using filesort更差这表示sql产生了临时表保存中间结果mysql在对查询结果排序时使用临时表。常见于order by和分组查询group by。using index:表示相应的sql操作中使用了覆盖索引covering index避免访问了表的数据行效率不错。如果同时出现了using where表明索引被用来执行索引键值的查找如果没有同时出现using where表明索引用来读取数据而非执行查动作三、定位sql0.查询优化1.慢查询的开启并捕获2.explain慢sql分析3.show profile查询sql在mysql服务器里面的执行细节和生命周期情况4.sql数据库服务器的参数调优0.查询优化查询优化分3点遵循小的数据集驱动大的数据集select * from A where id in (select id from B)当B表的数据集必须小于A表的数据集时用in优于existsselect * from A where exists (select 1 from B where B.idA.id)当A的数据集小于B的数据集时用exists优于in.注意A表与B表的id字段应建立索引。order by会不会产生using filesort?如A表中组合索引idxa1,a2,a3,a4,有以下几种情况1.SELECT * from A WHERE a1a1 ORDER BY a1索引不会失效不会有using filesort2.SELECT * from A ORDER BY a1 desc,a2 asc;索引失效会有using filesort3.SELECT * from A ORDER BY a1 desc,a2 desc;索引不会失效4.SELECT * from A ORDER BY a2索引失效会有using filesort5.SELECT * from A ORDER BY a1,a10索引失效有using filesort原因a10不是索引列6.SELECT * from A where a1 in(……) ORDER BY a2,a3索引失效有using filesort原因对于排序来说多个相等条件也是范围查询group by类似order by实质是先排序后分组遵循索引列最佳左前缀where高于having,能写在where限定的条件就不要去having限定了。1.慢查询的开启并捕获mysql的慢查询日志是mysql提供的一种日志记录它用来记录mysql中响应时间超过阙值的语句具体指运行时间超过long_query_time值的sql则会被记录到慢查询日志中。查看慢查询日志是否开启SHOW VARIABLES LIKE %slow_query_log%;设置为开启,设置输出目录set GLOBAL slow_query_log 1; 仅对当前数据库生效如mysql重启则会失效 若要永久生效需要修改配置文件my.cnf其他系统变量也是如此复制下面内容进去 slow_query_log1 slow_query_log_file/var/lib/mysql/host_name-slow.log#地址设置输出格式file/TABLEset global log_outputTABLE 可以使用select * from mysql.slow_log;查看慢日志情况 set global log_outputfile 可以在slow_query_log_file中查看慢查询语句设置阙值时间SHOW VARIABLES LIKE %long_query_time%; set global long_query_time3; #表示sql大于3s的都会记录在slow_query_log_file中查看慢查询的条数show global status like %Slow_queries%然后进入设置的慢查询日志目录slow_query_log_file查看文件内容里面会有对应慢查询sql,执行时间时间戳2.explain慢sql分析1.通过慢查询日志获取到日志中的慢sql语句使用关键字explain查看sql的执行过程的属性。2.在linux终端执行mysqldumpslow -help可以利用mysqldumpslow查询对应sql的执行情况3.mysql profiles1.查看状态show VARIABLES LIKE %profiling%2.开启set profilingon3.查看profile记录的执行sql情况show profiles; 根据上面命令查询的sql执行所消耗的时间包含query_id针对某一条查询对应内部过程 show profile cpu,block io for QUERY $query_id; 如show profile cpu,block io for QUERY 102; 你将会看到一条sql的完整的生命周期及每一步花费的时间出现如下4条表示性能堪忧converting HEAP to MyISAM 查询结果太大内存都不够用了往磁盘上搬了。creating tmp table 创建临时表copying to tmp table on disk 把内存中临时表复制到磁盘危险locked4.全日志不推荐尤其是线上环境万不能在生产环境启动 在mysql的my.cnf,设置 #开启 general_log1 general_log_file/path/logfile #输出格式 log_outputFILE 或者log_outputTABLE ************************************ 命令 set global general_log1 set global log_outputTABLE 此后sql运行记录就会记录到general_log表可通过sql查询select * from mysql.general_log;