程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 錯誤使用MySQL前綴索引導致的慢查詢問題

錯誤使用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定義,合理的規劃自己的字段長度,字段類型都是首要的事情。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved