mysql索引的使用和优化
參考：
http://blog.csdn.net/xluren/article/details/32746183
http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html
?
關于MySQL索引的好處，如果正確合理設計并且使用索引的MySQL是一輛蘭博基尼的話，那么沒有設計和使用索引的MySQL就是一個人力三輪車。對于沒有索引的表，單表查詢可能幾十萬數據就是瓶頸，而通常大型網站單日就可能會產生幾十萬甚至幾百萬的數據，沒有索引查詢會變的非常緩慢。還是以WordPress來說，其多個數據表都會對經常被查詢的字段添加索引，比如wp_comments表中針對5個字段設計了BTREE索引。
一個簡單的對比測試
以我去年測試的數據作為一個簡單示例，20多條數據源隨機生成200萬條數據，平均每條數據源都重復大概10萬次，表結構比較簡單，僅包含一個自增ID，一個char類型，一個text類型和一個int類型，單表2G大小，使用MyIASM引擎。開始測試未添加任何索引。
執行下面的SQL語句：
| 1 | mysql>?SELECT?id,FROM_UNIXTIME(time)?FROM?article?WHERE?a.title='測試標題' |
查詢需要的時間非常恐怖的，如果加上聯合查詢和其他一些約束條件，數據庫會瘋狂的消耗內存，并且會影響前端程序的執行。這時給title字段添加一個BTREE索引：
| 1 | mysql>?ALTER?TABLE?article?ADD?INDEX?index_article_title?ON?title(200); |
再次執行上述查詢語句，其對比非常明顯：
?
MySQL索引的概念
索引是一種特殊的文件(InnoDB數據表上的索引是表空間的一個組成部分)，它們包含著對數據表里所有記錄的引用指針。更通俗的說，數據庫索引好比是一本書前面的目錄，能加快數據庫的查詢速度。上述SQL語句，在沒有索引的情況下，數據庫會遍歷全部200條數據后選擇符合條件的；而有了相應的索引之后，數據庫會直接在索引中查找符合條件的選項。如果我們把SQL語句換成“SELECT * FROM article WHERE id=2000000”，那么你是希望數據庫按照順序讀取完200萬行數據以后給你結果還是直接在索引中定位呢？上面的兩個圖片鮮明的用時對比已經給出了答案（注：一般數據庫默認都會為主鍵生成索引）。
索引分為聚簇索引和非聚簇索引兩種，聚簇索引是按照數據存放的物理位置為順序的，而非聚簇索引就不一樣了；聚簇索引能提高多行檢索的速度，而非聚簇索引對于單行的檢索很快。
MySQL索引的類型
1. 普通索引
這是最基本的索引，它沒有任何限制，比如上文中為title字段創建的索引就是一個普通索引，MyIASM中默認的BTREE類型的索引，也是我們大多數情況下用到的索引。
| 01 | –直接創建索引 |
| 02 | CREATE?INDEX?index_name?ON?table(column(length)) |
| 03 | –修改表結構的方式添加索引 |
| 04 | ALTER?TABLE?table_name?ADD?INDEX?index_name?ON?(column(length)) |
| 05 | –創建表的時候同時創建索引 |
| 06 | CREATE?TABLE?`table` ( |
| 07 | `id`?int(11)?NOT?NULL?AUTO_INCREMENT , |
| 08 | `title`?char(255)?CHARACTER?SET?utf8?COLLATE?utf8_general_ci?NOT?NULL?, |
| 09 | `content` text?CHARACTER?SET?utf8?COLLATE?utf8_general_ci?NULL?, |
| 10 | `time`?int(10)?NULL?DEFAULT?NULL?, |
| 11 | PRIMARY?KEY?(`id`), |
| 12 | INDEX?index_name (title(length)) |
| 13 | ) |
| 14 | –刪除索引 |
| 15 | DROP?INDEX?index_name?ON?table |
2. 唯一索引
與普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值（注意和主鍵不同）。如果是組合索引，則列值的組合必須唯一，創建方法和普通索引類似。
| 01 | –創建唯一索引 |
| 02 | CREATE?UNIQUE?INDEX?indexName?ON?table(column(length)) |
| 03 | –修改表結構 |
| 04 | ALTER?TABLE?table_name?ADD?UNIQUE?indexName?ON?(column(length)) |
| 05 | –創建表的時候直接指定 |
| 06 | CREATE?TABLE?`table` ( |
| 07 | `id`?int(11)?NOT?NULL?AUTO_INCREMENT , |
| 08 | `title`?char(255)?CHARACTER?SET?utf8?COLLATE?utf8_general_ci?NOT?NULL?, |
| 09 | `content` text?CHARACTER?SET?utf8?COLLATE?utf8_general_ci?NULL?, |
| 10 | `time`?int(10)?NULL?DEFAULT?NULL?, |
| 11 | PRIMARY?KEY?(`id`), |
| 12 | UNIQUE?indexName (title(length)) |
| 13 | ); |
3. 全文索引（FULLTEXT）
MySQL從3.23.23版開始支持全文索引和全文檢索，FULLTEXT索引僅可用于 MyISAM 表；他們可以從CHAR、VARCHAR或TEXT列中作為CREATE TABLE語句的一部分被創建，或是隨后使用ALTER TABLE 或CREATE INDEX被添加。對于較大的數據集，將你的資料輸入一個沒有FULLTEXT索引的表中，然后創建索引，其速度比把資料輸入現有FULLTEXT索引的速度更為快。不過切記對于大容量的數據表，生成全文索引是一個非常消耗時間非常消耗硬盤空間的做法。
| 01 | –創建表的適合添加全文索引 |
| 02 | CREATE?TABLE?`table` ( |
| 03 | `id`?int(11)?NOT?NULL?AUTO_INCREMENT , |
| 04 | `title`?char(255)?CHARACTER?SET?utf8?COLLATE?utf8_general_ci?NOT?NULL?, |
| 05 | `content` text?CHARACTER?SET?utf8?COLLATE?utf8_general_ci?NULL?, |
| 06 | `time`?int(10)?NULL?DEFAULT?NULL?, |
| 07 | PRIMARY?KEY?(`id`), |
| 08 | FULLTEXT (content) |
| 09 | ); |
| 10 | –修改表結構添加全文索引 |
| 11 | ALTER?TABLE?article?ADD?FULLTEXT index_content(content) |
| 12 | –直接創建索引 |
| 13 | CREATE?FULLTEXT?INDEX?index_content?ON?article(content) |
4. 單列索引、多列索引
多個單列索引與單個多列索引的查詢效果不同，因為執行查詢時，MySQL只能使用一個索引，會從多個索引中選擇一個限制最為嚴格的索引。
5. 組合索引（最左前綴）
平時用的SQL查詢語句一般都有比較多的限制條件，所以為了進一步榨取MySQL的效率，就要考慮建立組合索引。例如上表中針對title和time建立一個組合索引：ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))。建立這樣的組合索引，其實是相當于分別建立了下面兩組組合索引：
–title,time
–title
為什么沒有time這樣的組合索引呢？這是因為MySQL組合索引“最左前綴”的結果。簡單的理解就是只從最左面的開始組合。并不是只要包含這兩列的查詢都會用到該組合索引，如下面的幾個SQL所示：
| 1 | –使用到上面的索引 |
| 2 | SELECT?*?FROM?article WHREE title='測試'?AND?time=1234567890; |
| 3 | SELECT?*?FROM?article WHREE utitle='測試'; |
| 4 | –不使用上面的索引 |
| 5 | SELECT?*?FROM?article WHREE?time=1234567890; |
MySQL索引的優化
上面都在說使用索引的好處，但過多的使用索引將會造成濫用。因此索引也會有它的缺點：雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數據，還要保存一下索引文件。建立索引會占用磁盤空間的索引文件。一般情況這個問題不太嚴重，但如果你在一個大表上創建了多種組合索引，索引文件的會膨脹很快。索引只是提高效率的一個因素，如果你的MySQL有大數據量的表，就需要花時間研究建立最優秀的索引，或優化查詢語句。下面是一些總結以及收藏的MySQL索引的注意事項和優化方法。
1. 何時使用聚集索引或非聚集索引？
| 動作描述 | 使用聚集索引 | 使用非聚集索引 |
| 列經常被分組排序 | 使用 | 使用 |
| 返回某范圍內的數據 | 使用 | 不使用 |
| 一個或極少不同值 | 不使用 | 不使用 |
| 小數目的不同值 | 使用 | 不使用 |
| 大數目的不同值 | 不使用 | 使用 |
| 頻繁更新的列 | 不使用 | 使用 |
| 外鍵列 | 使用 | 使用 |
| 主鍵列 | 使用 | 使用 |
| 頻繁修改索引列 | 不使用 | 使用 |
事實上，我們可以通過前面聚集索引和非聚集索引的定義的例子來理解上表。如：返回某范圍內的數據一項。比如您的某個表有一個時間列，恰好您把聚合索引建立在了該列，這時您查詢2004年1月1日至2004年10月1日之間的全部數據時，這個速度就將是很快的，因為您的這本字典正文是按日期進行排序的，聚類索引只需要找到要檢索的所有數據中的開頭和結尾數據即可；而不像非聚集索引，必須先查到目錄中查到每一項數據對應的頁碼，然后再根據頁碼查到具體內容。其實這個具體用法我還不是很理解，只能等待后期的項目開發中慢慢學學了。
2. 索引不會包含有NULL值的列
只要列中包含有NULL值都將不會被包含在索引中，復合索引中只要有一列含有NULL值，那么這一列對于此復合索引就是無效的。所以我們在數據庫設計時不要讓字段的默認值為NULL。
3. 使用短索引
對串列進行索引，如果可能應該指定一個前綴長度。例如，如果有一個CHAR(255)的列，如果在前10個或20個字符內，多數值是惟一的，那么就不要對整個列進行索引。短索引不僅可以提高查詢速度而且可以節省磁盤空間和I/O操作。
4. 索引列排序
MySQL查詢只使用一個索引，因此如果where子句中已經使用了索引的話，那么order by中的列是不會使用索引的。因此數據庫默認排序可以符合要求的情況下不要使用排序操作；盡量不要包含多個列的排序，如果需要最好給這些列創建復合索引。
5. like語句操作
一般情況下不鼓勵使用like操作，如果非使用不可，如何使用也是一個問題。like “%aaa%” 不會使用索引而like “aaa%”可以使用索引。
6. 不要在列上進行運算
例如：select * from users where YEAR(adddate)<2007，將在每個行上進行運算，這將導致索引失效而進行全表掃描，因此我們可以改成：select * from users where adddate<’2007-01-01′。關于這一點可以圍觀：一個單引號引發的MYSQL性能損失。
最后總結一下，MySQL只對一下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些時候的like(不以通配符%或_開頭的情形)。而理論上每張表里面最多可創建16個索引，不過除非是數據量真的很多，否則過多的使用索引也不是那么好玩的，比如我剛才針對text類型的字段創建索引的時候，系統差點就卡死了。
?
索引與優化
寫在前面：索引對查詢的速度有著至關重要的影響，理解索引也是進行數據庫性能調優的起點。考慮如下情況，假設數據庫中一個表有10^6條記錄，DBMS的頁面大小為4K，并存儲100條記錄。如果沒有索引，查詢將對整個表進行掃描，最壞的情況下，如果所有數據頁都不在內存，需要讀取10^4個頁面，如果這10^4個頁面在磁盤上隨機分布，需要進行10^4次I/O，假設磁盤每次I/O時間為10ms(忽略數據傳輸時間)，則總共需要100s(但實際上要好很多很多)。如果對之建立B-Tree索引，則只需要進行log100(10^6)=3次頁面讀取，最壞情況下耗時30ms。這就是索引帶來的效果，很多時候，當你的應用程序進行SQL查詢速度很慢時，應該想想是否可以建索引。進入正題：
第二章、索引與優化
1、選擇索引的數據類型
MySQL支持很多數據類型，選擇合適的數據類型存儲數據對性能有很大的影響。通常來說，可以遵循以下一些指導原則：
(1)越小的數據類型通常更好：越小的數據類型通常在磁盤、內存和CPU緩存中都需要更少的空間，處理起來更快。
(2)簡單的數據類型更好：整型數據比起字符，處理開銷更小，因為字符串的比較更復雜。在MySQL中，應該用內置的日期和時間數據類型，而不是用字符串來存儲時間；以及用整型數據類型存儲IP地址。
(3)盡量避免NULL：應該指定列為NOT NULL，除非你想存儲NULL。在MySQL中，含有空值的列很難進行查詢優化，因為它們使得索引、索引的統計信息以及比較運算更加復雜。你應該用0、一個特殊的值或者一個空串代替空值。
1.1、選擇標識符
選擇合適的標識符是非常重要的。選擇時不僅應該考慮存儲類型，而且應該考慮MySQL是怎樣進行運算和比較的。一旦選定數據類型，應該保證所有相關的表都使用相同的數據類型。
(1)?? ?整型：通常是作為標識符的最好選擇，因為可以更快的處理，而且可以設置為AUTO_INCREMENT。
(2)?? ?字符串：盡量避免使用字符串作為標識符，它們消耗更好的空間，處理起來也較慢。而且，通常來說，字符串都是隨機的，所以它們在索引中的位置也是隨機的，這會導致頁面分裂、隨機訪問磁盤，聚簇索引分裂（對于使用聚簇索引的存儲引擎）。
2、索引入門
對于任何DBMS，索引都是進行優化的最主要的因素。對于少量的數據，沒有合適的索引影響不是很大，但是，當隨著數據量的增加，性能會急劇下降。
如果對多列進行索引(組合索引)，列的順序非常重要，MySQL僅能對索引最左邊的前綴進行有效的查找。例如：
假設存在組合索引it1c1c2(c1,c2)，查詢語句select * from t1 where c1=1 and c2=2能夠使用該索引。查詢語句select * from t1 where c1=1也能夠使用該索引。但是，查詢語句select * from t1 where c2=2不能夠使用該索引，因為沒有組合索引的引導列，即，要想使用c2列進行查找，必需出現c1等于某值。
2.1、索引的類型
索引是在存儲引擎中實現的，而不是在服務器層中實現的。所以，每種存儲引擎的索引都不一定完全相同，并不是所有的存儲引擎都支持所有的索引類型。
2.1.1、B-Tree索引
假設有如下一個表：
| CREATE TABLE People ( ???last_name?varchar(50)??? not null, ???first_name varchar(50)??? not null, ???dob??????? date?????????? not null, ???gender???? enum('m', 'f') not null, ???key(last_name, first_name, dob) ); |
?其索引包含表中每一行的last_name、first_name和dob列。其結構大致如下：
?
?索引存儲的值按索引列中的順序排列。可以利用B-Tree索引進行全關鍵字、關鍵字范圍和關鍵字前綴查詢，當然，如果想使用索引，你必須保證按索引的最左邊前綴(leftmost prefix of the index)來進行查詢。
(1)匹配全值(Match the full value)：對索引中的所有列都指定具體的值。例如，上圖中索引可以幫助你查找出生于1960-01-01的Cuba Allen。
(2)匹配最左前綴(Match a leftmost prefix)：你可以利用索引查找last name為Allen的人，僅僅使用索引中的第1列。
(3)匹配列前綴(Match a column prefix)：例如，你可以利用索引查找last name以J開始的人，這僅僅使用索引中的第1列。
(4)匹配值的范圍查詢(Match a range of values)：可以利用索引查找last name在Allen和Barrymore之間的人，僅僅使用索引中第1列。
(5)匹配部分精確而其它部分進行范圍匹配(Match one part exactly and match a range on another part)：可以利用索引查找last name為Allen，而first name以字母K開始的人。
(6)僅對索引進行查詢(Index-only queries)：如果查詢的列都位于索引中，則不需要讀取元組的值。
由于B-樹中的節點都是順序存儲的，所以可以利用索引進行查找(找某些值)，也可以對查詢結果進行ORDER BY。當然，使用B-tree索引有以下一些限制：
(1) 查詢必須從索引的最左邊的列開始。關于這點已經提了很多遍了。例如你不能利用索引查找在某一天出生的人。
(2) 不能跳過某一索引列。例如，你不能利用索引查找last name為Smith且出生于某一天的人。
(3) 存儲引擎不能使用索引中范圍條件右邊的列。例如，如果你的查詢語句為WHERE last_name="Smith" AND first_name LIKE 'J%' AND dob='1976-12-23'，則該查詢只會使用索引中的前兩列，因為LIKE是范圍查詢。
2.1.2、Hash索引
MySQL中，只有Memory存儲引擎顯示支持hash索引，是Memory表的默認索引類型，盡管Memory表也可以使用B-Tree索引。Memory存儲引擎支持非唯一hash索引，這在數據庫領域是罕見的，如果多個值有相同的hash code，索引把它們的行指針用鏈表保存到同一個hash表項中。
假設創建如下一個表：
CREATE TABLE testhash (
?? fname VARCHAR(50) NOT NULL,
?? lname VARCHAR(50) NOT NULL,
?? KEY USING HASH(fname)
) ENGINE=MEMORY;
包含的數據如下：
假設索引使用hash函數f( )，如下：
| f('Arjen') = 2323 f('Baron') = 7437 f('Peter') = 8784 f('Vadim') = 2458 |
此時，索引的結構大概如下：
?
?Slots是有序的，但是記錄不是有序的。當你執行
mysql> SELECT lname FROM testhash WHERE fname='Peter';
MySQL會計算’Peter’的hash值，然后通過它來查詢索引的行指針。因為f('Peter') = 8784，MySQL會在索引中查找8784，得到指向記錄3的指針。
因為索引自己僅僅存儲很短的值，所以，索引非常緊湊。Hash值不取決于列的數據類型，一個TINYINT列的索引與一個長字符串列的索引一樣大。
?
Hash索引有以下一些限制：
(1)由于索引僅包含hash code和記錄指針，所以，MySQL不能通過使用索引避免讀取記錄。但是訪問內存中的記錄是非常迅速的，不會對性造成太大的影響。
(2)不能使用hash索引排序。
(3)Hash索引不支持鍵的部分匹配，因為是通過整個索引值來計算hash值的。
(4)Hash索引只支持等值比較，例如使用=，IN( )和<=>。對于WHERE price>100并不能加速查詢。
2.1.3、空間(R-Tree)索引
MyISAM支持空間索引，主要用于地理空間數據類型，例如GEOMETRY。
2.1.4、全文(Full-text)索引
全文索引是MyISAM的一個特殊索引類型，主要用于全文檢索。
3、高性能的索引策略
3.1、聚簇索引(Clustered Indexes)
聚簇索引保證關鍵字的值相近的元組存儲的物理位置也相同（所以字符串類型不宜建立聚簇索引，特別是隨機字符串，會使得系統進行大量的移動操作），且一個表只能有一個聚簇索引。因為由存儲引擎實現索引，所以，并不是所有的引擎都支持聚簇索引。目前，只有solidDB和InnoDB支持。
聚簇索引的結構大致如下：
?
?注：葉子頁面包含完整的元組，而內節點頁面僅包含索引的列(索引的列為整型)。一些DBMS允許用戶指定聚簇索引，但是MySQL的存儲引擎到目前為止都不支持。InnoDB對主鍵建立聚簇索引。如果你不指定主鍵，InnoDB會用一個具有唯一且非空值的索引來代替。如果不存在這樣的索引，InnoDB會定義一個隱藏的主鍵，然后對其建立聚簇索引。一般來說，DBMS都會以聚簇索引的形式來存儲實際的數據，它是其它二級索引的基礎。
3.1.1、InnoDB和MyISAM的數據布局的比較
為了更加理解聚簇索引和非聚簇索引，或者primary索引和second索引(MyISAM不支持聚簇索引)，來比較一下InnoDB和MyISAM的數據布局，對于如下表：
?
| CREATE TABLE layout_test ( ???col1 int NOT NULL, ???col2 int NOT NULL, ???PRIMARY KEY(col1), ???KEY(col2) ); |
?假設主鍵的值位于1---10,000之間，且按隨機順序插入，然后用OPTIMIZE TABLE進行優化。col2隨機賦予1---100之間的值，所以會存在許多重復的值。
(1)?? ?MyISAM的數據布局
其布局十分簡單，MyISAM按照插入的順序在磁盤上存儲數據，如下：
?注：左邊為行號(row number)，從0開始。因為元組的大小固定，所以MyISAM可以很容易的從表的開始位置找到某一字節的位置。
據些建立的primary key的索引結構大致如下：
?注：MyISAM不支持聚簇索引，索引中每一個葉子節點僅僅包含行號(row number)，且葉子節點按照col1的順序存儲。
來看看col2的索引結構：
?實際上，在MyISAM中，primary key和其它索引沒有什么區別。Primary key僅僅只是一個叫做PRIMARY的唯一，非空的索引而已。
(2)?? ?InnoDB的數據布局
InnoDB按聚簇索引的形式存儲數據，所以它的數據布局有著很大的不同。它存儲表的結構大致如下：
?注：聚簇索引中的每個葉子節點包含primary key的值，事務ID和回滾指針(rollback pointer)——用于事務和MVCC，和余下的列(如col2)。
相對于MyISAM，二級索引與聚簇索引有很大的不同。InnoDB的二級索引的葉子包含primary key的值，而不是行指針(row pointers)，這減小了移動數據或者數據頁面分裂時維護二級索引的開銷，因為InnoDB不需要更新索引的行指針。其結構大致如下：
?聚簇索引和非聚簇索引表的對比：
?
?
?3.1.2、按primary key的順序插入行(InnoDB)
如果你用InnoDB，而且不需要特殊的聚簇索引，一個好的做法就是使用代理主鍵(surrogate key)——獨立于你的應用中的數據。最簡單的做法就是使用一個AUTO_INCREMENT的列，這會保證記錄按照順序插入，而且能提高使用primary key進行連接的查詢的性能。應該盡量避免隨機的聚簇主鍵，例如，字符串主鍵就是一個不好的選擇，它使得插入操作變得隨機。
?
?3.2、覆蓋索引(Covering Indexes)
如果索引包含滿足查詢的所有數據，就稱為覆蓋索引。覆蓋索引是一種非常強大的工具，能大大提高查詢性能。只需要讀取索引而不用讀取數據有以下一些優點：
(1)索引項通常比記錄要小，所以MySQL訪問更少的數據；
(2)索引都按值的大小順序存儲，相對于隨機訪問記錄，需要更少的I/O；
(3)大多數據引擎能更好的緩存索引。比如MyISAM只緩存索引。
(4)覆蓋索引對于InnoDB表尤其有用，因為InnoDB使用聚集索引組織數據，如果二級索引中包含查詢所需的數據，就不再需要在聚集索引中查找了。
覆蓋索引不能是任何索引，只有B-TREE索引存儲相應的值。而且不同的存儲引擎實現覆蓋索引的方式都不同，并不是所有存儲引擎都支持覆蓋索引(Memory和Falcon就不支持)。
對于索引覆蓋查詢(index-covered query)，使用EXPLAIN時，可以在Extra一列中看到“Using index”。例如，在sakila的inventory表中，有一個組合索引(store_id,film_id)，對于只需要訪問這兩列的查詢，MySQL就可以使用索引，如下：
| mysql> EXPLAIN SELECT store_id, film_id FROM sakila.inventory\G *************************** 1. row *************************** ???????????id: 1 ?select_type: SIMPLE ????????table: inventory ?????????type: index possible_keys: NULL ??????????key: idx_store_id_film_id ??????key_len: 3 ??????????ref: NULL ?????????rows: 5007 ????????Extra: Using index 1 row in set (0.17 sec) |
在大多數引擎中，只有當查詢語句所訪問的列是索引的一部分時，索引才會覆蓋。但是，InnoDB不限于此，InnoDB的二級索引在葉子節點中存儲了primary key的值。因此，sakila.actor表使用InnoDB，而且對于是last_name上有索引，所以，索引能覆蓋那些訪問actor_id的查詢，如：
?
| mysql> EXPLAIN SELECT actor_id, last_name ????-> FROM sakila.actor WHERE last_name = 'HOPPER'\G *************************** 1. row *************************** ???????????id: 1 ?select_type: SIMPLE ????????table: actor ?????????type: ref possible_keys: idx_actor_last_name ??????????key: idx_actor_last_name ??????key_len: 137 ??????????ref: const ?????????rows: 2 ????????Extra: Using where; Using index |
?
3.3、利用索引進行排序
MySQL中，有兩種方式生成有序結果集：一是使用filesort，二是按索引順序掃描。利用索引進行排序操作是非常快的，而且可以利用同一索引同時進行查找和排序操作。當索引的順序與ORDER BY中的列順序相同且所有的列是同一方向(全部升序或者全部降序)時，可以使用索引來排序。如果查詢是連接多個表，僅當ORDER BY中的所有列都是第一個表的列時才會使用索引。其它情況都會使用filesort。
| create table actor( actor_id?int unsigned NOT NULL AUTO_INCREMENT, name??????varchar(16) NOT NULL DEFAULT '', password??????? varchar(16) NOT NULL DEFAULT '', PRIMARY KEY(actor_id), ?KEY???? (name) ) ENGINE=InnoDB insert into actor(name,password) values('cat01','1234567'); insert into actor(name,password) values('cat02','1234567'); insert into actor(name,password) values('ddddd','1234567'); insert into actor(name,password) values('aaaaa','1234567'); |
?
?
| mysql> explain select actor_id from actor order by actor_id \G *************************** 1. row *************************** ???????????id: 1 ?select_type: SIMPLE ????????table: actor ?????????type: index possible_keys: NULL ??????????key: PRIMARY ??????key_len: 4 ??????????ref: NULL ?????????rows: 4 ????????Extra:?Using index 1 row in set (0.00 sec) ? mysql> explain select actor_id from actor order by password \G *************************** 1. row *************************** ???????????id: 1 ?select_type: SIMPLE ????????table: actor ?????????type: ALL possible_keys: NULL ??????????key: NULL ??????key_len: NULL ??????????ref: NULL ?????????rows: 4 ????????Extra:?Using filesort 1 row in set (0.00 sec) ? mysql> explain select actor_id from actor order by name \G *************************** 1. row *************************** ???????????id: 1 ?select_type: SIMPLE ????????table: actor ?????????type: index possible_keys: NULL ??????????key: name ??????key_len: 18 ??????????ref: NULL ?????????rows: 4 ????????Extra:?Using index 1 row in set (0.00 sec) |
?當MySQL不能使用索引進行排序時，就會利用自己的排序算法(快速排序算法)在內存(sort buffer)中對數據進行排序，如果內存裝載不下，它會將磁盤上的數據進行分塊，再對各個數據塊進行排序，然后將各個塊合并成有序的結果集（實際上就是外排序）。對于filesort，MySQL有兩種排序算法。
(1)兩遍掃描算法(Two passes)
實現方式是先將須要排序的字段和可以直接定位到相關行數據的指針信息取出，然后在設定的內存（通過參數sort_buffer_size設定）中進行排序，完成排序之后再次通過行指針信息取出所需的Columns。
注：該算法是4.1之前采用的算法，它需要兩次訪問數據，尤其是第二次讀取操作會導致大量的隨機I/O操作。另一方面，內存開銷較小。
(3)?? ?一次掃描算法(single pass)
該算法一次性將所需的Columns全部取出，在內存中排序后直接將結果輸出。
注：從 MySQL 4.1 版本開始使用該算法。它減少了I/O的次數，效率較高，但是內存開銷也較大。如果我們將并不需要的Columns也取出來，就會極大地浪費排序過程所需要的內存。在 MySQL 4.1 之后的版本中，可以通過設置 max_length_for_sort_data 參數來控制 MySQL 選擇第一種排序算法還是第二種。當取出的所有大字段總大小大于 max_length_for_sort_data 的設置時，MySQL 就會選擇使用第一種排序算法，反之，則會選擇第二種。為了盡可能地提高排序性能，我們自然更希望使用第二種排序算法，所以在 Query 中僅僅取出需要的 Columns 是非常有必要的。
當對連接操作進行排序時，如果ORDER BY僅僅引用第一個表的列，MySQL對該表進行filesort操作，然后進行連接處理，此時，EXPLAIN輸出“Using filesort”；否則，MySQL必須將查詢的結果集生成一個臨時表，在連接完成之后進行filesort操作，此時，EXPLAIN輸出“Using temporary;Using filesort”。
?
3.4、索引與加鎖
索引對于InnoDB非常重要，因為它可以讓查詢鎖更少的元組。這點十分重要，因為MySQL 5.0中，InnoDB直到事務提交時才會解鎖。有兩個方面的原因：首先，即使InnoDB行級鎖的開銷非常高效，內存開銷也較小，但不管怎么樣，還是存在開銷。其次，對不需要的元組的加鎖，會增加鎖的開銷，降低并發性。
InnoDB僅對需要訪問的元組加鎖，而索引能夠減少InnoDB訪問的元組數。但是，只有在存儲引擎層過濾掉那些不需要的數據才能達到這種目的。一旦索引不允許InnoDB那樣做（即達不到過濾的目的），MySQL服務器只能對InnoDB返回的數據進行WHERE操作，此時，已經無法避免對那些元組加鎖了：InnoDB已經鎖住那些元組，服務器無法解鎖了。
來看個例子：
| create table actor( actor_id?int unsigned NOT NULL AUTO_INCREMENT, name??????varchar(16) NOT NULL DEFAULT '', password??????? varchar(16) NOT NULL DEFAULT '', PRIMARY KEY(actor_id), ?KEY???? (name) ) ENGINE=InnoDB insert into actor(name,password) values('cat01','1234567'); insert into actor(name,password) values('cat02','1234567'); insert into actor(name,password) values('ddddd','1234567'); insert into actor(name,password) values('aaaaa','1234567'); |
| SET AUTOCOMMIT=0; BEGIN; SELECT actor_id FROM actor WHERE actor_id < 4 AND actor_id <> 1 FOR UPDATE; |
?該查詢僅僅返回2---3的數據，實際已經對1---3的數據加上排它鎖了。InnoDB鎖住元組1是因為MySQL的查詢計劃僅使用索引進行范圍查詢（而沒有進行過濾操作，WHERE中第二個條件已經無法使用索引了）：
?
| mysql> EXPLAIN SELECT actor_id FROM test.actor ????-> WHERE actor_id < 4 AND actor_id <> 1 FOR UPDATE \G *************************** 1. row *************************** ???????????id: 1 ?select_type: SIMPLE ????????table: actor ?????????type: index possible_keys: PRIMARY ??????????key: PRIMARY ??????key_len: 4 ??????????ref: NULL ?????????rows: 4 ????????Extra: Using where; Using index 1 row in set (0.00 sec) ? mysql> |
?表明存儲引擎從索引的起始處開始，獲取所有的行，直到actor_id<4為假，服務器無法告訴InnoDB去掉元組1。
為了證明row 1已經被鎖住，我們另外建一個連接，執行如下操作：
| SET AUTOCOMMIT=0; BEGIN; SELECT actor_id FROM actor WHERE actor_id = 1 FOR UPDATE; |
?
?該查詢會被掛起，直到第一個連接的事務提交釋放鎖時，才會執行（這種行為對于基于語句的復制(statement-based replication)是必要的）。
如上所示，當使用索引時，InnoDB會鎖住它不需要的元組。更糟糕的是，如果查詢不能使用索引，MySQL會進行全表掃描，并鎖住每一個元組，不管是否真正需要。
?
總結
以上是生活随笔為你收集整理的mysql索引的使用和优化的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 关于 屏幕阅读器 和 sr-only
- 下一篇： 套题T4