加速大型数据库查询

Question

我正在使用MySQL和InnoDB运行一个大型本地数据库。数据大小约为30 6GB，密钥为6GB。这台机器有16 of内存。

这张桌子看起来像这样

symbol VARCHAR(10),
iso_datestamp VARCHAR(14),
-- a lot more columns with values with mostly DECIMAL(26,6)s

我在符号和iso_datestamp上有BTREE索引。

我运行的操作主要是这样运行的更新：

1. DELETE FROM table WHERE symbol = 'XXX'
2. INSERT INTO table (columns) VALUES (...)

需要这样做，以避免数据不一致，而不仅仅是附加数据。

我还经常运行分析，比如在SELECT FROM table WHERE symbol in ('XXX', 'YYY', ...) [AND iso_datestamp > '2015...']中。

每一个符号我有大约3000条条目。

性能问题:一旦表开始增长超过10 to，DELETE和INSERT语句的速度就会大大降低，从通常不到0.4秒降到1秒以上，甚至是5秒甚至更长，因为表不断增长。RAM没有受到限制(根据top)。

我需要DELETE和INSERT的总数平均在1秒左右(当然更快也可以).

我现在有两个想法：

• 使用散列索引而不是btree索引，因为我只执行相等的操作。
• 使用符号和范围划分表

这些是可行的策略吗？两者都做，就一个？其他我应该做的事情来提高速度吗？

Answer 1

• iso_datestamp到MySQL TIMESTAMP，以便它可以是5个字节而不是15个字节。
• 使用InnoDB，而不是MyISAM。(这对下面的一些项目至关重要。)
• 除非需要多字节utf8 8/utf8mb4，否则使用CHARACTER SET ascii (或latin1)。
• PRIMARY KEY(symbol, datestamp)
• auto_increment是对空间和索引的浪费。以上是一个“自然PK”，它提供了一个有效的索引。
• PARTITION BY RANGE(TO_DAYS(datestamp)) --这将大大加快删除“旧”行的速度。
• 使用该PK和分区，您可能不需要任何辅助索引，从而完全消除了您提到的6GB。(如果您的查询没有得到充分处理，请告诉我。)
• 拍摄不超过50个分区，不要麻烦的子分区。(见下文链接)
• 使用DROP PARTITION非常有效地删除by date。(见下面的链接)如果你要删除符号，请告诉我。
• DECIMAL(26,6)需要12个字节。考虑缩小它和/或移动到一个8字节的DOUBLE。注意: Double's ~16位数和二进制格式可能导致舍入问题。
• 考虑对一个MEDIUMINT INT使用一个3字节的symbol_id，并将实际的symbol名称规范化为另一个(小)表。
• 度量标准(例如，"alpha")应该是4字节的FLOATs。对于这样的人来说，7位重要的数字太过分了。
• 将innodb_buffer_pool_size设置为大约70%的可用内存(包括应用程序、web服务器等)
• 以100-1000的批次插入新数据。这实际上是一次一行速度的10倍。

这些更改将有助于您解决各种问题。

目前，由于多种原因，性能正在从悬崖上滑落。以上提示将使悬崖延迟约两倍的时间，并使悬崖不那么陡峭。在某些情况下，悬崖被消灭了。

此外，我的技巧还将减少所需的I/O --通过集群、引用的局部性、更好的缓存等等。

http://mysql.rjweb.org/doc.php/partitionmaint

Answer 2

删除，序列化非常糟糕。您可以通过使用符号的散列对表进行分区，从而减轻用例的影响。这将使您能够在这些删除上实现更好的并发性。

对于插入，可能有用的内容是：

1. 将事务隔离级别设置为读提交。
2. 确保您有足够的(innodb_log_file_size X innodb_log_files_in_group)。这些应该成倍增加到你的写作高峰时间所需的数量。
3. 根据表中是否有auto_increment列，innodb_autoinc_lock_mode=2在高并发性方面将有所帮助。
4. 确保您的innodb_io_capacity_max设置为存储堆栈可以提供的16 is。

将您的MySQL数据目录放在优化调优的ZFS将提高您的写入性能.上。

此外，确保您的innodb_buffer_pool_size设置适当(经验法则是50-80%的内存，取决于您有多少内存)。您最热的索引和主键应该适合RAM。在您的例子中，如果是一个专用的数据库服务器，那么12 is似乎是一个合理的数量。