修正MySQL表统计信息以获得更好的执行计划

案例学习：

表的统计信息错误导致优化器不能选择正确的执行计划

一个客户说，在没有代码和配置变更的情况下，一个查询发生了灾难性的性能下降。为简介起见，对本文中的数据进行了编辑和修改，以免信息泄露。该案例也获得客户的允许。

以下是执行计划和执行结果：

mysql> explain 
-> SELECT count(con.id)          ,
->        MAX(DAYNAME(con.date)) ,
->        now()                  ,
->        pcz.type,
->        pcz.c_c
-> FROM   con AS con
->        join orders o on con.o_id = o.id
->        JOIN pcz AS pcz ON o.d_p_c_z_id = pcz.id
->        left join c c on con.c_id = c.id
-> WHERE con.date = current_date() and pcz.type = "T_D"
-> GROUP BY con.date, pcz.c_c,  pcz.type;
+----+-------------+-------+------------+--------+-------------------+----------+---------+----------------------------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys     | key      | key_len | ref                        | rows | filtered | Extra                                        |
+----+-------------+-------+------------+--------+-------------------+----------+---------+----------------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | pcz   | NULL       | ALL    | PRIMARY           | NULL     | NULL    | NULL                       |  194 |    10.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | o     | NULL       | ref    | PRIMARY,dpcz_FK   | dpcz_FK  | 9       | custom.pcz.id              | 1642 |   100.00 | Using index                                  |
|  1 | SIMPLE      | con   | NULL       | ref    | FK_order,IDX_date | FK_order | 8       | custom.o.id                |    1 |     4.23 | Using where                                  |
|  1 | SIMPLE      | c     | NULL       | eq_ref | PRIMARY           | PRIMARY  | 8       | custom.con.c_id            |    1 |   100.00 | Using index                                  |
+----+-------------+-------+------------+--------+-------------------+----------+---------+----------------------------+------+----------+----------------------------------------------+

评估出的行数= (194x10%)x1642x(1x4.23%)=1347

深入研究一下查询，可以看到，条件con.date=current_date()。这个条件似乎是个能很好地过滤结果，但是MySQL优化器为什么跳过了使用索引呢？我们再来看看执行计划，通过强制使用在con.date列上的索引。执行计划输出结果是：

mysql> explain 
-> SELECT count(con.id)          ,
->        MAX(DAYNAME(con.date)) ,
->        now()                  ,
->        pcz.type,
->        pcz.c_c
-> FROM   con AS con USE INDEX(IDX_date)
->        join orders o on con.o_id = o.id
->        JOIN p_c_z AS pcz ON o.d_p_c_z_id = pcz.id
->        left join c c on con.c_id = c.id
-> WHERE con.date = current_date() and pcz.type = "T_D"
-> GROUP BY con.date, pcz.c_c,  pcz.type;
+----+-------------+-------+------------+--------+-----------------+----------+---------+---------------------------------------+--------+----------+---------------------------------+
| id | select_type | table | partitions | type   | possible_keys   | key      | key_len | ref                                   | rows   | filtered | Extra                           |
+----+-------------+-------+------------+--------+-----------------+----------+---------+---------------------------------------+--------+----------+---------------------------------+
|  1 | SIMPLE      | con   | NULL       | ref    | IDX_date        | IDX_date | 3       | const                                 | 110446 |   100.00 | Using temporary; Using filesort |
|  1 | SIMPLE      | c     | NULL       | eq_ref | PRIMARY         | PRIMARY  | 8       | custom.con.c_id                       |      1 |   100.00 | Using index                     |
|  1 | SIMPLE      | o     | NULL       | eq_ref | PRIMARY,dpcz_FK | PRIMARY  | 8       | custom.con.o_id                       |      1 |   100.00 | Using where                     |
|  1 | SIMPLE      | pcz   | NULL       | eq_ref | PRIMARY         | PRIMARY  | 8       | custom.o.d_p_c_z_id                   |      1 |    10.00 | Using where                     |
+----+-------------+-------+------------+--------+-----------------+----------+---------+---------------------------------------+--------+----------+---------------------------------+

