http://www.phpben.com/?post=74

?

摘要：

第一部分：基礎(chǔ)知識 第二部分：MYISAM和INNODB索引結(jié)構(gòu) 1、?簡單介紹B-tree B+ tree樹 2、?MyisAM索引結(jié)構(gòu) 3、?Annode索引結(jié)構(gòu) 4、?MyisAM索引與InnoDB索引相比較 第三部分：MYSQL優(yōu)化 1、表數(shù)據(jù)類型選擇 2、sql語句優(yōu)化 (1)?????最左前綴原則 (1.1)??能正確的利用索引 (1.2)??不能正確的利用索引 (1.3)??如果一個查詢where子句中確實不需要password列，那就用“補(bǔ)洞”。 (1.4)??like (2)?????Order by?優(yōu)化 (2.1)?filesort優(yōu)化算法. (2.2)?單獨order by?用不了索引，索引考慮加where?或加limit (2.3)?where + orerby?類型，where滿足最左前綴原則，且orderby的列和where子句用到的索引的列的子集。即是(a,b,c)索引，where滿足最左前綴原則且order by中列a、b、c的任意組合 (2.4) where + orerby+limit (2.5)?如何考慮order by來建索引 (3)?????隔離列 (4)?????OR、IN、UNION ALL，可以嘗試用UNION ALL (4.1)?or會遍歷表就算有索引 (4.2)關(guān)于in (4.2)?UNION All (5)?????范索引選擇性 (6)?????重復(fù)或多余索引 3、系統(tǒng)配置與維護(hù)優(yōu)化 (1)?????重要的一些變量 (2)?????Fds?optimize、Analyze、check、repair維護(hù)操作 (3)?????表結(jié)構(gòu)的更新與維護(hù) 第四部分：圖說mysql查詢執(zhí)行流程 ?

?

?

?

?

第一部分：基礎(chǔ)知識：

索引

官方介紹索引是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。筆者理解索引相當(dāng)于一本書的目錄，通過目錄就知道要的資料在哪里，不用一頁一頁查閱找出需要的資料。關(guān)鍵字index

-------------------------------------------------------------

唯一索引

強(qiáng)調(diào)唯一，就是索引值必須唯一，關(guān)鍵字unique index

創(chuàng)建索引：

1、create? unique index ?索引名?on?表名(列名);

2、alter table?表名?add? unique index ?索引名?(列名);

刪除索引：

1、??drop index?索引名?on?表名;

2、??alter table?表名?drop index?索引名;

-------------------------------------------------------------

主鍵

主鍵就是唯一索引的一種，主鍵要求建表時指定，一般用auto_increatment列，關(guān)鍵字是primary key

主鍵創(chuàng)建：

creat table test2 (id int not null? primary key ?auto_increment);

-------------------------------------------------------------

全文索引

InnoDB不支持，Myisam支持性能比較好，一般在?CHAR、VARCHAR?或?TEXT?列上創(chuàng)建。

Create table?表名( id int not null primary anto_increment,title

varchar(100), FULLTEXT (title))type= myisam

------------------------------

單列索引與多列索引

索引可以是單列索引也可以是多列索引（也叫復(fù)合索引）。按照上面形式創(chuàng)建出來的索引是單列索引，現(xiàn)在先看看創(chuàng)建多列索引：

create table test3 (id int not null primary key auto_increment,uname char

(8) not null default '',password char(12) not null, INDEX (uname,password))type

=myisam;

注意：INDEX(a, b, c)可以當(dāng)做a或(a, b)的索引來使用，但和b、c或(b,c)的索引來使用這是一個 最左前綴 的優(yōu)化方法，在后面會有詳細(xì)的介紹，你只要知道有這樣兩個概念

-------------------------------------------------------------

聚集索引

一種索引，該索引中鍵值的邏輯順序決定了表中相應(yīng)行的物理順序。?聚集索引確定表中數(shù)據(jù)的物理順序。Mysql中myisam表是沒有聚集索引的，innodb有（主鍵就是聚集索引），聚集索引在下面介紹innodb結(jié)構(gòu)的時有詳細(xì)介紹。

-------------------------------------------------------------

查看表的索引

通過命令：Show index from?表名

如：

?

?

?Table：表名

Key_name：什么類型索引（這了是主鍵）

Column_name：索引列的字段名

Cardinality：索引基數(shù)，很關(guān)鍵的一個參數(shù)，平均數(shù)值組=索引基數(shù)/表總數(shù)據(jù)行，平均數(shù)值組越接近1就越有可能利用索引

