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

[drymonfidelia]

[wuzhi1234]

加缓存？

[j1132888093]

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

[ruiyinjinqu]

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

[pandaidea]

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

[drymonfidelia]

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

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

[drymonfidelia]

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

[drymonfidelia]

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

[crazyweeds]

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

[cabing]

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

[liprais]

你看你索引用对了么

[lidong88]

试试 分布式数据库

[dollck]

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

[xiebruce]

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

[me1onsoda]

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

[lambdaq]

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

[june4]

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

[sujin190]

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

[yufeng0681]

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

[IwfWcf]

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