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欢迎评论分享.