SQL语句偶尔出现全表扫描一个可能性

最近在和朋友讨论一个问题的时候，他发现在自动收集统计数据的时候偶尔语句会触发全表扫描，并且发了一个BUG给我，这个问题应该是5.7/8.0 都存在，测试主要使用较新的8036版本，如下，

觉得比较意思，以前也确实遇到过类似问题，简单分析了一下如下，因为涉及的东西较多，仅供参考。

一、环境建立

mysql> show create table test1.myteststat \G
*************************** 1. row ***************************
       Table: myteststat
Create Table: CREATE TABLE `myteststat` (
  `id` int NOT NULL,
  `a` int DEFAULT NULL,
  `b` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (2.99 sec)

首先插入一些数据，然后使用如下语句查看执行计划，

mysql> set eq_range_index_dive_limit=1;
Query OK, 0 rows affected (0.00 sec)
mysql>  desc select * from test1.myteststat where a=1;
+----+-------------+------------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | myteststat | NULL       | ref  | a             | a    | 5       | const |  492 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

注意这里的禁用了索引下探的功能，语句使用了统计数据判断执行计划，这里的rows通常就是表记录的行数/不同键值的个数，是是否能使用索引的重要依据。

二、执行计划中rows的来源

对于如上语句的rows(注意我们这里只讨论使用索引后的rows)其实际内部一般用rec_per_key来表示，也很容易理解也就是每个键值的行数。innodb主要是通过Table_ref::fetch_number_of_rows调用ha_innobase::info_low进行获取，其主要有2个来源，

A:table share中的share->key_info[key_nr]中的rec_per_key，每个索引都有一个key_info，这里的rec_per_key为B的缓存
B:innodb中通过调用innodb_rec_per_key从 (表的rows/不同键值的个数)得到rec_per_key，大概如下，

 rec_per_key = static_cast<rec_per_key_t>(records) / n_diff

但是我们在普通的语句执行的时候，获取的是并不会获取B的信息，这里有一个重要的标记就是HA_STATUS_CONST，如果这个flag设置则会从B获取rec_per_key，但是一般都没有设置，如下(ha_innobase::info_low中)，

    if (flag & HA_STATUS_CONST) {  //这里没有实际获取  一般不会加HA_STATUS_CONST标记收集常量数据
      ...
        const rec_per_key_t rec_per_key =
            innodb_rec_per_key(index, (ulint)j, index->table->stat_n_rows);
      ...

因此执行计划获取的rec_per_key和当前实际的统计数据可能有一定区别，体现出来出来就是rows的差距较大。

三、rec_per_key何时更新缓存

当前看更新主要有如下3个途径，下面3中途径中会实际的获取B中的rec_per_key，并且更新A中缓存的rec_per_key。

手动analyze table，如下

#0  ha_innobase::info_low (this=0x7fff59753670, flag=28, is_analyze=true) at /pxc/mysql-8.0.36/storage/innobase/handler/ha_innodb.cc:17373
#1  0x00000000049a6d83 in ha_innobase::analyze 
#2  0x00000000036a9286 in handler::ha_analyze 
#3  0x00000000039e3b43 in mysql_admin_table

table instance建立，如下

#0  ha_innobase::info_low (this=0x7fff78189310, flag=26, is_analyze=false) at /pxc/mysql-8.0.36/storage/innobase/handler/ha_innodb.cc:17373
#1  0x00000000049a4fff in ha_innobase::info
#2  0x000000000498b8c8 in ha_innobase::open 
#3  0x00000000036a1a87 in handler::ha_open 
#4  0x00000000034686ae in open_table_from_share

table share建立

注意这里的flag分别为28和26，而HA_STATUS_CONST为8，因此这两个flag都包含了HA_STATUS_CONST标记。

四、关于BUG

虽然前面的问题可能导致rows更新不及时，可能导致执行计划出现错误，但是这个BUG在特定的情况下一定会导致全表扫描，主要原因不管自动还是手动收集统计数据都需要先清空当前innodb统计数据的一些信息，并且在persist统计数据中并没有加相应的锁，重新收集其中就包含了

 index->stat_n_diff_key_vals[i] = 0;

如果这个时候刚好有session正在跑语句且由于table instance失效需要通过table share 建立table instance，因此需要将B的rec_per_key缓存到A中，则就会出现rows计算丢失，导致使用全表扫描，如果这是一个大表则影响比较严重。先看看当前的执行计划如下，

mysql> set eq_range_index_dive_limit=1;
Query OK, 0 rows affected (0.00 sec)

mysql> desc select * from test1.myteststat where a=1;
+----+-------------+------------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | myteststat | NULL       | ref  | a             | a    | 5       | const | 1517 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (2.57 sec)

可以看到使用的是索引a，然后如下处理，我们测试使用线程级别调试流程大概如下，

debug程序	session 1	session2
		flush tables;目的在于关闭所有的table instance和table share
断点放到dict_stats_empty_index
	执行analyze table test1.myteststat;
确定索引a的stat_n_diff_key_vals已经全部清理
		查看执行计划

主要的栈如下，

#0  dict_stats_empty_index (index=0xb0ed108) 
#1  0x0000000004efb3ec in dict_stats_analyze_index_low 
#2  0x0000000004efbf0c in dict_stats_analyze_index 
#3  0x0000000004efc027 in dict_stats_update_persistent 
#4  0x0000000004efe0e6 in dict_stats_update

在这种情况下查看到执行计划如下，

QQ_1757492024637.png

可以看到索引已经丢失。从测试来看随后执行计划可以自行恢复。当然如果下面两个参数设置得当比较容易避免这个问题

table_definition_cache
table_open_cache

五、其他

Old value = 1
New value = 38
ha_innobase::info_low (this=0x7fff78189310, flag=26, is_analyze=false) at /pxc/mysql-8.0.36/storage/innobase/handler/ha_innodb.cc:17373
17373         for (j = 0; j < key->actual_key_parts; j++) {
(gdb) bt
#0  ha_innobase::info_low (this=0x7fff78189310, flag=26, is_analyze=false) at /pxc/mysql-8.0.36/storage/innobase/handler/ha_innodb.cc:17373
#1  0x00000000049a4fff in ha_innobase::info (this=0x7fff78189310, flag=26) at /pxc/mysql-8.0.36/storage/innobase/handler/ha_innodb.cc:17482
#2  0x000000000498b8c8 in ha_innobase::open (this=0x7fff78189310, name=0x7fff5972dc58 "./test1/myteststat", open_flags=2, table_def=0x7fff596ff060)
    at /pxc/mysql-8.0.36/storage/innobase/handler/ha_innodb.cc:7578   info(HA_STATUS_NO_LOCK | HA_STATUS_VARIABLE | HA_STATUS_CONST); //收集统计数据
#3  0x00000000036a1a87 in handler::ha_open (this=0x7fff78189310, table_arg=0x7fff781709b0, name=0x7fff5972dc58 "./test1/myteststat", mode=2, test_if_locked=2, table_def=0x7fff596ff060)
    at /pxc/mysql-8.0.36/sql/handler.cc:2792
#4  0x00000000034686ae in open_table_from_share (thd=0x7fff7818c5f0, share=0x7fff5972d8a0, alias=0x7fff78170468 "myteststat", db_stat=39, prgflag=8, ha_open_flags=0, outparam=0x7fff781709b0, 
    is_create_table=false, table_def_param=0x7fff596ff060) at /pxc/mysql-8.0.36/sql/table.cc:3183
#5  0x00000000031c11f1 in open_table (thd=0x7fff7818c5f0, table_list=0x7fff78190590, ot_ctx=0x7fff5c6f3fe0) at /pxc/mysql-8.0.36/sql/sql_base.cc:3385
#6  0x00000000031c4caa in open_and_process_table (thd=0x7fff7818c5f0, lex=0x7fff7801f040, tables=0x7fff78190590, counter=0x7fff7801f098, prelocking_strategy=0x7fff5c6f4108, 
    has_prelocking_list=false, ot_ctx=0x7fff5c6f3fe0) at /pxc/mysql-8.0.36/sql/sql_base.cc:5052
#7  0x00000000031c6606 in open_tables (thd=0x7fff7818c5f0, start=0x7fff5c6f40b0, counter=0x7fff7801f098, flags=0, prelocking_strategy=0x7fff5c6f4108) at /pxc/mysql-8.0.36/sql/sql_base.cc:5854
#8  0x00000000031c801d in open_tables_for_query (thd=0x7fff7818c5f0, tables=0x7fff78190590, flags=0) at /pxc/mysql-8.0.36/sql/sql_base.cc:6736
#9  0x000000000335fad5 in Sql_cmd_dml::prepare (this=0x7fff78190fc0, thd=0x7fff7818c5f0) at /pxc/mysql-8.0.36/sql/sql_select.cc:540
#10 0x0000000003360558 in Sql_cmd_dml::execute (this=0x7fff78190fc0, thd=0x7fff7818c5f0) at /pxc/mysql-8.0.36/sql/sql_select.cc:718
#11 0x00000000032da7c7 in mysql_execute_command (thd=0x7fff7818c5f0, first_level=true) at /pxc/mysql-8.0.36/sql/sql_parse.cc:3688
#12 0x00000000032dfe21 in dispatch_sql_command (thd=0x7fff7818c5f0, parser_state=0x7fff5c6f5910) at /pxc/mysql-8.0.36/sql/sql_parse.cc:5370
#13 0x00000000032d5b61 in dispatch_command (thd=0x7fff7818c5f0, com_data=0x7fff5c6f6a00, command=COM_QUERY) at /pxc/mysql-8.0.36/sql/sql_parse.cc:2054
#14 0x00000000032d3aad in do_command (thd=0x7fff7818c5f0) at /pxc/mysql-8.0.36/sql/sql_parse.cc:1439
#15 0x00000000034f39d5 in handle_connection (arg=0xb01d410) at /pxc/mysql-8.0.36/sql/conn_handler/connection_handler_per_thread.cc:302
#16 0x000000000518ad94 in pfs_spawn_thread (arg=0xb0363a0) at /pxc/mysql-8.0.36/storage/perfschema/pfs.cc:3042
#17 0x00007ffff7bc6ea5 in start_thread () from /lib64/libpthread.so.0
#18 0x00007ffff6370b0d in clone () from /lib64/libc.so.6

Old value = 38
New value = 49
ha_innobase::info_low (this=0x7fff59753670, flag=28, is_analyze=true) at /pxc/mysql-8.0.36/storage/innobase/handler/ha_innodb.cc:17373
17373         for (j = 0; j < key->actual_key_parts; j++) {
(gdb) bt
#0  ha_innobase::info_low (this=0x7fff59753670, flag=28, is_analyze=true) at /pxc/mysql-8.0.36/storage/innobase/handler/ha_innodb.cc:17373
#1  0x00000000049a6d83 in ha_innobase::analyze (this=0x7fff59753670) at /pxc/mysql-8.0.36/storage/innobase/handler/ha_innodb.cc:18067   
int ret = info_low(HA_STATUS_TIME | HA_STATUS_CONST | HA_STATUS_VARIABLE, true /* this is ANALYZE */);
#2  0x00000000036a9286 in handler::ha_analyze (this=0x7fff59753670, thd=0x7fff5800cc10, check_opt=0x7fff58010430) at /pxc/mysql-8.0.36/sql/handler.cc:4847
#3  0x00000000039e3b43 in mysql_admin_table (thd=0x7fff5800cc10, tables=0x7fff5a62da10, check_opt=0x7fff58010430, operator_name=0x60cd6cf "analyze", lock_type=TL_READ_NO_INSERT, 
    open_for_modify=true, repair_table_use_frm=false, extra_open_options=0, prepare_func=0x0, operator_func=
    (int (handler::*)(handler * const, THD *, HA_CHECK_OPT *)) 0x36a91f8 <handler::ha_analyze(THD*, HA_CHECK_OPT*)>, check_view=0, alter_info=0x7fff5a62d868, 
    need_to_acquire_shared_backup_lock=true) at /pxc/mysql-8.0.36/sql/sql_admin.cc:1128
#4  0x00000000039e5d57 in Sql_cmd_analyze_table::execute (this=0x7fff5a62e070, thd=0x7fff5800cc10) at /pxc/mysql-8.0.36/sql/sql_admin.cc:1735
#5  0x00000000032ddb84 in mysql_execute_command (thd=0x7fff5800cc10, first_level=true) at /pxc/mysql-8.0.36/sql/sql_parse.cc:4721
#6  0x00000000032dfe21 in dispatch_sql_command (thd=0x7fff5800cc10, parser_state=0x7fff94171910) at /pxc/mysql-8.0.36/sql/sql_parse.cc:5370
#7  0x00000000032d5b61 in dispatch_command (thd=0x7fff5800cc10, com_data=0x7fff94172a00, command=COM_QUERY) at /pxc/mysql-8.0.36/sql/sql_parse.cc:2054
#8  0x00000000032d3aad in do_command (thd=0x7fff5800cc10) at /pxc/mysql-8.0.36/sql/sql_parse.cc:1439
#9  0x00000000034f39d5 in handle_connection (arg=0xb01d410) at /pxc/mysql-8.0.36/sql/conn_handler/connection_handler_per_thread.cc:302
#10 0x000000000518ad94 in pfs_spawn_thread (arg=0xb0363a0) at /pxc/mysql-8.0.36/storage/perfschema/pfs.cc:3042
#11 0x00007ffff7bc6ea5 in start_thread () from /lib64/libpthread.so.0
#12 0x00007ffff6370b0d in clone () from /lib64/libc.so.6
                                                                 
1、设置table为null 调到table share找到阶段   
          
2、查看ha_innobase::info_low 打开的flag 是否包含            
info(HA_STATUS_NO_LOCK | HA_STATUS_VARIABLE | HA_STATUS_CONST);



                                                                                                   
                                                                                                   
                                                                                                   
                                                                                                   
(gdb) p *this->table_share->key_info[1]->rec_per_key
$9 = 246                                            


10337/21 = 492




mysql>  desc select * from test1.myteststat where a=1;
+----+-------------+------------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | myteststat | NULL       | ref  | a             | a    | 5       | const |  492 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

这里的rows可以理解为 行数/索引distinct值，先来像test1.myteststat插入一些数据，这样行数实际是要变化的。


mysql>  desc select * from test1.myteststat where a=1;
+----+-------------+------------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | myteststat | NULL       | ref  | a             | a    | 5       | const |  492 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

这里rows并没有改变依旧为492，

这个时候我们看一下缓存值依旧为

(gdb)  p *this->table_share->key_info[1]->rec_per_key
$13 = 246

也就是246*2 等到了rows中的值，接下来通过强制打开从share 打开的table cache的方式强制同步一次后执行计划如下，

mysql>  desc select * from test1.myteststat where a=1;
+----+-------------+------------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | myteststat | NULL       | ref  | a             | a    | 5       | const |  492 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)


