MySQL優(yōu)化（一）——哪些因素影響了數(shù)據(jù)庫性能

所謂知己知彼潜支，百戰(zhàn)不殆娄徊。既然要優(yōu)化數(shù)據(jù)庫，我們就首先要知道胸嘴，優(yōu)化的是什么雏掠，或者說：什么因素影響了數(shù)據(jù)庫的性能。

影響數(shù)據(jù)庫因素主要因素總結(jié)如下：

sql查詢速度

網(wǎng)卡流量

服務(wù)器硬件

磁盤IO

以上因素并不是時(shí)時(shí)刻刻都會(huì)影響我們的數(shù)據(jù)庫性能劣像，而就像木桶效應(yīng)一樣：如果其中一個(gè)因素嚴(yán)重影響性能乡话，那么整個(gè)數(shù)據(jù)庫性能就會(huì)嚴(yán)重受阻。另外耳奕，這些影響因素都是相對的绑青，例如：當(dāng)數(shù)據(jù)量并沒有達(dá)到百萬千萬這樣的級別，那么sql查詢速度也許就不是個(gè)重要因素屋群，換句話說闸婴，你的sql語句效率適當(dāng)?shù)拖驴赡懿⒉挥绊懻麄€(gè)效率多少，反之芍躏，這種情況邪乍，無論如何怎么優(yōu)化sql語句，可能都沒有太明顯的效果。?

因此溺欧，知道哪些影響因素會(huì)直接導(dǎo)致哪些現(xiàn)象產(chǎn)生喊熟，是至關(guān)重要的經(jīng)驗(yàn)，就像福爾摩斯一樣姐刁，通過現(xiàn)象看本質(zhì)芥牌。接下來我們對不同的現(xiàn)象與影響因素做一一對應(yīng)的總結(jié)。

超高的QPS和TPS

風(fēng)險(xiǎn)：效率低下的SQL

什么是QPS：每秒鐘查詢量聂使。如果每秒鐘能處理100條查詢sql語句壁拉，那么QPS就約等于100

什么是TPS：每秒鐘事務(wù)處理的數(shù)量。

在大促的情況下柏靶，訪問量暴增弃理。這種情況下，sql語句的優(yōu)化顯得最直接最有效屎蜓。由于現(xiàn)在的mysql不支持多cpu并發(fā)運(yùn)算痘昌，即每條sql只能由一條cpu執(zhí)行。這也就意味著炬转，如果我們想提高單挑sql的執(zhí)行速度辆苔，無法通過增加cpu的方式達(dá)到效果。

大量的并發(fā)和超高的CPU使用率

風(fēng)險(xiǎn)：

大量的并發(fā)：數(shù)據(jù)庫連接數(shù)被占滿

對于數(shù)據(jù)庫而言扼劈，所能建立的連接數(shù)是有限的驻啤，mysql中max_connections參數(shù)默認(rèn)值是100。

超高的CPU使用率：因CPU資源耗盡而出現(xiàn)宕機(jī)

磁盤IO

風(fēng)險(xiǎn)：磁盤IO性能突然下降荐吵。

使用更好的磁盤設(shè)備解決骑冗。

其他大量消耗磁盤性能的計(jì)劃任務(wù)

調(diào)整計(jì)劃任務(wù)

網(wǎng)卡流量

風(fēng)險(xiǎn)：網(wǎng)卡IO被占滿（100Mb/8=100MB）

如何避免無法連接數(shù)據(jù)庫的情況：

減少從服務(wù)器的數(shù)量。從服務(wù)器都要從主服務(wù)器上復(fù)制日志先煎，所以贼涩，從服務(wù)器越多，網(wǎng)絡(luò)流量越大榨婆。

進(jìn)行分級緩存磁携。前方大量緩存突然失效會(huì)對數(shù)據(jù)庫造成嚴(yán)重的沖擊。

避免使用“select * ”進(jìn)行查詢

分離業(yè)務(wù)網(wǎng)絡(luò)和服務(wù)器網(wǎng)絡(luò)

大表

什么樣的表可以稱為大表

記錄行數(shù)巨大良风，單表超過千萬行

表數(shù)據(jù)文件巨大，表數(shù)據(jù)文件超過10G

影響

慢查詢

很難在一定的時(shí)間內(nèi)過濾出所需要的數(shù)據(jù)

