MySQL Query优化:索引优化

Published on 2016 - 06 - 06

索引优化,可以说是数据库相关优化,尤其是Query优化中最常用的优化手段之一。很多人大部分时候都只是大概了解索引的用途,知道索引能够让Query执行得更快,但并不知道为什么会更快。尤其是索引的实现原理、存储方式,以及不同索引之间的区别等就更不清楚了。正因为索引对Query的性能影响很大,所以我们更应该深入理解MySQL中索引的基本实现,以及不同索引之间的区别,这样才能分析出如何设计最优的索引,最大幅度地提升Query的执行效率。

常用索引介绍

在MySQL中,主要有4种类型的索引,分别为:B-Tree索引、Hash索引、Fulltext索引和R-Tree索引,下面针对这4种索引的基本实现方式及存储结构做一个大概的分析。

B-Tree索引

B-Tree索引是MySQL数据库中使用最为频繁的索引类型,除了Archive存储引擎之外的其他所有的存储引擎都支持B-Tree索引。不仅在MySQL中是如此,在其他的很多数据库管理系统中B-Tree索引也同样是作为最主要的索引类型的,这主要是因为B-Tree索引的存储结构在数据库的数据检索中有着非常优异的表现。

一般来说,MySQL中的B-Tree索引的物理文件大多是以Balance Tree的结构来存储的,也就是所有实际需要的数据都存放于Tree的Leaf Node,而且到任何一个Leaf Node的最短路径的长度都是完全相同的,所以把它称之为B-Tree索引。不过,可能各种数据库(或MySQL的各种存储引擎)在存放自己的B-Tree索引的时候会对存储结构稍作改造。如InnoDB存储引擎的B-Tree索引使用的存储结构实际上是B+Tree,在B-Tree数据结构的基础上做了很小的改造,在每一个Leaf Node上面除了存放索引键的相关信息之外,还存储了指向与该Leaf Node相邻的后一个Leaf Node的指针信息,这主要是为了加快检索多个相邻Leaf Node的效率。

在InnoDB存储引擎中,存在两种不同形式的索引,一种是Cluster形式的主键索引(Primary Key),另外一种则是和其他存储引擎(如MyISAM存储引擎)存放形式基本相同的普通B-Tree索引,这种索引在InnoDB存储引擎中被称为Secondary Index。下面通过图1针对这两种索引的存放形式做一个比较。

图1左侧为Clustered形式存放的Primary Key,右侧则为普通的B-Tree索引。两种索引在Root Node和Branch Nodes方面完全一样。但它们会在Leaf Nodes方面出现差异。在Primary Key中,Leaf Nodes存放的是表的实际数据,不仅仅包括主键字段的数据,还包括其他字段的数据,整个数据以主键值有序的排列。而Secondary Index则和其他普通的B-Tree索引没有太大的差异,只是在Leaf Nodes除了存放索引键的相关信息外,还存放了InnoDB的主键值。

所以,在InnoDB中如果通过主键来访问数据效率是非常高的,而如果是通过Secondary Index来访问数据的话,InnoDB首先通过Secondary Index的相关信息及相应的索引键检索到Leaf Node,再通过Leaf Node中存放的主键值和主键索引来获取相应的数据行。

MyISAM存储引擎的主键索引和非主键索引差别很小,只不过主键索引的索引键是一个唯一且非空的键。而且MyISAM存储引擎的索引和InnoDB的Secondary Index的存储结构基本相同,主要的区别只是MyISAM存储引擎在Leaf Nodes上除了存放索引键信息之外,还存放能直接定位到MyISAM数据文件中相应数据行的信息(如Row Number),但并不会存放主键的键值信息。

Hash索引

Hash索引在MySQL中使用的并不是很多,目前主要是Memory和NDB Cluster存储引擎使用。所谓Hash索引,实际上就是通过一定的Hash算法,将须要索引的键值进行Hash运算,然后将得到的Hash值存入一个Hash表中。每次须要检索的时候,都会将检索条件进行相同算法的Hash运算,再和Hash表中的Hash值进行比较,并得出相应的信息。

