前言

這篇是MySQL 數(shù)據(jù)庫規(guī)范的最后一篇--調(diào)優(yōu)篇，旨在提供我們發(fā)現(xiàn)系統(tǒng)性能變?nèi)?、MySQL系統(tǒng)參數(shù)調(diào)優(yōu)，SQL腳本出現(xiàn)問題的精準(zhǔn)定位與調(diào)優(yōu)方法。
哈哈，文尾有福利彩蛋哦

目錄

1.MySQL 調(diào)優(yōu)金字塔理論
2.MySQL 慢查詢分析--mysqldumpslow、pt_query_digest工具的使用(SQL腳本層面)
3.選擇合適的數(shù)據(jù)類型
4.去除無用的索引--pt_duplicate_key_checker工具的使用(索引層面)
5.反范式化設(shè)計(jì)(表結(jié)構(gòu))
6.垂直水平分表
7.MySQL 重要參數(shù)調(diào)優(yōu)(系統(tǒng)配置)

1.MySQL 調(diào)優(yōu)金字塔理論

如下圖所示：

MySQL調(diào)優(yōu)金字塔理論.png

數(shù)據(jù)庫優(yōu)化維度有四個(gè):
硬件、系統(tǒng)配置、數(shù)據(jù)庫表結(jié)構(gòu)、SQL及索引
優(yōu)化成本:
硬件>系統(tǒng)配置>數(shù)據(jù)庫表結(jié)構(gòu)>SQL及索引
優(yōu)化效果:
硬件<系統(tǒng)配置<數(shù)據(jù)庫表結(jié)構(gòu)<SQL及索引

2.MySQL 慢查詢分析

對于系統(tǒng)中慢查詢的分析，有助于我們更高效的定位問題，分析問題。
mysqldumpslow、pt_query_digest是進(jìn)行慢查詢分析的利器。

前置條件

1.查看本機(jī)MySQL Server 慢查詢是否打開

show variables like 'slow%'; 

慢查詢打開的情況如下所示：

慢查詢狀態(tài)

若慢查詢未打開則通過如下腳本設(shè)置慢查詢：

set global slow_query_log = on;
即
set global [上圖中選項(xiàng)] = [你要設(shè)置的參數(shù)值]
注意 slow_query_log_file 路徑要加單引號，因?yàn)槁窂絭archar  類型的。

2.1 mysqldumpslow分析慢查詢

mysqldumpslow 是MySQL自帶的分析數(shù)據(jù)庫慢查詢的原生利器，使用方法如下：

mysqldumpslow -t 3 /data/mysql/log/mysql_slow_query.log | more \G;
-t  3 顯示前3條慢查詢。

慢查詢信息及分析

慢查詢信息.png

但是 mysqldumpslow 顯示的信息比較少，比如說此條sql執(zhí)行次數(shù)在整體的執(zhí)行次數(shù)中占用的百分比。類似于上述信息在 mysqldumpslow 的分析結(jié)果中是不存在的。

接下里我們介紹另一種工具 pt_query_digest

2.2 pt_query_digest分析慢查詢

之所以使用 pt_query_digest 工具對慢查詢?nèi)罩具M(jìn)行分析，主要原因是上述工具分析的內(nèi)容更佳豐富，更加方便我們分析慢查詢。
前置條件
安裝 pt_query_digest ,Google搜索應(yīng)該一大把。

確保 pt_query_digest 安裝成功 執(zhí)行如下操作：

pt-query-digest /data/mysql/log/mysql_slow_query.log > slow_log.report

上述命令表示分析本機(jī)慢查詢，并輸出報(bào)表(文件)
接下來分析生成的報(bào)表：

tail slow_log.report

按如下圖所示信息：

pt_query_digest報(bào)表分析.png

我們對以上紅色框圖標(biāo)記的報(bào)表信息進(jìn)行詳細(xì)描述，事實(shí)上這也是我們需要掌握的重點(diǎn)：

1.pct :sql語句某執(zhí)行屬性占所有慢查詢語句某執(zhí)行屬性的百分比
1.total：sql語句某執(zhí)行屬性的所有屬性時(shí)間。
2.Count：sql語句執(zhí)行的次數(shù)，對應(yīng)的pct 表示此sql 語句執(zhí)行次數(shù)占所有慢查詢語句執(zhí)行次數(shù)的％比。上圖為25%，total:表示總共執(zhí)行了1次。
3.Exec time:sql執(zhí)行時(shí)間
4.Lock time:sql執(zhí)行期間被鎖定的時(shí)間
5.Rows sent:傳輸?shù)挠行?shù)據(jù)，在select 查詢語句中才有值
6.Rows examine:總共查詢的數(shù)據(jù)，非目標(biāo)數(shù)據(jù)。
7.Query_time distribution:查詢時(shí)間分布
8.SQL 語句:上圖中為 select * from payment limit 10\G;

