Mysql优化之分区分表

2024-04-23 20:44
文章标签 mysql 优化 database 区分 之分

本文主要是介绍Mysql优化之分区分表，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

为什么要分区分表

分区和分表是两种用于优化大型数据集查询性能的技术，它们有不同的应用场景和优势。随着数据库数据越来越大，单个表中数据太多。以至于查询速度变慢，而且由于表的锁机制导致应用操作也受到严重影响，就出现了数据库性能瓶颈。当出现这种情况时，我们可以考虑分表或分区。

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

分表

  • 分表是数据库优化技术之一，它将一个大表拆分成多个小表，每个小表只包含部分数据。这样做的目的是减少单个表中的数据量，提高查询性能、降低锁竞争，并且可以更灵活地管理数据。这些表可以分布在同一块磁盘上，也可以在不同的机器上。读写的时候根据事先定义好的规则得到对应的表名，然后去操作它。Mysql分表分为垂直切分和水平切分。

1、垂直切分：垂直切分是指数据表列的拆分，把一张列比较多的表拆分为多张表。通常我们按以下原则进行垂直拆分: 根据列的访问频率和关联性来进行划分。把不常用的字段单独放在一张表; 把text，blob等大字段拆分出来放在附表中;通常用于将大型宽表拆分成多个相对较小的窄表，以减少单个表的数据量和提高查询性能（减少查询时的数据量和锁竞争)。 垂直拆分更多时候就应该在数据表设计之初就执行的步骤。

2、水平切分：水平切分是指数据表行的拆分，把一张的表的数据拆成多张表来存放，每个表包含原表的一部分行。通常是根据某个列的值范围或者哈希值来进行划分。水平切分通常用于解决单个表数据量过大、性能瓶颈明显的情况。例如：一个包含大量用户的用户表可以根据用户所在地区进行水平切分，将不同地区的用户数据存储在不同的表中。这样可以降低单个表的数据量，提高查询性能和并发性能。

分表的几种方式

  1. 集群：它并不是分表，但起到了和分表相同的作用。集群可分担数据库的操作，将任务分担到多台数据库上。集群可以读写分离，减少读写压力从而提升数据库性能。
  2. 预先估计会出现大数据量并且访问频繁的表，将其分为若干个表。例如聊天的信息表：可以先预估有100个这样的表判断用户的ID将信息存入相应的表。也可以设计每张表的容量是X条，插入数据前进行判断小于X就直接插入数据，大于X就创建新表后插入数据。

垂直分表

  • 根据数据访问模式进行拆分：根据业务需求和数据访问模式来确定拆分的策略。将常用的列放在一个表中，不常用的列放在另一个表中，以提高查询性能和降低数据访问的复杂性。

  • 保持关联数据的完整性：如果需要将一个实体的数据拆分到多个表中，确保这些表之间的数据关联是完整的，可以通过主键和外键来保持数据的一致性。

  • 避免过度拆分：不要将数据拆分得过于细致，以免增加查询的复杂度和维护的成本。合理拆分可以提高性能，但过度拆分可能会导致额外的复杂性和性能损失。

  • 考虑扩展性：在进行垂直分表时，考虑到系统的扩展性，确保分表方案能够支持未来系统的扩展和变化，避免频繁的表结构调整和数据迁移。

  • 评估性能影响：在实施分表方案之前，进行性能评估和测试，确保分表方案能够达到预期的性能提升，并且不会引入新的性能问题。

  • 考虑维护成本：评估分表方案的维护成本，包括数据迁移、查询优化和系统维护等方面的成本。确保分表方案不会增加过多的维护工作量。

我这里有库查询一下最大的表：

mysql> select table_name as `table`,round(((data_length + index_length)/1024/1024),2) as `size (MB)` from infor+-----------------------+-----------+ma = 'bwk_test' order by (data_length + index_length) desc limit 1;
| table                 | size (MB) |
+-----------------------+-----------+
| tb_charm_value_record |    119.06 |
+-----------------------+-----------+
1 row in set (0.18 sec)

