【作者】
吳宙旭，攜程資料庫專家
【問題描述】
線上我們偶爾會碰到MySQL的狀態是statistics. 但如果出現大量的statistics等待，會引起MySQL效能急劇下降。官方的文件對這個狀態的解釋如下：
The server is calculating statistics to develop a query execution plan. If a thread is in this state for a long time, the server is probably disk-bound performing other work.這段話的意思是說，statistics這個狀態是用於生成執行計劃，如果較長時間出現這個狀態，那麼伺服器的IO就比較重。我們線上上對資料庫伺服器進行壓測，發現出現了大量的statistics的等待並非是IO重引起的，並對這個等待進行了分析。
【問題分析】
測試表的表結構如下：資料庫版本是5.7.37
CREATE TABLE `t` (
Col1 bigint NOT NULL,
Col2 bigint NOT NULL,
Col3 varchar(20) NOT NULL,
Col4 varchar(10) NOT NULL,
Col5 smallint NOT NULL,
Col6 smallint NOT NULL,
Col7 int,
Col8 int,
Col9 smallint NOT NULL,
Col10 smallint NOT NULL,
Col11 smallint NOT NULL,
Col12 varchar(150) ,
Col13 datetime,
Col14 datetime,
Col15 datetime,
Col16 datetime,
Col17 datetime,
Col18 datetime, NOT NULL,
PRIMARY KEY (col1),
KEY ix_1 (col18),
KEY ix_2 (col2),
KEY ix_3 (col3, col13),
KEY ix_4 (col3, col14),
KEY ix_5 (col3, col13, col8),
KEY ix_6 (col3, col13, col8, col11),
KEY ix_7 (col3, col8, col11, col9 ,col10, col5, col7, col13),
KEY ix_8 (col3, col8, col9, col10, col5, col7, col13),
KEY ix_9 (col6, col13),
KEY idx_10 (col12)
) 我們用查詢語句進行壓測，當伺服器QPS壓到1萬左右，資料庫伺服器的CPU使用率在85%左右，並且thread明顯開始堆積。開始出現大量的statistics等待，如下圖所示：
壓測的語句效能沒有問題，就是有比較多的AND和OR，我們把語句單獨執行，都能走到合適的索引，速度都是非常快的。
我們抓取了pstack, 當時的call stack主要有兩種：
下面的call stack應該是在等待creating sort index， 這部分也可以進一步提優，但佔比並非第一，所以不是我們分析的重點：
Thread 1 (process 81043):
#0 mach_read_from_2 at storage/innobase/include/mach0data.ic:96
#1 page_header_get_field at storage/innobase/include/page0page.ic:176
#2 page_dir_get_n_slots at storage/innobase/include/page0page.ic:699
#3 page_cur_search_with_match at storage/innobase/page/page0cur.cc:519
#4 btr_cur_search_to_nth_level at storage/innobase/btr/btr0cur.cc:1446
#5 btr_pcur_open_with_no_init_func at storage/innobase/include/btr0pcur.ic:530
#6 row_search_mvcc at storage/innobase/row/row0sel.cc:5203
#7 ha_innobase::index_read at storage/innobase/handler/ha_innodb.cc:8817
#8 handler::ha_index_read_map at sql/handler.cc:3050
#9 handler::read_range_first at sql/handler.cc:7421
#10 handler::multi_range_read_next at sql/handler.cc:6488
#11 QUICK_RANGE_SELECT::get_next at sql/opt_range.cc:11254
#12 find_all_keys at sql/filesort.cc:977
#13 filesort at sql/filesort.cc:430
#14 create_sort_index at sql/sql_executor.cc:3712
#15 QEP_TAB::sort_table at sql/sql_executor.cc:2625
#16 join_init_read_record at sql/sql_executor.cc:2491
#17 sub_select at sql/sql_executor.cc:1284
#18 do_select at sql/sql_executor.cc:957
#19 JOIN::exec at sql/sql_executor.cc:206
#20 handle_query at sql/sql_select.cc:191
#21 execute_sqlcom_select at sql/sql_parse.cc:5167
#22 mysql_execute_command at sql/sql_parse.cc:2829
#23 mysql_parse atsql/sql_parse.cc:5600
#24 dispatch_command at sql/sql_parse.cc:1493
#25 do_command at sql/sql_parse.cc:1032
#26 handle_connection at sql/conn_handler/connection_handler_per_thread.cc:321
#27 pfs_spawn_thread at storage/perfschema/pfs.cc:2451
#28 start_thread () from /lib64/libpthread.so.0
#29 clone () from /lib64/libc.so.6還有一部分執行緒其call stack如下：
#0 rw_lock_lock_word_incr at storage/innobase/include/sync0rw.ic:317
#1 rw_lock_s_unlock_func at storage/innobase/include/sync0rw.ic:531
#2 pfs_rw_lock_s_unlock_func at storage/innobase/include/sync0rw.ic:1006
#3 buf_page_get_gen at storage/innobase/buf/buf0buf.cc:4228
#4 btr_cur_search_to_nth_level at storage/innobase/btr/btr0cur.cc:1113
#5 btr_estimate_n_rows_in_range_low at storage/innobase/btr/btr0cur.cc:5733
#6 btr_estimate_n_rows_in_range at storage/innobase/btr/btr0cur.cc:5994
#7 ha_innobase::records_in_range at storage/innobase/handler/ha_innodb.cc
#8 handler::multi_range_read_info_const at sql/handler.cc:6293
#9 DsMrr_impl::dsmrr_info_const at sql/handler.cc:6968
#10 check_quick_select at sql/opt_range.cc:10091
#11 get_key_scans_params at sql/opt_range.cc:5859
#12 test_quick_select at sql/opt_range.cc:3113
#13 get_quick_record_count at sql/sql_optimizer.cc:6027
#14 JOIN::estimate_rowcount at sql/sql_optimizer.cc:5774
#15 JOIN::make_join_plan at sql/sql_optimizer.cc:5131
#16 JOIN::optimize at sql/sql_optimizer.cc:375
#17 st_select_lex::optimize at sql/sql_select.cc:1016
#18 handle_query at sql/sql_select.cc:171
#19 execute_sqlcom_select at sql/sql_parse.cc:5167
#20 mysql_execute_command at sql/sql_parse.cc:2829
#21 mysql_parse at sql/sql_parse.cc:5600
#22 dispatch_command at sql/sql_parse.cc:1493
#23 do_command at sql/sql_parse.cc:1032
#24 handle_connection at sql/conn_handler/connection_handler_per_thread.cc:321
#25 pfs_spawn_thread at storage/perfschema/pfs.cc:2451
#26 start_thread () from /lib64/libpthread.so.0
#27 clone () from /lib64/libc.so.6這個執行緒在生成執行計劃(make_join_plan), 所以也就是我們對應的statistics等待. 對於函式check_quick_select, 在相應的程式碼sql/opt_range.cc檔案中，有如下註解：
3. SEL_ARG GRAPH USE
Use get_mm_tree() to construct SEL_ARG graph from WHERE condition.
Then walk the SEL_ARG graph and get a list of dijsoint ordered key
intervals (i.e. intervals in form
(constA1, .., const1_K) < (keypart1,.., keypartK) < (constB1, .., constB_K)
Those intervals can be used to access the index. The uses are in:
- check_quick_select() - Walk the SEL_ARG graph and find an estimate of
how many table records are contained within all
intervals.
- get_quick_select() - Walk the SEL_ARG, materialize the key intervals,
and create QUICK_RANGE_SELECT object that will
read records within these intervals.從描述中，我們可以看到，test_quick_select應該是在評估行數，SEL_ARG圖在參考資料中有介紹，MySQL用於組織複雜條件並計算各個Ranges所影響到的對應可以使用的索引的代價和使用索引的不同快速方式，從而選出最優的計劃。
理解這些概念可能過於複雜，但是從描述中，我們找到了問題的突破點，就是索引評估。我們看到這張表建有10個索引，而且明顯的有些索引是被覆蓋，可以直接刪除。所以根據索引的使用情況，我們直接刪除如下三個索引：
KEY ix_3 (col3, col13), # 被ix_6覆蓋
KEY ix_5 (col3, col13, col8), # 被ix_6覆蓋
KEY ix_8 (col3, col8, col9, col10, col5, col7, col13), #基本上被ix_7覆蓋，且無使用記錄刪除如上三個索引後，我們進一步壓測，發現QPS可以打到2萬, CPU消耗在75%左右。 所以僅僅刪除三個索引，就能提升效能將近一倍左右。
我們用火焰圖做驗證：下圖是刪索引前和刪除索引後的MySQL消耗火焰圖，可以很直觀的發現optimize的消耗從81%減少到了65%，因為SQL實際執行的邏輯及使用的索引並沒有產生變化，消耗與刪除索引前後一致，所以可以得出對於生成查詢計劃的本身的效能可以提升2倍多。
我們也可以用show profile檢視不同情況下的佔比：
>show profile for query 1;
# 刪除索引前 生成執行計劃佔比75%
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000121 |
| checking permissions | 0.000005 |
| Opening tables | 0.000013 |
| init | 0.000045 |
| System lock | 0.000007 |
| optimizing | 0.000021 |
| statistics | 0.003136 |
| preparing | 0.000029 |
| Sorting result | 0.000006 |
| executing | 0.000004 |
| Sending data | 0.000008 |
| Creating sort index | 0.000746 |
| end | 0.000004 |
| query end | 0.000006 |
| closing tables | 0.000005 |
| freeing items | 0.000021 |
| cleaning up | 0.000012 |
+----------------------+----------+
# 索引刪除後的 生成執行計劃佔比28.4%
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000457 |
| checking permissions | 0.000237 |
| Opening tables | 0.000133 |
| init | 0.000145 |
| System lock | 0.000121 |
| optimizing | 0.000126 |
| statistics | 0.001163 |
| preparing | 0.000133 |
| Sorting result | 0.000124 |
| executing | 0.000117 |
| Sending data | 0.000124 |
| Creating sort index | 0.000590 |
| end | 0.000121 |
| query end | 0.000125 |
| closing tables | 0.000118 |
| freeing items | 0.000134 |
| cleaning up | 0.000127 |
+----------------------+----------+
# 我們用Force Index指定索引 生成執行計劃佔比 59%
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000140 |
| checking permissions | 0.000008 |
| Opening tables | 0.000016 |
| init | 0.000046 |
| System lock | 0.000008 |
| optimizing | 0.000021 |
| statistics | 0.001606 |
| preparing | 0.000023 |
| Sorting result | 0.000005 |
| executing | 0.000004 |
| Sending data | 0.000008 |
| Creating sort index | 0.000755 |
| end | 0.000005 |
| query end | 0.000013 |
| closing tables | 0.000006 |
| freeing items | 0.000019 |
| cleaning up | 0.000018 |
+----------------------+----------+
【結論】
- MySQL最佳化器沒有執行計劃快取功能，因此會有部分的計算資源消耗在頻繁的生成查詢計劃上。
- 當我們看到在等待statistics這個狀態，確實是在等待執行計劃的生成，但並不一定是IO出現了瓶頸。有時候刪除重複索引，可以極大的減少執行計劃生成開銷，效能甚至可以達到1倍以上，（具體提升幅度根據查詢語句的複雜度不同而不同）
- 每張表不建議建立過多的索引。這個例子主要是因為開發透過ChatGPT提供的索引建議而新增，最佳化了區域性的個別語句效能，但總體吞吐反而下降。
參考資料：MySQL原始碼分析 Range(Min-Max Tree)結構分析：
https://www.bookstack.cn/read/aliyun-rds-core/1324b904437addf8.md