Index_type：如果索引是全文索引，則是fulltext,這里是b+tree索引，b+tre也是這篇文章研究的重點之一

其他的就不詳細(xì)介紹，更多：

第二部分： MYISAM 和 INNODB 索引結(jié)構(gòu)

1、? 簡單介紹 B-tree B+ tree 樹

B-tree結(jié)構(gòu)視圖

?

?

一棵m階的B-tree樹，則有以下性質(zhì)

（1）Ki表示關(guān)鍵字值，上圖中，k1<k2<…<ki<k0<Kn(可以看出，一個節(jié)點的左子節(jié)點關(guān)鍵字值<該關(guān)鍵字值<右子節(jié)點關(guān)鍵字值)

（2）Pi表示指向子節(jié)點的指針，左指針指向左子節(jié)點，右指針指向右子節(jié)點。即是：p1[指向值]<k1<p2[指向值]<k2……

（3）所有關(guān)鍵字必須唯一值（這也是創(chuàng)建myisam?和innodb表必須要主鍵的原因），每個節(jié)點包含一個說明該節(jié)點多少個關(guān)鍵字，如上圖第二行的i和n

（4）節(jié)點：

l??每個節(jié)點最可以有m個子節(jié)點。

l??根節(jié)點若非葉子節(jié)點，至少2個子節(jié)點，最多m個子節(jié)點

l??每個非根，非葉子節(jié)點至少[m/2]子節(jié)點或叫子樹（[]表示向上取整），最多m個子節(jié)點

（5）關(guān)鍵字：

l??根節(jié)點的關(guān)鍵字個數(shù)1~m-1

l??非根非葉子節(jié)點的關(guān)鍵字個數(shù)[m/2]-1~m-1,如m=3，則該類節(jié)點關(guān)鍵字個數(shù)：2-1~2

（6）關(guān)鍵字?jǐn)?shù)k和指向子節(jié)點個數(shù)指針p的關(guān)系：

l??k+1=p?，注意根據(jù)儲存數(shù)據(jù)的具體需求，左右指針為空時要有標(biāo)志位表示沒有

?

??B+tree結(jié)構(gòu)示意圖如下：

?

?

B+樹是B-樹的變體，也是一種多路搜索樹：

l??非葉子結(jié)點的子樹指針與關(guān)鍵字個數(shù)相同

l??為所有葉子結(jié)點增加一個鏈指針（紅點標(biāo)志的箭頭）

?

?

B+樹是B-樹的變體，也是一種多路搜索樹：

l??非葉子結(jié)點的子樹指針與關(guān)鍵字個數(shù)相同

l??為所有葉子結(jié)點增加一個鏈指針（紅點標(biāo)志的箭頭）

2 、? MyisAM 索引結(jié)構(gòu)

MyisAM索引用的B+tree來儲存數(shù)據(jù)，MyisAM索引的指針指向的是鍵值的地址，地址存儲的是數(shù)據(jù)，如下圖：

?

（1）結(jié)構(gòu)講解：上圖3階樹，主鍵是Col2，Col值就是改行數(shù)據(jù)保存的物理地址，其中紅色部分是說明標(biāo)注。

l??1標(biāo)注部分也許會迷惑，前面不是說關(guān)鍵字15右指針的指向鍵值要大于15，怎么下面還有15關(guān)鍵字？因為B+tree的所以葉子節(jié)點包含所有關(guān)鍵字且是按照升序排列（主鍵索引唯一，輔助索引可以不唯一），所以等于關(guān)鍵字的數(shù)據(jù)值在右子樹

l??2標(biāo)注是相應(yīng)關(guān)鍵字存儲對應(yīng)數(shù)據(jù)的物理地址，注意這也是之后和InnoDB索引不同的地方之一

l??2標(biāo)注也是一個所說MyiAM表的索引和數(shù)據(jù)是分離的，索引保存在”表名.MYI”文件內(nèi)，而數(shù)據(jù)保存在“表名.MYD”文件內(nèi)，2標(biāo)注的物理地址就是“表名.MYD”文件內(nèi)相應(yīng)數(shù)據(jù)的物理地址。（InnoDB表的索引文件和數(shù)據(jù)文件在一起）

l??輔助索引和主鍵索引沒什么大的區(qū)別，輔助索引的索引值是可以重復(fù)的（但I(xiàn)nnoDB輔助索引和主鍵索引有很明顯的區(qū)別，這里先提醒注意一下）

