投稿
  1. 云编程开发首页
  2. 数据库

MySQL分区学习

374165030 • • 数据库 • 阅读 135

https://dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html

分区是一种表设计模式，自5.1版本开始支持分区，逻辑上是一张表，物理上可能是多个对象。其中MyISAM、INNODB、NDB等存储引擎都支持分区，但CSV、MERGE、FEDORATED不支持分区。

水平分区：对表记录进行拆分，同一表的不同行记录分配到不同的物理文件中。

Range 分区：行根据基于属于一个给定连续区间的列值放入分区，自MYSQL5.5开始支持列范围分区，最常用的分区。

List分区：和Range分区一样，只是List分区是面对离散值，自MYSQL5.5开支支持列List分区。

Hash分区：根据用户自定义的表达式的返回值进行分区，返回值不能为负数。

Key分区：根据MYSQL数据库提供的散列函数来进行分区。

垂直分区：对表字段进程拆分（MYSQL暂不支持），同一表中不同的列分配在不同的物理文件中。

优点：

可极大提高查询效率；

主要用于数据库的高可用性，方便管理；

缺点：

无论何种分区，如果表中存在主键或者唯一键索引，分区列必须是唯一索引的一个组成部分。

create table t_range(id int)

partition by range(id)

(partition p0 values less than (100),

partition p1 values less than(500),

partition p2 values less than maxvalue );

insert into t_range values(10),(120),(600);

(root:localhost:Sat Jul 8 20:05:12 2017)[dbtest]> ! ls -lnrth
/home/mysql/dbtest

total 320K

-rw-rw—- 1 500 500 61 Mar 17 15:58 db.opt

-rw-rw—- 1 500 500 32 Jul 8 20:04 t_range.par ##存储分区信息

-rw-rw—- 1 500 500 8.4K Jul 8 20:04 t_range.frm

-rw-rw—- 1 500 500 96K Jul 8 20:05 t_range#P#p2.ibd

-rw-rw—- 1 500 500 96K Jul 8 20:05 t_range#P#p1.ibd

-rw-rw—- 1 500 500 96K Jul 8 20:05 t_range#P#p0.ibd

(root:localhost:Sat Jul 8 20:14:13 2017)[(none)]> select * from
information_schema.partitions where table_name=’t_range’ G

*************************** 1. row
***************************

TABLE_CATALOG: def

TABLE_SCHEMA: dbtest

TABLE_NAME: t_range

PARTITION_NAME: p0

SUBPARTITION_NAME: NULL

PARTITION_ORDINAL_POSITION: 1

SUBPARTITION_ORDINAL_POSITION: NULL

PARTITION_METHOD: RANGE

SUBPARTITION_METHOD: NULL

PARTITION_EXPRESSION: id

SUBPARTITION_EXPRESSION: NULL

PARTITION_DESCRIPTION: 100

TABLE_ROWS: 1

AVG_ROW_LENGTH: 16384

DATA_LENGTH: 16384

MAX_DATA_LENGTH: NULL

INDEX_LENGTH: 0

DATA_FREE: 0

CREATE_TIME: NULL

UPDATE_TIME: NULL

CHECK_TIME: NULL

CHECKSUM: NULL

PARTITION_COMMENT:

NODEGROUP: default

TABLESPACE_NAME: NULL

*************************** 2. row
***************************

TABLE_CATALOG: def

TABLE_SCHEMA: dbtest

TABLE_NAME: t_range

PARTITION_NAME: p1

SUBPARTITION_NAME: NULL

PARTITION_ORDINAL_POSITION: 2

SUBPARTITION_ORDINAL_POSITION: NULL

PARTITION_METHOD: RANGE

SUBPARTITION_METHOD: NULL

PARTITION_EXPRESSION: id

SUBPARTITION_EXPRESSION: NULL

PARTITION_DESCRIPTION: 500

TABLE_ROWS: 1

AVG_ROW_LENGTH: 16384

DATA_LENGTH: 16384

MAX_DATA_LENGTH: NULL

INDEX_LENGTH: 0

DATA_FREE: 0

CREATE_TIME: NULL

UPDATE_TIME: NULL

CHECK_TIME: NULL

CHECKSUM: NULL

PARTITION_COMMENT:

NODEGROUP: default

TABLESPACE_NAME: NULL

*************************** 3. row
***************************

TABLE_CATALOG: def

TABLE_SCHEMA: dbtest

TABLE_NAME: t_range

PARTITION_NAME: p2

SUBPARTITION_NAME: NULL

PARTITION_ORDINAL_POSITION: 3

SUBPARTITION_ORDINAL_POSITION: NULL

PARTITION_METHOD: RANGE

SUBPARTITION_METHOD: NULL

PARTITION_EXPRESSION: id

SUBPARTITION_EXPRESSION: NULL

PARTITION_DESCRIPTION: MAXVALUE

TABLE_ROWS: 1

AVG_ROW_LENGTH: 16384

DATA_LENGTH: 16384

MAX_DATA_LENGTH: NULL

INDEX_LENGTH: 0

DATA_FREE: 0

CREATE_TIME: NULL

