Centos7-mysql- partition

Partitioning is similar to subtables, the purpose of which is to divide a table into impassable storage, which reduces the IO footprint of a single disk and improves database efficiency.

There are two main forms of zoning

Horizontal zoning

Partition the rows of the table

Vertical partition

Reduce the width of the table

If you want to use partitions, you need to check whether it is currently supported.

Show variables like'% partition%'

It can be used when yes appears.

Instance, a table partitioned according to range mode

Create table if not exists gao (id int primary key auto_increment, name varchar (30))

Default charset=utf8 auto_increment=1

Partition by range (id) (

Partition p0 values less than (10) data directory'/ data/gao1'

Partition p1 values less than maxvalue data directory'/ data/gao2')

Alter table table name add partition (partition name values less than (n)) adds partition

Alter table table name reorganize partition p1, p2 values less than p3 into (partition p1 values less than (10), partition p2 values less than (20)) merge partition

-

Use list Partition

Partition by list (id) is a numeric column

(partition p1 values in (1pm 3pm 5)

Partition p2 values in; each partition table writes the same id value, if it is not written in the numeric partition table, it will report an error, and there can be only one value that cannot be repeated.

-

The difference between partitions and subtables

Mysql sub-table is a real sub-table, a table is divided into multiple tables, each table is a complete one, all have three files, MYD data file, MYI index file, FRM table structure file

Does a partition store a table in multiple places or in a single table, which will not change?

Partition and sub-table can be used at the same time! No conflict

It's all about improving the performance of mysql.

Sub-table trouble, need to create a separate sub-table, partition is not needed, will be created automatically.