mysql分区表

怼烎@ 2021-11-10 20:31 450阅读 0赞

## 一、什么是分区表 ？ ##

**1.1 创建一个分区表并写入数据**

CREATE TABLE `t` (
      `ftime` datetime NOT NULL,
      `c` int(11) DEFAULT NULL,
      KEY (`ftime`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    PARTITION BY RANGE (YEAR(ftime))
    (PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
     PARTITION p_2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
     PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
    PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
    
    insert into t values('2017-4-1',1),('2018-4-1',1),('2019-4-1',1);

**1.2 对于mysql 引擎层、server 层的不同表现**  
![在这里插入图片描述][20190803182851169.png]  
可以看到， 这个表包含了一个.frm文件和4个.ibd文件，对应每个分区对应一个.ibd 文件。 也就是说：

*  对于引擎层来说， 这是4个表
 *  对于Server层来说， 这是1个表

**1.3 分区表的引擎层行为**  
**用InnoDB引擎， 会是什么样的情况 ？**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTE4MTQ5_size_16_color_FFFFFF_t_70]  
我们在初始化表t的时候，直插入两行数据，ftime 的值分别是：‘2017-4-1’ 、‘2018-4-1’。session A 的 select 语句对索引 ftime 上这两个记录之间的间隙加了锁。 如果普通表，那么T1时刻，在表t的ftime 索引上， 间隙和加锁状态应该如下图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTE4MTQ5_size_16_color_FFFFFF_t_70 1]  
也就是说，‘2017-4-1’ 、‘2018-4-1’ 两个记录间的间隙是被锁住的。 那么session的两条插入语句应该都要进入锁等待状态。

但，从上面实验效果看出， session B 的第一个insert 语句是可以执行成功的。 这是因为， 对于引擎来说 p\_2018 和 p\_2019 是两个不同的表，也就是说2017-4-1 的下一条记录并不是 2018-4-1， 而是 p\_2018 分区的 supermum。 所以T1时刻，在表的ftime索引上，间隙和加锁的状态如下图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTE4MTQ5_size_16_color_FFFFFF_t_70 2]  
由于分区表的规则， session A 的select 语句其实只操作了 分区 p\_2018,因此加锁范围就是图中深绿色的部分 。  
所以， session B 要写入一行ftime 是 2018-2-1 的时候是可以成功的， 而要写入 2017-12-1 这个记录，就要等session A的间隙锁。  
上面的例子都是基于 InnoDB 引擎的情况，  
**下面看一下 MyISAM 引擎会出现什么情况 ？**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTE4MTQ5_size_16_color_FFFFFF_t_70 3]  
由于MyISAM引擎只支持表锁， 所以这条update 语句会锁住整个表t上的读。  
看上图结果是，session B的第一条查询语句是可以正常执行的， 第二条语句才进入锁等待状态。  
这是因为MyISAM的表锁是在引擎层实现的， session A 加的表锁， 其实是锁在分区 p\_2017 上，因此会堵住在这个分区上的查询， 其它分区查询不受影响。

**1.4 分区表的Server层行为**  
从server层看来，一个分区表就是一个表。  
在server层， 认为这是同一张表， 因此所有分区公用MDL锁；  
接下来， 验证一下：  
![在这里插入图片描述][20190803192543879.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTE4MTQ5_size_16_color_FFFFFF_t_70 4]  
虽然session B只需操作p\_2017这个分区，但由于session A持有整个 表t的 MDL锁， 就导致session B的 alter 语句被堵住。

## 二、分区表适用于什么场景？ ##

*  单表数量太大的时候，可以选用分区表；
 *  如果一项业务跑的时间足够长，往往需要删除历史数据，这时候， 按时间分区的分区表，就可以直接 通过 alter table drop  
    partition … 这语句删除分区，从而删除过期的历史数据；

## 三、为什么公司不推荐使用分区表 ？ ##

每当第一次访问一个分区表的时候，MySql 需要将所有分区访问一遍；  
如果一个分区表的分区很多， 比如超过 1000个，而MySQL 启动的时候，open\_files\_limit 参数是默认值 1024 ，由于访问这表的时候，需要打开所有分区文件，导致这个数量超限而报错；  
分区表，在做DDL的时候，影响会更大；

[20190803182851169.png]: /images/20211109/a7ea25b257ea486caef10f07aa00be42.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTE4MTQ5_size_16_color_FFFFFF_t_70]: /images/20211109/a9bcac01db9142d0b4d852f7e52b67cc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTE4MTQ5_size_16_color_FFFFFF_t_70 1]: /images/20211109/79a307058f0b480f827a2f07075cdfa0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTE4MTQ5_size_16_color_FFFFFF_t_70 2]: /images/20211109/d22dff0d1e6746c29677151c971569b1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTE4MTQ5_size_16_color_FFFFFF_t_70 3]: /images/20211109/573295c819d44e6095b6d2ed3737ecc6.png
[20190803192543879.png]: /images/20211109/d4af64ba65354db589528ef2e99c1634.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTE4MTQ5_size_16_color_FFFFFF_t_70 4]: /images/20211109/b313616d4fd34e5cadd0612683e10952.png