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

為麟游等地區(qū)用戶提供了全套網(wǎng)頁設(shè)計制作服務(wù)，及麟游網(wǎng)站建設(shè)行業(yè)解決方案。主營業(yè)務(wù)為網(wǎng)站設(shè)計、成都做網(wǎng)站、麟游網(wǎng)站設(shè)計，以傳統(tǒng)方式定制建設(shè)網(wǎng)站，并提供域名空間備案等一條龍服務(wù)，秉承以專業(yè)、用心的態(tài)度為用戶提供真誠的服務(wù)。我們深信只要達到每一位用戶的要求，就會得到認可，從而選擇與我們長期合作。這樣，我們也可以走得更遠！

思考角度

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

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

數(shù)據(jù)庫跟系統(tǒng)業(yè)務(wù)是強關(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表進行數(shù)據(jù)統(tǒng)計分析匯總，如果直接用mysql多表查詢的話一定會產(chǎn)生慢查詢從而導致msyql服務(wù)的宕機，這時解決方案便是要不產(chǎn)品端妥協(xié)，要不改變技術(shù)棧。

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

一個窮小伙攀上一個富家女，短暫的甜蜜終敵不過現(xiàn)實階級的不對等，美好的結(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)里其實是充滿了門道的，往后章節(jié)再細聊其中門道。
優(yōu)點：

1. 事務(wù)

2. 持久化

3. 相對通用的SQL語言

問題

1. 對硬盤I/O要求非常高

2. 大數(shù)據(jù)量的聚合查詢效率低

3. 索引不命中

4. 索引最左匹配原則導致不合適做全文檢索

5. 事務(wù)使用不當會引起鎖堵塞

6. 水平擴展后帶來的種種問題難處理

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

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

Key-Value型

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

搜索型

全文搜索的場景下，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)擴展不方便的問題的。筆者沒有使用過

列式

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

案例

以2021年為時間節(jié)點來看，大多數(shù)的系統(tǒng)的初期都是以以下方案為起點的，接下來我會在這個案例中慢慢做一些調(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ā)生，如用戶行為，當行為產(chǎn)生后，系統(tǒng)會記錄是用戶在何時何地做了何事，這樣的一行（或多行）數(shù)據(jù)會以增刪改的方式在數(shù)據(jù)庫中進行數(shù)據(jù)的更新處理操作，要求實時性高、穩(wěn)定性強、確保數(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í)行較率

• 查看索引命中情況 （重點）

• mysql優(yōu)化器

• 優(yōu)化器選取索引時，會參考索引的基數(shù)(Cardinality)

• 基數(shù)是MySQL自動維護且估算出來的，不一定完成準確

• 索引不命中或用錯索引就是優(yōu)化器這一步出了問題

• analyze 可以重新統(tǒng)計索引信息并重算基數(shù)

• 強制索引

• force 關(guān)鍵字可以強制使用索引，在業(yè)務(wù)代碼上強制指定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

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

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

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

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

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

• 分頁查詢

• 先想辦法走索引覆蓋

• 先查出所需要數(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ù)操作

• 對索引字段進行函數(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ù)可以按某個時間點來結(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)先級中）

• 代碼實現(xiàn)分表 - 按一定的規(guī)則把單表拆分到多張表，在PHP、GO的大多數(shù)框架ORM中分拆后需要對框架ORM做一定的修改，JAVA中的ORM有原生的支持，建議在項目初期就考慮，越往后難度越大（優(yōu)先級低）

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

• 鎖

• 自行g(shù)oogle/baidu

• 表鎖

• 元數(shù)據(jù)鎖

• 自行g(shù)oogle/baidu

• 自行g(shù)oogle/baidu

• 自行g(shù)oogle/baidu

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

• 全局鎖

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

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

• 解決死鎖

• 調(diào)整innodb_lock_wait_timeout參數(shù)

• 主動死鎖檢測：innodb_deadlock_detect

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

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

• 發(fā)現(xiàn)死鎖時回滾代價較小的事務(wù)

• 默認開啟

• 參數(shù)配置

• 沒必要情況下不開啟事務(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-倒排索引 （重點）

• 自行g(shù)oogle/baidu

• Elasticsearch-聚合分析

• 自行g(shù)oogle/baidu

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

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

早期數(shù)據(jù)倉庫構(gòu)建主要指的是把企業(yè)的業(yè)務(wù)數(shù)據(jù)庫如ERP、CRM、SCM等數(shù)據(jù)按照決策分析的要求建模并匯總到數(shù)據(jù)倉庫引擎中，其應用以報表為主，目的是支持管理層和業(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埋點數(shù)據(jù)等，這些數(shù)據(jù)量比以往結(jié)構(gòu)化的數(shù)據(jù)大了幾個量級。

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

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

DEMO方案

生產(chǎn)方案

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

感謝各位的閱讀，以上就是“分析MySQL優(yōu)化思路”的內(nèi)容了，經(jīng)過本文的學習后，相信大家對分析MySQL優(yōu)化思路這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是創(chuàng)新互聯(lián)，小編將為大家推送更多相關(guān)知識點的文章，歡迎關(guān)注！