11-23 09:38 阅读 60

关注

探索一个MySQL删除多条记录时造成阻塞的奇怪现象

为描述方便，如无特殊指定，下面提到的“数据库”、"MySQL"，均指使用了InnoDB存储引擎的MySQL数据库，测试使用的MySQL版本是 5.7，数据库隔离级别为默认的可重复读（Repeated Read）级别

问题背景

在 记一次由delete语句导致的MySQL死锁分析 文章中还遗留了一些未解决的问题。先把表的DDL再贴一下：

CREATE TABLE `session` (     `id`                    binary(16) NOT NULL,     `code`                  varchar(64)         DEFAULT NULL,     `topic`                 varchar(255)        DEFAULT NULL,     PRIMARY KEY (`id`),     KEY `code_idx` (`code`) ) ENGINE = InnoDB   DEFAULT CHARSET = utf8mb4; insert into `session`(id, code, topic) values (0x338A697F27EC4EFC832BBFC109D34505, '84709989', '84709989'), (0x3B3DE6A8C48940239907F7D4FAFB5418, '32700709', '32700709'), (0x4567EC174BAD41A5A7B6587B7FDB6DFD, '34106954', '34106954'), (0x72A0BF611835464B9F1F13E3A7EE9923, '50241728', '50241728'), (0x8B2845485D584A3EB38B6D7143AF0979, '23785170', '23785170'), (0xB41D1ACB485A4E599A687E4AB1C36648, '16264951', '16264951'), (0xB626729F75AA42CEBA86C07880F9A3DA, '38992827', '38992827'), (0xCF06F6E7AFC247BC914242E7F7D93FE3, '44151024', '44151024'), (0xDB71BC0D218A46678FE9B64ABBCE7212, '98073250', '98073250'); 复制代码

在调试死锁问题的时候，发现执行第3步的删除语句时，会导致T1阻塞

一开始我以为是由于id的顺序问题，当执行到删除id为 0x72A0BF611835464B9F1F13E3A7EE9923 的记录时，会找到刚插入的 0x965b1face74948039ab0dd8da6daa71d 记录而导致阻塞。但是，当我执行了如下SQL时，发现并不会造成阻塞：

delete from session where id in (x'72A0BF611835464B9F1F13E3A7EE9923'); 复制代码

另外，我又尝试了以下的SQL，发现也不会阻塞。

delete from session where id in (x'338A697F27EC4EFC832BBFC109D34505'); delete from session where id in (x'338A697F27EC4EFC832BBFC109D34505', x'3B3DE6A8C48940239907F7D4FAFB5418'); delete from session where id in (x'338A697F27EC4EFC832BBFC109D34505', x'3B3DE6A8C48940239907F7D4FAFB5418', x'4567EC174BAD41A5A7B6587B7FDB6DFD'); 复制代码

为什么删除4条记录的时候会阻塞，而删除3条记录及以下记录的时候，不会阻塞呢？这又不得不去调试一下MySQL源码来寻找答案了。

问题原因分析

通过删除4条记录和删除3条记录时的调试对比，发现这两条SQL执行的代码上并不一样。

执行如下SQL时：

delete from session where id in (x'338A697F27EC4EFC832BBFC109D34505', x'3B3DE6A8C48940239907F7D4FAFB5418', x'4567EC174BAD41A5A7B6587B7FDB6DFD'); 复制代码

读取记录的方法为 rr_quick函数，代码所在位置为 sql/records.cc的int rr_quick(READ_RECORD *info)函数

而在执行如下SQL时：

delete from session where id in (x'338A697F27EC4EFC832BBFC109D34505', x'3B3DE6A8C48940239907F7D4FAFB5418', x'4567EC174BAD41A5A7B6587B7FDB6DFD', x'72A0BF611835464B9F1F13E3A7EE9923'); 复制代码

读取记录的方法却是 rr_sequential 函数

看来是 由于rr_quick和rr_sequential这两方法在执行上的差异，导致了不同的读取记录的方式，从而导致了使用 rr_sequential 函数读取时阻塞。按这样推断，这两条SQL肯定有一些不同的地方，导致MySQL选择了不同的读取记录的方式。通过走查代码发现，决定选取何种读取记录的方式，是在这个地方决定的：

// sql/sql_delete.cc: mysql_delete函数 if (usable_index==MAX_KEY || qep_tab.quick())     error= init_read_record(&info, thd, NULL, &qep_tab, 1, 1, FALSE);   else     error= init_read_record_idx(&info, thd, table, 1, usable_index, reverse); 复制代码