我的方法是在 
1、在函数table_open 中当从table cache找到table instance后，依旧手动设置为nullptr，这样就会走从table share 重新打开
设置table为null 调到table share找到阶段   
          
2、从table share建立table instance会同步缓存中的 per table key rec，并且ha_innobase::info_low 打开的flag 是否包含            
HA_STATUS_CONST 标记


如下
(gdb) set table=0
(gdb) c
Continuing.

Breakpoint 6, ha_innobase::info_low (this=0xc9acc90, flag=26, is_analyze=false) at /pxc/mysql-8.0.36/storage/innobase/handler/ha_innodb.cc:17168
17168     DBUG_TRACE;

这个时候flag 26 就带有了HA_STATUS_CONST 标记，再次查看执行计划

mysql> desc select * from test1.myteststat where a=1;
+----+-------------+------------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | myteststat | NULL       | ref  | a             | a    | 5       | const |  618 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (52.94 sec)


这个时候我们看一下缓存值依为

(gdb) p *this->table_share->key_info[1]->rec_per_key
$15 = 309

证明已经做了更新

那么什么情况下更新这个缓存呢？我们前面说过要更新说过要更新这个缓存就需要在获取统计数据的时候带有HA_STATUS_CONST 标记，当前知道只有如下3个方法
1、手动analyze table，如下
2、table instance建立
3、table share建立

但是这个问题带来的主要是由于rows更新不及时带来问题，但是索引一般还是可以使用的或者原有语句变成了全表扫描，但是可能持续一段时间，但是还有一种情况
就是导致偶尔出现全表扫描的情况随及又恢复正常