Mysql分区，分表，主从复制原理@year12

分区表的原理

工作原理：

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

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

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

分区的适用场景：

1、表非常大，无法全部存在内存，或者只在表的最后有热点数据，其他都是历史数据。

2、分区表的数据更易维护，可以对独立的分区进行独立的操作。

3、分区表的数据可以分布在不同的机器上，从而高效使用资源。

4、可以使用分区表来避免某些特殊的瓶颈。

5、可以备份和恢复独立的分区。

分区表的限制：

1、一个表最多只能有1024个分区。

2、5.1版本中，分区表达式必须是整数，5.5可以使用列分区。

3、分区字段中如果有主键和唯一索引列，那么主键和唯一列都必须包含进来。

4、分区表中无法使用外键索引。

5、需要对现有表的结构进行修改。

6、所有分区都必须使用相同的存储引擎。

7、分区函数中可以使用的函数和表达式会有一些限制。

8、某些存储引擎是不支持分区的。

9、对MyISAM的分区表，不能使用load index into cache

10,对于MyISAM表，使用分区表时需要打开更多的文件描述符。（相对会降低一些查询效率）

 

 

 

分库分表的原理

工作原理：

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

适用场景：

1、单表记录数达到百万到千万级别时。

2、解决表锁的问题。

分表缺点：

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

分表方式：

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

使用场景：1、表中的数据本身就有独立性，例如表中分别记录各个地区的数据或者不同时期的数据，特别是有些数据常用，有些不常用。2、需要把数据存放在多个介质上。

水平分表缺点：1、给应用增加了复杂度，通常查询时需要多个表名，查询所有数据都需UNION操作。2、在许多数据库应用中，这种复杂性会超过他带来的优点，查询时会增加读一个索引层的磁盘次数。（从某种意义会降低查询速度）。

二、垂直拆分，把主键和一些列放在一个表，然后把主键和另外的列放在另一个表中。

使用场景：1、如果一个表中的某些列常用，而另外一些列不常用，可以把常用的列单独拆分出来。2、可以使数据行变小，一个数据页能存储更多数据，查询时减少I/O次数。

垂直拆分缺点：管理冗余列，查询所有的数据，需要join操作。

 

 

 

MySQL的主从复制原理：

MySQL主从复制的工作原理：在主库把数据更改记录到二进制日志，从库将主库的日志复制到自己的中继日志，从库读取中继日志的事件，将其充放到从库数据中。

MySQL主从复制解决的问题：

1、数据分布：随意停止或开始复制，并在不同地理位置分布数据备份。

2、负载均衡：降低单个服务器的压力。

3、高可用和故障切换：帮助应用程序避免单点失败。

4、升级测试：可以使用更高版本的MySQL作为从库。