MySQL前綴索引導(dǎo)致的慢查詢分析總結(jié)_MySQL教程
推薦:Mysql,phpmyadmin密碼忘了怎么辦1、關(guān)閉mysql服務(wù) # service mysql stop 如果提示mysql: unrecognized service這樣的錯(cuò)誤提示。 先查看查找mysql.server,使用:find / -name mysql.server 再使用/usr/local/mysql/share/mysql/mysql.server stop命令關(guān)閉mysql。 2、 安全模式啟動(dòng)mysql跳過授權(quán)表 先查
前端時(shí)間跟一個(gè)DB相關(guān)的項(xiàng)目,alanc反饋有一個(gè)查詢,使用索引比不使用索引慢很多倍,有點(diǎn)毀三觀。所以跟進(jìn)了一下,用explain,看了看2個(gè)查詢不同的結(jié)果。不用索引的查詢的時(shí)候結(jié)果如下,實(shí)際查詢中速度比較塊。
復(fù)制代碼 代碼如下:www.wf0088.com
+----+-------------+-------------+------+---------------+------+---------+------+---------+-------+
| 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è)開源聊天服務(wù)器ejabberd的一張表。初看以為主鍵i_rosteru_user_jid是username,和jid的聯(lián)合索引,那么使用order by username時(shí)應(yīng)該是可以使用到索引才對(duì)呀?
復(fù)制代碼 代碼如下:www.wf0088.com
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)度描述,稍稍一愣,原來用的是前綴索引,而不是整個(gè)字段都是索引。(我的記憶里面InnoDB還不支持這玩意,估計(jì)是4.0后什么版本加入的),前綴索引就是將數(shù)據(jù)字段中前面N個(gè)字節(jié)作為索引的一種方式。
發(fā)現(xiàn)了這個(gè)問題后,我們開始懷疑慢查詢和這個(gè)索引有關(guān),前綴索引的主要用途在于有時(shí)字段過程,而MySQL支持的很多索引長(zhǎng)度是有限制的。
首先不帶order by 的limit 這種查詢,本質(zhì)可能還是和主鍵相關(guān)的,因?yàn)镸ySQL 的INNODB的操作實(shí)際都是依靠主鍵的(即使你沒有建立,系統(tǒng)也會(huì)有一個(gè)默認(rèn)的),而limit這種查詢,使用主鍵是可以加快速度,(explain返回的rows 應(yīng)該是一個(gè)參考值),雖然我沒有看見什么文檔明確的說明過這個(gè)問題,但從不帶order by 的limit 查詢的返回結(jié)果基本可以證明這點(diǎn)。
但當(dāng)我們使用order by username的時(shí)候,由于希望使用的是username的排序,而不是username(75)的排序,但實(shí)際索引是前綴索引,不是完整字段的索引。所以反而導(dǎo)致了order by的時(shí)候完全無法利用索引了。(我在SQL語句里面增加強(qiáng)制使用索引i_rosteru_user_jid也不起作用)。而其實(shí)使用中,表中的字段username 連75個(gè)都用不到,何況定義的250的長(zhǎng)度。完全是自己折騰導(dǎo)致的麻煩。由于這是其他產(chǎn)品的表格,我們無法更改,暫時(shí)只能先將就用不不帶排序的查詢講究。
總結(jié):
•前綴索引,并不是一個(gè)萬能藥,他的確可以幫助我們對(duì)一個(gè)寫過長(zhǎng)的字段上建立索引。但也會(huì)導(dǎo)致排序(order by ,group by)查詢上都是無法使用前綴索引的。
•任何時(shí)候,對(duì)于DB Schema定義,合理的規(guī)劃自己的字段長(zhǎng)度,字段類型都是首要的事情。
分享:淺談SQLite時(shí)間函數(shù)的使用說明與總結(jié)分析本篇文章是對(duì)SQLite時(shí)間函數(shù)的使用進(jìn)行了詳細(xì)的分析介紹,需要的朋友參考下
相關(guān)MySQL教程:
- MSSQL清空日志刪除日志文件
- 關(guān)于數(shù)據(jù)庫中保留小數(shù)位的問題
- 解析mysql與Oracle update的區(qū)別
- mysql 導(dǎo)入導(dǎo)出數(shù)據(jù)庫以及函數(shù)、存儲(chǔ)過程的介紹
- MySQL——修改root密碼的4種方法(以windows為例)
- 解決MYSQL出現(xiàn)Can''t create/write to file ''#sql_5c0_0.MYD''的問題
- 深入理解SQL的四種連接-左外連接、右外連接、內(nèi)連接、全連接
- 解析:內(nèi)聯(lián),左外聯(lián),右外聯(lián),全連接,交叉連接的區(qū)別
- mysql出現(xiàn)“Incorrect key file for table”處理方法
- mysql重裝后出現(xiàn)亂碼設(shè)置為utf8可解決
- 淺析一個(gè)MYSQL語法(在查詢中使用count)的兼容性問題
- 解析MySQL中INSERT INTO SELECT的使用
MySQL教程Rss訂閱編程教程搜索
MySQL教程推薦
- 10大關(guān)系數(shù)據(jù)庫SQL注入工具一覽
- 如何通過配置自動(dòng)實(shí)現(xiàn)ValueList中hql語句的整型參數(shù)轉(zhuǎn)換
- 網(wǎng)站模板MySQL timestamp自動(dòng)更新時(shí)間
- linux Xtrabackup安裝及使用方法
- Mysql高性能備份方案解決數(shù)據(jù)不間斷訪問
- mysql 定時(shí)更新表字段列的值狀態(tài)
- mysql創(chuàng)建函數(shù)出現(xiàn)1418錯(cuò)誤的解決辦法
- MySQL無法啟動(dòng)1067錯(cuò)誤的解決方法
- MYSQL 批量替換之replace語法的使用詳解
- MySQL筆記之索引的使用
猜你也喜歡看這些
- sql里將重復(fù)行數(shù)據(jù)合并為一行數(shù)據(jù)使用逗號(hào)進(jìn)行分隔
- 數(shù)據(jù)庫的分離及附加
- 關(guān)于sql server批量插入和更新的兩種解決方案
- 淺析SQL Server asp.net 數(shù)據(jù)提供程序連接池
- SQL Server 2005數(shù)據(jù)加密技術(shù)應(yīng)用研究
- 關(guān)于升級(jí)SQL Server 2008數(shù)據(jù)庫引擎
- 如何在SQL 2005中實(shí)現(xiàn)循環(huán)每一行做一定的操作
- 解讀SQL語句經(jīng)驗(yàn)技巧
- MyEclipse 配置SQL Server 2008數(shù)據(jù)庫驅(qū)動(dòng)操作步驟
- 微軟SQL Server 2008 的新壓縮特性
- 相關(guān)鏈接:
- 教程說明:
MySQL教程-MySQL前綴索引導(dǎo)致的慢查詢分析總結(jié)。