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';

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

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

| 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操作只對分割槽內的資料進行排序

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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26451536/viewspace-2699581/，如需轉載，請註明出處，否則將追究法律責任。

MySQL的分割槽（二）

相關文章