關于mysql innodb count(*)速度慢的解決辦法
更新時間:2012年12月16日 12:35:32 作者:
innodb引擎在統(tǒng)計方面和myisam是不同的,Myisam內置了一個計數(shù)器,所以在使用 select count(*) from table 的時候,直接可以從計數(shù)器中取出數(shù)據(jù)。而innodb必須全表掃描一次方能得到總的數(shù)量
innodb引擎在統(tǒng)計方面和myisam是不同的,Myisam內置了一個計數(shù)器,所以在使用 select count(*) from table 的時候,直接可以從計數(shù)器中取出數(shù)據(jù)。而innodb必須全表掃描一次方能得到總的數(shù)量。要初步解決這個問題,需要做不同于myisam的一些工作:
1、使用第二索引(一般不使用主鍵索引),并且添加where條件,如:
select count(*) from product where comp_id>=0 ;
show index from product ;
id primary key
comp_id index
2、如果只需要粗略統(tǒng)計的話也可使用
show status from product; 來得到大約值
這種方法可在數(shù)據(jù)分頁中使用!
3、使用外部計數(shù)器,比如建立一個觸發(fā)器來計數(shù)或者在程序上使用緩存方式定時計數(shù),缺陷是這些方法會額外消耗一些資源!
參考資料:
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.
轉自:http://www.sphinxsearch.org/archives/89
1、使用第二索引(一般不使用主鍵索引),并且添加where條件,如:
復制代碼 代碼如下:
select count(*) from product where comp_id>=0 ;
show index from product ;
id primary key
comp_id index
2、如果只需要粗略統(tǒng)計的話也可使用
show status from product; 來得到大約值
這種方法可在數(shù)據(jù)分頁中使用!
3、使用外部計數(shù)器,比如建立一個觸發(fā)器來計數(shù)或者在程序上使用緩存方式定時計數(shù),缺陷是這些方法會額外消耗一些資源!
參考資料:
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.
轉自:http://www.sphinxsearch.org/archives/89
相關文章
windows server2014 安裝 Mysql Applying Security出錯的完美解決方法
這篇文章給大家介紹了windows server2014 安裝 Mysql Applying Security出錯的完美解決方法,造成這種問題的主要原因是因為安裝一遍之后沒有卸載干凈,要解決這個問題需要注意以下幾點,具體解決方法,大家參考下本文2017-07-07mysql自動停止 Plugin FEDERATED is disabled 的完美解決方法
今天在配置服務器的時候,發(fā)現(xiàn)mysql的一個錯誤提示在 本地計算機 無法啟動 MySQL 服務。錯誤 1067: 進程意外終止,其實原因很多這個不是導致進程意外終止的最終原因,但可以解決2016-04-04MySQL 5.0.96 for Windows x86 32位綠色精簡版安裝教程
這篇文章主要介紹了MySQL 5.0.96 for Windows x86 32位綠色精簡版安裝教程,需要的朋友可以參考下2017-10-10MySQL數(shù)據(jù)庫show processlist指令使用解析
這篇文章主要介紹了MySQL數(shù)據(jù)庫show processlist指令使用解析,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友可以參考下2019-11-11