百度四川建设厅网站,dw网站大学生代做,wordpress 年度归档,公司设计网站建设分区表
Hi#xff0c;我是阿昌#xff0c;今天学习记录的是关于分区表的内容。
经常被问到这样一个问题#xff1a; 分区表有什么问题#xff0c;为什么公司规范不让使用分区表呢#xff1f; 一、分区表是什么#xff1f;
为了说明分区表的组织形式#xff0c;先创建…分区表
Hi我是阿昌今天学习记录的是关于分区表的内容。
经常被问到这样一个问题 分区表有什么问题为什么公司规范不让使用分区表呢 一、分区表是什么
为了说明分区表的组织形式先创建一个表 t
CREATE TABLE t (ftime datetime NOT NULL,c int(11) DEFAULT NULL,KEY (ftime)
) ENGINEInnoDB DEFAULT CHARSETlatin1
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);在表 t 中初始化插入了两行记录按照定义的分区规则这两行记录分别落在 p_2018 和 p_2019 这两个分区上。
可以看到这个表包含了一个.frm 文件和 4 个.ibd 文件每个分区对应一个.ibd 文件。
也就是说
对于引擎层来说这是 4 个表对于 Server 层来说这是 1 个表。
可能会觉得这两句都是废话。
其实不然这两句话非常重要可以理解分区表的执行逻辑。 二、分区表的引擎层行为
先给举个在分区表加间隙锁的例子目的是说明对于 InnoDB 来说这是 4 个表。 这里顺便回忆一下在加锁规则的一些问题介绍的间隙锁加锁规则。
初始化表 t 的时候只插入了两行数据 ftime 的值分别是‘2017-4-1’ 和’2018-4-1’ 。
session A 的 select 语句对索引 ftime 上这两个记录之间的间隙加了锁。
如果是一个普通表的话那么 T1 时刻在表 t 的 ftime 索引上间隙和加锁状态应该是图 3 这样的。 也就是说‘2017-4-1’ 和’2018-4-1’ 这两个记录之间的间隙是会被锁住的。那么sesion B 的两条插入语句应该都要进入锁等待状态。但是从上面的实验效果可以看出session B 的第一个 insert 语句是可以执行成功的。
这是因为对于引擎来说p_2018 和 p_2019 是两个不同的表也就是说 2017-4-1 的下一个记录并不是 2018-4-1而是 p_2018 分区的 supremum。
所以 T1 时刻在表 t 的 ftime 索引上间隙和加锁的状态其实是图 4 这样的 由于分区表的规则session A 的 select 语句其实只操作了分区 p_2018因此加锁范围就是图 4 中深绿色的部分。
所以session B 要写入一行 ftime 是 2018-2-1 的时候是可以成功的而要写入 2017-12-1 这个记录就要等 session A 的间隙锁。
图 5 就是这时候的 show engine innodb status 的部分结果。 看完 InnoDB 引擎的例子再来一个 MyISAM 分区表的例子。
首先用 alter table t enginemyisam把表 t 改成 MyISAM 表然后再用下面这个例子说明对于 MyISAM 引擎来说这是 4 个表。 在 session A 里面我用 sleep(100) 将这条语句的执行时间设置为 100 秒。由于 MyISAM 引擎只支持表锁所以这条 update 语句会锁住整个表 t 上的读。但看到的结果是session B 的第一条查询语句是可以正常执行的第二条语句才进入锁等待状态。
这正是因为 MyISAM 的表锁是在引擎层实现的session A 加的表锁其实是锁在分区 p_2018 上。
因此只会堵住在这个分区上执行的查询落到其他分区的查询是不受影响的。
可能会说分区表看来还不错嘛为什么不让用呢
使用分区表的一个重要原因就是单表过大。那么如果不使用分区表的话就是要使用手动分表的方式。 接下来一起看看手动分表和分区表有什么区别。
比如按照年份来划分就分别创建普通表 t_2017、t_2018、t_2019 等等。
手工分表的逻辑也是找到需要更新的所有分表然后依次执行更新。在性能上这和分区表并没有实质的差别。
分区表和手工分表一个是由 server 层来决定使用哪个分区一个是由应用层代码来决定使用哪个分表。
因此从引擎层看这两种方式也是没有差别的。其实这两个方案的区别主要是在 server 层上。
从 server 层看我们就不得不提到分区表一个被广为诟病的问题打开表的行为。 三、分区策略
每当第一次访问一个分区表的时候MySQL 需要把所有的分区都访问一遍。
一个典型的报错情况是这样的如果一个分区表的分区很多比如超过了 1000 个而 MySQL 启动的时候open_files_limit 参数使用的是默认值 1024那么就会在访问这个表的时候由于需要打开所有的文件导致打开表文件的个数超过了上限而报错。
下图就是创建的一个包含了很多分区的表 t_myisam执行一条插入语句后报错的情况。 可以看到这条 insert 语句明显只需要访问一个分区但语句却无法执行。
这时你一定从表名猜到了这个表用的是 MyISAM 引擎。是的因为使用 InnoDB 引擎的话并不会出现这个问题。MyISAM 分区表使用的分区策略我们称为通用分区策略generic partitioning每次访问分区都由 server 层控制。
通用分区策略是 MySQL 一开始支持分区表的时候就存在的代码在文件管理、表管理的实现上很粗糙因此有比较严重的性能问题。
从 MySQL 5.7.9 开始InnoDB 引擎引入了本地分区策略native partitioning。这个策略是在 InnoDB 内部自己管理打开分区的行为。MySQL 从 5.7.17 开始将 MyISAM 分区表标记为即将弃用 (deprecated)意思是“从这个版本开始不建议这么使用请使用替代方案。
在将来的版本中会废弃这个功能”。从 MySQL 8.0 版本开始就不允许创建 MyISAM 分区表了只允许创建已经实现了本地分区策略的引擎。
目前来看只有 InnoDB 和 NDB 这两个引擎支持了本地分区策略。 四、分区表的 server 层行为
接下来再看一下分区表在 server 层的行为。
如果从 server 层看的话一个分区表就只是一个表。
这句话是什么意思呢接下来就用下面这个例子来和你说明。
如图 8 和图 9 所示分别是这个例子的操作序列和执行结果图。 可以看到虽然 session B 只需要操作 p_2017 这个分区但是由于 session A 持有整个表 t 的 MDL 锁就导致了 session B 的 alter 语句被堵住。
DBA 同学经常说的分区表在做 DDL 的时候影响会更大。
如果使用的是普通分表那么当在 truncate 一个分表的时候肯定不会跟另外一个分表上的查询语句出现 MDL 锁冲突。
到这里小结一下
MySQL 在第一次打开分区表的时候需要访问所有的分区在 server 层认为这是同一张表因此所有分区共用同一个 MDL 锁在引擎层认为这是不同的表因此 MDL 锁之后的执行过程会根据分区表规则只访问必要的分区。
而关于“必要的分区”的判断就是根据 SQL 语句中的 where 条件结合分区规则来实现的。 比如上面的例子中where ftime‘2018-4-1’根据分区规则 year 函数算出来的值是 2018那么就会落在 p_2019 这个分区。但是如果这个 where 条件改成 where ftime‘2018-4-1’虽然查询结果相同但是这时候根据 where 条件就要访问 p_2019 和 p_others 这两个分区。如果查询语句的 where 条件中没有分区 key那就只能访问所有分区了。
当然这并不是分区表的问题。即使是使用业务分表的方式where 条件中没有使用分表的 key也必须访问所有的分表。
那么什么场景下适合使用分区表呢 五、分区表的应用场景
分区表的一个显而易见的优势是对业务透明相对于用户分表来说使用分区表的业务代码更简洁。
还有分区表可以很方便的清理历史数据。如果一项业务跑的时间足够长往往就会有根据时间删除历史数据的需求。
这时候按照时间分区的分区表就可以直接通过 alter table t drop partition … 这个语法删掉分区从而删掉过期的历史数据。
这个 alter table t drop partition … 操作是直接删除分区文件效果跟 drop 普通表类似。
与使用 delete 语句删除数据相比优势是速度快、对系统影响小。 六、总结
需要注意的是我是以范围分区range为例和你介绍的。实际上MySQL 还支持 hash 分区、list 分区等分区方法。可以在需要用到的时候再翻翻手册。实际使用时分区表跟用户分表比起来有两个绕不开的问题一个是第一次访问的时候需要访问所有分区另一个是共用 MDL 锁。因此如果要使用分区表就不要创建太多的分区。见过一个用户做了按天分区策略然后预先创建了 10 年的分区。
这种情况下访问分区表的性能自然是不好的。这里有两个问题需要注意
分区并不是越细越好。实际上单表或者单分区的数据一千万行只要没有特别大的索引对于现在的硬件能力来说都已经是小表了。分区也不要提前预留太多在使用之前预先创建即可。比如如果是按月分区每年年底时再把下一年度的 12 个新分区创建上即可。对于没有数据的历史分区要及时的 drop 掉。
至于分区表的其他问题比如查询需要跨多个分区取数据查询性能就会比较慢基本上就不是分区表本身的问题而是数据量的问题或者说是使用方式的问题了。
当然如果你的团队已经维护了成熟的分库分表中间件用业务分表对业务开发同学没有额外的复杂性对 DBA 也更直观自然是更好的。 举例的表中没有用到自增主键假设现在要创建一个自增字段 id。 MySQL 要求分区表中的主键必须包含分区字段。 如果要在表 t 的基础上做修改会怎么定义这个表的主键呢为什么这么定义呢 如果不包含分区字段那么对于主键和唯一键都可能冲突。
