MySQL Sub-table strategy

When MySQL single-table data is huge, query performance can be poor, and it is often encountered that storing log-related data produces a large amount of data per day.

Here are three ideas for splitting a single table into multiple table storage:

One, fixed n tables, id modulo storage

Create n tables in advance, and records are stored in the appropriate table by ID modulo.

Pros : Simple rough

Disadvantages:

By ID mode, the number of pre-created tables is not easy to expand and change.

Search by ID is convenient, but query by time is more troublesome.

Migration can affect performance when the volume of data is large.

Example: Table test split into 100 tables

INSERT into test{$i} SELECT * from Test WHERE id%100={$i}

Second, sorted by date

Insert INTO Test_yyyymm (Field1,field2 ...) SELECT field1,field2 .... From Test WHERE filed3 >= "YYYYMMDD" and Filed3 <= "yyyymmdd+1";

Advantages: Sorting statistics by good time.

disadvantage: The specific ID query is inconvenient, the migration can affect the performance when the data volume is large.

Third, rename rename mode

First copy the structure of the test table to create a temporary table:
CREATE TABLE test_tmp like test

RENAME TABLE test to test_yyyymm,test_tmp to test;

The table is locked during rename, so don't worry about rename data loss!

Note that monthly storage is performed on the 1th number per month.

Of course, you can change the way of splitting, free to play!

Pros: Fast, no data import and export required.

MySQL Sub-table strategy