Mysql partition, mysql

Mysql partition, mysql

When a table contains a large amount of data, for example, if a single. myd file reaches 10 Gb, the reading efficiency will inevitably decrease. In this case, you can use the partition function provided by mysql to separate the table data from several tables and query data based on different regions for optimization purposes.

Mysql stores data in different table files according to the specified rules.
It is equivalent to splitting a file into small pieces.
However, the user interface is still one table.


Partition statement

(Range partitioning)
Article table
Create table news (table Name )(
-> Tid int primary key auto_increment,
-> Title char (20) not null default''
->) Engine myisam charset utf8
-> Partition by range (tid) (which field is used for partitioning )(
-> Partition t0 values less than (10), (the first partition ends with 10)
-> Partition t1 values less than (20), (partition fg ends with 20)
-> Partition t2 values less than (MAXVALUE) (end with the largest tid value)
-> );
Set the id of a table to a range to accelerate the query speed to a certain extent!


(Hash partitioning)
1 => bj 2 => nj 3 => sh 4 => sz
Member table
Create table user (table Name )(
-> Uid int primary key auto_increment,
-> Name char (20) not null default''
-> Aid int () not null default''
->) Engine myisam charset utf8
-> Partition by list (aname )(
-> Partition bj (Beijing, you can name it yourself) values in (1 ),
-> Partition nj (Nanjing) values in (2 ),
-> Partition sh (Shanghai) values in (3 ),
-> Partition sz (Shenzhen) values in (4)
-> );
Note that the column value should not be NULL when hash partitions are used, otherwise an error will be reported.

Mysql can not only use the id range partition method, hash partition method, but also use the year () function