大佬们, sql 查询语句里面如果加了 ORDER BY create_time desc 会导致扫描全表变得很慢

SELECT * FROM `orders` WHERE user_id = 10 AND order_status IN ('PROCESSED') AND status = 'pending_orders' AND shop_id IN ('123456') ORDER BY create_time desc LIMIT 20;

这个怎么处理才能让排序不扫描全表, 不加 ORDER BY create_time 能在一秒内了, 加了要有一定几率到 30 秒,我估计着应该是进行了全表排序

全表

order

desc

and

33 replies • 2023-06-27 11:34:27 +08:00

sunxiaping521

Jun 26, 2023

EXPALIN 看一下啊

hhjswf

Jun 26, 2023 via Android

按时间排不如按 id 排

Mystery0

Jun 26, 2023 via Android

create time 是 varchar ？

dw2693734d

Jun 26, 2023

index 一下？

fiypig

Jun 26, 2023

是不是有 null 导致的

xpyusrs

Jun 26, 2023

@Mystery0 是 bigint

LeegoYih

Jun 26, 2023

DDL 看一下

mineralsalt

Jun 26, 2023

@hhjswf #2 请教个问题, 一张表, 长字符串 id 和一个有索引的整数字段, 那么这两个排序哪个快?

xpyusrs

Jun 26, 2023

@sunxiaping521 https://s1.ax1x.com/2023/06/26/pCU32KU.png, 都走了索引的, 但是扫描全表会很慢

huajia2005

Jun 26, 2023

转为根据自增 id 查询吧,一样的

hhjswf

Jun 26, 2023 via Android

@mineralsalt id ，因为还要回表，除非你就查索引字段这一列。

xpyusrs

Jun 26, 2023

@fiypig 不是, create_time 已经设置了不是 NULL

LeegoYih

Jun 26, 2023

你 status 也设置索引吗？去掉试试。实在不行用 Force Index

xpyusrs

Jun 26, 2023

@LeegoYih 嗯, 设置了, 跟这个也有关系么, 我去试一下, 如果不加 ORDER BY create_time 就正常

hhjswf

Jun 26, 2023 via Android

可能要考虑建一条联合索引( create_time, user_id)。

hhjswf

Jun 26, 2023 via Android

你这样执行器还是要全盘扫查询 userid

iosyyy

Jun 26, 2023