對DDL操作

建立索引需要很長的時(shí)間

風(fēng)險(xiǎn)：MySQL版本<5.5建立索引會(huì)鎖表闷供，>=5.5雖然不會(huì)鎖表但會(huì)引起主從延遲烟央。

修改表結(jié)構(gòu)需要很長時(shí)間鎖表

風(fēng)險(xiǎn)：會(huì)造成長時(shí)間的主從延遲；影響正常的數(shù)據(jù)操作

如何處理數(shù)據(jù)庫中的大表

分庫分表：把一張大表分成多個(gè)小表

難點(diǎn)：

分表主鍵的選擇歪脏。如訂單號(hào)疑俭，訂單地區(qū)等，好的分表主鍵選擇婿失，會(huì)對后期的擴(kuò)展更有利

分表后跨分區(qū)數(shù)據(jù)的查詢和統(tǒng)計(jì)钞艇。

分庫分表需要消耗大量的人力物力啄寡，而且要冒著影響業(yè)務(wù)的風(fēng)險(xiǎn)，所以要慎重哩照。

歷史數(shù)據(jù)歸檔

大表的歷史數(shù)據(jù)歸檔挺物，可以減少對前后端業(yè)務(wù)的影響。?

難點(diǎn)：

歸檔時(shí)間點(diǎn)的選擇飘弧。例如訂單表识藤，可以對一年前的數(shù)據(jù)進(jìn)行歸檔；日志類的數(shù)據(jù)次伶，對一個(gè)月前的數(shù)據(jù)歸檔即可痴昧。

如何進(jìn)行歸檔操作。

大事務(wù)

事務(wù)是數(shù)據(jù)庫系統(tǒng)區(qū)別于其他一切文件系統(tǒng)的重要特性之一

事務(wù)是一組具有原子性的SQL語句

事務(wù)特性：原子性冠王，一致性赶撰，隔離性，持久性

原子性：

一個(gè)事務(wù)必須被視為一個(gè)不可分割的最小工作單元柱彻。整個(gè)事務(wù)要么全部提交成功豪娜，要么全部失敗。

例如：銀行轉(zhuǎn)賬绒疗，我向你匯錢侵歇，要么成功，我的賬戶減少1000元吓蘑，你的賬戶增加1000元惕虑。要么失敗，我不減磨镶，你也沒有增加溃蔫。不能出現(xiàn)：我的賬戶減少1000，這時(shí)候斷電了琳猫，你沒收到伟叛。

一致性：

事務(wù)將數(shù)據(jù)庫從一種一致性狀態(tài)轉(zhuǎn)換到另一種一致性狀態(tài)，在事務(wù)開始之前和事務(wù)結(jié)束之后數(shù)據(jù)庫中的數(shù)據(jù)的完整性沒有被破壞

例如：銀行轉(zhuǎn)賬脐嫂，轉(zhuǎn)來轉(zhuǎn)去统刮，總和應(yīng)該保持不變。在我看開账千，一致性其實(shí)就是宏觀上強(qiáng)調(diào)了一下原子性侥蒙。只要原子性原則沒有被破壞，應(yīng)該就總是一致的匀奏。

隔離性：

一個(gè)事務(wù)對數(shù)據(jù)庫中的數(shù)據(jù)進(jìn)行修改鞭衩，在未提交完成前對其他事務(wù)是否可見的。隔離性有四種級別：

未提交讀（READ UNCOMMITED）

已提交讀（READ COMMITED）

可重復(fù)讀（REPEATABLE READ）

串行化（SERIALIZABLE）

例如：銀行轉(zhuǎn)賬。比如你答應(yīng)給你女朋友轉(zhuǎn)1000塊錢給她買粉论衍。

未提交讀：你開啟一個(gè)事務(wù)瑞佩，然后轉(zhuǎn)賬1000給你女朋友，注意不要提交事務(wù)坯台。然后讓你女朋友開啟一個(gè)事務(wù)查賬戶炬丸，她會(huì)發(fā)現(xiàn)賬戶確實(shí)多了1000，然后屁顛屁顛出門了捂人。這時(shí)候你回滾事務(wù)御雕，等于轉(zhuǎn)賬失敗。這時(shí)候你的女朋已經(jīng)選好準(zhǔn)備刷卡了滥搭，可是錢沒了酸纲。你就可以教訓(xùn)她了。為什么女朋友尷尬了瑟匆，就是因?yàn)樗x到了你沒有提交事務(wù)的數(shù)據(jù)闽坡，這樣的數(shù)據(jù)是臟數(shù)據(jù)，是不算數(shù)的愁溜。

