mysql 四百万左右数据 count(*) 49 秒才响应，求助大佬怎么优化？

EXPLAIN SELECT COUNT(*) AS tp_count FROM `fa_people_check` `people_check` LEFT JOIN `fa_people` `people` ON `people_check`.`people_id`=`people`.`id` LIMIT 1;

大佬们，像这种怎么优化字段提升查询速度。执行一次 49s 就很离谱

count

People

大佬

字段

55 条回复 • 2021-08-02 17:26:10 +08:00

xixihaha1

2021-07-30 11:41:08 +08:00

people_id 有索引吗

luckyrayyy

2021-07-30 11:41:18 +08:00

没看太懂，你这怎么两个表 left join 两个表？另外左关联的 count(*) 不就是左表的 count(*) 嘛？

xixihaha1

2021-07-30 11:44:15 +08:00

@luckyrayyy 什么两个表，那是表的别名

ipwx

2021-07-30 11:46:32 +08:00

@luckyrayyy AS 省略，SQL 的奇葩语法

0o0o0o0

2021-07-30 11:48:08 +08:00

people_check.people_id
people.id
需要加索引

edk24

2021-07-30 11:49:07 +08:00

@0o0o0o0 加了的
@xixihaha1

加了索引的，因为是必须常用的字段

edk24

2021-07-30 11:49:31 +08:00

@ipwx 哈哈直接在慢日志里 copy 的。这个 as 是 thinkphp 框架加上的

iyaozhen

2021-07-30 11:51:38 +08:00

表结构呢？ people_check 还用了表分区，是依据哪个字段分的？

还有不加条件的话 count * 貌似是比较慢，count id 啥的看看？感觉这个 sql 想干什么不明确

ledkk

2021-07-30 11:54:08 +08:00

people_check 是一个分区表？分区的方式是什么样子的？分区表不带分区字段，查询肯定会非常慢了。

people 表只有一条记录么？如果是的话，或者记录条数很少的话，就没必要做 join 了，可以考虑用 in 替换一下

edk24

2021-07-30 11:54:37 +08:00

@iyaozhen 分区是原先想的替代方案，结果太麻烦而且并没有什么好处没用了，现在只想好好优化这个 sql 。

这个 sql 是查询数据 limit 前后，要返回数据总数，用来分页

SjwNo1

2021-07-30 11:55:49 +08:00

上面的索引明显加了啊。。
查看下 join_buffer_size 值吧，适当调整看看，不要 count(*)，先试试看

iyaozhen

2021-07-30 11:56:50 +08:00

@edk24 那你得把分区去掉呀，不然会有影响的

iyaozhen

2021-07-30 12:05:51 +08:00

@edk24 总数的话和 count people_check 这个表没啥区别？

myd

2021-07-30 12:17:17 +08:00

1. 给 people_check 加一个 people_id 普通索引
2. 给 fa_people 的 id 字段加一个唯一索引（已有主键索引的情况下，再加一个唯一索引）

myd

2021-07-30 12:17:37 +08:00

SQL 不用改

Jooooooooo

2021-07-30 12:42:46 +08:00

最好是增量的 count

klesh

2021-07-30 12:55:26 +08:00 via Android

索引对 where 和 order 有帮助，对 count 没有直接影响，因原理上需要遍历所有结果集里面的行。
可用 trigger 维护一个变量或定时 count 的方式绕过。

liuxu

2021-07-30 13:00:15 +08:00

这是索引的事吗，这是 join 出 temp 表做 count 的事啊。。

wellsc

2021-07-30 13:00:38 +08:00

单独搞个字段增量去记录 count

JJsty1e

2021-07-30 13:07:19 +08:00 via iPhone

你左表一条数据会对应右表多条数据吗？如果没有的话，就直接 count 左表吧没必要 left join 了

JJsty1e

2021-07-30 13:08:35 +08:00 via iPhone

晕，右表是主键啊，那可以去掉 left join
了

zoharSoul

2021-07-30 13:09:33 +08:00

不 count 返回个 99999 拉倒

est

2021-07-30 13:18:54 +08:00

百万级的表我觉得几十秒返回是正常的。。mysql 就这个水平。。。

micean

2021-07-30 13:20:47 +08:00

如果 fa_people 记录数小，可以分几步算出来
如果 fa_people 记录数大，fa_people_check 直接加字段吧

otakustay

2021-07-30 13:22:20 +08:00

不用 InnoDB 就好了

zhaokun

2021-07-30 13:29:02 +08:00

数据量小的表在前 join 其他表，可以稍微快一丢丢，join 能不要就不要了，查 count 不需要 json 吧

oueryini

