centos7-mysql-partition

partitions are similar to sub-tables in order to divide a table into non-accessible storage, which reduces the single disk IO footprint and improves database efficiency
There are two main forms of partitioning
Horizontal Partitioning
Partition the rows of a table,
Vertical partitioning
Reduce the width of the table,
To use a partition, you need to first see if it currently supports
Show variables like '%partition% '
Show Yes to use
instance, a table partitioned by range
CREATE table if not exists Gao (ID int primary key auto_increment, name varchar ())
default Charset=utf8 auto_increment=1
partition by range (ID) (
partition P0 values less than (Ten) data directory '/data/gao1 ',
partition P1 values less than MaxValue data directory '/data/gao2 ');
650) this.width=650; "height=" 274 "src=" http://b287.photo.store.qq.com/psb?/81c1194a-ff39-4e50-b1b1-40057b06260f/ 8pfnxn95us05.jrnwv1t.kdrso1ueh8*smvpq5j68ke!/b/db8baaaaaaaa&bo=uqesaqaaaaafeha! "width=" 337 "style=" vertical-align:top;font-family:arial;font-size:14px;white-space:normal; "Alt=" db8baaaaaaaa&bo= uqesaqaaaaafeha! "/>
ALTER TABLE name add partition (partition name values less than (n)) add partition
ALTER TABLE name REORGANIZE partition P1,P2,P3 into (partition P1 values less than), partition P2 values less tha N (20)) Merge partitions
----------------------------------------------------------------------------------------------------------- -
using the list partition
partition by list (ID) is a numeric column
(partition P1 values in (1,3,5)
partition P2 values in (2,4,6)); Each partition table writes an ID value, and if no number is written in the partition table, an error is found, and the value cannot be duplicated.
----------------------------------------------------------------------------------------------------------- ----
partition is different from the table,
MySQL's sub-table is a real sub-table, a table divided into multiple tables, each table is a complete one, has three files, myd data files, myi index files, FRM table structure files
partitioning is a table for multiple storage, or a table, does not change much,
partitions and sub-tables are available at the same time!!!! No conflict
It's all for MySQL. High performance
Divide the table trouble, need to create a separate table, partition is not required, will be created automatically.

This article is from the "11627223" blog, please be sure to keep this source http://11637223.blog.51cto.com/11627223/1911978

centos7-mysql-partition