13. mysql partition RANGE & LIST, mysqlrange
1. range partition creation instance: create table emp (id int not null primary key auto_increment, name char (25) not null default '') partition by range (id) (define the field id as the PARTITION field PARTITION P0 values less than (100), and the IDS below 99 are all classified into p0 PARTITION P1 values less than (200 ), 100-199 PARTITION P2 values less than (300) 200-299); PS: Because 0 is calculated by default, P0 can actually store 0-99 or-Number of PS :: either it does not contain the primary key. To include the primary key, the partition field must also be the primary key PS: mysql5.5 or above. Date partitioning is supported. What is the role of ^ _ ^, you 2. list PARTITION creation instance: create table LIST_EMP (tid int not null PRIMARY key auto_increment, tname char (20) partition by list (tid) (PARTITION P0 values in (1, 3, 5, 7, 9), PARTITION P1 values in (, 10); PS: it is not difficult to see from the preceding two partitions that the range partition specifies a RANGE, list partitions specify a list. In List, only the specified value can be saved! PS: I personally think more List partitions can be used in classification tests: insert into list_emp values (null, 'php'); insert into list_emp values (null, 'php '); insert into list_emp values (null, 'php '); insert into list_emp values (null, 'php '); insert into list_emp values (null, 'php '); ERROR 1526 (HY000): Table has no partition for value 11 PS: 11th data records, because no partition Table contains it
What if the range partition method in mysql conflicts with the primary key? What is the impact of having no primary key in innodb? Innodb does not have an index. By default, the clustered index Rowid is hidden.
Mainly depends on business logic
People's point of view: removing the primary key will affect the innodb. A hidden index is used. consider increasing the partition size as much as possible to keep the write order. This will also improve the efficiency.
How does mysql partition existing tables? I am also looking for methods. mysql has partitioned tables since 5.1.