高贊分享：符合生產(chǎn)的MySQL優(yōu)化思路

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?

前言

寫這篇文章的出發(fā)點(diǎn)是記錄我在工作中處理數(shù)據(jù)的問題中累計的經(jīng)驗，寫著寫著發(fā)現(xiàn)每一個點(diǎn)都會衍生出其它的背景知識，如優(yōu)化索引時需要對慢查詢、explain等相關(guān)功能有一定的了解，如引入elasticsearch需要解決數(shù)據(jù)的同步，學(xué)習(xí)elasticsearch的知識等等，由于文章的篇幅不可能把每一個點(diǎn)都像視頻教程一樣細(xì)細(xì)道來，只能以我有限的認(rèn)知和對一些通用性的點(diǎn)進(jìn)行歸納總結(jié)。即便是這樣，文章的篇幅也已經(jīng)很長了，大家如果對某一點(diǎn)有興趣的話還請自行baidu/google單個細(xì)節(jié)的深入知識。

文章的篇幅較長，如有興趣不妨品味一遍，希望沒有浪費(fèi)您的數(shù)十分鐘。【推薦學(xué)習(xí)：《mysql視頻教程》】

思考角度

數(shù)據(jù)庫技術(shù)到目前共經(jīng)歷了人工管理階段、文件系統(tǒng)階段和數(shù)據(jù)庫系統(tǒng)階段。

在早期沒有軟件系統(tǒng)的時候，通過手工計帳和口頭協(xié)議的人工管理階段也能實(shí)現(xiàn)現(xiàn)實(shí)世界對某種業(yè)務(wù)運(yùn)行，這種形式存在了相當(dāng)長的時間，是效率相對低下的一種方案。往后的一個階段，隨著計算機(jī)技術(shù)的發(fā)展，出現(xiàn)了以excel表格代替手工計帳的文件系統(tǒng)階段，一定程度的提高了生產(chǎn)力。再到軟件系統(tǒng)以操作簡單、效率高效的數(shù)據(jù)庫系統(tǒng)階段，實(shí)現(xiàn)了生產(chǎn)力的再次提升，把現(xiàn)實(shí)世界的具體問題抽象成了數(shù)據(jù)，通過數(shù)據(jù)的流轉(zhuǎn)與變動來表代現(xiàn)實(shí)世界的業(yè)務(wù)。而在軟件系統(tǒng)中，數(shù)據(jù)的存儲一般由一個關(guān)系型數(shù)據(jù)庫搭配多個非關(guān)系型數(shù)據(jù)庫組建而成。

數(shù)據(jù)庫跟系統(tǒng)業(yè)務(wù)是強(qiáng)關(guān)聯(lián)的，這就要求產(chǎn)品經(jīng)理的設(shè)計業(yè)務(wù)的時候要了解數(shù)據(jù)存儲跟查詢的流程，在設(shè)計之初就明確改業(yè)務(wù)對數(shù)據(jù)庫會有什么影響跟是否需要引用新的技術(shù)棧。如產(chǎn)品經(jīng)理設(shè)計的一個業(yè)務(wù)是對多張單表體積百萬級的mysql表進(jìn)行數(shù)據(jù)統(tǒng)計分析匯總，如果直接用mysql多表查詢的話一定會產(chǎn)生慢查詢從而導(dǎo)致msyql服務(wù)的宕機(jī)，這時解決方案便是要不產(chǎn)品端妥協(xié)，要不改變技術(shù)棧。

系統(tǒng)架構(gòu)與數(shù)據(jù)庫方案中要選擇更合適公司團(tuán)隊能力的，在系統(tǒng)前期，簡單的數(shù)據(jù)庫優(yōu)化配合鈔能力會是最有性價比方案，但遇到mysql數(shù)據(jù)庫鈔能力也無能為力的時候，引入對關(guān)鍵功能為核心的軟件服務(wù)就會成為最有性價比方案，如何在遇到問題時選擇合適的方案，就是體現(xiàn)你價值的時候了。

一個窮小伙攀上一個富家女，短暫的甜蜜終敵不過現(xiàn)實(shí)階級的不對等，美好的結(jié)局只存在于窮小伙的幻想與瓊瑤老師的電視劇中。

如何在有限的成本中提升數(shù)據(jù)存儲的性能，便是本文章于大家論討的中心思想。

背景知識

相信大家的日常工作中會經(jīng)常接觸到以下內(nèi)容，小弟就簡單地總結(jié)一下吧。

