首页 / MYSQL / MySQL 之分区分表

MySQL 之分区分表

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL 之分区分表，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含9475字，纯文字阅读大概需要14分钟。

内容图文

1、为什么要分表？
数据库数据越来越大，随之而来的是单个表中数据太多。以至于查询速度变慢，而且由于表的锁机制导致应用操作也搜到严重影响，出现了数据库性能瓶颈。
mysql中有一种机制是表锁定和行锁定，是为了保证数据的完整性。表锁定表示你们都不能对这张表进行操作，必须等我对表操作完才行。行锁定也一样，别的sql必须等我对这条数据操作完了，才能对这条数据进行操作。当出现这种情况时，我们可以考虑分表或分区。

2、MySQL分表
分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，每个表都对应三个文件，MYD数据文件，.MYI索引文件，.frm表结构文件。这些表可以分布在同一块磁盘上，也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的表名，然后去操作它。
将单个数据库表进行拆分，拆分成多个数据表，然后用户访问的时候，根据一定的算法（如用hash的方式，也可以用求余（取模）的方式），让用户访问不同的表，这样数据分散到多个数据表中，减少了单个数据表的访问压力。提升了数据库访问性能。分表的目的就在于此，减小数据库的负担，缩短查询时间。

Mysql分表分为垂直切分和水平切分，具体区别如下：
垂直切分是指数据表列的拆分，把一张列比较多的表拆分为多张表通常我们按以下原则进行垂直拆分: 把不常用的字段单独放在一张表; 把text，blob（binary large object，二进制大对象）等大字段拆分出来放在附表中;
经常组合查询的列放在一张表中; 垂直拆分更多时候就应该在数据表设计之初就执行的步骤，然后查询的时候用join关键起来即可。

水平拆分是指数据表行的拆分，把一张的表的数据拆成多张表来存放。水平拆分原则，通常情况下，我们使用hash、取模等方式来进行表的拆分比如一张有400W的用户表users，为提高其查询效率我们把其分成4张表users1，users2，users3，users4 通过用ID取模的方法把数据分散到四张表内Id%4= [0,1,2,3] 然后查询,更新,删除也是通过取模的方法来查询部分业务逻辑也可以通过地区，年份等字段来进行归档拆分; 进行拆分后的表，这时我们就要约束用户查询行为。比如我们是按年来进行拆分的,这个时候在页面设计上就约束用户必须要先选择年,然后才能进行查询。
3、利用merge存储引擎实现分表
注：只有myisam引擎的原表才可以利用merge存储引擎实现分表。

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

mysql> create database test1;
mysql> use test1;
mysql> create table member
    -> (
    -> id bigint auto_increment primary key,
    -> name varchar(20),
    -> sex tinyint not null default ‘0‘
    -> )engine=myisam default charset=utf8 auto_increment=1;
#插入数据
mysql> insert into member(name,sex) values(‘tom1‘,1);
mysql> insert into member(name,sex) select name,sex from member;             # 插入语句多执行几次，即可插入大量的数据
mysql> select count(*) from member;         # 手贱了，这里我插入了16384条数据
+----------+
| count(*) |
+----------+
|    16384 |
+----------+
1 row in set (0.00 sec)

2）对上面完整的表进行分表

分表注意事项：

子表和主表的字段定义需要一致，包括数据类型，数据长度等；

当分表完成后，所有的操作（增删改查）需要对主表进行，虽然主表并不存放实际的数据。

#创建两个分表，表结构必须和上面完整的表结构一致
mysql> create table tb_member1 like member;
mysql> create table tb_member2 like member;
#创建merge引擎的表作为主表，并关联上面的两个分表
mysql> create table tb_member
    -> (
    -> id bigint auto_increment primary key,
    -> name varchar(20),
    -> sex tinyint not null default ‘0‘
    -> )engine=merge union=(tb_member1,tb_member2) insert_method=last charset=utf8;

注：在上面创建主表时，指定的“insert_method=last”有三个可选参数，分别是：last：表示插入到最后一张表里面；first：表示插入到第一张表里面；NO：表示该表不能做任何写入操作，只作为查询使用。
3）查看刚刚创建的三个表结构如下：
MySQL 之分区分表 - 文章图片
4）将数据分到两个表中：

