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

The following content mainly brings you a detailed explanation of the Mysql partition of HASH, the knowledge mentioned here, which is slightly different from books, is summed up by professional and technical personnel in the process of contact with users, and has a certain value of experience sharing. I hope to bring help to the majority of readers.

Hash partitions use primary keys to ensure that data is evenly distributed over a predetermined number partition. In the range or list partition. You must explicitly specify which partition the given data is written to or set a column value to save; in the hash partition. Mysql has prepared it for you. You only need to specify a column value or expression based on the column value to hash and partition the number in which partition table.

CREATE TABLE employees (id INT NOT NULL, fname VARCHAR (30), lname VARCHAR (30), hired DATE NOT NULL DEFAULT '1970-01-01-01, separated DATE NOT NULL DEFAULT' 9999-12-31, job_code INT, store_id INT) PARTITION BY HASH (store_id) PARTITIONS 4

If PARTITIONS is not included, the default is 1 partition

Use date partition

CREATE TABLE employees (id INT NOT NULL, fname VARCHAR (30), lname VARCHAR (30), hired DATE NOT NULL DEFAULT '1970-01-01-01, separated DATE NOT NULL DEFAULT' 9999-12-31, job_code INT, store_id INT) PARTITION BY HASH (YEAR (hired)) PARTITIONS 4

Expr must be a non-constant number, a non-random number, that is, the number is different, but can be determined.

How to determine the partition of a piece of data? Create a table first

CREATE TABLE T1 (col1 INT, col2 CHAR (5), col3 DATE) PARTITION BY HASH (YEAR (col3)) PARTITIONS 4

If you insert a record to T1 with a col3 value of "2005-09-15', then allocate its storage as follows:

MOD (YEAR ('2005-09-01'), 4) = MOD (2005 jade 4) = 11. Linear hash partition

Linear hash partitions use a linear power algorithm of 2

REATE TABLE employees (id INT NOT NULL, fname VARCHAR (30), lname VARCHAR (30), hired DATE NOT NULL DEFAULT '1970-01-01-01, separated DATE NOT NULL DEFAULT' 9999-12-31, job_code INT, store_id INT) PARTITION BY LINEAR HASH (YEAR (hired)) PARTITIONS 4

Given an expression expr, the records in the partition are stored in the linear hash using the partition number n in the Num partition, where n is derived from the following algorithm:

It is found that the number greater than 2 is called the force under the value V; it can be calculated as:

V = POWER (2, CEILING (LOG (2, num)

(suppose the number is 13). Then LOG (2p13) is 3.7004397181411. CEILING (3.7004397181411) is 4, and V = power (2p4), which is 16. )

N = F (column_list) & (V-1).

N > = num:

Set V = V / 2Set N = N & (V-1)

The advantage of linear hash partitions is that adding, dropping, merging, and splitting partitions can be faster and facilitate processing that contains a very large number of data tables (megabytes). The disadvantage is that compared with the distribution obtained by conventional hash partitions, data is less likely to be evenly distributed between partitions.

For the above detailed explanation of the Mysql partition of HASH, if you have more information, you can continue to pay attention to the innovation of our industry. If you need professional solutions, you can contact the pre-sales and after-sales on the official website. I hope this article can bring you some knowledge updates.

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.