MySQL的分区（二）

发布时间：2020-08-07 09:05:40 作者：to_be_Dba
来源：ITPUB博客阅读：265

分区裁剪

使用explain partitions能显示出是否进行了分区裁剪.

mysql> drop table t2;

Query OK, 0 rows affected (2.90 sec)

mysql> CREATE TABLE t2 (

-> fname VARCHAR(50) NOT NULL,

-> lname VARCHAR(50) NOT NULL,

-> region_code TINYINT UNSIGNED NOT NULL,

-> dob DATE NOT NULL

-> )

-> PARTITION BY RANGE( YEAR(dob) ) (

-> PARTITION d0 VALUES LESS THAN (1970),

-> PARTITION d1 VALUES LESS THAN (1975),

-> PARTITION d2 VALUES LESS THAN (1980),

-> PARTITION d3 VALUES LESS THAN (1985),

-> PARTITION d4 VALUES LESS THAN (1990),

-> PARTITION d5 VALUES LESS THAN (2000),

-> PARTITION d6 VALUES LESS THAN (2005),

-> PARTITION d7 VALUES LESS THAN MAXVALUE

-> );

Query OK, 0 rows affected (1.19 sec)

mysql> explain partitions SELECT * FROM t2 WHERE dob = '1982-06-23';

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

| 1 | SIMPLE | t2 | d3 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set, 2 warnings (0.10 sec)

mysql> explain partitions SELECT * FROM t2 WHERE year(dob) = 1972;

+----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+----------+-------------+

+----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+----------+-------------+

| 1 | SIMPLE | t2 | d0,d1,d2,d3,d4,d5,d6,d7 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |

+----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set, 2 warnings (0.01 sec)

与oracle不同的是，不需要考虑分区键的函数（year）。使用year（）进行查询时，反而无法进行裁剪。

分区裁剪可以用于delete、update、select。insert操作也会自动选择分区。

mysql> explain partitions UPDATE t2 SET region_code = 8 WHERE dob BETWEEN '1991-02-15' AND '1997-04-25';

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

| 1 | UPDATE | t2 | d5 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.38 sec)

mysql> explain partitions DELETE FROM t2 WHERE dob >= '1984-06-21' AND dob <= '1999-06-21';

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

| 1 | DELETE | t2 | d3,d4,d5 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.41 sec)

mysql> explain partitions SELECT * FROM t2 WHERE dob < '1982-12-01';

+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------------+

+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------------+

| 1 | SIMPLE | t2 | d0,d1,d2,d3 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |

+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set, 2 warnings (0.00 sec)

使用不合法的日期是，执行计划也进行了分区裁剪，但实际查不到数据：

mysql> explain partitions SELECT * FROM t2 WHERE dob < '1982-12-00';

+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------------+

+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------------+

| 1 | SIMPLE | t2 | d0,d1,d2,d3 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |

+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set, 5 warnings (0.00 sec)

mysql> select * from t4 where datecol <date '2000-01-01';

+----+------------+

| id | datecol |

+----+------------+

| 1 | 1995-02-21 |

| 4 | 1996-03-14 |

| 5 | 1995-03-11 |

| 6 | 1997-05-07 |

| 9 | 1997-05-27 |

| 13 | 1996-02-06 |

| 22 | 1998-12-28 |

| 27 | 1997-10-28 |

| 29 | 1996-02-17 |

| 7 | 1999-03-01 |

| 12 | 1999-09-15 |

| 20 | 1999-03-11 |

| 21 | 1999-09-17 |

| 25 | 1999-03-03 |

| 26 | 1999-06-20 |

+----+------------+

15 rows in set (0.00 sec)

mysql> select * from t4 where datecol <date '2000-01-00';

ERROR 1525 (HY000): Incorrect DATE value: '2000-01-00'

不仅range分区可以裁剪，list、hash等分区也可以。如：

mysql> CREATE TABLE t8 (

-> fname VARCHAR(50) NOT NULL,

-> lname VARCHAR(50) NOT NULL,

-> region_code TINYINT UNSIGNED NOT NULL,

-> dob DATE NOT NULL

-> )

