本文作者:心月

MySQL分区表、分库表原理和使用场景介绍

心月IT博客 2019-10-11
MySQL分区表、分库表原理和使用场景介绍摘要:当一张表的数据达到几千万时,你查询一次所花的时间会变多,如果有联合查询的话,我想有可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。

MySQL为什么要分表?

        当一张表的数据达到几千万时,你查询一次所花的时间会变多,如果有联合查询的话,我想有可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。

        mysql中有一种机制是表锁定和行锁定,是为了保证数据的完整性。表锁定表示你们都不能对这张表进行操作,必须等我对表操作完才行。行锁定也一样,别的sql必须等我对这条数据操作完了,才能对这条数据进行操作。

MySQL

①分区表的原理:

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

工作原理:

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

分区的目的:

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

使用场景:

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

        B、分区表的数据更易维护,可以对独立的分区进行独立的操作;

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

        D、可以使用分区表避免某些特殊的瓶颈;

        E、可以备份和恢复独立的分区;

限制:

        A、一个表最多只能有1024份分区

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

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

        D、分区表中无法使用外键约束;

        E、需要对现有表的结构进行修改;

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

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

        H、某些存储引擎是不支持分区的;

        I、对于MyISAM的分区表,不能使用load index into cache;

        J、对于MyISAM表,使用分区表时需要打开更多的文件描述符;


②分库表

工作原理:

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

使用场景:

        单标记录条数达到百万到千万级别时;

        解决表锁的问题;

分表方式:

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

         垂直分表:把主键和列放在一个表,然后把主键和另外的列放在另一个表中;

水平分割使用场景:

        A、表中的数据本身就有独立性

        B、需要把数据存放在多个介质上

水平分割缺点:

        A、给应用增加复杂度,通常查询时需要多个表名,查询所有数据都需UNION 操作;

        B、在许多数据库应用中,这种复杂性会超过它带来的优点,查询时会增加读一个索引层的磁盘次数

垂直分表使用场景:

        A、如果一个表中某些列常用,而另外一些列不常用

        B、可以使数据行变小,一个数据页能存储更多数据,查询时减少I/O次数

垂直分表缺点:

        管理冗余列,查询所有数据需要JOIN操作

分表缺点:

        有些分表的策略基于应用层的逻辑算法,一旦逻辑算法改变,整个分表逻辑都会改变,扩展性较差;

        对于应用层来说,逻辑算法无疑增加开发成本

文章版权及转载声明:

本文由 心月IT技术博客 博主整理于 2019-10-11
若转载请注明原文及出处:https://www.xinyueseo.com/mysql/386.html

分享到:
赞(
发表评论
快捷输入:

验证码

    评论列表 (有 0 条评论,人围观)参与讨论