MySQL partition Knowledge Point (iii)

Objective:

MySQL partition after each partition into a separate file, although logically or a table actually has been divided into a number of separate tables,

From INFORMATION_SCHEMA. Innodb_sys_tables system table can see that each partition has a separate table_id;

　　

Since INNODB data and indexes are stored in the ". IBD" File (from the Innodb_sys_indexes system table, each index is corresponding to its respective partition (primary key and unique).

Therefore, the indexes of partitioned tables are also stored separately with each partition.

First, the partition index

Index mode:
　　Reduced performance in turn
1. Primary KEY Partitioning

Primary key partitioning that is, the field is the primary key and also the partition field, the best performance

2. Partial primary key + partition index

Use the partial field in the combined primary key as the partition field, and Jianjian the partition character index

3. Partitioned indexes

No primary key, only partition field and partition Word Jianjian index

4. partition + partition field not indexed

Only partitions are built, but the partition fields are not indexed

Summary of references:

Because each table requires a primary key to reduce the number of locks, the primary key needs to address global uniqueness and can be inserted and updated without having to scan all partitions, resulting in a relationship between the primary key and the partition column;

So the best partitioning effect is to use the primary key as the partition field followed by the partial primary key as the partition field and the index to create the partition field, other partitioning methods are recommended not to take.

MySQL partition Knowledge Point (iii)