Shulou(Shulou.com)05/31 Report--

This article shows you how to create a Key partition table in MySQL, which is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Partitioning by KEY is similar to partitioning by HASH, except for the user-defined expressions used by the HASH partition, while the hash function for the KEY partition is provided by the MySQL server. The MySQL cluster (Cluster) uses the function MD5 () to implement the KEY partition

For tables using other storage engines, the server uses its own internal hash functions, which are based on the same algorithm as PASSWORD ().

Key partition is very similar to Hash partition, except that the Hash function is different. When defining it, you can replace the Hash keyword with Key. Similarly, Key partition also has a linear Key partition method corresponding to linear Key.

Syntax is PARTITION BY LINEAR KEY (column name)

An example of creating a key partition table is as follows:

Mysql > CREATE TABLE `dsf_ data` (

-> `id` bigint (20) NOT NULL AUTO_INCREMENT

-> `SH`varchar (32) DEFAULT NULL COMMENT 'tax number'

-> `KPJH` varchar (32) DEFAULT NULL COMMENT 'invoice machine number'

-> `ZFJH` varchar (32) DEFAULT NULL COMMENT 'main extension'

-> `MONTH` varchar (10) DEFAULT NULL

-> `STATUS` varchar (255) DEFAULT NULL COMMENT 'parsing status ID'

-> `TIME`datetime DEFAULT NULL COMMENT 'CREATE_ time'

-> `TIME`datetime DEFAULT NULL COMMENT 'update time'

-> `DATA` mediumtext COMMENT 'invoice data'

-> PRIMARY KEY (`id`, `SH`)

-> KEY `index_ sh` (`SH`)

->) ENGINE=InnoDB AUTO_INCREMENT=1173560 DEFAULT CHARSET=utf8 PARTITION BY LINEAR KEY (SH) PARTITIONS 8

Query OK, 0 rows affected (0.11 sec)

Note: if there are primary key or unique index columns in the partition field, then all primary key columns and unique index columns must be included, so there must be two primary keys PRIMARY KEY (`id`, `SH`) in the previous step.

Insert data:

Mysql > insert into dsf_data select * from test.fp_data

Query OK, 202632 rows affected, 1 warning (18.96 sec)

Records: 202632 Duplicates: 0 Warnings: 1

Mysql > explain partitions select sh from dsf_data;-A total of 8 partitions were accessed by the full table scan (p0--p7)

+-- +

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |

+-- +

| | 1 | SIMPLE | dsf_data | p0Magol p1, p2, p3, p4, p5, p6, index | index | NULL | index_sh | 98 | NULL | 8 | Using index |

+-- +

1 row in set (0.00 sec)

Mysql > explain partitions select sh from dsf_data where sh='130202568907641';-the value is randomly assigned to the p0 partition

+-+-

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |

+-+-

| | 1 | SIMPLE | dsf_data | p0 | ref | index_sh | index_sh | 98 | const | 1 | Using where; Using index |

+-+-

1 row in set (0.00 sec)

Mysql > explain partitions select sh from dsf_data where sh='440300683797687';-values are randomly assigned to the p4 partition

+-+-

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |

+-+-

| | 1 | SIMPLE | dsf_data | p4 | ref | index_sh | index_sh | 98 | const | 1 | Using where; Using index |

+-+-

1 row in set (0.00 sec)

Mysql > explain partitions select sh from dsf_data where sh='91500107784224861G';-- the value of sh is randomly assigned to the p6 partition

+-+-

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |

+-+-

| | 1 | SIMPLE | dsf_data | p6 | ref | index_sh | index_sh | 98 | const | 452 | Using where; Using index |

+-+-

1 row in set (0.00 sec)

The value is randomly assigned to each partition, indicating that the partition table was created successfully.

The above is how to create Key partition tables in MySQL. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.