MySQL Partition introduction

The MySQL standard does not provide much of a physical way of storing data. The SQL language itself is designed to be independent of any data structure or the modal media, tables, rows, or columns it works on. Nonetheless, most advanced database management systems already have methods to store specific data in a physical location in the file system or hardware. In MySQL, the InnoDB storage engine supports the concept of table space for a long time. The MySQL server also has a very early partitioning concept that allows different physical directories to store different databases.

Partitioning is a step closer to this concept, allowing you to divide a table into different zones, depending on your needs, to a large extent across file systems. This way, different parts of a table are stored in different tables in different locations. The rules for user segmentation of data are called partition functions, which can be modulus in MySQL, simple matches based on a set of ranges or value lists, internal hash functions, or a linear hash function. The function is selected based on the type of partition the user specifies and is provided as a parameter value to the user expression, which can be a column value, a merit function for one or more columns, or a value in one or more columns, depending on the type of all partitions.

For RANGE, LIST, and [LINEAR] hash partitions, the value of the partition column is passed to the partition function, returning an integer value representing the partition number of the particular record store. This function must be very few and non-random. It may not contain any queries, but it may also use a valid SQL expression in MySQL, as long as the expression returns a null or an integer value.

for [LINEAR] key,range,columns and list columns partitions, the partition representation consists of one or more columns.

For the [LINEAR] key partition, MySQL supports the partition function.

This article is from the "This person's IT World" blog, be sure to keep this source http://favccxx.blog.51cto.com/2890523/1668858

MySQL Partition introduction