MySQL 特性之分区表

对于小量数据来讲，数据库的优化往往是建立高效的索引策略，最常用的索引类型是 B-Tree 索引。然而量变引起质变，在单表数据量较大时，B-Tree索引就无法起作用了。除非是索引覆盖查询，否则数据库服务器需要根据索引扫描的结果回表，查询所有符合条件的记录，如果数据量巨大，这将产生大量随机I/O，随之，数据库的响应时间将大到不可接受的程度。另外，索引维护（磁盘空间、I/O操作）的代价也非常高。

分表与分区

分表

分表又可分为垂直分表和水平分表。

垂直分表

原因：

1.对于 InnoDB引擎来讲，主索引叶子节点存储着当前行的所有信息，所以减少字段可使内存加载更多行数据，有利于查询。

2.受限于操作系统中的文件大小限制。

切分原则： 把不常用或业务逻辑不紧密或存储内容比较多的字段分到新的表中可使表存储更多数据。

水平分表：

原因：

1.随着数据量的增大，行数巨大，索引失效，查询的效率越来越低。

2.同样受限于操作系统中的文件大小限制，数据量不能无限增加，当到达一定容量时，需要水平切分以降低单表（文件）的大小。

切分原则： 增量区间或散列或其他业务逻辑。

使用哪种切分方法要根据实际业务逻辑判断。比如对表中有时间字段，就可以根据时间区间分表。

如果对表的访问较均匀，没有明显的热点区域，则可以考虑用范围（比如每500w一个表）或普通 Hash 或一致性Hash来切分。

分区

　在日常的工作中，我们经常遇到一张表里面保存了上亿甚至过十亿的记录。这些表里面保存了大量的历史记录，并且对历史数据的访问较少，有明显的冷热区分，由于数据量剧增会导致热点数据查询效率降低，当想清除无效的历史数据时，因为清楚数据量较大会使得锁表时间变长，对数据库的造成了很大压力。即使把这些无效数据删除了，底层的数据文件并没有变小。面对这类问题，最有效的方法就是在使用分区表。最常见的分区方法就是按照时间进行分区。分区的优点是可以解决大数据量下的慢查询，也可以非常高效的进行历史数据的清理。

分区功能并不是在存储引擎层完成的，因此不只有 InnoDB 存储引擎支持分区，常见的存储引擎MyISAM、NDB等都支持分区。但是并不是所有的存储引擎都支持，如 CSV、FEDORATED、MERGE 等就不支持分区。在使用此分区功能前，应该对选择的存储引擎对分区的支持有所了解。

可以通过使用SHOW VARIABLES 命令来确定 MySQL 是否支持分区，例如：

1	mysql> SHOW VARIABLES LIKE '%partition%';

分区表冷热数据分离实践

业务场景

某订单表中冷热数据区分明显，现将历史数据迁移到一张新的历史表中，历史表属分区表。

建表

热点表

CREATE TABLE `t_hot` (
  `id` int(11) NOT NULL ,
  `date` date DEFAULT NULL,
    -- omitted other column
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `IX_DCCC` (`date`,`col_1`,`col_2`,`col_3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

历史表

CREATE TABLE `t_history` (
  `id` int(11) NOT NULL ,
  `date` date DEFAULT NULL,
  -- omitted other column
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
) ENGINE=InnoDB DEFAULT CHARSET=utf8 PARTITION  BY RANGE(TO_DAYS(`date`)) (
    PARTITION p_defalut VALUES LESS THAN (MAXVALUE)
);

数据迁移

使用脚本将热点数据迁移到历史表中，迁移过程如下

public function moveGeData() {
    // 如果表不存在，则建表
	... omitted ...
    // 一月新增一个分区
    $month_ago = date('Y-m-d',time() - 1 * 30 * 24 * 60 * 60);
    $ym = date('ym',(strtotime($six_months_ago)-1));
    $partition = "ALTER TABLE t_history REORGANIZE PARTITION pmax INTO (PARTITION p{$ym} VALUES LESS THAN (TO_DAYS('{$month_ago}')),PARTITION pmax VALUES LESS THAN MAXVALUE)";
    $this->db->query($partition);
    echo "add partition:$partition\n";
    // 迁移
    $sql_insert = "insert into t_history select * from t_hot where date <= '$month_ago';";
    $ret =  $this->db->query($sql_insert)->affect_rows();

    $sql_del = "delete from t_hot where date <= '$month_ago' limit ".$ret;
    $this->db->query($sql_del);
    echo "[Done ".date("h:i:s")."]\n";
}

参考

http://xieminis.me/?p=216

http://www.cnblogs.com/tinywan/p/6625432.html

https://www.awaimai.com/371.html