order by 和limit 连用的坑

sql如下

SELECT
	*
		FROM
			tb_system_order so 
		WHERE
			so.org_id IN ( '52e47c02-845e-4fe3-961b-38109551c619' ) 
			AND so.item_id IN ( '44c106fb-e57b-4736-a375-1380d041d099' ) 
			AND ( so.STATUS = '1' ) 
			AND ( so.create_time >= '2022-01-25 00:00:00' ) 
			AND ( so.create_time < '2022-02-09 00:00:00' ) 
			AND 1 = 1 
			AND 1 = 1 
		ORDER BY
			SYSTEM_ORDER_NO ASC 
			limit 0,10
			
				LIMIT 10 	OFFSET 0 

• 前提
  where 子句的每个字段都有索引， orderby 字段也有索引。
  结果集全集386条，表总数据1kw+

• 现象
  单独 order by， 或者使用limit 都没问题， 查询在1s，
  连用，一分钟才查到。

• 解决方式
  1、加上IGNORE INDEX(Index_sysorder_orderNo)，不走orderby子句的索引。
  2、或者orderby 字段加上运算，有运算的列不会走索引，但是这样效率会低，不如第一种。

SELECT
	*
		FROM
			tb_system_order so 
-- 			IGNORE INDEX(Index_sysorder_orderNo)
		WHERE
			so.org_id IN ( '52e47c02-845e-4fe3-961b-38109551c619' ) 
			AND so.item_id IN ( '44c106fb-e57b-4736-a375-1380d041d099' ) 
			AND ( so.STATUS = '1' ) 
			AND ( so.create_time >= '2022-01-25 00:00:00' ) 
			AND ( so.create_time < '2022-02-09 00:00:00' ) 
			AND 1 = 1 
			AND 1 = 1 
		ORDER BY
			SYSTEM_ORDER_NO ASC 
				LIMIT 10 	OFFSET 0 
				
-- ********************------------------

SELECT
	*
		FROM
			tb_system_order so 
-- 			IGNORE INDEX(Index_sysorder_orderNo)
		WHERE
			so.org_id IN ( '52e47c02-845e-4fe3-961b-38109551c619' ) 
			AND so.item_id IN ( '44c106fb-e57b-4736-a375-1380d041d099' ) 
			AND ( so.STATUS = '1' ) 
			AND ( so.create_time >= '2022-01-25 00:00:00' ) 
			AND ( so.create_time < '2022-02-09 00:00:00' ) 
			AND 1 = 1 
			AND 1 = 1 
		ORDER BY
			SYSTEM_ORDER_NO+'' ASC 
				LIMIT 10 	OFFSET 0 

• 原因
  orderby和 limit连用后走的是order by的索引， 而这个字段不在where子句里面 ， 基本上等于全表扫描

• 相关材料
  https://zhuanlan.zhihu.com/p/356463167

mysql 官方文档 https://dev.mysql.com/doc/refman/8.0/en/limit-optimization.html

For a query with an ORDER BY or GROUP BY and a LIMIT clause, the optimizer tries to choose an ordered index by default when it appears doing so would speed up query execution. Prior to MySQL 8.0.21, there was no way to override this behavior, even in cases where using some other optimization might be faster. --引自官方文档 ， 所以在有order by 或者groupby 的时候， 默认走的是有序索引。 nnd