mysql - 从 S3 加载数据 - 大数据和索引的最快方式

Question

我有一个包含假日优惠的表格，因此为了给您一个想法，每行将包含以下数据:

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 个表格打包而成，分别是航类、住宿 和接送，打包后的结果是查找每种变化的最便宜的交易，例如每个出发机场、持续时间、登机基础等。

我要导入的表将包含大约 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)  */;

```

Answer 1

如果有 50M 行，但 AUTO_INCRMENT=1258378560，请指出另一个迫在眉睫的问题。 (可能与加载慢有关。)

`aid` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT

只允许40亿；你已经是12亿了。做一些数学计算来估计 id 何时会用完。强力解决方案是更改为 BIGINT，但让我们分析一下 id 被“烧毁”的原因。 INSERT/REPLACE/etc 可以通过多种方式丢弃 id。请描述导入是如何进行的。 REPLACE 可能是最糟糕的——它会烧毁 ids 并且实际上是DELETE + INSERT。其他技术更快。

(我现在将在多个方向上闲逛......)

按月分区(我假设您正在使用(startdatet)进行操作)可能不会增加任何性能。您的经验是什么？(我通常反对使用PARTITION code> 除了少数有好处的用例之外。我认为您的情况没有任何好处。)

19 个索引意味着 19 个 BTree 必须更新。在INSERT完成之前必须检查这2个唯一的；其他 17 个项目可以推迟，但不能永远推迟。 (详细信息将在“更改缓冲区”下讨论。)

多少内存？ innodb_buffer_pool_size的设置是什么？它应该是 RAM 的 70% 左右。更改缓冲区是其中的一部分。

我发现至少有 4 个索引可以删除，因为其他索引可以满足它们的需求。一般来说，如果您有 INDEX(a, b)，则不需要 INDEX(a)。 (从 19 个索引缩减到 15 个会对一些有所帮助。)

标志和其他低基数的东西本身作为索引实际上是没有用的。优化器将决定扫描表比在索引的 BTree 和数据 BTree 之间跳转更便宜。我正在考虑INDEX(评级)。

任何在 WHERE 中没有有 startdatet 的 SELECT 可能会变慢 与没有分区相比。这是因为查询必须检查所有 13 个分区。即使使用 AND startdatet = 4，性能也不会比有一个包含 startdatet 的索引更好。

让我讨论以列(可能是 价格、评级、开始日期)开始的任何索引，该列是作为“范围”进行查询(例如，WHERE Price BETWEEN ...)。处理不能使用该列之后的任何列。我怀疑 ik_FILTER_ALL 会扫描索引的很大一部分，因为它只根据 price 进行过滤。重新排列列。根据名称，我猜测这是一个“覆盖”索引。也就是说，一个普通的查询只引用那6列？注意:SELECT * ... 引用的不仅仅是这 6 个，因此索引不是“覆盖”的。 (向我们展示查询；我可以进一步讨论。)

对于某些查询来说，5 个“DirectFromPrice”索引可能都是“完美”的。但它们非常长(很多列)。我猜想 2 个较短的列表将接近“足够好”处理这 5 个案例。 (请记住，减少索引数量将有助于实现插入时间的目标。)

您使用的 MySQL/MariaDB 版本是什么？

此时的主要操作项:向我们展示导入。 (在看到所使用的方法后，我将讨论对输入进行排序。)