一、分区表是什么？

图 1 表 t 的磁盘文件

插入两行记录，落在 p_2018 和 p_2019 

Server  1 个表，引擎层 4 个表：一个.frm 文件和 4 个.ibd 文件（每个分区对应一个）

二、分区表的引擎层行为

分区表加间隙锁，说明对于 InnoDB 来说，这是 4 个表。

图 2 分区表间隙锁示例

第 21 篇文章间隙锁加锁规则。初始化，ftime 值分别是，‘2017-4-1’ 和’2018-4-1’ 。A 的 select 对索引 ftime 间隙加了锁，普通加锁状态：sesion B 两条insert锁等待

图 3 普通表的加锁范围

分区：第一个 insert 语句是可以执行成功的。p_2018 和 p_2019 是两个不同表，也就是说 2017-4-1下一个记录，是 p_2018 分区 supremum（不是 2018-4-1）。 T1 表 t  ftime 索引上：

图 4 分区表 t 的加锁范围

A 的 select 只操作（深绿色），B 写入2018-2-1 成功，2017-12-1 等 A 间隙锁。

show engine innodb status 

图 5 session B 被锁住信息

MyISAM 分区表的例子。alter table t engine=myisam，对于 MyISAM 引擎来说，这是 4 个表。

图 6 用 MyISAM 表锁验证

A sleep时间设置100 秒。MyISAM 只支持表锁，锁表 t读。引擎层实现，其实是锁 p_2018，其他不受影响的

B 正常执行，第二条锁等待状态。

手动分表和分区表区别

性能没差别：按年份划分，手工分表的逻辑，依次执行更新。

server 层决定用哪个分区，应用层代码决定用哪个分表。没差别。

区别：在 server 层打开表行为。

三、分区策略

分区表过多，超过 1000 个，MySQL 启动时，open_files_limit默认值 1024，访问表时，打开所有的文件，个数超过了上限而报错。只需要访问一个分区，无法执行。

图 7 insert 语句报错

用 InnoDB 引擎的话，不会出现这个问题。

通用分区策略（generic partitioning）：MyISAM 分区策略，server 层控制访问分区。一开始支持分区表存在的代码，文件管理、表管理粗糙，性能问题。

本地分区策略（native partitioning）：InnoDB 内部管理打开分区。只有 InnoDB 和 NDB 支持

四、分区表的 server 层行为

server 层看，只是一个表

图 8 分区表的 MDL 锁  

图 9 show processlist 结果

A 持有整个表 t 的 MDL 锁，B 的 alter 语句被堵住。

分区表， DDL 影响大。普通分表， truncate 分表的时候，不会跟另外一个分表上的查询语句，出现 MDL 锁冲突。

1.  第一次打开分区表时，访问所有分区；

2.   server 层，认为同一张表，所有分区共用 MDL 锁；

3.  引擎层，认为不同表，MDL 锁之后，只访问必要分区。

where 条件改成 where ftime>=‘2018-4-1’，查询结果相同，where 条件，访问 p_2019 和 p_others 这两个分区。

where 没有分区 key，访问所有分区。没有用分表key，访问所有分表。

五、分区表的应用场景

优势，业务透明，业务代码简洁。方便清理历史数据。

alter table t drop partition 直接删除分区文件，跟 drop 普通表类似。与 delete 语句删除数据相比，速度快、影响小。

小结

范围分区（range）为例介绍。还支持 hash 、list 等分区方法。

分区表跟用户分表比起来，两个绕不开的问题：第一次访问的时候需要访问所有分区，共用 MDL 锁。注意：

1. 不是越细越好。单表或者单分区的数据一千万行，是小表。DDL耗时严重

2.  不预留太多（主从延迟），按月分区，年底创建12 个新分区即可。没数据分区drop 掉。

ps：规则预先设置好,后来修改麻烦

思考题

创建自增字段 id。分区表中主键必须包含分区字段。怎么定义表主键呢？为什么？两种：

（1）(ftime, id)：利用率高，用 ftime 做分区 key，大多数语句包含 ftime ，用前缀索引，减少索引。

（2） (id, ftime)

尽量用 InnoDB 引擎。要求至少有一个索引，自增字段作为第一个字段，所以加id单独索引。

 PRIMARY KEY (`id`,`ftime`),

  KEY `id` (`ftime`)也可以

评论1

分区表场景：历史数据表改造,用存储过程创建和改造；后台数据分析汇总,比如日志数据,便于清理

分区表业务采用是hash 用户ID方式,大规模应用分区表没遇到过

评论2

innodb_open_files：限制Innodb打开表数量。InnoDB引擎打开文件超过 innodb_open_files值，会关掉之前打开文件。

open_files_limit：用本地分区策略，即使分区个数大于open_files_limit ，打开InnoDB分区表也不会报“打开文件过多”这个错误，innodb_open_files发挥作用。

评论3

add column after column_name跟add column不指定位置：

性能没差别，尽量加到最后一列（不要after column_name，）好处：

1. 分支可快速加列，瞬间完成

2. 先做备库、切换、再做备库，执行ddl，用after column_name时用不上这种方式。