高性能的索引策略

心已赠人 2021-12-05 15:58 276阅读 0赞

# 独立的列 #

如果查询中的列不是独立的，则MySQL就不会使用索引。“独立的列”是指：

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

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

mysql> SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

上面WHERE条件其实可以简化成`actor_id = 4`。要养成**简化WHERE条件**的习惯，始终**将索引列单独放在比较符号的一侧**。

# 前缀索引和索引选择性 #

有时候需要索引很长的字符列，这会让索引变得大且慢。该怎么办？

*  模拟哈希索引
 *  **索引开始的部分字符，而不是全部字符**

模拟哈希索引这里不讲，并且只用它有时候也是不够的。此处只分析前缀索引。

只索引开始的部分字符，可以**大大节约索引空间**，从而提高索引效率，但这样也会**降低索引的选择性**。

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

对于**BLOB、TEXT或者很长的VARCHAR**类型的列，必须使用前缀索引。MySQL不允许索引这些列的完整长度。

诀窍在于要**选择足够长的前缀以保证较高的选择性，同时又不能太长**（节约空间）。

*  前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。
 *  即前缀的“基数”应该接近于完整列的“基数”。

**以一个示例来说明选择前缀长度的基本思路：**

对于city表，查询每个城市出现的次数：  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Rhcmxpbmd3b29kMjAxMw_size_16_color_FFFFFF_t_70][]  
上面每个值都出现了45~65次。

如果选择的前缀长度为3，查询不同前缀出现的次数：  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Rhcmxpbmd3b29kMjAxMw_size_16_color_FFFFFF_t_70 1][]  
我们发现每个前缀都比原来的城市出现的次数更多。  
增加前缀长度，直到这个前缀的选择性接近完整列的选择性。经过试验，发现前缀长度为7比较合适：  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Rhcmxpbmd3b29kMjAxMw_size_16_color_FFFFFF_t_70 2][]  
计算合适的前缀长度的另外一个办法就是**计算完整列的选择性**，并使前缀的选择性接近于完整列的选择性。  
![20190718170944186.png][]  
通常来说，这个例子中如果前缀的选择性能够接近0.031，基本就可以了。  
可以在一个查询中**针对不同前缀的长度进行计算**，这对于大表非常有用。  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Rhcmxpbmd3b29kMjAxMw_size_16_color_FFFFFF_t_70 3][]  
根据上面的查询结果可以看出来，当前缀长度到达7的时候，再增加前缀长度，选择性提升的幅度已经很小了。  
除了**平均选择性**，有时也需要考虑**最坏情况下的选择性**。  
平均选择性会让你认为前缀长度为4或者5的索引已经足够了，但如果数据分布很不均匀，可能会有问题。  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Rhcmxpbmd3b29kMjAxMw_size_16_color_FFFFFF_t_70 4][]  
如果前缀是4个字节，则最常出现的前缀的出现次数比最常出现的城市的出现次数要大很多。即**这些值的选择性比平均选择性要低**。  
例如在真实的城市名上建一个长度为4的前缀索引，对于以“San”和“New”开头的城市的选择性就会非常糟糕，因为很多城市都以这两个词开头。

找到合适的前缀长度就可以创建索引了：  
![20190718172211797.png][]  
**前缀索引总结**

*  前缀索引是一种**能使索引更小、更快**的有效办法。
 *  但MySQL无法使用前缀索引做 **ORDER BY** 和 **GROUP BY**，也无法使用前缀索引做**覆盖扫描**。  
    （覆盖扫描是啥？）
 *  一个常见的应用场景：针对很长的十六进制唯一ID使用前缀索引。

**延伸**  
有时候后缀索引也有用途，eg.找到某个域名的所有电子邮件地址。  
MySQL原生不支持反向索引，但可以把字符串反转后存储，并基于此建立前缀索引。

# 多列索引 #

# 选择合适的索引列顺序 #

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Rhcmxpbmd3b29kMjAxMw_size_16_color_FFFFFF_t_70]: /images/20211205/d75df1629da6491da7fdbe73695b6d59.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Rhcmxpbmd3b29kMjAxMw_size_16_color_FFFFFF_t_70 1]: /images/20211205/26f9762e74c649c59ee54ebe809088d8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Rhcmxpbmd3b29kMjAxMw_size_16_color_FFFFFF_t_70 2]: /images/20211205/76fc895314bf47628da38501e8629a6d.png
[20190718170944186.png]: /images/20211205/2b981a504943463c977ff2e8b257dcb9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Rhcmxpbmd3b29kMjAxMw_size_16_color_FFFFFF_t_70 3]: /images/20211205/dcd838a1887249a888576549d193b67a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Rhcmxpbmd3b29kMjAxMw_size_16_color_FFFFFF_t_70 4]: /images/20211205/167c27b04977422ca5524a466272c5d0.png
[20190718172211797.png]: /images/20211205/1a4beeece21745cebeddb87652cdac48.png