1.背景及分析

近期，某客戶完成對數據庫 MySQL 5.7 到 8.0 的版本升級，升級后查詢性能顯著變慢。

原來是 MySQL 8.0 對某些 ORDER BY 相關的參數修改，導致了優化器不生效。下面我們進入本次的 SQL 優化分析，也建議升級后有類似情況的讀者自檢。

分析過程

首先，查看慢日志及對應的表結構。

• 慢 SQL：select * from xx where xx order by xx limit xx.
• 執行計劃：發現  order by 的字段沒有索引，若加上索引可從原來的 4 秒變為毫秒級別。

在升級前（MySQL 5.7），該字段沒有索引，查詢只需 1 秒左右，需要找到這個原因。

通過執行計劃（profile，trace 等方式）對比了升級前后的區別，發現只有 profile 會有明顯的區別。其中，MySQL 5.7 的耗時主要在 Creating sort index 階段，而 MySQL 8.0 的耗時都是在執行階段。

在 MySQL 8.0 中 SELECT 少數字段時間也在 1 秒左右。隨著 SELECT 查詢的字段增多，時間也越來越長。當 select * 時能達到 4 秒，而 MySQL 5.7 中不管多少字段都是 1 秒左右。

根據以上信息可以推測，變慢主要在排序環節，需要進一步了解 MySQL 8.0 的排序方式發生了哪些改變。

通過 MySQL 官網文檔^[1] 可知：

• MySQL 8.0.20 之前的版本：排序跟 max_length_for_sort_data 參數有關。當需要排序的行的大小大于參數設置對應的值時（byte），會使用 row_id 排序，反之使用全字段排序。通過測試，在 MySQL 5.7 版本時，設置參數的值若大于所有列對應的大小，select * 查詢也需要耗時 4 秒左右。
• MySQL 8.0.20 及之后的版本：max_length_for_sort_data 參數被廢棄，不再生效。

官網中對該參數的調整說明

分析完畢，下面我們將進行驗證。

2.驗證測試

在本次升級涉及的具體版本是 MySQL 5.7.44 和 MySQL 8.0.30。根據上面的分析過程，推斷 MySQL 5.7.44 版在涉及到排序查詢時會受到 max_length_for_sort_data 的影響，而 MySQL 8.0.30 則不會。

數據準備

在 MySQL 5.7 和 8.0 版本庫中建表并插入 400W 行數據。

CREATE TABLE `t` (
`id` int NOT NULL AUTO_INCREMENT,
`create_date` datetime DEFAULT NULL,
`status` int DEFAULT NULL,
`col1` varchar(50) DEFAULT NULL,
`col2` varchar(50) DEFAULT NULL,
`col3` varchar(50) DEFAULT NULL,
`col4` varchar(50) DEFAULT NULL,
`col5` varchar(50) DEFAULT NULL,
`col6` varchar(50) DEFAULT NULL,
`col7` varchar(50) DEFAULT NULL,
`col8` varchar(50) DEFAULT NULL,
`col9` varchar(50) DEFAULT NULL,
`col10` varchar(50) DEFAULT NULL,
`col11` varchar(255) DEFAULT NULL,
`col12` varchar(255) DEFAULT NULL,
`col13` varchar(255) DEFAULT NULL,
`col14` varchar(255) DEFAULT NULL,
`col15` varchar(255) DEFAULT NULL,
`col16` varchar(255) DEFAULT NULL,
`col17` varchar(255) DEFAULT NULL,
`col18` varchar(255) DEFAULT NULL,
`col19` varchar(255) DEFAULT NULL,
`col20` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- 插入數據過程略

select count(*) from `t`;
+-------------+
|  count( * ) |
+-------------+
|     4194304 |
+-------------+
1 row in set (0.11 sec)

在 MySQL 5.7 和 8.0 版本環境中執行（參數配置一致），分別執行查詢三個字段和查詢所有字段兩種 SELECT 語句。

-- 查詢三個字段
select id,create_date,status from t  where status=1 order by create_date desc limit 1;

-- 查詢所有字段
select * from t  where status=1 order by create_date desc limit 1;

MySQL 5.7 的兩種 SELECT 語句執行時間均為 1 秒左右。

MySQL 5.7 兩種查詢對比

MySQL 8.0 查詢三個字段 1 秒左右，查詢所有字段則為 4 秒左右。

MySQL 8.0 兩種查詢對比

在 MySQL 8.0.30 查詢的字段越多，時間越長。

對比查詢 7 個字段和 11 個字段

若在 MySQL 5.7.44 中，把 max_length_for_sort_data 參數的值設置大于所有列的大小時，查詢時間也會變慢（全字段排序）。

3.結論及優化方案

MySQL 8.0.20 及之后的版本，針對無索引的排序方式發現改變。不會再通過 max_length_for_sort_data 參數來判斷，而是通過查詢的字段和排序的字段大小動態來進行排序。所以在查詢列較多時會導致比 MySQL 5.7 更慢。

最好的解決方式是給排序字段加上索引 :)

參考資料

[1]order-by-optimization: https://dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html

作者：龔唐杰，愛可生 DBA 團隊成員，主要負責 MySQL 技術支持，擅長 MySQL、PG、國產數據庫。