在Memory存储引擎中,MySQL还支持非唯一的Hash索引。可能很多人会比较惊讶,如果是非唯一的Hash索引,那相同的值该如何处理呢?在Memory存储引擎的Hash索引中,如果遇到非唯一值,存储引擎会将它们链接到同一个Hash键值下,并以一个链表的形式存在,然后在取得实际键值时过滤不符合的键。

由于Hash索引结构的特殊性,其检索效率非常高,索引的检索可以一次定位,不像B-Tree索引需要从根节点到枝节点,最后才能访问到页节点这样多次的IO访问,所以Hash索引的查询效率要远高于B-Tree索引。

可能很多人又有疑问了,既然Hash索引的效率要比B-Tree高很多,为什么大家不都用Hash索引而还要使用B-Tree索引呢?任何事物都是有两面性的,Hash索引也一样,虽然Hash索引效率高,但是Hash索引本身由于其特殊性也带来了很多限制和弊端,主要有以下这些。

  • Hash索引仅仅能满足“=”,“IN”和“<=>”查询,不能使用范围查询。

由于Hash索引比较的是进行Hash运算之后的Hash值,所以它只能用于等值的过滤,不能用于基于范围的过滤,因为经过相应的Hash算法处理之后的Hash值的大小关系,并不能保证和Hash运算前完全一样。

  • Hash索引无法被用来避免数据的排序操作。

由于Hash索引中存放的是经过Hash计算之后的Hash值,而且Hash值的大小关系并不一定和Hash运算前的键值完全一样,所以数据库无法利用索引的数据来避免任何排序运算;

  • Hash索引不能利用部分索引键查询。

对于组合索引,Hash索引在计算Hash值的时候是组合索引键合并后再一起计算Hash值,而不是单独计算Hash值,所以通过组合索引的前面一个或几个索引键进行查询的时候,Hash索引也无法被利用。

  • Hash索引在任何时候都不能避免表扫描。

前面已经知道,Hash索引是将索引键通过Hash运算之后,将Hash运算结果的Hash值和所对应的行指针信息存放于一个Hash表中,由于不同索引键存在相同Hash值,所以即使取满足某个Hash键值的数据的记录条数,也无法从Hash索引中直接完成查询,还是要通过访问表中的实际数据进行相应的比较,并得到相应的结果。

  • Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

对于选择性比较低的索引键,如果创建Hash索引,那么将会存在大量记录指针信息存于同一个Hash值相关联。这样要定位某一条记录时就会非常麻烦,会浪费多次表数据的访问,而造成整体性能低下。

Fulltext索引

Full-text索引也就是全文索引,目前在MySQL中仅有MyISAM存储引擎支持它,但并不是所有的数据类型都支持。目前,仅有CHAR、VARCHAR和TEXT这三种数据类型的列可以建Full-text索引。

一般来说,Fulltext索引主要用来替代效率低下的LIKE'%***%'操作。实际上,Full-text索引并不是只能简单地替代传统的全模糊LIKE操作,它能通过多字段组合的Full-text索引一次全模糊匹配多个字段。

Full-text索引和普通的B-Tree索引实现区别较大,虽然它同样是以B-Tree形式来存放索引数据的,但是它并不是通过字段内容的完整匹配,而是通过特定的算法,将字段数据进行分割后再进行的索引。一般来说MySQL系统会按照最小4个字节来分隔。在整个Full-text索引中,存储内容被分为两部分,一部分是分隔前的索引字符串数据集合,另一部分是分隔后的词(或者词组)索引信息。所以,Full-text索引中,真正在B-Tree索引结构的叶节点中的并不是表中的原始数据,而是分词之后的索引数据。在B-Tree索引的节点信息中,存放了各个分隔后的词信息,以及指向包含该词的分隔前字符串信息在索引数据集合中的位置信息。

