點擊藍字

關注我們

在正文開始前，我們先補充一輪知識點。

DING！

什么叫統計信息？

統計信息是數據庫對所有表信息進行數據抽樣后得出的數據統計，它是一個數據庫優化器選擇最佳執行計劃的核心依據。

什么是SQL呢？

SQL就是一種在數據庫中的結構化查詢語言，就像英語在世界上的地位一樣，在不同的數據庫都可以用SQL這個語言。

它主要是對數據進行定義、操縱和管理，就是可以對數據進行整理，產生約束的條件，還有查詢數據、修改數據和進行用戶權限的管理。

簡單的信息說好了，那我們開始吧。(別怕看不懂，小編陪著你一起看！)

在一個風和日麗的下午，奮哥哥突然接到業務方線上業務數據庫CPU資源告警信息(數據庫出現了問題)，奮哥哥立馬放下手里的枸杞杯登錄業務方阿里云控制臺查看具體問題。

對于數據庫當前正在發生中的問題，我們首先從數據庫實時會話信息中嘗試抓取有效信息，可以看到該告警實例的會話已經出現堆積狀態，大量會話處于"Sending data"狀態(正在向客戶發送數據)且從TIME字段可以看到這些會話長時間執行未結束。

會話長時間執行表示當前會話一直占用的數據庫資源未釋放，且堆積會話基本為同一類型的業務SQL，這也就是導致我們數據庫CPU資源占用過高的問題SQL。

我們拎出這個問題SQL(問題代碼)登錄數據庫查看SQL的執行計劃，對問題SQL進行分析，從SQL執行計劃中我們很明顯發現一個資源消耗比較大的操作"ALL"全表掃描操作，而且比較詭異的一點是，a表進行表關聯possible_keys(可能使用到的索引)明明是primary(主鍵索引)但是卻沒有使用，所以我們下一步的方向就是排查為什么表關聯沒有有效利用索引。

導致索引失效的問題的原因最常見的就是隱式轉換(系統自動識別轉換)，關于隱式轉換我們之前的文章也做過比較詳細的講解，總體概括主要是以下幾個場景：

1.傳遞數據類型和字段類型不一致

2.關聯字段類型不一致

3.關聯字段字符集不一致

4.校驗規則不一致

在表關聯字段索引失效的情況下，可能導致索引失效的場景主要是2～4，于是我們馬上查看表關聯字段相關信息進行一一驗證。emmmm，查詢到的結果卻似乎有些不盡人意，表關聯字段均是bigint類型(一種數據類型)，完美的規避掉了以上所有可能。

再次陷入沉思，在沒有發生隱式轉換的情況下索引一般都是會有效利用的，除非MySQL優化器認為ALL全表掃描的效率并不差。

我們知道，MySQL優化器會通過具體表的統計信息基于CBO(基于成本的優化)進行代價計算，幫我們選擇最佳執行計劃。

但是統計信息并不是完全精確的，某些時候可能會出現一定的誤差，也正是因為統計信息的誤差，就可能導致MySQL優化器錯誤的選擇一個并不是很好的"最佳執行計劃"。

接下來我們就可以進一步查看表的統計信息以及hint(強制SQL走指定索引)進行驗證。

表關聯對應的統計信息

通過hint強制走primary索引

觀察執行計劃并測試執行效率

問題排查到這里，導致該SQL大量消耗CPU資源的原因也就水落石出了。

對于業務方目前的CPU資源占用過高的情況，我們可以建議業務方先將目前堆積的會話進行Kill(將會話刪除)，避免影響其他正常的業務查詢，等數據庫CPU資源有所回落后，在數據庫執行"analyze table"對問題表的統計信息重新采集，統計信息更新后MySQL優化器就可以正確的選擇最佳執行計劃。

統計信息更新

執行計劃更新

雖然客戶的問題已經處理，對于本案例還是有一些點值得我們思考：

索引失效的場景都有哪些？

隱式轉換

統計信息不準確

MySQL統計信息是如何更新采集？

在MySQL中有一些參數設置決定了統計信息采集的行為方式，一般情況下不會做特別設置，我們需要正確的理解這些參數，明白統計信息只是一個統計估計值，并不是絕對精準。

統計信息相關參數

innodb_stats_method?

默認nulls_equal，表示統計信息時把所有的null當作等值對待

innodb_stats_auto_recalc?

是否打開自動化采集統計數據 ，默認打開，當表數據量更新10%觸發重新采集統計信息

innodb_stats_on_metadata?

默認關閉，若該參數開啟時表示數據庫執行"show table status",

訪問"INFORMATION_SCHEMA.TABLES or INFORMATION_SCHEMA.STATISTICS"時，都會觸發重新采集統計信息的操作

innodb_stats_persistent?

統計信息是否持久化到磁盤，默認打開。持久化磁盤當數據庫重新啟動后可從磁盤讀取。

innodb_stats_persistent_sample_pages?

默認20，對于持久化存儲統計信息的表，每次重新采集信息需要采集20個索引頁進行分析

innodb_stats_transient_sample_pages?

默認8，對于非持久化的表，其統計信息重新采集需要掃描8個索引頁進行分析

MySQL幾種重新采集統計信息的時機

1.新打開一張表時

表數據變更超過10%觸發該表的統計信息重新采集當innodb_stats_on_metadata參數打開，數據庫執行"show table status",訪問"INFORMATION_SCHEMA.TABLES or INFORMATION_SCHEMA.STATISTICS"時

2.手動執行analyze tables時

關于analyze table操作：執行該操作需要具有該表的select/insert權限；支持Innodb、Myisam、NDB存儲引擎下的表，不支持視圖；支持對分區表中某個分區單獨執行統計分析；alter table ... analyze partition在執行analyze期間，會對該表加一個。

在探索完技術的真理后，奮哥哥默默的拿起了之前放下的枸杞杯又悠哉了起來。

小編在這里做一下總結哦，來幫助大家理解。

簡單來講，這是一個由于索引失效而導致的數據庫CPU資源占用過高的問題，在解決這個問題的過程中探尋出索引失效的原因：MySQL優化器根據錯誤的統計信息選擇一個并不是很好的"最佳執行計劃"。

通常發生這種情況，我們建議先將目前堆積的會話進行刪除，避免影響其他正常的業務查詢，等數據庫CPU資源有所回落后，在數據庫執行"analyze table"(統計索引分布信息)對問題表的統計信息重新采集，統計信息更新后MySQL優化器就可以正確的選擇最佳執行計劃。

如果還有不明白的地方歡迎大家點擊“在看”進行留言，和小編進行討論哦！

就?

我知道你在看喲

總結

以上是生活随笔為你收集整理的mysql 非自然月统计_技本功|统计信息对SQL执行效率的影响的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。