搜索
首页数据库mysql教程Mysql分区管理基本操作_MySQL

初探:

很长时间没写博客了,这两天一直在学习Mysql分区,总结下:

Mysql支持水平分区,并不支持垂直分区;

水平分区:指将同一表中不同行的记录分配到不同的物理文件中;

垂直分区:指将同一表中不同列的记录分配到不同的物理文件中;

其中CSV、FEDORATED、MERGE等引擎不支持分区,MYISAM、InnoDB、NDB等引擎支持分区

目的:

将一个表或索引分解为多个更小、更可管理的部分,从逻辑上讲,只有一个表或者索引,但是物理上这个表或者索引可能由数十个物理分区组成;没个分区都是独立的对象,可以独自处理,也可以作为一个更大对象的一部分进行处理(如果分区表很大,亦可以将分区分配到不同的磁盘上去);在执行查询的时候,优化器会根据分区定义过滤哪些没有我们需要数据的分区,这样查询就无须全表扫描所有分区,只查找包含需要数据的分区即可

适用场景:

1、表非常大以至于无法全部都放到内存,或者只在表的最后部分有热点数据,其他均为历史数据
2、分区表数据更容易维护(可独立对分区进行优化、检查、修复及批量删除大数据可以采用drop分区的形式等)
3、分区表的数据可以分布在不同的物理设备上,从而高效地利用多个硬件设备
4、分区表可以避免某些特殊的瓶颈(ps: InnoDB的单个索引的互斥访问、ext3文件系统的inode锁竞争等)
5、可以备份和恢复独立的分区,非常适用于大数据集的场景

分区表限制:
  1. 单表最多支持1024个分区
  2. MySQL5.1只能对数据表的整型列进行分区,或者数据列可以通过分区函数转化成整型列;MySQL5.5的RANGE LIST类型可以直接使用列进行分区
  3. 如果分区字段中有主键或唯一索引的列,那么所有的主键列和唯一索引列都必须包含进来
  4. 分区表无法使用外键约束
  5. 分区必须使用相同的Engine
  6. 对于MyISAM分区表,不能在使用LOAD INDEX INTO CACHE操作
  7. 对于MyISAM分区表,使用时会打开更多的文件描述符(单个分区是一个独立的文件)
分区策略:
  1. 全量扫描数据,不需要任何索引:通过where条件大概定位哪个分区,必须将查询所需要扫描的分区个数限制在很小的数量
  2. 建立分区索引,分离热点:如将明显的热点数据分离到一个分区,使其尽量缓存到内存中,这样就能充分使用索引和缓存

    注意:以上策略均以查询得到过滤,丢掉额外的分区,分区本身不产生额外的代价为准则】
分区表使用过程的坑坑:
  1. NULL值会使分区过滤无效:

    分表的表达式的值可以是NULL,第一个分区为特殊分区存放NULL或者非法值

    如: PARTITION BY RANGE YEAR(order_date)进行分区,那么order_date为NULL或者非法值,记录存放在第一个分区:

    WHERE order_date BETWEEN ‘2014-01-01’ AND ‘2014-01-31’查询时会检查两个分区:

    第一个分区及1月份分区,避免第一分区数据过大时造成查询代价过高,可以使用:建立第一分区专门存放order_date为NULL和非法值记录
    PARTITION p_nulls VALUES LESS THAN(0)

    MySQL5.5以后可以才用一下语法解决问题:
    PARTITION BY RANGE COLUMNS(order_date)

2.分区列和索引列不匹配

此种情况下查询无法进行分区过滤,分区失效除非查询中包含了可以过滤分区的条件

3.RANGE类型分区随着分区数量增加会对MYSQL额外增加查询分区定义列表(符合条件行在哪个分区)的压力,尽量限制适当的分区数量;key和hash类型分区不存在此问题

4.重组分区或者类似alter语句可能会造成很大的开销

新建或者删除分区操作很快,重组分区或者类似ALTER语句操作会先创建一个临时的分区,将数据复制其中,然后在删除原分区

分区表类型:

1.RANGE分区:行数据基于属于一个给定连续区间的列值被放入分区

MySQL5.5开始支持RANGE COLUMNS的分区(引入Columns分区解决了MySQL 5.5版本之前RANGE分区和LIST分区只支持整数分区,从而导致需要额外的函数计算得到整数或者通过额外的转换表来转换为整数再分区的问题。Columns分区可以细分为RANGE Columns分区和LIST Columns分区,RANGE Columns分区和LIST Columns分区都支持整数、日期时间、字符串三大数据类型)

2.LIST分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