mysql> insert into tb_member1(id,name,sex) select id,name,sex from member where id%2=0;
Query OK, 8192 rows affected (0.01 sec)
Records: 8192  Duplicates: 0  Warnings: 0

mysql> insert into tb_member2(id,name,sex) select id,name,sex from member where id%2=1;
Query OK, 8192 rows affected (0.02 sec)
Records: 8192  Duplicates: 0  Warnings: 0

5）查看主表和两个子表中的数据
第一个子表部分数据如下：
MySQL 之分区分表 - 文章图片
第二个子表部分数据如下：

主表部分查询的部分数据如下：

数据总行数如下：

注意：总表只是一个外壳，存取数据发生在一个一个的子表里面。每个子表都有自已独立的相关表文件，而主表只是一个壳，并没有完整的相关表文件，当确定主表中可以查到的数据和分表之前查到的数据完全一致时，就可以将原来的表删除了，之后对表的读写操作，都可以对分表后的主表进行。
上面三个表对应的本地文件如下：
MySQL 之分区分表 - 文章图片
可以看出，能够查询到所有数据的主表的本地数据文件是非常小的，这也验证了，数据并没有存在这个主表中。
6）对主表进行插入数据的操作，如下：

mysql> insert into tb_member values(16385,‘tom2‘,0),(16386,‘tom3‘,1);

MySQL 之分区分表 - 文章图片
可以看出，新增的两条数据都插入在了第二张表中，因为在创建主表的时候，指定的“insert_method”是last，也就是所有插入数据的操作都是对最后一张表里进行的，可以通过alter指令修改插入方法，如下：

mysql> alter table tb_member INSERT_METHOD=first;

修改插入方法后，再自行对表进行插入数据的操作，可以发现所有的数据都写入了第一个表（我这里插入了四条数据），查看如下：

mysql> insert into tb_member values(16387,‘tom4‘,2),(16388,‘tom5‘,3),(16389,‘tom6‘,4),(16390,‘tom7‘,5);

MySQL 之分区分表 - 文章图片
上面是新增了四条数据，可以发现都插入到了第一张表。

若将插入方法修改为no，则表示这个表不能再插入任何数据，如下：

mysql> alter table tb_member insert_method=no;
mysql> insert into tb_member values(16391,‘tom7‘,9);

MySQL 之分区分表 - 文章图片

4、MySQL分区
1）什么是分区？
分区和分表相似，都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表，而分区是将数据分段划分在多个位置存放，分区后，表还是一张表，但数据散列到多个位置了。app读写的时候操作的还是表名字，db自动去组织分区的数据。
分区主要有以下两种形式：
水平分区：这种形式分区是对表的行进行分区，所有在表中定义的列在每个数据集中都能找到，所以表的特性依然得以保持。
举个简单例子：一个包含十年发票记录的表可以被分区为十个不同的分区，每个分区包含的是其中一年的记录。
垂直分区：这种分区方式一般来说是通过对表的垂直划分来减少目标表的宽度，使某些特定的列被划分到特定的分区，每个分区都包含了其中的列所对应的行。
举个简单例子：一个包含了大text和BLOB列的表，这些text和BLOB列又不经常被访问，这时候就要把这些不经常使用的text和BLOB了划分到另一个分区，在保证它们数据相关性的同时还能提高访问速度。
2）查看当前数据库是否支持分区
MySQL 5.6之前，使用下面的参数查看当前配置是否支持分区（如果为yes则表示支持分区）：

mysql> SHOW VARIABLES LIKE ‘%partition%‘;
+-----------------------+---------------+
|Variable_name | Value |
+-----------------------+---------------+
| have_partition_engine | YES |
+-----------------------+------------------+

在5.6及以后采用以下方式查看：

mysql> show plugins;

返回的结果中，有以下字段（如果status列为“ACTIVE”，则表示支持分区）：
MySQL 之分区分表 - 文章图片
3）按照范围（range）方式的表分区

