MySQL sql优化-组合索引理解

由于mysql在执行查询计划中对于单个表的查询只会使用一个索引，但在系统使用中对于单表总是各种不同的条件组合查询，在建立索引的过程中要保证可用性和普遍性，单个表建多个单字段索引是浪费空间又达不到最好效果，推荐的是建立组合索引。组合索引，这样看上去索引个数比较精简，效果也是比较好的。

select * from t_bc_customer where fkcontrolunitid = '00d0ddd' limit 0,30 

对于这样的查询肯定是建索引fkcontrolunitid ， alter table t_bc_customer  add index idx_cuid(fkcontrolunitid )

select * from t_bc_customer where fkcontrolunitid = '00d0ddd' and fcreatetime > '2019-01-01' limit 0,30 

对于这样的查询肯定是建索引fkcontrolunitid, fcreatetime ， alter table t_bc_customer  add index idx_comp(fkcontrolunitid,fcreatetime )

组合索引的字段先后顺序十分关键，直接决定了sql在查询中是否能使用到该索引。

1.组合索引的字段先后顺序需要和查询sql中的查询字段出现顺序一致，否则可能只使用到了一部分索引。

如 select * from t_bc_customer where fkcontrolunitid = '00d0ddd' and fstatus = 'y' and fcreatetime > '2019-01-01' 

如果创建的索引是idx_comp(fkcontrolunitid,fcreatetime ) 则只会使用到fkcontrolunitid第一部分的索引

如果将sql改成  select * from t_bc_customer where fkcontrolunitid = '00d0ddd' and fcreatetime > '2019-01-01' and fstatus = 'y'  则可以使用到该索引的100%

2.排序字段也可以使用到索引，字段顺序排在前面查询字段的最后。

所以组合索引的建立对SQL的写法有个顺序的限制。由于在开发过程中对一个表有多种查询需求，字段的组合也是多种多样，如何保证索引的精简和有效性是需要根据具体业务需求规范表字段的查询顺序，一般按字段影响的范围从大到小排序 。否则每条sql都需要单独建个组合索引，这是很浪费空间的做法