MySQL 8.0统计信息不准确的原因

前言

不管是Oracle还是MySQL，新版本推出的新特性，一方面给产品带来功能、性能、用户体验等方面的提升，另一方面也可能会带来一些问题，如代码bug、客户使用方法不正确引发问题等等。

案例分享

MySQL 5.7下的场景

（1）首先，创建两张表，并插入数据

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49

mysql> select version(); + ------------+ | version() | + ------------+ | 5.7.30-log | + ------------+ 1 row in set (0.00 sec)   mysql> show create table test\G *************************** 1. row ***************************      Table : test Create Table : CREATE TABLE `test` (   `id` int (10) unsigned NOT NULL AUTO_INCREMENT,   `k` int (10) unsigned NOT NULL DEFAULT '0' ,   `c` char (120) NOT NULL DEFAULT '' ,   `pad` char (60) NOT NULL DEFAULT '' ,   PRIMARY KEY (`id`),   KEY `k_1` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 MAX_ROWS=1000000 1 row in set (0.00 sec)   mysql> show create table sbtest1\G *************************** 1. row ***************************      Table : sbtest1 Create Table : CREATE TABLE `sbtest1` (   `id` int (10) unsigned NOT NULL AUTO_INCREMENT,   `k` int (10) unsigned NOT NULL DEFAULT '0' ,   `c` char (120) NOT NULL DEFAULT '' ,   `pad` char (60) NOT NULL DEFAULT '' ,   PRIMARY KEY (`id`),   KEY `k_1` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 MAX_ROWS=1000000 1 row in set (0.00 sec)   mysql> select count (*) from test; + ----------+ | count (*) | + ----------+ |   100 | + ----------+ 1 row in set (0.00 sec)   mysql> select count (*) from sbtest1; + ----------+ | count (*) | + ----------+ | 1000000 | + ----------+ 1 row in set (0.14 sec)

（2）查看两张表的统计信息，均比较准确

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

mysql> select table_schema,table_name,table_rows from tables where table_name= 'test' ; + --------------+------------+------------+ | table_schema | table_name | table_rows | + --------------+------------+------------+ | test     | test    |    100 | + --------------+------------+------------+ 1 row in set (0.00 sec)   mysql> select table_schema,table_name,table_rows from tables where table_name= 'sbtest1' ; + --------------+------------+------------+ | table_schema | table_name | table_rows | + --------------+------------+------------+ | test     | sbtest1  |   947263 | + --------------+------------+------------+ 1 row in set (0.00 sec)

（3）我们持续往test表插入1000w条记录，并再次查看统计信息，还是相对准确的，因为在默认情况下，数据变化量超过10%，就会触发统计信息更新

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

mysql> select count (*) from test; + ----------+ | count (*) | + ----------+ | 10000100 | + ----------+ 1 row in set (1.50 sec)   mysql> select table_schema,table_name,table_rows from tables where table_name= 'test' ; + --------------+------------+------------+ | table_schema | table_name | table_rows | + --------------+------------+------------+ | test     | test    |  9749036 | + --------------+------------+------------+ 1 row in set (0.00 sec)

MySQL 8.0下的场景

（1）接下来我们看看8.0下的情况吧，同样地，我们创建两张表，并插入相同记录

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49

mysql> select version(); + -----------+ | version() | + -----------+ | 8.0.20  | + -----------+ 1 row in set (0.00 sec)   mysql> show create table test\G *************************** 1. row ***************************      Table : test Create Table : CREATE TABLE `test` (   `id` int unsigned NOT NULL AUTO_INCREMENT,   `k` int unsigned NOT NULL DEFAULT '0' ,   `c` char (120) NOT NULL DEFAULT '' ,   `pad` char (60) NOT NULL DEFAULT '' ,   PRIMARY KEY (`id`),   KEY `k_1` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE =utf8mb4_0900_ai_ci MAX_ROWS=1000000 1 row in set (0.00 sec)   mysql> show create table sbtest1\G *************************** 1. row ***************************      Table : sbtest1 Create Table : CREATE TABLE `sbtest1` (   `id` int unsigned NOT NULL AUTO_INCREMENT,   `k` int unsigned NOT NULL DEFAULT '0' ,   `c` char (120) NOT NULL DEFAULT '' ,   `pad` char (60) NOT NULL DEFAULT '' ,   PRIMARY KEY (`id`),   KEY `k_1` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE =utf8mb4_0900_ai_ci MAX_ROWS=1000000 1 row in set (0.00 sec)   mysql> select count (*) from test; + ----------+ | count (*) | + ----------+ |   100 | + ----------+ 1 row in set (0.00 sec)   mysql> select count (*) from sbtest1; + ----------+ | count (*) | + ----------+ | 1000000 | + ----------+ 1 row in set (0.02 sec)

（2）查看两张表的统计信息，均比较准确

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

mysql> select table_schema,table_name,table_rows from tables where table_name= 'test' ; + --------------+------------+------------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_ROWS | + --------------+------------+------------+ | test     | test    |    100 | + --------------+------------+------------+ 1 row in set (0.00 sec)   mysql> select table_schema,table_name,table_rows from tables where table_name= 'sbtest1' ; + --------------+------------+------------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_ROWS | + --------------+------------+------------+ | test     | sbtest1  |   947468 | + --------------+------------+------------+ 1 row in set (0.01 sec)

（3）同样地，我们持续往test表插入1000w条记录，并再次查看统计信息，发现table_rows显示还是100条，出现了较大偏差

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

mysql> select count (*) from test; + ----------+ | count (*) | + ----------+ | 10000100 | + ----------+ 1 row in set (0.33 sec)   mysql> select table_schema,table_name,table_rows from tables where table_name= 'test' ; + --------------+------------+------------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_ROWS | + --------------+------------+------------+ | test     | test    |    100 | + --------------+------------+------------+ 1 row in set (0.00 sec)

原因剖析

那么导致统计信息不准确的原因是什么呢？其实是MySQL 8.0为了提高information_schema的查询效率，将视图tables和statistics里面的统计信息缓存起来，缓存过期时间由参数information_schema_stats_expiry决定，默认为86400s；如果想获取最新的统计信息，可以通过如下两种方式：

（1）analyze table进行表分析

（2）设置information_schema_stats_expiry=0

继续探索

那么统计信息不准确，会带来哪些影响呢？是否会影响执行计划呢？接下来我们再次进行测试

测试1：表test记录数100，表sbtest1记录数100w

执行如下SQL，查看执行计划，走的是NLJ，小表test作为驱动表(全表扫描)，大表sbtest1作为被驱动表(主键关联)，执行效率很快

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48

mysql> select count (*) from test; + ----------+ | count (*) | + ----------+ |   100 | + ----------+ 1 row in set (0.00 sec)   mysql> select count (*) from sbtest1; + ----------+ | count (*) | + ----------+ | 1000000 | + ----------+ 1 row in set (0.02 sec)   mysql> select table_schema,table_name,table_rows from tables where table_name= 'test' ; + --------------+------------+------------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_ROWS | + --------------+------------+------------+ | test     | test    |    100 | + --------------+------------+------------+ 1 row in set (0.00 sec)   mysql> select table_schema,table_name,table_rows from tables where table_name= 'sbtest1' ; + --------------+------------+------------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_ROWS | + --------------+------------+------------+ | test     | sbtest1  |   947468 | + --------------+------------+------------+ 1 row in set (0.01 sec)   mysql> select t.* from test t inner join sbtest1 t1 on t.id=t1.id where t.c= '08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977' and t1.c= '08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977' ; + ----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ | id | k   | c                                                            | pad                             | + ----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ | 1 | 501885 | 08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977 | 63188288836-92351140030-06390587585-66802097351-49282961843 | + ----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ 1 row in set (0.00 sec)   mysql> explain select t.* from test t inner join sbtest1 t1 on t.id=t1.id where t.c= '08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977' and t1.c='08566691963-88624912351-16662227201-4664 + ----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+ | id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref    | rows | filtered | Extra    | + ----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+ | 1 | SIMPLE   | t   | NULL    | ALL  | PRIMARY    | NULL  | NULL  | NULL   | 100 |  10.00 | Using where | | 1 | SIMPLE   | t1  | NULL    | eq_ref | PRIMARY    | PRIMARY | 4    | test.t.id |  1 |  10.00 | Using where | + ----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+ 2 rows in set , 1 warning (0.00 sec)

测试2：表test记录数1000w左右，表sbtest1记录数100w

再次执行SQL，查看执行计划，走的也是NLJ，相对小表sbtest1作为驱动表，大表test作为被驱动表，也是正确的执行计划

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48

mysql> select count (*) from test; + ----------+ | count (*) | + ----------+ | 10000100 | + ----------+ 1 row in set (0.33 sec)   mysql> select count (*) from sbtest1; + ----------+ | count (*) | + ----------+ | 1000000 | + ----------+ 1 row in set (0.02 sec)   mysql> select table_schema,table_name,table_rows from tables where table_name= 'test' ; + --------------+------------+------------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_ROWS | + --------------+------------+------------+ | test     | test    |    100 | + --------------+------------+------------+ 1 row in set (0.00 sec)   mysql> select table_schema,table_name,table_rows from tables where table_name= 'sbtest1' ; + --------------+------------+------------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_ROWS | + --------------+------------+------------+ | test     | sbtest1  |   947468 | + --------------+------------+------------+ 1 row in set (0.01 sec)   mysql> select t.* from test t inner join sbtest1 t1 on t.id=t1.id where t.c= '08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977' and t1.c= '08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977' ; + ----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ | id | k   | c                                                            | pad                             | + ----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ | 1 | 501885 | 08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977 | 63188288836-92351140030-06390587585-66802097351-49282961843 | + ----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ 1 row in set (0.37 sec)   mysql> explain select t.* from test t inner join sbtest1 t1 on t.id=t1.id where t.c= '08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977' and t1.c= '08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977' ; + ----+-------------+-------+------------+--------+---------------+---------+---------+------------+--------+----------+-------------+ | id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref    | rows  | filtered | Extra    | + ----+-------------+-------+------------+--------+---------------+---------+---------+------------+--------+----------+-------------+ | 1 | SIMPLE   | t1  | NULL    | ALL  | PRIMARY    | NULL  | NULL  | NULL    | 947468 |  10.00 | Using where | | 1 | SIMPLE   | t   | NULL    | eq_ref | PRIMARY    | PRIMARY | 4    | test.t1.id |   1 |  10.00 | Using where | + ----+-------------+-------+------------+--------+---------------+---------+---------+------------+--------+----------+-------------+ 2 rows in set , 1 warning (0.01 sec)

为什么优化器没有选择错误的执行计划呢？之前文章也提过，MySQL 8.0是将元数据信息存放在mysql库下的数据字典表里，information_schema库只是提供相对方便的视图供用户查询，所以优化器在选择执行计划时，会从数据字典表中获取统计信息，生成正确的执行计划。

总结

MySQL 8.0为了提高information_schema的查询效率，会将视图tables和statistics里面的统计信息缓存起来，缓存过期时间由参数information_schema_stats_expiry决定(建议设置该参数值为0)；这可能会导致用户查询相应视图时，无法获取最新、准确的统计信息，但并不会影响执行计划的选择。

以上就是MySQL 8.0统计信息不准确的原因的详细内容，更多关于MySQL 8.0统计信息不准确的资料请关注服务器之家其它相关文章！

原文链接：https://cloud.tencent.com/developer/article/1684247