舉例說明：加入某執(zhí)行次數(shù)(count) 占比較高的sql語句，執(zhí)行時(shí)間很長，Rows sent 數(shù)值很小，Rows examine 數(shù)值很大則表明(I/O較大)。那就表明有可能 sql 查詢語句走了全表掃描，或者全索引掃描。那么就要建立合適索引或者優(yōu)化sql語句了。
如下很好的展示了我們在分析慢查詢時(shí)需要著重分析的三點(diǎn)：

慢查詢分析的三個(gè)基準(zhǔn)點(diǎn).png

3.選擇合適的數(shù)據(jù)類型

可以參考MySQL開發(fā)規(guī)范--設(shè)計(jì)篇中的1.6 數(shù)據(jù)表設(shè)計(jì)與規(guī)劃

如下圖是常用字段類型的選擇建議：

選擇合適的數(shù)據(jù)類型

4.去除無用的索引--pt_duplicate_key_checker工具的使用(索引層面)

此工具可以分析選定的 database 中的所有表中建立的index 中可能重復(fù)的索引，并給出了刪除建議。

5.反范式化設(shè)計(jì)(表結(jié)構(gòu))

關(guān)于范式的理解，請參考--MySQL 數(shù)據(jù)庫規(guī)范--設(shè)計(jì)篇1.1 數(shù)據(jù)庫表的設(shè)計(jì)范式(三范式&反范式)
先看一個(gè)不滿足第三范式的數(shù)據(jù)表設(shè)計(jì)：

不滿足第三范式的數(shù)據(jù)表設(shè)計(jì).png

不滿足第三范式產(chǎn)生的問題：
假如將表中屬于飲料分類的數(shù)據(jù)全部刪除了，那么飲料分類也就不存在了，飲料的分類描述也就沒了，查詢不到了。這明顯是不合理的。

重點(diǎn)：滿足第三范式要求非鍵屬性之間沒有任何依賴關(guān)系，上圖中分類與分類描述存在直接依賴關(guān)系。所以不符合第三范式的要求，那么要讓表符合第三范式需要怎樣做呢？

拆分后滿足第三范式的表：

滿足第三范式的表.png

我們采用一張 分類--商品名稱 中間表來充當(dāng)分表之后的中間橋梁。

當(dāng)然如果一直遵循范式化設(shè)計(jì)，什么設(shè)計(jì)都向第三范式靠攏，當(dāng)查詢需要連接很多表的時(shí)候，建立索引已經(jīng)起不到什么作用了，因?yàn)樽侄味疾辉谕粡埍碇?，所以建立索引是無用功，那么就要考慮反范式化的設(shè)計(jì)了。

6.垂直、水平分表

原則上當(dāng)表中數(shù)據(jù)記錄的數(shù)量超過3000萬條，再好的索引也已經(jīng)不能提高數(shù)據(jù)查詢的速度了，這時(shí)候就需要將表拆分成更多的小表，來進(jìn)行查詢。
分表的機(jī)制有兩種:

垂直分表:也就是將一部分列割裂開將數(shù)據(jù)放置在新設(shè)置的表中，優(yōu)先選擇字段值長度較長，類型較重的字段進(jìn)行垂直分離。
水平分表:將表中數(shù)據(jù)水平切分，可以按照范圍、取模運(yùn)算、hash運(yùn)算進(jìn)行數(shù)據(jù)切割，每張表的結(jié)構(gòu)信息都是一樣的。

7.MySQL 重要參數(shù)調(diào)優(yōu)(系統(tǒng)配置)

7.1 操作系統(tǒng)配置優(yōu)化

操作系統(tǒng)配置優(yōu)化

打開操作系統(tǒng)文件限制.png

簡要介紹一下：

1.tcp連接配置，超時(shí)時(shí)間配置
2.linux上文件打開數(shù)量限制
3.除此之外，最好在MySQL 服務(wù)器上關(guān)閉iptables,selinux 等防火墻軟件。

7. 2 MySQL 配置文件優(yōu)化

MySQL 可以通過啟動(dòng)時(shí)制定配置參數(shù)和使用配置文件兩種方法進(jìn)行配置，在大多數(shù)情況下配置文件位于/etc/my.cnf或是/etc/mysql/my.cnf MySQL查找配置文件順序可以通過以下方法獲得：

