mysql 这个查询速度正常吗，怎么优化？

create table `bio-hub`.`pubmed-article-0` ( pm_id int not null primary key, title varchar(2000) not null, author text not null, lang varchar(255) null, abstract text null, keywords text null, journal_title varchar(255) null, journal_pub_year varchar(255) null, journal_pub_month varchar(255) null, journal_i_s_s_n varchar(255) null, mesh_ids varchar(2000) null, mesh_cat varchar(2000) null comment '医学主题词所属分类，如`A01`', created_at datetime not null, updated_at datetime not null ); create index `pubmed-article-0_journal_pub_year` on `bio-hub`.`pubmed-article-0` (journal_pub_year);

现状是我再 DataGrip 里光执行下面这样一句 count 都要三四十秒（首次，没缓存的情况下），是我哪里姿势不对吗，这也太慢了。带上关键词查询的 sql 不得更慢了。怎么破？

更新：我人在上海，数据库节点也是上海的。

SELECT COUNT(1) FROM `pubmed-article-1`;

Supplement 1 Nov 7, 2023

参考大家的建议，这几天做了一些优化，量一上来还是很慢，感觉没辙了，下面是新的表结构。

create table if not exists `bio-hub`.`pubmed-article` ( pm_id int not null primary key, title varchar(2000) not null, author text not null, lang char(3) not null, abstract text not null, keywords text not null, journal_title char(239) not null, journal_pub_year smallint not null, journal_pub_month tinyint not null, journal_i_s_s_n char(9) not null, mesh_ids varchar(543) not null, mesh_cat char(3) not null, created_at datetime not null, updated_at datetime not null ); create index `pubmed-article_journal_pub_year` on `bio-hub`.`pubmed-article` (journal_pub_year); create fulltext index title_index on `bio-hub`.`pubmed-article` (title);

Supplement 2 Nov 7, 2023

然后执行这条语句：
```sql
SELECT COUNT(pm_id) FROM `pubmed-article` WHERE journal_pub_year > 0

```
返回的 count 结果是 14375917 （我把最近 20 年的数据放到了 pubmed-article 表中）。
用时：1 row retrieved starting from 1 in 3 s 194 ms (execution: 3 s 159 ms, fetching: 35 ms)

```sql
SELECT COUNT(pm_id) FROM `pubmed-article` WHERE journal_pub_year > 2022;
```
返回 count 结果是 10907 。
用时：1 row retrieved starting from 1 in 135 ms (execution: 116 ms, fetching: 19 ms)

```sql
SELECT COUNT(pm_id) FROM `pubmed-article` WHERE journal_pub_year > 2022 AND title like '%test%';
```
返回 count 结果是 184 。
用时：1 row retrieved starting from 1 in 110 ms (execution: 88 ms, fetching: 22 ms)。

```sql
SELECT COUNT(pm_id) FROM `pubmed-article` WHERE journal_pub_year > 2022 AND MATCH (title) AGAINST ('hello' IN NATURAL LANGUAGE MODE);
```
返回 count 结果是 8621 。
用时：1 row retrieved starting from 1 in 17 m 45 s 295 ms (execution: 17 m 45 s 257 ms, fetching: 38 ms)

```sql
SELECT COUNT(pm_id) FROM `pubmed-article` WHERE journal_pub_year > 2010
```
返回 count 结果是 10135085 。
用时：1 row retrieved starting from 1 in 2 s 178 ms (execution: 2 s 147 ms, fetching: 31 ms)

```sql
SELECT COUNT(pm_id) FROM `pubmed-article` WHERE journal_pub_year > 2010 AND title like '%test%';
```
返回 count 结果是 197070 。
用时：1 row retrieved starting from 1 in 1 m 39 s 260 ms (execution: 1 m 39 s 221 ms, fetching: 39 ms)

```sql
explain SELECT COUNT(pm_id) FROM `pubmed-article` WHERE journal_pub_year > 2010 AND title like '%test%';
```
返回：
```json
[
{
"id": 1,
"select_type": "SIMPLE",
"table": "pubmed-article",
"partitions": null,
"type": "ALL",
"possible_keys": "pubmed-article_journal_pub_year",
"key": null,
"key_len": null,
"ref": null,
"rows": 14042175,
"filtered": 5.55,
"Extra": "Using where"
}
]
```

Supplement 3 Nov 8, 2023

数据量的话，我用 datagrip 的导出到文件（多行）试了下导出 750 万行大概 9.5GB 大小。估算 4000 万行数据大学对应 50GB 空间。

Supplement 4 Nov 8, 2023