而在sql/records.cc的 init_read_record函数的注释上，有这样一段说明：

All other accesses use either index access methods (rr_quick) or a full   table scan (rr_sequential).   rr_quick:   ---------     rr_quick uses one of the QUICK_SELECT classes in opt_range.cc to  perform an index scan. There are loads of functionality hidden  in these quick classes. It handles all index scans of various kinds. rr_sequential:   --------------     This is the most basic access method of a table using rnd_init,  ha_rnd_next and rnd_end. No indexes are used. bool init_read_record(READ_RECORD *info,THD *thd,                         TABLE *table, QEP_TAB *qep_tab,                       int use_record_cache, bool print_error,    bool disable_rr_cache) 复制代码

从注释上可以看出，如果MySQL在查询时判断会使用索引，那么会使用rr_quick 函数；如果MySQL在查询时判断不使用索引，那么就会使用 rr_sequential 函数。这样的话，我们可以从SQL的执行计划看到这两条语句之间是否有走索引：

explain delete from session where id in (   x'338A697F27EC4EFC832BBFC109D34505',   x'3B3DE6A8C48940239907F7D4FAFB5418',   x'4567EC174BAD41A5A7B6587B7FDB6DFD'   ); 复制代码

删除三条记录时，type=range，key=PRIMARY，说明走的是范围查询，使用了主键索引；

explain delete from session where id in (   x'338A697F27EC4EFC832BBFC109D34505',   x'3B3DE6A8C48940239907F7D4FAFB5418',   x'4567EC174BAD41A5A7B6587B7FDB6DFD',   x'72A0BF611835464B9F1F13E3A7EE9923'   ); 复制代码

而当删除四条记录时，type=ALL，key=NULL，说明此时MySQL并没有选择索引，而选择了全表扫描。

这也和刚刚在代码中看到的注释是对应的，在删除3条记录时，因为选择使用了索引，所以在查询记录时，用的是rr_quick 函数；而在删除4条记录时，因为选择了全表扫描，所以在查询记录时，使用的是rr_sequential函数。这也解释了为什么阻塞现象从第4条开始才发生，而在删除1条或3条记录时，不会有阻塞问题。

可问题又来了，在执行这两条SQL时，MySQL是如何选择索引的呢？

探索MySQL如何选择索引-基于成本的优化

注意到在上面的分析中，选择使用 rr_quick函数还是rr_sequential 函数，是通过 qep_tab.quick() 来判断的。qep 在MySQL源码中是 Query Execution Plan 的缩写，也就是查询的执行计划。也就是说，当执行计划认为现在执行的SQL是 quick 的，那么就会使用 rr_quick 函数。追溯 mysql_delete函数的源码，发现在这个函数里，有这么一段代码：

bool zero_rows= false; // True if it's sure that we'll find no rows   if (limit == 0)     zero_rows= true;   else if (conds != NULL)   {     key_map keys_to_use(key_map::ALL_BITS), needed_reg_dummy;     QUICK_SELECT_I *qck;     zero_rows= test_quick_select(thd, keys_to_use, 0, limit, safe_update,                                  ORDER::ORDER_NOT_RELEVANT, &qep_tab,                                  conds, &needed_reg_dummy, &qck,                                  qep_tab.table()->force_index) < 0;     qep_tab.set_quick(qck);   } 复制代码

在调用test_quick_select 函数之后，会把得到的 qck 结果设置到qep_tab中。也就是说，test_quick_select函数的执行结果决定了MySQL到底选择哪种读取记录的方式。我们知道，MySQL选择使用何种索引，是基于执行成本来进行优化的，而在test_quick_select函数里，正是在计算执行成本，选择成本最小的方式来执行。

先来看看在MySQL里，是如何表示执行成本的：

// sql/handler.h /**     Used to store optimizer cost estimates.     The class consists of PODs only: default operator=, copy constructor  and destructor are used. */ class Cost_estimate   {    private:     double io_cost;                               ///< cost of I/O operations     double cpu_cost;                              ///< cost of CPU operations     double import_cost;                           ///< cost of remote operations     double mem_cost;                              ///< memory used (bytes) public:     Cost_estimate() :       io_cost(0),       cpu_cost(0),       import_cost(0),       mem_cost(0)     {}        /// Returns sum of time-consuming costs, i.e., not counting memory cost     double total_cost() const  { return io_cost + cpu_cost + import_cost; }      // 以下省略其他函数   ... } 复制代码

