mysql索引结构原理、性能分析与优化
摘要：
| 第一部分：基礎知識 第二部分：MYISAM和INNODB索引結構 1、簡單介紹B-tree B+ tree樹 2、MyisAM索引結構 3、Annode索引結構 4、MyisAM索引與InnoDB索引相比較 第三部分：MYSQL優(yōu)化 1、表數(shù)據(jù)類型選擇 2、sql語句優(yōu)化 (1) ? ? 最左前綴原則 (1.1) ?能正確的利用索引 (1.2) ?不能正確的利用索引 (1.3) ?如果一個查詢where子句中確實不需要password列，那就用“補洞”。 (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)關于in (4.2)UNION All (5) ? ? 范索引選擇性 (6) ? ? 重復或多余索引 3、系統(tǒng)配置與維護優(yōu)化 (1) ? ? 重要的一些變量 (2) ? ? Fdsoptimize、Analyze、check、repair維護操作 (3) ? ? 表結構的更新與維護 第四部分：圖說mysql查詢執(zhí)行流程 ? |
?
?
第一部分：基礎知識：
索引
官方介紹索引是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結構。筆者理解索引相當于一本書的目錄，通過目錄就知道要的資料在哪里，不用一頁一頁查閱找出需要的資料。關鍵字index
-------------------------------------------------------------
唯一索引
強調唯一，就是索引值必須唯一，關鍵字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列，關鍵字是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
------------------------------
單列索引與多列索引
索引可以是單列索引也可以是多列索引(也叫復合索引)。按照上面形式創(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)可以當做a或(a, b)的索引來使用，但和b、c或(b,c)的索引來使用這是一個最左前綴的優(yōu)化方法，在后面會有詳細的介紹，你只要知道有這樣兩個概念
-------------------------------------------------------------
聚集索引
一種索引，該索引中鍵值的邏輯順序決定了表中相應行的物理順序。聚集索引確定表中數(shù)據(jù)的物理順序。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 ? ? ?| ? ? ? ? | ?
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+
Table：表名
Key_name：什么類型索引(這了是主鍵)
Column_name：索引列的字段名
Cardinality：索引基數(shù)，很關鍵的一個參數(shù)，平均數(shù)值組=索引基數(shù)/表總數(shù)據(jù)行，平均數(shù)值組越接近1就越有可能利用索引
Index_type：如果索引是全文索引，則是fulltext,這里是b+tree索引，b+tre也是這篇文章研究的重點之一
其他的就不詳細介紹，更多：
第二部分：MYISAM和INNODB索引結構
1、?簡單介紹B-tree B+ tree樹
B-tree結構視圖
一棵m階的B-tree樹，則有以下性質
(1)Ki表示關鍵字值，上圖中，k1?<…?<該關鍵字值<右子節(jié)點關鍵字值)
(2)Pi表示指向子節(jié)點的指針，左指針指向左子節(jié)點，右指針指向右子節(jié)點。即是：p1[指向值]
(3)所有關鍵字必須唯一值(這也是創(chuàng)建myisam 和innodb表必須要主鍵的原因)，每個節(jié)點包含一個說明該節(jié)點多少個關鍵字，如上圖第二行的i和n
(4)節(jié)點：
l ?每個節(jié)點最可以有m個子節(jié)點。
l ?根節(jié)點若非葉子節(jié)點，至少2個子節(jié)點，最多m個子節(jié)點
l ?每個非根，非葉子節(jié)點至少[m/2]子節(jié)點或叫子樹([]表示向上取整)，最多m個子節(jié)點
(5)關鍵字：
l ?根節(jié)點的關鍵字個數(shù)1~m-1
l ?非根非葉子節(jié)點的關鍵字個數(shù)[m/2]-1~m-1,如m=3，則該類節(jié)點關鍵字個數(shù)：2-1~2
(6)關鍵字數(shù)k和指向子節(jié)點個數(shù)指針p的關系：
l ?k+1=p ，注意根據(jù)儲存數(shù)據(jù)的具體需求，左右指針為空時要有標志位表示沒有
?B+tree結構示意圖如下：
?
B+樹是B-樹的變體，也是一種多路搜索樹：
l ?非葉子結點的子樹指針與關鍵字個數(shù)相同
l ?為所有葉子結點增加一個鏈指針(紅點標志的箭頭)
?
?
B+樹是B-樹的變體，也是一種多路搜索樹：
l ?非葉子結點的子樹指針與關鍵字個數(shù)相同
l ?為所有葉子結點增加一個鏈指針(紅點標志的箭頭)
2、?MyisAM索引結構
MyisAM索引用的B+tree來儲存數(shù)據(jù)，MyisAM索引的指針指向的是鍵值的地址，地址存儲的是數(shù)據(jù)，如下圖：
(1)結構講解：上圖3階樹，主鍵是Col2，Col值就是改行數(shù)據(jù)保存的物理地址，其中紅色部分是說明標注。
l ?1標注部分也許會迷惑，前面不是說關鍵字15右指針的指向鍵值要大于15，怎么下面還有15關鍵字？因為B+tree的所以葉子節(jié)點包含所有關鍵字且是按照升序排列(主鍵索引唯一，輔助索引可以不唯一)，所以等于關鍵字的數(shù)據(jù)值在右子樹
l ?2標注是相應關鍵字存儲對應數(shù)據(jù)的物理地址，注意這也是之后和InnoDB索引不同的地方之一
l ?2標注也是一個所說MyiAM表的索引和數(shù)據(jù)是分離的，索引保存在”表名.MYI”文件內，而數(shù)據(jù)保存在“表名.MYD”文件內，2標注的物理地址就是“表名.MYD”文件內相應數(shù)據(jù)的物理地址。(InnoDB表的索引文件和數(shù)據(jù)文件在一起)
l ?輔助索引和主鍵索引沒什么大的區(qū)別，輔助索引的索引值是可以重復的(但InnoDB輔助索引和主鍵索引有很明顯的區(qū)別，這里先提醒注意一下)
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的有很明顯的區(qū)別
l ?MyisAM表的索引和數(shù)據(jù)是分開的，用指針指向數(shù)據(jù)的物理地址，而InnoDB表中索引和數(shù)據(jù)是儲存在一起。看紅框1可一看出一行數(shù)據(jù)都保存了。
l ?還有一個上圖多了三行的隱藏數(shù)據(jù)列(虛線表)，這是因為MyisAM不支持事務，InnoDB處理事務在性能上并發(fā)控制上比較好，看圖中的紅框2中的DB_TRX_ID是事務ID，自動增長；db_roll_ptr是回滾指針，用于事務出錯時數(shù)據(jù)回滾恢復；db_row_id是記錄行號，這個值其實在主鍵索引中就是主鍵值，這里標出重復是為了容易介紹，還有的是若不是主鍵索引(輔助索引)，db_row_id會找表中unique的列作為值，若沒有unique列則系統(tǒng)自動創(chuàng)建一個。關于InnoDB跟多事務MVCC點此：http://www.phpben.com/?post=72
(2)加入上表中Col1是主鍵(下圖標錯)，而Col2是輔助索引，則相應的輔助索引結構圖：
?
可以看出InnoDB輔助索引并沒有保存相應的所有列數(shù)據(jù)，而是保存了主鍵的鍵值(圖中1、2、3….)這樣做利弊也是很明顯：
l ?在已有主鍵索引，避免數(shù)據(jù)冗余，同時在修改數(shù)據(jù)的時候只需修改輔助索引值。
l ?但輔助索引查找數(shù)據(jù)事要檢索兩次，先找到相應的主鍵索引值然后在去檢索主鍵索引找到對應的數(shù)據(jù)。這也是網(wǎng)上很多mysql性能優(yōu)化時提到的“主鍵盡可能簡短”的原因，主鍵越長輔助索引也就越大，當然主鍵索引也越大。
4、?MyisAM索引與InnoDB索引相比較
l MyisAM支持全文索引(FULLTEXT)、壓縮索引，InnoDB不支持
l AnnoDB支持事務，MyisAM不支持
l MyisAM順序儲存數(shù)據(jù)，索引葉子節(jié)點保存對應數(shù)據(jù)行地址，輔助索引很主鍵索引相差無幾；AnnoDB主鍵節(jié)點同時保存數(shù)據(jù)行，其他輔助索引保存的是主鍵索引的值
l MyisAM鍵值分離，索引載入內存(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ū)內
l MyisAM處理字符串索引時用增量保存的方式，如第一個索引是‘preform’，第二個是‘preformence’，則第二個保存是‘7，ance‘，這個明顯的好處是縮短索引，但是缺陷就是不支持倒序提取索引，必須順序遍歷獲取索引
?
?
第三部分：MYSQL優(yōu)化
mysql優(yōu)化是一個重大課題之一，這里會重點詳細的介紹mysql優(yōu)化，包括表數(shù)據(jù)類型選擇，sql語句優(yōu)化，系統(tǒng)配置與維護優(yōu)化三類。
1、 ?表數(shù)據(jù)類型選擇
(1)能小就用小。表數(shù)據(jù)類型第一個原則是：使用能正確的表示和存儲數(shù)據(jù)的最短類型。這樣可以減少對磁盤空間、內存、cpu緩存的使用。
(2)避免用NULL，這個也是網(wǎng)上優(yōu)化技術博文傳的最多的一個。理由是額外增加字節(jié)，還有使索引，索引統(tǒng)計和值更復雜。很多還忽略一
? ? 個count(列)的問題，count(列)是不會統(tǒng)計列值為null的行數(shù)。更多關于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個 |
| 如何處理字符串末尾空格 | 去掉末尾空格，取值出來比較的時候自動加上進行比較 | Version<=4.1，字符串末尾空格被刪掉，version>5.0則保留 |
| 儲存空間 | 固定空間，比喻char(10)不管字符串是否有10個字符都分配10個字符的空間 | Varchar內節(jié)約空間，但更新可能發(fā)生變化，若varchar(10),開始若儲存5個字符，當update成7個時有myisam可能把行拆開，innodb可能分頁，這樣開銷就增大 |
| 適用場合 | 適用于存儲很短或固定或長度相似字符，如MD5加密的密碼char(33)、昵稱char(8)等 | 當最大長度遠大于平均長度并且發(fā)生更新的時候。 |
?
注意當一些英文或數(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ù)據(jù)表的時候很習慣的用int，壓根不考慮這個問題
筆者建議：能用tinyint的絕不用smallint
誤區(qū)：int(1) 和int(11)是一樣的，唯一區(qū)別是mysql客戶端顯示的時候顯示多少位。
整形優(yōu)先原則：能用整形的不用其他類型替換，如ip可以轉換成整形保存，如商品價格‘50.00元’則保存成50
(5)精確度與空間的轉換。在存儲相同數(shù)值范圍的數(shù)據(jù)時，浮點數(shù)類型通常都會比DECIMAL類型使用更少的空間。FLOAT字段使用4字節(jié)存儲
數(shù)據(jù)。DOUBLE類型需要8 個字節(jié)并擁有更高的精確度和更大的數(shù)值范圍，DECIMAL類型的數(shù)據(jù)將會轉換成DOUBLE類型。
2、 ?sql語句優(yōu)化
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多條數(shù)據(jù).
| 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 ? ? ?| ? ? ? ? | ?
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+
(1) ? ?最左前綴原則
定義：最左前綴原則指的的是在sql where 字句中一些條件或表達式中出現(xiàn)的列的順序要保持和多索引的一致或以多列索引順序出現(xiàn)，只要出現(xiàn)非順序出現(xiàn)、斷層都無法利用到多列索引。
舉例說明：上面給出一個多列索引(username,password,last_login)，當三列在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條，加上沒有在實體機上運行，很多未可預知的影響因素都沒考慮進去。當在大數(shù)據(jù)量，高并發(fā)的時候，最左前綴原則對與提高性能方面是不可否認的。
Ps：最左前綴原則中where字句有or出現(xiàn)還是會遍歷全表
(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) ?
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) ?
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) ?
上面可以看出type=ref 是多列索引，key_len分別是24、43、83，這說明用到的索引分別是(username), (username,password), (username,password, last_login );row分別是5、1、1檢索的數(shù)據(jù)行都很少，因為這三個查詢都按照索引前綴原則，可以利用到索引。
(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) ?
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) ?
以上的兩條語句都不是以username開始，這樣是用不了索引，通過type=all(全表掃描)，key_len=null，rows都很大20146
Ps：one表里只有20003條數(shù)據(jù)，為什么出現(xiàn)20146，這是優(yōu)化器對表的一個估算值，不精確的。
l ?Where 子句表達式雖然順序是(username,password, last_login)或(username,password)但第一個是有范圍’<’、’>’，’<=’，’>=’等出現(xiàn)
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) ?
這個查詢很明顯是遍歷所有表，一個索引都沒用到，非第一列出現(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)
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) ?
注意這里的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) ?
這個就是要的返回結果，所以可以知道斷層(username,last_login)，這樣只用到username索引，把用到索引的數(shù)據(jù)再重新檢查last_login條件，這個相對全表查詢來說還是有性能上優(yōu)化，這也是很多sql優(yōu)化文章中提到的where 范圍查詢要放在最后(這不絕對，但可以利用一部分索引)
(1.3)如果一個查詢where子句中確實不需要password列，那就用“補洞”。
mysql> select distinct(password) from one; ?
+----------+ ?
| password | ?
+----------+ ?
| 234567 ? | ?
| 345678 ? | ?
| 456789 ? | ?
| 123456 ? | ?
+----------+ ?
4 rows in set (0.08 sec)
可以看出password列中只有這幾個值，當然在現(xiàn)實中不可能密碼有這么多一樣的，再說數(shù)據(jù)也可能不斷更新，這里只是舉例說明補洞的方法
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) ?
可以看出ref=83 所有的索引都用到了，type=range是因為用了in子句。
這個被“補洞”列中的值應該是有限的，可預知的，如性別，其值只有男和女(加多一個不男不女也無妨)。
“補洞”方法也有瓶頸，當很多列，且需要補洞的相應列(可以多列)的值雖有限但很多(如中國城市)的時候，優(yōu)化器在優(yōu)化時組合起來的數(shù)量是很大，這樣的話就要做好基準測試和性能分析，權衡得失，取得一個合理的優(yōu)化方法。
(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) ?
對比就知道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ù)兩次，第一次若能使用索引則使用，第二次是隨機讀(當然不同引擎也不同)。mysql version()>=4.1,更新了一個新算法，就是在第一次讀的時候也把selcet的列也讀出來，然后在sort_buffer_size中排序(不夠大則建臨時表保存排序順序)，這算法只需要一次讀取數(shù)據(jù)。所以有這個廣為人傳的一個優(yōu)化方法，那就是增大sort_buffer_size。Filesort第二種算法要用到更的空間，sort_buffer_size不夠大反而會影響速度，所以mysql開發(fā)團隊定了個變量max_length_for_sort_data，當算法中讀出來的需要列的數(shù)據(jù)的大小超過該變量的值才使用，所以一般性能分析的時候會嘗試把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) ?
開始沒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) ?
上面兩條語句明顯的區(qū)別是多了一個非索引列l(wèi)evel的排序，在extra這列對了Using filesort
筆者測試結果：where滿足最左前綴且order by中的列是該多列索引的子集時(也就是說orerby中沒最左前綴原則限制)，不管是否有asc ,desc混合出現(xiàn)，都能用索引來滿足order by。
筆者測試過，因為篇幅比較大，這里就不一一列出。
Ps:很優(yōu)化博文都說order by中的列要where中出現(xiàn)的列(是索引)的順序一致，筆者認為不夠嚴謹。
(2.3) where + orerby+limit
這個其實也差不多，只要where最左前綴，orderby也正確，limit在此影響不大
(2.4)如何考慮order by來建索引
這個回歸到創(chuàng)建索引的問題來，在比較常用的oder by的列和where中常用的列建立多列索引，這樣優(yōu)化起來的廣度和擴張性都比較好，當然如果要考慮UNION、JOIN、COUNT、IN等進來就復雜很多了
(3) ? ?隔離列
隔離列是只查詢語句中把索引列隔離出來，也就是說不能在語句中把列包含進表達式中，如id+1=2、inet_aton('210.38.196.138')---ip轉換成整數(shù)、convert(123,char(3))---數(shù)字轉換成字符串、date函數(shù)等mysql內置的大多函數(shù)。
非隔離列影響性能很大甚至是致命的，這也就是趕集網(wǎng)石展的《三十六軍規(guī)》中的一條，雖然他沒說明是隔離列。
以下就測試一下：
首先建立一個索引(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) ?
last_login +1=8388608非隔離列的出現(xiàn)導致查找的列20197，說明是遍歷整張表且索引不能使用。
這是因為這條語句要找出所有l(wèi)ast_login的數(shù)據(jù)，然后+1再和20197比較，優(yōu)化器在這方面比較差，性能很差。
所以要盡可能的把列隔離出來，如last_login +1=8388606改成login_login=8388607,或者把計算、轉換等操作先用php函數(shù)處理過再傳遞給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) ?
(4.2)對于in，這個是有爭議的，網(wǎng)上很多優(yōu)化方案中都提到盡量少用in，這不全面，其實在in里面如果是常量的話，可一大膽的用in，這個也是趕集網(wǎng)石展、阿里hellodab的觀點(筆者從微博中獲知)。應用hellodab一句話“MySQL用IN效率不好，通常是指in中嵌套一個子查詢，因為MySQL的查詢重寫可能會產(chǎn)生一個不好的執(zhí)行計劃，而如果in里面是常量的話，我認為性能沒有任何問題，可以放心使用”---------當然對于這個比較的話，沒有實戰(zhàn)數(shù)據(jù)的話很難辯解，就算有，影響性能的因素也很多，也許會每個dba都有不同的測試結果.這也簽名最左前綴中“補洞”一個方法
(4.3)UNION All 直接返回并集，可以避免去重的開銷。之所說“嘗試”用UNION All 替代 OR來優(yōu)化sql語句，因為這不是一直能優(yōu)化的了，這里只是作為一個方法去嘗試。
(5) ? ?索引選擇性
索引選擇性是不重復的索引值也叫基數(shù)(cardinality)表中數(shù)據(jù)行數(shù)的比值，索引選擇性=基數(shù)/數(shù)據(jù)行，基數(shù)可以通過“show index from 表名”查看。
高索引選擇性的好處就是mysql查找匹配的時候可以過濾更多的行，唯一索引的選擇性最佳，值為1。
那么對于非唯一索引或者說要被創(chuàng)建索引的列的數(shù)據(jù)內容很長，那就要選擇索引前綴。這里就簡單說明一下：
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列數(shù)據(jù)很長，則可以通過
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引擎的索引來說，每次修改數(shù)據(jù)都要把主鍵索引，輔助索引中相應索引值修改，這可能會出現(xiàn)大量數(shù)據(jù)遷移，分頁，以及碎片的出現(xiàn)。
3、系統(tǒng)配置與維護優(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當排序后按排序后的順序讀取行時，則通過該緩沖區(qū)讀取行，避免搜索硬盤。將該變量設置為較大的值可以大大改進ORDER BY的性能。但是，這是為每個客戶端分配的緩沖區(qū)，因此你不應將全局變量設置為較大的值。相反，只為需要運行大查詢的客戶端更改會話變量
l ?join_buffer_size用于表間關聯(lián)(join)的緩存大小
l ?tmp_table_size緩存表的大小
l ?table_cache允許 MySQL 打開的表的最大個數(shù)，并且這些都cache在內存中
l ?delay_key_write針對MyISAM存儲引擎,延遲更新索引.意思是說,update記錄時,先將數(shù)據(jù)up到磁盤,但不up索引,將索引存在內存里,當表關閉時,將內存索引,寫到磁盤
更多參數(shù)查看http://www.phpben.com/?post=70
(2) ? ?optimize、Analyze、check、repair維護操作
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”，當然也可以通過命令：alter table one type=innodb; 來替代。
l ?Analyze 用來分析和存儲表的關鍵字的分布，使得系統(tǒng)獲得準確的統(tǒng)計信息，影響 SQL 的執(zhí)行計劃的生成。對于數(shù)據(jù)基本沒有發(fā)生變化的表，是不需要經(jīng)常進行表分析的。但是如果表的數(shù)據(jù)量變化很明顯，用戶感覺實際的執(zhí)行計劃和預期的執(zhí)行計劃不 同的時候，執(zhí)行一次表分析可能有助于產(chǎn)生預期的執(zhí)行計劃。
Analyze table 表名
l ?Check檢查表或者視圖是否存在錯誤，對 MyISAM 和 InnoDB 存儲引擎的表有作用。對于 MyISAM 存儲引擎的表進行表檢查，也會同時更新關鍵字統(tǒng)計數(shù)據(jù)
l ?Repair optimize需要有足夠的硬盤空間，否則可能會破壞表，導致不能操作，那就要用上repair，注意INNODB不支持repair操作
以上的操作出現(xiàn)的都是如下這是check
+----------+-------+--------------+-------------+ ?
| Table ?| Op ?| Msg_type| Msg_text | ?
+----------+-------+--------------+-------------+ ?
| test.one | check | status ?| OK ? ? | ?
+----------+-------+--------------+-------------+ ?
其中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ù)遷移等操作，否則可能導致一些功能無法正常使用甚至數(shù)據(jù)庫崩潰。
(3)表結構的更新與維護
l ?改表結構。當要在數(shù)據(jù)量千萬級的數(shù)據(jù)表中使用alter更改表結構的時候，這是一個棘手問題。一種方法是在低并發(fā)低訪問量的時候用平常的alter更改表。另外一種就是建另一個與要修改的表，這個表除了要修改的結構屬性外其他的和原表一模一樣，這樣就能得到一個相應的.frm文件，然后用flush with read lock 鎖定讀，然后覆蓋用新建的.frm文件覆蓋原表的.frm，最后unlock table 釋放表。
l ?建立新的索引。一般方法這里不說。
1、 ?創(chuàng)建沒索引的a表，導入數(shù)據(jù)形成.MYD文件。
2、 ?創(chuàng)建包括索引b表，形成.FRM和.MYI文件
3、 ?鎖定讀寫
4、 ?把b表的.FRM和.MYI文件改成a表名字
5、 ?解鎖
6、 ?用repair創(chuàng)建索引。
這個方法對于大表也是很有效的。這也是為什么很多dba堅持說“先導數(shù)據(jù)庫在建索引，這樣效率更快”
l ?定期檢查mysql服務器
定期使用show status、show processlist等命令檢查數(shù)據(jù)庫。這里就不細說，這說起來也篇幅是比較大的，筆者對這個也不是很了解
第四部分：圖說mysql查詢執(zhí)行流程
1、 ?查詢緩存，判斷sql語句是否完全匹配，再判斷是否有權限，兩個判斷為假則到解析器解析語句，為真則提取數(shù)據(jù)結果返回給用戶。
2、 ?解析器解析。解析器先詞法分析，語法分析，檢查錯誤比如引號有沒閉合等，然后生成解析樹。
3、 ?預處理。預處理解決解析器無法決解的語義，如檢查表和列是否存在，別名是否有錯，生成新的解析樹。
4、 ?優(yōu)化器做大量的優(yōu)化操作。
5、 ?生成執(zhí)行計劃。
6、 ?查詢執(zhí)行引擎，負責調度引擎獲取相應數(shù)據(jù)
7、 ?返回結果。
?
?
這篇博文準備，寫，將用了一個月時間！終于寫完，但真的學了很多東西！ 有紕漏請聯(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
覺得文章有用？立即： 和朋友一起?共學習 共進步！建議繼續(xù)學習：
轉載于:https://www.cnblogs.com/mr-amazing/p/4535544.html
總結
以上是生活随笔為你收集整理的mysql索引结构原理、性能分析与优化的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 招行e分期需要单独还款吗？这些还款事项必
- 下一篇： 中信银行信用卡怎么激活？激活失败怎么办？