对大型 MySQL 表使用分区
我尝试在 MacBook Pro 上的 MySQL 上实现 505,000,000 行表的更新： 按照给出的建议，我对表 tr: 进行了分区，
i UNSIGNED INT NOT NULL,
j UNSIGNED INT NOT NULL,
A FLOAT(12,8) NOT NULL,
nu BIGINT NOT NULL,
KEY (nu), key (A)
范围为 nu。 nu 应该是一个实数，但因为我只有 6-dp 精度，而 nu 的最大值是 30000。我将它乘以 10^8 使它成为一个 BIGINT - 我认为不能使用 FLOAT 或 DOUBLE 值对 MySQL 表进行分区。无论如何，我有 15 个分区（p0: nu<25,000,000,000，p1: nu<50,000,000,000 等）。 我认为这应该加速典型的 SELECT:
SELECT * FROM tr WHERE nu>95000000000 AND nu<100000000000 AND A.>1.
到仅包含相关分区中的数据的表上的相同查询的顺序（<30 秒）。但是，需要 30 分钟以上才能返回分区内查询的行，如果查询跨越两个（连续）分区的行，则需要加倍时间。 我意识到我可以只有 15 个不同的表，并分别查询它们，但是有没有办法通过分区“自动”执行此操作？有人有什么建议吗？
An update on my attempts to implement a 505,000,000-row table on MySQL on my MacBook Pro:
Following the advice given, I have partitioned my table, tr:
i UNSIGNED INT NOT NULL,
j UNSIGNED INT NOT NULL,
A FLOAT(12,8) NOT NULL,
nu BIGINT NOT NULL,
KEY (nu), key (A)
with a range on nu. nu ought to be a real number, but because I only have 6-d.p. accuracy and the maximum value of nu is 30000. I multiplied it by 10^8 made it a BIGINT - I gather one can't use FLOAT or DOUBLE values to PARTITION a MySQL table. Anyway, I have 15 partitions (p0: nu<25,000,000,000, p1: nu<50,000,000,000, etc.).
I was thinking that this should speed up a typical to SELECT:
SELECT * FROM tr WHERE nu>95000000000 AND nu<100000000000 AND A.>1.
to something of the order of the same query on a table consisting of only the data in the relevant partition (<30 secs). But it's taking 30mins+ to return rows for queries within a partition and double that if the query is for rows spanning two (contiguous) partitions.
I realise I could just have 15 different tables, and query them separately, but is there a way to do this 'automatically' with partitions? Has anyone got any suggestions?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论