1
wuzhi1234 316 天前 via iPhone
加缓存?
|
2
j1132888093 316 天前 1
贴表结构索引结构查询语句 explain 结果
|
3
ruiyinjinqu 316 天前
不行就分表吧,虽然会增加复杂性,但是速度会大大提升
|
4
pandaidea 316 天前 via iPhone
- 硬盘升级,换 io 更快的
- MySQL 同步到 OLAP 型数据库 |
5
drymonfidelia OP @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| |
6
drymonfidelia OP @drymonfidelia 每个 query 对应 1~10 条数据,只需要按 type 优先级查出一条数据,如果同 type 有超过一条则需要最新的一条,之前的做法是一个个 type 执行查询,直到查到一条返回,优化成了现在这种,还是很慢,有的查询最慢要十几秒
|
7
drymonfidelia OP @drymonfidelia 因为优先级高的 type 有数据的概率更大,综合看现在这种查法有的查询比以前更慢了
|
8
drymonfidelia OP 每个 query 在哪几个 type 有可能有数据能在查询前知道,所以不需要查全部的 type ,每次需要查询的 type 在 1~7 个之间( 90%以上的情况在 1~4 个),部分 type 有数据的概率更大,需要平均总查询时间、最大总查询时间尽可能短
|
9
crazyweeds 316 天前
一个字:拆。如果嫌麻烦,先试试看分区表?数据库层面就能很快测试。如果试了,麻烦给个反馈。
|
10
cabing 316 天前
如果服务只是查询的话,可以试试 clickhouse 或者 doris 。
|
11
liprais 316 天前 via iPhone
你看你索引用对了么
|
12
lidong88 316 天前 via Android
试试 分布式数据库
|
13
dollck 316 天前
如果实时性要求不高,换 clickhouse 吧
|
14
xiebruce 316 天前
你是指单表吗?如果是单表 2 亿条也太多了吧,要分表,当然也可以试试分区
|
15
me1onsoda 316 天前
性能瓶颈了吧,总共才扫了 68 行,这还慢的话,那就是回表查询了吧
|
16
lambdaq 316 天前
先试试把 select x.* FROM 改成 select `query`, `type` FROM 。感觉是单条记录比较大 (data text 可能有 65535 字节长)读盘慢了。
|
17
june4 316 天前
性能明显不正常。这表是不是已经用了 mysql 的分区功能,区区 2 亿数据完全不用分区,分得不好性能反而大降。
|
18
sujin190 316 天前 via Android
mysql 机器内存多少啊?如果索引不能充分放到内存里性能也不行,所以索引用不到的就别留了,query 字段离散度高的话可以考虑索引只建前部分字符可以减小索引大小,除了考虑索引扫描行数索引相对内存大小也是要考虑的
|
19
yufeng0681 315 天前
看你的描述,你单列索引和多列索引都建立了。
1 、sql 语句可以使用强制索引试试,走多列索引,看看是否速度能提升 2 、如果这个表,query 字段肯定会查,而且还有多条数据; 我觉得保留多列索引就可以了,还能省了单列字段索引占用的空间。 |
20
IwfWcf 315 天前
硬盘性能怎么样?是 ssd 吗?
索引数据是否因为随机插入的因素在物理存储上并不是顺序的?如果是的话试下 optimize table 后再试下 |
21
BugCry 315 天前 via Android
同单表 2 亿,用分区表性能爆炸
|
22
drymonfidelia OP @crazyweeds 试了分区,看起来提升不是很大
|
23
drymonfidelia OP |
24
yidinghe 315 天前
优化思路大概是两个方面:
1. 尽可能减少扫描记录数。索引和分区就是起这个作用的。 2. 尽可能减少选取字段数。首先可以的话只选索引包含的字段,其次避免选取 text 等外部引用的字段。 3. 极端情况下,可以在先带业务条件查出记录的主键列表后,做二次查询来填充其余字段。这个在分页查询中比较有用,因为第二次查询可以并发执行,所以有可能总耗时反而更少。 |
25
fallingg 315 天前
有排除网络因素吗?如果执行 select 1 时间是多少
|
26
RangerWolf 315 天前
建议试试看分两步查询
1. 第一步先 Select record_id 2. 第二步再试试看 select x.* from xxx qcs.records x where record _id in ( ... ) 上面有同学已经提到类似的改进了 |
27
vibbow 315 天前
@drymonfidelia ECS 的话就别纠结了
直接上物理机+nvme ,性能直接翻 N 倍 |
28
sujin190 315 天前 via Android
@drymonfidelia 那给 mysql 分了多少?理论上 innodb buffer 给超过 16g 了吧,btree 索引覆盖的情况下应该性能可以的吧
|
29
rs9G7IrdOdiNR3h1 315 天前
如果只是需要最新的一条 type ,为什么不考虑缓存最新的一条呢?
|
30
iseki 315 天前 via Android
你试试相同条件执行两次,是不是第二次就很快🤣
|
31
wakaka 315 天前
着急的话先升级硬件,加内存,换更快的 SSD 。然后再考虑其他的。
|
32
hefish 315 天前
也许可以删掉点数据,剩下 10000000 条,那应该就快了。
|
33
drymonfidelia OP |
34
EminemW 315 天前 via iPad
是不是 in 这个条件导致的,改成 union 试试。另外看看索引有没有用对,强制指定索引,看哪个索引比较快
|
35
drymonfidelia OP @EminemW 昨天试过了改成=都不行
|
36
lujiaxing 314 天前 1
首先你需要看下数据库物理文件是存放在什么地方的. 是不是 NVME 盘.
其次看下设置中 INNODB_BUFFER 设置的多少. 越多越好. 还有发一下 mysql 版本. 个人认为, MYSQL 的能力并不足以支撑单表 2GB 的数据量. 如果你确实有如此巨大规模的数据量, 建议换 MSSQLServer 或者 Oracle. 别想着什么加什么 ClickHouse, Doris... 相比招一些一年要付 20W 年薪的开发比, 买一个数据库产品授权成本并不算高. |
37
lujiaxing 314 天前
innodb_buffer_pool
|
38
msg7086 314 天前
试试把 data 字段拆出去呢?
|
39
zw5473547 314 天前
已经用上 SSD 的话,就采用分表和主从库分开,索引再优化优化。十几年前 discuz 的 posts 表 3.2 亿条数据这么处理都大负载下没问题的。
|
40
igeeky 314 天前
可以试试联合索引(query, type) 并且使用 hash 索引.
hash 索引不支持范围查询, 但是索引体积更小, 并且查询效率也更高. |
41
orczhou 310 天前
@drymonfidelia 索引使用看起来没什么问题。考虑增加 MySQL/InnoDB 的内存,也就是调大参数 innodb_buffer_pool ,然后把所有的数据查一遍加载到内存( warm up ),再试试。内存充足,性能应该在几毫秒以内
|
42
dyv9 280 天前 via Android
@drymonfidelia 那我猜想按年分表,反 t 正都不用,何必一直占着坑
|
43
wxf666 259 天前 1
@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 集合,去主表拿整行数据。 |
44
wxf666 259 天前
@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; 后续参考上一楼。 |