MySQL评估索引合理性字段---Cardinality

Cardinality 表示索引的选择性。建立索引的前提是列中的数据是高选择性的。

在MySQL数据库中，可以使用 show index from tableName \G 语句来查看表中创建的索引的信息：

mysql> show index from user_operation_log \G ;
*************************** 1. row ***************************
        Table: user_operation_log
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 8376129
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: user_operation_log
   Non_unique: 1
     Key_name: user_operation_log_ip_IDX
 Seq_in_index: 1
  Column_name: ip
    Collation: A
  Cardinality: 1
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.00 sec)

上面有一个属性Cardinality，可以通过观察它来评估索引是否合理。

Cardinality值非常关键，它表示索引中不重复记录数量的预估值。它是一个预估值，而不是一个准确的值，基本上我们不可能得到一个准确的值。它会估计索引中不重复记录，如果这个相对值很小，可能就要评估索引是否有意义。

Cardinality表示索引的选择性。建立索引的前提是列中的数据是高选择性的。

并不是出现在where 条件中的字段都需要创建索引。

对于那种作为条件查询时，能够过滤掉很多数据的字段（选择性高的列），可以考虑对其创建索引。类似 “性别”、“状态”、“类型”等字段不适合创建索引。

按照“性别”查找时，可取值的范围一般是“M”和“F”。因此sql语句得到的结果可能是该表的50%数据，这个时候添加B+索引没有必要。但如果某个字段的取值范围很广，几乎没有重复，我们称之为高选择性，添加B+树索引很合适。

Cardinality表示索引的选择性。建立索引的前提是列中的数据是高选择性的。

MySQL如何来统计Cardinality信息呢？

MySQL数据库中有各种不同的存储引擎，而每种存储引擎对于B+树的实现方式各不相同，所以对于Cardinality的统计是放在存储引擎层进行的。 

我们需要知道，在生产环境中，索引的更新操作可能是非常频繁的。如果每次索引在发生更新操作时，就对其进行Cardinality值的统计，那么将会给数据库带来很大的负担。如果一张表的数据非常大，假设有上百G，那么统计一次Cardinality信息所需要的时间可能非常长。在生产环境中，也是不能接受的。因此，数据库对于Cardinality的统计是通过采样（Sample）的方法来完成的。 

那么什么时候会更新Cardinality值呢？以及这个值是如何得到的？

更新Cardinality发生在insert和update两个操作中。但是不是每次表中的索引发生insert和update的时候就去更新Cardinality信息。InnoDB存储引擎内部对更新Cardinality信息的策略为：

1） 表中1/16的数据已发生过变化

2） stat_modified_counter>2 000 000 000 (stat_modified_counter是innodb存储引擎中的一个计数器)

第一种策略为自从上一次统计过Cardinality信息之后，表中1/16的数据已经发生过变化，此时就要触发更新Cardinality信息了。

第二种策略考虑到一种情况，如果对表中某一行或者多行的数据频繁地进行更新操作，但是表中的记录数没有增加，发生变化的数据还是这一行或者多行。那么很显然，第一种更新策略无法适用这种情况。这个计数器stat_modified_counter大于2 000 000 000时，同样需要更新Cardinality的信息。

默认Innodb存储引擎对8个叶子节点进行采样。受参数innodb_stats_sample_pages影响。

 手动更新统计值

如果系统运行一段时间之后，我们可以通过执行下面的sql，重新计算cardinality值

当执行语句analyze table、show table status、show index、访问information_schema.tables、访问information_schema.statistics时都会导致innodb存储引擎重新计算cardinality的值。

如果表中数据大，且存在多个辅助索引，以上操作可能会很慢，建议在业务低峰时候执行。

相关SQL语句（innodb引擎）： 

show variables like '%innodb_buffer_pool%';

show variables like '%data%';

/**查询表中的 索引信息**/
show index from user_operation_log \G;

/**查询数据页语句  （可以针对一个查询SQL查看加载的数据页有多少）**/
select index_name,count(*) 
from information_schema.INNODB_BUFFER_PAGE 
where INDEX_NAME in('val','primary') and TABLE_NAME like '%user_operation_log%'

group by index_name;

select * from information_schema.INNODB_SYS_TABLES ;

select * from information_schema.INNODB_SYS_COLUMNS ;

select * from information_schema.INNODB_SYS_FIELDS ;

select * from information_schema.INNODB_SYS_INDEXES ;