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

This article introduces the relevant knowledge of "what are the knowledge points of the MySQL partition table". In the operation of the actual case, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

MySQL partition table

1) Partition type

? RANGE: ranges should be contiguous but not overlapping, defined using the VALUES LESS THAN operator.

? LIST: as in partitioning by RANGE, each partition must be explicitly defined.

? HASH: operates on the column values of the rows to be inserted into the table.

? KEY: similar to HASH, except that only one or more columns to be evaluated are provided and the MySQL server provides hash functions. It applies to all allowed column types.

? Variants on COLUMNS:RANGE and LIST partitions. COLUMNS partitions allow one or more columns to be used in the partitioning key. All of these columns will be considered when placing rows in a partition and determining which partitions will be checked to match the rows in the partition deletion.

-RANGE COLUMNS and LIST COLUMNS partitions support the use of non-integer columns (and other data types listed earlier) to define value ranges or list members.

? LINEAR:MySQL also supports linear hashes, which are different from regular hashes, which use the power algorithm of linear 2, while conventional hashes use modules with hash function values.

2) confirm whether the server supports partitioning

Mysql > show plugins\ G

Name: partition

Status: ACTIVE

Type: STORAGE ENGINE

Library: NULL

License: PROPRIETARY

Disable partition support:

Shell > mysqld-- skip-partition

-the partition plug-in now has the value DISABLED

3) create a partition

(root@localhost) [mydb1] > create table tt (F1 int,f2 varchar (20)) partition by range (F1)

(

Partition tt_p1 values less than (100)

Partition tt_p2 values less than (1000)

Partition tt_p3 values less than (10000)

Partition tt_p4 values less than (maxvalue)

)

4) View table partitions

(root@localhost) [mydb1] > show create table tt

(root@localhost) [mydb1] > show table status like 'tt'\ G

(root@localhost) [mydb1] > select table_name, group_concat (partition_name) pn from information_schema.partitions where table_schema='mydb1' group by table_name

+-+ +

| | table_name | pn |

+-+ +

| | tt | tt_p1,tt_p2,tt_p3,tt_p4 |

+-+ +

(root@localhost) [mydb1] > EXPLAIN PARTITIONS SELECT * FROM tt\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: tt

Partitions: tt_p1,tt_p2,tt_p3,tt_p4

Type: ALL

Possible_keys: NULL

Key: NULL

Key_len: NULL

Ref: NULL

Rows: 1

Filtered: 100.00

Extra: NULL

1 row in set, 2 warnings (0.00 sec)

5) Partition restriction

? Routine

-the maximum number of partitions per table is 8192.

-Space types are not supported.

-temporary tables cannot be partitioned.

-cannot partition log tables.

? Foreign keys and indexes

-Foreign keys are not supported.

-FULLTEXT indexing is not supported.

-No global index: each partition has its own index.

? Subpartitions are only possible in the following cases:

-when partitioning through RANGE and LIST.

-when traveling through LINEAR HASH or LINEAR KEY.

This is the end of the content of "what are the knowledge points of the MySQL partition table". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.