mysql - 使用 MySQL 分区来加速并发删除和选择？

Question

我有一个包含大约 850 万行的 MySQL Innodb 表。表结构基本上是这样的:

CREATE TABLE `mydatatable` (
  `ext_data_id` int(10) unsigned NOT NULL,
  `datetime_utc` date NOT NULL DEFAULT '0000-00-00',
  `type` varchar(8) NOT NULL DEFAULT '',
  `value` decimal(6,2) DEFAULT NULL,
  PRIMARY KEY (`ext_data_id`,`datetime_utc`,`type`),
  KEY `datetime_utc` (`datetime_utc`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

每天晚上，我都会使用以下查询从该表中删除过期值:

delete from mydatatable where datetime_utc < '2013-09-23'

这个查询好像没有使用indizes，而且运行起来需要相当长的时间。但是，我也在同一张表上获得并发更新和选择。然后这些被锁定，导致我的网站当时没有响应。

我正在寻找加速此设置的各种方法。我遇到了 MySQL 分区，我想知道这是否适合。我总是在该表中添加和选择较新的数据并删除旧数据。我可以根据 MOD(DAYOFYEAR(datetime),4) 之类的东西创建分区。现在，当我删除时，我总是会从另一个分区中删除值，而不是我正在读取或写入的分区。

使用此设置我会遇到锁定吗？就我而言，分区会提高查询速度和可用性吗？或者我应该寻找另一种解决方案，如果是的话，是哪一种？

Answer 1

从 MySQL 5.5 开始，您可以使用函数 COLUMNS ，这简化了非整数列(例如 datetime_utc)的分区。

性能方面:

• 删除分区是 LIST 和 RANGE 分区的常量时间操作。速度相当于 TRUNCATE TABLE 或 rm 文件，因此实际上与分区的大小无关。
• 在分区表上执行 SELECT 受益于 partition pruning ，以便您只从符合搜索条件的分区中读取。这也可以加快范围扫描。

提示:

不要忘记添加一个“默认”分区，例如

    PARTITION the_last_one VALUES LESS THAN(MAXVALUE)

为了避免 INSERT/UPDATE 语句失败，因为找不到要插入的分区。