深入理解Oracle索引(14)：Composite Index 两大原理解析

声明：虽然题目是Oracle、但同样适合MySQL InnoDB索引

在大多数情况下、复合索引比单字段索引好
很多系统就是靠新建一些合适的复合索引、使效率大幅度提高
复合索引比单字段索引复杂、有两个原则需把握：前缀性和可选性
但是、可叹的是、国内很多IT系统开发人员没有意识到应该优先设计复合索引
更没有充分理解复合索引的前缀性和可选性这两个重要原则

㈠ 前缀性（Prefixing）

在谓词条件中、只有将复合索引的第一个字段作为约束条件、该复合索引才会被用上
当然、存在一种例外：Index Skip Scan

这个原理强调 使用

比如：

① 假如c1的选择性比c2高很多、且如果有大量如下查询：
select * from t where c1= :x and c2= :y;
select * from t where c2= :y;
这个时候(c2,c1)比(c1,c2)好

② 有ID和CREATE_TIME这两个字段、一个是主键、一个是创建时间
按一般情况、创建复合索引时、需要将ID放在前面、
不过、如果以CREATE_TIME为查询条件的SQL比较多、而ID的使用比较少
那么、我们在设计时、应该把CREATE_TIME前缀

㈡ 可选性（Selectivity）

概念可见

Selectivity介绍

这个原理强调成本

比如：

① 选择性强的字段放在前面、可以减少 Index Range Scan 的扫描成本
② 有个税务系统、原先的复合索引是(月份，税务机关代号，纳税人识别号，发票代号，收费类别)
对于这个索引、优化器并没有选择走索引、而是全表扫、原因如下：
● 同一月份的记录太多了、超过1/3、全表扫成本低
● 字段顺序没有考虑可选性
新的复合索引是(纳税人识别号，月份，收费类别，税务机关代号，发票代号)


㈢ 复合索引好处

① 尽可能让一个索引为更多的SQL服务
② 复合索引是排序的、ORDER BY涉及索引字段时、可减少排序成本


我认为呢、在为复合索引字段排序时、应该综合考虑权衡前缀性和可选性、绝不可偏袒任何一方