2021-07-30 13:29:53 +08:00

换 MyISAM

bootvue

2021-07-30 13:30:06 +08:00

删掉 399 万

SjwNo1

2021-07-30 13:30:31 +08:00

上面的某几位仁兄简直在误导别人啊。。。

ruke

2021-07-30 13:37:45 +08:00

count 和索引没啥关系吧，索引是辅助查询的，count 是要对结果进行统计的

ruke

2021-07-30 13:38:45 +08:00

innodb 的话 count 本身就是扫描，就算不加条件 count 一样慢

0o0o0o0

2021-07-30 13:43:32 +08:00

@edk24 去掉分区之后有索引也这么慢吗？

robinlovemaggie

2021-07-30 13:46:39 +08:00

@est #23 如你所说，那 mysql 就是 shit 级数据库了

Valid

2021-07-30 13:56:46 +08:00

我 250 万的数据 count(*)用时 (0.876 秒)

0o0o0o0

2021-07-30 13:58:04 +08:00

@ruke 好像如果是辅助索引的话就不需要全表扫描了，不过这里有 left join 用索引可以加速的吧。。。

sunjiayao

2021-07-30 14:12:06 +08:00

做几个测试
1. 去掉 join
2. 去掉分区
3. left join 改成 right
然后抛下测试结果

nuistzhou

2021-07-30 14:14:51 +08:00 via iPhone

看看两个表的 id 有没有加索引吧

chenyi

2021-07-30 14:29:52 +08:00

people 表就一行，还是用的 left join，这个 left join 可以去掉

还有那些问有没有索引的，自己看看 explain 结果呀

ifconfig

2021-07-30 14:39:16 +08:00

很疑惑，既然没有加 where 条件，为什么要 join ?

realpg

2021-07-30 14:56:46 +08:00

抛开 49 秒的问题，你这 SQL 应该回炉了

maxiaofeng

2021-07-30 15:03:00 +08:00

@ifconfig 盲生，你发现了华点

atom234

2021-07-30 15:11:21 +08:00

数据库是 mysql，使用 count 的话 people_check 必须要有主键，排查下这 9 个分区的分区方式是啥，这个数据量好像也没必要分区啊。。。
还有换 MyISAM 的话并不建议会有事务问题。

Aruforce

2021-07-30 15:27:54 +08:00

有 where 过滤么？不过滤左联干啥啊

ybleak

2021-07-30 15:28:04 +08:00

这个 JOIN 我怕

rekulas

2021-07-30 16:21:38 +08:00

count(主键)会稍微快一些，当然想彻底解决只能缓存起来，count 本身很难优化，互联网巨头都没办法

hhjswf

2021-07-30 17:51:13 +08:00

几百万的数据分页好像无解啊，页数越往后越慢

Loku

2021-07-30 18:20:12 +08:00

有 9 个分区，然后 count(*)原因吧。count(`people_check`.id)吧

encro

2021-07-30 22:23:16 +08:00

写得什么鬼 SQL，
我大几千万数据 mysql 阿里云 2C4G RDS count 也就几秒啊，没分表分库。
不过对于我来说，超过 0.2 秒的 sql 是不允许存在的，所以 Count 的时候都是加了条件，保证 explain rows 不超过几千条记录的。对于统计是走聚合的。

那些还在鼓吹 myisam 的，也是一群无知，麻烦先试下是不是 Count 是否也慢？
还有说 innodb Count 慢的，能说出那个引擎 Count 不慢（包括收费的），
建议以上两类都先了解下 Count 慢的原因（原理）再开口？
采用 innodb 引擎的 mysql 工程师们都是一个傻子？丢下原来好用的 myisam 不用去全面切换为 innodb ？
商业数据库 count 快？他们是如何解决 like，in , or 等复杂条件 count 的？原理上可行吗？

rekulas

2021-07-30 22:53:57 +08:00

@encro 我们也用阿里数据库 rds polar 之类的也在用自建数据库，阿里的是做了优化感觉比原生 mysql 性能要高些，然后云数据库估计硬件配置也好所以快

abcbuzhiming

2021-07-31 11:07:18 +08:00

@encro 阿里云的 RDS 比很多人自建的快，楼上这些说性能的能把自己硬件软件配置报一下吗？楼主最好也把配置报一下，我见过有些人不懂，直接自己在云 ECS 上建 MySQL 的，他那个 ECS 还不是高性能 IO 的，那不就这个性能。要知道很多 Mysql 看起来测试性能很高，那人家那硬件动不动就是在 64 核，123G 内存，SSD 阵列，参数还优化到极致。不比你自己用 MySQL 社区版默认装的强到哪里去了