教你如何定位及优化SQL语句的性能问题

性能从好到差依次为：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL，除了ALL之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引。

所以，如果通过执行计划发现某张表的查询语句的type显示为ALL，那就要考虑添加索引，或者更换查询方式，使用索引进行查询。

possible_keys

可能使用的索引，注意不一定会使用。查询涉及到的字段上若存在索引，则该索引将被列出来。当该列为 NULL时就要考虑当前的SQL是否需要优化了。

key

显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。

TIPS:查询中若使用了覆盖索引(覆盖索引：索引的数据覆盖了需要查询的所有数据)，则该索引仅出现在key列表中。

select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。

key_length

索引长度char()、varchar()索引长度的计算公式：

(Character Set：utf8mb4=4,utf8=3,gbk=2,latin1=1) * 列长度 + 1(允许null) + 2(变长列) 

其他类型索引长度的计算公式:ex:

CREATE TABLE `student` ( 
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT, 
 `name` varchar(128) NOT NULL DEFAULT '', 
 `age` int(11), 
 PRIMARY KEY (`id`), 
 UNIQUE KEY `idx` (`name`), 
 KEY `idx_age` (`age`) 
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4; 

name 索引长度为： 编码为utf8mb4,列长为128,不允许为NULL,字段类型为varchar(128)。key_length = 128 * 4 + 0 + 2 = 514;

(https://juejin.im/post/5a52386d51882573443c852a)

age 索引长度：int类型占4位，允许null,索引长度为5。

(https://juejin.im/post/5a52386d51882573443c852a)

ref

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

rows

返回估算的结果集数目，注意这并不是一个准确值。

extra

extra的信息非常丰富，常见的有：

• Using index 使用覆盖索引
• Using where 使用了用where子句来过滤结果集
• Using filesort 使用文件排序，使用非索引列进行排序时出现，非常消耗性能，尽量优化。
• Using temporary 使用了临时表。

一些SQL优化建议 

1、SQL语句不要写的太复杂。

一个SQL语句要尽量简单，不要嵌套太多层。

2、使用『临时表』缓存中间结果。

简化SQL语句的重要方法就是采用临时表暂存中间结果，这样可以避免程序中多次扫描主表，也大大减少了阻塞，提高了并发性能。

3、使用like的时候要注意是否会导致全表扫

有的时候会需要进行一些模糊查询比如

select id from table where username like ‘%hollis%’ 

关键词%hollis%，由于hollis前面用到了“%”，因此该查询会使用全表扫描，除非必要，否则不要在关键词前加%，

4、尽量避免使用!=或<>操作符

在where语句中使用!=或<>，引擎将放弃使用索引而进行全表扫描。

5、尽量避免使用 or 来连接条件

在 where 子句中使用 or 来连接条件，引擎将放弃使用索引而进行全表扫描。

可以使用

select id from t where num=10union allselect id from t where num=20 

替代

select id from t where num=10 or num=20 

6、尽量避免使用in和not in

在 where 子句中使用 in和not in，引擎将放弃使用索引而进行全表扫描。

可以使用

select id from t where num between 10 and 20 

替代

select id from t where num in (10,20) 

（编辑：PHP编程网 - 黄冈站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!