MySQL優(yōu)化技巧與實(shí)例

MySQL優(yōu)化技巧與實(shí)例MySQL數(shù)據(jù)庫的大體介紹優(yōu)化思想控制SELECT行為使用索引查詢優(yōu)化器優(yōu)化字段屬性設(shè)置使用連接來代替子查詢優(yōu)化查詢語句使用外鍵

MySQL數(shù)據(jù)庫的大體介紹

MySQL是一個(gè)高性能、多線程、多用戶、建立在客戶-服務(wù)器結(jié)構(gòu)上的RDBMS，專門為速度和穩(wěn)定性而設(shè)計(jì)。開源，免費(fèi)對大容量的數(shù)據(jù)有很好的支持大多數(shù)中小型網(wǎng)站的首選目前版本到6.0，主流為5.x，國內(nèi)還有不少用4.x。目前流行的LAMP架構(gòu)的一部分。

MySQL數(shù)據(jù)庫的大體介紹

舉例：惠普，摩托羅拉，索尼，騰訊，百度，YouTube等大企業(yè)、大網(wǎng)站所使用日本第三大，世界排名42的網(wǎng)站mixi.jp的數(shù)據(jù)庫技術(shù)資料Database:MySQLServer

DatabaseSize:836GB

(Diaries)400MillionRowsperTable

優(yōu)化思想80%─20%即程序80%運(yùn)行時(shí)間是浪費(fèi)在20%的代碼上分析找出消耗80%運(yùn)行時(shí)間的那20%的數(shù)據(jù)庫語句，對其進(jìn)行優(yōu)化處理。時(shí)間─空間認(rèn)識兩者間的矛盾和權(quán)衡其利弊關(guān)系，盡量接近所謂的“時(shí)間─空間黃金分割點(diǎn)”。

優(yōu)化思想舉例桑梓貼吧首頁的數(shù)據(jù)庫語句優(yōu)化過程:1.輸出所有執(zhí)行的數(shù)據(jù)庫語句.2.查看每條數(shù)據(jù)庫語句的執(zhí)行時(shí)間.3.找出那20%的數(shù)據(jù)庫語句加以優(yōu)化;得到主要消耗時(shí)間的查詢語句：SELECTSQL_NO_CACHEu_id,u_name,SUM(tl_hit)ASamountFROMuserLEFTJOINtb_listONtl_userid=u_idGROUPBYu_nameORDERBYamountDESCLIMIT0,5

優(yōu)化思想4.增加有效的數(shù)據(jù)冗余，在用戶表中增加一個(gè)記錄點(diǎn)擊率的字段。查詢語句轉(zhuǎn)變?yōu)椋篠ELECTSQL_NO_CACHEu_id,u_name,u_hitnumFROMuserORDERBYu_hitnumDESCLIMIT0,55.速度得到很大改善，從原來的1秒多降至毫秒級。

控制SELECT的行為

SELECT[action]*FROMtable[action]參數(shù)DISTINCT關(guān)鍵字刪除包含結(jié)果集中具有重復(fù)值的記錄SQL_CALC_FOUND_ROWS

告訴MySQL計(jì)算符合查詢的總行數(shù)（不考慮LIMIT）。通過調(diào)用FOUND_ROWS()函數(shù)可以得到總行數(shù)。

控制SELECT的行為

SQL_CACHE和SQL_NO_CACHE

告訴MySQL查詢結(jié)果是否需要高速緩存。SQL_BIG_RESULT和SQL_SMALL_RESULT

可以指定結(jié)果集的期望大小，因此可以幫助找到最佳方法對返回的記錄進(jìn)行排序和存儲。(臨時(shí)表)SQL_HIGH_PRIORITY

提升了與UPDATE,INSERT和DELETE語句相競爭的查詢優(yōu)先級，因而可以在繁忙的數(shù)據(jù)庫服務(wù)器上快速地執(zhí)行查詢。

使用索引

如果沒有使用索引，在很多情況下，你試圖使用其它的方法來提高性能都是在浪費(fèi)時(shí)間。首先使用索引來獲取最大的性能提高，接著再看其它的技術(shù)是否有用。

恰當(dāng)?shù)氖褂盟饕茉诤艽蟪潭壬咸岣進(jìn)ySQL的查詢性能。反之，如果濫用，則會造成性能的降低。

使用索引

