os: centos 7.4.1708
db: mysql 8.0.20
版本
# cat /etc/centos-release
CentOS Linux release 7.4.1708 (Core)
#
#
# yum list installed |grep -i mysql80
mysql-community-client.x86_64 8.0.20-1.el7 @mysql80-community
mysql-community-common.x86_64 8.0.20-1.el7 @mysql80-community
mysql-community-devel.x86_64 8.0.20-1.el7 @mysql80-community
mysql-community-libs.x86_64 8.0.20-1.el7 @mysql80-community
mysql-community-libs-compat.x86_64 8.0.20-1.el7 @mysql80-community
mysql-community-server.x86_64 8.0.20-1.el7 @mysql80-community
mysql-community-test.x86_64 8.0.20-1.el7 @mysql80-community
mysql80-community-release.noarch el7-3 installed
# mysql -e "select version();"
+-----------+
| version() |
+-----------+
| 8.0.20 |
+-----------+
partition by range
# mysql
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> CREATE TABLE part_range (
id INT NOT NULL,
fname VARCHAR(32),
lname VARCHAR(32),
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
)
ENGINE=INNODB
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (1000000),
PARTITION p1 VALUES LESS THAN (2000000),
PARTITION p2 VALUES LESS THAN (3000000),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
alter table part_range add index idx_part_range_fname (fname);
mysql>
mysql> select * from information_schema.tables where table_schema='test' and table_name like 'part_range%';
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------------+
| def | test | part_range | BASE TABLE | InnoDB | 10 | Dynamic | 0 | 0 | 65536 | 0 | 0 | 0 | NULL | 2020-07-31 03:52:12 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | partitioned | |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------------+
1 row in set (0.03 sec)
mysql> select * from information_schema.partitions where table_schema='test' and table_name like 'part_range%';
+---------------+--------------+------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+------------+----------------+-------------+-----------------+--------------+-----------+---------------------+-------------+------------+----------+-------------------+-----------+-----------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | PARTITION_ORDINAL_POSITION | SUBPARTITION_ORDINAL_POSITION | PARTITION_METHOD | SUBPARTITION_METHOD | PARTITION_EXPRESSION | SUBPARTITION_EXPRESSION | PARTITION_DESCRIPTION | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | CREATE_TIME | UPDATE_TIME | CHECK_TIME | CHECKSUM | PARTITION_COMMENT | NODEGROUP | TABLESPACE_NAME |
+---------------+--------------+------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+------------+----------------+-------------+-----------------+--------------+-----------+---------------------+-------------+------------+----------+-------------------+-----------+-----------------+
| def | test | part_range | p0 | NULL | 1 | NULL | RANGE | NULL | `store_id` | NULL | 1000000 | 0 | 0 | 16384 | 0 | 0 | 0 | 2020-07-31 03:52:12 | NULL | NULL | NULL | | default | NULL |
| def | test | part_range | p1 | NULL | 2 | NULL | RANGE | NULL | `store_id` | NULL | 2000000 | 0 | 0 | 16384 | 0 | 0 | 0 | 2020-07-31 03:52:12 | NULL | NULL | NULL | | default | NULL |
| def | test | part_range | p2 | NULL | 3 | NULL | RANGE | NULL | `store_id` | NULL | 3000000 | 0 | 0 | 16384 | 0 | 0 | 0 | 2020-07-31 03:52:12 | NULL | NULL | NULL | | default | NULL |
| def | test | part_range | p3 | NULL | 4 | NULL | RANGE | NULL | `store_id` | NULL | MAXVALUE | 0 | 0 | 16384 | 0 | 0 | 0 | 2020-07-31 03:52:12 | NULL | NULL | NULL | | default | NULL |
+---------------+--------------+------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+------------+----------------+-------------+-----------------+--------------+-----------+---------------------+-------------+------------+----------+-------------------+-----------+-----------------+
4 rows in set (0.01 sec)
mysql> select * from information_schema.innodb_tables where name like '%part_range%';
+----------+----------------------+------+--------+-------+------------+---------------+------------+--------------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+----------------------+------+--------+-------+------------+---------------+------------+--------------+
| 1207 | test/part_range#p#p0 | 33 | 10 | 59 | Dynamic | 0 | Single | 0 |
| 1208 | test/part_range#p#p1 | 33 | 10 | 60 | Dynamic | 0 | Single | 0 |
| 1209 | test/part_range#p#p2 | 33 | 10 | 61 | Dynamic | 0 | Single | 0 |
| 1210 | test/part_range#p#p3 | 33 | 10 | 62 | Dynamic | 0 | Single | 0 |
+----------+----------------------+------+--------+-------+------------+---------------+------------+--------------+
4 rows in set (0.01 sec)
mysql> show create table part_range;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| part_range | CREATE TABLE `part_range` (
`id` int NOT NULL,
`fname` varchar(32) DEFAULT NULL,
`lname` varchar(32) DEFAULT NULL,
`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,
KEY `idx_part_range_fname` (`fname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (`store_id`)
(PARTITION p0 VALUES LESS THAN (1000000) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (2000000) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (3000000) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
/!../ 是一种特殊的注释，其他的数据库产品不会执行。mysql特殊处理，会选择性的执行。可以认为是：预编译中的条件编译。
特别注意 50100，它表示5.01.00 版本或者更高的版本，才执行。
# ls -l /var/lib/mysql/test/part_range*
-rw-r----- 1 mysql mysql 131072 Jul 31 03:52 /var/lib/mysql/test/part_range#p#p0.ibd
-rw-r----- 1 mysql mysql 131072 Jul 31 03:52 /var/lib/mysql/test/part_range#p#p1.ibd
-rw-r----- 1 mysql mysql 131072 Jul 31 03:52 /var/lib/mysql/test/part_range#p#p2.ibd
-rw-r----- 1 mysql mysql 131072 Jul 31 03:52 /var/lib/mysql/test/part_range#p#p3.ibd
mysql> call generate_seed(1,4000000);
mysql> insert into part_range(id,fname,lname,hired,separated,job_code,store_id)
select @a := @a +1, md5(@a),md5(@a),date_sub(current_date(), interval mod(@a,365) day),date_sub(current_date(), interval mod(@a,365) day),@a,@a
from generate_seed_tmp,(select @a:=0) as seq
;
mysql> select * from part_range limit 10;
+----+----------------------------------+----------------------------------+------------+------------+----------+----------+
| id | fname | lname | hired | separated | job_code | store_id |
+----+----------------------------------+----------------------------------+------------+------------+----------+----------+
| 1 | c4ca4238a0b923820dcc509a6f75849b | c4ca4238a0b923820dcc509a6f75849b | 2020-07-30 | 2020-07-30 | 1 | 1 |
| 2 | c81e728d9d4c2f636f067f89cc14862c | c81e728d9d4c2f636f067f89cc14862c | 2020-07-29 | 2020-07-29 | 2 | 2 |
| 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 | 2020-07-28 | 2020-07-28 | 3 | 3 |
| 4 | a87ff679a2f3e71d9181a67b7542122c | a87ff679a2f3e71d9181a67b7542122c | 2020-07-27 | 2020-07-27 | 4 | 4 |
| 5 | e4da3b7fbbce2345d7772b0674a318d5 | e4da3b7fbbce2345d7772b0674a318d5 | 2020-07-26 | 2020-07-26 | 5 | 5 |
| 6 | 1679091c5a880faf6fb5e6087eb1b2dc | 1679091c5a880faf6fb5e6087eb1b2dc | 2020-07-25 | 2020-07-25 | 6 | 6 |
| 7 | 8f14e45fceea167a5a36dedd4bea2543 | 8f14e45fceea167a5a36dedd4bea2543 | 2020-07-24 | 2020-07-24 | 7 | 7 |
| 8 | c9f0f895fb98ab9159f51fd0297e236d | c9f0f895fb98ab9159f51fd0297e236d | 2020-07-23 | 2020-07-23 | 8 | 8 |
| 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26 | 45c48cce2e2d7fbdea1afc51c7c6ad26 | 2020-07-22 | 2020-07-22 | 9 | 9 |
| 10 | d3d9446802a44259755d38e6d163e820 | d3d9446802a44259755d38e6d163e820 | 2020-07-21 | 2020-07-21 | 10 | 10 |
+----+----------------------------------+----------------------------------+------------+------------+----------+----------+
10 rows in set (0.25 sec)
mysql> explain select * from part_range where id=10000;
+----+-------------+------------+-------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+-------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | part_range | p0,p1,p2,p3 | ALL | NULL | NULL | NULL | NULL | 3971640 | 10.00 | Using where |
+----+-------------+------------+-------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from part_range where store_id=10000;
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | part_range | p0 | ALL | NULL | NULL | NULL | NULL | 992910 | 10.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
在 id 上创建个索引
mysql> create index idx_part_range_id on part_range(id);
mysql> explain select * from part_range where id=10000;
+----+-------------+------------+-------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+-------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | part_range | p0,p1,p2,p3 | ref | idx_part_range_id | idx_part_range_id | 4 | const | 1 | 100.00 | NULL |
+----+-------------+------------+-------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
参考:
https://dev.mysql.com/doc/refman/8.0/en/partitioning.html
https://dev.mysql.com/doc/refman/8.0/en/partitioning-types.html
https://dev.mysql.com/doc/refman/8.0/en/create-table.html
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
