MySQL前綴索引誘致的慢查詢剖析總結。本站提示廣大學習愛好者：（MySQL前綴索引誘致的慢查詢剖析總結）文章只能為提供參考，不一定能成為您想要的結果。以下是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界說，公道的計劃本身的字段長度，字段類型都是重要的工作。
