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。

相关文章

SQL注入漏洞扫描之sqlmap详解

SQL注入漏洞扫描之sqlmap详解

《SQL注入漏洞扫描之sqlmap详解》SQLMap是一款自动执行SQL注入的审计工具,支持多种SQL注入技术,包括布尔型盲注、时间型盲注、报错型注入、联合查询注入和堆叠查询注入... 目录what支持类型how---less-1为例1.检测网站是否存在sql注入漏洞的注入点2.列举可用数据库3.列举数据库
阅读更多...
Oracle查询优化之高效实现仅查询前10条记录的方法与实践

Oracle查询优化之高效实现仅查询前10条记录的方法与实践

《Oracle查询优化之高效实现仅查询前10条记录的方法与实践》：本文主要介绍Oracle查询优化之高效实现仅查询前10条记录的相关资料,包括使用ROWNUM、ROW_NUMBER()函数、FET... 目录1. 使用 ROWNUM 查询2. 使用 ROW_NUMBER() 函数3. 使用 FETCH FI
阅读更多...
Mysql虚拟列的使用场景

Mysql虚拟列的使用场景

《Mysql虚拟列的使用场景》MySQL虚拟列是一种在查询时动态生成的特殊列,它不占用存储空间,可以提高查询效率和数据处理便利性,本文给大家介绍Mysql虚拟列的相关知识,感兴趣的朋友一起看看吧... 目录1. 介绍mysql虚拟列1.1 定义和作用1.2 虚拟列与普通列的区别2. MySQL虚拟列的类型2
阅读更多...
mysql数据库分区的使用

mysql数据库分区的使用

《mysql数据库分区的使用》MySQL分区技术通过将大表分割成多个较小片段,提高查询性能、管理效率和数据存储效率,本文就来介绍一下mysql数据库分区的使用,感兴趣的可以了解一下... 目录【一】分区的基本概念【1】物理存储与逻辑分割【2】查询性能提升【3】数据管理与维护【4】扩展性与并行处理【二】分区的
阅读更多...
C#使用HttpClient进行Post请求出现超时问题的解决及优化

C#使用HttpClient进行Post请求出现超时问题的解决及优化

《C#使用HttpClient进行Post请求出现超时问题的解决及优化》最近我的控制台程序发现有时候总是出现请求超时等问题,通常好几分钟最多只有3-4个请求,在使用apipost发现并发10个5分钟也... 目录优化结论单例HttpClient连接池耗尽和并发并发异步最终优化后优化结论我直接上优化结论吧，
阅读更多...
Java内存泄漏问题的排查、优化与最佳实践

Java内存泄漏问题的排查、优化与最佳实践

《Java内存泄漏问题的排查、优化与最佳实践》在Java开发中,内存泄漏是一个常见且令人头疼的问题,内存泄漏指的是程序在运行过程中,已经不再使用的对象没有被及时释放,从而导致内存占用不断增加,最终... 目录引言1. 什么是内存泄漏？常见的内存泄漏情况2. 如何排查 Java 中的内存泄漏？2.1 使用 J
阅读更多...
MySQL中时区参数time_zone解读

MySQL中时区参数time_zone解读

《MySQL中时区参数time_zone解读》MySQL时区参数time_zone用于控制系统函数和字段的DEFAULTCURRENT_TIMESTAMP属性,修改时区可能会影响timestamp类型... 目录前言1.时区参数影响2.如何设置3.字段类型选择总结前言mysql 时区参数 time_zon
阅读更多...
Python MySQL如何通过Binlog获取变更记录恢复数据

Python MySQL如何通过Binlog获取变更记录恢复数据

《PythonMySQL如何通过Binlog获取变更记录恢复数据》本文介绍了如何使用Python和pymysqlreplication库通过MySQL的二进制日志（Binlog）获取数据库的变更记录... 目录python mysql通过Binlog获取变更记录恢复数据1.安装pymysqlreplicat
阅读更多...
使用SQL语言查询多个Excel表格的操作方法

使用SQL语言查询多个Excel表格的操作方法

《使用SQL语言查询多个Excel表格的操作方法》本文介绍了如何使用SQL语言查询多个Excel表格,通过将所有Excel表格放入一个.xlsx文件中,并使用pandas和pandasql库进行读取和... 目录如何用SQL语言查询多个Excel表格如何使用sql查询excel内容1. 简介2. 实现思路3
阅读更多...
Mysql DATETIME 毫秒坑的解决

Mysql DATETIME 毫秒坑的解决

《MysqlDATETIME毫秒坑的解决》本文主要介绍了MysqlDATETIME毫秒坑的解决,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着... 今天写代码突发一个诡异的 bug，代码逻辑大概如下。1. 新增退款单记录boolean save = s
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2