首页 / MYSQL / MySQL调优6---分区表

MySQL调优6---分区表

内容导读

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

内容图文

分区表原理

分区表由多个相关的底层表实现，这个底层表也是由句柄对象标识，我们可以直接访问各个分区。存储引擎管理分区的各个底层表和管理普通表一样（所有的底层表都必须使用相同的存储引擎），分区表的索引知识在各个底层表上各自加上一个完全相同的索引。从存储引擎的角度来看，底层表和普通表没有任何不同，存储引擎也无须知道这是一个普通表还是一个分区表的一部分。

分区表的操作按照以下的操作逻辑进行：

select查询

当查询一个分区表的时候，分区层先打开并锁住所有的底层表，优化器先判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据

insert操作

?当写入一条记录的时候，分区层先打开并锁住所有的底层表，然后确定哪个分区接受这条记录，再将记录写入对应底层表

delete操作

当删除一条记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作

update操作

当更新一条记录时，分区层先打开并锁住所有的底层表，mysql先确定需要更新的记录再哪个分区，然后取出数据并更新，再判断更新后的数据应该再哪个分区，最后对底层表进行写入操作，并对源数据所在的底层表进行删除操作

有些操作时支持过滤的，例如，当删除一条记录时，MySQL需要先找到这条记录，如果where条件恰好和分区表达式匹配，就可以将所有不包含这条记录的分区都过滤掉，这对update同样有效。如果是insert操作，则本身就是只命中一个分区，其他分区都会被过滤掉。mysql先确定这条记录属于哪个分区，再将记录写入对应得曾分区表，无须对任何其他分区进行操作

?虽然每个操作都会“先打开并锁住所有的底层表”，但这并不是说分区表在处理过程中是锁住全表的，如果存储引擎能够自己实现行级锁，例如innodb，则会在分区层释放对应表锁。

分区表类型

范围分区

根据列值在给定范围内将行分配给分区
范围分区表的分区方式是：每个分区都包含行数据且分区的表达式在给定的范围内，分区的范围应该是连续的且不能重叠，可以使用values less than运算符来定义。

?1、创建普通的表

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
);

2、创建带分区的表，下面建表的语句是按照store_id来进行分区的，指定了4个分区

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN (21)
);
--在当前的建表语句中可以看到，store_id的值在1-5的在p0分区，6-10的在p1分区，11-15的在p3分区，16-20的在p4分区，但是如果插入超过20的值就会报错，因为mysql不知道将数据放在哪个分区

3、可以使用less than maxvalue来避免此种情况

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);
--maxvalue表示始终大于等于最大可能整数值的整数值

列表分区

类似于按range分区，区别在于list分区是基于列值匹配一个离散值集合中的某个值来进行选择

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY LIST(store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);

列分区

mysql从5.5开始支持column分区，可以认为i是range和list的升级版，在5.5之后，可以使用column分区替代range和list，但是column分区只接受普通列不接受表达式