这是不是意味着我需要买台 64G 运行内存的数据库/服务器？

64 replies 2023-11-10 12:20:18 +08:00

14v45mJPBYJW8dT7

Oct 29, 2023

不正常，尝试用数据库同地域的服务器测试下

winglight2016

Oct 29, 2023

配置低了，我们这里 4000 多万条数据，也是 mysql ，机器配置高一些，count 一下也需要 17 秒左右。

count 是全表扫描，这个速度算是正常吧。

如果是查询 limit 1000 以内，可以 1 秒左右返回。

errZX

Oct 29, 2023 via Android

打个索引看看，不走索引的话估计比较难受

xoxo419

Oct 29, 2023 via iPhone

count 走的全表扫描，where 后的字段加索引后应该是很快的，如果需要频繁统计总数再建立一张统计表用来专门保存统计的数据

ZZ74

Oct 29, 2023

count(journal_pub_year ) 试一试？

ZZ74

Oct 29, 2023

或者 count(pm_id) 试一试？

mayli

Oct 29, 2023

500 万行 = 5M 行, 你要是不需要 InnoDB 的特性，试试用 MyISAM 。
一般这种静态表可以不用 InnoDB ，如果必须要 InnoDB 看看增大 innodb_buffer_pool_size 。

mayli

Oct 29, 2023

我测试了一下，甚至 sqlite3 也没有这么慢

$ sqlite3 test.db
SQLite version 3.40.1 2022-12-28 14:03:47
Enter ".help" for usage hints.
sqlite> create table random_data as
with recursive tmp(x) as (
select random()
union all
select random() from tmp
limit 5000000
)
sqlite> select count(1) from random_data;
5000000

$ time sqlite3 test.db 'select count(1) from random_data;'
5000000

real 0m0.175s
user 0m0.124s
sys 0m0.051s

fredcc

Oct 29, 2023 via Android

mysql.n8m.medium.2c 是通用型，与其他用户共享 CPU 磁盘资源，不保证最大 iops
阿里云 RDS 自带基础硬件性能监控，查询性能监控和查询优化建议。

owen800q

Oct 29, 2023

差不多吧，建 mongo

bthulu

Oct 29, 2023

mysql 的全表 count 就是这么慢的, 你要么换 sql server 或者 oracle, 表 count 瞬间返回

akira

Oct 29, 2023

不正常， explain 看一眼呢

me1onsoda

Oct 29, 2023

说个题外话，这个配置合理吗？我 4 核 8g ，只能只吃 60%巍然不动，cpu 经常打满

Bingchunmoli

Oct 29, 2023 via Android

大概率是公网调用等延迟了

wellsc

Oct 29, 2023

别用 count ，找个外部存储存计数

dV9zZM1wROuBT16X

Oct 29, 2023

缺少数据库参数，innodb_buffer_pool_size 是多少

Itesting

Oct 29, 2023

你这全表扫描了，count 就这么慢，加配置 or 加 where 条件吧

ahopunk

Oct 29, 2023

同阿里云 mysql 实例，这个速度是正常的。
不过楼主 2 核 16G 内存的配置有点不理解，我 4 核 8G 的实例，跑起来内存和 cpu 占用比较和谐。

fd9xr

Oct 29, 2023 via iPhone

无语…才四千万你优化它干毛

Features

Oct 29, 2023

啊？还有人的 slow_launch_time 值大于 1 吗？
楼上说 count 就是这么慢认真的吗？

huigeer

Oct 29, 2023 via iPhone

这种场景需要用 mysql 嘛

NickX

Oct 29, 2023

看看 cpu 占用情况，感觉是服务器的问题。

cleveryun

Oct 29, 2023

@winglight2016 你们用的配置大概是如何，可以参考下吗，不知道该升级到什么配置。这个配置 limit 1000 的话用时在 2 秒左右。

cleveryun

Oct 29, 2023

@errZX explain 一下看着是走了索引。

```sql
EXPLAIN SELECT COUNT(1) FROM `pubmed-article-0`;
```

结果：
[
{
"id": 1,
"select_type": "SIMPLE",
"table": "pubmed-article-0",
"partitions": null,
"type": "index",
"possible_keys": null,
"key": "pubmed-article-0_journal_pub_year",
"key_len": "1023",
"ref": null,
"rows": 3722473,
"filtered": 100,
"Extra": "Using index"
}
]

dimingchan

Oct 29, 2023

肯定是用了默认的 InnoDB 存储引擎，InnoDB 的 count 是需要全表扫描的，如果不需要用到事务，建议换成 myisam 存储引擎，元数据直接记录表的记录数的；另外，不要 count(1)，count(pm_id)，count("主键")是最快的了。

