由淺入深探究 MySQL索引結構原理、效能分析與優化

發表於2015-05-25

MySql索引優化

第一部分：基礎知識：

索引

官方介紹索引是幫助MySQL高效獲取資料的資料結構。筆者理解索引相當於一本書的目錄，通過目錄就知道要的資料在哪裡，不用一頁一頁查閱找出需要的資料。關鍵字index

————————————————————-

唯一索引

強調唯一，就是索引值必須唯一，關鍵字unique index

建立索引：

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

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

刪除索引：

1、 drop index 索引名 on 表名;

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

————————————————————-

主鍵

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

主鍵建立：

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

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

————————————————————-

全文索引

InnoDB不支援，Myisam支援效能比較好，一般在 CHAR、VARCHAR 或 TEXT 列上建立。

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

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

——————————

單列索引與多列索引

索引可以是單列索引也可以是多列索引(也叫複合索引)。按照上面形式建立出來的索引是單列索引，現在先看看建立多列索引：

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;

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)可以當做a或(a, b)的索引來使用，但和b、c或(b,c)的索引來使用這是一個最左字首的優化方法，在後面會有詳細的介紹，你只要知道有這樣兩個概念

————————————————————-

聚集索引

一種索引，該索引中鍵值的邏輯順序決定了表中相應行的物理順序。聚集索引確定表中資料的物理順序。Mysql中myisam表是沒有聚集索引的，innodb有(主鍵就是聚集索引)，聚集索引在下面介紹innodb結構的時有詳細介紹。

————————————————————-

檢視錶的索引

通過命令：Show index from 表名

如：

mysql> show index from test3;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+----+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part |
Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+----+
| test3 | 0 | PRIMARY | 1 | id | A | 0 | NULL |
NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+

mysql> show index from test3;

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+----+

Packed | Null | Index_type | Comment |

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+----+

| test3 | 0 | PRIMARY | 1 | id | A | 0 | NULL |

NULL | | BTREE | |

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+

Table：表名

Key_name：什麼型別索引(這了是主鍵)

Column_name：索引列的欄位名

Cardinality：索引基數，很關鍵的一個引數，平均數值組=索引基數/表總資料行，平均數值組越接近1就越有可能利用索引

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

其他的就不詳細介紹，更多：

第二部分：MYISAM和INNODB索引結構

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

B-tree結構檢視

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

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

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

(3)所有關鍵字必須唯一值(這也是建立myisam 和innodb表必須要主鍵的原因)，每個節點包含一個說明該節點多少個關鍵字，如上圖第二行的i和n

(4)節點：

l 每個節點最可以有m個子節點。

l 根節點若非葉子節點，至少2個子節點，最多m個子節點

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

(5)關鍵字：

l 根節點的關鍵字個數1~m-1

l 非根非葉子節點的關鍵字個數[m/2]-1~m-1,如m=3，則該類節點關鍵字個數：2-1~2

(6)關鍵字數k和指向子節點個數指標p的關係：

l k+1=p ，注意根據儲存資料的具體需求，左右指標為空時要有標誌位表示沒有

B+tree結構示意圖如下：

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

l 非葉子結點的子樹指標與關鍵字個數相同

l 為所有葉子結點增加一個鏈指標(紅點標誌的箭頭)

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

l 非葉子結點的子樹指標與關鍵字個數相同

l 為所有葉子結點增加一個鏈指標(紅點標誌的箭頭)

2、 MyisAM索引結構

MyisAM索引用的B+tree來儲存資料，MyisAM索引的指標指向的是鍵值的地址，地址儲存的是資料，如下圖：

(1)結構講解：上圖3階樹，主鍵是Col2，Col值就是改行資料儲存的實體地址，其中紅色部分是說明標註。

l 1標註部分也許會迷惑，前面不是說關鍵字15右指標的指向鍵值要大於15，怎麼下面還有15關鍵字？因為B+tree的所以葉子節點包含所有關鍵字且是按照升序排列(主鍵索引唯一，輔助索引可以不唯一)，所以等於關鍵字的資料值在右子樹

l 2標註是相應關鍵字儲存對應資料的實體地址，注意這也是之後和InnoDB索引不同的地方之一