關(guān)系型數(shù)據(jù)庫

關(guān)系型數(shù)據(jù)庫就是由二維表及其之間的聯(lián)系所組成的一個數(shù)據(jù)組織,為軟件提供事務(wù)數(shù)據(jù)一致性、數(shù)據(jù)持久化等功能，是軟件系統(tǒng)的核心存儲服務(wù)，是我們開發(fā)跟面試都是最常接觸到的數(shù)據(jù)庫，對于一些小型外包項目，一個mysql足以滿足全部的業(yè)務(wù)需求了。就是一個我們經(jīng)常接觸到的東西，內(nèi)里其實(shí)是充滿了門道的，往后章節(jié)再細(xì)聊其中門道。
優(yōu)點(diǎn)：

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

問題

1. 對硬盤I/O要求非常高
2. 大數(shù)據(jù)量的聚合查詢效率低
3. 索引不命中
4. 索引最左匹配原則導(dǎo)致不合適做全文檢索
5. 事務(wù)使用不當(dāng)會引起鎖堵塞
6. 水平擴(kuò)展后帶來的種種問題難處理

非關(guān)系型數(shù)據(jù)庫 – nosql

MySQL數(shù)據(jù)庫作為一種關(guān)系型數(shù)據(jù)的存儲軟件，有優(yōu)點(diǎn)同時也有明顯的缺點(diǎn)，因此通常在軟件系統(tǒng)數(shù)據(jù)量不斷擴(kuò)大與業(yè)務(wù)復(fù)雜度不段提升的情況下，不能指望通過增強(qiáng)MySQL數(shù)據(jù)庫的能力來解決全部的問題，用是引入其他存儲軟件，利用各類型的NoSql來解決軟件系統(tǒng)數(shù)據(jù)量不斷擴(kuò)大與業(yè)務(wù)復(fù)雜度不段提升的問題。
關(guān)系型數(shù)據(jù)庫是對關(guān)系型數(shù)據(jù)庫的在不同場景的優(yōu)化，不是意味著引入某種NoSql就萬事大吉，而是充分了解市面上NoSQL的類型與應(yīng)用難度，在合適的場景下選擇合適的存儲軟件才是正確的做法。

Key-Value型

在業(yè)務(wù)中會存在經(jīng)常對某些表的內(nèi)容進(jìn)行查詢，但查詢的結(jié)果絕大數(shù)是不變的，所以出現(xiàn)了以memcached、redis為主的Key-value存儲軟件，廣泛應(yīng)用在系統(tǒng)中的緩存模塊。Redis比Memcached多多的數(shù)據(jù)結(jié)構(gòu)與持久化讓其成為KV型NoSql中應(yīng)用最廣的。

搜索型

全文搜索的場景下，MySQLB+樹索引的查詢優(yōu)化，like查詢是無法命中索引的，每一次like關(guān)鍵字查詢都是一次全表掃描，在幾萬條數(shù)據(jù)量的表還算可以支撐，但數(shù)據(jù)最一在就會產(chǎn)生慢查詢，要是業(yè)務(wù)代碼寫得不好在事務(wù)中調(diào)用了Like查詢就會產(chǎn)生讀鎖。以倒排索引為核心的ElasticSearch為能完美地滿足全文搜索的場景，同時ElasticSearch對海量數(shù)據(jù)支持也十分好，文檔與生態(tài)也很好，ElasticSearch是搜索型的代表產(chǎn)品。

文檔型

文檔型NoSql指的是將半結(jié)構(gòu)化數(shù)據(jù)存儲為文檔的一種NoSql，文檔型NoSql通常以json或者xml格式存儲數(shù)據(jù)，因此文檔型NoSql是沒有Schema的，由于沒有Schema的特性，我們可以隨意地存儲與讀取數(shù)據(jù)，因此文檔型NoSql的出現(xiàn)是解決關(guān)系型數(shù)據(jù)庫表結(jié)構(gòu)擴(kuò)展不方便的問題的。筆者沒有使用過

列式