-> PARTITION BY KEY(region_code)

-> PARTITIONS 8;

Query OK, 0 rows affected (1.07 sec)

mysql> explain update t8 set fname='1' where region_code=7;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

| 1 | UPDATE | t8 | p2 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set (0.09 sec)

####################################################

分区表的查询

可以在以下语句中指定分区名称列表：

select、delete、insert、replace、update、load data、load xml

可以同时指定多个分区或子分区，名称可以无需、相互包含。如：

mysql> show create table employees_sub\G

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

Table: employees_sub

Create Table: CREATE TABLE `employees_sub` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`fname` varchar(25) NOT NULL,

`lname` varchar(25) NOT NULL,

`store_id` int(11) NOT NULL,

`department_id` int(11) NOT NULL,

PRIMARY KEY (`id`,`lname`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

/*!50100 PARTITION BY RANGE (id)

SUBPARTITION BY KEY (lname)

SUBPARTITIONS 2

(PARTITION p0 VALUES LESS THAN (5) ENGINE = InnoDB,

PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB,

PARTITION p2 VALUES LESS THAN (15) ENGINE = InnoDB,

PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

1 row in set (0.00 sec)

mysql> INSERT INTO employees_sub # re-use data in employees table

-> SELECT * FROM employees;

Query OK, 18 rows affected (0.40 sec)

Records: 18 Duplicates: 0 Warnings: 0

mysql> select table_name,partition_name,subpartition_name,table_rows from information_schema.partitions where table_name='employees_sub';

+---------------+----------------+-------------------+------------+

+---------------+----------------+-------------------+------------+

| employees_sub | p0 | p0sp0 | 4 |

| employees_sub | p0 | p0sp1 | 0 |

| employees_sub | p1 | p1sp0 | 5 |

| employees_sub | p1 | p1sp1 | 0 |

| employees_sub | p2 | p2sp0 | 5 |

| employees_sub | p2 | p2sp1 | 0 |

| employees_sub | p3 | p3sp0 | 4 |

| employees_sub | p3 | p3sp1 | 0 |

+---------------+----------------+-------------------+------------+

8 rows in set (0.40 sec)

mysql> select * from employees_sub partition(p0,p1sp0,p1);

+----+-------+----------+----------+---------------+

+----+-------+----------+----------+---------------+

| 1 | Bob | Taylor | 3 | 2 |

| 2 | Frank | Williams | 1 | 2 |

| 3 | Ellen | Johnson | 3 | 4 |

| 4 | Jim | Smith | 2 | 4 |

| 5 | Mary | Jones | 1 | 1 |

| 6 | Linda | Black | 2 | 3 |

| 7 | Ed | Jones | 2 | 1 |

| 8 | June | Wilson | 3 | 1 |

| 9 | Andy | Smith | 1 | 3 |

+----+-------+----------+----------+---------------+

9 rows in set (0.01 sec)

其他几个语句的举例；

UPDATE employees PARTITION (p0) SET store_id = 2 WHERE fname = 'Jill';

REPLACE INTO employees PARTITION (p3) VALUES (20, 'Jan', 'Jones', 3, 2);

############################################################

分区的限制条件

分区中不允许使用存储过程、函数等，不能声明变量

分区表达式中可以使用算数运算符，但结果必须为整数或NULL

sql mode的修改可能导致分区表的中断或数据丢失，因此不要对其进行修改

分区表的性能受文件系统类型、字符集、磁盘转速、swap空间等因素影响。

一般应确保开启了large_files_support,并合理设置open_files_limit.

innodb引擎开启innodb_file_per_table可提高性能。

表的分区操作会在表上施加写锁

使用MyISAM引擎要比Innodb、NDB快

在5.7版本中，LOAD DATA使用缓存提高性能，每个分区使用130KB的buffer来提高性能。

最大分区数：8192，包括子分区

不支持查询缓存

innodb分区表不支持外键

alter table …… order by操作只对分区内的数据进行排序

表中的主键和唯一索引必须包含分区键的所有列。

MySQL的分区（二）

相关阅读