mysql 8.0 分区表之一 hash

悠悠 2023-02-22 03:58 11阅读 0赞

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)

注意看执行计划的 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