注册 登录
  • 欢迎访问"运维那点事",推荐使用Google浏览器访问,可以扫码关注本站的"微信公众号"。
  • 如果您觉得本站对你有帮助,那么可以扫码捐助以帮助本站更好地发展。

MySQL InnoDB索引选择性与统计信息

MySQL 彭东稳 3019次浏览 已收录 5个评论

一、什么是索引选择性?

既然索引可以加快查询速度,那么是不是只要是查询语句需要,就建上索引?答案是否定的。因为索引虽然加快了查询速度,但索引也是有代价的:索引文件本身要消耗存储空间,同时索引会加重插入、删除和修改记录时的负担,另外,MySQL在运行时也要消耗资源维护索引,因此索引并不是越多越好。一般两种情况下不建议建索引。

第一种情况是表记录比较少,例如一两千条甚至只有几百条记录的表,没必要建索引,让查询做全表扫描就好了。至于多少条记录才算多,这个个人有个人的看法,我个人的经验是以2000作为分界线,记录数不超过2000可以考虑不建索引,超过2000条可以酌情考虑索引。

另一种不建议建索引的情况是索引的选择性较低。所谓索引的选择性(Selectivity),是指不重复的索引值(也叫基数,Cardinality)与表记录数(#T)的比值:

Index Selectivity = Cardinality / #T

显然选择性的取值范围为(0, 1),选择性越高的索引价值越大,这是由B+Tree的性质决定的。

什么是Cardinality(基数)?

索引可以用来提升数据的查询速度,但是并不是在所有的查询条中出现的列都需要添加索引,对于什么时候添加B+树索引,一般的经验是,在访问表中很少一部分数据时使用B+数索引才有意义。对于性别字段、地区字段、类型字段,它们可取值的范围很小,称为低选择性。如:

select * from student where sex=’M’

按性别进行查询时,可取值的范围一般只有’M’,’F’。因此上述SQL语句得到的结果可能是该表50%的数据,这时添加B+树索引是完全没有必要的。相反,如果某个字段的取值范围很广,几乎没有重复,即属于高选择性,则此时使用B+树索引是最合适的。例如,对于姓名字段,基本上在一个应用中不允许重名的出现。

索引的选择性是指索引列唯一值的数目与表中记录数的比例,如果一个表中有2000条记录,表索引列有1980个不同的值,那么这个索引的选择性就是“1980/2000=0.99”。一个索引的选择性越接近于1,这个索引的效率就越高。如果是使用基于Cost的最优化,优化器不应该使用选择性不好的索引。如果是使用基于Rule的最优化,优化器在确定执行路径时不会考虑索引的选择性,当然除非是唯一性索引(唯一性索引属于高选择性),并且不得不手工优化查询以避免使用非选择性的索引。

怎么查看索引是否属于高选择性呢?可以通过SHOW INDEX结果中的Cardinality列来观察,或者通过information_schema.STATISTICS表查看。

Cardinality值非常关键,表示索引中不重复记录数量的预估值。同时需要注意的是,Cardinality是一个预估值,而不是一个准确值,基本上用户也不可能得到一个准确的值。在实际应用中,Cardinality/n_rows_in_table应尽可能地接近1。如果非常小,那么用户需要考虑是否还有必要创建这个索引。故在访问高选择性属性的字段并从表中取出很少一部分数据时,对这个字段添加B+树索引是非常有必要的。使用执行计划(explain)可以用来查看这个SQL语句需要扫描的行数。

上面说的是单列索引Cardinality,如果是复合索引呢?其实有些不一样,比如复合索引(a,b),当你SHOW INDEX时可以看到多个索引条目。如果是单列索引,那么SEQ_IN_INDEX值就是1;如果是复合索引,那么SEQ_IN_INDEX值会递增。它们都各自有自己的Cardinality,只是计算方式不同而已。简单来说就是,第一列的Cardinality代表的是当前列中不重复记录数量的预估值(就是count(distinct a)),第二列的Cardinality代表的是第一列加第二列合并后不重复记录数量的预估值(就是count(distinct a, b)),以此类推。

Innodb存储引擎是如何收集统计信息?

我们知道了Cardinality表示索引选择性,建立索引的前提是列中的数据是高选择性的,这对数据库来说才具有实际意义。MySQL查询优化器根据索引的相对选择性来选择执行计划的索引。然而数据库是怎样来统计Cardinality信息的呢?因为MySQL数据库中有各种不同的存储引擎,而每种存储引擎对于B+树索引的实现又各不相同,所以对Cardinality的统计是放在存储引擎层进行的。

此外需要考虑到的是,在生产环境中,索引的更新操作可能是非常频繁的。如果每次索引在发生操作时就对其进行Cardinality的统计,那么将会给数据库带来很大的负担。另外需要考虑的是,如果一张表的数据非常大,如一张表有50G的数据,那么统计一次Cardinality信息所需要的时间可能非常长。这在生产环境下,也是不能接受的。因此,数据库对于Cardinality的统计都是通过采样的方法来完成的。

在InnoDB存储引擎中,Cardinality统计信息的更新发生在两个操作中:INSERT和UPDATE。根据前面的叙述,不可能在每次发生INSERT和UPDATE时就去更新Cardinality信息,这样会增加数据库系统的负荷,同时对于大表的统计,时间上也不允许数据库这样去操作。因此,InnoDB存储引擎内部对更新Cardinality信息的策略为:持久化(PERSISTENT)与非持久化统计数据(TRANSIENT)

  • 持久化(PERSISTENT)统计数据,存储在mysql.innodb_index_stats和mysql.innodb_table_stats中。
  • 非持久化(TRANSIENT)统计数据,存储在information_schema.statistics和information_schema.tables中。

前者是innodb表后者是memory表,他们受参数innodb_stats_persistent的控制。从MySQL 5.6.6开始,默认情况下,innodb_stats_persistent默认为ON,优化器统计信息会保留在磁盘上。

对于持久化(persistent)统计数据策略:

  • 表中1/10的数据已发生变化时,且设置了innodb_stats_auto_recalc和innodb_stats_persistent,则通过persistent方式更新统计信息。
  • 两次申请统计数据收集要超过10S。

对于非持久化(transient)统计数据策略:

  • InnoDB检测到自上次更新统计信息以来表的1/16已被修改,通过transient方式更新统计信息。
  • stat_modified_counter > 2000000000。

对于非持久化,第一种策略为自上次统计Cardinality信息后,表中1/16的数据已经发生过变化,这时需要更新Cardinality信息。第二种情况考虑的是,如果对表中某一行数据频繁地进行更新操作,这时表中的数据实际并没有增加,实际发生变化的还是这一行数据,则第一种更新策略就无法适用这种情况。故在InnoDB存储引擎内部有一个计数器stat_modified_counter,用来表示发生变化的次数。当stat_modified_counter大于2000000000时,同样需要更新Cardinality信息。

一些说明,非持久化统计数据是直接更新的或者说是同步更新的,但是持久化统计数据应该是异步更新的,典型的生产者消费者模型。MySQL中有一个专门的后台线程来负责收集统计数据如下:

实际上优化器统计数据基本逻辑都在row/row0mysql.cc:row_update_statistics_if_needed函数中,代码如下(应该不是整个逻辑链):

对于持久化统计(persistent)信息,InnoDB 1.2版本提供了以下参数对优化器统计信息进行设置,这些参数如下:

innodb_stats_auto_recalc

默认为ON,是否自动触发更新统计信息,开启与否只会影响persistent类型的统计,也就是仅影响持久化存储统计信息的表,而阈值是变化的数据超过表行数的10%。也就是说,当一个表索引统计信息是持久化存储的,并且表中数据变化了超过10%,如果innodb_stats_auto_recalc为ON,就会自动更新统计信息,否则不更。

由于自动统计信息重新计算(发生在后台)是异步,在运行影响超过10%的表的DML操作时(即使innodb_stats_auto_recalc启用后),可能不会立即重新计算统计信息 。在某些情况下,统计重新计算可能会延迟几秒钟。如果在更改表的重要部分之后立即需要最新统计信息,请运行ANALYZE TABLE以启动统计信息的同步(前台)重新计算。

innodb_stats_persistent

是否将命令ANALYZE TABLE计算得到的Cardinality值存放到磁盘上,存放mysql.innodb_index_stats表中。若是,则这样的好处是可以减少重新计算每个索引的Cardinality值,例如当MySQL数据库重启时。此外,用户也可以通过命令CREATE TABLE和ALTER TABLE的选项STATS_PERSISTENT来对每张表进行控制,默认值:ON,InnoDB 1.2版本提供。

innodb_stats_persistent_sample_pages

若参数innodb_stats_persistent设置为ON,该参数就是用来设置持久化更新统计信息时候索引页的取样页数。默认值20,InnoDB 1.2版本提供。如果设置的过高,那么在更新统计信息的时候,会增加ANALYZE TABLE的执行时间。

innodb_stats_include_delete_marked

MySQL 5.7.16中引入。默认情况下,InnoDB在计算统计信息时读取未提交的数据,如果未提交的事务从表中删除行,则InnoDB排除在计算行估计和索引统计信息时删除标记的记录,这可能会导致对正在该表上操作的其他事务使用非最佳执行计划。为了避免这种情况,innodb_stats_include_delete_marked可以在计算持久化优化器统计信息时确保包括删除标记的记录。当innodb_stats_include_delete_marked启用时,ANALYZE TABLE重新计算统计数据时,会考虑删除标记的记录。innodb_stats_include_delete_marked是影响所有InnoDB表的全局设置,仅适用于持久性优化器统计信息。

innodb_stats_method

用来判断如何对待索引中出现的NULL值记录,该参数默认值为nulls_equal,表示将NULL值记录视为相同的记录。其有效值还有nulls_unequal,nulls_ignored,分别表示将NULL值记录视为不同的记录和忽略NULL值记录。例如某页中索引记录为NULL、NULL、1、2、2、3、3、3,在参数innodb_stats_method的默认设置下,该页的Cardinality为4;若值为nulls_unequal,则该页的Cardinality为5;若值为nulls_ignored,则Cardinality为3。

对于非持久化统计(transient)信息,InnoDB 1.2版本提供了以下参数对优化器统计信息进行设置,这些参数如下:

innodb_stats_on_metadata

MySQL 5.6.6默认启用了持久化优化器统计信息后,默认设置innodb_stats_on_metadata为OFF。参数的意义是当通过命令SHOW TABLE STATUS、SHOW INDEX及访问INFORMATION_SCHEMA架构下的表TABLES和STATISTICS时,是否需要重新计算统计信息?默认值:OFF,InnoDB 1.2版本提供。当innodb_stats_on_metadata设置为ON的时候,就需要重新计算;但仅在统计信息配置为非持久化的时候生效,也就是说在innodb_stats_persistent配置为OFF时,innodb_stats_on_metadata的设置才生效。具体某个索引的统计信息更新时间参考mysql.innodb_index_stats这个系统表。

innodb_stats_sample_pages

在InnoDB 1.2版本之前,用来设置统计时每次采样的数量,默认值:8,在InnoDB 1.2版本时被innodb_stats_transient_sample_pages参数取代。

innodb_stats_transient_sample_pages

该参数用来取代之前版本的参数innodb_stats_sample_pages,表示每次采样页的数量。默认值:8,InnoDB 1.2版本提供。

以非持久化(transient)为例,接着考虑InnoDB存储引擎内部是怎样来进行Cardinality信息的统计和更新操作的?同样是通过采样的方法,默认InnoDB存储引擎对8个叶子节点(Leaf Page)进行采样,采样的过程如下:

  • 取得B+树索引中叶子节点的数量,记为A。
  • 随机取得B+树索引中8个叶子节点,统计每个页不同记录的个数,即为P1,P2,…,P8。
  • 根据采样信息给出Cardinality的预估值:Cardinality = (P1+P2+…+P8) * A/8。

通过上述的说明可以发现,在InnoDB存储引擎中,Cardinality值是通过对8个叶子节点预估而得到的,不是一个精确的值。再者,每次对Cardinality值的统计,都是通过随机取8个叶子节点得到的,这同时又暗示了另一个Cardinality对象,即每次得到的Cardinality值可能是不同的。如:SHOW INDEX FROM OrderDetails语句会触发MySQL数据库对于Cardinality值的统计,所以可能会出现此表没有任何数据变化,但是你多次执行SHOW INDEX FROM OrderDetails看到的Cardinality值不同。因为每次执行都会触发MySQL数据对于Cardinality值的统计,随机选取8个叶子节点进行分析。所以如果遇到这个并不是InnoDB存储因为的Bug,只是随机采样而导致的。

当然,有一种情况使得用户每次观察到的索引Cardinality值都是一样的,那就是表足够小,表的叶子节点数小于或者等于8个。这时即使随机采样,也总是会采取到这些页,因此每次得到的Cardinality值是相同的。

当执行SQL语句ANALYZE TABLE、SHOW TABLE STATUS、SHOW INDEX以及访问INFORMATION_SCHEMA架构下的表TABLES和STATISTICS时会导致InnoDB存储引擎去重新计算索引的Cardinality值。若表中的数据量非常大,并且表中存在多个辅助索引时,执行上述这些操作可能会非常慢。虽然用户可能并不希望去更新Cardinality值。当我们show index from table时,实际上就是访问information_schema.statistics表。

二、持久化统计信息表

在MySQL中提供了两个表记录统计信息的相关内容,分别是innodb_table_stats与innodb_index_stats,下面就这两个表进行介绍。

innodb_table_stats

重要的列:

  • last_update:就是最后一次收集统计信息的时间。
  • clustered_index_size:聚集索引的page数量。
  • sum_of_other_index_sizes:非聚集索引的page数量。

通过这些信息我们可以算出聚集索引的大小:

innodb_index_stats

这个表里面输出的内容相对会比较复杂一些。

我们主要关注的的列:

  • stat_value:显示统计值的大小。
  • stat_description:类型的描述。
  • stat_name:此列显示统计的类型,会出现下面这些:

size:此时stat_value显示索引的page数量。

n_leaf_pages:此时stat_value显示叶子节点的数量。

n_diff_pfxNN:显示索引字段上唯一值的数量,这里需要特殊说明:

  • 主键索引与唯一索引,例如上面结果中index_name = PRIMARY时
  1. index_name = PRIMARY 且 stat_name = n_diff_pfx01,则stat_value代表主键索引中第一列distinct之后的数量,从上面的例子我们看到stat_value是1,因为a这一列的值都是(1)。
  2. index_name = PRIMARY 且 stat_name = n_diff_pfx02,则stat_value代表主键索引中第一列和第二列distinct之后的数量,从上面的例子我们看到stat_value是5,因为a,b两列存在的值是(1,1)(1,2)(1,3)(1,4)(1,5)。
  3. stat_description中我们可以看到是那几个列的信息。
  4. n_diff_pfxNN 以此类推。
  • 非唯一索引不同的地方是在原有的列之后会添加上主键索引,这样说可能比较难理解,针对上面查询出来的记过下面详细说明下:
  1. 根据表结构定义我们知道i1是一个非唯一索引,是由(c,d)两个列组成的。我们根据上面的结果可以看到除了n_diff_pfx01,n_diff_pfx02又多出来了n_diff_pfx03,n_diff_pfx04,通过stat_description我们可以看到n_diff_pfx03,n_diff_pfx04是在原有的(c,d)两列上又多出了 (c,d,a) (c,d,a,b) 这里就是将主键索引添加到了这里。
  2. 例如 n_diff_pfx03 的stat_value是2 代表的就是在原有的非唯一索引上添加了主键索引的第一列(a),这个时候distinct之后的值是2所存在的值就是: (10,11,1) (10,12,1)。

通过这个表我们可以查看索引选择性如何,并且可以看到组合索引中每一列选择性如何,还可以计算索引的大小:

查询结果如下:

数据库中的一些系统表对DBA是非常重要的,可以帮助我们排查问题、性能分析、去更好的了解一些机制。

innodb_table_stats与innodb_index_stats两张表我们可以了解统计信息、计算索引的大小、索引的选择性如何,也可以做到监控中。

通过MySQL 5.7中添加了Sys Schema也就是让大家不用通过去查看代码的方式去排查各种问题、故障处理等,可见对系统表的学习在日后会更重要。

三、索引选择性实践

为了讨论索引策略,需要一个数据量不算小的数据库作为示例。本文选用MySQL官方文档中提供的示例数据库之一:employees。这个数据库关系复杂度适中,且数据量较大。下图是这个数据库的E-R关系图(引用自MySQL官方手册):

MySQL InnoDB索引选择性与统计信息

MySQL官方文档中关于此数据库的页面为https://dev.mysql.com/doc/employee/en。里面详细介绍了此数据库,并提供了下载地址和导入方法,如果有兴趣导入此数据库到自己的MySQL可以参考文中内容。

显然选择性的取值范围为(0, 1],其公式Index Selectivity = Cardinality / #T, 选择性越高的索引价值越大,这是由B+Tree的性质决定的。例如,如测试数据库中用到的employees.titles表,如果title字段经常被单独查询,是否需要建索引,我们看一下它的选择性:

title的选择性不足0.0001(精确值为0.00001579),所以实在没有什么必要为其单独建索引。

有一种与索引选择性有关的索引优化策略叫做前缀索引,就是用列的前缀代替整个列作为索引key,当前缀长度合适时,可以做到既使得前缀索引的选择性接近全列索引,同时因为索引key变短而减少了索引文件的大小和维护开销。下面以employees.employees表为例介绍前缀索引的选择和使用。

从上图可以看到employees表只有一个索引<emp_no>,那么如果我们想按名字搜索一个人,就只能全表扫描了:

如果频繁按名字搜索员工,这样显然效率很低,因此我们可以考虑建索引。有两种选择,建<first_name>或<first_name, last_name>,看下两个索引的选择性:

<first_name>显然选择性太低,<first_name, last_name>选择性很好,但是first_name和last_name加起来长度为30,有没有兼顾长度和选择性的办法(对于VARCHAR索引最长可以有768字节)?可以考虑用first_name和last_name的前几个字符建立索引,例如<first_name, left(last_name, 3)>,看看其选择性:

选择性还不错,但离0.9313还是有点距离,那么把last_name前缀加到4:

这时选择性已经很理想了,而这个索引的长度只有18,比<first_name, last_name>短了接近一半,这时我们把这个前缀索引建上。

先开启profile再执行一遍按名字查询,比较分析一下与建索引前的结果:

性能的提升是显著的,查询速度提高了200多倍。

前缀索引兼顾索引大小和查询速度,但是其缺点是不能用于ORDER BY和GROUP BY操作,也不能用于Covering index(即当索引本身包含查询所需全部数据时,不再访问数据文件本身)。

<参考>

http://www.cnblogs.com/justfortaste/p/3276363.html

https://www.jianshu.com/p/019378bfd51f

https://dev.mysql.com/doc/refman/5.7/en/innodb-performance-optimizer-statistics.html


如果您觉得本站对你有帮助,那么可以支付宝扫码捐助以帮助本站更好地发展,在此谢过。
喜欢 (5)or分享 (0)
关于作者:

您必须 登录 才能发表评论!

(5)个小伙伴在吐槽
  1. 我看错了。是1/16
    johny6662017-12-25 10:45 Windows 10 | Chrome 55.0.2883.87
  2. 因此,InnoDB存储引擎内部对更新Cardinality信息的策略为: 表中1/16的数据已发送过变化。 这句错了,是1/6不是1/16,希望改正下
    johny6662017-12-20 14:25 Windows 10 | Chrome 55.0.2883.87
    • 请问一下,你是在什么版本中看到的1/6这个值,告知一下。
      彭东稳2017-12-21 19:08 未知操作系统 | Chrome 61.0.3163.79
      • 我看错了,是1/16
        johny6662017-12-25 10:45 Windows 10 | Chrome 55.0.2883.87
        • 模式已经改了,1/16是非持久化模式。可以看https://dev.mysql.com/doc/refman/5.7/en/innodb-performance-optimizer-statistics.html
          彭东稳2017-12-25 15:53 未知操作系统 | Chrome 63.0.3239.84