MySQL概述及性能調(diào)優(yōu)

一、基本架構(gòu)

1.服務(wù)層：處理客戶端和服務(wù)端的鏈接、安全驗(yàn)證

2.核心層：查詢分析，優(yōu)化，緩存，內(nèi)置函數(shù)   內(nèi)建的視圖/存儲(chǔ)過(guò)程/觸發(fā)器（是開(kāi)發(fā)數(shù)據(jù)庫(kù)和讀MySQL源碼最應(yīng)該關(guān)注的地方）

3.儲(chǔ)存引擎層：負(fù)責(zé)數(shù)據(jù)的存取，通過(guò)存儲(chǔ)引擎的API和存儲(chǔ)引擎通信，遮蔽了存儲(chǔ)引擎之間的差異

圖解：

二、選擇版本

MariaDB數(shù)據(jù)庫(kù)-->開(kāi)源，完全兼容MySQL，使用了XtraDB引擎，代替MySQL的InnoDB引擎

四個(gè)版本：

企業(yè)版：收費(fèi)

社區(qū)版：開(kāi)源免費(fèi) 用的人多

Percona Server：新特性多，不穩(wěn)定

MariaDB：國(guó)內(nèi)用的不多

三、配置文件詳解

位置：/etc/my.cnf

1.max_connections:MySQL允許的同時(shí)會(huì)話數(shù)  當(dāng)MySQL到了一個(gè)最大連接數(shù)上限的時(shí)候，其中一個(gè)連接將會(huì)保留作為管理員使用 Too many connections-->同時(shí)會(huì)話數(shù)設(shè)置小了，將其增大

2.max_connect_erros 最大錯(cuò)誤允許數(shù)，默認(rèn)是50  使用FLUSH HOSTS或者重啟服務(wù)，這樣報(bào)錯(cuò)的記錄就會(huì)消失（這個(gè)錯(cuò)誤不是指的刪除數(shù)據(jù)的錯(cuò)誤，而是連接本身的錯(cuò)誤，當(dāng)錯(cuò)誤的次數(shù)達(dá)到的時(shí)候就無(wú)法連接了，會(huì)阻塞這個(gè)連接）

3.key_buffer_size:關(guān)鍵詞緩沖區(qū)大小，用來(lái)緩存MyISAM索引塊，這個(gè)值決定了索引值處理速度，尤其是讀取索引處理速度

4.max_allowed_packet:設(shè)置最大包，限制了server接受數(shù)據(jù)塊的大小，避免超長(zhǎng)的sql執(zhí)行，當(dāng)服務(wù)器收到大于這個(gè)信息包的時(shí)候會(huì)發(fā)出信息包過(guò)大這個(gè)報(bào)錯(cuò)，關(guān)閉鏈接，節(jié)約資源（報(bào)錯(cuò)：丟失與MySQL服務(wù)器連接）

5.thread_cache_size:服務(wù)器線程緩存，可以重新利用保存在緩存中的線程數(shù)據(jù)，可以將線程中的緩存數(shù)據(jù)緩存下來(lái)，當(dāng)緩存中還有空間的時(shí)候，服務(wù)端/客戶端的線程斷開(kāi)，他將會(huì)存放在緩存當(dāng)中，當(dāng)客戶端重新連接的時(shí)候，從緩存中把鏈接取出來(lái)重新使用。如果緩存是空的或者新的請(qǐng)求的話，那么線程將會(huì)被重新創(chuàng)建，這樣是為了避免重復(fù)建立大量線程，減少資源的消耗

6.thread_concurrency:CPU核數(shù)x2，設(shè)置錯(cuò)誤后，MySQL不能很好利用多核處理器性能，不要瞎設(shè)！

7.sort_buffer_size:每個(gè)連接使用buffer分配的內(nèi)存大小，不是越大越好，高并發(fā)的時(shí)候，比如20W個(gè)連接，這個(gè)值設(shè)為1M，將消耗20Wx1M=200GB內(nèi)存左右，而一般單機(jī)才128G。

