mysql分区是什么东西

甜甜的杏仁

于 2022-01-15 14:11:35 发布

阅读量355

点赞数 1

文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/bobshui/article/details/122509495

版权

1.MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

引入

最近刚被交接了一个用于记录日志的mysql数据库（具体做啥的其实俺也不清楚），便收到了db管理员亲切的问候，“喂，你们数据库磁盘使用80%了，快处理！”。我心头一惊，急问同事以前是如何处理的，同事微微一笑，“我们都是手动删一下数据就行了”，旋即交给我一行命令

alter table log01 drop partition p20190902;

于是乎，我到机房一通操作，删了一大批数据，db管理员看着24%的磁盘使用，笑着对我竖起了大拇指。我也和他相视一笑，一切尽在不言中。事情告一段落，但某天午夜梦回，突然意识到，其实我根本不知道partition是什么，有什么用。于是挑灯夜战，四处百度，再CV大法，做个记录。

概述

当遇到大数据量数据时，单表已经很难覆盖，我们通常会考虑分库或者分表，但分区，也不失为一种解决方案。

分区的原理也很简单弱智，我们以InnoDB为例，普通的表数据，会存储在.idb文件中，分区则是根据一定的分区逻辑，将同一表中不同行的记录分配到不同的物理文件中，几个分区就有几个.idb文件。如果搜索条件包含了分区键，sql会自动根据分区逻辑，仅去对应的分区文件中查找结果。如果没有包含，那对不起，我只好去所有文件里面找一圈，和遍历多张表不会有本质区别。

设置好分区后，我们可以使用explain partitons select xxxxx语句来查看一句select语句走了几个分区。示例如下：

mysql> explain partitions select * from TxnList where startTime>'2016-08-25 00:00:00' and startTime<'2016-08-25 23:59:00';  
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+  
| id | select_type | table             | partitions | type | possible_keys | key  | key_len | ref  | rows  | Extra       |  
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+  
|  1 | SIMPLE      | ClientActionTrack | p20160825  | ALL  | NULL          | NULL | NULL    | NULL | 33868 | Using where |  
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+  
row in set (0.00 sec)

其他一些常见的分区命令如下：

alter table xxxxxxx add partition (partition p0 values less than(1991));  //添加分区
alter table xxxxxxx drop partition p0; //删除分区
alter table xxxxxx truncate partition p1,p2; 
或 alter table xxxxxx truncate partition all; 
或 delete from xxxxxx where separated < '2006-01-01' or (separated >= '2006-01-01' and separated<'2011-01-01'); //删除分区数据

分区的分类

目前MySQL支持范围分区（RANGE），列表分区（LIST），哈希分区（HASH）以及KEY分区四种。如果表存在主键或者唯一索引时，分区列必须是唯一索引的一个组成部分。实战十有八九都是用RANGE分区。

RANGE分区

　　RANGE分区是实战最常用的一种分区类型，行数据基于属于一个给定的连续区间的列值被放入分区。但是记住，当插入的数据不在一个分区中定义的值的时候，会抛异常。RANGE分区主要用于日期列的分区，比如交易表啊，销售表啊等。可以根据年月来存放数据。如果你分区走的唯一索引中date类型的数据，那么注意了，优化器只能对YEAR()，TO_DAYS()，TO_SECONDS()，UNIX_TIMESTAMP()这类函数进行优化选择。

CREATE TABLE `m_test_db`.`Order` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `partition_key` INT NOT NULL,
  `amt` DECIMAL(5) NULL,
  PRIMARY KEY (`id`, `partition_key`)) PARTITION BY RANGE(partition_key) PARTITIONS 5( PARTITION part0 VALUES LESS THAN (201901),  PARTITION part1 VALUES LESS THAN (201902),  PARTITION part2 VALUES LESS THAN (201903),  PARTITION part3 VALUES LESS THAN (201904),  PARTITION part4 VALUES LESS THAN (201905)) ;

INSERT INTO `m_test_db`.`Order` (`id`, `partition_key`, `amt`) VALUES ('1', '201901', '1000');
INSERT INTO `m_test_db`.`Order` (`id`, `partition_key`, `amt`) VALUES ('2', '201902', '800');
INSERT INTO `m_test_db`.`Order` (`id`, `partition_key`, `amt`) VALUES ('3', '201903', '1200');

现在我们查询一下，通过EXPLAIN PARTITION命令发现SQL优化器只需搜对应的区，不会搜索所有分区