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

表用

MySQL

索引

查出

44 replies • 2024-04-10 19:42:53 +08:00

wuzhi1234

Feb 13, 2024 via iPhone

1

加缓存？

j1132888093

Feb 13, 2024

1

2

贴表结构索引结构查询语句 explain 结果

ruiyinjinqu

Feb 13, 2024

3

不行就分表吧，虽然会增加复杂性，但是速度会大大提升

pandaidea

Feb 13, 2024 via iPhone

4

- 硬盘升级，换 io 更快的
- MySQL 同步到 OLAP 型数据库

drymonfidelia

OP

Feb 13, 2024

5

@j1132888093
Column Name # Data Type Not Null Auto Increment Key Default Extra Expression Comment
record_id 1 int(11) true true PRI [NULL] auto_increment
query 2 varchar(20) false false MUL [NULL]
type 3 int(11) false false MUL [NULL]
data 4 text false false [NULL] [NULL]
created_at 5 datetime false false [NULL] CURRENT_TIMESTAMP

Index Name Column Table Index Type Ascending Nullable Unique Extra Cardinality Comment
PRIMARY recorde_id records BTree [NULL] [NULL] true [NULL] 218924181
record_id_IDX record_id records BTree [NULL] [NULL] false [NULL] 219268402
record_query_IDX query records BTree [NULL] [NULL] false [NULL] 193121862
record_query_IDX2 query, type record BTree [NULL] [NULL] false [NULL] 211496542
record_type_IDX type records BTree [NULL] [NULL] false [NULL] 15732

explain select x.* FROM qcs.records x WHERE `query` = "DEMOQUERY1111" AND `type` in (1,2,4,7,2510,27442,440097,800022);

|id |select_type|table|partitions|type |possible_keys |key |key_len|ref|rows|filtered|Extra |
|---|-----------|-----|----------|-----|--------------------------------------------------|------------------------|-------|---|----|--------|---------------------|
|1 |SIMPLE |x | |range|record_query_IDX,record_type_IDX,record_query_IDX2|record_query_IDX2 |68 | |2 |100 |Using index condition|

drymonfidelia

OP

Feb 13, 2024

6

@drymonfidelia 每个 query 对应 1~10 条数据，只需要按 type 优先级查出一条数据，如果同 type 有超过一条则需要最新的一条，之前的做法是一个个 type 执行查询，直到查到一条返回，优化成了现在这种，还是很慢，有的查询最慢要十几秒

drymonfidelia

OP

Feb 13, 2024

7

@drymonfidelia 因为优先级高的 type 有数据的概率更大，综合看现在这种查法有的查询比以前更慢了

drymonfidelia

OP

Feb 13, 2024

8

每个 query 在哪几个 type 有可能有数据能在查询前知道，所以不需要查全部的 type ，每次需要查询的 type 在 1~7 个之间（ 90%以上的情况在 1~4 个），部分 type 有数据的概率更大，需要平均总查询时间、最大总查询时间尽可能短

crazyweeds

Feb 13, 2024

9

一个字：拆。如果嫌麻烦，先试试看分区表？数据库层面就能很快测试。如果试了，麻烦给个反馈。

cabing

Feb 13, 2024

10

如果服务只是查询的话，可以试试 clickhouse 或者 doris 。

F281M6Dh8DXpD1g2

Feb 13, 2024 via iPhone

11

你看你索引用对了么

lidong88

Feb 13, 2024 via Android

12

试试分布式数据库

edwintuan

Feb 13, 2024

13

如果实时性要求不高，换 clickhouse 吧

xiebruce

Feb 13, 2024

14

你是指单表吗？如果是单表 2 亿条也太多了吧，要分表，当然也可以试试分区

me1onsoda

Feb 13, 2024

15

性能瓶颈了吧，总共才扫了 68 行，这还慢的话，那就是回表查询了吧

tf2

Feb 13, 2024

16

