關(guān)于mysql innodb count(*)速度慢的解決辦法
更新時(shí)間：2012年12月16日 12:35:32 作者：
innodb引擎在統(tǒng)計(jì)方面和myisam是不同的，Myisam內(nèi)置了一個(gè)計(jì)數(shù)器，所以在使用 select count(*) from table 的時(shí)候，直接可以從計(jì)數(shù)器中取出數(shù)據(jù)。而innodb必須全表掃描一次方能得到總的數(shù)量
innodb引擎在統(tǒng)計(jì)方面和myisam是不同的，Myisam內(nèi)置了一個(gè)計(jì)數(shù)器，所以在使用 select count(*) from table 的時(shí)候，直接可以從計(jì)數(shù)器中取出數(shù)據(jù)。而innodb必須全表掃描一次方能得到總的數(shù)量。要初步解決這個(gè)問題，需要做不同于myisam的一些工作：
1、使用第二索引（一般不使用主鍵索引），并且添加where條件，如：
select count（*） from product where comp_id>=0 ;
show index from product ;
id primary key
comp_id index
2、如果只需要粗略統(tǒng)計(jì)的話也可使用
show status from product; 來得到大約值
這種方法可在數(shù)據(jù)分頁中使用！
3、使用外部計(jì)數(shù)器，比如建立一個(gè)觸發(fā)器來計(jì)數(shù)或者在程序上使用緩存方式定時(shí)計(jì)數(shù)，缺陷是這些方法會(huì)額外消耗一些資源！
參考資料：
mysql高性能：http://www.mysqlperformanceblog.com/2006/12/01/count-for-innodb-tables/
mysql DBA:http://imysql.cn/2008_06_24_speedup_innodb_count
COUNT(*) for Innodb Tables
I guess note number one about MyISAM to Innodb migration is warning what Innodb is very slow in COUNT(*) queries. The part which I often however see omitted is fact it only applies to COUNT(*) queries without WHERE clause.
So if you have query like SELECT COUNT(*) FROM USER It will be much faster for MyISAM (MEMORY and some others) tables because they would simply read number of rows in the table from stored value. Innodb will however need to perform full table scan or full index scan because it does not have such counter, it also can't be solved by simple singe counter for Innodb tables as different transactions may see different number of rows in the table.
If you have query like SELECT COUNT(*) FROM IMAGE WHERE USER_ID=5 this query will be executed same way both for MyISAM and Innodb tables by performing index rage scan. This can be faster or slower both for MyISAM and Innodb depending on various conditions.
In real applications there are much more queries of second type rather than first type so it is typically not as bad problem as it may look. Most typically count of rows is needed by admin tools which may show it in table statistics, it may also be used in application stats to show something like “We have 123.345 users which have uploaded 1.344.656 images” but these are normally easy to remove.
So remember Innodb is not slow for ALL COUNT(*) queries but only for very specific case of COUNT(*) query without WHERE clause. It does not mean I would not like to see it fixed though, it is pretty annoying.
轉(zhuǎn)自：http://www.sphinxsearch.org/archives/89
1、使用第二索引（一般不使用主鍵索引），并且添加where條件，如：
復(fù)制代碼 代碼如下:
select count（*） from product where comp_id>=0 ;
show index from product ;
id primary key
comp_id index
2、如果只需要粗略統(tǒng)計(jì)的話也可使用
show status from product; 來得到大約值
這種方法可在數(shù)據(jù)分頁中使用！
3、使用外部計(jì)數(shù)器，比如建立一個(gè)觸發(fā)器來計(jì)數(shù)或者在程序上使用緩存方式定時(shí)計(jì)數(shù)，缺陷是這些方法會(huì)額外消耗一些資源！
參考資料：
mysql高性能：http://www.mysqlperformanceblog.com/2006/12/01/count-for-innodb-tables/
mysql DBA:http://imysql.cn/2008_06_24_speedup_innodb_count
COUNT(*) for Innodb Tables
I guess note number one about MyISAM to Innodb migration is warning what Innodb is very slow in COUNT(*) queries. The part which I often however see omitted is fact it only applies to COUNT(*) queries without WHERE clause.
So if you have query like SELECT COUNT(*) FROM USER It will be much faster for MyISAM (MEMORY and some others) tables because they would simply read number of rows in the table from stored value. Innodb will however need to perform full table scan or full index scan because it does not have such counter, it also can't be solved by simple singe counter for Innodb tables as different transactions may see different number of rows in the table.
If you have query like SELECT COUNT(*) FROM IMAGE WHERE USER_ID=5 this query will be executed same way both for MyISAM and Innodb tables by performing index rage scan. This can be faster or slower both for MyISAM and Innodb depending on various conditions.
In real applications there are much more queries of second type rather than first type so it is typically not as bad problem as it may look. Most typically count of rows is needed by admin tools which may show it in table statistics, it may also be used in application stats to show something like “We have 123.345 users which have uploaded 1.344.656 images” but these are normally easy to remove.
So remember Innodb is not slow for ALL COUNT(*) queries but only for very specific case of COUNT(*) query without WHERE clause. It does not mean I would not like to see it fixed though, it is pretty annoying.
轉(zhuǎn)自：http://www.sphinxsearch.org/archives/89
相關(guān)文章
最新Navicat?15?for?MySQL破解+教程?正確破解步驟
Navicat?for?MySQL是一個(gè)針對(duì)MySQL數(shù)據(jù)庫而開發(fā)的第三方mysql管理工具，該軟件可以用于?MySQL?數(shù)據(jù)庫服務(wù)器版本?3.21?或以上的和?MariaDB?5.1?或以上，這篇文章主要介紹了最新Navicat?15?for?MySQL破解+教程?正確破解步驟,需要的朋友可以參考下2023-04-04
這篇文章主要介紹了深入解析MySQL的窗口函數(shù),窗口可以理解為記錄集合，窗口函數(shù)就是在滿足某種條件的記錄集合上執(zhí)行的特殊函數(shù),即：應(yīng)用在窗口內(nèi)的函數(shù),需要的朋友可以參考下2023-07-07
MySQL數(shù)據(jù)庫復(fù)合查詢與內(nèi)外連接圖文詳解
本文詳細(xì)介紹了在SQL中進(jìn)行多表查詢的技術(shù),包括笛卡爾積、自連接、子查詢、內(nèi)連接和外連接等,文章還解釋了union和unionall的區(qū)別,以及如何在from子句中使用子查詢,這些技術(shù)對(duì)于處理復(fù)雜的數(shù)據(jù)庫查詢非常重要,可以有效地從不同表中提取和組合數(shù)據(jù),需要的朋友可以參考下2024-10-10
MySQL數(shù)據(jù)庫之?dāng)?shù)據(jù)表操作DDL數(shù)據(jù)定義語言
這篇文章主要介紹了MySQL數(shù)據(jù)庫之?dāng)?shù)據(jù)表操作DDL數(shù)據(jù)定義語言，文章圍繞主題展開詳細(xì)的內(nèi)容介紹，具有一定的參考價(jià)值，需要的小伙伴可以參考一下2022-08-08
這篇文章主要介紹了CentOS7 通過YUM安裝MySQL5.7的步驟詳解，本文給大家介紹的非常詳細(xì)，具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2020-01-01
MySQL INNER JOIN 的底層實(shí)現(xiàn)原理分析
這篇文章主要介紹了MySQL INNER JOIN 的底層實(shí)現(xiàn)原理,INNER JOIN的工作分為篩選和連接兩個(gè)步驟，連接時(shí)可以使用多種算法，通過本文，我們深入了解了MySQL中INNER JOIN的底層實(shí)現(xiàn)原理，需要的朋友可以參考下2023-06-06
淺談MySQL中g(shù)roup_concat()函數(shù)的排序方法
下面小編就為大家?guī)硪黄獪\談MySQL中g(shù)roup_concat()函數(shù)的排序方法。小編覺得挺不錯(cuò)的，現(xiàn)在就分享給大家，也給大家做個(gè)參考。一起跟隨小編過來看看吧2016-12-12
MySQL的存儲(chǔ)函數(shù)與存儲(chǔ)過程的區(qū)別解析
這篇文章主要介紹了MySQL的存儲(chǔ)函數(shù)與存儲(chǔ)過程的區(qū)別,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2022-04-04
巧用mysql提示符prompt清晰管理數(shù)據(jù)庫的方法
隨著管理mysql服務(wù)器越來越多，同樣的mysql>的提示符有可能會(huì)讓你輸入錯(cuò)誤的命令到錯(cuò)誤的數(shù)據(jù)庫，這時(shí)候需要巧用mysql的提示符，這是我的提示符root@localhost(mysql) 08:55:21> 用prompt命令實(shí)現(xiàn)(適用于windows和linux環(huán)境)2009-08-08
如何使用myisamchk和mysqlcheck工具快速修復(fù)損壞的MySQL數(shù)據(jù)庫文件
有時(shí)候數(shù)據(jù)庫突然就壞了很郁悶，用mysqlcheck.exe可以修復(fù)受損數(shù)據(jù)庫2020-01-01