mysql - 在这种情况下，MyISAM 比 mysql 中的 InnoDB 快得多

Question

我一直在编写计算 InnoDB 表中客户之间距离的算法的结果。例如，如果我的客户是 A、B、C 和 D，则数据库中的表如下所示，除其他列外:

From | To    | Distance
  A     B        344
  A     C        274
  A     D        182
  B     C        338

等等...行数很多我想我会达到5000万。

其他列是 product_type 和 value。这些告诉我客户 B(列中的 customer_to)对该 product_type 的购买量。这意味着我会根据客户 B 购买的 product_type 的数量对每对进行多次。

我需要一个查询来将每个客户与他的邻居购买的产品和值(value)分组。查询如下所示:

select customer_from, product_type, avg(value) as opportunity
from customer_distances
where distance < 500
group by customer_from, product_type
order by opportunity desc; 

innodb 表无法回答我的查询。尽管我将 net_read_timeout 更改为 28800，但在查询期间 mysql 连接丢失。

我认为它与用于事务处理而非密集查询的 innodb 构建有关。所以我用 MyIsam 作为引擎创建了一个新表，并从 innodb 表中插入选择所有记录。

正如预期的那样，选择速度非常快(70 段)，所有其他选择都像 count(distinct customer_from)，几乎是瞬时的。

出于好奇，我尝试继续在 myisam 表中插入距离的过程。当程序开始运行时比它在 innodb 表上运行时至少快 100 倍 - 对于 INSERTS!

对于每个客户，程序会插入大约 3000 行(每个邻居对应每个 product_type。大约 300 个邻居和每个客户 10 个 product_type)。使用 innodb 表插入单个客户需要 40 到 60 秒(大约 3000 行)。使用 myisam 表，插入 3 个客户需要 1 秒(大约 9000 行)。

一些额外信息:

• mysql 数据库在我的 PC(本地主机)中。
• 用 java 编写的程序并在我的电脑上运行。
• 我正在使用准备好的语句，我只更改每个语句之间的数据行和下一个。这与这个问题有关 Why is myisam storage engine is faster than Innodb storage engine

所以总而言之，问题是:为什么 MyISAM 使用插入语句那么快？你怎么看？

编辑 1:我正在为 innodb 和 myisam 这两个表添加创建语句。编辑 2:我删除了一些无用的信息，并在这里和那里进行了一些格式化。

/* INNODB TABLE */
CREATE TABLE `customer_distances` (
  `customer_from` varchar(50) NOT NULL,
  `customer_from_type` varchar(50) DEFAULT NULL,
  `customer_from_segment` varchar(50) DEFAULT NULL,
  `customer_from_district` int(11) DEFAULT NULL,
  `customer_from_zone` int(11) DEFAULT NULL,
  `customer_from_longitud` decimal(15,6) DEFAULT NULL,
  `customer_from_latitud` decimal(15,6) DEFAULT NULL,
  `customer_to` varchar(50) NOT NULL,
  `customer_to_type` varchar(50) DEFAULT NULL,
  `customer_to_segment` varchar(50) DEFAULT NULL,
  `customer_to_district` int(11) DEFAULT NULL,
  `customer_to_zone` int(11) DEFAULT NULL,
  `customer_to_longitud` decimal(15,6) DEFAULT NULL,
  `customer_to_latitud` decimal(15,6) DEFAULT NULL,
  `distance` decimal(10,2) DEFAULT NULL,
  `product_business_line` varchar(50) DEFAULT NULL,
  `product_type` varchar(50) NOT NULL,
  `customer_from_liters` decimal(10,2) DEFAULT NULL,
  `customer_from_dollars` decimal(10,2) DEFAULT NULL,
  `customer_from_units` decimal(10,2) DEFAULT NULL,
  `customer_to_liters` decimal(10,2) DEFAULT NULL,
  `customer_to_dollars` decimal(10,2) DEFAULT NULL,
  `customer_to_units` decimal(10,2) DEFAULT NULL,
  `liters_opportunity` decimal(10,2) DEFAULT NULL,
  `dollars_opportunity` decimal(10,2) DEFAULT NULL,
  `units_oportunity` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`cliente_desde`,`cliente_hasta`,`grupo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/* MYISAM TABLE */
CREATE TABLE `customer_distances` (
  `customer_from` varchar(50) NOT NULL,
  `customer_from_type` varchar(50) DEFAULT NULL,
  `customer_from_segment` varchar(50) DEFAULT NULL,
  `customer_from_district` int(11) DEFAULT NULL,
  `customer_from_zone` int(11) DEFAULT NULL,
  `customer_from_longitud` decimal(15,6) DEFAULT NULL,
  `customer_from_latitud` decimal(15,6) DEFAULT NULL,
  `customer_to` varchar(50) NOT NULL,
  `customer_to_type` varchar(50) DEFAULT NULL,
  `customer_to_segment` varchar(50) DEFAULT NULL,
  `customer_to_district` int(11) DEFAULT NULL,
  `customer_to_zone` int(11) DEFAULT NULL,
  `customer_to_longitud` decimal(15,6) DEFAULT NULL,
  `customer_to_latitud` decimal(15,6) DEFAULT NULL,
  `distance` decimal(10,2) DEFAULT NULL,
  `product_business_line` varchar(50) DEFAULT NULL,
  `product_type` varchar(50) NOT NULL,
  `customer_from_liters` decimal(10,2) DEFAULT NULL,
  `customer_from_dollars` decimal(10,2) DEFAULT NULL,
  `customer_from_units` decimal(10,2) DEFAULT NULL,
  `customer_to_liters` decimal(10,2) DEFAULT NULL,
  `customer_to_dollars` decimal(10,2) DEFAULT NULL,
  `customer_to_units` decimal(10,2) DEFAULT NULL,
  `liters_opportunity` decimal(10,2) DEFAULT NULL,
  `dollars_opportunity` decimal(10,2) DEFAULT NULL,
  `units_oportunity` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`cliente_desde`,`cliente_hasta`,`grupo`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Answer 1

插入

• InnoDB，默认情况下，立即“提交”每个INSERT。这可以通过一次聚集 100-1000 行来解决。
• 批量插入将加速 MyISAM 和 InnoDB - 可能提高 10 倍。
• 了解自动提交和BEGIN..COMMIT。

选择

• InnoDB 比 MyISAM 消耗更多的磁盘空间——通常是 2 到 3 倍；这会影响表扫描，您可能是
• 对于该查询，(customer_from、product_type、distance)的复合索引可能对两个引擎都有帮助。

调整

• 当运行 just MyISAM 时，将 key_buffer_size 设置为 RAM 的 20% 和 innodb_buffer_pool_size=0。
• 仅运行 InnoDB 时，将 key_buffer_size 设置为仅 10M，并将 innodb_buffer_pool_size 设置为 RAM 的 70%。

标准化和节省空间

• 更小 --> 更可缓存 --> 更少的 I/O --> 更快(在任一引擎中)
• DECIMAL(10,2) 在大多数情况下并不是最好的。考虑非货币的 FLOAT(例如 distance)。考虑更少的数字；最多可处理 99,999,999.99，占用 5 个字节。
• 复制列通常不是一个好主意，例如 customer_from 和 customer_to 的 10 列。有一个 Customers 表，两者都在里面。
• 你的每一个纬度和经度都是 7 个字节并且有不必要的分辨率。建议 latidud DECIMAL(6,4) 和 longitud (7,4)，总共为 7 个字节。 (这些分辨率为 16 米/52 英尺。)

结果

在这些建议之后，5000 万行的表将小得多，并且在两个引擎中运行得更快。然后再次运行比较。