MYSQL索引相关

原sql

SELECT id,
       user_id,
       package_id,
       term_id,
       source,
       type,
       order_id,
       code_record_id,
       created_at,
       updated_at,
       version,
       is_deleted AS deleted
FROM tbl_purchased_package
WHERE (term_id IN (9828, 11022, 11025, 11029, 11139, 11143, 11165, 11180, 11205) AND is_deleted = 0)
LIMIT 2000,500; # 2000 表示从第 2001 行开始（偏移量）。500 表示返回 500 行数据。

新建联合索引-----遵循索引最左原则（where后面的条件是从左到右写）

新建联合索引 idx_user_term_delete_type

SELECT
<include refid="selectColumns"/>
FROM `tbl_purchased_package`
WHERE 1=1
<if test="userId != null">
    AND `user_id` = #{userId}
</if>
<if test="termId != null">
    AND `term_id` = #{termId}
</if>
<if test="isDeleted != null">
    AND `is_deleted` = #{isDeleted}
</if>
<if test="termIds != null and termIds.size > 0">
    AND `term_id` IN
    <foreach collection="termIds" index="index" item="item" open="(" separator="," close=")">
        #{item}
    </foreach>
</if>
and id >= #{minId}
order by id
limit #{limit}

执行sql 查看有没有走上索引

explain SELECT id,
       user_id,
       package_id,
       term_id,
       source,
       type,
       order_id,
       code_record_id,
       created_at,
       updated_at,
       version,
       is_deleted AS deleted
FROM tbl_purchased_package
WHERE (user_id=1189376439  and term_id IN (13566,13583,13530,11470) AND is_deleted = 0)
# LIMIT 20,5;