對于一定規(guī)模的企業(yè)，業(yè)務(wù)上會經(jīng)常涉及到一些實(shí)時且靈活的數(shù)據(jù)匯總，這種業(yè)務(wù)不太合適用提前計算的方案來解決，那怕是能用提前計算匯總的方案寫出了業(yè)務(wù)，但隨著匯總的數(shù)量據(jù)增加的時候，對匯總數(shù)據(jù)做最后一步累加也會慢慢變得很慢，那列式NoSql就是這種場景下的產(chǎn)物，大數(shù)據(jù)時代最具代表性的技術(shù)之一了，常見的有hbase，但HBase的應(yīng)用是十分重的，往往需要一整套hadoop生態(tài)來運(yùn)行，筆者公司用的是阿里云的AnalyticDB，一個兼容MySql查詢語句的列式存儲軟件。利用匯總+列式存儲軟件的強(qiáng)大查詢能力，足以支持各種實(shí)時且靈活的數(shù)據(jù)匯總務(wù)業(yè)。

案例

以2021年為時間節(jié)點(diǎn)來看，大多數(shù)的系統(tǒng)的初期都是以以下方案為起點(diǎn)的，接下來我會在這個案例中慢慢做一些調(diào)整。

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

硬優(yōu)化-鈔能力

• 階段一

  • 提高磁盤I/O,盡量拿用SSD磁盤 （質(zhì)的提升）
  • 提高內(nèi)存 ,增加查詢緩存空間
  • 增加CPU核心數(shù)，增加執(zhí)行線程

• 階段二

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

• 階段三

  • 服務(wù)商mysql服務(wù)更換為云原生分布式數(shù)據(jù)庫
  • 開啟自帶讀寫分離功能
  • 開啟自帶分表功能

軟優(yōu)化 – 查詢 – OLTP

OLTP主要用來記錄某類業(yè)務(wù)事件的發(fā)生，如用戶行為，當(dāng)行為產(chǎn)生后，系統(tǒng)會記錄是用戶在何時何地做了何事，這樣的一行（或多行）數(shù)據(jù)會以增刪改的方式在數(shù)據(jù)庫中進(jìn)行數(shù)據(jù)的更新處理操作，要求實(shí)時性高、穩(wěn)定性強(qiáng)、確保數(shù)據(jù)及時更新成功，像常見的業(yè)務(wù)系統(tǒng)系統(tǒng)都屬于OLTP，而使用的數(shù)據(jù)庫都為帶事務(wù)的數(shù)據(jù)庫，如MySlq、oracle等。對OLTP來說，提升查詢的速度、服務(wù)穩(wěn)定就是優(yōu)化的核心

• 慢查詢
  • 通過慢查詢?nèi)罩景l(fā)現(xiàn)有效率問題的SQL
• 問題sql排查方向
  • 索引設(shè)計有問題
  • SQL語句有問題
  • 數(shù)據(jù)庫選錯索引
  • 單表體積大
• Explain具體分析
  • 查看sql執(zhí)行較率
  • 查看索引命中情況 （重點(diǎn)）
• mysql優(yōu)化器
  • 優(yōu)化器選取索引時，會參考索引的基數(shù)(Cardinality)
  • 基數(shù)是MySQL自動維護(hù)且估算出來的，不一定完成準(zhǔn)確
  • 索引不命中或用錯索引就是優(yōu)化器這一步出了問題
  • analyze 可以重新統(tǒng)計索引信息并重算基數(shù)
• 強(qiáng)制索引
  • force 關(guān)鍵字可以強(qiáng)制使用索引，在業(yè)務(wù)代碼上強(qiáng)制指定index
• 覆蓋索引 – 最理想的命中索引
  • 覆蓋索引指的是，查詢語句從執(zhí)行到返回結(jié)果均使用同一個索引(唯一、普通、聯(lián)合索引等)
  • 覆蓋索引可以有交減少回表查詢
  • 若數(shù)據(jù)的查詢不只使用了一個索引，則不是覆蓋索引
  • 可以通過優(yōu)化SQL語句或優(yōu)化聯(lián)合索引，來使用覆蓋索引
• count() 函數(shù)
  • count(非索引字段) – 無法使用覆蓋索引，理論上最慢
  • count(索引字段) – 可以覆蓋索引，依然需要每次判斷字段是否為null
  • count(主鍵) – 同上
  • count(1) – 只有掃描索引樹，沒有解析數(shù)據(jù)行的過程，理論更快,但還是會判讀1是否為null
  • count(* ) – MySQL專門優(yōu)化了count(*)函數(shù)直接返回索引樹中數(shù)據(jù)的個數(shù)，最優(yōu)
• ORDER BY
  • 盡量減少額外的排序，指定where條件
  • where 語句與ORDER BY語句組合滿足最左前綴
  • 最高效-索引覆蓋(場景少，遇見機(jī)率不大)
    • 索引覆蓋可以跳過生成中間結(jié)果集，直接輸出查詢結(jié)果
    • ORDER字段需要有索引且與WHERE的條件且與輸出內(nèi)容均在同一個索引中
