MySQL 8.0统计信息不准确?

  • 回答 (1)
  • 关注 (0)
  • 查看 (57)

MySQL 5.7下的场景

(1)首先,创建两张表,并插入数据

mysql >选择版本(); 
+  -  -  -  -  -  -  +  
|  版本()   |  
+  -  -  -  -  -  -  +  
|   5.7。30  -日志|  
+  -  -  -  -  -  -  +  
1行中集合(0.00秒)

的MySQL的>显示创建表测试\ G变变
**  **  **  **  **  **  **  **  **  **  **  **  **  *   1。排**  **  **  **  **  **  **  **  **  **  **  **  **  *
       表:测试
创建表:CREATE TABLE `test`(
   `id`   INT(10)无符号NOT NULL AUTO_INCREMENT,
   `k`   INT(10)无符号NOT NULL DEFAULT '0',
   `C`炭(120)NOT NULL DEFAULT '',
   `pad`   char(60)NOT NULL DEFAULT ”,
  主键(`id`),
  键`k_1`(`k`)
)ENGINE = InnoDB的AUTO_INCREMENT =  101个字符集= utf8mb4 MAX_ROWS =百万
1行中集合(0.00秒)

的MySQL的>显示创建表sbtest1 \ G变变
**  **  **  **  **  **  **  **  **  **  **  **  **  *   1。第**  **  **  **  **  **  **  **  **  **  **  **  **  **  *
       表:sbtest1
创建表: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)非空默认值”,
  主键(`id`),
  键`k_1`(`k`)
)ENGINE = InnoDB的AUTO_INCREMENT =  1000001个字符集= utf8mb4 MAX_ROWS =百万
1行中集合(0.00秒)

的MySQL >从测试中选择计数(*); +  -  -  -  -  -  +  |数(*)|  +  -  -  -  -  -  +  |  100  |  +  -  -  -  -  -  +  1行中集合(0.00秒)

  

       

  

mysql >从sbtest1选择计数(*); +  -  -  -  -  -  +  |数(*)|  +  -  -  -  -  -  +  |  1000000  |  +  -  -  -  -  -  +  1行中集合(0.14秒)

(2)查看两张表的统计信息,均比较准确

mysql >从表名=  '测试'的表中选择table_schema,table_name,table_rows ;  +  -  -  -  -  -  -  -  +  -  -  -  -  -  -  +  -  -  -  -  -  -  +  | table_schema | table_name | table_rows |  +  -  -  -  -  -  -  -  +


-  -  -  -  -  -  +  -  -  -  -  -  -  +  
|测试         |测试       |          100   |  
+  -  -  -  -  -  -  -  +  -  -  -  -  -  -  +  -  -  -  -  -  -  +  
1行中集合(0.00秒)

mysql >从表名=  'sbtest1'的表中选择table_schema,table_name,table_rows ;  +  -  -  -  -  -  -  -  +  -  -  -  -  -  -  +  -  -  -  -  -  -  +  | table_schema | table_name | table_rows |  +  -  -  -  -  -  -  -  +


-  -  -  -  -  -  +  -  -  -  -  -  -  +  
|测试         | sbtest1      |       947263   |  
+  -  -  -  -  -  -  -  +  -  -  -  -  -  -  +  -  -  -  -  -  -  +  
1行中集合(0.00秒)

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

的MySQL >从测试中选择计数(*); +  -  -  -  -  -  +  |数(*)|  +  -  -  -  -  -  +  |  10000100  |  +  -  -  -  -  -  +  1行中集合(1.50秒)

  

  

  

mysql >从表名=  '测试'的表中选择table_schema,table_name,table_rows ;  +  -  -  -  -  -  -  -  +  -  -  -  -  -  -  +  -  -  -  -  -  -  +  | table_schema | table_name | table_rows |  +  -  -  -  -  -  -  -  +


-  -  -  -  -  -  +  -  -  -  -  -  -  +  
|测试         |测试       |      9749036   |  
+  -  -  -  -  -  -  -  +  -  -  -  -  -  -  +  -  -  -  -  -  -  +  
1行中集合(0.00秒)