l 2標註也是一個所說MyiAM表的索引和資料是分離的，索引儲存在”表名.MYI”檔案內，而資料儲存在“表名.MYD”檔案內，2標註的實體地址就是“表名.MYD”檔案內相應資料的實體地址。(InnoDB表的索引檔案和資料檔案在一起)

l 輔助索引和主鍵索引沒什麼大的區別，輔助索引的索引值是可以重複的(但InnoDB輔助索引和主鍵索引有很明顯的區別，這裡先提醒注意一下)

3、 Annode索引結構

(1)首先有一個表，內容和主鍵索引結構如下兩圖：

Col1 Col2 Col3

1 15 phpben

2 20 mhycoe

3 23 phpyu

4 25 bearpa

5 40 phpgoo

6 45 phphao

7 48 phpxue

……

結構上：由上圖可以看出InnoDB的索引結構很MyisAM的有很明顯的區別

l MyisAM表的索引和資料是分開的，用指標指向資料的實體地址，而InnoDB表中索引和資料是儲存在一起。看紅框1可一看出一行資料都儲存了。

l 還有一個上圖多了三行的隱藏資料列(虛線表)，這是因為MyisAM不支援事務，InnoDB處理事務在效能上併發控制上比較好，看圖中的紅框2中的DB_TRX_ID是事務ID，自動增長；db_roll_ptr是回滾指標，用於事務出錯時資料回滾恢復；db_row_id是記錄行號，這個值其實在主鍵索引中就是主鍵值，這裡標出重複是為了容易介紹，還有的是若不是主鍵索引(輔助索引)，db_row_id會找表中unique的列作為值，若沒有unique列則系統自動建立一個。關於InnoDB跟多事務MVCC點此：http://www.phpben.com/?post=72

(2)加入上表中Col1是主鍵(下圖示錯)，而Col2是輔助索引，則相應的輔助索引結構圖：

可以看出InnoDB輔助索引並沒有儲存相應的所有列資料，而是儲存了主鍵的鍵值(圖中1、2、3….)這樣做利弊也是很明顯：

l 在已有主鍵索引，避免資料冗餘，同時在修改資料的時候只需修改輔助索引值。

l 但輔助索引查詢資料事要檢索兩次，先找到相應的主鍵索引值然後在去檢索主鍵索引找到對應的資料。這也是網上很多mysql效能優化時提到的“主鍵儘可能簡短”的原因，主鍵越長輔助索引也就越大，當然主鍵索引也越大。

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

l MyisAM支援全文索引(FULLTEXT)、壓縮索引，InnoDB不支援

l AnnoDB支援事務，MyisAM不支援

l MyisAM順序儲存資料，索引葉子節點儲存對應資料行地址，輔助索引很主鍵索引相差無幾；AnnoDB主鍵節點同時儲存資料行，其他輔助索引儲存的是主鍵索引的值

l MyisAM鍵值分離，索引載入記憶體(key_buffer_size),資料快取依賴作業系統；InnoDB鍵值一起儲存，索引與資料一起載入InnoDB緩衝池

l MyisAM主鍵(唯一)索引按升序來儲存儲存，InnoDB則不一定

l MyisAM索引的基數值(Cardinality，show index 命令可以看見)是精確的，InnoDB則是估計值。這裡涉及到資訊統計的知識，MyisAM統計資訊是儲存磁碟中，在alter表或Analyze table操作更新此資訊，而InnoDB則是在表第一次開啟的時候估計值儲存在快取區內

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

第三部分：MYSQL優化

mysql優化是一個重大課題之一，這裡會重點詳細的介紹mysql優化，包括表資料型別選擇，sql語句優化，系統配置與維護優化三類。

1、表資料型別選擇

(1)能小就用小。表資料型別第一個原則是：使用能正確的表示和儲存資料的最短型別。這樣可以減少對磁碟空間、記憶體、cpu快取的使用。

(2)避免用NULL，這個也是網上優化技術博文傳的最多的一個。理由是額外增加位元組，還有使索引，索引統計和值更復雜。很多還忽略一