3、? Annode 索引結(jié)構(gòu)

?

?

（1）首先有一個表，內(nèi)容和主鍵索引結(jié)構(gòu)如下兩圖：

Col1	Col2	Col3
1	15	phpben
2	20	mhycoe
3	23	phpyu
4	25	bearpa
5	40	phpgoo
6	45	phphao
7	48	phpxue
……

?

結(jié)構(gòu)上：由上圖可以看出InnoDB的索引結(jié)構(gòu)很MyisAM的有很明顯的區(qū)別

?l??MyisAM表的索引和數(shù)據(jù)是分開的，用指針指向數(shù)據(jù)的物理地址，而InnoDB表中索引和數(shù)據(jù)是儲存在一起。看紅框1可一看出一行數(shù)據(jù)都保存了。

?l??還有一個上圖多了三行的隱藏數(shù)據(jù)列（虛線表），這是因為MyisAM不支持事務(wù)，InnoDB處理事務(wù)在性能上并發(fā)控制上比較好，看圖中的紅框2中的DB_TRX_ID是事務(wù)ID，自動增長；db_roll_ptr是回滾指針，用于事務(wù)出錯時數(shù)據(jù)回滾恢復(fù)；db_row_id是記錄行號，這個值其實在主鍵索引中就是主鍵值，這里標(biāo)出重復(fù)是為了容易介紹，還有的是若不是主鍵索引（輔助索引），db_row_id會找表中unique的列作為值，若沒有unique列則系統(tǒng)自動創(chuàng)建一個。關(guān)于InnoDB跟多事務(wù)MVCC點此： http://www.phpben.com/?post=72 ?

（2）加入上表中Col1是主鍵（下圖 標(biāo)錯 ），而Col2是輔助索引，則相應(yīng)的輔助索引結(jié)構(gòu)圖：

?

?

可以看出InnoDB輔助索引并沒有保存相應(yīng)的所有列數(shù)據(jù)，而是保存了主鍵的鍵值（圖中1、2、3….）這樣做利弊也是很明顯：

l??在已有主鍵索引，避免數(shù)據(jù)冗余，同時在修改數(shù)據(jù)的時候只需修改輔助索引值。

l??但輔助索引查找數(shù)據(jù)事要檢索兩次，先找到相應(yīng)的主鍵索引值然后在去檢索主鍵索引找到對應(yīng)的數(shù)據(jù)。這也是網(wǎng)上很多mysql性能優(yōu)化時提到的“主鍵盡可能簡短”的原因，主鍵越長輔助索引也就越大，當(dāng)然主鍵索引也越大。

4、? MyisAM 索引與 InnoDB 索引相比較

l?MyisAM支持全文索引（FULLTEXT）、壓縮索引，InnoDB不支持

l?AnnoDB支持事務(wù)，MyisAM不支持

l?MyisAM順序儲存數(shù)據(jù)，索引葉子節(jié)點保存對應(yīng)數(shù)據(jù)行地址，輔助索引很主鍵索引相差無幾；AnnoDB主鍵節(jié)點同時保存數(shù)據(jù)行，其他輔助索引保存的是主鍵索引的值

l?MyisAM鍵值分離，索引載入內(nèi)存（key_buffer_size）,數(shù)據(jù)緩存依賴操作系統(tǒng)；InnoDB鍵值一起保存，索引與數(shù)據(jù)一起載入InnoDB緩沖池

l?MyisAM主鍵（唯一）索引按升序來存儲存儲，InnoDB則不一定

l?MyisAM索引的基數(shù)值（Cardinality，show index?命令可以看見）是精確的，InnoDB則是估計值。這里涉及到信息統(tǒng)計的知識，MyisAM統(tǒng)計信息是保存磁盤中，在alter表或Analyze table操作更新此信息，而InnoDB則是在表第一次打開的時候估計值保存在緩存區(qū)內(nèi)

l?MyisAM處理字符串索引時用增量保存的方式，如第一個索引是‘preform’，第二個是‘preformence’，則第二個保存是‘7，ance‘，這個明顯的好處是縮短索引，但是缺陷就是不支持倒序提取索引，必須順序遍歷獲取索引

?

?

第三部分： MYSQL 優(yōu)化

