第10章 - MySQL性能優(yōu)化

學(xué)習(xí)目標(biāo)：

• 了解MySQL優(yōu)化
• 了解常見(jiàn)的優(yōu)化思路
• 了解查詢優(yōu)化
• 了解索引優(yōu)化
• 了解存儲(chǔ)優(yōu)化
• 了解數(shù)據(jù)庫(kù)結(jié)構(gòu)優(yōu)化
• 了解查詢緩存等緩存優(yōu)化

1 優(yōu)化介紹

在進(jìn)行優(yōu)化講解之前，先請(qǐng)大家記住不要聽(tīng)信你看到的關(guān)于優(yōu)化的“絕對(duì)真理”，而應(yīng)該是在實(shí)際的業(yè)務(wù)場(chǎng)景下通過(guò)測(cè)試來(lái)驗(yàn)證你關(guān)于執(zhí)行計(jì)劃以及響應(yīng)時(shí)間的假設(shè)。本課程只是給大家提供一些優(yōu)化方面的方向和思路十兢，而具體業(yè)務(wù)場(chǎng)景的不同授翻，使用的MySQL服務(wù)版本不同涯穷，都會(huì)使得優(yōu)化方案的制定也不同襟衰。

1.1 MySQL介紹

MySQL憑借著出色的性能兔辅、低廉的成本驮瞧、豐富的資源氓扛，已經(jīng)成為絕大多數(shù)互聯(lián)網(wǎng)公司的首選關(guān)系型數(shù)據(jù)庫(kù)。可以看到Google采郎，F(xiàn)acebook千所，Twitter，百度蒜埋，新浪淫痰，騰訊，淘寶整份，網(wǎng)易待错，久游等絕大多數(shù)互聯(lián)網(wǎng)公司數(shù)據(jù)庫(kù)都是用的MySQL數(shù)據(jù)庫(kù)，甚至將其作為核心應(yīng)用的數(shù)據(jù)庫(kù)系統(tǒng)烈评。

雖然性能出色火俄，但所謂“好馬配好鞍”，如何能夠更好的使用它讲冠，已經(jīng)成為開(kāi)發(fā)工程師的必修課瓜客，我們經(jīng)常會(huì)從職位描述上看到諸如“精通MySQL”、“SQL語(yǔ)句優(yōu)化”沟启、“了解數(shù)據(jù)庫(kù)原理”等要求忆家。我們知道一般的應(yīng)用系統(tǒng)犹菇，讀寫比例在10:1左右德迹，而且插入操作和一般的更新操作很少出現(xiàn)性能問(wèn)題，遇到最多的揭芍，也是最容易出問(wèn)題的胳搞，還是一些復(fù)雜的查詢操作，所以查詢語(yǔ)句的優(yōu)化顯然是重中之重称杨。

我們將這里進(jìn)行一個(gè)較為全面的分析肌毅，讓大家了解到MySQL的性能到底與哪些地方有關(guān)，以便于讓大家尋找出其性能問(wèn)題的根本原因姑原，而盡可能清楚的知道該如何去優(yōu)化自己的數(shù)據(jù)庫(kù)悬而。

1.2 優(yōu)化要考慮的問(wèn)題

注意：優(yōu)化有風(fēng)險(xiǎn)，涉足需謹(jǐn)慎锭汛！

1.2.1 優(yōu)化可能帶來(lái)的問(wèn)題

• 優(yōu)化不總是對(duì)一個(gè)單純的環(huán)境進(jìn)行笨奠，還很可能是一個(gè)復(fù)雜的已投產(chǎn)的系統(tǒng)！
• 優(yōu)化手段有很大的風(fēng)險(xiǎn)唤殴，一定要意識(shí)到和預(yù)見(jiàn)到般婆！
• 任何的技術(shù)可以解決一個(gè)問(wèn)題，但必然存在帶來(lái)一個(gè)問(wèn)題的風(fēng)險(xiǎn)朵逝！
• 對(duì)于優(yōu)化來(lái)說(shuō)調(diào)優(yōu)而帶來(lái)的問(wèn)題,控制在可接受的范圍內(nèi)才是有成果蔚袍。
• 保持現(xiàn)狀或出現(xiàn)更差的情況都是失敗配名！

1.2.2 優(yōu)化的需求

• 穩(wěn)定性和業(yè)務(wù)可持續(xù)性,通常比性能更重要啤咽！
• 優(yōu)化不可避免涉及到變更晋辆，變更就有風(fēng)險(xiǎn)！
• 優(yōu)化使性能變好宇整，維持和變差是等概率事件栈拖！
• 優(yōu)化應(yīng)該是各部門協(xié)同，共同參與的工作没陡，任何單一部門都不能對(duì)數(shù)據(jù)庫(kù)進(jìn)行優(yōu)化涩哟！

所以優(yōu)化工作,是由業(yè)務(wù)需要驅(qū)使的！Ｅ涡贴彼！

1.2.3 優(yōu)化由誰(shuí)參與

在進(jìn)行數(shù)據(jù)庫(kù)優(yōu)化時(shí)，應(yīng)由數(shù)據(jù)庫(kù)管理員埃儿、業(yè)務(wù)部門代表器仗、應(yīng)用程序架構(gòu)師、應(yīng)用程序設(shè)計(jì)人員童番、應(yīng)用程序開(kāi)發(fā)人員精钮、硬件及系統(tǒng)管理員、存儲(chǔ)管理員等剃斧，業(yè)務(wù)相關(guān)人員共同參與轨香。

1.3 優(yōu)化的思路

1.3.1 優(yōu)化的方向

在數(shù)據(jù)庫(kù)優(yōu)化上有兩個(gè)主要方向：即安全與性能。

• 安全 ---> 數(shù)據(jù)安全性
• 性能 ---> 數(shù)據(jù)的高性能訪問(wèn)

本課程主要是在性能優(yōu)化方向進(jìn)行介紹

1.3.2 優(yōu)化的維度

image.png

從上圖中可以看出幼东，我們把數(shù)據(jù)庫(kù)優(yōu)化分為四個(gè)緯度：硬件臂容，系統(tǒng)配置，數(shù)據(jù)庫(kù)表結(jié)構(gòu)根蟹，SQL及索引

**硬件： **CPU脓杉、內(nèi)存、存儲(chǔ)简逮、網(wǎng)絡(luò)設(shè)備等

系統(tǒng)配置： 服務(wù)器系統(tǒng)球散、數(shù)據(jù)庫(kù)服務(wù)參數(shù)等

數(shù)據(jù)庫(kù)表結(jié)構(gòu)： 高可用、分庫(kù)分表散庶、讀寫分離蕉堰、存儲(chǔ)引擎、表設(shè)計(jì)等

Sql及索引： sql語(yǔ)句督赤、索引使用等

• 從優(yōu)化成本進(jìn)行考慮：硬件>系統(tǒng)配置>數(shù)據(jù)庫(kù)表結(jié)構(gòu)>SQL及索引
• 從優(yōu)化效果進(jìn)行考慮：硬件<系統(tǒng)配置<數(shù)據(jù)庫(kù)表結(jié)構(gòu)<SQL及索引

1.3.3 優(yōu)化的工具

檢查問(wèn)題常用工具

msyqladmin      #mysql客戶端嘁灯，可進(jìn)行管理操作  
mysqlshow       #功能強(qiáng)大的查看shell命令  
show [SESSION | GLOBAL] variables   #查看數(shù)據(jù)庫(kù)參數(shù)信息  
SHOW [SESSION | GLOBAL] STATUS      #查看數(shù)據(jù)庫(kù)的狀態(tài)信息  
SHOW ENGINE INNODB STATUS Innodb    #引擎的所有狀態(tài)  
information_schema  #獲取元數(shù)據(jù)的方法
SHOW PROCESSLIST    #查看當(dāng)前所有連接session狀態(tài)  
explain     #獲取查詢語(yǔ)句的執(zhí)行計(jì)劃
how index   #查看表的索引信息  
slow-log    #記錄慢查詢語(yǔ)句  
mysqldumpslow   #分析slowlog文件的 

不常用但好用的工具

zabbix      #監(jiān)控主機(jī)、系統(tǒng)躲舌、數(shù)據(jù)庫(kù)（部署zabbix監(jiān)控平臺(tái)）
mysqlslap   #分析慢日志
sysbench    #壓力測(cè)試工具
workbench   #管理丑婿、備份、監(jiān)控、分析羹奉、優(yōu)化工具（比較費(fèi)資源）
pt-query-digest #分析慢日志
mysql profiling #統(tǒng)計(jì)數(shù)據(jù)庫(kù)整體狀態(tài)工具
Performance Schema mysql    #性能狀態(tài)統(tǒng)計(jì)的數(shù)據(jù)

1.3.4 數(shù)據(jù)庫(kù)使用優(yōu)化思路

