基础知识

Explain性能分析

通过explain我们可以获得以下信息：
表的读取顺序
数据读取操作的操作类型
哪些索引可以被使用
哪些索引真正被使用
表的直接引用
每张表的有多少行被优化器查询了
在这里插入图片描述

1）ID字段说明

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
id相同，执行顺序由上至下

id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

2）select_type和table字段说明

表示查询类型，主要用于区别普通查询，联合查询，子查询等的复杂查询

simple : 简单的select查询，查询中不包含子查询或者UNION
EXPLAIN SELECT * FROM L1 where id = 1;
在这里插入图片描述

primary : 查询中若包含任何复杂的子部分，最外层查询被标记
subquery : 在select或where列表中包含了子查询
EXPLAIN SELECT * FROM L2 WHERE id = (
SELECT id FROM L1 WHERE id = (SELECT L3.id FROM L3 WHERE L3.title =
‘ruyuan08’));
在这里插入图片描述
union : union连接的两个select查询，第一个查询是dervied派生表，除了第一个表外，第二个
以后的表select_type都是union
derived : 在from列表中包含的子查询被标记为derived（派生表），MySQL会递归执行这些子
查询，把结果放到临时表中
union result : UNION 的结果
EXPLAIN SELECT * FROM (select * from L3 union select * from L4)a;
在这里插入图片描述

3） type字段说明

type字段显示的是连接类型 ( join type表示的是用什么样的方式来获取数据)，它描述了找到所需数据所使用的扫描方式, 是较为重要的一个指标。
完整的连接类型比较多，简化之后,我们可以只关注一下几种，按照从最佳类型到最坏类型进行排序：
system > const > eq_ref > ref > range > index > ALL
一般来说,需要保证查询至少达到 range级别,最好能到ref,否则就要就行SQL的优化调整

下面介绍type字段不同值表示的含义:
system: 表中就仅有一行数据的时候. 这是const连接类型的一个特例,很少出现。

const: const表示命中主键索引(primary key) 或者唯一索引(unique),表示通过索引一次就找到数据记录.
因为只匹配一条记录,所以被连接的部分是一个常量. (如果将主键放在 where条件中, MySQL就能将该查询转换为一个常量) 这种类型非常快.
例如以下查询:
explain select * from L1 where id = 3;
– 为L1表的title字段添加唯一索引
alter table L1 add unique(title);
explain select * from L1 where title = ‘ruyuan001’;
在这里插入图片描述

eq_ref : 对于前一个表中的每个一行，后表只有一行被扫描。除了system和const类型之外，这是最好的连接类型。只有当联接使用索引的部分都是主键或惟一非空索引时，才会出现这种类型。
例如以下查询：
EXPLAIN SELECT L1.id,L1.title FROM L1 left join L2 on L1.id = L2.id;
在这里插入图片描述

ref : 非唯一性索引扫描(使用了普通索引), 对于前表的每一行(row)，后表可能有多于一行的数据被扫描,它返回所有匹配某个单独值的行.
例如以下查询:
– 为L1表的title字段添加普通索引
alter table L1 add index idx_title (title) ;
EXPLAIN SELECT * FROM L1 inner join L2 on L1.title = L2.title;
在这里插入图片描述

range : 索引上的范围查询,检索给定范围的行,between，in函数，> 都是典型的范围(range)查询,
例如以下查询:
EXPLAIN SELECT * FROM L1 WHERE L1.id between 1 and 10;
注: 当in函数中的数据很大时,可能会导致效率下降,最终不走索引
在这里插入图片描述

index : 出现index 是 SQL 使用了索引, 但是没有通过索引进行过滤,需要扫描索引上的全部数据 (查找所有索引树,比ALL快一些,因为索引文件要比数据文件小 ), 一般是使用了索引进行排序分组.
EXPLAIN SELECT * FROM L2 group by id order by id;
– 该count查询需要通过扫描索引上的全部数据来计数
EXPLAIN SELECT count(*) FROM L2;
在这里插入图片描述

ALL : 没有使用到任何索引, 连接查询时对于前表的每一行，后表都要被全表扫描。
EXPLAIN SELECT * FROM L3 inner join L4 on L3.title = L4.title ;
在这里插入图片描述

总结各类type类型的特点：
type类型 | 解释
system：不进行磁盘IO,查询系统表,仅仅返回一条数据
const：查找主键索引,最多返回1条或0条数据. 属于精确查找
eq_re：查找唯一性索引,返回数据最多一条, 属于精确查找
ref：查找非唯一性索引,返回匹配某一条件的多条数据,属于精确查找,数据返回可能是
多条.
range：查找某个索引的部分索引,只检索给定范围的行,属于范围查找. 比如: > 、 < 、in、between
index 查找所有索引树,比ALL快一些,因为索引文件要比数据文件小.
ALL 不使用任何索引,直接进行全表扫描

4）possible_keys 与 key说明

possible_keys
显示可能应用到这张表上的索引，一个或者多个. 查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用.
key
实际使用的索引，若为null，则没有使用到索引。（两种可能，1.没建立索引, 2.建立索引，但索引失效）。查询中若使用了覆盖索引，则该索引仅出现在key列表中。

5）key_len字段说明

表示索引中使用的字节数, 可以通过该列计算查询中使用索引的长度.
key_len 字段能够帮你检查是否充分利用了索引 ken_len 越长，说明索引使用的越充分

