高性能索引策略（上）

迷南。 2022-07-15 12:46 160阅读 0赞

高效的选择和使用索引有很多方式，其中有些事针对特殊案例的优化方法，有些则是针对特定行为的优化，使用哪个索引，以及如何评估选择不同索引性能影响的技巧，则需要持续不断的练习。接下来将介绍如何高效的使用索引。

独立的列

我们通常会看到一些查询不当的使用索引，或者是的MySQL无法使用已有的索引。如果查询中的列不是独立的，则MySQL就不会使用索引。“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。

例如，下面的这个查询无法使用actor\_id的索引：

SELECT actor\_id FROM actor WHERE actor\_id +1 = 5;

凭肉眼很容易看出WHERE 中的表达式其实等价于 actor\_id=4 ,但是MySQL无法自动解析这个方程式。这完全是用户的行为。我们应该养成简化WHERE条件的习惯，始终将索引列单独放在比较符合的一侧。

下面是常见的错误：

SELECT ... WHERE TO\_DAYS(CURRENT\_DATE) - TO\_DAYS(date\_col) <=10;

前缀索引和索引选择性

有时候需要索引很长的字符列，这会让索引编的大且慢。一个策略是前面提到的模拟hash索引。蛋有时候这样做还不够，还可以做什么呢？

通常可以索引开始的部分字符串，这样可以大大节约索引空间，从而提高索引效率。但是这样也会降低索引的选择性。索引的选择性是指，不重复索引的值（也称为基数）和数据表的记录总数（\#T）的比值，范围从1/\#T到1之间。索引的选择性越高，则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

一般情况下某个列前缀索引的选择性也是足够高的，足以满足查询性能。对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。

诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）。前缀应该足够长，以使得前缀索引的选择性接近于索引整个列，换句话说，前缀的“基数”应该接近于完整列的“基数”。

为了决定前缀的合适长度，需要找到最常见的值的列表，然后和最常见的前缀列进行比较。

前缀索引是一种能使索引更小，更快的有效办法，但另一方面也有其缺点：MySQL无法使用前缀索引做order by 和group by 操作，也无法使用前缀索引做覆盖扫描。

一个常见的场景是针对很懂行的十六进制唯一ID使用前缀索引。在前面已经讨论了很多有效的基数来存储这类ID信息，但如果使用打包过的解决方案，因而无法修改存储结构，那该怎么办？此时如果采用长度为8的浅醉索引通常能够显著的提升性能，并且这种方法对引用层上完全透明。

有时候后缀索引也有用途（例如，找到摸个域名的所有电子邮件地址）。MySQL原生并不支持反向索引，但是可以吧字符串反转后存储，并给予此建立前缀索引。可以通过触发器来维护这种索引。

多列索引

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

我们会在稍后的章节中单独讨论索引列的顺序问题。先来看第一个问题，为每个列床架独立的索引，从SHOW CREATE TABLE 中很容易看到这种情况：

CREATE TABLE t(

c1 INT,c2 INT , c3 INT ,KEY(c1),KEY(c2),KEY(c3)

);

这种索引策略，一般是由于人们听到一些专家诸如“把WHERE 条件里面的列都建上索引”这样模糊的建议导致的。实际上这个建议是非常错误的。这样一来最好的情况也只能是“一星”索引，其性能比起真正最有效的索引可能差几个数量级。有时如果无法设计出一个“三星”索引，那么不如忽略掉WHERE 子句，集中精力优化索引列的顺序，或者创建一个全覆盖索引。

在多个列上奖励独立的单列索引大部分情况下不能提高MySQL的查询性能。MySQL5.0和更高的版本医用了一种叫“索引合并”策略，一定程度上可以使用表上的多个单列索引来定位指定的行。更早版本的MySQL只能使用其中某一个单列索引，然而这种情况下没有哪一个独立索引是非常有效的。例如在film\_actor在字段film\_id和actor\_id上各有一个单列索引。但是对于这个查询WHERE 条件，这两个单列索引都不是好的选择：

SELECT film\_id ,actor\_id FROM film\_actor WHERE actor\_id=1 or film\_id =1;

在老的MySQL版本中，MySQL对于这个查询是会使用全表扫描的，除非改写成如下的两个查询UNION的方式：

SELECT film\_id ,actor\_id FROM film\_actor WHERE actor\_id=1

UNION ALL

SELECT film\_id ,actor\_id FROM film\_actor WHERE film\_id=1；

但是在MySQL5.0 和更高的版本中，产线能够同时使用者两个单列索引进行扫米昂，并将结果进行合并。这种算法有三个变种：OR条件的联合，AND条件的相交，组合前面两种情况的联合及相交。下面的查询就是使用了两个索引扫描的联合，通过EXPLAIN中的Extra列可以看出这点：

EXPLAIN SELECT film\_id,actor\_id FROM film\_actor WHERE actor\_id=1 or film\_id = 1 \\G

MySQL会使用这类技术优化负责的查询，所以在某些语句的EXTRA列中还可以看到嵌套操作。

索引合并策略有时候是一种优化的结构，但实际上更多的时候说明了表上的索引建的很糟糕：

当出现服务器对多个索引做相交操作（通常有多个AND条件），通常意味着需要一个包含所有相关列的多个索引，而不是独立的单列索引。

当服务器需要对多个索引做联合操作（通常有多个OR条件），通常需要耗费大量的cpu和内存资源在算法的缓冲，排序和合并的操作上。特别是当其中有些索引的选择性不高。需要合并扫描返回大量数据的时候。

更重要的是，优化器不会吧这些成本算到“查询成本”中，游虎丘只关心随机页面读取。这会使得查询成本被低估，导致该执行计划还不如直接走全表扫描。这样做不但会消耗更多的cup和内存资源，还可能影响查询的并发性，但如果是单独鱼腥这样的查询则往往会忽略对并发现的影响。通常来说，还不弱在MySQL4.1或更早的时代一样，将查询改写成UNION的方式往往会更好。

原文：http://www.cnblogs.com/zhengyanqiu/p/4979441.html