MySQL学习mysql分区

最新推荐文章于 2023-12-14 20:42:00 发布
最新推荐文章于 2023-12-14 20:42:00 发布
阅读量124 收藏
点赞数
分类专栏： mysql 文章标签： mysql分区

测试数据准备800万条数据，这里通过存储过程导入数据

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
	
 delimiter ;
 call load_part_tab();

 #导入末分区表中数据

 insert into no_part_tab select * from part_tab;

#查询数据库的数量

  SELECT count(*) FROM part_tab;   //8000000

 #测试sql性能

      #分区之后的数据时间

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

       花费时间 0.138s

      #未分区的数据

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

     花费时间1.166s

通过explain关键字进行分析

分区表的数据分析

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

 

未分区表的数据分析

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

结果如下：

对没有分区的数据进行加索引进行测试

create index idx_of_c3 on no_part_tab (c3)

#创建索引执行时间24.008s

创建索引执行

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

花费时间：0.198s

为了验证对未使用所以你的额两个查询再次进行校验：
 

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

'1996-12-31' and c2='hello';   

 #0.291s

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

#查询时间1.216s

= 初步结论 =

* 分区和未分区占用文件空间大致相同 （数据和索引文件）

* 如果查询语句中有未建立索引字段，分区时间远远优于未分区时间

* 如果查询语句中字段建立了索引，分区和未分区的差别缩小，分区略优于未分区。

= 最终结论 =

* 对于大数据量，建议使用分区功能。

* 去除不必要的字段

* 根据手册， 增加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'
);
在这里，将用户表分成4个分区，以每300万条记录为界限，每个分区都有自己独立的数据、索引文件的存放目录，与此同时，这些目录所在的物理磁盘分区可能也都是完全独立的，可以提高磁盘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'
);   
分成4个区，数据文件和索引文件单独存放。

* HASH 类型     
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 HASH (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'
);
分成4个区，数据文件和索引文件单独存放。

例子：
CREATE TABLE ti2 (id INT, amount DECIMAL(7,2), tr_date DATE)
    ENGINE=myisam
    PARTITION BY HASH( MONTH(tr_date) )
    PARTITIONS 6;
 
CREATE PROCEDURE load_ti2()
       begin
    declare v int default 0;
    while v < 80000
    do
        insert into ti2
        values (v,'3.14',adddate('1995-01-01',(rand(v)*3652) mod 365));
         set v = v + 1;
    end while;
    end
    //


* 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'
);   
分成4个区，数据文件和索引文件单独存放。

* 子分区
子分区是针对 RANGE/LIST 类型的分区表中每个分区的再次分割。再次分割可以是 HASH/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 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 分区再次进行子分区划分，子分区采用 HASH 类型。
或者
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 KEY(uid) 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 分区再次进行子分区划分，子分区采用 KEY 类型。

= 分区管理 =

    * 删除分区  
ALERT TABLE users DROP PARTITION p0;
      删除分区 p0。

    * 重建分区
          o RANGE 分区重建
ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000));
            将原来的 p0,p1 分区合并起来，放到新的 p0 分区中。
          o LIST 分区重建
ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES IN(0,1,4,5,8,9,12,13));
            将原来的 p0,p1 分区合并起来，放到新的 p0 分区中。
          o HASH/KEY 分区重建
 ALTER TABLE users REORGANIZE PARTITION COALESCE PARTITION 2;
            用 REORGANIZE 方式重建分区的数量变成2，在这里数量只能减少不能增加。想要增加可以用 ADD PARTITION 方法。
    * 新增分区
          o 新增 RANGE 分区   
 ALTER TABLE category ADD PARTITION (PARTITION p4 VALUES IN (16,17,18,19)
            DATA DIRECTORY = '/data8/data'
            INDEX DIRECTORY = '/data9/idx');
            新增一个RANGE分区。
          o 新增 HASH/KEY 分区
  ALTER TABLE users ADD PARTITION PARTITIONS 8;
            将分区总数扩展到8个。

