SELECT count(*) FROM table1;
如果执行上述代码
对于MyISAM:
因为MySQL对该引擎的count有对应优化,精确的行数会被储存在存储引擎中,因此此类没有where条件的单表总行数查询会迅速返回结果。
对于InnoDB:
因为InnoDB的事务特性,在同一时刻表中的行数对于不同的事务而言是不一样的,因此count统计会计算对于当前事务而言可以统计到的行数,而不是将总行数储存起来方便快速查询。InnoDB会尝试遍历一个尽可能小的索引除非优化器提示使用别的索引。如果二级索引不存在,InnoDB还会尝试去遍历其他聚簇索引。
如果索引并没有完全处于InnoDB维护的缓冲区(Buffer Pool)中,count操作会比较费时。可以建立一个记录总行数的表并让你的程序在INSERT/DELETE时更新对应的数据。和上面提到的问题一样,如果此时存在多个事务的话这种方案也不太好用。如果得到大致的行数值已经足够满足需求可以尝试SHOW TABLE STATUS
SHOW TABLE STATUS
[{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]
dba.stackexchange上有个很有意思的回答:
- MyISAM
If mydb.mytable is a MyISAM table, launching SELECT COUNT(*) FROM mydb.mytable; is just like running SELECT
table_rows FROM information_schema.table WHERE table_schema = 'mydb' AND table_name = 'mytable';.
This triggers a quick lookup of the row count in the header of the MyISAM table.
- InnoDB
If mydb.mytable is a InnoDB table, you get hodge-podge of things going on. You have MVCC going on,
governing the following:
- ib_logfile0/ib_logfile1 (Redo Logs)
- ibdata1
- Undo Logs
- Rollbacks
- Data Dictionary Changes
- Buffer Pool Management
- Transaction Isolation (4 types)
- Repeatable Reads
- Read Committed
- Read Uncommitted
- Serializable
https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_count
https://dba.stackexchange.com/questions/17926/why-doesnt-innodb-store-the-row-count