当前位置:首页 > 数据库 > Mysql服务端(五)--- 分库分表设计

Mysql服务端(五)--- 分库分表设计

2022年11月06日 20:57:06数据库10

        面对海量数据,例如,上千万甚至上亿的数据,查询一次所花费的时间会变长,甚至会造成数据库的单点压力。因此,分库与分表的目的在于,减小数据库的单库单表负担,提高查询性能,缩短查询时间。

分表概述

随着用户数的不断增加,以及数据量的不断增加,会使得单表压力越来越大,面对上千万甚至上亿的数据,查询一次所花费的时间会变长,如果有联合查询的情况下,甚至可能会成为很大的瓶颈。此外,MySQL 存在表锁和行锁,因此更新表数据可能会引起表锁或者行锁,这样也会导致其他操作等待,甚至死锁问题。

通过分表,可以减少数据库的单表负担,将压力分散到不同的表上,同时因为不同的表上的数据量少了,起到提高查询性能,缩短查询时间的作用,此外,可以很大的缓解表锁的问题。

分表策略可以归纳为垂直拆分和水平拆分。

垂直拆分,把表的字段进行拆分,即一张字段比较多的表拆分为多张表,这样使得行数据变小。一方面,可以减少客户端程序和数据库之间的网络传输的字节数,因为生产环境共享同一个网络带宽,随着并发查询的增多,有可能造成带宽瓶颈从而造成阻塞。另一方面,一个数据块能存放更多的数据,在查询时就会减少 I/O 次数。举个例子,假设用户表中有一个字段是家庭地址,这个字段是可选字段,在数据库操作的时候除了个人信息外,并不需要经常读取或是更改这个字段的值。在这种情况下,更建议把它拆分到另外一个表,从而提高性能。

如何设计好垂直拆分,我的建议:

  • 将不常用的字段单独拆分到另外一张扩展表,例如前面讲解到的用户家庭地址,这个字段是可选字段,在数据库操作的时候除了个人信息外,并不需要经常读取或是更改这个字段的值。
  • 将大文本的字段单独拆分到另外一张扩展表,例如 BLOB 和 TEXT 字符串类型的字段,以及 TINYBLOB、 MEDIUMBLOB、 LONGBLOB、 TINYTEXT、 MEDIUMTEXT、 LONGTEXT字符串类型。这样可以减少客户端程序和数据库之间的网络传输的字节数。
  • 将不经常修改的字段放在同一张表中,将经常改变的字段放在另一张表中。举个例子,假设用户表的设计中,还存在“最后登录时间”字段,每次用户登录时会被更新。这张用户表会存在频繁的更新操作,此外,每次更新时会导致该表的查询缓存被清空。所以,可以把这个字段放到另一个表中,这样查询缓存会增加很多性能。
  • 对于需要经常关联查询的字段,建议放在同一张表中。不然在联合查询的情况下,会带来数据库额外压力。

水平拆分,把表的行进行拆分。因为表的行数超过几百万行时,就会变慢,这时可以把一张的表的数据拆成多张表来存放。水平拆分,有许多策略,例如,取模分表,时间维度分表,以及自定义 Hash 分表,例如用户 ID 维度分表等。在不同策略分表情况下,根据各自的策略写入与读取。

实际上,垂直拆分后的表依然存在单表数据量过大的问题,需要进行水平拆分。因此,实际情况中,水平拆分往往会和垂直拆分结合使用。假设,随着用户数的不断增加,用户表单表存在上千万的数据,这时可以把一张用户表的数据拆成多张用户表来存放。

常见的水平分表策略归纳起来,可以总结为随机分表和连续分表两种情况。例如,取模分表就属于随机分表,而时间维度分表则属于连续分表。

连续分表可以快速定位到表进行高效查询,大多数情况下,可以有效避免跨表查询。如果想扩展,只需要添加额外的分表就可以了,无需对其他分表的数据进行数据迁移。但是,连续分表有可能存在数据热点的问题,有些表可能会被频繁地查询从而造成较大压力,热数据的表就成为了整个库的瓶颈,而有些表可能存的是历史数据,很少需要被查询到。

随机分表是遵循规则策略进行写入与读取,而不是真正意义上的随机。通常,采用取模分表或者自定义 Hash 分表的方式进行水平拆分。随机分表的数据相对比较均匀,不容易出现热点和并发访问的瓶颈。但是,分表扩展需要迁移旧的数据。此外,随机分表比较容易面临跨表查询的复杂问题。

对于日志场景,可以考虑根据时间维度分表,例如年份维度分表或者月份维度分表,在日志记录表的名字中包含年份和月份的信息,例如 log_2017_01,这样可以在已经没有新增操作的历史表上做频繁地查询操作,而不会影响时间维度分表上新增操作。

对于海量用户场景,可以考虑取模分表,数据相对比较均匀,不容易出现热点和并发访问的瓶颈。