8.join_buffer_size:join表使用的緩存大小

9.query_cache_size:查詢緩存大小，mysql會(huì)將查詢的結(jié)果緩存下來(lái)，當(dāng)下次的時(shí)候就將其直接返回，緩存期間相關(guān)表必須沒(méi)有變更，否則失效，多寫(xiě)操作數(shù)據(jù)庫(kù)設(shè)置大了會(huì)影響寫(xiě)入效率（千萬(wàn)不能線上建索引）

10.read_buffer_size:MyISAM全表掃描時(shí)候的緩沖大小，而且無(wú)法通過(guò)添加索引來(lái)優(yōu)化全表掃描， 增大會(huì)優(yōu)化

11.read_rnd_buffer_size：從排序好的數(shù)據(jù)中讀取行時(shí)，行數(shù)據(jù)會(huì)從緩沖區(qū)讀取，這個(gè)值會(huì)提升order by性能。注意mysql會(huì)為每個(gè)客戶端申請(qǐng)這個(gè)緩沖區(qū)，并發(fā)過(guò)大，設(shè)置過(guò)大影響內(nèi)存開(kāi)銷。

12.myisam_sort_buffer_size:MyISAM表發(fā)生變化時(shí)，重新排序所需緩存大小

13.innodb_buffer_size:InnoDB使用緩存保存索引 原始數(shù)據(jù) 緩存大小，可以有效減少讀取數(shù)據(jù)所需的磁盤(pán)IO（通過(guò)多次試驗(yàn)得知對(duì)MySQL性能調(diào)優(yōu)innodb_buffer_size參數(shù)可以有效減少讀取數(shù)據(jù)的磁盤(pán)IO）

14.innodb_log_file_size:數(shù)據(jù)日志文件大小，大的值可以提高性能，但增加了恢復(fù)故障數(shù)據(jù)庫(kù)的時(shí)間（通過(guò)修改innodb_log_file_size可以提高性能，但是與此同時(shí)增加了恢復(fù)故障數(shù)據(jù)庫(kù)的時(shí)間）

15.innodb_log_buffer_size:日志文件緩存   增大該值可以提高性能，但增大了忽然宕機(jī)損失數(shù)據(jù)的風(fēng)險(xiǎn)（宕機(jī)時(shí)候緩存就沒(méi)了）

16.innodb_flush_log_at_trx_commit:執(zhí)行事務(wù)的時(shí)候，會(huì)往innodb存儲(chǔ)引擎 日志緩存插入事務(wù)日志（當(dāng)事務(wù)提交的時(shí)候必須將存儲(chǔ)引擎的緩存寫(xiě)入磁盤(pán)，也就是寫(xiě)數(shù)據(jù)前先寫(xiě)日志，這個(gè)叫做預(yù)寫(xiě)日志方式）當(dāng)這個(gè)參數(shù)設(shè)置為0時(shí)，表示每秒日志緩存寫(xiě)入文件，文件實(shí)時(shí)寫(xiě)入磁盤(pán)，當(dāng)設(shè)置為1時(shí)，緩存實(shí)時(shí)寫(xiě)入文件，文件實(shí)時(shí)寫(xiě)入磁盤(pán)（雙實(shí)時(shí)），當(dāng)為2的時(shí)候，緩存實(shí)時(shí)寫(xiě)入文件，每秒日志文件寫(xiě)入磁盤(pán)

17.innodb_lock_wait_timeout:被回滾前一個(gè)InnoDB事務(wù)應(yīng)該等待一個(gè)鎖被批準(zhǔn)多久（當(dāng)InnoDB自動(dòng)檢測(cè)這個(gè)事務(wù)是否死鎖并自動(dòng)回滾，如果使用locktables指定，或者事務(wù)中使用了InnoDB外的存儲(chǔ)引擎，那么這個(gè)死鎖就很可能發(fā)生了，而InnoDB無(wú)法檢測(cè)到死鎖發(fā)生，這個(gè)時(shí)候這個(gè)值有用）

四、軟件優(yōu)化

