MySQL 分区和分表

最新推荐文章于 2022-02-18 16:30:33 发布
最新推荐文章于 2022-02-18 16:30:33 发布
阅读量549 收藏
点赞数
分类专栏： MySQL 文章标签： mysql 高性能 数据 class hash
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Thousa_Ho/article/details/77803381
版权

MySQL 分区

自5.1开始对分区(Partition)有支持

水平分区

根据列属性按行划分

  1. Range（范围）: 允许DBA将数据划分成不同范围，例如按照时间范围进行划分
  2. Hash（哈希）：允许通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同值对数据区域进行划分
  3. Key（键值）：Hash Key是MySQL系统产生的
  4. List（预定义列表） 允许系统通过DBA定义的列表的值所对应的行数据进行分割
  5. Composite (复合模式）：以上模式的组合

垂直分区

按照列属性进行划分

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

分区实验

1.首先创建分区表 按照日期年份拆分

mysql> CREATE TABLE part_tab ( c1 int default NULL, c2 varchar(30) default NULL, c3 date default NULL) engine=myisam   
PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (1995),  
PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) ,  
PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) ,  
PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) ,  
PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) ,  
PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010),  
PARTITION p11 VALUES LESS THAN MAXVALUE );

2.接下来创建未分区表进行方便进行对比

mysql> create table no_part_tab (c1 int(11) default NULL,c2 varchar(30) default NULL,c3 date default NULL) engine=myisam;

3.使用存储过程存入800W条测试数据

mysql> CREATE PROCEDURE load_part_tab()  
       begin  
    declare v int default 0;  
    while v < 8000000  
    do  
        insert into part_tab  
        values (v,'testing partitions',adddate('1995-01-01',(rand(v)*36520) mod 3652));  
         set v = v + 1;  
    end while;  
    end
mysql> call load_part_tab(); 
mysql> insert into no_part_tab select * from part_tab;

接下来开始测试SQL性能

先分别查询两个表中的记录条数

分区表

mysql> select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';  
+----------+
| count(*) |
+----------+
|   795181 |
+----------+
1 row in set (0.55 sec)

未分区表

mysql> select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';   
+----------+
| count(*) |
+----------+
|   795181 |
+----------+
1 row in set (4.69 sec)

结果表明分区表比未分区表的执行时间少90%。

通过explain语句来分析执行过程

mysql > explain select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'

执行结果:注意ROWS是800W条

           id: 1
select_type: SIMPLE
        table: no_part_tab
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 8000000
        Extra: Using where
1 row in set (0.00 sec)

再看分区表的

mysql> explain select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'

结果可以看到检索条数只有70多w

         id: 1
select_type: SIMPLE
        table: part_tab
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 798458
        Extra: Using where
1 row in set (0.00 sec)

检验创建索引之后的情况

mysql> create index idx_of_c3 on no_part_tab (c3);
mysql> create index idx_of_c3 on part_tab (c3);

再次检索数据

mysql> select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';

结果明显块了很多

select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';
受影响的行: 0
时间: 3.977s

检索分区表

mysql> select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';  
select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';
受影响的行: 0
时间: 0.494s

进一步的实验 增加未检索字段查询

mysql> select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date  
'1996-12-31' and c2='hello';  
[SQL]select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date  
'1996-12-31' and c2='hello';
受影响的行: 0
时间: 0.712s

未分区表

mysql> select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31' and c2='hello';
[SQL]select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date  
'1996-12-31' and c2='hello';
受影响的行: 0
时间: 9.763s

结论

  • 分区和未分区占用文件的空间大致是一致的（数据和索引文件）
  • 如果查询中存在未建立索引字段 分区表时间优于未分区表时间
  • 如果查询中字段建立了索引 分区和未分区的差别缩小 分区略优于未分区
  • 对于大数据量 建议使用分区功能
  • 去除不必要的字段
  • 根据手册， 增加myisam_max_sort_file_size 会增加分区性能

分区例子

Range类型

CREATE TABLE users (  
       uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  
       name VARCHAR(30) NOT NULL DEFAULT '',  
       email VARCHAR(30) NOT NULL DEFAULT ''  
)  
PARTITION BY RANGE (uid) (  
       PARTITION p0 VALUES LESS THAN (3000000)  
       DATA DIRECTORY = '/data0/data'  
       INDEX DIRECTORY = '/data1/idx',  

       PARTITION p1 VALUES LESS THAN (6000000)  
       DATA DIRECTORY = '/data2/data'  
       INDEX DIRECTORY = '/data3/idx',  

       PARTITION p2 VALUES LESS THAN (9000000)  
       DATA DIRECTORY = '/data4/data'  
       INDEX DIRECTORY = '/data5/idx',  

       PARTITION p3 VALUES LESS THAN MAXVALUE     DATA DIRECTORY = '/data6/data'   
       INDEX DIRECTORY = '/data7/idx'  
);

