利用explain排查分析慢sql的实战案例_Mysql_

一 概述

1.0 sql调优的过程

SQL调优过程：

1. 观察，至少跑1天，看看生产的慢SQL情况。
2. 开启慢查询日志，设置阙值，比如超过5秒钟的就是慢SQL，并将它抓取出来。
3. explain + 慢SQL分析。
4. show profile，查询SQL在Mysql服务器里面的执行细节和生命周期情况。
5. 运维经理 or DBA，进行SQL数据库服务器的参数调优。

1.1 优化索引口诀

优化的口诀如下：

全值匹配我最爱， 最佳左前缀法则 ；

带头大哥不能死， 中间兄弟不能断；

索引列上少计算， 范围之后全失效；

LIKE 百分写最右， 覆盖索引不写 *；

不等空值还有 OR， 索引影响要注意；

VAR 引号不可丢， SQL 优化有诀窍。

注意：以下操作都是在所建索引

CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);

下进行的操作，如下:

1.1.1 全值匹配我最爱

CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);

结论：全职匹配我最爱指的是，查询的字段按照顺序在索引中都可以匹配到！ ,SQL 中查询字段的顺序，跟使用索引中字段的顺序，没有关系。优化器会在不影响 SQL 执行结果的前提下，给你自动地优化。

1.1.2  最佳左前缀法则，带头大哥不能死， 中间兄弟不能断；

使用复合索引，需要遵循最佳左前缀法则，即如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列

 前面两个sql索引失效的原因：

即使跳过了中间的索引，但是其长度没变化，跟第一个sql只使用name的索引的长度一样，那就说明第二个sql值使用了部分索引，只使用了name的索引，后面的age，pos失效。不然的话长度肯定大于74。

结论： 过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无 法被使用。  

1.1.3  索引列上少计算

 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描。

 所以字符串类型的数据，该加单引号的一定要加！

1.1.4  范围之后全失效

复合索引：CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);

索引列上不能有范围查询，少用>，<，between…and等结构；范围查询的列忽略，索引失效，后面的索引列也跟着失效，不起作用。

 建议：将可能做范围查询的字段的索引顺序放在最后

1.1.5  覆盖索引不写 *

即查询列和索引列一致，不要写 select *!， 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致））区别在于extra，索引的不同，速度不一样

1.1.6  使用不等于(!= 或者<>)的时候

mysql 在使用不等于 (!= 或者 <>) 时，有时会无法使用索引会导致全表扫描。这个得知道这种情况，根据业务情况，得写这种范围查询，还是要写的。一切满足业务。

1.1.7 不用 is null 或者is not null 

is not null 用不到索引， 如果某列字段中包含null，is null是可以用到索引的 如果某列字段中不包含null，is null是不可以用到索引的

1.1.8 LIKE 百分写最右

1. 注意看模糊查询的细节，只有xx%前缀查询才不会失效

 2.如果要实现两边百分号，不能失效，%xx%，可以使用覆盖索引来解决

增加一个索引CREATE INDEX idx_user_nameAge ON tbl_user(NAME,age);

如果使用下面这些，都是使用的覆盖索引，结果都是一样的， 注意id之所以没加索引，但是可以加入使用不会失效，是因为他是主键

但是但是如果加入了没有主键又不是索引的东西，%xx%就会失效

1.1.9 字符串不加单引号索引失效

如varchar类型，自己写成int型，虽然类型不正确也可以查询，但是底层会帮你转换类型，索引直接失效，变成了全表查询。字符串不加单引号索引失效。

1.1.10  少用or，用它来连接时会索引失效。

少用or，用它来连接时会索引失效。

用使用 union all 或者 union 来替代：

 1.2  案例分析 

1.3 建索引总结

1.对于单键索引，尽量选择针对当前query过滤性更好的索引。

2.在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。

3.在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引。

4.尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。

二 案例分析

2.1 单表分析

2.2.1 分析过程

1.sql语句

explain SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

通过执行查看发现：此sql进行了全表查询，而且在extra还出现了Using filesort等问题 。

2解决办法：建立其复合索引

create index idx_article_ccv on article(category_id,comments,views);

提示： 本文由神整理自网络，如有侵权请联系本站删除！
本站声明：
1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持；
2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！