MySQL的联合索引及案例分析

1. 联合索引

关于联合索引的详解参考博客【Mysql-----联合索引和最左匹配】，包含讲解

最左匹配
联合索引失效的情况
- 不遵循最左匹配原则
- 范围查询右边失效原理
- like索引失效原理

比较关注的点在于：

对A、B、C三个字段创建一个联合索引（A, B, C），若where条件后是以下几种情况会不会走索引？

select A, B, C, D from t_a where A=1 and B=1 and C=1;	-- 走索引
select A, B, C, D from t_a where A=1 and B=1;	-- 走索引
select A, B, C, D from t_a where A=1 and C=1 and B=1;	-- 走索引,MySQL有优化器会自动调整A,B,C的顺序与索引顺序一致
select A, B, C, D from t_a where C=1 and B=1 and A=1;	-- 走索引
select A, B, C, D from t_a where B=1 and C=1;	-- 不走索引

select A, B, C, D from t_a where A=1 and B>1 and C=1;	-- A、B走索引,C不走索引,因为前面是范围查询
select A, B, C, D from t_a where A>1 and B=1;	-- A走索引,B不走索引

select A, B, C, D from t_a where A like 'wan%';	-- 有时能走索引
select A, B, C, D from t_a where A like '%wan%';	-- 必然不走索引
select A, B, C, D from t_a where A like '%wan';	-- 必然不走索引

2. 案例分析

2.1 问题重现：

要执行如下的删除逻辑，<where>标签中只有code和is_deleted一定不为空。

假设dish表有上百万的数据量，delete from ...执行效率低，若不建立合适的索引，容易产生锁表问题，执行报错！

    <delete id="deleteOldData">
        delete from dish
        <where>
            <if test="param.code != null and param.code != ''">
                and code = #{param.code}
            </if>
            <if test="param.status != null and param.status != ''">
                and status = #{param.status}
            </if>
            <if test="param.updateUser != null and param.updateUser != ''">
                and update_user = #{updateUser}
            </if>
            <if test="param.isDeleted != null and param.isDeleted != ''">
                and is_deleted = #{isDeleted}
            </if>
        </where>
    </delete>

2.2 建立索引：

上述SQL的where条件中，只有两项一定不为空，我们该如何建立合适的索引避免死锁问题？

根据上述章节对联合索引的介绍，我们可以考虑建立如下索引：

CREATE INDEX IDX_DISH_CODE_ISDELETED ON dish(`code`, `is_deleted`, `update_user`, `status`);

把两个一定不为空的字段code和is_deleted放在左侧，且区分度大的字段code放在最左侧，其他两个可能为空的字段放在右侧。

由于联合索引会帮助我们给where条件后的字段重排序，这样至少该delete from where...的前两个字段会走索引，效率提升，降低锁表风险。

未创建该索引时，表索引和执行计划情况：

在这里插入图片描述

创建该索引后，表索引和执行计划情况：

在这里插入图片描述

（1）字段齐全：

执行这段SQL：

EXPLAIN SELECT * FROM dish WHERE CODE = '123412341234' AND STATUS = '1' AND update_user = '1' AND is_deleted = '0';
-- mysql会帮助调整字段顺序为：
SELECT * FROM dish WHERE CODE = '123412341234' AND is_deleted = '0' AND update_user = '1' AND STATUS = '1';

查看执行计划：

在这里插入图片描述

（2）缺失update_user：

执行这段SQL：

EXPLAIN SELECT * FROM dish WHERE CODE = '123412341234' AND STATUS = '1' AND is_deleted = '0';
-- mysql会帮助调整字段顺序为：
SELECT * FROM dish WHERE CODE = '123412341234' AND is_deleted = '0' AND STATUS = '1';