强制使用索引后，估算出的记录数= 110446(110%)=11045 。

根据预估，1347是11045的十分之一，MySQL自然会选择第一个执行计划。

但是，真正运行时间与期望的响应时间相比，明显有些不对。第二个执行计划在预计的时间内返回了结果，第一个执行计划时间超过了预估时间。

进一步深入地检查表的结构和执行计划一，我们发现pcz表实际只有194行记录。但是，再看orders表通过索引orders.dpcz_FK，表orders会返回1642行记录，因为外键约束orders_ibfk_10(如下面所示)：这意味着表orders中的记录数应该是194*1642=318548，但是实际的记录数是32508150，是估算记录数318548的十倍。

CREATE TABLE `orders` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  ...
  `d_p_c_z_id` bigint(20) DEFAULT NULL,
  ...,
  PRIMARY KEY (`id`),
  ...
  KEY `dpcz_FK` (`d_p_c_z_id`),
  ...
  CONSTRAINT `orders_ibfk_10` FOREIGN KEY (`d_p_c_z_id`) REFERENCES `p_c_z` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ...
) ENGINE=InnoDB ....
mysql> select * from mysql.innodb_table_stats  where database_name='cutom' and table_name='orders';
+---------------+------------+---------------------+----------+----------------------+--------------------------+
| database_name | table_name | last_update         | n_rows   | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+----------+----------------------+--------------------------+
| custom        | orders     | 2022-03-03 21:58:18 | 32508150 |               349120 |                   697618 |
+---------------+------------+---------------------+----------+----------------------+--------------------------+

因此，我们怀疑表上关于orders.dpcz_FK的统计信息是不准的。可以通过以下的语句进行查询：

mysql> select * from mysql.innodb_index_stats where database_name='cutom' and table_name='orders' and index_name='dpcz_FK';
mysql> select * from mysql.innodb_index_stats  where database_name='custom' and table_name='orders' and index_name='dpcz_FK';
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| custom        | orders     | dpcz_FK    | 2022-02-28 12:35:30 | n_diff_pfx01 |      19498 |          50 | d_p_c_z_id                        |
| custom        | orders     | dpcz_FK    | 2022-02-28 12:35:30 | n_diff_pfx02 |   32283087 |         128 | d_p_c_z_id,id                     |
| custom        | orders     | dpcz_FK    | 2022-02-28 12:35:30 | n_leaf_pages |      55653 |        NULL | Number of leaf pages in the index |
| custom        | orders     | dpcz_FK    | 2022-02-28 12:35:30 | size         |      63864 |        NULL | Number of pages in the index      |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
mysql> select count(distinct d_p_c_z_id) from orders; 
+----------------------------------------------+
| count(distinct d_p_c_z_id)                   |
+----------------------------------------------+
|                                          195 |
+----------------------------------------------+

bingo！在表orders上，列d_p_c_z_id的cardinality是195。在表mysql.innodb_index_stats中，索引dpcz_FK的stat_value是19498，这个值是错误的，与实际的值相差很大。索引的stat_value被认为是列的cardinality。

使用正确的stat_value=195,从执行计划一的第二行可以得到实际的行数是32508150/195=166708，然后要被检查的行数是(194x10%)x166708x(1x4.23%)=136804。这个值比11045的十倍还大，11045是执行计划二预估的值，现在MySQL会选择执行计划二，而不需要强制走索引。

但是，为什么MySQL会统计出错误的统计信息？如何去修正呢？

为了回答这个问题，我们首先需要知道MySQL是如何估算统计信息，哪些参数会有影响。然后就可以很容易地找到解决路径。

InnoDB是如何估算表的统计信息的？

表的统计信息可以自动收集或显式收集。人们通常会开启(默认就是开启)innodb_stats_auto_recalc，在表中的数据变更一定比例后，自动重新收集持久性统计信息。当表中的记录超过10%被修改后，InnoDB会重新收集统计信息。我们也可以使用analyze table来显式地重新收集表的统计信息。