查询一下表的结构数据：

mysql> select table_name,engine,table_collation from information_schema.tables where table_schema='bwk_test' and table_name='tb_charm_value_record';
+-----------------------+--------+--------------------+
| TABLE_NAME            | ENGINE | TABLE_COLLATION    |
+-----------------------+--------+--------------------+
| tb_charm_value_record | InnoDB | utf8mb3_general_ci |
+-----------------------+--------+--------------------+

查询表键属性：

mysql> desc tb_charm_value_record;
+--------------------+---------------+------+-----+-------------------+-----------------------------------------------+
| Field              | Type          | Null | Key | Default           | Extra                                         |
+--------------------+---------------+------+-----+-------------------+-----------------------------------------------+
| CharmValueRecordID | varchar(50)   | NO   | PRI | NULL              |                                               |
| TaskID             | varchar(50)   | YES  | MUL | NULL              |                                               |
| UserID             | varchar(50)   | YES  | MUL | NULL              |                                               |
| CharmValue         | decimal(10,2) | YES  |     | NULL              |                                               |
| CharmType          | int           | YES  |     | NULL              |                                               |
| IsReceive          | bit(1)        | YES  |     | NULL              |                                               |
| ReceiveTime        | datetime      | YES  |     | NULL              |                                               |
| CreationTime       | datetime      | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED                             |
| Creator            | varchar(50)   | NO   |     | NULL              |                                               |
| RevisionTime       | datetime      | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| Reviser            | varchar(50)   | NO   |     | NULL              |                                               |
| Remark             | varchar(50)   | YES  |     | NULL              |                                               |
| CharmInAndOut      | int           | NO   |     | NULL              |                                               |
| Currency           | int           | YES  |     | 5                 |                                               |
| OperationID        | varchar(50)   | YES  |     | NULL              |                                               |
+--------------------+---------------+------+-----+-------------------+-----------------------------------------------+
15 rows in set (0.01 sec)

创建分表：tb_charm_value_record_1、tb_charm_value_record_2、tb_charm_value_record_3

create table tb_charm_value_record_1 (CharmValueRecordID varchar(50) not null primary key, TaskID varchar(50),UserID varchar(50), CharmValue decimal(10,2),CharmType int ,IsReceive bit(1)) engine=innodb  charset=utf8mb3 collate=utf8mb3_general_ci; create table tb_charm_value_record_2 (CharmValueRecordID varchar(50) not null primary key, ReceiveTime datetime, CreationTime datetime not null default current_timestamp,Creator varchar(50) not null ,RevisionTime datetime not null default current_timestamp on update current_timestamp) engine=innodb  charset=utf8mb3 collate=utf8mb3_general_ci; create table tb_charm_value_record_3 (CharmValueRecordID varchar(50) not null primary key, Reviser varchar(50) not null, Remark varchar(50), CharmInAndOut int not null, Currency int,OperationID varchar(50)) engine=innodb  charset=utf8mb3 collate=utf8mb3_general_ci;

创建分表关联视图：

create view tb_charm_value_record_viem_time(CharmValueRecordID,UserID,CharmValue,ReceiveTime,Remark) as select t_1.CharmValueRecordID,t_1.UserID,t_1.CharmValue,t_2.ReceiveTime,t_3.Remark from tb_charm_value_record_1 as t_1 join  tb_charm_value_record_2 t_2 on t_1.CharmValueRecordID=t_2.CharmValueRecordID join tb_charm_value_record_3 t_3 on t_1.CharmValueRecordID=t_3.CharmValueRecordID;

查询数据核查：