[ 给已有的表加上分区 ]
alter table results partition by RANGE (month(ttime)) 
(PARTITION p0 VALUES LESS THAN (1),
PARTITION p1 VALUES LESS THAN (2) , PARTITION p2 VALUES LESS THAN (3) ,
PARTITION p3 VALUES LESS THAN (4) , PARTITION p4 VALUES LESS THAN (5) ,
PARTITION p5 VALUES LESS THAN (6) , PARTITION p6 VALUES LESS THAN (7) ,
PARTITION p7 VALUES LESS THAN (8) , PARTITION p8 VALUES LESS THAN (9) ,
PARTITION p9 VALUES LESS THAN (10) , PARTITION p10 VALUES LESS THAN (11),
PARTITION p11 VALUES LESS THAN (12),
PARTITION P12 VALUES LESS THAN (13) ); 


默认分区限制分区字段必须是主键（PRIMARY KEY)的一部分，为了去除此
限制：
[方法1] 使用ID
mysql> ALTER TABLE np_pk
    ->     PARTITION BY HASH( TO_DAYS(added) )
    ->     PARTITIONS 4;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

However, this statement using the id column for the partitioning column is valid, as shown here:

mysql> ALTER TABLE np_pk
    ->     PARTITION BY HASH(id)
    ->     PARTITIONS 4;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0

[方法2] 将原有PK去掉生成新PK
mysql> alter table results drop PRIMARY KEY;
Query OK, 5374850 rows affected (7 min 4.05 sec)
Records: 5374850 Duplicates: 0 Warnings: 0

mysql> alter table results add PRIMARY KEY(id, ttime);
Query OK, 5374850 rows affected (6 min 14.86 sec)
Records: 5374850 Duplicates: 0 Warnings: 0

转自https://blog.csdn.net/tjcyjd/article/details/11194489

