SQL优化 - Limit优化
文章目录
- 优化器策略
- prefer_ordering_index
- 深分页问题
- 优化方式
- 子查询+索引
- 上一次分页最大ID
- 按业务限制
https://dev.mysql.com/doc/refman/8.4/en/limit-optimization.html
优化器策略
下面是一些优化器处理LIMIT使用的一些策略：
- 如果使用LIMIT只选择几行，MySQL在某些情况下会使用索引，而一般情况下会进行全表扫描
EXPLAIN SELECT * FROM student LIMIT 1; // 即使查1条数据, type = All
LIMIT row_count和ORDER BY同时使用
MySQL在找到排序结果的row_count行后会立即停止排序，而不是对所有数据进行排序。如果使用索引进行排序，则速度非常快。如果要进行filesort，则在找到row_count行之前，所有不带LIMIT子句能查出来的行都会被查询，并对其中大部分或全部行进行排序。在找到row_count行之后，MySQL不会对结果集的任何剩余部分进行排序。
如果ORDER BY排序字段存在重复值，那么结果的顺序可能是不同的，也就是说在分页查询中可能会出现多页出现同一条数据的情况，数据重复。
为了避免这种情况，可以在 SQL 查询语句中使用唯一的排序字段来进行排序，或者在 SQL 查询语句中增加一个唯一的排序条件，例如使用 id 字段进行排序，这样可以确保每条记录在排序后都是唯一的。
-
如果将
LIMIT row_count与DISTINCT结合使用，MySQL会在发现row_count唯一行时立即停止。 -
在某些情况下，处理
GROUP BY时可以通过按顺序读取索引（或对索引进行排序），然后进行聚合，直到索引值发生变化。在这种情况下，LIMIT row_count不会计算任何不必要的GROUP BY值。 -
LIMIT 0会快速返回
-
如果server层使用临时表，会使用LIMIT来计算临时表需要多大空间
-
如果ORDER BY没用上索引，同时存在LIMIT子句，优化器可能采用内存filesort操作
prefer_ordering_index
https://dev.mysql.com/doc/refman/8.4/en/switchable-optimizations.html#optflag_prefer-ordering-index
对于具有ORDER BY或GROUP BY和LIMIT子句的查询，优化器会在默认情况下尝试选择有序的索引，这样做会加快查询执行。在使用其他优化可能更快的情况下，可以通过将optimizer_switch系统变量的prefer_ordering_index标志设置为关闭来关闭此优化。
mysql> SELECT @@optimizer_switch LIKE '%prefer_ordering_index=on%';
+------------------------------------------------------+
| @@optimizer_switch LIKE '%prefer_ordering_index=on%' |
+------------------------------------------------------+
| 1 |
+------------------------------------------------------+
深分页问题
分页查询通常需要指定每页要显示的记录数量（pageSize）和要查询的目标页数（pageIndex）。在进行分页查询时，通常会使用 LIMIT offset，pageSize 的方式来指定要查询的记录范围。
SELECT *
FROM table_name
LIMIT (pageIndex - 1) * pageSize, pageSize
因为 MySQL 需要扫描大量数据才能找到指定页的数据，pageIndex 越大，查询耗时就越久。深分页查询指的就是在分页查询中，页码比较大的情况。
优化方式
子查询+索引
通过将 select * 转变为 select id，把符合条件的 id 筛选出来后，最后通过嵌套查询的方式按顺序取出 id 对应的行。
上一次分页最大ID
将上一页的查询结果中的最大 id 作为下一页查询的 where 条件，这样可以大幅减少扫描行数，提高查询性能。只要索引设置得当，可以提供与浅分页近似的查询性能。
这种方式需要前端的分页组件不能支持按页码跳转（比如下面这种），只能一页一页的翻，否则用户可以直接跳到最后一页
按业务限制
限制只能查近3天，近一周，近一个月的数据，有些历史数据其实查询是没多大意义的