Full-text索引不仅能实现模糊匹配查找,还能实现基于自然语言的匹配度查找。当然,这个匹配度到底有多准确就需要读者自行验证了。Full-text通过一些特定的语法信息,针对自然语言做了各种相应规则的匹配,最后给出了非负的匹配值。

此外,有一点须要大家注意,MySQL目前的Full-text索引在中文支持方面还不太好,须要借助第三方的补丁或插件来完成,且Full-text的创建所消耗的资源也比较大,所以在应用于实际生产环境之前还是尽量做好评估。

R-Tree索引

R-Tree索引可能是在其他数据库中很少见的一种索引类型,主要用来解决空间数据检索的问题。

在MySQL中,支持一种用来存放空间信息的数据类型GEOMETRY,且基于OpenGIS规范。在MySQL 5.0.16之前的版本中,仅MyISAM存储引擎支持该数据类型,但是从MySQL 5.0.16版本开始,BDB、InnoDB、NDBCluster和Archive存储引擎也开始支持该数据类型。当然,虽然多种存储引擎都开始支持GEOMETRY数据类型,但是仅仅之后的MyISAM存储引擎支持R-Tree索引。

在MySQL中采用了具有二次分裂特性的R-Tree来索引空间数据信息,然后通过几何对象(MRB)信息来创建索引。

虽然只有MyISAM存储引擎支持空间索引(R-Tree Index),但是如果是精确的等值匹配,创建在空间数据上面的B-Tree索引同样可以起到优化检索的效果,空间索引的主要优势在于使用范围查找的时候,可以利用R-Tree索引,而B-Tree索引就无能为力了。

索引的利弊与如何判定,是否需要索引

相信读者都知道索引能够极大地提高数据检索的效率,让Query执行得更快,但是可能并不是每一位朋友都清楚索引在极大提高检索效率的同时,也给数据库带来了一些负面的影响。下面就分别对MySQL中索引的利与弊做一个简单的分析。

索引的好处

在数据库中表的某个字段创建索引,所带来的最大益处就是将该字段作为检索条件时可以极大地提高检索效率,加快检索时间,降低检索过程中须要读取的数据量。但是索引带来的收益只是提高表数据的检索效率吗?当然不是,索引还有一个非常重要的用途,那就是降低数据的排序成本。

我们知道,每个索引中的数据都是按照索引键键值进行排序后存放的,所以,当Query语句中包含排序分组操作时,如果排序字段和索引键字段刚好一致,MySQL Query Optimizer就会告诉mysqld在取得数据后不用排序了,因为根据索引取得的数据已经满足客户的排序要求。

那如果是分组操作呢?分组操作没办法直接利用索引完成。但是分组操作是须要先进行排序然后分组的,所以当Query语句中包含分组操作,而且分组字段也刚好和索引键字段一致,那么mysqld同样可以利用索引已经排好序的这个特性,省略掉分组中的排序操作。

排序分组操作主要消耗的是内存和CPU资源,如果能够在进行排序分组操作中利用好索引,将会极大地降低CPU资源的消耗。

索引的弊端

索引的益处已经清楚了,但是我们不能只看到这些益处,并认为索引是解决Query优化的圣经,只要发现Query运行不够快就将WHERE子句中的条件全部放在索引中。

确实,索引能够极大地提高数据检索效率,也能够改善排序分组操作的性能,但有不能忽略的一个问题就是索引是完全独立于基础数据之外的一部分数据。假设在Table ta中的Column ca创建了索引idx_ta_ca,那么任何更新Column ca的操作,MySQL在更新表中Column ca的同时,都须要更新Column ca的索引数据,调整因为更新带来键值变化的索引信息。而如果没有对Column ca进行索引,MySQL要做的仅仅是更新表中Column ca的信息。这样,最明显的资源消耗就是增加了更新所带来的IO量和调整索引所致的计算量。此外,Column ca的索引idx_ta_ca须要占用存储空间,而且随着Table ta数据量的增加,idx_ta_ca所占用的空间也会不断增加,所以索引还会带来存储空间资源消耗的增加。

如何判定是否须要创建索引

在了解了索引的利与弊之后,那我们到底该如何来判断某个索引是否应该创建呢?