1.選合適的引擎

MySQL5.1默認(rèn)的是MyISAM引擎，MySQL5.5、5.6用的是InnoDB引擎。

MyISAM索引順序訪問(wèn)方法，優(yōu)點(diǎn)是支持全文索引，但非事務(wù)安全，不支持外鍵，是表級(jí)鎖。

有三個(gè)文件，可以恢復(fù)數(shù)據(jù)，F(xiàn)RM文件存放表結(jié)構(gòu)，MYD文件存放數(shù)據(jù)，MYI存放索引

InnoDB事務(wù)性存儲(chǔ)引擎，行鎖（但也不絕對(duì)，因?yàn)閳?zhí)行更新語(yǔ)句時(shí)無(wú)法確定范圍的時(shí)候也會(huì)鎖表，例如：update table set age=3 where name like“%jeff%”,也會(huì)鎖表），這個(gè)引擎可以回滾，可以崩潰恢復(fù)，也可以ACID事務(wù)控制  InnoDB表和索引放在一個(gè)表空間里面，表空間里面有多個(gè)文件

2.正確使用索引（書(shū)的目錄就是類似于索引的一種，是用來(lái)幫助讀者找到相應(yīng)的章節(jié)）

a.給合適的列建立索引  如where子句經(jīng)常需要給檢索建立索引，又或者連接子句，而不應(yīng)該是select選擇列表建立索引

b.索引值應(yīng)該不盡相同 對(duì)于唯一性的值，索引效果最好（對(duì)于在百度員工表里面，員工ID肯定是最好，因?yàn)閱T工號(hào)不重復(fù)，而如果對(duì)男女或者叫張三的話因?yàn)橛卸鄠€(gè)所以建立索引的效果不是最好的，當(dāng)有大量重復(fù)效果很差）

c.使用短索引 對(duì)字符類型的列進(jìn)行索引，有可能都要指定前綴長(zhǎng)度，例如：char（50） 但是前20個(gè)字符內(nèi)很多都是唯一的（時(shí)間戳），后面是一樣的，對(duì)于較小的索引，索引緩存一定，存的索引多，消耗磁盤(pán)IO少，能提高查找速度

d.最左前綴 數(shù)據(jù)如何取名字應(yīng)該是按照最左前綴的原則，這樣好設(shè)計(jì)索引，索引緩存的更多，查找速度就提升了。n列索引 多個(gè)索引能夠起n列索引的作用，可以最左列的值進(jìn)行匹配

e.like查詢的時(shí)候索引失效，盡量少用like（可以寫(xiě)一個(gè)帶like版本的和一個(gè)不帶like版本的，寫(xiě)帶like版本的是因?yàn)槟軌驅(qū)崿F(xiàn)功能，寫(xiě)不帶like版本的是因?yàn)樗饕龝?huì)失效，能夠提高并發(fā)性能，所以我們實(shí)際業(yè)務(wù)中應(yīng)該少用帶like版本的）對(duì)于百萬(wàn)、千萬(wàn)的數(shù)，要用like的話，盡量用一些開(kāi)源的東西-->用Sphinx開(kāi)源方案結(jié)合MySQL進(jìn)行全文檢索類似like的功能

f.不能濫用索引

1.索引占用空間，降低性能

2.更新數(shù)據(jù)，索引必須更新，索引會(huì)越來(lái)越多，會(huì)花費(fèi)時(shí)間，所以盡量不要在長(zhǎng)期不用的字段上建立索引

3.SQL語(yǔ)句執(zhí)行一個(gè)查詢語(yǔ)句進(jìn)行優(yōu)化時(shí)，可能不是最佳的索引，增加查詢時(shí)間

3.避免使用select *

1.返回結(jié)果多，降低查詢速度

2.返回過(guò)多的返回結(jié)果，增大服務(wù)器返回給App端的數(shù)據(jù)傳輸量。網(wǎng)絡(luò)傳輸速度慢，弱網(wǎng)絡(luò)環(huán)境下，會(huì)容易造成請(qǐng)求失效

