买的数据库是阿里云的，配置信息：
- 数据库类型：MySQL8.0
- 规格族：通用型
- CPU：2 核
- 数据库内存：16384 M
- 规格默认连接数：8000
- 最大 IOPS：6800
- 实例规格：mysql.n8m.medium.2c
目前有 4 千万不到的数据，我拆成了 8 个表，每个表放 500 万行数据。单张表的表结构如下：
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 月 7 日
参考大家的建议，这几天做了一些优化，量一上来还是很慢，感觉没辙了，下面是新的表结构。
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 月 7 日
然后执行这条语句：
```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"
}
]
```
```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 月 8 日
数据量的话，我用 datagrip 的导出到文件（多行）试了下导出 750 万行大概 9.5GB 大小。估算 4000 万行数据大学对应 50GB 空间。
第 4 条附言 · 2023 年 11 月 8 日
这是不是意味着我需要买台 64G 运行内存的数据库/服务器？
64 条回复 • 2023-11-10 12:20:18 +08:00
 |
1
14v45mJPBYJW8dT7 2023 年 10 月 29 日
不正常，尝试用数据库同地域的服务器测试下
|
 |
2
winglight2016 2023 年 10 月 29 日
配置低了，我们这里 4000 多万条数据，也是 mysql ，机器配置高一些，count 一下也需要 17 秒左右。
count 是全表扫描，这个速度算是正常吧。 如果是查询 limit 1000 以内，可以 1 秒左右返回。 |
 |
3
errZX 2023 年 10 月 29 日 via Android
打个索引看看，不走索引的话估计比较难受
|
 |
4
xoxo419 2023 年 10 月 29 日 via iPhone
count 走的全表扫描，where 后的字段加索引后应该是很快的，如果需要频繁统计总数再建立一张统计表 用来专门保存统计的数据
|
 |
5
ZZ74 2023 年 10 月 29 日
count(journal_pub_year ) 试一试？
|
|
6
ZZ74 2023 年 10 月 29 日
或者 count(pm_id) 试一试？
|
 |
7
mayli 2023 年 10 月 29 日
500 万行 = 5M 行, 你要是不需要 InnoDB 的特性，试试用 MyISAM 。
一般这种静态表可以不用 InnoDB ，如果必须要 InnoDB 看看增大 innodb_buffer_pool_size 。 |
|
8
mayli 2023 年 10 月 29 日  1
我测试了一下，甚至 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 |
 |
9
fredcc 2023 年 10 月 29 日 via Android
mysql.n8m.medium.2c 是通用型，与其他用户共享 CPU 磁盘资源，不保证最大 iops
阿里云 RDS 自带基础硬件性能监控，查询性能监控和查询优化建议。 |
 |
10
owen800q 2023 年 10 月 29 日
差不多吧，建議換 mongo
|
 |
11
bthulu 2023 年 10 月 29 日
mysql 的全表 count 就是这么慢的, 你要么换 sql server 或者 oracle, 表 count 瞬间返回
|
 |
12
akira 2023 年 10 月 29 日
不正常， explain 看一眼呢
|
 |
13
me1onsoda 2023 年 10 月 29 日
说个题外话，这个配置合理吗？我 4 核 8g ，只能只吃 60%巍然不动，cpu 经常打满
|
 |
14
Bingchunmoli 2023 年 10 月 29 日 via Android
大概率是公网调用等延迟了
|
 |
15
wellsc 2023 年 10 月 29 日
别用 count ，找个外部存储存计数
|
 |
16
dV9zZM1wROuBT16X 2023 年 10 月 29 日
缺少数据库参数，innodb_buffer_pool_size 是多少
|
 |
17
Itesting 2023 年 10 月 29 日
你这全表扫描了，count 就这么慢，加配置 or 加 where 条件吧
|
 |
18
ahopunk 2023 年 10 月 29 日
同阿里云 mysql 实例，这个速度是正常的。
不过楼主 2 核 16G 内存的配置有点不理解，我 4 核 8G 的实例，跑起来内存和 cpu 占用比较和谐。 |
 |
19
fd9xr 2023 年 10 月 29 日 via iPhone
无语…才四千万你优化它干毛
|
 |
20
Features 2023 年 10 月 29 日
啊？还有人的 slow_launch_time 值大于 1 吗？
楼上说 count 就是这么慢认真的吗？ |
 |
21
huigeer 2023 年 10 月 29 日 via iPhone
这种场景需要用 mysql 嘛😄
|
 |
22
NickX 2023 年 10 月 29 日
看看 cpu 占用情况，感觉是服务器的问题。
|
 |
23
cleveryun OP @winglight2016 你们用的配置大概是如何，可以参考下吗，不知道该升级到什么配置。这个配置 limit 1000 的话用时在 2 秒左右。
|
|
24
cleveryun OP @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" } ] |
 |
25
dimingchan 2023 年 10 月 29 日
肯定是用了默认的 InnoDB 存储引擎，InnoDB 的 count 是需要全表扫描的，如果不需要用到事务，建议换成 myisam 存储引擎，元数据直接记录表的记录数的；另外，不要 count(1)，count(pm_id)，count("主键")是最快的了。
|
|
26
cleveryun OP @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 。 |
 |
28
mahone3297 2023 年 10 月 29 日
@cleveryun 看你的 explain 结果，372w 行的数据，你的配置也不高，2c ，这个结果就这样，我认为合理吧
你应该考虑的事，这些数据不从 mysql 查，从 redis 查。每次都基本上全表扫描，当然就是这结果了。 ps：4kw 完全不需要分表吧。mysql 完全可以上亿。不过也要看单行数据量。 |
 |
29
ohxiaobai 2023 年 10 月 29 日
这个 count 语句相等于扫描全表了，慢是正常的。
1. MySQL 单表数量上限很高，这种数据量级不算大。 2. 建议根据具体场景优化，比如针对这个 SQL ，如果不考虑删除，那么可以 count 1 次，然后用缓存计数，后面就不用 count 了；或者加一个自增 id 字段，设置从当前 count 之后开始自增，这样只需要记录新增后的自增 id 值就行了。 |
 |
30
happy32199 2023 年 10 月 30 日 via iPhone
换 32 核 64g 内存的 ecs ，自己装 mysql ，保证又快又省钱……而且 10 亿前不用分表
|
 |
31
lxy42 2023 年 10 月 30 日
从 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 自带的分区表 |
 |
34
chunworkhard 2023 年 10 月 30 日
count 不加条件 单表 500W 按理说正常也得 4-5s 吧
|
 |
36
ZX16815 2023 年 10 月 30 日
排查一下你的网络，如果没问题的话就提个工单问问吧。
|
 |
37
encro 2023 年 10 月 30 日
不要问了，count 就是慢。。。。。。
|
|
38
encro 2023 年 10 月 30 日
你这 4000 万数据不用分表，直接查询，count 一定要代条件，建议为时间建立索引，只 count 最近几天的，就能快非常多。
|
 |
39
xlzyxxn 2023 年 10 月 30 日
先说结论：1 、count(*)=count(1)>count(主键字段)>count(字段)
2 、对大表使用 count 是不好的 楼主这张表创建了二级索引，所以 count(*)会使用这个二级索引，从 explain 的结果可以看出来符合 优化：1 、使用近似值，show table status 或者 explain 命令来表进行估算，explain 是很快的，rows 字段值就是估算出来的 2 、如#15 楼所说，将具体计数保存在另外一张表中 |
 |
40
coderzhangsan 2023 年 10 月 30 日
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 |
 |
41
opengps 2023 年 10 月 30 日
mysql 的 count 好像本身就慢
|
 |
42
28Sv0ngQfIE7Yloe 2023 年 10 月 30 日
感觉楼上没说到点子上吧，你直接用阿里的 DMS 平台查下
|
 |
43
noparking188 2023 年 10 月 30 日
你 DataGrip 确定没问题嘛，你直接 mycli 命令行连上去 count 看看时间
|
 |
44
realNewBee 2023 年 10 月 30 日
正常，这个数据量这个配置，count 就是慢。所以，如果非得查询全表的数据，不走条件的情况下，必须得根据实际需求来换种查法
|
 |
45
justfindu 2023 年 10 月 30 日
count(*) 试一试, 不要 1 .
|
 |
46
sleepyfevniv 2023 年 10 月 30 日
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 比你的稍微慢一点点。从上海访问成都。 |
|
47
sleepyfevniv 2023 年 10 月 30 日
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 这个快不少 |
 |
48
9y7cz863P00C7Lie 2023 年 10 月 30 日
MySQL 从 8.0.17 开始对于无条件的 count(*)会强制走主键，即使执行计划里面写的是走二级索引，这是因为因为在这个版本更新了并行扫描主键的功能。由于你的机器 CPU 配置不高，肯定跑不出好的效果。所以从结果上来说就比 5.7 的扫描二级索引要慢
|
 |
49
yh7gdiaYW 2023 年 10 月 30 日
mysql 做这个量级的统计查询时就是很慢（当然你这个有点不正常），我们当初因为这个换了 MongoDB ，现在准备换到 StarRocks （这玩意儿速度太逆天了）
|
 |
50
iyaozhen 2023 年 10 月 30 日
count(*)=count(1)>count(主键字段)>count(字段) 这个不认同，可以让楼主试下，count 啥都是慢
按之前经验来看，就是慢，不要不加 where 条件的 count 。用前面大家说的估算方法 带上关键词查询的 sql 不得更慢了，NO 。你 where 命中索引是很快的，几百万一张表几乎不需要啥优化。 |
 |
51
MoYi123 2023 年 10 月 30 日
mysql 的 count 就是这样的, 具体原因和事务隔离有关系;
|
 |
52
tangyiyong 2023 年 10 月 30 日
会不会是因为索引建在 varchar(255)上的问题？文本的字段一般不能做索引吧？
|
|
53
tangyiyong 2023 年 10 月 30 日
journal_pub_year varchar(255) null,
create index `pubmed-article-0_journal_pub_year` on `bio-hub`.`pubmed-article-0` (journal_pub_year); 索引里允许 null ？ |
 |
54
f9EHtxE2UtX3fG3 2023 年 10 月 30 日 via Android
这种是不是得阿里云监控看看有什么指标达到瓶颈了吗？感觉也太慢了
|
 |
55
sivacohan 2023 年 10 月 31 日
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 性能看看 |
|
56
cleveryun OP 大佬们求救，最新的情况我 Append 追加了。
|
|
57
cleveryun OP 字段长度我都改成实际数据中各字段实际最长的长度值了
|
|
59
cleveryun OP @tangyiyong 已修改，年月都改成数字重新建了索引
|
|
63
yh7gdiaYW 2023 年 11 月 9 日
@cleveryun 比 MySQL 速度快很多，主要是查询耗时不会随数据规模非线性增长。但用于数据分析的话，MongoDB 和 starrocks 比有数量级上的差距，10 倍速度、1/10 空间占用，我调研完都惊了
|
|
64
tangyiyong 2023 年 11 月 10 日
MATCH() AGAINST()会使用全文索引，journal_pub_year > 2022 的条件会尝试使用 B-tree 索引；通常不会在一个查询中组合使用全文索引和 B-tree 索引，可能是索引问题吧？
|
› MySQL 5.5 Community Server
› MySQL 5.6 Community Server
› Percona Configuration Wizard
› XtraBackup 搭建主从复制
Great Sites on MySQL
› Percona
› MySQL Performance Blog
› Severalnines
推荐管理工具
› Sequel Pro
› phpMyAdmin
推荐书目
› MySQL Cookbook
MySQL 相关项目
› MariaDB
› Drizzle
参考文档
› http://mysql-python.sourceforge.net/MySQLdb.html
MySQL 相关项目
› MariaDB
› Drizzle
参考文档
› http://mysql-python.sourceforge.net/MySQLdb.html