分库分表以及分区

为什么要对数据库进行分库分表？

答： 当单表的数据量达到1000W或100G以后，优化索引、添加从库等可能对数据库性能提升效果不明显，此时就要考虑对其进行切分了。切分的目的就在于减少数据库的负担，缩短查询的时间。

数据库的大表怎么优化

1，限定数据范围，

比如：用户在查询历史信息的时候，可以控制在一个月的时间范围内

2，数据库的主从复制，读写分离

也称读写分离：经典的数据库拆分方案，主库负责写，从库负责读；

3，分库分表 - 对数据库进行水平拆分和垂直拆分

3.1 垂直拆分：

垂直划分数据库是根据业务进行划分的，
有两种情况，

1. 一是将数据库中的不同数据表放到不同的数据库中，
   
   e.g. 例如购物场景，可以将库中涉及商品、订单、用户的表分别划分出成一个库，通过降低单库的大小来提高性能。
   
2. 二是把数据库的一张大表中不同的业务属性/字段，分成一个个子表，然后放到不同的数据库中，
   e.g. 例如商品基本信息和商品描述，商品基本信息一般会展示在商品列表，商品描述在商品详情页，可以将商品基本信息和商品描述拆分成两张表。

优点： 每一行的行记录变小，数据页可以存放更多记录，在查询时减少I/O次数。

缺点：

• 主键出现冗余，需要管理冗余列(每一个子表中都需要添加一个主键以唯一标识数据)；
• 会引起表连接JOIN操作（跨节点join逻辑复杂，性能差），可以通过在业务服务器上进行join来减少数据库压力；
• 依然存在单表数据量过大（是指数据行数或者记录数）的问题。

3.2 水平拆分

数据水平拆分就是把同一个表中的数据行拆分成不同的数据块，每一个数据块的结构一致，然后将他们存入到两个甚至多个数据库中。

优点：

• 单库（表）的数据量得以减少，提高性能；
• 切分出的表结构相同，程序改动较少。

缺点：

• 分片事务一致性难以解决
• 跨节点join性能差，逻辑复杂
• 数据分片在扩容时需要先进行数据迁移

4 水平分区（不推荐）

高可用的定义：高可用HA（High Availability）是分布式系统架构设计中必须考虑的因素之一，它通常是指，通过设计减少系统不能提供服务的时间。

4.1 定义

1，分区的过程是将一个表或索引分解为多个更小、更可管理的部分。分区的目的主要在于数据库高可用性的管理。

2，分区表是一个独立的逻辑表，但是底层由多个物理子表组成。

3，当查询条件的数据分布在某一个分区的时候，查询引擎只会去某一个分区查询，而不是遍历整个表。在管理层面，如果需要删除某一个分区的数据，只需要删除对应的分区即可。

4.2 分区的类型

1. RANGE分区是实战最常用的一种分区类型，行数据基于属于一个给定的连续区间的列值被放入分区。但是记住，当插入的数据不在一个分区中定义的值的时候，会抛异常。RANGE分区主要用于日期列的分区，比如交易表啊，销售表啊等。可以根据年月来存放数据。如果你分区走的唯一索引中date类型的数据，那么注意了，优化器只能对YEAR()，TO_DAYS()，TO_SECONDS()，UNIX_TIMESTAMP()这类函数进行优化选择。实战中可以用int类型，那么只用存yyyyMM就好了。也不用关心函数了。

创建分区代码如下：

CREATE TABLE `m_test_db`.`Order` ( 
`id` INT NOT NULL AUTO_INCREMENT,  
`partition_key` INT NOT NULL,  
`amt` DECIMAL(5) NULL,  
PRIMARY KEY (`id`, `partition_key`))  
PARTITION BY RANGE(partition_key)   
PARTITIONS 5(    PARTITION part0 VALUES LESS THAN (201901),PARTITION part1 VALUES LESS THAN (201902),
PARTITION part2 VALUES LESS THAN (201903), 
PARTITION part3 VALUES LESS THAN (201904), 
PARTITION part4 VALUES LESS THAN (201905)) ;

 
 

这时候我们先插入一些数据

INSERT INTO `m_test_db`.`Order` (`id`, `partition_key`, `amt`) VALUES ('1', '201901', '1000');

INSERT INTO `m_test_db`.`Order` (`id`, `partition_key`, `amt`) VALUES ('2', '201902', '800');

INSERT INTO `m_test_db`.`Order` (`id`, `partition_key`, `amt`) VALUES ('3', '201903', '1200');

现在我们查询一下，通过EXPLAIN PARTITION命令发现SQL优化器只需搜对应的区，不会搜索所有分区

如果sql语句有问题，那么会走所有区。会很危险。所以分区表后，select语句必须走分区键。

2. LIST分区
   LIST分区和RANGE分区很相似，只是分区列的值是离散的，不是连续的。
   对于List分区，分区字段必须是已知的，如果插入的字段不在分区时枚举值中，将无法插入。

create table test_list_partiotion
   (
       id int auto_increment,
       data_type tinyint,
       primary key(id,data_type)
   )partition by list(data_type)
   (
       partition p0 values in (0,1,2,3,4,5,6),
       partition p1 values in (7,8,9,10,11,12),
       partition p2 values in (13,14,15,16,17)
   );

3. HASH分区
   说到哈希，那么目的很明显了，将数据均匀的分布到预先定义的各个分区中，保证每个分区的数量大致相同。

create table test_hash_partiotion
   (
       id int auto_increment,
       create_date datetime,
       primary key(id,create_date)
   )partition by hash(year(create_date)) partitions 10;

4. KEY分区
   KEY分区和HASH分区相似，不同之处在于HASH分区使用用户定义的函数进行分区，KEY分区使用数据库提供的函数进行分区。

为什么在提高数据库的查询速度的情况下，不推荐使用分区表而推荐分库分表？

分区表看上去很帅气，为什么大部分互联网公司不使用，而更多的选择分库分表来进行水平切分呢？
分区表的一些缺点，是大数据量，高并发量的业务难以接受的：

（1）如果SQL不走分区键，很容易出现全表锁；

（2）在分区表实施多表关联查询，性能很差（因为可能涉及多个分区，还有可能触发多个表的表锁）；

（3）分库分表，自己掌控业务场景与访问模式，可控；分区表，工程师写了一个SQL，自己无法确定MySQL是怎么玩的，不可控；

应用场景

一般在并发量不高的情况下或者将数据按照日期范围归档时（此时可能会使用range范围分区）或处理冷热数据时，可以接受使用mysql分区