mysql索引怎么建？

发表于 更新于 分类于 阅读次数：

谈谈mysql索引的使用知识，说说我的工作中遇到的问题

谈到mysql索引优化大家首先能想到的是explan分析一下sql，这是基本操作！

explan 解释出来的字段有什么意义？

  • select_type:表示SELECT的类型，常见的取值说明：
类型 说明
SIMPLE 简单表，不使用表连接或子查询
PRIMARY 主查询，即外层的查询
UNION UNION中的第二个或者后面的查询语句
SUBQUERY 子查询中的第一个

  • table:输出结果集的表（表别名）

  • type:表示MySQL在表中找到所需行的方式，或者叫访问类型。常见访问类型如下，从上到下，性能由差到最好：

类型 说明
ALL 全表扫描，MySQL遍历全表来找到匹配行
index 索引全扫描，MySQL遍历整个索引来查询匹配行，并不会扫描表
range 索引范围扫描,索引范围扫描，常用于<、<=、>、>=、between等操作
ref 非唯一索引扫描，使用非唯一索引或唯一索引的前缀扫描，返回匹配某个单独值的记录行
eq_ref 唯一索引扫描，类似ref，区别在于使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配
const,system 单表最多有一个匹配行，单表中最多有一条匹配行，查询起来非常迅速，所以这个匹配行的其他列的值可以被优化器在当前查询中当作常量来处理
NULL 不用扫描表或索引，MySQL不用访问表或者索引，直接就能够得到结果

  • possible_keys: 表示查询可能使用的索引

  • key: 实际使用的索引

  • key_len: 使用索引字段的长度

  • ref: 使用哪个列或常数与key一起从表中选择行。

  • rows: 扫描行的数量

  • filtered: 存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例(百分比)

  • Extra: 执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息
    最主要的有一下三种：

类型 说明
Using Index 表示索引覆盖，不会回表查询
Using Where 表示进行了回表查询
Using Index Condition 表示进行了ICP优化
Using Flesort 表示MySQL需额外排序操作, 不能通过索引顺序达到排序效果

什么是ICP？
MySQL5.6引入了Index Condition Pushdown（ICP）的特性，进一步优化了查询。Pushdown表示操作下放，某些情况下的条件过滤操作下放到存储引擎。

EXPLAIN SELECT * FROM rental WHERE rental_date=’2005-05-25’ AND customer_id>=300 AND customer_id<=400;

在5.6版本之前：

优化器首先使用复合索引idx_rental_date过滤出符合条件rental_date=’2005-05-25’的记录，然后根据复合索引idx_rental_date回表获取记录，最终根据条件customer_id>=300 AND customer_id<=400过滤出最后的查询结果（在服务层完成）。

在5.6版本之后：

MySQL使用了ICP来进一步优化查询，在检索的时候，把条件customer_id>=300 AND customer_id<=400也推到存储引擎层完成过滤，这样能够降低不必要的IO访问。Extra为Using index condition就表示使用了ICP优化。

关于mysql索引在实际工作中的学习

前两天公司有个需求上线领导让给某个新表建一个索引，根据以往的经验我考虑到最左匹配原则，between字段往后放的云云~~ 。

于是，根据查询sql直接将按条件顺序建立一个多列索引，通过explan在测试环境分析了一下，发现索引使用正常，由于sql中有使用到between，所以type为range级别。

我建的索引是这样的:
ADD INDEX idx_xxxid_xxxdate_status(xxxid, xxxdate, STATUS) USING BTREE;

等到sql提交给领导准备上线时，领导询问了我一下索引为何这么建立？

好吧，我有点懵逼，但是领导尽然诚心诚意的问了，我还是要如实招来的，一番解释后，领导说索引得改改。

修改后索引变成这样了：
KEY idx_xxxid (xxxid) USING BTREE ,
KEY idx_xxxid_xxxdate (xxxid,xxxdate) USING BTREE

主要修改是把值比较固定的status（0或1）从索引中踢掉了，并且增加了一个 xxxid 的单独索引。

不是很理解这么做的原因，于是自己搞了几百万条测试数据来比比到底索引怎么样！下面来个使用不同索引分析的对比图
多列索引分析

修改后的索引分析

两个索引的区别似乎很容易看出来，修改后索引使用到的idx_xxxid_xxxdate索引，原来的sql是range级别，修改后的是ref级别，这么一看修改后的sql看起来确实是比较好。往后继续看Extra，这个依然不能忽视，原索引是使用了ICP优化（上面有讲到）进行纯索引查询，新的索引虽然是ref级别但是有进行回表。

经过多次sql执行时间比对，虽然索引不同但是查询时间基本一致，我的渣渣服务器一二百万数据，使用索引稳定在180ms左右。可是为什么少了一个索引但是查询时间却没有差距呢？依然很疑惑，经过一番资料查询加之深思熟虑【手动狗头】，原因大概是这个样子的：

  • 虽然少一列的索引，但是由于现有的两列索引已经可以过滤掉绝大部分数据了，尽管进行了回表但是寥寥几条结果数据筛选对于mysql来说是没有多少压力的。

以上就是关于这次工作中有关索引建立的一点学习与思考，尽管领导还多建立的一个单独的索引，暂且没有用到，但是在测试数据插入时我有注意到多了一个索引但是插入速度要比原来的多耗时近1/3。可能是领导为以后的业务考虑，对，一定是这个样子的。

思考总结

仔细想想，索引的建立其实是和业务息息相关的，表中存储的业务数据怎么建立索引能使数据筛选更加快捷，索引的作用并不一定是要完全每个字段都要用到，能最大限度的筛选出符合条件的数据，减少mysql服务层的筛选这个其实也是重点所在，搜索学习过程中也找到了一个点：必须在唯一性较高的字段上建立索引,思路基本一致。

还是要继续学习，还想了解更多就得啃一啃mysql相关的书籍了，溜了溜了。