已添加索引的MySQL查询如何提速？300-450ms查询优化求助

首先，你的查询耗时过长的核心原因是在查询条件的列上使用了LCASE()函数——这会让MySQL无法有效利用你创建的索引。因为索引存储的是原始列值，函数会改变列值的形式，数据库不得不对每一行执行函数计算后再匹配，本质上变成了全表扫描，30万行数据自然会慢下来。

接下来给你几个针对性的优化方案，按优先级排序：

1. 利用大小写不敏感排序规则，直接去掉LCASE()函数 #

先检查你的geolocate表的字符集和排序规则，执行这条命令：

SHOW CREATE TABLE geolocate;

如果输出里的排序规则是类似utf8mb4_general_ci、utf8_unicode_ci这类以_ci结尾的（ci代表大小写不敏感），那MySQL本身就会忽略大小写进行匹配，你完全不需要用LCASE()函数。

把查询改成这样即可：

SELECT latitude, longitude, timezone 
FROM geolocate 
WHERE (country = 'cambodia' OR countryiso = 'cambodia') 
  AND (city = 'kaôh préab' OR cityabbr = 'kaôh préab');

修改后，你的索引就有机会被正常使用了。

2. 优化索引结构，适配查询逻辑 #

你的查询是(A OR B) AND (C OR D)的结构，这种OR组合对索引不太友好，单条索引很难覆盖所有分支。推荐两种优化方式：

方案A：拆分查询为子句，用UNION ALL合并结果

把原查询拆成4个明确的子查询，每个子查询对应一个条件组合，这样每个子查询都能精准匹配索引：

SELECT latitude, longitude, timezone FROM geolocate WHERE country = 'cambodia' AND city = 'kaôh préab'
UNION ALL
SELECT latitude, longitude, timezone FROM geolocate WHERE country = 'cambodia' AND cityabbr = 'kaôh préab'
UNION ALL
SELECT latitude, longitude, timezone FROM geolocate WHERE countryiso = 'cambodia' AND city = 'kaôh préab'
UNION ALL
SELECT latitude, longitude, timezone FROM geolocate WHERE countryiso = 'cambodia' AND cityabbr = 'kaôh préab';

然后为每个子查询创建对应的联合索引：

-- 对应第一个子查询
CREATE INDEX idx_country_city ON geolocate(country, city);
-- 对应第二个子查询
CREATE INDEX idx_country_cityabbr ON geolocate(country, cityabbr);
-- 对应第三个子查询
CREATE INDEX idx_countryiso_city ON geolocate(countryiso, city);
-- 对应第四个子查询
CREATE INDEX idx_countryiso_cityabbr ON geolocate(countryiso, cityabbr);

UNION ALL比UNION更高效，因为它不会做去重操作（如果你的数据不会有重复结果，优先用这个；如果有重复，换成UNION即可）。

方案B：调整现有索引顺序（适合不想拆分查询的场景）

如果不想拆分查询，可以尝试创建两个覆盖主要分支的联合索引：

-- 覆盖 (country OR countryiso) + city 的匹配场景
CREATE INDEX idx_country_countryiso_city ON geolocate(country, countryiso, city);
-- 覆盖 (country OR countryiso) + cityabbr 的匹配场景
CREATE INDEX idx_country_countryiso_cityabbr ON geolocate(country, countryiso, cityabbr);

不过这种索引对OR条件的优化效果不如拆分查询，建议优先选方案A。

3. 验证优化效果 #

每次修改后，用EXPLAIN查看执行计划，确认索引是否被正确使用：

EXPLAIN
SELECT latitude, longitude, timezone 
FROM geolocate 
WHERE (country = 'cambodia' OR countryiso = 'cambodia') 
  AND (city = 'kaôh préab' OR cityabbr = 'kaôh préab');

如果type列显示ref或range，说明索引生效；如果还是ALL，则需要进一步调整索引或查询逻辑。

额外建议 #

• 如果业务允许，将country、countryiso、city、cityabbr这些字段统一存储为小写（或大写），并在插入/更新数据时强制转换，彻底避免大小写问题，也让索引更高效。
• 你现有的Index3 (city, cityabbr, country, countryiso)顺序不合理——查询时先匹配country/countryiso，再匹配city/cityabbr，索引前缀应该优先放置过滤性高、查询时先匹配的列，所以这个索引对当前查询帮助不大，若没有其他查询依赖，可以考虑删除。

内容的提问来源于stack exchange，提问作者Meezaan-ud-Din