为什么要自定义分隔符mysql mysql为什么要分库分表

转载

mob64ca13faa4e6 2023-12-20 16:33:57

文章标签 为什么要自定义分隔符mysql mysql 数据库服务器数据 文章分类 MySQL 数据库

MySQL(八)–关于分库分表

一.为什么要分库分表？

我们的数据库数据越来越大，随之而来的是单个表中数据太多，以至于查询速度过慢，而且由于表的锁机制导致应用操作也受到严重影响，出现数据库性能瓶颈。

MySQL中有一种机制是表锁定和行锁定，是为了保证数据的完整性。表锁定表示你们都不能对这张表进行操作，必须等我对表操作完才行。行锁定也一样，别的sql必须等我对这条数据操作完了，才能对这条数据进行操纵。但出现这种情况时，我们可以考虑分表或者分区。

分库分表是两个不同的概念，分表是为了避免单表的数据量太大，执行SQL时影响语句的执行性能；分库主要是为了提高系统的并发量。单个库的并发量最好维持在（1000）左右。

一、分库：

什么时候考虑使用分库？

单台DB的存储空间不够
随着查询量的增加单台数据库服务器已经没办法支撑

分库解决的问题

其主要目的是为突破单节点数据库服务器的 I/O 能力限制，解决数据库扩展性问题。

分库也包含水平分库和垂直分库，在分表中有写，看下面的笔记。

二、分表：

什么是分表：

分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，每个表都对应三个文件，MYD数据文件，MYI索引文件，frm表结构文件。这些表可以分布在同一块磁盘上，也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的表名，然后去操作他。

用单个数据库表进行拆分，拆分成多个数据表，然后用户访问的时候，根据一定的算法（如用hash算法的方式，也可以用求余（取模）的方式），让用户访问不同的表，这样数据分散到多个数据表中，减少了单个表的访问压力，提升了数据库的访问性能。分表的目的就在于此，减少数据库的负担，缩短查询时间。

什么时候考虑分表？

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

MySQL分表分为垂直切分和水平切分：

垂直切分常见有垂直分库和垂直分表两种。

垂直分库就是根据业务耦合性，将关联度低的不同表存储在不同的数据库。做法与大系统拆分为多个小系统类似，按业务分类进行独立划分。与"微服务治理"的做法相似，每个微服务使用单独的一个数据库。如图：

为什么要自定义分隔符mysql mysql为什么要分库分表_服务器

垂直分表是指数据表列的切分，把一张列比较多的表拆分为多张表。

另外数据库以行为单位将数据加载到内存中，这样表中字段长度较短且访问频率较高，内存能加载更多的数据，命中率更高，减少了磁盘IO，从而提升了数据库性能。

为什么要自定义分隔符mysql mysql为什么要分库分表_服务器_02

通常我们按照以下原则进行垂直切分：

把不常用的字段单独放到一张表。
把text，blob（binary large object，二进制大对象）等大字段拆分出来放在附表中
经常组合查询的列放在一张表中：

垂直切分更多时候就应该在数据表设计之初就执行的步骤，然后查询的时候用join关键起来即可。（一般的表可以通过join的方式连接起来，但是部分表无法join，只能通过接口聚合方式解决，提升了开发的复杂度）

垂直切分的优点：

解决业务系统层面的耦合，业务清晰
与微服务的治理类似，也能对不同业务的数据进行分级管理、维护、监控、扩展等
高并发场景下，垂直切分一定程度的提升IO、数据库连接数、单机硬件资源的瓶颈

缺点：

部分表无法join，只能通过接口聚合方式解决，提升了开发的复杂度
分布式事务处理复杂
依然存在单表数据量过大的问题（需要水平切分）

当一个应用难以再细粒度的垂直切分，或切分后数据量行数巨大，存在单库读写、存储性能瓶颈，这时候就需要进行水平切分了。

