MySQL的分区（二）

分区裁剪

成都创新互联-专业网站定制、快速模板网站建设、高性价比建昌网站开发、企业建站全套包干低至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操作只对分区内的数据进行排序

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