已提交讀：后來你女朋友改了數(shù)據(jù)庫的隔離級別疾嗅。這次你還想這么玩，可是當(dāng)你不提交事務(wù)時(shí)冕象，你女朋友就永遠(yuǎn)看不到自己的賬戶到賬1000代承，你只能老老實(shí)實(shí)的提交事務(wù)了，事務(wù)已提交渐扮，錢就回不來了论悴。?

這種方式有效地防止了程序員的女盆友上當(dāng)受騙

可重復(fù)讀：女朋友覺得這樣還不夠，把數(shù)據(jù)庫隔離級別又改了墓律。這時(shí)候你由于上次的教訓(xùn)膀估，只能老老實(shí)實(shí)匯錢了。但是你發(fā)現(xiàn)無論你匯了多少錢過去耻讽，而且提交了察纯，女朋友的賬戶就是不見加錢。你開始懷疑了针肥，原來女朋友在要錢的時(shí)候開啟了一個(gè)查詢的事務(wù)饼记，這個(gè)事務(wù)在你匯錢之前，可重復(fù)讀的隔離級別這是后根本看不到此時(shí)的賬戶變化慰枕，查詢結(jié)果也一直是她事務(wù)開啟前的狀態(tài)握恳。等你匯了夠多了，她提交查詢事務(wù)捺僻，第二次查詢賬戶，整整多了2000塊呢。

串行化：互聯(lián)網(wǎng)行業(yè)很少用匕坯，不介紹了束昵。

持久性

一旦事務(wù)提交，則其所做的修改就會(huì)永久保存到數(shù)據(jù)庫中葛峻。

什么是大事務(wù)

運(yùn)行時(shí)間比較長锹雏，操作數(shù)據(jù)比較多的事務(wù)

風(fēng)險(xiǎn)：

鎖定太多數(shù)據(jù)，造成大量的阻塞和鎖超時(shí)术奖。

回滾時(shí)需要的時(shí)間比較長礁遵。

執(zhí)行時(shí)間長，容易造成主從延遲采记。

如何處理大事務(wù)

避免一次處理太多的數(shù)據(jù)

移出不必要在事務(wù)中的select操作

MySQL優(yōu)化（二）——什么影響了MySQL性能

影響性能的幾個(gè)方面：

服務(wù)器硬件

服務(wù)器系統(tǒng)

數(shù)據(jù)庫存儲(chǔ)引擎的選擇

數(shù)據(jù)庫參數(shù)配置

數(shù)據(jù)庫結(jié)構(gòu)設(shè)計(jì)和SQL語句

一佣耐、服務(wù)器硬件

1、CPU的選擇

我們的應(yīng)用是否是CPU密集型的

對于cpu密集型的應(yīng)用唧龄，我們需要加快sql語句的處理速度兼砖。由于mysql的sql語句處理是單線程的，因此我們需要更好的cpu既棺，而不是更多的cpu讽挟。

我們系統(tǒng)的并發(fā)量如何

一個(gè)cpu同時(shí)只能處理一條sql語句。所以丸冕，高并發(fā)量的情況下耽梅，就需要更多的cpu而不是更快的cpu。

選擇32位還是64位的cpu

64位已經(jīng)是默認(rèn)配置了胖烛。

2眼姐、內(nèi)存的選擇

內(nèi)存的io要遠(yuǎn)高于磁盤，即便是SSD或者Fusion_IO洪己。所以把數(shù)據(jù)緩存到內(nèi)存中讀取妥凳，可以大大提高性能。?

常用的mysql引擎中答捕，MyISAM把索引緩存到內(nèi)存逝钥，數(shù)據(jù)不緩存。而InnoDB同時(shí)緩存數(shù)據(jù)和索引拱镐。

緩存不僅對讀取有益艘款，對寫入也是可以優(yōu)化的，我們可以通過緩存沃琅，將多次寫入合并成一次寫入操作哗咆。

