mysql索引优化技巧

前面一篇文章学习了索引的基本知识以及B-TREE索引的适用范围,这一篇文章主要是讨论一些如何高效设计、使用索引的技巧,有些是通用的,有些是针对特定场景的。

1.独立的列

如果在查询条件中列不是独立的,及时在这个列上建立了索引,mysql也无法使用索引进行优化。所谓”独立的列”,指的是索引列不是表达式的一部分,也不能是函数的参数。比如下面这条查询sql,就无法使用actor_id列上的索引

  select * from actor where actor_id+1=5

其实一眼就可以看出where里面的条件等同于actor_id=4,但是mysql无法自动转化这个表达式,所以就会导致无法使用actor_id列上的索引。所以应该始终养成简化where条件的习惯,始终将索引列单独放到比较条件的一边。下面的sql是另一个常见的错误

select * from branch where TO_DAYS(CURRENT_DATE)-TO_DAYS(date_col)<=10

2.前缀索引和索引选择性

有时候可能需要在存储很长字符串的列上建立索引,这会让索引变得大且慢,一种处理方法就是前面提到的为每个列提供一个模拟哈希索引。但也有一些其它的处理方法,比如说只索引前面的部分字符串,这样可以大大减少索引空间,从而提升索引效率。但这样处理会降低索引的选择性,索引选择性指的是,不重复的索引数和数据库总记录数(#T)的比值,范围从1/#T到1之间,所以选择性越高则查询效率越高,因为选择性高的所有可以让mysql在查找时过滤掉更多的行。唯一索引的选择性是1,这是最好的选择性,效率也是最好的。
一般来说某个列前缀的选择性也是足够高的,足够满足查询性能。并且对于text blob和足够长的varchar类型,必须使用前缀索引,因为mysql不允许索引这些列的完整长度。设计前缀索引的诀窍在于要选择足够长的前缀以保证较高的选择性,又不能太长以节省存储空间。前缀要足够长使得前缀索引的选择性接近于索引整个列。
为了决定前缀的长度,需要找到最常见值的列表,然后和常见的前缀列进行比较。大致的思想是找出列值的分布(group by),然后尝试不同长度的前缀观察他们的分布,如果他们的分布和列值的分布差不多,则可以认为该长度的前缀的选择性和整个列的选择性差不多，则可以将这个长度的前缀作为前缀索引。
下面是个具体的例子。city_demo表中只有一个字段：city，存储的是城市的名字，现在表中总共有7680条数据，但是一共只有10个城市的名称，所以说每个城市的名称都会重复很多次。下面是具体分布情况：

为了选出一个合适长度前缀的索引，下面来分别来观察长度为1，2，3前缀的分布。
长度为1的前缀分布：

长度为2的前缀分布：

长度为3的前缀分布：

可以看到长度为3时候前缀的分布已经和整个列的分布完全一致了，所以可以选择长度为3的前缀索引来代替整个列的索引。
也可以通过计算列选择性和不同长度选择性的方法来进行对照。对于上面的例子,先看下列的选择性,然后计算出长度分别为1 2 3的前缀的选择性,可以看到也是长度为3时候前缀的选择性已经和整个列的选择性一样了。所以对这个例子来说,建立一个长度为3的前缀索引就完全可以代表整个列情况。

上面的两种评估方法都是基于平均值的进行的评估,这样通常没有问题,但是如果数据分布很不均匀,也可能出现很大的误差,比如虽然前缀总体的选择性和列的选择性是吻合的,但是实际上某个列出现的频率可能远小于它前缀出现的频率(多个列具有相同的前缀),实际中这种情况可能会出现,所以在选择前缀的时候可能还需要考虑最坏的情况。上面已经确定长度为3的前缀是可以满足我们要求的,下面展示如何建立这样一个长度的前缀索引:

索引建立之后,对于city列的查询就可以走前缀索引了。前缀索引可以使得索引更小、更快,但是mysql无法使用前缀索引进行order by和group by操作,也无法利用前缀索引进行覆盖扫描。

3.多列索引

如果能创建好的多列索引,那么对性能的提升是很大的,但是对于多列索引的创建存在一些常见的误区,比如为每一个列都创建一个索引或按照错误的顺序创建了索引。在多个列上建立单独的索引,大多数情况下并不能提升mysql的查询性能。但mysql 5.0及其以后的版本中引入了一种”索引合并”的策略,一定程度上可以利用表上多个单列索引定位指定的行,更早的mysql版本中只能利用其中一个单独的索引,但是有时候两个索引都不是很好的选择。
比如针对如下的查询:

select film_id,actor_id from film_actor where film_id=1 or actor_id=1

在老的mysql中这个查询会走全表扫描,除非改写成下面这种形式:

select film_id and actor_id from film_actor where film_id=1
union all
select film_id and actor_id from film_actor where actor_id=1 and film_id !=1

在mysql 5.0以上版本查询可以同时使用者两个单列索引所有进行扫描,并将结果进行合并。总的策略来说有三种，OR条件的联合、AND条件的相交、以及组合前两者条件的联合及相交。下面的explain展示了上面查询使用索引的情况(mysql 5.6版本)：

mysql会使用这类技术来优化复杂查询,但并不是说存在索引合并策略只建立单列索引也能满足要求,相反如果出现了索引合并策略,则说明原先的索引
建的很糟糕。

1. 当出现服务器对多个索引做相交操作时(通常有多个AND条件),通常意味着需要一个包含相关列的多列索引。
2. 当服务器需要对多个索引做联合操作的时候(通常都有多个OR条件),通常需要耗费大量的CPU和内存资源在算法和缓存、合并和排序上,特别是在某个索引选择性不高,需要合并扫描返回的大量数据的时候。
3. 更重要的是,优化器不会将这些计算到”查询成本”中去,优化器只关心随机页面的读取,导致走该执行计划还不如走全表扫描,甚至这样的查询还会导致对并发性的影响，还不如将查询改成Union形式。

所以如果在查询explain中看到有索引合并就应该好好检查下查询和表结构,看下是否已经是最优了,可以通过optimizer_switch来关闭索引合并功能,也可以使用ignore index提示让优化器忽略掉某些索引。

选择合适的索引列顺序
我们遇到的最容易引起困惑的问题就是索引列的顺序,正确的顺序其实是依赖于使用该索引的查询,并且也需要考虑如何更好的满足排序和分组的需求。在一个多列B-TREE索引中,索引会按照最左边列进行排序,这样最左边索引列对应的数据其实是完全有序的,这个列的order by,group by和distinct需求都可以通过索引满足。对于如何选择索引列的顺序,有一个经验法则:总是把选择性最高的列放在最左边,这个法则一般情况下是适用的,但有时候在考虑到排序和避免随机IO的情况下就不能遵循它了。如果不考虑排序和分组,那么索引就只是用于优化where查询的效率,这种时候一般使用选择性高的列作为最左边的索引列是具有很高的效率的。但是对于具体的查询语句,有时候因为数据的分布问题,还是可能存在一些问题,所以如果在实际中需要优化一个性能很糟糕的查询,可以结合这个查询具体的数据量来额外分析下。

4.聚簇索引

聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。具体的实现方式依赖于存储引擎的实现,但对于InnoDB的聚簇索引实际上是在同一个结构里保存了B-TREE索引和数据行(InnoDB默认会建立聚簇索引)。当表有聚簇索引的时候,它的数据行实际上存放在索引的叶子页中。术语”聚簇”表示数据行和相邻的键值紧凑的存储在一起,因为无法同时把数据行存放在两个不同的地方,所以一个表只能有一个聚簇索引。
下图展示了聚簇索引中的记录是如何存放的,注意到,叶子页包含了所有数据,而节点页只存储了索引值,在这个例子中索引值是整数。

一些数据库允许选择某个索引列作为聚簇索引,但对于mysql无法指定;InnoDB默认通过主键列来聚合数据,如果没有定义主键列那么就会选择一个唯一的非空索引代替,如果仍没有这样的列,InnoDB会隐式定义一个主键来作为聚簇索引。
聚簇索引可能对性能有索帮助但也有可能带来严重的性能问题,所以需要对其进行考察,特别是当切换底层存储引擎的时候。
下面是聚簇索引的一些重要的优点:

1. 可以把相关的数据保存到一起。例如实现电子邮箱的时候,可以根据用户ID来聚集数据,这样只需要从磁盘读取少量的数据页就可以拿到用户的全部邮件数据。如果没有使用聚簇索引,那么就可能每封邮件都导致一次磁盘IO。
2. 访问数据更快.聚簇索引将索引和数据保存在同一个B-TREE中,因此从聚簇索引中查找数据通常比非聚簇索引要快。
3. 使用覆盖索引扫描的查询,可以直接使用节点中主键的值。

总结起来就是在聚簇索引的情况下如果查询可以走主键索引，那么效率就会很高。如果在设计表和索引的时候能够充分利用上面的优点，可以很大的提升性能。但是聚簇索引也有一些缺点：

1. 聚簇数据最大限度的提高了I/O密集型应用的性能，但如果数据全都放在内存中，则访问顺序就没那么重要了，聚簇索引也就没什么优势了。
2. 插入速度严重依赖于插入顺序，按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。如果不是按照主键顺序加载顺序，加载完成之后最好使用OPTIMIZE TABLE命令重新组织下表。
3. 更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。
4. 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临”页分裂(page split)”的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将改页分裂成两个页面来容纳改行，这是一次页分裂操作。页分裂会导致表占用更多磁盘空间。
5. 聚簇索引可能会导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
6. 二级索引(非聚簇索引)可能比想象中更大，因为在二级索引的叶子节点包含了引用行的主键列。
7. 二级索引访问数据需要两次索引查找，而不是一次。这是因为二级索引中保存的”行指针”实质上不是指向行物理位置的指针，而是行的主键值。这就意味着根据二级索查找行的时候，会先在二级索引叶子节点获取对应的主键值，然后根据这个值去聚簇索引中查找到对应的行。这里就会产生两次B-TREE的查找。不过对于InnoDB，自适应哈希索引能够减少这样的重复工作。

如果使用聚簇索引,则尽量使用单调递增的主键,顺序插入对聚簇索引来说是最优的插入方式。

5.覆盖索引

如果一个索引包含(或者说覆盖)所有需要查询的值,那么我们就称其为”覆盖索引”。覆盖索引可以极大的提高性能,原理很容易理解:只需要扫描索引就可以
拿到数据不需要再回表中查询一次。下面是一些具体的好处:

• 索引条目通常远小于数据行大小，所以如果只需要读取索引，那么mysql就会极大地减少数据访问量。这对缓存的负载非常重要，因为这种情况下响应时间大部分花费在数据拷贝上。覆盖索引对于I/O密集型的应用也有帮助，因为索引比数据更小，更容易全部放入到内存中。
• 因为索引是按照列值顺序存储的，所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少得多。
• 一些存储引擎如MyISAM在内存中只缓存索引，数据依赖于操作系统来缓存，因此访问数据需要一次系统调用。这可能会导致严重的性能问题，尤其是那些系统调用占了数据访问中的最大开销的场景。
• 由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级索引能够覆盖查询，则可以避免对主键索引的二次查询。

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值,而哈希索引、空间索引、全文索引都不存储索引列的值,所以Mybatis只能使用B-TREE索引做覆盖索引。另外不同存储引擎实现覆盖索引的方式不同,也有一些存储引擎不支持覆盖索引。
当发起一个被索引覆盖的查询(也称为索引覆盖查询)时,Explain的Extra列可以看到”Using index”的提示信息。例如表inventory有一个两列的索引：(stroe_id,film_id)如果只查询这两列的值,那么就会走覆盖索引的查询逻辑。

但是覆盖索引也存在很多陷阱可能会导致无法优化,mysql查询优化器会在查询执行器判断是否有一个索引可以进行覆盖。如果索引覆盖了where查询到的字段但不是整个查询设计的字段,则也无法使用覆盖索引.比如下面的查询:

另外有一点需要注意的是InnoDB的二级索引上都是默认有主键索引的值,所以默认可以覆盖包含主键索引和二级索引联合查询的情况。

6.使用索引扫描来排序

mysql有两种方式可以用来生成有序的结果:通过排序操作或者是按索引顺序扫描。如果explain出来的type列的值为index,则说明mysql使用了索引扫描来做排序。
扫描索引本身是很快的,因为只需要从一条索引记录移动到紧邻的另一条索引记录。但是如果索引中不包含所有需要查询的列,则扫描一条所有就得回表查询一次,这种查询一般都是随机IO,速度会比较慢.这种情况下按照索引顺序读取数据的速度一般要比顺序扫描全表读取数据要慢,因为顺序读取全表的时候是顺序IO。
mysql中的索引是可以同时满足排序和数据查找需求的,因此如果有可能设计索引的时候应该尽量同时满足这两个需求。只有当索引的列顺序和order by子句中列的顺序完全一致,并且order by子句中所有列都是升序(或者降序)的情况下才能使用索引来对结果进行排序.如果查询需要关联多个表,则只有当order by子句中的列都是第一个表中列的时候,才能使用索引进行排序.order by子句中对列查询的限制和where子句一样,都必须满足左前缀的需求,否则无法利用索引。