mysql> create table user
    -> (
    -> id int not null auto_increment,
    -> name varchar(30) not null default ‘‘,
    -> sex int(1) not null default ‘0‘,
    -> primary key(id)
    -> )default charset=utf8 auto_increment=1
    -> partition by range(id)
    -> (
    -> partition p0 values less than (3),
    -> partition p1 values less than (6),
    -> partition p2 values less than (9),
    -> partition p3 values less than (12),
    -> partition p4 values less than maxvalue
    -> );

注：在上面创建的表中，当id列的值小于3将会插入到p0分区，大于3小于6的记录将会插入到p1分区，以此类推，所有id值大于12的记录都会插入到p4分区。
4）利用存储过程插入一些数据

mysql> delimiter //
mysql> create procedure adduser()
    -> begin
    -> declare n int;
    -> declare summary int;
    -> set n = 0;
    -> while n <= 20
    -> do
    -> insert into test1.user(name,sex) values("tom",0);
    -> set n=n+1;
    -> end while;
    -> end //
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> delimiter ;
mysql> call adduser();
Query OK, 1 row affected (0.01 sec)

mysql> select * from user;
+----+------+-----+
| id | name | sex |
+----+------+-----+
|  1 | tom  |   0 |
|  2 | tom  |   0 |
|  3 | tom  |   0 |
|  4 | tom  |   0 |
|  5 | tom  |   0 |
|  6 | tom  |   0 |
|  7 | tom  |   0 |

5）到存放数据表文件的目录下看一下：
MySQL 之分区分表 - 文章图片
可以看到数据是被分散存到不同的文件中的，本地的文件名都是“user#P#p0...”命名的，其中p0是自定义的分区名。
6）统计数据行数

mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
|       21 |
+----------+
1 row in set (0.00 sec)

7）从information_schema系统库中的partition表中查看分区信息

mysql> select * from information_schema.partitions where table_schema=‘test1‘ and table_name=‘user‘\G

MySQL 之分区分表 - 文章图片
8）从分区中查询数据

9）添加及合并分区（需要先合并分区再新增分区）
1.添加分区：
注意：由于在创建表的时候，指定的最后一个分区range是maxvalue，所以是无法直接增加分区的，如下：

mysql> alter table user add partition (partition p5 values less than (20));
ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition

大意是：MAXVALUE只能在最后一个分区定义中使用

但也不可以将最后定义了maxvalue的分区直接删除，因为删除分区的话，分区中的数据也会丢失，所以，如果需要新增分区的正确做法，应该是先合并分区，再新增分区，这样才可以保证数据的完整性，如下：

mysql> alter table user  reorganize partition p4 into (partition p03 values less than (15),partition p04 values less than maxvalue );

上述命令的作用就是将最后一个分区分为两个分区，一个是自己所需要的分区，最后一个分区还是maxvalue（也必须是maxvalue），这样就完成了添加分区。
本地表文件如下：
MySQL 之分区分表 - 文章图片
查询新增分区中的数据如下：

2.合并分区
将p0、p1、p2、p3四个分区合并为p02：

mysql> alter table user reorganize partition p0,p1,p2,p3 into 
    -> (partition p02 values less than (12));

可以看到p02将整合了p0,p1,p2,p3三个分区的数据，如下：
MySQL 之分区分表 - 文章图片
本地文件如下：

10）删除分区

mysql> alter table user drop partition p02;

注意：分区被删除后，分区中的数据也将被删除，删除分区p02的表中所有数据如下：
MySQL 之分区分表 - 文章图片

MySQL 之分区分表

标签：都对数据库表操作 ons 作用另一个特定写入包括

本文系统来源：https://blog.51cto.com/14227204/2501071

内容总结

以上是互联网集市为您收集整理的MySQL 之分区分表全部内容，希望文章能够帮你解决MySQL 之分区分表所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/519232.html

来源：【匿名】

【上一篇】pymysql.err.InternalError:【下一篇】用IE远程创建Mysql数据库的简易程序

更多 ►

【MySQL 之分区分表】教程文章相关的互联网学习教程文章

MyCat：对MySQL数据库进行分库分表【代码】

