貼合生產的MySql優化思路

前言

寫這篇文章的出發點是記錄我在工作中處理資料的問題中累計的經驗，寫著寫著發現每一個點都會衍生出其它的背景知識，如優化索引時需要對慢查詢、Explain等相關功能有一定的瞭解，如引入Elasticsearch需要解決資料的同步，學習Elasticsearch的知識等等，由於文章的篇幅不可能把每一個點都像視訊教程一樣細細道來，只能以我有限的認知和對一些通用性的點進行歸納總結。即便是這樣，文章的篇幅也已經很長了，大家如果對某一點有興趣的話還請自行baidu/google單個細節的深入知識。

文章的篇幅較長，如有興趣不妨品味一遍，希望沒有浪費您的數十分鐘。

思考角度

資料庫技術到目前共經歷了人工管理階段、檔案系統階段和資料庫系統階段。

在早期沒有軟體系統的時候，通過手工計帳和口頭協議的人工管理階段也能實現現實世界對某種業務執行，這種形式存在了相當長的時間，是率效相對低下的一種方案。往後的一個階段，隨著計算機技術的發展，出現了以excel表格代替手工計帳的檔案系統階段，一定程度的提高了生產力。再到軟體系統以操作簡單、效率高效的資料庫系統階段，實現了生產力的再次提升，把現實世界的具體問題抽象成了資料，通過資料的流轉與變動來表代現實世界的業務。而在軟體系統中，資料的儲存一般由一個關係型資料庫搭配多個非關係型資料庫組建而成。

資料庫跟系統業務是強關聯的，這就要求產品經理的設計業務的時候要了解資料儲存跟查詢的流程，在設計之初就明確改業務對資料庫會有什麼影響跟是否需要引用新的技術棧。如產品經理設計的一個業務是對多張單表體積百萬級的mysql表進行資料統計分析彙總，如果直接用mysql多表查詢的話一定會產生慢查詢從而導致msyql服務的當機，這時解決方案便是要不產品端妥協，要不改變技術棧。

系統架構與資料庫方案中要選擇更合適公司團隊能力的，在系統前期，簡單的資料庫優化配合鈔能力會是最有價效比方案，但遇到mysql資料庫鈔能力也無能為力的時候，引入對關鍵功能為核心的軟體服務就會成為最有價效比方案，如何在遇到問題時選擇合適的方案，就是體現你價值的時候了。

一個窮小夥攀上一個富家女，短暫的甜蜜終敵不過現實階級的不對等，美好的結局只存在於窮小夥的幻想與瓊瑤老師的電視劇中。

如何在有限的成本中提升資料儲存的效能，便是本文章於大家論討的中心思想。

背景知識

相信大家的日常工作中會經常接觸到以下內容，小弟就簡單地總結一下吧。

關係型資料庫

關係型資料庫就是由二維表及其之間的聯絡所組成的一個資料組織,為軟體提供事務資料一致性、資料持久化等功能，是軟體系統的核心儲存服務，是我們開發跟面試都是最常接觸到的資料庫，對於一些小型外包專案，一個mysql足以滿足全部的業務需求了。就是一個我們經常接觸到的東西，內裡其實是充滿了門道的，往後章節再細聊其中門道。
優點：

1. 事務
2. 持久化
3. 相對通用的SQL語言

問題

1. 對硬碟I/O要求非常高
2. 大資料量的聚合查詢效率低
3. 索引不命中
4. 索引最左匹配原則導致不合適做全文檢索
5. 事務使用不當會引起鎖堵塞
6. 水平擴充套件後帶來的種種問題難處理

非關係型資料庫 - NoSql

MySQL資料庫作為一種關係型資料的儲存軟體，有優點同時也有明顯的缺點，因此通常在軟體系統資料量不斷擴大與業務複雜度不段提升的情況下，不能指望通過增強MySQL資料庫的能力來解決全部的問題，用是引入其他儲存軟體，利用各型別的NoSql來解決軟體系統資料量不斷擴大與業務複雜度不段提升的問題。
關係型資料庫是對關係型資料庫的在不同場景的優化，不是意味著引入某種NoSql就萬事大吉，而是充分了解市面上NoSQL的型別與應用難度，在合適的場景下選擇合適的儲存軟體才是正確的做法。