从代码中可以看到，执行成本是通过计算 在IO上的花费、在CPU上的花费、在远程调用的花费、在内存上的花费 来决定的。mem_cost在统计总成本的时候并没有计算在内，而在delete操作的时候，也没有远程调用的操作，所以也可以暂时忽略。所以，在现在这个问题上，计算delete语句所花费的执行成本，其实就是关注在 IO和CPU上花费的成本即可。

另外，还需要关注的是，MySQL的一些成本常数定义：

// 代码定义在 sql/opt_costconstants.cc 中 /*     Values for cost constants defined as static const variables in the  Server_cost_constants class.  These are the default cost constant values that will be use if  the server administrator has not added new values in the server_cost  table.*/      // Default cost for evaluation of the query condition for a row.   const double Server_cost_constants::ROW_EVALUATE_COST= 0.2;      // Default cost for comparing row ids.   const double Server_cost_constants::KEY_COMPARE_COST= 0.1;     /*     Creating a Memory temporary table is by benchmark found to be as  costly as writing 10 rows into the table.*/   const double Server_cost_constants::MEMORY_TEMPTABLE_CREATE_COST= 2.0;      /*     Writing a row to or reading a row from a Memory temporary table is  equivalent to evaluating a row in the join engine.*/   const double Server_cost_constants::MEMORY_TEMPTABLE_ROW_COST= 0.2;      /*     Creating a MyISAM table is 20 times slower than creating a Memory table.*/   const double Server_cost_constants::DISK_TEMPTABLE_CREATE_COST= 40.0;      /*     Generating MyISAM rows sequentially is 2 times slower than  generating Memory rows, when number of rows is greater than  1000. However, we do not have benchmarks for very large tables, so  setting this factor conservatively to be 5 times slower (ie the cost  is 1.0).*/   const double Server_cost_constants::DISK_TEMPTABLE_ROW_COST= 1.0; /*     Values for cost constants defined as static const variables in the  SE_cost_constants class.  These are the default cost constant values that will be used if  the server administrator has not added new values in the engine_cost  table.*/      // The cost of reading a block from a main memory buffer pool   const double SE_cost_constants::MEMORY_BLOCK_READ_COST= 1.0;      // The cost of reading a block from an IO device (disk)   const double SE_cost_constants::IO_BLOCK_READ_COST= 1.0; 复制代码

接下来需要跟踪源码，在sql/opt_range.cc的test_quick_select函数打上断点。看看这两条SQL的执行成本到底是多少。test_quick_select函数的计算结果会保存在qck变量中，所以在调试的时候，关注qck的结果，就可以知道MySQL选择的是rr_quick还是rr_sequential函数了。

计算删除4条记录的SQL执行成本

先跟踪这条SQL：

delete from session where id in (   x'338A697F27EC4EFC832BBFC109D34505',   x'3B3DE6A8C48940239907F7D4FAFB5418',   x'4567EC174BAD41A5A7B6587B7FDB6DFD',   x'72A0BF611835464B9F1F13E3A7EE9923'   ); 复制代码

接着来到了这段关键代码上：

从代码逻辑上可以看出，先计算扫描时间 scan_time，以此作为花费在cpu上的执行成本：

double scan_time=     cost_model->row_evaluate_cost(static_cast<double>(records)) + 1;  cost_est.add_cpu(scan_time); 复制代码

然后从head->file->table_scan_cost()中得到一个cost_est对象，再粗暴地加上了 1.1 :

Cost_estimate cost_est= head->file->table_scan_cost();   cost_est.add_io(1.1);   复制代码

所以对于这条SQL，接下来只需要详细分析这两部分即可。

先看MySQL是如何计算scan_time的。进入row_evaluate_cost函数内部，发现逻辑非常简单：

只是把 要扫描的行数和 row_evaluate_cost常数相乘即可。在这个例子中，rows=5，row_evaluate_cost常数的定义从代码中可以得到：const double Server_cost_constants::ROW_EVALUATE_COST= 0.2;，所以这里的返回值应该是 5 * 0.2 = 1.0。

所以，scan_time=  cost_model->row_evaluate_cost(records) + 1 = 1.0 + 1 = 2.0。

从代码的调试结果上看也印证了scan_time的计算是2

接下来再计算花费在IO上的执行成本。在table_scan_cost函数里也有对IO成本的计算，并不只是单纯地创建了Cost_est对象：

