最近面试经常被问到mysql的高可用的相关问题,分区、分库分表、主从复制被问起的最多,所以参考资料做一个总结。
分区操作:
-
工作原理:对用户来说,分区表是一个独立的逻辑链表,但底层的MySQL将其分成了多个物理子表(用户看来是一个整表,但每一个分区表都会使用一个独立的表文件)。分区后,对代码上来说是无感知的,代码该怎么写就怎么写,不用做特定的改变。执行查询的时候,优化器会根据分区定义(年龄段、地区、ID段等等)找出那些我们需要的数据所在的分区,然后进行查找。
如上图,比如一张表1000万条数据,分了5个区,每个区就可以存200万的数据。这样查询起来速度就会快一些。
分区的主要目的是把数据以较粗的粒度分布在不同的表中,将相关的数据放在一起,如果想一次性删除整个分区的数据也很方便。 -
适用场景:
1.表非常大,无法全部存在内存中,或者只在表的最后有热点数据,其他的都是历史数据。
2.为了更好的维护分区数据,对独立的分区进行对的操作。
3.分区表的数据可以分布在不同的机器上面,可以高效的使用资源。
4.备份和恢复独立的分区。 -
限制:
1.一个表最多只能分1024个分区。
2.分区字段中如果有主键和唯一索引列,那么主键和唯一索引列必须包含进来。
3.分区表无法使用外键索引。
4.所有分区都必须使用相同的存储引擎。
5.某些存储引擎不支持分区(MyISAM 和 InnoDB都支持)。
分库分表:
-
工作原理:通过一些hash算法或者工具实现将一张表垂直或者水平进行物理切分。
-
分表方式:
水平分割 :分割后可降低在查询时需要读取的数据和索引的页数,同时也降低了索引层数,提高查询的速度。 切分后变成了多个表,查询的时候就需要实现算法判断数据存放在哪一张表上。
水平分割适用场景:
1.表中的数据本身具有独立性,比如各个时期的数据,不同地区的数据,特别是有些数据常用有些不常用。
2.需要把数据存放在多个介质上(放在不同的服务器上,利用不同服务器的资源)。
水平分割缺点:
1.增加应用的复杂度,通常查询时需要多个表名,查询所有数据需要union操作。
2.在许多数据库应用中,这种复杂性往往会超过它带来的优点。垂直分割 :把主键和一些列放在一张表,再把主键和另外的一些列放在一张表。
垂直分割适用场景:
1.表中的一些列常用,另外一些列不常用。
2.可以使数据行变小,一个数据页能存放更多的数据,查询时可以减少I/O次数。
垂直分割缺点:
1.查询所有数据需要join(关联)操作。
2.在许多数据库应用中,这种复杂性往往会超过它带来的优点。 -
分表的缺点和限制:
1.分表的策略基于应用层的逻辑算法,一旦算法改变,整个分表逻辑都会改变,扩展性较差。
主从复制:
- 工作原理:在主库上把数据更改记录到二进制日志中(binlog),从库将主库中的日志复制到自己的中继日志,然后读取中继日志中的事件(增删改操作的SQL语句),在从库中执行一遍。
- 解决的问题:
1.数据分布:随意的开始或停止复制,在不同的地理位置数据备份。
2.负载均衡:降低单个服务器的压力。
3.高可用和故障切换:帮助应用程序避免单点失败。比如主数据库机器挂掉,可以立马切换到从库,服务是不会受到影响的。
4.升级测试:使用更高版本的数据库作为从库。