Hexo

点滴积累 豁达处之

0%

索引及Explain介绍

Posted on Edited on In
Mysql

一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境中，我们遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，因此对查询语句的优化显然是重中之重。说起加速查询，就不得不提到索引了。

1 索引介绍

索引的目的在于提高查询效率， **本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。**

2 索引分类

1
2
3
4
5
6
7
8
9
10
1.普通索引index :加速查找
2.唯一索引
    主键索引：primary key ：加速查找+约束（不为空且唯一）
    唯一索引：unique：加速查找+约束 （唯一）
3.联合索引
    -primary key(id,name):联合主键索引
    -unique(id,name):联合唯一索引
    -index(id,name):联合普通索引
4.全文索引fulltext :用于搜索很长一篇文章的时候，效果最好。
5.空间索引spatial :了解就好，几乎不用

2.1 创建/删除索引的语法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
#方法一：创建表时
      　　CREATE TABLE 表名 (
                  字段名1  数据类型 [完整性约束条件…],
                  字段名2  数据类型 [完整性约束条件…],
                  [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
                  [索引名]  (字段名[(长度)]  [ASC |DESC]) 
                  );
  
  
 #方法二：CREATE在已存在的表上创建索引
         CREATE  [UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名 
                      ON 表名 (字段名[(长度)]  [ASC |DESC]) ;
 
 
 #方法三：ALTER TABLE在已存在的表上创建索引
         ALTER TABLE 表名 ADD  [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                              索引名 (字段名[(长度)]  [ASC |DESC]) ;
                              
 #删除索引：DROP INDEX 索引名 ON 表名字;

示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
1.创建索引
    -在创建表时就创建（需要注意的几点）
    create table s1(
    id int ,#可以在这加primary key
    #id int index #不可以这样加索引，因为index只是索引，没有约束一说，
    #不能像主键，还有唯一约束一样，在定义字段的时候加索引
    name char(20),
    age int,
    email varchar(30)
    #primary key(id) #也可以在这加
    index(id) #可以这样加
    );
    -在创建表后在创建
    create index name on s1(name); #添加普通索引
    create unique age on s1(age);添加唯一索引
    alter table s1 add primary key(id); #添加住建索引，也就是给id字段增加一个主键约束
    create index name on s1(id,name); #添加普通联合索引
2.删除索引
    drop index id on s1;
    drop index name on s1; #删除普通索引
    drop index age on s1; #删除唯一索引，就和普通索引一样，不用在index前加unique来删，直接就可以删了
    alter table s1 drop primary key; #删除主键(因为它添加的时候是按照alter来增加的，那么我们也用alter来删)

2.2 最左匹配原则

MySQL中的索引可以以一定顺序引用多个列，这种索引叫做联合索引，一般的，一个联合索引是一个有序元组<a1, a2, …, an>，其中各个元素均为数据表的一列，实际上要严格定义索引需要用到关系代数，但是这里我不想讨论太多关系代数的话题，因为那样会显得很枯燥，所以这里就不再做严格定义。另外，单列索引可以看成联合索引元素数为1的特例。

测试：准备数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
#1. 准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);

#2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<3000000)do
        insert into s1 values(i,concat('egon',i),'male',concat('egon',i,'@oldboy'));
        set i=i+1;
    end while;
END$$ #$$结束
delimiter ; #重新声明分号为结束符号

#3. 查看存储过程
show create procedure auto_insert1\G 

#4. 调用存储过程
call auto_insert1();

创建联合索引 ：create index index_i_n_e on s1 (id, name, email)

显示索引： SHOW INDEX FROM s1;

Mysql_showindex

2.1 全列匹配

EXPLAIN SELECT * FROM s1 WHERE id=1 AND name=’egon1’ AND email=’egon1@oldboy’;

Mysql_index1

很明显，当按照索引中所有列进行精确匹配（这里精确匹配指“=”或“IN”匹配）时，索引可以被用到。这里有一点需要注意，理论上索引对顺序是敏感的，但是由于MySQL的查询优化器会自动调整where子句的条件顺序以使用适合的索引，例如我们将where中的条件顺序颠倒：

EXPLAIN SELECT * FROM s1 WHERE email=’egon1@oldboy’ and id=1 AND name=’egon1’ ;

Mysql_index1

效果一样

2.2 最左前缀匹配

EXPLAIN SELECT * FROM s1 WHERE id=1

Mysql_index2

当查询条件精确匹配索引的左边连续一个或几个列时，如<id>或<id, name>，所以可以被用到，但是只能用到一部分，即条件所组成的最左前缀。上面的查询从分析结果看用到了PRIMARY索引，但是key_len为5，说明只用到了索引的第一列前缀。

2.3 中间条件未提供

EXPLAIN SELECT * FROM s1 WHERE id=1 AND email=’egon1@oldboy’;

Mysql_index3

此时索引使用情况和情况二相同，因为name未提供，所以查询只用到了索引的第一列，而后面的email虽然也在索引中，但是由于name不存在而无法和左前缀连接，因此需要对结果进行扫描过滤email（这里由于id唯一，所以不存在扫描）。如果想让email也使用索引而不是where过滤，可以增加一个辅助索引<id, email>，此时上面的查询会使用这个索引。除此之外，还可以使用一种称之为“隔离列”的优化方法，将id与email之间的“坑”填上。

首先我们看下name一共有几种不同的值：

SELECT DISTINCT(name) FROM s1;

Mysql_index4

只有3种。在这种成为“坑”的列值比较少的情况下，可以考虑用“IN”来填补这个“坑”从而形成最左前缀：

EXPLAIN SELECT * FROM s1 WHERE email=’egon1@oldboy’ and id=1 AND name in (‘egon1’ , ‘egon2’, ‘egon3’)

Mysql_index5

这次key_len为291，说明索引被用全了，但是从type和rows看出IN实际上执行了一个range查询，这里检查了3个key。

2.4 查询条件没有索引第一列

EXPLAIN SELECT * FROM s1 WHERE email=’egon1@oldboy’;

Mysql_index6

由于不是最左前缀，索引这样的查询显然用不到索引。

2.5 匹配某列的前缀字符串

EXPLAIN SELECT * FROM s1 WHERE id= 01 and name like ‘egon1%’;

Mysql_index7

此时可以用到索引，如果通配符%不出现在开头，则可以用到索引

问题:解决like’%字符串%’索引不被使用的方法?

使用覆盖索引，查询字段必须是建立覆盖索引字段

explain select name from test where name like ‘%33%’;

Mysql_index7

2.6 范围查询

EXPLAIN SELECT * FROM s1 WHERE id < 10 and name = ‘egon1’;

Mysql_index8

范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引。但是，用了“between”并不意味着就是范围查询，例如下面的查询：

EXPLAIN SELECT * FROM s1 WHERE id between 1 and 10 and name = ‘egon1’ and email=’egon1@oldboy’;

Mysql_index9

看起来是用了范围查询，但作用于id上的“BETWEEN”实际上相当于“IN”，也就是说id实际是多值精确匹配。可以看到这个查询用到了索引全部三个列。因此在MySQL中要谨慎地区分多值匹配和范围匹配，否则会对MySQL的行为产生困惑。

3 explain

属性 说明
id SELECT识别符。这是SELECT的查询序列号
select_type SELECT类型,可以为以下任何一种:
SIMPLE:简单SELECT(不使用UNION或子查询)
PRIMARY: 一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为primary。且只有一个
UNION: union连接的两个select查询，第一个查询是dervied派生表，除了第一个表外，第二个以后的表select_type都是union
DEPENDENT UNION: 与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响
UNION RESULT: 包含union的结果集，在union和union all语句中,因为它不需要参与查询，所以id字段为null
SUBQUERY: 除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery
DEPENDENT SUBQUERY: 与dependent union类似，表示这个subquery的查询要受到外部表查询的影响
DERIVED: from字句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套select
table 输出的行所引用的表
type 联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序:
system: 表中只有一行数据或者是空表 。 且只能用于myisam和memory表。如果是Innodb引擎表，type列在这个情况通常都是all或者index
const: 使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描
eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。
ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。
ref_or_null:该联接类型如同ref,但是添加了MySQL专门搜索包含NULL值的行。
index_merge:该联接类型表示使用了索引合并优化方法。
unique_subquery:该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。
index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)
range:只检索给定范围的行,使用一个索引来选择行。
index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
ALL:对于每个来自于先前的表的行组合,进行完整的表扫描
possible_keys 指出MySQL能使用哪个索引在该表中找到行
key 显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。
key_len 显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。
ref 显示使用哪个列或常数与key一起从表中选择行。
rows 显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。
filtered 显示了通过条件过滤出的行数的百分比估计值。
Extra 该列包含MySQL解决查询的详细信息
Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。
Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。
Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。
Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。
Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。
Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。
Using sort_union(…), Using union(…), Using intersect(…):这些函数说明如何为index_merge联接类型合并索引扫描。
Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。

3.1 select_type的说明

1.1 PRIMARY 和 UNION

当通过union来连接多个查询结果时，第二个之后的select其select_type为UNION。

explain select * from s1 where id = 100 union select * from s1 where id = 200;

Mysql_explain1

1.2 DEPENDENT UNION和DEPENDENT SUBQUERY

当union作为子查询时，其中第二个union的select_type就是DEPENDENT UNION。 第一个子查询的select_type则是DEPENDENT SUBQUERY。

explain select * from s1 where id in (select id from s1 where id=100 union select id from s1 where id=200);

Mysql_explain2

1.3 SUBQUERY

子查询中的第一个select其select_type为SUBQUERY。

explain select * from s1 where id =(select id from s1 where id = 100);

Mysql_explain3

1.4 DERIVED

当子查询是from子句时，其select_type为DERIVED。

explain select * from ( select id from s1 where id = 100 ) a ;

Mysql_explain4

3.2 type 的说明

这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。
依次从最优到最差分别为:system > const > eq_ref > ref > range > index > ALL一般来说，得保证查询达到range级别，最好达到ref
NULL:mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如:在
索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表

2.1 system，const

mysql能对查询的某部分进行优化并将其转化成一个常量(可以看show warnings 的结果)。用于 primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。system是const的特例，表里只有一条元组匹配时为system

explain select * from test2_myisam where id = 1；（myisam引擎）

Mysql_explain_type

explain select * from test where id = 1

Mysql_explain_type2

explain select * from s1 where id = 1

Mysql_explain_type3

2.2 eq_ref

primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种type。

在s1表中的id是主键，test表中的id也是主键，该表可以认为是订单表的补充信息表，他们的关系是1对1，在下面的例子中可以看到b表的连接类型是eq_ref，这是极快的联接类型。

explain select * from s1 a,test b where a.id=b.id;

Mysql_eq_ref

2.3 ref

相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。

  1. 简单 select 查询，name是普通索引(非唯一索引)

explain select * from test b where b.name = ‘33’;

Mysql_type_ref

2.关联表查询，idx_film_actor_id是film_id和actor_id的联合索引，这里使用到了film_actor
的左边前缀film_id部分。

explain select film_id from film left join film_actor on film.id =film_actor.film_id;

Mysql_type_ref

2.4 range

范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。

 explain select * from s1 where id in (100,200,300);

Mysql_type_range

2.5 index

扫描全表索引，这通常比ALL快一些。

explain select count(*) from s1;

Mysql_type_index

2.6 ALL

即全表扫描，意味着mysql需要从头到尾去查找所需要的行。通常情况下这需要增加索
引来进行优化了

explain select * from s1;

Mysql_type_all

possible_keys

这一列显示查询可能使用哪些索引来查找。explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中
数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。

key

这一列显示mysql实际采用哪个索引来优化对该表的访问。如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。

key_len

这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。
key_len计算规则如下:

  • 字符串

    • char(n):n字节长度
    • varchar(n):2字节存储字符串长度，如果是utf-8，则长度 3n+2

  • 数值类型

    • tinyint:1字节
    • smallint:2字节
    • int:4字节
    • bigint:8字节

  • 时间类型

    • date:3字节
    • timestamp:4字节
    • datetime:8字节

  • 如果字段允许为 NULL，需要1字节记录是否为 NULL 索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半 部分的字符提取出来做索引。

ref

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有:const(常量)，字段名(例:film.id)

rows

这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。

3.3 extra

这一列展示的是额外信息。常见的重要值如下:

Using index

这是性能很高的一种情况。当查询所需的数据可以直接从索引树中检索到时，就会出现

explain select id from test where id = 2; (id为索引列，查询的列为id)

Mysql_exists_usingFilesort

Using where

使用 where 语句来处理结果，查询的列未被索引覆盖

explain select * from test where age = 2; (age不为索引列)

Mysql_exists_usingFilesort

Using index condition

查询的列不完全被索引覆盖，where条件中是一个前导列的范围;

3.4 Using filesort

在有排序子句的情况下很常见的一种情况。此时mysql会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行。

explain select * from s1 order by email desc limit 10; （email列没有索引）

Mysql_exists_usingFilesort

3.6 Using temporary

发生这种情况一般都是需要进行优化的。mysql需要创建一张临时表用来处理此类查询。

explain select distinct age from test; (age列没有索引)

Mysql_exists_usingTemporary

3.8 Using index for group-by

表明可以在索引中找到分组所需的所有数据，不需要查询实际的表。

参考链接

参考链接