— 分区;
1.分区就是将数据库或其构成元素划分为不同的独立部分;
1.预先组织表存储的方法;
2.当大型表占用大量可用磁盘空间并且需要其他空间来存储表数据时,可以使用物理分区;
3.表文件可以放在多个位置,而不是将数据集中于一个过载的磁盘;
4.此技术与存储引擎有关;
2.MySQL支持水平分区;
1.将特定表行分配为行的子集;
2.有水平分区和垂直分区,本课着重讲述称为“水平分区”的MySQL分区技术;
— 分区类比;
1.“搬家”的类比:
1.假定您正搬出家外,您可以将客厅中的所有物品都放到一个大盒子中;
2.但是,这不是很实际;您将很难找到单个物品,并且这将是一个非常大并且非常重的盒子;
3.最好使用多个盒子来存放您的所有物品,并且根据类型对这些盒子分类;
2.“Home”数据库示例:
1.可以将标签为“书籍”的盒子用于所有书籍;
2.标签为“CD”的盒子用于CD,标签为“影片”的盒子用于影片等等;
3.您现在已将您的物品“分区”到特定盒子中;
— 特定于MySQL的分区;
1.分区的分布是跨物理存储进行的:
1.根据用户在需要时设置的指定规则:MySQL分区允许你设置的规则将各个表的部分分布在整个物理存储中;
2.每个分区存储为单独的单元,非常像一个表;
2.数据的划分:
1.根据分区功能将数据划分为子集:数据的划分通过分区功能来完成,这在MySQL中可以是针对一组范围或值列表,内部散列函数或线性散列函数的简单匹配;
2.分区类型和表达式是表定义的一部分;使用RANGE/LIST COLUMNS时,表达式可以是INT/DATE/DATETIME/CHAR/VARCHAR/BINARY/VARBINARY等数据类型;
3.表达式可以是整数或返回整数值的函数,表达式必须返回NULL或整数值;
4.此值根据定义确定将每条记录存储在哪个分区中;
— 分区类型;
MySQL支持多种类型的分区:
1.RANGE:根据属于指定范围的列值将行分配到分区;范围应该连续但不重叠,使用VALUES LESS THAN运算符进行定义;
2.LIST:根据与离散值集之一匹配的列将行分配到分区;像在由RANGE进行分区一样,必须显式定义每个分区;
3.HASH:基于由用户定义的表达式返回的值而选择的分区,对要插入表中的行的列值进行操作;
4.KEY:与HASH类似,不同之处在于仅提供要评估的一个或多个列并且MySQL服务器提供散列函数;它适用于所有允许的列类型;
5.COLUMNS:RANGE和LIST分区上的变体;COLUMNS分区允许在分区键中使用一个或多个列;
1.RANGE COLUMNS和LIST COLUMNS分区支持使用非整数列(以及前面列出的其他数据类型)来定义值范围或列表成员;
6.LINEAR:MySQL还支持线性散列,其不同于常规散列,线性散列使用线性2的幂算法,而常规散列使用散列函数值的模;
— 分区支持;
1.确定服务器分区支持状态:SHOW PLUGINS\G
1.如果MySQL二进制文件构建有分区支持,您不需要执行任何进一步操作来启用该二进制文件(例如,my.cnf文件中不需要任何特殊条目);
2.如果您没有看到partition插件以及Status值ACTIVE列在SHOW PLUGINS的输出中,则您的MySQL版本不支持分区;
2.禁用分区支持:shell> mysqld –skip-partition
1.partition插件现在具有值DISABLED;
— 使用分区改善性能;
1.特定于大型表的地址问题:
1.将数据分为较小的组以使查询更有效:将数据分割为逻辑分组的表中时,查询搜索较少记录,这样可以大大提高速度;
2.遵守文件系统的文件大小限制;
3.加快维护和删除行运行时速度:例如维护操作运行时速度缓慢(例如,OPTIMIZE和REPAIR)和删除不需要的行时运行时速度缓慢;
2.使用较小的索引来允许“最热”分区具有内存中的整个索引;
3.删除分区来更方便地删除数据;
4.根据日期和时间部署表:例如,您可能希望按年,季度或月部署店铺订单表;
— 使用分区改善性能:删改(分区排除);
1.分区排除以将查询范围限制为确切范围:
1.MySQL优化器可以为查询过滤不需要的分区,它仅搜索符合查询标准的分区;
2.使用WHERE子句进行整表扫描可以作为非匹配删改掉分区;
3.示例所示,假定您有一个书店并且您具有一个数据库,其中包含书店的整个书籍库存;此数据库具有针对每种书籍类别的表,一个客户来到书店,要求提供1990年和2007年之间出版的医学类别的所有书籍列表;因为您使用了分区并且按年份对医学表进行了分区,所以您可以运行仅包括这些年份的查询;优化器“删改”该搜索来仅扫描1990–2007分区,节省了大量时间和工作;
SELECT title FROM book
WHERE category = ‘medicine‘
AND published > 1990 AND published < 2007;
2.执行显式分区选择;
1.例如,如果您知道p0包括2007年以前出版的所有书籍,可以显式指出该分区,从而允许优化器忽略其他分区;可以通过提供列表来选择多个分区和子分区,例如PARTITION(p0, p2sp1);
SELECT title FROM book PARTITION(p0) WHERE category = 'medicine‘
AND published > 1990;
3.还将显式分区选择用于INSERT/REPLACE/UPDATE/DELETE/LOAD,即使您在分区中添加或更改数据,也必须确保新数据满足该分区的分区标准;
— 分区的性能挑战;
1.打开分区表时必须打开所有分区:这是一个小问题,因为打开的表缓存在表高速缓存中;
2.删改具有特定限制:
1.删改在索引列中效率较低,因为它每个分区仅避免一次索引查找;
2.删改对于非索引列的作用要大得多:
1.其WHERE子句中包含非索引列的查询可能需要进行完整表扫描;
2.分区扫描花费的时间是表扫描花费时间的一小部分;
— 基本分区语法;
1.通过在CREATE TABLE语句中使用PARTITION BY子句来创建分区表;PARTITION BY跟在CREATE TABLE的所有部分后面:
CREATE TABLE
PARTITION BY
2.分区类型后面是括号括起来的分区表达式;根据使用的分区类型以及您的要求,该表达式可以是表列的名称,涉及一个或多个列的简单数学表达式或者列名称的逗号分隔列表;
3.PARTITION BY类型包括在其相应语法中:
• PARTITION BY RANGE …
• PARTITION BY RANGE COLUMNS …
• PARTITION BY LIST …
• PARTITION BY LIST COLUMNS …
• PARTITION BY HASH …
• PARTITION BY LINEAR HASH …
• PARTITION BY KEY …
• PARTITION BY LINEAR KEY …
— RANGE分区;
1.使用PARTITION BY RANGE对包含一组指定范围内的指定值的行进行分区;
2.例如,假定您有一个表来存储与其订单相关的数据,并且您希望指定id号小于10,000的订单存储在一个分区中,id号介于10,000和19,999之间的订单存储在另一个分区中,id号介于20,000和29,999之间的订单存储在另一个分区中等等;换句话说,您基于连续值范围来对表进行分区;RANGE分区非常适合于此类分区模式;
CREATE TABLE orders_range
(id INT AUTO_INCREMENT PRIMARY KEY,
customer_surname VARCHAR(30),
store_id INT,
salesperson_id INT,
order_date DATE,
note VARCHAR(500)
) ENGINE = InnoDB
PARTITION BY RANGE(id)
(
PARTITION p0 VALUES LESS THAN(10000),
PARTITION p1 VALUES LESS THAN(20000),
PARTITION p2 VALUES LESS THAN(30000),
PARTITION p3 VALUES LESS THAN(40000),
PARTITION p4 VALUES LESS THAN(50000)
);
— LIST分区;
1.使用PARITION BY LIST对包含指定值的行进行分区;
2.为每个分区指定值列表,从而包含匹配列值的行将存储在相应分区中;例如,假设您的每个订单都放在组织到五个地区中的多个不同店铺中的一个店铺,并且您希望店铺订单放在相同分区中的每个地区中;使用这种类型的分区,您可以任意分隔店铺;假定在这五个地区中有19个店铺,可以创建orders_list表,其按如下所示将来自相同地区的店铺的订单分配到相同分区;
CREATE TABLE orders_list
(id INT AUTO_INCREMENT,
customer_surname VARCHAR(30),
store_id INT,
salesperson_id INT,
order_date DATE,
note VARCHAR(500),
INDEX idx (id)
) ENGINE = InnoDB
PARTITION BY LIST(store_id)
(
PARTITION p0 VALUES IN (1, 3, 4, 17),
PARTITION p1 VALUES IN (2, 12, 14),
PARTITION p2 VALUES IN (6, 8, 20),
PARTITION p3 VALUES IN (5, 7, 9, 11, 16),
PARTITION p4 VALUES IN (10, 13, 15, 18)
);
— HASH分区;
1.PARTITION BY HASH确保在分区中平均分布数据;
2.与RANGE或LIST不同,HASH不需要单独的分区定义;
3.该分布基于创建表时提供的表达式,使用关键字PARTITIONS,后跟所需的(整数)分区数量;
CREATE TABLE orders_hash
(id INT AUTO_INCREMENT PRIMARY KEY,
customer_surname VARCHAR(30),
store_id INT,
salesperson_id INT,
order_date DATE,
note VARCHAR(500)
) ENGINE = InnoDB
PARTITION BY HASH(id) PARTITIONS 4;
— KEY分区;
1.PARTITION BY KEY类似于HASH,只是MySQL服务器使用其自己的散列表达式;
2.不要求分区表达式返回整数或NULL;
3.[LINEAR] KEY后面的表达式仅包含一组零或更多列名称,多个名称由逗号分隔;幻灯片中的示例创建orders_key表,其按已放置订单的日期进行分区;
CREATE TABLE orders_key
(id INT AUTO_INCREMENT PRIMARY KEY,
customer_surname VARCHAR(30),
store_id INT,
salesperson_id INT,
order_date DATE,
note VARCHAR(500)
) ENGINE = InnoDB
PARTITION BY KEY(order_date) PARTITIONS 4;
— 子分区;
1.RANGE和LIST分区表可以进行子分区:子分区(也称为复合分区)是对分区表中的每个分区进行进一步划分;
2.子分区可以使用HASH,LINEAR HASH,KEY或LINEAR KEY分区;
3.在此“店铺订单”数据库示例中,根据orders_range表,可以进一步划分表,这样表可以将每个分区拆分为两个子分区,一共5*2=10个子分区;
CREATE TABLE orders_range_hash
(id INT AUTO_INCREMENT,
customer_surname VARCHAR(30),
store_id INT,
salesperson_id INT,
order_date DATE,
note VARCHAR(500),
PRIMARY KEY(id, store_id)
) ENGINE = InnoDB
PARTITION BY RANGE(id)
SUBPARTITION BY HASH(store_id) SUBPARTITIONS 2
(
PARTITION p0 VALUES LESS THAN(10000),
PARTITION p1 VALUES LESS THAN(20000),
PARTITION p2 VALUES LESS THAN(30000),
PARTITION p3 VALUES LESS THAN(40000),
PARTITION p4 VALUES LESS THAN(50000)
);
TIPS:ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table’s partitioning function
— 获取分区信息;
MySQL提供了多种方法来确定表的分区状态:
• SHOW CREATE TABLE:查看用于创建分区表的分区子句;
• SHOW TABLE STATUS:确定表是否进行了分区;
• INFORMATION_SCHEMA.PARTITIONS:查询INFORMATION_SCHEMA.PARTITIONS表;
• EXPLAIN PARTITIONS SELECT:显示给定SELECT语句使用的分区;
— 获取分区信息:SHOW CREATE TABLE;
SHOW CREATE TABLE orders_hash\G
— 获取分区信息:SHOW TABLE STATUS;
SHOW TABLE STATUS LIKE ‘orders_hash’\G
1.Create_options列包含字符串partitioned;
2.Engine列包含表的所有分区使用的存储引擎的名称;但是如果指定不同的引擎会报错;
ERROR 1497 (HY000): The mix of handlers in the partitions is not allowed in this version of MySQL;
— 获取分区信息:INFORMATION_SCHEMA;
1.可以在INFORMATION_SCHEMA.PARTITIONS表中查询分区详细信息;
2.获取所有数据库表及其分区的列表:
SELECT PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME=’orders_list’ AND TABLE_SCHEMA=’orders’;
+—————-+——————+———————-+———————–+
| PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION |
+—————-+——————+———————-+———————–+
| p0 | LIST | store_id | 1,3,4,17 |
| p1 | LIST | store_id | 2,12,14 |
| p2 | LIST | store_id | 6,8,20 |
| p3 | LIST | store_id | 5,7,9,11,16 |
| p4 | LIST | store_id | 10,13,15,18 |
+—————-+——————+———————-+———————–+
5 rows in set (0.00 sec)
— 获取分区信息:EXPLAIN PARTITIONS;
1.显示MySQL处理分区的方式;
2.判断查询将访问哪些分区以及使用这些分区的方式;
3.只能用在包含行数据的表中:EXPLAIN PARTITIONS SELECT * FROM orders_range\G
— 更改分区;
1.数据库通常要求对现有分区设置进行动态更改;
2.MySQL允许多种类型的分区更改:
– 添加;
– 删除;
– 合并;
– 拆分;
– 重组;
– 重新定义;
3.使用ALTER TABLE语句的简单扩展进行这些更改;
— 重新定义分区类型;
1.将非分区表更改为分区表;
2.完全重新定义现有分区,包括类型;
3.将ALTER TABLE与PARTITION BY配合使用,将分区类型从RANGE更改为HASH:
1.查看分区类型:
SELECT PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME=’orders_range’ AND TABLE_SCHEMA=’db1′;
2.在线重定义:
ALTER TABLE orders_range PARTITION BY HASH(id) PARTITIONS 4;
3.再次查看;
SELECT PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME=’orders_range’ AND TABLE_SCHEMA=’db1′;
— 交换分区;
1.将表分区或子分区与表交换:
1.将分区或子分区中的所有现有行移至非分区表;
2.将非分区表中的所有现有行移至表分区或子分区;
3.交换分区不会在分区表或交换表上调用触发器;
4.将重置交换表中的所有AUTO_INCREMENT列;
2.要交换的表不能进行分区:
1.它必须与分区表具有相同表结构:
2.交换之前,非分区表中的行必须位于为分区或子分区定义的范围内;
例子:
1.把表修改回范围分区;
ALTER TABLE orders_range
PARTITION BY RANGE(id)
(
PARTITION p0 VALUES LESS THAN(10000),
PARTITION p1 VALUES LESS THAN(20000),
PARTITION p2 VALUES LESS THAN(30000),
PARTITION p3 VALUES LESS THAN(40000),
PARTITION p4 VALUES LESS THAN(50000)
);
2.查看表信息:
SELECT PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME=’orders_range’ AND TABLE_SCHEMA=’db1′;
3.创建测试表并插入数据;
CREATE TABLE orders_ex
(id INT AUTO_INCREMENT PRIMARY KEY,
customer_surname VARCHAR(30),
store_id INT,
salesperson_id INT,
order_date DATE,
note VARCHAR(500)
) ENGINE = InnoDB;
INSERT INTO orders_ex VALUES(1, ‘test’, 1, 1, sysdate(), ”);
INSERT INTO orders_ex VALUES(10001, ‘test’, 1, 1, sysdate(), ”);
COMMIT;
INSERT INTO orders_range VALUES(1, ‘xxx’, 1, 1, sysdate(), ”);
INSERT INTO orders_range VALUES(10001, ‘xxx’, 1, 1, sysdate(), ”);
COMMIT;
4.分区交换;
mysql> ALTER TABLE orders_range EXCHANGE PARTITION p0 WITH TABLE orders_ex;
ERROR 1737 (HY000): Found a row that does not match the partition
5.修正数据;
mysql> UPDATE orders_ex SET id = 9999 WHERE id = 10001;
mysql> COMMIT;
mysql> ALTER TABLE orders_range EXCHANGE PARTITION p0 WITH TABLE orders_ex;
6.查看结果;
mysql> SELECT * FROM orders_ex;
mysql> SELECT * FROM orders_range PARTITION(p0);
— 删除分区;
1.可以删除一个或多个分区;
1.删除分区与删除行不一样;虽然删除分区确实可以删除分区内的所有数据,但是使用DROP,PARTITION的主要目的是删除分区指定自身,以及不再允许将数据传递到该分区;
2.可以使用ALTER TABLE … TRUNCATE PARTITION语句删除分区表的一个或多个分区中的所有行(子句中指定的分区不必是连续的);
2.RANGE或LIST表允许此操作;
3.将ALTER TABLE与DROP PARTITION配合使用:
mysql> ALTER TABLE orders_range TRUNCATE PARTITION p0, p1;
mysql> SELECT * FROM orders_range;
mysql> ALTER TABLE orders_range DROP PARTITION p0;
mysql> SELECT PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME=’orders_range’ AND TABLE_SCHEMA=’db1′;
— 使用DROP PARTITION的问题;
1.需要DROP特权才能使用DROP PARTITION;
2.删除分区会删除该分区中的所有数据;
3.DROP PARTITION不返回所删除的行数;
4.DROP PARTITION更改表定义;
5.可以按任何顺序删除分区;
6.将根据新分区处理插入到所删除分区中的行;
1.删除LIST分区会禁止已经匹配该分区的INSERT和UPDATE操作;
2.删除RANGE分区将删除该分区及其数据,来自INSERT和UPDATE操作的新数据将存储在范围中的下一个更大的分区,如果没有范围大于已删除分区的范围的分区,无法对已删除分区的范围中的数据执行INSERT和UPDATE操作;
7.可以用单个语句删除多个分区:ALTER TABLE orders_range DROP PARTITION p1, p3;
— 删除分区;
1.要删除表中的所有分区从而将其恢复为非分区表,请将ALTER TABLE与REMOVE PARTITIONING配合使用;
1.此操作不会删除任何表数据;
2.REMOVE PARTITIONING执行完整表复制,与普通ALTER TABLE ALGORITHM=COPY一样;
例子:
1.使orders_range表恢复到非分区状态:ALTER TABLE orders_range REMOVE PARTITIONING;
2.查看orders_range表信息:
SHOW TABLE STATUS LIKE ‘orders_range’;
— 更改分区对性能的影响;
1.根据分区的数量,与创建非分区表相比,创建分区表的速度稍微慢一些;
2.分区操作处理速度比较:
1.对于大型事务表,DROP PARTITION比DELETE快得多;
2.ADD PARTITION在RANGE和LIST表上相当快;
3.对于在KEY或HASH表上运行的ADD PARTITION,速度取决于已经存储的行数;
1.如果存在更多数据,需要花费更长时间来添加新分区;
2.在KEY/HASH上运行的ADD PARTITION将所有行重新分布到新数量的分区,有效地执行完整表复制;
4.对超大型表运行COALESCE PARTITION,REORGANIZE PARTITION和PARTITION BY时,它们的执行速度可能会很慢;
3.在此类操作过程中,硬件I/O的开销比分区引擎的开销高得多;
— 测验;
d
— 分区:存储引擎功能;
1.分区存储在与InnoDB表相同位置中的文件中:
1.可以提供DATA DIRECTORY选项来重定位分区;
2.每个PARTITION子句可以包括一个[STORAGE] ENGINE选项,该选项没有作用,每个 分区使用的存储引擎与表作为一个整体而使用的存储引擎相同;
2.每个分区在以下数据目录中具有其自己的文件:
1.如果禁用innodb_file_per_table,分区将存储在共享表空间中;
3.所有数据和索引都将进行分区:
1.不能仅对数据或仅对索引进行分区;
4.分区可用于其他存储引擎:
1.不可用于MERGE,FEDERATED,CSV;
2.是在存储引擎级别实现的;
eg:
CREATE TABLE entries
(id INT,
entered DATE
)
PARTITION BY RANGE(YEAR(entered))
(
PARTITION p0 VALUES LESS THAN (2000) DATA DIRECTORY = ‘/data/p0’,
PARTITION p1 VALUES LESS THAN MAXVALUE DATA DIRECTORY = ‘/data/p1’
);
— 分区与锁定;
1.MySQL表将在执行服务器代码的过程中锁定;
1.锁定操作在执行每个语句过程中由表存储引擎来处理;
2.每个存储引擎处理锁的方式均不相同;
2.每个分区有一个存储引擎实例;
1.将要求锁定每个非删改分区/存储引擎实例;
3.锁定分区表时,将锁定非删改分区;
1.仅在执行语句过程中保持该锁定;
4.ALTER…PARTITION语句通常在表上使用写入锁;
1.从此类表进行的读取相对来说不受影响,待定INSERT和UPDATE操作在分区一完成后就会执行;优先级的问题;
2.执行ALTER TABLE t
— 分区限制;
1.常规:
– 每个表的最大分区数为8192;
– 不支持空间类型;
– 不能对临时表进行分区;
– 不能对日志表进行分区;
2.外键和索引:
– 不支持外键;
– 不支持FULLTEXT索引;
– 无全局索引:每个分区都有各自的索引;
3.仅可能在以下情况下进行子分区:
– 通过RANGE和LIST进行分区时;
– 通过LINEAR HASH或LINEAR KEY进行时;
— 分区表达式限制;
1.用于RANGE/LIST/HASH分区的表达式的结果必须为整数:
1.RANGE COLUMNS和LIST COLUMNS分区允许更大范围的数据类型;
2.RANGE COLUMNS和LIST COLUMNS分区类型不需要整数列或表达式;
3.RANGE COLUMNS和LIST COLUMNS分区可以在分区键中使用多个列;
4.该列可以是任何整数类型/DATE/DATETIME/CHAR/VARCHAR/BINARY/VARBINARY;
2.不能在分区表达式中使用TEXT或BLOB;
3.不允许使用UDF,存储函数,变量,某些运算符和某些置函数:
1.运算符:|,&,^,< <,>>,~;
4.不应在创建表后更改SQL模式;
5.分区表达式中不支持子查询;
6.分区表达式中使用的所有列都必须是表的所有唯一索引的一部分:
1.因为索引必须与数据一起进行分区;
2.要保持“唯一性”,具有相同唯一键的两行必须转至相同分区;否则,它们将违反唯一键限制;
— 课后练习;