Shulou(Shulou.com)06/01 Report--

Hash partition:

(if it is a non-integer column, you can use a function to convert it to an integer.)

(a function that converts a non-integer to an integer)

Inserting and querying data into the partitioned table uses the same statement:

Range partition:

(in the figure above, if you do not create a p3 partition, data inserts greater than 30000 will report an error.)

List partition:

In practice, log in the log table partition for the user:

You can partition like this:

(of course, the logic converted to time can be put into the application layer to save database performance.)

The way to add partitions is simple:

Alter table customer_login_log add partition (partition p4 values less than (2018))

(zoning can be added automatically at the end of the year)

Delete the partition:

Alter table customer_login_log drop partition p0

(it takes a lot of time and resources to delete it with delete statements in large tables.)

Archiving of expired data:

Create different tables with the same structure:

Swap partition table:

Alter table customer_login_log exchange partition p2 with table arch_customer_login_log

(that is, the data from the p2 partition has been migrated to the archive data table)

The advantage of archiving is that it takes up less space than the data stored in innodb, but it cannot write, so it can only do queries.

Summary:

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.