实际上,并没有一个非常明确的定律可以清晰地定义什么字段应该创建索引,什么字段不该创建索引。因为应用场景实在是太复杂,存在太多的差异。当然,还是仍然能够找到几点基本的判定策略来帮助分析的。

较频繁的作为查询条件的字段应该创建索引

提高数据查询检索的效率最有效的办法就是减少须要访问的数据量,从上面索引的益处中我们知道,索引正是减少通过索引键字段作为查询条件的Query的IO量之最有效手段。所以一般来说应该为较为频繁的查询条件字段创建索引。

唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件

唯一性太差的字段主要是指哪些呢?如状态字段、类型字段等这些字段中存放的数据可能总共就是那么几个或几十个值重复使用,每个值都会存在于成千上万或更多的记录中。对于这类字段,完全没有必要创建单独的索引。因为即使创建了索引,MySQL Query Optimizer大多数时候也不会去选择使用,如果什么时候MySQL Query Optimizer选择了这种索引,那么非常遗憾地告诉你,这可能会带来极大的性能问题。由于索引字段中每个值都含有大量的记录,那么存储引擎在根据索引访问数据的时候会带来大量的随机IO,甚至有些时候还会出现大量的重复IO。

这主要是由于数据基于索引扫描的特点引起的。当我们通过索引访问表中数据时,MySQL会按照索引键的键值顺序来依序访问。一般来说,每个数据页中大都会存放多条记录,但是这些记录可能大多数都不会和你所使用的索引键的键值顺序一致。

假如有以下场景,我们通过索引查找键值为A和B的某些数据。在通过A键值找到第一条满足要求的记录后,会读取这条记录所在的X数据页,然后继续往下查找索引,发现A键值所对应的另外一条记录也满足要求,但是这条记录不在X数据页上,而在Y数据页上,这时候存储引擎就会丢弃X数据页,而读取Y数据页。如此继续一直到查找完A键值所对应的所有记录。然后轮到B键值了,这时发现正在查找的记录又在X数据页上,可之前读取的X数据页已经被丢弃了,只能再次读取X数据页。这时候,实际上已经重复读取X数据页两次了。在继续往后的查找中,可能还会出现一次又一次的重复读取,这无疑给存储引擎极大地增加了IO访问量。

不仅如此,如果一个键值对应了太多的数据记录,也就是说通过该键值会返回占整个表比例很大的记录时,由于根据索引扫描产生的都是随机IO,其效率比进行全表扫描的顺序IO效率低很多,即使不会出现重复IO的读取,同样会造成整体IO性能的下降。

很多比较有经验的Query调优专家经常说,当一条Query返回的数据超过了全表的15%时,就不应该再使用索引扫描来完成这个Query了。对于“15%”这个数字我们并不能判定是否很准确,但是至少侧面证明了唯一性太差的字段并不适合创建索引。

更新非常频繁的字段不适合创建索引

上面在索引的弊端中已经分析过了,索引中的字段被更新的时候,不仅要更新表中的数据,还要更新索引数据,以确保索引信息是准确的。这个问题致使IO访问量较大增加,不仅仅影响了更新Query的响应时间,还影响了整个存储系统的资源消耗,加大了整个存储系统的负载。

当然,并不是存在更新的字段就不适合创建索引,从判定策略的用语上也可以看出,是“非常频繁”的字段。到底什么样的更新频率应该算是“非常频繁”呢?每秒?每分钟?还是每小时呢?说实话,还真难定义。很多时候是通过比较同一时间段内被更新的次数和利用该字段作为条件的查询次数来判断的,如果通过该字段的查询并不是很多,可能几个小时或是更长才会执行一次,更新反而比查询更频繁,那这样的字段肯定不适合创建索引。反之,如果我们通过该字段的查询比较频繁,但更新并不是特别多,比如查询几十次或更多才可能会产生一次更新,那我个人觉得更新所带来的附加成本也是可以接受的。

单键索引还是组合索引

