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 hash

# 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_hash ( id INT NOT NULL AUTO_INCREMENT, amount DECIMAL(7,2), tr_date DATE, PRIMARY KEY pk_part_hash (id,tr_date) ) ENGINE=INNODB PARTITION BY HASH( MONTH(tr_date) ) PARTITIONS 6; alter table part_hash add index idx_month_tr_date ((month(tr_date))); mysql> mysql> select * from information_schema.tables where table_schema='test' and table_name like 'part_hash%'; +---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------------+ | 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_hash | BASE TABLE | InnoDB | 10 | Dynamic | 51 | 1927 | 98304 | 0 | 98304 | 0 | 52 | 2020-07-01 15:30:40 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | partitioned | | +---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------------+ 1 row in set (0.00 sec) mysql> select * from information_schema.partitions where table_schema='test' and table_name like 'part_hash%'; +---------------+--------------+------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+------------+----------------+-------------+-----------------+--------------+-----------+---------------------+-------------+------------+----------+-------------------+-----------+-----------------+ | 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_hash | p0 | NULL | 1 | NULL | HASH | NULL | month(`tr_date`) | NULL | NULL | 30 | 546 | 16384 | 0 | 16384 | 0 | 2020-07-01 15:30:40 | NULL | NULL | NULL | | default | NULL | | def | test | part_hash | p1 | NULL | 2 | NULL | HASH | NULL | month(`tr_date`) | NULL | NULL | 0 | 0 | 16384 | 0 | 16384 | 0 | 2020-07-01 15:30:40 | NULL | NULL | NULL | | default | NULL | | def | test | part_hash | p2 | NULL | 3 | NULL | HASH | NULL | month(`tr_date`) | NULL | NULL | 0 | 0 | 16384 | 0 | 16384 | 0 | 2020-07-01 15:30:40 | NULL | NULL | NULL | | default | NULL | | def | test | part_hash | p3 | NULL | 4 | NULL | HASH | NULL | month(`tr_date`) | NULL | NULL | 0 | 0 | 16384 | 0 | 16384 | 0 | 2020-07-01 15:30:40 | NULL | NULL | NULL | | default | NULL | | def | test | part_hash | p4 | NULL | 5 | NULL | HASH | NULL | month(`tr_date`) | NULL | NULL | 0 | 0 | 16384 | 0 | 16384 | 0 | 2020-07-01 15:30:40 | NULL | NULL | NULL | | default | NULL | | def | test | part_hash | p5 | NULL | 6 | NULL | HASH | NULL | month(`tr_date`) | NULL | NULL | 21 | 780 | 16384 | 0 | 16384 | 0 | 2020-07-01 15:30:40 | NULL | NULL | NULL | | default | NULL | +---------------+--------------+------------+----------------+-------------------+----------------------------+-------------------------------+------------------+---------------------+----------------------+-------------------------+-----------------------+------------+----------------+-------------+-----------------+--------------+-----------+---------------------+-------------+------------+----------+-------------------+-----------+-----------------+ 6 rows in set (0.00 sec) mysql> select * from information_schema.innodb_tables where name like '%part_hash%'; +----------+---------------------+------+--------+-------+------------+---------------+------------+--------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | +----------+---------------------+------+--------+-------+------------+---------------+------------+--------------+ | 1190 | test/part_hash#p#p0 | 33 | 6 | 42 | Dynamic | 0 | Single | 0 | | 1191 | test/part_hash#p#p1 | 33 | 6 | 43 | Dynamic | 0 | Single | 0 | | 1192 | test/part_hash#p#p2 | 33 | 6 | 44 | Dynamic | 0 | Single | 0 | | 1193 | test/part_hash#p#p3 | 33 | 6 | 45 | Dynamic | 0 | Single | 0 | | 1194 | test/part_hash#p#p4 | 33 | 6 | 46 | Dynamic | 0 | Single | 0 | | 1195 | test/part_hash#p#p5 | 33 | 6 | 47 | Dynamic | 0 | Single | 0 | +----------+---------------------+------+--------+-------+------------+---------------+------------+--------------+ 6 rows in set (0.01 sec) mysql> show create table part_hash; +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | part_hash | CREATE TABLE `part_hash` ( `id` int NOT NULL AUTO_INCREMENT, `amount` decimal(7,2) DEFAULT NULL, `tr_date` date NOT NULL, PRIMARY KEY (`id`,`tr_date`), KEY `idx_month_tr_date` ((month(`tr_date`))) ) ENGINE=InnoDB AUTO_INCREMENT=52 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY HASH (month(`tr_date`)) PARTITIONS 6 */ | +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

/!../ 是一种特殊的注释，其他的数据库产品不会执行。mysql特殊处理，会选择性的执行。可以认为是：预编译中的条件编译。

特别注意 50100，它表示5.01.00 版本或者更高的版本，才执行。

# ls -l /var/lib/mysql/test total 786432 -rw-r----- 1 mysql mysql 131072 Jul 1 15:30 part_hash#p#p0.ibd -rw-r----- 1 mysql mysql 131072 Jul 1 15:30 part_hash#p#p1.ibd -rw-r----- 1 mysql mysql 131072 Jul 1 15:30 part_hash#p#p2.ibd -rw-r----- 1 mysql mysql 131072 Jul 1 15:30 part_hash#p#p3.ibd -rw-r----- 1 mysql mysql 131072 Jul 1 15:30 part_hash#p#p4.ibd -rw-r----- 1 mysql mysql 131072 Jul 1 15:30 part_hash#p#p5.ibd mysql> call generate_seed(50,100); mysql> insert into part_hash(amount,tr_date) select @a := @a +1, date_sub(current_date(), interval @a day) from generate_seed_tmp,(select @a:=0) as seq ; mysql> select * from part_hash; +----+--------+------------+ | id | amount | tr_date | +----+--------+------------+ | 1 | 1.00 | 2020-06-30 | | 2 | 2.00 | 2020-06-29 | | 3 | 3.00 | 2020-06-28 | | 4 | 4.00 | 2020-06-27 | | 5 | 5.00 | 2020-06-26 | ··· ··· ··· | 47 | 47.00 | 2020-05-15 | | 48 | 48.00 | 2020-05-14 | | 49 | 49.00 | 2020-05-13 | | 50 | 50.00 | 2020-05-12 | | 51 | 51.00 | 2020-05-11 | +----+--------+------------+ 51 rows in set (0.00 sec) mysql> explain select * from part_hash where MONTH(tr_date)=5; +----+-------------+-----------+-------------------+------+-------------------+-------------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+-------------------+------+-------------------+-------------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | part_hash | p0,p1,p2,p3,p4,p5 | ref | idx_month_tr_date | idx_month_tr_date | 5 | const | 21 | 100.00 | NULL | +----+-------------+-----------+-------------------+------+-------------------+-------------------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)

注意看执行计划的 partitions 列为 p0,p1,p2,p3,p4,p5

参考: 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