分区裁剪
成都创新互联-专业网站定制、快速模板网站建设、高性价比建昌网站开发、企业建站全套包干低至880元,成熟完善的模板库,直接使用。一站式建昌网站制作公司更省心,省钱,快速模板网站建设找我们，业务覆盖建昌地区。费用合理售后完善，10多年实体公司更值得信赖。
使用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';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 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;
+----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+----------+-------------+
| 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';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 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';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 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';
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------------+
| 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';
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------------+
| 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 +----+------------+ | 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 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; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 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'; +---------------+----------------+-------------------+------------+ | table_name | partition_name | subpartition_name | table_rows | +---------------+----------------+-------------------+------------+ | 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); +----+-------+----------+----------+---------------+ | id | fname | lname | store_id | department_id | +----+-------+----------+----------+---------------+ | 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的分区（二）
文章网址：http://bzwzjz.com/article/jdgsij.html