今天給大家分享的是MySQL性能優(yōu)化,也是大數(shù)據(jù)開發(fā)指南MySQL的最后一部分。性能優(yōu)化對于老劉來說,是必須掌握的一個(gè)手段,如何讓自己變得更加優(yōu)秀,這塊內(nèi)容還是好好看看!
本篇內(nèi)容相對簡潔,核心內(nèi)容在SQL優(yōu)化經(jīng)驗(yàn)總結(jié),通過這篇mysql的性能優(yōu)化,大家能夠掌握如下內(nèi)容:
- 會使用和分析慢查詢?nèi)罩?/li>
- 會使用和分析profile
- SQL優(yōu)化經(jīng)驗(yàn)總結(jié)
如何進(jìn)行性能分析?
一般進(jìn)行性能分析,分如下三步:
- 首先需要使用慢查詢?nèi)罩?/strong>功能,去獲取所有查詢時(shí)間比較長的SQL語句
- 其次查看執(zhí)行計(jì)劃查看有問題的SQL的執(zhí)行計(jì)劃 explain
- 最后可以使用show profile查看有問題的SQL的性能使用情況
慢查詢?nèi)罩痉治?/h3>
首先我們要使用慢查詢?nèi)罩荆驗(yàn)樗占瞬樵儠r(shí)間比較長的SQL語句,但使用之前必須開啟慢查詢?nèi)罩荆谂渲梦募y.cnf(一般為/etc/my.cnf)中的[mysqld] 增加如下參數(shù):
slow_query_log=ON
long_query_time=3
slow_query_log_file=/var/lib/mysql/slow-log.log
增加這些參數(shù)之后,重啟MySQL,可以進(jìn)行查詢慢查詢?nèi)罩臼欠耖_啟。
分析慢查詢?nèi)罩镜墓ぞ?/h3>
分析慢查詢?nèi)罩镜墓ぞ哂泻芏啵蟿⒎窒韼追N工具,詳細(xì)的用法大家自行查詢。
- mysqldumpslow是MySQL自帶的慢查詢?nèi)罩竟ぞ撸覀兛梢允褂胢ysqldumpslow工具搜索慢查詢?nèi)罩局械腟QL語句。
- percona-toolkit是一組高級命令行工具的集合,可以查看當(dāng)前服務(wù)的摘要信息,磁盤檢測,分析慢查詢?nèi)罩荆檎抑貜?fù)索引,實(shí)現(xiàn)表同步等等(有空單獨(dú)寫一篇關(guān)于percona-toolkit的入門博客)。
explain查看有問題的SQL語句
當(dāng)SQL查詢速度比較慢的時(shí)候,我們可以用explain查看這個(gè)SQL語句的相關(guān)情況,這部分內(nèi)容已經(jīng)在精通MySQL之索引篇講過,大家可以去看看。
show profile查看有問題的SQL語句
Query Profiler是MySQL自帶的一種query診斷分析工具,通過它可以分析出一條SQL語句的硬件性能瓶頸在什么地方。比如CPU,IO等,以及該SQL執(zhí)行所耗費(fèi)的時(shí)間等。不過該工具只有在MySQL 5.0.37以及以上版本中才有實(shí)現(xiàn)。默認(rèn)的情況下,MYSQL的該功能沒有打開,需要自己手動啟動。
SQL優(yōu)化經(jīng)驗(yàn)總結(jié)
由于老劉還是研究生以及還沒工作,所以在SQL性能優(yōu)化這塊只能總結(jié)別人的經(jīng)驗(yàn)分享給大家,老劉本篇主要想做的事情也是分享一些優(yōu)秀工程師總結(jié)的SQL優(yōu)化知識點(diǎn),前面的內(nèi)容寫的相對簡潔,希望大家不要埋怨!
-
任何地方都不要使用 select * from t,用具體的字段列表代替“*“,不要返回用不到的任何字段。
-
索引并不是越多越好,索引固然可以提高相應(yīng)的 select 的效率,但同時(shí)也降低了 insert 及 update 的效率,因?yàn)?insert 或 update 時(shí)有可能會重建索引,所以怎樣建索引需要慎重考慮,視具體情況而定。一個(gè)表的索引數(shù)最好不要超過6個(gè),若太多則應(yīng)考慮一些不常使用到的列上建的索引是否有必要。
-
并不是所有索引對查詢都有效,SQL是根據(jù)表中數(shù)據(jù)來進(jìn)行查詢優(yōu)化的,當(dāng)索引列有大量數(shù)據(jù)重復(fù)時(shí),SQL查詢可能不會去利用索引,如一表中有字段sex,male、female幾乎各一半,那么即使在sex上建了索引也對查詢效率起不了作用。
-
盡量使用數(shù)字型字段,若只含數(shù)值信息的字段盡量不要設(shè)計(jì)為字符型,這會降低查詢和連接的性能,并會增加存儲開銷。這是因?yàn)橐嬖谔幚聿樵兒瓦B接時(shí)會逐個(gè)比較字符串中每一個(gè)字符,而對于數(shù)字型而言只需要比較一次就夠了。
-
盡可能的使用 varchar 代替 char ,因?yàn)槭紫茸冮L字段存儲空間小,可以節(jié)省存儲空間, 其次對于查詢來說,在一個(gè)相對較小的字段內(nèi)搜索效率顯然要高些。
-
如果使用到了臨時(shí)表,在存儲過程的最后務(wù)必將所有的臨時(shí)表顯式刪除,先 truncate table ,然后 drop table ,這樣可以避免系統(tǒng)表的較長時(shí)間鎖定。
-
對查詢進(jìn)行優(yōu)化,應(yīng)盡量避免全表掃描,首先應(yīng)考慮在 where和order by相關(guān)的列上建立索引。
-
應(yīng)盡量避免在 where 子句中對字段進(jìn)行 null 值判斷,否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。
例如: select * from t where num is null
我們可以在num上設(shè)置默認(rèn)值0,確保表中num列沒有null值,然后這樣查詢:select * from t where num=0。
-
索引字段上不要使用不等,索引字段上使用(!= 或者 < >)判斷時(shí),會導(dǎo)致索引失效而轉(zhuǎn)向全表掃描。
-
應(yīng)盡量避免在 where 子句中使用 or 來連接條件,否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。
例如: select * from t where num=10 or num=20
我們可以這樣查詢:select * from t where num=10 union all select * from t where num=20
-
應(yīng)盡量避免在 where 子句中對字段進(jìn)行表達(dá)式操作,這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。
例如:select * from t where num/2=100
我們應(yīng)該改為: select * from t where num=100*2
-
應(yīng)盡量避免在where子句中對字段進(jìn)行函數(shù)操作,這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。
例如:select * from t where substring(name,1,3)='abc' -- name以abc開頭的id
我們應(yīng)該改為: select * from t where name like 'abc%'
-
不要在 where 子句中的“=”左邊進(jìn)行函數(shù)、算術(shù)運(yùn)算或其他表達(dá)式運(yùn)算,否則系統(tǒng)將可能無法正確使用索引。
-
很多時(shí)候用 exists 代替 in 是一個(gè)好的選擇。
例如:select num from a where num in(select num from b)
我們應(yīng)該這樣替換:select num from a where exists(select 1 from b where num=a.num)
總結(jié)
本文作為大數(shù)據(jù)開發(fā)指南MySQL的最后一篇簡潔明練的講述了一些SQL性能優(yōu)化的技巧,希望大家能夠跟著老劉的文章,好好捋捋思路,爭取能夠用自己的話把這些知識點(diǎn)講述出來!
盡管當(dāng)前水平可能不及各位大佬,但老劉會努力變得更加優(yōu)秀,讓各位小伙伴自學(xué)從此不求人!