MySQL多表ORDER BY查询性能优化方案咨询
2026-7-6
表结构与索引
table1结构
mysql> describe table1; +---------+---------------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+---------------------+------+-----+-------------------+----------------+ | id | bigint(20) unsigned | NO | PRI | NULL | auto_increment | | field1 | varchar(255) | NO | UNI | NULL | | | date | timestamp | NO | MUL | CURRENT_TIMESTAMP | | | text | varchar(10000) | NO | | NULL | | | flag | tinyint(1) | YES | | 0 | | +---------+---------------------+------+-----+-------------------+----------------+
table1索引
mysql> show indexes from table1; +--------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | table1 | 0 | PRIMARY | 1 | id | A | 1420047 | NULL | NULL | | BTREE | | | | table1 | 0 | table1_field1_unique | 1 | field1 | A | 1420047 | NULL | NULL | | BTREE | | | | table1 | 1 | table1_date_idx | 1 | date | A | 1420047 | NULL | NULL | | BTREE | | | +--------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
table2结构
mysql> describe table2; +------------------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------------+---------------------+------+-----+---------+----------------+ | id | bigint(20) unsigned | NO | PRI | NULL | auto_increment | | table1_id | bigint(20) unsigned | NO | MUL | NULL | | | some1_id | bigint(20) unsigned | YES | MUL | NULL | | | some1_name | varchar(255) | YES | MUL | NULL | | | some2_id | bigint(20) unsigned | NO | MUL | NULL | | | some2_name | varchar(255) | NO | MUL | NULL | | | some3_name | varchar(255) | YES | MUL | NULL | | | some4_email | varchar(255) | YES | | NULL | | | some4_name | varchar(255) | YES | MUL | NULL | | | some4_place1_gift | varchar(255) | YES | | NULL | | | some4_place2_gift | varchar(255) | YES | | NULL | | | some4_place3_gift | varchar(255) | YES | | NULL | | +------------------------+---------------------+------+-----+---------+----------------+
table2索引
mysql> show indexes from table2; +--------------+------------+--------------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------------+------------+--------------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | table2 | 0 | PRIMARY | 1 | id | A | 462911 | NULL | NULL | | BTREE | | | | table2 | 1 | table2_table1_table1_id_foreign | 1 | table1_id | A | 462911 | NULL | NULL | | BTREE | | | | table2 | 1 | some4_name_idx | 1 | some4_name | A | 5645 | NULL | NULL | YES | BTREE | | | | table2 | 1 | some3_name_idx | 1 | some3_name | A | 3560 | NULL | NULL | YES | BTREE | | | | table2 | 1 | some2_id_idx | 1 | some2_id | A | 116 | NULL | NULL | | BTREE | | | | table2 | 1 | some1_id_idx | 1 | some1_id | A | 390 | NULL | NULL | YES | BTREE | | | | table2 | 1 | some1_name_idx | 1 | some1_name | A | 1727 | NULL | NULL | YES | BTREE | | | | table2 | 1 | some2_name_idx | 1 | some2_name | A | 221 | NULL | NULL | | BTREE | | | +--------------+------------+--------------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
查询语句
SELECT table1.id AS table1_id, table1.field1, table1.date, table1.text, table2.id AS table2_id, table2.some1_id, table2.some1_name, table2.some2_id, table2.some2_name, table2.some3_name, table2.some4_email, table2.some4_name, table2.some4_place1_gift, table2.some4_place2_gift, table2.some4_place3_gift FROM table2 INNER JOIN table1 ON table2.table1_id = table1.id WHERE table2.some1_name = 'Some1_Name_Example' AND table2.some2_name = 'Some2_Name_Example' AND table2.some3_name = 'Some3_Name_Example' AND ( table2.some2_id IN (1, 22, 975, 5981, 6127, 10861) OR table2.some1_id IN (2564, 4886, 12514, 12724, 13905, 15491, 16295, 18125, 22162, 20702, 20704, 21503) OR (table2.some2_id = 1277 AND table1.date < '2022-03-27 00:00:00') OR (table2.some2_id = 1678 AND table1.date < '2023-06-16 00:00:00') OR (table2.some2_id = 9519 AND table1.date < '2021-01-05 00:00:00') OR (table2.some1_id = 4648 AND table1.date < '2023-06-16 00:00:00') ) ORDER BY table1.date DESC, table2.id DESC LIMIT 200;
执行性能与计划对比
带ORDER BY table1.date DESC的情况
- 执行时间：1.04秒
- EXPLAIN结果：
+----+-------------+--------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+---------+----------------------------+--------+---------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+---------+----------------------------+--------+---------------------------------------------------------------------+ | 1 | SIMPLE | table2 | ref | table2_table1_id_foreign,some3_name_idx,some2_id_idx,some1_id_idx,some1_name_index,some2_name_index | some3_name_idx | 768 | const | 231455 | Using index condition; Using where; Using temporary; Using filesort | | 1 | SIMPLE | events | eq_ref | PRIMARY,table1_date_idx | PRIMARY | 8 | db.table2.table1_id | 1 | Using where | +----+-------------+--------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+---------+----------------------------+--------+---------------------------------------------------------------------+
注：EXPLAIN中events应为table1，属于笔误
仅保留ORDER BY table2.id DESC的情况
- 执行时间：0.12秒
- EXPLAIN结果：
+----+-------------+--------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+---------+----------------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+---------+----------------------------+--------+-------------+ | 1 | SIMPLE | table2 | ref | table2_table1_id_foreign,some3_name_idx,some2_id_idx,some1_id_idx,some1_name_index,some2_name_index | some3_name_idx | 768 | const | 231455 | Using where | | 1 | SIMPLE | events | eq_ref | PRIMARY,table1_date_idx | PRIMARY | 8 | db.table2.table1_id | 1 | Using where | +----+-------------+--------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+---------+----------------------------+--------+-------------+
注：EXPLAIN中events应为table1，属于笔误
优化建议
1. 构建覆盖索引消除排序开销
当前查询因table1.date的索引无法和关联条件结合，导致MySQL需对23万+条结果做临时表排序。可尝试两种方案：
方案A：反向关联+复合索引
调整查询顺序从table1开始，利用table1_date_idx的有序性，同时给table2创建覆盖过滤条件与关联字段的复合索引：
-- 调整后的查询语句 SELECT table1.id AS table1_id, table1.field1, table1.date, table1.text, table2.id AS table2_id, table2.some1_id, table2.some1_name, table2.some2_id, table2.some2_name, table2.some3_name, table2.some4_email, table2.some4_name, table2.some4_place1_gift, table2.some4_place2_gift, table2.some4_place3_gift FROM table1 INNER JOIN table2 ON table1.id = table2.table1_id WHERE table2.some1_name = 'Some1_Name_Example' AND table2.some2_name = 'Some2_Name_Example' AND table2.some3_name = 'Some3_Name_Example' AND ( table2.some2_id IN (1, 22, 975, 5981, 6127, 10861) OR table2.some1_id IN (2564, 4886, 12514, 12724, 13905, 15491, 16295, 18125, 22162, 20702, 20704, 21503) OR (table2.some2_id = 1277 AND table1.date < '2022-03-27 00:00:00') OR (table2.some2_id = 1678 AND table1.date < '2023-06-16 00:00:00') OR (table2.some2_id = 9519 AND table1.date < '2021-01-05 00:00:00') OR (table2.some1_id = 4648 AND table1.date < '2023-06-16 00:00:00') ) ORDER BY table1.date DESC, table2.id DESC LIMIT 200; -- 创建table2的复合索引 CREATE INDEX idx_table2_filter_join ON table2 (some3_name, some1_name, some2_name, table1_id, some2_id, some1_id);
该索引可让MySQL快速筛选符合条件的table2记录，无需回表即可获取关联table1的字段。
方案B：冗余字段+排序索引
若业务允许，将table1.date冗余到table2，并创建包含过滤、排序字段的复合索引：
-- 添加冗余字段 ALTER TABLE table2 ADD COLUMN table1_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP; -- 同步历史数据 UPDATE table2 t2 JOIN table1 t1 ON t2.table1_id = t1.id SET t2.table1_date = t1.date; -- 创建排序用复合索引 CREATE INDEX idx_table2_filter_sort ON table2 (some3_name, some1_name, some2_name, table1_date DESC, id DESC);
之后修改查询语句，直接用table2.table1_date排序，MySQL可利用索引有序性返回结果，避免临时表与文件排序。
2. 拆分OR条件为UNION ALL
原查询的OR会降低索引效率，可将每个OR分支拆分为独立查询，用UNION ALL合并后再排序：
SELECT * FROM ( -- 分支1：some2_id IN 条件 SELECT table1.id AS table1_id, table1.field1, table1.date, table1.text, table2.id AS table2_id, table2.some1_id, table2.some1_name, table2.some2_id, table2.some2_name, table2.some3_name, table2.some4_email, table2.some4_name, table2.some4_place1_gift, table2.some4_place2_gift, table2.some4_place3_gift FROM table2 INNER JOIN table1 ON table2.table1_id = table1.id WHERE table2.some1_name = 'Some1_Name_Example' AND table2.some2_name = 'Some2_Name_Example' AND table2.some3_name = 'Some3_Name_Example' AND table2.some2_id IN (1, 22, 975, 5981, 6127, 10861) UNION ALL -- 分支2：some1_id IN 条件 SELECT table1.id AS table1_id, table1.field1, table1.date, table1.text, table2.id AS table2_id, table2.some1_id, table2.some1_name, table2.some2_id, table2.some2_name, table2.some3_name, table2.some4_email, table2.some4_name, table2.some4_place1_gift, table2.some4_place2_gift, table2.some4_place3_gift FROM table2 INNER JOIN table1 ON table2.table1_id = table1.id WHERE table2.some1_name = 'Some1_Name_Example' AND table2.some2_name = '
相关产品推荐
相关产品推荐