InnoDB使用采样技术(被熟知技术random dive)，采样索引的随机的页，来估算索引的cardinality。innodb_stats_persistent_sample_pages控制了采样的页数。可以参考：https://dev.mysql.com/doc/refman/5.7/en/innodb-persistent-stats.html

随机采样不是完全随机。采样的页是根据采样算法算出来的。最后，全部不同的键值，即索引的stat_value会根据公式：N * R * N_DIFF_AVG_LEAF

其中：

N：表示叶子页的数量

R：Level LA上不同的值的个数/Levl LA上所有的记录数。从root页开始，向下一层(level)遍历它的全部child page，直到遇到这样的一个leve：此level中至少包含A*10条不同的key，把此level标记为LA。

N_DIFF_AVG_LEAF:在所有的A叶子页上，不同键值的平均值

详细的算法：https://github.com/mysql/mysql-server/blob/6846e6b2f72931991cc9fd589dc9946ea2ab58c9/storage/innobase/dict/dict0stats.cc:

理解了上面的算法之后，当表的索引有了碎片之后，叶子页的数量，和level LA的不同键值与LA上的所有记录的比例边的越来越不准确，因此估算stat_value的值就会不准确。一旦发生碎片，除非参数inodb_stats_persistent_sample_pages被修改或者重构、显式地重新计算(手动执行analyze table)，否则就无法生成准确的stat_value值。

解决方案：如何纠正表的统计信息，避免统计信息再次错误

根据采样算法，只有两个因素会影响统计信息的估算：参数innodb_stats_persistent_sample_pages;索引是如何组织的

要想innodb获得正确的统计信息，要么增加innodb_stats_persistent_sample_pages;或者重构索引。重构索引的首要方式是重构表，例如执行一个alter操作。

让我们来看看下面的三个例子：

1.analyze table，不rebuild表，保持innodb_stats_persistent_sample_pages=128。将stat_value更新成了19582 ，接近原来不准确的值19498。索引中叶子页的数量从55653变成了55891，索引中的页的数量也从63864变成了64248：

mysql> show variables = 'innodb_stats_persistent_sample_pages';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| innodb_stats_persistent_sample_pages | 128   |
+--------------------------------------+-------+
mysql> analyze table orders;
+---------------+---------+----------+----------+
| Table         | Op      | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| custom.orders | analyze | status   | OK       |
+---------------+---------+----------+----------+
mysql> select * from mysql.innodb_index_stats  where database_name='custom' and table_name='orders' and index_name='dpcz_FK';
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| custom        | orders     | dpcz_FK    | 2022-03-03 21:58:18 | n_diff_pfx01 |      19582 |          50 | d_p_c_z_id                        |
| custom        | orders     | dpcz_FK    | 2022-03-03 21:58:18 | n_diff_pfx02 |   32425512 |         128 | d_p_c_z_id,id                     |
| custom        | orders     | dpcz_FK    | 2022-03-03 21:58:18 | n_leaf_pages |      55891 |        NULL | Number of leaf pages in the index |
| custom        | orders     | dpcz_FK    | 2022-03-03 21:58:18 | size         |      64248 |        NULL | Number of pages in the index      |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+

2.analyze table，不rebuild表，但是将innodb_stats_persistent_sample_pages从128增加到512。得到的stat_value的值为192，非常接近真正的cardinality，即195。索引中叶子页的数量有的很大的改变，从55653变成了44188。索引中页的数量也有很大的改边，从63864变成了50304。

mysql> show variables like '%persistent_sample%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| innodb_stats_persistent_sample_pages | 512   |
+--------------------------------------+-------+
mysql> analyze table orders;
+---------------+---------+----------+----------+
| Table         | Op      | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| custom.orders | analyze | status   | OK       |
+---------------+---------+----------+----------+
mysql> select * from mysql.innodb_index_stats  where database_name='custom' and table_name='orders' and index_name='dpcz_FK';
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| custom        | orders     | dpcz_FK    | 2022-03-09 06:54:29 | n_diff_pfx01 |        192 |         179 | d_p_c_z_id                        |
| custom        | orders     | dpcz_FK    | 2022-03-09 06:54:29 | n_diff_pfx02 |   31751321 |         512 | d_p_c_z_id,id                     |
| custom        | orders     | dpcz_FK    | 2022-03-09 06:54:29 | n_leaf_pages |      44188 |        NULL | Number of leaf pages in the index |
| custom        | orders     | dpcz_FK    | 2022-03-09 06:54:29 | size         |      50304 |        NULL | Number of pages in the index      |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+

