關於mysql innodb count(*)速度慢的處理方法。本站提示廣大學習愛好者：（關於mysql innodb count(*)速度慢的處理方法）文章只能為提供參考，不一定能成為您想要的結果。以下是關於mysql innodb count(*)速度慢的處理方法正文
innodb引擎在統計方面和myisam是分歧的，Myisam內置了一個計數器，所以在應用 select count(*) from table 的時刻，直接可以從計數器中掏出數據。而innodb必需全表掃描一次方能獲得總的數目。要初步處理這個成績，須要做分歧於myisam的一些任務：
1、應用第二索引（普通不應用主鍵索引），而且添加where前提，如：
select count（*） from product where comp_id>=0 ;
show index from product ;
id primary key
comp_id index
2、假如只須要粗略統計的話也可以使用
show status from product; 來獲得年夜約值
這類辦法可在數據分頁中應用！
3、應用內部計數器，好比樹立一個觸發器來計數或許在法式上應用緩存方法准時計數，缺點是這些辦法會額定消費一些資本！
參考材料：
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
