高效利用mysql索引指南

前言

mysql 相信大部分人都用过，索引肯定也是用过的，但是你知道如何创建恰当的索引吗？在数据量小的时候，不合适的索引对性能并不会有太大的影响，但是当数据逐渐增大时，性能便会急剧的下降。

本篇是对 mysql 索引的一个归纳总结，如果有错误的地方，记得评论指出哦。

索引基础

我们都有都知道查字典的步骤，是先在索引页中找到这个字的页码，然后再到对应的页码中查看这个字的信息。mysql 的索引方法也是和这个类似的，先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。假如有下面的 sql 语句：

select * from student where code='2333'

加入 code 列上建立有索引，mysql 将使用该索引找到值为'2333'的数据行，然后读取数据行的所有数据返回。

索引类型

B-Tree 索引

（不是 B 减树，就是 B 树），绝大多数的索引类型都是 B-Tree 的（或者是 B-Tree 的变体），通常我们使用的也是这类索引。Mysql 中 MyISAM 存储引擎使用的是 B-tree，InnoDB 使用的是 B+Tree，B 树和 B+树的区别自行百度。

树结构的索引能够加快访问数据的速度，存储引擎不再需要全表扫描来获取所需的数据，取而代之的是从树的根节点来进行二分搜索，总所周知二分搜索的速度是相当快的，因此我们能够利用索引来极大的提高查询速度。B-Tree 支持以下几种类型的查询：

假设再 student 表中仅有：name,age,weight 这样一个多列索引,下面的查询都能利用到此索引

• 全值匹配

和索引列中的所有列进行匹配。比如查询name='abc' and age=12,这里用到了第一列和第二列

• 匹配最左前列

只是用索引的开头部分，比如查询name='ggg'只使用索引的第一列，查询name='ggg' and age=12是用索引的第一、二列。

• 匹配列前缀

也可以只匹配某一列的开头部分，比如查询name lik 'g%',查询 name 以 g 开头的记录。这里用到了第一列

• 匹配范围值

可用于匹配范围值，比如查询name > 'abc' and name < 'bcd'

• 精确匹配某一列并范围匹配另外一列

用于匹配多列，比如查询name='abc' and age > 12。

总的来看，可以发现 B-Tree 索引适用于根据最左前缀的查找，也就是查询字段字段顺序要和索引字段顺序一样，且以第一个索引字段开头。比如查询name,name and age,name and age and weight都能使用索引，但是查询age,age and name不能使用索引。

哈希索引

hash 索引基于 hash 表实现，只有精确匹配索引所有列才会生效。MySQL 中只有 Memory 引擎显示支持哈希索引，同时也是其默认索引。

InnoDB 无法创建 hash 索引，但是它有一个功能叫自适应hash索引,当某些索引值使用非常频繁时，引擎会在内存中基于 B-Tree 索引之上再创建一个 hash 索引，这样就让 B-Tree 索引也有了一点 hash 索引的优点。这个功能是一个完全自动的、内部的行为，也就是无法手动控制或配置。

高性能索引策略

下面是一些常见的索引策略。

独立的列

这个很简单，如果查询中的列不是独立，便无法使用索引，比如：

select * from student where age+1=12

即使 age 列有索引，上面的查询语句也是无法利用索引的。

前缀索引和索引选择性

如果需要索引很长的字符串列，直接创建索引，会让索引占用更多的空间且速度较慢。一个优化策略是模拟 hash 索引：给列计算一个 hash 值，并在 hash 值列建立索引。

另外一个办法就是建立前缀索引。只索引这个字段开始的部分字符，这样可以极大的解决空间占用，索引建立速度也会快很多。但是这样也有如下弊端：

• 降低了索引选择性，如果多个字符串前缀相同便无法区分，还需要进行字符串对比。
• 不支持order by,group by,原因显而易见，只索引了部分字符，无法完全区分。

这里的关键是确定索引多少个字符合适。既要避免长度过大，还要有足够的索引选择性。有以下两种办法来帮助确定索引字符数：