对于租户场景,可以考虑租户维度分表,不同的租户数据独立,而不应该在每张表中添加租户 ID,这是一个不错的选择。

分库概述

库内分表,仅仅是解决了单表数据过大的问题,但并没有把单表的数据分散到不同的物理机上,因此并不能减轻 MySQL 服务器的压力,仍然存在同一个物理机上的资源竞争和瓶颈,包括 CPU、内存、磁盘 IO、网络带宽等。

分库策略也可以归纳为垂直拆分和水平拆分。

垂直拆分,按照业务和功能划分,把数据分别放到不同的数据库中。举个例子,可以划分资讯库、百科库等。

水平拆分,把一张表的数据划分到不同的数据库,两个数据库的表结构一样。实际上,水平分库与水平分表类似,水平拆分有许多策略,例如,取模分库,自定义 Hash 分库等,在不同策略分库情况下,根据各自的策略写入与读取。举个例子,随着业务的增长,资讯库的单表数据过大,此时采取水平拆分策略,根据取模分库。

转载于:http://blog.720ui.com/2017/mysql_core_08_multi_db_table/

作者:wenjieyatou
来源链接:https://blog.csdn.net/wenjieyatou/article/details/80635472

版权声明:
1、JavaClub(https://www.javaclub.cn)以学习交流为目的,由作者投稿、网友推荐和小编整理收藏优秀的IT技术及相关内容,包括但不限于文字、图片、音频、视频、软件、程序等,其均来自互联网,本站不享有版权,版权归原作者所有。

2、本站提供的内容仅用于个人学习、研究或欣赏,以及其他非商业性或非盈利性用途,但同时应遵守著作权法及其他相关法律的规定,不得侵犯相关权利人及本网站的合法权利。
3、本网站内容原作者如不愿意在本网站刊登内容,请及时通知本站(javaclubcn@163.com),我们将第一时间核实后及时予以删除。


本文链接:https://www.javaclub.cn/database/68252.html

分享给朋友:

“Mysql服务端(五)--- 分库分表设计” 的相关文章

MySQL触发器

MySQL触发器 触发器是一种特殊的存储过程,触发器和存储过程一样是一个能完成特定功能、存储在数据库服务器上的SQL片段,但是触发器无需调用,当对数据库表中的数据执行DML操作时自动触发这个SQL片段的执行,无需手动调用. 在MySQL中,只...

MySQL表的增删改查(进阶)

MySQL表的增删改查(进阶)

数据库的约束 约束类型 NOT NULL UNIQUE DEFAULT PRIMARY...

MySQL事务和锁

MySQL事务和锁

1.事务 1.什么是事务? 事务: 要么全部成功,要么全部失败 事务是数据库管理系统(DBMS)执行过程中的一个 逻辑单位 ,由一个 有限的数据库操作序列 组成。 逻辑单位:最小的操作单位,不可再分割。 有限的数据库操作序列:...

mysql|mysql删除重复数据,一条sql就搞定

SQL 逻辑: 首先找出所有重复的sku_id 在找出这些sku_id中关联的主键id最大的一条记录(用来保留,不删除) 两个sql合并 就可以筛选出来最终需要删除的id select id from sku_and...

mysql查询字段是否包含某个值或某些值的方法

mysql查询字段是否包含某个值或某些值的方法

方法一:SELECT * from demo where type like  "%3%";                字段type中包含3的都查询到了,会导致索...

mysql查询最后一条记录

首先要确定什么是最后一条。 是编辑时间最新的为最后一条,还是某个字段数字最大的未最后一条。 比如以时间最大为最后一条,则将符合条件的资料都筛选出来,再按时间排序,再取一笔资料。 SQL如下: select a,b from table whe...

mysql 查询操作日志

最近在操作mysql的过程中,因某些愿意需要查看mysql的日志,故总结如下: 要查看日志,首先需要查看日志是否开启, 使用一下语句: SHOW VARIABLES LIKE 'log_bin'; 如果显示ON,则表示日志已...

shell简单处理mysql查询结果

首先理清要了解shell脚本的数组与字符串的一些特性: str=("hello" "world" "!") #结果: str: 3 #普通的字符串数组 echo "str: " ${#str[@]} str1=("hello world !")...

MySQL查询某个时间段内的字段,使用的日期是String类型,但数据库中是时间戳

MySQL查询某个时间段内的字段,使用的日期是String类型 在Java中,想要实现对数据库中的特殊查询——查询某个时间段内的字段,但是传入的日期值是String型,而数据库中的日期是时间戳…… 可以使用如下方法: 比如: ’ 2019-0...

mysql查询数据库表的数据行数

查看mysql某个数据库下所有表的数据总条数 use information_schema; select sum(TABLE_ROWS) from tables where TABLE_SCHEMA = 'db_name'; 查询出来的是...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法和观点。