1、什么是 Cardinality

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

SELECT FROM student Where sex='M';

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

怎样查看索引是否是高选择性的呢?可以通过 SHOW INDEX结果中的列 Cardinality来观察。 Cardinality值非常关键,表示索引中不重复记录数量的预估值。同时需要注意的是, Cardinality是一个预估值,而不是一个准确值,基本上用户也不可能得到一个准确的值。在实际应用中, Cardinality/n_rows_in_table应尽可能地接近1。如果非常小,那么用户需要考虑是否还有必要创建这个索引。故在访问高选择性属性的字段并从表中取出很少一部分数据时,对这个字段添加B+树索引是非常有必要的。如:

SELECT FROM member WHERE usernick ='David';

表 member大约有500万行数据。 usemick字段上有一个唯一的索引。这时如果查找用户名为 David的用户,将会得到如下的执行计划:

EXPLAIN SELECT* FROM memberWHERE usernick='David'\G

可以看到使用了 usernick这个索引,这也符合之前提到的高选择性,即SQL语句选取表中较少行的原则。

2、InnoDB存储引擎的 Cardinality统计

建立索引的前提是列中的数据是高选择性的,这对数据库来说才具有实际意义。然而数据库是怎样来统计 Cardinality信息的呢?因为 MySQL数据库中有各种不同的存储引擎,而每种存储引擎对于B+树索引的实现又各不相同,所以对 Cardinality的统计是放在存储

引擎层进行的。

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

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

• 表中116的数据已发生过变化。
• stat_modified_counter>2000000000。

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

接着考虑 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

上述这句SQL语句会触发 MySQL数据库对于 Cardinality值的统计,第一次运行得到的结果如图所示。

在上述测试过程中,并没有通过 INSERT、 UPDATE这类操作来改变表 OrderDetails中的内容,但是当第二次再运行 SHOW INDEX FROM语句时, Cardinality值还是会发生变化,如图所示。

可以看到,第二次运行 SHOW INDEX FROM语句时,表 OrderDetails中索引的Cardinality值都发生了变化,虽然表 OrderDetails本身并没有发生任何的变化,但是,由于 Cardinality是对随机取8个叶子节点进行分析,所以即使表没有发生变化,用户观察到的索引 Cardinality值还是会发生变化,这本身并不是 InnoDB存储引擎的Bug,只是随机采样而导致的结果。

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

在 InnoDB1.2版本之前,可以通过参数 innodb_stats_sample_pages用来设置统计Cardinality时每次采样页的数量,默认值为8。同时,参数 innodb_stats_method用来判断如何对待索引中出现的NULL值记录。该参数默认值为 nulls_equal,表示将NULL值记录视为相等的记录。其有效值还有 nulls_unequal, nulls_ignored,分别表示将NULL值记录视为不同的记录和忽略NULL值记录。例如某页中索引记录为NULL、NULL、12、2、3、3、3,在参数 innodb_stats_method的默认设置下,该页的 Cardinality为4;若参数 innodb_stats_method为 nulls_unequal,则该页的Cardinality为5;若参数 innodb_stats_method为nulls_ignored,则Cardinality为3。

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

InnoDB1.2版本提供了更多的参数对 Cardinality统计进行设置,这些参数如下表所示。

InnoDB1.2新增参数

参数

说明

innodb_stats_persistent

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

innodb_stats_on_metadata

当通过命令 SHOW TABLE STATUS、 SHOW INDEX及访问INFORMATION SCHEMA架构下的表 TABLES和 STATISTICS时,是否需要重新计算索引的 Cardinality值。默认值:OFF

innodb stats_persistent_ sample_ pages

若参数 innodb_stats_persistent设置为ON,该参数表示 ANALYZETABLE更新 Cardinality t值时每次采样页的数量。默认值:20

innodb_stats_transient_sample_pages

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