Query-Performance
升級到 MySQL 8 後的性能問題
更新（tl；博士；）：
我在這裡送出了一個錯誤報告：https ://bugs.mysql.com/bug.php?id=99593 ，該報告已被確認並提供了解決方法。有關詳細資訊，請參閱下面的答案。
某些查詢似乎在 MySQL 8.0.20 下苦苦掙扎，我想知道是否有人可以指出一些可能的解決方案。目前我的舊伺服器啟動並執行，仍然在 5.7.30 上，所以很容易 A/B 性能結果。兩台伺服器都有 32GB 的 RAM，幾乎相同的配置，所有的表都是 InnoDB。以下是一些（相關）設置：
innodb_flush_log_at_trx_commit = 0 innodb_flush_method = O_DIRECT innodb_file_per_table = 1 innodb_buffer_pool_instances = 12 innodb_buffer_pool_size = 16G innodb_log_buffer_size = 256M innodb_log_file_size = 1536M innodb_read_io_threads = 64 innodb_write_io_threads = 64 innodb_io_capacity = 5000 innodb_thread_concurrency = 0範例 1：
SELECT DISTINCT vehicle_id, submodel_id, store_id FROM product_to_store pts JOIN product_to_vehicle ptv USING (product_id) WHERE vehicle_id != 0 AND pts.store_id = 21;此查詢產生以下解釋：
MySQL 8.0.20（查詢需要 24 秒）：
+----+-------------+-------+------------+------+-------------------------------------------+--------------------------+---------+----------------+--------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+-------------------------------------------+--------------------------+---------+----------------+--------+----------+------------------------------+ | 1 | SIMPLE | pts | NULL | ref | PRIMARY,product_id,store_id,store_product | store_id | 4 | const | 813308 | 100.00 | Using index; Using temporary | | 1 | SIMPLE | ptv | NULL | ref | product_vehicle_submodel,vehicle_product | product_vehicle_submodel | 4 | pts.product_id | 53 | 50.00 | Using where; Using index | +----+-------------+-------+------------+------+-------------------------------------------+--------------------------+---------+----------------+--------+----------+------------------------------+MySQL 5.7.30（查詢需要 12 秒）：
+----+-------------+-------+------------+------+-------------------------------------------+--------------------------+---------+----------------+--------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+-------------------------------------------+--------------------------+---------+----------------+--------+----------+------------------------------+ | 1 | SIMPLE | pts | NULL | ref | PRIMARY,product_id,store_id,store_product | store_product | 4 | const | 547242 | 100.00 | Using index; Using temporary | | 1 | SIMPLE | ptv | NULL | ref | product_vehicle_submodel,vehicle_product | product_vehicle_submodel | 4 | pts.product_id | 22 | 50.00 | Using where; Using index | +----+-------------+-------+------------+------+-------------------------------------------+--------------------------+---------+----------------+--------+----------+------------------------------+有問題的兩個表在兩台伺服器上都是相同的。在這種情況下，計劃看起來有點不同，但我還有其他這樣的：
範例 2：
SELECT DISTINCT vehicle_type_id, vehicle_type_name FROM base_vehicle bv INNER JOIN vehicle_type vt USING (vehicle_type_id);這在兩台伺服器上產生相同的解釋，但在 MySQL 5.7 上平均為 0.07 秒，在 MySQL 8 上平均為 0.30 秒，大約是 4 倍！
+----+-------------+-------+------------+-------+-----------------+-------------------+---------+--------------------+------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+-----------------+-------------------+---------+--------------------+------+----------+------------------------------+ | 1 | SIMPLE | vt | NULL | index | PRIMARY | vehicle_type_name | 194 | NULL | 11 | 100.00 | Using index; Using temporary | | 1 | SIMPLE | bv | NULL | ref | vehicle_type_id | vehicle_type_id | 2 | vt.vehicle_type_id | 6428 | 100.00 | Using index | +----+-------------+-------+------------+-------+-----------------+-------------------+---------+--------------------+------+----------+------------------------------+在這一點上，我完全不知所措，希望有人能幫助說明昇級後可能導致性能如此糟糕的原因。
**更新：**根據要求，以下是上述查詢中涉及的表的表架構：
CREATE TABLE `product_to_store` ( `product_id` int NOT NULL, `store_id` int NOT NULL, PRIMARY KEY (`product_id`,`store_id`), KEY `product_id` (`product_id`), KEY `store_id` (`store_id`), KEY `store_product` (`store_id`,`product_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `product_to_vehicle` ( `product_to_vehicle_id` int NOT NULL AUTO_INCREMENT, `product_id` int NOT NULL, `vehicle_id` mediumint NOT NULL DEFAULT '0', `submodel_id` smallint NOT NULL DEFAULT '0', PRIMARY KEY (`product_to_vehicle_id`), KEY `submodel_id` (`submodel_id`), KEY `product_vehicle_submodel` (`product_id`,`vehicle_id`,`submodel_id`), KEY `vehicle_product` (`vehicle_id`,`product_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `vehicle_type` ( `vehicle_type_id` smallint NOT NULL AUTO_INCREMENT, `vehicle_type_name` varchar(64) NOT NULL, PRIMARY KEY (`vehicle_type_id`), KEY `vehicle_type_name` (`vehicle_type_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `base_vehicle` ( `vehicle_id` mediumint NOT NULL AUTO_INCREMENT, `year` smallint NOT NULL DEFAULT '0', `make_id` smallint NOT NULL DEFAULT '0', `model_id` mediumint NOT NULL DEFAULT '0', `vehicle_type_id` smallint NOT NULL DEFAULT '0', PRIMARY KEY (`vehicle_id`), KEY `make_id` (`make_id`), KEY `model_id` (`model_id`), KEY `year_make` (`year`,`make_id`), KEY `year_model` (`year`,`model_id`), KEY `vehicle_type_id` (`vehicle_type_id`), KEY `ymm` (`year`,`make_id`,`model_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;表狀態： 有趣的
TABLE_ROWS是，兩者都是錯誤的。select count(1) from product_to_vehicle;在這兩種情況下都給我 18330148 和 8.0 表是轉儲和導入到 8.0 的結果，所以沒有理由這些應該不同。8.0.20 上的表狀態
+--------------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+ | TABLE_NAME | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | +--------------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+ | base_vehicle | InnoDB | 10 | Dynamic | 72210 | 36 | 2637824 | 0 | 12681216 | 4194304 | 150814 | 2020-05-14 04:16:34 | NULL | NULL | utf8_general_ci | NULL | | | product_to_store | InnoDB | 10 | Dynamic | 2636946 | 32 | 86622208 | 0 | 124452864 | 5242880 | NULL | 2020-05-14 04:24:26 | 2020-05-14 04:31:18 | NULL | utf8_general_ci | NULL | | | product_to_vehicle | InnoDB | 10 | Dynamic | 22502991 | 50 | 1147092992 | 0 | 1274970112 | 7340032 | 23457421 | 2020-05-14 05:15:41 | 2020-05-14 05:24:36 | NULL | utf8_general_ci | NULL | | | vehicle_type | InnoDB | 10 | Dynamic | 11 | 1489 | 16384 | 0 | 16384 | 0 | 2190 | 2020-05-14 04:29:15 | NULL | NULL | utf8_general_ci | NULL | | +--------------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+5.7.30 上的表狀態
+--------------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+ | TABLE_NAME | Engine | Version | Row_format | table_rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | table_collation | Checksum | Create_options | +--------------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+ | base_vehicle | InnoDB | 10 | Dynamic | 70716 | 52 | 3686400 | 0 | 11124736 | 4194304 | 150814 | 2020-05-14 01:04:16 | NULL | NULL | utf8_general_ci | NULL | | | product_to_store | InnoDB | 10 | Dynamic | 2517116 | 39 | 99270656 | 0 | 144637952 | 7340032 | NULL | 2020-05-08 22:36:31 | NULL | NULL | utf8_general_ci | NULL | | | product_to_vehicle | InnoDB | 10 | Dynamic | 15627279 | 37 | 584024064 | 0 | 1739882496 | 685768704 | 23457421 | 2020-05-14 01:03:35 | NULL | NULL | utf8_general_ci | NULL | | | vehicle_type | InnoDB | 10 | Dynamic | 11 | 1489 | 16384 | 0 | 16384 | 0 | 2190 | 2020-05-08 22:36:31 | NULL | NULL | utf8_general_ci | NULL | | +--------------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+更多分析：
8.0.20
EXPLAIN ANALYZE SELECT DISTINCT vehicle_id, submodel_id, store_id FROM product_to_store pts JOIN product_to_vehicle ptv USING (product_id) WHERE vehicle_id != 0 AND pts.store_id = 21; | -> Table scan on <temporary> (actual time=0.001..3.453 rows=60193 loops=1) -> Temporary table with deduplication (actual time=27786.823..27795.343 rows=60193 loops=1) -> Nested loop inner join (cost=3222988.86 rows=14633875) (actual time=0.064..6910.370 rows=8610547 loops=1) -> Index lookup on pts using store_id (store_id=21) (cost=81628.75 rows=813308) (actual time=0.041..176.566 rows=420673 loops=1) -> Filter: (ptv.vehicle_id <> 0) (cost=0.26 rows=18) (actual time=0.006..0.014 rows=20 loops=420673) -> Index lookup on ptv using product_vehicle_submodel (product_id=pts.product_id) (cost=0.26 rows=36) (actual time=0.006..0.011 rows=20 loops=420673)5.7.30
EXPLAIN format = JSON SELECT DISTINCT vehicle_id, submodel_id, store_id FROM product_to_store pts JOIN product_to_vehicle ptv USING (product_id) WHERE vehicle_id != 0 AND pts.store_id = 21; { "query_block": { "select_id": 1, "cost_info": { "query_cost": "2711880.30" }, "duplicates_removal": { "using_temporary_table": true, "using_filesort": false, "nested_loop": [ { "table": { "table_name": "pts", "access_type": "ref", "possible_keys": [ "PRIMARY", "product_id", "store_id", "store_product" ], "key": "store_product", "used_key_parts": [ "store_id" ], "key_length": "4", "ref": [ "const" ], "rows_examined_per_scan": 547242, "rows_produced_per_join": 547242, "filtered": "100.00", "using_index": true, "cost_info": { "read_cost": "1067.75", "eval_cost": "109448.40", "prefix_cost": "110516.15", "data_read_per_join": "8M" }, "used_columns": [ "product_id", "store_id" ] } }, { "table": { "table_name": "ptv", "access_type": "ref", "possible_keys": [ "product_vehicle_submodel", "vehicle_product" ], "key": "product_vehicle_submodel", "used_key_parts": [ "product_id" ], "key_length": "4", "ref": [ "pts.product_id" ], "rows_examined_per_scan": 18, "rows_produced_per_join": 5097113, "filtered": "50.00", "using_index": true, "cost_info": { "read_cost": "562530.32", "eval_cost": "1019422.75", "prefix_cost": "2711880.30", "data_read_per_join": "77M" }, "used_columns": [ "product_to_vehicle_id", "product_id", "vehicle_id", "submodel_id" ], "attached_condition": "(`ptv`.`vehicle_id` <> 0)" } } ] } } }這是第二個查詢的比較：
8.0.20
{ "query_block": { "select_id": 1, "cost_info": { "query_cost": "7186.24" }, "duplicates_removal": { "using_temporary_table": true, "using_filesort": false, "nested_loop": [ { "table": { "table_name": "vt", "access_type": "index", "possible_keys": [ "PRIMARY" ], "key": "vehicle_type_name", "used_key_parts": [ "vehicle_type_name" ], "key_length": "194", "rows_examined_per_scan": 11, "rows_produced_per_join": 11, "filtered": "100.00", "using_index": true, "cost_info": { "read_cost": "0.25", "eval_cost": "1.10", "prefix_cost": "1.35", "data_read_per_join": "2K" }, "used_columns": [ "vehicle_type_id", "vehicle_type_name" ] } }, { "table": { "table_name": "bv", "access_type": "ref", "possible_keys": [ "vehicle_type_id" ], "key": "vehicle_type_id", "used_key_parts": [ "vehicle_type_id" ], "key_length": "2", "ref": [ "vt.vehicle_type_id" ], "rows_examined_per_scan": 6519, "rows_produced_per_join": 71712, "filtered": "100.00", "using_index": true, "cost_info": { "read_cost": "13.69", "eval_cost": "7171.20", "prefix_cost": "7186.24", "data_read_per_join": "1M" }, "used_columns": [ "vehicle_id", "vehicle_type_id" ] } } ] } } }5.7.30
{ "query_block": { "select_id": 1, "cost_info": { "query_cost": "14684.01" }, "duplicates_removal": { "using_temporary_table": true, "using_filesort": false, "nested_loop": [ { "table": { "table_name": "vt", "access_type": "index", "possible_keys": [ "PRIMARY" ], "key": "vehicle_type_name", "used_key_parts": [ "vehicle_type_name" ], "key_length": "194", "rows_examined_per_scan": 11, "rows_produced_per_join": 11, "filtered": "100.00", "using_index": true, "cost_info": { "read_cost": "1.00", "eval_cost": "2.20", "prefix_cost": "3.20", "data_read_per_join": "2K" }, "used_columns": [ "vehicle_type_id", "vehicle_type_name" ] } }, { "table": { "table_name": "bv", "access_type": "ref", "possible_keys": [ "vehicle_type_id" ], "key": "vehicle_type_id", "used_key_parts": [ "vehicle_type_id" ], "key_length": "2", "ref": [ "vt.vehicle_type_id" ], "rows_examined_per_scan": 6647, "rows_produced_per_join": 73126, "filtered": "100.00", "using_index": true, "cost_info": { "read_cost": "55.61", "eval_cost": "14625.20", "prefix_cost": "14684.01", "data_read_per_join": "1M" }, "used_columns": [ "vehicle_id", "vehicle_type_id" ] } } ] } } }奇怪的是，這些數字似乎表明 MySQL 8 的總體成本較低，但執行速度仍然慢得多。
Shane Bester 提出了一個建議作為對我的錯誤報告的評論，它解釋了這是一個已知錯誤，將在下一個版本中解決。更重要的是，我嘗試了一種解決方法並取得了巨大成功。感謝 Shane，無論您身在何處！
internal_tmp_mem_storage_engine=MEMORY感謝提供測試數據。在 8.0.20 上找到了一種解決方法來提高速度：
SET GLOBAL internal_tmp_mem_storage_engine=MEMORY;看來這個錯誤是內部送出的副本：
錯誤 30562964：8.0.18：在 8.0.18 中引入並在 8.0.21 中修復的 SELECT DISTINCT 中的性能回歸。
我已經在 8.0.17 和目前的 8.0 內部版本上確認了這一點。性能回歸消失了。
因此，更改 internal_tmp_mem_storage_engine 應該會有所幫助，直到 8.0.21 發布（沒有 ETA）。