引子

對于一條SQL腺毫，開發(fā)同學最先關心的啥字柠？ 我覺得還不到這個SQL在數據庫的執(zhí)行過程，而是這條SQL是否能盡快的返回結果淳蔼，在SQL的生命周期里稚虎，每一個環(huán)節(jié)都有足夠的優(yōu)化空間撤嫩，但是我們有沒有想過，SQL優(yōu)化的本質是啥蠢终？終極目標又是啥序攘？其實優(yōu)化本質上就是減少SQL對資源的消耗和依賴茴她，正如數據庫優(yōu)化的終極目的是Do nothing in database一樣，SQL優(yōu)化的終極目的是Consume no resource程奠。

數據庫資源有兩個特性：

• 首先資源是有限的丈牢，大家都搶著用就會有瓶頸的，所以SQL的瓶頸可能是由資源緊張產生的瞄沙。
• 其次資源是有代價的己沛，并且代價各異，比如內存的時延100ns距境， SSD100us申尼，SAS盤10ms，網絡更高肮疗，那么訪問CPU L1/L2/L3 cache的代價就比訪問內存的要低，訪問內存資源的代價要比訪問硬盤資源的代價扒接，所以SQL的瓶頸也可能是訪問了代價比較高的資源導致的伪货。

現代計算機體系下，機器上粗粒度的資源就那么幾種钾怔，無非是CPU碱呼，內存，硬盤宗侦，和網絡愚臀。那么我們來看下SQL需要消耗哪些資源：

• 比較、排序矾利、SQL解析姑裂、函數或邏輯運算需要用到CPU；
• 緩存數據訪問男旗，臨時數據存放需要用到內存舶斧；
• 冷數據讀取，大數據量的排序和關聯察皇，數據寫入落盤茴厉，需要訪問硬盤；
• SQL請求交互什荣，結果集返回需要網絡資源矾缓。

那么SQL優(yōu)化思路自然是減少SQL的解析，減少復雜的運算稻爬，減少數據處理的規(guī)模嗜闻，減少對物理IO的依賴，減少服務器和客戶端的網絡交互桅锄， 本文的每一節(jié)都解決上面的一兩點泞辐，索引策略的組合最大化提升SQL優(yōu)化性能：

• 獨立的列： 減少SQL的解析
• 前綴索引和索引選擇性： 減少數據處理的規(guī)模笔横，減少對物理IO的依賴
• 多列索引：減少對物理IO的依賴
• 選擇和是的索引列順序： 減少數據處理的規(guī)模，減少對物理IO的依賴
• 聚簇索引： 減少數據處理的規(guī)模咐吼，減少對物理IO的依賴
• 覆蓋索引： 減少對物理IO的依賴
• 使用索引掃描來做排序： 減少復雜的運算
• 返回必要的列： 減少對物理IO的依賴吹缔，減少服務器和客戶端的網絡交互

在學習MySQL索引之前，最好先學習MySQL索引背后的數據結構及算法原理锯茄。

獨立的列

獨立的列是指索引列不能是表達式的一部分厢塘，也不能是函數的參數。

例如：下面這個查詢無法使用actor_id列的索引：

mysql> explain select actor_id from actor where actor_id + 1 = 5;
+----+-------------+-------+-------+---------------+---------------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key                 | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | actor | index | NULL          | idx_actor_last_name | 137     | NULL |  200 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------------------+---------+------+------+--------------------------+

憑肉眼容易看出where的表達式其實等價于actor_id=4肌幽，但是MySQL無法自動解析這個函數晚碾。所以應該簡化where條件：始終將索引列單獨放在比較符號的一側，使用索引的正確寫法如下喂急，此時使用主鍵索引：

mysql> explain select actor_id from actor where actor_id = 4;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | actor | const | PRIMARY       | PRIMARY | 2       | const |    1 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+

下面是另外一個常見的錯誤：

mysql>select ...  where to_days(current_date)-to_days(date_col) <=10;

前綴索引和索引選擇性

