（转载）联合索引优化多条件查询

转载自：联合索引优化多条件查询

---

联合索引是由多个字段组成的组合索引。若经常需要使用多个字段的多条件查询（WHERE col1 = … AND col2 = … AND col3 = …），可以考虑使用联合索引。

---

现在数据表myIndex中i_testID是主键列，其他列无任何索引：

这里写图片描述

---

多条件查找名字为xiaoming，城市为beijing，年龄为21的人：

这里写图片描述

返回了一行数据，从执行计划中看到，查询没有使用任何索引，进行了全表扫描，磁盘IO大。

---

为vc_Name建立索引：

这里写图片描述

进行同样的查询并查看执行计划：

这里写图片描述

返回了相同的结果，分析执行计划：SQL通过刚刚建立的index_of_name索引，不再进行全表扫描，而是先在索引中查找满足节点值为xiaoming的节点（有5个），再指向数据库中相应的行，返回一个初步结果集后再由另外两个条件进行一步步筛选得到最终结果。大大减少了磁盘IO，查询效率也高于前者。

---

虽然在 vc_Name 上建立了索引，查询时MYSQL不用扫描整张表，效率有所提高，但离我们的要求还有一定的距离。同样的，在 vc_City 和 i_Age 分别建立的MySQL单列索引的效率相似。为了进一步榨取 MySQL 的效率，就要考虑建立组合索引。

---

为多条件涉及的列建立联合索引：

这里写图片描述

值得注意的是，建立索引的时候应该根据需要规定索引长度，例如一个人的名字长度应该不会超过10个字符，通过vc_Name(10)规定索引长度后一定长度可以减少索引所占内存。

现在表中的结构：

这里写图片描述

进行相同的查询并查看执行计划：

这里写图片描述

返回相同的结果，从执行计划可以看到：本次查询使用了联合索引name_city_age，在遍历索引时就确定了只有一个节点满足条件，直接指向数据库表进行查询（rows：1）。有更少的磁盘IO，所用时间更少！

---

使用联合索引应该注意：

1. MySQL使用联合索引只能使用左侧的部分，例如INDEX(a,b,c)，当条件为a或a,b或a,b,c时都可以使用索引，但是当条件为b,c时将不会使用索引。这好比一本先根据姓，再根据名进行排序的电话簿，当查找的时候有姓的条件，效率会比没有任何条件高；如果在姓的基础上还有名的条件，效率会更高；但若只有名的条件，电话簿将不起作用。
2. 离散度更高的索引应该放在联合索引的前面，因为离散度高索引的可选择性高。考虑一种极端的情况，数据表中有100条记录，若INDEX(a,b)中a只有两种情况，而b有100种情况。这样对于查询唯一记录a = …，b = …时，先遍历全部索引看满足a条件的有50个索引节点，接下来还要再一个个遍历这50个索引节点。如果是INDEX(b,a)，先遍历全部索引发现满足b条件的索引节点只有一个，再遍历这个节点发现也满足a条件。虽然最后都能找到那个唯一的索引节点，但是第二种索引顺序对引擎遍历索引效率有很大的提高（用电话薄的思想去思考问题）。
3. 查看列的离散程度：

这里写图片描述

customer_id列的离散程度更高，建联合索引时应该INDEX(customer_id,staff_id);