我一直在编写计算结果,该算法计算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_types数量 .
我需要查询将每个客户与他的邻居购买的产品和 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 segs),所有其他选择都像count(distinct customer_from),几乎是瞬间的 .
出于好奇,我试图继续在myisam表中插入距离的过程 . 当程序开始运行至少比在innodb表上工作时快100倍时,我感到很惊讶 - 对于INSERTS!
对于每个客户,程序会插入3000行(每个product_type每个邻居一个 . 每个客户有300个邻居和10个product_types) . 使用innodb表插入一个客户需要40到60秒(aprox.3000行) . 使用myisam表,插入3个客户(9000行aprox)需要1秒钟 .
Some extra information:
-
mysql数据库在我的PC(localhost)中 .
-
用java编写的程序,从我的电脑上运行 .
-
我正在使用预处理语句,我只更改每行和下一行之间的数据 . 这与这个问题有关Why is myisam storage engine is faster than Innodb storage engine
So in summary the question is: 为什么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;
1 回答
Inserts
InnoDB,默认情况下,"commits"立即
INSERT
. 这可以通过一次聚集100-1000行来解决 .批量插入将加速MyISAM和InnoDB - 可能是10倍 .
了解
autocommit
和BEGIN..COMMIT
.Select
InnoDB占用的磁盘空间比MyISAM多 - 通常为2x-3x;这可能影响表扫描
对于该查询,(customer_from,product_type,distance)上的复合索引可能对两个引擎都有帮助 .
Tuning
当仅运行MyISAM时,将
key_buffer_size
设置为20%的RAM和innodb_buffer_pool_size=0
.当只运行InnoDB时,将
key_buffer_size
设置为仅10M,将innodb_buffer_pool_size
设置为70%的RAM .Normalization and saving space
更小 - >更多可缓存 - >更少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英尺 . )Result
在这些建议之后,50M行表将非常小,并且在两个引擎中运行得非常快 . 然后再次运行比较 .