MySQL 8.0统计信息不准确的原因
前言
不管是Oracle还是MySQL,新版本推出的新特性,一方面给产品带来功能、性能、用户体验等方面的提升,另一方面也可能会带来一些问题,如代码bug、客户使用方法不正确引发问题等等。
案例分享
MySQL 5.7下的场景
(1)首先,创建两张表,并插入数据
mysql> select version();+------------+| version() |+------------+| 5.7.30-log |+------------+1 row in set (0.00 sec)mysql> show create table test\G*************************** 1. row *************************** Table: testCreate 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=10000001 row in set (0.00 sec)mysql> show create table sbtest1\G*************************** 1. row *************************** Table: sbtest1Create 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=10000001 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)
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)
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)
(1)接下来我们看看8.0下的情况吧,同样地,我们创建两张表,并插入相同记录
mysql> select version();+-----------+| version() |+-----------+| 8.0.20 |+-----------+1 row in set (0.00 sec)mysql> show create table test\G*************************** 1. row *************************** Table: testCreate 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=10000001 row in set (0.00 sec)mysql> show create table sbtest1\G*************************** 1. row *************************** Table: sbtest1Create 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=10000001 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)
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 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作为被驱动表(主键关联),执行效率很快
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)
再次执行SQL,查看执行计划,走的也是NLJ,相对小表sbtest1作为驱动表,大表test作为被驱动表,也是正确的执行计划
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为了提高information_schema的查询效率,会将视图tables和statistics里面的统计信息缓存起来,缓存过期时间由参数information_schema_stats_expiry决定(建议设置该参数值为0);这可能会导致用户查询相应视图时,无法获取最新、准确的统计信息,但并不会影响执行计划的选择。
更多相关文章
- Android市场官方的统计信息
- Android市场官方的统计信息
- Android市场官方的统计信息
- TextView碉堡了!android源代码的一些统计信息!
- 藏在表分区统计信息背后的小秘密
- 11gR2 新特性--待定的统计信息(Pending Statistic)
- 我的应用所需权限都已全部打开,定位数据依然不准确?
- Oracle收集统计信息
- 【DB笔试面试628】Oracle的统计信息包括哪几种类型?