logo标签列表

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

mysqldatabaseperformanceinnodbmyisam
3

我一直在编写一个算法，用于计算InnoDB表中客户之间的距离。例如，如果我的客户是A、B、C和D，那么数据库中的表格看起来像这样，除了其他列：

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

等等...我认为这是很多行，我想我会达到5000万。

其他列是产品类型和价值。它们告诉我客户B（在列中的customer_to）购买了多少该产品类型。这意味着每个对都会因客户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（distinct customer_from））几乎是瞬间完成的。
只是出于好奇，我尝试继续在myisam表中插入距离的过程。当程序开始运行时，它以至少比在InnoDB表上工作时快100倍-对于INSERTS！
对于每个客户端，程序会插入大约3000行（每个产品类型的每个邻居都有一行。每个客户端大约有300个邻居和10个产品类型）。在InnoDB表中，插入单个客户端需要大约40到60秒（约3000行）。在myisam表中，插入3个客户端（约9000行）需要1秒钟。
一些额外的信息：
  • MySQL数据库在我的个人电脑上（本地主机）。
  • 该程序由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;
这些表是否完全相同（索引等）？并且引擎有单独的服务器设置（例如内存缓存大小），可能会影响性能。 - Uueerdo
相同的表格，相同的服务器。 - Dago Borda
1
通常关于性能的问题需要对所有相关表进行CREATE，以及使用EXPLAIN。 - Strawberry
1
你无法说服我，像这样对数据进行去规范化处理，并将拥有5000万行的表格大小增加两倍或三倍，真的会使你的报告运行显著更快。同样有可能会导致它们运行得更慢。而且你打算如何维护这些数据呢？ - user207421
你说得对，我没有理解你的评论。不同的引擎之间肯定应该有不同的设置。我已经有一段时间没有在那个项目上工作了，但是只要我有答案，我会在这里发布结果。 - Dago Borda
显示剩余5条评论
1个回答
4

插入

  • 默认情况下，InnoDB会立即“提交”每个INSERT。这可以通过将100-1000行分组来解决。
  • 批量插入将加快MyISAM和InnoDB的速度，可能提高10倍。
  • 学习有关autocommit和BEGIN..COMMIT的知识。

选择

  • InnoDB占用的磁盘空间比MyISAM多——通常为2倍至3倍；这会影响表扫描，您可能正在使用的操作。
  • 对于该查询，在（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)在大多数情况下不是最好的选择。对于非金额（例如distance），可以考虑FLOAT。考虑较少的数字；它们处理高达99,999,999.99，只需要5个字节。
  • 通常不建议有复制列，例如customer_from和customer_to的10个列。使用一个包含这两个值的Customers表。
  • 您的每个纬度和经度为7个字节，并具有不必要的分辨率。建议使用latidud DECIMAL(6,4)和longitud (7,4)，总共只有7个字节。（这些给出16m / 52ft的分辨率。）

结果

在采取这些建议之后，5000万行表格将变得更小，在两个引擎中运行速度也会更快。然后再次进行比较。

非常感谢您的回答。我会进行更改并在此记录结果。 - Dago Borda
@DagoBorda - 有结果了吗？ - Rick James
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接