阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

MySQL中Cardinality值的介绍

112次阅读
没有评论

共计 2922 个字符,预计需要花费 8 分钟才能阅读完成。

1) 什么是 Cardinality

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

SELECT * FROM student WHERE sex=’M’

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

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

SELECT * FROM member WHERE usernick=’David’;

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

MySQL 中 Cardinality 值的介绍

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

2)        InnoDB 存储引擎的 Cardinality 统计

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

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

在 InnoDB 存储引擎中,Cardinality 统计信息的更新发生在两个操作中:insert 和 update。InnoDB 存储引擎内部对更新 Cardinality 信息的策略为:

表中 1 /16 的数据已发生了改变

stat_modified_counter>2000 000 000

第一种策略为自从上次统计 Cardinality 信息后,表中的 1 /16 的数据已经发生过变化,这是需要更新 Cardinality 信息

第二种情况考虑的是,如果对表中某一行数据频繁地进行更新操作,这时表中的数据实际并没有增加,实际发生变化的还是这一行数据,则第一种更新策略就无法适用这种情况,故在 InnoDB 存储引擎内部有一个计数器 start_modified_counter, 用来表示发生变化的次数, 当 start_modified_counter>2 000 000 000 时,则同样更新 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 值的统计,第一次运行得到的结果如图 5 -20

 MySQL 中 Cardinality 值的介绍

在上述测试过程中,并没有通过 INSERT、UPDATE 这类的操作来改变 OrderDetails 中的内容,但是当第二次运行 SHOW INDEX FROM OrderDetails 语句是,发生了变化,如图 5 -21

MySQL 中 Cardinality 值的介绍

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

当然,有一种情况可以使得用户每次观察到的索引 Cardinality 值是一样的。那就是表足够小,表的叶子节点树小于或者等于 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、1、2、2、3、3、3, 在参数 innodb_stats_method 默认设置下,该页的 Cardinality 为 4;若参数 innodb_stats_method 为 nulls_unequal, 则该页的 Cardinality 为 5,若参数 innodb_stats_method 为 nulls_ignored,则 Cardinality 值为 3

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

InnoDB1.2 版本提供了更多参数对 Cardinality 进行设置。如表

MySQL 中 Cardinality 值的介绍

本文永久更新链接地址:http://www.linuxidc.com/Linux/2017-03/142323.htm

正文完
星哥说事-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计2922字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中