1.背景及分析

近期，某客戶完成對(duì)數(shù)據(jù)庫(kù) MySQL 5.7 到 8.0 的版本升級(jí)，升級(jí)后查詢性能顯著變慢。

原來(lái)是 MySQL 8.0 對(duì)某些 ORDER BY 相關(guān)的參數(shù)修改，導(dǎo)致了優(yōu)化器不生效。下面我們進(jìn)入本次的 SQL 優(yōu)化分析，也建議升級(jí)后有類似情況的讀者自檢。

分析過(guò)程

首先，查看慢日志及對(duì)應(yīng)的表結(jié)構(gòu)。

• 慢 SQL：select * from xx where xx order by xx limit xx.
• 執(zhí)行計(jì)劃：發(fā)現(xiàn)  order by 的字段沒(méi)有索引，若加上索引可從原來(lái)的 4 秒變?yōu)楹撩爰?jí)別。

在升級(jí)前（MySQL 5.7），該字段沒(méi)有索引，查詢只需 1 秒左右，需要找到這個(gè)原因。

通過(guò)執(zhí)行計(jì)劃（profile，trace 等方式）對(duì)比了升級(jí)前后的區(qū)別，發(fā)現(xiàn)只有 profile 會(huì)有明顯的區(qū)別。其中，MySQL 5.7 的耗時(shí)主要在 Creating sort index 階段，而 MySQL 8.0 的耗時(shí)都是在執(zhí)行階段。

在 MySQL 8.0 中 SELECT 少數(shù)字段時(shí)間也在 1 秒左右。隨著 SELECT 查詢的字段增多，時(shí)間也越來(lái)越長(zhǎng)。當(dāng) select * 時(shí)能達(dá)到 4 秒，而 MySQL 5.7 中不管多少字段都是 1 秒左右。

根據(jù)以上信息可以推測(cè)，變慢主要在排序環(huán)節(jié)，需要進(jìn)一步了解 MySQL 8.0 的排序方式發(fā)生了哪些改變。

通過(guò) MySQL 官網(wǎng)文檔^[1] 可知：

• MySQL 8.0.20 之前的版本：排序跟 max_length_for_sort_data 參數(shù)有關(guān)。當(dāng)需要排序的行的大小大于參數(shù)設(shè)置對(duì)應(yīng)的值時(shí)（byte），會(huì)使用 row_id 排序，反之使用全字段排序。通過(guò)測(cè)試，在 MySQL 5.7 版本時(shí)，設(shè)置參數(shù)的值若大于所有列對(duì)應(yīng)的大小，select * 查詢也需要耗時(shí) 4 秒左右。
• MySQL 8.0.20 及之后的版本：max_length_for_sort_data 參數(shù)被廢棄，不再生效。

官網(wǎng)中對(duì)該參數(shù)的調(diào)整說(shuō)明

分析完畢，下面我們將進(jìn)行驗(yàn)證。

2.驗(yàn)證測(cè)試

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

數(shù)據(jù)準(zhǔn)備

在 MySQL 5.7 和 8.0 版本庫(kù)中建表并插入 400W 行數(shù)據(jù)。

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;

-- 插入數(shù)據(jù)過(guò)程略

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

在 MySQL 5.7 和 8.0 版本環(huán)境中執(zhí)行（參數(shù)配置一致），分別執(zhí)行查詢?nèi)齻€(gè)字段和查詢所有字段兩種 SELECT 語(yǔ)句。

-- 查詢?nèi)齻€(gè)字段
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 語(yǔ)句執(zhí)行時(shí)間均為 1 秒左右。

MySQL 5.7 兩種查詢對(duì)比

MySQL 8.0 查詢?nèi)齻€(gè)字段 1 秒左右，查詢所有字段則為 4 秒左右。

MySQL 8.0 兩種查詢對(duì)比

在 MySQL 8.0.30 查詢的字段越多，時(shí)間越長(zhǎng)。

對(duì)比查詢 7 個(gè)字段和 11 個(gè)字段

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

3.結(jié)論及優(yōu)化方案

MySQL 8.0.20 及之后的版本，針對(duì)無(wú)索引的排序方式發(fā)現(xiàn)改變。不會(huì)再通過(guò) max_length_for_sort_data 參數(shù)來(lái)判斷，而是通過(guò)查詢的字段和排序的字段大小動(dòng)態(tài)來(lái)進(jìn)行排序。所以在查詢列較多時(shí)會(huì)導(dǎo)致比 MySQL 5.7 更慢。

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

參考資料

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

作者：龔唐杰，愛(ài)可生 DBA 團(tuán)隊(duì)成員，主要負(fù)責(zé) MySQL 技術(shù)支持，擅長(zhǎng) MySQL、PG、國(guó)產(chǎn)數(shù)據(jù)庫(kù)。