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

买的数据库是阿里云的，配置信息：

数据库类型：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`;

cleveryun

251 天前

@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"
}
]

cleveryun

251 天前

@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 。

mahone3297

251 天前

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

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

ohxiaobai

251 天前

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

lxy42

250 天前

从 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 自带的分区表

xlzyxxn

250 天前

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

coderzhangsan

250 天前

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