MySQL性能优化之如何高效正确的使用索引

实践是检验真理的唯一途径,本篇只是站在索引使用的全局来定位的,你只需要通读全篇并结合具体的例子,或回忆以往使用过的地方,对整体有个全面认识,并理解索引是如何工作的,就可以了。在后续使用索引,或者优化索引时,可以从这些方面出发,进一步来加深对索引正确高效的使用。

一、索引失效

索引失效,是一个老生常谈的话题了。只要提到数据库优化、使用索引,都能一口气说出一大堆索引失效的场景,什么不能用、什么不该用这类的话,在此,我就不再一一罗列啰嗦了。

索引失效,是指表中有字段创建了索引,由于sql语句书写不当导致索引失效的情况。

在sql语句中,将索引列作为表达式的一部分、参与函数/数学等运算,将会导致索引失效。

例如,下面这个查询无法使用age列的索引:

select id,name,age from t_user where age + 1 = 7;

很容易看出where中的表达式其实等价于age=8,但是MySQL无法自动解析这个表达式,这完全是用户行为。

(在上一篇文章中,我们知道MySQL先在索引上按值进行查找,然后返回索引值对应的数据行,一旦对索引列进行运算,则将无法正确的找到对应的数据行,从而改为全表逐行扫描查询对比)

二、前缀索引和索引选择性

有时候将内容很长的列作为索引列,这将会让索引变得很大而且很慢。如果非要在该列添加索引,解决策略就是上一篇文章提到过的模拟哈希索引。

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

索引的选择性是指,不重复的索引值(也称为基数)和表数据的记录总数T的比值,范围从1/T到1之间。索引的选择性越高,则查询效率越高,因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。

唯一索引的选择性为1,这是最好的索引选择性,性能也是最好的。

对于BLOB、TEXT或很大的VARCHAR类型的列,作为查询条件时(原则上是要避免这样的操作,但有时总是情非得已),该列必须使用前缀索引,这样来提高查询性能。因为MySQL是不允许索引这些列的完整长度的。

三、多列索引

多列索引,是指为每个列创立独立的索引。

在SQL优化时,有人会采取“把where条件里面的列都建上索引”,希望能够对查询性能有所优化。但实际上这样的优化是非常错误的,这样一来最好的情况下也只能是“一星”索引,其性能比起真正最优的索引可能差几个数据级。有时如果无法设计一个“三星”索引,那么不如忽略掉where子句,集中精力优化索引列的顺序,或者创建一个全覆盖索引。

三星索引:在Lahdenmaki和Leach编写的Relational Database Index Design and the Optimizers一书中,提到如何评价一个索引是否适合某个查询的“三星系统”:索引将相关的记录放到一起则获得“一星”;如果索引中的数据顺序和查找中的排序顺序一致则获得“二星”;如果索引中的列包含了查询中需要的全部列则获得“三星”。

在多个列上建立独立的单列索引,大部分情况下并不能提高MySQL的查询性能。这也是将其错误的做法。

MySQL5.0及之后版本引入了索引合并策略,一定程度上可以使用表上的多个单列索引来定位指定的行。更早的MySQL只能使用其中某一个单列索引,然而这个情况下没有哪一个独立的单列索引是非常有效的。

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

1)当出现对多个索引做相交操作时(通常由多个AND条件),通常意味着需要一个包含所有相关列的多列索引,而不是多个独立的单列索引。

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

3)优化器不会把这些计算到“查询成本”中,优化器只关心随机页面读取。这会使得查询的成本被“低估”,导致该执行计划还不如直接走全表扫描。这样做不但会消耗更多的CPU和内存资源,还可能会影响查询的并发性,但如果是单独运行这样的查询,则往往会忽略对并发性的影响。

MySQL性能优化之如何高效正确的使用索引

扫一扫手机访问