sql在不添加索引的前提下下的解决思路

文章目录

• 场景
• 一、数据库优化法
• 二、非数据库优化法，使用redis

---

场景

目前有一个sql，需要查出某个客户的前100次消费记录，订单表有几亿数据，且只有主键id自增索引，不能改造表结构。如何解决？需要从数据库层面与非数据途径两方面给出两种解决方案。sql如下：
select * from order where user_id = 1 order by created_time desc limit 100

---

一、数据库优化法

1.、 第一步优化：

将order by created_time替换为order by desc，原因为只有id有索引。
看一下区别：
看一下 order by created_time


可以看到题目给的sql是超级慢的，全表所描+文件排序，上亿级别数据几分钟都执行不完。

看一下 order by id desc


可以看出是使用到索引的，且没有file sort，一定要避免file sort，超级慢的原因之一就是文件排序

2、 第二步优化：

直接使用where user_id = 112233 order by id desc limit 10，虽然比原sql强，但由于使用了排序，依然会慢。
这时我们可以考虑利用id主键索引，从后往前查，先查到这个用户的最后一笔订单的id，然后使用where id < xxx and user_id = 112233。

3、 试一下效果：

找了一张300万数据的表：

原sql处理速度 where user_id = 112233 order by created_time desc limit 1：

第一次优化：
where user_id = 112233 order by id desc limit 1

使用最大的id，查出剩余9条最新消费订单数据WHERE id < xxx AND user_id = 112233 LIMIT 9：

二、非数据库优化法，使用redis

1、 思路

可以使用redis的list结构实现队列，为每个用户实时维护前N笔消费的order_id
redis结构：key:user_id value:list结构,存储order_id
在存储order_id时需要注意并发的情况，多个用户同时下单，操作redis可能导致多插，考虑使用redis事物+乐观锁。redis事物可以保证一致性与隔离性，但由于没有回滚机制，不能保证原子性，可以使用乐观锁来弥补。

2、 伪代码

// cas乐观锁变量
boolean cas = false;
// 失败后自旋次数
in i = 0;
// 允许自旋10次
while(i <= 10){
if(
// 使用lua脚本，并开启redis事务
// 开启事物
multi
// 存储最新的10笔订单号
redis.call(
if (redis.call(‘llen’, user_id) >10
then redis.call(‘rpop’, user_id)
else redis.call(‘rpush’, user_id，order_id)
)
// 提交事物
exec
)为真，cas = true
}
if(cas === false){
// 异常记录，并发太大，需要继续调优
}

本文地址：https://blog.csdn.net/qq_37099837/article/details/111028650