投稿
  1. 云编程开发首页
  2. 数据库

MySQL的分区（二）

1524990177 • • 数据库 • 阅读 105

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

相关推荐: 查询全天的数据

•查询当天的所有数据SELECT FROM 表名 WHERE DATEDIFF(字开发云主机域名段,NOW())=0•查询昨天的所有数据SELECT FROM 表名 WHERE DATEDIFF(字段,NOW())=-1相关推荐: MySQL索引最左前缀原则导…

免责声明：本站发布的图片视频文字，以转载和分享为主，文章观点不代表本站立场，本站不承担相关法律责任；如果涉及侵权请联系邮箱：360163164@qq.com举报，并提供相关证据，经查实将立刻删除涉嫌侵权内容。

赞 (0)
打赏 微信扫一扫 微信扫一扫
15249901771524990177
0 0
上一篇 06/04 21:53
下一篇 06/04 21:53

相关推荐