Key-Value型

在業務中會存在經常對某些表的內容進行查詢，但查詢的結果絕大數是不變的，所以出現了以Memcached、Redis為主的Key-value儲存軟體，廣泛應用在系統中的快取模組。Redis比Memcached多多的資料結構與持久化讓其成為KV型NoSql中應用最廣的。

搜尋型

全文搜尋的場景下，MySQLB+樹索引的查詢優化，like查詢是無法命中索引的，每一次like關鍵字查詢都是一次全表掃描，在幾萬條資料量的表還算可以支撐，但資料最一在就會產生慢查詢，要是業務程式碼寫得不好在事務中呼叫了Like查詢就會產生讀鎖。以倒排索引為核心的ElasticSearch為能完美地滿足全文搜尋的場景，同時ElasticSearch對海量資料支援也十分好，文件與生態也很好，ElasticSearch是搜尋型的代表產品。

文件型

文件型NoSql指的是將半結構化資料儲存為文件的一種NoSql，文件型NoSql通常以JSON或者XML格式儲存資料，因此文件型NoSql是沒有Schema的，由於沒有Schema的特性，我們可以隨意地儲存與讀取資料，因此文件型NoSql的出現是解決關係型資料庫表結構擴充套件不方便的問題的。筆者沒有使用過

列式

對於一定規模的企業，業務上會經常涉及到一些實時且靈活的資料彙總，這種業務不太合適用提前計算的方案來解決，那怕是能用提前計算彙總的方案寫出了業務，但隨著彙總的數量據增加的時候，對彙總資料做最後一步累加也會慢慢變得很慢，那列式NoSql就是這種場景下的產物，大資料時代最具代表性的技術之一了，常見的有HBase，但HBase的應用是十分重的，往往需要一整套Hadoop生態來執行，筆者公司用的是阿里雲的AnalyticDB，一個相容MySql查詢語句的列式儲存軟體。利用匯總+列式儲存軟體的強大查詢能力，足以支援各種實時且靈活的資料彙總務業。

案例

以2021年為時間節點來看，大多數的系統的初期都是以以下方案為起點的，接下來我會在這個案例中慢慢做一些調整。

硬體升級所帶來的收益是越往後越收益越低，在時間、人員緊張的時候這是最快的優化方案。軟體優化所帶來的收益是越往後越收益越高，但越往後所要求技術人員的水平也越高，在時間、人員允許的情況下是最有價效比的優化方案。硬體與軟體的優化不是互斥的，在需要的時候兩者同時可接近MYSQL效能的上限。

硬優化-鈔能力

• 階段一

  • 提高磁碟I/O,儘量拿用SSD磁碟 （質的提升）
  • 提高記憶體 ,增加查詢快取空間
  • 增加CPU核心數，增加執行執行緒

• 階段二

  • 自建mysql更換為服務商mysql服務
  • 開啟自帶讀寫分離功能

• 階段三

  • 服務商mysql服務更換為雲原生分散式資料庫
  • 開啟自帶讀寫分離功能
  • 開啟自帶分表功能

軟優化 - 查詢 - OLTP

OLTP主要用來記錄某類業務事件的發生，如使用者行為，當行為產生後，系統會記錄是使用者在何時何地做了何事，這樣的一行（或多行）資料會以增刪改的方式在資料庫中進行資料的更新處理操作，要求實時性高、穩定性強、確保資料及時更新成功，像常見的業務系統系統都屬於OLTP，而使用的資料庫都為帶事務的資料庫，如MySlq、Oracle等。對OLTP來說，提升查詢的速度、服務穩定就是優化的核心

• 慢查詢
  • 通過慢查詢日誌發現有效率問題的SQL
• 問題sql排查方向
  • 索引設計有問題
  • SQL語句有問題
  • 資料庫選錯索引
  • 單表體積大
• Explain具體分析
  • 檢視sql執行較率
  • 檢視索引命中情況 （重點）
