在一个表中管理70亿条记录- MYSQL

06odsfpq 于 2023-01-08 发布在 Mysql

关注(0)|答案(3)|浏览(101)

我需要在Mysql表中保存大约78亿条记录。该表是读写密集型的。我必须保持每小时至少0.2亿条记录的插入率。而在表上搜索不应该超过10秒。我们有一个用户界面，用户可以从那里根据不同的列属性进行搜索。
大多数搜索查询可能类似于：
1.第一个月

select * from mytable where prop1='sip:+100008521149' order by event_timestamp desc limit 10;
select * from mytable where prop2='asdsa' order by event_timestamp desc limit 10;
当前表上有2个索引：

1- idx_1(prop1,event_timestamp)
2- idx_2(prop2,event_timestamp)

InnoDB设置如下：
x一个一个一个一个x一个一个二个x
系统启动后速度很快，但当记录达到2.2亿时性能下降很多。虽然我们使用了LOAD INFILE，但插入速度非常慢。在索引参数上搜索时搜索速度非常快。看起来缓冲池不够。
我有几个问题：
1.这种配置是否可以支持这种类型的数据？

70亿条记录的理想和实用的缓冲池大小应该是多少。
1.数据+索引大小接近150 GB，只有0.22亿条记录。看起来我需要有TB的内存。
1.我们正在考虑主/从配置，以使用于读取和写入的congif在各自的服务器上占主导地位。
1.还有其他更好的方法来设计此解决方案吗？
1.增加更多的索引会使UI搜索更好，但是增加单个索引会使插入速度降低很多倍。

- 最新版本：1**

表比RAM大很多，对吗？buffer_pool不能做得足够大--它必须比RAM小，否则性能会受到影响。
A-RAM大小为100 GB，缓冲池为70 G。是的，数据大小大于RAM。
Q-请提供显示创建表;有几个问题我需要研究。2（数据类型，字段大小等）
所有的字段都是字符串类型。我们使用了varchar（127）。PK是自动生成的id bigint（20）。
LOAD DATA INFILE中有多少条记录？是否直接装入表中？装入的频率是多少？
A-每个文件100000条记录。多个线程将数据从CSV文件加载到DB。在初始迁移中，我们必须连续加载，直到6.5亿条记录。之后，频率将降低到每15分钟左右。
Q-主机+从机：请记住，所有写入操作也是在从机上执行的。如果您有大量读取操作，那么多个从机将分散读取操作，从而获得一定的扩展。
A-我们目前正在使用主/从方法进行测试。
我们用MYISAM创建了MASTER，没有索引。MASTER将用于插入。SLAVE有INNODB，有2个索引。搜索将在SLAVE上执行。两者是不同的机器，不共享RAM或CPU。应用程序在第三台机器上。
问：你有旋转驱动器吗？或固态硬盘？答：如何检查它？
你的行看起来很大。有文本或BLOB吗？如果有，SELECT * 可能会是一个严重的性能负担。
行有50列，但数据在15 - 20列。我们不能减少数据类型的大小，因为所有字段可以容纳任意数量的字母数字数据。所有都是文本，没有BLOB。

mysql

来源：https://stackoverflow.com/questions/38237996/manage-7-billion-records-in-a-table-mysql

3条答案

按热度按时间

92dk7w1h1#

关闭查询缓存：每次发生INSERT时，它必须清除QC中的所有条目--即每秒5555次！

query_cache_type = 0
query_cache_size = 0

第一个查询需要INDEX(prop1, prop2, event_timestamp)（prop 1和prop 2可以交换）。
有了这个新增的索引，这三个查询中的每一个查询都将接触索引中不超过10行，并且对数据执行不超过10次随机（？）提取。最坏的情况下，这仅仅是大约11次磁盘命中。而@Bernd的“懒惰eval”也不会让它变得更好。
这个表比RAM大得多，对吗？buffer_pool不能做得足够大--它必须小于RAM，否则性能会受到影响。
请提供SHOW CREATE TABLE;有几个问题我需要研究。2（数据类型，字段大小等）
LOAD DATA INFILE中有多少条记录？你直接把LOAD放入表中吗？LOAD的频率是多少？
主机+从机：请记住，所有写入操作也是在从机上执行的。如果您有大量读取操作，那么多个从机会分散 * 读取 *，从而获得一定的扩展。
你有旋转驱动器吗？或者固态硬盘？
您的行看起来很大。是否存在TEXTs或BLOBs？如果是，SELECT *可能是一个严重的性能负担。

赞(0）回复(0）举报 2023-01-08

46qrfjad2#

我通过用Elasticsearch替换MYSQL DB达到了这个要求。它看起来非常适合快速插入率和该死的快速搜索。此外，Lucene的全文功能使它成为一个完美的工具。ES最好的部分是它对硬件的要求非常低。它可以水平扩展而不是垂直扩展。

赞(0）回复(0）举报 2023-01-08

lymgl2op3#

这不是答案，但我不能在评论格式
你可以试试这个，看看它是否更快。所以MySQL必须不排序孔行，只有id（主键）

SELECT r.*
FROM (
  SELECT id
  FROM mytable
  WHERE 
      prop1='sip:+100008521149'
    AND
      prop2='asdsa'
  ORDER BY event_timestamp DESC
  LIMIT 10
) AS r
LEFT JOIN mytable m ON m.id =r.id
ORDER BY r.event_timestamp DESC;

赞(0）回复(0）举报 2023-01-08

我来回答

在一个表中管理70亿条记录- MYSQL

3条答案

相关问题

热门标签

最新问答