以下是基于我結(jié)合B+樹(shù)的數(shù)據(jù)結(jié)構(gòu)和對(duì)實(shí)驗(yàn)結(jié)果的推測(cè)作出的判斷,如有錯(cuò)誤,懇請(qǐng)指正!
今天實(shí)驗(yàn)了一下MySQL的count()操作優(yōu)化, 以下討論基于mysql5.7 InnoDB存儲(chǔ)引擎. x86 windows操作系統(tǒng)。
創(chuàng)建的表的結(jié)構(gòu)如下(數(shù)據(jù)量為100萬(wàn)):
首先是關(guān)于mysql的count(*),count(PK), count(1)哪個(gè)快的問(wèn)題。
實(shí)現(xiàn)結(jié)果如下:
并沒(méi)有什么區(qū)別!加上了WHERE子句之后3個(gè)查詢(xún)的時(shí)間也是相同的,我就不貼圖片了。
之前在公司的時(shí)候就寫(xiě)過(guò)一個(gè)select count(*) from table
的SQL語(yǔ)句,在數(shù)據(jù)多的時(shí)候非常慢。所以要怎么優(yōu)化呢?
這要從InnoDB的索引說(shuō)起, InnoDB的索引是B+Tree。
對(duì)主鍵索引來(lái)說(shuō):它只有在葉子節(jié)點(diǎn)上存儲(chǔ)數(shù)據(jù),它的key是主鍵,并且value為整條數(shù)據(jù)。
對(duì)輔助索引來(lái)說(shuō):key為建索引的列,value為主鍵。
這給我們兩個(gè)信息:
1. 根據(jù)主鍵會(huì)查到整條數(shù)據(jù)
2. 根據(jù)輔助索引只能查到主鍵,然后必須通過(guò)主鍵再查到剩余信息。
所以如果要優(yōu)化count(*)操作的話,我們需要找一個(gè)短小的列,為它建立輔助索引。
在我的例子中就是status
,雖然它的”severelity”幾乎為0.
先建立索引:ALTER TABLE test1 ADD INDEX (
status);
然后查詢(xún),如下圖:
可以看到,查詢(xún)時(shí)間從3.35s下降到了0.26s,查詢(xún)速度提升近13倍。
如果索引是str
這一列,結(jié)果又會(huì)是怎么樣呢?
先建立索引: alter table test1 add index (str)
結(jié)果如下:
可以看到,時(shí)間為0.422s,也很快,但是比起status
這列還是有著1.5倍左右的差距。
再大膽一點(diǎn)做個(gè)實(shí)驗(yàn),我把status
這列的索引刪掉,建立一個(gè)status
和left(omdb,200)
(這一列平均1000個(gè)字符)的聯(lián)合索引,然后看查詢(xún)時(shí)間。
建立索引: alter table test1 add index (
status,omdb(200))
結(jié)果如下:
時(shí)間為1.172s
alter table test1 add index (status,imdbid);
補(bǔ)充!!
要注意索引失效的情況!
建立了索引后正常的的樣子:
可以看到key_len為6, Extra的說(shuō)明是using index.
而如果索引失效的話:
索引失效有很多種情況,比如使用函數(shù),!=操作等,具體請(qǐng)參考官方文檔。
對(duì)MySQL沒(méi)有很深的研究,以上是基于我結(jié)合B+樹(shù)的數(shù)據(jù)結(jié)構(gòu)和對(duì)實(shí)驗(yàn)結(jié)果的推測(cè)作出的判斷,如有錯(cuò)誤,懇請(qǐng)指正!
到此這篇關(guān)于MySQL 大表的count()優(yōu)化實(shí)現(xiàn)的文章就介紹到這了,更多相關(guān)MySQL 大表count()優(yōu)化內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
標(biāo)簽:甘南 資陽(yáng) 黑河 荊州 錦州 滄州 隨州 吉林
巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《MySQL 大表的count()優(yōu)化實(shí)現(xiàn)》,本文關(guān)鍵詞 MySQL,大表,的,count,優(yōu)化,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題,煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無(wú)關(guān)。