mysql> select CharmValueRecordID,UserID,CharmValue,ReceiveTime,Remark from tb_charm_value_record where CharmValueRecordID='00018ece9f834ea288d3be23a39ef870';
+----------------------------------+----------------------------------+------------+---------------------+--------+
| CharmValueRecordID               | UserID                           | CharmValue | ReceiveTime         | Remark |
+----------------------------------+----------------------------------+------------+---------------------+--------+
| 00018ece9f834ea288d3be23a39ef870 | 1fd33b53837946849ffc4ad73f4df747 |       5.00 | 2022-06-20 07:30:35 |        |
+----------------------------------+----------------------------------+------------+---------------------+--------+
1 row in set (0.00 sec)mysql> select CharmValueRecordID,UserID,CharmValue,ReceiveTime,Remark from tb_charm_value_record_viem_time where CharmValueRecordID='00018ece9f834ea288d3be23a39ef870';
+----------------------------------+----------------------------------+------------+---------------------+--------+
| CharmValueRecordID               | UserID                           | CharmValue | ReceiveTime         | Remark |
+----------------------------------+----------------------------------+------------+---------------------+--------+
| 00018ece9f834ea288d3be23a39ef870 | 1fd33b53837946849ffc4ad73f4df747 |       5.00 | 2022-06-20 07:30:35 |        |
+----------------------------------+----------------------------------+------------+---------------------+--------+
1 row in set (0.00 sec)

水平分表

  • 按照行范围拆分：根据某个范围条件，将数据拆分到不同的物理表中。例如，可以根据时间范围、用户 ID 范围等将数据拆分到不同的表中。
  • 按照哈希拆分：根据某个哈希函数将数据拆分到不同的物理表中。这样可以均匀地将数据分布到不同的表中，避免单一表数据过大的问题。
  • 按照业务逻辑拆分：根据业务需求将数据拆分到不同的物理表中。例如，可以根据商品类别、地区或者用户等级等将数据拆分到不同的表中。

按照表中：CreationTime 时间来进行拆分成2022年和2023年的两个表。

create table tb_charm_value_record_2022 (CharmValueRecordID varchar(50) not null primary key, TaskID varchar(50),UserID varchar(50), CharmValue decimal(10,2),CharmType int ,IsReceive bit(1),ReceiveTime datetime, CreationTime datetime not null default current_timestamp,Creator varchar(50) not null ,RevisionTime datetime not null default current_timestamp on update current_timestamp,Reviser varchar(50) not null, Remark varchar(50), CharmInAndOut int not null,Currency int,OperationID varchar(50)) engine=innodb  charset=utf8mb3 collate=utf8mb3_general_ci;

修改table tb_charm_value_record_2022为2023在创建2023表

插入数据：

insert into  tb_charm_value_record_2022 (CharmValueRecordID, TaskID, UserID, CharmValue, CharmType, IsReceive, ReceiveTime, CreationTime, Creator, RevisionTime, Reviser, Remark, CharmInAndOut, Currency, OperationID) select CharmValueRecordID, TaskID, UserID, CharmValue, CharmType, IsReceive, ReceiveTime, CreationTime, Creator, RevisionTime, Reviser, Remark, CharmInAndOut, Currency, OperationID FROM tb_charm_value_record WHERE YEAR(ReceiveTime) = 2022;

修改时间插入2023的数据

对数据分表前要做好备份，最后对数据进行详细核查。

分区

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

1、性能提升： 可以将数据分散到多个物理存储介质上，从而提高并发读写操作的性能。
2、数据管理： 可以针对不同的分区实施不同的数据管理策略，例如备份、恢复、优化和维护等。
3、数据清理： 可以轻松地删除或移动某些分区中的数据，而不会影响其他分区的数据。
4、提高可用性： 可以根据应用需求将不同的分区放置在不同的物理存储设备上，以提高系统的可用性和容错性。

分区主要有两种形式：

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

  • 垂直分区（Vertical Partitioning）这种分区方式一般来说是通过对表的垂直划分来减少目标表的宽度，使某些
    特定的列被划分到特定的分区，每个分区都包含了其中的列所对应的行