本篇前提： mycat配置正确，且能正常启动。1、schema.xml<table>标签：dataNode -- 分片节点指定（取值：dataNode中的name属性值）rule ------ 分片规则选择（取值：rule标签中的name属性值）123[root@dras-test conf]# vim schema.xml 1 <?xml version="1.0"?> 2 <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> 3 <mycat:schemaxmlns:mycat="http://io.mycat/"> 4 <schemaname="mycatdb"checkSQLschema="false"sqlMax...

mysql 分库分表备份脚本【代码】

#!/bin/bash USER=root #用户 PASSWORD=123456 #密码 MYSQL_PATH=127.0.0.1 #地址 MYSQL_BIN=/bin/mysql MYSQL_DUMP_BIN=/bin/mysqldump MYSQL_CMD="${MYSQL_BIN} -u${USER} -p${PASSWORD} -h${MYSQL_PATH}" DATABASE=($(${MYSQL_CMD} -e "show databases;"|egrep -v "Database|mysql|test|information_schema")) BACKUP_DIR=/data/mysql_backup/$(date +%Y-%y-%d) #备份目录 [ -d ${BACKUP_DIR} ]||mkdir -p ${BACKUP_DIR} fo...

MySQL分库分表方案分析【图】

一、数据库瓶颈不管是IO瓶颈，还是CPU瓶颈，最终都会导致数据库的活跃连接数增加，进而逼近甚至达到数据库可承载活跃连接数的阈值。在业务Service来看就是，可用数据库连接少甚至无连接可用。接下来就可以想象了吧（并发量、吞吐量、崩溃）。1、IO瓶颈第一种：磁盘读IO瓶颈，热点数据太多，数据库缓存放不下，每次查询时会产生大量的IO，降低查询速度 -> 分库和垂直分表。第二种：网络IO瓶颈，请求的数据太多，网络带宽不够 -> 分库...

thinkphp使用mysql百万数据分表快速分页查询【代码】