• 分頁查詢
  • 先想辦法走索引覆蓋
  • 先查出所需要數(shù)據(jù)的id,回表得到最終結(jié)果集
• 索引下推
  • 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新特性
  • 松散索引掃描可以打破”左側(cè)原則”，解決帶頭大哥丟失的問題
  • 效率低于聯(lián)合索引
• 函數(shù)操作
  • 對索引字段進(jìn)行函數(shù)操作，優(yōu)化器會放棄索引
  • 這種情況可能包函：時間函數(shù)，字符串轉(zhuǎn)為數(shù)字，字符編碼轉(zhuǎn)換
  • 優(yōu)化使用服務(wù)端邏輯來代替mysql函數(shù)
• 單表體積過大
  • 升級mysql，不同的mysql軟件能承載的單表體積是不同的，我以目前的經(jīng)驗看，阿里云polardb集群版單表2億的情況下查詢命中索引是沒有問題的（優(yōu)先級高）
  • 數(shù)據(jù)結(jié)算 – 如流水類的數(shù)據(jù)可以按某個時間點(diǎn)來結(jié)算得到一個最新值，已結(jié)算流水轉(zhuǎn)到備份表 （優(yōu)先級中）
  • 數(shù)據(jù)冷熱分離 – 不能做結(jié)算的數(shù)據(jù)跟據(jù)查詢的頻次做區(qū)分，頻次低的轉(zhuǎn)移到另外的表中查詢，業(yè)務(wù)上區(qū)分好查詢的入口 （優(yōu)先級中）
  • 分布式數(shù)據(jù)庫分表 – 開啟分布式數(shù)據(jù)庫帶單的分表功能，分布式數(shù)據(jù)庫組件管理對分表后的插入、查詢（優(yōu)先級中）
  • 代碼實(shí)現(xiàn)分表 – 按一定的規(guī)則把單表拆分到多張表，在php、GO的大多數(shù)框架ORM中分拆后需要對框架ORM做一定的修改，Java中的ORM有原生的支持，建議在項目初期就考慮，越往后難度越大（優(yōu)先級低）

軟優(yōu)化 – 寫入更新刪除

• 鎖

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

  • 全局鎖

    • 自行g(shù)oogle/baidu

  • 表級鎖分為表鎖（數(shù)據(jù)鎖）和元數(shù)據(jù)鎖

    • 表鎖
      • 自行g(shù)oogle/baidu
    • 元數(shù)據(jù)鎖
      • 自行g(shù)oogle/baidu

  • 行鎖會鎖住數(shù)據(jù)行，分為共享鎖和獨(dú)占鎖

    • 自行g(shù)oogle/baidu

• 解決死鎖

  • 參數(shù)配置
    • 調(diào)整innodb_lock_wait_timeout參數(shù)
      • 默認(rèn)為50秒，即等待50秒還未獲取鎖，當(dāng)前語句報錯
      • 如果等待時間過長，可以適當(dāng)縮短此參數(shù)
    • 主動死鎖檢測：innodb_deadlock_detect
      • 發(fā)現(xiàn)死鎖時回滾代價較小的事務(wù)
      • 默認(rèn)開啟
  • 沒必要情況下不開啟事務(wù)
  • 查詢盡量放在事務(wù)外，減少鎖的行數(shù)
  • 避免事務(wù)時間過長，不要在事務(wù)中觸發(fā)http請求
  • 主動查看事務(wù)狀態(tài)

    show  processlist;SELECT * FROM information_schema.INNODB_TRX; //長事務(wù)SELECT * FROM information_schema.INNODB_LOCKs; //查看鎖SELECT * FROM information_schema.INNODB_LOCK_waits; //查看阻塞事務(wù)

搜索業(yè)務(wù)

• 搜索行數(shù)10萬以下 – ?mysql硬扛
  • 提升mysql的cpu、io、內(nèi)存硬件
• 搜索行數(shù)10萬以上 – 引入Elasticsearch

Elasticsearch的倒排索引，適合做全文搜索，但數(shù)據(jù)構(gòu)結(jié)的靈活性差。

• 數(shù)據(jù)同步
  • 業(yè)務(wù)代碼變動數(shù)據(jù)時同時同步到Elasticsearch
  • Canel訂閱mysql日志觸發(fā)同步
