mysql

mysql partition

 

查看sql版本是否支持分区

mysql> SELECT
    ->   PLUGIN_NAME as Name,
    ->   PLUGIN_VERSION as Version,
    ->   PLUGIN_STATUS as Status
    ->     FROM INFORMATION_SCHEMA.PLUGINS
    ->       WHERE PLUGIN_TYPE='STORAGE ENGINE';
+--------------------+---------+----------+
| Name               | Version | Status   |
+--------------------+---------+----------+
| binlog             | 1.0     | ACTIVE   |
| CSV                | 1.0     | ACTIVE   |
| MEMORY             | 1.0     | ACTIVE   |
| InnoDB             | 5.7     | ACTIVE   |
| MyISAM             | 1.0     | ACTIVE   |
| MRG_MYISAM         | 1.0     | ACTIVE   |
| PERFORMANCE_SCHEMA | 0.1     | ACTIVE   |
| ARCHIVE            | 3.0     | ACTIVE   |
| BLACKHOLE          | 1.0     | ACTIVE   |
| FEDERATED          | 1.0     | DISABLED |
| partition          | 1.0     | ACTIVE   |
+--------------------+---------+----------+
11 rows in set (0.00 sec)

 

 

 

RANGE Partitioning (范围分区)

 

创建表

 

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN (21)
);

 

初始化一些数据源

 

insert into employees(id, fname, lname, hired, job_code, store_id) values(1, '周杰伦', '', '2015-04-01', 100, 1);
insert into employees(id, fname, lname, hired, job_code, store_id) values(2, '蔡依林', ' ', '2015-04-01', 100, 2);
insert into employees(id, fname, lname, hired, job_code, store_id) values(3, 'C罗', ' ', '2015-04-01', 100, 3);
insert into employees(id, fname, lname, hired, job_code, store_id) values(4, '梅西', ' ', '2015-04-01', 100, 4);
insert into employees(id, fname, lname, hired, job_code, store_id) values(5, '里克尔梅', ' ', '2015-04-01', 100, 5);
insert into employees(id, fname, lname, hired, job_code, store_id) values(6, '贝尔', ' ', '2015-04-01', 100, 6);
insert into employees(id, fname, lname, hired, job_code, store_id) values(7, '托雷斯', ' ', '2015-04-01', 100, 7);
insert into employees(id, fname, lname, hired, job_code, store_id) values(8, '伊瓜因', ' ', '2015-04-01', 100, 8);
insert into employees(id, fname, lname, hired, job_code, store_id) values(9, '迪玛利亚', ' ', '2015-04-01', 100, 9);
insert into employees(id, fname, lname, hired, job_code, store_id) values(10, '博格巴', ' ', '2015-04-01', 100, 10);
insert into employees(id, fname, lname, hired, job_code, store_id) values(11, '佩佩', ' ', '2015-04-01', 100, 11);
insert into employees(id, fname, lname, hired, job_code, store_id) values(12, 'J罗', ' ', '2015-04-01', 100, 12);
insert into employees(id, fname, lname, hired, job_code, store_id) values(13, '本泽马', '', '2015-04-01', 100, 13);
insert into employees(id, fname, lname, hired, job_code, store_id) values(14, '郜林', '', '2015-04-01', 100, 14);
insert into employees(id, fname, lname, hired, job_code, store_id) values(15, '舍甫琴科', '', '2015-04-01', 100, 15);
insert into employees(id, fname, lname, hired, job_code, store_id) values(16, '苏亚雷斯', '', '2015-04-01', 100, 16);
insert into employees(id, fname, lname, hired, job_code, store_id) values(17, '内马尔', '', '2015-04-01', 100, 17);
insert into employees(id, fname, lname, hired, job_code, store_id) values(18, '罗本', '', '2015-04-01', 100, 18);
insert into employees(id, fname, lname, hired, job_code, store_id) values(19, '鲁尼', '', '2015-04-01', 100, 19);

 

 

mysql> explain
    -> select * from employees where store_id = 8;
+----+-------------+-----------+------------+------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | key  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | p1         | ALL  | NULL |    5 |    20.00 | Using where |
+----+-------------+-----------+------------+------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

 

 

分区p1只有5条记录

 

 

 查看mysql的系统表

select * from information_schema.partitions 
	where table_name = 'employees'

  

 

mysql 分区的文档  https://dev.mysql.com/doc/refman/5.5/en/partitioning.html