有時候索引很長的字符列格嘁，讓索引變得大且慢。通常索引開始的部分字符廊移，可以大大節(jié)約索引空間糕簿，從而提高索引效率。但這樣也會降低索引的選擇性狡孔。 索引的選擇性是指：不重復的索引值（也稱為基數懂诗，Cardinality）和數據表的記錄總數(#T)的比值，范圍是 1/#T ~ 1苗膝。索引的選擇性越高則查詢效率越高殃恒，因為選擇性高的索引讓MySQL在查找時過濾掉更多的行。唯一索引的選擇性是1辱揭，這是最好的索引選擇性离唐，性能也是最好的。

一般情況下某個列前綴的選擇性如果足夠高问窃，也是可以滿足查詢性能侯繁。對于BLOB、TEXT或者很長的VARCHAR類型的列泡躯，必須使用前綴索引贮竟，因為MySQL不允許索引這些列的完整長度。如下所示较剃，varchar(4000)類型的comment列最多只能建前綴長度為255的索引咕别。

mysql> show create table shop;
+-------+-------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
+-------+-------------------------------------------------------------------------------------------------------------------------------+
| shop  | CREATE TABLE `demo` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '記錄ID',
  `comment` varchar(4000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_comment` (`comment`(255))
) ENGINE=InnoDB AUTO_INCREMENT=20001 DEFAULT CHARSET=utf8              
|
+-------+-------------------------------------------------------------------------------------------------------------------------------+

訣竅在于選擇足夠長的前綴以保證較高的選擇性，同時又不能太長（以便節(jié)約空間）写穴。前綴應該足夠長惰拱，以使得前綴索引的選擇性接近于索引整個列。換句話說啊送，前綴的”基數“應該接近于完整列的”基數“偿短。

為了決定前綴的合適長度欣孤，需要找到最常見的值的列表，然后和最常見的前綴列表進行比較昔逗。在示例數據Sakila沒有合適的例子降传，所以我們從表city生成一個示例表，生成足夠的數據用來演示：

mysql> CREATE TABLE city_demo (city VARCHAR(50) NOT NULL);

mysql> INSERT INTO city_demo(city) SELECT city from city;
Records: 600  Duplicates: 0  Warnings: 0

重復執(zhí)行下面的sql 五次：

mysql> insert into city_demo(city) select city from city_demo;
Records: 600  Duplicates: 0  Warnings: 0

執(zhí)行下面sql 隨機分布數據：

mysql> update city_demo set city = (select city from city order by RAND() limit 1);
Rows matched: 19200  Changed: 19170  Warnings: 0

示例數據集分布是隨機生成的勾怒，與你的結果會有所不同婆排，但是對于結論沒有影響。首先笔链，我們找到最常見的城市列表：

mysql> select count(*) as cnt, city from city_demo group by city order by cnt desc limit 10;
+-----+-------------------+
| cnt | city              |
+-----+-------------------+
|  60 | London            |
|  49 | Skikda            |
|  48 | Izumisano         |
|  47 | Valle de Santiago |
|  47 | Tegal             |
|  46 | Goinia            |
|  46 | Tychy             |
|  46 | Idfu              |
|  46 | Clarksville       |
|  46 | Paarl             |
+-----+-------------------+

注意到段只，上面每個值都出現了46-60次，現在查找到最頻繁出現的城市前綴鉴扫，先從3個前綴字母開始：

mysql> select count(*) as cnt,left(city,3) as pref from city_demo group by pref order by cnt desc limit 10;
+-----+------+
| cnt | pref |
+-----+------+
| 453 | San  |
| 195 | Cha  |
| 161 | Tan  |
| 157 | Sou  |
| 148 | Shi  |
| 146 | Sal  |
| 145 | al-  |
| 140 | Man  |
| 137 | Hal  |
| 134 | Bat  |
+-----+------+

每個前綴都比原來的城市出現的次數要多赞枕，因此唯一前綴比唯一城市要少得多。然后我們增加前綴長度坪创，直到這個前綴的選擇性接近完整列的選擇性炕婶。經過實驗發(fā)現前綴長度為7時最為合適：

mysql> select count(*) as cnt,left(city,7) as pref from city_demo group by pref order by cnt desc limit 10;
+-----+---------+
| cnt | pref    |
+-----+---------+
|  74 | Valle d |
|  70 | Santiag |
|  61 | San Fel |
|  60 | London  |
|  49 | Skikda  |
|  48 | Izumisa |
|  47 | Tegal   |
|  46 | Tychy   |
|  46 | Goinia  |
|  46 | Idfu    |
+-----+---------+

計算合適的前綴長度的另外一個方法就是計算完整列的選擇性，并使前綴的選擇性接近于完整列的選擇性误堡。下面展示計算完整列的選擇性：

mysql> select count(distinct city)/count(*) from city_demo;
+-------------------------------+
| count(distinct city)/count(*) |
+-------------------------------+
|                        0.0312 |
+-------------------------------+

通常來說（盡管也有例外情況）古话，這個例子中如果前綴的選擇性能夠接近于0.031雏吭，基本上就可用了锁施。可以在一個查詢中針對不同的前綴長度進行計算杖们，這對于大表非常有用悉抵。下面給出了如何在同一個查詢中計算不同前綴長度的選擇性：

mysql> select 
    -> count(distinct left(city,3))/count(*) as sel3,
    -> count(distinct left(city,4))/count(*) as sel4,
    -> count(distinct left(city,5))/count(*) as sel5,
    -> count(distinct left(city,6))/count(*) as sel6,
    -> count(distinct left(city,7))/count(*) as sel7
    -> from city_demo;
+--------+--------+--------+--------+--------+
| sel3   | sel4   | sel5   | sel6   | sel7   |
+--------+--------+--------+--------+--------+
| 0.0239 | 0.0293 | 0.0305 | 0.0309 | 0.0310 |
+--------+--------+--------+--------+--------+

查詢顯示當前綴長度達7的時候，再增加前綴長度摘完，選擇性提升的幅度已經很小姥饰，但是增加的前綴索引占用空間。

只看平均選擇性是不夠的孝治，也有例外的情況列粪，需要考慮最壞情況下的選擇性。平均選擇性會讓你認為前綴長度為4或者5的索引已經足夠了谈飒，但如果數據分布很不均勻岂座，可能就會有陷阱。如果觀察前綴為4的最常出現城市的次數杭措，可以看到明顯不均勻：

mysql> select count(*) as cnt,left(city,4) as pref from city_demo group by pref order by cnt desc limit 5;
+-----+------+
| cnt | pref |
+-----+------+
| 198 | Sant |
| 186 | San  |
| 124 | Sout |
| 106 | Toul |
| 102 | Chan |
+-----+------+

如果前綴是4個字節(jié)费什，則最常出現的前綴的出現次數比最常出現的城市的出現次數要大很多。即這些值的選擇性比平均選擇性要低手素。如果有比這個隨機生成的示例更真實的數據鸳址，就更有可能看到這種現象瘩蚪。例如在真實的城市名上建一個長度為4的前綴索引，對于以“San”和“New”開頭的城市的選擇性就會非常糟糕稿黍，因為很多城市都以這兩個詞開頭疹瘦。

在上面的示例中，已經找到了合適的前綴長度闻察，下面演示一下如何創(chuàng)建前綴索引：

mysql>alter table city_demo add index idx_city(city(7));

前綴索引是一種能使索引更小更快的有效辦法拱礁，但另一方面也有其缺點：MySQL無法使用前綴索引做ORDER BY和GROUP BY，也無法使用前綴索引做覆蓋掃描辕漂。

有時候后綴索引(suffix index)也有用途（例如呢灶，找到某個域名的所有電子郵件地址）。MySQL原生并不支持反向索引钉嘹，但是可以把字符串反轉后存儲鸯乃，并基于此建立前綴索引“匣粒可以通過觸發(fā)器來維護這種索引缨睡。

多列索引

很多人對多列索引的理解都不夠。一個常見的錯誤就是陈辱，為每個列創(chuàng)建獨立的索引奖年，或者按照錯誤的順序創(chuàng)建多列索引。

先來看第一個問題沛贪，為每個列創(chuàng)建獨立的索引陋守，從show create table 中很容易看到這種情況：

create talbe t (
        c1 int,
        c2 int,
        c3 int,
        key(c1),
        key(c2),
        key(c3)
);

這種索引策略，一般是人們聽到一些專家諸如“把where條件里面的列都建上索引”這樣模糊的建議導致的利赋。實際上這個建議非常錯誤水评。這樣一來最好的情況下也只能是“一星”索引（關于三星索引可以參考拙作《高性能MySQL》讀后感——B-Tree索引的三星索引說明），其性能比起真正最優(yōu)的索引可能差幾個數量級媚送。有時如果無法設計一個“三星”索引中燥，那么不如忽略掉where子句，集中精力優(yōu)化索引列的順序塘偎，或者創(chuàng)建一個全覆蓋索引疗涉。

在多個列上建立獨立的單列索引大部分情況下并不能提高MySQL的查詢性能。MySQL 5.0和更新的版本引入了一種叫”索引合并”（index merge)策略吟秩，一定程度上可以使用表上的多個單列索引來定位指定的行咱扣。

索引合并策略有時候是一種優(yōu)化的結果，但大多數時候說明表索引建得很糟糕：

• 當出現服務器對多個索引做相交操作時（通常有多個AND條件）峰尝，通常意味著需要一個包含所有相關列的多列索引偏窝，而不是多個獨立的單列索引。
• 當服務器需要對多個索引做聯合操作時（通常有多個OR條件），通常需要耗費大量CPU和內存資源在算法的緩存祭往、排序和合并操作上伦意。特別是當其中有些索引的選擇性不高，需要合并掃描返回的大量數據的時候硼补。
• 更重要的是驮肉，優(yōu)化器不會把這些計算到“查詢成本”（cost）中，優(yōu)化器只關心隨機頁面讀取已骇。這使得查詢的成本被“低估”离钝，導致該執(zhí)行計劃還不如直接走全表掃描。這樣做不但消耗更多的CPU和內存資源褪储，還可能影響查詢的并發(fā)性卵渴，但如果是單獨運行這樣的查詢，則往往忽略對并發(fā)性的影響鲤竹。

如果在explain中看到有索引合并浪读，應該好好檢查一下查詢和表的結構，看是不是已經是最優(yōu)的辛藻。也可以通過參數optimizer_switch來關閉索引合并功能碘橘。也可以使用ignore index提示讓優(yōu)化器忽略掉某些索引。

選擇合適的索引列順序

我們遇到的最容易引起困惑的問題就是索引列的順序吱肌。正確的順序依賴于使用該索引的查詢痘拆，并且同時需要考慮如何更好地滿足排序和分組的需要（順便說明，本節(jié)內容適用于B-Tree索引氮墨；哈戏那或者其他類型的索引并不會像B-Tree索引一樣按順序存儲數據）。

在一個多列B-Tree索引中勇边，索引列的順序意味著索引首先按照最左列進行排序犹撒，其次是第二列折联，等等粒褒。所以，索引可以按照升序或者降序進行掃描诚镰，以滿足精確符合列順序order by奕坟，group by和distinct等子句的查詢需求。

所以多列索引的列順序至關重要清笨。

對于如何選擇索引的順序有一個經驗法則：將選擇性最高的列放到索引最前列月杉。這個建議有用嗎？在某些場景可能有幫助抠艾，但通常不如避免隨機IO和排序那么重要苛萎，考慮問題需要更全面（場景不同則選擇不同，沒有一個放之四海皆準的法則。這里只是說明腌歉，這個經驗法則可能沒有你想象的重要）蛙酪。

當不需要考慮排序和分組時，將選擇性最高的列放在前面通常是最好的翘盖。這時候索引的作用只是用于優(yōu)化where條件的查找桂塞。在這種情況下，這樣設計的索引確實能夠最快的過濾出需要的行馍驯，對于在where子句中使用了索引部分前綴列的查詢來說選擇性也更高阁危。然而，性能不只是依賴于所有索引列的選擇性（整體基數）汰瘫，和查詢條件的具體值也有關系狂打，也就是和值的分布有關。這和前面介紹的選擇前綴的長度需要考慮的地方一樣混弥×飧福可能需要根據那些運行頻率最高的查詢來調整索引列的順序，讓這種情況下索引的選擇性最高剑逃。

以下面的查詢?yōu)槔?/p>

select * from payment where staff_id=2 and customer_id=584;

是應該創(chuàng)建一個(staff_id,customer_id)索引還是應該顛倒一下順序浙宜？可以跑一些查詢來確定在這個表中值的分布情況，并確定哪個列的選擇性更高蛹磺。先用下面的查詢預測一下粟瞬，看看各個where條件的分支對應的數據基數有多大：

mysql> select sum(staff_id=2),sum(customer_id=584) from payment \G
*************************** 1. row ***************************
     sum(staff_id=2): 7992
sum(customer_id=584): 30

根據前面的經驗法則，應該將索引customer_id放到前面萤捆，因為對應條件值的customer_id數量更小裙品。我們再來看看對于這個customer_id的條件值，對應的staff_id列的選擇性如何：

mysql> select sum(staff_id=2) from payment where customer_id=584\G
*************************** 1. row ***************************
sum(staff_id=2): 17

這樣做有一個地方需要注意俗或，查詢的結果非常依賴于特定的具體值市怎。如果按上述辦法優(yōu)化，可能對其他一些條件值的查詢不公平辛慰，服務器的整體性能可能變得更糟区匠，或者其他某些查詢的運行變得不如預期。

如果是從諸如pt-query-digest這樣的工具的報告中提取“最差”查詢帅腌，那么再按上述辦法選定的索引順序往往是非常高效的驰弄。如果沒有類似的具體查詢來運行，那么最好按經驗法則來做速客，因為經驗法則考慮的是全局基數和選擇性戚篙，而不是某個具體查詢：

mysql> select count(distinct staff_id)/count(*) as staff_id_selectivity,
    -> count(distinct customer_id)/count(*) as customer_id_selectivity,
    -> count(*)
    -> from payment\G
*************************** 1. row ***************************
   staff_id_selectivity: 0.0001
customer_id_selectivity: 0.0373
               count(*): 16049

customer_id的選擇性更高，所以答案是將其作為索引列的第一列：

mysql>alter table payment add index idx_cust_staff_id(customer_id,staff_id);

當使用前綴索引的時候溺职，在某些條件值的基數比正常值高的時候岔擂，問題就來了位喂。例如，在某些應用程序中乱灵，對于沒有登錄的用戶忆某，都將其用戶名記錄為”guest”，在記錄用戶行為的會話表和其他記錄用戶活動的表中”guest”就成為了一個特殊用戶ID阔蛉。一旦查詢涉及這個用戶弃舒，那么和對于正常用戶的查詢就大不同了，因為通常有很多會話都是沒有登錄的状原。系統賬號也會導致類似的問題聋呢。一個應用通常都有一個特殊的管理員賬號，和普通賬號不同颠区，它并不是一個具體的用戶削锰，系統中所有的其他用戶都是這個用戶的好友，所以系統往往通過它向網站的所有用戶發(fā)送狀態(tài)通知和其他消息毕莱。這個賬號的巨大的好友列表很容易導致網站出現服務器性能問題器贩。

覆蓋索引

通常大家都會根據查詢的where條件來創(chuàng)建合適的索引，不過這只是索引優(yōu)化的一個方面朋截。設計優(yōu)秀的索引應該考慮到整個查詢蛹稍，而不單單是where條件部分。索引確實是一種查找數據的高效方式部服，但是MySQL也可以使用索引來直接獲取列的數據唆姐，這樣就不再索引讀取數據行。如果索引的葉子節(jié)點中已經包含要查詢的數據廓八，那么還有什么必要再回表查詢呢奉芦？如果一個索引包含（或者說覆蓋）所有需要查詢的字段的值，我們就稱之為“覆蓋索引”剧蹂。

覆蓋索引是非常有用的工具声功，能夠極大地提高性能〕璧穑考慮一下如果查詢只需要掃描索引而無須回表先巴，會帶來多少好處：

• 索引條目通常遠小于數據行大小，如果只讀取索引车吹，那么MySQL訪問更少數據量筹裕。這對緩存的負載非常重要醋闭，因為這種情況下響應時間大部分花費在數據拷貝上窄驹。覆蓋索引對于IO密集型的應用也有幫助，因為索引比數據還小证逻，更容易全部放入內存中（這對于MyISAM尤其正確乐埠，因為MyISAM能壓縮索引以變得更小）。
• 索引按照列值順序存儲（至少在單個頁內是如此）丈咐，對于IO密集型的范圍查詢會比隨機從磁盤讀取每一行數據的IO要少得多瑞眼。
• 大多數據引擎能更好的緩存索引。比如MyISAM在內存中只緩存索引棵逊，數據則依賴于操作系統來緩存伤疙，因此訪問數據多一次系統調用。
• 覆蓋索引對于InnoDB表特別有用辆影，因為InnoDB使用聚集索引組織數據徒像。InnoDB的二級索引在葉子節(jié)點中保存行的主鍵值，如果二級索引能夠覆蓋查詢蛙讥，則可以避免對主鍵索引的二次查詢锯蛀。

在所有這些場景中，在索引中滿足查詢的成本一般比查詢行要小得多次慢。

對于索引覆蓋查詢(index-covered query)旁涤，使用EXPLAIN時，在Extra一列中看到“Using index”迫像。例如劈愚，在sakila的inventory表中，有一個組合索引(store_id,film_id)闻妓，對于只需要訪問這兩列的查詢造虎，MySQL就可以使用覆蓋索引，如下：

mysql> EXPLAIN SELECT store_id, film_id FROM 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: 5341
        Extra: Using index

在大多數引擎中纷闺，只有當查詢語句所訪問的列是索引的一部分時算凿，索引才會覆蓋。但是犁功，InnoDB不限于此氓轰，InnoDB的二級索引在葉子節(jié)點中存儲了primary key的值。例如浸卦，sakila.actor表使用InnoDB署鸡，而且對于是last_name上有二級索引，所以索引能覆蓋那些訪問actor_id的查詢：

mysql> EXPLAIN SELECT actor_id, last_name FROM 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

使用索引掃描來做排序

MySQL有兩種方式生成有序的結果：

• 通過排序操作限嫌，Explain 的Extra 輸出“Using filesort”靴庆， MySQL使用文件排序；
• 通過索引順序掃描怒医，Explain的type列值為index炉抒，MySQL使用索引掃描排序（不要和Extra 列的“Using index”混淆）。

掃描索引本身很快稚叹，因為只需從一條記錄移動到緊接著的下一條記錄焰薄。但如果索引不能覆蓋查詢所需的全部列拿诸，就要每掃描一條索引記錄得回表查詢一次對應的行。這基本上都是隨機IO塞茅，因此按索引順序讀取數據的速度通常比順序的全表掃描慢亩码，尤其是在IO密集型。所以野瘦，設計索引時讓同一個索引既滿足排序描沟，又用于查找行，避免隨機IO鞭光。

當索引的列的順序和ORDER BY子句的順序完全一致芽丹，并且所有列的排序方向（倒序和正序）都一樣時榄融，MySQL才能使用索引來對結果做排序弹砚。如果查詢需要關聯多張表无切，則只有ORDER BY子句引用的字段全部為第一個表時，才能使用索引來做排序啡省。ORDER BY子句和Where查詢的限制是一樣的：需要滿足索引的最左前綴的要求娜睛。

當MySQL不能使用索引進行排序時，就會利用自己的排序算法(快速排序算法)在內存(sort buffer)中對數據進行排序卦睹，如果內存裝載不下畦戒，它會將磁盤上的數據進行分塊，再對各個數據塊進行排序结序，然后將各個塊合并成有序的結果集（實際上就是外排序障斋，使用臨時表）。
對于filesort徐鹤，MySQL有兩種排序算法垃环。
(1)兩次掃描算法(Two passes)
實現方式是先將需要排序的字段和可以直接定位到相關行數據的指針信息取出，然后在設定的內存（通過參數sort_buffer_size設定）中進行排序返敬，完成排序之后再次通過行指針信息取出所需的Columns遂庄。
注：該算法是4.1之前采用的算法，它需要兩次訪問數據劲赠，尤其是第二次讀取操作會導致大量的隨機I/O操作涛目。另一方面，內存開銷較小凛澎。

(2)一次掃描算法(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”抚芦。

當前導列為常量時，ORDER BY子句可以不滿足索引的最左前綴要求迈螟。例如叉抡，Sakila數據庫的表rental在列(rental_date,inventory_id,customer_id)上有名為rental_date的索引，如下表所示答毫。

CREATE TABLE `rental` (
  `rental_id` int(11) NOT NULL AUTO_INCREMENT,
  `rental_date` datetime NOT NULL,
  `inventory_id` mediumint(8) unsigned NOT NULL,
  `customer_id` smallint(5) unsigned NOT NULL,
  `return_date` datetime DEFAULT NULL,
  `staff_id` tinyint(3) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`rental_id`),
  UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
  KEY `idx_fk_inventory_id` (`inventory_id`),
  KEY `idx_fk_customer_id` (`customer_id`),
  KEY `idx_fk_staff_id` (`staff_id`),
  CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8;

MySQL使用rental_date索引為下面的查詢排序卜壕，從EXPLAIN中看出沒有出現filesort：

mysql> EXPLAIN SELECT rental_id, staff_id FROM rental WHERE rental_date = '2005-05-25' ORDER BY inventory_id, customer_id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
         type: ref
possible_keys: rental_date
          key: rental_date
      key_len: 8
          ref: const
         rows: 1
        Extra: Using where

即使ORDER BY 字句不滿足最左前綴索引，也可以用于查詢排序烙常，因為索引的第一列被指定為常數轴捎。

下面這個查詢可以利用索引排序，是因為查詢?yōu)樗饕牡谝涣刑峁┝顺Ａ織l件蚕脏，用第二列進行排序侦副，將兩列組合在一起，就形成了索引的最左前綴：
... where rental_date = '2005-05-25' order by inventory_id desc;
下面這個查詢也沒問題驼鞭，因為order by使用的就是索引的最左前綴：
... where rental_data > '2005-05-25' order by rental_date,inventory_id;

下面一些不能使用索引做排序的查詢：

• 下面這個查詢使用兩種不同的排序方向：
  ... where rental_date = '2005-05-25' order by inventory_id desc,customer_id asc;
• 下面這個查詢的order by 子句中引用一個不在索引中的列(staff_id)：
  ... where rental_date = '2005-05-25' order by inventory_id,staff_id;
• 下面這個查詢的where 和 order by的列無法組合成索引的最左前綴：
  ... where rental_date = '2005-05-25' order by customer_id;
• 下面這個查詢在索引列的第一列是范圍條件秦驯，所以MySQL無法使用索引的其余列：
  ... where rental_date > '2005-05-25' order by customer_id;
• 這個查詢在inventory_id列上有多個等于條件。對于排序來說挣棕，這也是一種范圍查詢：
  ... where rental_date = '2005-05-25' and inventory_id in(1,2) order by customer_id;

壓縮（前綴壓縮）索引

MyISAM使用前綴壓縮來減少索引的大小译隘，讓更多的索引可以放入內存中亲桥，這在某些情況下能極大地提高性能。默認只壓縮字符串固耘，但通過參數設置也可以對整數做壓縮题篷。MyISAM壓縮每個索引塊的方法是，先完全保存索引塊中的第一個值厅目，然后將其他值和第一個值進行比較得到相同前綴的字節(jié)數和剩余的不同后綴部分番枚，把這部分存儲起來即可。例如：索引塊中的第一個值是“perform”损敷，第二個值是“performance”葫笼，那么第二個值的前綴壓縮后存儲的是類似“7,ance”這樣的形式。MyISAM對行指針也采用類似的前綴壓縮方式拗馒。

壓縮塊使用更少的空間路星，代價是某些操作可能更慢。因為每個值的壓縮前綴都依賴前面的值诱桂，所以MyISAM查找時無法在索引塊使用二分查找而只能從頭開始掃描洋丐。正序的掃描速度還不錯，但是如果是倒序掃描——例如ORDER BY DESC——就不是很好访诱。所以在塊中查找某一行的操作平均都需要掃描半個索引塊垫挨。

測試表明，對于CPU密集型應用触菜，因為掃描需要隨機查找九榔，壓縮索引使得MyISAM在索引查找上要慢好幾倍。壓縮索引的倒序掃描就更慢了涡相。壓縮索引需要在CPU內存資源與磁盤之間做權衡哲泊。壓縮索引可能只需要十分之一大小的磁盤空間，如果是IO密集型應用催蝗，對某些查詢帶來的好處會比成本多很多切威。

可以在CREATE TABLE 語句中指定pack_keys參數來控制索引壓縮的方式。

冗余和重復索引

MySQL允許在相同列上創(chuàng)建多個索引丙号，MySQL需要單獨維護重復的索引先朦，并且優(yōu)化器在優(yōu)化查詢的時候需要逐個地進行考慮，影響查詢性能犬缨。

重復索引是指在相同的列上按照相同的順序創(chuàng)建相同類型的索引喳魏。應該避免這樣創(chuàng)建重復索引，發(fā)現以后也應該立即移除怀薛。

如下面的代碼刺彩，創(chuàng)建一個主鍵，先加上唯一限制，然后再加上索引以供查詢使用创倔。事實上嗡害，MySQL的唯一限制和主鍵限制都是通過索引實現的，因此畦攘，實際上在相同的列上創(chuàng)建了三個重復的索引霸妹。通常并沒有理由這樣做，除非在同一列上創(chuàng)建不同類型的索引來滿足不同的查詢需求念搬。

create table test(
  id int not null primary key,
  a int not null,
  b int not null,
  unique(id),
  index(id)
) engine=InnoDB;

冗余索引和重復索引有一些不同抑堡。如果創(chuàng)建了索引(a,b)摆出，再創(chuàng)建索引(a)就是冗余索引朗徊，因為這只是前一個索引的前綴索引。因此索引(a,b)也可以當作索引(a)來使用（這種冗余只是對B-Tree索引來說的）偎漫。但是如果再創(chuàng)建索引(b,a)爷恳，則不是冗余索引，索引(b)也不是象踊，因為b不是索引(a,b)的最左前綴列温亲。另外其他不同類型的索引（例如哈希索引或者全文索引）也不會是B-Tree索引的冗余索引，而無論覆蓋的索引列是什么杯矩。

冗余索引通常發(fā)生在為表添加新索引的時候栈虚。例如，有人可能會增加一個新的索引(a,b)而不是擴展已有的索引(a)史隆。還有一種情況是將一個索引擴展為(a,id)魂务，其中id是主鍵，對于InnoDB來說主鍵列已經包含在二級索引中了泌射，所以這也是冗余的粘姜。

大多數情況下都不需要冗余索引，應該盡量擴展已有的索引而不是創(chuàng)建新索引熔酷。但也有時候出于性能方面的考慮需要冗余索引孤紧，因為擴展已有的索引會導致其變得太大，從而影響其他使用該索引的查詢的性能拒秘。

例如：如果在整數列上有一個索引号显，現在需要額外增加一個很長的varchar列來擴展該索引，那性能可能會急劇下降躺酒。特別是有查詢把這個索引當作覆蓋索引押蚤，或者這是MyISAM表并且有很多范圍查詢（由于MyISAM的前綴壓縮）的時候。

舉例阴颖，MyISAM引擎活喊，表userinfo有100W行記錄，每個state_id值大概2W行，在state_id列有一個索引對下面的查詢有用钾菊，假設查詢名為Q1：

mysql> select count(*) from userinfo where state_id=5;

查詢測試結果：QPS=115帅矗。還有一個相關查詢檢索幾個列的值，而不是統計行數煞烫，假設名為Q2：

mysql> select state_id,city,address from userinfo where state_id=5;

查詢測試結果：QPS<10浑此。提升該查詢的性能可以擴展索引為為(state_id, city, address)，讓索引覆蓋查詢：

mysql> ALTER TABLE userinfo DROP KEY state_id, 
    ->     ADD KEY state_id_2 (state_id, city, address);

如果把state_id索引擴展為(state_id,city,address)滞详，那么第二個查詢的性能更快了凛俱，但是第一個查詢卻變慢了，如果要兩個查詢都快料饥，那么就必須要把state_id列索引進行冗余了蒲犬。但如果是innodb表，不冗余state_id列索引對第一個查詢的影響并不明顯岸啡，因為innodb沒有使用索引壓縮原叮，

MyISAM和InnoDB表使用不同索引策略的查詢QPS測試結果（以下測試數據僅供參考）：

上表結論：

• 對于MyISAM引擎，把state_id擴展為state_id_2(state_id,city,address)巡蘸，Q2的QPS更高奋隶，覆蓋索引起作用；但是Q1的QPS下降明顯悦荒，受MyISAM的前綴壓縮影響需要從索引塊頭開始掃描唯欣。
• 對于InnoDB引擎，把state_id擴展為state_id_2(state_id,city,address)搬味，Q2的QPS更高境氢，覆蓋索引起作用；但是Q1的QPS下降不明顯身腻，因為InnoDB沒有使用索引壓縮产还。
• MyISAM引擎需要建state_id和state_id_2索引，才能保證Q1/Q2性能最佳嘀趟；而InnoDB引擎只需state_id_2索引就能保證Q1/Q2性能最佳脐区，從這里看出，索引壓縮也并不是最好的她按。

有兩個索引的缺點是索引成本更高牛隅，下表是在不同的索引策略時插入InnoDB和MyISAM表100W行數據的速度（以下測試數據僅供參考）：

可以看到，不論什么引擎酌泰，索引越多媒佣，插入速度越慢，特別是新增索引后導致達到了內存瓶頸的時候陵刹，所以默伍，要避免冗余索引和重復索引。

在刪除索引的時候要非常小心：如果在InnoDB引擎表上有where a=5 order by id 這樣的查詢，那么索引（a）就會很有用也糊，索引(a,b)實際上是（a,b,id）索引炼蹦，這個索引對于where a=5 order by id 這樣的查詢就無法使用索引做排序，而只能使用文件排序(filesort)狸剃。
舉例說明掐隐，表shop表結構如下：

CREATE TABLE `shop` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '記錄ID',
  `shop_id` int(11) NOT NULL COMMENT '商店ID',
  `goods_id` int(11) NOT NULL COMMENT '物品ID',
  `pay_type` tinyint(1) NOT NULL COMMENT '支付方式',
  `price` decimal(10,2) NOT NULL COMMENT '物品價格',
  `comment` varchar(4000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `shop_id` (`shop_id`,`goods_id`),
  KEY `price` (`price`),
  KEY `pay_type` (`pay_type`),
  KEY `idx_comment` (`comment`(255))
) ENGINE=InnoDB AUTO_INCREMENT=20001 DEFAULT CHARSET=utf8 COMMENT='商店物品表'

如下情況，使用pay_type索引：

mysql> explain select * from shop where pay_type = 2 order by id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: shop
         type: ref
possible_keys: pay_type
          key: pay_type
      key_len: 1
          ref: const
         rows: 9999
        Extra: Using where

如下情況钞馁，雖然使用shop_id索引虑省，但是無法使用索引做排序，EXPLAIN出現filesort：

mysql> explain select * from shop where shop_id = 2 order by id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: shop
         type: ref
possible_keys: shop_id
          key: shop_id
      key_len: 4
          ref: const
         rows: 1
        Extra: Using where; Using filesort

如下情況僧凰，當WHERE 條件覆蓋索引shop_id的所有值時探颈，使用索引做排序，EXPLAIN沒有filesort：

mysql> explain select * from shop where shop_id = 2 and goods_id = 2 order by id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: shop
         type: const
possible_keys: shop_id
          key: shop_id
      key_len: 8
          ref: const,const
         rows: 1
        Extra: 

索引和鎖

索引可以讓查詢鎖定更少的行允悦。如果你的查詢從不訪問那些不需要的行膝擂，那么就會鎖定更少的行虑啤，從兩個方面來看這對性能都有好處隙弛。首先，雖然InnoDB的行鎖效率很高狞山，內存使用也很少全闷，但是鎖定行的時候仍然會帶來額外開銷；其次萍启，鎖定超過需要的行會增加鎖爭用并減少并發(fā)性总珠。

InnoDB只有在訪問行的時候才會對其加鎖，而索引能夠減少InnoDB訪問的行數勘纯，從而減少鎖的數量局服。但這只有當InnoDB在存儲引擎層能夠過濾掉所有不需要的行時才有效。如果索引無法過濾掉無效的行驳遵，那么在InnoDB檢索到數據并返回給服務器層以后淫奔，MySQL服務器才能應用 where子句。這時已經無法避免鎖定行了：InnoDB已經鎖住這些行堤结，到適當的時候才釋放唆迁。在MySQL5.1及更新的版本中，InnoDB可以在服務器端過濾掉行后就釋放鎖竞穷。

下面的例子再次使用Sakila很好的解釋這些情況：

圖1 索引和鎖（1）

這條查詢只返回2~4行數據唐责，實際上獲取1~4行排他鎖。InnoDB鎖住第1行瘾带，因為MySQL為該查詢選擇的執(zhí)行計劃是索引范圍掃描：

mysql> explain select actor_id from actor where actor_id < 5 and actor_id <> 1 FOR UPDATE\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: NULL
         rows: 3
        Extra: Using where; Using index

換句話說鼠哥，底層存儲引擎的操作是“從索引的開頭獲取滿足條件 actor_id < 5 的記錄”，服務器并沒有告訴InnoDB可以過濾第1行的WHERE 條件。Explain的Extra出現“Using Where”表示MySQL服務器將存儲引擎返回行以后再應用WHERE 過濾條件朴恳。

我們來證明第1行確實是被鎖定科盛，保持這個終端鏈接不關閉，然后我們打開另一個終端菜皂。如圖2贞绵，這個查詢會掛起，直到第1個事務釋放第1行的鎖恍飘。

圖2 索引和鎖（2）

按照這個例子榨崩，即使使用索引，InnoDB也可能鎖住一些不需要的數據章母。如果不能使用索引查找和鎖定行的話母蛛，結果會更糟。MySQL會全表掃描并鎖住所有的行乳怎，而不管是不是需要彩郊。