怎么選擇內(nèi)存：主頻盡量大，型號(hào)盡量相同益眉，單條內(nèi)存容量盡量打晌柬。

3姥份、磁盤的選擇

傳統(tǒng)機(jī)械磁盤

RAID增強(qiáng)傳統(tǒng)機(jī)械磁盤

固態(tài)存儲(chǔ)SSD和PCIe卡

網(wǎng)絡(luò)存儲(chǔ)NAS和SAN

如何選擇傳統(tǒng)機(jī)械硬盤

存儲(chǔ)容量

傳輸速度

訪問時(shí)間

主軸轉(zhuǎn)速

物理尺寸：越小性能越高，存儲(chǔ)空間越小

RAID增強(qiáng)

什么是RAID：把多個(gè)容量小的磁盤組成一組容量更大的磁盤年碘，并提供數(shù)據(jù)冗余來保證數(shù)據(jù)完整性的技術(shù)澈歉。

RAID 0，多磁盤串聯(lián)屿衅。成本最低埃难，數(shù)據(jù)容易丟失?

RAID 1，鏡像涤久。磁盤利用率降低一半涡尘。?

RAID5，分布式奇偶校驗(yàn)磁盤陣列?

RAID10响迂，分片鏡像

等級特點(diǎn)是否冗余盤數(shù)讀寫

RAID0便宜考抄，快速，危險(xiǎn)否N快快

RAID1高速讀栓拜，簡單座泳，安全有2快慢

RAID5安全，成本這種有N+1快取決于最慢的盤

RAID10貴幕与，高速挑势，安全有2N快快

推薦RAID10

固態(tài)存儲(chǔ)

相比機(jī)械磁盤，固態(tài)磁盤有更好的隨機(jī)讀寫性能啦鸣。?

相比機(jī)械磁盤潮饱，固態(tài)磁盤能更好的支持并發(fā)。?

相比機(jī)械磁盤诫给，固態(tài)磁盤 更容易損壞

SSD：?

1.使用SATA接口香拉。可以替換傳統(tǒng)磁盤而不需任何改變?

2.SATA接口的SSD同樣支持RAID技術(shù)中狂。注意SSD的RAID控制器與傳統(tǒng)不同凫碌。

PCI-E?

1.無法使用SATA接口，需要獨(dú)特的驅(qū)動(dòng)和配置?

2.價(jià)格相對于SSD要貴胃榕，但是性能比SSD更好

固態(tài)存儲(chǔ)的使用場景：?

1.適用于存在大量的隨機(jī)I/O的場景盛险。?

2.適用于解決單線程負(fù)載的I/O瓶頸。

網(wǎng)絡(luò)存儲(chǔ)

SAN：通過光釬鏈接服務(wù)器勋又，服務(wù)器可以將其當(dāng)做硬盤使用苦掘。適合大量順序讀寫?

NAS：使用網(wǎng)絡(luò)連接，通過基于文件的協(xié)議如NFS或SMB來訪問楔壤。

網(wǎng)絡(luò)存儲(chǔ)適合的場景：

數(shù)據(jù)庫備份

4鹤啡、網(wǎng)絡(luò)性能的影響

延遲、帶寬（吞吐）

網(wǎng)絡(luò)帶寬的影響蹲嚣，不必多說递瑰∷钌可能很多人認(rèn)為數(shù)據(jù)庫服務(wù)器與Web服務(wù)器的通信是在內(nèi)網(wǎng)下的，帶寬影響不大泣矛。其實(shí)在大促的情況下疲眷，我們有50臺(tái)服務(wù)器，同時(shí)向數(shù)據(jù)庫請求2M的數(shù)據(jù)您朽，那么就需要100M的帶寬了。?

建議：

采用高性能和高帶寬的網(wǎng)絡(luò)接口設(shè)備和交換機(jī)换淆。

對多個(gè)網(wǎng)卡進(jìn)行綁定哗总，增強(qiáng)可用性和帶寬。

盡可能的進(jìn)行網(wǎng)絡(luò)隔離倍试。

5讯屈、服務(wù)器硬件對性能的影響，總結(jié)

cpu?

1县习、64位的cpu一定要工作在64位的系統(tǒng)下涮母。?

2、對于并發(fā)比較高的場景躁愿，cpu的數(shù)量比頻率重要?