mysql優(yōu)化是一個重大課題之一，這里會重點詳細(xì)的介紹mysql優(yōu)化，包括表數(shù)據(jù)類型選擇，sql語句優(yōu)化，系統(tǒng)配置與維護(hù)優(yōu)化三類。

1、?? 表數(shù)據(jù)類型選擇

（1）?能小就用小。表數(shù)據(jù)類型第一個原則是：使用能正確的表示和存儲數(shù)據(jù)的最短類型。這樣可以減少對磁盤空間、內(nèi)存、cpu緩存的使用。

（2）避免用NULL，這個也是網(wǎng)上優(yōu)化技術(shù)博文傳的最多的一個。理由是額外增加字節(jié)，還有使索引，索引統(tǒng)計和值更復(fù)雜。很多還忽略一

? ? ?個count(列)的問題，count(列)是不會統(tǒng)計列值為null的行數(shù)。更多關(guān)于NULL可參考： http://www.phpben.com/?post=71

（3）?字符串如何選擇char和varchar？一般phper能想到就是char是固定大小，varchar能動態(tài)儲存數(shù)據(jù)。這里整理一下這兩者的區(qū)別：

屬性	Char	Varchar
值域大小	最長 字符數(shù) 是255（不是字節(jié)），不管什么編碼，超過此值則自動截取255個字符保存并沒有報錯。	65535個字節(jié)，開始兩位存儲長度，超過255個字符，用2位儲存長度，否則1位，具體字符長度根據(jù)編碼來確定，如utf8 則字符最長是21845個
如何處理字符串末尾空格	去掉末尾空格，取值出來比較的時候自動加上進(jìn)行比較	Version<=4.1，字符串末尾空格被刪掉，version>5.0則保留
儲存空間	固定空間，比喻char(10)不管字符串是否有10個字符都分配10個字符的空間	Varchar內(nèi)節(jié)約空間，但更新可能發(fā)生變化，若varchar(10),開始若儲存5個字符，當(dāng)update成7個時有myisam可能把行拆開，innodb可能分頁，這樣開銷就增大
適用場合	適用于存儲很短或固定或長度相似字符，如MD5加密的密碼char(33)、昵稱char(8)等	當(dāng)最大長度遠(yuǎn)大于平均長度并且發(fā)生更新的時候。

?

注意當(dāng)一些英文或數(shù)據(jù)的時候，最好用每個字符用字節(jié)少的類型，如latin1

（4）?整型、整形優(yōu)先原則

Tinyint、smallint、mediumint、int、bigint，分別需要8、16、24、32、64。

值域范圍：-2^(n-1)~ 2^(n-1)-1

很多程序員在設(shè)計數(shù)據(jù)表的時候很習(xí)慣的用int，壓根不考慮這個問題

筆者建議：能用tinyint的絕不用smallint

誤區(qū) ：int(1)?和int(11)是一樣的，唯一區(qū)別是mysql客戶端顯示的時候顯示多少位。

整形優(yōu)先原則：能用整形的不用其他類型替換，如ip可以轉(zhuǎn)換成整形保存，如商品價格‘50.00元’則保存成50

（5）精確度與空間的轉(zhuǎn)換。在存儲相同數(shù)值范圍的數(shù)據(jù)時，浮點數(shù)類型通常都會比DECIMAL類型使用更少的空間。FLOAT字段使用4字節(jié)存儲

?數(shù)據(jù)。DOUBLE類型需要8?個字節(jié)并擁有更高的精確度和更大的數(shù)值范圍，DECIMAL類型的數(shù)據(jù)將會轉(zhuǎn)換成DOUBLE類型。

?

2、?? sql 語句優(yōu)化 ??

這是test表，其中id是主鍵，多列索引(username,password,last_login),里面有10000多條數(shù)據(jù).

?

(1)???? 最左前綴原則

定義 ：最左前綴原則指的的是在sql where?字句中一些條件或表達(dá)式中出現(xiàn)的列的順序要保持和多索引的一致或以多列索引順序出現(xiàn)，只要出現(xiàn)非順序出現(xiàn)、斷層都無法利用到多列索引。

舉例說明：上面給出一個多列索引(username,password,last_login)，當(dāng)三列在where中出現(xiàn)的順序如(username,password,last_login)、(username,password)、(username)才能用到索引，如下面幾個順序(password,last_login)、(passwrod)、(last_login)---這三者不從username開始，(username,last_login)---斷層，少了password，都無法利用到索引。

因為B+tree多列索引保存的順序是按照索引創(chuàng)建的順序，檢索索引時按照此順序檢索

