数据库查询优化问题，有没有老哥懂的？

count(*) 忽略 null
count(id) 筛查 id<> null

SELECT id FROM table_name 只是查出了一部分，可能就几十条，不是全部查出，你的数据库工具也没有把 30 万 id 都放内存里吧。
select count(id) from table_name 需要全部查出

如 #2 所说。

你的 SELECT id 是在哪里运行的，怎样算是“出结果”？

id 列自身就是个索引。
回归下实体案例理解下：你那本新华字典，真正内容厚 5cm （全文扫描），但你只看索引（执行 count(id) ）只需要 0.5cm

@adoal
@cstj0505 其实用什么工具查没影响的，我用的是 navicat ，在查询标签里面手打的 sql 语句，连远程数据库查的，直接查 id 本地查到了 40 万（刚刚数据库又增加了一些数据）数据，每个 id 都有，但是无序，我是拖动滚动条看的，右下角会显示“第 1 条”到“第 400000 条”的字样，由于数据库还在更新，现在查 id 大概在 3s 以内，查 count 还是远超 20 秒

navicat 默认给你查 1000 条所以很快，你试试 limit 400000 全查出来应该也慢。id 是主键应该不可 null,直接用 count(*)和 count(1)性能是最快的

什么数据库？什么版本？什么存储引擎？
这些都没说怎么分析数据库里怎么跑的？

没有优化方法途径。count 就是慢。order by 也是慢

可能是 SELECT id FROM table_name 被 navicat 自动加了 limit

innodb 的大表 count 就是难搞。
1. 如果只有一个 id 索引，然后表又很大, 解决方案是加一个非主键索引，然后查询用 count(*), 它会挑最小数据量的索引进行统计。
2. 使用其他取巧方法:
比如 id 是自增的，从 0 开始，那么最大的 id 就是总数。
比如用 show table status, 虽然不准但勉强够用.
比如上缓存, 程序启动时候查一次缓存起来, 业务更新缓存.
3. 改需求，不要 count 全表。比如后台的管理列表的分页, 就可以只查询前 10W 条，其他情况下带精确的查询条件时再解除限制。

覆盖索引

看看表结构

navicat 的统计自动走了统计表，不是准确的。

count 是必须准确的，而且是需要考虑脏数据。

explain select count(id) from table_name; 看看用的什么索引，索引长度是多少

关系数据库几个很多无法解决的问题：

1,count 慢
2,order by xxx limit 1000000,10 （取最后页的慢）
3,like “%xxx”慢
4,没有合理建立索引慢
5,自动更新物理试图
6,。。。

以上等等限制才会有 es,mongo,hbase,redis,InfluxDB,GDB 等等的机会。以上导致关系数据库没有走主键索引的话也就千万级别数据量比较合适吧。如果都是走主键那随便放。

你把两个 sql 的执行过程贴出来

看执行计划，时间花在哪了。。。

我寻思 count 可不就必须全盘扫描了，还搁着索引呢。。有用吗

应该是 count 用了临时表

因为你 SELECT id FROM table_name; 这条查询的是第一页吧....

还有一种可能，都慢，第一种方式走缓存了

navicat 会自动加 limit ，所以你前面那个是秒出。

SELECT id FROM table_name;--一秒钟出结果，我的表大概三十万数据
select count(id) from table_name;--超过二十秒

不可能，不科学，SELECT id FROM table_name 也是把 30 万条数据每一条都查出来，怎么可能差别那么大

@encro 支持事务 mvcc 数据库 count 基本无解的，因为需要扫描每一行数据的更新状态，对当前查询是否可见，如果被别的事务修改了还要去查找历史版本

MySQL 8.0 ，innodb 的引擎，实在不好意思，本人确实对数据库不太了解，不知道检测需要从哪些指标切入，感谢指点

@leahoop 你怎么不看一看我的评论呢，navicat 查出来的结果是 40 万条，我不希望因为这些没有意义的讨论浪费时间，还请认真一点

@encro 我这种用法直接用命令行不也是一样的结果吗？跟 navicat 没关系吧

@littlewing 这就是我的疑问呀，我不明白的就是这个地方

@cstj0505 确实，因为查询的时候我的数据库还在更新，其实我只是需要一个大概的数值来检查更新进度就可以了，那这样我感觉直接查所有 id 然后在程序中对 id 计数会比较满足需求，直接用 count 可能就会因为更新问题导致查询变慢，应该这样理解吧

如果查询没有 where 条件的话，而且是大表，我建议自己在 db 中缓存 count 计数，insert 一条，count+1 ，insert 和 count+1 的 update 在同一事物中一并提交。
如果有 where 条件的话，上覆盖索引。
再者 count(*)是最快的

@wangxin3 没有 where 条件。你说的这个记录是在另外一个表中记录吗？

@wangxin3 还有，我这里实测就是 count （*）没有查所有 id 快啊，你直接再来给个结论是有什么依据吗？

@cy1027 #31 原文：“@wangxin3 没有 where 条件。你说的这个记录是在另外一个表中记录吗？”
======
回复：是的

@cy1027 #32 原文：“@wangxin3 还有，我这里实测就是 count （*）没有查所有 id 快啊，你直接再来给个结论是有什么依据吗？”
======
回复：count(主键 id)，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。
但是 count （*），InnoDB 引擎是作过特殊处理的，不取值，直接按行累加。相较于 count （主键 id ）少了取每一行 id 的操作。

@cy1027 #32 原文：“@wangxin3 还有，我这里实测就是 count （*）没有查所有 id 快啊，你直接再来给个结论是有什么依据吗？”
======
回复：可能我理解错了 select count(*) from table; 和 select id form table;不存在比较的意义。不是一个层级的东西。

@cy1027


就是 navicat 自动给你加了 limit...
然后他的统计是来自 infomation 表的大概统计，如果你仔细看前面有一个约等于符号，我没有记错的话。

你直接用命令行 select * 看看，一片跑一边看 show full processlist ，几十万数据 send data 状态都要几秒吧。

数据库的耗时有几个阶段：

查询解析：分析关系，走哪个索引？
取数据：内存（走了索引）或磁盘（没走索引）？
预处理结果：排序或格式化等，走了内存或者临时表？
发送数据：发数据到客户端，结果集大肯定慢了

结合慢查询，processlist 和 explain 可以发现并解决问题，如果是我前面的问题，基本无解。

如前面所说，我刚才试了下，在我的阿里云小 rds （ 1 核）上，900 多万数据无论是 select * 还是 id ，都需要一分多钟，两者走的是同样的索引，所以实际项目我都是加了条件限制的，默认只查当个月数据，用户加日期建立了组合索引，不让用户直接翻页到最后一页。

@wangxin3

对不起，我用的 mysql8.17 没有这个功能。
explain
两个走的是一样的索引。
同时走了一个 tinyint 的索引。
你可以在线上试一下看。

@wangxin3

这个特性好像是 20 年前的 myisam 引擎的，不是 innodb 引擎的。

pg 100 万数据，select count(*)和 select count(id)也都是一样的索引，一样的 200ms ，刚试过的。

所以：

不要相信关系性数据库 count 能快，是要实际排序计算的。

不要相信数据库工程师很菜，不会自动分析走哪个索引，明明有更优化的索引不走，走个慢的。

@wangxin3 感谢回复

@encro #38 原文：“@wangxin3 这个特性好像是 20 年前的 myisam 引擎的，不是 innodb 引擎的。”
======
回复：是 innodb 的。myisam 引擎是把 count 数目保存在磁盘上的，如果没有 where 条件，获取速度是非常快的。