创建一个高性能索引，有哪些策略可以借鉴？

高性能的索引策略

独立的列

如果查询的列不是独立的，则MySQL就不会使用索引。独立的列是指索引列不能是表达式的一部分，也不能是函数的参数。

如：

select actor_id FROM sakila.actor where actor_id + 1 = 5;

其实屏肉眼你是可以判断出这个actor_id其实就是=4，但是mysql是无法自动解析这个方程式的。这完全是用户行为，我们应该养成习惯，始终将索引列单独放在比较符号的一侧

前缀索引和索引的选择性

有时啊，需要的索引很长的字符列，这样呢会使索引变得大且慢。一个策略是前面提到过的模拟哈希索引。再者呢？

我们可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。

但是

这样会降低索引的选择性：

索引的选择性=不重复的索引值和数据表的记录总数的比值。

索引的选择性越高则查询的效率越高，因为选择性高的会在查找时过滤掉更多的行。

当然对于blob，text，varchar这种很长类型的列，必须使用前缀索引，mysql不允许索引这些列的完整长度。

多列索引

很多人对多列索引的理解不够。一个常见的错误就是，为每个列都创建独立的索引，或者按照错误错误的顺序创建多列索引。

你比如说下面这个例子：

CREATE TABLE t(
c1 int,
c2 int,
c3 int,
key(c1),
key(c2),
key(c3)
);

像这样在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。

但是

MySQL5.0和更新的版本引入了一个叫“索引合并”的策略，一定程度上是可以使用表上的多个单列索引来定位指定的行。在此之前的版本的MySQL只能使用其中某个单列索引。

你再比如说下面这个例子：

select film_id,actor_id from biao
where actor_id =1 OR film_id = 1;

在老版本的mysql中，你像上面这样where里的OR语句的情况是不能使用两个单列索引进行扫描的，只会进行全表扫描。除非你把or改成UNION的形式。

索引合并策略确确实实的解决了上面我们所描述的那种情况。但实际上更多时候还是说明呀，表上的索引建的hin糟糕：

• 当出现服务器对多个索引做相交操作时（通常有多个and条件）。通常其实意味着是需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
• 当服务器需要对多个索引做联合操作时（通常有多个or操作），通常需要耗费大量的CPU和内存资源在算法的缓存，排序和合并操作上。特别是有些索引的选择性不高，需要合并扫描返回的大量数据的时候
• 更重要的是，优化器并不会把这些计算到“查询成本”中，优化器只关心随机页面读取。这会使得查询的成本被低估，导致该执行计划还不如直接走全表扫描。这样子不但会消耗更多的CPU和内存资源，还可能会影响到查询的并发性。

所以：如果在explain中看到有索引合并，应该好好去检查一下查询的表和结构，看看是不是已经最优了。你可以通过参数optimizer switch 来关闭索引合并功能。也可以使用ignore index 提示优化器忽略掉某些索引。

选择合适的索引顺序

我们遇到的最容易引起困惑的问题就是索引列的顺序。这个顺序的正确性其实是依赖使用该索引的查询是怎么样的。并且我们还需要考虑如何更好的满足排序和分组的需要。（顺带说一嘴，本节内容适用于b树索引）

我们之前也有介绍，在一个多列b树索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，索引可以按照升序或者是降序进行扫描，以满足查询列里的orderby groupby 和distinct等子句的查询需求。

多列索引的列顺序是至关重要的。

那么，对于如何选择索引的列顺序呢？有这样一个经验法则：将选择性最高的列放到索引的最前列。当然了场景不同，选择不同，没有那种放任四海皆准的法则。

当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。这时候索引的作用就是用于优化where条件的查找。在这种情况下，这样设计的索引确实能够最快的过滤出需要的行，对于在where子句中只使用索引部分前缀列的查询来说选择性也更高。当然为了性能你还得考虑哪些列是运行频率比较高的，来调整索引列的顺序,让这种情况下的索引的选择性最高。

你比如说下面这个例子：

SELECT * FROM 表 
WHERE
staff_id = 2 AND customer_id = 584

那么你就要考虑是创建一个（staff_id,customer_id）索引，还是两者调换一下顺序呢？