UPDATE_TIME: NULL

CHECK_TIME: NULL

CHECKSUM: NULL

PARTITION_COMMENT:

NODEGROUP: default

TABLESPACE_NAME: NULL

3 rows in set (0.00 sec)

-rw-rw—- 1 500 500 28
Jul 8 21:39 t_list.par

-rw-rw—- 1 500 500 8.4K Jul 8 21:39 t_list.frm

-rw-rw—- 1 500 500 96K Jul 8 21:39 t_list#P#p1.ibd

-rw-rw—- 1 500 500 96K Jul 8 21:39 t_list#P#p0.ibd

CREATE TABLE `t_list` (

`id` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

/*!50100 PARTITION BY LIST (id)

(PARTITION p0 VALUES IN (1,3,5,7,9) ENGINE
= InnoDB,

PARTITION p1 VALUES IN (2,4,6,8,10) ENGINE =
InnoDB) */;


HASH分区将数据均匀的分布到预先定义的各个分区中，保障各个分区的数据数量大致一样的。在range和list分区定义时，必须明确指定分区的列值或列值集合保存在哪个分区中，而hash分区自动完成列值的分配，平均的将数据放在不同的分区。

CREATE TABLE `t_hash` (

`id` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

/*!50100 PARTITION BY HASH (id)

PARTITIONS 4 */

-rw-rw—- 1 500 500 32 Jul 8 21:53 t_hash.par

-rw-rw—- 1 500 500 8.4K Jul 8 21:53 t_hash.frm

-rw-rw—- 1 500 500 96K Jul 8 21:54 t_hash#P#p3.ibd

-rw-rw—- 1 500 500 96K Jul 8 21:54 t_hash#P#p2.ibd

-rw-rw—- 1 500 500 96K Jul 8 21:54 t_hash#P#p1.ibd

-rw-rw—- 1 500 500 96K Jul 8 21:54 t_hash#P#p0.ibd


Hash分区根据用户自定义的函数进行分区，key使用MYSQL数据库提供的函数进行分区。

CREATE TABLE `t_key` (

`id` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

/*!50100 PARTITION BY KEY (id)

PARTITIONS 4 */;

Range、List、Hash和Key分区都是针对整型分区，如果不是整型分区，则需要通过相关函数转换。但通过columns分区，不需要转换。

Columns支持int/small
int/tinyint/bigint/date/datetime/char/varchar/binary支持，对float/decimal/blob/text不支持

create table t_range_columns(dtime
datetime)

partition by range columns (dtime)

(partition p0 values less than
(‘2016-01-01’),

partition p1 values less
than(‘2017-01-01’),

partition p2 values less than maxvalue );

在分区的基础上再进行分区，也称之为复合分区。

Alter table table_name

|
ADD PARTITION (partition_definition)

|
DROP PARTITION partition_names

|
TRUNCATE PARTITION {partition_names | ALL }

|
COALES开发云主机域名CE PARTITION number

|
REORGANIZE PARTITION partition_names INTO (partition_definitions)

|
ANALYZE PARTITION {partition_names | ALL
}

|
CHECK PARTITION {partition_names | ALL }

|
OPTIMIZE PARTITION {partition_names |
ALL }

|
REBUILD PARTITION {partition_names | ALL
}

|
REPAIR PARTITION {partition_names | ALL
}

|
REMOVE PARTITIONING

alter table t_range add partition (
partition p2 values less than maxvalue)
;

alter table t_range drop partition p2;

alter table t_list remove partitioning;

information_schema.partitions

alter table t_range truncate partition p2;


(root:localhost:Sat Jul 8 21:30:03 2017)[dbtest]> explain
partitions select * from t_range G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: t_range

partitions: p0,p1,p2

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 3

Extra: NULL

1 row in set (0.00 sec)

(root:localhost:Sat Jul 8 21:30:18 2017)[dbtest]> explain
partitions select * from t_range where id=800G

*************************** 1. row
***************************

id: 1

select_type: SIMPLE

table: t_range

partitions: p2

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 2

Extra: Using where

1 row in set (0.00 sec)

（只查询指定的分区）

MYSQL5.6支持了交换分区，具体语法如下：

alter table t_range exchange partition p0 with table t;

将分区表t_range的P0分区的数据交换到t表中，而t表的数据也会交换到t_range表中，交换是双向的。


相关推荐: xtrabackup参数补充

点击(此处)折叠或打开 点击(此处)折叠或打开 点开发云主机域名击(此处)折叠或打开 点击(此处)折叠或打开相关推荐: 防止SQL注入的实现方式有哪些今天就跟大家聊聊有关防止SQL注入的实现方式有哪些，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了…

免责声明：本站发布的图片视频文字，以转载和分享为主，文章观点不代表本站立场，本站不承担相关法律责任；如果涉及侵权请联系邮箱：360163164@qq.com举报，并提供相关证据，经查实将立刻删除涉嫌侵权内容。

赞 (0)
打赏 微信扫一扫 微信扫一扫
374165030374165030
0 0
上一篇 06/04 22:21
下一篇 06/04 22:21

相关推荐