MySQL分区表_MySQL

来源：懂视网责编：小采时间：2020-11-09 19:33:38

MySQL分区表_MySQL

MySQL分区表_MySQL:1 分区表简介数据库分区是一种物理数据库设计技术。虽然分区技术可以实现很多效果，但其主要目的是为了在特定的SQL操作中减少数据读写的总量以缩减sql语句的响应时间，同时对于应用来说分区完全是透明的。分区表是一种粗粒度,简易的索引策略,适用于大数据的

推荐度：

点击下载本文 文档为doc格式

导读MySQL分区表_MySQL:1 分区表简介数据库分区是一种物理数据库设计技术。虽然分区技术可以实现很多效果，但其主要目的是为了在特定的SQL操作中减少数据读写的总量以缩减sql语句的响应时间，同时对于应用来说分区完全是透明的。分区表是一种粗粒度,简易的索引策略,适用于大数据的

1 分区表简介

数据库分区是一种物理数据库设计技术。虽然分区技术可以实现很多效果，但其主要目的是为了在特定的SQL操作中减少数据读写的总量以缩减sql语句的响应时间，同时对于应用来说分区完全是透明的。

分区表是一种粗粒度,简易的索引策略,适用于大数据的过滤场景.最适合的场景是,没有合适的索引时,对其中几个分区表进行全表扫描.或者只有一个分区表和索引是热点,而且这个分区和索引能够全部存储在内存中.限制单表分区数不要超过150个,并且注意某些导致无法做分区过滤的细节,分区表对于单条记录的查询没有优势,需要注意这类查询的性能。

2 分区表优缺点

2.1 分区表优点

可以把一些归类的数据放在一个分区中，可以减少服务器检查数据的数量加快查询。
方便维护，通过删除分区来删除老的数据。
分区数据可以被分布到不同的物理位置，可以做分布式有效利用多个硬盘驱动器。

可以优化、检查、修复个别分区。

根据查找条件，也就是where后面的条件，查找只查找相应的分区不用全部查找了。
进行大数据搜索时可以进行并行处理。
跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。

2.2 分区表缺点

每张表最大分区数为1024。
所有的主键或者唯一索引必须被包含在分区表达式中。
不能使用任何外键约束。

3 分区类型

MYSQL的分区主要有两种形式：水平分区和垂直分区。

3.1 水平分区（根据列属性按行分）

这种形式的分区是对根据表的行进行分区，通过这样的方式不同分组里面的物理列分割的数据集得以组合，从而进行个体分割（单分区）或集体分割（1个或多个分区）。
所有在表中定义的列在每个数据集中都能找到，所以表的特性依然得以保持。水平分区一定要通过某个属性列来分割。常见的比如年份，日期等。

例如：一个包含十年发票记录的表可以被分区为十个不同的分区，每个分区包含的是其中一年的记录。

3.1.1 水平分区几种模式：

Range（范围） – 这种模式允许DBA将数据划分不同范围。例如DBA可以将一个表通过年份划分成三个分区，80年代（1980's）的数据，90年代（1990's）的数据以及任何在2000年（包括2000年）后的数据。
Hash（哈希） – 这中模式允许DBA通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区，。例如DBA可以建立一个对表主键进行分区的表。 Key（键值） – 上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。
List（预定义列表） – 这种模式允许系统通过DBA定义的列表的值所对应的行数据进行分割。例如：DBA建立了一个横跨三个分区的表，分别根据2004年2005年和2006年值所对应的数据。

Composite（复合模式） - 其实是以上模式的组合使用而已。例如：在初始化已经进行了Range范围分区的表上，我们可以对其中一个分区再进行hash哈希分区。

3.2 垂直分区（按列分）

这种分区方式一般来说是通过对表的垂直划分来减少目标表的宽度，使某些特定的列被划分到特定的分区，每个分区都包含了其中的列所对应所有行。

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

4 实例分析

4.1 查看分区表

查看是否支持分区

mysql> show variables like '%partition%';+-------------------+-------+| Variable_name | Value |+-------------------+-------+| have_partitioning | YES |+-------------------+-------+mysql> show plugins;+--------------------------+----------+--------------------+---------+-------------+| Name | Status | Type | Library | License |+--------------------------+----------+--------------------+---------+-------------+| binlog | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY || mysql_native_password | ACTIVE | AUTHENTICATION | NULL | PROPRIETARY || mysql_old_password | ACTIVE | AUTHENTICATION | NULL | PROPRIETARY || MEMORY | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY || MyISAM | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY || CSV | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY || MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY || InnoDB | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY || INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | PROPRIETARY || PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY || FEDERATED | DISABLED | STORAGE ENGINE | NULL | PROPRIETARY || BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY || ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY || partition | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY |+--------------------------+----------+--------------------+---------+-------------+

4.2 Range分区

创建range分区表

CREATE TABLE IF NOT EXISTS `user` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID', `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 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 );

插入一些数据

INSERT INTO `user` (`name` ,`sex`)VALUES ('name1', '0'),('name2',1),('name3',1),('name4',1),('name5',0),('name6',1),('name7',1),('name8',1),('name9',1),('name10',1),('name11',1),('name12',1),('name13',1),('name14',1),('name15',1),('name16',1);

查看分区表信息

SHOW CREATE TABLE user;
SHOW TABLE STATUS LIKE 'user';

查看各分区信息

mysql> SELECT TABLE_NAME,PARTITION_NAME,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,AVG_ROW_LENGTH/1024 as 'AVG_ROW_LENGTH (KB)',DATA_LENGTH/(1024*1024) as 'DATA_LENGTH (MB)',MAX_DATA_LENGTH/(1024*1024*1024*1024) as 'MAX_DATA_LENGTH (TB)',INDEX_LENGTH/(1024*1024) as 'INDEX_LENGTH (MB)',UPDATE_TIME,CHECK_TIME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='user' AND TABLE_SCHEMA='test1';

5 分区管理

6 分区表和未分区表性能对比

转载请注明出处：http://blog.csdn.net/jesseyoung/article/details/37813331

声明：本网页内容旨在传播知识，若有侵权等问题请及时与本网联系，我们将在第一时间删除处理。TEL:177 7030 7066 E-MAIL:11247931@qq.com