InnoDB 索引统计信息
1. 引言
存储引擎会将索引统计信息提供给SERVER层和优化器，为优化器确定执行计划提供计算依据。oracle中表与索引分开存储，统计信息会分为表的统计信息与索引的统计信息，InnoDB中表是聚簇索引表，数据即索引，因此提及统计信息，都是指索引统计信息。索引统计信息就是与索引选择性相关的度量，主要有两个统计信息：唯一值的数量以及某个范围内值的数量。简言之，索引统计信息主要描述索引中数据分布的近似信息。
本文主要介绍索引统计信息的收集、分类以及Mysql提供的统计信息视图。
2. 统计信息的收集策略
InnoDB通过分析随机采样的索引叶子页来估算索引的统计信息。处于性能考虑，mysql选择只扫描较少的页来估算（默认收集持久统计信息只采样20个页）。既然是通过采样来估算，那么这个统计信息就不是十分准确的，就算采样率为100%，而数据库中的数据是不断变化的，统计信息并不是时时刻刻都在收集的，就会有一定的滞后性，也是不会十分准确的。尽管如此，只要数量级上与实际相符，对指导优化器选择执行计划也是很有意义的，绝大多数情况优化器都能做出正确的选择。
2.1. 统计信息对null值的处理。
InnoDB的索引与Oracle的一个很大不同，即使索引列值都为null，也会存入索引。那么在收集索引统计信息时，如何处理null值呢？由于NULL不等于NULL的属性，收集统计信息时是将NULL值都分配到一个bucket中还是将其分开？这主要取决于你要执行的查询。将所有NULL值视为不同的值，会增加索引的基数，尤其是当索引有很多NULL值时。另一方面，如果将所有NULL值视为相同的值，就会减少索引的基数，这对于查询NULL值就很有用。InnoDB用变量innodb_stats_method来控制收集统计信息时如何处理NULL值。
innodb_stats_method有如下3个值可以采用：
nulls_equal：所有null值都被视为相同的值。这个也是默认值。
nulls_unequal: null值都视为不同的值。
nulls_ignore: 收集统计信息时忽略null值。
2.2. 采样页设置。
使用近似统计信息不利之处在于它们未必能很好的表示实际的分布情况。因此优化器可能会选择错误的索引，或者错误的表连接顺序，从而导致查询变慢。可以通过调整采样页的数量使统计信息趋于准确。InnoDb使用两个变量来设置要采样的默认页数量，持久统计信息使用innodb_stats_persistent_sample_pages（默认值20）来设置，临时统计信息用innodb_stats_transient_sample_pages(默认值8)来设置。采样页数设置，对于多列索引而言，实际采样的页数是列数*采样页变量值。比如将采样页数设为20，索引中有4个列，就采样4*20=80页（实际考虑的可能要复杂的多）。
需要检查多少页才能获得良好的估算结果？如果数据是分布均匀的，即每个索引值的行数大致相同，只需要检查较少的页即可。此时往往默认就够用了。另一方面，如果数据分布非常不规则，则可能需要增加采样页数。比如一个列上99.9%是相同的值，随机采样看到的都是相同的值，InnoDB可能得出结论索引中只包含一个值，该索引作为过滤器毫无价值（这种情况可以用直方图来解决）。此外表的大小也是需要考虑的因素，表越大，通常情况下需要检查的页就越多，原因是，表越大，则整个叶子页指向具有相同索引值的行的可能性就越大。这会降低每个采样页中值的个数，因此需要进行补偿，就有必要采样更多的页。
2.3. 事务的隔离级别。
收集统计信息时使用未提交读隔离级别，统计信息主要用于将来的查询，没有理由在收集统计信息时增加维护读视图的开销。就是说某些事务发生dml操作，即使事务没有完成，收集统计信息时也会考虑在内。
3. 统计信息类型
InnoDB有两种存储索引统计信息的方法，可以使用持久存储，也可以使用临时存储。表默认使用何种方法来存储索引统计信息，可以通过全局选项innodb_stats_persistent来设置，设置为1或on时表明使用持久统计信息，此为默认值。设置为0或off时，则更改为临时统计信息。
3.1. 持久统计信息
MySQL自5.6开始引入持久索引统计信息，从引入开始，它就一直是默认设置，也是推荐的设置。它使得查询计划比之前使用临时索引统计信息时更为稳定。持久索引统计信息，就是每次收集统计信息后将保存到表中，以便在MySQL重启之后也不会丢失这些统计信息。
收集持久统计信息的配置可以在全局或表级别来设置，如果没有对表进行特定设置，则会将全局设置作为默认配置。
上面我们提到了全局选项innodb_stats_persistent来配置使用何种索引统计信息。还有另外3个全局级别的选项与之配合使用：
Innodb_stats_persistent_sample_pages: 要采样的页数量。采样的页数量越多，统计信息越准确，但成本也会越高，默认值为20。
Innodb_stats_auto_recalc: 表中的数据超过10%的行被更新后，是否自动更新统计信息。默认启用。
Innodb_stats_include_delete_marked: 是否在统计信息中标记那些已删除但尚未提交的行。默认设置为禁用(关闭，OFF)。
表级别的控制选项有：stats_persistent、stats_sample_pages、stats_auto_recalc。这些选项与对应的全局选项意义相同，只是控制的是当前表的行为，控制粒度更细，表级别的选项配置会覆盖掉全局配置的行为。这些选项可以在create table时指定，也可以使用alter table来设置，如果没有指定stats_persistent选项，则按全局选项innodb_stats_persistent的设置。
例如：create table 时指定选项stats_persistent=0， 采用临时存储统计信息。再通过alter table将表选项stats_persistent改为1，采用持久存储统计信息，并且修改采样页的数量为100。
请注意alter table是如何返回受影响的行数（0）的。更改持久统计信息选项，只会更改表的元数据，这些更改会立即生效，不会影响表中数据。这意味着可以根据需要随时更改这些设置，而不必担心这样做会带来较大开销。
InnoDB使用mysql库的两张表来存储持久统计信息：innodb_index_stats、innodb_table_stats。
1. Innodb_index_stats:
对于每个B-tree索引，此表中都有几行来包含相关数据，例如它提供索引的每个部分的唯一值数量（基数）、索引中的叶子页数量以及索引的总大小。
该表结构如下：
Stat_name列为统计信息的名称，stat_value为stat_name列统计信息的值，sample_size为采样页数，stats_description为统计信息的秒数信息，对于基数而言，即为计算基数时所包含的列。
下面的示例为查询的world库countrylanguage的索引统计信息。发现country_code, language列上有主键索引，还有二级索引，这在oracle是被限制的，InnoDB中却是比较灵活，不会限制这样的冗余索引。
每个索引会有几行数据，它把每项统计信息逐行进行显示。下面介绍一下这些统计信息。
n_diff_pfxNN:索引中前NN列的基数，因此对于多列的索引，就会对应多行来显示，stat_description列包含了该统计信息对应的列。例如上面示例中索引countrycode的统计信息n_diff_pfx01对应countrycode列，即统计即基数值为233是对应这一列的，n_diff_pfx02对应列countrycode,lanuguage，也就是统计的基数值984是对应两个列的。
n_leaf_pages:索引中的叶子页总数。可将其与n_diff_pfxNN统计信息的样本大小进行比较，从而确定索引的采样比例。比如索引countrycode，n_leaf_pages为3，n_diff_pfxNN对应sample_size为3，则采样比例为100%。
size:索引中总页数，包含非叶子页。
2. innodb_table_stats
此表包含整张表的汇总统计信息。表结构如下：
n_rows为估算的表中包含的行数，cluster_index_size为聚簇索引中的页数，sum_of_other_index_sizes为所有二级索引的总页数。下图为world库countrylanguage表的统计信息。
聚簇索引的大小与innodb_index_stats中的主键大小相同。
innodb_index_stats与innodb_table_stats这两个表都是常规的innodb引擎表，将它们包含在备份中比较合适，这样如果查询计划突然改变，还可回退比较其统计信息。Oracle中是包含历史统计信息的，这一点就比mysql要好。具有update权限的用户也可更新这两张表，但是基本不建议手工修改索引的统计信息。
3.2. 临时统计信息
临时统计信息是InnoDB中实现的用于处理索引统计信息的原始方法。临时统计信息是第一次打开表并将其保存在内存中计算的。由于统计信息无法持久保存，因此统计信息非常不稳定，更容易看到查询计划发生更改的情况。
有两个配置选项可影响临时统计信息的行为：
Innodb_stats_transient_sample_pages: 更新统计信息时采样的页数，默认值为8.
使用临时统计信息的表，不仅在首次打开表时重新计算统计信息，在表中6.25%(1/16)的行发生更改时也会重新计算统计信息。在自动重新计算统计信息时，临时统计信息不使用后台进程，因此更可能影响性能，为此该选项默认值仅为8页。
Innodb_stats_on_metadata:查询表的元数据时是否重新估算统计信息。
自MySQL5.6起，默认值为OFF。如果开启，在查询information库的tables和statistics表，或使用等效的show语句时，均会触发对索引统计信息的更新，实际上没有什么原因需要如此频繁的更新统计信息，因此建议关闭此选项。
我们知道收集统计信息使用未提交读隔离级别。在极端情况下，请考虑如下一张缓存表，其中的事务通过两个步骤来刷新数据：
（1）从表中删除所有数据
（2）用更新后的数据重建表。
默认情况下，当表中超过6.25%的数据行发生更新就会触发临时统计信息的自动更新，这意味着当步骤(1)完成时，InnoDB将重新计算统计信息。这很容易理解--此时表已空，因此没有内容。如果正好在此时执行查询，优化器会将该表视为空表。但除非查询是在未提交读的事务隔离级别执行的，负责查询将读取所有旧行，并且很可能出现查询执行效率低下的问题，对于这样的情况需要使用持久统计信息。
有一个疑问：持久统计信息的自动更新的后台进程，隔多久进行一次收集。
4. 统计信息更新方式
4.1. 自动更新统计信息
对于持久统计信息与临时统计信息，在满足某些条件时会触发统计信息自动更新机制，它们的自动更新机制有所不同，下面以表格的形式来展示：
属性 | 持久统计信息 | 临时统计信息 | |
自动更新触发机制 | 更新行 | 表的10% | 表的6.25% |
由于更新行而导致的最小更新时间间隔 | 10秒 | 更新16次 | |
其他触发更新的操作 | 首次打开表时，查询表元数据时(可选) | ||
是否后台更新 | 是 | 否 | |
自动更新配置 | 全局变量： innodb_stats_auto_recalc 表选项：stats_auto_recalc | 无 | |
4.2. 手动更新统计信息
4.2.1. analyze table语句
analyze table语句可以更新索引统计信息与直方图信息。
analyze和table之间加no_write_to_binlog或local，则该语句只应用于本地示例，而不会写入二进制日志。
analyze table语句可以同时更新多张表的索引统计信息，只需要用逗号分隔列表来列出这些表即可。
当执行analyze table语句时会强制刷新索引统计信息和表的缓存值，所以含隐式flush table语句，注意只有在所有相关查询操作完成之后，才可以执行刷新表操作，因此在运行时间较长的查询时不要使用analyze table或mysqlcheck语句。
使用示例：
4.2.2. mysqlcheck程序
通过cron守护进程或windows计划管理器来调用shell脚本进行触发，使用mysqlcheck就很方便，可以更新单张表或多张表。
--analyze 选项让mysqlcheck更新索引统计信息，也可使用--write-binlog/--skip-write-binlog选项来指定是否要将语句写入二进制日志中，默认为写入。
--databases 指定要分析的database，可以指定多个库，以空格隔开。
--all-databases 选项，分析所有database，除information_schema与performance_schema库。
mysqlcheck程序需要了解MySQL,需要使用标准的连接选项。
使用示例：
不指定databases选项，--analyze后第一个参数被解析为database。
指定--databases选项，分析一个或多个databases
mysqlcheck可以做的事情不仅是分析表以更新索引统计信息，这里只探讨其分析特性。
5. 统计信息相关视图介绍
5.1. information_schema.statistics
此视图是索引统计信息的主表，不仅包含索引统计信息本身，也包含索引的元数据信息。视图中各列信息如下：
列名 | 数据类型 | 描述 |
TABLE_CATALOG | varchar(64) | 表所属目录，始终为def |
TABLE_SCHEMA | varchar(64) | 表所属方案/库 |
TABLE_NAME | varchar(64) | 索引所在表名 |
NON_UNIQUE | int | 索引是唯一的(0)，还是非唯一(1)的。 |
INDEX_SCHEMA | varchar(64) | 与table_schema相同（因为索引与表在同一方案中） |
INDEX_NAME | varchar(64) | 索引名称 |
SEQ_IN_INDEX | int unsigned | 列在索引中的位置。单列索引始终为1 |
COLUMN_NAME | varchar(64) | 列名 |
COLLATION | varchar(1) | 索引排序方式。 该列可以为NULL(不排序),A(升序),D(降序) |
CARDINALITY | bigint | 指的索引中包含该列的部分的基数，如果此列处于多列索引的第2位，第3位，对应的是索引包含到此列的基数，不是单列的基数。 |
SUB_PART | bigint | 对于前缀索引，该值为索引的字符数或字节数，针对整个列建立的索引，此列为null |
PACKED | binary(0) | 对于innodb表，该列始终为null |
NULLABLE | varchar(3) | 是否允许null值，该列可以是空字符串或YES |
INDEX_TYPE | varchar(11) | 索引类型。例如B-tree索引为BTREE |
COMMENT | varchar(8) | 索引的额外信息，不适用于innnodb表 |
INDEX_COMMENT | varchar(2048) | 添加索引时指定的注释 |
IS_VISIBLE | varchar(3) | 索引是否可见。（YES or NO) |
EXPRESSION | longtext | 对于函数索引，该值包含用于生成函数索引的表达式。非函数索引则为NULL. |
下面通过information_schema.statistics视图查询world库countrylanguage表的索引统计信息。
从这个视图的信息可以看出countrycode列行有一个单列的二级索引，与mysql.innodb_index_stats表示的有所不同，mysql.innodb_index_stats会将主键附加到非唯一的二级索引上，而statistics视图不包含这一信息。
这个视图中的信息也可以通过show index 语句获得。
5.2. Information_schema.innodb_tablestats
此视图是基于InnoDB的内部内存结构而形成的，不包含索引基数信息，但包含了自上次分析表以来，索引统计信息的状态和修改次数等方面的内容。该视图包含InnoDB表的所有信息无论使用的是持久统计信息还是临时统计信息。
列名 | 数据类型 | 描述 |
TABLE_ID | bigint unsigned | 内部的InnoDB表ID，可在information库的innodb_tables上查找表 |
NAME | varchar(193) | 表名。格式为<schema>/<tablename> |
STATS_INITIALIZED | varchar(193) | 表的内存结构是否已被初始化。表示索引统计信息和相关的元数据是否加载到内存中。可能取值为Uninitialized和Initialized。 当执行flush table和analyze table 命令时，状态会变为Uninitialized。 |
NUM_ROWS | bigint unsigned | 表中行数的估计值 |
CLUST_INDEX_SIZE | bigint unsigned | 聚簇索引中的页数 |
OTHER_INDEX_SIZE | bigint unsigned | 二级索引的页数之和 |
MODIFIED_COUNTER | bigint unsigned | 自上次索引统计信息更新以来，使用DML语句更改的行数。仅当dml语句影响到索引时，该计数器才会增加。 |
AUTOINC | bigint unsigned | 自动增量计数器的值。如果该表没有自动递增列，该值为0。 |
REF_COUNT | int | 元数据被引用的次数。当引用计数器的值为0时，InnoDB会将相应的元数据逐出，并且初始化状态返回Uninitialized。 |
查询示例：查询yu库中a表的统计信息。自上次收集统计信息以来更新了1行。对于Uninitialized的表，在此视图中num_rows为0， 对表插入一条数据，即使不重新收集统计信息，num_rows会加1。
5.3. Information_shcema.tables
该视图包含了对行数的估计值，以及数据和索引的大小。
视图中各列信息如下：
列名 | 数据类型 | 描述 |
TABLE_CATALOG | varchar(64) | 表所属目录，该值始终为def |
TABLE_SCHEMA | varchar(64) | 表所属方案 |
TABLE_NAME | varchar(64) | 表名 |
TABLE_TYPE | enum('BASE TABLE','VIEW','SYSTEM VIEW') | 表的种类。System view指的是information库中的视图等由MySQL创建的内容。 |
ENGINE | varchar(64) | 表使用的存储引擎 |
VERSION | int | 在mysql8中不使用该列。因为它与.frm文件有关。现在被硬编码为10 |
ROW_FORMAT | enum('Fixed','Dynamic','Compressed','Redundant','Compact','Paged') | 表使用的行格式。 |
TABLE_ROWS | bigint unsigned | 估计的行数。对于InnoDB表，来自聚簇索引的基数。 |
AVG_ROW_LENGTH | bigint unsigned | 估计的数据长度除以/估计的行数 |
DATA_LENGTH | bigint unsigned | 数据的估计大小。就是聚簇索引中的页数*页大小。 |
MAX_DATA_LENGTH | bigint unsigned | 允许的最大数据长度。InnoDB不使用此值。 |
INDEX_LENGTH | bigint unsigned | 二级索引的估计大小。就是非聚簇索引中的页总数*页大小 |
DATA_FREE | bigint unsigned | 该表所属的表空间中的可用空间的估计值。 |
AUTO_INCREMENT | bigint unsigned | 该表使用的自动递增计数器的下一个值。 |
CREATE_TIME | timestamp | 表创建的时间 |
UPDATE_TIME | datetime | 表空间文件上次更新的时间。 |
CHECK_TIME | datetime | 上次检查表（check table）的时间。对于分区表，InnoDB始终返回null。 |
TABLE_COLLATION | varchar(64) | 对使用字符串的直方图 |
CHECKSUM | bigint | 表的校验和。InnoDB不使用该值，为NULL |
CREATE_OPTIONS | varchar(256) | 表选项。如stats_persistent,stats_auto_recalc |
TABLE_COMMENT | text | 创建表时指定的注释 |
下面示例查询yu库中一个表的tables视图信息。
SHOW TABLE STATUS语句
Show table status语句的信息与视图tables的信息相似。默认从当前database中查询指定的表，也可以显示指定从哪个schema中查询指定的表。不指定表，默认查询当前database下所有的表。也可以查询指定方案下的所有表。
mysql> show table status from yu like 'a'\G
mysql> show table status from yu\G
mysql> show table status like 'a'\G
mysql> show table status\G
