mysql - 优化 DELETE FROM mdl_grade_items_history

Question

上周我安装了一些额外的数据库监控，后来发现我们的数据库负载的 30% 花费在单个表的单个查询上(目前该表大约有 600 万个查询)行):

delete FROM mdl_grade_items_history WHERE timemodified < ?

在测试环境中，我尝试进行一些架构更改:

在此查询上运行 EXPLAIN 表示每次运行此查询时都会完成全表扫描。

EXPLAIN DELETE FROM mdl_grade_items_history WHERE timemodified < '1490528405';
+----+-------------+-------------------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table                   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------------------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | DELETE      | mdl_grade_items_history | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 140784 |   100.00 | Using where |
+----+-------------+-------------------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set (0.00 sec)

检查EXPLAIN(非常相似)SELECT 查询会显示类似的情况。

EXPLAIN SELECT id FROM mdl_grade_items_history WHERE timemodified < '1490528405';
+----+-------------+-------------------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table                   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------------------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | mdl_grade_items_history | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 140784 |    33.33 | Using where |
+----+-------------+-------------------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

检查表定义，timemodified上似乎没有索引

SHOW INDEX FROM mdl_grade_items_history;
+-------------------------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                   | Non_unique | Key_name                | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| mdl_grade_items_history |          0 | PRIMARY                 |            1 | id          | A         |      140784 |     NULL | NULL   |      | BTREE      |         |               |
| mdl_grade_items_history |          1 | mdl_graditemhist_act_ix |            1 | action      | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| mdl_grade_items_history |          1 | mdl_graditemhist_old_ix |            1 | oldid       | A         |       17170 |     NULL | NULL   |      | BTREE      |         |               |
| mdl_grade_items_history |          1 | mdl_graditemhist_cou_ix |            1 | courseid    | A         |        1065 |     NULL | NULL   | YES  | BTREE      |         |               |
| mdl_grade_items_history |          1 | mdl_graditemhist_cat_ix |            1 | categoryid  | A         |        2300 |     NULL | NULL   | YES  | BTREE      |         |               |
| mdl_grade_items_history |          1 | mdl_graditemhist_sca_ix |            1 | scaleid     | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |               |
| mdl_grade_items_history |          1 | mdl_graditemhist_out_ix |            1 | outcomeid   | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
| mdl_grade_items_history |          1 | mdl_graditemhist_log_ix |            1 | loggeduser  | A         |          30 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------------------------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
8 rows in set (0.00 sec)

所以我尝试添加一个(都通过CREATE INDEX和ALTER TABLE .. ADD INDEX)

CREATE INDEX `mdl_gradeitemhist_tim_ix` ON `mdl_grade_items_history` (`timemodified`);
ALTER TABLE `mdl_grade_items_history` ADD INDEX `mdl_gradeitemhist_tim_ix` (`timemodified`);

在这两种情况下，SELECT 查询都会受到影响(请注意类型的变化)

EXPLAIN `SELECT` id FROM mdl_grade_items_history WHERE timemodified < '1490528405';
+----+-------------+-------------------------+------------+-------+--------------------------+--------------------------+---------+------+-------+----------+--------------------------+
| id | select_type | table                   | partitions | type  | possible_keys            | key                      | key_len | ref  | rows  | filtered | Extra                    |
+----+-------------+-------------------------+------------+-------+--------------------------+--------------------------+---------+------+-------+----------+--------------------------+
|  1 | SIMPLE      | mdl_grade_items_history | NULL       | range | mdl_gradeitemhist_tim_ix | mdl_gradeitemhist_tim_ix | 9       | NULL | 70206 |   100.00 | Using where; Using index |
+----+-------------+-------------------------+------------+-------+--------------------------+--------------------------+---------+------+-------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

但不是 DELETE 查询。

EXPLAIN DELETE FROM mdl_grade_items_history WHERE timemodified < '1490528405';
+----+-------------+-------------------------+------------+------+--------------------------+------+---------+------+--------+----------+-------------+
| id | select_type | table                   | partitions | type | possible_keys            | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------------------------+------------+------+--------------------------+------+---------+------+--------+----------+-------------+
|  1 | DELETE      | mdl_grade_items_history | NULL       | ALL  | mdl_gradeitemhist_tim_ix | NULL | NULL    | NULL | 140412 |   100.00 | Using where |
+----+-------------+-------------------------+------------+------+--------------------------+------+---------+------+--------+----------+-------------+
1 row in set (0.00 sec)

我做错了什么？我还能尝试什么？

Answer 1

• 几乎从不使用低基数索引(action、scaleid、outcomeid)。摆脱他们。
• 拥有大量单列索引是一个危险信号。请了解“复合”索引的威力和好处。 (与此处提到的选择/删除无关，但可能与其他查询相关。)
• 表上的额外索引会稍微减慢 INSERT 和 DELETE 的速度，因为索引(最终)需要更新。
• 如果索引列被修改，额外的索引会减慢UPDATE。
• CREATE INDEX 和 ALTER TABLE ADD INDEX 执行相同的操作；您现在可能有一个冗余索引。
• EXPLAIN 不同，因为 (1) SELECT 和 DELETE 执行不同的操作，并且 (2) EXPLAIN不是很复杂。
• 删除大量行需要付出很大的努力 - 请记住，在发生ROLLBACK的情况下，已删除的行会被挂起。只有在COMMIT之后才能真正删除行。 (使用 autocommit=ON 时，存在隐式 COMMIT。)

有关大量删除的提示:

• Deleting in chunks
• Using PARTITIONs for very efficient deletion of time series