博客
关于我
【MySQL语法】分发规则 partition等
阅读量:165 次
发布时间:2019-02-28

本文共 2252 字,大约阅读时间需要 7 分钟。

数据库分区指南

1. 分发规则

1.1 分区的基本概念

分区是一种将数据库表中的数据划分为多个独立的子集的技术,旨在提高数据库性能和管理效率。分区的核心目标是通过将数据分布到不同的存储介质上,减少I/O操作时间,提升查询速度。

1.2 分区的模式

数据库分区主要有以下几种模式,每种模式适用于不同的场景:

1.2.1 Range(范围)

Range模式允许数据库管理员将数据按照特定列的取值范围划分为不同的分区。例如,年份可以作为分区的依据,将数据按年份分区,便于按年份进行查询和管理。

1.2.2 Hash(哈希)

Hash模式通过计算特定列(通常是主键)的哈希值,将数据分布到不同的分区中。这种模式适合需要快速定位特定记录的场景。

1.2.3 List(列表)

List模式通过预定义的列表值将数据划分为不同的分区。例如,可以根据客户ID的值将数据按客户分区。

1.2.4 Composite(复合模式)

Composite模式是以上几种模式的组合使用,例如可以先按年份分区,然后再在每个年份的分区中按月份或其他列进行进一步的分区。

1.3 分区管理

数据库分区需要有效的管理策略,包括:

1.3.1 删除分区

ALTER TABLE users DROP PARTITION p0;

1.3.2 重建分区

ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000));

1.3.3 新增分区

ALTER TABLE category ADD PARTITION (PARTITION p4 VALUES IN (16,17,18,19) DATA DIRECTORY = '/data8/data' INDEX DIRECTORY = '/data9/idx');

1.3.4 数据目录和索引目录

PARTITION p0 VALUES LESS THAN (1995) DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data1/idx'

2. 水平分区

2.1 水平分区的概述

水平分区(根据列属性按行分)是数据库分区的一种常用方式。例如,一个包含十年发票记录的表可以被分区为十个不同的分区,每个分区包含的是其中一年的记录。

2.2 水平分区的模式

水平分区的模式主要包括以下几种:

2.2.1 Range(范围)

Range模式允许DBA将数据划分为不同的范围。例如,根据年份将数据分为1980年代、1990年代和2000年后的数据。

2.2.2 Hash(哈希)

Hash模式通过对表的一或多个列的哈希值进行计算,将数据分布到不同的分区中。例如,主键的哈希值可以用来分区。

2.2.3 List(列表)

List模式通过预定义的列表值对应的行数据进行分割。例如,可以根据2004年、2005年和2006年的值将数据分区。

2.2.4 Composite(复合模式)

Composite模式是Range、Hash、List等模式的组合使用。例如,可以先按年份分区,然后在每个年份的分区中再按月份或其他列进行进一步的分区。

3. 垂直分区

3.1 分区示例

垂直分区的核心思想是将表中的某些列分区,而不是整个表。这种方式特别适用于存储大量文本和BLOB的表。

3.1.1 partition by

垂直分区的实现方式可以通过以下SQL语句来完成:

CREATE TABLE part_tab (
c1 int default NULL,
c2 varchar(30) default NULL,
c3 date default NULL
) engine=myisam
PARTITION BY RANGE (year(c3)) (
PARTITION p0 VALUES LESS THAN (1995),
PARTITION p1 VALUES LESS THAN (1996),
...
PARTITION p11 VALUES LESS THAN (2010),
PARTITION p12 VALUES LESS THAN (MAXVALUE)
);

3.2 分区管理

数据库分区的管理包括以下几个方面:

3.2.1 删除分区

ALTER TABLE users DROP PARTITION p0;

3.2.2 重建分区

ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000));

3.2.3 新增分区

ALTER TABLE users ADD PARTITION PARTITIONS 8;

3.2.4 数据目录和索引目录

PARTITION p0 VALUES LESS THAN (1995) DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data1/idx'

3.3 优势

初步结论:

  • 分区和未分区占用文件空间大致相同
  • 如果查询语句中有未建立索引字段,分区时间远远优于未分区时间
  • 如果查询语句中字段建立了索引,分区和未分区的差别缩小,分区略优于未分区

转载地址:http://dbxj.baihongyu.com/

你可能感兴趣的文章
mysql 权限整理记录
查看>>
mysql 权限登录问题:ERROR 1045 (28000): Access denied for user ‘root‘@‘localhost‘ (using password: YES)
查看>>
MYSQL 查看最大连接数和修改最大连接数
查看>>
MySQL 查看有哪些表
查看>>
mysql 查看锁_阿里/美团/字节面试官必问的Mysql锁机制,你真的明白吗
查看>>
MySql 查询以逗号分隔的字符串的方法(正则)
查看>>
MySQL 查询优化:提速查询效率的13大秘籍(避免使用SELECT 、分页查询的优化、合理使用连接、子查询的优化)(上)
查看>>
mysql 查询数据库所有表的字段信息
查看>>
【Java基础】什么是面向对象?
查看>>
mysql 查询,正数降序排序,负数升序排序
查看>>
MySQL 树形结构 根据指定节点 获取其下属的所有子节点(包含路径上的枝干节点和叶子节点)...
查看>>
mysql 死锁 Deadlock found when trying to get lock; try restarting transaction
查看>>
mysql 死锁(先delete 后insert)日志分析
查看>>
MySQL 死锁了,怎么办?
查看>>
MySQL 深度分页性能急剧下降,该如何优化?
查看>>
MySQL 深度分页性能急剧下降,该如何优化?
查看>>
MySQL 添加列,修改列,删除列
查看>>
mysql 添加索引
查看>>
MySQL 添加索引,删除索引及其用法
查看>>
mysql 状态检查,备份,修复
查看>>