先试试把 select x.* FROM 改成 select `query`, `type` FROM 。感觉是单条记录比较大 (data text 可能有 65535 字节长)读盘慢了。

june4

Feb 13, 2024

17

性能明显不正常。这表是不是已经用了 mysql 的分区功能，区区 2 亿数据完全不用分区，分得不好性能反而大降。

sujin190

Feb 13, 2024 via Android

18

mysql 机器内存多少啊？如果索引不能充分放到内存里性能也不行，所以索引用不到的就别留了，query 字段离散度高的话可以考虑索引只建前部分字符可以减小索引大小，除了考虑索引扫描行数索引相对内存大小也是要考虑的

yufeng0681

Feb 13, 2024

19

看你的描述，你单列索引和多列索引都建立了。
1 、sql 语句可以使用强制索引试试，走多列索引，看看是否速度能提升
2 、如果这个表，query 字段肯定会查，而且还有多条数据；我觉得保留多列索引就可以了，还能省了单列字段索引占用的空间。

IwfWcf

Feb 13, 2024

20

硬盘性能怎么样？是 ssd 吗？
索引数据是否因为随机插入的因素在物理存储上并不是顺序的？如果是的话试下 optimize table 后再试下

BugCry

Feb 13, 2024 via Android

21

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

drymonfidelia

OP

Feb 13, 2024

22

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

drymonfidelia

OP

Feb 13, 2024

23

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

yidinghe

PRO

Feb 13, 2024

24

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

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

fallingg

Feb 14, 2024

25

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

RangerWolf

Feb 14, 2024

26

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

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

vibbow

Feb 14, 2024

27

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

sujin190

Feb 14, 2024 via Android

28

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

rs9G7IrdOdiNR3h1

Feb 14, 2024

29

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

iseki

Feb 14, 2024 via Android

30

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

wakaka

Feb 14, 2024

31

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

hefish

Feb 14, 2024

32

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

drymonfidelia

OP

Feb 14, 2024

33

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

EminemW

Feb 14, 2024 via iPad

34

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

drymonfidelia

OP

Feb 14, 2024

35

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

lujiaxing

Feb 14, 2024

1

36

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

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

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

lujiaxing

Feb 14, 2024

37

innodb_buffer_pool

msg7086

Feb 15, 2024

38

试试把 data 字段拆出去呢？

zw5473547

Feb 15, 2024

39

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

igeeky

Feb 15, 2024

40

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

orczhou

Feb 19, 2024

41

@drymonfidelia 索引使用看起来没什么问题。考虑增加 MySQL/InnoDB 的内存，也就是调大参数 innodb_buffer_pool ，然后把所有的数据查一遍加载到内存（ warm up ），再试试。内存充足，性能应该在几毫秒以内

dyv9

Mar 20, 2024 via Android

42

@drymonfidelia 那我猜想按年分表，反 t 正都不用，何必一直占着坑

wxf666

Apr 10, 2024

1

43

@drymonfidelia 你 type IN (...) 少的时候，是不是能快些？

若是，我猜是 MySQL 去匹配不同范围的次数太多了。

如果你要的数据，都比较集中在最新添加的几天内，那么只使用一个范围，应该能提速很多。

假设你的 record_id 越大，created_at 也越大。

则可以：select record_id FROM qcs.records x WHERE `query` = "DEMOQUERY1111" ORDER BY record_id DESC;

这会使用你的 record_query_IDX 索引。

接着，你在程序内，一条条读取，直到 (1,2,4,7,2510,27442,440097,800022) 的 type 全部有数据为止。

最后，再根据得到的 record_id 集合，去主表拿整行数据。

wxf666

Apr 10, 2024

44

@drymonfidelia 噢，漏了一些东西。

你的 record_query_IDX 索引，没有 type 。。

那就建个 (query, record_id, type) 的索引？

然后 SQL 是 select record_id, type FROM qcs.records x WHERE `query` = "DEMOQUERY1111" ORDER BY record_id DESC;

后续参考上一楼。