High Performance MySQL 7th Chapter MySQL advanced features partition table

Partition table:

A partitioned table is a separate logical table that is implemented by multiple physical tables.

The way MySQL implements partitioned tables is to encapsulate the underlying tables. This means that there is no global index, and the index is built on each of the underlying tables (unlike Oracle).

There are several scenarios for using partitioned tables:

• The amount of data is very large and cannot be put into memory.
• Only part of the data is hot-spot data, and other data is historical data.

Limit:

• A table can only have 1024 partitions, the author is stable under 100, and too many will have performance problems.
• The partition table cannot use FOREIGN key constraints.
• The PARTITION by range partition expression must be an integer or an expression that returns an integer.
• The partitioned by RANGE columns partition can accept reshape, date, and character types as partition parameters.
• All partitions must use the same storage engine.

Problems that can occur:

• If you use partition by RANGE, all null values on the partition expression (which is itself null or when using an illegal value) are placed on the first partition. When you do a query, you will not only query where the partition is located, but also query the partition where the first null is located. If the first partition is large, there will be a performance risk. Workaround, reduce the amount of data in the first partition. Or make an empty partition for the first partition.

Attach the partition by range example:

2. CREATE TABLE Employees (
4. ID Intnotnull,
6. FName VARCHAR (30),
8. LName VARCHAR (30),
10. Hired Datenotnulldefault '1970-01-01 ',
12. Separated datenotnulldefault '9999-12-31 ',
14. RANGE Partitioning
16. 3001
18. Job_code Intnotnull,
20. store_id Intnotnull
22. )
24. PARTITION by RANGE (store_id) (
26. PARTITION p0 VALUES less THAN (6),
28. PARTITION p1 VALUES less THAN (11),
30. PARTITION P2 VALUES less THAN (16),
32. PARTITION P3 VALUES less THAN (21)
34. )

• The partition column and index column do not match. For example, a partition is established on column A, an index is established on column B, and the b= is used. When retrieving, if there is no condition for a to limit the partitions to be scanned, the B-index will be looked up on each partition. If the partition is more, it is also a performance risk.

EXPLAIN partitions See which partitions are used

2. CREATE TABLE trb1 (id INT, name VARCHAR (), purchased DATE)
4. PARTITION by RANGE (ID)
6. (
8. PARTITION p0 VALUES Less THAN (3),
10. PARTITION p1 VALUES less THAN (7),
12. PARTITION P2 VALUES less THAN (9),
14. PARTITION P3 VALUES less THAN (11)
16. );
18. INSERT Into trb1 VALUES
20. (1, 'desk organiser ', ' 2003-10-15 '),
22. (2, 'CD player ', ' 1993-11-05 '),
24. Obtaining information about partitions
26. 3044
28. (3, 'TV set ', ' 1996-03-10 '),
30. (4, 'bookcase ', ' 1982-01-10 '),
32. (5, 'exercise bike ', ' 2004-05-09 '),
34. (6, 'sofa ', ' 1987-06-05 '),
36. (7, 'popcorn maker ', ' 2001-11-22 '),
38. (8, 'aquarium ', ' 1992-08-04 '),
40. (9, 'study desk ', ' 1984-09-16 '),
42. (Ten, 'lava lamp ', ' 1998-12-25 ');
44. mysql> EXPLAIN Partitions SELECT * from Trb1\g
46. 1. Row ***************************
48. Id:1
50. Select_type:simple
52. Table : Trb1
54. Partitions:p0,p1,p2,p3
56. Type:all
58. Possible_keys:null
60. Key : NULL
62. Key_len:null
64. Ref:null
66. rows : Ten
68. Extra:using Filesort
70. mysql> EXPLAIN Partitions SELECT * FROM TRB1 WHERE ID < 5\g
72. 1. Row ***************************
74. Id:1
76. Select_type:simple
78. Table : Trb1
80. Partitions:p0,p1
82. Type:all
84. Possible_keys:null
86. Key : NULL
88. Key_len:null
90. Ref:null
92. rows : Ten
94. Extra:usingwhere

High Performance MySQL 7th Chapter MySQL advanced features partition table