背景
最近频繁出现慢 SQL 导致系统性能问题, 于是决定针对索引进行一些优化. 一些表结构本身已经有了不少索引, 如果再继续添加索引, 势必会影响到插入数据的性能. 那么, 是否可以使用组合索引来达到目的呢? 这篇文章咱们来一探究竟.
认识复合索引
如果 where 条件中使用到多个字段, 并且需要对多个字段建立索引, 此时就可以考虑采用复合索引(组合索引). 比如查询地址时需要输入省, 市, 那么在省, 市上建立索引, 当数据量大时会明显提高查询速度.
组合索引有啥优势呢?
减少查询开销: 建立复合索引 (c1,c2,c3), 实际上相当于建立了(c1),(c1,c2),(c1,c2,c3) 三个索引. 对于大表来说, 可以极大减少开销.
覆盖索引: MySQL 可以直接通过遍历索引取得数据, 而无需回表, 减少了很多的随机 io 操作.
效率高: 索引列越多, 通过索引筛选出来的数据就越少, 从而提升查询效率.
缺点:
索引字段越多, 创建的索引越多, 每个索引都会增加磁盘空间的开销;
索引越多对查询效率提升越高, 但对需要更新索引的增删改操作会有效率影响;
复合索引使用建议: 单表最好不要超过 1 个复合索引, 单个复合索引最好不超过 3 个字段. 一旦超过, 就需要考虑必要性和是否有其他替代方案.
最左匹配原则
复合索引遵从最左匹配原则, 顾名思义, 在组合索引中, 最左侧的字段优先匹配. 因此, 在创建组合索引时, where 子句中使用最频繁的字段放在组合索引的最左侧.
辅助索引是 B + 树实现的, 虽然可以指定多个列, 但是每个列的比较优先级不一样, 写在前面的优先比较高. 一旦出现遗漏, 在 B + 树上就无法继续搜索了(通过补齐等措施解决的除外), 因此是按照最左连续匹配来的. 既然是在 B + 树上搜索, 对于条件的比较自然是要求精确匹配(即 "=" 和 "IN").
在 where 子句中用到两个字段 c1 和 c2, 那么创建索引时, 两个字段的顺序应该是 (c1,c2) 还是 (c2,c1) 呢?
正确的做法是: 把重复值最少的放前面. 比如, 95% 的值都不重复, 则可考虑放最前面.
字段顺序的影响
复合索引遵从最左匹配原则, 那么在 where 查询条件中的字段是否也需要按照索引的顺序来写呢?
比如, 复合索引为(c1,c2,c3), 下面两个查询条件是否会对索引有影响呢?
- select * from t_user where c1 = 1 and c2 = 4;
- select * from t_user where c2 = 4 and c1 = 1;
看到有文章提出第一条 SQL 语句的效率更高, 是否可信? 两种查询方式条件一样, 结果也应该一样, 正常来说 MySQL 也会让它们走同样的索引.
通过 MySQL 的查询优化器 explain 分析上述两个条语句, 会发现执行计划完全相同. 也就是说: SQL 语句中的字段顺序并不需要与复合索引字段顺序一致, 查询优化器会自动调整顺序.
如果说有效率影响, 那么也就是查询优化器矫正顺序的影响吧, 几乎可以忽略不计.
单字段是否可以触发索引?
对于复合索引为 (c1,c2,c3), 相当于(c1),(c1,c2),(c1,c2,c3) 三个索引, 如果查询条件中只有 c1, 很显然是会走索引的.
但如果 where 条件如下呢:
from t_user where c2 = 4;
上述语句是否会走索引呢? 这得分几种情况来说明.
执行 explan 查询 c1 为条件的 SQL 语句:
explain select * from t_user where c1 = 1;
上述语句走的索引类型为: ref.ref 类型表示 MySQL 会根据特定的算法快速查找到符合条件的索引, 而不会对索引中每一个数据都进行扫描判断. 这种类型的索引为了快速查出数据, 索引就需要满足一定的数据结构.
执行 explan 查询 c2 为条件的 SQL 语句:
explain select c2 from t_user where c2 = 4;
上述语句走的索引类型为: index.index 类型表示 MySQL 会对整个索引进行扫描, 只要是索引或索引的一部分 MySQL 就可能会采用 index 方类型的方式扫描. 由于此种方式是一条数据一条数据查找, 性能并不高.
在这个例子中, 对查询的字段有一定的要求, where 中条件为 c2,select 中查询出的字段也只能是 c2, 才会走 index 类型的索引.
如果将 c2 换成 * 或其他字段:
explain select * from t_user where c2 = 4;
上述语句会发现, 不再走 index 索引, 而是走全表扫描了. 这也从侧面说明了 MySQL 为什么要讲最左匹配原则了.
所以结论是: 如果单个字段为复合索引的首个字段, 则会正常走索引; 如果单个字段是复合索引的其他字段, 且仅有该字段出现在 select 后面, 则会走 index 类型索引; 而其他情况, 则走全表扫描.
复合索引可以替代单一索引吗?
单一索引:(c1), 复合索引:(c1,c2).
当 c1 作为查询条件时, 单一索引和复合索引查询速度几乎一样, 甚至比复合索引还要略快.
如果仅用复合聚集索引的非起始列 (c2) 作为查询条件的话, 复合索引是不起任何作用的.
对于一张表来说, 如果有复合索引(c1,c2), 则无需再建单一索引(c1).
如果已经存在单一索引 (c1), 因查询所需, 可添加复合索引(c1,c2) 来提升效率.
小结
本篇文章整理了 MySQL 复合索引使用时所需注意的一些知识点, 在使用时可以通过 explain 来查看一下你的 SQL 语句是否走了索引, 走了什么索引.
但还要了解的是: MySQL 的执行计划和查询的实际执行过程并不完全吻合.
别问我为什么知道, 因为在实践中遇到过. 同一条 SQL 语句, 查询条件不同, 有可能会走索引, 也有可能不会走索引.
来源: http://database.51cto.com/art/202112/697087.htm