索引的优点

索引大大减少了服务器需要扫描的数据量
索引可以帮助服务器避免排序、分组和临时表（临时表主要是在排序和分组过程中创建，因为不需要排序和分组，也就不需要创建临时表）
索引可以将随机I/O变为顺序I/O（B-Tree索引是有序的，会将相邻的数据都存储在一起）

索引的类型

在MySQL中，索引是在存储引擎层而不是服务器层实现的，所以不同的存储引擎的索引类型和实现不同。

B-Tree索引

大多数存储引擎都使用B-Tree作为默认索引类型，但实际在技术上往往使用的是B+Tree，例如InnoDB。B-Tree索引之能够加快访问数据的速度，是因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索。关于B-Tree和B+Tree的工作原理可以参考之前的一篇笔记：常用查找算法之B/B+树。

B-Tree索引适用于全键值、键值范围或键前缀（最左前缀）查找。因为索引树中的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的ORDER BY操作。

B-Tree同样也有一些限制：

如果不是按照索引的最左列开始查找，则无法使用索引。
不能跳过索引中的列，否则只会使用跳过之前的索引列。
如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。

哈希索引

哈希索引基于哈希表实现，对于每一行数据，存储引擎都会对所有索引列计算一个哈希码并存储在索引中，同时在哈希表中保存指向每个数据行的指针。如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。哈希索引有如下限制：

哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。
无法用于排序。
不支持部分索引列匹配查找。
不支持范围查询。
哈希冲突。

InnoDB引擎有一个特殊的功能叫做“自适应哈希索引”，当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引，这样就让B+Tree索引具有哈希索引的一些优点，比如快速的哈希查找。

全文索引

全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文索引使用倒排索引实现，它记录着关键词到其所在文档的映射。在相同的列上同时创建全文索引和基于值的B-Tree索引不会有冲突，全文索引适用于MATCH AGAINST操作，而不是普通的WHERE条件操作。

空间数据索引

MyISAM存储引擎支持空间数据索引（R-Tree），可以用作地理数据存储。空间索引会从所有维度来索引数据，查询时可以有效利用任意维度来组合查询。必须使用MySQL的GIS相关函数来维护数据。

索引优化

独立的列

在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则MySQL无法使用索引。

例如：SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

多列索引

在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。例如下面的语句中，最好把actor_id和film_id设置为多列索引：

1 2	SELECT film_id, actor_id FROM sakila.film_actor WHERE actor_id = 1 AND film_id = 1;

索引列的顺序

将选择性最强的索引列放在前面。索引的选择性是指不重复的索引值（基数）和记录总数的比值，选择性越高则查询效率也越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。

前缀索引

对于很长的字符串可以索引开始的部分字符，使得前缀的选择性接近于完整列的选择性。

聚簇索引

B-Tree索引类型都可以用在MyISAM和InnoDB上，但InnoDB有聚簇索引的特性而MyISAM没有。

聚簇表示数据行和相邻的键值紧凑地存储在一起，因为无法同时把数据行存放在两个不同的地方，所以每张Innodb引擎表都只有一个聚簇索引。一般情况，聚簇索引就是主键索引（因为聚簇索引在有主键的情况下，默认指定主键为聚簇索引），而非聚簇索引都是二级索引。

如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。采用聚簇索引，索引和其他列值存储在一起，因此数据访问比采用非聚簇索引（如MyISAM引擎）更快，节省了磁盘I/O资源。

二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获得对应的主键值，然后根据这个值去聚簇索引中查找到对应的行。这样虽然会让二级索引占用更多的空间，但换来的好处是InnoDB在移动行时减少了二级索引的维护工作。

MyISAM没有聚簇索引的特性，主键索引和其它索引在结构上没有什么不同。

使用InnoDB存储引擎时应该尽可能地按主键顺序插入数据（可以使用AUTO_INCREMENT自增），最好避免随机的插入（例如使用UUID作为主键）。因为当主键的值是顺序的时，InnoDB会把每一条记录都存储在上一条记录的后面，当达到页的最大填充因子时（默认为15/16），下一条记录就会写入新的页中。而每次插入主键的值近似于随机时，新纪录根据值的大小要被插入到现有索引页的中间某个合适位置，此时页分裂会导致大量的数据移动并产生碎片，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销。

覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”，此时不需要回表操作。其具有以下优点：

索引条目通常远小于数据行大小，所以如果只需要读取索引，那MySQL就会极大地减少数据访问量。
因为索引是按照列值顺序存储的，所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少得多。
一些存储引擎如MyISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用，比较费时。
对于InnoDB引擎，若二级索引能够覆盖查询，则可以避免对主键索引的二次查询。

使用索引扫描来排序

MySQL有两种方式可以生成有序的结果：通过排序操作或者按索引顺序扫描。如果EXPLAIN出来的type列的值为index，则说明MySQL使用了索引扫描来做排序。

只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。ORDER BY子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求，否则MySQL都需要执行排序操作，而无法利用索引排序。

MySQL索引类型与优化