Mysql联合索引实测分析

对于为什么要使用联合索引，最重要的几点如下：

1.在mysql查询中一次简单查询仅能使用一个索引，索引过多，会影响INSERT及UPDATE性能，简单说就是会影响数据写入性能。因为更新数据的同时，也要同时更新索引。但是索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select * from table where a = 1 and b =2 and c = 3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W*10%=100w 条数据，然后再回表从100w条数据中找到符合b=2 and c= 3的数据，然后再排序，再分页；如果是复合索引，通过索引筛选出1000w *10% *10% *10%=1w，然后再排序、分页，哪个更高效，一眼便知。

2.建了一个(a,b,c)的复合索引，那么实际等于建了(a),(a,b),(a,b,c)三个索引，因为每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，所有细节的优化都需要考虑。

3.覆盖索引。同样的有复合索引（a,b,c），如果有如下的sql: select a,b,c from table where a=1 and b = 1。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。

在测试表中插入10万+数据，建立了三个字段的联合索引，测试索引名称：lh_index，实际被索引字段：VENDOR_ID(订单号)、 MEMBER_ID（用户名）、ARRIVE_AREA_ID（区域号），注意这三个字段是有顺序的。被索引字段均为varchar类型。三个字段填充的数据均为随机的数字。

造数脚本很简单如下：

BEGIN
INSERT INTO `waybill_1`.`waybill_c_25` (`TEST_CODE`, `VENDOR_ID`, `MEMBER_ID`, `ARRIVE_AREA_ID`) VALUES (floor(rand()*1000003300), 
floor(rand()*1000003300), floor(rand()*1000003300), floor(rand()*1000003300),
);
    set i=i+1;
END WHILE;
END

下面对联合索引进行测试：

1.单字段测试VENDOR_ID

explain select * from waybill_c_25 where  VENDOR_ID='1233'

结论：对联合索引的第一个字段进行单独的查询可以利用到索引。

2.单字段测试MEMBER_ID

explain select * from waybill_c_25 where  MEMBER_ID='1233'  

结论：未使用到索引，进行了全表扫描。

3.单字段测试ARRIVE_AREA_ID

explain select * from waybill_c_25 where  ARRIVE_AREA_ID='1233'  

结论：未使用到索引，进行了全表扫描。

4.多字段测试，全部字段包含

explain select * from waybill_c_25 where  VENDOR_ID='456' and MEMBER_ID='3332' and ARRIVE_AREA_ID='1233'  

结论：三个字段都包含是建立联合索引的初衷，可以使用到索引。

5.多字段测试，前两个字段

explain select * from waybill_c_25 where  VENDOR_ID='456' and MEMBER_ID='3332'

结论：对三个字段的联合索引使用前两个字段进行查询，可以利用到索引。

6.多字段测试，第一个和最后一个字段

explain select * from waybill_c_25 where  VENDOR_ID='456' and ARRIVE_AREA_ID='3332'

结论：对三个字段的联合索引使用前两个字段进行查询，可以利用到索引，但只能利用到第一个字段的索引，实际用到的仅是第一个字段的索引，和第5中情况有所区别。

7.多字段测试，第二个和最后一个字段

explain select * from waybill_c_25 where  MEMBER_ID='456' and ARRIVE_AREA_ID='3332'

结论：对后两个字段进行查询无法使用到索引。

引用一篇其他资料的结论图：

附参考文章：https://segmentfault.com/q/1010000000342176