如何利用索引加速大型MySQL表的查询速度

优化方案 #

1. 简化查询条件，替换冗余的LIKE语句

你的LIKE语句没有通配符，本质是精确匹配，换成IN可以让条件更简洁高效：

SELECT `id`, `url` FROM `mytable`
WHERE `viewdate` > "2022-11-20 23:23:00"
  AND `url` IN ("https://www.domain.tld/path/to/dir", "https://www.domain.tld/path/to/dir/")
ORDER BY `id` DESC;

2. 创建复合覆盖索引

由于url是longtext类型，无法直接创建全字段索引，所以构建前缀复合覆盖索引，包含查询所需的所有字段，避免回表查询和文件排序：

-- 若viewdate与id递增顺序一致（比如viewdate是记录插入时间），优先用这个索引
CREATE INDEX idx_viewdate_url_id ON mytable(viewdate DESC, url(255), id DESC);

-- 若viewdate与id顺序无关，优先匹配URL条件，用这个索引
CREATE INDEX idx_url_viewdate_id ON mytable(url(255), viewdate, id DESC);

覆盖索引的优势是查询可直接从索引中提取id和url，无需访问主表，大幅降低IO开销。

3. 移除强制索引提示

你当前使用的USE INDEX(idx-viewdate)会限制MySQL优化器选择更优索引，删除该提示，让优化器自动判断最佳执行路径。

4. 切换至InnoDB引擎

MyISAM不支持行级锁、事务，缓存机制也不如InnoDB高效，40万条数据量级下切换引擎通常能明显提升性能：

ALTER TABLE mytable ENGINE=InnoDB;

切换前务必备份数据，InnoDB会将主键作为聚集索引，进一步优化基于主键的查询效率。

5. 统一URL存储格式（可选）

如果业务允许，统一URL的末尾斜杠规则（要么都带，要么都不带），可将查询条件简化为单个url = 'xxx'，进一步降低查询复杂度。

验证优化效果 #

执行EXPLAIN查看执行计划，优化后的理想状态是：

• type列显示ref或range（ref最优）
• Extra列无Using filesort和Using temporary
• rows列显示扫描行数大幅减少

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