mysql踩的坑in中子查询索引失效
前言
前段时间有一个需求,查询用户每个贷款类型下最后一笔订单..
当时脑抽写出这么一个sql
版本1
select * from (
select * from orders where user_id = 1150 order by id desc
) o group by product_id ;
这个做法有点取巧的意思,将订单倒序后再分组,这样获取出来每个分组的所有列改组最后一条.
优化一
感觉上方的SQL有点取巧的意思,而且万一出来的不是倒序每组第一条,这样感觉会有问题,所以我进行了第一次优化,优化如下
版本2
select *
from orders o
where o.user_id = 1
and id = (select id
from orders
where o.product_id = orders.product_id
and o.user_id = orders.user_id
order by id desc
limit 1)
group by product_id;
楼上SQL的约束下跑出来的一定是最后一条,解释器十分强大给力,竟然执行出来了,结果和预期一致.所以它就成了1.1版本
发现了坑
有一次闲的蛋疼,把楼上SQL放到本地环境跑了一下,本地环境5.7,发现报错了.报错信息如下
[42000][1055] Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'o.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
大致看了一下,搜索一番找到问题所在,MySQL5.6(含)之前不会强制要求查询结果集当中只包含group by 字段和聚合函数结果,可以有其他表字段.而5.7之后这一做法被禁止,如果需要查其他字段需要用上any_value()函数,使用方法如下
select status, any_value(user_id) from orders group by status
这样就不会报错了,可是我的需求查出来是*,用这种方式我岂不是要把字段一个个全写出来...思考一番,出来第三个版本SQL
遇到大坑
版本3
SELECT *
FROM `orders`
WHERE `id` IN (SELECT max(id) FROM `orders` WHERE `user_id` = 1 GROUP BY product_id);
楼上这条SQL终于解决了版本兼容问题,我将SQL替换以后,写上单元测试集,对新旧两个SQL结果比对确认没问题之后准备上线.上线前扔到从库试查一下,发现sql执行时间竟然要10s+.当场惊出一身冷汗(测试环境的数据库orders只有万条数据,而线上千万级别).
果断explain 分析一波, 内层查询如我想的一般,走的user_id索引,rows =1 问题出现在外层,外层竟然不走索引,进行全表扫描了.这不科学..于是我试运行下面这条SQL
select * from orders where id in (1323, 23232, 3323232)
执行sql,execution 56ms很妥...那版本3的SQL为什么会慢查询呢?
搜索一番发现MySQL当中in 内如果跟上子查询,竟然会让索引失效.我去,神之大坑...这要是上线了妥妥要崩...只能继续改...吃饭午饭回来,终于出来最终版SQL
最终版
版本4
select o.*
from (select max(id) id from orders where user_id = 1 group by product_id) as o
inner join orders on o.id = orders.id
放到从库测试,52ms妥了,预想的user_id和id两个索引也全都正常运作.
结语
关于索引失效还有一个常见坑容易被忽略,索引字段类型为字符型,而查询字段为数值型,索引将会失效,常见SQL如下
select * from users where phone = 1310000000;
phone字段类型varchar,上方这个查询会让索引失效.写SQL之前一定要三思,多用explain分析查看SQL.
针对这个需求如果你有其他更好的SQL欢迎评论分享.
