MySQL前綴索引導(dǎo)致的慢查詢分析總結(jié)
更新時(shí)間：2013年05月19日 16:12:15 作者：
前綴索引，并不是一個(gè)萬(wàn)能藥，他的確可以幫助我們對(duì)一個(gè)寫(xiě)過(guò)長(zhǎng)的字段上建立索引。但也會(huì)導(dǎo)致排序（order by ，group by）查詢上都是無(wú)法使用前綴索引的
前端時(shí)間跟一個(gè)DB相關(guān)的項(xiàng)目，alanc反饋有一個(gè)查詢，使用索引比不使用索引慢很多倍，有點(diǎn)毀三觀。所以跟進(jìn)了一下，用explain，看了看2個(gè)查詢不同的結(jié)果。
不用索引的查詢的時(shí)候結(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)行排序。這個(gè)當(dāng)然慢了。
但數(shù)據(jù)表上在username，的確是有索引的。怎么會(huì)反而要Using filesort?
看了一下數(shù)據(jù)表定義。是一個(gè)開(kāi)源聊天服務(wù)器ejabberd的一張表。初看以為主鍵i_rosteru_user_jid是username，和jid的聯(lián)合索引，那么使用order by username時(shí)應(yīng)該是可以使用到索引才對(duì)呀？
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)其主鍵定義，不是定義的完整的主鍵名稱，而跟了一個(gè)75的長(zhǎng)度描述，稍稍一愣，原來(lái)用的是前綴索引，而不是整個(gè)字段都是索引。（我的記憶里面InnoDB還不支持這玩意，估計(jì)是4.0后什么版本加入的），前綴索引就是將數(shù)據(jù)字段中前面N個(gè)字節(jié)作為索引的一種方式。
發(fā)現(xiàn)了這個(gè)問(wèn)題后，我們開(kāi)始懷疑慢查詢和這個(gè)索引有關(guān)，前綴索引的主要用途在于有時(shí)字段過(guò)程，而MySQL支持的很多索引長(zhǎng)度是有限制的。
首先不帶order by 的limit 這種查詢，本質(zhì)可能還是和主鍵相關(guān)的，因?yàn)镸ySQL 的INNODB的操作實(shí)際都是依靠主鍵的（即使你沒(méi)有建立，系統(tǒng)也會(huì)有一個(gè)默認(rèn)的），而limit這種查詢，使用主鍵是可以加快速度，（explain返回的rows 應(yīng)該是一個(gè)參考值），雖然我沒(méi)有看見(jiàn)什么文檔明確的說(shuō)明過(guò)這個(gè)問(wèn)題，但從不帶order by 的limit 查詢的返回結(jié)果基本可以證明這點(diǎn)。
但當(dāng)我們使用order by username的時(shí)候，由于希望使用的是username的排序，而不是username（75）的排序，但實(shí)際索引是前綴索引，不是完整字段的索引。所以反而導(dǎo)致了order by的時(shí)候完全無(wú)法利用索引了。（我在SQL語(yǔ)句里面增加強(qiáng)制使用索引i_rosteru_user_jid也不起作用）。而其實(shí)使用中，表中的字段username 連75個(gè)都用不到，何況定義的250的長(zhǎng)度。完全是自己折騰導(dǎo)致的麻煩。由于這是其他產(chǎn)品的表格，我們無(wú)法更改，暫時(shí)只能先將就用不不帶排序的查詢講究。
總結(jié)：
•前綴索引，并不是一個(gè)萬(wàn)能藥，他的確可以幫助我們對(duì)一個(gè)寫(xiě)過(guò)長(zhǎng)的字段上建立索引。但也會(huì)導(dǎo)致排序（order by ，group by）查詢上都是無(wú)法使用前綴索引的。
•任何時(shí)候，對(duì)于DB Schema定義，合理的規(guī)劃自己的字段長(zhǎng)度，字段類型都是首要的事情。
不用索引的查詢的時(shí)候結(jié)果如下，實(shí)際查詢中速度比較塊。
復(fù)制代碼 代碼如下:
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)行排序。這個(gè)當(dāng)然慢了。
但數(shù)據(jù)表上在username，的確是有索引的。怎么會(huì)反而要Using filesort?
看了一下數(shù)據(jù)表定義。是一個(gè)開(kāi)源聊天服務(wù)器ejabberd的一張表。初看以為主鍵i_rosteru_user_jid是username，和jid的聯(lián)合索引，那么使用order by username時(shí)應(yīng)該是可以使用到索引才對(duì)呀？
復(fù)制代碼 代碼如下:
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)其主鍵定義，不是定義的完整的主鍵名稱，而跟了一個(gè)75的長(zhǎng)度描述，稍稍一愣，原來(lái)用的是前綴索引，而不是整個(gè)字段都是索引。（我的記憶里面InnoDB還不支持這玩意，估計(jì)是4.0后什么版本加入的），前綴索引就是將數(shù)據(jù)字段中前面N個(gè)字節(jié)作為索引的一種方式。
發(fā)現(xiàn)了這個(gè)問(wèn)題后，我們開(kāi)始懷疑慢查詢和這個(gè)索引有關(guān)，前綴索引的主要用途在于有時(shí)字段過(guò)程，而MySQL支持的很多索引長(zhǎng)度是有限制的。
首先不帶order by 的limit 這種查詢，本質(zhì)可能還是和主鍵相關(guān)的，因?yàn)镸ySQL 的INNODB的操作實(shí)際都是依靠主鍵的（即使你沒(méi)有建立，系統(tǒng)也會(huì)有一個(gè)默認(rèn)的），而limit這種查詢，使用主鍵是可以加快速度，（explain返回的rows 應(yīng)該是一個(gè)參考值），雖然我沒(méi)有看見(jiàn)什么文檔明確的說(shuō)明過(guò)這個(gè)問(wèn)題，但從不帶order by 的limit 查詢的返回結(jié)果基本可以證明這點(diǎn)。
但當(dāng)我們使用order by username的時(shí)候，由于希望使用的是username的排序，而不是username（75）的排序，但實(shí)際索引是前綴索引，不是完整字段的索引。所以反而導(dǎo)致了order by的時(shí)候完全無(wú)法利用索引了。（我在SQL語(yǔ)句里面增加強(qiáng)制使用索引i_rosteru_user_jid也不起作用）。而其實(shí)使用中，表中的字段username 連75個(gè)都用不到，何況定義的250的長(zhǎng)度。完全是自己折騰導(dǎo)致的麻煩。由于這是其他產(chǎn)品的表格，我們無(wú)法更改，暫時(shí)只能先將就用不不帶排序的查詢講究。
總結(jié)：
•前綴索引，并不是一個(gè)萬(wàn)能藥，他的確可以幫助我們對(duì)一個(gè)寫(xiě)過(guò)長(zhǎng)的字段上建立索引。但也會(huì)導(dǎo)致排序（order by ，group by）查詢上都是無(wú)法使用前綴索引的。
•任何時(shí)候，對(duì)于DB Schema定義，合理的規(guī)劃自己的字段長(zhǎng)度，字段類型都是首要的事情。
您可能感興趣的文章:
相關(guān)文章
mysql慢查詢操作實(shí)例分析【開(kāi)啟、測(cè)試、確認(rèn)等】
這篇文章主要介紹了mysql慢查詢操作,結(jié)合實(shí)例形式分析了mysql慢查詢操作中的開(kāi)啟、測(cè)試、確認(rèn)等實(shí)現(xiàn)方法及相關(guān)操作技巧,需要的朋友可以參考下2019-12-12
這篇文章主要介紹了在MySQL中生成隨機(jī)密碼的方法,作者還給出了密碼所對(duì)應(yīng)類型限制的參數(shù)表,需要的朋友可以參考下2015-05-05
MYSQL 數(shù)據(jù)庫(kù)導(dǎo)入導(dǎo)出命令
在不同操作系統(tǒng)或MySQL版本情況下，直接拷貝文件的方法可能會(huì)有不兼容的情況發(fā)生。所以一般推薦用SQL腳本形式導(dǎo)入。下面分別介紹兩種方法。2010-11-11
MySQL 編碼utf8 與 utf8mb4 utf8mb4_unicode_ci 與 utf8mb4_general_
這篇文章主要介紹了MySQL 編碼utf8 與 utf8mb4 utf8mb4_unicode_ci 與 utf8mb4_general_ci的相關(guān)知識(shí)，本文通過(guò)實(shí)例代碼給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2020-05-05
這篇文章主要為大家詳細(xì)介紹了mysql5.7安裝及配置教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-11-11
深入講解數(shù)據(jù)庫(kù)中Decimal類型的使用以及實(shí)現(xiàn)方法
MySQL?DECIMAL數(shù)據(jù)類型用于在數(shù)據(jù)庫(kù)中存儲(chǔ)精確的數(shù)值,我們經(jīng)常將DECIMAL數(shù)據(jù)類型用于保留準(zhǔn)確精確度的列,例如會(huì)計(jì)系統(tǒng)中的貨幣數(shù)據(jù),下面這篇文章主要給大家介紹了關(guān)于數(shù)據(jù)庫(kù)中Decimal類型的使用以及實(shí)現(xiàn)方法的相關(guān)資料,需要的朋友可以參考下2022-02-02
原來(lái)MySQL?數(shù)據(jù)類型也可以優(yōu)化
這篇文章主要介紹了原來(lái)MySQL?數(shù)據(jù)類型也可以優(yōu)化，文章圍繞主題展開(kāi)詳細(xì)的內(nèi)容介紹，具有一定的參考價(jià)值，需要的小伙伴可以參考一下，希望對(duì)你的學(xué)習(xí)有所幫助2022-08-08
我們?cè)谌粘９ぷ髦腥绻佑|Mysql數(shù)據(jù)庫(kù),那么不可避免會(huì)遇到Mysql中,下面這篇文章主要給大家介紹了關(guān)于MySQL中NULL的相關(guān)資料,文中通過(guò)示例代碼介紹的非常詳細(xì),需要的朋友可以參考下2021-11-11
Windows11下MySQL?8.0.29?安裝配置方法圖文教程
這篇文章主要為大家詳細(xì)介紹了Windows11下MySQL?8.0.29?安裝配置方法圖文教程，文中安裝步驟介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2022-07-07
本文將介紹mysql之innodb的鎖分類，需要了解更多的朋友可以參考下2012-11-11