0%

MySQL分库分区分表

发表于 2017-03-21 分类于 MySQL 1k 1 分钟

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

分库

一、分库

show engines\G;
概念：把原本存储于一个库的数据分块存储到多个库(不同主机)上。
分类
- 垂直拆分
  - 将系统中不存在关联关系的表拆出来
  - 根据业务拆分，如日志库、业务库等
- 水平分库分表：将单张表的数据切分到多个服务器上去，每个服务器具有相应的库与表，只是表中数据集合不同。
  - 能够有效的缓解单机和单库的性能瓶颈和压力，突破IO、连接数、硬件资源等的瓶颈。
- 读写分离
适用场景
- 单台DB的存储空间不够
- 随着查询量的增加单台数据库服务器已经没办法支撑
作用：突破单节点数据库服务器的I/O能力限制，解决数据库扩展性问题。

分表

二、分表

概念：把一张表按一定的规则分解成N个具有独立存储空间的实体表。系统读写时需要根据定义好的规则得到对应的字表明，然后操作它。
分类
- 垂直拆分：基于列字段进行
  - 一般是表中的字段较多，将不常用、数据较大、长度较长的列拆分到扩展表
  - 一般是针对那种几百列的大表，也避免查询时，数据量太大造成的跨页问题
- 水平分表：基于全表进行
  - 水平分表可以大大减少单表数据量，提升查询效率
适用场景
- 一张表的查询速度已经慢到影响使用的时候
- sql经过优化
- 数据量大
- 当频繁插入或者联合查询时，速度变慢
作用
- 分表后可以提高单表的并发能力、磁盘I/O性能和写操作效率
- 查询一次的时间短了
- 数据分布在不同的文件，磁盘I/O性能提高
- 读写锁影响的数据量变小
- 插入数据库需要重新建立索引的数据减少

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

分区

三、分区

概念：把一张表的数据按一定的逻辑规则分成N个区块，在逻辑上看最终只是一张表，但底层是由N个物理区块组成的。
分类
- range:分区字段在给定的连续区间范围
- list:分区字段必须为int型
- columns
  - range columns
  - list columns：分区字段的数据类型除了数字类型外，还可以使用字符串类型，date和datetime
- hash
- key
- sub子分区
适用场景
- 一张表的查询速度已经慢到影响使用的时候
- sql经过优化
- 数据量大
- 表中的数据是分段的
- 对数据的操作往往只涉及一部分数据，而不是所有的数据
作用：提升查询效率
优点
- 分区可以分在多个磁盘，存储更大一点
- 根据where条件，只查找相应的分区而不用全部查找
- 进行大数据搜索时可以进行并行处理
- 跨多个磁盘来分散数据查询，来获得更大的查询吞吐量

分区和分表的区别和联系

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