$ /usr/sbin/mysqld --verbose --help | grep -A 1 'Default options'

注意:如果多個(gè)位置存在配置文件，后面的會(huì)覆蓋前面的

7.2.1 innodb_buffer_pool_size

innodb_buffer_pool_size 是非常重要的一個(gè)參數(shù)，用戶配置Innodb 的緩沖池大小。如果數(shù)據(jù)庫中只有Innodb表，則推薦配置量為總內(nèi)存的75%。
一般情況下運(yùn)行如下命令，即可獲得配置innodb_buffer_pool_size 參數(shù)的最佳值：

select engine round(sum(data_length+index_length)/1024/1024,1) as 
'total MB' from information_schema.tables where table_schema not in ("information_schema","performance_schema") group by engine;
Innodb_buffer_pool_size > Total MB;

7.2.2 innodb_buffer_pool_instance

MySQL 系統(tǒng)中有一些資源是需要獨(dú)占使用的，比如緩沖去就是這樣一種資源,因此如果系統(tǒng)中只有一個(gè)緩沖池，那么會(huì)增加阻塞的幾率。我們多分成多個(gè)，則可以增加并發(fā)性能。

7.2.3 innodb_log_buffer_size

innodb log緩沖的大小，設(shè)置大小只能能容得下1s中產(chǎn)生的事務(wù)日志就可以。

7.2.4 innodb_flush_log_at_trx_commit

關(guān)鍵參數(shù)，對innodb 的I/O影響很大。默認(rèn)值為1，可以去0，1，2三個(gè)值，一般建議為2，但如果數(shù)據(jù)安全性要求較高則默認(rèn)使用1。

• 0:每隔1s中才將事務(wù)提交的變更記錄刷新到磁盤
• 1:每一次事務(wù)提交都把變更日志刷新到磁盤（最安全的方式）
• 2:每一次提交將日志刷新到緩沖區(qū)，隔1s之后會(huì)將日志刷新到磁盤。

7.2.5 innodb_read_io_threads && innodb_write_io_threads

這兩個(gè)參數(shù)決定了Innodb讀寫的I/O進(jìn)程數(shù)，默認(rèn)為4。
決定這兩個(gè)參數(shù)數(shù)值的因素也有兩個(gè)：cpu核數(shù)、應(yīng)用場景中讀寫事務(wù)比例。

7.2.6 innodb_file_per_table

關(guān)鍵參數(shù)，默認(rèn)情況下配置為off。
控制innodb每一個(gè)表使用獨(dú)立的表空間，默認(rèn)情況下，所有的表都會(huì)建立在共享表空間當(dāng)中。
使用共享表空間會(huì)帶來什么問題：

 1.多個(gè)表對共享表空間的操作，是順序進(jìn)行的，這樣的話操作效率在并發(fā)情況下回降低。
2.如果現(xiàn)在要?jiǎng)h除一張表，會(huì)導(dǎo)致共享表空間先要將數(shù)據(jù)導(dǎo)出來，再重組。

7.2.7 innodb_stats_on_metadata

作用：決定了MySQL在什么情況下會(huì)刷新innodb表的統(tǒng)計(jì)信息。
保證數(shù)據(jù)庫優(yōu)化器能使用到最新的索引，但不能太頻繁，一般設(shè)置為off。

福利彩蛋

職位:騰訊OMG 廣告后臺(tái)高級開發(fā)工程師;
Base:深圳;
場景:海量數(shù)據(jù)，To B，To C，場景極具挑戰(zhàn)性。
基礎(chǔ)要求:
熟悉常用數(shù)據(jù)結(jié)構(gòu)與算法;
熟悉常用網(wǎng)絡(luò)協(xié)議，熟悉網(wǎng)絡(luò)編程;
熟悉操作系統(tǒng)，有線上排查問題經(jīng)驗(yàn);
熟悉MySQL,oracle;
熟悉JAVA，GoLang，c++其中一種語言均可;
可內(nèi)推，歡迎各位優(yōu)秀開發(fā)道友私信[微笑]
期待關(guān)注我的開發(fā)小哥哥，小姐姐們私信我，機(jī)會(huì)很好，平臺(tái)對標(biāo)抖音，廣告生態(tài)平臺(tái)，類似Facebook 廣告平臺(tái)，希望你們用簡歷砸我~
聯(lián)系方式 微信 13609184526

博客搬家：大坤的個(gè)人博客
歡迎評論哦~