• mysql優化器
  • 優化器選取索引時，會參考索引的基數(Cardinality)
  • 基數是MySQL自動維護且估算出來的，不一定完成準確
  • 索引不命中或用錯索引就是優化器這一步出了問題
  • analyze 可以重新統計索引資訊並重算基數
• 強制索引
  • force 關鍵字可以強制使用索引，在業務程式碼上強制指定index
• 覆蓋索引 - 最理想的命中索引
  • 覆蓋索引指的是，查詢語句從執行到返回結果均使用同一個索引(唯一、普通、聯合索引等)
  • 覆蓋索引可以有交減少回表查詢
  • 若資料的查詢不只使用了一個索引，則不是覆蓋索引
  • 可以通過優化SQL語句或優化聯合索引，來使用覆蓋索引
• count() 函式
  • count(非索引欄位) - 無法使用覆蓋索引，理論上最慢
  • count(索引欄位) - 可以覆蓋索引，依然需要每次判斷欄位是否為null
  • count(主鍵) - 同上
  • count(1) - 只有掃描索引樹，沒有解析資料行的過程，理論更快,但還是會判讀1是否為null
  • count(* ) - MySQL專門優化了count(*)函式直接返回索引樹中資料的個數，最優
• ORDER BY
  • 儘量減少額外的排序，指定where條件
  • where 語句與ORDER BY語句組合滿足最左字首
  • 最高效-索引覆蓋(場景少，遇見機率不大)
    • 索引覆蓋可以跳過生成中間結果集，直接輸出查詢結果
    • ORDER欄位需要有索引且與WHERE的條件且與輸出內容均在同一個索引中
• 分頁查詢
  • 先想辦法走索引覆蓋
  • 先查出所需要資料的id,回表得到最終結果集
• 索引下推
  • KEY store_id_guide_id (store_id,guide_id) USING BTREE
  • select * from table where store_id in (1,2) and guide_id = 3;
  • MySQL5.6之前，需要先拿用索引查詢store_id in (1,2),再全部加表驗證film_id = 3
  • MySQL5.6之後，如果索引中可以判讀，直接使用索引過濾
• 鬆散索引掃描
  • KEY store_id_guide_id (store_id,guide_id) USING BTREE
  • select film_id from table where guide_id = 3
  • MySQL8.0新特性
  • 鬆散索引掃描可以打破”左側原則”，解決帶頭大哥丟失的問題
  • 效率低於聯合索引
• 函式操作
  • 對索引欄位進行函式操作，優化器會放棄索引
  • 這種情況可能包函：時間函式，字串轉為數字，字元編碼轉換
  • 優化使用服務端邏輯來代替mysql函式
• 單表體積過大
  • 升級mysql，不同的mysql軟體能承載的單表體積是不同的，我以目前的經驗看，阿里雲polardb叢集版單表2億的情況下查詢命中索引是沒有問題的（優先順序高）
  • 資料結算 - 如流水類的資料可以按某個時間點來結算得到一個最新值，已結算流水轉到備份表 （優先順序中）
  • 資料冷熱分離 - 不能做結算的資料跟據查詢的頻次做區分，頻次低的轉移到另外的表中查詢，業務上區分好查詢的入口 （優先順序中）
  • 分散式資料庫分表 - 開啟分散式資料庫帶單的分表功能，分散式資料庫元件管理對分表後的插入、查詢（優先順序中）
  • 程式碼實現分表 - 按一定的規則把單表拆分到多張表，在PHP、GO的大多數框架ORM中分拆後需要對框架ORM做一定的修改，JAVA中的ORM有原生的支援，建議在專案初期就考慮，越往後難度越大（優先順序低）

軟優化 - 寫入更新刪除

• 鎖

  • 按照粒度分，MySQL鎖可以分為全域性鎖、表級鎖、行鎖

  • 全域性鎖

    • 自行google/baidu

  • 表級鎖分為表鎖（資料鎖）和後設資料鎖

    • 表鎖
      • 自行google/baidu
    • 後設資料鎖
      • 自行google/baidu

  • 行鎖會鎖住資料行，分為共享鎖和獨佔鎖

    • 自行google/baidu

