前端時間跟一個DB相關(guān)的項(xiàng)目,alanc反饋有一個查詢,使用索引比不使用索引慢很多倍,有點(diǎn)毀三觀。所以跟進(jìn)了一下,用explain,看了看2個查詢不同的結(jié)果。
不用索引的查詢的時候結(jié)果如下,實(shí)際查詢中速度比較塊。
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的查詢結(jié)果如下,速度反而慢的驚人。
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 |
+----+-------------+-------------+------+---------------+------+---------+------+---------+----------------+
區(qū)別在于,使用索引查詢的Extra變成了,Using filesort。居然用了使用外部文件進(jìn)行排序。這個當(dāng)然慢了。
但數(shù)據(jù)表上在username,的確是有索引的。怎么會反而要Using filesort?
看了一下數(shù)據(jù)表定義。是一個開源聊天服務(wù)器ejabberd的一張表。初看以為主鍵i_rosteru_user_jid是username,和jid的聯(lián)合索引,那么使用order by username時應(yīng)該是可以使用到索引才對呀?
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;
仔細(xì)檢查突然發(fā)現(xiàn)其主鍵定義,不是定義的完整的主鍵名稱,而跟了一個75的長度描述,稍稍一愣,原來用的是前綴索引,而不是整個字段都是索引。(我的記憶里面InnoDB還不支持這玩意,估計是4.0后什么版本加入的),前綴索引就是將數(shù)據(jù)字段中前面N個字節(jié)作為索引的一種方式。
發(fā)現(xiàn)了這個問題后,我們開始懷疑慢查詢和這個索引有關(guān),前綴索引的主要用途在于有時字段過程,而MySQL支持的很多索引長度是有的。
首先不帶order by 的limit 這種查詢,本質(zhì)可能還是和主鍵相關(guān)的,因?yàn)镸ySQL 的INNODB的操作實(shí)際都是依靠主鍵的(即使你沒有建立,系統(tǒng)也會有一個默認(rèn)的),而limit這種查詢,使用主鍵是可以加快速度,(explain返回的rows 應(yīng)該是一個參考值),雖然我沒有看見什么文檔明確的說明過這個問題,但從不帶order by 的limit 查詢的返回結(jié)果基本可以證明這點(diǎn)。
但當(dāng)我們使用order by username的時候,由于希望使用的是username的排序,而不是username(75)的排序,但實(shí)際索引是前綴索引,不是完整字段的索引。所以反而導(dǎo)致了order by的時候完全無法利用索引了。(我在SQL語句里面增加強(qiáng)制使用索引i_rosteru_user_jid也不起作用)。而其實(shí)使用中,表中的字段username 連75個都用不到,何況定義的250的長度。完全是自己折騰導(dǎo)致的麻煩。由于這是其他產(chǎn)品的表格,我們無法更改,暫時只能先將就用不不帶排序的查詢講究。
總結(jié):
【本文作者是雁渡寒潭,本著自由的精神,你可以在無盈利的情況完整轉(zhuǎn)載此文檔,轉(zhuǎn)載時請附上BLOG鏈接:http://www.cnblogs.com/fullsail/ 或者h(yuǎn)ttp://blog.csdn.net/fullsail,否則每字一元,每圖一百不講價。
bitsCN.comCopyright ? 2019- 91gzw.com 版權(quán)所有 湘ICP備2023023988號-2
違法及侵權(quán)請聯(lián)系:TEL:199 1889 7713 E-MAIL:2724546146@qq.com
本站由北京市萬商天勤律師事務(wù)所王興未律師提供法律服務(wù)