count(*)统计技术的操作，不同的数据库可能实现不同，当然性能上也会有一些坑，例如Oracle中，如果索引中存在空值，count(*)还会用全表扫，而不是索引全扫描的访问，因为怕统计错了。
MySQL中的count(*)，不同引擎，实现上略有区别，参考技术社群的这篇文章《新特新解读 | MySQL 8.0 对 count(*)的优化》，了解下对count(*)所做的优化，知其然，更要知其所以然。
还是ytt1，不过每次查询的时候用sql_calc_found_rows和found_rows()来获取总数，比如：
mysql> select sql_calc_found_rows * from ytt1 where 1 order by id desc limit 1;
+------+------+
| id | r1 |
+------+------+
| 3072 | 73 |
+------+------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead. |
+---------+------+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select found_rows() as 'count(*)';
+----------+
| count(*) |
+----------+
| 3072 |
+----------+
1 row in set, 1 warning (0.00 sec)
从warnings信息看，这种是MySQL 8.0之后要淘汰的语法。
3. 从数据字典里面拿出来粗略的值
mysql> select table_rows from information_schema.tables where table_name = 'ytt1';
+------------+
| TABLE_ROWS |
+------------+
| 3072 |
+------------+
1 row in set (0.12 sec)
那这样的适合新闻展示，比如行数非常多，每页显示几行，一般后面的很多大家也都不怎么去看。缺点是数据不是精确值。
4. 根据表结构特性特殊的取值
mysql> select max(id) as cnt from ytt1;
+------+
| cnt |
+------+
| 3072 |
+------+
1 row in set (0.00 sec)
不过这种对表的数据要求比较高。
5. 标准推荐取法（MySQL 8.0.17建议）
mysql> select * from ytt1 where 1 limit 1;
+----+------+
| id | r1 |
+----+------+
| 87 | 1 |
+----+------+
1 row in set (0.00 sec)
mysql> select count(*) from ytt1;
+----------+
| count(*) |
+----------+
| 3072 |
+----------+
1 row in set (0.01 sec)
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.27-log |
+------------+
1 row in set (0.00 sec)
mysql> explain format=json select count(*) from ytt1\G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "622.40"
},
"table": {
"table_name": "ytt1",
"access_type": "index",
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "4",
"rows_examined_per_scan": 3072,
"rows_produced_per_join": 3072,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "8.00",
"eval_cost": "614.40",
"prefix_cost": "622.40",
"data_read_per_join": "48K"
}
}
}
}
1 row in set, 1 warning (0.00 sec)
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.17 |
+-----------+
1 row in set (0.00 sec)
mysql> explain format=json select count(*) from ytt1\G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "309.95"
},
"table": {
"table_name": "ytt1",
"access_type": "index",
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "4",
"rows_examined_per_scan": 3072,
"rows_produced_per_join": 3072,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "2.75",
"eval_cost": "307.20",
"prefix_cost": "309.95",
"data_read_per_join": "48K"
}
}
}
}
1 row in set, 1 warning (0.00 sec)
如果您认为这篇文章有些帮助，还请不吝点下文章末尾的"点赞"和"在看"，或者直接转发pyq，