3叛本、對于cpu密集型場景和復(fù)雜SQL則頻率越高越好。

內(nèi)存?

1彤钟、選擇主板所能使用的最高頻率的內(nèi)存?

2来候、內(nèi)存的大小對性能很重要，所以盡可能的大

I/O子系統(tǒng)?

PCIe–>SSD–>Raid10–>磁盤–>SAN

二逸雹、服務(wù)器系統(tǒng)

MySql適合的操作系統(tǒng)

Windows

FreeBSD

Solaris

Linux

下面的內(nèi)容以CentOs系統(tǒng)為例

系統(tǒng)參數(shù)優(yōu)化

內(nèi)核相關(guān)參數(shù)（/etc/sysctl.conf）

網(wǎng)絡(luò)相關(guān)?

- net.core.somaxconn=65535

? ? 對于一個(gè)TCP連接來說营搅，服務(wù)器端和客戶端需要進(jìn)行三次握手來建立網(wǎng)絡(luò)的連接。當(dāng)三次握手成功之后梆砸，我們可以用netstat命令查看端口的狀態(tài)由監(jiān)聽轉(zhuǎn)變成了連接转质，接著該連接就可以傳輸數(shù)據(jù)了。對于一個(gè)監(jiān)聽狀態(tài)的端口帖世，都會(huì)有自己的監(jiān)聽隊(duì)列休蟹，而該參數(shù)就決定了監(jiān)聽隊(duì)列的最大長度。

1

2

- net.core.netdev_max_backlog=65535?

- net.ipv4.tcp_max_syn_backlog=65535

加快tcp連接的回收

net.ipv4.tcp_fin_timeout=10

net.ipv4.tcp_tw_reuse=1

net.ipv4.tcp_tw_recycle=1

tcp連接接收和發(fā)送緩沖區(qū)大小的默認(rèn)值和最大值

net.core.wmem_default = 87380

net.core.wmem_=16777216

net.core.rmem_default=87380

net.core.rmem_max=16777216

檢測占用的tcp連接是否已經(jīng)失效

net.ipv4.tcp_keepalive_time=120 發(fā)送時(shí)間

net.ipv4.tcp_keepalive_intvl=30 沒有返回再次發(fā)送的間隔

net.ipv4.tcp_keepalive_probes=3 發(fā)送幾次

內(nèi)存相關(guān)參數(shù)

kernel.shmmax = 4294967295

注意：?

1狮暑、這個(gè)參數(shù)應(yīng)該設(shè)置的足夠大鸡挠，以便能在一個(gè)共享內(nèi)存段下容納下整個(gè)的Innodb緩沖池大小?

2、這個(gè)值的大小對于64位linux系統(tǒng)搬男，可取的最大值為物理內(nèi)存值-1byte拣展，建議值為大于物理內(nèi)存的一半，一般取值大于Innodb緩沖池大小即可缔逛。

vm.swappiness=0?

這個(gè)參數(shù)當(dāng)內(nèi)存不足時(shí)會(huì)對性能產(chǎn)生比較明顯的影響备埃。

拓展：

Linux系統(tǒng)內(nèi)存交換區(qū)：在linux系統(tǒng)安裝時(shí)都會(huì)有一個(gè)特殊的磁盤分區(qū)姓惑，稱之為系統(tǒng)交換分區(qū)。如果我們使用free-m在系統(tǒng)中查看可以看到類似下面內(nèi)容按脚，其中swap就是交換分區(qū)于毙。

1

2

3

當(dāng)操作系統(tǒng)因?yàn)闆]有足夠的內(nèi)存時(shí)就會(huì)將一些虛擬內(nèi)存寫到磁盤的交換區(qū)中，這樣就會(huì)發(fā)生內(nèi)存交換

在MySQL服務(wù)器上是否要使用交換分區(qū)有一些爭議：

-主張 完全禁用 的風(fēng)險(xiǎn)：?

1辅搬、降低操作系統(tǒng)的性能?

2唯沮、容易造成內(nèi)存溢出，崩潰堪遂，或都被操作系統(tǒng)kill掉

結(jié)論：在MySQL服務(wù)器上保留交換分區(qū)介蛉，但是要控制何時(shí)使用。vm.swappiness=0就是告訴Linux內(nèi)核除非虛擬內(nèi)存完全滿了溶褪，否則不要使用交換區(qū)币旧。

