MySQL分区表

标签: Mysql  mysql

表初始化

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);
  1. 在表t中初始化插入两行记录,按照分区规则,分别落在p_2018和p_2019两个分区上
  2. 包含4个ibd文件,每个分区对应一个ibd文件
    1. 对于Server层来说,只是1个表
    2. 对于引擎层来说,这是4个表

引擎层行为

InnoDB

session A session B
BEGIN;SELECT * FROM t WHERE ftime=’2017-05-01’ FOR UPDATE;
NSERT INTO t VALUES (‘2018-02-01’,1);(Query OK)INSERT INTO t VALUES (‘2017-12-01’,1);(Blocked)
mysql> SELECT locked_index,locked_type,waiting_lock_mode,blocking_lock_mode FROM sys.innodb_lock_waits WHERE locked_table='`test`.`t`';
+--------------+-------------+-------------------+--------------------+
| locked_index | locked_type | waiting_lock_mode | blocking_lock_mode |
+--------------+-------------+-------------------+--------------------+
| ftime        | RECORD      | X                 | X                  |
+--------------+-------------+-------------------+--------------------+


mysql> SHOW ENGINE INNODB STATUS\G;
INSERT INTO t VALUES ('2017-12-01',1)
------- TRX HAS BEEN WAITING 49 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 104 page no 5 n bits 72 index ftime of table `test`.`t` /* Partition `p_2018` */ trx id 7417349 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

对于普通表,session A持有的锁为ftime:Next-Key Lock:(‘2017-4-1’,‘2018-4-1’]
在这里插入图片描述
但对于引擎来说,分区表的分区是不同的表,即2017-4-1的下一个记录是p_2018分区的supremum

在这里插入图片描述

MyISAM

SessionA SessionB SessionC
ALTER TABLE t ENGINE=MyISAM;(MySQL 5.7)
UPDATE t SET c=SLEEP(100) WHERE ftime=’2017-04-01’;
SELECT * FROM t WHERE ftime=’2018-4-1’;(Query OK)
SELECT * FROM t WHERE ftime=’2017-5-1’;(Blocked) SHOW PROCESSLIST;
mysql> SHOW PROCESSLIST;
+----+------+-----------+------+---------+------+------------------------------+----------------------------------------------------+
| Id | User | Host      | db   | Command | Time | State                        | Info                                               |
+----+------+-----------+------+---------+------+------------------------------+----------------------------------------------------+
|  2 | root | localhost | test | Query   |   49 | User sleep                   | UPDATE t SET c=SLEEP(100) WHERE ftime='2017-04-01' |
|  3 | root | localhost | test | Query   |   27 | Waiting for table level lock | SELECT * FROM t WHERE ftime='2017-5-1'             |
|  4 | root | localhost | test | Query   |    0 | starting                     | SHOW PROCESSLIST                                   |
+----+------+-----------+------+---------+------+------------------------------+----------------------------------------------------+

  1. 对于MyISAM引擎来说,分区表是4个表
  2. MyISAM只支持表锁,MyISAM的表锁是在引擎层实现的,session A加的表锁,其实是锁在分区p_2018上

手工分表 VS 分区表

  1. 手工分表的逻辑,找到所有需要更新的分表,然后依次更新,在性能上,与分区表并没有实质的差别
  2. 分区表由Server层决定使用哪个分区,手工分表由应用代码决定使用哪一个分表

分区策略

  1. 每当第一次访问一个分区表的时候,MySQL需要把所有的分区都访问一遍
    1. 如果一个分区表的分区很多,比如超过了1000个
    2. 在MySQL启动时,如果需要打开的文件超过了open_files_limit,就会报错
    3. 实际只需要访问一个分区,但语句却无法执行(MyISAM才会如此,InnoDB采用本地分区策略)

MyISAM

  1. MyISAM分区表使用的分区策略是通用分区策略(generic partitioning)。 每次访问分区都由Server层控制
  2. 通用分区策略是MySQL一开始支持分区表时就存在的代码
    1. 在文件管理和表管理的实现上很粗糙
    2. 同时,还有比较严重的性能问题
  3. MySQL 5.7.17开始,将MyISAM分区表标记为Deprecated
  4. 从MySQL 8.0开始,不再允许创建MyISAM分区表了,只允许创建已经实现了本地分区策略的引擎。 目前只有InnoDB引擎和NDB引擎支持本地分区策略

InnoDB

从MySQL 5.7.9开始,InnoDB引擎引入了本地分区表(native partitioning)。在InnoDB内部自己管理打开分区的行为

Server层行为

从Server层来看,一个分区表就只是一个表

SessionA SessionB sessionC
BEGIN;SELECT * FROM t WHERE ftime=’2018-04-01’;
ALTER TABLE t TRUNCATE PARTITION p_2017;(Blocked)
SHOW PROCESSLIST;
-- session A持有整个表的MDL锁,导致session B被堵住
mysql> SHOW PROCESSLIST;
+----+-----------------+-----------+------+---------+--------+---------------------------------+-----------------------------------------+
| Id | User            | Host      | db   | Command | Time   | State                           | Info                                    |
+----+-----------------+-----------+------+---------+--------+---------------------------------+-----------------------------------------+
|  4 | event_scheduler | localhost | NULL | Daemon  | 137019 | Waiting on empty queue          | NULL                                    |
| 24 | root            | localhost | test | Sleep   |    126 |                                 | NULL                                    |
| 25 | root            | localhost | test | Query   |      0 | starting                        | SHOW PROCESSLIST                        |
| 26 | root            | localhost | test | Query   |      3 | Waiting for table metadata lock | ALTER TABLE t TRUNCATE PARTITION p_2017 |
+----+-----------------+-----------+------+---------+--------+---------------------------------+-----------------------------------------+

小结

  1. MySQL在第一次打开分区表的时候,需要访问所有的分区
  2. 在Server层,认为是同一张表,因此所有分区共用同一个MDL锁
  3. 在引擎层,认为是不同的表,因此拿到MDL锁之后,根据分区规则,只访问必要的分区
    1. 必要的分区需要根据SQL语句中的WHERE条件和分区规则来实现
    2. WHERE ftime=‘2018-4-1’,必要分区是p_2019分区
    3. WHERE ftime>=‘2018-4-1’,必要分区是p_2019分区和p_others分区
    4. 如果查询语句的WHERE条件没有分区Key,就只能访问所有分区了

优势

  1. 对业务透明_,方便清理历史数据_
  2. DROP TABLE t DROP PARTITION,与DELETE语句删除数据相比,速度更快,对系统影响小

PS:参考 mysql45讲

版权声明:本文为u013246898原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/u013246898/article/details/107474573