我有一个针对 Maridb 运行的查询，当我们以 ASC 顺序查询时，优化器检查较少数量的记录（r_rows）并在大约 500 毫秒内完成查询，但是当将顺序切换为 DESC 时，则相同的查询需要更多时间才能完成，r_rows 约为 227 万。
这是为什么呢？为什么 ASC/DESC 顺序会影响查询性能？
这是 SQL 查询
SELECT x_nuvo_eam_scheduled_m9e_e8s0.`sys_id`
FROM (
x_nuvo_eam_scheduled_m9e_e8s x_nuvo_eam_scheduled_m9e_e8s0
LEFT JOIN x_nuvo_eam_scheduled_m10s x_nuvo_eam_scheduled_maintena1 ON x_nuvo_eam_scheduled_m9e_e8s0.`scheduled_maintenance` = x_nuvo_eam_scheduled_maintena1.`sys_id`
)
WHERE x_nuvo_eam_scheduled_m9e_e8s0.`status` = 'Pending'
AND x_nuvo_eam_scheduled_m9e_e8s0.`scheduled_date` >= '2022-02-15 06:00:00'
AND x_nuvo_eam_scheduled_maintena1.`asset` IS NULL
ORDER BY x_nuvo_eam_scheduled_m9e_e8s0.`sys_created_on` ASC
limit 0, 100
下面2个MariaDB分析输出显示执行计划
ASC 有序查询完成约 503 毫秒
+---------+------------------------------------------------------------------------------------------------------------------------
| 1 result(s):
+---------+------------------------------------------------------------------------------------------------------------------------
| ANALYZE | {
| | "query_block": {
| | "select_id": 1,
| | "r_loops": 1,
| | "r_total_time_ms": 503.93,
| | "table": {
| | "table_name": "Table_A",
| | "access_type": "index",
| | "possible_keys": ["idx1"],
| | "key": "sys_created_on",
| | "key_length": "6",
| | "used_key_parts": ["sys_created_on"],
| | "r_loops": 1,
| | "rows": 2695302,
| | "r_rows": 234328,
| | "r_total_time_ms": 476.64,
| | "filtered": 50,
| | "r_filtered": 0.1903,
| | "attached_condition": "Table_A.`status` = 'Pending' and Table_A.scheduled_date >= '2022-02-15 06:00:00'"
| | },
+---------+------------------------------------------------------------------------------------------------------------------------
DESC ASC 有序查询完成 ~9118 毫秒
r_rows significantly Larger as comparing to ASC.
+---------+-----------------------------------------------------------------------------------------------------------------------
| 1 result(s):
+---------+-----------------------------------------------------------------------------------------------------------------------
| ANALYZE | {
| | "query_block": {
| | "select_id": 1,
| | "r_loops": 1,
| | "r_total_time_ms":9118.4,
| | "table": {
| | "table_name": "Table_A",
| | "access_type": "index",
| | "possible_keys": ["idx1"],
| | "key": "sys_created_on",
| | "key_length": "6",
| | "used_key_parts": ["sys_created_on"],
| | "r_loops": 1,
| | "rows": 2695302,
| | "r_rows": 2.27e6,
| | "r_total_time_ms": 4380.1,
| | "filtered": 50,
| | "r_filtered": 70.102,
| | "attached_condition": "Table_A.`status` = 'Pending' and Table_A.scheduled_date >= '2022-02-15 06:00:00'" |
| | },
+---------+-----------------------------------------------------------------------------------------------------------------------
P粉8484421852024-02-04 18:24:16
索引优化建议
表索引 x_nuvo_eam_scheduled_m9e_e8s（状态、scheduled_date、scheduled_maintenance、sys_created_on） x_nuvo_eam_scheduled_m10s ( sys_id )
然后，修改为没有 (parens) 和 ticks，但也使用了预定 vs 维护的更干净的别名。第一个表具有适当的索引来优化 WHERE 和 JOIN 标准将会有所帮助。但创建完成覆盖索引也将有助于查询，因为所有元素都可以来自索引，而不是返回到每个表的原始数据页。
SELECT
sched.sys_id
FROM
x_nuvo_eam_scheduled_m9e_e8s sched
LEFT JOIN x_nuvo_eam_scheduled_m10s maint
ON sched.scheduled_maintenance = maint.sys_id
WHERE
sched.status = 'Pending'
AND sched.scheduled_date >= '2022-02-15 06:00:00'
AND maint.asset IS NULL
ORDER BY
sched.sys_created_on ASC
limit
0, 100