高性能MYSQL（学习笔记）-索引篇2

 高性能的索引策略

高效的选择和使用索引有很多种方式，其中有些是针对特殊案例的优化，有些则是针对行为的优化，接下来介绍如何高效的使用索引。

独立的列

我们通常会看到一些查询不当的索引，如果查询的列不是独立的，则MYSQL就不会使用索引。独立的列指的是所以不能是表达式的一部分，也不能是函数的参数

例如：wher actor_id+1=5 to_days(current_date)-to_days(date_col)<=10;都无法被索引到。

前缀索引和索引选择性

当需要索引很长的字符串时候，一个策略是模拟哈希索引；其次可以索引开始的部分字符，这样可以减少索引的空间，从而提高索引效率。但是同时也会降低索引的选择性。

索引选择性：不重复的索引值和数据表的记录总数（#T）比值，范围从1/#T到1之间，数值越高则查询效率越高，选择性高的索引可以让MYSQL在查询时过滤掉更多的行。

对于BLOB/TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，选择足够长的前缀保证较高的选择性，同时又不能太长，前缀的基数应该接近于完整列基数，计算方式可以参考：

Selectcount(distinct city)/count(*) from city_table，计算出完整的选择性

得出的小数值，通过以下方式去尝试，看看哪个值更接近：

Selectcount(distinct left(city,3))/count(*) as sel3,count(distinctleft(city,4))/count(*) sel4…from city_table.取得最接近的就是前缀长度。

如何创建前缀索引

Alter tablecity_table add key(city(7));

前缀索引的优点在于能使索引更小、更快的有效方法，但是另外一方面是：无法使用前缀索引做order by 和 group by操作，无法使用前缀索引做覆盖扫描。

后缀索引也有用途，例如查找某个域名的所有电子邮件地址，可以字符串翻转后存储！

多列索引

1、  当出现服务器对多个索引做相交操作时候（通常有多个AND条件），意味着需要建立一个包含所有相关的多列索引，而不是多个独立的单列索引index（a/b/c），而不是inde(a)/index(b)/index(c)

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

选择合适的索引列顺序

  本内容适用于B-Tree索引，将选择性最高的列放到索引的最前列，当不需要考虑排序和分组时候，这个是最好的选择。这个时候索引的作用只是用于优化where条件的查找。

经验法则：

使用类似的查询：selectcount(distinct staff_id)/count(*) as staff_id_selectivity,

    Count(distinct customer_id)/count(*) as customer_id_selectivity,

     Count(*) from payment。

结果为：staff_id_selectivity:0.0001,customer_id_selectivity：0.0373、count（*）：16049，customer_id 选择性更高一些，所以选择其作为索隐列的第一列。