高性能索引设计

索引的优点

• 减少了服务器需要扫描的数据量
• 帮助服务器避免排序和临时表
• 将随机I/O变成顺序I/O

索引的类型

B-Tree索引

1. B-TREE通常就意味着里面存储的所有值都是有序的，并且查询的时候，不用全表扫描，而是按照索引结构查找，所以会更快
2. INNODB使用的B+TREE，B+TREE是B-TREE的一个变种，区别是B+TREE为所有叶子结点增加了一个存储指向下个叶子结点的链指针和所有关键字都在叶子结点中出现.

适用的查询范围：

• 全值匹配。和索引中的所有列进行匹配。
• 匹配最左前缀。多列索引的最左前缀原则。例如一个多列索引为（A,B,C），当你的查询中包括A或A,B或A,B,C都可以用到索引，如果只有B，则无法用到该索引。
• 匹配列前缀。像like 'abc%'可以用到索引，而like '%abc%'就无法用到该类索引。
• 匹配范围值。其实就是范围查询，但当多列索引中有一列用到范围查询时，那么该列后面的索引都没法被用到。例如一个索引为（A,B,C），又一个查询为where A=1, B>1, C=1,那么这个查询只会用到（A,B,C）中的A,B列，C是不会被用到。
• 只访问索引的查询。其实就是覆盖索引查询。

 hash索引

哈希索引是基于哈希表实现，在MySQL中，目前只有Memory引擎支持哈希索引。

	适用场景	不支持场景
哈希索引	只支持等值比较查询	1、哈希索引的数据并不是按照索引值顺序存放的，因此无法用于排序查找和范围查找 2、不支持部分索引列按匹配查找，使用索引列的全部内容来计算哈希值

高性能索引策略

1 独立的列

索引列不能是表达式的一部分，也不能是函数的参数

2 前缀索引和索引选择性

索引选择性：不重复的索引值和数据表记录总数（T）的比值，取值范围是从1/T ~1之间。索引的选择性越高，查询效率越高。唯一索引的选择性是1。

前缀索引：某个列太长，比如varchar类型很长的列，则必须使用前缀索引。关键在选择足够长的前缀保证较高的选择性，但又不能太长。方法：找到最常见的值列表，然后和最常见的前缀列表进行比对，然后逐渐增加前缀长度，直到前缀的选择性接近于完整列的选择性。

select count(distinct city) / count(*) from city_demo; mysql> select count(distinct left(city,3))/count(*) as sel3,     -> count(distinct left(city,4))/count(*) as sel4,     -> count(distinct left(city,5))/count(*) as sel5,     -> count(distinct left(city,6))/count(*) as sel6     -> from city_demo; +--------+--------+--------+--------+ | sel3   | sel4   | sel5   | sel6   | +--------+--------+--------+--------+ | 0.3367 | 0.4075 | 0.4208 | 0.4267 | +--------+--------+--------+--------+ 1 row in set (0.01 sec) mysql>

 

缺点：mysql无法使用其前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描

3 多列索引

建立多列索引，那么选择合适的顺序相当重要。对于如何选择合适的索引顺序，有一个经验法则：将选择性最高的列放到索引最前列。

4 聚簇索引

聚簇索引把数据行存储在叶子页中，一个表中只能有一个聚簇索引。InnoDB存储引擎支持聚簇索引，在InnoDB中，聚簇索引其实就是主键索引。如果表中没有定义主键，InnoDB会选择一个唯一非空索引作为主键。如果没有这样的索引，InnoDB会隐式的定义一个主键来作为聚簇索引。聚簇索引的优点如下：

• 可以把相关数据保存在一起。
• 数据访问更快。
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

如果表在设计和查询的时候能充分利用以上特点，将会极大提高性能。

当然，聚簇索引也有它的缺点：

• 聚簇索引最大限度提高了I/O密集型应用的性能，但如果所有的数据都存放在内存中，聚簇索引就没有优势了。
• 插入速度严重依赖插入顺序。这也是为什么InnoDB一般都会设置一个自增的int列作为主键。
• 更新聚簇索引的代价很高，因为会强制InnoDB将每个被更新的行移到新的位置。
• 如果不按顺序插入新数据时，可能会导致"页分裂"。
• 二级索引可能会比想象的更大。因为在二级索引的页子节点中包含了引用行的主键列。
• 二级索引访问可能会需要进行回表查询。

5 覆盖索引

　如果一个索引包含或覆盖所有需要查询的字段值，我们就称之为“覆盖索引”。 覆盖索引是一个非常有用的工具，可以极大的提升性能。所以可能一个索引对于某些查询是覆盖索引，而对于其他的查询则不是。覆盖索引其实是二级索引的特例，它满足了一个特定条件：

• 索引行通常远小于数据行的大小，所以如果只需要索引，那么MySQL就会极大地减少数据访问量。
• 因为索引是按照顺序存储的，所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少的多。
• 由于InnoDB的聚簇索引，所以覆盖索引对InnoDB特别有用。

当发起一个覆盖查询的时候，在Explain中的Extra列中可以看到“Using index”的信息。对于select *，没有任何一个索引能够覆盖所有列。所以select * 可能会导致原本可以用到覆盖索引的查询而无法使用覆盖索引。

6 索引条件下推(ICP)

ICP的目的是通过减少完整记录读取的数量来减少IO操作。在没有ICP的时候，WHERE条件中没有被索引用到的列的过滤是在MySQL服务层中，而有了ICP之后，这种过滤就直接在储存引擎层中完成了，而且是在二级索引回表查询前就完成了过滤，这就避免了大量数据传输，从而降低磁盘IO。