MySQL优化INSERT速度由于索引而减慢

  • Z时代
  • 2024-01-10
  • 分类：问答

MySQL文档说：

假设B树索引，表的大小会减慢日志N的索引插入速度。

这是否意味着对于每个新行的插入，插入速度将降低log N倍，其中N是行数？即使我只在一个查询中插入所有行？即：

INSERT INTO mytable VALUES (1,1,1), (2,2,2),  (3,3,3), .... ,(n,n,n)

其中n为〜70,000

我目前在一个表格中约有147万行，其结构如下：

CREATE TABLE mytable (
   `id` INT,
   `value` MEDIUMINT(5),
   `date` DATE,
   PRIMARY_KEY(`id`,`date`)
) ENGINE = InnoDB

当我以上述方式插入事务时，提交时间约为275秒。我该如何优化这一点，因为每天都会添加新数据，并且插入时间只会继续变慢。

此外，除了查询可能有什么帮助之外吗？也许一些配置设置？

可能的方法1-删除索引

我读到在插入之前删除索引可能有助于提高插入速度。在插入之后，我再次添加索引。但是这里唯一的索引是主键，我认为删除它不会有多大帮助。同样，当主键被 放下时

，所有的选择查询将变得缓慢。

我不知道任何其他可能的方法。

这是关于在表中插入约60,000行以及约147万行的一些测试：

146秒

145秒

60个文件（每个1000行）136秒，6个文件（每个10,000行）136秒

删除键花费了11秒，插入数据花费了0.8秒，但是插入 花费了153秒，总共花费了165秒

回答：

如果要快速插入，首先需要的是合适的硬件。假定有足够的RAM，一个SSD而不是机械驱动器以及相当强大的CPU。

由于您使用InnoDB，因此默认设置是针对慢速和老式计算机设计的，因此您需要对其进行优化。

这是有关配置InnoDB的精彩读物

在那之后，您需要了解一件事-那就是数据库在内部如何执行其工作，硬盘如何工作等等。我将在以下描述中简化该机制：

MySQL是一个事务，它等待硬盘驱动器确认它已写入数据。这就是为什么机械驱动器上的事务处理很慢的原因，它们每秒可以执行200-400次输入输出操作。转换后，这意味着您可以在机械驱动器上使用InnoDB每秒获得200ish插入查询。自然，

，只是为了概述正在发生的事情， 。

由于查询（尤其是与表大小相对应的查询）的字节数相对较小-您实际上在单个查询上浪费了宝贵的IOPS。

如果在单个事务中包装多个查询（100或200或更多，没有确切的数字，则必须测试），然后提交它-您将立即每秒获得更多写入。

Percona家伙正在相对便宜的硬件上实现每秒15k插入。即使每秒插入5k也不错。像您这样的表格很小，我已经在类似的表格上进行了测试（多了3列），并且使用带有240GB

SSD的16GB内存计算机（1个驱动器，没有RAID，用于测试目的）。

TL;

DR：-按照上面的链接，配置服务器，获取SSD，在1次交易中包装多次插入并获利。而且不要关闭索引然后再打开，这并不总是适用的，因为在某些时候，您将花费处理和IO时间来构建它们。

以上是 MySQL优化INSERT速度由于索引而减慢 的全部内容， 来源链接： utcz.com/qa/432053.html

回到顶部