MySQL前綴索引導致的慢查詢分析總結
更新時間：2013年05月19日 16:12:15 作者：
前綴索引，并不是一個萬能藥，他的確可以幫助我們對一個寫過長的字段上建立索引。但也會導致排序（order by ，group by）查詢上都是無法使用前綴索引的
前端時間跟一個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 |
+----+-------------+-------------+------+---------------+------+---------+------+---------+----------------+
區(qū)別在于，使用索引查詢的Extra變成了，Using filesort。居然用了使用外部文件進行排序。這個當然慢了。
但數(shù)據表上在username，的確是有索引的。怎么會反而要Using filesort?
看了一下數(shù)據表定義。是一個開源聊天服務器ejabberd的一張表。初看以為主鍵i_rosteru_user_jid是username，和jid的聯(lián)合索引，那么使用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;
仔細檢查突然發(fā)現(xiàn)其主鍵定義，不是定義的完整的主鍵名稱，而跟了一個75的長度描述，稍稍一愣，原來用的是前綴索引，而不是整個字段都是索引。（我的記憶里面InnoDB還不支持這玩意，估計是4.0后什么版本加入的），前綴索引就是將數(shù)據字段中前面N個字節(jié)作為索引的一種方式。
發(fā)現(xiàn)了這個問題后，我們開始懷疑慢查詢和這個索引有關，前綴索引的主要用途在于有時字段過程，而MySQL支持的很多索引長度是有限制的。
首先不帶order by 的limit 這種查詢，本質可能還是和主鍵相關的，因為MySQL 的INNODB的操作實際都是依靠主鍵的（即使你沒有建立，系統(tǒng)也會有一個默認的），而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定義，合理的規(guī)劃自己的字段長度，字段類型都是首要的事情。
不用索引的查詢的時候結果如下，實際查詢中速度比較塊。
復制代碼 代碼如下:
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 |
+----+-------------+-------------+------+---------------+------+---------+------+---------+----------------+
區(qū)別在于，使用索引查詢的Extra變成了，Using filesort。居然用了使用外部文件進行排序。這個當然慢了。
但數(shù)據表上在username，的確是有索引的。怎么會反而要Using filesort?
看了一下數(shù)據表定義。是一個開源聊天服務器ejabberd的一張表。初看以為主鍵i_rosteru_user_jid是username，和jid的聯(lián)合索引，那么使用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;
仔細檢查突然發(fā)現(xiàn)其主鍵定義，不是定義的完整的主鍵名稱，而跟了一個75的長度描述，稍稍一愣，原來用的是前綴索引，而不是整個字段都是索引。（我的記憶里面InnoDB還不支持這玩意，估計是4.0后什么版本加入的），前綴索引就是將數(shù)據字段中前面N個字節(jié)作為索引的一種方式。
發(fā)現(xiàn)了這個問題后，我們開始懷疑慢查詢和這個索引有關，前綴索引的主要用途在于有時字段過程，而MySQL支持的很多索引長度是有限制的。
首先不帶order by 的limit 這種查詢，本質可能還是和主鍵相關的，因為MySQL 的INNODB的操作實際都是依靠主鍵的（即使你沒有建立，系統(tǒng)也會有一個默認的），而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定義，合理的規(guī)劃自己的字段長度，字段類型都是首要的事情。
相關文章
HammerDB?是一個開源的數(shù)據庫負載測試和基準測試工具，同時支持?Windows?和?Linux?平臺，這篇文章主要介紹了MySQL壓測神器HammerDB的部署及使用,需要的朋友可以參考下2022-08-08
這篇文章主要為大家分享了mysql 5.7.9 免安裝版配置方法圖文教程，感興趣的小伙伴們可以參考一下2016-08-08
MySQL數(shù)據據庫中我們經常使用SQL SELECT語句來查詢數(shù)據，那么關于它的執(zhí)行順序，下面小編來帶大家簡單了解一下2019-05-05
linux（Centos7）下安裝mysql8.0.18的教程圖解
這篇文章主要介紹了linux（Centos7）安裝mysql8.0.18的教程，本文圖文并茂給大家介紹的非常詳細，具有一定的參考借鑒價值,需要的朋友可以參考下2019-11-11
MYSQL必知必會讀書筆記第十和十一章之使用函數(shù)處理數(shù)據
這篇文章主要介紹了MYSQL必知必會讀書筆記第十和十一章之使用函數(shù)處理數(shù)據的相關資料,需要的朋友可以參考下2016-05-05
數(shù)據庫表一般包含大量的數(shù)據，很少需要檢索表中的所有行。通常只是根據特定的需要提取表數(shù)據的子集。因此檢索所需數(shù)據時需要指定搜索條件，搜索條件也稱為過濾條件2022-10-10
這篇文章主要介紹了MySQL prepare的相關內容，包括prepare的產生，在服務器端的執(zhí)行過程，以及jdbc對prepare的處理以及相關測試，需要的朋友可以了解下。希望對大家有所幫助。2017-09-09
這篇文章主要給大家介紹了關于如何徹底刪除mysql服務（清理注冊表）的相關資料，文中通過圖文介紹的非常詳細，對大家的學習或者使用mysql具有一定的參考學習價值，需要的朋友們下面來一起學習學習吧2019-03-03
Node-Red實現(xiàn)MySQL數(shù)據庫連接的方法
這篇文章主要介紹了Node-Red實現(xiàn)MySQL數(shù)據庫連接的方法,本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2021-08-08
這篇文章主要介紹了Mysql常用命令操作方法，本文通過實例代碼給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值,需要的朋友可以參考下2023-03-03