本課程盡可能的全面介紹數(shù)據(jù)庫(kù)的調(diào)優(yōu)思路秒旋，但是在多數(shù)時(shí)候，我們進(jìn)行調(diào)優(yōu)不需要進(jìn)行這么全面诀拭、大范圍的調(diào)優(yōu)迁筛，一般情況下，我們進(jìn)行數(shù)據(jù)庫(kù)層面的優(yōu)化就可以了耕挨，那我們?cè)撊绾握{(diào)優(yōu)的呢细卧？

應(yīng)急調(diào)優(yōu)的思路：

針對(duì)突然的業(yè)務(wù)辦理卡頓，無(wú)法進(jìn)行正常的業(yè)務(wù)處理筒占！需要立馬解決的場(chǎng)景贪庙！

1. show processlist（查看鏈接session狀態(tài)）

2. explain(分析查詢計(jì)劃)，show index from table（分析索引）

3. 通過(guò)執(zhí)行計(jì)劃判斷翰苫，索引問(wèn)題（有沒(méi)有止邮、合不合理）或者語(yǔ)句本身問(wèn)題

4. show status like '%lock%'; # 查詢鎖狀態(tài)

5. SESSION_ID; # 殺掉有問(wèn)題的session

常規(guī)調(diào)優(yōu)的思路：

針對(duì)業(yè)務(wù)周期性的卡頓，例如在每天10-11點(diǎn)業(yè)務(wù)特別慢奏窑，但是還能夠使用导披，過(guò)了這段時(shí)間就好了。

1. 查看slowlog埃唯，分析slowlog撩匕，分析出查詢慢的語(yǔ)句。
2. 按照一定優(yōu)先級(jí)筑凫，進(jìn)行一個(gè)一個(gè)的排查所有慢語(yǔ)句滑沧。
3. 分析top sql并村，進(jìn)行explain調(diào)試巍实，查看語(yǔ)句執(zhí)行時(shí)間。
4. 調(diào)整索引或語(yǔ)句本身哩牍。

2 查詢優(yōu)化

2.1 MySQL查詢流程

我們?cè)撊绾芜M(jìn)行sql優(yōu)化呢棚潦， 首先我們需要知道，sql優(yōu)化其實(shí)主要是解決查詢的優(yōu)化問(wèn)題膝昆，所以我們先從數(shù)據(jù)庫(kù)的查詢開(kāi)始入手丸边，下面這幅圖顯示了查詢的執(zhí)行路徑：

① 客戶端將查詢發(fā)送到服務(wù)器；

② 服務(wù)器檢查查詢緩存荚孵，如果找到了妹窖，就從緩存中返回結(jié)果，否則進(jìn)行下一步收叶。

③ 服務(wù)器解析骄呼，預(yù)處理。

④ 查詢優(yōu)化器優(yōu)化查詢

⑤ 生成執(zhí)行計(jì)劃，執(zhí)行引擎調(diào)用存儲(chǔ)引擎API執(zhí)行查詢

⑥服務(wù)器將結(jié)果發(fā)送回客戶端蜓萄。

image.png

查詢緩存 在解析一個(gè)查詢語(yǔ)句之前隅茎，如果查詢緩存是打開(kāi)的，那么MySQL會(huì)優(yōu)先檢查這個(gè)查詢是否命中查詢緩存中的數(shù)據(jù)嫉沽，如果命中緩存直接從緩存中拿到結(jié)果并返回給客戶端辟犀。這種情況下，查詢不會(huì)被解析绸硕，不用生成執(zhí)行計(jì)劃堂竟，不會(huì)被執(zhí)行。

語(yǔ)法解析和預(yù)處理器 MySQL通過(guò)關(guān)鍵字將SQL語(yǔ)句進(jìn)行解析玻佩，并生成一棵對(duì)應(yīng)的“解析樹(shù)”跃捣。MySQL解析器將使用MySQL語(yǔ)法規(guī)則驗(yàn)證和解析查詢。

查詢優(yōu)化器 語(yǔ)法書(shū)被校驗(yàn)合法后由優(yōu)化器轉(zhuǎn)成查詢計(jì)劃夺蛇，一條語(yǔ)句可以有很多種執(zhí)行方式疚漆，最后返回相同的結(jié)果。優(yōu)化器的作用就是找到這其中最好的執(zhí)行計(jì)劃刁赦。

查詢執(zhí)行引擎 在解析和優(yōu)化階段娶聘，MySQL將生成查詢對(duì)應(yīng)的執(zhí)行計(jì)劃，MySQL的查詢執(zhí)行引擎則根據(jù)這個(gè)執(zhí)行計(jì)劃來(lái)完成整個(gè)查詢甚脉。最常使用的也是比較最多的引擎是MyISAM引擎和InnoDB引擎丸升。mysql5.5開(kāi)始的默認(rèn)存儲(chǔ)引擎已經(jīng)變更為innodb了。

2.2 查詢優(yōu)化

前面的查詢流程分析牺氨，我們大概了解了MySQL是如何執(zhí)行的狡耻，其中涉及到的部分我們?cè)诤竺鏁?huì)一一道來(lái)。現(xiàn)在我們先從查詢優(yōu)化部分開(kāi)始猴凹。

sql是我們和數(shù)據(jù)庫(kù)交流最重要的部分夷狰，所以我們?cè)谡{(diào)優(yōu)的時(shí)候，需要花費(fèi)的大量時(shí)間就在sql調(diào)優(yōu)上面郊霎。常見(jiàn)的分析手段有慢查詢?nèi)罩菊油罚珽XPLAIN 分析查詢，通過(guò)定位分析性能的瓶頸书劝，才能更好的優(yōu)化數(shù)據(jù)庫(kù)系統(tǒng)的性能进倍。

2.2.1 慢查詢

• 慢查詢?nèi)罩鹃_(kāi)啟

在配置文件my.cnf或my.ini中在[mysqld]一行下面加入兩個(gè)配置參數(shù)

log-slow-queries=/data/mysqldata/slow-query.log
long_query_time=5

log-slow-queries參數(shù)為慢查詢?nèi)罩敬娣诺奈恢茫话氵@個(gè)目錄要有mysql的運(yùn)行帳號(hào)的可寫權(quán)限购对，一般都將這個(gè)目錄設(shè)置為mysql的數(shù)據(jù)存放目錄猾昆；

long_query_time=5中的5表示查詢超過(guò)五秒才記錄；

還可以在my.cnf或者my.ini中添加log-queries-not-using-indexes參數(shù)骡苞，表示記錄下沒(méi)有使用索引的查詢垂蜗。

• 慢查詢分析

我們可以通過(guò)打開(kāi)log文件查看得知哪些SQL執(zhí)行效率低下 坑赡，從日志中，可以發(fā)現(xiàn)查詢時(shí)間超過(guò)5 秒的SQL么抗，而小于5秒的沒(méi)有出現(xiàn)在此日志中毅否。

如果慢查詢?nèi)罩局杏涗泝?nèi)容很多，可以使用mysqldumpslow工具（MySQL客戶端安裝自帶）來(lái)對(duì)慢查詢?nèi)罩具M(jìn)行分類匯總蝇刀。mysqldumpslow對(duì)日志文件進(jìn)行了分類匯總螟加，顯示匯總后摘要結(jié)果。

進(jìn)入log的存放目錄吞琐，運(yùn)行：

[root@mysql_data]# mysqldumpslow slow-query.log
Reading mysql slow query log fromslow-query.log
Count: 2 Time=11.00s (22s) Lock=0.00s (0s)Rows=1.0 (2), root[root]@mysql
select count(N) from t_user; 

mysqldumpslow命令

/path/mysqldumpslow -s c -t 10/database/mysql/slow-query.log

這會(huì)輸出記錄次數(shù)最多的10條SQL語(yǔ)句捆探，其中：

-s, 是表示按照何種方式排序，c站粟、t黍图、l、r分別是按照記錄次數(shù)奴烙、時(shí)間助被、查詢時(shí)間、返回的記錄數(shù)來(lái)排序切诀，ac揩环、at、al幅虑、ar丰滑，表示相應(yīng)的倒敘

-t, 是top n的意思，即為返回前面多少條的數(shù)據(jù)倒庵；

-g, 后邊可以寫一個(gè)正則匹配模式褒墨，大小寫不敏感的；

例如：

/path/mysqldumpslow -s r -t 10/database/mysql/slow-log

得到返回記錄集最多的10個(gè)查詢擎宝。

/path/mysqldumpslow -s t -t 10 -g “l(fā)eftjoin” /database/mysql/slow-log

得到按照時(shí)間排序的前10條里面含有左連接的查詢語(yǔ)句郁妈。

使用mysqldumpslow命令可以非常明確的得到各種我們需要的查詢語(yǔ)句，對(duì)MySQL查詢語(yǔ)句的監(jiān)控认臊、分析圃庭、優(yōu)化是MySQL優(yōu)化非常重要的一步。開(kāi)啟慢查詢?nèi)罩竞笫纾捎谌罩居涗洸僮鳎谝欢ǔ潭壬蠒?huì)占用CPU資源影響mysql的性能拘央，但是可以階段性開(kāi)啟來(lái)定位性能瓶頸涂屁。

