分库分表

分库分表

简述MySQL分表操作和分区操作的工作原理，分别说说分区和分表的使用场景和各自的优缺点。

为什么要分表和分区？

日常开发中我们经常会遇到大表的情况，所谓的大表是指存储了百万级乃至千万级条记录的表。这样的表过于庞大，导致数据库在查询和插入的时候耗时太长，性能低下，如果涉及联合查询的情况，性能会更加糟糕。分表和表分区的目的就是减少数据库的负担，提高数据库的效率，通常点来讲就是提高表的增删改查效率。

什么是分表？

分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，我们可以称为子表，每个表都对应三个文件，MYD数据文件，.MYI索引文件，.frm表结构文件。这些子表可以分布在同一块磁盘上，也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的子表名，然后去操作它。

什么是分区？

分区和分表相似，都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表，而分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。分区后，表面上还是一张表，但数据散列到多个位置了。app读写的时候操作的还是大表名字，db自动去组织分区的数据

mysql分表和分区有什么区别
1.实现方式上
a）mysql的分表是真正的分表，一张表分成很多表后，每一个小表都是完正的一张表，都对应三个文件，一个.MYD数据文件， .MYI索引文件，.frm表结构文件。 简单说明一下，上面的分表呢是利用了merge存储引擎（分表的一种），alluser是总表，下面有二个分表，user1，user2。他们二个都是独立的表，取数据的时候，我们可以通过总表来取。这里总表是没有.MYD,.MYI这二个文件的，也就是说， 总表他不是一张表，没有数据，数据都放在分表里面。我们来看看.MRG到底是什么东西 alluser.MRG里面就存了一些分表的关系，以及插入数据的方式。可以把总表理解成一个外壳，或者是联接池。

注释:
针对每个不同的存储引擎（engine）,每个表的文件形式不同。mysql每个表其实以文件的形式存放在磁盘上的。
如果是myisam的表：表名.frm:存放表结构的文件; 表名.MYD:存放表数据的文件; 表名.MYI:存放表索引的文件。
如果是innodb的表：表名.frm:存放表结构；表数据和索引放在一个文件中：ibdata

b）分区不一样，一张大表进行分区后，他还是一张表，不会变成二张表，但是他存放数据的区块变多了，分区后，还是一张，而不是多张表。

2.数据处理上
a）分表后，数据都是存放在分表里，总表只是一个外壳，存取数据发生在一个一个的分表里面。看下面的例子： select * from alluser where id='12’表面上看，是对表alluser进行操作的，其实不是的。是对alluser里面的分表进行了操作。
b）分区呢，不存在分表的概念，分区只不过把存放数据的文件分成了许多小块，分区后的表呢，还是一张表。数据处理还是由自己来完成。

3.提高性能上
a）分表后，单表的并发能力提高了，磁盘I/O性能也提高了。并发能力为什么提高了呢，因为查寻一次所花的时间变短了，如果出现高并发的话，总表可以根据不同的查询，将并发压力分到不同的小表里面。磁盘I/O性能怎么搞高了呢，本来一个非常大的.MYD文件现在也分摊到各个小表的.MYD中去了。
b）mysql提出了分区的概念，我觉得就想突破磁盘I/O瓶颈，想提高磁盘的读写能力，来增加mysql性能。 分表重点是存取数据时，如何提高mysql并发能力上；而分区呢，如何突破磁盘的读写能力，从而达到提高mysql性能的目的。

4.实现的难易度上
a）分表的方法有很多，用merge来分表，是最简单的一种方式。这种方式根分区难易度差不多，并且对程序代码来说可以做到透明的。如果是用其他分表方式就比分区麻烦了。
b）分区实现是比较简单的，建立分区表，根建平常的表没什么区别，并且对开代码端来说是透明的。

1 分区表的原理

工作原理

对用户而言，分区表是一个独立的逻辑表，但是底层 MYSQL将其分成了多个物理子表，这对用户来说是透明的，每一个分区表都会使用一个独立的表文件。

创建表时使用 partition by子句定义每个分区存放的数据，执行查询时，优化器会根据分区定义过滤那些没有我们需要数据的分区，这样查询只需要查询所需数据在的分区。

分区的主要目的是将数据按照一个较粗的粒度分在不同的表中，这样可以将相关的数据存放在一起，而且如果想一次性删除整个分区的数据也很方便。

使用场景

表非常大，无法全部存在内存，或者只在表的最后有热点数据其他都是历史数据
分区表的数据更易维护,可以对独立的分区进行独立的操作
分区表的数据可以分布在不同的机器上,从而高效使用资源
可以使用分区表来避免某些特殊的瓶颈
备份和恢复独立的分区

限制

一个表最多只能有1024个分区
MySQL 5.1 版本中，分区表表达式必须是整数，5.5可以使用列分区
分区字段中如果有主键和唯一索引列，那么主键列和唯一索引列都必须包含进来；
分区表中无法使用外键约束
需要对现有表的结构进行修改
所有分区都必须使用相同的存储引擎
分区函数中可以使用的函数和表达式会有一些限制
某些存储引擎不支持分区
对于MyISAM的分区表，不能使用load index into cache
对于MyISAM表，使用分区表时需要打开更多的文件描述符

2 分库分表的原理

工作原理

通过一些hash算法或工具实现将一张数据表垂直或者水平进行物理分割。

使用场景

单条记录条数达到百万到千万级别
解决表锁的问题

分表方式

水平分割

表很大，分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询速度

水平分割

使用场景

表中的数据本身就有独立性，例如表中分别记录各个地区的数据或者不同时期的数据,特别是有些数据常用，有些不常用
需要把数据存放在多个介质上
水平分表缺点

给应用增加复杂度，通常查询时需要多个表名，查询所有数据都需 UNION操作
在许多数据库应用中，这种复杂性会超过它带来的优点，查询时会增加读一个索引层的磁盘次数
垂直分割
把主键和一些列放在一个表，然后把主键和另外的列放在另一张表中。

垂直分割

使用场景

如果一个表中某些列常用，而另外一些列不常用
可以使数据行变小，一个数据页能存储更多数据，查询时减少I/O次数
垂直分表缺点

管理冗余列，查询所有数据需要join操作

分表的缺点

有些分表的策略基于应用层的逻辑算法，一旦逻辑算法改变，整个分表逻辑都会改变，扩展性较差
对于应用层来说,逻辑算法无疑增加开发成本

3 延伸：MySQL的主从复制原理及负载均衡

MySQL的主从复制原理

在主库上把数据库上把更改记录到二进制日志
从库将主库的日志复制到自己的中继日志
从库读取中继日志中的事件，将其重放在从库数据库中

主从复制解决的问题

数据分布：随意停止或开始复制，并在不同地理位置分布数据备份
负载均衡：降低单个服务器的压力
高可用和故障切换：帮助应用程序避免单点失败
升级测试：可以使用更高版本的 MYSQL作为从库