2.Explain详解与索引优化

一、工具介绍

1.1 Explain介绍

    Explain工具是分析并查看SQL执行性能的模拟优化器。

    在select 语句执行之前加一个 explain关键字，mySql查询上会设置一个标记，执行查询会返回的计划信息，但不执行这条语句。

    如果执行查询子查询，仍然会执行子查询，将结果放入临时表中。

1.2 Explain两个变种

1.2.1 explain extend

    会在explain的基础上额外提供一些查询优化信息，紧随其后输入show warnings命令可以查看mysql建议优化的的查询语句（该语句有的可以直接执行，有的不可以，类似伪sql，可以用来借鉴优化自己的sql语句）

1.2.2 explain partitions

    相对于explain会多一个partitions列，如果查询时基于分区，则会返回分区结果。(分区查询较复杂，很少有企业使用)

1.3 Explain列介绍

1.3.1 id

    id列是select查询时执行的先后顺序。一个完整的sql查询可能会嵌套、连接多层查询，有多少个查询就会有多少行查询优化展示信息，id越大执行顺序越优先，若ID相同，则排在上面的优先执行

1.3.2 select_type

select_type表示对应的查询复杂度。

类型	描述
simple	简单查询。不包含子查询和union查询
primary	复杂查询中的外层查询
subquery	包含在select中的子查询，不包含在from中
derived	包含在form中的子查询。会放入临时表
union	在union中的第二个和随后的select中

1.3.3 table

    表示当前查询正在查询的表。

    如上图所示id=3查询行，代表from中的子查询正在访问film表；

    id=2查询行, 代表select中的子查询征在访问actor表；

    id=3查询行，因为是因为form后面的子查询形成的表，代表访问i=3的查询行衍生结果，也就是derived3

    当有uninon时，union后面查询的结果table列显示，表示union正在访问id=1和2的查询结果

1.3.4 partitions

使用explain partitions命令，如果是分区表查询的话，则会返回信息

1.3.4 type

sql的查询类型，表示sql查询的快慢等级。通常优化查看这里的等级类型。

从最优到最差等级分为：system>const>eq_ref>ref>range>index>all

通常要优化到range，最好是ref

类型	描述
system	特殊的const，表里只匹配到一条数据
const	mysql内部针对sql查询结果可以看成查找一个常量，使用 show warnings查看 用于primary key 或者 unique key 的所有列与常数比较时，最多一个匹配行，读取1次，效率很快
eq_ref	展示了一种连接查询类型；对于前表的每一行，从后表中读取一行，一种非常高效和理想的连接类型； 通常在 主键索引或者唯一索引中出现；当使用两个表进行连接时，其中一个表的连接列时主键或者唯一索引，而另外一个表使用该列进行等值连接。 例如： select * from table t1 left join table t2 on t1.id = t2.table1_id; table1中id主键或者唯一索引，table2中的table1_id与table1中的id进行连接
ref	和eq-ref相比，使用的是普通索引或者唯一索引的部分前缀，可能会找到多个符合条件的值
range	通常是范围查询，例如 in，between,>,=等操作
index	扫秒全索引获取到值，一般扫秒二级索引，这种扫秒不会从根节点获取值，而是扫秒全部叶子节点和值， 速度比较慢，通常使用覆盖索引优化
all	全表扫秒

1.3.5 possible_keys

mysql内部的建议优化，可能会用到的索引。

1.3.6 key

查询实际用到的索引

1.3.7 key_len

索引的长度，如何计算？

• 字符串char(n)和varchar(n),n代表字符数，不是字节数，如果是utf-8,一个数字或字母占1个字节，一个汉字占3个字节

        char(n) 如果是汉字就是3n

        varchar(n) 如果是汉字就是3n+2, 加2是用来存储字符串的长度，varchar是变长字符串

• 数值类型

        tinyint 1个字节

        smallint 2个字节

        int 4个字节

        bigint 8个字节

• 时间类型

        date 3个字节

        timestamp 4个字节

        datetime 8个字节

• null 1个字节

1.3.8 ref

     key列索引的类型，常量(const)，字段名(username)

1.3.9 rows

     mysql查询时估计要检索的行数，不是实际值行数

1.3.10 filtered

    该列是针对表中查询值的一个百分比的估算值，rows * filtered / 100

1.3.11 extra

额外信息，常用到值有