• Elasticsearch-index
  • 由具有相同字段的文檔列表組成 – 類比為mysql的table
  • 字段類型一旦設(shè)定后，禁止修改，允許新增字段
  • 具體方法自行g(shù)oogle/baidu
• Elasticsearch-Document
  • 用戶存儲在es中的數(shù)據(jù)文檔 – 類比為mysql的行
  • 由 元數(shù)據(jù) 與 Json Object 組成
  • 元數(shù)據(jù) 與 Json Object詳情自行g(shù)oogle/baidu
• Elasticsearch-分詞器
  • 自行g(shù)oogle/baidu
• Elasticsearch-倒排索引 （重點(diǎn)）
  • 自行g(shù)oogle/baidu
• Elasticsearch-聚合分析
  • 自行g(shù)oogle/baidu

統(tǒng)計業(yè)務(wù) -OLAP

OLAP是相對于OLTP事務(wù)處理場景而然用來對數(shù)據(jù)的決策分析，是一種運(yùn)用在大數(shù)據(jù)分析上的離線數(shù)倉思路，不是具體的技術(shù)棧,當(dāng)你的方案能體現(xiàn)OLAP分析處理的思路的話，那該方案就是OLAP了。

早期數(shù)據(jù)倉庫構(gòu)建主要指的是把企業(yè)的業(yè)務(wù)數(shù)據(jù)庫如ERP、CRM、SCM等數(shù)據(jù)按照決策分析的要求建模并匯總到數(shù)據(jù)倉庫引擎中，其應(yīng)用以報表為主，目的是支持管理層和業(yè)務(wù)人員決策（中長期策略型決策）。隨著IT技術(shù)走向互聯(lián)網(wǎng)、移動化，數(shù)據(jù)源變得越來越豐富，在原來業(yè)務(wù)數(shù)據(jù)庫的基礎(chǔ)上出現(xiàn)了非結(jié)構(gòu)化數(shù)據(jù)，比如網(wǎng)站log，iot設(shè)備數(shù)據(jù)，APP埋點(diǎn)數(shù)據(jù)等，這些數(shù)據(jù)量比以往結(jié)構(gòu)化的數(shù)據(jù)大了幾個量級。

無論OLAP面對的業(yè)務(wù)如何變化，都離不開以下的步驟：確定分析領(lǐng)域->同步業(yè)務(wù)數(shù)據(jù)到運(yùn)算庫->數(shù)據(jù)清洗建模->同步到數(shù)據(jù)倉庫->對外暴露

其中計算源數(shù)據(jù)庫是為專門給數(shù)據(jù)清洗用的，目的是避免數(shù)據(jù)清洗時影響業(yè)務(wù)數(shù)據(jù)庫的性能。通過將計算源數(shù)據(jù)庫的數(shù)據(jù)按業(yè)務(wù)、維度清洗，增加數(shù)據(jù)易用性和復(fù)用性，得到最終的實(shí)時明細(xì)數(shù)據(jù)，落盤到數(shù)據(jù)倉庫，再由數(shù)據(jù)倉庫提供最后的決策分析數(shù)據(jù)。

DEMO方案

生產(chǎn)方案

每個環(huán)節(jié)的軟件都是可用相同功能的軟件替換的，用團(tuán)隊最有把握的軟件實(shí)現(xiàn)方案，那該方案就是OLAP了。

總結(jié)

優(yōu)化要遵循腳踏實(shí)地，一步步地做能力沉淀，多輪迭代，不可一蹴而就。基于自己的基礎(chǔ)、業(yè)務(wù)場景和未來的發(fā)展預(yù)期來多輪迭代。

迭代的原則是先把單個軟件服務(wù)通過軟優(yōu)化與硬優(yōu)化提升軟件的效率，當(dāng)優(yōu)化成本低于收益時，站在未來的發(fā)展預(yù)期參考市面上成熟的方案，跟據(jù)方案按需地引入新的軟件進(jìn)行組合式創(chuàng)新，切忌盲目照搬，有機(jī)地融合才能達(dá)到1+1>2、2+1>3的效果，當(dāng)引用的軟件遇到瓶頸時再反復(fù)這個過程。

謝謝您看到這里，以上便是文章的所有內(nèi)容，內(nèi)容中所提出的優(yōu)化點(diǎn)與方案不一定是最優(yōu)解，是個人工作中的最佳實(shí)踐，有不同見解歡迎談?wù)摻涣鳌?span>? ? ? ? ? ? ? ? ? ? ? ?? ? ? ? ? ? ? ? ? ? ? ? ? ? ?