個count(列)的問題，count(列)是不會統計列值為null的行數。更多關於NULL可參考：http://www.phpben.com/?post=71

(3)字串如何選擇char和varchar？一般phper能想到就是char是固定大小，varchar能動態儲存資料。這裡整理一下這兩者的區別：

屬性 Char Varchar

值域大小最長字元數是255(不是位元組)，不管什麼編碼，超過此值則自動擷取255個字元儲存並沒有報錯。 65535個位元組，開始兩位儲存長度，超過255個字元，用2位儲存長度，否則1位，具體字元長度根據編碼來確定，如utf8，則字元最長是21845個

如何處理字串末尾空格去掉末尾空格，取值出來比較的時候自動加上進行比較 Version<=4.1，字串末尾空格被刪掉，version>5.0則保留

儲存空間固定空間，比喻char(10)不管字串是否有10個字元都分配10個字元的空間 Varchar內節約空間，但更新可能發生變化，若varchar(10),開始若儲存5個字元，當update成7個時有myisam可能把行拆開，innodb可能分頁，這樣開銷就增大

適用場合適用於儲存很短或固定或長度相似字元，如MD5加密的密碼char(33)、暱稱char(8)等當最大長度遠大於平均長度並且發生更新的時候。

注意當一些英文或資料的時候，最好用每個字元用位元組少的型別，如latin1

(4)整型、整形優先原則

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

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

很多程式設計師在設計資料表的時候很習慣的用int，壓根不考慮這個問題

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

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

整形優先原則：能用整形的不用其他型別替換，如ip可以轉換成整形儲存，如商品價格‘50.00元’則儲存成50

(5)精確度與空間的轉換。在儲存相同數值範圍的資料時，浮點數型別通常都會比DECIMAL型別使用更少的空間。FLOAT欄位使用4位元組儲存

資料。DOUBLE型別需要8 個位元組並擁有更高的精確度和更大的數值範圍，DECIMAL型別的資料將會轉換成DOUBLE型別。

2、 sql語句優化

mysql> create table one (
id smallint(10) not null auto_increment primary key,
username char(8) not null,
password char(4) not null,
`level` tinyint (1) default 0,
last_login char(15) not null,
index(username,password,last_login))engine=innodb;

mysql> create table one (

id smallint(10) not null auto_increment primary key,

username char(8) not null,

password char(4) not null,

`level` tinyint (1) default 0,

last_login char(15) not null,

index(username,password,last_login))engine=innodb;

這是test表，其中id是主鍵，多列索引(username,password,last_login),裡面有10000多條資料.

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null |
Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+
| one | 0 | PRIMARY | 1 | id | A |20242 | NULL | NULL | |
BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+
| one | 1 | username | 1 | username | A |10121 | NULL | NULL | |
BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+
| one | 1 | username | 2 | password | A |10121 | NULL | NULL | YES |
BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+
| one | 1 | username | 3 | last_login | A |20242 | NULL | NULL | |
BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+

Index_type | Comment |

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+

| one | 0 | PRIMARY | 1 | id | A |20242 | NULL | NULL | |

BTREE | |

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+

| one | 1 | username | 1 | username | A |10121 | NULL | NULL | |

BTREE | |

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+

| one | 1 | username | 2 | password | A |10121 | NULL | NULL | YES |

BTREE | |

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+

| one | 1 | username | 3 | last_login | A |20242 | NULL | NULL | |

BTREE | |

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+

(1) 最左字首原則

定義：最左字首原則指的的是在sql where 字句中一些條件或表示式中出現的列的順序要保持和多索引的一致或以多列索引順序出現，只要出現非順序出現、斷層都無法利用到多列索引。

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

因為B+tree多列索引儲存的順序是按照索引建立的順序，檢索索引時按照此順序檢索

測試：以下測試不精確，這裡只是說明如何才能正確按照最左字首原則使用索引。還有的是以下的測試用的時間0.00sec看不出什麼時間區別，因為資料量只有20003條，加上沒有在實體機上執行，很多未可預知的影響因素都沒考慮進去。當在大資料量，高併發的時候，最左字首原則對與提高效能方面是不可否認的。

Ps：最左字首原則中where字句有or出現還是會遍歷全表