測試 ：以下測試不精確，這里只是說明如何才能正確按照最左前綴原則使用索引。還有的是以下的測試用的時間0.00sec看不出什么時間區(qū)別，因為數(shù)據(jù)量只有20003條，加上沒有在實體機(jī)上運(yùn)行，很多未可預(yù)知的影響因素都沒考慮進(jìn)去。當(dāng)在大數(shù)據(jù)量，高并發(fā)的時候，最左前綴原則對與提高性能方面是不可否認(rèn)的。

Ps ：最左前綴原則中where字句有or出現(xiàn)還是會遍歷全表

(1.1) 能正確的利用索引

l??Where子句表達(dá)式?順序是(username)

l??Where子句表達(dá)式?順序是(username,password)

l??Where子句表達(dá)式?順序是(username,password, last_login)

上面可以看出type=ref?是多列索引，key_len分別是24、43、83，這說明用到的索引分別是(username), (username,password), (username,password, last_login );row分別是5、1、1檢索的數(shù)據(jù)行都很少，因為這三個查詢都按照索引前綴原則，可以利用到索引。

(1.2) 不能正確的利用索引

l??Where子句表達(dá)式?順序是(password, last_login)

l??Where?子句表達(dá)式順序是(last_login)

以上的兩條語句都不是以username開始，這樣是用不了索引，通過type=all（全表掃描），key_len=null，rows都很大20146

Ps：one表里只有20003條數(shù)據(jù)，為什么出現(xiàn)20146，這是優(yōu)化器對表的一個估算值，不精確的。

l??Where?子句表達(dá)式雖然順序是(username,password, last_login)或(username,password)但第一個是有范圍’<’、’>’，’<=’，’>=’等出現(xiàn)

這個查詢很明顯是遍歷所有表，一個索引都沒用到，非第一列出現(xiàn)范圍（password列或last_login列），則能利用索引到首先出現(xiàn)范圍的一列，也就是“where username='abgvwfnt' and password >'123456'and last_login='1338251170';”或則“where username='abgvwfnt' and password >'123456'and last_login<'1338251170';”索引長度ref_len=43,索引檢索到password列，所以考慮多列索引的時候把那些查詢語句用的比較的列放在最后（或非第一位）。

l??斷層，即是where順序(username, last_login)

注意 這里的key_len=24=8*3(8是username的長度，3是utf8編碼)，rows=5，和下面一條sql語句搜索出來一樣

這個就是要的返回結(jié)果，所以可以知道斷層(username,last_login)，這樣只用到username索引，把用到索引的數(shù)據(jù)再重新檢查last_login條件，這個相對全表查詢來說還是有性能上優(yōu)化，這也是很多sql優(yōu)化文章中提到的where?范圍查詢要放在最后（這不絕對，但可以利用一部分索引）

(1.3) 如果一個查詢 where 子句中確實不需要 password 列，那就用“補(bǔ)洞”。

可以看出password列中只有這幾個值，當(dāng)然在現(xiàn)實中不可能密碼有這么多一樣的，再說數(shù)據(jù)也可能不斷更新，這里只是舉例說明補(bǔ)洞的方法

可以看出ref=83?所有的索引都用到了，type=range是因為用了in子句。

這個被“補(bǔ)洞”列中的值應(yīng)該是有限的，可預(yù)知的，如性別，其值只有男和女（加多一個不男不女也無妨）。

“補(bǔ)洞”方法也有瓶頸，當(dāng)很多列，且需要補(bǔ)洞的相應(yīng)列（可以多列）的值雖有限但很多（如中國城市）的時候，優(yōu)化器在優(yōu)化時組合起來的數(shù)量是很大，這樣的話就要做好基準(zhǔn)測試和性能分析，權(quán)衡得失，取得一個合理的優(yōu)化方法。

(1.4)like

對比就知道like操作abgvwfnt%能用到索引，%abgvwfnt%用不到

---------------------------------------------------------------------------------------------

(2)???? Order by? 優(yōu)化

(2.1)filesort優(yōu)化算法.