2.2.2 EXPLAIN

EXPLAIN可以幫助開(kāi)發(fā)人員分析SQL問(wèn)題，EXPLAIN顯示了MySQL如何使用使用SQL執(zhí)行計(jì)劃灰伟，可以幫助開(kāi)發(fā)人員寫出更優(yōu)化的查詢語(yǔ)句拆又。使用方法儒旬，在select語(yǔ)句前加上Explain就可以了：

EXPLAIN SELECT * FROM products

結(jié)果的列的說(shuō)明如下：

image.png

1) id

SELECT識(shí)別符。這是SELECT查詢序列號(hào)帖族。這個(gè)不重要

2) select_type

表示SELECT語(yǔ)句的類型栈源。

1. simple:簡(jiǎn)單select（不使用union或子查詢）。
2. primary:最外面的select竖般。
3. union:union中的第二個(gè)或后面的select語(yǔ)句甚垦。
4. dependent union:union中的第二個(gè)或后面的select語(yǔ)句，取決于外面的查詢涣雕。
5. union result:union的結(jié)果艰亮。
6. subquery:子查詢中的第一個(gè)select。
7. dependent subquery:子查詢中的第一個(gè)select挣郭，取決于外面的查詢迄埃。
8. derived:導(dǎo)出表的select（from子句的子查詢）。

3) table

顯示這查詢的數(shù)據(jù)是關(guān)于哪張表的兑障。

4) type

區(qū)間索引侄非，這是重要的列，顯示連接使用了何種類型流译。從最好到最差的連接類型為：

system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般來(lái)說(shuō)彩库，得保證查詢至少達(dá)到range級(jí)別,最好能達(dá)到ref。

1. system：表僅有一行先蒋，這是const類型的特列骇钦，平時(shí)不會(huì)出現(xiàn)，這個(gè)也可以忽略不計(jì)竞漾。
2. const：數(shù)據(jù)表最多只有一個(gè)匹配行眯搭，因?yàn)橹黄ヅ湟恍袛?shù)據(jù)，所以很快
3. eq_ref：mysql手冊(cè)是這樣說(shuō)的:"對(duì)于每個(gè)來(lái)自于前面的表的行組合业岁，從該表中讀取一行鳞仙。這可能是最好的聯(lián)接類型，除了const類型笔时。它用在一個(gè)索引的所有部分被聯(lián)接使用并且索引是UNIQUE或PRIMARY KEY"棍好。eq_ref可以用于使用=比較帶索引的列。
4. ref：查詢條件索引既不是UNIQUE也不是PRIMARY KEY的情況允耿。ref可用于=或<或>操作符的帶索引的列借笙。
5. ref_or_null：該聯(lián)接類型如同ref，但是添加了MySQL可以專門搜索包含NULL值的行较锡。在解決子查詢中經(jīng)常使用該聯(lián)接類型的優(yōu)化业稼。
6. index_merge：該聯(lián)接類型表示使用了索引合并優(yōu)化方法。在這種情況下蚂蕴，key列包含了使用的索引的清單低散，key_len包含了使用的索引的最長(zhǎng)的關(guān)鍵元素俯邓。
7. unique_subquery：該類型替換了下面形式的IN子查詢的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一個(gè)索引查找函數(shù),可以完全替換子查詢,效率更高。
8. index_subquery：該聯(lián)接類型類似于unique_subquery熔号』蓿可以替換IN子查詢,但只適合下列形式的子查詢中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)
9. range：只檢索給定范圍的行,使用一個(gè)索引來(lái)選擇行。
10. index：該聯(lián)接類型與ALL相同,除了只有索引樹(shù)被掃描引镊。這通常比ALL快,因?yàn)樗饕募ǔ１葦?shù)據(jù)文件小朦蕴。
11. ALL：對(duì)于每個(gè)來(lái)自于先前的表的行組合,進(jìn)行完整的表掃描。（性能最差）

5) possible_keys

指出MySQL能使用哪個(gè)索引在該表中找到行祠乃。如果是空的梦重，沒(méi)有相關(guān)的索引。這時(shí)要提高性能亮瓷，可通過(guò)檢驗(yàn)WHERE子句琴拧，看是否引用某些字段，或者檢查字段不是適合索引嘱支。

6) key

實(shí)際使用到的索引蚓胸。如果為NULL，則沒(méi)有使用索引除师。如果為primary的話沛膳，表示使用了主鍵。

7) key_len

最長(zhǎng)的索引寬度汛聚。如果鍵是NULL锹安，長(zhǎng)度就是NULL。在不損失精確性的情況下倚舀，長(zhǎng)度越短越好叹哭。

8) ref

顯示使用哪個(gè)列或常數(shù)與key一起從表中選擇行。

9) rows

顯示MySQL認(rèn)為它執(zhí)行查詢時(shí)必須檢查的行數(shù)痕貌。

10) Extra

執(zhí)行狀態(tài)說(shuō)明风罩，該列包含MySQL解決查詢的詳細(xì)信息

• Distinct:MySQL發(fā)現(xiàn)第1個(gè)匹配行后,停止為當(dāng)前的行組合搜索更多的行。
• Not exists:MySQL能夠?qū)Σ樵冞M(jìn)行LEFT JOIN優(yōu)化,發(fā)現(xiàn)1個(gè)匹配LEFT JOIN標(biāo)準(zhǔn)的行后,不再為前面的的行組合在該表內(nèi)檢查更多的行舵稠。
• range checked for each record (index map: #):MySQL沒(méi)有發(fā)現(xiàn)好的可以使用的索引,但發(fā)現(xiàn)如果來(lái)自前面的表的列值已知,可能部分索引可以使用超升。
• Using filesort:MySQL需要額外的一次傳遞,以找出如何按排序順序檢索行。
• Using index:從只使用索引樹(shù)中的信息而不需要進(jìn)一步搜索讀取實(shí)際的行來(lái)檢索表中的列信息哺徊。
• Using temporary:為了解決查詢,MySQL需要?jiǎng)?chuàng)建一個(gè)臨時(shí)表來(lái)容納結(jié)果室琢。
• Using where:WHERE 子句用于限制哪一個(gè)行匹配下一個(gè)表或發(fā)送到客戶。
• Using sort_union(...), Using union(...), Using intersect(...):這些函數(shù)說(shuō)明如何為index_merge聯(lián)接類型合并索引掃描唉工。
• Using index for group-by:類似于訪問(wèn)表的Using index方式,Using index for group-by表示MySQL發(fā)現(xiàn)了一個(gè)索引,可以用來(lái)查 詢GROUP BY或DISTINCT查詢的所有列,而不要額外搜索硬盤訪問(wèn)實(shí)際的表研乒。

3 索引優(yōu)化

3.1 索引的介紹

創(chuàng)建以下表：