增加資源限制（/etc/security/limit.conf）?

這個(gè)文件實(shí)際上是linux PAM也就是插入式認(rèn)證模塊的配置文件。?

其中重要的配置是：打開文件數(shù)的限制猿妈。

* soft nofile 65535

* hard nofile 65535

其中：*表示對所有用戶有效吹菱，soft指當(dāng)前系統(tǒng)生效的設(shè)置，hard表明系統(tǒng)中所能設(shè)定的最大值彭则，nofile表示所限制的資源是打開文件的最大數(shù)目鳍刷，65535就是限制的數(shù)量。

結(jié)論：把可打開的文件數(shù)量增加到65535贰剥，以保證可以打開足夠多的文件句柄倾剿。?

注意：這個(gè)文件的修改需要重啟系統(tǒng)才可以生效。

磁盤調(diào)度策略（/sys/block/devname/queue/scheduler）

noop anticipatory deadline [cfq]?

查看：cat /sys/block/sda/queue/scheduler

noop（電梯式調(diào)度策略）

? ? noop實(shí)現(xiàn)了一個(gè)FIFO隊(duì)列蚌成，它像電梯的工作方法一樣對I/O請求進(jìn)行組織前痘，當(dāng)有一個(gè)新的請求到來時(shí)，它將請求合并到最近的請求之后担忧，以此來保證請求同一介質(zhì)芹缔。

deadline（截止時(shí)間調(diào)度策略）?

對數(shù)據(jù)庫最好。

修改磁盤調(diào)度策略

echo deadline > /sys/block/sda/queue/scheduler

文件系統(tǒng)的選擇

windows：?

- FAT?

- NTFS

linux：

EXT3

EXT4

XFS（性能更高）

三瓶盛、存儲(chǔ)引擎

MySQL體系結(jié)構(gòu)

注意：存儲(chǔ)引擎是針對于表的而不是針對于庫的（一個(gè)庫中的不同表可以使用不同的存儲(chǔ)引擎）

MyISAM

MySql5.5之前版本默認(rèn)存儲(chǔ)引擎最欠。?

MyISAM存儲(chǔ)引擎表有MYD和MYI組成。

特性：

并發(fā)性與鎖級別：對讀寫混合的并發(fā)性并不會(huì)太好

表損壞修復(fù)

支持的索引類型：全文索引

支持壓縮：壓縮表只支持讀操作惩猫，不支持寫操作

適用場景：

非事務(wù)型應(yīng)用

只讀類應(yīng)用

空間類應(yīng)用

★Innodb

系統(tǒng)表空間 和 獨(dú)立表空間的選擇

比較：

系統(tǒng)表空間無法簡單的收縮文件大小

獨(dú)立表空間可以通過optimize table命令收縮系統(tǒng)文件

系統(tǒng)表空間會(huì)產(chǎn)生IO瓶頸

獨(dú)立表空間可以同時(shí)向多個(gè)文件刷新數(shù)據(jù)

建議：對Innodb使用獨(dú)立表空間

如何把原來存在于系統(tǒng)表空間中的表轉(zhuǎn)移到獨(dú)立表空間中芝硬？

1、使用mysqldump導(dǎo)出所有數(shù)據(jù)庫表數(shù)據(jù)

2轧房、停止MySql服務(wù)拌阴，修改參數(shù)，并刪除Innodb相關(guān)文件

3奶镶、重啟MySql服務(wù)迟赃，重建Innodb系統(tǒng)表空間

4陪拘、重新導(dǎo)入數(shù)據(jù)

Innodb存儲(chǔ)引擎的特性

事務(wù)性存儲(chǔ)引擎

完全支持事務(wù)的ACID特性

Redo Log 和 Undo Log

Innodb支持行級鎖（MyISAM支持表級鎖），行級鎖可以最大程度的支持并發(fā)纤壁，行級鎖是由存儲(chǔ)引擎層實(shí)現(xiàn)的

Innodb適用場景?

基本上只要不使用到Innodb不包含的功能左刽，基本上都使用Innodb作為默認(rèn)存儲(chǔ)引擎（例如MyISAM的空間應(yīng)用，全文索引酌媒。但實(shí)際上MySql5.7之后Innodb已經(jīng)支持了前面兩點(diǎn)）欠痴。