MySQL 两亿条数据的表用索引也要 1 秒才能查出结果，还有办法优化吗？

[drymonfidelia]

[BugCry]

同单表 2 亿，用分区表性能爆炸

[drymonfidelia]

@crazyweeds 试了分区，看起来提升不是很大

[drymonfidelia]

@IwfWcf 是阿里云的系统盘，应该是 SSD
@sujin190 阿里云的实例，内存 32GB

[yidinghe]

优化思路大概是两个方面：

1. 尽可能减少扫描记录数。索引和分区就是起这个作用的。
2. 尽可能减少选取字段数。首先可以的话只选索引包含的字段，其次避免选取 text 等外部引用的字段。
3. 极端情况下，可以在先带业务条件查出记录的主键列表后，做二次查询来填充其余字段。这个在分页查询中比较有用，因为第二次查询可以并发执行，所以有可能总耗时反而更少。

[fallingg]

有排除网络因素吗？如果执行 select 1 时间是多少

[RangerWolf]

建议试试看分两步查询
1. 第一步先 Select record_id
2. 第二步再试试看 select x.* from xxx qcs.records x where record _id in ( ... )

上面有同学已经提到类似的改进了

[vibbow]

@drymonfidelia ECS 的话就别纠结了
直接上物理机+nvme ，性能直接翻 N 倍

[sujin190]

@drymonfidelia 那给 mysql 分了多少？理论上 innodb buffer 给超过 16g 了吧，btree 索引覆盖的情况下应该性能可以的吧

[rs9G7IrdOdiNR3h1]

如果只是需要最新的一条 type ，为什么不考虑缓存最新的一条呢？

[iseki]

你试试相同条件执行两次，是不是第二次就很快🤣

[wakaka]

着急的话先升级硬件，加内存，换更快的 SSD 。然后再考虑其他的。

[hefish]

也许可以删掉点数据，剩下 10000000 条，那应该就快了。

[drymonfidelia]

@whooami 因为每次的 query 都不一样，查完一次可能几年后才会再查相同条件
@iseki 没有变快多少
@hefish 一条都不能删

[EminemW]

是不是 in 这个条件导致的，改成 union 试试。另外看看索引有没有用对，强制指定索引，看哪个索引比较快

[drymonfidelia]

@EminemW 昨天试过了改成=都不行

[lujiaxing]

首先你需要看下数据库物理文件是存放在什么地方的. 是不是 NVME 盘.
其次看下设置中 INNODB_BUFFER 设置的多少. 越多越好.
还有发一下 mysql 版本.

个人认为, MYSQL 的能力并不足以支撑单表 2GB 的数据量. 如果你确实有如此巨大规模的数据量, 建议换 MSSQLServer 或者 Oracle. 别想着什么加什么 ClickHouse, Doris...

相比招一些一年要付 20W 年薪的开发比, 买一个数据库产品授权成本并不算高.

[lujiaxing]

innodb_buffer_pool

[msg7086]

试试把 data 字段拆出去呢？

[zw5473547]

已经用上 SSD 的话，就采用分表和主从库分开，索引再优化优化。十几年前 discuz 的 posts 表 3.2 亿条数据这么处理都大负载下没问题的。

[igeeky]

可以试试联合索引(query, type) 并且使用 hash 索引.
hash 索引不支持范围查询, 但是索引体积更小, 并且查询效率也更高.