在大概了解了MySQL各种类型的索引,以及索引本身的利弊与判断一个字段是否须要创建索引之后,就要着手创建索引来优化Query了。在很多时候,WHERE子句中的过滤条件并不只是针对于单一的某个字段,经常会有多个字段一起作为查询过滤条件存在于WHERE子句中。在这种时候,就必须要判断是该仅仅为过滤性最好的字段建立索引,还是该在所有字段(过滤条件中的)上建立一个组合索引。

对于这种问题,很难有一个绝对的定论,须要从多方面来分析考虑,平衡两种方案各自的优劣,然后选择一种最佳的方案。因为从上一节中已了解到索引在提高某些查询的性能同时,也会让某些更新的效率下降。而组合索引中因为有多个字段存在,理论上被更新的可能性肯定比单键索引要大很多,这样带来的附加成本也就比单键索引要高。但是,当WHERE子句中的查询条件含有多个字段时,通过这多个字段共同组成的组合索引的查询效率肯定比只用过滤条件中的某一个字段创建的索引要高。因为通过单键索引过滤的数据并不完整,和组合索引相比,存储引擎须要访问更多的记录数,自然就会访问更多的数据量,也就是说需要更高的IO成本。

可能有朋友会说,那可以创建多个单键索引啊。确实可以将WHERE子句中的每一个字段都创建一个单键索引。但是这样真的有效吗?在这样的情况下,MySQL Query Optimizer大多数时候都只会选择其中的一个索引,然后放弃其他的索引。即使他选择了同时利用两个或更多的索引通过INDEX_MERGE来优化查询,所收到的效果可能并不会比选择其中某一个单键索引更高效。因为如果选择通过INDEX_MERGE来优化查询,就须要访问多个索引,同时还要将几个索引进行merge操作,这带来的成本可能反而会比选择其中一个最有效的索引更高。

在一般的应用场景中,只要不是其中某个过滤字段在大多数场景下能过滤90%以上的数据,而其他的过滤字段会频繁的更新,一般更倾向于创建组合索引,尤其是在并发量较高的场景下。因为当并发量较高的时候,即使只为每个Query节省了很少的IO消耗,但因为执行量非常大,所节省的资源总量仍然是非常可观的。

当然,创建组合索引并不是说就须要将查询条件中的所有字段都放在一个索引中,还应该尽量让一个索引被多个Query语句利用,尽量减少同一个表上的索引数量,减少因为数据更新带来的索引更新成本,同时还可以减少因为索引所消耗的存储空间。

此外,MySQL还提供了另外一个优化索引的功能,那就是前缀索引。在MySQL中,可以仅仅使用某个字段的前面部分内容做为索引键索引该字段,以达到减小索引占用的存储空间和提高索引访问效率的目的。当然,前缀索引的功能仅仅适用于字段前缀随机重复性很小的字段。如果须要索引的字段前缀内容有较多的重复,索引的过滤性自然也会随之降低,通过索引所访问的数据量就会增加,这时候前缀索引虽然能够减少存储空间消耗,但是可能会造成Query访问效率的极大降低,得不偿失。

Query的索引选择

在有些场景下,Query由于存在多个过滤条件,而这多个过滤条件可能会存在于两个或更多的索引中。这时,MySQL Query Optimizer一般都能根据系统的统计信息选择出一个针对该Query最优的索引完成查询,但是在有些情况下,可能是由于系统统计的信息不够准确完整,也可能是MySQL Query Optimizer自身功能的缺陷,会造成他并没有选择一个真正最优的索引而选择了其他查询效率较低的索引。这时,我们就不得不人为干预,在Query中增加Hint,提示MySQL Query Optimizer该使用哪个索引而不该使用哪个索引,或者通过调整查询条件来达到相同的目的。

在group_message上增加索引,如示例代码10所示:

