[慢查优化]建索引时注意字段选择性&范围查询注意组合_MySQL

优化策略B：

由于 mysql 索引是基于 B-Tree 的，所以组合索引有“字段顺序”概念。

所以，查询条件中有 ac.city_id IN (0, 8005)，而组合索引是 (ads_id,city_id)，则该查询无法使用到这个组合索引。

DBA总结道：

组合索引查询的各种场景兹有 Index (A,B,C) ——组合索引多字段是有序的，并且是个完整的BTree 索引。

下面条件可以用上该组合索引查询：

A>5

A=5 AND B>6

A=5 AND B=6 AND C=7

A=5 AND B IN (2,3) AND C>5

下面条件将不能用上组合索引查询：

B>5 ——查询条件不包含组合索引首列字段

B=6 AND C=7 ——查询条件不包含组合索引首列字段

下面条件将能用上部分组合索引查询：

A>5 AND B=2 ——当范围查询使用第一列，查询条件仅仅能使用第一列

A=5 AND B>6 AND C=2 ——范围查询使用第二列，查询条件仅仅能使用前二列

组合索引排序的各种场景兹有组合索引 Index(A,B)。

下面条件可以用上组合索引排序：

ORDER BY A——首列排序

A=5 ORDER BY B——第一列过滤后第二列排序

ORDER BY A DESC, B DESC——注意，此时两列以相同顺序排序

A>5 ORDER BY A——数据检索和排序都在第一列

下面条件不能用上组合索引排序：

ORDER BY B ——排序在索引的第二列

A>5 ORDER BY B ——范围查询在第一列，排序在第二列

A IN(1,2) ORDER BY B ——理由同上

ORDER BY A ASC, B DESC ——注意，此时两列以不同顺序排序

顺着组合索引怎么建继续往下延伸，请各位注意“索引合并”概念：

MySQL 5,0以下版本，SQL查询时，一张表只能用一个索引（use at most only one index for each referenced table），

从 MySQL 5.0开始，引入了 index merge 概念，包括 Index Merge Union Access Algorithm（多个索引并集访问），包括Index Merge Intersection Access Algorithm（多个索引交集访问），可以在一个SQL查询里用到一张表里的多个索引。

MySQL 在5.6.7之前，使用 index merge 有一个重要的前提条件：没有 range 可以使用。[出自参考资源2]

索引合并的简单说明：

MySQL 索引合并能使用多个索引

SELECT * FROM TB WHERE A=5 AND B=6

能分别使用索引(A) 和 (B) 或 索引合并；

创建组合索引(A,B) 更好；

SELECT * FROM TB WHERE A=5 OR B=6

能分别使用索引(A) 和 (B) 或 索引合并；

组合索引(A,B)不能用于此查询，分别创建索引(A) 和 (B)会更好；

最后的总结：仍然是强调再强调：记住，explain 后再提测是一种美德！

---

参考资源：1）中文译稿，2013，MySQL 索引最佳实践之问题反馈；2）orczhou,2013，MySQL优化器：index merge介绍；3）orczhou，2013，index merge的补充说明；4）zhengyun，2013，[慢查优化]联表查询注意谁是驱动表 & 你搞不清楚谁join谁更好时请放手让mysql自行判定；5）nocode，2013，MySQL Internals-Index Merge优化；

---

赠图1枚：转：喵了个咪的，老板催我去重构遗留的 Python 代码……bitsCN.com

声明：本网页内容旨在传播知识，若有侵权等问题请及时与本网联系，我们将在第一时间删除处理。TEL:0731-84117792 E-MAIL:11247931@qq.com