我一直在写一个算法的结果,该算法计算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。
我需要查询以将每个客户与邻居购买的产品和价值分组。查询如下所示:
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(distingant customer_from)都几乎是瞬时的)。
出于好奇,我尝试继续在myisam表中插入距离的过程。当程序开始运行的速度比在innodb表上运行的速度至少快100倍时(对于INSERTS),这让我感到惊讶!
对于每个客户,程序会插入约3000行(每个product_type的每个邻居插入一行。每个客户约300的邻居和10个product_type)。通过插入innodb表,单个客户花费了40到60秒(约3000行)的时间。使用myisam表,插入3个客户(大约9000行aprox)需要1秒。
一些额外的信息:
因此,总的来说,问题是:为什么MyISAM的插入语句这么快?你怎么认为?
编辑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;
插入物
INSERT
立即“提交” 。可以通过一次集中100-1000行来解决此问题。autocommit
和BEGIN..COMMIT
。选择
调音
key_buffer_size
RAM和设置为20%innodb_buffer_pool_size=0
。key_buffer_size
仅设置为10M并设置innodb_buffer_pool_size
为70%的RAM。规范化和节省空间
DECIMAL(10,2)
在大多数情况下不是最好的。考虑FLOAT
使用非货币(例如distance
)。考虑较少的数字;最多可处理99,999,999.99,并占用5个字节。customer_from
和customer_to
。有一张Customers
桌子,里面都有。latidud DECIMAL(6,4)
和longitud (7,4)
,总共7个字节。(这些分辨率为16m / 52ft。)结果
根据这些建议,50M行表将非常小,并且在两个引擎中的运行速度都非常快。然后再次运行比较。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句