3.rebuild表，保持innodb_stats_persistent_sample_pages=128,得到的stat_value值是187，接近准确的cardinality 195。索引中叶子页的数量改变很大，从55653变成了43733，索引中页的数量也从63864变成了50111。

mysql> show variables = 'innodb_stats_persistent_sample_pages';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| innodb_stats_persistent_sample_pages | 128   |
+--------------------------------------+-------+
mysql> alter table orders engine=innodb;
Query OK, 0 rows affected (11 min 16.37 sec)
mysql> select * from mysql.innodb_index_stats where database_name='custom' and table_name='orders' and index_name='dpcz_FK';
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| custom        | orders     | dpcz_FK    | 2022-03-07 18:44:43 | n_diff_pfx01 |        187 |         128 | d_p_c_z_id                        |
| custom        | orders     | dpcz_FK    | 2022-03-07 18:44:43 | n_diff_pfx02 |   31531493 |         128 | d_p_c_z_id,id                     |
| custom        | orders     | dpcz_FK    | 2022-03-07 18:44:43 | n_leaf_pages |      43733 |        NULL | Number of leaf pages in the index |
| custom        | orders     | dpcz_FK    | 2022-03-07 18:44:43 | size         |      50111 |        NULL | Number of pages in the index      |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+

表的统计信息正确之后，mysql优化器就会使用正确的执行计划：

mysql> explain
SELECT count(con.id)          ,
       MAX(DAYNAME(con.date)) ,
       now()                  ,
       pcz.type,
       pcz.c_c
FROM con AS con
         join orders o on con.order_id = o.id
         JOIN p_c_z AS pcz ON o.d_p_c_z_id = pcz.id
         left join c c on con.c_id = c.id
WHERE con.date = current_date()
and pcz.type = "T_D"
GROUP BY con.date, pcz.c_c,  pcz.type;
+----+-------------+-------+------------+--------+-------------------+----------+---------+---------------------------------------+------+----------+---------------------------------+
| id | select_type | table | partitions | type   | possible_keys     | key      | key_len | ref                                   | rows | filtered | Extra                           |
+----+-------------+-------+------------+--------+-------------------+----------+---------+---------------------------------------+------+----------+---------------------------------+
|  1 | SIMPLE      | con   | NULL       | ref    | FK_order,IDX_date | IDX_date | 3       | const                                 | 3074 |   100.00 | Using temporary; Using filesort |
|  1 | SIMPLE      | c     | NULL       | eq_ref | PRIMARY           | PRIMARY  | 8       | custom.con.c_id                       |    1 |   100.00 | Using index                     |
|  1 | SIMPLE      | o     | NULL       | eq_ref | PRIMARY,dpcz_FK   | PRIMARY  | 8       | custom.con.order_id                   |    1 |   100.00 | Using where                     |
|  1 | SIMPLE      | pcz   | NULL       | eq_ref | PRIMARY           | PRIMARY  | 8       | custom.o.d_p_c_z_id                   |    1 |    10.00 | Using where                     |
+----+-------------+-------+------------+--------+-------------------+----------+---------+---------------------------------------+------+----------+---------------------------------+
4 rows in set, 1 warning (0.01 sec)

结论

mysql优化器依赖表的统计信息的准确性来选择优化的执行计划。我们可以控制表的统计信息的准确性，通过修改innodb_stats_persistent_pages。

我们也可以强制重新统计表的统计信息，通过重构表，从而重构索引，这样可以增强表的统计信息的准确性。

重构表我们可以使用alter table或者pt工具来完成。