在线DDL,分库分表分区

online ddl
ddl不是对某一个数据行进行操作，是对整个表进行操作，或者整个表的所有的行、列。
ddl往往意味着很多坑。坑往往是灾难性的。

• 1.整个表的操作
• 2.ddl往往意味着会短暂的加上 exclusive锁（排他锁）或shared锁
• 3.产生海量的IO、长时间的锁

如何对ddl风险测试？

• 1.要意识到这是一个ddl
• 2.确认这个ddl是否会产生大量的IO。
• 在测试系统里面，建立一张大表，执行ddl。观察时间、iostat。如果时间长、合并读或者合并写非常大，则说明产生了很大的ddl操作。

---

alter table t1 add column d6 varchar(10),algorithm in place,lock=none#给表t1加一个列 

DDL的两种方式：

• ①inplace方式：（建议）。在表上加列的时候，分裂出来，在原来的位置上操作,搁不下时，再加数据块。
• ②copy方式：新建另一个表同时增加列，然后把原来所有的数据读出来，插入到新表。（意味着海量的io）

lock意味着：

• ①lock=none表示对表不加锁。
• ②shared 共享锁
• ③exclusive 排他锁

online中lock=none并不是完全的none！在增量应用期间，也会产生shared或者exclusive锁！

mysql5.5以前，
shared：建索引会触发；
exclusive：添加列，删除列会触发；
即：

• 5.5版本不支持online ddl！
• 5.6版本开始支持在线dll;lock可以等于none
• 5.7继续支持。

---

（注：oracle不支持在线ddl）
online ddl小结：

• ①尽量in place
• ②尽量lock=none
• ③对于ddl期间产生海量io的ddl操作来说，即使支持online dll，生产期间尽量不要做！

---

开发经常会对表进行ddl，如何规范ddl操作，写出一个规范的流程。

• ①提交ddl需求给DBA。限制开发人员对数据库的权限（grant）。
• ②DBA测试：io测试、时间测试、是否支持in place和none，显式的加上lock none
• ③DBA形成操作规范和风向评估报告，走流程审批。
• ④进行相关的操作。

---

分区表
将一个大表分成多个分区，对一个分区进行ddl，便于管理。
分区表四大好处：

• ①还是一个表名字，不影响SQL
• ②每一个分区都有自己的名字，可以独立于各分区进行操作
• ③便于数据的归档（把对应的分区置换出去）
• ④提升性能

分区类型：范围分区、列表分区、哈希分区、key分区、子分区等
（一）范围分区

• ①指定分区列；
• ②指定分区范围；
• ③分区范围是小于；
• ④最好指定maxvalue列；
• ⑤最长使用的列：数字、日期

例子1：以年和天分区的表，且带上maxvalue：