4.字段盡量設(shè)置為NOT NULL

“”和NULL是兩回事

例如：服務(wù)端給后臺(tái)傳一個(gè)json字段{“name”:“”}{“hobby”:空Array}null占空間 安卓判斷“”還是NULL  在java和OC中的強(qiáng)類型，NULL和空值是不一樣的，會(huì)造成App閃退

五、硬件優(yōu)化

1.增加物理內(nèi)存，因?yàn)镸ySQL讀寫(xiě)數(shù)據(jù)最大的憑據(jù)是磁盤(pán)IO，可以提高磁盤(pán)IO

linux內(nèi)核 內(nèi)存開(kāi)緩存 存放數(shù)據(jù)的方式：

寫(xiě)文件 文件延遲寫(xiě)入機(jī)制，先把文件放到緩存中，達(dá)到一定程度寫(xiě)入文件

讀文件 讀文件到緩存中，下次需要相同文件時(shí)，從緩存中取，而不是從硬盤(pán)中取出

2.增加應(yīng)用緩存

a.本地緩存

數(shù)據(jù)放到服務(wù)器內(nèi)存或文件中

b.分布式緩存

Redis、memcache  讀寫(xiě)性能非常高QPS 每秒查詢請(qǐng)求數(shù)達(dá)到1W以上

數(shù)據(jù)持久化用Redis  數(shù)據(jù)不持久化 兩者都可以

3.固態(tài)硬盤(pán)SSD代替機(jī)械硬盤(pán)

a.日志和數(shù)據(jù)分來(lái)存儲(chǔ) 日志肯定順序讀寫(xiě)放在機(jī)械硬盤(pán) 數(shù)據(jù)隨機(jī)讀寫(xiě)放在SSD

b.調(diào)整參數(shù) innodb_flush_method=O_DIRECT  告訴操作系統(tǒng)禁用緩存fsync（異步）方式數(shù)據(jù)刷入機(jī)械硬盤(pán)innodb_io_capacity=10000 控制mysql一次刷新臟頁(yè)面的數(shù)量，因?yàn)槭褂肧SD之后IO能力增強(qiáng)，需要增大一次刷新臟頁(yè)的數(shù)量

4.SSD+SATA混合存儲(chǔ)

FlashCache是facebook的一個(gè)開(kāi)源技術(shù)，在文件系統(tǒng)和設(shè)備驅(qū)動(dòng)之間加了一層緩存 對(duì)熱數(shù)據(jù)緩存

六、架構(gòu)優(yōu)化

1.分表

a.水平拆分：數(shù)據(jù)分成多個(gè)表

b.垂直拆分：字段分成多個(gè)表

c.MyISAM MERGE存儲(chǔ)引擎， InnoDB用alter table將n個(gè)子表合成一個(gè)整表，但實(shí)際上是n個(gè)子表

2.讀寫(xiě)分離

讀是一些機(jī)器，寫(xiě)是一些機(jī)器，有二進(jìn)制文件的主從復(fù)制  還有延遲的解決方案

3.分庫(kù)

Mycat部署的工作流程

七、SQL慢查詢

是調(diào)參數(shù)

八、活用存儲(chǔ)結(jié)構(gòu)

我有一個(gè)內(nèi)容表，給我三個(gè)字段id user_id content, 當(dāng)我的字段要變得很多的時(shí)候，可以分成索引表和內(nèi)容表，索引表里面放字段，內(nèi)容表里面放數(shù)據(jù)，使用key-value鍵值對(duì)存放數(shù)據(jù)，這樣能夠使得存儲(chǔ)結(jié)構(gòu)靈活起來(lái)

九、故障排除案例

App服務(wù)商家，后臺(tái)數(shù)據(jù)庫(kù)load居高不下

解決：使用like查詢，在這個(gè)時(shí)候因?yàn)椴荒苡盟饕枰闅v上百萬(wàn)的數(shù)據(jù)，性能低下，這個(gè)時(shí)候使用Sphinx Coreseek開(kāi)源全文檢索