<?phpnamespace app\admin\controller;use app\admin\common\Base; use think\Db; use think\Session;class Lable extends Base {public function index(){$data = $this->request->param();if (isset($data[‘ajax‘]) && $data[‘ajax‘] == 1) {$page = isset($data[‘page‘]) ? $data[‘page‘] : 1;$page = intval($page);$limit = isset($data[‘rows‘]) ? $data[‘rows‘] : 10;$limit = intval($limit);// $start = $lim...

mysql 数据库优化，分表超作【代码】

CREATETABLEIFNOTEXISTS `table1` ( `id` int(11) NOTNULL AUTO_INCREMENT, `name` varchar(50) DEFAULTNULL, PRIMARYKEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;CREATETABLEIFNOTEXISTS `table2` ( `id` int(11) NOTNULL AUTO_INCREMENT, `name` varchar(50) DEFAULTNULL, PRIMARYKEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;INSERTINTO `table1` (`name`) VALUES(‘name1‘...

mysql 的垂直分表和水平分表

1，水平分表：一条记录一条记录切断分出来！ 2, 垂直分表：把常用的，不常用的，字段很长的拆出来！目前很多互联网系统都存在单表数据量过大的问题，这就降低了查询速度，影响了客户体验。为了提高查询速度，我们可以优化sql语句，优化表结构和索引，不过度那些百万级，千万级的数据库表，即便优化过后，查询速度还是满足不了要求。这时候我们就可以通过分表降低单次查询数据量，从而提高查询速度，一般分表的方式有2种：水平拆...

mysql数据库优化--分库分表【代码】【图】

mysql如何调试　　1.数据库设计三范式　　2.数据库分表分库——水平分割，垂直分割　　3.定位慢查询　　4.存储过程mysql如何实现优化　　1.数据库设计要合理（3F）　　2.添加索引（普通索引，主键索引，唯一索引，全文索引）　　3分表分库技术（取模分表，水平分割，垂直分割）　　4.读写分离（读一个写一个减少IO量）　　5.存储过程　　6.配置MYSQL最大连接数（my.ini文件）　　7.MySQL服务器升级　　8.随时清理碎片化　　9.SQL语...

企业Shell实战-MySQL分库分表备份脚本

本文来自 http://www.xuliangwei.com/xubusi/252.html免费视频讲解见 http://edu.51cto.com/course/course_id-5064.html企业Shell实战-MySQL分库分表备份今天是2015年的最后一天,大家都开心的跨年,而我还在苦逼的呵呵—省略此处内容来自老男孩教育oldboy以及老男孩26期王续精彩分享整理而来为表示感谢,特整理此篇博文分享给大家！项目联系笔者QQ：572891887 也可以加入架构师交流群:471443208 进行交流[root@db02 scripts]# ...

mysql分库分表的基本方法

参考：https://zhuanlan.zhihu.com/p/137368446 （知乎：MySQL：互联网公司常用分库分表方案汇总！）https://learnku.com/articles/35456 （Laravel Eloquent 分表方法并使用模型关联）https://learnku.com/articles/32579 （Laravel自动分表）原文：https://www.cnblogs.com/tkzc2013/p/14210733.html

mysql恢复备份数据时，部分表数据丢失的问题

原因：是由于默认的通信缓冲区的最大长度为50M（max_allowed_packed）。处理办法：//查看当前max_allowed_packet的大小show global variables like ‘max_allowed_packet‘;//修改max_allowed_packet的大小 set global max_allowed_packet=1024*1024*50;原文：https://www.cnblogs.com/GaoAnLee/p/10683422.html

MYSQL 分表原理（转）

简介:引用MySQL官方文档中的一段话:MERGE存储引擎,也被认识为MRG_MyISAM引擎,是一个相同的可以被当作一个来用的MyISAM表的集合."相同"意味着所有表同样的列和索引信息.你不能合并列被以不同顺序列于其中的表,没有恰好同样列的表,或有不同顺序索引的表.而且,任何或者所有的表可以用myisampack来压缩.例子:mysql> show engines;+--------------------+---------+----------------------------------------------------------------...

MYSQL - 技术教程分类

MySQL 教程 MySQL 安装 MySQL 管理 MySQL PHP 语法 MySQL 连接 MySQL 创建数据库 MySQL 删除数据库 MySQL 选择数据库 MySQL 数据类型 MySQL 创建数据表 MySQL 删除数据表 MySQL 插入数据 MySQL 查询数据 MySQL WHERE 子句 MySQL UPDATE 更新 MySQL DELETE 语句 MySQL LIKE 子句 MySQL UNION MySQL 排序 MySQL 分组 MySQL 连接的使用 MySQL NULL 值处理 MySQL 事务 MySQL ALTER命令 MySQL 索引 MySQL 临时表 MySQL 复制表 MySQL 元数据 MySQL 序列使用 MySQL 处理重复数据 MySQL 及 SQL 注入 MySQL 导出数据 MySQL 导入数据 MySQL 函数 MySQL 运算符 mysql 全部

MYSQL - 最热教程

sql分组取最大记录方法 mysql如何设置默认值 mysql创建数据表时指定默认值教程 MySQL 5.7 的初始化操作（root初始密码...Oracle中合并数据集(多行变一行)mysql 相同内容的字段合并为一条的方法解决mysql设置时区时的错误Unknown or ...解决ubuntu下mysql的'Access denied fo...ubuntu系统中MysqlERROR1045(28000)报错...mysql数据库设置不区分大小写

首页 / MYSQL / MySQL 之分区分表

MySQL 之分区分表

内容导读

内容图文

内容总结

内容备注

内容手机端

【MySQL 之分区分表】教程文章相关的互联网学习教程文章

MyCat：对MySQL数据库进行分库分表【代码】

mysql 分库分表备份脚本【代码】

MySQL分库分表方案分析【图】

thinkphp使用mysql百万数据分表快速分页查询【代码】

mysql 数据库优化，分表超作【代码】

mysql 的垂直分表和水平分表

mysql数据库优化--分库分表【代码】【图】

企业Shell实战-MySQL分库分表备份脚本

mysql分库分表的基本方法

mysql恢复备份数据时，部分表数据丢失的问题

MYSQL 分表原理（转）

mysql 主从同步（部分表）【图】

MySQL 常用分库分表方案，都在这里了！【图】

mysql数据表分表策略（转）【代码】

MySQL的分表与分区

MYSQL - 相关标签

MYSQL - 技术教程分类

MYSQL - 最新教程

MYSQL - 最热教程