在mysql version()<4.1之前，優(yōu)化器采用的是filesort第一種優(yōu)化算法，先提取鍵值和指針，排序后再去提取數(shù)據(jù)，前后要搜索數(shù)據(jù)兩次，第一次若能使用索引則使用，第二次是隨機(jī)讀（當(dāng)然不同引擎也不同）。mysql version()>=4.1,更新了一個新算法，就是在第一次讀的時候也把selcet的列也讀出來，然后在sort_buffer_size中排序（不夠大則建臨時表保存排序順序），這算法只需要一次讀取數(shù)據(jù)。所以有這個廣為人傳的一個優(yōu)化方法，那就是增大sort_buffer_size。Filesort第二種算法要用到更的空間，sort_buffer_size不夠大反而會影響速度，所以mysql開發(fā)團(tuán)隊定了個變量max_length_for_sort_data，當(dāng)算法中讀出來的需要列的數(shù)據(jù)的大小超過該變量的值才使用，所以一般性能分析的時候會嘗試把max_length_for_sort_data改小。

(2.2)單獨order by?用不了索引，索引考慮加where?或加limit

先建一個索引(last_login),建的過程就不給出了

開始沒limit查詢是遍歷表的，加了limit后，索引可以使用，看key_len?和key

(2.3)where + orerby?類型，where滿足最左前綴原則，且orderby的列和where子句用到的索引的列的子集。即是(a,b,c)索引，where滿足最左前綴原則且order by中列a、b、c的任意組合

上面兩條語句明顯的區(qū)別是多了一個非索引列l(wèi)evel的排序，在extra這列對了Using filesort

筆者測試結(jié)果：where滿足最左前綴且order by中的列是該多列索引的子集時（也就是說orerby中沒最左前綴原則限制），不管是否有asc ,desc混合出現(xiàn)，都能用索引來滿足order by。

筆者測試過，因為篇幅比較大，這里就不一一列出。

Ps:很優(yōu)化博文都說order by中的列要where中出現(xiàn)的列（是索引）的順序一致，筆者認(rèn)為不夠嚴(yán)謹(jǐn)。

(2.3) where + orerby+limit

這個其實也差不多，只要where最左前綴，orderby也正確，limit在此影響不大

(2.4)如何考慮order by來建索引

這個回歸到創(chuàng)建索引的問題來，在比較常用的oder by的列和where中常用的列建立多列索引，這樣優(yōu)化起來的廣度和擴(kuò)張性都比較好，當(dāng)然如果要考慮UNION、JOIN、COUNT、IN等進(jìn)來就復(fù)雜很多了

(3)???? 隔離列

隔離列是只查詢語句中把索引列隔離出來，也就是說不能在語句中把列包含進(jìn)表達(dá)式中，如id+1=2、inet_aton('210.38.196.138')---ip轉(zhuǎn)換成整數(shù)、convert(123,char(3))---數(shù)字轉(zhuǎn)換成字符串、date函數(shù)等mysql內(nèi)置的大多函數(shù)。

非隔離列影響性能很大甚至是致命的，這也就是趕集網(wǎng)石展的《三十六軍規(guī)》中的一條，雖然他沒說明是隔離列。

以下就測試一下：

首先建立一個索引(last_login )，這里就不給出建立的代碼了，且把last_login改成整型（這里只是為了方便測試，并不是影響條件）

容易看出建的索引已起效

last_login +1=8388608非隔離列的出現(xiàn)導(dǎo)致查找的列20197，說明是遍歷整張表且索引不能使用。

這是因為這條語句要找出所有l(wèi)ast_login的數(shù)據(jù)，然后+1再和20197比較，優(yōu)化器在這方面比較差，性能很差。

所以要盡可能的把列隔離出來，如last_login +1=?8388606改成login_login=8388607,或者把計算、轉(zhuǎn)換等操作先用php函數(shù)處理過再傳遞給mysql服務(wù)器

(4)???? OR 、 IN 、 UNION ALL ，可以嘗試用 UNION ALL

(4.1)or會遍歷表就算有索引

(4.2)對于in，這個是有爭議的，網(wǎng)上很多優(yōu)化方案中都提到盡量少用in，這不全面，其實在in里面如果是常量的話，可一大膽的用in，這個也是趕集網(wǎng)石展、阿里hellodab的觀點（筆者從微博中獲知）。應(yīng)用hellodab一句話“MySQL用IN效率不好，通常是指in中嵌套一個子查詢，因為MySQL的查詢重寫可能會產(chǎn)生一個不好的執(zhí)行計劃，而如果in里面是常量的話，我認(rèn)為性能沒有任何問題，可以放心使用”---------當(dāng)然對于這個比較的話，沒有實戰(zhàn)數(shù)據(jù)的話很難辯解，就算有，影響性能的因素也很多，也許會每個dba都有不同的測試結(jié)果.這也簽名最左前綴中“補(bǔ)洞”一個方法

