MySQL執行計劃extra中的using index 和 using where using index 的區別

本文出處：http://www.cnblogs.com/wy123/p/7366486.html 
（保留出處並非什麼原創作品權利，本人拙作還遠遠達不到，僅僅是為了鏈接到原文，因為後續對可能存在的一些錯誤進行修正或補充，無他）

mysql執行計劃中的extra列中表明瞭執行計劃的每一步中的實現細節，其中包含了與索引相關的一些細節信息
其中跟索引有關的using index 在不同的情況下會出現Using index， Using where Using index ，Using index condition等
那麼Using index 和 Using where;Using index 有什麼區別？網上搜了一大把文章，說實在話也沒怎麼弄懂，於是就自己動手試試。

本文僅從最簡單的單表去測試using index 和 using where using index以及簡單測試using index condition的情況的出現時機 。
執行計劃的生成與表結構，表數據量，索引結構，統計信息等等上下文等多種環境有關，無法一概而論，複雜情況另論。

測試環境搭建

　　測試表以及測試數據搭建，類似於訂單表和訂單明細表，暫時先用訂單表做測試

　　測試表結構

create table test_order
(
    id int auto_increment primary key,
    user_id int,
    order_id int,
    order_status tinyint,
    create_date datetime
);

create table test_orderdetail
(
    id int auto_increment primary key,
    order_id int,
    product_name varchar(100),
    cnt int,
    create_date datetime
);

create index idx_userid_order_id_createdate on test_order(user_id,order_id,create_date);

create index idx_orderid_productname on test_orderdetail(order_id,product_name);

測試數據（50W）

CREATE DEFINER=`root`@`%` PROCEDURE `test_insertdata`(IN `loopcount` INT)
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
    declare v_uuid  varchar(50);
    while loopcount>0 do
        set v_uuid = uuid();
        insert into test_order (user_id,order_id,order_status,create_date) values (rand()*1000,id,rand()*10,DATE_ADD(NOW(), INTERVAL - RAND()*20000 HOUR));
        insert into test_orderdetail(order_id,product_name,cnt,create_date) values (rand()*100000,v_uuid,rand()*10,DATE_ADD(NOW(), INTERVAL - RAND()*20000 HOUR));
        set loopcount = loopcount -1;
    end while;
END

Using index VS Using where Using index

　　首先，在"訂單表"上，這裡是一個多列複合索引
　　create index idx_userid_order_id_createdate on test_order(user_id,order_id,create_date);

Using index 

1，查詢的列被索引覆蓋，並且where篩選條件是索引的是前導列，Extra中為Using index

Using where Using index

　　1，查詢的列被索引覆蓋，並且where篩選條件是索引列之一但是不是索引的不是前導列，Extra中為Using where; Using index，意味著無法直接通過索引查找來查詢到符合條件的數據

2，查詢的列被索引覆蓋，並且where篩選條件是索引列前導列的一個範圍，同樣意味著無法直接通過索引查找查詢到符合條件的數據

NULL（既沒有Using index，也沒有Using where Using index，也沒有using where）

　　1，查詢的列未被索引覆蓋，並且where篩選條件是索引的前導列，
　　     意味著用到了索引，但是部分欄位未被索引覆蓋，必須通過“回表”來實現，不是純粹地用到了索引，也不是完全沒用到索引，Extra中為NULL(沒有信息)

Using where

　　1，查詢的列未被索引覆蓋，where篩選條件非索引的前導列，Extra中為Using where

　　2，查詢的列未被索引覆蓋，where篩選條件非索引列，Extra中為Using where

　　using where 意味著通過索引或者表掃描的方式進程where條件的過濾，
　　反過來說，也就是沒有可用的索引查找，當然這裡也要考慮索引掃描+回表與表掃描的代價。
　　這裡的type都是all，說明MySQL認為全表掃描是一種比較低的代價。

 Using index condition

　　1，-- 查詢的列不全在索引中，where條件中是一個前導列的範圍

　　2，查詢列不完全被索引覆蓋，查詢條件完全可以使用到索引（進行索引查找）

　　參考：MySQL · 特性分析 · Index Condition Pushdown (ICP)

　　多表關聯的時候Using index condition出現的情況更多，目前還不怎麼理解Using index condition的內部實現模式。

結論：
　　1,Extra中的為Using index的情況
　　　　where篩選列是索引的前導列 &&查詢列被索引覆蓋 && where篩選條件是一個基於索引前導列的查詢，意味著通過索引超找就能直接找到符合條件的數據，並且無須回表

　　2,Extra中的為空的情況
　　　　查詢列存在未被索引覆蓋&&where篩選列是索引的前導列，意味著通過索引超找並且通過“回表”來找到未被索引覆蓋的欄位，

3,Extra中的為Using where Using index：
　　出現Using where Using index意味著是通過索引掃描（或者表掃描）來實現sql語句執行的，即便是索引前導列的索引範圍查找也有一點範圍掃描的動作，不管是前非索引前導列引起的，還是非索引列查詢引起的。

尚未解決的問題：

　　　　 查詢1

查詢2

　　　　查詢3（邏輯上等價於查詢1+查詢2），執行計劃發生了很大的變化。

總結：

　　MySQL執行計劃中的Extra中信息非常多，不僅僅包括Using index，Using where Using index，Using index condition，Using where，尤其是在多表連接的時候，這一點在相對MSSQL來說，不夠直觀或者結構化。
　　MSSQL中是通過區分索引查找（index seek），索引掃描（index scan），表掃描（table scan）來實現具體的查詢的，這圖形化的執行計劃在不同的場景下是非常直觀的，要想完全弄懂MySQL的這個執行計劃，可能要更多地在實踐中摸索。