带有插入的大型表上的 MySQL 性能问题 - mysql performance issues on large tables with insert

我们有一个带有8GB RAM的专用服务器和带有MySQL 5.1的PHP5.3

最多有大约 500 个并发连接，每个连接使用用户数据对较小的表执行 1-2 次 SELECT 查询，然后在大表transactions中插入。选择查询不需要太多时间，我们在每个查询之间添加了监视，以查看每个查询的响应时间，并且从未出现问题。

我们在代码中添加了跟踪，有时会导致一些简单的 INSERT 查询需要 14-15 秒。下面列出的此查询有时需要 14 秒，有时需要 6 秒，有时需要 0.2 秒或更短。可能是什么问题？

有时会返回这些巨大延迟的 PHP 代码：

$starT = microtime(true);
echo '&timestampTS_02='.(microtime(true) - $startT);
mysqli_query($GLOBALS['con'],"INSERT INTO `transactions` (`id`,`data`) VALUES('id','some_data')") or die(mysqli_error($GLOBALS['con']));
echo '&timestampTS_03='.(microtime(true) - $startT);

到目前为止，transactions表有大约 200 万个条目。

CREATE TABLE IF NOT EXISTS `transactions` (
  `id` int(11) NOT NULL,
  `data` varchar(1000) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

InnoDB. （因为下面的一些东西需要它。

批量插入。 Either by INSERT ... VALUES (1,2,3), (4,5,6), ...或与LOAD DATA. （一次插入 100 行时性能提升 10 倍。如果无法"批处理"，请参阅下面的"临时表"建议。

innodb_flush_log_at_trx_commit=2（不是默认值 1）（这可能是您的测试未能显示良好性能的原因。

innodb_buffer_pool_size = 可用内存的 70%。（在您的 8GB 机器中约为 5000M）。

从 5.1 升级。（但这并不能单独解决问题。

如果可能，请减少 transactions 上的索引数。

对于非常高的摄取速率，乒乓球两个"暂存表"，如高速摄取中所述。

您的表可能已被锁定，无法进行其他进程。这可能是硬件问题（硬盘速度慢或寿命过长）、软件或推测（共享系统中的服务器速度慢）。

您可以观察是什么用show processlist锁定刀片

在任何数据库中（无论引擎如何），最高的成本始终是写入数据，因为您增加了强制磁盘操作的开销（具有足够的 RAM，MySQL 服务器可以很好地缓存表和查询，以避免SELECT的高 I/O）。MyISAM（带表锁定）与InnoDB（带行锁定）相比可能没有帮助，但你不一定必须切换存储引擎。

关于多个插入的一个主要提示是使用预准备语句。设置查询一次，然后更改参数并每行执行一次。正如MySQL手册中规定的速度（强调我的）

插入行所需的时间由以下因素决定，其中数字表示近似比例：
连接：（3）
向服务器发送查询：（2）
解析查询：（2）
插入行：（1 ×行大小）
插入索引：（1 ×索引数）
结语：（1）

由于预准备语句仅发送和解析一次（上面加粗），因此可以节省相当多的开销，并保护数据插入。

另一种选择是启用并发插入。仅当您不从该表中删除数据时，此选项才是一个选项。

最后一个对您来说可能不是很可行，但如果这是您业务的主要痛点，则应该考虑这一点。现代云计算大大降低了更高 I/O 选项（RAID、SSD 等）的成本。您可能希望切换到具有高 I/O 选项的云服务（AWS、Azure 等）。您可以在数据库中投入任意数量的 RAM，但如果存储速度很慢，这只会拖累数据库的其余部分。

首先检查transactions表是否被引用为外键的持有者。

然后运行show triggers并检查是否有任何触发器设置为在transactions或任何引用表中发生更改时运行。

然后调整找到的任何触发器。

如果这没有帮助，请返回到添加到代码中的跟踪，并添加以下内容：在运行查询之前，检查当时哪些查询已在运行并记录。您可能会发现一些正在运行的慢查询正在锁定您的表。

你应该

使用引擎InnoDB而不是MyISAM 您可以通过此站点配置MySQL配置：
www.percona.com

注意：innodb-buffer-pool在你的配置中非常重要

在大数据上使用mysqli，它具有多插入语句功能。

并且在专用服务器中更可取。

请参阅链接 - http://www.w3schools.com/php/php_mysql_insert_multiple.asp

问候