MySQL中索引使用的四種方式：一、索引被用于提高WHERE條件的數(shù)據(jù)行匹配或者執(zhí)行聯(lián)結(jié)操作時(shí)匹配其它表的數(shù)據(jù)行的搜索速度。二、對于使用了MIN()或MAX()函數(shù)的查詢，索引數(shù)據(jù)列中最小或最大值可以很快地找到，不用檢查每個(gè)數(shù)據(jù)行。三、MySQL利用索引來快速地執(zhí)行ORDERBY和GROUPBY語句的排序和分組操作。四、有時(shí)候MySQL會利用索引來讀取查詢得到的所有信息。

使用索引

舉例：SELECTSQL_NO_CACHEu_id,u_name,SUM(tl_hit)ASamountFROMuserLEFTJOINtb_listONtl_userid=u_idGROUPBYu_nameORDERBYamountDESCLIMIT0,5觀察在有建立tl_userid索引和沒建立tl_userid索引下的執(zhí)行時(shí)間

使用索引

索引的代價(jià)

減慢了插入、刪除的速度，同時(shí)也減慢了更新被索引的數(shù)據(jù)列中的值的速度索引會花費(fèi)磁盤空間，多個(gè)索引相應(yīng)地花費(fèi)更多的磁盤空間

使用索引

選擇索引字段用于索引的最好的備選數(shù)據(jù)列是那些出現(xiàn)在WHERE子句、join子句、ORDERBY或GROUPBY子句中的列。僅僅出現(xiàn)在SELECT關(guān)鍵字后面的輸出數(shù)據(jù)列列表中的數(shù)據(jù)列不是很好的備選列，比如：

SELECT

col_a<-不是備選列

FROM

table1LEFTJOINtable2

ONtable1.col_b=table2.col_c<-備選列

WHERE

col_d=value;<-備選列

使用索引

考慮數(shù)據(jù)列的基數(shù)?；鶖?shù)是數(shù)據(jù)列所包含的不同值的數(shù)量。索引的基數(shù)相對于數(shù)據(jù)表行數(shù)越高，索引的工作效率越好，反之則越低。查詢優(yōu)化器發(fā)現(xiàn)某個(gè)值出現(xiàn)在表的數(shù)據(jù)行中的百分比很高的時(shí)候，它一般會忽略索引，進(jìn)行全表掃描。

索引較短的值，盡可能地使用較小的數(shù)據(jù)類型。較短的值可以更快地進(jìn)行比較，因此索引的查找速度更快了。較小的值導(dǎo)致較小的索引，需要更少的磁盤I/O。

使用索引

索引字符串值的前綴如果你需要索引一個(gè)字符串?dāng)?shù)據(jù)列，那么最好在任何適當(dāng)?shù)那闆r下都應(yīng)該指定前綴長度。例如，如果有CHAR(200)數(shù)據(jù)列，如果前面10個(gè)或20個(gè)字符都不同，就不要索引整個(gè)數(shù)據(jù)列?？山⑶熬Y的類型有CHAR、VARCHAR、BINARY、VARBINARY、BLOB和TEXT

使用索引

使用最左前綴。

建立多列復(fù)合索引的時(shí)候，你實(shí)際上建立了MySQL可以使用的多個(gè)索引。復(fù)合索引可以作為多個(gè)索引使用，因?yàn)樗饕凶钭筮叺牧屑隙伎梢杂糜谄ヅ鋽?shù)據(jù)行。這種列集合被稱為“最左前綴”。例如：在表的c1、c2和c3數(shù)據(jù)列上建立了復(fù)合索引索引可搜索如下的數(shù)據(jù)列組合為c1c2c3c1c2c1

使用索引

舉例貼吧內(nèi)容搜索的優(yōu)化ALTERTABLE`tb_list`ADDINDEX`tl_select_title_describe`(`tl_title`(10),`tl_describe`(10))

使用索引

索引使用時(shí)的一些注意事項(xiàng)：盡量不要在列上進(jìn)行數(shù)據(jù)庫函數(shù)、計(jì)算表達(dá)式等操作，這樣將會導(dǎo)致表的掃描，使索引失效。IN、OR子句常會使用工作表，使索引失效。如果不產(chǎn)生大量重復(fù)值，可以考慮把子句拆開。拆開的子句中應(yīng)該包含索引。

使用索引

舉例：如果在fields1和fields2上同時(shí)建立了索引，fields1為主索引

會用到索引select*fromtablename1wherefields1='value1'andfields2='value2'不會用到索引select*fromtablename1wherefields1='value1'andfields2='value2'索引失效Select*fromtablename1wheresum(fields1)>100

查詢優(yōu)化器用EXPLAIN來分析查詢語句。類型（type）值表明它可以使用索引來搜索特定范圍的值（小于右邊表達(dá)式給定的值）。

