最左前缀 联合索引

以下SQL优化：
生产中有一个打印日志：print_log。id为主键，domain_id为二次索引
EXPLAIN select
o.*
FROM
print_log o
WHERE
o.domain_id = 10091
AND o.deleteVersion = 0
AND o.operatedTime > ‘2017-10-01’
AND o.receiverPerson LIKE concat(‘%’, ‘丁淑君’, ‘%’)
ORDER BY o.id DESC
LIMIT 0,500

当发现这个SQL执行时间很长时，执行explain，发现只走了domain_id的索引，

但是不能满足需求，我们考虑建立联合索引（domain_id,operateTime）
再次执行：
但是只走了 id主键的索引，还是没有走联合索引

此时因为优化器根据limit 0，500判断，结果集会很多，排序的时候用的id，所以直接走了id索引

去掉limit
EXPLAIN select
o.*
FROM
print_log o
WHERE
o.domain_id = 10091
AND o.deleteVersion = 0
AND o.operatedTime > ‘2017-10-01’
AND o.receiverPerson LIKE concat(‘%’, ‘丁淑君’, ‘%’)
ORDER BY o.id DESC
OK,走了联合索引

考虑将id换成opreateTime,
EXPLAIN select
o.*
FROM
print_log o
WHERE
o.domain_id = 10091
AND o.deleteVersion = 0
AND o.operatedTime > ‘2017-10-01’
AND o.receiverPerson LIKE concat(‘%’, ‘丁淑君’, ‘%’)
ORDER BY o.operatedTime DESC
LIMIT 0,500
ok.走了联合索引

总结一下多列索引的最左前缀原则（order BY）：
范围查询后，后面的列将无法走索引，但是范围查询的列还可以order by 走索引
详见：《高性能MYSQL》 177页