• 解決死鎖

  • 引數配置
    • 調整innodb_lock_wait_timeout引數
      • 預設為50秒，即等待50秒還未獲取鎖，當前語句報錯
      • 如果等待時間過長，可以適當縮短此引數
    • 主動死鎖檢測：innodb_deadlock_detect
      • 發現死鎖時回滾代價較小的事務
      • 預設開啟
  • 沒必要情況下不開啟事務
  • 查詢儘量放在事務外，減少鎖的行數
  • 避免事務時間過長，不要在事務中觸發http請求
  • 主動檢視事務狀態

    show  processlist;
    SELECT * FROM information_schema.INNODB_TRX; //長事務
    SELECT * FROM information_schema.INNODB_LOCKs; //檢視鎖
    SELECT * FROM information_schema.INNODB_LOCK_waits; //檢視阻塞事務

搜尋業務

• 搜尋行數10萬以下 - mysql硬扛
  • 提升mysql的cpu、io、記憶體硬體
• 搜尋行數10萬以上 - 引入Elasticsearch

Elasticsearch的倒排索引，適合做全文搜尋，但資料構結的靈活性差。

• 資料同步
  • 業務程式碼變動資料時同時同步到Elasticsearch
  • Canel訂閱mysql日誌觸發同步
• Elasticsearch-index
  • 由具有相同欄位的文件列表組成 - 類比為mysql的table
  • 欄位型別一旦設定後，禁止修改，允許新增欄位
  • 具體方法自行google/baidu
• Elasticsearch-Document
  • 使用者儲存在es中的資料文件 - 類比為mysql的行
  • 由 後設資料 與 Json Object 組成
  • 後設資料 與 Json Object詳情自行google/baidu
• Elasticsearch-分詞器
  • 自行google/baidu
• Elasticsearch-倒排索引 （重點）
  • 自行google/baidu
• Elasticsearch-聚合分析
  • 自行google/baidu

統計業務 -OLAP

OLAP是相對於OLTP事務處理場景而然用來對資料的決策分析，是一種運用在大資料分析上的離線數倉思路，不是具體的技術棧,當你的方案能體現OLAP分析處理的思路的話，那該方案就是OLAP了。

早期資料倉儲構建主要指的是把企業的業務資料庫如ERP、CRM、SCM等資料按照決策分析的要求建模並彙總到資料倉儲引擎中，其應用以報表為主，目的是支援管理層和業務人員決策（中長期策略型決策）。隨著IT技術走向網際網路、移動化，資料來源變得越來越豐富，在原來業務資料庫的基礎上出現了非結構化資料，比如網站log，IoT裝置資料，APP埋點資料等，這些資料量比以往結構化的資料大了幾個量級。

無論OLAP面對的業務如何變化，都離不開以下的步驟：確定分析領域->同步業務資料到運算庫->資料清洗建模->同步到資料倉儲->對外暴露

其中計算源資料庫是為專門給資料清洗用的，目的是避免資料清洗時影響業務資料庫的效能。通過將計算源資料庫的資料按業務、維度清洗，增加資料易用性和複用性，得到最終的實時明細資料，落盤到資料倉儲，再由資料倉儲提供最後的決策分析資料。

DEMO方案

生產方案

每個環節的軟體都是可用相同功能的軟體替換的，用團隊最有把握的軟體實現方案，那該方案就是OLAP了。

總結

優化要遵循腳踏實地，一步步地做能力沉澱，多輪迭代，不可一蹴而就。基於自己的基礎、業務場景和未來的發展預期來多輪迭代。

迭代的原則是先把單個軟體服務通過軟優化與硬優化提升軟體的效率，當優化成本低於收益時，站在未來的發展預期參考市面上成熟的方案，跟據方案按需地引入新的軟體進行組合式創新，切忌盲目照搬，有機地融合才能達到1+1>2、2+1>3的效果，當引用的軟體遇到瓶頸時再反覆這個過程。

謝謝您看到這裡，以上便是文章的所有內容，內容中所提出的優化點與方案不一定是最優解，是個人工作中的最佳實踐，有不同見解歡迎談論交流。

本作品採用《CC 協議》，轉載必須註明作者和本文連結