代码之家 › 专栏 › 技术社区 › Dago Borda

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

myisam innodb performance database mysql

Dago Borda · 技术社区 · 8 年前

我一直在写一个算法的结果,该算法在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购买的产品类型数量,我会多次使用每一对产品。

我需要对每个客户进行查询,将其邻居购买的产品和价值分组。查询如下:

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表中的所有记录。

出于好奇,我尝试继续在myisam表中插入距离的过程。当程序开始运行时,它的速度比在innodb表上运行时至少快100倍时,这让我感到惊讶——用于INSERTS!

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

一些额外信息:

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

总之,问题是: 为什么MyISAM使用insert语句会这么快?

编辑1:我正在为innodb和myisam这两个表添加create语句。编辑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;

1 回复 | 直到 7 年前

Rick James 8 年前

插入

InnoDB默认“提交”每个 INSERT 立即这可以通过一次聚集100-1000行来解决。
批量插入将使MyISAM和InnoDB的速度都提高10倍。
了解 autocommit 和 BEGIN..COMMIT .

选择

InnoDB比MyISAM占用更多磁盘空间,通常为2x-3x;这会影响表扫描,您可能是
对于该查询,(customer_from、product_type、distance)上的复合索引可能对这两个引擎都有帮助。

调谐

运行时只是 MyISAM,套 key_buffer_size 至20%的RAM和 innodb_buffer_pool_size=0 .
运行时只是 InnoDB,套 密钥缓冲区大小 仅10M和 innodb_buffer_pool_size 至70%的RAM。

规范化和节省空间

更小-->更易于缓存-->更少的I/O-->更快(在任一发动机中)
DECIMAL(10,2) 在大多数情况下都不是最好的。考虑 FLOAT 对于非货币(例如 distance ). 考虑更少的数字;它最多可以处理99999999.99,并且需要5个字节。
复制列通常不是一个好主意,例如 customer_from 和 customer_to .有一个 Customers 桌子,两个都在里面。
你的纬度和经度都是7字节,并且有不必要的分辨率。建议 latidud DECIMAL(6,4) 和 longitud (7,4) ,对于 全部的 共7个字节。(这些分辨率为16米/52英尺。)

后果

根据这些建议,50M行表将非常小,并且在两个引擎中运行得更快。然后再次运行比较。