索引字段前缀数据分布均匀。也就是以索引字符开头的字符串数目分布均匀，比如索引 name 字段的前 3 个字符，下面的结果是比较合理(只取排名前 8 的）：

如果每一列的数据都比较大，说明区分度还不高需要增大索引字符数，直到这个前缀的选择性接近完整列的索引性，也就是前面的数据要尽可能的小。

计算完整列的选择性，并使前缀的选择性接近于完整列的选择性。下面语句用户计算完整列选择性：

-- 不同字符串的数目/总的数目就是完整列选择性
select count(distinct name)/count(*) from person;

下面语句计算索引前 3 个字段选择性：

-- 前3个字符不同的字符串数据/总的数据
select count(distincy left(city,3))/count(*) from person

不断增大索引字符数目，直到选择性接近完整列选择性且继续增大数据选择性提升幅度不大的时候。

创建方法

-- 假设最佳长度为4
alter table person add key (name(4));

多列索引

不少人有这样的误解，如果一个查询用有多个字段 ‘and'查询，那么给每个字段都建立索引不就能最大化提高效率了？事实并不是如此，mysql 只会选择其中一个字段来进行索引查找。这种情况下应该建立多列索引（又叫联合索引），就能利用多个索引字段了，注意索引列顺序要和查询的顺序一致。

在 5.0 及以上版本中引入了“索引合并”的策略。一定程度上也可以使用多个单列索引，比如下面的查询：

-- mysql会分别使用name和age索引查出数据然后合并
-- 如果使and则查出数据后再对比取交集
select * from person where name = "bob" or age=12

但是不推荐这么做，and 或 or条件过多会耗费大量的 CPU 和内存在算法的缓存、排序和合并操作上。

选择合适的索引列顺序

在一个多列 B-Tree 索引中，索引列的顺序意味着索引首先是按照最左列进行排序，然后是第二列…索引一个良好的多列索引应该是将选择性最高的索引放在最前面，然后依次降低，这样才能更好的利于索引。选择性计算方发见：前缀索引 小节。

聚族索引

聚族索引不是一种单独的索引类型，而是一种数据存储方法，具体的细节依赖其实现方式。

InnoDB 的聚族索引实际是在同一个结构中保存索引值和数据行。因为不能同时将数据行放在两个不同的地方，所以一个表只能有一个聚族索引。InnoDB 的聚族索引列为“主键列”。

如果没有定义主键,InnoDB 会选择一个唯一的非空索引代替。如果这样的索引也没有，InnoDB 会隐式定义一个主键来作为聚族索引。

聚族索引的主要优点是：可以把相关数据保存在一起，减少磁盘 IO,提高查询效率。但是也有缺点：

• 插入顺序严重依赖于插入顺序。按照主键的顺序插入是速度最快的方式，否则可能会导致页分裂的问题出现，会占用更多的磁盘空间，扫描速度也会变慢。可通过OPTIMIZE TABLE重新组织表。
• 更新聚族索引列代价很高，因为索引值变了，行数据也会跟着索引移动到新的位置上。
• 二级索引（非聚族索引）访问行数据需要两次索引查找，因为二级索引叶子节点存储的并不是行数据的物理位置，而是行的主键值，再通过主键值到聚族索引中取行数据。

覆盖索引

简单来说就是一个索引覆盖了需要查询的列字段，这样就不需要再到聚族索引中利用主键进行二次查找，在一个二级索引中就能取到所需的数据。

InnoDB 的索引会在叶子节点中保存索引值，因此如果要查询的字段全部包含在某个索引中，且这个索引被使用了，那么就能极大的提高查询速度。比如如下查询语句：

-- name有索引的情况下，直接从索引的叶子节点中取name值返回，无需二次查找
select name from person where name = 'abc'
-- 如果存在`name,age`聚合索引，也会直接返回数据，无需二次查找
select name,age from person where name='abc' and age=12

使用索引进行排序

mysql 的排序操作也是可以利用索引的，只有当索引的列顺序和ORDER BY的顺序完全一致，并且所有列的排序方法（正序或者倒序）也一样时，才能够使用索引来进行排序。注意：排序的字段可以比对应的索引字段少，但是顺序必须一致。如下:

-- 假设有：(name,age,sex)联合索引
-- 可使用索引排序
select ... order by name desc,age desc
select ... order by name desc,age desc,sex desc
-- 不可使用排序
select ... order by name desc,sex desc
select ... order by name desc,age asc

结束

本篇基于 mysql 5.5 的版本，更新的版本可能会有不一样的策略。