类型	描述
using index(覆盖索引)	select查询结果的字段可以从索引树中得到，这个索引通常是辅助索引，无需通过索引找到主键,再通过主键去获取具体的值，这种情况叫覆盖索引
using where	使用where语句处理结果，并且查询列未走索引
using index conditon	查询的列不完全被索引覆盖，where中的条件通常是前置部分索引 例如：select * from user where user_id > 1
using temporary	使用临时表处理查询结果，这种情况一般都要使用索引优化
using filesort	查询结果使用文件排序，不是使用索引排序，数据量较小在内存排序，较大在磁盘排序 select * from user order by username(非索引字段)，此时会查询整个表，并对数据进行排序，这种情况也需要使用索引排序，给username创建索引，
select tables optimized away	使用某些聚合函数，例如 min，max等来访问存在索引的字段

二、常用优化

 这里以表employess为例

CREATE TABLE 'employees'(

    'id' int(11) NOT NULL AUTO INCREMENT,

    'name' varchar(24) NOT NULL DEFAULT '' COMMENT'姓名',

    'age' int(11) NOT NULL DEFAULT 'O' COMMENT '年龄',

    'position' varchar(28) NOT NULL DEFAULT '' COMMENT'职位',

    'hire_time' timeStamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'时间',

    PRIMARY KEY('id'),

    KEY 'idx_name_age_position'('name','age','position')USING BTREE )ENGINE=InnODB_AUTO_INCREMENT=4 DEFAULT CHARSET=Utf8 COMMENT='员工记录表';

INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NoW());

INSERT INTO employees(name,age,position,hire_time) VALUES( 'HanMeimei', 23, 'dev' ,NOW());

INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23, 'dev',NOW());

• 1. 全值匹配

EXPLAIN SELECT * FROM employees WHERE name ='LiLei'; -- 一个字段简单全值查询，并且使用了索引，精确定位一条数据

• 2. 最左侧原则

-- 如果是联合索引，无论多少个字段，只要遵循查询的字段能够正确从左到右区匹配索引，并且不跳过索引中的列。 EXPLAIN SELECT * FROM employees WHERE name = 'Bill' and age = 31; -- 可以走索引 EXPLAIN SELECT * FROM employees WHERE age = 30 AND position = 'dev'; -- 不可以走索引 跳过了name列 EXPLAIN SELECT * FROM employees WHERE position = 'manager'; -- 不可以走索引

• 3. like 尽量不使用%开头，会导致索引失效进行全表扫秒。可以使用%结尾（当成常量），若使%开头，尽量使用覆盖索引

EXPLAIN SELECT * FROM employees WHERE name like '%Lei'

EXPLAIN SELECT * FROM employees WHERE name like 'Lei%'

• 4. 尽量少使用 or 和 in ，mysql内部会对sql进行一个优化，根据检索的比例、表大小、数据量大小选择是否使用索引

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' or name = 'HanMeimei'; -- 这里只查出3条数据，mysql优化器认为全盘扫描聚簇索引更快

• 5. 不在索引上做计算、函数、类型转换等操作，导致索引失效进行全表扫秒

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei'; 
EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei'; -- 导致索引失效

• 6. 条件字段尽量少使用索引中的范围查询，若数据量过大，可能会造成索引失效

-- 例如给 age建议索引 
ALTER TABLE employees ADD INDEX 'idx_age'('age') USING BTREE ; 
explain select * from employees where age >=1 and age <=2000;

-- 优化方式，可以将age查询范围缩小
explain select * from employees where age >=1 and age <=1000; 
explain select * from employees where age >=1001 and age <=2000

• 7. 尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句

EXPLAIN SELECT name,age FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager';

• 8. 尽量不适用 not in、!=、not exists、>=等这些查询条件,会导致索引失效全盘扫秒，mysql会根据检索比例，数据量大小等衡量是否使用索引

EXPLAIN SELECT * FROM employees WHERE name != 'LiLei';

• 9. is null 、is not null 一般情况下也无法使用索引

EXPLAIN SELECT * FROM employees WHERE name is null;

• 10. 字符串不加单引号索引会失效

EXPLAIN SELECT * FROM employees WHERE name = '1000'; 
EXPLAIN SELECT * FROM employees WHERE name = 1000 -- 字符串不加引导可能会进行隐式转换，导致索引失效

• 11. 存储引擎不能使用索引中范围条件右边的列

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manager'；
-- age 使用了范围查找，导致后面position索引失效