MySQL innodb 引擎 : 如何優化select count(*)的速度!? 有可能嗎?

這是一個相當特別的問題, 從以前學習MySQL的印象就是, myisam查詢快、insert 快，因為不需處理transaction問題, 相較於innodb engine做純查詢使用myisam engine是比較好的選擇.

卻不知道原來select count(*) 約兩千萬筆的innodb table資料 需要20秒的時間.

原先以為可能是mysql 5.7.18 上面的bug (Bug #24337385, Bug #82313) , 不過爬了一下該link  https://docs.oracle.com/cd/E17952_01/mysql-5.7-relnotes-en/news-5-7-18.html

#EXPLAIN SELECT COUNT(*) FROM tbl_name could incorrectly report an Extra value of Select tables optimized away due to a modification introduced in MySQL 5.7.2 that caused InnoDB to count rows by traversing the clustered index instead of a smaller secondary index. The Extra value now displays Count Rows. (Bug #24337385, Bug #82313)

看到那是針對explain select count(*) 才會發生的不正確output, 而客戶這邊的測試並非下了explain select count(*)，也沒有建任何pk (clustered index)

而是下select count(*)... 且表格沒有加pk , 沒有index...花了二十秒的時間 (加了pk後同樣的SQL查詢速度就飛快了, 走index查詢)

思考了一下在實際場景下，其實要看實際的AP scenario/ SQL 語法(SQL text)來優化mysql

畢竟知道了mysql的限制在哪 ;  google關鍵字: innodb select count very slow 會發現一堆已經踩到雷的人

innodb 引擎沒有對select count(*) 優化 所以要透過pk or index的方式來加速查詢(table上面建PK是基本一定要做的)

如果innodb表格有時間欄位或特定欄位 只有0 , 1 ,2 , 3 等資料型態  可以從SQL tuning開始著手. 從AP的寫法開始 tuning(把select count(*) 的結果暫存在AP上數分鐘) 有時候會來的更有效率一點.

不能作什麼事都select count(*) 給他下去

->這樣可是會搞死DBA的...   #了解資料庫引擎特性很重要

ref1:
https://www.percona.com/blog/2006/12/01/count-for-innodb-tables/
So remember Innodb is not slow for ALL COUNT(*) queries but only for very specific case of COUNT(*) query without WHERE clause.

ref2:

http://www.liwei1987.com/2016/05/12/mysql%E5%A4%A7%E8%A1%A8%E6%9F%A5%E8%AF%A2count%E6%85%A2%E7%9A%84%E4%BC%98%E5%8C%96%E6%96%B9%E6%A1%88/

ref3:

http://xiaoych.iteye.com/blog/582728