分析MySQL優化思路

這篇文章主要講解了“分析MySQL優化思路”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“分析MySQL優化思路”吧！      

思考角度

數據庫技術到目前共經歷了人工管理階段、文件系統階段和數據庫系統階段。

在早期沒有軟件系統的時候，通過手工計帳和口頭協議的人工管理階段也能實現現實世界對某種業務運行，這種形式存在了相當長的時間，是效率相對低下的一種方案。往后的一個階段，隨著計算機技術的發展，出現了以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

• 索引覆蓋可以跳過生成中間結果集，直接輸出查詢結果

• ORDER字段需要有索引且與WHERE的條件且與輸出內容均在同一個索引中

• 盡量減少額外的排序，指定where條件

• where 語句與ORDER BY語句組合滿足最左前綴

• 最高效-索引覆蓋(場景少，遇見機率不大)

• 分頁查詢

• 先想辦法走索引覆蓋

• 先查出所需要數據的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有原生的支持，建議在項目初期就考慮，越往后難度越大（優先級低）

軟優化 - 寫入更新刪除

• 鎖

• 自行google/baidu

• 表鎖

• 元數據鎖

• 自行google/baidu

• 自行google/baidu

• 自行google/baidu

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

• 全局鎖

• 表級鎖分為表鎖（數據鎖）和元數據鎖

• 行鎖會鎖住數據行，分為共享鎖和獨占鎖

• 解決死鎖

• 調整innodb_lock_wait_timeout參數

• 主動死鎖檢測：innodb_deadlock_detect

• 默認為50秒，即等待50秒還未獲取鎖，當前語句報錯

• 如果等待時間過長，可以適當縮短此參數

• 發現死鎖時回滾代價較小的事務

• 默認開啟

• 參數配置

• 沒必要情況下不開啟事務

• 查詢盡量放在事務外，減少鎖的行數

• 避免事務時間過長，不要在事務中觸發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了。

感謝各位的閱讀，以上就是“分析MySQL優化思路”的內容了，經過本文的學習后，相信大家對分析MySQL優化思路這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！