水平切分分为库内分表和分库分表，是根据表内数据内在的逻辑关系，将同一个表按不同的条件分散到多个数据库或多个表中，每个表中只包含一部分数据，从而使得单个表的数据量变小，达到分布式的效果。如图所示：

为什么要自定义分隔符mysql mysql为什么要分库分表_为什么要自定义分隔符mysql_03

库内分表只解决了单一表数据量过大的问题，但没有将表分布到不同机器的库上，因此对于减轻MySQL数据库的压力来说，帮助不是很大，大家还是竞争同一个物理机的CPU、内存、网络IO，最好通过分库分表来解决。

水平拆分是指数据表行的拆分，把一张表的数据拆成多张表来存放。

水平拆分的原则：（这个在下面有相对的展开来谈的）

通常情况下，我们使用hash，取模的方式来进行表的拆分。
比如一张拥有400W的用户表users，为了提高其查询效率，我们把其分为四张表 user1，user2，user3，user4
通过ID取模的方法把数据分散到四张表内 id%4=[0,1,2,3]
然后查询，更新，删除也是通过取模的方式来查询
部分业务逻辑也可以通过地区，年份等字段来进行归档拆分；
进行拆分后的表，这是我们就要约束用户的查询行为。比如我们是按年来进行拆分的，这个时候在页面设计上就约束用户必须要先选择年，然后才能进行查询。

水平切分的优点：

不存在单库数据量过大、高并发的性能瓶颈，提升系统稳定性和负载能力
应用端改造较小，不需要拆分业务模块

缺点：

跨分片的事务一致性难以保证
跨库的join关联查询性能较差
数据多次扩展难度和维护量极大

水平切分后同一张表会出现在多个数据库/表中，每个库/表的内容不同。几种典型的数据分片规则为：

1、根据数值范围：

按照时间区间或ID区间来切分。例如：按日期将不同月甚至是日的数据分散到不同的库中；将userId为1-9999的记录分到第一个库，10000~20000的分到第二个库，以此类推。某种意义上，某些系统中使用的"冷热数据分离"，将一些使用较少的历史数据迁移到其他库中，业务功能上只提供热点数据的查询，也是类似的实践。

这样的优点在于：

单表大小可控
天然便于水平扩展，后期如果想对整个分片集群扩容时，只需要添加节点即可，无需对其他分片的数据进行迁移
使用分片字段进行范围查找时，连续分片可快速定位分片进行快速查询，有效避免跨分片查询的问题。

缺点：

热点数据成为性能瓶颈。连续分片可能存在数据热点，例如按时间字段分片，有些分片存储最近时间段内的数据，可能会被频繁的读写，而有些分片存储的历史数据，则很少被查询

为什么要自定义分隔符mysql mysql为什么要分库分表_数据库_04

2、根据数值取模：

一般采用hash取模mod的切分方式，例如：将 Customer 表根据 cusno 字段切分到4个库中，余数为0的放到第一个库，余数为1的放到第二个库，以此类推。这样同一个用户的数据会分散到同一个库中，如果查询条件带有cusno字段，则可明确定位到相应库去查询。

优点：

数据分片相对比较均匀，不容易出现热点和并发访问的瓶颈

缺点：

后期分片集群扩容时，需要迁移旧的数据（使用一致性hash算法能较好的避免这个问题）
容易面临跨分片查询的复杂问题。比如上例中，如果频繁用到的查询条件中不带cusno时，将会导致无法定位数据库，从而需要同时向4个库发起查询，再在内存中合并数据，取最小集返回给应用，分库反而成为拖累。

为什么要自定义分隔符mysql mysql为什么要分库分表_数据库_05

分表的几种方式：
1、mysql集群

他并不是分表，但是起到了和分表相同的作用。集群可分担数据库的操作次数，将任务分担到多台数据库上。集群可以读写分离，减少读写的压力。从而提升数据库的性能。

