MySQL 8中关联多子表的DELETE及EXPLAIN查询性能异常问题

问题现象 #

从MySQL 5.7迁移至MySQL 8.0后，对关联大量带ON DELETE CASCADE外键子表的父表执行DELETE查询时性能极慢，甚至执行EXPLAIN DELETE操作也同样耗时。该问题可稳定复现，但在MySQL 5.7.30中无此现象。

问题复现示例 #

mysql> delete from parent_table_2 where id =1;
Query OK, 0 rows affected (4.45 sec)

mysql> explain delete from parent_table_2 where id =1;
+----+-------------+----------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table          | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | DELETE      | parent_table_2 | NULL       | range | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using where |
+----+-------------+----------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (3.28 sec)

测试环境构建 #

• 创建带主键的父表：

CREATE TABLE parent_table_2 (
    id INT PRIMARY KEY,
    name VARCHAR(255)
);

• 通过Shell脚本创建10000个带ON DELETE CASCADE外键的子表：

#!/bin/bash
MYSQL_USER="your_username"
MYSQL_PASSWORD="your_password"
MYSQL_DATABASE="your_database"

for ((i=1; i<=10000; i++)); do
    TABLE_NAME="table_child_FK_WITH_ON_DELETE_$i"
    QUERY="CREATE TABLE IF NOT EXISTS $TABLE_NAME (
        id INT PRIMARY KEY,
        column1 INT,
        column2 VARCHAR(255),
        FOREIGN KEY (column1) REFERENCES parent_table_2(id) ON DELETE CASCADE
    );"
    mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" "$MYSQL_DATABASE" -e "$QUERY"
done

问题细节 #

• 即使父表parent_table_2中无任何数据，执行DELETE查询仍耗时4.45秒，查询执行状态显示耗时集中在Opening tables和closing tables阶段：

mysql> delete from parent_table_2 where id =1;
Query OK, 0 rows affected (4.45 sec)
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000119 |
| Executing hook on transaction  | 0.000010 |
| starting                       | 0.000010 |
| checking permissions           | 0.000007 |
| Opening tables                 | 1.876346 |
| init                           | 0.000048 |
| System lock                    | 0.000092 |
| updating                       | 0.000035 |
| end                            | 0.000005 |
| query end                      | 0.000004 |
| waiting for handler commit     | 0.000065 |
| Waiting for semi-sync ACK from | 0.000511 |
| waiting for handler commit     | 0.000015 |
| closing tables                 | 2.571113 |
| freeing items                  | 0.000098 |
| logging slow query             | 0.000056 |
| cleaning up                    | 0.001471 |
+--------------------------------+----------+

• 已确认table_open_cache设置为524288，磁盘使用率极低，但问题依旧。
• 执行EXPLAIN DELETE时同样缓慢，耗时也集中在表操作阶段。
• 执行查询后Opened_table_definitions数值大幅增加，该现象在MySQL 5.7.30中未出现。

内容的提问来源于stack exchange，提问作者CrazyMinion