(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秒)
(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秒)
回答来自于问答智囊团成员:王文安@DBA
实际上是MySQL 8.0为了提高information_schema的查询效率,将表和统计信息放入内部的统计信息缓存起来,缓存时间由参数information_schema_stats_expiry决定,至少为86400s;如果想获取最新的统计信息,可以通过如下两种方式:
(1)分析表进行表分析
(2)设置information_schema_stats_expiry = 0
那么统计信息不准确,会带来哪些影响呢?或者会影响执行计划呢?然后我们再次进行测试
执行如下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秒)
再次执行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);这可能会导致用户查询上方视图时,无法获取最新,准确的统计信息,但并不会影响执行计划的选择。
实际上是MySQL 8.0为了提高information_schema的查询效率,将表和统计信息放入内部的统计信息缓存起来,缓存时间由参数information_schema_stats_expiry决定,至少为86400s;如果想获取最新的统计信息,可以通过如下两种方式:
(1)分析表进行表分析
(2)设置information_schema_stats_expiry = 0