MySQL join 在從 5.6 升級到 5.7 後性能急劇下降
從 MySQL 5.6.35 升級到 MySQL 5.7.12（在 AWS RDS 上執行）後，我有一個相當簡單的查詢變得非常慢。
SELECT DISTINCT Name,d.id,deviceType,issuedBy, description,avNum,CompanyName, BrandName,dwNumber,quant,discDate,Type FROM table_one d JOIN table_two i ON d.id = i.id;在 5.6 中，此查詢在 90 秒內完成。升級到 5.7 後，需要 30 多分鐘。每個表大約有 200 萬行。
我首先比較了 5.6 和 5.7 之間的優化器設置：
# 5.6 index_merge=on index_merge_union=on index_merge_sort_union=on index_merge_intersection=on engine_condition_pushdown=on index_condition_pushdown=on mrr=on mrr_cost_based=on block_nested_loop=on batched_key_access=off materialization=on semijoin=on loosescan=on firstmatch=on subquery_materialization_cost_based=on use_index_extensions=on# 5.7 Optimizer settings in 5.7: index_merge=on index_merge_union=on index_merge_sort_union=on index_merge_intersection=on engine_condition_pushdown=on index_condition_pushdown=on mrr=on mrr_cost_based=on block_nested_loop=on batched_key_access=off materialization=on semijoin=on loosescan=on firstmatch=on subquery_materialization_cost_based=on use_index_extensions=on condition_fanout_filter=on derived_merge=on duplicateweedout=on我看到的唯一變化是 5.7 中的最後三個選項。我將它們關閉如下：
SET optimizer_switch='condition_fanout_filter=off'; SET optimizer_switch='derived_merge=off'; SET optimizer_switch='duplicateweedout=off';這沒有效果。接下來，我查看了我的 innodb 設置的差異（我的 5.7 實例有更多記憶體，因此池大小存在差異）。
# my innodb changes 5.6 -> 5.7 innodb_adaptive_hash_index_parts added in 5.7, set to ‘8’ innodb_additional_mem_pool_size set to 8388608 in 5.6, removed in 5.7 innodb_buffer_pool_dump_at_shutdown changed from ‘OFF’ to ‘ON’ innodb_buffer_pool_dump_pct added in 5.7, set to ’25’ innodb_buffer_pool_load_at_startup changed from ‘OFF’ to ‘ON’ innodb_buffer_pool_size changed from 2,804,940,800 to 11,811,160,064 innodb_checksum_algorithm changed from ‘innodb’ to ‘crc32’ innodb_deadlock_detect added in 5.7, set to ‘ON’ innodb_default_row_format added in 5.7, set to ‘dynamic’ innodb_file_format changed from ‘Antelope’ to ‘Barracuda’ innodb_file_format_max changed from ‘Antelope’ to ‘Barracuda’ innodb_fill_factor added in 5.7 set to ‘100’ innodb_flush_sync added in 5.7 set to ‘ON’ innodb_log_checksums added in 5.7 set to ‘ON’ innodb_log_write_ahead_size added in 5.7 set to 8192 innodb_max_undo_log_size added in 5.7 set to 1,073,741,824 innodb_mirrored_log_groups removed in 5.7, set to ‘1’ in 5.6 innodb_numa_interleave added in 5.7 set to ‘OFF’ innodb_page_cleaners added in 5.7 set to ‘4’ innodb_purge_rseg_truncate_frequency added in 5.7 set to ‘128’ innodb_strict_mode changed from ‘OFF’ to ‘ON’ innodb_temp_data_file_path added in 5.7, set to 'ibtmp1:12M:autoextend' innodb_undo_log_truncate added in 5.7, set to ‘OFF’我查看了參考手冊 並嘗試關閉以下內容：
innodb_strict_mode=‘OFF’ innodb_deadlock_detect=‘OFF’ innodb_flush_sync=‘OFF’ innodb_log_checksums=‘OFF’而且，沒有效果。
我希望有人能告訴我這裡發生了什麼？該查詢是更大工作流程的一部分，因此無法使用。如果可能的話，我想恢復 5.6 的行為，但是由於我有限的 DBA 知識，我已經沒有東西可以嘗試了。我希望有人能指出我正確的方向或給我更多的調查途徑。
下面是我的表。許多列的寬度很大，它們大多被定義為
VARCHAR(X) DEFAULT NULL因為數據源不干淨（我無法控制源）。請注意，我需要 utf8 數據，但我在對慢速查詢進行故障排除時使用 latin1，以消除可能導致慢速的原因。（我們需要從 5.6 -> 5.7 遷移的原因是為了 5.7 中更大的索引大小，這可以容納我們更大的 UTF8 列大小）。（另外，我應該指出該
id欄位來自源數據，並且是一長串可變寬度的字母數字字元（最多 30 個字元），所以我需要它是一個 VARCHAR）。CREATE TABLE `table_one` ( `id` varchar(200) NOT NULL DEFAULT '', `RecordKey` varchar(50) DEFAULT NULL, `VersionStat` varchar(200) DEFAULT NULL, `Status` varchar(200) DEFAULT NULL, `VersionNumber` varchar(50) DEFAULT NULL, `VersionDate` varchar(20) DEFAULT NULL, `PublishDate` varchar(20) DEFAULT NULL, `DistStart` varchar(20) DEFAULT NULL, `DistCommStat` varchar(2000) DEFAULT NULL, `BrandName` varchar(100) DEFAULT NULL, `VersionModelNumber` varchar(100) DEFAULT NULL, `Catalog` varchar(100) DEFAULT NULL, `dwNumber` varchar(100) DEFAULT NULL, `CompanyName` varchar(500) DEFAULT NULL, `DeviceCount` varchar(200) DEFAULT NULL, `description` varchar(3000) DEFAULT NULL, `Exemption` varchar(1100) DEFAULT NULL, `PreMarket` varchar(1500) DEFAULT NULL, `DevDRMT` varchar(1000) DEFAULT NULL, `DTKit` varchar(200) DEFAULT NULL, `Combination` varchar(250) DEFAULT NULL, `Usage` varchar(500) DEFAULT NULL, `SingleBatch` varchar(50) DEFAULT NULL, `SerialNumber` varchar(250) DEFAULT NULL, `ManuDate` varchar(20) DEFAULT NULL, `ExpDate` varchar(20) DEFAULT NULL, `Donation` varchar(50) DEFAULT NULL, `LabeldWithMLO` varchar(50) DEFAULT NULL, `NLabledMLO` varchar(50) DEFAULT NULL, `MLOStatus` varchar(1000) DEFAULT NULL, `BTT` varchar(50) DEFAULT NULL, `OPP` varchar(50) DEFAULT NULL, `BRC` varchar(50) DEFAULT NULL, `PriorUse` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1CREATE TABLE `table_two` ( `id` varchar(200) DEFAULT NULL, `Name` varchar(200) DEFAULT NULL, `deviceType` varchar(50) DEFAULT NULL, `issuedBy` varchar(200) DEFAULT NULL, `avNum` varchar(50) DEFAULT NULL, `quant` varchar(50) DEFAULT NULL, `discDate` varchar(50) DEFAULT NULL, `PkgStatus` varchar(50) DEFAULT NULL, `Type` varchar(50) DEFAULT NULL, KEY `idx_table_two_id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1兩者之間的 EXPLAIN 也是相同的：
explain extended SELECT DISTINCT ... ******************** 1. row ********************* id: 1 select_type: SIMPLE table: d type: ALL possible_keys: PRIMARY key: key_len: ref: rows: 1596593 filtered: 100.00 Extra: Using temporary ******************** 2. row ********************* id: 1 select_type: SIMPLE table: i type: ref possible_keys: idx_table_two_id key: idx_table_two_id key_len: 203 ref: mydb.d.id rows: 1 filtered: 100.00 Extra:更新＃1：
這是更多資訊。我在兩台伺服器上都啟用了性能模式。5.7 伺服器顯示了許多
hash_table_locks：SELECT EVENT_NAME, COUNT_STAR FROM performance_schema.events_waits_summary_global_by_event_name ORDER BY COUNT_STAR DESC LIMIT 10; # EVENT_NAME, COUNT_STAR 'wait/synch/sxlock/innodb/hash_table_locks', '3256808433' 'wait/synch/mutex/innodb/buf_pool_mutex', '19266748' 'wait/synch/mutex/innodb/log_sys_mutex', '14488781' 'wait/io/table/sql/handler', '13676918' 'wait/synch/mutex/innodb/lock_mutex', '11431841' ...此外，我看到很多時間都花在 . 上
wait/io/table/sql/handler，大概是因為hash_table_locks. 它們是時間的前兩個消費者（以皮秒為單位）：SELECT EVENT_NAME, SUM_TIMER_WAIT FROM performance_schema.events_waits_summary_global_by_event_name where event_name != "idle" ORDER BY SUM_TIMER_WAIT DESC LIMIT 10; # EVENT_NAME, SUM_TIMER_WAIT 'wait/io/table/sql/handler', '1300909619487480' # 1300s 'wait/synch/sxlock/innodb/hash_table_locks', '98099101074540' # 98s 'wait/io/file/innodb/innodb_data_file', '5035505718525' 'wait/io/socket/sql/client_connection', '344937541275' 'wait/synch/mutex/innodb/fil_system_mutex', '198749837865' ...當我在 5.6 伺服器上重複此操作時，我看不到這樣的
hash_table_locks消耗時間。更新#2：
要檢查磁碟是否是瓶頸，我執行了以下操作。
我安裝了兩個相同的 EC2 實例。它們都是 i3.large（2 個 vCPU / 15.25 GB RAM）以及 1x425 GB SSD 磁碟。在第一個實例中，我安裝了 MySQL 5.7.25。在第二個實例中，我安裝了 MariaDB 10.2.21。我為兩者保留了開箱即用的配置。在 MySQL 上查詢仍然需要 30 分鐘，但 MariaDB 實例只需要 30 秒！InnoDB 的版本幾乎相同：MySQL 執行 5.7.25，MariaDB 執行 5.7.24。在我看來，這一定是 MySQL 中的配置問題，而不是 Innodb 或磁碟的限制。
另一個更新：在 MariaDB 實例中，EXPLAIN 略有不同。有一個“使用where”子句，並且表是相反的。我嘗試了一個 STRAIGHT_JOIN 來更改表順序，但這並沒有改變任何東西：
# MariaDB EXPLAIN SELECT SQL_NO_CACHE DISTINCT Name,d.id,deviceType,issuedBy, description,avNum,CompanyName, BrandName,dwNumber,quant,discDate,Type FROM table_one d JOIN table_two i ON d.id = i.id; ******************** 1. row ********************* id: 1 select_type: SIMPLE table: i type: ALL possible_keys: idx_table_two_id key: key_len: ref: rows: 2496908 Extra: Using where; Using temporary ******************** 2. row ********************* id: 1 select_type: SIMPLE table: d type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 202 ref: mydb.i.id rows: 1 Extra: 2 rows in set我還在下面應用了威爾遜建議的更改，但沒有解決問題。
更新#3：
我發現了一些看起來很重要的東西。我在 5.6 和 5.7 伺服器上啟用了查詢跟踪器。我在這裡附上了他們的輸出：
5.6: https://pastebin.com/KSTeTDdy
5.7: https://pastebin.com/3SaXNdCU
最後，我注意到臨時表的創建方式不同：
# 5.6 { "converting_tmp_table_to_myisam": { "cause": "memory_table_size_exceeded", "tmp_table_info": { "table": "intermediate_tmp_table", "row_length": 4570, "key_length": 4585, "unique_constraint": true, "location": "disk (MyISAM)", "record_format": "packed" } } }# 5.7 { "converting_tmp_table_to_ondisk": { "cause": "memory_table_size_exceeded", "tmp_table_info": { "table": "intermediate_tmp_table", "row_length": 4575, "key_length": 8, "unique_constraint": true, "location": "disk (InnoDB)", "record_format": "packed" } } }5.6 使用 MyISAM 作為 tmp 表，5.7 使用 InnoDB。
也許這就是為什麼我wait/synch/sxlock/innodb/hash_table_locks在 5.7 中看到如此多的事件？這可能與 InnoDB 行級鎖定有關嗎？問題是，是否有可能恢復舊的行為進行測試？我在 5.7 伺服器上找到
internal_tmp_disk_storage_engine並更改了它。MyISAM這也不能解決問題。我可以看到在 /tmp 下創建的臨時表：# ls -ltrh /tmp -rw-rw---- 1 mysql mysql 56K Apr 26 02:27 #sql_f55_0.MAI -rw-rw---- 1 mysql mysql 368M Apr 26 02:27 #sql_f55_0.MAD現在肯定是在使用MyISAM，但是查詢還是很慢。我確實注意到，在所有伺服器上，5.7、5.6 和 MariaDB，這個文件大小都達到了 368M，然後不再變大。與 5.7 (<1MB/s) 相比，文件在 5.6/MariaDB (~10-15 MB/s) 中的增長速度要快得多。
VARIABLES 和 GLOBAL STATUS 的分析：（
唉，這裡沒有什麼可以作為對手頭問題的解釋）
我不喜歡做
FORCE INDEX，等等，但STRAIGHT_JOIN可能是強制兩個表排序的最簡單和最安全的方法。觀察：
- 版本：5.7.25-日誌
- 15.25 GB 記憶體
- 正常執行時間 = 14:05:48；一些 GLOBAL STATUS 值可能還沒有意義。
- 您沒有在 Windows 上執行。
- 執行 64 位版本
- 您似乎完全（或大部分）執行 InnoDB。
更重要的問題：
使用 SSD，更改這些：
innodb_flush_neighbors = 0 – 從 1 innodb_io_capacity = 800 – 從 200
聽起來您的數據集大小遠小於
innodb_buffer_pool_size. 那麼，也許您支付的 RAM 超出了您的需要？如果您正在為增長做準備，請忽略此評論。除了極少數例外，我認為不
innodb_deadlock_detect應該關閉。很多性能不佳的查詢。縮短 long_query_time 並使用慢日誌：http: //mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog
由於似乎只需要 11 個連接，因此減少
max_connections到 40 個。這將避免對 RAM 的某些壓力。你確定要
READ-UNCOMMITTED嗎？細節和其他觀察：
( Table_open_cache_misses / (Table_open_cache_hits + Table_open_cache_misses) ) = 272 / (6879 + 272) = 3.8%– table_open_cache 的有效性。– 增加table_open_cache 並檢查table_open_cache_instances。
( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,024 * 4 = 4,096– 頁面清理器每秒的工作量。– “InnoDB: page_cleaner: 1000ms 預期循環佔用了 …” 可以通過降低 lru_scan_depth 來修復：考慮 1000 / innodb_page_cleaners
( innodb_page_cleaners / innodb_buffer_pool_instances ) = 4 / 8 = 0.5– innodb_page_cleaners – 建議將 innodb_page_cleaners 設置為 innodb_buffer_pool_instances
( innodb_lru_scan_depth ) = 1,024– “InnoDB: page_cleaner: 1000ms 預期循環佔用了……”可以通過降低 lru_scan_depth 來修復
( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 494,639 / 720896 = 68.6%– buffer_pool 的 pct 目前未使用 – innodb_buffer_pool_size 比需要的大嗎？
( Innodb_buffer_pool_bytes_data / innodb_buffer_pool_size ) = 3,590,832,128 / 11264M = 30.4%– 數據佔用緩衝池的百分比 – 小百分比可能表明 buffer_pool 過大。
( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 50,748 / 60 * 128M / 2513408 = 45,166– InnoDB 日誌輪換之間的分鐘數從 5.6.8 開始，可以動態更改；請務必同時更改 my.cnf。– （輪換間隔 60 分鐘的建議有些隨意。）調整 innodb_log_file_size。（不能在 AWS 中更改。）
( innodb_flush_neighbors ) = 1– 將塊寫入磁碟時的小優化。– 使用 0 表示 SSD 驅動器；1 用於硬碟。
( innodb_io_capacity ) = 200- 磁碟上每秒的 I/O 操作數。100 用於慢速驅動器；200 用於旋轉驅動器；SSD 1000-2000；乘以 RAID 係數。
( innodb_thread_concurrency ) = 0– 0 = 讓 InnoDB 決定 concurrency_tickets 的最佳值。– 設置為 0 或 64。這可能會減少 CPU。
( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF– 是否記錄所有死鎖。– 如果你被死鎖困擾，打開它。注意：如果你有很多死鎖，這可能會寫入很多磁碟。
( innodb_deadlock_detect ) = innodb_deadlock_detect = OFF– 5.7.15 提供了通過關閉死鎖檢測來加速大量插入的方法 – 你真的要關閉它嗎？
( local_infile ) = local_infile = ON– local_infile = ON 是一個潛在的安全問題
( Created_tmp_disk_tables / Questions ) = 37,591 / 73621 = 51.1%– 需要磁碟 tmp 表的查詢的百分比。– 更好的索引/沒有斑點/等等。
( Created_tmp_disk_tables / Created_tmp_tables ) = 37,591 / 46560 = 80.7%– 溢出到磁碟的臨時表的百分比 – 可能增加 tmp_table_size 和 max_heap_table_size；改進指標；避免斑點等
( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (168 + 0 + 2 + 0) / 170 = 1– 每個送出的語句（假設所有 InnoDB） – 低：可能有助於在事務中將查詢分組；高：長期交易使各種事情緊張。
( Select_scan ) = 51,492 / 50748 = 1 /sec– 全表掃描 – 添加索引/優化查詢（除非它們是小表）
( Select_scan / Com_select ) = 51,492 / 71543 = 72.0%– % 的選擇進行全表掃描。（可能被儲存常式愚弄。）——添加索引/優化查詢
( ( Com_stmt_prepare - Com_stmt_close ) / ( Com_stmt_prepare + Com_stmt_close ) ) = ( 2 - 0 ) / ( 2 + 0 ) = 100.0%– 你是( binlog_format ) = binlog_format = MIXED– 聲明/行/混合。ROW 是首選；它可能成為預設值。
( expire_logs_days ) = 0– 多久自動清除 binlog（經過這麼多天） – 太大（或為零）= 消耗磁碟空間；太小 = 需要快速響應網路/機器崩潰。
( slow_query_log ) = slow_query_log = OFF– 是否記錄慢查詢。(5.1.12)
( long_query_time ) = 10– 用於定義“慢”查詢的截止時間（秒）。– 建議 2
( Threads_created / Connections ) = 11 / 197 = 5.6%– 程序創建速度 – 增加thread_cache_size（非Windows）
( thread_cache_size / max_connections ) = 21 / 1320 = 1.6%– (Windows 為 0)
( thread_cache_size / Max_used_connections ) = 21 / 11 = 190.9%
- 讓執行緒記憶體大於您可能的連接數沒有任何優勢。浪費空間是缺點。
你有一半的查詢記憶體。您應該同時設置 query_cache_type = OFF 和 query_cache_size = 0 。（根據傳言）QC 程式碼中有一個“錯誤”，除非您關閉這兩個設置，否則某些程式碼會保持打開狀態。
異常小：
Com_set_option = 6.7 /HR Handler_read_rnd = 0.85 /HR Innodb_dblwr_pages_written / Innodb_dblwr_writes = 1.05 Select_range = 0 Select_range / Com_select = 0 Sort_rows = 0.85 /HR Table_locks_immediate = 0.03 /sec Table_open_cache_hits = 0.14 /sec異常大：
(Com_select + Qcache_hits) / (Com_insert + Com_update + Com_delete + Com_replace) = 420 Com_alter_user = 0.071 /HR Com_flush = 12 /HR Com_purge = 12 /HR Com_release_savepoint = 0.071 /HR Com_savepoint = 0.071 /HR Handler_read_next / Handler_read_key = 145 Handler_read_rnd_next / Handler_read_rnd = 2.43e+6 Handler_savepoint = 0.071 /HR Handler_savepoint_rollback = 0.28 /HR Innodb_buffer_pool_pages_flushed / max(Questions, Queries) = 1.24 Performance_schema_file_instances_lost = 2異常字元串：
ft_boolean_syntax = + -><()~*:& gtid_mode = OFF_PERMISSIVE have_ssl = YES innodb_fast_shutdown = 1 innodb_flush_sync = OFF innodb_log_checksums = OFF log_output = TABLE log_statements_unsafe_for_binlog = OFF optimizer_trace = enabled=off,one_line=off optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on relay_log_recovery = ON session_track_system_variables = time_zone, autocommit, character_set_client, character_set_results, character_set_connection time_zone = UTC transaction_isolation = READ-UNCOMMITTED tx_isolation = READ-UNCOMMITTED
您已經增加了 RAM，將 innodb 緩衝池大小從 ~2G 增加到 ~11G，這對我來說是總 RAM 的 ~70%。
由於執行計劃和數據庫配置看起來相同，您可以查看您的硬體配置。從硬體方面確認兩件事：
- 上一個和目前的 CPU 時鐘速度比較
- 機器的先前和目前磁碟類別（寫入/讀取能力）以及您是否正在使用任何磁碟條帶化。
為了安全起見，你會分析桌子嗎？如果表大小較小，您還可以對其進行優化以消除任何碎片。要獲取碎片報告，您可以在數據庫上執行mysqltuner工具。
希望這可以幫助。
謝謝，桑吉瓦