錯誤使用MySQL首碼索引導致的慢查詢

前端時間跟一個DB相關的項目，alanc反饋有一個查詢，使用索引比不使用索引慢很多倍，有點毀三觀。所以跟進了一下，用explain，看了看2個查詢不同的結果。

不用索引的查詢的時候結果如下，實際查詢中速度比較塊。

mysql> explain select * from rosterusers limit 10000,3 ;

+----+-------------+-------------+------+---------------+------+---------+------+---------+-------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows    | Extra |
+----+-------------+-------------+------+---------------+------+---------+------+---------+-------+
|  1 | SIMPLE      | rosterusers | ALL  | NULL          | NULL | NULL    | NULL | 2010066 |       |
+----+-------------+-------------+------+---------------+------+---------+------+---------+-------+

 

 

而使用索引order by的查詢結果如下，速度反而慢的驚人。

mysql> explain select * from rosterusers order by username limit 10000,3 ;

+----+-------------+-------------+------+---------------+------+---------+------+---------+----------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows    | Extra          |
+----+-------------+-------------+------+---------------+------+---------+------+---------+----------------+
|  1 | SIMPLE      | rosterusers | ALL  | NULL          | NULL | NULL    | NULL | 2010087 | Using filesort |
+----+-------------+-------------+------+---------------+------+---------+------+---------+----------------+

 

區別在於，使用索引查詢的Extra變成了，Using filesort。居然用了使用外部檔案進行排序。這個當然慢了。

但資料表上在username，的確是有索引的。怎麼會反而要Using filesort?

看了一下資料表定義。是一個開源聊天伺服器ejabberd的一張表。初看以為主鍵i_rosteru_user_jid是username，和jid的聯合索引，那麼使用order by username時應該是可以使用到索引才對呀？

CREATE TABLE `rosterusers` (

`username` varchar(250) NOT NULL,

`jid` varchar(250) NOT NULL,

UNIQUE KEY `i_rosteru_user_jid` (`username`(75),`jid`(75)),

KEY `i_rosteru_jid` (`jid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

仔細檢查突然發現其主鍵定義，不是定義的完整的主鍵名稱，而跟了一個75的長度描述，稍稍一愣，原來用的是首碼索引，而不是整個欄位都是索引。（我的記憶裡面InnoDB還不支援這玩意，估計是4.0後什麼版本加入的），首碼索引就是將資料欄位中前面N個位元組作為索引的一種方式。。

發現了這個問題後，我們開始懷疑慢查詢和這個索引有關，首碼索引的主要用途在於有時欄位過程，而MySQL支援的很多索引長度是有限制的。

首先不帶order by 的limit 這種查詢，本質可能還是和主鍵相關的，因為MySQL 的INNODB的操作實際都是依靠主鍵的（即使你沒有建立，系統也會有一個預設的），而limit這種查詢，使用主鍵是可以加快速度，（explain返回的rows 應該是一個參考值），雖然我沒有看見什麼文檔明確的說明過這個問題，但從不帶order by 的limit 查詢的返回結果基本可以證明這點。

但當我們使用order by username的時候，由於希望使用的是username的排序，而不是username（75）的排序，但實際索引是首碼索引，不是完整欄位的索引。所以反而導致了order by的時候完全無法利用索引了。（我在SQL語句裡面增加強制使用索引i_rosteru_user_jid也不起作用）。而其實使用中，表中的欄位username 連75個都用不到，何況定義的250的長度。完全是自己折騰導致的麻煩。由於這是其他產品的表格，我們無法更改，暫時只能先將就用不不帶排序的查詢講究。

總結：

• 首碼索引，並不是一個萬能藥，他的確可以協助我們對一個寫過長的欄位上建立索引。但也會導致排序（order by ，group by）查詢上都是無法使用首碼索引的。
• 任何時候，對於DB Schema定義，合理的規劃自己的欄位長度，欄位類型都是首要的事情。

【本文作者是雁渡寒潭，本著自由的精神，你可以在無盈利的情況完整轉載此文檔，轉載時請附上BLOG連結:http://www.cnblogs.com/fullsail/ 或者http://blog.csdn.net/fullsail，否則每字一元，每圖一百不講價。對Baidu文庫，360doc加價一倍】