Mysql调优你不知道这几点，就太可惜了
轉(zhuǎn)載自??Mysql調(diào)優(yōu)你不知道這幾點，就太可惜了
一、Mysql的邏輯分層
Mysql分為：連接層、服務層、引擎層、存儲層。
當客戶端向服務端發(fā)起操作請求的時候，執(zhí)行過程是這樣的：
1、客戶端端與Mysql服務端的連接層建立連接，根據(jù)請求類型去選擇相應的服務層的請求接口。
二、SQL優(yōu)化
1、Insert的優(yōu)化
在執(zhí)行insert操作時經(jīng)常遇到插入多條數(shù)據(jù)的時候，例如：
-
管理員在同時添加多名用戶的時候
-
在某種數(shù)據(jù)結構比較復雜的情況下添加數(shù)據(jù)
在1對n的表結構的情況下，經(jīng)常會遇到這種插入多次子表的情況。那么程序開發(fā)人員在開發(fā)時候，首先想到的是利用for循環(huán)進行插入子表數(shù)據(jù)：
第一種情況（合并插入）
例如：我想插入三條，利用for循環(huán)在循環(huán)3次才能執(zhí)行，那么就需要這樣執(zhí)行：
insert into st(name,password) values('zhangsan','123456'); insert into st(name,password) values('lisi','123456'); insert into st(name,password) values('wangwu','123456');怎么才能改進呢？mysql的sql有一個語法可以支持，如下：
insert into st(name,password) values('zhangsan','123456'),('lisi','123456'),('wangwu','123456');只有三條可能看不出來，那么接下來做一個測試，復制了50次遍。
-
測試結果1：以單個插入的的方式，插入了50條數(shù)據(jù)，用了0.077s
測試結果2：插入了271條數(shù)據(jù)，用了0.077s
插入3241條用了0.044s
一個插入了50條數(shù)據(jù)，用了0.077s，一個插入了271條數(shù)據(jù)，用了0.077s。
很明顯的對比。
第二種情況（事務手動提交）
開啟事務，事務提交，改為手動提交。
start TRANSACTION; #先開啟事務insert into st(name,password) values('zhangsan','123456');insert into st(name,password) values('lisi','123456');insert into st(name,password) values('wangwu','123456'); COMMIT; #最后提交?
測試結果：開啟事務后，插入50條數(shù)據(jù)用了0.040s，比沒開啟事務插入數(shù)據(jù)快了將近一半。
第三種情況（主鍵順序）
在插入大批量的數(shù)據(jù)時，建議歸類、有序的插入數(shù)據(jù)。
st(id key,name)
insert into st(name,id) values('zhangsana',10); insert into st(name,id) values('lisi',3); insert into st(name,id) values('wangwu',2); insert into st(name,id) values('wangwub',8); insert into st(name,id) values('wangwua',34);優(yōu)化后：（進行排序，按主鍵的順序）
insert into st(name,id) values(2,'wangwu'); insert into st(name,id) values(3,'lisi'); insert into st(name,id) values(8,'wangwub'); insert into st(name,id) values(10,'zhangsana'); insert into st(name,id) values(34,'wangwua');?
2、order by的優(yōu)化
環(huán)境準備
-
準備測試表
-
準備測試數(shù)據(jù)
- 建立索引
兩種排序方式
1）filesort排序
EXPLAIN select * from user ORDER BY age;-
多字段排序
-
多字段升序或者降序，都是走的全表掃描
可以從上面的例子中看出，都是Using filesort，全部走了全表掃描
效率是比較低的。
2）index排序
通過using index排序???????
#在查詢的時候，只把加了索引的給查出來 EXPLAIN select id,age,salary from user ORDER BY age DESC,salary DESC;-
如果想要其他字段也想走index排序的話，也需要給這個字段加上索引
-
給username加索引
-
給username加索引后
3）多字段排序
-
一個升序一個降序
總結：最好不要既有升序也有降序，效率會降低。
-
顛倒排序的位置
如果位置有變化了，也會影響效率。排序的位置，最好和索引的順序符合。
在優(yōu)化排序的相關sql時，盡量減少額外的字段排序，通過索引直接返回有序的數(shù)據(jù)。where條件和Order by 使用相同的索引并且Order By的順序和索引順序相同，并且Order by 的字段都是升序或者都是降序。
Filesort優(yōu)化
1）兩次掃描算法
在MySQL4.1之前，使用該方式排序。首先根據(jù)條件取出排序字段和行指針信息，然后在排序區(qū)sort buffer中排序，如果sort buffer不夠則在臨時表temporary table中存儲排序結果。完成排序之后，再根據(jù)行指針回表讀取記錄，該操作可能會導致大量隨機I/O操作。
2）一次掃描算法
一次性取出滿足條件的所有字段，然后在排序區(qū)sort buffer中排序后直接輸出結果集。排序時內(nèi)存開銷較大，但是排序效率比兩次掃描算法高的多。
?
MySQL通過比較系統(tǒng)變量max_length_for_sort_data的大小和Qury語句取出的字段總大小，來判定是否符合哪種排序算法，如果max_length_for_sort_data更大，則使用第二種優(yōu)化之后
?
三、JOIN的用法
直接用代碼表示的話，不能很直觀的看到效果，為了方便，我使用圖片+SQL的形式來講解。
在實際的數(shù)據(jù)庫應用中，我們經(jīng)常需要從多個數(shù)據(jù)表中讀取數(shù)據(jù)，這時我們就可以使用SQL語句中的連接（JOIN），在兩個或多個數(shù)據(jù)表中查詢數(shù)據(jù)。
JOIN 按照功能可分為如下三類：
-
INNER JOIN（內(nèi)連接，或等值連接）：獲取兩個表中字段匹配關系的記錄；
-
LEFT JOIN（左連接）：獲取左表中的所有記錄，即使在右表沒有對應匹配的記錄；
-
RIGHT JOIN（右連接）：與 LEFT JOIN 相反，用于獲取右表中的所有記錄，即使左表沒有對應匹配的記錄。
有以下幾個表：
學生表(students)：student_id,student_name,sno,class_id; 學號，姓名，學號，班級號
班級表(classes)：student_id,class_name,class_id; 學號，姓名，班級
四、額外補充能量
-
利用存儲過程大批量插入數(shù)據(jù)
1、創(chuàng)建存儲過程
2、查詢存儲過程
show create PROCEDURE insertData ;\G3、使用存儲過程
CALL insertData()為什么要優(yōu)化？
隨著數(shù)據(jù)量的增大， mysql服務性能差從而直接影響用戶體驗。
查詢時結果顯示的很慢等。
哪些方面可以優(yōu)化？
1、優(yōu)化硬件、操作系統(tǒng)
2、優(yōu)化MySQL服務器
3、優(yōu)化DB設計
4、優(yōu)化SQL語句
5、優(yōu)化應用
1、優(yōu)化硬件、操作系統(tǒng)
-CPU，內(nèi)存，硬盤
Linux操作系統(tǒng)的內(nèi)核優(yōu)化
內(nèi)核相關參數(shù)(/etc/sysctl.conf)
-網(wǎng)絡TCP連接
-加快資源回收效率
-增加資源限制
-改變磁盤調(diào)度策略
?
2、優(yōu)化MySQL服務器
最大連接數(shù)：
max_connections=2000
默認：max_connections=151
?
指定MySQL可能的連接數(shù)量
#指定MySQL可能的連接數(shù)量。當MySQL主線程在很短的時間內(nèi)得到非常多的連接請求，該參數(shù)就起作用，之后主線程花些時間（盡管很短）檢查連接并且啟動一個新線程。
#back_log參數(shù)的值指出在MySQL暫時停止響應新請求之前的短時間內(nèi)多少個請求可以被存在堆棧中。
back_log=1024
默認：back_log=80
?
索引塊的緩沖區(qū)大小
key_buffer_size = 32M
#索引塊的緩沖區(qū)大小，對MyISAM表性能影響最大的一個參數(shù).決定索引處理的速度，尤其是索引讀的速度。默認值是8M，通過檢查狀態(tài)值Key_read_requests
#和Key_reads，可以知道key_buffer_size設置是否合理
默認：key_buffer_size=8M
?
MySQL執(zhí)行排序使用的緩沖大小
sort_buffer_size = 16M
#是MySQL執(zhí)行排序使用的緩沖大小。如果想要增加ORDER BY的速度，首先看是否可以讓MySQL使用索引而不是額外的排序階段。
#如果不能，可以嘗試增加sort_buffer_size變量的大小。
默認：sort_buffer_size=256K
?
MYSQL讀入緩沖區(qū)大小
read_buffer_size = 64M
#是MySQL讀入緩沖區(qū)大小。對表進行順序掃描的請求將分配一個讀入緩沖區(qū)，MySQL會為它分配一段內(nèi)存緩沖區(qū)。read_buffer_size變量控制這一緩沖區(qū)的大小。
#如果對表的順序掃描請求非常頻繁，并且你認為頻繁掃描進行得太慢，可以通過增加該變量值以及內(nèi)存緩沖區(qū)大小提高其性能。
默認：read_buffer_size=64K
Join操作緩存大小
join_buffer_size = 16M
#應用程序經(jīng)常會出現(xiàn)一些兩表（或多表）Join的操作需求，MySQL在完成某些 Join 需求的時候（all/index join），為了減少參與Join的“被驅(qū)動表”的
#讀取次數(shù)以提高性能，需要使用到 Join Buffer 來協(xié)助完成 Join操作。當 Join Buffer 太小，MySQL 不會將該 Buffer 存入磁盤文件，
#而是先將Join Buffer中的結果集與需要 Join 的表進行 Join 操作，
#然后清空 Join Buffer 中的數(shù)據(jù)，繼續(xù)將剩余的結果集寫入此 Buffer 中，如此往復。這勢必會造成被驅(qū)動表需要被多次讀取，成倍增加 IO 訪問，降低效率。
默認：join_buffer_size=256K
MySQL的隨機讀緩沖區(qū)大小
read_rnd_buffer_size = 32M
#是MySQL的隨機讀緩沖區(qū)大小。當按任意順序讀取行時(例如，按照排序順序)，將分配一個隨機讀緩存區(qū)。進行排序查詢時，MySQL會首先掃描一遍該緩沖，以避免磁盤搜索，
#提高查詢速度，如果需要排序大量數(shù)據(jù)，可適當調(diào)高該值。但MySQL會為每個客戶連接發(fā)放該緩沖空間，所以應盡量適當設置該值，以避免內(nèi)存開銷過大。
默認：read_rnd_buffer_size=256K
緩存排序索引大小
myisam_sort_buffer_size = 256M
#當對MyISAM表執(zhí)行repair table或創(chuàng)建索引時，用以緩存排序索引；設置太小時可能會遇到” myisam_sort_buffer_size is too small”
myisam_sort_buffer_size=102M
緩存空閑的線程以便不被銷毀
thread_cache_size = 384
#thread_cahe_size線程池，線程緩存。用來緩存空閑的線程，以至于不被銷毀，如果線程緩存在的空閑線程，需要重新建立新連接，
#則會優(yōu)先調(diào)用線程池中的緩存，很快就能響應連接請求。每建立一個連接，都需要一個線程與之匹配。
默認：thread_cache_size=10???????
set global max_connections=2000;#設置最大連接數(shù) set global key_buffer_size=512*1024*1024;#設置索引塊緩沖區(qū)大小 set global sort_buffer_size=128*1024*1024;#MySQL執(zhí)行排序使用的緩沖大小 set global read_buffer_size=64*1024*1024;#MYSQL讀入緩沖區(qū)大小 set global join_buffer_size=128*1024*1024;#Join操作緩存大小 set global read_rnd_buffer_size=32*1024*1024;#MySQL的隨機讀緩沖區(qū)大小 set global myisam_sort_buffer_size=256*1024*1024;#緩存排序索引大小 set global thread_cache_size=384;#緩存空閑的線程以便不被銷毀 set global innodb_buffer_pool_size=1000*1024*1024;#內(nèi)存#查詢SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size'
???????
3、優(yōu)化DB設計
-參照范式進行設計（1級范式）
1NF
包含分隔符類字符的字符串數(shù)據(jù)。
名字尾端有數(shù)字的屬性。
沒有定義鍵或鍵定義不好的表。
2NF
多個屬性有同樣的前綴。
重復的數(shù)據(jù)組。
匯總的數(shù)據(jù)，所引用的數(shù)據(jù)在一個完全不同的實體中。
BCNF- “每個鍵必須唯一標識實體，每個非鍵熟悉必須描述實體。
4NF
三元關系（實體:實體:實體）。
潛伏的多值屬性。（如多個手機號。）
臨時數(shù)據(jù)或歷史值。（需要將歷史數(shù)據(jù)的主體提出，否則將存在大量冗余。）
?
-建立合適的索引
建索引的目的
加快查詢速度。
減少I/O操作，通過索引的路徑來檢索數(shù)據(jù)，不是在磁盤中隨機檢索。
消除磁盤排序，索引是排序的，走完索引就排序完成
1）B-Tree 索引
B-Tree 索引是 MySQL 數(shù)據(jù)庫中使用最為頻繁的索引類型
2）Hash 索引
Hash 索引結構的特殊性，其檢索效率非常高，索引的檢索可以一次定位，不像B-Tree索引需要從根節(jié)點到枝節(jié)點，最后才能訪問到頁節(jié)點這樣多次的IO訪問，所以Hash索引的查詢效率要遠高于B-Tree索引。
3）什么時候可以建索引
1）列無重復值，可以建索引：唯一索引和普通索引
2）聚集索引和非聚集索引都可以是唯一的。因此，只要列中的數(shù)據(jù)是唯一的，就可以在同一個表上創(chuàng)建一個唯一的聚集索引和多個唯一的非聚集索引。
3）建了索引性能得到提高
4）區(qū)分度高的列可以建索引，比如表示男和女的列區(qū)分度就不高，就不能建索引
5）什么時候不可以建索引
1.頻繁更新的字段不適合建立索引
2.where條件中用不到的字段不適合建立索引
3.表數(shù)據(jù)可以確定比較少的不需要建索引
4.數(shù)據(jù)重復且發(fā)布比較均勻的的字段不適合建索引（唯一性太差的字段不適合建立索引），例如性別，真假值
5.參與列計算的列不適合建索引，如select * from where amount+1>10
6.查詢返回的記錄數(shù)不適合建立索引
7.查詢的排序表記錄小于40%不適合建立索引
8.查詢非排序表的記錄小于 7%不適合建立索引
9.表的碎片較多（頻繁增加、刪除）不適合建立索引
?
4、優(yōu)化架構設計方案
-
加緩存
-
DNS輪詢
通過在DNS-server上對一個域名設置多個ip解析，來擴充web-server性能及實施負載均衡的技術 。
-
LVS（負載均衡）
Linux Virtual Server，使用集群技術，實現(xiàn)在linux操作系統(tǒng)層面的一個高性能、高可用、負載均衡服務器 。
-
nginx：一個高性能的web-server和實施反向代理的軟件
一個高性能的web-server和實施反向代理的軟件
?
創(chuàng)作挑戰(zhàn)賽新人創(chuàng)作獎勵來咯，堅持創(chuàng)作打卡瓜分現(xiàn)金大獎總結
以上是生活随笔為你收集整理的Mysql调优你不知道这几点，就太可惜了的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 安卓经营游戏（安卓 经营游戏）
- 下一篇： dedecms后台程序已经安装完了怎么把