user表分成4个分区 每分区300W条记录为界限 每个分区都有自己独立的数据、索引文件的存放目录，这些目录所在的物理磁盘分区可能也是完全独立的，可以提高磁盘IO吞吐量

List类型

CREATE TABLE category (  
     cid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  
     name VARCHAR(30) NOT NULL DEFAULT ''  
)  
PARTITION BY LIST (cid) (  
     PARTITION p0 VALUES IN (0,4,8,12)  
     DATA DIRECTORY = '/data0/data'   
     INDEX DIRECTORY = '/data1/idx',  

     PARTITION p1 VALUES IN (1,5,9,13)  
     DATA DIRECTORY = '/data2/data'  
     INDEX DIRECTORY = '/data3/idx',  

     PARTITION p2 VALUES IN (2,6,10,14)  
     DATA DIRECTORY = '/data4/data'  
     INDEX DIRECTORY = '/data5/idx',  

     PARTITION p3 VALUES IN (3,7,11,15)  
     DATA DIRECTORY = '/data6/data'  
     INDEX DIRECTORY = '/data7/idx'  
);

Key类型

CREATE TABLE users (  
     uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  
     name VARCHAR(30) NOT NULL DEFAULT '',  
     email VARCHAR(30) NOT NULL DEFAULT ''  
)  
PARTITION BY KEY (uid) PARTITIONS 4 (  
     PARTITION p0  
     DATA DIRECTORY = '/data0/data'  
     INDEX DIRECTORY = '/data1/idx',  

     PARTITION p1  
     DATA DIRECTORY = '/data2/data'   
     INDEX DIRECTORY = '/data3/idx',  

     PARTITION p2   
     DATA DIRECTORY = '/data4/data'  
     INDEX DIRECTORY = '/data5/idx',  

     PARTITION p3   
     DATA DIRECTORY = '/data6/data'  
     INDEX DIRECTORY = '/data7/idx'  
);

子分区

CREATE TABLE users (  
     uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  
     name VARCHAR(30) NOT NULL DEFAULT '',  
     email VARCHAR(30) NOT NULL DEFAULT ''  
)  
PARTITION BY RANGE (uid) SUBPARTITION BY HASH (uid % 4) SUBPARTITIONS 2(  
     PARTITION p0 VALUES LESS THAN (3000000)  
     DATA DIRECTORY = '/data0/data'  
     INDEX DIRECTORY = '/data1/idx',  

     PARTITION p1 VALUES LESS THAN (6000000)  
     DATA DIRECTORY = '/data2/data'  
     INDEX DIRECTORY = '/data3/idx'  
);

针对RANGE/LIST类型的分区表中每个分区再次分割 再次分割可以是HASH/KEY等类型

删除分区

ALERT TABLE users DROP PARTITION p0;

重建分区

Range分区重建

ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000));

List分区重建

ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES IN(0,1,4,5,8,9,12,13));

Hash/KEY分区重建

ALTER TABLE users REORGANIZE PARTITION COALESCE PARTITION 2;

分区和分表的区别

实现方式上

a）mysql的分表是真正的分表，一张表分成很多表后，每一个小表都是完正的一张表，都对应三个文件，一个.MYD数据文件，.MYI索引文件，.frm表结构文件。

[root@BlackGhost test]# ls |grep user  
alluser.MRG  
alluser.frm  
user1.MYD  
user1.MYI  
user1.frm  
user2.MYD  
user2.MYI  
user2.frm

总表他不是一张表，没有数据，数据都放在分表里面，可以把总表理解成一个外壳，或者是联接池。

[root@BlackGhost test]# cat alluser.MRG |more  
user1  
user2  
#INSERT_METHOD=LAST

b）分区不一样，一张大表进行分区后，他还是一张表，不会变成二张表，但是他存放数据的区块变多了

[root@BlackGhost test]# ls |grep aa  
aa#P#p1.MYD  
aa#P#p1.MYI  
aa#P#p3.MYD  
aa#P#p3.MYI  
aa.frm  
aa.par

从上面我们可以看出，aa这张表，分为二个区，p1和p3，本来是三个区，被我删了一个区。我们都知道一张表对应三个文件.MYD,.MYI,.frm。分区呢根据一定的规则把数据文件和索引文件进行了分割，还多出了一个.par文件，打开.par文件后你可以看出他记录了，这张表的分区信息，根分表中的.MRG有点像。分区后，还是一张，而不是多张表。

数据处理上

a），分表后，数据都是存放在分表里，总表只是一个外壳，存取数据发生在一个一个的分表里面。看下面的例子：
select * from alluser where id='12'表面上看，是对表alluser进行操作的，其实不是的。是对alluser里面的分表进行了操作。
b），分区呢，不存在分表的概念，分区只不过把存放数据的文件分成了许多小块，分区后的表呢，还是一张表。数据处理还是由自己来完成。

