Order by 优化

通过索引顺序扫描直接返回有序数据

通过对返回数据进行排序,即 FileSort 排序。

所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。FileSort 并不代表通过磁盘文件进行排序,而只是说进行了一个排序操作,至于排序操作是否使用了磁盘文件或临时表取决于 MySQL 服务器对排序参数的设置和需要排序数据的大小。

EXPLAIN 排序分析

customer DDL

CREATE TABLE `customer` ( `customer_id` smallint unsigned NOT NULL AUTO_INCREMENT, `store_id` tinyint unsigned NOT NULL, `first_name` varchar(45) NOT NULL, `last_name` varchar(45) NOT NULL, `email` varchar(50) DEFAULT NULL, `address_id` smallint unsigned NOT NULL, `active` tinyint(1) NOT NULL DEFAULT '1', `create_date` datetime NOT NULL, `last_update` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`customer_id`), KEY `idx_fk_store_id` (`store_id`), KEY `idx_fk_address_id` (`address_id`), KEY `idx_last_name` (`last_name`), KEY `idx_storeid_email` (`store_id`,`email`), CONSTRAINT `fk_customer_address` FOREIGN KEY (`address_id`) REFERENCES `address` (`address_id`) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT `fk_customer_store` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=600 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; CREATE DEFINER=`root`@`%` TRIGGER `customer_create_date` BEFORE INSERT ON `customer` FOR EACH ROW SET NEW.create_date = NOW();

返回所有用户记录,根据 customer_id 进行排序。因为 customer_id 是主键,记录都是按照主键排好序的,所以无需进行额外的排序操作,直接返回所有数据即可。

EXPLAIN SELECT * FROM customer ORDER BY customer_id;

Order by 优化

由于默认是根据 customer_id 进行排序的,相对于上面来说,这里需要全表扫描,然后根据 store_id 对全表扫描结果进行排序,因此这里用到了 FileSort 排序。

EXPLAIN SELECT * FROM customer ORDER BY store_id;

Order by 优化

store_id , email 这两个字段是有联合索引 idx_storeid_email 的,只查询 store_id 和 email 两个字段,直接通过联合索引所在的 B+ 树返回查询数据(该索引树先根据 store_id 字段先进行排序,然后再根据 email 字段排序好的),所以这里的查询结果就是排序好的。

EXPLAIN SELECT store_id , email FROM customer ORDER BY store_id;

Order by 优化

和上面不同的是,排序的字段是 email,导致 FileSort 排序的原因是联合索引 idx_storeid_email 的是先根据 store_id 字段先进行排序,然后再根据 email 字段排序好的,如果直接使用 email 排序,则无法使用 idx_storeid_email 索引树排好的顺序。

EXPLAIN SELECT store_id , email FROM customer ORDER BY email;

Order by 优化

ORDER BY 使用联合索引进行排序

EXPLAIN SELECT store_id , email FROM customer ORDER BY store_id , email;

Order by 优化

ORDER BY 的字段混用 ASC 和 DESC 会导致使用 FileSort 排序。

EXPLAIN SELECT store_id , email FROM customer ORDER BY store_id ASC , email DESC;

Order by 优化

固定 store_id = 1 情况下对 email 字段进行排序,使用 idx_storeid_email 索引即可

EXPLAIN SELECT store_id , email FROM customer WHERE store_id = 1 ORDER BY email;

Order by 优化

where 条件先进行 store_id 范围查询导致 ORDER BY email 字段无法使用 idx_storeid_email 索引进行排序。

EXPLAIN SELECT store_id , email FROM customer WHERE store_id >= 1 AND store_id <= 3 ORDER BY email;

Order by 优化

ORDER BY 可能出现 FileSort 的几种情况:

order by 字段混用 ASC 和 DESC 排序方式。

SELECT * 时,如果 order by 排序字段不是主键可能导致 FileSort。

联合索引情况下,order by 多字段排序的字段左右顺序和联合索引的字段左右顺序不一致导致 FileSort。

联合索引情况下,where 字段和 order by 字段的左右顺序和联合索引字段左右顺序或者 where 字段出现范围查询都可能导致 FileSort。

FileSort 优化

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/wsxfxp.html