MySQL的分区（二）

来源：这里教程网时间：2026-03-01 15:13:28 作者：

分区裁剪使用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 <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; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 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操作只对分区内的数据进行排序表中的主键和唯一索引必须包含分区键的所有列。