从索引的Sub_part中,可以看到subject字段是取前16个字符的前缀作为索引键的。下面假设我们知道某个用户的user_id、nick_name和subject字段的部分前缀信息(weiurazs),希望通过这些条件查询出所有满足存在于group_message中的信息。存在三个索引可以被利用:idx_group_message_author、idx_group_message_uid和group_message_author_ subject,而且每个user_id实际上都是分别和一个author一一对应的。所以实际上,无论是使用user_id和author(nick_name)中的某一个来作为条件或将两个都作为条件,所得到的数据是完全一样的。当然,还需要subject LIKE 'weiurazs%'这个条件来过滤与subject相关的信息。

根据三个索引的组成和查询条件,group_message_author_subject索引可以达到最高的检索效率,因为只有它索引了与subject相关的信息,而subject是查询必须包含的过滤条件。下面看看分别使用user_id、author和两者共同被使用时的执行计划,如示例代码12所示:

很明显,这不是我们所期望的执行计划,然而并不能责怪MySQL,因为没有使用author来进行过滤,所以Optimizer当然不会选择group_message_author_subject这个索引,这是我们自己的错。稍作调整,再如示例代码13所示:

这次改用了author作为查询条件,可MySQL Query Optimizer仍然没有选择group_message_author_subject这个索引,即使通过analyze分析也是同样的结果,如示例代码14所示:

同时使用user_id和author时,MySQL Query Optimizer又再次选择了idx_group_message_ uid这个索引,仍然不是我们期望的结果,如示例代码15所示:

最后,不得不利用MySQL提供的在优化Query时所使用的高级功能,通过显式告诉MySQL Query Optimizer我们要使用哪个索引的Hint功能。强制MySQL使用group_message_author_subject这个索引来完成查询,这才达到了需要的效果。

或许有些读者会想,会不会是因为group_message_author_subject这个索引本身就不是一个最优的选择呢?大家请看示例代码16中通过mysqlslap来执行各条Query的实际测试结果:

可以清晰地看出,通过添加Hint之后选择group_message_author_subject这个索引的Query确实比其他三条要快很多。

通过这个示例,可以看出在优化Query的时候,选择合适的索引是非常重要的,同时也证明了MySQL Query Optimizer并不是在任何时候都能选择出最佳的执行计划,有时,不得不通过人为的干预来让MySQL Query Optimizer改变它的“想法”,按照我们的思路走。

当然,这个示例仅说明了选择合适索引的重要性,并且不能在任何时候都完全相信MySQL Query Optimizer,却没有说明到底该如何来选择一个更合适的索引。下面是我对于选择合适索引的几点建议,虽然不一定在任何场景下都合适,但在大多数场景下还是适用的。

  1. 对于单键索引,尽量选择针对当前Query过滤性更好的索引;
  2. 在选择组合索引时,当前Query中过滤性最好的字段在索引字段顺序中排列越靠前越好;
  3. 在选择组合索引时,尽量选择可以包含当前Query的WHERE子句中更多字段的索引;
  4. 尽可能通过分析统计信息和调整Query的写法来达到选择合适索引的目的,减少通过使用Hint人为控制索引的选择,因为这会使后期的维护成本增加,同时增加维护所带来的潜在风险。

MySQL中索引的限制

在使用索引的同时,还应该了解MySQL中索引存在的限制,以便在索引应用中尽可能地避开限制所带来的问题。下面列出了目前MySQL中与索引使用相关的限制。

  1. MyISAM存储引擎索引键长度的总和不能超过1000字节;
  2. BLOB和TEXT类型的列只能创建前缀索引;
  3. MySQL目前不支持函数索引;
  4. 使用不等于(!=或者<>)的时候,MySQL无法使用索引;
  5. 过滤字段使用了函数运算(如abs(column))后,MySQL无法使用索引;
  6. Join语句中Join条件字段类型不一致的时候,MySQL无法使用索引;
  7. 使用LIKE操作的时候如果条件以通配符开始(如'%abc...')时,MySQL无法使用索引;
  8. 使用非等值查询的时候,MySQL无法使用Hash索引。

在使用索引的时候,须要注意上面的这些限制,尤其是要注意无法使用索引的情况,因为这很容易造成极大的性能隐患。

参考文档