提高性能上

a）分表后，单表的并发能力提高了，磁盘I/O性能也提高了。并发能力为什么提高了呢，因为查寻一次所花的时间变短了，如果出现高并发的话，总表可以根据不同的查询，将并发压力分到不同的小表里面。磁盘I/O性能怎么搞高了呢，本来一个非常大的.MYD文件现在也分摊到各个小表的.MYD中去了

b）mysql提出了分区的概念，我觉得就想突破磁盘I/O瓶颈，想提高磁盘的读写能力，来增加mysql性能。
在这一点上，分区和分表的测重点不同，分表重点是存取数据时，如何提高mysql并发能力上；而分区呢，如何突破磁盘的读写能力，从而达到提高mysql性能的目的。

heqianqiann
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql分库分区分表
阿东的博客
06-12 7145
1.分库分区分表概念分区    就是把一张表的数据分成N个区块，在逻辑上看最终只是一张表，但底层是由N个物理区块组成的分表    就是把一张数据量很大的表按一定的规则分解成N个具有独立存储空间的实体表。系统读写时需要根据定义好的规则得到对应的字表明，然后操作它。表名可以按照某种业务hash进行映射。分库    一旦分表，一个库中的表会越来越多2.实现    2.1分区的实现方式（简单）    my...
mysql分表和分区的区别浅析
09-10
MySQL中的分表和分区是两种不同的数据库优化策略，它们都在一定程度上解决了大数据量下的查询效率问题，但原理和实现方式有所不同。 **一、分表** 分表，顾名思义，是将一张大表分解为多张小表，每个小表都是独立...
MySQL的分区和分表
彳亍
02-23 325
1. 分区MySQL中的分区是指将一个数据表按照某种规则（如时间范围、哈希等），划分为多个区块，各个区块所属的数据文件是相互独立的。分区对于SQL层来说，是一个完全封装的、底层实现的黑盒子；但对于底层的文件系统来说，每一个区块都对应 一个使用 # 分割命名的表文件。分区的一个主要目的是将数据按照一个较粗的粒度划分到不同的分区表中，这样，可以将相关的数据存放在一起，另外，如果想一次删除整个分区的数据...
mysql分区和分表
热门推荐
进化的深山猿
07-17 2万+
mysql分区和分表？ 分区 1）为什么要分区？ 如果一张表的数据量太大的话，索引和数据空间会很大，查找数据就会变的很慢。这个时候我们可以利用mysql的分区功能 2）分区的具体实现： 分为range分区，list，hash，key List分区实现： mysql&gt; CREATE TABLE IF NOT EXISTS `list_part` (   -&gt;   `id`...
Mysql的分区与分表
漫
02-07 356
为什么要进行分区和分表呢? 当一张表的数据非常多的时候,比如单个.myd文件都达到10G, 这时,必然读取起来效率降低. 可不可以把表的数据分开在几张表上? 可以的. 什么是分表，从表面意思上看呢，就是把一张表分成N多个小表 什么是分区，分区呢就是把一张表的数据分成N多个区块，这些区块可以在同一个磁盘上，也可以在不同的磁盘上 1: 从业务角度可以解决.(分表) 比如, 通过id%10 ,
mysql 数据库性能提升，分表分区请收藏
02-18 605
现在企业中，数据库选择用 MySQL 的还是非常非常多的，公司比较规范的，在立项之初，就会认真讨论数据库的设计，而更多的中小微企业，可能就不会那么系统性的讨论数据库的设计了。 但是，不管怎么样，随着数据量的增加，或迟或早，都会遇到数据库性能瓶颈。数据库性能问题，套上那句话“不是没有，只是时机未到”。当数据量比较少时，基本不会有性能问题，在数据量比较多时，性能问题就会出现了。 今天，我们不是讨论使用层面上怎么优化脚本，我们讲讲数据库的设计，分表和分区，通过对某些特定的表减负，缩短查询时间，从而提高性能。.
MySQL分区和分表技术总结.docx
10-24
MySQL 分表和分区的关系 1. 都能提高 MySQL 的性能，在高并发状态下都有一个良好的表现。 2. 分表和分区不矛盾，可以相互配合的，对于那些大访问量，并且表数据比较多的表，我们可以采取分表和分区结合的方式。 3. ...
MySQL 分区和分表的干货总结.doc
10-24
MySQL 分区和分表的干货总结.doc
MySQL分表和分区最佳攻略word
08-03
MySQL分表和分区最佳攻略 word版本，
什么是分表和分区 MySql数据库分区和分表方法
09-09
MySQL数据库的分表和分区是两种常见的优化策略，旨在应对大数据量带来的性能问题。当一个表包含数百万甚至上千万的记录时，查询和插入操作可能会变得非常缓慢，导致整个数据库系统的性能下降。分表和分区的目标是...
MySQL分表和分区最佳攻略
10-12
常开发中我们经常会遇到大表的情况，所谓的大表是指存储了百万级乃至千万级条记录的表。这样的表过于庞大，导致数据库在查询和插入的时候耗时太长，性能低下，如果涉及联合查询的情况，性能会更加糟糕。分表和表分区的目的就是减少数据库的负担，提高数据库的效率，通常点来讲就是提高表的增删改查效率。
mysql分区与分表
qq_42849214的博客
07-01 1383
当数据库表数据量多起来了 一般100w以上了，这时候查询 特别是连表查询的时候就会非常慢了，这时我们就要想着将数据量细分 不用查那么多数据 因为很多数据并不是我们所需要的，所以我们就要想着分，分是现在一个很重要的编程思想，从以前的集中式服务到现在的分布式服务，微服务等，都散发着分和细化的思想。编程思想很重要，大家一定要多去了解了解 mysql现在基本上都是InnoDB引擎了 下面所讲的默认是innodb引擎 分区 这里讲的分区，此“区”非彼“区”，这里讲的分...
mysql分区分表
LC900730的博客
10-19 460
分表分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表。行锁是加在索引上的，不是加在数据记录上的，如果sql没有用到索引依然会锁表分区表的请求，会通过句柄对象转化成对存储引擎的接口调用，所以分区对于SQL来说是一个完全封装层实现的黑盒子，对应用是透明的。但是底层文件可以看出，每一个分区表都有一个使用#分隔命名的表文件。创建分区表使用Partition by，在执行查询的时候，优化器会根
MYSQL分表与分区
weixin_34194379的博客
12-27 998
什么是分表分区分表分区的区别实现方式上数据处理上提高性能上实现的难易度上mysql分表和分区的联系如何分区概述分区技术支持分区类型及举例注意应用场景示例订单表比预想中扩张速度快坑爹的日志表每半月一个分区，自动维护备注RDS for MySQL 对表分区的限制RDS是否需要自己做分表、读写分离RDS MySQL的单表尺寸限制 PS：分库能够突破单机瓶颈 什么是分表分区 分表 ...
mysql 数据表分区与分表介绍
小洪帽i的博客
08-10 1202
什么是分表？ 分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，我们可以称为子表，每个表都对应三个文件，MYD数据文件，MYI索引文件，frm表结构文件。这些子表可以分布在同一块磁盘上，也可以在不同的机器上。用户读写的时候根据事先定义好的规则得到对应的子表名，然后去操作它。 什么是分区？ 分区和分表相似，都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表，而分区是将数据分段划分在多个位置存放，可以是同一磁盘也可以在不同的机器。分区后，表面上还是一张表，但数据散列到多个..
MySQL分区和分表的概念
木子叶的奔跑
09-11 388
文章目录一、概念二、分区1.分区的类型：2.分区管理3.分区应该注意的事项：三、分表1.垂直分表2.水平分表（1）按时间结构（2）按版块结构（3）按哈希结构 一、概念 1.为什么要分表和分区？ 日常开发中我们经常会遇到大表的情况，所谓的大表是指存储了百万级乃至千万级条记录的表。这样的表过于庞大，导致数据库在查询和插入的时候耗时太长，性能低下，如果涉及联合查询的情况，性能会更加糟糕。分表和表分区的目的就是减少数据库的负担，提高数据库的效率，通常点来讲就是提高表的增删改查效率。 2.什么是分表？ 分表是将一个大
MySQL分表分区
qq_40813329的博客
11-12 1631
分表 MySQL分表的话就是创建一个’外壳’表，向这个表插入数据会放到其他表里面 CREATE TABLE `alluser` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, `sex` int(1) NOT NULL DEFAULT '0', KEY `id` (`id`) ) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST
Mysql分表和表分区详解
qq_35622837的博客
11-26 655
Mysql分表和表分区详解 为什么要分表和分区？ 日常开发中我们经常会遇到大表的情况，所谓的大表是指存储了百万级乃至千万级条记录的表。这样的表过于庞大，导致数据库在查询和插入的时候耗时太长，性能低下，如果涉及联合查询的情况，性能会更加糟糕。分表和表分区的目的就是减少数据库的负担，提高数据库的效率，通常点来讲就是提高表的增删改查效率。   什么是分表？ 分表就是将一个大表按照一定的规则分解...
mysql分表和分区最佳攻略_mysql分区和分表哪个好
最新发布
09-06
MySQL是一个广泛使用的关系型数据库管理系统，有时候当数据量增加时，为了提高查询效率和管理数据，我们会考虑使用分表或者分区。 分表是将一个表按照某种规则拆分成多个独立的表，每个表包含相同的字段结构，但是...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值