MySQL 性能大动脉-索引命中分析(上篇)

本篇文章丢丢哥主要介绍下explain的基本使用以及作用和关于命中主键索引、唯一索引以及普通索引的命中规律以及实例，让大家对于数据库设计以及索引设计有更高层次的认识，下篇博客将主要介绍复合索引命中率规律及设计原则。

首先我们先要普及一下，索引加了有好处，也有弊处；索引可以加，但是不能加太多，不然在增删改时性能就太弱了；我们首先不讨论这个，只说在有必要加部分索引的情况下怎么让他们效率更高，加了就能提升性能，而不是盲目的加，或者加了因为复杂语句的缘故也还是扫表或者笛卡尔积了。

在实际产品开发中，索引原则上是在产品相对稳定，项目上线前给一波索引最好了，然后根据后端语言查询业务的语句进行一条条检测分析，如果基本都命中了（特别是大表）一定要检查细心。

explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。

一、准备工作

首先我们准备一个简单的表测试，用最简单的例子让大家入门！

注意这里我们只给了 id为主键索引，uid和username为唯一索引,其他的暂时不设置

添加几条简单的数据

使用方法，在select语句前加上explain就可以了： explain select username,password,addtime from data_admin_user where id=2;

在Navicat 可视化工具中能看到Result1里面给我们返回了一个列表

基本理论的了解

Explain通俗的解释：

字段解释	描述
id	SELECT识别符。这是SELECT的查询序列号
select_type	SELECT类型,可以为以下任何一种: - SIMPLE:简单SELECT(不使用UNION或子查询) - PRIMARY:最外面的SELECT - UNION:UNION中的第二个或后面的SELECT语句 - DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询 - UNION RESULT:UNION 的结果 - SUBQUERY:子查询中的第一个SELECT - DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询 - DERIVED:导出表的SELECT(FROM子句的子查询)
table	输出的行所引用的表
type	联接类型:下面给出各种联接类型,按照从最佳类型到最坏类型进行排序: - system:表仅有一行(=系统表)。这是const联接类型的一个特例。 - const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。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查询的所有列,而不要额外搜索硬盘访问实际的表。

比较概括的理论我们看完了，下面开始正菜！

三、索引命中实例

1.主键索引搜索(id)

explain select* from data_admin_user where id=2;

结果命中

type联接的优化程度已经很高了
命中的key也主键索引，长度4，是因为我起的字段类型为int占4个二进制位
查一行便能查到数据

2.普通索引搜索(uid)

explain select* from data_admin_user where uid='369d86744b96ee7170762721424415e7';

结果命中

type联接的优化相比较主键降了两级但是优化程度也很高
命中的key也普通索引，长度96，是因为我起的字段类型为char 32
查一行便能查到数据

3.唯一索引搜索(username)

explain select* from data_admin_user where username='admin';

结果命中

type联接的优化程度已经很高了(与主键一致)
命中的key也主键索引，长度50，是因为我起的字段类型为varchar 16
查一行便能查到数据

4.无索引搜索(addtime)

explain select* from data_admin_user where addtime="12312312353";

结果未命中(扫表)

type联接的ALL,最低下的效率
key未命中全部为空
查询到所有数据才能得到答案(测试库中共4条数据)

5.主键索引 + 无索引搜索(id + addtime)

explain select* from data_admin_user where id=4 and addtime="12312312353";

结果命中(情况与单主键索引相同)

type联接的已经最高
命中的key也主键索引，长度4，是因为我起的字段类型为int占4个二进制位
查一行便能查到数据

6.普通索引模糊搜索(uid like)

explain select* from data_admin_user where uid like "%123%";

结果未命中(扫表)

type联接ALL(扫整表)
命中的key空，未命中
查询了所有数据

7.普通索引 + 无索引搜索(uid like)

explain select* from data_admin_user where uid="12332112221" and password="671c9a9b0a74cd26cdcda3ac0bfa5972";

结果命中(与单普通索引一致)

type联接的优化相比较主键降了两级但是优化程度也很高
命中的key也普通索引，长度96，是因为我起的字段类型为char 32
查一行便能查到数据

h好了，简单的例子就做到这里，没试明白的可以继续组合试试，总而言之言而总之，只要有索引条件的语句，基本上都能命中，但是带索引条件的大于小于，或者区间、模糊的条件就算是主键或唯一索引的搜索都不一定100%能命中了。

1.留给大家一个思考的问题，如果是复合索引(多个字段在一个普通索引里)命中规律又是怎么样的呢？

2.主键索引，唯一索引，普通索引，复合索引再混合上多表的复杂查询下，索引的命中率又会怎么样呢？

后面的博客，丢丢哥将会为大家一一分析。

希望我的博客对你们有所帮助，你的认可就是我最大的动力。。

本文为作者原创，允许转载，转载后请以链接形式说明文章出处.
如转载但不标明来源，后果自负。