(1.1)能正確的利用索引

l Where子句表示式順序是(username)

mysql> explain select * from one where username='abgvwfnt';
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref |rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
| 1 | SIMPLE | one | ref | username | username | 24 | const |5 | Using where |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from one where username='abgvwfnt';

+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+

+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+

| 1 | SIMPLE | one | ref | username | username | 24 | const |5 | Using where |

+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+

1 row in set (0.00 sec)

l Where子句表示式順序是(username,password)

mysql> explain select * from one where username='abgvwfnt' and password='123456';
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-------------+
| 1 | SIMPLE | one | ref | username | username | 43 | const,const | 1 | Using where |
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from one where username='abgvwfnt' and password='123456';

+----+-------------+-------+------+---------------+----------+---------+-------------+------+-------------+

+----+-------------+-------+------+---------------+----------+---------+-------------+------+-------------+

| 1 | SIMPLE | one | ref | username | username | 43 | const,const | 1 | Using where |

+----+-------------+-------+------+---------------+----------+---------+-------------+------+-------------+

1 row in set (0.00 sec)

l Where子句表示式順序是(username,password, last_login)

mysql> explain select * from one where username='abgvwfnt' and password='123456'and last_login='1338251170';
+----+-------------+-------+------+---------------+----------+---------+-------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref| rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------------------+------+-------------+
| 1 | SIMPLE | one | ref | username | username | 83 | const,const,const | 1 | Using where |
+----+-------------+-------+------+---------------+----------+---------+-------------------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from one where username='abgvwfnt' and password='123456'and last_login='1338251170';

+----+-------------+-------+------+---------------+----------+---------+-------------------+------+-------------+

+----+-------------+-------+------+---------------+----------+---------+-------------------+------+-------------+

| 1 | SIMPLE | one | ref | username | username | 83 | const,const,const | 1 | Using where |

+----+-------------+-------+------+---------------+----------+---------+-------------------+------+-------------+

1 row in set (0.00 sec)

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

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

l Where子句表示式順序是(password, last_login)

mysql> explain select * from one where password='123456'and last_login='1338251170';
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | one | ALL | NULL | NULL | NULL | NULL | 20146 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from one where password='123456'and last_login='1338251170';

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

1 row in set (0.00 sec)

l Where 子句表示式順序是(last_login)

mysql> explain select * from one where last_login='1338252525';
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | one | ALL | NULL | NULL | NULL | NULL | 20146 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from one where last_login='1338252525';

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

1 row in set (0.00 sec)

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

Ps：one表裡只有20003條資料，為什麼出現20146，這是優化器對錶的一個估算值，不精確的。

l Where 子句表示式雖然順序是(username,password, last_login)或(username,password)但第一個是有範圍’<’、’>’，’<=’，’>=’等出現

mysql> explain select * from one where username>'abgvwfnt' and password ='123456'and last_login='1338251170';
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | one | ALL | username | NULL | NULL | NULL | 20146 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from one where username>'abgvwfnt' and password ='123456'and last_login='1338251170';

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

1 row in set (0.00 sec)

這個查詢很明顯是遍歷所有表，一個索引都沒用到，非第一列出現範圍(password列或last_login列)，則能利用索引到首先出現範圍的一列，也就是“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)

mysql> explain select * from one where username='abgvwfnt' and last_login='1338252525';
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
| 1 | SIMPLE | one | ref | username | username | 24 | const |5 | Using where |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from one where username='abgvwfnt' and last_login='1338252525';

+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+

+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+

| 1 | SIMPLE | one | ref | username | username | 24 | const |5 | Using where |

+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+

1 row in set (0.00 sec)

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

mysql> select * from one where username='abgvwfnt';
+-------+----------+----------+-------+------------+
| id | username | password | level | last_login |
+-------+----------+----------+-------+------------+
| 3597 | abgvwfnt | 234567 | 0 | 1338251420 |
| 7693 | abgvwfnt | 456789 | 0 | 1338251717 |
| 11789 | abgvwfnt | 456789 | 0 | 1338251992 |
| 15885 | abgvwfnt | 456789 | 0 | 1338252258 |
| 19981 | abgvwfnt | 456789 | 0 | 1338252525 |
+-------+----------+----------+-------+------------+
5 rows in set (0.00 sec)

