我有一个由假日交易组成的表,为了给您一个想法,每行将包含以下数据位:
Departure airport
Arrival airport
Start date
Duration
Hotel destination
Resort
Hotel name
Hotel rating
A few tiny integer columns for 1s and 0s.
Price
Date time the row was updated
现在,所有这些交易都从3个表中打包,它们是 flights
, accommodation
以及 transfers
Package 是为了找到每个变化的最便宜的交易,例如,每个出发机场,持续时间,登机基础等。
我要导入的表将包含大约5000万行,导入速度非常慢。
我已经删除了索引,这造成了巨大的差异,但是现在当我在所有数据都在表中之后将索引重新添加回表中时,要花很长时间才能完成。
我想知道有没有一种快速批量加载数据的方法,或者在添加数据后有没有一种更快的方法将索引添加回表中?
创建表
CREATE TABLE iv_deals
(aid
INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Deal Autonumber PK',startdate
DATE NULL DEFAULT NULL COMMENT 'Holiday Start Date',startdatet
TINYINT(2) NOT NULL DEFAULT '0',depairport
CHAR(3) NULL DEFAULT NULL COMMENT 'Departure Airport IATA Code',arrairport
CHAR(3) NULL DEFAULT NULL COMMENT 'Arrival Airport IATA Code',destination
VARCHAR(30) NULL DEFAULT NULL COMMENT 'Holiday Destination',resort
VARCHAR(30) NULL DEFAULT NULL COMMENT 'Holiday Resort',hotel
VARCHAR(50) NULL DEFAULT NULL COMMENT 'Holiday Property Name',iv_PropertyID
INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Holiday Property ID',rating
VARCHAR(2) NULL DEFAULT NULL COMMENT 'Holiday Property Star Rating',board
VARCHAR(10) NULL DEFAULT NULL COMMENT 'Holiday Meal Option',duration
TINYINT(2) UNSIGNED NULL DEFAULT '0' COMMENT 'Holiday Duration',2for1
TINYINT(1) UNSIGNED NULL DEFAULT '0' COMMENT 'Is 2nd Week FREE Offer, 0 = False, 1 = True',3for2
TINYINT(1) UNSIGNED NULL DEFAULT '0' COMMENT 'Is 3rd Week FREE Offer, 0 = False, 1 = True',3and4
TINYINT(1) UNSIGNED NULL DEFAULT '0' COMMENT 'Is 3rd and 4th Week FREE Offer, 0 = False, 1 = True',4for3
TINYINT(1) UNSIGNED NULL DEFAULT '0' COMMENT 'Is 4th Week FREE Offer, 0 = False, 1 = True',freebb
VARCHAR(2) NULL DEFAULT NULL COMMENT 'Free Week Meal Option',adults
TINYINT(1) UNSIGNED NULL DEFAULT '0' COMMENT 'Number of Adults',children
TINYINT(1) UNSIGNED NULL DEFAULT '0' COMMENT 'Number of Children',infants
TINYINT(1) UNSIGNED NULL DEFAULT '0' COMMENT 'Number of Infants',price
SMALLINT(4) UNSIGNED NULL DEFAULT '9999' COMMENT 'Price',carrier
VARCHAR(40) NULL DEFAULT NULL COMMENT 'Flight Carrier IATA Code',DateUpdated
DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (aid
, startdatet
),
UNIQUE INDEX Unique
(startdate
, depairport
, arrairport
, iv_PropertyID
, board
, duration
, adults
, children
, startdatet
),
INDEX ik_Price
(price
),
INDEX ik_Destination
(destination
),
INDEX ik_Resort
(resort
),
INDEX ik_DepAirport
(depairport
),
INDEX ik_Startdate
(startdate
),
INDEX ik_Board
(board
),
INDEX ik_FILTER_ALL
(price
, depairport
, destination
, resort
, board
, startdate
),
INDEX iv_PropertyID
(iv_PropertyID
),
INDEX ik_Duration
(duration
),
INDEX rating
(rating
),
INDEX adults
(adults
),
INDEX DirectFromPrice
(iv_PropertyID
, depairport
, arrairport
, board
, duration
, adults
, children
, startdate
),
INDEX DirectFromPrice_wo_depairport
(iv_PropertyID
, arrairport
, board
, duration
, adults
, children
),
INDEX DirectFromPrice_w_pid_dep
(iv_PropertyID
, depairport
, adults
, children
, price
),
INDEX DirectFromPrice_w_pid_night
(iv_PropertyID
, duration
, adults
, children
),
INDEX DirectFromPrice_Dur_Board
(iv_PropertyID
, duration
, board
, adults
, children
),
INDEX join_index
(destination
, startdate
, duration
)
)
COLLATE='utf8_general_ci'
AUTO_INCREMENT=1258378560
/*!50100 PARTITION BY LIST (startdatet)
(PARTITION part0 VALUES IN (1) ENGINE = InnoDB,
PARTITION part1 VALUES IN (2) ENGINE = InnoDB,
PARTITION part2 VALUES IN (3) ENGINE = InnoDB,
PARTITION part3 VALUES IN (4) ENGINE = InnoDB,
PARTITION part4 VALUES IN (5) ENGINE = InnoDB,
PARTITION part5 VALUES IN (6) ENGINE = InnoDB,
PARTITION part6 VALUES IN (7) ENGINE = InnoDB,
PARTITION part7 VALUES IN (8) ENGINE = InnoDB,
PARTITION part8 VALUES IN (9) ENGINE = InnoDB,
PARTITION part9 VALUES IN (10) ENGINE = InnoDB,
PARTITION part10 VALUES IN (11) ENGINE = InnoDB,
PARTITION part11 VALUES IN (12) ENGINE = InnoDB,
PARTITION part12 VALUES IN (0) ENGINE = InnoDB) */;
1条答案
按热度按时间qxsslcnc1#
如果有50米排,但是
AUTO_INCREMENT=1258378560
,让我们指出另一个迫在眉睫的问题(这可能与慢速负载有关。)只允许40亿;你已经有12亿了。做一点数学来估计你什么时候会用完ID。暴力解决方案是
BIGINT
,但让我们分析一下ID被“烧掉”的原因。有几种方法可以INSERT
/REPLACE
/etc可以扔掉身份证。请描述导入是如何工作的。REPLACE
可能是最糟糕的——它燃烧身份证,而且有效DELETE
+INSERT
. 其他技术更快。(我现在要向四面八方漫谈……)
按月分区(我假设您正在使用(
startdatet
)可能不会增加任何性能。你的经历是什么(我通常反对使用PARTITION
除了少数有好处的用例。我看你的情况没有好处。)19个索引表示必须更新的19个btrees。两个唯一的必须在测试前检查
INSERT
完成;另外17个可以推迟,但不能永远推迟(详情在“更改缓冲区”下讨论多少公羊?什么是背景
innodb_buffer_pool_size
? 它应该是内存的70%左右。更改缓冲区就是其中的一部分。我看到至少有4个索引可以删除,因为其他索引可以处理它们的需要。一般来说,如果你有
INDEX(a, b)
,你不需要INDEX(a)
. (从19个索引缩减到15个索引会有所帮助。)旗子和其他低基数的东西本身作为索引实际上是无用的。优化器将决定扫描表要比在索引的btree和数据btree之间跳转便宜。我在想
INDEX(rating)
.任何
SELECT
那是不可能的startdatet
在WHERE
可能比没有分区时慢。这是因为查询必须检查所有13个分区。即使有AND startdatet = 4
,性能不会比包含startdate的索引更好。让我来讨论任何以列开始的索引(也许
price
,rating
,startdate
)作为“范围”查询的(例如,WHERE price BETWEEN ...
). 处理不能使用该列之后的任何列。我怀疑ik_FILTER_ALL
将扫描一大块索引,因为它只在price
. 重新排列列。根据这个名字,我猜这是一个“覆盖”索引。也就是说,一个公共查询只引用这6列?注:SELECT * ...
引用的不仅仅是这6个,因此索引没有“覆盖”(向我们展示查询;我可以进一步讨论。)5个“directfromprice”索引对于某些查询来说可能都是“完美的”。但是它们太长了(很多列)。我猜两个短名单将接近处理5个案件“足够好”(请记住,减少索引的数量将有助于实现插入时间的目标。)
您使用的是哪个版本的mysql/mariadb?
此时的主要动作是:向我们展示导入(在看到使用的方法之后,我将讨论对输入进行排序。)