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

2018-06-18 12:36:37

我一直在写一个算法来计算InnoDB表中的客户之间的距离。例如，如果我的客户是A，B，C和D，则数据库中的表格如下所示：

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

等等......我认为我会打五千万。

其他列是product_type和value。那些告诉我客户B（customer_to在列中）购买了多少product_type。这意味着我有多个对，每个对取决于客户B购买多少个产品类型。

我需要进行查询，将每个客户的邻居购买的产品和价值分组。该查询如下所示：

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（不同的customer_from），其中几乎是瞬时的。

只是为了好奇，我试图继续在myisam表中插入距离的过程。对于我来说，当程序开始运行至少比在innodb表上工作快100倍时，对我来说是一个惊喜！

对于每个客户，程序插入3000行（每个product_type的每个邻居一个，例如300个邻居和每个客户的10个product_types）。用innodb表插入一个客户需要40到60秒（约3000行）。使用myisam表格，插入3个客户需要1秒钟（9000行aprox）。

一些额外的信息：

mysql数据库在我的PC（localhost）中。

用java编写的程序正在我的电脑上运行。

我正在使用准备好的语句，并且我只更改每行和下一行之间的数据。这与这个问题有关为什么myisam存储引擎比Innodb存储引擎更快

总而言之，问题是：为什么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;

插入

默认情况下，InnoDB立即“提交”每个INSERT 。这可以通过一次聚集100-1000行来弥补。

批量插入将加速MyISAM和InnoDB - 可能会增加10倍。

了解autocommit和BEGIN..COMMIT 。

选择

InnoDB比MyISAM消耗更多的磁盘空间 - 通常是2x-3x; 这会影响桌面扫描，您可能会这样做

对于该查询，（customer_from，product_type，distance）上的复合索引可能有助于两个引擎。

调音

当运行刚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英尺。）

结果

在提出这些建议之后，50M排的桌子将会小得多，并且在两个发动机中运行得非常快。然后再次运行比较。

链接地址: http://www.djcxy.com/p/52237.html

上一篇: In this case MyISAM is dramatically faster than InnoDB in mysql

下一篇: InnoDB or MyISAM for sessions?