Cost_estimate handler::table_scan_cost()   {     /*       This function returns a Cost_estimate object. The function should be    implemented in a way that allows the compiler to use "return value    optimization" to avoid creating the temporary object for the return value    and use of the copy constructor.  */     const double io_cost= scan_time() * table->cost_model()->page_read_cost(1.0);     Cost_estimate cost;     cost.add_io(io_cost);     return cost;   } 复制代码

io_cost的计算分为两部分：scan_time()函数的返回值、table->cost_model()->page_read_cost(1.0)的计算值。

先要计算 scan_time() 函数的值。在scan_time()函数的内部实际上返回了stat_clustered_index_size的值。而这个值的定义是聚簇索引使用的数据页的近似值，对于这条SQL，值为1。

// storage/innobase/include/dict0mem.h /** Approximate clustered index size in database pages. */   ib_uint64_t                         stat_clustered_index_size; 复制代码

接着再计算page_read_cost(1.0)函数的返回值，其函数内部逻辑为：

double Cost_model_table::page_read_cost(double pages) const   {     assert(m_initialized);     assert(pages >= 0.0);        const double in_mem= m_table->file->table_in_memory_estimate();        const double pages_in_mem= pages * in_mem;     const double pages_on_disk= pages - pages_in_mem;     assert(pages_on_disk >= 0.0);        const double cost= buffer_block_read_cost(pages_in_mem) +       io_block_read_cost(pages_on_disk);        return cost;   } 复制代码

这里计算的是读取一页数据所花费的成本，而这一页既有可能在内存中，也有可能在磁盘里。所以两者的成本相加即为花费在IO上的成本。从代码的逻辑上也很好理解：

 // 计算花费在内存IO上的成本，计算有多少页在内存里，再计算读取内存上的页要多少成本： const double pages_in_mem= pages * in_mem;  读取内存上的页花费的成本=buffer_block_read_cost(pages_in_mem) // 再计算花费在磁盘IO上的成本，计算有多少页在磁盘里，再计算读取磁盘上的页要多少成本： const double pages_on_disk= pages - pages_in_mem; // 在磁盘中的页数 = 总页数 - 在内存中的页数 读取磁盘上的页花费的成本=io_block_read_cost(pages_in_mem) // 所以，花费在IO上的总成本cost = 读取内存上的页花费的成本 +  读取磁盘上的页花费的成本;  复制代码

最终在Cost_est对象初始化阶段，计算的花费在IO上的成本是1，结果如图：

在源代码中得到Cost_est对象之后，还会粗暴地加上一个数值 1.1，所以最终计算得到的 cost_est的IO成本为 1+1.1=2.1

Cost_estimate cost_est= head->file->table_scan_cost();   cost_est.add_io(1.1);   复制代码

所以，对于这个SQL来说，目前计算得到的花费的总成本为 cost = cpu_cost + io_cost = 2 + 2.1 = 4.1

可这只是计算执行成本的第一步。从结果导向，我们可以看到，test_quick_select 函数的返回结果的判断是这样的：

best_trp是 Best Table Read Plan的缩写，从代码逻辑和注释上可以看到，在函数的最后，会根据records的数量来返回quick的值。而quick值的变更，取决于是否在之前的步骤里找到最优的Table Read Plan。在这条SQL中，best_trp为NULL，说明此时没有比一开始计算的成本更优的方案了，所以最终得到的quick值为初始值NULL。而MY_TEST是一个宏定义：

#define MY_TEST(a)             ((a) ? 1 : 0) 复制代码

当quick的值为NULL时，得到的结果为0，即test_quick_select 函数的返回值为0。而返回0意味着，这个SQL不能使用快速选择的方案，所以最终选择了全表扫描，这从test_quick_select函数的注释中可以得到不同的返回值的含义：

当quick值为NULL时，执行 qep_tab.set_quick(qck);后，qep_tab中的quick值也为NULL，所以MySQL选择了使用 rr_sequential函数来读取记录，而不使用rr_quick函数来读取记录。

综上，当删除4条记录时，此SQL的执行成本是4.1，MySQL判断没有成本更低的查询方案了，所以就使用了全表扫描的方式。

计算删除3条记录的SQL执行成本

在debug模式下运行MySQL，并且运行如下SQL：

delete from session where id in (   x'338A697F27EC4EFC832BBFC109D34505',   x'3B3DE6A8C48940239907F7D4FAFB5418',   x'4567EC174BAD41A5A7B6587B7FDB6DFD' ); 复制代码

