一、Using where;Using index和Using index condition區(qū)別是什么

using index代表使用覆蓋索引，不用回表using where代表數(shù)據(jù)庫引擎返回結(jié)果后mysql server還會(huì)再次篩選。（引擎就是innodb這種，要注意區(qū)分引擎和mysql server的區(qū)別。）using condition index代表使用二級(jí)索引不夠還要回表，但回表之前會(huì)過濾此二級(jí)索引能過濾的where條件?？傊琔sing where、Using index、Using index condition三者的區(qū)別主要在于是否需要全表掃描、是否使用了覆蓋索引、以及是否采用了索引條件過濾等技術(shù)來提高查詢性能。使用合適的索引和查詢方式，可以顯著提高M(jìn)ySQL的查詢性能和效率。

二、MySQL索引優(yōu)化Using where、Using filesort

1、官方定義

Explain分析SQL語句的時(shí)候，經(jīng)常發(fā)現(xiàn)有的語句在Extra列會(huì)出現(xiàn)Using filesort，根據(jù)MySQL官方文檔對(duì)他的描述：

MySQL must do an extra pass to find out how to retrieve the rows in sorted order. The sort is done by going through all rows according to the join type and storing the sort key and pointer to the row for all rows that match the WHERE clause。

中文手冊上的翻譯：Mysql需要額外的一次傳遞，以找出如何按排序順序檢索行，通過根據(jù)聯(lián)接類型瀏覽所有行并為所有匹配where子句的行保存排序關(guān)鍵字和行的指針來完成排序，然后關(guān)鍵字被排序，并按排序順序檢索行。

總的來說，Using filesort 是Mysql里一種速度比較慢的外部排序，如果能避免是較好的了，很多時(shí)候，我們可以通過優(yōu)化索引來盡量避免出現(xiàn)Using filesort，從而提高速度。

2、實(shí)例

這里舉個(gè)簡單的例子：

CREATE TABLE testing (id int(10) unsigned NOT NULL auto_increment,room_number int(10) unsigned NOT NULL default '0',PRIMARY KEY (id),KEY room_number (room_number)) ENGINE=MyISAM DEFAULT CHARSET=latin1

寫個(gè)存儲(chǔ)過程askwan，插入10萬條測試數(shù)據(jù)：

mysql> DELIMITER $$DROP PROCEDURE IF EXISTS askwan.askwanCREATEPROCEDURE‘a(chǎn)skwan‘.‘a(chǎn)skwan‘()BEGINDECLAREvINTDEFAULT1;WHILEv<100000;DOINSERTINTOtestingVALUES(v,v);SETv=v+1;ENDWHILE;ENDmysql> DELIMITER ;mysql> CALL askwan();Query OK, 1 row affected (13.21 sec)

開始試驗(yàn)，由上面例子中建立的表信息，我已經(jīng)建立了兩個(gè)索引，一個(gè)主鍵id，一個(gè)room_number列索引
那現(xiàn)在來看一條SQL：

SELECT id FROM testing WHERE room_number=1000 ORDER BY id ;

分析一下：

mysql> EXPLAIN SELECT id FROM testing WHERE room_number=1000 ORDER BY id ;
+----+-------------+---------+------+---------------+-------------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+-------------+---------+-------+------+-----------------------------+
| 1 | SIMPLE | testing | ref | room_number | room_number | 4 | const | 1 | Using where; Using filesort |
+----+-------------+---------+------+---------------+-------------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)

出現(xiàn)了Using filesort，并且用到了room_number這列索引，但是，在這里用到的索引是針對(duì)WHERE后面的room_number條件的，而最后面的排序是根據(jù)id來的，這就是手冊中說的，“額外的一次排序”，于是就會(huì)出現(xiàn)Using filesort，再建立一個(gè)聯(lián)合索引 room_number_id：

alter table testing add index room_number_id(room_number,id);

再來分析一下：

mysql> EXPLAIN SELECT id FROM testing WHERE room_number=1000 ORDER BY id ;
+----+-------------+---------+------+----------------------------+----------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+----------------------------+----------------+---------+-------+------+--------------------------+
| 1 | SIMPLE | testing | ref | room_number,room_number_id | room_number_id | 4 | const | 1 | Using where; |
+----+-------------+---------+------+----------------------------+----------------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

現(xiàn)在Using filesort不出現(xiàn)了。

3、總結(jié)

一般有order by語句，在索引加得不當(dāng)?shù)那闆r下，都有可能出現(xiàn)Using filesort，這時(shí)候就要對(duì)SQL語句和索引進(jìn)行優(yōu)化了，但是，并不是說出現(xiàn)Using filesort就是個(gè)嚴(yán)重的問題，不是這樣的，此次舉的例子比較極端，幾乎不太可能出現(xiàn)這么傻瓜的查詢，優(yōu)化和不優(yōu)化，要看它是不是影響了業(yè)務(wù)性能。從上面可以看到聯(lián)合索引，也可以叫多列索引，形如 key (‘A1′,’A2′,’A3′ ,’A4’)等的，排序的思路一般是，先按照A1來排序，A1相同，然后按照A2排序，以此類推，這樣對(duì)于（A1）,(A1,A2),(A1,A2,A3)的索引都是有效的，但是對(duì)于（A2,A3）這樣的索引就無效了。

需要了解MySQL 的特性：

一條 SQL 語句只能使用 1 個(gè)索引 (5.0-)，MySQL 根據(jù)表的狀態(tài)，選擇一個(gè)它認(rèn)為較好的索引用于優(yōu)化查詢；聯(lián)合索引，只能按從左到右的順序依次使用；從上邊可以看到結(jié)合索引，也可以叫多列索引，形如 key (‘B1′,’B2′,’B3′ ,’B4’)等的，排序的思路通常為，先按照B1來排序，B1相同，然后按照B2排序，以此類推，這樣對(duì)于（B1）,(B1,B2), (B1,B2,B3)的索引都是有效的，可是對(duì)于（B2,B3）這樣的索引就無效了。

根據(jù)這個(gè)特性就可以解決問題：

user_id 和 item_id 是 2 個(gè)索引，我的語句中，MySQL 選擇了 user_id，那么 item_id 的索引沒有起到任何用處，所以，當(dāng)要排序的時(shí)候，由于記錄數(shù)較多，內(nèi)存中的排序 buffer 滿了，只能 Using filesort 進(jìn)行外部排序，因此每次查詢要從磁盤讀取幾十 M 的數(shù)據(jù)，速度很慢。

修改表結(jié)構(gòu)，刪除 user_id 和 item_id 的 INDEX 索引，建立一個(gè)名為 user_item 的聯(lián)合 UNIQUE 索引，順序是先 user_id 后 item_id，再 EXPLAIN，這回只有 Using where 了。

延伸閱讀1：MySQL版本介紹

針對(duì)不同的用戶，MySQL分為兩個(gè)不同的版本：

MySQL Community Server（社區(qū)版）：該版本完全免費(fèi)，但是官方不提供技術(shù)支持。用戶可以自由下載使用。MySQL Enterprise Server(企業(yè)版服務(wù)器)：為企業(yè)提供數(shù)據(jù)庫應(yīng)用，支持ACID事務(wù)處理，提供完整的提交、回滾、崩潰恢復(fù)和行政鎖定功能。需要付費(fèi)使用，官方提供技術(shù)支持。