mysql - 非常慢的 MySQL 插入查询

Question

我有一张表，里面有大约一百万条记录。它并不大。几个 varchar(255) 字段、一些整数、一个 float 和几个时间戳。整数上有索引，也有外键约束。插入将花费永远。我说的是插入 一个 行需要 1-4 秒。我不得不多次处理缓慢的选择查询，但我一直试图弄清楚这个插入是怎么回事。

编辑:好的，我真的只是在询问如何调试它的想法，但是，这里是所有涉及的表。插入“成分”是永远需要的。希望将我的架构的很大一部分放到网络上以后不会咬我......

CREATE TABLE `ingredients` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `quantity` float DEFAULT NULL,
  `food` varchar(255) NOT NULL,
  `unit_id` int(11) DEFAULT NULL,
  `ingredient_group_id` int(11) DEFAULT NULL,
  `order_by` int(11) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `range` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `unit_id` (`unit_id`),
  KEY `ingredient_group_id` (`ingredient_group_id`),
  CONSTRAINT `ingredients_ibfk_1` FOREIGN KEY (`unit_id`) REFERENCES `units` (`id`),
  CONSTRAINT `ingredients_ibfk_2` FOREIGN KEY (`ingredient_group_id`) REFERENCES `ingredient_groups` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=269974 DEFAULT CHARSET=utf8

CREATE TABLE `units` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `abbreviation` varchar(255) CHARACTER SET latin1 NOT NULL,
  `type` int(11) NOT NULL,
  `si` float NOT NULL,
  `lower_bound` float DEFAULT NULL,
  `lower_unit_id` int(11) DEFAULT NULL,
  `upper_bound` float DEFAULT NULL,
  `upper_unit_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `lower_unit_id` (`lower_unit_id`),
  KEY `upper_unit_id` (`upper_unit_id`),
  CONSTRAINT `units_ibfk_1` FOREIGN KEY (`lower_unit_id`) REFERENCES `units` (`id`),
  CONSTRAINT `units_ibfk_2` FOREIGN KEY (`upper_unit_id`) REFERENCES `units` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8

CREATE TABLE `ingredient_groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `recipe_id` int(11) NOT NULL,
  `order_by` int(11) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `recipe_id` (`recipe_id`),
  CONSTRAINT `ingredient_groups_ibfk_1` FOREIGN KEY (`recipe_id`) REFERENCES `recipes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=32739 DEFAULT CHARSET=utf8

Answer 1

缺少很多信息，但我首先要检查的是:

• 如果在 MyISAM 表上:极度碎片化的文件，尤其是索引文件。使用 filefrag 来检查。如果数据库随时间增长缓慢，就会发生这种情况。如果是，直接关闭MySQL，复制数据库目录，将原来的和新的副本重命名，然后重启MySQL

• 如果您使用 InnoDB 表:基于文件的数据存储，同样过于分散。在这种情况下，碎片可以在文件系统级别(如上所述检查和处理)或在数据存储级别使用 InnoDB 工具。在最坏的情况下，基于 block 设备的数据存储(不能在外部产生碎片)可能会出现内部碎片的糟糕情况。

• 一些基数极低的索引。也就是说，一个非唯一索引具有很少的不同值，即有很多重复。该索引渐近地接近线性列表，具有 O(n) 时间配置文件。这可以是表上的索引，也可以是引用的外部索引。

• 读者争论。不太可能，但是大量的并发读者可以拖延单个作者。

编辑:

阅读您的定义后，我认为 ingredients.unit_id 和 ingredients.ingredient_group_id 是第一个要检查的候选对象，因为它们的基数似乎很低。

第一个不太可能有用(你打算选择所有用勺子测量的成分吗？)，所以你可以放弃它。

第二个可能非常有用；但如果成分组很少，基数可能会非常低，从而降低性能。要提高基数，请添加一些部分以使其更具辨别力。如果没有其他字段可能与组 ID 一起出现在查询中，只需添加主 ID 或创建日期，使其成为 (ingredient_group_id, id) 或 (ingredient_group_id, created_at)。增加复杂性以使其更快似乎违反直觉，但它确实有帮助。作为奖励，您可以将 sort by created_at 添加到按 ingredient_group_id 选择的任何查询，而不会降低性能。