数据库分区

李炳辰

1.数据库分区

分区优点

1、与单个磁盘或文件系统分区相比，可以存储更多的数据。
2、通过删除与增加那些数据有关的分区，很容易地删除或增加那些数据。
3、一些查询可以得到极大的优化。
4、通过跨多个磁盘甚至服务器来分散数据查询，来获得更大询吞吐量。
5、MySQL5.5之后支持所有函数的分区优化。限定只查询有效的分区。

Range分区

基于属于一个给定连续区间的列值，把多行分配给分区。

1. ALTER TABLE titles
2. //分区语句   range
3. partition by range (year(from_date))// year(from_date)  分区的依据
4. (
5.     partition po1 values less than (1985), 小于不包含[-无穷，1985)
6.     partition po2 values less than (1986),
7.     partition po3 values less than (1987),
8.     partition p04 values less than (1988),
9.     partition p15 values less than (1999),
10.     partition p16 values less than (MAXVALUE)
11. );

复制代码

list分区

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

1. create table t2 (id int,cid int,name varchar(20),pos_date datetime)
2. partition by list (cid)       
3. (
4.     partition po1 values in (1,2,3),
5.     partition p02 values in (4,5,6),
6.     partition po3 values in (7,8,9)
7. );

复制代码

Hash分区
基于用户定义的表达式的返回值来进行选择的分区，基于插入的行的列信息通过表达式计算返回非负整数来确定分区
使用HASH分区的优点在于数据分布较为均匀

1. create table t3 (id int,cid int,name varchar(20),pos_date datetime)
2. partition by hash(cid)
3. partitions 4;

复制代码

Linear Hash分区
1.缺点
数据分布不均衡（有的分区数据多，有的分区数据少）
2.优点

• 增加、删除、合并和拆分分区快捷
  
• 有利于处理大量数据表
  

1. create table t3 (id int,cid int,name varchar(20),pos_date datetime)
2. partition by linear hash(cid)
3. partitions 4;

复制代码

key分区

1. create table t3 (id int,cid int,name varchar(20),pos_date datetime)
2. partition by key(cid)
3. partitions 4;

复制代码

添加功能（mysql 5.5版本以上）

多列分区columns    (string date)

1. create table t4(a int,b int,c int)
2. partition by range columns(a,b)   //插入时按从左往右进行对比
3. (
4.      partition po1 values less than (10,20),
5.      partition p02 values less than (10,30),
6.      partition po3 values less than (10,maxvalue)
7. );

复制代码

子分区

子分区是分区表中每个分区的再次分割。
子分区可以用于特别大的表，在多个磁盘间分配数据和索引。

1. CREATE TABLE t5 (id INT,udate DATE)
2. PARTITION BY RANGE(YEAR(udate))
3. SUBPARTITION BY HASH(TO_DAYS(udate))
4. SUBPARTITIONS 2
5. (
6.     PARTITION PO VALUES LESS THAN(1990)
7.     PARTITION p1 VALUES LESS THAN (2000),
8.     PARTITION p2 VALUES LESS THAN MAXVALUE
9. )；

复制代码

子分区将数据和索引分配到不同文件夹下，减轻单个I/O压力

1. CREATE TABLE t5 (id INT,udate DATE)
2. PARTITION BY RANGE(YEAR(udate))
3. SUBPARTITION BY HASH(TO_DAYS(udate))
4. (
5.      partition p0l values less than (1999)
6.     (
7.         subpartition s0
8.         data directory='/var/a/data'
9.         inde× directory="/var/a/idx',
10.         subpartition s1
11.         data directory='/var/b/data'
12.         index directory='/var/b/idx'
13.     ),
14.         partition p02 values less than (2000)
15.     (
16.         subpartition s2
17.         data directory='/var/c/data'
18.         index directory='/var/c/idx',
19.         subpartition s3
20.         data directory='/var/d/data'
21.         index directory='/var/d/idx'
22.     )
23. );

复制代码

分区管理和维护

1.alter进行简单修改分区

ALTER语句简单修改了分区。效果和先删除原表再按新的分区方式重新建表效果相同。

1. alter table t3 partition by key() partitions 2

复制代码

2.删除分区

1. alter table t3 drop partition  p02  //删除本分区结构和数据
2. alter table t3 truncate partition p01 //不删除本分区结构，只删除本分区数据

复制代码

3.range添加分区

1. partition by range (year(from_date))// year(from_date)  分区的依据
2. (
3.     partition po1 values less than (1985), 小于不包含[-无穷，1985)
4.     partition po2 values less than (1986)
5. )
6. alter table t1 add partition (partition p02 values less than("date"))
7. 注意
8.         "date"填写的数据必须高于上次创建分区的最大值

复制代码

4.list添加分区

1. partition by list (cid)       
2. (
3.     partition po1 values in (1,2,3),
4.     partition p02 values in (4,5,6),
5.     partition po3 values in (7,8,9)
6. );
7. alter table t1 add partition (partition p02 values in ("date"))
8. 注意
9. "date"填写的数据不能包含原有的数据

复制代码

5.分区重组

1. ALTER TABLE t1 REORGANIZE PARTITION pO1 INTO(
2. PARTITION sO VALUES LESS THAN(5)
3. PARTITION s1 VALUES LESS THAN(10));
4. 注意
5.         1.range分区重组 只能相邻的分区 不能跳过分区重组
6.         2.list分区重组，重组的必须覆盖原有区间

复制代码

6.hash和key分区数量

1. alter table t3 coalesce partition 2  //在原有的分区数量上减少2个
2. alter table t3 add partition partitions 2 //在原有的分区数量上增加2个

复制代码

分片

• 垂直分片
  

业务维度将表拆分到不同的数据库中，专库专用，分担数据库压力。

• 水平分片
  

解决单表数据过大的问题

数据库索引

• 按数据结构分类：B+tree索引 Hash索引  Full-text索引，
  
• 按物理存储分类： 聚集索引、非聚集索引。
  
• 按字段特性分类：主键索引(PRIMARY KEY)、唯一索引(UNIQUE)、普通索 (INDEX)、全文索引(FULLTEXT)。
  
• 按字段个数分类：单列索引、联合索引（也叫复合索引、组合索引）
  

来源:https://www.cnblogs.com/darkerKing/p/17752913.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！