什么是Cardinality
Cardinality表示索引中不重复记录数量的预估值
哪些列该添加索引(高选择性)
并不是所有的查询条件中出现的列都需要添加索引。
对于什么时候添加B+树索引,一般的经验是,在(结果数据是)访问表中很少一部分时使用B+树索引才有意义。
对于性别字段、地区字段、类型字段,它们可取值的范围很小,称为低选择性。如:
SELECT * FROM student WHERE sex = 'M'
按性别进行查询时,可取值的范围一般只有’M’、‘F’。
因此上述SQL得到的结果可能是该表的50%的数据(假设男女比1:1),即低选择性,这时添加B+树索引是完全没有必要的。
相反,如果某个字段的取值范围很广,几乎没有重复,即属于高选择性,则此时使用B+树是合适的。例如,姓名字段,基本上在一个应用中不允许重名出现。
如何查看是否有高选择性(查看Cardinality值)
可以通过SHOW INDEX结果中的列Cardinality来观察。
Cardinality表示索引中不重复记录数量的预估值。同时需要注意,Cardinality是一个预估值,而不是一个准确值。
在实际应用中,Cardinality/n_rows_in_table应尽可能地接近1。
如果非常小,那么用户需要考虑是否还有必要创建这个索引。
故在访问高选择性属性的字段并从表中取出很少一部分数据时,对这个字段添加B+树索引是非常有必要的。
Cardinality的更新策略和计算原理
建立索引的前提是列中的数据是高选择性的,这对数据库来说才具有实际意义。
然而数据库是怎样来统计Cardinality信息的呢?Cardinality的统计是在存储引擎层进行的。
此外需要考虑的是,在生产环境中,索引的更新操作可能是非常频繁的。
如果每次索引在发生操作时就对其进行Cardinality的统计,那么将会给数据库带来很大的负担。
另外需要考虑的是,如果一张表的数据很大,如一张表有50G的数据,那么统计一次Cardinalidy信息所需要的时间可能非常长。这在生产环境下也是不能接受的。因此,数据库对于Cardinality的统计都是通过采样(Sample)的方法来完成的。
InnoDB中Cardinality的更新策略
在InnoDB存储引擎中,Cardinality统计信息的更新发生在两个操作中:INSERT和UPDATE。
根据前面的叙述,不可能在每次发生INSERT和UPDATE时就去更新Cardinality信息,这样会增加数据库系统的负荷。同时对于大表的统计,时间上也不允许数据库这样去操作。
因此,InnoDB存储引擎内部对更新Cardinality信息的策略为:
- 表中1/16的数据已发生过变化
- stat_modified_counter>2000 000 000
第一种策略为自从上次统计Cardinality信息后,表中1/16的数据已经发生过变化,这时需要更新Cardinality信息。
第二种情况考虑的是,如果对表中某一行数据频繁地进行更新操作,这时表中的数据实际并没有增加,实际发生变化的还是这一行数据,则第一种更新策略就无法适用这种情况。
故在InnoDB存储引擎内部有一个计数器stat_modified_counter,用来表示发生变化的次数,当stat_modified_counter大于2000 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值可能是不同的。
但是有一种情况可能使得用户每次 观察到的索引Cardinality值都是一样的,那就是当表足够小,表的叶子节点小于或者等于8个。这样即使随机采样,也总是会采取到这些页,因此每次得到的Cardinality值是相同的。
一些参数:
- innodb_stats_sample_pages:设置统计Cardinality时每次采样的数量买,默认是8.
- innodb_stats_method:如何对待索引中出现的NULL记录,默认是nulls_equal,表示将null值视为相等的记录,类似的还有nulls_unequal, nulls_ignore;
其他触发更新Cardinality的行为:
- ANALYZE TABLE
- SHOW TABLE STATUS
- SHOW INDEX
- 访问INFORMATION——SCHEMA架构下的表TABLES和STATISTICS。