MySQL/MariaDB partition

View the create commands for the table

SHOW CREATE TABLE my_table_name

Check to see if the table is partitioned (Create_options field is partitioned or empty)

SHOW TABLE STATUS LIKE'% my_table_name%'

View partition information

# all Information SELECT * FROM INFORMATION_SCHEMA.PARTITIONSWHERE TABLE_SCHEMA = 'my_db_name' # Library name AND TABLE_NAME =' my_table_name' # Table name # Common information SELECT PARTITION_METHOD, PARTITION_NAME, PARTITION_EXPRESSION, PARTITION_DESCRIPTION, TABLE_ROWSFROM INFORMATION_SCHEMA.PARTITIONSWHERE TABLE_SCHEMA = SCHEMA () # Library name AND TABLE_NAME = 'my_table_name'; # Table name

[KEY partition]

Create KEY partitions when there is a primary key. It is recommended that the number of partitions is prime.

Please refer to this article: the murder caused by the KEY partition of MySQL

After testing, under MySQL 5.7.26 and MariaDB 10.4.6, when the number of partitions is even, the number of odd numbered partitions is still 0.

CREATE TABLE my_table_name (id INT NOT NULL PRIMARY KEY, name VARCHAR (20)) PARTITION BY KEY () PARTITIONS 11; # 11 partition

Create a key partition without a primary key

CREATE TABLE my_table_name (name VARCHAR (20)) PARTITION BY KEY (name) PARTITIONS 11; # 11 partition

For partitioning an existing table, it is recommended to create a new partition table and then import the data. You can also modify the table with the ALTER statement:

ALTER TABLE my_table_namePARTITION BY KEY () PARTITIONS 11

[related reading]

MySQL/MariaDB Tips

* walker * *