6）ref 字段说明

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
L1.id=‘1’; 1是常量 , ref = const
EXPLAIN SELECT * FROM L1 WHERE L1.id=1;
在这里插入图片描述

7）rows 字段说明

表示MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数
L3中的title没有添加索引, 所以L3中有3条记录,就需要读取3条记录进行查找.
EXPLAIN SELECT * FROM L3,L4 WHERE L3.id = L4.id AND L3.title LIKE ‘ruyuan007’;
在这里插入图片描述

8）filtered 字段说明

它指返回结果的行占需要读到的行(rows列的值)的百分比

9）extra 字段说明

Extra 是 EXPLAIN 输出中另外一个很重要的列，该列显示MySQL在查询过程中的一些详细信息.

Using filesort
执行结果Extra为 Using filesort ，这说明，得到所需结果集，需要对所有记录进行文件排序。
这类SQL语句性能极差，需要进行优化。
典型的，在一个没有建立索引的列上进行了order by，就会触发filesort，常见的优化方案是，在order by的列上添加索引，避免每次查询都全量排序。
EXPLAIN SELECT * FROM users ORDER BY age;
在这里插入图片描述

Using temporary
表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询
EXPLAIN SELECT COUNT(*),uname FROM users GROUP BY uname;
在这里插入图片描述

Using where
意味着全表扫描或者在查找使用索引的情况下,但是还有查询条件不在索引字段当中.
需要注意的是：

返回所有记录的SQL，不使用where条件过滤数据，大概率不符合预期，对于这类SQL往往需
要进行优化；
使用了where条件的SQL，并不代表不需要优化，往往需要配合explain结果中的type（连接
类型）来综合判断。例如本例查询的 age 未设置索引，所以返回的type为ALL，仍有优化空
间，可以建立索引优化查询。
EXPLAIN SELECT * FROM users WHERE age=10;

Using index
表示直接访问索引就能够获取到所需要的数据(覆盖索引) , 不需要通过索引回表.
– 为uname创建索引
alter table users add index idx_uname(uname);
EXPLAIN SELECT uid,uname FROM users WHERE uname=‘lisa’;
在这里插入图片描述

Using join buffer
使用了连接缓存, 会显示join连接查询时,MySQL选择的查询算法 .
EXPLAIN SELECT * FROM users u1 LEFT JOIN (SELECT * FROM users WHERE age = 1) u2 ON u1.age = u2.age;
在这里插入图片描述
Using join buffer (Block Nested Loop) 说明，需要进行嵌套循环计算, 这里每个表都有五
条记录，内外表查询的type都为ALL。
问题在于两个关联表的关联使用了字段 age，并且age字段未建立索引，就会出现这种情况。

Extra主要指标的含义(有时会同时出现)
using index ：使用覆盖索引的时候就会出现
using where ：在查找使用索引的情况下，需要回表去查询所需的数据
using index condition ：查找使用了索引，但是需要回表查询数据
using index & using where ：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

优化原则详解

1)最佳左前缀法则

最佳左前缀法则: 如果创建的是联合索引,就要遵循该法则. 使用索引时，where后面的条件需要从索引
的最左前列开始使用,并且不能跳过索引中的列使用。

最佳左前缀底层原理
MySQL创建联合索引的规则是: 首先会对联合索引最左边的字段进行排序, 在第一个字段的基础之上再对第二个字段进行排序.
所以: 最佳左前缀原则其实是和B+树的结构有关系, 最左字段肯定是有序的, 第二个字段则是无序的(联合索引的排序方式是: 先按照第一个字段进行排序,如果第一个字段相等再根据第二个字段排序).

2) 不要在索引列上做任何计算

不要在索引列上做任何操作，比如计算、使用函数、自动或手动进行类型转换,会导致索引失效，从而使查询转向全表扫描。

3) 范围之后全失效

在编写查询语句时, where条件中如果有范围条件, 并且范围条件之后还有其他过滤条件, 那么范围条件之后的列就都将会索引失效.

4) 避免使用 is null 、 is not null、!= 、or

使用 is null 会使索引失效

使用 is not null 会使索引失效

使用 != 和 or 会使索引失效

5) like以%开头会使索引失效

like查询为范围查询，%出现在左边，则索引失效。%出现在右边索引未失效.

解决%出现在左边索引失效的方法
使用覆盖索引
通过使用覆盖索引 type = index ,并且 extra = Using index ,从全表扫描
变成了全索引扫描.

like 失效的原理

%号在右: 由于B+树的索引顺序，是按照首字母的大小进行排序，%号在右的匹配又是匹配首
字母。所以可以在B+树上进行有序的查找，查找首字母符合要求的数据。所以有些时候可以
用到索引.
%号在左: 是匹配字符串尾部的数据，我们上面说了排序规则，尾部的字母是没有顺序的，
所以不能按照索引顺序查询，就用不到索引.
两个%%号: 这个是查询任意位置的字母满足条件即可，只有首字母是进行索引排序的，其他
位置的字母都是相对无序的，所以查找任意位置的字母是用不上索引的.

索引优化原则总结
最左前缀法则要遵守
索引列上不计算
范围之后全失效
覆盖索引记住用。
不等于、is null、is not null、or导致索引失效。
like百分号加右边，加左边导致索引失效，解决方法：使用覆盖索引。