一鬓椭、Mysql的邏輯分層

Mysql分為：連接層、服務(wù)層娜饵、引擎層坡贺、存儲(chǔ)層。

當(dāng)客戶(hù)端向服務(wù)端發(fā)起操作請(qǐng)求的時(shí)候箱舞，執(zhí)行過(guò)程是這樣的：

1遍坟、客戶(hù)端端與Mysql服務(wù)端的連接層建立連接，根據(jù)請(qǐng)求類(lèi)型去選擇相應(yīng)的服務(wù)層的請(qǐng)求接口晴股。

image

二愿伴、SQL優(yōu)化

1、Insert的優(yōu)化

在執(zhí)行insert操作時(shí)經(jīng)常遇到插入多條數(shù)據(jù)的時(shí)候电湘，例如：

• 管理員在同時(shí)添加多名用戶(hù)的時(shí)候

image

• 在某種數(shù)據(jù)結(jié)構(gòu)比較復(fù)雜的情況下添加數(shù)據(jù)

image

在1對(duì)n的表結(jié)構(gòu)的情況下隔节，經(jīng)常會(huì)遇到這種插入多次子表的情況。那么程序開(kāi)發(fā)人員在開(kāi)發(fā)時(shí)候胡桨，首先想到的是利用for循環(huán)進(jìn)行插入子表數(shù)據(jù)：

第一種情況（合并插入）

例如：我想插入三條官帘，利用for循環(huán)在循環(huán)3次才能執(zhí)行，那么就需要這樣執(zhí)行：

insert into st(name,password) values('zhangsan','123456');

怎么才能改進(jìn)呢昧谊？mysql的sql有一個(gè)語(yǔ)法可以支持刽虹，如下：

insert into st(name,password) values('zhangsan','123456'),('lisi','123456'),('wangwu','123456');

只有三條可能看不出來(lái)，那么接下來(lái)做一個(gè)測(cè)試呢诬，復(fù)制了50次遍涌哲。

• 測(cè)試結(jié)果1：以單個(gè)插入的的方式，插入了50條數(shù)據(jù)尚镰，用了0.077s

image

測(cè)試結(jié)果2：插入了271條數(shù)據(jù)阀圾，用了0.077s

image

插入3241條用了0.044s

image

一個(gè)插入了50條數(shù)據(jù)，用了0.077s狗唉，一個(gè)插入了271條數(shù)據(jù)初烘，用了0.077s。

很明顯的對(duì)比分俯。

第二種情況（事務(wù)手動(dòng)提交）

開(kāi)啟事務(wù)肾筐，事務(wù)提交，改為手動(dòng)提交缸剪。

start TRANSACTION; #先開(kāi)啟事務(wù)

測(cè)試結(jié)果：開(kāi)啟事務(wù)后吗铐，插入50條數(shù)據(jù)用了0.040s，比沒(méi)開(kāi)啟事務(wù)插入數(shù)據(jù)快了將近一半杏节。

image

第三種情況（主鍵順序）

在插入大批量的數(shù)據(jù)時(shí)唬渗，建議歸類(lèi)典阵、有序的插入數(shù)據(jù)。

st(id key,name)

insert into st(name,id) values('zhangsana',10);

優(yōu)化后：（進(jìn)行排序镊逝，按主鍵的順序）

insert into st(name,id) values(2,'wangwu');

2壮啊、order by的優(yōu)化

環(huán)境準(zhǔn)備

• 準(zhǔn)備測(cè)試表