2、预先估计会出现大数据量并且访问频繁的表，将其分为若干个表

根据一定的算法（如hash的方式，也可以使用求余（取模）的方式）让用户访问不同的表。

3、利用merge存储去引擎来实现分表

== merge分表，分为主表和子表==，主表类似于一个壳子，逻辑上封装了子表，实际上都是存储在子表中的。我们可以通过主表插入和查询数据，如果清除分表规律也可以直接操作子表。

这里还涉及到分区：

数据分区是一种物理数据库的设计技术，它的目的是为了在特定的SQL操作中减少数据读写的总量以缩减响应时间。

分区并不是生成新的数据表，而是将表的数据均衡分摊到不同的硬盘，系统或是不同服务器存储介子中，实际上还是一张表。另外，分区可以做到将表的数据均衡到不同的地方，提高数据检索的效率，降低数据库的频繁IO压力值，分区的优点如下：

1、相对于单个文件系统或是硬盘，分区可以存储更多的数据；
2、数据管理比较方便，比如要清理或废弃某年的数据，就可以直接删除该日期的分区数据即可；
3、精准定位分区查询数据，不需要全表扫描查询，大大提高数据检索效率；
4、可跨多个分区磁盘查询，来提高查询的吞吐量；
5、在涉及聚合函数查询时，可以很容易进行数据的合并；

分区和分表相似，都是按照规则分解表。不同在**与分表将大表分解若干个独立的实体表，而分区是将数据分段划分在多个位置存放，分区后，表还是一张表，但数据散列到多个位置了。**app读写的时候操作还是表名子，db自动去组织分区的数据。

分区主要的两种数据;
    水平分区：这种形式的分区是对表的行进行分区，所有在表中定义的列在每个数据集中都能找到，所以表的特性依然得以保持。

    举个简单例子：一个包含十年发票记录的表可以被分区为十个不同的分区，
    每个分区包含的是其中一年的记录。
    （朋奕注：这里具体使用的分区方式我们后面再说，可以先说一点，一定要通过某个属性列来分割，譬如这里使用的列就是年份）
 
    垂直分区：这种分区形式一般来说是通过对表的垂直划分来减少对目标表的宽度，使某些特定的列，被划分到特定的分区，每个分区都包括了其中的列所对应的行。

    举个简单例子：一个包含了大text和BLOB列的表，这些text和BLOB列又不经常被访问，
    这时候就要把这些不经常使用的text和BLOB了划分到另一个分区，
    在保证它们数据相关性的同时还能提高访问速度。

什么时候考虑使用分区？

一张表的查询速度已经慢到影响使用的时候。
sql经过优化
数据量大
表中的数据是分段的
对数据的操作往往只涉及一部分数据，而不是所有的数据

MySQL的分区类型：

1、RANGE分区：

基于属于一个给定连续区间的列值，把多行分配给给分区。这些区间要连续且不能相互重叠，使用values less than操作符进行定义。
2、LIST分区

类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

LIST分区通过使用"PARTITION BY LIST"（expr）来实现，其中expr 是某列值或一个基于某个列值、并返回一个整数值的表达式，然后通过：VALUES IN （value_list）”的方式来定义某个分区，其中“value——list“是一个通过逗号分隔的整数列表。

3、Hash分区

这种模式允许DBA通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同的数值对应的数据区域进行分区。
hash分区的目的是将数据均匀分步到预先定义的各个分区中，保证各分区的数据的数据量大致一致。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪一个分区中；而在RANGE和LIST分区中MySQL自动完成这个动作，用户所要定一个列值或者表达式，以及指定被分区的表将要被分割成分区的数量。

4、Key分区

Key分区和Hash相似，不同在于 hash分区是用户自定义函数进行分区，Key分区使用mysql数据库提供的分区进行分区，NDB clust使用MD5函数进行分区，对于其他存储引擎mysql使用内部的hash函数。

分区和分表的区别与联系