MySQL分区表的使用介绍_MySQL
程序员文章站
2022-05-30 21:26:45
...
bitsCN.com
MySQL使用分区表的好处:1,可以把一些归类的数据放在一个分区中,可以减少服务器检查数据的数量加快查询。2,方便维护,通过删除分区来删除老的数据。3,分区数据可以被分布到不同的物理位置,可以做分布式有效利用多个硬盘驱动器。
MySQL可以建立四种分区类型的分区: RANGE 分区:基于属于一个给定连续区间的列值,把多行分配给分区。· LIST 分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。 · HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。· KEY 分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。一般用得多的是range分区和list分区。RANGE分区这里以一个销售的业务来做测试销售表有日期/商品/销售额三个字段测试数据从2010年1月1日至2010年9月31日以“月”为单位进行分区初期分区定义首先需要查看,当前数据库是否支持分区mysql>SHOW VARIABLES LIKE '%partition%';+-------------------+-------+| Variable_name | Value | +-------------------+-------+| have_partitioning | YES | +-------------------+-------+1 row in set (0.03 sec)创建分区表,按照年月的方式分区。mysql> CREATE TABLE sale_data ( -> sale_date DATETIME NOT NULL, -> sale_item VARCHAR(2) NOT NULL , -> sale_money DECIMAL(10,2) NOT NULL -> ) -> PARTITION BY RANGE (YEAR(sale_date)*100+MONTH(sale_date)) ( -> PARTITION p201001 VALUES LESS THAN (201002), -> PARTITION p201002 VALUES LESS THAN (201003), -> PARTITION p201003 VALUES LESS THAN (201004), -> PARTITION p201004 VALUES LESS THAN (201005), -> PARTITION p201005 VALUES LESS THAN (201006), -> PARTITION p201006 VALUES LESS THAN (201007), -> PARTITION p201007 VALUES LESS THAN (201008), -> PARTITION p201008 VALUES LESS THAN (201009), -> PARTITION p201009 VALUES LESS THAN (201010), -> PARTITION pcatchall VLAUES LESS THAN MAXVALUE -> ); Query OK, 0 rows affected (0.20 sec)新增分区mysql> ALTER TABLE sale_data -> ADD PARTITION (PARTITION p201010 VALUES LESS THAN (201011)); Query OK, 0 rows affected (0.36 sec)Records: 0 Duplicates: 0 Warnings: 0 删除分区--当删除了一个分区,也同时删除了该分区中所有的数据。mysql> ALTER TABLE sale_data DROP PARTITION p201010;Query OK, 0 rows affected (0.22 sec) Records: 0 Duplicates: 0 Warnings: 0 分区的合并下面的SQL,将p201001 - p201009 合并为3个分区p2010Q1 - p2010Q3 mysql> ALTER TABLE sale_data -> REORGANIZE PARTITION p201001,p201002,p201003, -> p201004,p201005,p201006, -> p201007,p201008,p201009 INTO -> ( -> PARTITION p2010Q1 VALUES LESS THAN (201004), -> PARTITION p2010Q2 VALUES LESS THAN (201007), -> PARTITION p2010Q3 VALUES LESS THAN (201010) -> ); Query OK, 0 rows affected (1.14 sec)Records: 0 Duplicates: 0 Warnings: 0 分区的拆分下面的SQL,将p2010Q1 分区,拆分为s2009 与s2010 两个分区 mysql> ALTER TABLE sale_data REORGANIZE PARTITION p2010Q1 INTO ( -> PARTITION s2009 VALUES LESS THAN (201001), -> PARTITION s2010 VALUES LESS THAN (201004) -> ); Query OK, 0 rows affected (0.36 sec)Records: 0 Duplicates: 0 Warnings: 0 一个利用不同物理位置数据源做分区的例子:CREATE TABLE ts (id INT, purchased DATE) ENGINE=innodb PARTITION BY RANGE(YEAR(purchased)) SUBPARTITION BY HASH(id) ( PARTITION p0 VALUES LESS THAN (1990) ( SUBPARTITION s0 //在大的分区下又有小的分区 DATA DIRECTORY='/usr/local/mysql/data0' //数据源 INDEX DIRECTORY='/usr/local/mysql/index0', //索引数据源 SUBPARTITION s1 DATA DIRECTORY='/usr/local/mysql/data1' INDEX DIRECTORY='/usr/local/mysql/index1' ), PARTITION p1 VALUES LESS THAN (MAXVALUE) ( SUBPARTITION s2 DATA DIRECTORY='/usr/local/mysql/data1' INDEX DIRECTORY='/usr/local/mysql/index1', SUBPARTITION s3 DATA DIRECTORY='/usr/local/mysql/data2' INDEX DIRECTORY='/usr/local/mysql/index2' ) ); 分区索引的局限:1,所有分区都要使用同样的引擎。2,分区表的每一个唯一索引必须包含由分区函数引用的列。3,mysql能避免查询所有的分区,但仍然锁定了所有分区。4,分区函数能使用的函数和表达式有限,例如函数有上面的4种。5,分区不支持外键。 6,不能使用LOAD INDEX INTO CACHE7,分区并不能总是改善性能,要进行性能评测。例如可以使用expalin partitions 来查看查询语句是否使用分区过滤了数据:mysql> explain partitions select * from fenqubiao where day
MySQL使用分区表的好处:1,可以把一些归类的数据放在一个分区中,可以减少服务器检查数据的数量加快查询。2,方便维护,通过删除分区来删除老的数据。3,分区数据可以被分布到不同的物理位置,可以做分布式有效利用多个硬盘驱动器。
MySQL可以建立四种分区类型的分区: RANGE 分区:基于属于一个给定连续区间的列值,把多行分配给分区。· LIST 分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。 · HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。· KEY 分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。一般用得多的是range分区和list分区。RANGE分区这里以一个销售的业务来做测试销售表有日期/商品/销售额三个字段测试数据从2010年1月1日至2010年9月31日以“月”为单位进行分区初期分区定义首先需要查看,当前数据库是否支持分区mysql>SHOW VARIABLES LIKE '%partition%';+-------------------+-------+| Variable_name | Value | +-------------------+-------+| have_partitioning | YES | +-------------------+-------+1 row in set (0.03 sec)创建分区表,按照年月的方式分区。mysql> CREATE TABLE sale_data ( -> sale_date DATETIME NOT NULL, -> sale_item VARCHAR(2) NOT NULL , -> sale_money DECIMAL(10,2) NOT NULL -> ) -> PARTITION BY RANGE (YEAR(sale_date)*100+MONTH(sale_date)) ( -> PARTITION p201001 VALUES LESS THAN (201002), -> PARTITION p201002 VALUES LESS THAN (201003), -> PARTITION p201003 VALUES LESS THAN (201004), -> PARTITION p201004 VALUES LESS THAN (201005), -> PARTITION p201005 VALUES LESS THAN (201006), -> PARTITION p201006 VALUES LESS THAN (201007), -> PARTITION p201007 VALUES LESS THAN (201008), -> PARTITION p201008 VALUES LESS THAN (201009), -> PARTITION p201009 VALUES LESS THAN (201010), -> PARTITION pcatchall VLAUES LESS THAN MAXVALUE -> ); Query OK, 0 rows affected (0.20 sec)新增分区mysql> ALTER TABLE sale_data -> ADD PARTITION (PARTITION p201010 VALUES LESS THAN (201011)); Query OK, 0 rows affected (0.36 sec)Records: 0 Duplicates: 0 Warnings: 0 删除分区--当删除了一个分区,也同时删除了该分区中所有的数据。mysql> ALTER TABLE sale_data DROP PARTITION p201010;Query OK, 0 rows affected (0.22 sec) Records: 0 Duplicates: 0 Warnings: 0 分区的合并下面的SQL,将p201001 - p201009 合并为3个分区p2010Q1 - p2010Q3 mysql> ALTER TABLE sale_data -> REORGANIZE PARTITION p201001,p201002,p201003, -> p201004,p201005,p201006, -> p201007,p201008,p201009 INTO -> ( -> PARTITION p2010Q1 VALUES LESS THAN (201004), -> PARTITION p2010Q2 VALUES LESS THAN (201007), -> PARTITION p2010Q3 VALUES LESS THAN (201010) -> ); Query OK, 0 rows affected (1.14 sec)Records: 0 Duplicates: 0 Warnings: 0 分区的拆分下面的SQL,将p2010Q1 分区,拆分为s2009 与s2010 两个分区 mysql> ALTER TABLE sale_data REORGANIZE PARTITION p2010Q1 INTO ( -> PARTITION s2009 VALUES LESS THAN (201001), -> PARTITION s2010 VALUES LESS THAN (201004) -> ); Query OK, 0 rows affected (0.36 sec)Records: 0 Duplicates: 0 Warnings: 0 一个利用不同物理位置数据源做分区的例子:CREATE TABLE ts (id INT, purchased DATE) ENGINE=innodb PARTITION BY RANGE(YEAR(purchased)) SUBPARTITION BY HASH(id) ( PARTITION p0 VALUES LESS THAN (1990) ( SUBPARTITION s0 //在大的分区下又有小的分区 DATA DIRECTORY='/usr/local/mysql/data0' //数据源 INDEX DIRECTORY='/usr/local/mysql/index0', //索引数据源 SUBPARTITION s1 DATA DIRECTORY='/usr/local/mysql/data1' INDEX DIRECTORY='/usr/local/mysql/index1' ), PARTITION p1 VALUES LESS THAN (MAXVALUE) ( SUBPARTITION s2 DATA DIRECTORY='/usr/local/mysql/data1' INDEX DIRECTORY='/usr/local/mysql/index1', SUBPARTITION s3 DATA DIRECTORY='/usr/local/mysql/data2' INDEX DIRECTORY='/usr/local/mysql/index2' ) ); 分区索引的局限:1,所有分区都要使用同样的引擎。2,分区表的每一个唯一索引必须包含由分区函数引用的列。3,mysql能避免查询所有的分区,但仍然锁定了所有分区。4,分区函数能使用的函数和表达式有限,例如函数有上面的4种。5,分区不支持外键。 6,不能使用LOAD INDEX INTO CACHE7,分区并不能总是改善性能,要进行性能评测。例如可以使用expalin partitions 来查看查询语句是否使用分区过滤了数据:mysql> explain partitions select * from fenqubiao where day
声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn核实处理。
下一篇: 绑定变量与非绑定变量资源消耗对比
推荐阅读
-
MySQL常见内存不足启动失败的完美解决方法
-
Mysql5.7中使用group concat函数数据被截断的问题完美解决方法
-
Windows 64 位 mysql 5.7以上版本包解压中没有data目录和my-default.ini及服务无法启动的快速解决办法(问题小结)
-
linux下mysql的安装步骤
-
搜狐影音及搜狐影音中的一些小技巧使用介绍
-
Mysql根据时间查询日期的优化技巧
-
浅谈SQLServer的ISNULL函数与Mysql的IFNULL函数用法详解
-
SQLServer导出数据到MySQL实例介绍
-
mysql 终结点映射器中没有更多的终结点可用的解决方法
-
Windows(x86,64bit)升级MySQL 5.7.17免安装版的详细教程