[root@mysql-master ~]# mysql -u root -p
mysql> use course;
Database changed
mysql> 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) );
Query OK, 0 rows affected (0.14 sec)
mysql> insert into employees values(1,'a','a',now(),now(),1,1),(2,'b','b',now(),now(),1,6);
Query OK, 2 rows affected, 4 warnings (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 4
mysql> select * from employees;
+----+-------+-------+------------+------------+----------+----------+
| id | fname | lname | hired | separated | job_code | store_id |
+----+-------+-------+------------+------------+----------+----------+
| 1 | a | a | 2020-05-07 | 2020-05-07 | 1 | 1 |
| 2 | b | b | 2020-05-07 | 2020-05-07 | 1 | 6 |
+----+-------+-------+------------+------------+----------+----------+
2 rows in set (0.00 sec)
mysql> select * from employees where store_id=1;
+----+-------+-------+------------+------------+----------+----------+
| id | fname | lname | hired | separated | job_code | store_id |
+----+-------+-------+------------+------------+----------+----------+
| 1 | a | a | 2020-05-07 | 2020-05-07 | 1 | 1 |
+----+-------+-------+------------+------------+----------+----------+
1 row in set (0.00 sec)
mysql> explain select * from employees where store_id=1;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | employees | p0 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from employees;
+----+-------------+-----------+-------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+-------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | employees | p0,p1,p2,p3 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | NULL |
+----+-------------+-----------+-------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from employees where store_id=6;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | employees | p1 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from employees where store_id in (6,15);
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | employees | p1,p2 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> exit;
Bye
[root@mysql-master ~]# ps -ef| grep mysql
root 4118 1 0 04:25 ? 00:00:00 /bin/sh /data/mysql/mysql/bin/mysqld_safe --datadir=/data/mysql/mysql/data --pid-file=/data/mysql/mysql/data/mysql-master.pid
mysql 4315 4118 0 04:25 ? 00:02:50 /data/mysql/mysql/bin/mysqld --basedir=/data/mysql/mysql --datadir=/data/mysql/mysql/data --plugin-dir=/data/mysql/mysql/lib/plugin --user=mysql --log-error=/data/mysql/mysql/data/abc.log --pid-file=/data/mysql/mysql/data/mysql-master.pid
root 4951 4915 0 09:33 pts/0 00:00:00 grep --color=auto mysql
[root@mysql-master ~]# cd /data/mysql/mysql/data/course
[root@mysql-master /data/mysql/mysql/data/course]# pwd
/data/mysql/mysql/data/course
[root@mysql-master /data/mysql/mysql/data/course]# ll
total 1980
-rw-r----- 1 mysql mysql 114688 Apr 23 14:51 A.ibd
-rw-r----- 1 mysql mysql 114688 Apr 23 14:51 B.ibd
-rw-r----- 1 mysql mysql 114688 Apr 23 14:51 C.ibd
-rw-r----- 1 mysql mysql 131072 Apr 22 21:14 course.ibd
-rw-r----- 1 mysql mysql 131072 Apr 23 17:54 Course.ibd
-rw-r----- 1 mysql mysql 114688 Apr 22 21:27 dept.ibd
-rw-r----- 1 mysql mysql 114688 Apr 23 14:51 D.ibd
-rw-r----- 1 mysql mysql 114688 May 7 09:27 employees#P#p0.ibd
-rw-r----- 1 mysql mysql 114688 May 7 09:27 employees#P#p1.ibd
-rw-r----- 1 mysql mysql 114688 May 7 09:25 employees#P#p2.ibd
-rw-r----- 1 mysql mysql 114688 May 7 09:25 employees#P#p3.ibd
-rw-r----- 1 mysql mysql 114688 Apr 22 14:47 myset.ibd
-rw-r----- 1 mysql mysql 114688 Apr 21 11:54 score_avg.ibd
-rw-r----- 1 mysql mysql 114688 Apr 21 11:54 score.ibd
-rw-r----- 1 mysql mysql 114688 Apr 22 14:34 shirts.ibd
-rw-r----- 1 mysql mysql 131072 Apr 22 21:26 students.ibd
-rw-r----- 1 mysql mysql 114688 Apr 23 17:30 teacher_history.ibd
-rw-r----- 1 mysql mysql 131072 Apr 30 16:58 teacher.ibd
-rw-r----- 1 mysql mysql 114688 May 6 16:44 temp122.ibd
-rw-r----- 1 mysql mysql 114688 May 5 21:14 temp2.ibd
-rw-r----- 1 mysql mysql 114688 Apr 21 20:42 temp3.ibd
-rw-r----- 1 mysql mysql 114688 Apr 21 22:13 temp5.ibd
-rw-r----- 1 mysql mysql 114688 May 6 20:52 temp.ibd
-rw-r----- 1 mysql mysql 2277 Apr 22 18:38 test_451.sdi
-rw-r----- 1 mysql mysql 35 May 6 17:14 test.CSM
-rw-r----- 1 mysql mysql 12 Apr 22 18:38 test.CSV
[root@mysql-master /data/mysql/mysql/data/course]# mysql -u root -p
mysql> show plugins;
+---------------------------------+----------+--------------------+---------+---------+
| Name | Status | Type | Library | License |
+---------------------------------+----------+--------------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| caching_sha2_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha2_cache_cleaner | ACTIVE | AUDIT | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CACHED_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SESSION_TEMP_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| TempTable | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| mysqlx | ACTIVE | DAEMON | NULL | GPL |
| mysqlx_cache_cleaner | ACTIVE | AUDIT | NULL | GPL |
| ngram | ACTIVE | FTPARSER | NULL | GPL |
+---------------------------------+----------+--------------------+---------+---------+
44 rows in set (0.01 sec)
mysql> use course;
Database changed
mysql> select * from employees;
+----+-------+-------+------------+------------+----------+----------+
| id | fname | lname | hired | separated | job_code | store_id |
+----+-------+-------+------------+------------+----------+----------+
| 1 | a | a | 2020-05-07 | 2020-05-07 | 1 | 1 |
| 2 | b | b | 2020-05-07 | 2020-05-07 | 1 | 6 |
+----+-------+-------+------------+------------+----------+----------+
2 rows in set (0.01 sec)
mysql> explain select * from employees where store_id=6;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | employees | p1 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
mysql> select * from employees partition(p0);
+----+-------+-------+------------+------------+----------+----------+
| id | fname | lname | hired | separated | job_code | store_id |
+----+-------+-------+------------+------------+----------+----------+
| 1 | a | a | 2020-05-07 | 2020-05-07 | 1 | 1 |
+----+-------+-------+------------+------------+----------+----------+
1 row in set (0.00 sec)
mysql> select * from employees;
+----+-------+-------+------------+------------+----------+----------+
| id | fname | lname | hired | separated | job_code | store_id |
+----+-------+-------+------------+------------+----------+----------+
| 1 | a | a | 2020-05-07 | 2020-05-07 | 1 | 1 |
| 2 | b | b | 2020-05-07 | 2020-05-07 | 1 | 6 |
+----+-------+-------+------------+------------+----------+----------+
2 rows in set (0.00 sec)
mysql> show create table employees;
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employees | CREATE TABLE `employees` (
`id` int(11) NOT NULL,
`fname` varchar(30) DEFAULT NULL,
`lname` varchar(30) DEFAULT NULL,
`hired` date NOT NULL DEFAULT '1970-01-01',
`separated` date NOT NULL DEFAULT '9999-12-31',
`job_code` int(11) NOT NULL,
`store_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (`store_id`)
(PARTITION p0 VALUES LESS THAN (6) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (11) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (16) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (21) ENGINE = InnoDB) */ |
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> select * from employees partition(p0);
+----+-------+-------+------------+------------+----------+----------+
| id | fname | lname | hired | separated | job_code | store_id |
+----+-------+-------+------------+------------+----------+----------+
| 1 | a | a | 2020-05-07 | 2020-05-07 | 1 | 1 |
+----+-------+-------+------------+------------+----------+----------+
1 row in set (0.00 sec)
mysql> select * from employees partition(p1);
+----+-------+-------+------------+------------+----------+----------+
| id | fname | lname | hired | separated | job_code | store_id |
+----+-------+-------+------------+------------+----------+----------+
| 2 | b | b | 2020-05-07 | 2020-05-07 | 1 | 6 |
+----+-------+-------+------------+------------+----------+----------+
1 row in set (0.00 sec)
mysql> select * from employees partition(p2);
Empty set (0.00 sec)
mysql> select * from employees partition(p3);
Empty set (0.00 sec)
mysql> insert into employees values(3,'a','a',now(),now(),1,12);
Query OK, 1 row affected, 2 warnings (0.02 sec)
mysql> select * from employees partition(p2);
+----+-------+-------+------------+------------+----------+----------+
| id | fname | lname | hired | separated | job_code | store_id |
+----+-------+-------+------------+------------+----------+----------+
| 3 | a | a | 2020-05-07 | 2020-05-07 | 1 | 12 |
+----+-------+-------+------------+------------+----------+----------+
1 row in set (0.00 sec)
mysql> explain update employees set fname='a' where store_id=4;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | UPDATE | employees | p0 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain delete from employees where store_id=4;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | DELETE | employees | p0 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
浙公网安备 33010602011771号