mysql> select * from one where username='abgvwfnt' and last_login='1338252525';
+-------+----------+----------+-------+------------+
| id | username | password | level | last_login |
+-------+----------+----------+-------+------------+
| 19981 | abgvwfnt | 456789 | 0 | 1338252525 |
+-------+----------+----------+-------+------------+
1 row in set (0.00 sec)

mysql> select * from one where username='abgvwfnt';

+-------+----------+----------+-------+------------+

+-------+----------+----------+-------+------------+

| 3597 | abgvwfnt | 234567 | 0 | 1338251420 |

| 7693 | abgvwfnt | 456789 | 0 | 1338251717 |

| 11789 | abgvwfnt | 456789 | 0 | 1338251992 |

| 15885 | abgvwfnt | 456789 | 0 | 1338252258 |

| 19981 | abgvwfnt | 456789 | 0 | 1338252525 |

+-------+----------+----------+-------+------------+

5 rows in set (0.00 sec)

mysql> select * from one where username='abgvwfnt' and last_login='1338252525';

+-------+----------+----------+-------+------------+

+-------+----------+----------+-------+------------+

| 19981 | abgvwfnt | 456789 | 0 | 1338252525 |

+-------+----------+----------+-------+------------+

1 row in set (0.00 sec)

這個就是要的返回結果，所以可以知道斷層(username,last_login)，這樣只用到username索引，把用到索引的資料再重新檢查last_login條件，這個相對全表查詢來說還是有效能上優化，這也是很多sql優化文章中提到的where 範圍查詢要放在最後(這不絕對，但可以利用一部分索引)

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

mysql> select distinct(password) from one;
+----------+
| password |
+----------+
| 234567 |
| 345678 |
| 456789 |
| 123456 |
+----------+
4 rows in set (0.08 sec)

mysql> select distinct(password) from one;

+----------+

| password |

+----------+

| 234567 |

| 345678 |

| 456789 |

| 123456 |

+----------+

4 rows in set (0.08 sec)

可以看出password列中只有這幾個值，當然在現實中不可能密碼有這麼多一樣的，再說資料也可能不斷更新，這裡只是舉例說明補洞的方法

mysql> explain select * from one where username='abgvwfnt' and password in('123456','234567','345678','456789')
and last_login='1338251170';
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | one | range | username | username| 83 | NULL |4 | Using where |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from one where username='abgvwfnt' and password in('123456','234567','345678','456789')

and last_login='1338251170';

+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+

+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+

+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+

1 row in set (0.00 sec)

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

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

“補洞”方法也有瓶頸，當很多列，且需要補洞的相應列(可以多列)的值雖有限但很多(如中國城市)的時候，優化器在優化時組合起來的數量是很大，這樣的話就要做好基準測試和效能分析，權衡得失，取得一個合理的優化方法。

(1.4)like

mysql> explain select * from one where username like 'abgvwfnt%';
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref |
rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | one | range | username | username | 24 | NULL |
5 | Using where |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from one where username like '%abgvwfnt%';
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | one | ALL | NULL | NULL | NULL | NULL | 20259 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.01 sec)

mysql> explain select * from one where username like 'abgvwfnt%';

+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+

rows | Extra |

+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+

5 | Using where |

+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+

1 row in set (0.00 sec)

mysql> explain select * from one where username like '%abgvwfnt%';

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

1 row in set (0.01 sec)

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

———————————————————————————————

(2) Order by 優化

(2.1)filesort優化演算法.

在mysql version()<4.1之前，優化器採用的是filesort第一種優化演算法，先提取鍵值和指標，排序後再去提取資料，前後要搜尋資料兩次，第一次若能使用索引則使用，第二次是隨機讀(當然不同引擎也不同)。mysql version()>=4.1,更新了一個新演算法，就是在第一次讀的時候也把selcet的列也讀出來，然後在sort_buffer_size中排序(不夠大則建臨時表儲存排序順序)，這演算法只需要一次讀取資料。所以有這個廣為人傳的一個優化方法，那就是增大sort_buffer_size。Filesort第二種演算法要用到更的空間，sort_buffer_size不夠大反而會影響速度，所以mysql開發團隊定了個變數max_length_for_sort_data，當演算法中讀出來的需要列的資料的大小超過該變數的值才使用，所以一般效能分析的時候會嘗試把max_length_for_sort_data改小。

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

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

