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`;

第 1 条附言 · 2023-11-07 23:02:39 +08:00

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

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);

第 2 条附言 · 2023-11-07 23:03:15 +08:00

然后执行这条语句：
```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"
}
]
```

第 3 条附言 · 2023-11-08 22:33:33 +08:00

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

第 4 条附言 · 2023-11-08 22:37:29 +08:00

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

64 条回复 • 2023-11-10 12:20:18 +08:00

rimutuyuan

2023-10-29 11:12:02 +08:00

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

winglight2016

2023-10-29 11:19:51 +08:00

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

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

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

errZX

2023-10-29 12:01:01 +08:00 via Android

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

xoxo419

2023-10-29 12:16:21 +08:00 via iPhone

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

ZZ74

2023-10-29 12:19:31 +08:00

count(journal_pub_year ) 试一试？

ZZ74

2023-10-29 12:20:52 +08:00

或者 count(pm_id) 试一试？

mayli

2023-10-29 12:47:40 +08:00

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

mayli

2023-10-29 12:50:03 +08:00

我测试了一下，甚至 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

2023-10-29 13:11:00 +08:00 via Android

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

owen800q

2023-10-29 13:58:49 +08:00

差不多吧，建議換 mongo

bthulu

2023-10-29 14:13:08 +08:00

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

akira

2023-10-29 15:21:41 +08:00

不正常， explain 看一眼呢

me1onsoda

2023-10-29 16:33:25 +08:00

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

Bingchunmoli

2023-10-29 17:09:53 +08:00 via Android

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

wellsc

2023-10-29 17:12:14 +08:00

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

Flourite

2023-10-29 17:12:39 +08:00

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

Itesting

2023-10-29 18:19:24 +08:00

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

ahopunk

2023-10-29 19:16:24 +08:00

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

fd9xr

2023-10-29 19:20:03 +08:00 via iPhone

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

Features

2023-10-29 20:25:44 +08:00

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

huigeer

2023-10-29 20:47:09 +08:00 via iPhone

这种场景需要用 mysql 嘛😄

NickX

2023-10-29 21:00:19 +08:00

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

cleveryun

2023-10-29 21:19:27 +08:00

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

cleveryun

2023-10-29 21:22:28 +08:00

@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

2023-10-29 21:22:50 +08:00

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

cleveryun

2023-10-29 21:26:48 +08:00

@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

2023-10-29 21:35:20 +08:00

@huigeer 用什么比较合适呢

mahone3297

2023-10-29 22:59:57 +08:00

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

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

ohxiaobai

2023-10-29 23:33:46 +08:00

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

happy32199

2023-10-30 00:04:37 +08:00 via iPhone

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

lxy42

2023-10-30 00:16:50 +08:00

从 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

2023-10-30 00:21:27 +08:00

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

Rehtt

2023-10-30 08:24:29 +08:00

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

chunworkhard

2023-10-30 09:03:41 +08:00

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

ljsh093

2023-10-30 09:14:33 +08:00

@mayli #8 sqlite3 性能不弱的

ZX16815

2023-10-30 09:22:52 +08:00

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

encro

2023-10-30 09:29:08 +08:00

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

encro

2023-10-30 09:31:26 +08:00

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

xlzyxxn

2023-10-30 09:58:19 +08:00

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

coderzhangsan

2023-10-30 10:00:36 +08:00

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

2023-10-30 10:25:59 +08:00

mysql 的 count 好像本身就慢

28Sv0ngQfIE7Yloe

2023-10-30 10:52:57 +08:00

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

noparking188

2023-10-30 10:56:29 +08:00

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

realNewBee

2023-10-30 11:13:42 +08:00

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

justfindu

2023-10-30 11:17:09 +08:00

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

sleepybear1113

2023-10-30 11:56:13 +08:00

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

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

sleepybear1113

2023-10-30 12:01:18 +08:00

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

2023-10-30 13:23:13 +08:00

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

yh7gdiaYW

2023-10-30 13:52:36 +08:00

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

iyaozhen

2023-10-30 14:08:42 +08:00

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

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

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

MoYi123

2023-10-30 14:19:59 +08:00

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

tangyiyong

2023-10-30 14:43:46 +08:00

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

tangyiyong

2023-10-30 14:45:24 +08:00

journal_pub_year varchar(255) null,

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

索引里允许 null ？

ccagml

2023-10-30 18:37:20 +08:00 via Android

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

sivacohan

2023-10-31 11:17:59 +08:00

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

2023-11-08 19:41:02 +08:00 via Android

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

cleveryun

2023-11-08 19:43:10 +08:00 via Android

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

cleveryun

2023-11-08 19:44:04 +08:00 via Android

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

cleveryun

2023-11-08 19:44:59 +08:00 via Android

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

cleveryun

2023-11-08 19:46:44 +08:00 via Android

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

cleveryun

2023-11-08 19:50:22 +08:00 via Android

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

sivacohan

2023-11-09 09:43:33 +08:00

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

yh7gdiaYW

2023-11-09 13:53:45 +08:00

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

tangyiyong

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

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