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