cleveryun

Oct 29, 2023

@mayli 谢谢提醒，我搜了下阿里云的 RDS MySQL 不支持 MyISAM 。InnoDB 的 innodb_buffer_pool_size 默认配置是{DBInstanceClassMemory*3/4}，最大可以调整到{DBInstanceClassMemory*8/10}，看了大家的评论，我去调整到 8/10 了（最大可调值）。

*注：DBInstanceClassMemory：实例规格的内存大小减去实例的管控进程占用的内存大小，整数型。例如，实例规格的内存大小为 16 GB ，实例的管控进程占用的内存大小为 4 GB ，则 DBInstanceClassMemory 的值为 12 GB 。

cleveryun

Oct 29, 2023

@huigeer 用什么比较合适呢

mahone3297

Oct 29, 2023

@cleveryun 看你的 explain 结果，372w 行的数据，你的配置也不高，2c ，这个结果就这样，我认为合理吧

你应该考虑的事，这些数据不从 mysql 查，从 redis 查。每次都基本上全表扫描，当然就是这结果了。
ps：4kw 完全不需要分表吧。mysql 完全可以上亿。不过也要看单行数据量。

ohxiaobai

Oct 29, 2023

这个 count 语句相等于扫描全表了，慢是正常的。
1. MySQL 单表数量上限很高，这种数据量级不算大。
2. 建议根据具体场景优化，比如针对这个 SQL ，如果不考虑删除，那么可以 count 1 次，然后用缓存计数，后面就不用 count 了；或者加一个自增 id 字段，设置从当前 count 之后开始自增，这样只需要记录新增后的自增 id 值就行了。

happy32199

Oct 30, 2023 via iPhone

换 32 核 64g 内存的 ecs ，自己装 mysql ，保证又快又省钱……而且 10 亿前不用分表

lxy42

Oct 30, 2023

从 explain 结果来看, 查询使用了 pubmed-article-0_journal_pub_year 索引, 正文说这个索引建立在 journal_pub_year 列, journal_pub_year 的长度是 255, 主键的长度是 4, 那么 explain 中的 key_len 不应该是 1023 啊.

另外你说分了 8 张表, 每张表 5M 记录, 可是 explain 中的 rows 是 3722473. 看你的描述应该是手动分表, 也没有使用 MySQL 自带的分区表

lxy42

Oct 30, 2023

@lxy42 忘了一个 char 根据 charset 的不同可能对应多个字节, key_len 还是有可能是 1023 的

Rehtt

Oct 30, 2023

@fd9xr 才四千万就这么慢不优化难道删库跑路吗

chunworkhard

Oct 30, 2023

count 不加条件单表 500W 按理说正常也得 4-5s 吧

ljsh093

Oct 30, 2023

@mayli #8 sqlite3 性能不弱的

ZX16815

Oct 30, 2023

排查一下你的网络，如果没问题的话就提个工单问问吧。

encro

Oct 30, 2023

不要问了，count 就是慢。。。。。。

encro

Oct 30, 2023

你这 4000 万数据不用分表，直接查询，count 一定要代条件，建议为时间建立索引，只 count 最近几天的，就能快非常多。

xlzyxxn

Oct 30, 2023

先说结论：1 、count(*)=count(1)>count(主键字段)>count(字段)
2 、对大表使用 count 是不好的
楼主这张表创建了二级索引，所以 count(*)会使用这个二级索引，从 explain 的结果可以看出来符合
优化：1 、使用近似值，show table status 或者 explain 命令来表进行估算，explain 是很快的，rows 字段值就是估算出来的
2 、如#15 楼所说，将具体计数保存在另外一张表中

coderzhangsan

Oct 30, 2023

1.mysql count 查询默认会使用表中索引长度最短得二级索引，索引长度越长，扫描越慢，可以冗余个 tinyint 或 int 列做二级索引。
2.如果不是精确的统计查询，可以使用 explain count ... 中得扫描行数或 show table status like '{table}' rows 来替代。
https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_count

opengps

Oct 30, 2023

mysql 的 count 好像本身就慢

28Sv0ngQfIE7Yloe

Oct 30, 2023

感觉楼上没说到点子上吧，你直接用阿里的 DMS 平台查下

noparking188

Oct 30, 2023

你 DataGrip 确定没问题嘛，你直接 mycli 命令行连上去 count 看看时间

realNewBee

Oct 30, 2023