?CREATE TABLE `list_c` (
?`c1` int(11) DEFAULT NULL,
?`c2` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE?COLUMNS(c1)
(PARTITION p0 VALUES LESS THAN (5) ENGINE = InnoDB,
?PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB) */

?CREATE TABLE `list_c` (
?`c1` int(11) DEFAULT NULL,
?`c2` int(11) DEFAULT NULL,
?`c3` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE?COLUMNS(c1,c3)
(PARTITION p0 VALUES LESS THAN (5,'aaa') ENGINE = InnoDB,
?PARTITION p1 VALUES LESS THAN (10,'bbb') ENGINE = InnoDB) */

?CREATE TABLE `list_c` (
?`c1` int(11) DEFAULT NULL,
?`c2` int(11) DEFAULT NULL,
?`c3` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50500 PARTITION BY LIST?COLUMNS(c3)
(PARTITION p0 VALUES IN ('aaa') ENGINE = InnoDB,
?PARTITION p1 VALUES IN ('bbb') ENGINE = InnoDB) */

hash分区

基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含myql中有效的、产生非负整数值的任何表达式

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;
CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY LINEAR HASH(YEAR(hired))
PARTITIONS 4;

key分区

类似于hash分区，区别在于key分区只支持一列或多列，且mysql服务器提供其自身的哈希函数，必须有一列或多列包含整数值

CREATE TABLE tk (
    col1 INT NOT NULL,
    col2 CHAR(5),
    col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;

子分区

在分区的基础之上，再进行分区后存储

CREATE TABLE `t_partition_by_subpart`
(
  `id` INT AUTO_INCREMENT,
  `sName` VARCHAR(10) NOT NULL,
  `sAge` INT(2) UNSIGNED ZEROFILL NOT NULL,
  `sAddr` VARCHAR(20) DEFAULT NULL,
  `sGrade` INT(2) NOT NULL,
  `sStuId` INT(8) DEFAULT NULL,
  `sSex` INT(1) UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`id`, `sGrade`)
)  ENGINE = INNODB
PARTITION BY RANGE(id)
SUBPARTITION BY HASH(sGrade) SUBPARTITIONS 2
(
PARTITION p0 VALUES LESS THAN(5),
PARTITION p1 VALUES LESS THAN(10),
PARTITION p2 VALUES LESS THAN(15)
);

分区表限制

1.一个表最多只能有1024个分区，在5.7版本的时候可以支持8196个分区
2.在早期的mysql中，分区表达式必须是整数或者是返回整数的表达式，在mysql5.5中，某些场景可以直接使用列来进行分区
3.如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来
4.分区表无法使用外键约束

如何使用

大数据拆分（即使使用索引，会发现会产生大量的碎片，还会产生大量的随机IO）
1.使用简单的分区方式存放表，不要任何索引，根据分区规则大致定位需要的数据为止，通过使用where条件将需要的数据限制在少数分区中，这种策略适用于以正常的方式访问大量数据
2.如果数据有明显的热点，而且除了这部分数据，其他数据很少被访问到，那么可以将这部分热点数据单独放在一个分区中，让这个分区的数据能够有机会都缓存在内存中，这样查询就可以只访问一个很小的分区表，能够使用索引，也能够有效的使用缓存

应用场景

1.表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据
2.分区表的数据更容易维护。批量删除大量数据可以使用清除整个分区的方式；对一个独立分区进行优化、检查、修复等操作。
3.分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备
4.可以使用分区表来避免某些特殊的瓶颈。innodb的单个索引的互斥访问；ext3文件系统的inode锁竞争。
5.可以备份和恢复独立的分区

注意问题

null值会使分区过滤无效
分区列和索引列不匹配，会导致查询无法进行分区过滤
选择分区的成本可能很高（一个分区会存储一个文件，会占用大量的存储空间）
打开并锁住所有底层表的成本可能很高
维护分区的成本可能很高（分区变更要删除老分区，创建新分区）

内容总结

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

内容备注

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

内容手机端

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

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

来源：【匿名】

【上一篇】MySql优化基础【下一篇】用IE远程创建Mysql数据库的简易程序

更多 ►

【MySQL调优6---分区表】教程文章相关的互联网学习教程文章

MySQL分区表例子——List分区

列表分区(List分区)这里假设表中有一个sale_item_type 字段，数据类型为INT 型当sale_item_type 为1，3，5的时候，作为一个分区当sale_item_type 为2，4，6的时候，作为另一个分区当sale_item_type 为7，8的时候，作为最后一个分区初期分区定义mysql> CREATE TABLE sale_data2 ( -> sale_date DATETIME NOT NULL, -> sale_item_type INT NOT NULL , -> sale_money DECIMAL(10,2) NOT NULL -> ) -> PART...

MySQL5.7传输表空间——迁移分区表

Mysql 传输表空间--将InnoDB分区表复制到另一个实例（二）实验环境：（都是mysql5.7）源库：192.168.2.200 mysql5.7.16 zhangdb下的emp_2分区表的目标库：192.168.2.100 mysql5.7.18 test下（将zhangdb的emp表，导入到目标库的test schema下）--：在源数据库中创建测试分区表emp_2，然后导入数据MySQL [zhangdb]> CREATE TABLE emp_2(id BIGINT unsigned NOT NULL AUTO_INCREMENT,x VARCHAR(500) NOT NULL,y ...

Mysql5.6—分区表及独享表空间

1、先停止Mysql [root@localhost ~]# /etc/init.d/mysqld stop Shutting down MySQL.[确定] 2、修改 /etc/my.cnf配置文件 [root@localhost ~]# vi /etc/my.cnf [mysqld] innodb_file_per_table = 1 3、启动Mysql [root@localhost ~]# /etc/init.d/mysqld start Starting MySQL..[确定] 4、连接到Mysql数据库 [root@localhost ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end wit...

详解MySQL分区表【代码】【图】

当数据库数据量涨到一定数量时，性能就成为我们不能不关注的问题，如何优化呢？常用的方式不外乎那么几种：　　1、分表，即把一个很大的表达数据分到几个表中，这样每个表数据都不多。　　　　优点：提高并发量，减小锁的粒度　　　　缺点：代码维护成本高，相关sql都需要改动　　2、分区，所有的数据还在一个表中，但物理存储数据根据一定的规则存放在不同的文件中，文件也可以放到另外磁盘上　　　　优点：代码维护量小，基本不...

Mysql临时表和分区表【代码】【图】

临时表与内存表内存表，指的是使用Memory引擎的表，建表语法是create table … engine=memory。这种表的数据都保存在内存里，系统重启的时候会被清空，但是表结构还在。除了这两个特性看上去比较“奇怪”外，从其他的特征上看，它就是一个正常的表临时表，可以使用各种引擎类型。如果是使用InnoDB引擎或者MyISAM引擎的临时表，写数据的时候是写到磁盘上的。当然，临时表也可以使用Memory引擎。临时表特性建表语法是create te...

MySQL 分区表各个分区的行数

分区的信息是记录在information_schema.partitions 这个表里的。它不能直接定位行所在的分区，但它可查到每个分区中有多少行。例子： select partition_name as part,partition_expression as expr,partition_description as descr,table_rows from information_schema.partitions where table_schema= schema() and table_name=‘t‘;MySQL 分区表各个分区的行数标签：本文系统来源：http://www.cnblogs.com/JiangLe/p/4491462.h...

Mysql5.6—分区表及独享表空间

[root@localhost ~]# /etc/init.d/mysqld stop Shutting down MySQL.[确定] 2、修改 /etc/my.cnf配置文件 [root@localhost ~]# vi /etc/my.cnf [mysqld] innodb_file_per_table = 1 3、启动Mysql [root@localhost ~]# /etc/init.d/mysqld start Starting MySQL..[确定] 4、连接到Mysql数据库 [root@localhost ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your...

MySQL之高级特性---分区表【代码】

CREATE TABLE sales (order_date DATETIME NOT NULL,-- Other columns omitted ) ENGINE=InnoDB PARTITION BY RANGE(YEAR(order_date)) ( PARTITION p_2010 VALUES LESS THAN (2010), PARTITION p_2011 VALUES LESS THAN (2011), PARTITION p_2012 VALUES LESS THAN (2012), PARTITION p_catchall VALUES LESS THAN MAXVALUE );PARTITION 分区子句可以使用各种函数。但有一个要求，表达式返回的值要是一个确定的整数，且不能是一个...

MySQL 分区表原理及使用详解【代码】

分区查询 1）查询某张表一共有多少个分区 mysql> select -> partition_name,-> partition_expression,-> partition_description,-> table_rows-> from -> INFORMATION_SCHEMA.partitions-> where-> table_schema=‘test‘-> and table_name = ‘emp‘; +----------------+----------------------+-----------------------+------------+ | partition_name | partition_expression | partition_description | table_r...

MySQL分区表【代码】【图】

CREATE TABLE sales (id INT AUTO_INCREMENT,amount DOUBLE NOT NULL,order_day DATETIME NOT NULL,PRIMARY KEY(id, order_day) ) ENGINE=Innodb PARTITION BY RANGE(YEAR(order_day)) (PARTITION p_2010 VALUES LESS THAN (2010),PARTITION p_2011 VALUES LESS THAN (2011),PARTITION p_2012 VALUES LESS THAN (2012),PARTITION p_catchall VALUES LESS THAN MAXVALUE);这段语句表示将表内数据按照order_dy的年份范围进行分区,201...

MySQL分区表管理【代码】

但是如果e2里面的数据不能够满足分区p0的要求的话,切分区就会失败.1737 - Found a row that does not match the partition只有再指定不验证的时候才不会报错ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITHOUT VALIDATION;WITHOUT VALIDATION 指定的时候效率会更高,因为不再做逐行验证了. 子分区和没分区的表进行切换 1:假设创建一个分区表,带有子分区> CREATE TABLE es (-> id INT NOT NULL,-> fname VARCHAR...

MySQL5.5的分区表

MySQL 分区表原理及数据备份转移实战

1、分区表含义分区表定义指根据可以设置为任意大小的规则，跨文件系统分配单个表的多个部分。实际上，表的不同部分在不同的位置被存储为单独的表。用户所选择的、实现数据分割的规则被称为分区函数，这在MySQL中它可以是模数，或者是简单的匹配一个连续的数值区间或数值列表，或者是一个内部HASH函数，或一个线性HASH函数。分表与分区的区别在于：分区从逻辑上来讲只有一张表，而分表则是将一张表分解成多张表。 2、分区表优点...

MySQL 分区表探究细节《整理自官档》

1. Date Time分区When partitioning by KEY or LINEAR KEY, youcan use a DATE, TIME, or DATETIME column as thepartitioning column without performing any modification of the column value. Forexample, this table creation statement is perfectly valid in MySQL:如果考虑不转换列值，可采取key进行分区。CREATE TABLE members(firstname VARCHAR(25)NOT NULL,lastname VARCHAR(25)NOT NULL,username VARCHAR(16)NOT NU...

Mysql --- 分区表【代码】【图】

TABLE IF EXISTS p_user; CREATE TABLE p_user(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(225),login_date DATETIME )ENGINE=INNODB PARTITION BY RANGE (id) ( PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20), PARTITION p2 VALUES LESS THAN (30), PARTITION p3 VALUES LESS THAN MAXVALUE ) 分区表p_user，小于10的id存储在p0分区中，10到20之间的id存储在p1中，以此类推，大于30的id存储在p3...

首页 / MYSQL / MySQL调优6---分区表

MySQL调优6---分区表

内容导读

内容图文

分区表原理

分区表类型

范围分区

列表分区

列分区

hash分区

key分区

子分区

分区表限制

如何使用

应用场景

注意问题

内容总结

内容备注

内容手机端

【MySQL调优6---分区表】教程文章相关的互联网学习教程文章

MySQL分区表例子——List分区

MySQL5.7传输表空间——迁移分区表

Mysql5.6—分区表及独享表空间

详解MySQL分区表【代码】【图】

Mysql临时表和分区表【代码】【图】

MySQL 分区表各个分区的行数

Mysql5.6—分区表及独享表空间

MySQL之高级特性---分区表【代码】

MySQL 分区表原理及使用详解【代码】

MySQL分区表【代码】【图】

MySQL分区表管理【代码】

MySQL5.5的分区表

MySQL 分区表原理及数据备份转移实战

MySQL 分区表探究细节《整理自官档》

Mysql --- 分区表【代码】【图】

MYSQL - 相关标签

分区表 - 相关标签

MYSQL - 技术教程分类

MYSQL - 最新教程

MYSQL - 最热教程