create table employeed (id int not null,fname varchar(30),lname varchar(30),hired date not null default '1970-01-01',separated date not null default '9999-12-31',job_code int,store_id int)partition by range (year(separated))  (parttion p0 values less than (1991),    #小于1991年的parttion p1 values less than (1996),    #小于1996年的parttion p2 values less than (2001),    #2001年以前的parttion p3 values less than maxvalue   #防止出现数值已经涨到最大，但是管理员还没有增加新的分区而导致的错误！);#建立了一个表，这个表有四个分区。

最常使用的分区：

• 1、范围分区
• 2、日期分区：年、季度、天

范围分区的例子：

---

（二）列表分区
对区域进行分区。
没有default分区。
（oracle里，当插入的数据不在分区内时，就默认存储到default分区）

新版本里面，尽量使用range columns和list columns分区替换range和list columns，好处：

• 1.分区支持的数据类型更多；
• 2.range columns支持多个列进行分区
  
  分区一般不会出现在多个列上建立分区的情况。

（三）哈希分区

• ①分区列没有含义
• ②表的分区个数一般是2的n次方
• ③数据均匀分布

哈希分区例子：

直接就建立了4个分区

（四）key分区
其实就是，在 主键列上做hash分区

create table k1 (id int not null primary key,name varchar(20))partition by key()partitions 2;

按常用度排序：
范围
列表
hash
key
5.6版本子分区只支持hash分区
列表hash

---

（五）子分区
先按年分了四个分区，然后找到日期列，每个分区再按日期列分成8个子分区。

子分区的一个特点：
将不同的分区放到不同的磁盘上。但是一般不怎么使用子分区，因为都用raid卡了。

---

分区对NULL值的处理

• 1.范围分区中
  
  • null会被分配到最小分区中
• 2.列表分区
  
  • 必须建立独立的null分区，不然会报错
• 3.hash分区
  
  • null会被处理成0

怎么查询分区信息？

select table_name,partition_name,table_rows,avg_row_length,data_lengthfrom information_schema.partitionswhere table_schema = 'tpcc1000' and table_name like 'e%';

---

分区的管理

• （1）删除分区

alter table tr drop partition p2；   #删除分区相当于清理数据；删除p2分区

• （2）添加分区

alter table members add partition (partition p3 values less than (2000));#增加新的分区；新加一个2000年的分区

• （3）合并分区

alter table members reorganize partition p0,p1,p2,p3 into (partition m0 values less than (1980),partition m1 values less than (2000));  #将p1,p2,p3,p4 四个分区合并成1980和2000这2个分区

注意：hash分区的合并操作，使用比较少！

• （4）重排分区

alter table tt reorganize partition p1,np into (partition p1 values in (6,18),partition np values in (4,8,12));

• （5）置换分区(操作起来很快)
  【利用exchange功能常常来 实现历史数据的归档！】

例：已经有了一个employees表，我们再仿着它新建一个：

create table t as select * from employees where 1=2;

置换出去分区：

alter table employees exchange partition p0 with table t;

（原表里已经没有了，而在新表里是有的）

optimize table t;   #对稀疏表做碎片整理

---

• ①删除分区：范围分区、列表分区。
• ②增加分区：范围分区、列表分区。#常见
• ③合并分区：范围分区、列表分区。
• ④拆分分区：范围分区、列表分区。#常见
• ⑤重排分区：范围分区、列表分区。原来按两年两年的分区，现在要改成一天一天的分区了。

---

分区级别进行的管理操作： #意义不是很大

• ①analyze #分析
• ②truncate #截断

---

分区消除

explain partitions select * from employees where store_id=6;

（可以看到，全表扫描的是p1分区！）

分区消除的特点

• ①省了一个索引，可以有效使用其他索引；
• ②分区消除是访问一个表相对大量数据的最有效的方式之一； 根据主键，可以取任何数量的数据； 二级索引：<5% 根据分区，可以取任何数量的数据
• ③实现分区消除的前提是select、update、delete中的where条件中有分区条件。

---

手工指定分区
手工指定分区来避免没有实现自动分区消除。强行优化的一个手法。

select * from employees partition (p2);

---

分库分表机构设计
唯品会的分区分表 优化案例

一主多从结构致命弱点。

• ①主库存储压力增加
• ②主库写入压力增加
• ③主库成为单点故障
• ④tps有上限
• ⑤单表容量有上限

唯品会的订单库使用场合：

• ①下订单的时候
• ②查询订单：发货、查询物流

随着唯品会业务的快速发展，订单量的不断增长，原有的订单存储架构已经不能满足公司的发展了，特别是在大促高峰期，原订单库已经成为抢购瓶颈，已经严重制约公司的发展。
唯品会旧订单库包含几十张订单相关表，旧订单库是典型的一主多从架构；主库容量已接近服务器物理空间上限，同时也已经达到MySQL的处理上限，很快将无法再处理新增订单。
旧订单库面临的问题有：

• 1、超大容量问题

  ● 订单相关表都已经是超大表，最大表的数据量已经是几十亿，数据库处理能力已经到了极限；
  ● 单库包含多个超大表，占用的硬盘空间已经接近了服务器的硬盘极限，很快将无空间可用；

• 2、性能问题

单一服务器处理能力是有限的，单一订单库的TPS也有上限，不管如何优化，总会有达到上限，这限制了单位时间的订单处理能力，这个问题在大促时更加明显，如果不重构，订单达到一定量以后，就无法再继续增长，严重影响到用户体验。

• 3、升级扩展问题

  ● 单一主库无法灵活的进行升级和扩展，无法满足公司快速发展要求；
  ● 所有的订单数据都放在同一库里面，存在单点故障的风险；
  综上所述，容量、性能问题是急需解决的问题，扩展是为了将来3~5年内能够很好的满足唯品会快速发展的需要，而不需要每隔几个月花费人力物力去考虑扩容等问题。

---

解决上述问题的方法？–>拆分！
1.垂直拆分：

• ①拆库（将不同用途的表分拆到不同的服务器上）；
• ②拆表（将一个列很多的表，分拆成两个表，经常访问的列放在一个表中，不经常访问的列放在另外一个表中，两个表都需要有相同的主键！）

2.水平拆分：

• ①拆库
• ②拆表

3.垂直水平拆分（最常用）

---

解决方法思考

• 1、解决容量问题
  我们可以考虑到最直接的方式是增加大容量硬盘，或者对IO有更高要求，还可以考虑增加SSD硬盘来解决容量的问题。此方法无法解决单表数据量问题。
  可以对数据表历史数据进行归档，但也需要频繁进行归档操作，而且不能解决性能问题。
• 2、解决性能问题
  提高数据库服务器的配置，这个可以提升一定数量的QPS和TPS，但仍然不能解决单服务器连接数、IO读写存在上限的问题，此方法仍然存在单点故障的问题。

拆分方法探讨

常见的数据库拆分方式有三种：垂直拆分、水平拆分、垂直水平拆分。

• 1、垂直拆分

垂直拆库是根据数据库里面的数据表的相关性进行拆分，比如：一个数据库里面既存在用户数据，又存在订单数据，那么垂直拆分可以把用户数据放到用户库、把订单数据放到订单库。如下图：

垂直拆表是对数据表进行垂直拆分的一种方式，常见的是把一个多字段的大表按常用字段和非常用字段进行拆分，每个表里面的数据记录数一般情况下是相同的，只是字段不一样，使用主键关联，如下图：

• 2、水平拆分

水平拆分是把单表按某个规则把数据分散到多个表的拆分方式，比如：把单表1亿数据按某个规则拆分，分别存储到10个相同结果的表，每个表的数据是1千万，拆分出来的表，可以分别放至到不同数据库中，即同时进行水平拆库操作，如下图：

水平拆分可以降低单表数据量，让每个单表的数据量保持在一定范围内，从而提升单表读写性能。但水平拆分后，同一业务数据分布在不同的表或库中，可能需要把单表事务改成跨表事务，需要转变数据统计方式等。

• 3、垂直水平拆分

垂直水平拆分，是综合了垂直和水平拆分方式的一种混合方式，垂直拆分把不同类型的数据存储到不同库中，再结合水平拆分，使单表数据量保持在合理范围内，提升总TPS，提升性能，如下图：
垂直拆分策略
原订单库把所有订单相关的数据（订单销售、订单售后、订单任务处理等数据）都放在同一数据库中，不符合电商系统分层设计，对于订单销售数据，性能第 一，需要能够在大促高峰承受每分钟几万到几十万订单的压力；而售后数据，是在订单生成以后，用于订单物流、订单客服等，性能压力不明显，只要保证数据的及 时性即可；所以根据这种情况，把原订单库进行垂直拆分，拆分成订单售后数据、订单销售数据、其他数据等，如下图：

水平拆分策略
垂直拆分从业务上把订单下单数据与下单后处理数据分开，但对于订单销售数据，由于数据量仍然巨大，最大的订单销售相关表达到几十亿的数据量，如果遇 到大型促销（如：店庆128、419、618、双十一等等），数据库TPS达到上限，单销售库单订单表仍然无法满足需求，还需要进一步进行拆分，在这里使用水平拆分策略。
订单分表是首先考虑的，分表的目标是保证每个数据表的数量保持在1000~5000万左右，在这个量级下，数据表的大小与性能是最理想的。
如果几十个分表都放到一个订单库里面，运行于单组服务器上，则受限于单组服务器的处理能力，数据库的TPS有限，所以需要考虑分库，把分表放到分库里面，减轻单库的压力，增加总的订单TPS。

• 1、用户编号HASH切分

使用用户编号哈希取模，根据数据量评估，把单库拆分成n个库，n个库分别存放到m组服务器中，如下图：

每组服务器容纳4个库，如果将来单服务器达到性能、容量等瓶颈，可以直接把数据库水平扩展为2倍服务器集群，还可以继续扩展为4倍服务器集群。水平扩展可以支撑公司在未来3~5年的快速订单增长。
使用用户编号进行 sharding，可以使得创建订单的处理更简单，不需要进行跨库的事务处理，提高下单的性能与成功率。

• 2、订单号索引表

根据用户编号进行哈希分库分表，可以满足创建订单和通过用户编号维度进行查询操作的需求，但是根据统计，按订单号进行查询的占比达到80%以上，所以需要解决通过订单号进行订单的CURD等操作，所以需要建立订单号索引表。
订单号索引表是用于用户编号与订单号的对应关系表，根据订单号进行哈希取模，放到分库里面。根据订单号进行查询时，先查出订单号对应的用户编号，再根据用户编号取模查询去对应的库查询订单数据。
订单号与用户编号的关系在创建订单后是不会更改的，为了进一步提高性能，引入缓存，把订单号与用户编号的关系存放到缓存里面，减少查表操作，提升性能，索引不命中时再去查表，并把查询结果更新到缓存中。

• 3、分布式数据库集群

订单水平分库分表以后，通过用户编号，订单号的查询可以通过上面的方法快速定位到订单数据，但对于其他条件的查询、统计操作，无法简单做到，所以引入分布式数据库中间件。
下图是基本构架：

总结与思考
技术架构与业务场景息息相关，不能脱离实际的业务场景、历史架构、团队能力、数据体量等等去做架构重构，对于一家快速发展的电子商务公司，订单系统是核心，订单库是核心的核心，订单库的重构就像汽车在高速公路上跑着的过程中更换轮胎。
本文是对唯品会订单库重构——采用分库分表策略对原订单库表进行拆分的粗略总结，在订单库重构过程中遇到的问题远远超过这些，比如：历史数据的迁 移、各外围系统的对接等，但这些在公司强大的技术团队面前，最终都顺利的解决，新旧订单库顺利的切换，给公司快速的业务发展提供坚实的保障。
每一天都要行动，在前进中寻求卓越。