带ORDER BY的LIKE查询为何变慢？MariaDB索引选择疑问

问题背景 #

运行以下查询（MariaDB 10.4.12）：

SELECT *
FROM transazioni tr
LEFT JOIN acqurienti a ON tr.acquirente=a.id
WHERE
tr.frontend=1
AND tr.stato!=0
AND a.cognome LIKE 'AnyText%'
ORDER BY tr.creazione

相关索引信息 #

• frontend：int类型，基数166
• creazione：datetime类型，基数123541（覆盖全表所有行）

带ORDER BY的执行计划（耗时约12秒） #

+-----+--------------+--------+---------+-----------------------------------------+------------+----------+------------------------------------+-------+-------------+
| id  | select_type  | table  |  type   |             possible_keys               |    key     | key_len  |                ref                 | rows  |    Extra    |
+-----+--------------+--------+---------+-----------------------------------------+------------+----------+------------------------------------+-------+-------------+
|  1  | SIMPLE       | tr     | index   | acquirente,frontend                     | creazione  |       6  | NULL                               |  793  | Using where |
|  1  | SIMPLE       | a      | eq_ref  | PRIMARY                                 | PRIMARY    |       4  | tr.acquirente                      |    1  | Using where |
+-----+--------------+--------+---------+-----------------------------------------+------------+----------+------------------------------------+-------+-------------+

移除ORDER BY后的执行计划（耗时约0.4秒） #

+-----+--------------+--------+---------+-----------------------------------------+---------------------+----------+----------------+-------+-------------+
| id  | select_type  | table  |  type   |             possible_keys               |        key          | key_len  |      ref       | rows  |    Extra    |
+-----+--------------+--------+---------+-----------------------------------------+---------------------+----------+----------------+-------+-------------+
|  1  | SIMPLE       | tr     | ref     | acquirente,frontend                     | frontend            |       5  | const          | 3112  | Using where |
|  1  | SIMPLE       | a      | eq_ref  | PRIMARY                                 | PRIMARY             |       4  | tr.acquirente  |    1  | Using where |
+-----+--------------+--------+---------+-----------------------------------------+---------------------+----------+----------------+-------+-------------+

索引选择逻辑解析 #

MariaDB优化器选择索引时会对比两种执行路径的预估成本：

1. 选择frontend索引：先过滤出tr.frontend=1的3112行，再筛选tr.stato!=0，关联acquirienti后过滤a.cognome，最后对结果排序。优化器预估这里的排序成本较高（基于3112行的排序操作）。
2. 选择creazione索引：按索引顺序扫描（无需额外排序），但要逐行检查所有WHERE条件。优化器预估只需扫描793行，认为这个成本比“过滤+排序”更低。

但实际情况是，a.cognome LIKE 'AnyText%'这个关联后的过滤条件最终只留下2行，排序成本几乎可以忽略。优化器的问题在于无法精准预估关联后的过滤效果，它只能基于表统计信息估算整体成本，误判了“避免排序”的收益大于“提前过滤”的收益，最终选择了效率更低的creazione索引。

解决方案 #

1. 创建针对性联合索引：保留单独的creazione索引给其他查询，同时创建(frontend, creazione)联合索引。这个索引既能高效过滤frontend=1的行，又能利用索引的有序性省去排序操作，完美适配当前查询。
2. 强制指定索引：临时解决可以用FORCE INDEX强制优化器选择frontend索引，因为实际结果集极小，排序成本可忽略：

SELECT *
FROM transazioni tr FORCE INDEX(frontend)
LEFT JOIN acqurienti a ON tr.acquirente=a.id
WHERE
tr.frontend=1
AND tr.stato!=0
AND a.cognome LIKE 'AnyText%'
ORDER BY tr.creazione

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