tom有了cat
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql 分区学习
weixin_34008784的博客
03-26 67
2019独角兽企业重金招聘Python工程师标准>>> ...
mysql分区表学习
大魔王
05-04 298
学习地址： MySQL :: MySQL 5.7 Reference Manual :: 22 Partitioning 分区简介 查看当前数据库是否支持分区 SELECT PLUGIN_NAME AS NAME, PLUGIN_VERSION AS Version, PLUGIN_STATUS AS STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_TYPE = 'STORAGE ENGINE'; 分区优势： 分区.
MySql分区学习总结一：分区概述
Master_Kin的博客
11-28 205
mysql分区学习总结 通过看书和查资料对mysql的分区进行了简单了解。称还有映像记录一下，以便将来使用时回顾。 1.分区简述 什么是分区？ MySql从5.1以后开始支持分区功能。分区就是根据一定的规则，将数据库中的表分为多个更小的部分。但在逻辑上，还是只有一个表或者一个索引，在逻辑上这个表可能又几个物理分区对象组成。所以分区并不破坏表中数据的原子性，不违背第一范式（简单理解为表中不能有表）。...
MySQL分区学习
cuanzhutang8944的博客
07-08 97
https://dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html 一、 分区概述 分区是一种表设计...
MySQL表分区学习总结
weixin_39169471的博客
02-23 65
MySQL表分区学习总结 通过学习MySQL表分区，整理成知识框架 知识点原文https://my.oschina.net/ydsakyclguozi/blog/393583
MySQL分区表
01-27
优点：提高并发量，减小锁的粒度缺点：代码维护成本高，相关sql都需要改动2、分区，所有的数据还在一个表中，但物理存储数据根据一定的规则存放在不同的文件中，文件也可以放到另外磁盘上优点：代码维护量小，基本...
mysql表分区
08-30
mysql表分区策略，包含range分区、list分区、hash分区等方法介绍及详解
Mysql Mysql学习资料
最新发布
01-13
这份"Mysql Mysql学习资料"涵盖了关于MySQL的基础到高级的知识点，对于想要深入理解数据库管理和开发的人员来说是一份宝贵的资源。 1. **MySQL基础知识** - 数据库概念：理解数据库是什么，以及它在存储和管理数据...
Mysql优化之Zabbix分区优化
09-09
总的来说，对Zabbix进行MySQL分区优化是一项有益于长期稳定运行的重要措施，它可以帮助我们更好地管理和优化监控系统的性能。但需要注意的是，任何优化策略都需要结合实际情况进行，包括硬件资源、Zabbix的使用模式...
详解MySQL分区表
09-08
MySQL分区表是一种高级的数据组织策略，它将一个大表逻辑上划分为多个更小的单元，每个单元称为一个分区。这种技术的主要目的是提高查询性能、简化管理以及优化大数据量的存储。分区通常对应用程序透明，意味着应用...
Mysql 分区大全及讲解
Bingo
10-13 2547
注意：RANGE分区与LIST分区有一定的相似性，RANGE分区是基于一个连续的区间范围分区，而LIST分区是基于一个给定的值列表进行分区，HASH分区与KEY分区类似，HASH分区既可以使用MySQL本身提供的HASH函数进行分区，也可以使用用户自定义的表达式分区，而KEY分区只能使用MySQL本身提供的函数进行分区。与hash分区不同的是，当数据表中存在主键的时候，可以不指定分区键，MySQL默认使用主键作为key分区的分区键。其中，子分区可以使用HASH分区，也可以使用KEY分区。
MySQL分区操作详解
qq_62778995的博客
12-14 1323
分区表是一个独立的逻辑表，但是底层由多个物理子表组成。对分区表的请求，都会通过句柄对象转化成对存储引擎的接口调用。所以分区对于 SOL 层来说是一个完全封装底层实现的黑盒子，对应用是透明的，但是从底层的文件系统来看就很容易发现，每个分区表都有一个使用分隔命名的表文件。PARTITION BY LINEAR HASH(列)PARTITION BY KEY (列名)示例代码运行截图上面的子分区的名称都是按照分区名默认生成的，我们也可为每一个子分区定义具体的分区名和分区路径。
mysql分区资料,Mysql分区 资料
weixin_28312391的博客
03-23 48
追加 Mysql分区 资料RANGE分区按照RANGE分区的表是通过如下一种方式进行分区的，每个分区包含那些分区表达式的值位于一个给定的连续区间内的行//创建range分区表mysql> CREATE TABLE IF NOT EXISTS `user` (-> `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',-> ...
MySQL表分区
热门推荐
atwdy的博客
06-13 1万+
主要介绍mysql几种表分区的基本使用，包括range分区、list分区、hash分区、key分区、子分区和columns分区。
mysql 四种分区方式
小马哥的博客
10-18 5049
mysql 四种分区方式
Mysql分区大全及讲解
weixin_44432032的博客
06-27 7009
1.查看Mysql是否支持分区 show variables like ‘%partition%’; show plugins; 2.基于InnerDB创建分区表 create table tab_name( ->id int not null ->name varchar(30) ->)engine=InnoDB ->partition by hash(id) ->partitions 5; 3.分区的优势 a.存储更多的数据 b.优化查询 c.并行处理 d.快速
mysql分区讲解及操作语句
蓝天⊙白云的博客
07-06 2712
1. 创建分区 （1）创建range分区 根据数值范围： 根据DATE、DATETIME范围： 添加COLUMNS关键字可定义非integer范围及多列范围，不过需要注意COLUMNS括号内只能是列名，不支持函数；多列范围时，多列范围必须呈递增趋势： 根据多列范围： 当需要删除一个分区上的“旧的”数据时，只删除分区即可。如果你使用上面最近的那个例子给出的分区方案，你只需简单地使用”alter table staff drop partition p0;”来删除所有在1991年前就已经停止工作的雇员相对应
MySQL学习之分区一
LancoLiu
05-03 1014
MySQL从5.1版本开始支持分区功能，它允许可设置的一定逻辑，跨文件系统分配单个表的多个部分，但是就访问数据库而言，逻辑上还是只有一个表。还是老样子，在学习新知识之前都先带着问题去寻找想要的答案：1、什么是分区？2、为什么分区？好处在哪？3、如何分区？什么是MySQL分区？一开始也讲了，根据一定逻辑规则，将一个表拆成多个更小更容易管理的部分。例如我们新建一张表利用range分区逻辑上还是只有一张...
MySQL性能优化：分区详解
"MySQL性能优化，通过分区技术对大型数据库进行管理，提高查询效率和数据管理能力。分区方式包括range、list、hash和key分区，分区键需与主键或唯一键关联。分区的优势包括存储更多数据、优化查询、快速删除过期数据...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值