创建分区表：

create table tb_charm_value_record_p (CharmValueRecordID varchar(50) not null , TaskID varchar(50),UserID varchar(50), CharmValue decimal(10,2),CharmType int ,IsReceive bit(1),ReceiveTime datetime, CreationTime datetime not null default current_timestamp,Creator varchar(50) not null ,RevisionTime datetime not null default current_timestamp on update current_timestamp,Reviser varchar(50) not null, Remark varchar(50), CharmInAndOut int not null,Currency int,OperationID varchar(50), primary key(CharmValueRecordID,CharmType)) engine=innodb  charset=utf8mb3 collate=utf8mb3_general_ci partition by range(CharmType) (partition p0 values less than(5),partition p1 values less than(10), partition p2 values less than(maxvalue));

导入数据：

insert into  tb_charm_value_record_p (CharmValueRecordID, TaskID, UserID, CharmValue, CharmType, IsReceive, ReceiveTime, CreationTime, Creator, RevisionTime, Reviser, Remark, CharmInAndOut, Currency, OperationID) select CharmValueRecordID, TaskID, UserID, CharmValue, CharmType, IsReceive, ReceiveTime, CreationTime, Creator, RevisionTime, Reviser, Remark, CharmInAndOut, Currency, OperationID FROM tb_charm_value_record;

查看数据存储：

[root@mysql bwk_test]# ls | grep tb_charm_value_record_p
tb_charm_value_record_p#p#p0.ibd
tb_charm_value_record_p#p#p1.ibd
tb_charm_value_record_p#p#p2.ibd

mysql分表和分区有什么区

1）实现方式上：

1、mysql的分表是真正的分表，一张表分成很多表后，每一个小表都是完整的一张表。
2、分区不一样，一张大表进行分区后，他还是一张表，不会变成二张表，但是他存放数据的区块变多了

2）数据处理上：

1、分表后，数据都是存放在分表里，存取数据发生在一个一个的分表里面。
2、分区呢，不存在分表的概念，分区只不过把存放数据的文件分成了许多小块，分区后的表呢，还是一张表，数据处理还是由自己来完成。

3）提高性能上：

1、分表后，单表的并发能力提高了，磁盘I/O性能也提高了。并发能力为什么提高了呢，因为查寻一次所花的时间变短了，如果出现高并发的话，总表可以根据不同的查询，将并发压力分到不同的小表里面。
2、mysql提出了分区的概念，主要是想突破磁盘I/O瓶颈，想提高磁盘的读写能力，来增加mysql性能。在这一点上，分区和分表的测重点不同，分表重点是存取数据时，如何提高mysql并发能力上；而分区呢，如何突破磁盘的读写能力，从而达到提高mysql性能的目的。

4）都能提高mysql的性高，在高并发状态下都有一个良好的表现。

5）分表和分区不矛盾，可以相互配合的，对于那些大访问量，并且表数据比较多的表，我们可以采取分表和分区结合的方式，访问量不大，但是表数据很多的表，我们可以采取分区的方式等。

6、分表技术是比较麻烦的，需要手动去创建子表，app服务端读写时候需要计算子表名。采用merge好一
些，但也要创建子表和配置子表间的union关系。

7、表分区相对于分表，操作方便，不需要创建子表。

这篇关于Mysql优化之分区分表的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/929842。

相关文章

Vue3 的 shallowRef 和 shallowReactive：优化性能

Vue3 的 shallowRef 和 shallowReactive：优化性能