mysql> explain select * from one order by last_login desc;
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows
| Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------+
| 1 | SIMPLE | one | ALL | NULL | NULL | NULL | NULL | 2046
3 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------+
1 row in set (0.00 sec)

mysql> explain select * from one order by last_login desc limit 10;
+----+-------------+-------+-------+---------------+------------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+-------+-------+---------------+------------+---------+------+------+-------+
| 1 | SIMPLE | one | index | NULL | last_login | 4 | NULL
| 10 | |
+----+-------------+-------+-------+---------------+------------+---------+------+------+-------+
1 row in set (0.00 sec)

mysql> explain select * from one order by last_login desc;

+----+-------------+-------+------+---------------+------+---------+------+-------+----------------+

| Extra |

+----+-------------+-------+------+---------------+------+---------+------+-------+----------------+

| 1 | SIMPLE | one | ALL | NULL | NULL | NULL | NULL | 2046

3 | Using filesort |

+----+-------------+-------+------+---------------+------+---------+------+-------+----------------+

1 row in set (0.00 sec)

mysql> explain select * from one order by last_login desc limit 10;

+----+-------------+-------+-------+---------------+------------+---------+------+------+-------+

| rows | Extra |

+----+-------------+-------+-------+---------------+------------+---------+------+------+-------+

| 10 | |

+----+-------------+-------+-------+---------------+------------+---------+------+------+-------+

1 row in set (0.00 sec)

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

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

mysql> explain select * from one where username='abgvwfnt' and password ='123456
' and last_login='1338251001' order by password desc,last_login desc;

+----+-------------+-------+------+---------------+----------+---------+-------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref

| rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------------------+------+-------------+
| 1 | SIMPLE | one | ref | username | username | 83 | const,c
onst,const | 1 | Using where |
+----+-------------+-------+------+---------------+----------+---------+-------------------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from one where username='abgvwfnt' and password ='123456
' and last_login='1338251001' order by password desc,level desc;
+----+-------------+-------+------+---------------+----------+---------+-------------------+------+----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref| rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------------------+------+-----------------------------+
| 1 | SIMPLE | one | ref | username | username | 83 | const,c
onst,const | 1 | Using where; Using filesort |
+----+-------------+-------+------+---------------+----------+---------+-------------------+------+-----------------------------+

1 row in set (0.00 sec)

mysql> explain select * from one where username='abgvwfnt' and password ='123456

' and last_login='1338251001' order by password desc,last_login desc;

+----+-------------+-------+------+---------------+----------+---------+-------------------+------+-------------+

| rows | Extra |

+----+-------------+-------+------+---------------+----------+---------+-------------------+------+-------------+

| 1 | SIMPLE | one | ref | username | username | 83 | const,c

onst,const | 1 | Using where |

+----+-------------+-------+------+---------------+----------+---------+-------------------+------+-------------+

1 row in set (0.00 sec)

mysql> explain select * from one where username='abgvwfnt' and password ='123456

' and last_login='1338251001' order by password desc,level desc;

+----+-------------+-------+------+---------------+----------+---------+-------------------+------+----------------------------+

+----+-------------+-------+------+---------------+----------+---------+-------------------+------+-----------------------------+

| 1 | SIMPLE | one | ref | username | username | 83 | const,c

onst,const | 1 | Using where; Using filesort |

+----+-------------+-------+------+---------------+----------+---------+-------------------+------+-----------------------------+

1 row in set (0.00 sec)

上面兩條語句明顯的區別是多了一個非索引列level的排序，在extra這列對了Using filesort

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

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

Ps:很優化博文都說order by中的列要where中出現的列(是索引)的順序一致，筆者認為不夠嚴謹。

(2.3) where + orerby+limit

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

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

