MySQL的分區（二）

分區裁剪

使用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操作只對分區內的數據進行排序

表中的主鍵和唯一索引必須包含分區鍵的所有列。