可能鍵（possible_keys）和鍵（key）值顯示索引已經(jīng)被考慮作為備選索引，并且它也是真正使用的索引。

行數(shù)（rows）值顯示優(yōu)化器估計(jì)自己需要檢查數(shù)據(jù)行來處理該查詢。

查詢優(yōu)化器額外操作（Extra）：額外給定的一些操作舉例：用EXPLAIN來分析查詢語句。mysql>EXPLAINselecttb_comment.*,u_namefromtb_commentleftjoinuseronu_id=tc_useridorderbytc_iddesc

優(yōu)化字段屬性設(shè)置

前提：清楚的了解每個(gè)字段的功能和特征。字段的寬度盡可能設(shè)的小字段的類型寬度盡量設(shè)少，類型盡量設(shè)小。盡量少用VARCHAR、TEXT、BLOB類型。能用數(shù)值類型的，就不要用字符類型。數(shù)據(jù)為有限的幾個(gè)取值時(shí),最好使用ENUM類型

優(yōu)化字段屬性設(shè)置

舉例：密碼屬性等即可定為CHAR(X)X為最大密碼長度。而無需用VARCHAR等類型。性別、省份等字段可用ENUM或SMALLINT類型。而無需用CHAR、VARCHAR等類型。

CHAR、VARCHAR所帶來的效率上的差別演示。

使用連接來代替子查詢使用子查詢可以一次性完成很多邏輯上需要多個(gè)步驟才能完成的SQL操作,同時(shí)也可以避免事務(wù)或者表鎖死,并且寫起來也很容易。但是,有時(shí),子查詢可以被更有效的連接(JOIN)替代.

使用連接來代替子查詢舉例:SELECTSQL_NO_CACHE*FROMtb_commentWHEREtc_userid=(SELECTu_idFROMuserWHEREu_name=’lbj’)limit0,1000SELECTSQL_NO_CACHE*FROMtb_comment,userWHEREtc_userid=u_idANDu_name=’lbj’limit0,1000

優(yōu)化查詢語句任何對列的操作都將導(dǎo)致表掃描，它包括數(shù)據(jù)庫函數(shù)、計(jì)算表達(dá)式等等，查詢時(shí)要盡可能將操作移至等號右邊。最好是在相同類型的字段間進(jìn)行比較操作。在建有索引的字段上盡量不要使用函數(shù)進(jìn)行操作

。

優(yōu)化查詢語句在搜索字符型字段時(shí)，我們有時(shí)會使用LIKE關(guān)鍵字和通配符，這種做法雖然簡單，但卻也是以犧牲系統(tǒng)性能為代價(jià)的。可選擇建立FULLTEXT索引，或是用一些技巧來加以替換。

優(yōu)化查詢語句舉例：CREATETABLEarticles(

idINTUNSIGNEDAUTO_INCREMENTNOTNULLPRIMARYKEY,

titleVARCHAR(200),

bodyTEXT,

FULLTEXT(title,body)

);

優(yōu)化查詢語句INSERTINTOarticlesVALUES

(NULL,'MySQLTutorial','DBMSstandsforDataBase...'),

(NULL,'HowToUseMySQLEfficiently','Afteryouwentthrougha...'),

(NULL,'OptimisingMySQL','Inthistutorialwewillshow...'),

(NULL,'1001MySQLTricks','1.Neverrunmysqldasroot.2....'),

(NULL,'MySQLvs.YourSQL','Inthefollowingdatabasecomparison...'),

(NULL,'MySQLSecurity','Whenconfiguredproperly,MySQL...');

SELECT*FROMarticlesWHEREMATCH(title,body)AGAINST('database');

優(yōu)化查詢語句SELECTSQL_NO_CACHE*FROMtb_userrankWHEREYEAR(tu_date)<11SELECTSQL_NO_CACHE*FROMtb_userrankWHEREtu_date<"2008-01-01“SELECTSQL_NO_CACHE*FROMuserWHEREusernameLIKE"blu%"SELECTSQL_NO_CACHE*FROMuserWHEREusername>='blu'ANDusername<'blv'

優(yōu)化查詢語句使用隨機(jī)排序的時(shí)候，如果用rand()效率比較低，這時(shí)候可采用子查詢語句來替換。例子：貼吧隨機(jī)排序中的優(yōu)化SelectSQL_NO_CACHE*FROMtb_listORDERBYRAND()LIMIT100;替換成：SELECTSQL_NO_CACHE*FROMtb_listASr1JOIN(SELECTROUND(RAND()*(SELECTMAX(tl_id)FROMtb_list))ASid)ASr2WHEREr1.tl_id>=r2.idORDERBYr1.tl_idASCL