大家对 Vue3 的 ref 和 reactive 都很熟悉，那么对 shallowRef 和 shallowReactive 是否了解呢？ 在编程和数据结构中，“shallow”（浅层）通常指对数据结构的最外层进行操作，而不递归地处理其内部或嵌套的数据。这种处理方式关注的是数据结构的第一层属性或元素，而忽略更深层次的嵌套内容。 1. 浅层与深层的对比 1.1 浅层（Shallow） 定义
阅读更多...
SQL中的外键约束

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点： 1.确保子表中的某个字段（外键）只能引用父表中的有效记录2.主表中的列被删除时，子表中的关联列也会被删除3.主表中的列更新时，子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示
阅读更多...
基于MySQL Binlog的Elasticsearch数据同步实践

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展，我们的业务数据越来越多，单纯使用 MySQL 已经不能满足我们的数据查询需求，例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后，随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步，我们目前的解决方案是建立数据中间表。把需要检索的业务数据，统一放到一张M
阅读更多...
如何去写一手好SQL

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数，谈性能都是耍流氓。MySQL没有限制单表最大记录数，它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB，才推荐分库分表。性能由综合因素决定，抛开业务复杂度，影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考，并非铁律。 博主曾经操作过超过4亿行数据
阅读更多...
HDFS—存储优化（纠删码）

HDFS—存储优化（纠删码）

纠删码原理 HDFS 默认情况下，一个文件有3个副本，这样提高了数据的可靠性，但也带来了2倍的冗余开销。 Hadoop3.x 引入了纠删码，采用计算的方式，可以节省约50％左右的存储空间。 此种方式节约了空间，但是会增加 cpu 的计算。 纠删码策略是给具体一个路径设置。所有往此路径下存储的文件，都会执行此策略。 默认只开启对 RS-6-3-1024k
阅读更多...
性能分析之MySQL索引实战案例

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题，本文将一起从代码层去分析为什么没有建立索引？ 开源ERP项目地址：https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置
阅读更多...
使用opencv优化图片（画面变清晰）

使用opencv优化图片（画面变清晰）

文章目录 需求影响照片清晰度的因素 实现降噪测试代码 锐化空间锐化Unsharp Masking频率域锐化对比测试 对比度增强常用算法对比测试 需求 对图像进行优化，使其看起来更清晰，同时保持尺寸不变，通常涉及到图像处理技术如锐化、降噪、对比度增强等 影响照片清晰度的因素 影响照片清晰度的因素有很多，主要可以从以下几个方面来分析 1. 拍摄设备 相机传感器：相机传
阅读更多...
MySQL数据库宕机，启动不起来，教你一招搞定！

MySQL数据库宕机，启动不起来，教你一招搞定！

作者介绍：老苏，10余年DBA工作运维经验，擅长Oracle、MySQL、PG、Mongodb数据库运维（如安装迁移，性能优化、故障应急处理等）公众号：老苏畅谈运维欢迎关注本人公众号，更多精彩与您分享。 MySQL数据库宕机，数据页损坏问题，启动不起来，该如何排查和解决，本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志，排查哪个表（表空间
阅读更多...
MySQL高性能优化规范

MySQL高性能优化规范

前言：      笔者最近上班途中突然想丰富下自己的数据库优化技能。于是在查阅了多篇文章后，总结出了这篇！ 数据库命令规范 所有数据库对象名称必须使用小写字母并用下划线分割 所有数据库对象名称禁止使用mysql保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来） 数据库对象的命名要能做到见名识意，并且最后不要超过32个字符 临时库表必须以tmp_为前缀并以日期为后缀，备份
阅读更多...
[MySQL表的增删改查-进阶]

[MySQL表的增删改查-进阶]

🌈个人主页：努力学编程’ ⛅个人推荐： c语言从初阶到进阶 JavaEE详解 数据结构 ⚡学好数据结构，刷题刻不容缓：点击一起刷题 🌙心灵鸡汤：总有人要赢，为什么不能是我呢 💻💻💻数据库约束 🔭🔭🔭约束类型 not null: 指示某列不能存储 NULL 值unique: 保证某列的每行必须有唯一的值default: 规定没有给列赋值时的默认值.primary key:
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2