(4.3)UNION All?直接返回并集，可以避免去重的開銷。之所說“嘗試”用UNION All?替代?OR來優(yōu)化sql語句，因為這不是一直能優(yōu)化的了，這里只是作為一個方法去嘗試。

(5)???? 索引選擇性

索引選擇性是不重復(fù)的索引值也叫基數(shù)（cardinality）表中數(shù)據(jù)行數(shù)的比值，索引選擇性=基數(shù)/數(shù)據(jù)行，基數(shù)可以通過“show index from?表名”查看。

高索引選擇性的好處就是mysql查找匹配的時候可以過濾更多的行，唯一索引的選擇性最佳，值為1。

那么對于非唯一索引或者說要被創(chuàng)建索引的列的數(shù)據(jù)內(nèi)容很長，那就要選擇索引前綴。這里就簡單說明一下：

count(distinct(username))/count(*)就是索引選擇性的值，這里0.2太小了。

假如username列數(shù)據(jù)很長，則可以通過

select count(distinct(concat(first_name, left(last_name,? N ))/count(*)? from one;測試出接近1的索引選擇性，其中N是索引的長度，窮舉法去找出N的值，然后再建索引。

(6)???? 重復(fù)或多余索引

很多phper開始都以為建索引相對多點性能就好點，壓根沒考慮到有些索引是重復(fù)的，比如建一個(username),(username,password), (username,password,last_login),很明顯第一個索引是重復(fù)的，因為后兩者都能滿足其功能。

要有個意識就是，在滿足功能需求的情況下建最少索引。對于INNODB引擎的索引來說，每次修改數(shù)據(jù)都要把主鍵索引，輔助索引中相應(yīng)索引值修改，這可能會出現(xiàn)大量數(shù)據(jù)遷移，分頁，以及碎片的出現(xiàn)。

3 、 系統(tǒng)配置與維護(hù)優(yōu)化

(1)???? 重要的一些變量

l??key_buffer_size索引塊緩存區(qū)大小,?針對MyISAM存儲引擎,該值越大,性能越好.但是超過操作系統(tǒng)能承受的最大值,反而會使mysql變得不穩(wěn)定. ----這是很重要的參數(shù)

l??sort_buffer_size?這是索引在排序緩沖區(qū)大小，若排序數(shù)據(jù)大小超過該值，則創(chuàng)建臨時文件，注意和myisam_sort_buffer_size的區(qū)別----這是很重要的參數(shù)

l??read_rnd_buffer_size當(dāng)排序后按排序后的順序讀取行時，則通過該緩沖區(qū)讀取行，避免搜索硬盤。將該變量設(shè)置為較大的值可以大大改進(jìn)ORDER BY的性能。但是，這是為每個客戶端分配的緩沖區(qū)，因此你不應(yīng)將全局變量設(shè)置為較大的值。相反，只為需要運(yùn)行大查詢的客戶端更改會話變量

l??join_buffer_size用于表間關(guān)聯(lián)(join)的緩存大小

l??tmp_table_size緩存表的大小

l??table_cache允許?MySQL?打開的表的最大個數(shù)，并且這些都cache在內(nèi)存中

l??delay_key_write針對MyISAM存儲引擎,延遲更新索引.意思是說,update記錄時,先將數(shù)據(jù)up到磁盤,但不up索引,將索引存在內(nèi)存里,當(dāng)表關(guān)閉時,將內(nèi)存索引,寫到磁盤

更多參數(shù)查看 http://www.phpben.com/?post=70

(2)???? optimize 、 Analyze 、 check 、 repair 維護(hù)操作

l??optimize?數(shù)據(jù)在插入，更新，刪除的時候難免一些數(shù)據(jù)遷移，分頁，之后就出現(xiàn)一些碎片，久而久之碎片積累起來影響性能，這就需要DBA定期的優(yōu)化數(shù)據(jù)庫減少碎片，這就通過optimize命令。

如對MyisAM表操作：optimize table?表名

對于InnoDB表是不支持optimize操作，否則提示“Table does not support optimize, doing recreate + analyze instead”，當(dāng)然也可以通過命令：alter table one type=innodb;?來替代。

l??Analyze?用來分析和存儲表的關(guān)鍵字的分布，使得系統(tǒng)獲得準(zhǔn)確的統(tǒng)計信息，影響?SQL?的執(zhí)行計劃的生成。對于數(shù)據(jù)基本沒有發(fā)生變化的表，是不需要經(jīng)常進(jìn)行表分析的。但是如果表的數(shù)據(jù)量變化很明顯，用戶感覺實際的執(zhí)行計劃和預(yù)期的執(zhí)行計劃不?同的時候，執(zhí)行一次表分析可能有助于產(chǎn)生預(yù)期的執(zhí)行計劃。

Analyze table?表名

l??Check檢查表或者視圖是否存在錯誤，對?MyISAM?和?InnoDB?存儲引擎的表有作用。對于?MyISAM?存儲引擎的表進(jìn)行表檢查，也會同時更新關(guān)鍵字統(tǒng)計數(shù)據(jù)

l??Repair??optimize需要有足夠的硬盤空間，否則可能會破壞表，導(dǎo)致不能操作，那就要用上repair，注意INNODB不支持repair操作

以上的操作出現(xiàn)的都是如下這是check

其中op是option?可以是repair check optimize，msg_type?表示信息類型，msg_text?表示信息類型，這里就說明表的狀態(tài)正常。如在innodb表使用repair就出現(xiàn)note | The storage engine for the table doesn't support repair

注意：以上操作最好在數(shù)據(jù)庫訪問量最低的時候操作，因為涉及到很多表鎖定，掃描，數(shù)據(jù)遷移等操作，否則可能導(dǎo)致一些功能無法正常使用甚至數(shù)據(jù)庫崩潰。

(3) 表結(jié)構(gòu)的更新與維護(hù)

l??改表結(jié)構(gòu)。當(dāng)要在數(shù)據(jù)量千萬級的數(shù)據(jù)表中使用alter更改表結(jié)構(gòu)的時候，這是一個棘手問題。一種方法是在低并發(fā)低訪問量的時候用平常的alter更改表。另外一種就是建另一個與要修改的表，這個表除了要修改的結(jié)構(gòu)屬性外其他的和原表一模一樣，這樣就能得到一個相應(yīng)的.frm文件，然后用flush with read lock?鎖定讀，然后覆蓋用新建的.frm文件覆蓋原表的.frm，最后unlock table?釋放表。

l??建立新的索引。一般方法這里不說。

1、??創(chuàng)建沒索引的a表，導(dǎo)入數(shù)據(jù)形成.MYD文件。

2、??創(chuàng)建包括索引b表，形成.FRM和.MYI文件

3、??鎖定讀寫

4、??把b表的.FRM和.MYI文件改成a表名字

5、??解鎖

6、??用repair創(chuàng)建索引。

這個方法對于大表也是很有效的。這也是為什么很多dba堅持說“先導(dǎo)數(shù)據(jù)庫在建索引，這樣效率更快”

l??定期檢查mysql服務(wù)器

定期使用show status、show processlist等命令檢查數(shù)據(jù)庫。這里就不細(xì)說，這說起來也篇幅是比較大的，筆者對這個也不是很了解

第四部分：圖說 mysql 查詢執(zhí)行流程

?

?

?1、??查詢緩存，判斷sql語句是否完全匹配，再判斷是否有權(quán)限，兩個判斷為假則到解析器解析語句，為真則提取數(shù)據(jù)結(jié)果返回給用戶。

2、??解析器解析。解析器先詞法分析，語法分析，檢查錯誤比如引號有沒閉合等，然后生成解析樹。

3、??預(yù)處理。預(yù)處理解決解析器無法決解的語義，如檢查表和列是否存在，別名是否有錯，生成新的解析樹。

4、??優(yōu)化器做大量的優(yōu)化操作。

5、??生成執(zhí)行計劃。

6、??查詢執(zhí)行引擎，負(fù)責(zé)調(diào)度引擎獲取相應(yīng)數(shù)據(jù)

7、??返回結(jié)果。

?

?

?

?

這篇博文準(zhǔn)備，寫，將用了一個月時間！終于寫完，但真的學(xué)了很多東西！ 有紕漏請聯(lián)系：benwin(bw@7bus.net/445235728@qq.com)

?

?

參考：

? http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html

http://www.cnblogs.com/oldhorse/archive/2009/11/16/1604009.html

http://blog.csdn.net/zuiaituantuan/article/details/5909334

http://www.codinglabs.org/html/theory-of-mysql-index.html

http://isky000.com/database/mysql_order_by_implement

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

?

http://www.docin.com/p-211669085.html ?