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);

mysql> SYSTEM ls /usr/local/var/mysql/test
t#P#p_2017.ibd        t#P#p_2018.ibd		t#P#p_2019.ibd		t#P#p_others.ibd

在表t中初始化插入两行记录，按照分区规则，分别落在p_2018和p_2019两个分区上
包含4个ibd文件，_每个分区对应一个ibd文件_
- 对于Server层来说，只是1个表
- 对于引擎层来说，这是4个表

引擎层行为

InnoDB

session A	session B
BEGIN; SELECT * FROM t WHERE ftime=’2017-05-01’ FOR UPDATE;
	INSERT 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

session A	session B	session C
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                                   |
+----+------+-----------+------+---------+------+------------------------------+----------------------------------------------------+

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

手工分表 VS 分区表

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

分区策略

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

MyISAM

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

InnoDB

从MySQL 5.7.9开始，InnoDB引擎引入了本地分区表（native partitioning）
- 在InnoDB内部自己管理打开分区的行为

Server层行为

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

操作序列

session A	session B	session C
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 |
+----+-----------------+-----------+------+---------+--------+---------------------------------+-----------------------------------------+

小结

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