CREATE TABLE `user` (

• 準(zhǔn)備測(cè)試數(shù)據(jù)

INSERT INTO `user` VALUES ('1', 'zhangsan', 'zhangsan123', '30', '男', 'zhangsan@163.com');

• 建立索引

#給id與age建立索引

兩種排序方式

1）filesort排序

EXPLAIN select * from user ORDER BY age;

image

• 多字段排序

image

• 多字段升序或者降序，都是走的全表掃描

image

可以從上面的例子中看出撑蒜，都是Using filesort他巨，全部走了全表掃描

效率是比較低的。

2）index排序

通過(guò)using index排序

#在查詢(xún)的時(shí)候减江，只把加了索引的給查出來(lái)

image

• 如果想要其他字段也想走index排序的話(huà)染突，也需要給這個(gè)字段加上索引

image

• 給username加索引

image.gif

• 給username加索引后

3）多字段排序

• 一個(gè)升序一個(gè)降序

EXPLAIN select id,age,salary,username from user ORDER BY age asc,salary DESC;

image

總結(jié)：最好不要既有升序也有降序，效率會(huì)降低辈灼。

• 顛倒排序的位置

如果位置有變化了份企，也會(huì)影響效率。排序的位置巡莹，最好和索引的順序符合司志。

image

在優(yōu)化排序的相關(guān)sql時(shí)，盡量減少額外的字段排序降宅，通過(guò)索引直接返回有序的數(shù)據(jù)骂远。where條件和Order by 使用相同的索引并且Order By的順序和索引順序相同，并且Order by 的字段都是升序或者都是降序腰根。

Filesort優(yōu)化

1）兩次掃描算法

在MySQL4.1之前激才，使用該方式排序。首先根據(jù)條件取出排序字段和行指針信息额嘿，然后在排序區(qū)sort buffer中排序瘸恼，如果sort buffer不夠則在臨時(shí)表temporary table中存儲(chǔ)排序結(jié)果。完成排序之后册养，再根據(jù)行指針回表讀取記錄东帅，該操作可能會(huì)導(dǎo)致大量隨機(jī)I/O操作。

2）一次掃描算法

一次性取出滿(mǎn)足條件的所有字段球拦，然后在排序區(qū)sort buffer中排序后直接輸出結(jié)果集靠闭。排序時(shí)內(nèi)存開(kāi)銷(xiāo)較大，但是排序效率比兩次掃描算法高的多坎炼。

MySQL通過(guò)比較系統(tǒng)變量max_length_for_sort_data的大小和Qury語(yǔ)句取出的字段總大小愧膀，來(lái)判定是否符合哪種排序算法，如果max_length_for_sort_data更大点弯，則使用第二種優(yōu)化之后

三扇调、JOIN的用法

直接用代碼表示的話(huà)矿咕，不能很直觀的看到效果抢肛，為了方便狼钮，我使用圖片+SQL的形式來(lái)講解。

在實(shí)際的數(shù)據(jù)庫(kù)應(yīng)用中捡絮，我們經(jīng)常需要從多個(gè)數(shù)據(jù)表中讀取數(shù)據(jù)熬芜，這時(shí)我們就可以使用SQL語(yǔ)句中的連接（JOIN），在兩個(gè)或多個(gè)數(shù)據(jù)表中查詢(xún)數(shù)據(jù)福稳。

JOIN 按照功能可分為如下三類(lèi)：

• INNER JOIN（內(nèi)連接涎拉，或等值連接）：獲取兩個(gè)表中字段匹配關(guān)系的記錄；

• LEFT JOIN（左連接）：獲取左表中的所有記錄的圆，即使在右表沒(méi)有對(duì)應(yīng)匹配的記錄鼓拧；

• RIGHT JOIN（右連接）：與 LEFT JOIN 相反，用于獲取右表中的所有記錄越妈，即使左表沒(méi)有對(duì)應(yīng)匹配的記錄季俩。

有以下幾個(gè)表：

學(xué)生表(students)：student_id,student_name,sno,class_id; 學(xué)號(hào)，姓名梅掠，學(xué)號(hào)酌住，班級(jí)號(hào)

班級(jí)表(classes)：student_id,class_name,class_id; 學(xué)號(hào)，姓名阎抒，班級(jí)

image

四酪我、額外補(bǔ)充能量

• 利用存儲(chǔ)過(guò)程大批量插入數(shù)據(jù)

  1、創(chuàng)建存儲(chǔ)過(guò)程

DELIMITER inData

2且叁、查詢(xún)存儲(chǔ)過(guò)程

show create PROCEDURE insertData ;\G

3都哭、使用存儲(chǔ)過(guò)程

CALL insertData()

為什么要優(yōu)化？

隨著數(shù)據(jù)量的增大逞带， mysql服務(wù)性能差從而直接影響用戶(hù)體驗(yàn)质涛。

查詢(xún)時(shí)結(jié)果顯示的很慢等。

哪些方面可以?xún)?yōu)化掰担？

1汇陆、優(yōu)化硬件、操作系統(tǒng)

2带饱、優(yōu)化MySQL服務(wù)器

3毡代、優(yōu)化DB設(shè)計(jì)

4、優(yōu)化SQL語(yǔ)句

5勺疼、優(yōu)化應(yīng)用

1教寂、優(yōu)化硬件、操作系統(tǒng)

-CPU执庐，內(nèi)存酪耕，硬盤(pán)

Linux操作系統(tǒng)的內(nèi)核優(yōu)化

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

-網(wǎng)絡(luò)TCP連接

-加快資源回收效率

-增加資源限制

-改變磁盤(pán)調(diào)度策略

2、優(yōu)化MySQL服務(wù)器

最大連接數(shù)：

max_connections=2000

默認(rèn)：max_connections=151

