自学内容网 自学内容网
自学内容网 > 正文

SQL优化 - 排序

🕗 发布于 2024-10-04 18:08 sql  

文章目录

排序和索引

如果ORDER BY操作使用了索引，那么就可以避免排序操作，因为索引本身就是按索引 key 排好序的。那什么情况下，ORDER BY会走索引呢？

例如：sakila.rental 表有一个联合索引rental_date(rental_date, inventory_id, customer_id)

在这里插入图片描述
下面来看SELECT * FROM rental ORDER BY rental_date, inventory_id;这条 sql 是否走索引

mysql> EXPLAIN SELECT * FROM rental ORDER BY rental_date, inventory_id;
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+----------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra          |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+----------------+
|  1 | SIMPLE      | rental | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 16008 |   100.00 | Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

从执行计划来看是没走索引的，因为使用了SELECT *。在这种情况下，扫描整个索引并查找表行以查找不在索引中的列可能比扫描表并对结果进行排序成本更高，因此优化器可能不会使用索引。如果SELECT *仅选择索引列，也就是使用索引覆盖策略，则可以使用索引来避免排序。例如：

EXPLAIN SELECT inventory_id, rental_date, customer_id 
FROM rental ORDER BY rental_date, inventory_id;

那是不是SELECT *都不走索引？

如果索引不完全包含查询的所有列，需要回表的话，则需要WHERE子句的选择性足以使索引范围扫描比表扫描成本更低，则优化器会选择使用索引。也就是让索引的所有未使用部分和所有额外的ORDER BY列都是WHERE子句中的常量条件。这样即使ORDER BY的列与索引不完全匹配，也可以使用索引。例如：

SELECT * FROM t1 WHERE key_part1 = constant
  ORDER BY key_part2;

降序索引

https://dev.mysql.com/doc/refman/8.0/en/descending-indexes.html
如果索引的排序和ORDER BY的排序不一致，可以考虑使用降序索引

Filesort

如果ORDER BY不满足用索引的条件，MySQL将执行文件排序操作，读取表的行并对其进行排序。

从MySQL 8.0.12开始，优化器会根据需要增量地分配内存缓冲区（叫做 Sort Buffer），直到超过sort_buffer_size的大小，而MySQL 8.0.12之前，是直接预先分配固定数量的sort_buffer-size字节大小的内存缓冲区。将sort_buffer_size设置为更大的值来加速更大的排序。增量分配的这种方式可以在小排序发生时不会占用过多的内存。

如果结果集太大（超过sort_buffer_size）而无法放入内存，文件排序操作会根据需要使用临时磁盘文件，这种情况性能就比较差了

mysql> EXPLAIN SELECT * FROM rental ORDER BY inventory_id;
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+----------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra          |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+----------------+
|  1 | SIMPLE      | rental | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 16008 |   100.00 | Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

ORDER BY 顺序问题

如果order by列有相同的值，那么MySQL可以自由地以任何顺序返回这些行。换言之，只要order by列的值不重复，就可以保证返回的顺序。可以在order by子句中包含附加列，以使顺序具有确定性。为了保证每次都返回的顺序一致可以额外增加一个排序字段（比如：id），用两个字段来尽可能减少重复的概率

MySQL具体的排序策略受以下几个因素影响：

  1. 是否可用的索引
  2. 预期结果大小
  3. MySQL版本

一般来说，排序处理过程如下

  1. 读取与WHERE子句匹配的行
  2. 对于每一行，记录一个由排序 key 和行位置组成的值元组，以及查询所需的列
  3. 按排序 key 的值对元组进行排序
  4. 按排序好的顺序根据行未知检索行，但直接从排序的元组中读取所需的列，而不是第二次访问表

ORDER BY + LIMIT

对于order by查询，带或者不带limit可能返回行的顺序是不一样的。

如果limit row_count 与 order by 一起使用，那么在找到第一个row_count就停止排序，直接返回。

例如平时开发常见的分页查询

SELECT * FROM rental ORDER BY inventory_id LIMIT 1, 10

会导致多页出现同一条数据
在这里插入图片描述
如果想在limit存在或不存在的情况下，都保证排序结果相同，可以额外加一个排序条件。例如id字段是唯一的，可以考虑在排序字段中额外加个id排序去确保顺序稳定。这样分页的问题就解决了。

SELECT * FROM rental ORDER BY rental_id, inventory_id LIMIT 1, 10

原文地址：https://blog.csdn.net/qq_40926260/article/details/142702707

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

相关文章

自学内容网
Copyright © 2015-2024