这个我们就得去查询一下表中值的分布情况，来确定哪个列的选择性更高。

我们一查就发现：

staff_id有7992个数据，staff_id只有30个数据。

那么根据我们的经验法则，我们应该将索引列customer_id放到前面，因为对应条件值的customer_id数量更少。

这样做其实你还要注意一个地方，因为这个查询结果是十分依赖选定的具体值。如果按照上面的查询结果进行优化，可能对其他的值不公平，进而导致服务器整体的性能可能更糟。

但是一般情况下我们就用下面介绍的经验法则就可以知道那个字段选择性更高：

SELECT COUNT(DISTINCT staff_id)/count(*) AS staff_id_selectivity,
COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
COUNT(*)
FROM 表;

这样我们可以看出来customer_id的选择性更高， 所以就将它作为索引的第一列

ALTER TABLE 表  payment ADD KEY (customer_id,staff_id);

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，比如说innodb的聚簇索引实际上在同一个结构中保存了b树索引和数据行。

当表中有聚簇索引时，他的数据行实际上存放在索引的叶子页中。

如果没有定义主键，innodb会选择一个唯一的非空索引代替。如果没有这样的索引，innodb会隐式定义一个主键来作为聚簇索引。innodb只聚集在同一个页面中的记录。包含相邻键值的页面可能会相距甚远。

但是聚簇主键可能对性能有帮助，也可能导致严重的性能问题

:heavy_check_mark:聚集的数据有一些重要的优点：

• 可以把相关数据保存在一起。比如存储电子邮箱，可以根据用户ID来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘IO。
• 数据访问更快。聚簇索引将索引和数据保存在同一个b树中，因此从聚簇索引中获取数据你通常要比在非聚簇索引中查找要快
• 使用覆盖索引扫描的查询可以直接使用叶节点中的主键值。

:heavy_multiplication_x:聚簇索引的一些缺点：

• 聚簇数据最大限度地提高了IO密集型应用的性能，但是如果数据全部都放在内存中，则访问的数据就没那么重要了，聚簇索引也就没什么优势了。
• 插入速度严重依赖与插入顺序。按照主键的顺序插入是加载数据到innodb表中速度最快的方式。但是如果不是按照主键顺序加载数据，那么在加载完成后最好使用optimize TABLE命令重新组织一下表。
• 更新聚簇索引列的代价很大，因为innodb会强制将每个被更新的行移动到新的位置。
• 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“叶分裂”的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间。
• 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者页分裂导致数据存储不连续的时候。
• 二级索引（非聚簇索引）可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列。

最后一点，做出一个解释，为什么二级索引需要两次索引查找？因为二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。这就意味着通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获得对应的主键值。然后根据这个值去聚簇索引中查找到对应的行。

覆盖索引

通常啊，我们大家都会根据查询的where条件来创建合适的索引，不过这只是索引优化的一个方面。设计优秀的索引是应该考虑到整个查询，而不是单单where条件部分。索引确实是一种查找数据的高效方式，但是mysql也可以使用索引来直接获取列数据，这样就不需要再读取数据行。

咱就是说，如果索引的叶子节点中已经包含要查询的数据，那么还有什么必要回表查询呢？

如果一个索引包含所有需要查询的字段的值，我们就称之为“覆盖索引”。

如果咱们查询只是需要扫描索引而无须回表，是会带来很多好处的：

• 索引条目通常远小于数据行大小，所以如果只需要读取索引，那么MySQL就会极大的减少数据访问量。这对缓存的负载非常重要，因为这种情况下响应时间大部分花费在数据拷贝上了。覆盖索引对IO密集型的应用也有帮助，因为索引比数据更小，更容易全部放入内存。
• 因为索引是按照列值顺序存储的，所以对于IO密集型的范围查询会比随机从磁盘读取每一行数据的IO要少的多。
• 由于innodb使用聚簇索引，所以覆盖索引对innodb是特别有用的，innodb的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。这个我们之前也有提到过。

当然，我们要清楚一点，不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，而哈希索引，空间索引和全文索引等都不存储索引列的值，所以mysql只能使用b树索引做覆盖索引。