MySQL语句定位性能问题-explain

1.执行计划

语法：explain select....

使用extended可以在原来查询计划上提供一些优化的信息
语法:explain extended select ....,然后通过show warnings来查看相应的优化信息

，测试时发现一个警告，含义EXTENDED将被移除[本地环境mysql5.7]
尝试不使用EXTENDED参数，执行show warning明显，发现确实是可以使用的并消除了这个警告

2. 执行计划包含的信息

虽然不同版本的存储引擎执行计划不完全相同，但基本信息一致

          id: 1
 select_type: SIMPLE
       table: student
  partitions: NULL
        type: ALL
ossible_keys: NULL
         key: NULL
     key_len: NULL
         ref: NULL
        rows: 2
    filtered: 100.00
       Extra: NULL

下面进行解释：
id:由一组数字组成，表示子查询执行的顺序。id相同执行顺序自上而下

 explain SELECT
	i.cust_id AS '客户ID',
	i.cust_name AS '客户姓名',
	IF (l.cust_lmt < '1000',1000,  l.cust_lmt) AS '额度'
FROM
	cust_inf i
	JOIN cust_lmt l ON i.cust_id = l.cust_id ;

id不同，id越大优先级越高，越先被执行

explain	SELECT * FROM student union SELECT * FROM student ;

id为null时表示是一个结果集，不需要使用他查询，常出现包含union等查询语句中
select_type
每个子查询的查询类型，常见如下表

select_type	description
SIMPLE	不包含任何子查询或union等查询
PRIMARY	包含子查询，最外层查询显示为PRIMARY
SUBQUERY	在select或where子句中
DERIVED	from字句中包含的查询
UNION	出现在union后的查询语句中
UNION RESULT	从UNION中获取结果集

table
查询涉及到的数据表
如果查询使用别名，就显示别名，如果不涉及对数据表的操作，显示为null，如果是尖括号括起来的就标识这个是临时表，后边的N就是执行计划中的id,表示结果
来自于这个查询产生。如果是尖括号括来<union M,N>，与类似，也是一个临时表，标识这个结果来自于union查询的id为M,N的结果集。
type
访问类型：

• ALL 扫描全表数据
• index 遍历索引
• range 索引范围内查找
• index_subquery在子查询中使用ref
• unique_subquery在子查询中使用eq_ref
• ref_or_null 对Null进行优化的ref
• fulltext 使用全文索引
• ref 使用非唯一索引查找数据
• eq_ref 在join查询中使用PRIMARY KEY or UNIQUE NOT NULL索引关联
• const使用主键或者唯一索引，且匹配结果只有一条记录
• system const链接类型的特例，查询的表为系统表

性能从好到差依次为：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
提示：查询中若使用了覆盖索引（覆盖索引：索引的数据覆盖了需要查询的所有数据），则该列仅出现在key列表中

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

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

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

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

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;

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

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

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

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

extra
extra的信息非常丰富，常见的有：
Using index使用覆盖索引
Using where使用了where子句过滤结果集
Using filesort使用文件排序，使用非索引列进行排序时出现，非常消耗性能，尽量优化。
Using temporary使用了临时表

3.一些优化建议

1.SQL语句不要写的太复杂，尽量简单，不要嵌套太多层
2.使用“临时表”缓存查询结果
3.使用like要注意是否会导致全表扫描
例如：SELECT * FROM table where username like ‘%peiqi%’；
关键词前面有%会导致全表扫描
4.尽量避免使用!=或<>操作符
where语句中使用!=或者<>,引擎将放弃使用索引进而全表扫描
5.尽量避免使用or来连接查询条件
在where子句中使用or来连接查询条件，引擎将放弃索引进而全表扫描
可以使用
select id from t where num=‘10’
union all
select 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 where num in (10,20);
7. 可以考虑强制查询使用索引
select * from table force index(PRI) limit 2;(强制使用主键)
select* from table force index(hollis_index) limit 2;(强制使用索引hollis_index)
select * from table force index(PRI,hollis_index) limit 2;(强制使用索引PRI和hollis_index)
8.尽量避免使用表达式、函数等操作作为查询条件
9.尽量避免大事务操作，提高系统并发能力
10.尽量避免使用游标
11.任何地方都不要使用select* from t，使用具体字段替代*，不返回任何用不到的字段
12.，尽可能使用varchar和nvarchar替代char和nchar
13.尽量使用数字型字段，如果只含数字信息的字段被设计成字符型，郑辉降低查询和连接的性能，并且会增加存储开销。
14.索引并不是越多越好，索引可以提高select的查询效率，但是同时也会降低insert和select的效率
15.并不是所有的索引都有效，SQL是根据表中的数据来进行查询优化的，当索引列有大量的重复时，SQl查询可能不会使用索引.

参考

文章内容来源