前言

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

文章的篇幅較長，如有興趣不妨品味一遍，希望沒有浪費您的數(shù)十分鐘?！就扑]學習：《mysql視頻教程》】

思考角度

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

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

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

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

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

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

背景知識

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

關系型數(shù)據(jù)庫

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

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

問題

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

非關系型數(shù)據(jù)庫 – NoSql

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

Key-Value型

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

搜索型

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

文檔型

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

列式

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

案例

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

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

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

• 階段一

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

• 階段二

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

• 階段三

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

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

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

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

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

• 鎖

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

  • 全局鎖

    • 自行google/baidu

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

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

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

    • 自行google/baidu

• 解決死鎖

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

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

搜索業(yè)務

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

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

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

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

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

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

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

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

DEMO方案

生產(chǎn)方案

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

總結

優(yōu)化要遵循腳踏實地，一步步地做能力沉淀，多輪迭代，不可一蹴而就?；谧约旱幕A、業(yè)務場景和未來的發(fā)展預期來多輪迭代。

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

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