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

[cleveryun]

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

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

[huigeer]

这种场景需要用 mysql 嘛😄

[NickX]

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

[cleveryun]

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

[cleveryun]

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

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

[cleveryun]

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

@huigeer 用什么比较合适呢

[mahone3297]

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

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

[ohxiaobai]

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

[happy32199]

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

[lxy42]

从 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]

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

[Rehtt]

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

[chunworkhard]

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

[ljsh093]

@mayli #8 sqlite3 性能不弱的

[ZX16815]

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

[encro]

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

[encro]

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

[xlzyxxn]

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

[coderzhangsan]

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