create_time order_status status 建立联合索引 (user_id 如果是主机键就不用速度就提上来了

themostlazyman

Jun 26, 2023

筛选完数据量不大的话，根据数据的区分度大的一个 where 条件的字段建立索引就行了。例如 userId ，你得根据 sql 的执行顺序建立联合索引才行，单一个 create_time 索引不行，建议删除 create_time 索引，order_status 等区分度低的索引也建议删除。

themostlazyman

Jun 26, 2023

@xpyusrs 你的问题是索引建多了，删掉 idx orders create time 应该就没问题了，idx orders order status,idx orders status 状态就几个状态值的话也建议删除。

MoYi123

Jun 26, 2023

建一个联合索引 (user_id, create_time) , 这样 user_id = 10 order by create_time 就可以直接利用索引.

nothingistrue

Jun 26, 2023

排序扫得不是全表，是你 Where 筛选之后的实际数据。一旦 Where 筛选之后的数据量仍然很大，这时候不论是计数还是排序都是超级慢——比直接盲取前 100 条数据能慢上几十倍。

这种情况，我这里目前是无法优化的，只能是不做排序，或者将排序字段换成主键（对于 Mysql Innodb 引擎来说，因为是聚集索引，单独按照主键排序的时候实质上是无需排序的）。

8355

Jun 26, 2023

如果按照你前面说的 create_time 是 bigint 时间戳
最简单的要么 order by id desc 直接自增主键做排序只要加 limit 不会慢
要么 create_time 加索引但是需要考虑当前表数据量考虑加索引的成本

mercurius

Jun 26, 2023

从你给的 explain 图来看，走的是 [idx_orders_create_time] 索引，假设这个索引只有 create_time 这一个字段，这意味着它在底层查询实际上是这样的：

从创建时间开始降序查询，每条数据都去回表，查询是否满足条件为 [ WHERE user_id = 10 AND order_status IN ('PROCESSED') AND status = 'pending_orders' AND shop_id IN ('123456')] 的数据，捞够 20 条才停止。

某种意义上来说，跟全表扫描差不多……

seeyourface

Jun 26, 2023

你的 MYSQL 版本是多少，如果版本太老的话没有索引下推的功能，导致通过索引查询出来的数据回表之后再去判断其他条件的符合情况，还有你这个[idx_orders_create_time] 索引是只建立在 create_time 字段上的？

xuanbg

Jun 26, 2023

不全表扫描排序怎么给你 limit 20 啊。解决这个问题也很简单，加索引呀。

wxf666

Jun 26, 2023

如果你每个用户的订单不多的话，加个 force index(idx_orders_user_id)，应该能极大提速。

感觉 idx_orders_order_status 、idx_orders_status 、idx_orders_create_time 是没啥用的索引（要用它们，一般都会大量回表），建议删除

想更快一些，应该建个联合索引，甚至覆盖索引

luman

Jun 26, 2023

SELECT * FROM `orders` USE INDEX (idx_orders_user_id) WHERE user_id = 10 AND order_status IN ('PROCESSED') AND status = 'pending_orders' AND shop_id IN ('123456') ORDER BY create_time desc LIMIT 20;

试试

asmile1993

Jun 26, 2023

我估计楼主在 create_time 字段上建立了单列索引，查询具有 ORDER BY 或 GROUP BY 和 LIMIT 子句的情况下，控制优化器是否尝试使用有序索引而不是无序索引、文件排序或其他优化。每当优化器确定使用它可以更快地执行查询时，默认情况下都会执行此优化。
因为这种算法不能处理所有可能的情况（部分原因是假设数据的分布总大体是均匀的），所以在某些情况下，这种优化可能是不可取的。在 MySQL 5.7.33 之前，无法禁用此优化，但在 MySQL 5.7.33 及更高版本中，虽然它仍然是默认行为，但可以通过将 prefer_ordering_index 标志设置为 off 来禁用它。可以参考下文档 https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html

解决方案 1：禁用 prefer_ordering_index 优化（不推荐），可能导致 pt 工具或其他根据 order by 的查询耗时过长（无法利用最优索引）
解决方案 2：order by create_time, 其他字段（推荐），加上除主键外的其他列，来达到禁用 create_time 上索引的效果
解决方案 3：使用 ignore index idx_name 来禁用掉 create_time 上的索引
解决方案 4：使用联合索引（推荐）

honamx

Jun 26, 2023

create_time 、user_id 建个组合索引就好，如果单个用户的订单量过多，可以考虑将 shop_id 和订单状态也加入到索引里

xpyusrs

Jun 26, 2023

@LeegoYih zui
@mercurius 你着个分析可能是对的, 因为要取最新的数据, 我看 Handler_read_next 里面的数值是扫描了全表

Ericcccccccc

Jun 26, 2023

数据库的排序就是比较朴素的排序方法, 你想想如果你人肉去数据库里捞对应的数据会怎么做, 就是先全捞出来然后按照 ctime 排序, 无解.

最好是 ctime 改成 id, 可能也符合你的语义.

xpyusrs

Jun 26, 2023

@asmile1993 大佬厉害, create_time 是建立了索引,跟你说的一样

Dlin

Jun 27, 2023

问问 GPT 吧：mysql 什么情况下不会使用 where 里面的索引而是使用 order by 字段的索引
答：

在 MySQL 中，当查询中包含 WHERE 条件和 ORDER BY 子句时，MySQL 通常会使用 WHERE 条件的索引来过滤数据，并使用 ORDER BY 字段的索引来排序结果。然而，有一些情况下 MySQL 可能会选择不使用 WHERE 条件的索引而是使用 ORDER BY 字段的索引，这种情况包括：

当 WHERE 条件的选择性非常低时，也就是 WHERE 条件匹配的记录数非常多时，MySQL 可能会认为全表扫描比索引扫描更快，因此会选择使用 ORDER BY 字段的索引来排序结果。

当 WHERE 条件中使用了函数或表达式，而这些函数或表达式无法使用索引进行优化时，MySQL 可能会选择使用 ORDER BY 字段的索引。

当 WHERE 条件中使用了不等于（<>）或不包含（ NOT IN ）操作符时，MySQL 可能会选择使用 ORDER BY 字段的索引。

当 WHERE 条件中使用了范围查询（ BETWEEN 、>、<、>=、<=）时，MySQL 可能会选择使用 ORDER BY 字段的索引。

这些情况下 MySQL 的查询优化器可能会选择使用 ORDER BY 字段的索引，而不使用 WHERE 条件的索引。

关于这个问题的更多信息，你可以参考以下链接：

https://dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html
https://www.percona.com/blog/2009/03/05/what-to-do-when-mysql-ignores-useful-indexes/
https://www.xaprb.com/blog/2006/07/04/how-to-exploit-mysql-index-optimizations/
请注意，具体的查询优化行为可能会受到 MySQL 版本、表结构、数据分布等因素的影响。因此，在实际应用中，最好根据具体情况进行性能测试和优化