MySQL5.5开始支持RANGE COLUMNS的分区
3.HASH分区:根据用户自定义的表达式的返回值来进行分区,返回值不能为负数
4.KEY分区:根据MySQLS数据库提供的哈希函数来进行分区
【注:无论创建何种类型的分区,如果表中存在主键或唯一索引时,分区列必须是唯一索引的一个组成部分】

分区相关查询:
查看当前数据库是否支持分区mysql> show variables like '%partition%';+---------------------------------------+-------+| Variable_name | Value |+---------------------------------------+-------+| have_partitioning | YES || innodb_adaptive_hash_index_partitions | 1 |+---------------------------------------+-------+2 rows in set查看创建分区表的CREATE语句mysql>show create table operation_log;查看表是否为分区表(Create_options)mysql>show table status(当前库所有表状态)mysql>show table status from lockrank like '%operation_log%';(lockrank库operation_log表状态)*************************** 1. row ***************************Table: operation_logCreate Table: CREATE TABLE `operation_log` (  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,  `cid` mediumint(7) unsigned NOT NULL,  `accountid` mediumint(8) NOT NULL DEFAULT '0' ,  `flag` tinyint(1) unsigned NOT NULL DEFAULT '0',  `addtime` int(11) unsigned NOT NULL,  `device` tinyint(1) unsigned NOT NULL DEFAULT '1' ,  PRIMARY KEY (`id`,`addtime`),  KEY `idx_accountid_addtime` (`accountid`,`addtime`),  KEY `idx_accountid_flag` (`accountid`,`flag`),) ENGINE=InnoDB AUTO_INCREMENT=50951039 DEFAULT CHARSET=utf8 COMMENT='操作记录'/*!50100 PARTITION BY RANGE (addtime)(PARTITION `2013-05` VALUES LESS THAN (1370016000) ENGINE = InnoDB, PARTITION `2013-06` VALUES LESS THAN (1372608000) ENGINE = InnoDB, PARTITION `2013-07` VALUES LESS THAN (1375286400) ENGINE = InnoDB, PARTITION `2013-08` VALUES LESS THAN (1377964800) ENGINE = InnoDB, PARTITION `2013-09` VALUES LESS THAN (1380556800) ENGINE = InnoDB, PARTITION `2013-10` VALUES LESS THAN (1383235200) ENGINE = InnoDB, PARTITION `2013-11` VALUES LESS THAN (1385827200) ENGINE = InnoDB, PARTITION `2013-12` VALUES LESS THAN (1388505600) ENGINE = InnoDB, PARTITION `2014-01` VALUES LESS THAN (1391184000) ENGINE = InnoDB, PARTITION `2014-02` VALUES LESS THAN (1393603200) ENGINE = InnoDB, PARTITION `2014-03` VALUES LESS THAN (1396281600) ENGINE = InnoDB, PARTITION `2014-04` VALUES LESS THAN (1398873600) ENGINE = InnoDB, PARTITION `2014-05` VALUES LESS THAN (1401552000) ENGINE = InnoDB, PARTITION `2014-06` VALUES LESS THAN (1404144000) ENGINE = InnoDB, PARTITION `2014-07` VALUES LESS THAN (1406822400) ENGINE = InnoDB, PARTITION `2014-08` VALUES LESS THAN (1409500800) ENGINE = InnoDB, PARTITION `2014-09` VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */1 row in set (0.00 sec)查看select如何使用分区mysql> explain partitions select id,accountid,cid,flag from operation_log where addtime="1369362524" /G ; *************************** 1. row ***************************   id: 1  select_type: SIMPLEtable: operation_log   partitions: 2013-05 type: ALLpossible_keys: NULL  key: NULL  key_len: NULL  ref: NULL rows: 4384356Extra: Using where1 row in set (0.00 sec)``分区表元数据统计表:INFORMATION_SCHEMA.PARTITIONS查看分区表operation_log的分区信息mysql> SELECT partition_name part, partition_expression expr, partition_description descr, table_rows FROM INFORMATION_SCHEMA.partitions WHERE TABLE_SCHEMA = schema() AND TABLE_NAME='operation_log';+---------+---------+------------+------------+| part| expr| descr | table_rows |+---------+---------+------------+------------+| 2013-05 | addtime | 1370016000 | 5999642 || 2013-06 | addtime | 1372608000 | 4579263 || 2013-07 | addtime | 1375286400 | 3223772 || 2013-08 | addtime | 1377964800 | 1995058 || 2013-09 | addtime | 1380556800 | 2497406 || 2013-10 | addtime | 1383235200 | 4106974 || 2013-11 | addtime | 1385827200 | 6209559 || 2013-12 | addtime | 1388505600 | 6415349 || 2014-01 | addtime | 1391184000 | 3953594 || 2014-02 | addtime | 1393603200 | 0 || 2014-03 | addtime | 1396281600 | 0 || 2014-04 | addtime | 1398873600 | 0 || 2014-05 | addtime | 1401552000 | 0 || 2014-06 | addtime | 1404144000 | 0 || 2014-07 | addtime | 1406822400 | 0 || 2014-08 | addtime | 1409500800 | 0 || 2014-09 | addtime | MAXVALUE | 0 |+---------+---------+------------+------------+17 rows in set (1.48 sec)
创建分区操作
RANGE分区:mysql> CREATE TABLE `operation_log` ( ->  `id` int(11) unsigned NOT NULL AUTO_INCREMENT, -> `cid` mediumint(7) unsigned NOT NULL, -> `accountid` mediumint(8) NOT NULL DEFAULT '0' , ->  `flag` tinyint(1) unsigned NOT NULL DEFAULT '0', ->  `addtime` int(11) unsigned NOT NULL, -> `device` tinyint(1) unsigned NOT NULL DEFAULT '1' , ->  PRIMARY KEY (`id`,`addtime`), -> KEY `idx_accountid_addtime` (`accountid`,`addtime`), ->  KEY `idx_accountid_flag` (`accountid`,`flag`), ->) ENGINE=InnoDB AUTO_INCREMENT=50951039 DEFAULT CHARSET=utf8 COMMENT='操作记录' ->/*!50100 PARTITION BY RANGE (addtime) ->(PARTITION `2013-05` VALUES LESS THAN (1370016000) ENGINE = InnoDB, -> PARTITION `2013-06` VALUES LESS THAN (1372608000) ENGINE = InnoDB, -> PARTITION `2013-07` VALUES LESS THAN (1375286400) ENGINE = InnoDB, -> PARTITION `2013-08` VALUES LESS THAN (1377964800) ENGINE = InnoDB, -> PARTITION `2013-09` VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;1 row in set (0.00 sec)( LESS THAN MAXVALUE考虑到可能的最大值)list分区//这种方式失败mysql> CREATE TABLE IF NOT EXISTS `list_part` ( ->   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID', ->   `province_id` int(2) NOT NULL DEFAULT 0 COMMENT '省', ->   `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称', ->   `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0为男,1为女', ->   PRIMARY KEY (`id`) -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 -> PARTITION BY LIST (province_id) ( -> PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8), -> PARTITION p1 VALUES IN (9,10,11,12,16,21), -> PARTITION p2 VALUES IN (13,14,15,19), -> PARTITION p3 VALUES IN (17,18,20,22,23,24) -> );ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function//这种方式成功mysql> CREATE TABLE IF NOT EXISTS `list_part` ( ->   `id` int(11) NOT NULL  COMMENT '用户ID', ->   `province_id` int(2) NOT NULL DEFAULT 0 COMMENT '省', ->   `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称', ->   `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0为男,1为女' -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8 -> PARTITION BY LIST (province_id) ( -> PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8), -> PARTITION p1 VALUES IN (9,10,11,12,16,21), -> PARTITION p2 VALUES IN (13,14,15,19), -> PARTITION p3 VALUES IN (17,18,20,22,23,24) -> );Query OK, 0 rows affected (0.33 sec)上面的这个创建list分区时,如果有主銉的话,分区时主键必须在其中,不然就会报错。如果我不用主键,分区就创建成功了,一般情况下,一个张表肯定会有一个主键,这算是一个分区的局限性hash分区mysql> CREATE TABLE IF NOT EXISTS `hash_part` ( ->   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '评论ID', ->   `comment` varchar(1000) NOT NULL DEFAULT '' COMMENT '评论', ->   `ip` varchar(25) NOT NULL DEFAULT '' COMMENT '来源IP', ->   PRIMARY KEY (`id`) -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 -> PARTITION BY HASH(id) -> PARTITIONS 3;Query OK, 0 rows affected (0.06 sec)key分区 mysql> CREATE TABLE IF NOT EXISTS `key_part` ( ->   `news_id` int(11) NOT NULL  COMMENT '新闻ID', ->   `content` varchar(1000) NOT NULL DEFAULT '' COMMENT '新闻内容', ->   `u_id` varchar(25) NOT NULL DEFAULT '' COMMENT '来源IP', ->   `create_time` DATE NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '时间' -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8 -> PARTITION BY LINEAR HASH(YEAR(create_time)) -> PARTITIONS 3;Query OK, 0 rows affected (0.07 sec)

增加子分区操作:

子分区是分区表中每个分区的再次分割,子分区既可以使用HASH希分区,也可以使用KEY分区。这 也被称为复合分区(composite partitioning)。

1. 如果一个分区中创建了子分区,其他分区也要有子分区2. 如果创建了了分区,每个分区中的子分区数必有相同3. 同一分区内的子分区,名字不相同,不同分区内的子分区名子可以相同(5.1.50不适用) mysql> CREATE TABLE IF NOT EXISTS `sub_part` ( ->   `news_id` int(11) NOT NULL  COMMENT '新闻ID', ->   `content` varchar(1000) NOT NULL DEFAULT '' COMMENT '新闻内容', ->   `u_id` int(11) NOT NULL DEFAULT 0s COMMENT '来源IP', ->   `create_time` DATE NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '时间' -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8 -> PARTITION BY RANGE(YEAR(create_time)) -> SUBPARTITION BY HASH(TO_DAYS(create_time))( -> PARTITION p0 VALUES LESS THAN (1990)(SUBPARTITION s0,SUBPARTITION s1,SUBPARTITION s2), -> PARTITION p1 VALUES LESS THAN (2000)(SUBPARTITION s3,SUBPARTITION s4,SUBPARTITION good), -> PARTITION p2 VALUES LESS THAN MAXVALUE(SUBPARTITION tank0,SUBPARTITION tank1,SUBPARTITION tank3) -> );Query OK, 0 rows affected (0.07 sec)

分区管理:

增加分区操作(针对设置MAXVALUE) range添加分区mysql>alter table operation_log add  partition(partition `2013-10` values less than (1383235200));  --->适用于没有设置MAXVALUE的分区添加   ERROR 1481 (HY000):MAXVALUE can only be used in last partition definitionmysql>alter table operation_log REORGANIZE partition `2013-09` into (partition `2013-09` values less than (1380556800),partition `2013-10` values less than (1383235200),partition `2013-11` values less than maxvalue); list添加分区mysql> alter table list_part add partition(partition p4 values in (25,26,28));Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0 hash重新分区mysql> alter table list_part add partition(partition p4 values in (25,26,28));Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0 key重新分区mysql> alter table key_part add partition partitions 4;Query OK, 1 row affected (0.06 sec)//有数据也会被重新分配Records: 1  Duplicates: 0  Warnings: 0子分区添加新分区,虽然我没有指定子分区,但是系统会给子分区命名的mysql> alter table sub1_part add partition(partition p3 values less than MAXVALUE);Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0

删除分区操作

alter table user drop partition `2013-05`;
分区表其他操作
重建分区(官方:与先drop所有记录然后reinsert是一样的效果;用于整理表碎片)alter table operation_log rebuild partition `2014-01`;重建多个分区alter table operation_log rebuild partition `2014-01`,`2014-02`;过程如下:pro优化分区(如果删除了一个分区的大量记录或者对一个分区的varchar blob text数据类型的字段做了许多更新,此时可以对分区进行优化以回收未使用的空间和整理分区数据文件)alter table operation_log  optimize  partition `2014-01`;优化的操作相当于check partition,analyze partition 和repair patition分析分区alter table operation_log  analyze partition  `2014-01`;修复分区alter table operation_log repair partition   `2014-01`;检查分区alter table operation_log check  partition   `2014-01`;

注释:

  1. mysqlcheck、myisamchk并不支持分区表,analyze,check,optimize,rebuild,repair,truncate不支持子分区操作
  2. 在MySQL5.6中,可以使用清空一个分区数据:alter table operation_log truncate partition 2014-01;
  3. 清空该分区表所有分区数据:alter table operation_log truncate partition all;

参考文档:

http://blog.51yip.com/mysql/1013.html

https://dev.mysql.com/doc/refman/5.6/en/partitioning-maintenance.html

http://dev.mysql.com/doc/refman/5.6/en/index.html

声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
解释InnoDB缓冲池及其对性能的重要性。解释InnoDB缓冲池及其对性能的重要性。Apr 19, 2025 am 12:24 AM

InnoDBBufferPool通过缓存数据和索引页来减少磁盘I/O,提升数据库性能。其工作原理包括:1.数据读取:从BufferPool中读取数据;2.数据写入:修改数据后写入BufferPool并定期刷新到磁盘;3.缓存管理:使用LRU算法管理缓存页;4.预读机制:提前加载相邻数据页。通过调整BufferPool大小和使用多个实例,可以优化数据库性能。

MySQL与其他编程语言:一种比较MySQL与其他编程语言:一种比较Apr 19, 2025 am 12:22 AM

MySQL与其他编程语言相比,主要用于存储和管理数据,而其他语言如Python、Java、C 则用于逻辑处理和应用开发。 MySQL以其高性能、可扩展性和跨平台支持着称,适合数据管理需求,而其他语言在各自领域如数据分析、企业应用和系统编程中各有优势。

学习MySQL:新用户的分步指南学习MySQL:新用户的分步指南Apr 19, 2025 am 12:19 AM

MySQL值得学习,因为它是强大的开源数据库管理系统,适用于数据存储、管理和分析。1)MySQL是关系型数据库,使用SQL操作数据,适合结构化数据管理。2)SQL语言是与MySQL交互的关键,支持CRUD操作。3)MySQL的工作原理包括客户端/服务器架构、存储引擎和查询优化器。4)基本用法包括创建数据库和表,高级用法涉及使用JOIN连接表。5)常见错误包括语法错误和权限问题,调试技巧包括检查语法和使用EXPLAIN命令。6)性能优化涉及使用索引、优化SQL语句和定期维护数据库。

MySQL:初学者的基本技能MySQL:初学者的基本技能Apr 18, 2025 am 12:24 AM

MySQL适合初学者学习数据库技能。1.安装MySQL服务器和客户端工具。2.理解基本SQL查询,如SELECT。3.掌握数据操作:创建表、插入、更新、删除数据。4.学习高级技巧:子查询和窗口函数。5.调试和优化:检查语法、使用索引、避免SELECT*,并使用LIMIT。

MySQL:结构化数据和关系数据库MySQL:结构化数据和关系数据库Apr 18, 2025 am 12:22 AM

MySQL通过表结构和SQL查询高效管理结构化数据,并通过外键实现表间关系。1.创建表时定义数据格式和类型。2.使用外键建立表间关系。3.通过索引和查询优化提高性能。4.定期备份和监控数据库确保数据安全和性能优化。

MySQL:解释的关键功能和功能MySQL:解释的关键功能和功能Apr 18, 2025 am 12:17 AM

MySQL是一个开源的关系型数据库管理系统,广泛应用于Web开发。它的关键特性包括:1.支持多种存储引擎,如InnoDB和MyISAM,适用于不同场景;2.提供主从复制功能,利于负载均衡和数据备份;3.通过查询优化和索引使用提高查询效率。

SQL的目的:与MySQL数据库进行交互SQL的目的:与MySQL数据库进行交互Apr 18, 2025 am 12:12 AM

SQL用于与MySQL数据库交互,实现数据的增、删、改、查及数据库设计。1)SQL通过SELECT、INSERT、UPDATE、DELETE语句进行数据操作;2)使用CREATE、ALTER、DROP语句进行数据库设计和管理;3)复杂查询和数据分析通过SQL实现,提升业务决策效率。

初学者的MySQL:开始数据库管理初学者的MySQL:开始数据库管理Apr 18, 2025 am 12:10 AM

MySQL的基本操作包括创建数据库、表格,及使用SQL进行数据的CRUD操作。1.创建数据库:CREATEDATABASEmy_first_db;2.创建表格:CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY,titleVARCHAR(100)NOTNULL,authorVARCHAR(100)NOTNULL,published_yearINT);3.插入数据:INSERTINTObooks(title,author,published_year)VA

See all articles

热AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover

AI Clothes Remover

用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool

Undress AI Tool

免费脱衣服图片

Clothoff.io

Clothoff.io

AI脱衣机

AI Hentai Generator

AI Hentai Generator

免费生成ai无尽的。

热工具

Dreamweaver Mac版

Dreamweaver Mac版

视觉化网页开发工具

记事本++7.3.1

记事本++7.3.1

好用且免费的代码编辑器

mPDF

mPDF

mPDF是一个PHP库,可以从UTF-8编码的HTML生成PDF文件。原作者Ian Back编写mPDF以从他的网站上“即时”输出PDF文件,并处理不同的语言。与原始脚本如HTML2FPDF相比,它的速度较慢,并且在使用Unicode字体时生成的文件较大,但支持CSS样式等,并进行了大量增强。支持几乎所有语言,包括RTL(阿拉伯语和希伯来语)和CJK(中日韩)。支持嵌套的块级元素(如P、DIV),

安全考试浏览器

安全考试浏览器

Safe Exam Browser是一个安全的浏览器环境,用于安全地进行在线考试。该软件将任何计算机变成一个安全的工作站。它控制对任何实用工具的访问,并防止学生使用未经授权的资源。

适用于 Eclipse 的 SAP NetWeaver 服务器适配器

适用于 Eclipse 的 SAP NetWeaver 服务器适配器

将Eclipse与SAP NetWeaver应用服务器集成。