创建高性能的索引

索引是一种快速找到记录的数据结构

B-Tree索引

我们把它读作B+树索引，是一种特殊的树形结构。

自适应哈希索引

InnoDB 发现当某些索引值被频繁的访问时，会在原有的b+树索引上在构建一个哈希索引，使其也具备了哈希索引的优势。这些操作是InnoDB自动完成的，用户无法控制，不过可以通过参数关闭这个特性。

B+树索引的一些限制：

不符合最左前缀。即a、b、c三个索引只使用b、c索引。
不能跳过索引中的列。
like 条件，但是%在开头的情况下不会走索引（也没那么绝对）

全文索引

它查找的是文本中的关键词，而不是直接比较索引中的值。

前缀索引

有时候为了提升索引的性能，同时也节省索引空间，可以只对字段的前一部分字符进行索引。但是也有缺点：MySQL无法使用前缀索引做ORDER BY和GROUP BY操作，也无法使用前缀索引做覆盖扫描。

索引的选择性

索引的选择性是指，不重复的索引值（也称为基数，cardinality）和数据表的记录总数（＃T）的比值，范围从1/＃T到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

多列索引

错误的多列索引是为每列创建独立的索引，或者按照错误的顺序创建多列索引。

MySQL引入了一种叫“索引合并”（index merge）的策略，它在一定程度上可以使用表中的多个单列索引来定位指定的行。在这种情况下，查询能够同时使用两个单列索引进行扫描，并将结果进行合并。

虽然索引合并有时候效果不错，但是也从侧面说明了索引建立的很糟糕：

优化器对索引做相交操作时（通常发生在多个and条件），意味着使用多列索引可能会更好，而不是单个列的索引。
优化器对索引做联合操作时（通常发生在多个and条件），通常在算法的缓存、排序、合并上做大量的操作需要消耗cpu资源。
更加重要的时候优化器不会把这些计算到查询成本中，使得查询的成本被低估。

如果在EXPLAIN中看到有索引合并，那么就应该好好检查一下查询语句的写法和表的结构，看是不是已经是最优的。也可以通过参数optimizer_switch来关闭索引合并功能。

如何选择合适的索引列顺序？

经验：

通盘考虑，应该避免大量随机I/O和排序。如果不考虑排序和分组将选择性最高的列放在最前面是比较高性能的。
根据查询的值来判断，根据查询频率高的列或者根据列的值的分部来设置索引的排序。但是也不能根据某查询得出结果一概而论。

聚集索引

是一种存储数据的方式，将索引和数据存储在一起，拿到索引就相当于拿到了数据。在InnoDB中的聚集索引就是在同一个结构上保存了索引和数据结构。数据存储在索引的叶子结点上，所以一个表只能有一个聚集索引。不过覆盖索引可以模拟多个聚集索引的情况。

InnoDB根据主键聚集数据，所以一个表中没有定义主键的话，InnoDB会选择一个唯一的非空索引代替，如果没有符合的情况， InnoDB会自己创建一个唯一值作为主键索引。

聚集索引的好处：

能够更快的读取数据，相比于非聚集索引能够更快的查询到数据。
使用覆盖索引查询时，可以直接使用节点中的主键值。

缺点：

数据都存储在索引中，索引在内存中缓存，极大地增加了IO密集型的性能，但是如果都在内存中。聚集索引也没有什么优势了。
插入的速度依赖插入的顺序。
更新聚集索引的代价很高，会对所有的索引节点进行重新排序。
二级索引可能会变得更大，因为二级索引的叶子节点包含了引用行的主键列。
主键被更新导致需要移动行的时候，可能面临页分裂（page split）的问题，导致磁盘占用变大。
二级索引访问需要两次索引查找，而不是一次。因为二级索引保存的是主键的值，还需要在重新在聚集索引中茶盏一次，才能拿到数据。自适应哈希索引能够减少这样的重复工作。

非聚集索引

其他非主键索引都叫二级索引或者是非聚集索引。

覆盖索引

创建索引一般会根据where条件来创建，设计优秀的索引应该考虑到整个查询，而不单是WHERE条件部分。如果床读取索引是能直接获取到列的数据，即索引的叶子结点已经包含要查询的数据。就没有必要在回表查询（即再根据主键索引查询实际的数据列）了。如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为覆盖索引。需要注意是，只有B-tree索引可以用于覆盖索引。

好处：