进入 test_quick_select 函数，依然会计算一遍上述的Cost_est对象，因为上面已经分析过了，这里就直接贴出程序执行的结果：

这一步测算出来的成本是 cost_est = 2.1+2.2 = 4.3，虽然我们期望删除的是3条记录，但是MySQL的统计数据里面显示查询的记录数是6。

按照对上一条SQL的分析，函数最终是根据best_trp来决定quick值的，所以我们在这条SQL上也重点关注 best_trp这个变量。通过调试发现，程序最终会进入对范围查询的执行计划的计算：

此时的best_cost变量的值已经发生了变化，不再是一开始的初始值cost_est了。这一步测算出来的执行成本是 cost_est = 3.0 + 0.6 = 3.6，比上一步计算出来的4.3的成本要小。最终的quick值由best_trp->make_quick(...) 函数来构造出来。

最终test_quick_select函数的返回值为1，且quick变量有值。执行 qep_tab.set_quick(qck);后，qep_tab中的quick值非空，所以MySQL选择了使用 rr_quick函数来读取记录，而不使用rr_sequential函数来读取记录。

综上，当删除3条记录时，此SQL的执行成本是3.6，相比全表扫描花费的成本 4.3要低，所以就通过索引来进行查询，选择了rr_quick函数来进行读取。

为什么删除4条记录时不选择聚簇索引？

删除4条记录的时候，(range_trp= get_key_scans_params(&param, tree, FALSE, TRUE,  &best_cost)) 的计算结果是 range_trp=NULL，这导致了MySQL因为没有更好的执行计划，最终选择了全表扫描。可为什么删除4条记录时，range_trp的结果是NULL呢？再次执行删除4条记录的SQL，发现在执行get_key_scans_params函数之后，最终会执行到multi_range_read_info_const函数。因为删除语句使用的是 IN 的过滤条件，在MySQL的实现里，实际上是将 IN 列表里的元素作为单点区间来计算的。而下面这段代码正是对多个单点区间计算查询成本（注意这里的cost是入参，这里的计算会改变cost里的值）：

这里HA_MRR_INDEX_ONLY的宏定义如下，表示在多区间读的实现中不需要检索全部记录。MRR是 Multi Range Read的缩写。

/* MRR implementation doesn't have to retrieve full records */   #define HA_MRR_INDEX_ONLY 16 复制代码

在删除4条记录的情况下，*flags & HA_MRR_INDEX_ONLY的结果为false，所以调用到了read_cost函数

最终计算出来的 cost值如图所示：

在get_key_scans_params函数里，check_quick_select函数的计算结果，会影响后续成本的计算。在这个例子中，found_records的值是4，cost的总成本是 4+0.8=4.8，而read_cost的值是一开始计算的全表扫描的值，在上一部分已经介绍过，所以read_cost = 4.1。显然不成立。所以接下来对 key_to_read变量赋值的逻辑跳过。

最终，由于key_to_read变量为NULL，跳过了read_plan的实例化阶段，也意味着没有更好的执行计划了，所以最终返回了NULL。

删除3条记录的SQL和删除4条记录的SQL在get_key_scans_params函数上的分歧点，正是对多区间查询成本的计算差异上。从上面的结果可以看到，删除3条记录时，在多区间查询成本的计算结果是3.6，相比全表扫描花费的成本 4.3要低，所以选择使用聚簇索引；而在删除4条记录时，在多区间查询成本的计算结果是4.8，相比全表扫描花费的成本 4.1要高，所以就选择了全表扫描的方式。

总结

回到一开始提出的问题，在列出的执行流程中，为什么删除4条记录的时候会阻塞，而删除3条记录及以下记录的时候却不会阻塞。现在我可以拿出证据说明为什么会有这样的现象出现了。如果在之前遇到这样的问题，不调试源码，靠仅有的经验只能去猜测、推断，并不能很好地以理服人。通过调试源码，这种问题就变得十分直观，成本的计算结果是什么，MySQL如何做选择的，一目了然，而且对解释这类问题的现象也有了充分的依据。同时，在调试源码的过程中，也看到MySQL是怎么在代码中表示查询成本的，比起干巴巴地去理解网上对于查询成本的计算，来得更有意思，印象也更加深刻。虽然过程会比较曲折，源码相关的资料也比较少，但是对自己调试源码、掌握MySQL的原理还是很有帮助的。

作者：解bug的月月
链接：https://juejin.cn/post/7168869654444638245