Mysql Table Partitioning and performance

The following excerpt from the <mysql Technology Insider InnoDB Storage Engine >

MySQL Table partition:

　　Partitioning functionality is not supported by all storage engines, such as CSV, merge, and so on. The MySQL database supports a partition type of horizontal partitioning (that is, records in a table that are not in the same row are assigned to different physical files), and vertical partitioning is not supported (refers to assigning different columns in the same table to different physical files). In addition, the partition of the MySQL database is the local partition index, where both the data and the index are stored in a partition.

The current MySQL database supports several types of partitions:

Range Partition: Row data is placed into a partition based on the column values of a given contiguous interval. MySQL database 5.5 starts to support the range columns partition.

List partition: Similar to the range partition, except that the list partition is oriented to discrete values. MySQL database 5.5 starts to support the list columns partition.

Hash partition: Partition according to the return value of the user-defined expression, the return value cannot be negative.

Key partition: Partition According to the hash function provided by the MySQL database.

Partitioning and performance:

　　Database applications are divided into two categories: OLTP (online transaction processing), such as blogs, e-commerce, online games, and OLAP (online analytical processing), such as data warehouses, data marts.

For OLAP applications, partitioning can do a good job of improving query performance because most queries for OLAP applications require frequent scanning of a large table, and if you partition, you only need to scan the appropriate sections.

For OLTP applications, it is generally impossible to get 10% of the data in a large table, most of which return several records by index. For a large table, the average B + tree requires two or three disk IO. For example, a table with a data volume of 10 million rows B + Tree height of 3, divides him into 10 districts, 1 million rows of B + Tree height is 2. Queries for key indexes alone have a query cost of 2 IO based on partition design, whereas the original table design requires 2 to 3 IO. If you have a query for another column index, you might need 10 * 2 = 20 io. Partitioned queries are much more time consuming than queries that do not have a partitioned design data table.

Conclusion:

　　Partitioning does not apply to all applications, and you should plan your own partition design according to the actual situation.

Mysql Table Partitioning and performance