动态ip：MySQL优化—创建高性能的索引策略

　　正确的创建和使用索引是实现高性能查询的基础。前面已经介绍了各种类型的索引及其对应的优缺点。现在我们一起来看看如果真正的发挥这些索引的优势。

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

　　一 独立的列

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

　　例：select id from test_table where id + 1 = 3;

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

　　下面是另一个常见错误：select id from test_table where to_days(current_date) - to_days(date_col) <= 10;

　　二 前缀索引和索引选择性

　　有时候需要索引很长的字符列，这会让索引变大且慢。一个策略是前面提到过的模拟哈希索引。但是有时候这样做还不够。

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

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

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

　　为了决定前缀的合适长度，需要找到最常见的值的列表，然后和最常见的前缀列表进行比较。我们可以使用下面sql来进行测试

　　select count(*) as num,left(address,3) as name from test_user group by name order by cnt limit 10;

　　然后慢慢的增加前缀数量直到所得的num数量差距之间不是很大，并且数量在一个可接受的范围。

　　计算合适的前缀长度的另一个办法就是计算完整列的选择性，并是前缀的选择性接近与完整列的选择性。

　　例：select count(distinct address)/count(*) from test_table;

　　通常来说这个例子中如果前缀的选择性能够接近0.031，基本上就可以使用了(例外情况除外)。可以在一个查询中针对不同前缀长度进行计算，这对于大表非常有用。下面给出了如何在同一个查询中计算不同前缀长度的选择性

　　select count(distinct left(address,3))/count(*) ,

　　count(distinct left(address,4))/count(*) ,

　　count(distinct left(address,5))/count(*) ,

　　count(distinct left(address,6))/count(*) ,

　　count(distinct left(address,7))/count(*) ,

　　from test_table;

　　当然只看平均选择性是不够的，也有例外情况，需要考虑到最坏的情况下的选择性。平均选择会让你以为一个较短的长度就足够了，但如果数据分布不均与，可能就会碰到个坑。比如你查找一个长度limit 10 的时候看到第一个和最后一个的数量相差超过1倍以上，这就是一个很明显的不均匀情况。那么这些值的选择性就比平均选择性要低的多。

　　如何创建前缀索引：

　　alter table test_table add key(address(7));

　　前缀索引是一种能使索引更小、更宽的有效办法，但也有其缺点：

　　MySQL无法使用前缀索引做order by 和 group by，也无法使用前缀索引做覆盖扫描。

　　常见的场景就是针对一个很长的16进制的唯一ID使用前缀索引。例如使用基于MySQL的应用在存储网站的session时，需要在一个很长的16进制字符串上创建索引。此时如果采用长度为8的前缀索引通常可以显著的提升性能，并且这种发发对上层应用完全透明。

　　有时候后缀索引(suffix index)也有用途，例如查询某个域名的所有电子邮件地址。MySQL原生并不支持反向索引，但是可以把字符串反转后存储，并给予此简历前缀索引。可以通过触发器来维护这种索引。可以参考前面文章中的创建自定义哈希索引部分的内容。——芝麻代理

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31561243/viewspace-2636428/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31561243/viewspace-2636428/