0%

MySQL分库分区分表

MySQL删除语句delete凡是带有where条件的都不是真删除,只是MySQL给记录加了个删除标识,这样操作后表数据占有空间也不会变小。

一、分库

  1. 概念:把原本存储于一个库的数据分块存储到多个库(不同主机)上。

  2. 分类

    • 垂直拆分
      • 将系统中不存在关联关系的表拆出来
      • 根据业务拆分,如日志库、业务库等
    • 水平分库分表:将单张表的数据切分到多个服务器上去,每个服务器具有相应的库与表,只是表中数据集合不同。
      • 能够有效的缓解单机和单库的性能瓶颈和压力,突破IO、连接数、硬件资源等的瓶颈。
    • 读写分离
  3. 适用场景

    • 单台DB的存储空间不够
    • 随着查询量的增加单台数据库服务器已经没办法支撑
  4. 作用:突破单节点数据库服务器的I/O能力限制,解决数据库扩展性问题。

二、分表

  1. 概念:把一张表按一定的规则分解成N个具有独立存储空间的实体表。系统读写时需要根据定义好的规则得到对应的字表明,然后操作它。

  2. 分类

    • 垂直拆分:基于列字段进行
      • 一般是表中的字段较多,将不常用、数据较大、长度较长的列拆分到扩展表
      • 一般是针对那种几百列的大表,也避免查询时,数据量太大造成的跨页问题
    • 水平分表:基于全表进行
      • 水平分表可以大大减少单表数据量,提升查询效率
  3. 适用场景

    • 一张表的查询速度已经慢到影响使用的时候
    • sql经过优化
    • 数据量大
    • 当频繁插入或者联合查询时,速度变慢
  4. 作用

    • 分表后可以提高单表的并发能力、磁盘I/O性能和写操作效率
    • 查询一次的时间短了
    • 数据分布在不同的文件,磁盘I/O性能提高
    • 读写锁影响的数据量变小
    • 插入数据库需要重新建立索引的数据减少

分库分表存在的问题:跨库事务、连接查询、横向扩容、结果集合并、排序问题等

三、分区

  1. 概念:把一张表的数据按一定的逻辑规则分成N个区块,在逻辑上看最终只是一张表,但底层是由N个物理区块组成的。

  2. 分类

    • range:分区字段在给定的连续区间范围
    • list:分区字段必须为int型
    • columns
      • range columns
      • list columns:分区字段的数据类型除了数字类型外,还可以使用字符串类型,date和datetime
    • hash
    • key
    • sub子分区
  3. 适用场景

    • 一张表的查询速度已经慢到影响使用的时候
    • sql经过优化
    • 数据量大
    • 表中的数据是分段的
    • 对数据的操作往往只涉及一部分数据,而不是所有的数据
  4. 作用:提升查询效率

  5. 优点

    • 分区可以分在多个磁盘,存储更大一点
    • 根据where条件,只查找相应的分区而不用全部查找
    • 进行大数据搜索时可以进行并行处理
    • 跨多个磁盘来分散数据查询,来获得更大的查询吞吐量

分区和分表的区别和联系

1
2
3
4
•分区和分表的目的都是减少数据库的负担,提高增删改查效率。
•分区只是一张表中的数据的存储位置发生改变,对用户来说感知不到;分表是将一张表分成多张表,用户能直接看到。
•当访问量大且数据量都比较大时,两种方式可以互相配合使用。
•当访问量不大但数据量较大时,可以只进行分区。