這個迴歸到建立索引的問題來，在比較常用的oder by的列和where中常用的列建立多列索引，這樣優化起來的廣度和擴張性都比較好，當然如果要考慮UNION、JOIN、COUNT、IN等進來就複雜很多了

(3) 隔離列

隔離列是隻查詢語句中把索引列隔離出來，也就是說不能在語句中把列包含進表示式中，如id+1=2、inet_aton(‘210.38.196.138’)—ip轉換成整數、convert(123,char(3))—數字轉換成字串、date函式等mysql內建的大多函式。

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

以下就測試一下：

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

mysql> explain select * from one where last_login = 8388605;
+----+-------------+-------+------+---------------+------------+---------+-------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------------+---------+-------+-------+-------------+
| 1 | SIMPLE | one | ref | last_login | last_login | 3 | const
| 1 | Using where |
+----+-------------+-------+------+---------------+------------+---------+-------+-------+-------------+
1 row in set, 1 warning (0.00 sec)
容易看出建的索引已起效
mysql> explain select * from one where last_login +1= 8388606 ;
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows
| Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | one | ALL | NULL | NULL | NULL | NULL | 2049
7 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from one where last_login = 8388605;

+----+-------------+-------+------+---------------+------------+---------+-------+-------+-------------+

+----+-------------+-------+------+---------------+------------+---------+-------+-------+-------------+

| 1 | SIMPLE | one | ref | last_login | last_login | 3 | const

| 1 | Using where |

+----+-------------+-------+------+---------------+------------+---------+-------+-------+-------------+

1 row in set, 1 warning (0.00 sec)

容易看出建的索引已起效

mysql> explain select * from one where last_login +1= 8388606 ;

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

| Extra |

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

| 1 | SIMPLE | one | ALL | NULL | NULL | NULL | NULL | 2049

7 | Using where |

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

1 row in set (0.00 sec)

last_login +1=8388608非隔離列的出現導致查詢的列20197，說明是遍歷整張表且索引不能使用。

這是因為這條語句要找出所有last_login的資料，然後+1再和20197比較，優化器在這方面比較差，效能很差。

所以要儘可能的把列隔離出來，如last_login +1=8388606改成login_login=8388607,或者把計算、轉換等操作先用php函式處理過再傳遞給mysql伺服器

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

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

mysql> explain select * from one where username = 'abgvwfnt' or password='123456';
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | one | ALL | username | NULL | NULL | NULL | 20259 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from one where username = 'abgvwfnt' or password='123456';

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

1 row in set (0.00 sec)

(4.2)對於in，這個是有爭議的，網上很多優化方案中都提到儘量少用in，這不全面，其實在in裡面如果是常量的話，可一大膽的用in，這個也是趕集網石展、阿里hellodab的觀點(筆者從微博中獲知)。應用hellodab一句話“MySQL用IN效率不好，通常是指in中巢狀一個子查詢，因為MySQL的查詢重寫可能會產生一個不好的執行計劃，而如果in裡面是常量的話，我認為效能沒有任何問題，可以放心使用”———當然對於這個比較的話，沒有實戰資料的話很難辯解，就算有，影響效能的因素也很多，也許會每個dba都有不同的測試結果.這也簽名最左字首中“補洞”一個方法

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

(5) 索引選擇性

索引選擇性是不重複的索引值也叫基數(cardinality)表中資料行數的比值，索引選擇性=基數/資料行，基數可以通過“show index from 表名”檢視。

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

那麼對於非唯一索引或者說要被建立索引的列的資料內容很長，那就要選擇索引字首。這裡就簡單說明一下：

mysql> select count(distinct(username))/count(*) from one;
+------------------------------------+
| count(distinct(username))/count(*) |
+------------------------------------+
| 0.2047 |
+------------------------------------+
1 row in set (0.09 sec)

mysql> select count(distinct(username))/count(*) from one;

+------------------------------------+

| count(distinct(username))/count(*) |

+------------------------------------+

| 0.2047 |

+------------------------------------+

1 row in set (0.09 sec)

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

假如username列資料很長，則可以通過

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

(6) 重複或多餘索引

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

