一、背景
項目組接到兩家客戶回報，部分生産統計報表查詢緩慢，甚至某些報表無法查詢出1個月的統計資料。收到問題回報後，運維同學遠端至客戶服務，驗證生産問題現狀，以PM工時卡片（公司研發内控平台）形式轉至研發生産問題甬道。
傳統站務系統部署于客戶本地機房，使用Mysql-5.7.37資料庫；這兩家客戶均已上線多年，軟體版本較老；以他們訂單表單表為例，均擁有百萬量級資料。單站站務的報表具有兩個特性，一是以業務驅動，二是滿足财務報表要求（傳遞類項目通病）；這就使得報表的級聯關系較多，sql語句比較複雜，以下是針對生産慢查詢的優化思路。
1.1 慢查詢優化思路
- 一、分析sql，優化sql（檢查sql語句本身/聚合/拆分sql）；
- 二、建立合适的索引；
- 三、檢查mysql伺服器配置；
- 四、增加緩存、使用讀庫（讀寫分離）；
- 五、若有代碼，優化代碼邏輯；
- 六、提升伺服器IO性能，如提高硬體配置，機械盤換成SSD固态盤；
- 七、清洗表、中間件（如ES）；
- 八、分庫分表（通常分庫分表是最後的手段）；
以上是站務精英總結的慢查詢優化思路，可能還有其他辦法，順序也不一定正确，需要根據實際的情況靈活處理。比如有錢，更新機器的成本代價可能小于分庫分表帶來的研發成本代價。
二、慢sql舉例
select
s.*
from
tbl_zw_sell_ticket s
left join tbl_sys_user u on u.code = s.ticket_seller_id
left join tbl_sys_role r on u.role_code = r.code
where
s.leave_date between DATE_FORMAT('2022-12-01 00:00:00', '%Y-%m-%d') and DATE_FORMAT('2022-12-30 23:59:59', '%Y-%m-%d')
and s.ticket_state not in ('鎖票', '解鎖')
order by
s.sell_time desc
limit 50
2.1 檢查sql（not in）
觀察sql，最先發現 ‘s.ticket_state not in (‘鎖票’, ‘解鎖’)’。老生常談的問題，not in 到底走不走索引？
MySQL 會在選擇索引的時候進行優化，如果 MySQL 認為全表掃描比走索引+回表效率高， 那麼他會選擇全表掃描。注意，我們的sql裡面是 select s.* ，按理會出現大量的回表; 但是在sql的末尾 limit 50 , 因為 limit 的增加，讓 MySQL 優化的時候發現，索引 + 回表的性能更高一些。
如果不放心的同學，可以把 not in 換成 not exists 提升執行效率（取決于not exists裡面的内容是否多于主表）。
三、Explain
3.1 explain
老辦法，首先想到explain，檢視sql的執行計劃。在使用explain之前，執行上述sql查詢一個月的資料，竟然耗時2分鐘左右，趕緊使用explain檢視執行計劃。
百萬量級的 tbl_zw_sell_ticket 表 s 命中 cr_date 索引（sell_time）字段，執行計劃顯示掃描1167行。
‘order by s.sell_time desc’ 語句進入視線，s 表命中這個排序的索引，并不能減少查詢量級，這段sql中 s.leave_date 對訂單時間過濾是減少查詢量級的關鍵，沒有命中 leave_date 的索引将造成不可避免的慢查詢。
<font color="red"> 對生産執行第一個優化 </font>
++對生産執行第一個優化++：
drop index idx_leave_date on tbl_zw_sell_ticket;
create index idx_leave_date on tbl_zw_sell_ticket (leave_date, sell_time);
force index(idx_leave_date);
更換原來的 leave_date 索引，改為聯合索引 leave_date,sell_time 。（注意：原來生産單獨的 sell_time 索引不要動，涉及到聯合索引命中順序的問題，可能引起其他報表卡頓）
對這個報表單獨使用的sql，增加強制索引語句，force index(idx_leave_date) ，這輪優化做完後，sql查詢一個月資料，已達到秒級/毫秒級的執行速度。
3.2 order by的警惕
- 一、盡量使用index排序，避免filesort排序；
- 二、檢查mysql的配置檔案 sort_buffer_size 大小；
四、mysql服務配置
4.1 my.cnf
join_buffer_size = 2M
sort_buffer_size = 2M
innodb_buffer_pool_size=2G
生産配置，sort_buffer_size 為 2M，join_buffer_size 為 2M，innodb_buffer_pool_size=2G，而整體伺服器的記憶體占用隻達到了50%，如何判斷 innodb_buffer_pool_size=2G 記憶體頁是否合理 ？
4.1.1 記憶體頁是否合理判斷
通過 show status like ‘Innodb_buffer_pool_%’;可以看到跟buffer pool有關的一些資訊。
Innodb_buffer_pool_read_requests表示讀請求的次數。
Innodb_buffer_pool_reads 表示從實體磁盤中讀取資料的請求次數
pool 命中率 = 1 - (Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100%
一般情況下buffer pool命中率都在99%以上，如果低于這個值，才需要考慮加大innodb buffer pool的大小。
4.1.2 max_connections等待sql配置
判斷max_connections 等待sql排隊執行的情況，在生産環境max_connections=5000，不用修改。
<font color="red"> 對生産執行第二個優化 </font>
++對生産執行第二個優化++：
join_buffer_size = 64M
sort_buffer_size = 64M
innodb_buffer_pool_size = 4G
4.2 sql監控-profiling
如果執行計劃是正确的，SQL語句的性能還是很慢，可通過MySQL中的Profiling工具進一步定位問題。 通過開啟 profiling 檢視sql 執行流程，臨時開啟 set profiling=ON; 檢視開啟sql監控的情況show variables like ‘profiling’;
然後正常執行sql語句,如下：
select count(*) from checkticket_record where DEPARTURE_DATE >= ‘2023-02-01 00:00:00’ and DEPARTURE_DATE <= ‘2023-05-04 00:00:00’ and CURRENT_STATE = ‘ALREADY_CHECK’；
執行 show profiles; 檢視mysql最新執行的所有sql語句，找到剛剛執行的sql的id，執行show profile for query id;
+——————————+———-+
| Status | Duration |
+——————————+———-+
| Sending data | 0.000197 |
| Waiting for query cache lock | 0.000017 |
| Sending data | 0.000225 |
| Waiting for query cache lock | 0.000017 |
| Sending data | 0.000203 |
| Waiting for query cache lock | 0.000017 |
| Sending data | 0.000235 |
| Waiting for query cache lock | 0.000017 |
| Sending data | 0.000187 |
| Waiting for query cache lock | 0.000017 |
| Sending data | 0.000208 |
| Waiting for query cache lock | 0.000017 |
| Sending data | 0.000212 |
| Waiting for query cache lock | 0.000017 |
| Sending data | 0.000222 |
| Waiting for query cache lock | 0.000017 |
| Sending data | 0.000203 |
| Waiting for query cache lock | 0.000016 |
| Sending data | 0.000179 |
| Waiting for query cache lock | 0.000016 |
| Sending data | 0.000195 |
| Waiting for query cache lock | 0.000016 |
| Sending data | 0.000202 |
| Waiting for query cache lock | 0.000017 |
| Sending data | 0.000206 |
| Waiting for query cache lock | 0.000017 |
| Sending data | 0.000188 |
| Waiting for query cache lock | 0.000016 |
| Sending data | 0.000196 |
| Waiting for query cache lock | 0.000016 |
| Sending data | 0.000203 |
| Waiting for query cache lock | 0.000016 |
| Sending data | 0.000185 |
| Waiting for query cache lock | 0.000016 |
五、Waiting for query cache lock
使用Profiling工具後，發現一段簡單的sql執行，出現大量的 Waiting for query cache lock ！！！！
在my.cnf 的配置中，我發現有一段 query_cache_type = 1 的配置，該配置存儲SELECT語句及其産生的資料結果，特别适用于表資料變化不是很頻繁的場景。
生産上的兩家客戶，并未啟用讀庫，統計報表依然從寫庫查詢，而在寫庫的mysql配置裡面存在 query_cache_type = 1 的配置。對于更新壓力大的寫庫來說，查詢緩存的命中率也會非常低。
<font color="red"> 對生産執行第三個優化（寫庫不要開啟 query_cache） </font>
++對生産執行第三個優化（寫庫不要開啟 query_cache）++：
query_cache_type = 0
# query_cache_size=6M
我們可以将參數 query_cache_type 設定成 DEMAND(按需即用)方式,這樣對于預設的SQL語句不使用查詢緩存，而對于确定要使用query cache的SQL語句，可以用sql_cache的方法指定，例如：
select sql_cache * from table_name; 或 select sql_cache count(*) from table_name;
以下是query_cache_type三個參數的含義:
query_cache_type=0（OFF）關閉
query_cache_type=1（ON）緩存所有結果，除非select語句使用SQL_NO_CACHE禁用查詢緩存
query_cache_type=2(DEMAND)，隻緩存select語句中通過SQL_CACHE指定需要緩存的查詢
六、總結
性能優化是一個龐大的課題，要善用工具，本文主要是針對sql自身、索引、mysql的配置對生産報表查詢進行了一輪簡單的優化，利用到 sql、索引基礎知識，explain 執行指令、Profiling工具等，問題因環境而不同，不同的問題處理手段也不相同，但大緻的排查問題思路可以保持一緻。
為幫助開發者們提升面試技能、有機會入職BATJ等大廠公司，特别制作了這個專輯——這一次整體放出。
大緻内容包括了： Java 集合、JVM、多線程、并發程式設計、設計模式、Spring全家桶、Java、MyBatis、ZooKeeper、Dubbo、Elasticsearch、Memcached、MongoDB、Redis、MySQL、RabbitMQ、Kafka、Linux、Netty、Tomcat等大廠面試題等、等技術棧！
歡迎大家關注公衆号【Java爛豬皮】，回複【666】，擷取以上最新Java後端架構VIP學習資料以及視訊學習教程，然後一起學習，一文在手，面試我有。
每一個專欄都是大家非常關心，和非常有價值的話題，如果我的文章對你有所幫助，還請幫忙點贊、好評、轉發一下，你的支援會激勵我輸出更高品質的文章，非常感謝！