43 | 要不要使用分区表?

一、分区表是什么?

图 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时用不上这种方式。

©著作权归作者所有,转载或内容合作请联系作者
平台声明:文章内容(如有图片或视频亦包括在内)由作者上传并发布,文章内容仅代表作者本人观点,简书系信息发布平台,仅提供信息存储服务。

推荐阅读更多精彩内容

  • 今天看到一位朋友写的mysql笔记总结,觉得写的很详细很用心,这里转载一下,供大家参考下,也希望大家能关注他原文地...
    信仰与初衷阅读 4,760评论 0 30
  • 一、MySQL优化 MySQL优化从哪些方面入手: (1)存储层(数据) 构建良好的数据结构。可以大大的提升我们S...
    宠辱不惊丶岁月静好阅读 2,490评论 1 8
  • 创建Canvas的方式 基本绘制方法 Region.OP参数,和path的布尔值操作类似
    ccccccal阅读 379评论 0 1
  • 感賞早上看了阿拉丁频率提高,恢复力量,更高频率的想法对应更好的感觉
    可可可鑫阅读 316评论 0 0
  • 早晨的北京下着淅淅沥沥的小雨,这是不经常的状况。送完大女儿上学后回到小区楼下接着送小女去幼儿园。为了节省时间这...
    丰腴的生活阅读 196评论 1 0