正常，这个数据量这个配置，count 就是慢。所以，如果非得查询全表的数据，不走条件的情况下，必须得根据实际需求来换种查法

justfindu

Oct 30, 2023

count(*) 试一试, 不要 1 .

sleepyfevniv

Oct 30, 2023

weibo> SELECT COUNT(*)
FROM weibo.hot_search_realtime t

[2023-10-30 11:52:23] completed in 5 m 59 s 185 ms

总共行数：2892 7376

基础型-1 核 1000MB 内存，100GB 存储空间，高性能云盘，IOPS:2300

比你的稍微慢一点点。从上海访问成都。

sleepyfevniv

Oct 30, 2023

exam_data> SELECT COUNT(*)
FROM exam_data.admission_data t
[2023-10-30 11:57:56] completed in 256 ms

行数：45 4828
实例规格 1 核/1GB 最大 IOPS 8000 TDSQL-C MySQL 兼容数据库 MySQL5.7

这个快不少

9y7cz863P00C7Lie

Oct 30, 2023

MySQL 从 8.0.17 开始对于无条件的 count(*)会强制走主键，即使执行计划里面写的是走二级索引，这是因为因为在这个版本更新了并行扫描主键的功能。由于你的机器 CPU 配置不高，肯定跑不出好的效果。所以从结果上来说就比 5.7 的扫描二级索引要慢

yh7gdiaYW

Oct 30, 2023

mysql 做这个量级的统计查询时就是很慢（当然你这个有点不正常），我们当初因为这个换了 MongoDB ，现在准备换到 StarRocks （这玩意儿速度太逆天了）

iyaozhen

Oct 30, 2023

count(*)=count(1)>count(主键字段)>count(字段) 这个不认同，可以让楼主试下，count 啥都是慢

按之前经验来看，就是慢，不要不加 where 条件的 count 。用前面大家说的估算方法

带上关键词查询的 sql 不得更慢了，NO 。你 where 命中索引是很快的，几百万一张表几乎不需要啥优化。

MoYi123

Oct 30, 2023

mysql 的 count 就是这样的, 具体原因和事务隔离有关系;

tangyiyong

Oct 30, 2023

会不会是因为索引建在 varchar(255)上的问题？文本的字段一般不能做索引吧？

tangyiyong

Oct 30, 2023

journal_pub_year varchar(255) null,

create index `pubmed-article-0_journal_pub_year`
on `bio-hub`.`pubmed-article-0` (journal_pub_year);

索引里允许 null ？

f9EHtxE2UtX3fG3

Oct 30, 2023 via Android

这种是不是得阿里云监控看看有什么指标达到瓶颈了吗？感觉也太慢了

sivacohan

PRO

Oct 31, 2023

fio --ioengine=libaio --bs=4k --direct=1 --thread --time_based --rw=randrw --filename=/root/io_test --runtime=300 --numjobs=1 --iodepth=1 --group_reporting --name=randread-dep1 --size=256M

测一下磁盘 IO 性能看看

cleveryun

Nov 8, 2023 via Android

大佬们求救，最新的情况我 Append 追加了。

cleveryun

Nov 8, 2023 via Android

字段长度我都改成实际数据中各字段实际最长的长度值了

cleveryun

Nov 8, 2023 via Android

@sivacohan 这是是要在服务器上跑吗，我买的是单独的数据库，只能跑 sql 语句。

cleveryun

Nov 8, 2023 via Android

@tangyiyong 已修改，年月都改成数字重新建了索引

cleveryun

Nov 8, 2023 via Android

@iyaozhen 我的场景要用到类似 like '%keyword%'的功能。用户会用关键词搜。

cleveryun

Nov 8, 2023 via Android

@yh7gdiaYW 用 MongoDB 后速度怎么样了，这个量大概需要买多大的 MongoDB 数据库。

sivacohan

PRO

Nov 9, 2023

@cleveryun
噢，我看错了。我以为是自己部署的数据库。
直接用 RDS 的话，就看看监控吧，看跑查询的时候什么指标是瓶颈。

yh7gdiaYW

Nov 9, 2023

@cleveryun 比 MySQL 速度快很多，主要是查询耗时不会随数据规模非线性增长。但用于数据分析的话，MongoDB 和 starrocks 比有数量级上的差距，10 倍速度、1/10 空间占用，我调研完都惊了

tangyiyong

Nov 10, 2023

MATCH() AGAINST()会使用全文索引，journal_pub_year > 2022 的条件会尝试使用 B-tree 索引；通常不会在一个查询中组合使用全文索引和 B-tree 索引，可能是索引问题吧？