要有個意識就是，在滿足功能需求的情況下建最少索引。對於INNODB引擎的索引來說，每次修改資料都要把主鍵索引，輔助索引中相應索引值修改，這可能會出現大量資料遷移，分頁，以及碎片的出現。

3、系統配置與維護優化

(1) 重要的一些變數

l key_buffer_size索引塊快取區大小, 針對MyISAM儲存引擎,該值越大,效能越好.但是超過作業系統能承受的最大值,反而會使mysql變得不穩定. —-這是很重要的引數

l sort_buffer_size 這是索引在排序緩衝區大小，若排序資料大小超過該值，則建立臨時檔案，注意和myisam_sort_buffer_size的區別—-這是很重要的引數

l read_rnd_buffer_size當排序後按排序後的順序讀取行時，則通過該緩衝區讀取行，避免搜尋硬碟。將該變數設定為較大的值可以大大改進ORDER BY的效能。但是，這是為每個客戶端分配的緩衝區，因此你不應將全域性變數設定為較大的值。相反，只為需要執行大查詢的客戶端更改會話變數

l join_buffer_size用於表間關聯(join)的快取大小

l tmp_table_size快取表的大小

l table_cache允許 MySQL 開啟的表的最大個數，並且這些都cache在記憶體中

l delay_key_write針對MyISAM儲存引擎,延遲更新索引.意思是說,update記錄時,先將資料up到磁碟,但不up索引,將索引存在記憶體裡,當表關閉時,將記憶體索引,寫到磁碟

更多引數檢視http://www.phpben.com/?post=70

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

l optimize 資料在插入，更新，刪除的時候難免一些資料遷移，分頁，之後就出現一些碎片，久而久之碎片積累起來影響效能，這就需要DBA定期的優化資料庫減少碎片，這就通過optimize命令。

如對MyisAM表操作：optimize table 表名

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

l Analyze 用來分析和儲存表的關鍵字的分佈，使得系統獲得準確的統計資訊，影響 SQL 的執行計劃的生成。對於資料基本沒有發生變化的表，是不需要經常進行表分析的。但是如果表的資料量變化很明顯，使用者感覺實際的執行計劃和預期的執行計劃不同的時候，執行一次表分析可能有助於產生預期的執行計劃。

Analyze table 表名

l Check檢查表或者檢視是否存在錯誤，對 MyISAM 和 InnoDB 儲存引擎的表有作用。對於 MyISAM 儲存引擎的表進行表檢查，也會同時更新關鍵字統計資料

l Repair optimize需要有足夠的硬碟空間，否則可能會破壞表，導致不能操作，那就要用上repair，注意INNODB不支援repair操作

以上的操作出現的都是如下這是check

+———-+——-+————–+————-+

+———-+——-+————–+————-+

+———-+——-+————–+————-+

其中op是option 可以是repair check optimize，msg_type 表示資訊型別，msg_text 表示資訊型別，這裡就說明表的狀態正常。如在innodb表使用repair就出現note | The storage engine for the table doesn’t support repair

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

(3)表結構的更新與維護

l 改表結構。當要在資料量千萬級的資料表中使用alter更改表結構的時候，這是一個棘手問題。一種方法是在低併發低訪問量的時候用平常的alter更改表。另外一種就是建另一個與要修改的表，這個表除了要修改的結構屬性外其他的和原表一模一樣，這樣就能得到一個相應的.frm檔案，然後用flush with read lock 鎖定讀，然後覆蓋用新建的.frm檔案覆蓋原表的.frm，最後unlock table 釋放表。

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

1、建立沒索引的a表，匯入資料形成.MYD檔案。

2、建立包括索引b表，形成.FRM和.MYI檔案

3、鎖定讀寫

4、把b表的.FRM和.MYI檔案改成a表名字

5、解鎖

6、用repair建立索引。

這個方法對於大表也是很有效的。這也是為什麼很多dba堅持說“先導資料庫在建索引，這樣效率更快”

l 定期檢查mysql伺服器

定期使用show status、show processlist等命令檢查資料庫。這裡就不細說，這說起來也篇幅是比較大的，筆者對這個也不是很瞭解

第四部分：圖說mysql查詢執行流程