CREATE TABLE `tb_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `name` varchar(20) DEFAULT NULL COMMENT '姓名',
  `number` int(11) DEFAULT NULL COMMENT '編號(hào)',
  PRIMARY KEY (`id`),
  KEY `number` (`number`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

執(zhí)行以下sql，批量添加10條數(shù)據(jù)：

drop procedure if exists tb_insert; 
CREATE PROCEDURE tb_insert()
BEGIN
DECLARE i INT;
SET i = 0;
START TRANSACTION;
WHILE i < 10 DO -- 10即插入10條數(shù)據(jù)
    INSERT INTO tb_table (`name`,`number`) VALUES (concat("張三",i),i);
    SET i = i+1;
END WHILE;
COMMIT;
END;
?
call tb_insert();

在表沒(méi)有添加索引和添加索引的時(shí)候淋硝，都執(zhí)行以下查詢：

SELECT * FROM tb_table WHERE number = 500000

然后再添加數(shù)據(jù)庫(kù)的數(shù)據(jù)雹熬，插入100萬(wàn)條，再次測(cè)試有索引和沒(méi)有索引的查詢語(yǔ)句谣膳。

通過(guò)上面的對(duì)比測(cè)試可以看出竿报，索引是快速搜索的關(guān)鍵。MySQL索引的建立對(duì)于MySQL的高效運(yùn)行是很重要的继谚。對(duì)于少量的數(shù)據(jù)烈菌，沒(méi)有合適的索引影響不是很大，但是花履，當(dāng)隨著數(shù)據(jù)量的增加芽世，性能會(huì)急劇下降祭犯。

索引的目的在于提高查詢效率喜命，大家可以回憶之前學(xué)習(xí)的全文檢索技術(shù)沟娱。類似使用字典纤控，如果沒(méi)有目錄（索引）咏删，那么我們要從字典的第一個(gè)字開(kāi)始查詢到最后一個(gè)字才能有結(jié)果厦滤，可能要把字典中所有的字看一遍才能找到要結(jié)果片任，而目錄（索引）則能夠讓我們快速的定位到這個(gè)字的位置逗概，從而找到我們要的結(jié)果夺克。

3.2 索引的類型

1. 主鍵索引 PRIMARY KEY

   它是一種特殊的唯一索引箕宙，不允許有空值。一般是在建表的時(shí)候同時(shí)創(chuàng)建主鍵索引铺纽。

PRIMARY KEY (`id`)

2.唯一索引 UNIQUE

唯一索引列的值必須唯一柬帕，但允許有空值。如果是組合索引狡门，則列值的組合必須唯一陷寝。可以在創(chuàng)建表的時(shí)候指定融撞，也可以修改表結(jié)構(gòu)盼铁。

UNIQUE KEY `num` (`number`) USING BTREE

3.普通索引 INDEX

這是最基本的索引，它沒(méi)有任何限制尝偎∪幕穑可以在創(chuàng)建表的時(shí)候指定，也可以修改表結(jié)構(gòu)

KEY `num` (`number`) USING BTREE

4.組合索引 INDEX

索引分單列索引和組合索引(聯(lián)合索引)致扯。單列索引肤寝，即一個(gè)索引只包含單個(gè)列，一個(gè)表可以有多個(gè)單列索引抖僵，但這不是組合索引鲤看。組合索引，即一個(gè)索引包含多個(gè)列耍群。

KEY `num` (`number`,`name`) USING BTREE

5.全文索引 FULLTEXT

全文索引（也稱全文檢索）是目前搜索引擎使用的一種關(guān)鍵技術(shù)义桂。它能夠利用分詞技術(shù)等多種算法智能分析出文本文字中關(guān)鍵字詞的頻率及重要性找筝，然后按照一定的算法規(guī)則智能地篩選出我們想要的搜索結(jié)果。

3.3 索引的存儲(chǔ)結(jié)構(gòu)

3.3.1 BTree索引

在前面的例子中我們看見(jiàn)有USING BTREE慷吊，這個(gè)是什么呢袖裕？這個(gè)就是MySQL所使用的索引方案，MySQL中普遍使用B+Tree做索引溉瓶，也就是BTREE急鳄。

特點(diǎn)：

• BTREE索引以B+樹(shù)的結(jié)構(gòu)存儲(chǔ)數(shù)據(jù)
• BTREE索引能夠加快數(shù)據(jù)的查詢速度
• BTREE索引更適合進(jìn)行行范圍查找

使用的場(chǎng)景：

1. 全值匹配的查詢，例如根據(jù)訂單號(hào)查詢 order_sn='98764322119900'
2. 聯(lián)合索引時(shí)會(huì)遵循最左前綴匹配的原則,即最左優(yōu)先
3. 匹配列前綴查詢堰酿，例如：order_sn like '9876%'
4. 匹配范圍值的查找疾宏，例如：order_sn > '98764322119900'
5. 只訪問(wèn)索引的查詢

3.3.2 哈希索引

Hash索引在MySQL中使用的并不是很多，目前主要是Memory存儲(chǔ)引擎使用触创，在Memory存儲(chǔ)引擎中將Hash索引作為默認(rèn)的索引類型坎藐。所謂Hash索引，實(shí)際上就是通過(guò)一定的Hash算法嗅榕，將需要索引的鍵值進(jìn)行Hash運(yùn)算顺饮，然后將得到的Hash值存入一個(gè)Hash表中。然后每次需要檢索的時(shí)候凌那，都會(huì)將檢索條件進(jìn)行相同算法的Hash運(yùn)算兼雄，然后再和Hash表中的Hash值進(jìn)行比較并得出相應(yīng)的信息。

特點(diǎn)：

• Hash索引僅僅只能滿足“=”,“IN”和“<=>”查詢帽蝶，不能使用范圍查詢赦肋；
• Hash索引無(wú)法被利用來(lái)避免數(shù)據(jù)的排序操作；
• Hash索引不能利用部分索引鍵查詢励稳；
• Hash索引在任何時(shí)候都不能避免表掃描佃乘；
• Hash索引遇到大量Hash值相等的情況后性能并不一定就會(huì)比B-Tree索引高；

3.3.2 Full-text全文索引

Full-text索引也就是我們常說(shuō)的全文索引驹尼，MySQL中僅有MyISAM和InnoDB存儲(chǔ)引擎支持趣避。

對(duì)于文本的大對(duì)象，或者較大的CHAR類型的數(shù)據(jù)新翎，如果使用普通索引程帕，那么匹配文本前幾個(gè)字符還是可行的，但是想要匹配文本中間的幾個(gè)單詞地啰，那么就要使用LIKE %word%來(lái)匹配愁拭，這樣需要很長(zhǎng)的時(shí)間來(lái)處理，響應(yīng)時(shí)間會(huì)大大增加亏吝，這種情況岭埠，就可使用時(shí)FULLTEXT索引了，在生成Full-text索引時(shí)，會(huì)為文本生成一份單詞的清單惜论，在索引時(shí)根據(jù)這個(gè)單詞的清單來(lái)索引许赃。

注意：

• 對(duì)于較大的數(shù)據(jù)集，把數(shù)據(jù)添加到一個(gè)沒(méi)有Full-text索引的表来涨，然后添加Full-text索引的速度比把數(shù)據(jù)添加到一個(gè)已經(jīng)有Full-text索引的表快图焰。
• 針對(duì)較大的數(shù)據(jù)启盛，生成全文索引非常的消耗時(shí)間和空間蹦掐。
• 5.6版本前的MySQL自帶的全文索引只能用于MyISAM存儲(chǔ)引擎，如果是其它數(shù)據(jù)引擎僵闯，那么全文索引不會(huì)生效卧抗。5.6版本和之后InnoDB存儲(chǔ)引擎開(kāi)始支持全文索引。
• 在MySQL中鳖粟，全文索引支隊(duì)英文有用社裆，目前對(duì)中文還不支持。5.7版本之后通過(guò)使用ngram插件開(kāi)始支持中文向图。
• 在MySQL中泳秀，如果檢索的字符串太短則無(wú)法檢索得到預(yù)期的結(jié)果，檢索的字符串長(zhǎng)度至少為4字節(jié)榄攀。

3.4 索引的使用

雖然索引能夠?yàn)椴檎規(guī)?lái)速度上的提升嗜傅，但是也會(huì)對(duì)性能有一些損失。

• 索引會(huì)增加寫操作的成本
• 太多的索引會(huì)增加查詢優(yōu)化器的選擇時(shí)間

當(dāng)創(chuàng)建索引帶來(lái)的好處多過(guò)于消耗的時(shí)候檩赢，才是最優(yōu)的選擇~

使用索引的場(chǎng)景

• 主鍵自動(dòng)建立唯一索引吕嘀；
• 經(jīng)常作為查詢條件在WHERE或者ORDER BY 語(yǔ)句中出現(xiàn)的列要建立索引；
• 作為排序的列要建立索引贞瞒；
• 查詢中與其他表關(guān)聯(lián)的字段偶房，外鍵關(guān)系建立索引
• 高并發(fā)條件下傾向建立組合索引；
• 用于聚合函數(shù)的列可以建立索引军浆，例如使用count(number)時(shí)棕洋，number列就要建立索引

不使用索引的場(chǎng)景

• 有大量重復(fù)的列不單獨(dú)建立索引
• 表記錄太少不要建立索引，因?yàn)闆](méi)有太大作用乒融。
• 不會(huì)作為查詢的列不要建立索引

4 存儲(chǔ)優(yōu)化

MySQL中索引是在存儲(chǔ)引擎層實(shí)現(xiàn)的掰盘，這里我們會(huì)講解存儲(chǔ)引擎。

執(zhí)行查詢引擎的命令show engines簇抵，可以看到MySQL支持的存儲(chǔ)引擎結(jié)果如下：

image.png

mysql支持存儲(chǔ)引擎有好幾種庆杜，咱們這里主要討論一下常用的Innodb，MyISAM存儲(chǔ)引擎碟摆。

4.1 存儲(chǔ)引擎介紹

4.1.1 InnoDB存儲(chǔ)引擎

特點(diǎn)：

1. InnoDB存儲(chǔ)引擎提供了具有提交晃财、回滾和崩潰恢復(fù)能力的事務(wù)安全。相比較MyISAM存儲(chǔ)引擎，InnoDB寫的處理效率差一點(diǎn)并且會(huì)占用更多的磁盤空間保留數(shù)據(jù)和索引断盛。

2. 提供了對(duì)數(shù)據(jù)庫(kù)事務(wù)ACID（原子性Atomicity罗洗、一致性Consistency、隔離性Isolation钢猛、持久性Durability）的支持伙菜，實(shí)現(xiàn)了SQL標(biāo)準(zhǔn)的四種隔離級(jí)別。

3. 設(shè)計(jì)目標(biāo)就是處理大容量的數(shù)據(jù)庫(kù)系統(tǒng)命迈，MySQL運(yùn)行時(shí)InnoDB會(huì)在內(nèi)存中建立緩沖池贩绕，用于緩沖數(shù)據(jù)和索引。

4. 執(zhí)行“select count(*) from table”語(yǔ)句時(shí)需要掃描全表壶愤，因?yàn)槭褂胕nnodb引擎的表不會(huì)保存表的具體行數(shù)淑倾，所以需要掃描整個(gè)表才能計(jì)算多少行。

5. InnoDB引擎是行鎖征椒，粒度更小娇哆，所以寫操作不會(huì)鎖定全表，在并發(fā)較高時(shí)勃救，使用InnoDB會(huì)提升效率碍讨。即存在大量UPDATE/INSERT操作時(shí)，效率較高蒙秒。

6. InnoDB清空數(shù)據(jù)量大的表時(shí)勃黍，是非常緩慢，這是因?yàn)镮nnoDB必須處理表中的每一行税肪，根據(jù)InnoDB的事務(wù)設(shè)計(jì)原則溉躲，首先需要把“刪除動(dòng)作”寫入“事務(wù)日志”，然后寫入實(shí)際的表益兄。所以锻梳，清空大表的時(shí)候，最好直接drop table然后重建净捅。即InnoDB一行一行刪除疑枯，不會(huì)重建表。

使用場(chǎng)景：

1. 經(jīng)常UPDETE/INSERT的表蛔六，使用處理多并發(fā)的寫請(qǐng)求

2. 支持事務(wù)荆永，必選InnoDB。

3. 可以從災(zāi)難中恢復(fù)（日志+事務(wù)回滾）

4. 外鍵約束国章、列屬性AUTO_INCREMENT支持

4.1.2 MyISAM存儲(chǔ)引擎

特點(diǎn)：

1. MyISAM不支持事務(wù)具钥，不支持外鍵，SELECT/INSERT為主的應(yīng)用可以使用該引擎液兽。

2. 每個(gè)MyISAM在存儲(chǔ)成3個(gè)文件骂删，擴(kuò)展名分別是：

1) frm：存儲(chǔ)表定義（表結(jié)構(gòu)等信息）

2) MYD(MYData)，存儲(chǔ)數(shù)據(jù)

3) MYI(MYIndex)，存儲(chǔ)索引

3. 不同MyISAM表的索引文件和數(shù)據(jù)文件可以放置到不同的路徑下宁玫。

4. MyISAM類型的表提供修復(fù)的工具粗恢，可以用CHECK TABLE語(yǔ)句來(lái)檢查MyISAM表健康，并用REPAIR TABLE語(yǔ)句修復(fù)一個(gè)損壞的MyISAM表欧瘪。

5. 在MySQL5.6以前眷射，只有MyISAM支持Full-text全文索引

使用場(chǎng)景：

1. 經(jīng)常SELECT/INSERT的表，插入不頻繁佛掖，查詢非常頻繁
2. 不支持事務(wù)
3. 做很多count 的計(jì)算妖碉。
   select count(*) from tb;緩存了表的總記錄數(shù)
   select count(c) from tb;c這個(gè)列不能有null

4.1.3 MyISAM和Innodb區(qū)別

InnoDB和MyISAM是許多人在使用MySQL時(shí)最常用的兩個(gè)存儲(chǔ)引擎，這兩個(gè)存儲(chǔ)引擎各有優(yōu)劣苦囱，視具體應(yīng)用而定嗅绸。基本的差別為：MyISAM類型不支持事務(wù)處理撕彤，而InnoDB類型支持。MyISAM類型強(qiáng)調(diào)的是性能猛拴，其執(zhí)行速度比InnoDB類型更快羹铅，而InnoDB提供事務(wù)支持已經(jīng)外部鍵等高級(jí)數(shù)據(jù)庫(kù)功能。

具體實(shí)現(xiàn)的差別：

• MyISAM是非事務(wù)安全型的愉昆，而InnoDB是事務(wù)安全型的职员。

• MyISAM鎖的粒度是表級(jí)，而InnoDB支持行級(jí)鎖定跛溉。

• MyISAM不支持外鍵焊切，而InnoDB支持外鍵

• MyISAM相對(duì)簡(jiǎn)單，所以在效率上要優(yōu)于InnoDB芳室，小型應(yīng)用可以考慮使用MyISAM专肪。

• InnoDB表比MyISAM表更安全。

4.2 存儲(chǔ)優(yōu)化

4.2.1 禁用索引

對(duì)于使用索引的表堪侯，插入記錄時(shí)嚎尤，MySQL會(huì)對(duì)插入的記錄建立索引。如果插入大量數(shù)據(jù)伍宦，建立索引會(huì)降低插入數(shù)據(jù)速度芽死。為了解決這個(gè)問(wèn)題，可以在批量插入數(shù)據(jù)之前禁用索引次洼，數(shù)據(jù)插入完成后再開(kāi)啟索引关贵。

禁用索引的語(yǔ)句： ALTER TABLE table_name DISABLE KEYS
開(kāi)啟索引語(yǔ)句： ALTER TABLE table_name ENABLE KEYS

MyISAM對(duì)于空表批量插入數(shù)據(jù)，則不需要進(jìn)行操作卖毁，因?yàn)镸yISAM引擎的表是在導(dǎo)入數(shù)據(jù)后才建立索引揖曾。

4.2.2 禁用唯一性檢查

唯一性校驗(yàn)會(huì)降低插入記錄的速度，可以在插入記錄之前禁用唯一性檢查，插入數(shù)據(jù)完成后再開(kāi)啟翩肌。

禁用唯一性檢查的語(yǔ)句：SET UNIQUE_CHECKS = 0;
開(kāi)啟唯一性檢查的語(yǔ)句：SET UNIQUE_CHECKS = 1;

4.2.3 禁用外鍵檢查

插入數(shù)據(jù)之前執(zhí)行禁止對(duì)外鍵的檢查模暗，數(shù)據(jù)插入完成后再恢復(fù)，可以提供插入速度念祭。

禁用：SET foreign_key_checks = 0;
開(kāi)啟：SET foreign_key_checks = 1;

4.2.4批量插入數(shù)據(jù)

插入數(shù)據(jù)時(shí)兑宇，可以使用一條INSERT語(yǔ)句插入一條數(shù)據(jù)，也可以插入多條數(shù)據(jù)粱坤。
一個(gè)sql語(yǔ)句插入一條數(shù)據(jù)：

image.png

一條語(yǔ)句插入多條數(shù)據(jù)：

image.png

4.2.5禁止自動(dòng)提交

插入數(shù)據(jù)之前執(zhí)行禁止事務(wù)的自動(dòng)提交隶糕，數(shù)據(jù)插入完成后再恢復(fù)，可以提高插入速度站玄。

禁用：SET autocommit = 0;
開(kāi)啟：SET autocommit = 1;

5 數(shù)據(jù)庫(kù)結(jié)構(gòu)優(yōu)化

5.1 優(yōu)化表結(jié)構(gòu)

• 盡量將表字段定義為NOT NULL約束枚驻，這時(shí)由于在MySQL中含有空值的列很難進(jìn)行查詢優(yōu)化，NULL值會(huì)使索引以及索引的統(tǒng)計(jì)信息變得很復(fù)雜株旷。
• 對(duì)于只包含特定類型的字段再登，可以使用enum、set 等數(shù)據(jù)類型晾剖。
• 數(shù)值型字段的比較比字符串的比較效率高得多锉矢，字段類型盡量使用最小、最簡(jiǎn)單的數(shù)據(jù)類型齿尽。例如IP地址可以使用int類型沽损。
• 盡量使用TINYINT、SMALLINT循头、MEDIUM_INT作為整數(shù)類型而非INT绵估，如果非負(fù)則加上UNSIGNED。但對(duì)整數(shù)類型指定寬度卡骂，比如INT(11)国裳，沒(méi)有任何用，因?yàn)橹付ǖ念愋蜆?biāo)識(shí)范圍已經(jīng)確定偿警。
• VARCHAR的長(zhǎng)度只分配真正需要的空間
• 盡量使用TIMESTAMP而非DATETIME躏救，但TIMESTAMP只能表示1970 - 2038年，比DATETIME表示的范圍小得多螟蒸，而且TIMESTAMP的值因時(shí)區(qū)不同而不同盒使。
• 單表不要有太多字段，建議在20以內(nèi)
• 合理的加入冗余字段可以提高查詢速度七嫌。

5.2 表拆分

5.2.1 垂直拆分

垂直拆分按照字段進(jìn)行拆分少办，其實(shí)就是把組成一行的多個(gè)列分開(kāi)放到不同的表中，這些表具有不同的結(jié)構(gòu)诵原，拆分后的表具有更少的列英妓。例如用戶表中的一些字段可能經(jīng)常訪問(wèn)挽放，可以把這些字段放進(jìn)一張表里。另外一些不經(jīng)常使用的信息就可以放進(jìn)另外一張表里蔓纠。

插入的時(shí)候使用事務(wù)辑畦，也可以保證兩表的數(shù)據(jù)一致。缺點(diǎn)也很明顯腿倚，由于拆分出來(lái)的兩張表存在一對(duì)一的關(guān)系纯出，需要使用冗余字段，而且需要join操作敷燎。但是我們可以在使用的時(shí)候可以分別取兩次暂筝，這樣的來(lái)說(shuō)既可以避免join操作，又可以提高效率硬贯。

5.2.2 水平拆分

水平拆分按照行進(jìn)行拆分焕襟，常見(jiàn)的就是分庫(kù)分表。以用戶表為例饭豹，可以取用戶ID鸵赖，然后對(duì)ID取10的余數(shù)，將用戶均勻的分配進(jìn)這 0-9這10個(gè)表中墨状。查找的時(shí)候也按照這種規(guī)則卫漫，又快又方便。

有些表業(yè)務(wù)關(guān)聯(lián)比較強(qiáng)肾砂，那么可以使用按時(shí)間劃分的。例如每天的數(shù)據(jù)量很大宏悦，需要每天新建一張表镐确。這種業(yè)務(wù)類型就是需要高速插入，但是對(duì)于查詢的效率不太關(guān)心饼煞。表越大源葫，插入數(shù)據(jù)所需要索引維護(hù)的時(shí)間也就越長(zhǎng)。

5.3 表分區(qū)

分區(qū)適用于例如日志記錄砖瞧，查詢少息堂。一般用于后臺(tái)的數(shù)據(jù)報(bào)表分析。對(duì)于這些數(shù)據(jù)匯總需求块促，需要很多日志表去做數(shù)據(jù)聚合荣堰，我們能夠容忍1s到2s的延遲，只要數(shù)據(jù)準(zhǔn)確能夠滿足需求就可以竭翠。

MySQL主要支持4種模式的分區(qū)：range分區(qū)振坚、list預(yù)定義列表分區(qū)，hash 分區(qū)斋扰，key鍵值分區(qū)渡八。

錄入使用key鍵值分區(qū)：

CREATE TABLE `test2` (
  `id` int(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(100) DEFAULT NULL COMMENT '名稱',
  `state` int(1) DEFAULT NULL COMMENT '狀態(tài)',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY KEY (id)
PARTITIONS 10;

5.4 讀寫分離

大型網(wǎng)站會(huì)有大量的并發(fā)訪問(wèn)啃洋，如果還是傳統(tǒng)的數(shù)據(jù)存儲(chǔ)方案，只是靠一臺(tái)服務(wù)器處理屎鳍，如此多的數(shù)據(jù)庫(kù)連接宏娄、讀寫操作，數(shù)據(jù)庫(kù)必然會(huì)崩潰逮壁，數(shù)據(jù)丟失的話孵坚，后果更是不堪設(shè)想。這時(shí)候貌踏，我們需要考慮如何降低單臺(tái)服務(wù)器的使用壓力十饥，提升整個(gè)數(shù)據(jù)庫(kù)服務(wù)的承載能力。

我們發(fā)現(xiàn)一般情況對(duì)數(shù)據(jù)庫(kù)而言都是“讀多寫少”祖乳，也就說(shuō)對(duì)數(shù)據(jù)庫(kù)讀取數(shù)據(jù)的壓力比較大逗堵，這樣分析可以采用數(shù)據(jù)庫(kù)集群的方案。其中一個(gè)是主庫(kù)眷昆，負(fù)責(zé)寫入數(shù)據(jù)蜒秤，我們稱為寫庫(kù)；其它都是從庫(kù)亚斋，負(fù)責(zé)讀取數(shù)據(jù)作媚，我們稱為讀庫(kù)。這樣可以緩解一臺(tái)服務(wù)器的訪問(wèn)壓力帅刊。

MySql自帶主從復(fù)制功能纸泡，我們可以使用主從復(fù)制的主庫(kù)作為寫庫(kù)，從庫(kù)和主庫(kù)進(jìn)行數(shù)據(jù)同步赖瞒，那么可以使用多個(gè)從庫(kù)作為讀庫(kù)女揭，已完成讀寫分離的效果。

5.5 數(shù)據(jù)庫(kù)集群

如果訪問(wèn)量非常大栏饮，雖然使用讀寫分離能夠緩解壓力吧兔，但是一旦寫操作一臺(tái)服務(wù)器都不能承受了，這個(gè)時(shí)候我們就需要考慮使用多臺(tái)服務(wù)器實(shí)現(xiàn)寫操作袍嬉。

例如可以使用MyCat搭建MySql集群境蔼，對(duì)ID求3的余數(shù)，這樣可以把數(shù)據(jù)分別存放到3臺(tái)不同的服務(wù)器上伺通，由MyCat負(fù)責(zé)維護(hù)集群節(jié)點(diǎn)的使用箍土。

6 硬件優(yōu)化

服務(wù)器硬件的性能瓶頸，直接決定MySQL數(shù)據(jù)庫(kù)的運(yùn)行速度和效率泵殴。

可以從以下幾個(gè)方面考慮：

6.1 內(nèi)存

足夠大的內(nèi)存涮帘，是提高M(jìn)ySQL數(shù)據(jù)庫(kù)性能的方法之一。內(nèi)存的IO比硬盤快的多笑诅，可以增加系統(tǒng)的緩沖區(qū)容量调缨，使數(shù)據(jù)在內(nèi)存停留的時(shí)間更長(zhǎng)疮鲫，以減少磁盤的IO。服務(wù)器內(nèi)存建議不要小于2GB弦叶，推薦使用4GB以上的物理內(nèi)存俊犯。

6.2 磁盤

MySQL每秒鐘都在進(jìn)行大量、復(fù)雜的查詢操作伤哺，對(duì)磁盤的讀寫量可想而知燕侠。所以，通常認(rèn)為磁盤I/O是制約MySQL性能的最大因素之一立莉，對(duì)于日均訪問(wèn)量在100萬(wàn)PV以上的系統(tǒng)绢彤，由于磁盤I/O的制約，MySQL的性能會(huì)非常低下 考慮以下幾種解決方案：

• 使用SSD或者PCIe SSD設(shè)備蜓耻，至少獲得數(shù)百倍甚至萬(wàn)倍的IOPS提升茫舶；
• 購(gòu)置陣列卡，可明顯提升IOPS
• 盡可能選用RAID-10刹淌，而非RAID-5
• 使用機(jī)械盤的話饶氏，盡可能選擇高轉(zhuǎn)速的，例如選用15000RPM有勾，而不是7200RPM的盤

6.3 CPU

CPU僅僅只能決定運(yùn)算速度疹启，及時(shí)是運(yùn)算速度都還取決于與內(nèi)存之間的總線帶寬以及內(nèi)存本身的速度。但是一般情況下蔼卡，我們都需要選擇計(jì)算速度較快的CPU喊崖。

關(guān)閉節(jié)能模式。操作系統(tǒng)和CPU硬件配合雇逞，系統(tǒng)不繁忙的時(shí)候贷祈，為了節(jié)約電能和降低溫度，它會(huì)將CPU降頻喝峦。這對(duì)環(huán)保人士和抵制地球變暖來(lái)說(shuō)是一個(gè)福音，但是對(duì)MySQL來(lái)說(shuō)呜达，可能是一個(gè)災(zāi)難谣蠢。為了保證MySQL能夠充分利用CPU的資源，建議設(shè)置CPU為最大性能模式查近。

6.4 網(wǎng)絡(luò)

應(yīng)該盡可能選擇網(wǎng)絡(luò)延時(shí)低眉踱，吞吐量高的設(shè)備。

• 網(wǎng)絡(luò)延時(shí)：不同的網(wǎng)絡(luò)設(shè)備其延時(shí)會(huì)有差異霜威，延時(shí)自然是越小越好谈喳。
• 吞吐量：對(duì)于數(shù)據(jù)庫(kù)集群來(lái)說(shuō)，各個(gè)節(jié)點(diǎn)之間的網(wǎng)絡(luò)吞吐量可能直接決定集群的處理能力戈泼。

7 緩存優(yōu)化

7.1 查詢緩存

query_cache_size：作用于整個(gè) MySQL婿禽，主要用來(lái)緩存MySQL中的ResultSet赏僧，也就是一條SQL語(yǔ)句執(zhí)行的結(jié)果集，所以僅僅只能針對(duì)select語(yǔ)句扭倾。查詢緩存從MySQL 5.7.20開(kāi)始已被棄用淀零，并在MySQL 8.0中被刪除。

當(dāng)我們打開(kāi)了 Query Cache功能膛壹，MySQL在接受到一條select語(yǔ)句的請(qǐng)求后驾中，如果該語(yǔ)句滿足Query Cache的要求，MySQL會(huì)直接根據(jù)預(yù)先設(shè)定好的HASH算法將接受到的select語(yǔ)句以字符串方式進(jìn)行hash模聋，然后到Query Cache中直接查找是否已經(jīng)緩存肩民。如果已經(jīng)在緩存中，該select請(qǐng)求就會(huì)直接將數(shù)據(jù)返回链方，從而省略了后面所有的步驟(如SQL語(yǔ)句的解析持痰，優(yōu)化器優(yōu)化以及向存儲(chǔ)引擎請(qǐng)求數(shù)據(jù)等)，極大的提高性能侄柔。

當(dāng)然共啃，Query Cache也有一個(gè)致命的缺陷，那就是當(dāng)某個(gè)表的數(shù)據(jù)有任何任何變化暂题，都會(huì)導(dǎo)致所有引用了該表的select語(yǔ)句在Query Cache中的緩存數(shù)據(jù)失效移剪。所以，當(dāng)我們的數(shù)據(jù)變化非常頻繁的情況下薪者，使用Query Cache可能會(huì)得不償失纵苛。

如果緩存命中率非常高的話，有測(cè)試表明在極端情況下可以提高效率238%言津，而在糟糕時(shí)攻人，QC會(huì)降低系統(tǒng)13%的處理能力。

通過(guò)以下命令查看緩存相關(guān)變量

show variables like '%query_cache%';

• have_query_cache：表示此版本mysql是否支持緩存
• query_cache_limit ：緩存最大值
• query_cache_size：緩存大小
• query_cache_type：off 表示不緩存悬槽，on表示緩存所有結(jié)果怀吻。

7.2全局緩存

數(shù)據(jù)庫(kù)屬于IO密集型的應(yīng)用程序，其主職責(zé)就是數(shù)據(jù)的管理及存儲(chǔ)工作初婆。而我們知道蓬坡，從內(nèi)存中讀取一個(gè)數(shù)據(jù)庫(kù)的時(shí)間是微秒級(jí)別，而從一塊普通硬盤上讀取一個(gè) IO是在毫秒級(jí)別磅叛，二者相差3個(gè)數(shù)量級(jí)屑咳。所以，要優(yōu)化數(shù)據(jù)庫(kù)弊琴，首先第一步需要優(yōu)化的就是IO兆龙，盡可能將磁盤IO轉(zhuǎn)化為內(nèi)存IO,也就是使用緩存

啟動(dòng)MySQL時(shí)就要分配并且總是存在的全局緩存，可以在MySQL的my.conf或者my.ini文件的[mysqld]組中配置敲董。查詢緩存屬于全局緩存紫皇。

目前有：

key_buffer_size(默認(rèn)值：402653184,即384M)慰安、

innodb_buffer_pool_size(默認(rèn)值：134217728即：128M)、

innodb_additional_mem_pool_size（默認(rèn)值：8388608即：8M）坝橡、

innodb_log_buffer_size(默認(rèn)值：8388608即：8M)泻帮、

query_cache_size(默認(rèn)值：33554432即：32M)

1） key_buffer_size

用于索引塊的緩沖區(qū)大小，增加它可得到更好處理的索引(對(duì)所有讀和多重寫)计寇，對(duì)MyISAM表性能影響最大的一個(gè)參數(shù)锣杂。如果你使它太大，系統(tǒng)將開(kāi)始換頁(yè)并且真的變慢了番宁。

嚴(yán)格說(shuō)是它決定了數(shù)據(jù)庫(kù)索引處理的速度元莫，尤其是索引讀的速度。對(duì)于內(nèi)存在4GB左右的服務(wù)器該參數(shù)可設(shè)置為256M或384M.

2） innodb_buffer_pool_size

主要針對(duì)InnoDB表性能影響最大的一個(gè)參數(shù)蝶押。功能與Key_buffer_size一樣踱蠢。InnoDB占用的內(nèi)存，除innodb_buffer_pool_size用于存儲(chǔ)頁(yè)面緩存數(shù)據(jù)外棋电，另外正常情況下還有大約8%的開(kāi)銷茎截，主要用在每個(gè)緩存頁(yè)幀的描述、adaptive hash等數(shù)據(jù)結(jié)構(gòu)赶盔，如果不是安全關(guān)閉企锌，啟動(dòng)時(shí)還要恢復(fù)的話，還要另開(kāi)大約12%的內(nèi)存用于恢復(fù)于未，兩者相加就有差不多21%的開(kāi)銷撕攒。

3） innodb_additional_mem_pool_size

設(shè)置了InnoDB存儲(chǔ)引擎用來(lái)存放數(shù)據(jù)字典信息以及一些內(nèi)部數(shù)據(jù)結(jié)構(gòu)的內(nèi)存空間大小，所以當(dāng)我們一個(gè)MySQL Instance中的數(shù)據(jù)庫(kù)對(duì)象非常多的時(shí)候烘浦，是需要適當(dāng)調(diào)整該參數(shù)的大小以確保所有數(shù)據(jù)都能存放在內(nèi)存中提高訪問(wèn)效率的抖坪。

4） innodb_log_buffer_size

這是InnoDB存儲(chǔ)引擎的事務(wù)日志所使用的緩沖區(qū)。類似于Binlog Buffer闷叉。InnoDB在寫事務(wù)日志的時(shí)候擦俐，為了提高性能，也是先將信息寫入Innofb Log Buffer中握侧，當(dāng)滿足innodb_flush_log_trx_commit參數(shù)所設(shè)置的相應(yīng)條件(或者日志緩沖區(qū)寫滿)之后捌肴，才會(huì)將日志寫到文件(或者同步到磁盤)中∨河剑可以通過(guò)innodb_log_buffer_size 參數(shù)設(shè)置其可以使用的最大內(nèi)存空間。

InnoDB 將日志寫入日志磁盤文件前的緩沖大小秽五。理想值為 1M 至 8M孽查。大的日志緩沖允許事務(wù)運(yùn)行時(shí)不需要將日志保存入磁盤而只到事務(wù)被提交(commit)。因此坦喘，如果有大的事務(wù)處理盲再，設(shè)置大的日志緩沖可以減少磁盤I/O西设。這個(gè)參數(shù)實(shí)際上還和另外的flush參數(shù)相關(guān)。一般來(lái)說(shuō)不建議超過(guò)32MB答朋。

7.3局部緩存

除了全局緩沖贷揽，MySql還會(huì)為每個(gè)連接發(fā)放連接緩沖。個(gè)連接到MySQL服務(wù)器的線程都需要有自己的緩沖梦碗。大概需要立刻分配256K禽绪，甚至在線程空閑時(shí)，它們使用默認(rèn)的線程堆棧洪规，網(wǎng)絡(luò)緩存等印屁。事務(wù)開(kāi)始之后，則需要增加更多的空間斩例。運(yùn)行較小的查詢可能僅給指定的線程增加少量的內(nèi)存消耗雄人，然而如果對(duì)數(shù)據(jù)表做復(fù)雜的操作例如掃描、排序或者需要臨時(shí)表念赶，則需分配大約read_buffer_size础钠，

sort_buffer_size，read_rnd_buffer_size叉谜，tmp_table_size大小的內(nèi)存空間. 不過(guò)它們只是在需要的時(shí)候才分配旗吁，并且在那些操作做完之后就釋放了。

1） read_buffer_size

是MySql讀入緩沖區(qū)大小正罢。對(duì)表進(jìn)行順序掃描的請(qǐng)求將分配一個(gè)讀入緩沖區(qū)阵漏，MySql會(huì)為它分配一段內(nèi)存緩沖區(qū)。read_buffer_size變量控制這一緩沖區(qū)的大小翻具。如果對(duì)表的順序掃描請(qǐng)求非常頻繁履怯，并且你認(rèn)為頻繁掃描進(jìn)行得太慢，可以通過(guò)增加該變量值以及內(nèi)存緩沖區(qū)大小提高其性能.

2） sort_buffer_size

是MySql執(zhí)行排序使用的緩沖大小裆泳。如果想要增加ORDER BY的速度叹洲，首先看是否可以讓MySQL使用索引而不是額外的排序階段。如果不能工禾，可以嘗試增加sort_buffer_size變量的大小

3） read_rnd_buffer_size

是MySql的隨機(jī)讀緩沖區(qū)大小运提。當(dāng)按任意順序讀取行時(shí)(例如，按照排序順序)闻葵，將分配一個(gè)隨機(jī)讀緩存區(qū)民泵。進(jìn)行排序查詢時(shí)，MySql會(huì)首先掃描一遍該緩沖槽畔，以避免磁盤搜索栈妆，提高查詢速度，如果需要排序大量數(shù)據(jù)，可適當(dāng)調(diào)高該值鳞尔。但MySql會(huì)為每個(gè)客戶連接發(fā)放該緩沖空間嬉橙，所以應(yīng)盡量適當(dāng)設(shè)置該值，以避免內(nèi)存開(kāi)銷過(guò)大寥假。

4） tmp_table_size

是MySql的heap （堆積）表緩沖大小市框。所有聯(lián)合在一個(gè)DML指令內(nèi)完成，并且大多數(shù)聯(lián)合甚至可以不用臨時(shí)表即可以完成糕韧。大多數(shù)臨時(shí)表是基于內(nèi)存的(HEAP)表杆查。具有大的記錄長(zhǎng)度的臨時(shí)表 (所有列的長(zhǎng)度的和)或包含BLOB列的表存儲(chǔ)在硬盤上页滚。

如果某個(gè)內(nèi)部heap（堆積）表大小超過(guò)tmp_table_size例嘱，MySQL可以根據(jù)需要自動(dòng)將內(nèi)存中的heap表改為基于硬盤的MyISAM表卵沉。還可以通過(guò)設(shè)置tmp_table_size選項(xiàng)來(lái)增加臨時(shí)表的大小。也就是說(shuō)乒疏，如果調(diào)高該值额衙，MySql同時(shí)將增加heap表的大小，可達(dá)到提高聯(lián)接查詢速度的效果怕吴。

5） record_buffer:

record_buffer每個(gè)進(jìn)行一個(gè)順序掃描的線程為其掃描的每張表分配這個(gè)大小的一個(gè)緩沖區(qū)窍侧。如果你做很多順序掃描，你可能想要增加該值转绷。

7.4 其它緩存

1） table_cache

TABLE_CACHE(5.1.3及以后版本又名TABLE_OPEN_CACHE)伟件，table_cache指定表高速緩存的大小。每當(dāng)MySQL訪問(wèn)一個(gè)表時(shí)议经，如果在表緩沖區(qū)中還有空間斧账，該表就被打開(kāi)并放入其中，這樣可以更快地訪問(wèn)表內(nèi)容煞肾。

不能盲目地把table_cache設(shè)置成很大的值咧织。如果設(shè)置得太高，可能會(huì)造成文件描述符不足籍救，從而造成性能不穩(wěn)定或者連接失敗习绢。

2） thread_cache_size

服務(wù)器線程緩存，默認(rèn)的thread_cache_size=8蝙昙，,這個(gè)值表示可以重新利用保存在緩存中線程的數(shù)量,當(dāng)斷開(kāi)連接時(shí)如果緩存中還有空間,那么客戶端的線程將被放到緩存中,如果線程重新被請(qǐng)求闪萄，那么請(qǐng)求將從緩存中讀取,如果緩存中是空的或者是新的請(qǐng)求，那么這個(gè)線程將被重新創(chuàng)建,如果有很多新的線程奇颠，

增加這個(gè)值可以改善系統(tǒng)性能.通過(guò)比較Connections 和 Threads_created 狀態(tài)的變量败去，可以看到這個(gè)變量的作用。

8 服務(wù)器優(yōu)化

8.1 MySQL參數(shù)

通過(guò)優(yōu)化MySQL的參數(shù)可以提高資源利用率烈拒，從而達(dá)到提高M(jìn)ySQL服務(wù)器性能的目的为迈。MySQL的配置參數(shù)都在my.conf或者my.ini文件的[mysqld]組中三椿，常用的參數(shù)如下：

1） back_log

在MySQL暫時(shí)停止回答新請(qǐng)求之前的短時(shí)間內(nèi)多少個(gè)請(qǐng)求可以被存在堆棧中（每個(gè)連接256kb，占用：125M）葫辐。也就是說(shuō)，如果MySql的連接數(shù)據(jù)達(dá)到max_connections時(shí)伴郁，新來(lái)的請(qǐng)求將會(huì)被存在堆棧中耿战，以等待某一連接釋放資源，該堆棧的數(shù)量即back_log焊傅，如果等待連接的數(shù)量超過(guò)back_log剂陡，將不被授予連接資源。

2） wait_timeout

當(dāng)MySQL連接閑置超過(guò)一定時(shí)間后將會(huì)被強(qiáng)行關(guān)閉狐胎。MySQL默認(rèn)的wait-timeout值為8個(gè)小時(shí)鸭栖。

設(shè)置這個(gè)值是非常有意義的，比如你的網(wǎng)站有大量的MySQL鏈接請(qǐng)求（每個(gè)MySQL連接都是要內(nèi)存資源開(kāi)銷的）握巢，由于你的程序的原因有大量的連接請(qǐng)求空閑啥事也不干晕鹊，白白占用內(nèi)存資源，或者導(dǎo)致MySQL超過(guò)最大連接數(shù)從來(lái)無(wú)法新建連接導(dǎo)致“Too many connections”的錯(cuò)誤暴浦。在設(shè)置之前你可以查看一下你的MYSQL的狀態(tài)（可用showprocesslist)溅话，如果經(jīng)常發(fā)現(xiàn)MYSQL中有大量的Sleep進(jìn)程，則需要修改wait-timeout值了歌焦。

3） max_connections

是指MySql的最大連接數(shù)飞几，如果服務(wù)器的并發(fā)連接請(qǐng)求量比較大，建議調(diào)高此值独撇，以增加并行連接數(shù)量屑墨，當(dāng)然這建立在機(jī)器能支撐的情況下，因?yàn)槿绻B接數(shù)越多纷铣，介于MySql會(huì)為每個(gè)連接提供連接緩沖區(qū)卵史，就會(huì)開(kāi)銷越多的內(nèi)存，所以要適當(dāng)調(diào)整該值关炼，不能盲目提高設(shè)值程腹。

MySQL服務(wù)器允許的最大連接數(shù)16384

4） max_user_connections

是指每個(gè)數(shù)據(jù)庫(kù)用戶的最大連接針對(duì)某一個(gè)賬號(hào)的所有客戶端并行連接到MYSQL服務(wù)的最大并行連接數(shù)。簡(jiǎn)單說(shuō)是指同一個(gè)賬號(hào)能夠同時(shí)連接到mysql服務(wù)的最大連接數(shù)儒拂。設(shè)置為0表示不限制寸潦。

5） thread_concurrency

的值的正確與否, 對(duì)mysql的性能影響很大, 在多個(gè)cpu(或多核)的情況下，錯(cuò)誤設(shè)置了thread_concurrency的值, 會(huì)導(dǎo)致mysql不能充分利用多cpu(或多核), 出現(xiàn)同一時(shí)刻只能一個(gè)cpu(或核)在工作的情況社痛。thread_concurrency應(yīng)設(shè)為CPU核數(shù)的2倍见转。

6） skip-name-resolve

禁止MySQL對(duì)外部連接進(jìn)行DNS解析，使用這一選項(xiàng)可以消除MySQL進(jìn)行DNS解析的時(shí)間蒜哀。但需要注意斩箫，如果開(kāi)啟該選項(xiàng)，則所有遠(yuǎn)程主機(jī)連接授權(quán)都要使用IP地址方式，否則MySQL將無(wú)法正常處理連接請(qǐng)求乘客！

7） default-storage-engine

default-storage-engine=InnoDB(設(shè)置InnoDB類型狐血，另外還可以設(shè)置MyISAM類型)設(shè)置創(chuàng)建數(shù)據(jù)庫(kù)及表默認(rèn)存儲(chǔ)類型

8.2 Linux系統(tǒng)優(yōu)化

一般情況，我們都會(huì)使用Linux來(lái)進(jìn)行MySQL的安裝和部署易核，Linux系統(tǒng)在使用的時(shí)候匈织，也需要進(jìn)行相關(guān)的配置，以提高M(jìn)ySQL的使用性能牡直，這里列舉以下幾點(diǎn)：

• 避免使用Swap交換分區(qū)缀匕，因?yàn)榻粨Q時(shí)是從硬盤讀取的，速度很慢碰逸。
• 將操作系統(tǒng)和數(shù)據(jù)分區(qū)分開(kāi)乡小，不僅僅是邏輯上，還包括物理上饵史，因?yàn)椴僮飨到y(tǒng)的讀寫會(huì)影響數(shù)據(jù)庫(kù)的性能满钟。
• 把MySQL臨時(shí)空間和復(fù)制日志與數(shù)據(jù)放到不同的分區(qū)，數(shù)據(jù)庫(kù)后臺(tái)從磁盤進(jìn)行讀寫時(shí)會(huì)影響數(shù)據(jù)庫(kù)的性能约急。
• 避免使用軟件磁盤陣列零远。
• 在Linux中設(shè)置swappiness的值為0，因?yàn)樵跀?shù)據(jù)庫(kù)服務(wù)器中不需要緩存文件厌蔽。
• 使用 noatime 和 nodirtime 掛載文件系統(tǒng)牵辣，因?yàn)椴恍枰獙?duì)數(shù)據(jù)庫(kù)文件修改時(shí)間。
• 使用 XFS 文件系統(tǒng)奴饮，一種比ext3更快纬向、更小的文件系統(tǒng)。
• 調(diào)整 XFS 文件系統(tǒng)日志和緩沖變量 – 為了最高性能標(biāo)準(zhǔn)戴卜。
• 使用64位的操作系統(tǒng)逾条，這會(huì)支持更大的內(nèi)存。
• 刪除服務(wù)器上未使用的安裝包和守護(hù)進(jìn)程投剥，節(jié)省系統(tǒng)的資源占用师脂。
• 把使用MySQL的host和你的MySQL host放到一個(gè)hosts文件中。