MySQL 8.0下的场景

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

mysql >选择版本(); 
+  -  -  -  -  -  -  +  
|  版本()|  
+  -  -  -  -  -  -  +  
|   8.0。20      |  
+  -  -  -  -  -  -  +  
1行中集合(0.00秒)

的MySQL的>显示创建表测试\ G变变
**  **  **  **  **  **  **  **  **  **  **  **  **  *   1。排**  **  **  **  **  **  **  **  **  **  **  **  **  *
       表:测试
创建表:CREATE TABLE `test`(
  `id` INT无符号NOT NULL AUTO_INCREMENT,
  `k` INT无符号NOT NULL DEFAULT '0' ,`C`
  炭(120)NOT NULL DEFAULT '' ,`pad`
  炭(60)非空默认值”,
  主键(`id`),
  键`k_1`(`k`)
)引擎= InnoDB AUTO_INCREMENT =  101时间的字符集= utf8mb4 COLLATE = utf8mb4_0900_ai_ci MAX_ROWS =百万
1行中集合(0.00秒)

的MySQL的>显示创建表sbtest1 \ G变变
**  **  **  **  **  **  **  **  **  **  **  **  **  *   1。第**  **  **  **  **  **  **  **  **  **  **  **  **  **  *
       表:sbtest1
创建表:CREATE TABLE`sbtest1`(
   `id` INT无符号NOT NULL AUTO_INCREMENT,
   `k` INT无符号NOT NULL缺省'0',
   `C`炭(120)NOT NULL DEFAULT ”,
   ``pad`炭(60)非空默认值”,
  主键(`id`),
  键`k_1`(`k`)
)引擎= InnoDB AUTO_INCREMENT =  1000001默认的字符集= utf8mb4 COLLATE = utf8mb4_0900_ai_ci MAX_ROWS =百万
1行中集合(0.00秒)

的MySQL >从测试中选择计数(*); +  -  -  -  -  -  +  |数(*)|  +  -  -  -  -  -  +  |  100  |  +  -  -  -  -  -  +  1行中集合(0.00秒)

  

       

  

mysql >从sbtest1选择计数(*); +  -  -  -  -  -  +  |数(*)|  +  -  -  -  -  -  +  |  1000000  |  +  -  -  -  -  -  +  1行中集合(0.02秒)

(2)查看两张表的统计信息,均比较准确

mysql >从表名=  '测试'的表中选择table_schema,table_name,table_rows ;  +  -  -  -  -  -  -  -  +  -  -  -  -  -  -  +  -  -  -  -  -  -  +  | TABLE_SCHEMA | TABLE_NAME | TABLE_ROWS |  +  -  -  -  -  -  -  -  +


-  -  -  -  -  -  +  -  -  -  -  -  -  +  
|测试         |测试       |          100   |  
+  -  -  -  -  -  -  -  +  -  -  -  -  -  -  +  -  -  -  -  -  -  +  
1行中集合(0.00秒)

mysql >从表名=  'sbtest1'的表中选择table_schema,table_name,table_rows ;  +  -  -  -  -  -  -  -  +  -  -  -  -  -  -  +  -  -  -  -  -  -  +  | TABLE_SCHEMA | TABLE_NAME | TABLE_ROWS |  +  -  -  -  -  -  -  -  +


-  -  -  -  -  -  +  -  -  -  -  -  -  +  
|测试         | sbtest1      |       947468   |  
+  -  -  -  -  -  -  -  +  -  -  -  -  -  -  +  -  -  -  -  -  -  +  
1行中集合(0.01秒)

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

mysql >从测试中选择count (* ) ;+ - - - - - + | 数(* )| + - - - - - + | 10000100 | + - - - - - + 1行中集合(0.33秒)

  

  

  

mysql >从table_name = 'test'的表中选择table_schema , table_name , table_rows ; + - - - - - - - + - - - - - - + - - - - - - + | TABLE_SCHEMA | TABLE_NAME | TABLE_ROWS | + - - - - - - - +


- - - - - - + - - - - - - + 
| 测试         | 测试       |         100  | 
+ - - - - - - - + - - - - - - + - - - - - - + 
1行中 集合 (0.00秒)
asiocasioc提问于
刺激回答于
推荐

回答来自于问答智囊团成员:王文安@DBA

专栏:https://cloud.tencent.com/developer/user/7566357

原因剖析

实际上是MySQL 8.0为了提高information_schema的查询效率,将表和统计信息放入内部的统计信息缓存起来,缓存时间由参数information_schema_stats_expiry决定,至少为86400s;如果想获取最新的统计信息,可以通过如下两种方式:

(1)分析表进行表分析

(2)设置information_schema_stats_expiry = 0

继续探索

那么统计信息不准确,会带来哪些影响呢?或者会影响执行计划呢?然后我们再次进行测试

测试1:表test记录数100,表sbtest1记录数100w

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

mysql >从测试中选择count (* ) ;+ - - - - - + | 数(* )| + - - - - - + | 100 | + - - - - - + 1行中集合(0.00秒)

  

       

  

mysql >从sbtest1选择计数(* ) ; + - - - - - + | 数(* )| + - - - - - + | 1000000 | + - - - - - + 1行中集合(0.02秒)

  

   

  

mysql >从table_name = 'test'的表中选择table_schema , table_name , table_rows ; + - - - - - - - + - - - - - - + - - - - - - + | TABLE_SCHEMA | TABLE_NAME | TABLE_ROWS | + - - - - - - - +


- - - - - - + - - - - - - + 
| 测试         | 测试       |         100  | 
+ - - - - - - - + - - - - - - + - - - - - - + 
1行中 集合 (0.00秒)

mysql >从table_name = 'sbtest1'的表中选择table_schema , table_name , table_rows ; + - - - - - - - + - - - - - - + - - - - - - + | TABLE_SCHEMA | TABLE_NAME | TABLE_ROWS | + - - - - - - - +


- - - - - - + - - - - - - + 
| 测试         | sbtest1     |      947468  | 
+ - - - - - - - + - - - - - - + - - - - - - + 
1行中 集合 (0.01秒)

mysql >选择t 。* 来自于t的测试t内部联接sbtest1 t1 。id = t1 。id在哪里t 。c = '08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977'和t1 。c = '08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977' ; 
+ - - + - - - - + - - - - - -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + 
| id | k       | c                                                                                                                        | 垫                                                         | 
+ - - + -- - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -- - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + 
|   1  |  501885  |  08566691963 - 88624912351 - 16662227201 - 46648573979 -64646226163 - 77505759394 - 75470094713 - 41097360717 - 15161106334 - 50535565977  |  63188288836 - 92351140030 - 06390587585 - 66802097351 - 49282961843  | 
+ - - + - - - - + - - - - - - - - - - - - - - - - - - - - -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - -- - - - - - - - - - - - - - - - + 
1行中 集合 (0.00秒)

mysql >说明选择t 。* 来自于t的测试t内部联接sbtest1 t1 。id = t1 。id在哪里t 。c = '08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977'和t1 。Ç = ' 08566691963 - 88624912351 - 16662227201 - 4664 
+ - - + - - - - - - - + - - -- + - - - - - - + - - - - + - - - - - - - - + - - - - - + - - - - - + - - - - - - + - - - + - - - - - + - - - - - -- + 
| id | select_type | 桌子| 隔板| 键入   | 可能的钥匙| 关键     | key_len | 参考       | 行| 过滤| 额外       | 
+ - - + - - - - - - - + - - - - + - - - - - - + -- - - + - - - - - - - - + - - - - - + - - - - - + - - - - - - - + - - - + - - - - - + - - - - - - - + 
|   1  | SIMPLE       | t      |NULL        | 全部    | 主要       | NULL     | NULL     | NULL       |   100  |     10.00  | 使用Where | 
|   1  | SIMPLE       | t1     | NULL        | eq_ref | 主要       | 主要|  4        | 测试。Ť 。id |     1  |     10.00  | 使用Where | 
+ - -+ - - - - - - - + - - - - + - - - - - - + - - - - + - - - - - - - - - + - - - - - + - - - - - + - - - - - - + - - - +- - - - - + - - - - - - - + 
2行中 集合, 1个 警告 (0.00秒)

测试2:表test记录数1000w左右,表sbtest1记录数100w

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

mysql >从测试中选择count (* ) ;+ - - - - - + | 数(* )| + - - - - - + | 10000100 | + - - - - - + 1行中集合(0.33秒)

  

  

  

mysql >从sbtest1选择计数(* ) ; + - - - - - + | 数(* )| + - - - - - + | 1000000 | + - - - - - + 1行中集合(0.02秒)

  

   

  

mysql >从table_name = 'test'的表中选择table_schema , table_name , table_rows ; + - - - - - - - + - - - - - - + - - - - - - + | TABLE_SCHEMA | TABLE_NAME | TABLE_ROWS | + - - - - - - - +


- - - - - - + - - - - - - + 
| 测试         | 测试       |         100  | 
+ - - - - - - - + - - - - - - + - - - - - - + 
1行中 集合 (0.00秒)

mysql >从table_name = 'sbtest1'的表中选择table_schema , table_name , table_rows ; + - - - - - - - + - - - - - - + - - - - - - + | TABLE_SCHEMA | TABLE_NAME | TABLE_ROWS | + - - - - - - - +


- - - - - - + - - - - - - + 
| 测试         | sbtest1     |      947468  | 
+ - - - - - - - + - - - - - - + - - - - - - + 
1行中 集合 (0.01秒)

mysql >选择t 。* 来自于t的测试t内部联接sbtest1 t1 。id = t1 。id在哪里t 。c = '08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977'和t1 。c = '08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977' ; 
+ - - + - - - - + - - - - - -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + 
| id | k       | c                                                                                                                        | 垫                                                         | 
+ - - + -- - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -- - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + 
|   1  |  501885  |  08566691963 - 88624912351 - 16662227201 - 46648573979 -64646226163 - 77505759394 - 75470094713 - 41097360717 - 15161106334 - 50535565977  |  63188288836 - 92351140030 - 06390587585 - 66802097351 - 49282961843  | 
+ - - + - - - - + - - - - - - - - - - - - - - - - - - - - -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - -- - - - - - - - - - - - - - - - + 
1行中 集合 (0.37秒)

mysql >说明选择t 。* 来自于t的测试t内部联接sbtest1 t1 。id = t1 。id在哪里t 。c = '08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977'和t1 。c = '08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977' ; 
+ - - + - - - - - - - + - - -- + - - - - - - + - - - - + - - - - - - - - + - - - - - + - - - - - + - - - - - - + - - - - + - - - - - + - - - - - -- - + 
| id | select_type | 桌子| 隔板| 键入   | 可能的钥匙| 关键     | key_len | 参考        | 行   | 过滤| 额外       | 
+ - - + - - - - - - - + - - - - + - - - - - - +- - - - + - - - - - - - - + - - - - - + - - - - - + - - - - - - - + - - - - + - - - - - + - - - - - - - + 
|   1  | SIMPLE       |t1     | NULL        | 全部    | 主要       | NULL     | NULL     | NULL        |  947468  |     10.00  | 使用Where | 
|   1  | SIMPLE       | t      | NULL        | eq_ref | 主要       | 主要|  4        | 测试。T1 。id |       1  |     10.00  | 使用Where |
+ - - + - - - - - - - + - - - - + - - - - - - + - - - - + - - - - - - - - + - - - - - + - - - - - + - - - - - - + - ---  -  -  +  -  -  -  -  -  +  -  -  -  -  -  -  -  +  
2行中集合,1个警告(0.01秒)

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

总结

MySQL 8.0为了提高information_schema的查询效率,将视图和表的内部信息统计缓存起来,缓存时间由参数information_schema_stats_expiry决定(建议设置该参数为0);这可能会导致用户查询上方视图时,无法获取最新,准确的统计信息,但并不会影响执行计划的选择。

扫码关注云+社区

领取腾讯云代金券