指定MySQL可能的連接數(shù)量

指定MySQL可能的連接數(shù)量轨淌。當(dāng)MySQL主線(xiàn)程在很短的時(shí)間內(nèi)得到非常多的連接請(qǐng)求迂烁，該參數(shù)就起作用看尼，之后主線(xiàn)程花些時(shí)間（盡管很短）檢查連接并且啟動(dòng)一個(gè)新線(xiàn)程。

back_log參數(shù)的值指出在MySQL暫時(shí)停止響應(yīng)新請(qǐng)求之前的短時(shí)間內(nèi)多少個(gè)請(qǐng)求可以被存在堆棧中盟步。

back_log=1024

默認(rèn)：back_log=80

索引塊的緩沖區(qū)大小

key_buffer_size = 32M

索引塊的緩沖區(qū)大小藏斩，對(duì)MyISAM表性能影響最大的一個(gè)參數(shù).決定索引處理的速度，尤其是索引讀的速度却盘。默認(rèn)值是8M狰域，通過(guò)檢查狀態(tài)值Key_read_requests

和Key_reads，可以知道key_buffer_size設(shè)置是否合理

默認(rèn)：key_buffer_size=8M

MySQL執(zhí)行排序使用的緩沖大小

sort_buffer_size = 16M

是MySQL執(zhí)行排序使用的緩沖大小黄橘。如果想要增加ORDER BY的速度兆览，首先看是否可以讓MySQL使用索引而不是額外的排序階段。

如果不能塞关，可以嘗試增加sort_buffer_size變量的大小拓颓。

默認(rèn)：sort_buffer_size=256K

MYSQL讀入緩沖區(qū)大小

read_buffer_size = 64M

是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ū)大小提高其性能。

默認(rèn)：read_buffer_size=64K

Join操作緩存大小

join_buffer_size = 16M

應(yīng)用程序經(jīng)常會(huì)出現(xiàn)一些兩表（或多表）Join的操作需求廉羔，MySQL在完成某些 Join 需求的時(shí)候（all/index join）溉痢，為了減少參與Join的“被驅(qū)動(dòng)表”的

讀取次數(shù)以提高性能，需要使用到 Join Buffer 來(lái)協(xié)助完成 Join操作憋他。當(dāng) Join Buffer 太小孩饼，MySQL 不會(huì)將該 Buffer 存入磁盤(pán)文件，

而是先將Join Buffer中的結(jié)果集與需要 Join 的表進(jìn)行 Join 操作竹挡，

然后清空 Join Buffer 中的數(shù)據(jù)镀娶，繼續(xù)將剩余的結(jié)果集寫(xiě)入此 Buffer 中，如此往復(fù)揪罕。這勢(shì)必會(huì)造成被驅(qū)動(dòng)表需要被多次讀取梯码，成倍增加 IO 訪問(wèn)，降低效率好啰。

默認(rèn)：join_buffer_size=256K

MySQL的隨機(jī)讀緩沖區(qū)大小

read_rnd_buffer_size = 32M

是MySQL的隨機(jī)讀緩沖區(qū)大小轩娶。當(dāng)按任意順序讀取行時(shí)(例如，按照排序順序)框往，將分配一個(gè)隨機(jī)讀緩存區(qū)鳄抒。進(jìn)行排序查詢(xún)時(shí)，MySQL會(huì)首先掃描一遍該緩沖，以避免磁盤(pán)搜索许溅，

提高查詢(xún)速度瓤鼻，如果需要排序大量數(shù)據(jù)，可適當(dāng)調(diào)高該值闹司。但MySQL會(huì)為每個(gè)客戶(hù)連接發(fā)放該緩沖空間，所以應(yīng)盡量適當(dāng)設(shè)置該值沐飘，以避免內(nèi)存開(kāi)銷(xiāo)過(guò)大游桩。

默認(rèn)：read_rnd_buffer_size=256K

緩存排序索引大小

myisam_sort_buffer_size = 256M

當(dāng)對(duì)MyISAM表執(zhí)行repair table或創(chuàng)建索引時(shí)，用以緩存排序索引耐朴；設(shè)置太小時(shí)可能會(huì)遇到” myisam_sort_buffer_size is too small”

myisam_sort_buffer_size=102M

緩存空閑的線(xiàn)程以便不被銷(xiāo)毀

thread_cache_size = 384

thread_cahe_size線(xiàn)程池借卧，線(xiàn)程緩存。用來(lái)緩存空閑的線(xiàn)程筛峭，以至于不被銷(xiāo)毀铐刘，如果線(xiàn)程緩存在的空閑線(xiàn)程，需要重新建立新連接影晓，

則會(huì)優(yōu)先調(diào)用線(xiàn)程池中的緩存镰吵，很快就能響應(yīng)連接請(qǐng)求。每建立一個(gè)連接挂签，都需要一個(gè)線(xiàn)程與之匹配疤祭。

默認(rèn)：thread_cache_size=10

set global  max_connections=2000;#設(shè)置最大連接數(shù)

3、優(yōu)化DB設(shè)計(jì)

-參照范式進(jìn)行設(shè)計(jì)（1級(jí)范式）

1NF

包含分隔符類(lèi)字符的字符串?dāng)?shù)據(jù)饵婆。

名字尾端有數(shù)字的屬性勺馆。

沒(méi)有定義鍵或鍵定義不好的表。

2NF

多個(gè)屬性有同樣的前綴侨核。

重復(fù)的數(shù)據(jù)組草穆。

匯總的數(shù)據(jù)，所引用的數(shù)據(jù)在一個(gè)完全不同的實(shí)體中搓译。

BCNF- “每個(gè)鍵必須唯一標(biāo)識(shí)實(shí)體悲柱，每個(gè)非鍵熟悉必須描述實(shí)體。

4NF

三元關(guān)系（實(shí)體:實(shí)體:實(shí)體）些己。

潛伏的多值屬性诗祸。（如多個(gè)手機(jī)號(hào)。）

臨時(shí)數(shù)據(jù)或歷史值轴总。（需要將歷史數(shù)據(jù)的主體提出直颅，否則將存在大量冗余。）

-建立合適的索引

建索引的目的

加快查詢(xún)速度怀樟。
減少I(mǎi)/O操作功偿，通過(guò)索引的路徑來(lái)檢索數(shù)據(jù)，不是在磁盤(pán)中隨機(jī)檢索。
消除磁盤(pán)排序械荷，索引是排序的共耍，走完索引就排序完成

1）B-Tree 索引

B-Tree 索引是 MySQL 數(shù)據(jù)庫(kù)中使用最為頻繁的索引類(lèi)型

2）Hash 索引

Hash 索引結(jié)構(gòu)的特殊性，其檢索效率非常高吨瞎，索引的檢索可以一次定位痹兜，不像B-Tree索引需要從根節(jié)點(diǎn)到枝節(jié)點(diǎn)，最后才能訪問(wèn)到頁(yè)節(jié)點(diǎn)這樣多次的IO訪問(wèn)颤诀，所以Hash索引的查詢(xún)效率要遠(yuǎn)高于B-Tree索引字旭。

3）什么時(shí)候可以建索引

1）列無(wú)重復(fù)值，可以建索引：唯一索引和普通索引

2）聚集索引和非聚集索引都可以是唯一的崖叫。因此遗淳，只要列中的數(shù)據(jù)是唯一的，就可以在同一個(gè)表上創(chuàng)建一個(gè)唯一的聚集索引和多個(gè)唯一的非聚集索引心傀。

3）建了索引性能得到提高

4）區(qū)分度高的列可以建索引屈暗，比如表示男和女的列區(qū)分度就不高，就不能建索引

4）什么時(shí)候不可以建索引

1.頻繁更新的字段不適合建立索引

2.where條件中用不到的字段不適合建立索引

3.表數(shù)據(jù)可以確定比較少的不需要建索引

4.數(shù)據(jù)重復(fù)且發(fā)布比較均勻的的字段不適合建索引（唯一性太差的字段不適合建立索引）脂男，例如性別养叛，真假值

5.參與列計(jì)算的列不適合建索引，如select * from where amount+1>10

6.查詢(xún)返回的記錄數(shù)不適合建立索引

7.查詢(xún)的排序表記錄小于40%不適合建立索引

8.查詢(xún)非排序表的記錄小于 7%不適合建立索引

9.表的碎片較多（頻繁增加宰翅、刪除）不適合建立索引

4一铅、優(yōu)化架構(gòu)設(shè)計(jì)方案

• 加緩存

• DNS輪詢(xún)

通過(guò)在DNS-server上對(duì)一個(gè)域名設(shè)置多個(gè)ip解析，來(lái)擴(kuò)充web-server性能及實(shí)施負(fù)載均衡的技術(shù) 堕油。

• LVS（負(fù)載均衡）

Linux Virtual Server潘飘，使用集群技術(shù)，實(shí)現(xiàn)在linux操作系統(tǒng)層面的一個(gè)高性能掉缺、高可用卜录、負(fù)載均衡服務(wù)器 。

• nginx：一個(gè)高性能的web-server和實(shí)施反向代理的軟件

一個(gè)高性能的web-server和實(shí)施反向代理的軟件