Partitioning

This document introduces TiDB's implementation of partitioning.

Partitioning types

This section introduces the types of partitioning in TiDB. Currently, TiDB supportsRange partitioning,Range COLUMNS partitioning,List partitioning,List COLUMNS partitioning,Hash partitioning, andKey partitioning.

• Range partitioning, Range COLUMNS partitioning, List partitioning, and List COLUMNS partitioning are used to resolve the performance issues caused by a large number of deletions in the application, and support dropping partitions quickly.
• Hash partitioning and Key partitioning are used to distribute data in scenarios with a large number of writes. Compared with Hash partitioning, Key partitioning supports distributing data of multiple columns and partitioning by non-integer columns.

Range partitioning

When a table is partitioned by Range, each partition contains rows for which the partitioning expression value lies within a given Range. Ranges have to be contiguous but not overlapping. You can define it by usingVALUES LESS THAN.

Assume you need to create a table that contains personnel records as follows:

              

               CREATE
               TABLEemployees ( idINT
               NOT
               NULL, fnameVARCHAR(30), lnameVARCHAR(30), hiredDATE
               NOT
               NULL
               DEFAULT
               '1970-01-01', separatedDATE
               DEFAULT
               '9999-12-31', job_codeINT, store_idINT
               NOT
               NULL);

               
               

You can partition a table by Range in various ways as needed. For example, you can partition it by using thestore_idcolumn:

              

               CREATE
               TABLEemployees ( idINT
               NOT
               NULL, fnameVARCHAR(30), lnameVARCHAR(30), hiredDATE
               NOT
               NULL
               DEFAULT
               '1970-01-01', separatedDATE
               DEFAULT
               '9999-12-31', job_codeINT, store_idINT
               NOT
               NULL)PARTITION
               BY
               RANGE(store_id) (PARTITIONp0VALUESLESS THAN (6),PARTITIONp1VALUESLESS THAN (11),PARTITIONp2VALUESLESS THAN (16),PARTITIONp3VALUESLESS THAN (21));

               
               

In this partition scheme, all rows corresponding to employees whosestore_idis 1 through 5 are stored in thep0partition while all employees whosestore_idis 6 through 10 are stored inp1. Range partitioning requires the partitions to be ordered, from lowest to highest.

If you insert a row of data(72, 'Tom', 'John', '2015-06-25', NULL, NULL, 15), it falls in thep2partition. But if you insert a record whosestore_idis larger than 20, an error is reported because TiDB cannot know which partition this record should be inserted into. In this case, you can useMAXVALUEwhen creating a table:

              

               CREATE
               TABLEemployees ( idINT
               NOT
               NULL, fnameVARCHAR(30), lnameVARCHAR(30), hiredDATE
               NOT
               NULL
               DEFAULT
               '1970-01-01', separatedDATE
               DEFAULT
               '9999-12-31', job_codeINT, store_idINT
               NOT
               NULL)PARTITION
               BY
               RANGE(store_id) (PARTITIONp0VALUESLESS THAN (6),PARTITIONp1VALUESLESS THAN (11),PARTITIONp2VALUESLESS THAN (16),PARTITIONp3VALUESLESS THAN MAXVALUE );

               
               

MAXVALUE代表了我的一个整数值s larger than all other integer values. Now, all records whosestore_idis equal to or larger than 16 (the highest value defined) are stored in thep3partition.

You can also partition a table by employees' job codes, which are the values of thejob_codecolumn. Assume that two-digit job codes stand for regular employees, three-digit codes stand for office and customer support personnel, and four-digit codes stand for managerial personnel. Then you can create a partitioned table like this:

              

               CREATE
               TABLEemployees ( idINT
               NOT
               NULL, fnameVARCHAR(30), lnameVARCHAR(30), hiredDATE
               NOT
               NULL
               DEFAULT
               '1970-01-01', separatedDATE
               DEFAULT
               '9999-12-31', job_codeINT, store_idINT
               NOT
               NULL)PARTITION
               BY
               RANGE(job_code) (PARTITIONp0VALUESLESS THAN (100),PARTITIONp1VALUESLESS THAN (1000),PARTITIONp2VALUESLESS THAN (10000));

               
               

In this example, all rows relating to regular employees are stored in thep0partition, all office and customer support personnel in thep1partition, and all managerial personnel in thep2partition.

Besides splitting up the table bystore_id, you can also partition a table by dates. For example, you can partition by employees' separation year:

              

               CREATE
               TABLEemployees ( idINT
               NOT
               NULL, fnameVARCHAR(30), lnameVARCHAR(30), hiredDATE
               NOT
               NULL
               DEFAULT
               '1970-01-01', separatedDATE
               DEFAULT
               '9999-12-31', job_codeINT, store_idINT)PARTITION
               BY
               RANGE(YEAR(separated) ) (PARTITIONp0VALUESLESS THAN (1991),PARTITIONp1VALUESLESS THAN (1996),PARTITIONp2VALUESLESS THAN (2001),PARTITIONp3VALUESLESS THAN MAXVALUE );

               
               

In Range partitioning, you can partition based on the values of thetimestampcolumn and use theunix_timestamp()function, for example:

              

               CREATE
               TABLEquarterly_report_status ( report_idINT
               NOT
               NULL, report_statusVARCHAR(20)NOT
               NULL, report_updatedTIMESTAMP
               NOT
               NULL
               DEFAULT
               CURRENT_TIMESTAMP
               ON
               UPDATE
               CURRENT_TIMESTAMP)PARTITION
               BY
               RANGE( UNIX_TIMESTAMP(report_updated) ) (PARTITIONp0VALUESLESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),PARTITIONp1VALUESLESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),PARTITIONp2VALUESLESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),PARTITIONp3VALUESLESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),PARTITIONp4VALUESLESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),PARTITIONp5VALUESLESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),PARTITIONp6VALUESLESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),PARTITIONp7VALUESLESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),PARTITIONp8VALUESLESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),PARTITIONp9VALUESLESS THAN (MAXVALUE) );

               
               

It is not allowed to use any other partitioning expression that contains the timestamp column.

Range partitioning is particularly useful when one or more of the following conditions are satisfied:

• You want to delete the old data. If you use theemployeestable in the previous example, you can delete all records of employees who left this company before the year 1991 by simply usingALTER TABLE employees DROP PARTITION p0;. It is faster than executing theDELETE FROM employees WHERE YEAR(separated) <= 1990;operation.
• You want to use a column that contains time or date values, or containing values arising from some other series.
• You need to frequently run queries on the columns used for partitioning. For example, when executing a query likeEXPLAIN SELECT COUNT(*) FROM employees WHERE separated BETWEEN '2000-01-01' AND '2000-12-31' GROUP BY store_id;, TiDB can quickly know that only the data in thep2partition needs to be scanned, because the other partitions do not match theWHEREcondition.

Range COLUMNS partitioning

Range COLUMNS partitioning is a variant of Range partitioning. You can use one or more columns as partitioning keys. The data types of partition columns can be integer, string (CHARorVARCHAR),DATE, andDATETIME. Any expressions, such as non-COLUMNS partitioning, are not supported.

Suppose that you want to partition by name, and drop old and invalid data, then you can create a table as follows:

              

               CREATE
               TABLEt ( valid_until datetime, namevarchar(255)CHARACTER
               SETascii, notes text )PARTITION
               BY
               RANGECOLUMNS(name, valid_until) (PARTITION`p2022-g`VALUESLESS THAN ('G','2023-01-01 00:00:00'),PARTITION`p2023-g`VALUESLESS THAN ('G','2024-01-01 00:00:00'),PARTITION`p2022-m`VALUESLESS THAN ('M','2023-01-01 00:00:00'),PARTITION`p2023-m`VALUESLESS THAN ('M','2024-01-01 00:00:00'),PARTITION`p2022-s`VALUESLESS THAN ('S','2023-01-01 00:00:00'),PARTITION`p2023-s`VALUESLESS THAN ('S','2024-01-01 00:00:00'))

               
               

The preceding SQL statement will partition the data by year and by name in the ranges[ ('', ''), ('G', '2023-01-01 00:00:00') ),[ ('G', '2023-01-01 00:00:00'), ('G', '2024-01-01 00:00:00') ),[ ('G', '2024-01-01 00:00:00'), ('M', '2023-01-01 00:00:00') ),[ ('M', '2023-01-01 00:00:00'), ('M', '2024-01-01 00:00:00') ),[ ('M', '2024-01-01 00:00:00'), ('S', '2023-01-01 00:00:00') ), and[(“S”,“2023-01-01就是”),(' S ', ' 2024-01-0100:00:00') ). It allows you to easily drop invalid data while still benefit from partition pruning on bothnameandvalid_untilcolumns. In this example,[,)indicates a left-closed, right-open range. For example,[ ('G', '2023-01-01 00:00:00'), ('G', '2024-01-01 00:00:00') )indicates a range of data whose name is'G', the year contains2023-01-01 00:00:00and is greater than2023-01-01 00:00:00but less than2024-01-01 00:00:00. It does not include(G, 2024-01-01 00:00:00).

Range INTERVAL partitioning

Range INTERVAL partitioning is an extension of Range partitioning, which allows you to create partitions of a specified interval easily. Starting from v6.3.0, INTERVAL partitioning is introduced in TiDB as syntactic sugar.

The syntax is as follows:

              

               PARTITION
               BY
               RANGE[COLUMNS] (<partitioning expression>)INTERVAL(<
               intervalexpression>)FIRST
               PARTITIONLESS THAN (<expression>)LAST
               PARTITIONLESS THAN (<expression>) [NULL
               PARTITION] [MAXVALUEPARTITION]

               
               

For example:

              

               CREATE
               TABLEemployees ( idintunsignedNOT
               NULL, fnamevarchar(30), lnamevarchar(30), hireddate
               NOT
               NULL
               DEFAULT
               '1970-01-01', separateddate
               DEFAULT
               '9999-12-31', job_codeint, store_idint
               NOT
               NULL)PARTITION
               BY
               RANGE(id)INTERVAL(100)FIRST
               PARTITIONLESS THAN (100)LAST
               PARTITIONLESS THAN (10000) MAXVALUEPARTITION
              

               
               

It creates the following table:

              

               CREATE
               TABLE`employees` ( `id`intunsignedNOT
               NULL, `fname`varchar(30)DEFAULT
               NULL, `lname`varchar(30)DEFAULT
               NULL, `hired`date
               NOT
               NULL
               DEFAULT
               '1970-01-01', `separated`date
               DEFAULT
               '9999-12-31', `job_code`int
               DEFAULT
               NULL, `store_id`int
               NOT
               NULL)PARTITION
               BY
               RANGE(`id`) (PARTITION`P_LT_100`VALUESLESS THAN (100),PARTITION`P_LT_200`VALUESLESS THAN (200), ...PARTITION`P_LT_9900`VALUESLESS THAN (9900),PARTITION`P_LT_10000`VALUESLESS THAN (10000),PARTITION`P_MAXVALUE`VALUESLESS THAN (MAXVALUE))

               
               

Range INTERVAL partitioning also works withRange COLUMNSpartitioning.

For example:

              

               CREATE
               TABLEmonthly_report_status ( report_idint
               NOT
               NULL, report_statusvarchar(20)NOT
               NULL, report_datedate
               NOT
               NULL)PARTITION
               BY
               RANGECOLUMNS (report_date)INTERVAL(1
               MONTH)FIRST
               PARTITIONLESS THAN ('2000-01-01')LAST
               PARTITIONLESS THAN ('2025-01-01')

               
               

It creates this table:

              

               创建表“monthly_report_status”(“report_id”int(11) NOT NULL, `report_status` varchar(20) NOT NULL, `report_date` date NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin PARTITION BY RANGE COLUMNS(`report_date`) (PARTITION `P_LT_2000-01-01` VALUES LESS THAN ('2000-01-01'), PARTITION `P_LT_2000-02-01` VALUES LESS THAN ('2000-02-01'), ... PARTITION `P_LT_2024-11-01` VALUES LESS THAN ('2024-11-01'), PARTITION `P_LT_2024-12-01` VALUES LESS THAN ('2024-12-01'), PARTITION `P_LT_2025-01-01` VALUES LESS THAN ('2025-01-01'))

               
               

The optional parameterNULL PARTITIONcreates a partition with the definition asPARTITION P_NULL VALUES LESS THAN (), only matching when the partitioning expression evaluates toNULL. SeeHandling of NULL with Range partitioning, which explains thatNULLis considered to be less than any other value.

The optional parameterMAXVALUE PARTITIONcreates the last partition asPARTITION P_MAXVALUE VALUES LESS THAN (MAXVALUE).

ALTER INTERVAL partitioned tables

INTERVAL partitioning also adds simpler syntaxes for adding and dropping partitions.

The following statement changes the first partition. It drops all partitions whose values are less than the given expression, and makes the matched partition the new first partition. It does not affect a NULL PARTITION.

              

               ALTER TABLE table_name FIRST PARTITION LESS THAN (
                )
              

               
               

The following statement changes the last partition, meaning adding more partitions with higher ranges and room for new data. It will add new partitions with the current INTERVAL up to and including the given expression. It does not work if aMAXVALUE PARTITIONexists, because it needs data reorganization.

              

               ALTER TABLE table_name LAST PARTITION LESS THAN (
                )
              

               
               

INTERVAL partitioning details and limitations

• The INTERVAL partitioning feature only involves theCREATE/ALTER TABLEsyntax. There is no change in metadata, so tables created or altered with the new syntax are still MySQL-compatible.
• There is no change in the output format ofSHOW CREATE TABLEto keep MySQL compatibility.
• The newALTERsyntax applies to existing tables conforming to INTERVAL. You do not need to create these tables with theINTERVALsyntax.
• ForRANGE COLUMNS, only integer, date, and datetime column types are supported.

List partitioning

Before creating a List partitioned table, you need to set the value of the session variabletidb_enable_list_partitiontoON.

              

               set@@session.tidb_enable_list_partition=
               ON
              

               
               

Also, make sure thattidb_enable_table_partitionis set toON, which is the default setting.

List partitioning is similar to Range partitioning. Unlike Range partitioning, in List partitioning, the partitioning expression values for all rows in each partition are in a given value set. This value set defined for each partition can have any number of values but cannot have duplicate values. You can use thePARTITION ... VALUES IN (...)clause to define a value set.

Suppose that you want to create a personnel record table. You can create a table as follows:

              

               CREATE
               TABLEemployees ( idINT
               NOT
               NULL, hiredDATE
               NOT
               NULL
               DEFAULT
               '1970-01-01', store_idINT);

               
               

Suppose that there are 20 stores distributed in 4 districts, as shown in the table below:

              

               | Region | Store ID Numbers | | ------- | -------------------- | | North | 1, 2, 3, 4, 5 | | East | 6, 7, 8, 9, 10 | | West | 11, 12, 13, 14, 15 | | Central | 16, 17, 18, 19, 20 |

               
               

If you want to store the personnel data of employees of the same region in the same partition, you can create a List partitioned table based onstore_id:

              

               CREATE
               TABLEemployees ( idINT
               NOT
               NULL, hiredDATE
               NOT
               NULL
               DEFAULT
               '1970-01-01', store_idINT)PARTITION
               BYLIST (store_id) (PARTITIONpNorthVALUES
               IN(1,2,3,4,5),PARTITIONpEastVALUES
               IN(6,7,8,9,10),PARTITIONpWestVALUES
               IN(11,12,13,14,15),PARTITIONpCentralVALUES
               IN(16,17,18,19,20));

               
               

在创建分区上面,你可以easily add or delete records related to a specific region in the table. For example, suppose that all stores in the East region (East) are sold to another company. Then all the row data related to the store employees of this region can be deleted by executingALTER TABLE employees TRUNCATE PARTITION pEast, which is much more efficient than the equivalent statementDELETE FROM employees WHERE store_id IN (6, 7, 8, 9, 10).

You can also executeALTER TABLE employees DROP PARTITION pEastto delete all related rows, but this statement also deletes thepEastpartition from the table definition. In this situation, you must execute theALTER TABLE ... ADD PARTITIONstatement to recover the original partitioning scheme of the table.

Unlike Range partitioning, List partitioning does not have a similarMAXVALUEpartition to store all values that do not belong to other partitions. Instead, all expected values of the partition expression must be included in thePARTITION ... VALUES IN (...)clause. If the value to be inserted in anINSERTstatement does not match the column value set of any partition, the statement fails to execute and an error is reported. See the following example:

              

               test>
               CREATE
               TABLEt ( aINT, bINT)PARTITION
               BYLIST (a) (PARTITIONp0VALUES
               IN(1,2,3),PARTITIONp1VALUES
               IN(4,5,6));查询好了,0
               rowsaffected (0.11sec) test>
               INSERT
               INTOtVALUES(7,7); ERROR1525(HY000):Tablehasno
               partition
               for
               value
               7
              

               
               

To ignore the error type above, you can use theIGNOREkeyword. After using this keyword, if a row contains values that do not match the column value set of any partition, this row will not be inserted. Instead, any row with matched values is inserted, and no error is reported:

              

               test>
               TRUNCATEt; Query OK,1
               rowaffected (0.00sec) test>
               INSERTIGNOREINTOtVALUES(1,1), (7,7), (8,8), (3,3), (5,5); Query OK,3
               rowsaffected,2warnings (0.01sec) Records:5Duplicates:2Warnings:2test>
               select
               *
               fromt;+
               ------+------+
               |a|b|
               +
               ------+------+
               |
               5
               |
               5
               |
               |
               1
               |
               1
               |
               |
               3
               |
               3
               |
               +
               ------+------+
               3
               rows
               in
               set(0.01sec)

               
               

List COLUMNS partitioning

List COLUMNS partitioning is a variant of List partitioning. You can use multiple columns as partition keys. Besides the integer data type, you can also use the columns in the string,DATE, andDATETIMEdata types as partition columns.

Suppose that you want to divide the store employees from the following 12 cities into 4 regions, as shown in the following table:

              

               | Region | Cities | | :----- | ------------------------------ | | 1 | LosAngeles,Seattle, Houston | | 2 | Chicago, Columbus, Boston | | 3 | NewYork, LongIsland, Baltimore | | 4 | Atlanta, Raleigh, Cincinnati |

               
               

You can use List COLUMNS partitioning to create a table and store each row in the partition that corresponds to the employee's city, as shown below:

              

               CREATE
               TABLEemployees_1 ( idINT
               NOT
               NULL, fnameVARCHAR(30), lnameVARCHAR(30), hiredDATE
               NOT
               NULL
               DEFAULT
               '1970-01-01', separatedDATE
               DEFAULT
               '9999-12-31', job_codeINT, store_idINT, cityVARCHAR(15) )PARTITION
               BYLIST COLUMNS(city) (PARTITIONpRegion_1VALUES
               IN('LosAngeles','Seattle','Houston'),PARTITIONpRegion_2VALUES
               IN('Chicago','Columbus','Boston'),PARTITIONpRegion_3VALUES
               IN('NewYork','LongIsland','Baltimore'),PARTITIONpRegion_4VALUES
               IN('Atlanta','Raleigh','Cincinnati'));

               
               

Unlike List partitioning, in List COLUMNS partitioning, you do not need to use the expression in theCOLUMNS()clause to convert column values to integers.

List COLUMNS partitioning can also be implemented using columns of theDATEandDATETIMEtypes, as shown in the following example. This example uses the same names and columns as the previousemployees_1table, but uses List COLUMNS partitioning based on thehiredcolumn:

              

               CREATE
               TABLEemployees_2 ( idINT
               NOT
               NULL, fnameVARCHAR(30), lnameVARCHAR(30), hiredDATE
               NOT
               NULL
               DEFAULT
               '1970-01-01', separatedDATE
               DEFAULT
               '9999-12-31', job_codeINT, store_idINT, cityVARCHAR(15) )PARTITION
               BYLIST COLUMNS(hired) (PARTITIONpWeek_1VALUES
               IN('2020-02-01','2020-02-02','2020-02-03','2020-02-04','2020-02-05','2020-02-06','2020-02-07'),PARTITIONpWeek_2VALUES
               IN('2020-02-08','2020-02-09','2020-02-10','2020-02-11','2020-02-12','2020-02-13','2020-02-14'),PARTITIONpWeek_3VALUES
               IN('2020-02-15','2020-02-16','2020-02-17','2020-02-18','2020-02-19','2020-02-20','2020-02-21'),PARTITIONpWeek_4VALUES
               IN('2020-02-22','2020-02-23','2020-02-24','2020-02-25','2020-02-26','2020-02-27','2020-02-28'));

               
               

In addition, you can also add multiple columns in theCOLUMNS()clause. For example:

              

               CREATE
               TABLEt ( idint, namevarchar(10) )PARTITION
               BYLIST COLUMNS(id,name) (partitionp0values
               IN((1,'a'),(2,'b')),partitionp1values
               IN((3,'c'),(4,'d')),partitionp3values
               IN((5,'e'),(null,null)) );

               
               

Hash partitioning

Hash partitioning is used to make sure that data is evenly scattered into a certain number of partitions. With Range partitioning, you must specify the range of the column values for each partition when you use Range partitioning, while you just need to specify the number of partitions when you use Hash partitioning.

To create a Hash partitioned table, you need to append aPARTITION BY HASH (expr)clause to theCREATE TABLEstatement.expris an expression that returns an integer. It can be a column name if the type of this column is integer. In addition, you might also need to appendPARTITIONS num, wherenumis a positive integer indicating how many partitions a table is divided into.

The following operation creates a Hash partitioned table, which is divided into 4 partitions bystore_id:

              

               CREATE
               TABLEemployees ( idINT
               NOT
               NULL, fnameVARCHAR(30), lnameVARCHAR(30), hiredDATE
               NOT
               NULL
               DEFAULT
               '1970-01-01', separatedDATE
               DEFAULT
               '9999-12-31', job_codeINT, store_idINT)PARTITION
               BYHASH(store_id) PARTITIONS4;

               
               

IfPARTITIONS numis not specified, the default number of partitions is 1.

You can also use an SQL expression that returns an integer forexpr. For example, you can partition a table by the hire year:

              

               CREATE
               TABLEemployees ( idINT
               NOT
               NULL, fnameVARCHAR(30), lnameVARCHAR(30), hiredDATE
               NOT
               NULL
               DEFAULT
               '1970-01-01', separatedDATE
               DEFAULT
               '9999-12-31', job_codeINT, store_idINT)PARTITION
               BYHASH(YEAR(hired) ) PARTITIONS4;

               
               

The most efficient Hash function is one which operates upon a single table column, and whose value increases or decreases consistently with the column value.

For example,date_colis a column whose type isDATE, and the value of theTO_DAYS(date_col)expression varies with the value ofdate_col.YEAR(date_col)is different fromTO_DAYS(date_col), because not every possible change indate_colproduces an equivalent change inYEAR(date_col).

In contrast, assume that you have anint_colcolumn whose type isINT. Now consider about the expressionPOW(5-int_col,3) + 6. It is not a good Hash function though, because as the value ofint_colchanges, the result of the expression does not change proportionally. A value change inint_colmight result in a huge change in the expression result. For example, whenint_colchanges from 5 to 6, the change of the expression result is -1. But the result change might be -7 whenint_colchanges from 6 to 7.

In conclusion, when the expression has a form that is closer toy = cx, it is more suitable to be a Hash function. Because the more non-linear an expression is, the more unevenly scattered the data among the partitions tends to be.

In theory, pruning is also possible for expressions involving more than one column value, but determining which of such expressions are suitable can be quite difficult and time-consuming. For this reason, the use of hashing expressions involving multiple columns is not particularly recommended.

When usingPARTITION BY HASH, TiDB decides which partition the data should fall into based on the modulus of the result of the expression. In other words, if a partitioning expression isexprand the number of partitions isnum,MOD(expr, num)decides the partition in which the data is stored. Assume thatt1is defined as follows:

              

               CREATE
               TABLEt1 (col1INT, col2CHAR(5), col3DATE)PARTITION
               BYHASH(YEAR(col3) ) PARTITIONS4;

               
               

When you insert a row of data intot1and the value ofcol3is '2005-09-15', then this row is inserted into partition 1:

              

               MOD(YEAR('2005-09-01'),4) = MOD(2005,4) = 1

               
               

Key partitioning

Starting from v7.0.0, TiDB supports Key partitioning. For TiDB versions earlier than v7.0.0, if you try creating a Key partitioned table, TiDB creates it as a non-partitioned table and returns a warning.

Both Key partitioning and Hash partitioning can evenly distribute data into a certain number of partitions. The difference is that Hash partitioning only supports distributing data based on a specified integer expression or an integer column, while Key partitioning supports distributing data based on a column list, and partitioning columns of Key partitioning are not limited to the integer type. The Hash algorithm of TiDB for Key partitioning is different from that of MySQL, so the table data distribution is also different.

To create a Key partitioned table, you need to append aPARTITION BY KEY (columList)clause to theCREATE TABLEstatement.columListis a column list with one or more column names. The data type of each column in the list can be any type exceptBLOB,JSON, andGEOMETRY(Note that TiDB does not supportGEOMETRY). In addition, you might also need to appendPARTITIONS num(wherenumis a positive integer indicating how many partitions a table is divided into), or append the definition of the partition names. For example, adding(PARTITION p0, PARTITION p1)means dividing the table into two partitions namedp0andp1.

The following operation creates a Key partitioned table, which is divided into 4 partitions bystore_id:

              

               CREATE
               TABLEemployees ( idINT
               NOT
               NULL, fnameVARCHAR(30), lnameVARCHAR(30), hiredDATE
               NOT
               NULL
               DEFAULT
               '1970-01-01', separatedDATE
               DEFAULT
               '9999-12-31', job_codeINT, store_idINT)PARTITION
               BYKEY(store_id) PARTITIONS4;

               
               

IfPARTITIONS numis not specified, the default number of partitions is 1.

You can also create a Key partitioned table based on non-integer columns such as VARCHAR. For example, you can partition a table by thefnamecolumn:

              

               CREATE
               TABLEemployees ( idINT
               NOT
               NULL, fnameVARCHAR(30), lnameVARCHAR(30), hiredDATE
               NOT
               NULL
               DEFAULT
               '1970-01-01', separatedDATE
               DEFAULT
               '9999-12-31', job_codeINT, store_idINT)PARTITION
               BYKEY(fname) PARTITIONS4;

               
               

You can also create a Key partitioned table based on multiple columns. For example, you can divide a table into 4 partitions based onfnameandstore_id:

              

               CREATE
               TABLEemployees ( idINT
               NOT
               NULL, fnameVARCHAR(30), lnameVARCHAR(30), hiredDATE
               NOT
               NULL
               DEFAULT
               '1970-01-01', separatedDATE
               DEFAULT
               '9999-12-31', job_codeINT, store_idINT)PARTITION
               BYKEY(fname, store_id) PARTITIONS4;

               
               

Currently, TiDB does not support creating Key partitioned tables if the partition column list specified inPARTITION BY KEYis empty. For example, after you execute the following statement, TiDB will create a non-partitioned table and return anUnsupported partition type KEY, treat as normal tablewarning.

              

               CREATE
               TABLEemployees ( idINT
               NOT
               NULL, fnameVARCHAR(30), lnameVARCHAR(30), hiredDATE
               NOT
               NULL
               DEFAULT
               '1970-01-01', separatedDATE
               DEFAULT
               '9999-12-31', job_codeINT, store_idINT)PARTITION
               BYKEY() PARTITIONS4;

               
               

How TiDB handles Linear Hash partitions

Before v6.4.0, if you execute DDL statements ofMySQL Linear Hashpartitions in TiDB, TiDB can only create non-partitioned tables. In this case, if you still want to use partitioned tables in TiDB, you need to modify the DDL statements.

Since v6.4.0, TiDB supports parsing the MySQLPARTITION BY LINEAR HASHsyntax but ignores theLINEARkeyword in it. If you have some existing DDL and DML statements of MySQL Linear Hash partitions, you can execute them in TiDB without modification:

• For aCREATEstatement of MySQL Linear Hash partitions, TiDB will create a non-linear Hash partitioned table (note that there is no Linear Hash partitioned table in TiDB). If the number of partitions is a power of 2, the rows in the TiDB Hash partitioned table are distributed the same as that in the MySQL Linear Hash partitioned table. Otherwise, the distribution of these rows in TiDB is different from MySQL. This is because non-linear partitioned tables use a simple "modulus number of partition", while linear partitioned tables use "modulus next power of 2 and fold the values between the number of partitions and the next power of 2". For details, see#38450.

• For all other statements of MySQL Linear Hash partitions, they work in TiDB the same as that in MySQL, except that the rows are distributed differently if the number of partitions is not a power of 2, which will give different results forpartition selection,TRUNCATE PARTITION, andEXCHANGE PARTITION.

How TiDB handles Linear Key partitions

Starting from v7.0.0, TiDB supports parsing the MySQLPARTITION BY LINEAR KEYsyntax for Key partitioning. However, TiDB ignores theLINEARkeyword and uses a non-linear hash algorithm instead.

Before v7.0.0, if you try creating a Key partitioned table, TiDB creates it as a non-partitioned table and returns a warning.

How TiDB partitioning handles NULL

It is allowed in TiDB to useNULLas the calculation result of a partitioning expression.

Handling of NULL with Range partitioning

When you insert a row into a table partitioned by Range, and the column value used to determine the partition isNULL, then this row is inserted into the lowest partition.

              

               CREATE
               TABLEt1 ( c1INT, c2VARCHAR(20) )PARTITION
               BY
               RANGE(c1) (PARTITIONp0VALUESLESS THAN (0),PARTITIONp1VALUESLESS THAN (10),PARTITIONp2VALUESLESS THAN MAXVALUE );

               
               

              

               查询好了,0 rows affected (0.09 sec)

               
               

              

               select
               *
               fromt1partition(p0);

               
               

              

               +------|--------+ | c1 | c2 | +------|--------+ | NULL | mothra | +------|--------+ 1 row in set (0.00 sec)

               
               

              

               select
               *
               fromt1partition(p1);

               
               

              

               Empty set (0.00 sec)

               
               

              

               select
               *
               fromt1partition(p2);

               
               

              

               Empty set (0.00 sec)

               
               

Drop thep0partition and verify the result:

              

               alter
               tablet1drop
               partitionp0;

               
               

              

               查询好了,0 rows affected (0.08 sec)

               
               

              

               select
               *
               fromt1;

               
               

              

               Empty set (0.00 sec)

               
               

Handling of NULL with Hash partitioning

When partitioning tables by Hash, there is a different way of handlingNULLvalue - if the calculation result of the partitioning expression isNULL, it is considered as0.

              

               CREATE
               TABLEth ( c1INT, c2VARCHAR(20) )PARTITION
               BYHASH(c1) PARTITIONS2;

               
               

              

               查询好了,0 rows affected (0.00 sec)

               
               

              

               INSERT
               INTOthVALUES(NULL,'mothra'), (0,'gigan');

               
               

              

               查询好了,2 rows affected (0.04 sec)

               
               

              

               select
               *
               fromthpartition(p0);

               
               

              

               +------|--------+ | c1 | c2 | +------|--------+ | NULL | mothra | | 0 | gigan | +------|--------+ 2 rows in set (0.00 sec)

               
               

              

               select
               *
               fromthpartition(p1);

               
               

              

               Empty set (0.00 sec)

               
               

You can see that the inserted record(NULL, 'mothra')falls into the same partition as(0, 'gigan').

Handling of NULL with Key partitioning

For Key partitioning, the way of handlingNULLvalue is consistent with that of Hash partitioning. If the value of a partitioning field isNULL, it is treated as0.

Partition management

ForRANGE,RANGE COLUMNS,LIST, andLIST COLUMNSpartitioned tables, you can manage the partitions as follows:

• Add partitions using theALTER TABLE <表名称>添加分区(<分区specification>)statement.
• Drop partitions using theALTER TABLE DROP PARTITION statement.
  Remove all data from specified partitions using theALTER TABLE
  TRUNCATE PARTITION statement. The logic ofTRUNCATE PARTITIONis similar toTRUNCATE TABLEbut it is for partitions.
  Merge, split, or make other changes to the partitions using theALTER TABLE
  REORGANIZE PARTITION INTO ()statement.

  ForHASHandKEYpartitioned tables, you can manage the partitions as follows:

  • Decrease the number of partitions using theALTER TABLE
  COALESCE PARTITION statement. This operation reorganizes the partitions by copying the whole table to the new number of partitions online.
  Increase the number of partitions using theALTER TABLE
  ADD PARTITION statement. This operation reorganizes the partitions by copying the whole table to the new number of partitions online.
  Remove all data from specified partitions using theALTER TABLE
  TRUNCATE PARTITION statement. The logic ofTRUNCATE PARTITIONis similar toTRUNCATE TABLEbut it is for partitions.

  EXCHANGE PARTITIONworks by swapping a partition and a non-partitioned table, similar to how renaming a table likeRENAME TABLE t1 TO t1_tmp, t2 TO t1, t1_tmp TO t2works.

  For example,ALTER TABLE partitioned_table EXCHANGE PARTITION p1 WITH TABLE non_partitioned_tableswaps thepartitioned_tabletablep1partition with thenon_partitioned_tabletable.

  Ensure that all rows that you are exchanging into the partition match the partition definition; otherwise, the statement will fail.

  Note that TiDB has some specific features that might affectEXCHANGE PARTITION. When the table structure contains such features, you need to ensure thatEXCHANGE PARTITIONmeets theMySQL's EXCHANGE PARTITION condition. Meanwhile, ensure that these specific features are defined the same for both partitioned and non-partitioned tables. These specific features include the following:

  • Placement Rules in SQL: placement policies are the same.
  • TiFlash: the numbers of TiFlash replicas are the same.
  • Clustered Indexes: partitioned and non-partitioned tables are bothCLUSTERED,或bothNONCLUSTERED.

  In addition, there are limitations on the compatibility ofEXCHANGE PARTITIONwith other components. Both partitioned and non-partitioned tables must have the same definition.

  • TiFlash: when the TiFlash replica definitions in partitioned and non-partitioned tables are different, theEXCHANGE PARTITIONoperation cannot be performed.
  • TiCDC: TiCDC replicates theEXCHANGE PARTITIONoperation when both partitioned and non-partitioned tables have primary keys or unique keys. Otherwise, TiCDC will not replicate the operation.
  • TiDB Lightning and BR: do not perform theEXCHANGE PARTITIONoperation during import using TiDB Lightning or during restore using BR.

  Manage Range, Range COLUMNS, List, and List COLUMNS partitions

  This section uses the partitioned tables created by the following SQL statements as examples to show you how to manage Range and List partitions.

  
                
  
                 CREATE
                 TABLEmembers ( idint, fnamevarchar(255), lnamevarchar(255), dobdate, data json )PARTITION
                 BY
                 RANGE(YEAR(dob)) (PARTITIONpBefore1950VALUESLESS THAN (1950),PARTITIONp1950VALUESLESS THAN (1960),PARTITIONp1960VALUESLESS THAN (1970),PARTITIONp1970VALUESLESS THAN (1980),PARTITIONp1980VALUESLESS THAN (1990),PARTITIONp1990VALUESLESS THAN (2000));CREATE
                 TABLEmember_level ( idint, levelint, achievements json )PARTITION
                 BYLIST (level) (PARTITIONl1VALUES
                 IN(1),PARTITIONl2VALUES
                 IN(2),PARTITIONl3VALUES
                 IN(3),PARTITIONl4VALUES
                 IN(4),PARTITIONl5VALUES
                 IN(5));
  
                 
                 

  Drop partitions

  
                
  
                 ALTER
                 TABLEmembersDROP
                 PARTITIONp1990;ALTER
                 TABLEmember_levelDROP
                 PARTITIONl5;
  
                 
                 

  Truncate partitions

  
                
  
                 ALTER
                 TABLEmembersTRUNCATE
                 PARTITIONp1980;ALTER
                 TABLEmember_levelTRUNCATE
                 PARTITIONl4;
  
                 
                 

  Add partitions

  
                
  
                 ALTER
                 TABLEmembersADD
                 PARTITION(PARTITION`p1990to2010`VALUESLESS THAN (2010));ALTER
                 TABLEmember_levelADD
                 PARTITION(PARTITIONl5_6VALUES
                 IN(5,6));
  
                 
                 

  For a Range partitioned table,ADD PARTITIONwill append new partitions after the last existing partition. Compared with the existing partitions, the value defined inVALUES LESS THANfor new partitions must be greater. Otherwise, an error is reported:

  
                
  
                 ALTER
                 TABLEmembersADD
                 PARTITION(PARTITIONp1990VALUESLESS THAN (2000));
  
                 
                 

  
                
  
                 ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition
  
                 
                 

  Reorganize partitions

  Split a partition:

  
                
  
                 ALTER
                 TABLEmembers REORGANIZEPARTITION`p1990to2010`INTO(PARTITIONp1990VALUESLESS THAN (2000),PARTITIONp2000VALUESLESS THAN (2010),PARTITIONp2010VALUESLESS THAN (2020),PARTITIONp2020VALUESLESS THAN (2030),PARTITIONpMaxVALUESLESS THAN (MAXVALUE));ALTER
                 TABLEmember_level REORGANIZEPARTITIONl5_6INTO(PARTITIONl5VALUES
                 IN(5),PARTITIONl6VALUES
                 IN(6));
  
                 
                 

  Merge partitions:

  
                
  
                 ALTER
                 TABLEmembers REORGANIZEPARTITIONpBefore1950,p1950INTO(PARTITIONpBefore1960VALUESLESS THAN (1960));ALTER
                 TABLEmember_level REORGANIZEPARTITIONl1,l2INTO(PARTITIONl1_2VALUES
                 IN(1,2));
  
                 
                 

  Change the partitioning scheme definition:

  
                
  
                 ALTER
                 TABLEmembers REORGANIZEPARTITIONpBefore1960,p1960,p1970,p1980,p1990,p2000,p2010,p2020,pMaxINTO(PARTITIONp1800VALUESLESS THAN (1900),PARTITIONp1900VALUESLESS THAN (2000),PARTITIONp2000VALUESLESS THAN (2100));ALTER
                 TABLEmember_level REORGANIZEPARTITIONl1_2,l3,l4,l5,l6INTO(PARTITIONlOddVALUES
                 IN(1,3,5),PARTITIONlEvenVALUES
                 IN(2,4,6));
  
                 
                 

  When reorganizing partitions, you need to note the following key points:

  • Reorganizing partitions (including merging or splitting partitions) can change the listed partitions into a new set of partition definitions but cannot change the type of partitioning (for example, change the List type to the Range type, or change the Range COLUMNS type to the Range type).

  • For a Range partition table, you can reorganize only adjacent partitions in it.

    
                    
    
                     ALTER
                     TABLEmembers REORGANIZEPARTITIONp1800,p2000INTO(PARTITIONp2000VALUESLESS THAN (2100));
    
                     
                     

    
                    
    
                     ERROR 8200 (HY000): Unsupported REORGANIZE PARTITION of RANGE; not adjacent partitions
    
                     
                     

  • For a Range partitioned table, to modify the end of the range, the new end defined inVALUES LESS THANmust cover the existing rows in the last partition. Otherwise, existing rows no longer fit and an error is reported:

    
                    
    
                     INSERT
                     INTOmembersVALUES(313, "John", "Doe", "2022-11-22",NULL);ALTER
                     TABLEmembers REORGANIZEPARTITIONp2000INTO(PARTITIONp2000VALUESLESS THAN (2050));-- This statement will work as expected, because 2050 covers the existing rows.
                     ALTER
                     TABLEmembers REORGANIZEPARTITIONp2000INTO(PARTITIONp2000VALUESLESS THAN (2020));-- This statement will fail with an error, because 2022 does not fit in the new range.
                    
    
                     
                     

    
                    
    
                     ERROR 1526 (HY000): Table has no partition for value 2022
    
                     
                     

  • For a List partitioned table, to modify the set of values defined for a partition, the new definition must cover the existing values in that partition. Otherwise, an error is reported:

    
                    
    
                     INSERT
                     INTOmember_level (id, level)values(313,6);ALTER
                     TABLEmember_level REORGANIZEPARTITIONlEvenINTO(PARTITIONlEvenVALUES
                     IN(2,4));
    
                     
                     

    
                    
    
                     ERROR 1526 (HY000): Table has no partition for value 6
    
                     
                     

  • After partitions are reorganized, the statistics of the corresponding partitions are outdated, so you will get the following warning. In this case, you can use theANALYZE TABLEstatement to update the statistics.

    
                    
    
                     +
                     ---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
                     |Level|Code|Message|
                     +
                     ---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
                     |Warning|
                     1105
                     |The statisticsofrelated partitions will be outdated after reorganizing partitions. Please use'ANALYZE TABLE'statement if you wantto
                     updateit now|
                     +
                     ---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
                     1
                     row
                     in
                     set(0.00sec)
    
                     
                     

  Manage Hash and Key partitions

  This section uses the partitioned table created by the following SQL statement as examples to show you how to manage Hash partitions. For Key partitions, you can use the same management statements as well.

  
                
  
                 CREATE
                 TABLEexample ( idINT
                 PRIMARYKEY, dataVARCHAR(1024) )PARTITION
                 BYHASH(id) PARTITIONS2;
  
                 
                 

  Increase the number of partitions

  Increase the number of partitions for theexampletable by 1 (from 2 to 3):

  
                
  
                 ALTER
                 TABLEexampleADD
                 PARTITIONPARTITIONS1;
  
                 
                 

  You can also specify partition options by adding partition definitions. For example, you can use the following statement to increase the number of partitions from 3 to 5 and specify the names of the newly added partitions aspExample4andpExample5:

  
                
  
                 ALTER
                 TABLEexampleADD
                 PARTITION(PARTITIONpExample4 COMMENT=
                 'not p3, but pExample4 instead',PARTITIONpExample5 COMMENT=
                 'not p4, but pExample5 instead');
  
                 
                 

  Decrease the number of partitions

  Unlike Range and List partitioning,DROP PARTITIONis not supported for Hash and Key partitioning, but you can decrease the number of partitions withCOALESCE PARTITIONor delete all data from specific partitions withTRUNCATE PARTITION.

  Decrease the number of partitions for theexampletable by 1 (from 5 to 4):

  
                
  
                 ALTER
                 TABLEexample COALESCEPARTITION
                 1;
  
                 
                 

  Note

  The process of changing the number of partitions for Hash or Key partitioned tables reorganizes the partitions by copying all data to the new number of partitions. Therefore, after changing the number of partitions for a Hash or Key partitioned table, you will get the following warning about the outdated statistics. In this case, you can use theANALYZE TABLEstatement to update the statistics.

  
                   
  
                    +
                    ---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
                    |Level|Code|Message|
                    +
                    ---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
                    |Warning|
                    1105
                    |The statisticsofrelated partitions will be outdated after reorganizing partitions. Please use'ANALYZE TABLE'statement if you wantto
                    updateit now|
                    +
                    ---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
                    1
                    row
                    in
                    set(0.00sec)
  
                    
                    

  To better understand how theexampletable is organized now, you can show the SQL statement that is used to recreate theexampletable as follows:

  
                
  
                 SHOW
                 CREATE
                 TABLE\G
  
                 
                 

  
                
  
                 *************************** 1. row *************************** Table: example Create Table: CREATE TABLE `example` ( `id` int(11) NOT NULL, `data` varchar(1024) DEFAULT NULL, PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin PARTITION BY HASH (`id`) (PARTITION `p0`, PARTITION `p1`, PARTITION `p2`, PARTITION `pExample4` COMMENT 'not p3, but pExample4 instead') 1 row in set (0.01 sec)
  
                 
                 

  Truncate partitions

  Delete all data from a partition:

  
                
  
                 ALTER
                 TABLEexampleTRUNCATE
                 PARTITIONp0;
  
                 
                 

  
                
  
                 查询好了,0 rows affected (0.03 sec)
  
                 
                 

  Partition pruning

  Partition pruningis an optimization which is based on a very simple idea - do not scan the partitions that do not match.

  Assume that you create a partitioned tablet1:

  
                
  
                 CREATE
                 TABLEt1 ( fnameVARCHAR(50)NOT
                 NULL, lnameVARCHAR(50)NOT
                 NULL, region_code TINYINT UNSIGNEDNOT
                 NULL, dobDATE
                 NOT
                 NULL)PARTITION
                 BY
                 RANGE( region_code ) (PARTITIONp0VALUESLESS THAN (64),PARTITIONp1VALUESLESS THAN (128),PARTITIONp2VALUESLESS THAN (192),PARTITIONp3VALUESLESS THAN MAXVALUE );
  
                 
                 

  If you want to get the result of thisSELECTstatement:

  
                
  
                 SELECTfname, lname, region_code, dobFROMt1WHEREregion_code>
                 125
                 ANDregion_code<
                 130;
  
                 
                 

  It is evident that the result falls in either thep1or thep2partition, that is, you just need to search for the matching rows inp1andp2. Excluding the unneeded partitions is so-called "pruning". If the optimizer is able to prune a part of partitions, the execution of the query in the partitioned table will be much faster than that in a non-partitioned table.

  The optimizer can prune partitions throughWHEREconditions in the following two scenarios:

  • partition_column = constant
  • partition_column IN (constant1, constant2, ..., constantN)

  Currently, partition pruning does not work withLIKEconditions.

  Some cases for partition pruning to take effect

  1. Partition pruning uses the query conditions on the partitioned table, so if the query conditions cannot be pushed down to the partitioned table according to the planner's optimization rules, partition pruning does not apply for this query.

     For example:

     
                     
     
                      create
                      tablet1 (xint)partition
                      by
                      range(x) (partitionp0valuesless than (5),partitionp1valuesless than (10));create
                      tablet2 (xint);
     
                      
                      

     
                     
     
                      explainselect
                      *
                      fromt1left
                      joint2ont1.x=t2.xwheret2.x>
                      5;
     
                      
                      

     In this query, the left out join is converted to the inner join, and thent1.x > 5is derived fromt1.x = t2.xandt2.x > 5, so it could be used in partition pruning and only the partitionp1remains.

     
                     
     
                      explainselect
                      *
                      fromt1left
                      joint2ont1.x=t2.xandt2.x>
                      5;
     
                      
                      

     In this query,t2.x > 5cannot be pushed down to thet1partitioned table, so partition pruning would not take effect for this query.

  2. Since partition pruning is done during the plan optimizing phase, it does not apply for those cases that filter conditions are unknown until the execution phase.

     For example:

     
                     
     
                      create
                      tablet1 (xint)partition
                      by
                      range(x) (partitionp0valuesless than (5),partitionp1valuesless than (10));
     
                      
                      

     
                     
     
                      explainselect
                      *
                      fromt2wherex<(select
                      *
                      fromt1wheret2.x<t1.xandt2.x<
                      2);
     
                      
                      

     This query reads a row fromt2and uses the result for the subquery ont1. Theoretically, partition pruning could benefit fromt1.x > valexpression in the subquery, but it does not take effect there as that happens in the execution phase.

  3. As a result of a limitation from current implementation, if a query condition cannot be pushed down to TiKV, it cannot be used by the partition pruning.

     Take thefn(col)expression as an example. If the TiKV coprocessor supports thisfnfunction,fn(col)may be pushed down to the leaf node (that is, partitioned table) according to the predicate push-down rule during the plan optimizing phase, and partition pruning can use it.

     If the TiKV coprocessor does not support thisfnfunction,fn(col)would not be pushed down to the leaf node. Instead, it becomes aSelection节点的叶子节点。当前分区的公关uning implementation does not support this kind of plan tree.

  4. For Hash and Key partition types, the only query supported by partition pruning is the equal condition.

  5. For Range partition, for partition pruning to take effect, the partition expression must be in those forms:colorfn(col), and the query condition must be one of>,<,=,>=, and<=. If the partition expression is in the form offn(col), thefnfunction must be monotonous.

     If thefnfunction is monotonous, for anyxandy, ifx > y, thenfn(x) > fn(y). Then thisfnfunction can be called strictly monotonous. For anyxandy, ifx > y, thenfn(x) >= fn(y). In this case,fncould also be called "monotonous". In theory, all monotonous functions are supported by partition pruning.

     Currently, partition pruning in TiDB only support those monotonous functions:

     • UNIX_TIMESTAMP()
     • TO_DAYS()

     For example, the partition expression is a simple column:

     
                     
     
                      create
                      tablet (idint)partition
                      by
                      range(id) (partitionp0valuesless than (5),partitionp1valuesless than (10));select
                      *
                      fromtwhereid>
                      6;
     
                      
                      

     Or the partition expression is in the form offn(col)wherefnisto_days:

     
                     
     
                      create
                      tablet (dt datetime)partition
                      by
                      range(to_days(id)) (partitionp0valuesless than (to_days('2020-04-01')),partitionp1valuesless than (to_days('2020-05-01')));select
                      *
                      fromtwheredt>
                      '2020-04-18';
     
                      
                      

     An exception isfloor(unix_timestamp())as the partition expression. TiDB does some optimization for that case by case, so it is supported by partition pruning.

     
                     
     
                      create
                      tablet (tstimestamp(3)not
                      null
                      default
                      current_timestamp(3))partition
                      by
                      range(floor(unix_timestamp(ts))) (partitionp0valuesless than (unix_timestamp('2020-04-01 00:00:00')),partitionp1valuesless than (unix_timestamp('2020-05-01 00:00:00')));select
                      *
                      fromtwherets>
                      '2020-04-18 02:00:42.123';
     
                      
                      

  Partition selection

  SELECTstatements support partition selection, which is implemented by using aPARTITIONoption.

  
                
  
                 SET@@sql_mode=
                 '';CREATE
                 TABLEemployees ( idINT
                 NOT
                 NULLAUTO_INCREMENTPRIMARYKEY, fnameVARCHAR(25)NOT
                 NULL, lnameVARCHAR(25)NOT
                 NULL, store_idINT
                 NOT
                 NULL, department_idINT
                 NOT
                 NULL)PARTITION
                 BY
                 RANGE(id) (PARTITIONp0VALUESLESS THAN (5),PARTITIONp1VALUESLESS THAN (10),PARTITIONp2VALUESLESS THAN (15),PARTITIONp3VALUESLESS THAN MAXVALUE );INSERT
                 INTOemployeesVALUES('','Bob','Taylor',3,2), ('','Frank','Williams',1,2), ('','Ellen','Johnson',3,4), ('','Jim','Smith',2,4), ('','Mary','Jones',1,1), ('','Linda','Black',2,3), ('','Ed','Jones',2,1), ('','June','Wilson',3,1), ('','Andy','Smith',1,3), ('','Lou','Waters',2,4), ('','Jill','Stone',1,4), ('','Roger','White',3,2), ('',“霍华德”,'Andrews',1,2), ('','Fred','Goldberg',3,3), ('','Barbara','Brown',2,3), ('','Alice','Rogers',2,2), ('','Mark','Morgan',3,3), ('','Karen','Cole',3,2);
  
                 
                 

  You can view the rows stored in thep1partition:

  
                
  
                 SELECT
                 *
                 FROMemployeesPARTITION(p1);
  
                 
                 

  
                
  
                 +----|-------|--------|----------|---------------+ | id | fname | lname | store_id | department_id | +----|-------|--------|----------|---------------+ | 5 | Mary | Jones | 1 | 1 | | 6 | Linda | Black | 2 | 3 | | 7 | Ed | Jones | 2 | 1 | | 8 | June | Wilson | 3 | 1 | | 9 | Andy | Smith | 1 | 3 | +----|-------|--------|----------|---------------+ 5 rows in set (0.00 sec)
  
                 
                 

  If you want to get the rows in multiple partitions, you can use a list of partition names which are separated by commas. For example,SELECT * FROM employees PARTITION (p1, p2)returns all rows in thep1andp2partitions.

  When you use partition selection, you can still useWHEREconditions and options such asORDER BYandLIMIT. It is also supported to use aggregation options such asHAVINGandGROUP BY.

  
                
  
                 SELECT
                 *
                 FROMemployeesPARTITION(p0, p2)WHERElnameLIKE
                 'S%';
  
                 
                 

  
                
  
                 +----|-------|-------|----------|---------------+ | id | fname | lname | store_id | department_id | +----|-------|-------|----------|---------------+ | 4 | Jim | Smith | 2 | 4 | | 11 | Jill | Stone | 1 | 4 | +----|-------|-------|----------|---------------+ 2 rows in set (0.00 sec)
  
                 
                 

  
                
  
                 SELECTid, CONCAT(fname,' ', lname)ASnameFROMemployeesPARTITION(p0)ORDER
                 BYlname;
  
                 
                 

  
                
  
                 +----|----------------+ | id | name | +----|----------------+ | 3 | Ellen Johnson | | 4 | Jim Smith | | 1 | Bob Taylor | | 2 | Frank Williams | +----|----------------+ 4 rows in set (0.06 sec)
  
                 
                 

  
                
  
                 SELECTstore_id,COUNT(department_id)AScFROMemployeesPARTITION(p1,p2,p3)GROUP
                 BYstore_idHAVINGc>
                 4;
  
                 
                 

  
                
  
                 +---|----------+ | c | store_id | +---|----------+ | 5 | 2 | | 5 | 3 | +---|----------+ 2 rows in set (0.00 sec)
  
                 
                 

  Partition selection is supported for all types of table partitioning, including Range partitioning and Hash partitioning. For Hash partitions, if partition names are not specified,p0,p1,p2，…,或pN-1is automatically used as the partition name.

  SELECTinINSERT ... SELECTcan also use partition selection.

  Restrictions and limitations on partitions

  This section introduces some restrictions and limitations on partitioned tables in TiDB.

  Partitioning keys, primary keys and unique keys

  This section discusses the relationship of partitioning keys with primary keys and unique keys. The rule governing this relationship can be expressed as follows:Every unique key on the table must use every column in the table's partitioning expression. This also includes the table's primary key, because it is by definition a unique key.

  For example, the following table creation statements are invalid:

  
                
  
                 CREATE
                 TABLEt1 ( col1INT
                 NOT
                 NULL, col2DATE
                 NOT
                 NULL, col3INT
                 NOT
                 NULL, col4INT
                 NOT
                 NULL,UNIQUEKEY (col1, col2) )PARTITION
                 BYHASH(col3) PARTITIONS4;CREATE
                 TABLEt2 ( col1INT
                 NOT
                 NULL, col2DATE
                 NOT
                 NULL, col3INT
                 NOT
                 NULL, col4INT
                 NOT
                 NULL,UNIQUEKEY (col1),UNIQUEKEY (col3) )PARTITION
                 BYHASH(col1+col3) PARTITIONS4;
  
                 
                 

  In each case, the proposed table has at least one unique key that does not include all columns used in the partitioning expression.

  The valid statements are as follows:

  
                
  
                 CREATE
                 TABLEt1 ( col1INT
                 NOT
                 NULL, col2DATE
                 NOT
                 NULL, col3INT
                 NOT
                 NULL, col4INT
                 NOT
                 NULL,UNIQUEKEY (col1, col2, col3) )PARTITION
                 BYHASH(col3) PARTITIONS4;CREATE
                 TABLEt2 ( col1INT
                 NOT
                 NULL, col2DATE
                 NOT
                 NULL, col3INT
                 NOT
                 NULL, col4INT
                 NOT
                 NULL,UNIQUEKEY (col1, col3) )PARTITION
                 BYHASH(col1+col3) PARTITIONS4;
  
                 
                 

  The following example displays an error:

  
                
  
                 CREATE
                 TABLEt3 ( col1INT
                 NOT
                 NULL, col2DATE
                 NOT
                 NULL, col3INT
                 NOT
                 NULL, col4INT
                 NOT
                 NULL,UNIQUEKEY (col1, col2),UNIQUEKEY (col3) )PARTITION
                 BYHASH(col1+col3) PARTITIONS4;
  
                 
                 

  
                
  
                 ERROR 1491 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
  
                 
                 

  TheCREATE TABLEstatement fails because bothcol1andcol3are included in the proposed partitioning key, but neither of these columns is part of both of unique keys on the table. After the following modifications, theCREATE TABLEstatement becomes valid:

  
                
  
                 CREATE
                 TABLEt3 ( col1INT
                 NOT
                 NULL, col2DATE
                 NOT
                 NULL, col3INT
                 NOT
                 NULL, col4INT
                 NOT
                 NULL,UNIQUEKEY (col1, col2, col3),UNIQUEKEY (col1, col3) )PARTITION
                 BYHASH(col1+col3) PARTITIONS4;
  
                 
                 

  The following table cannot be partitioned at all, because there is no way to include in a partitioning key any columns that belong to both unique keys:

  
                
  
                 CREATE
                 TABLEt4 ( col1INT
                 NOT
                 NULL, col2INT
                 NOT
                 NULL, col3INT
                 NOT
                 NULL, col4INT
                 NOT
                 NULL,UNIQUEKEY (col1, col3),UNIQUEKEY (col2, col4) );
  
                 
                 

  Because every primary key is by definition a unique key, so the next two statements are invalid:

  
                
  
                 CREATE
                 TABLEt5 ( col1INT
                 NOT
                 NULL, col2DATE
                 NOT
                 NULL, col3INT
                 NOT
                 NULL, col4INT
                 NOT
                 NULL,PRIMARYKEY(col1, col2) )PARTITION
                 BYHASH(col3) PARTITIONS4;CREATE
                 TABLEt6 ( col1INT
                 NOT
                 NULL, col2DATE
                 NOT
                 NULL, col3INT
                 NOT
                 NULL, col4INT
                 NOT
                 NULL,PRIMARYKEY(col1, col3),UNIQUEKEY(col2) )PARTITION
                 BYHASH(YEAR(col2) ) PARTITIONS4;
  
                 
                 

  In the above examples, the primary key does not include all columns referenced in the partitioning expression. After adding the missing column in the primary key, theCREATE TABLEstatement becomes valid:

  
                
  
                 CREATE
                 TABLEt5 ( col1INT
                 NOT
                 NULL, col2DATE
                 NOT
                 NULL, col3INT
                 NOT
                 NULL, col4INT
                 NOT
                 NULL,PRIMARYKEY(col1, col2, col3) )PARTITION
                 BYHASH(col3) PARTITIONS4;CREATE
                 TABLEt6 ( col1INT
                 NOT
                 NULL, col2DATE
                 NOT
                 NULL, col3INT
                 NOT
                 NULL, col4INT
                 NOT
                 NULL,PRIMARYKEY(col1, col2, col3),UNIQUEKEY(col2) )PARTITION
                 BYHASH(YEAR(col2) ) PARTITIONS4;
  
                 
                 

  If a table has neither unique keys nor primary keys, then this restriction does not apply.

  When you change tables using DDL statements, you also need to consider this restriction when adding a unique index. For example, when you create a partitioned table as shown below:

  
                
  
                 CREATE
                 TABLEt_no_pk (c1INT, c2INT)PARTITION
                 BY
                 RANGE(c1) (PARTITIONp0VALUESLESS THAN (10),PARTITIONp1VALUESLESS THAN (20),PARTITIONp2VALUESLESS THAN (30),PARTITIONp3VALUESLESS THAN (40));
  
                 
                 

  
                
  
                 查询好了,0 rows affected (0.12 sec)
  
                 
                 

  You can add a non-unique index by usingALTER TABLEstatements. But if you want to add a unique index, thec1column must be included in the unique index.

  When using a partitioned table, you cannot specify the prefix index as a unique attribute:

  
                
  
                 CREATE
                 TABLEt (avarchar(20), bblob,UNIQUEINDEX (a(5)))PARTITION
                 by
                 rangecolumns (a) (PARTITIONp0valuesless than ('aaaaa'),PARTITIONp1valuesless than ('bbbbb'),PARTITIONp2valuesless than ('ccccc'));
  
                 
                 

  
                
  
                 ERROR1503(HY000): AUNIQUEINDEX must includeallcolumnsinthetable
                 's partitioning function
                
  
                 
                 

  Partitioning limitations relating to functions

  只有我显示的功能n the following list are allowed in partitioning expressions:

  
                
  
                 ABS() CEILING() DATEDIFF() DAY() DAYOFMONTH() DAYOFWEEK() DAYOFYEAR() EXTRACT() (see EXTRACT() function with WEEK specifier) FLOOR() HOUR() MICROSECOND() MINUTE() MOD() MONTH() QUARTER() SECOND() TIME_TO_SEC() TO_DAYS() TO_SECONDS() UNIX_TIMESTAMP() (with TIMESTAMP columns) WEEKDAY() YEAR() YEARWEEK()
  
                 
                 

  Compatibility with MySQL

  Currently, TiDB supports Range partitioning, Range COLUMNS partitioning, List partitioning, List COLUMNS partitioning, Hash partitioning, and Key partitioning. Other partitioning types that are available in MySQL are not supported yet in TiDB.

  Currently, TiDB does not support using an empty partition column list for Key partitioning.

  With regard to partition management, any operation that requires moving data in the bottom implementation is not supported currently, including but not limited to: adjust the number of partitions in a Hash partitioned table, modify the Range of a Range partitioned table, and merge partitions.

  对于不支持的分区类型,当你create a table in TiDB, the partitioning information is ignored and the table is created in the regular form with a warning reported.

  TheLOAD DATAsyntax does not support partition selection currently in TiDB.

  
                
  
                 create
                 tablet (idint, valint)partition
                 byhash(id) partitions4;
  
                 
                 

  The regularLOAD DATAoperation is supported:

  
                
  
                 loadlocaldata infile "xxx"intot ...
  
                 
                 

  ButLoad Datadoes not support partition selection:

  
                
  
                 loadlocaldata infile "xxx"intotpartition(p1)...
  
                 
                 

  For a partitioned table, the result returned byselect * from tis unordered between the partitions. This is different from the result in MySQL, which is ordered between the partitions but unordered inside the partitions.

  
                
  
                 create
                 tablet (idint, valint)partition
                 by
                 range(id) (partitionp0valuesless than (3),partitionp1valuesless than (7),partitionp2valuesless than (11));
  
                 
                 

  
                
  
                 查询好了,0 rows affected (0.10 sec)
  
                 
                 

  
                
  
                 insert
                 intotvalues(1,2), (3,4),(5,6),(7,8),(9,10);
  
                 
                 

  
                
  
                 查询好了,5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0
  
                 
                 

  TiDB returns a different result every time, for example:

  
                
  
                 select
                 *
                 fromt;
  
                 
                 

  
                
  
                 +------|------+ | | id val  | +------|------+ | 7 |8 | | 9 | 10 | | 1 | 2 | | 3 | 4 | | 5 | 6 | +------|------+ 5 rows in set (0.00 sec)
  
                 
                 

  The result returned in MySQL:

  
                
  
                 select
                 *
                 fromt;
  
                 
                 

  
                
  
                 +------|------+ | id | val | +------|------+ | 1 | 2 | | 3 | 4 | | 5 | 6 | | 7 | 8 | | 9 | 10 | +------|------+ 5 rows in set (0.00 sec)
  
                 
                 

  Thetidb_enable_list_partitionenvironment variable controls whether to enable the partitioned table feature. If this variable is set toOFF, the partition information will be ignored when a table is created, and this table will be created as a normal table.

  This variable is only used in table creation. After the table is created, modify this variable value takes no effect. For details, seesystem variables.

  Dynamic pruning mode

  TiDB accesses partitioned tables in eitherdynamicorstaticmode.dynamicmode is used by default since v6.3.0. However, dynamic partitioning is effective only after the full table-level statistics, or GlobalStats, are collected. Before GlobalStats are collected, TiDB will use thestaticmode instead. For detailed information about GlobalStats, seeCollect statistics of partitioned tables in dynamic pruning mode.

  
                
  
                 set@@session.tidb_partition_prune_mode=
                 'dynamic'
                
  
                 
                 

  Manual ANALYZE and normal queries use the session-leveltidb_partition_prune_modesetting. Theauto-analyzeoperation in the background uses the globaltidb_partition_prune_modesetting.

  Instaticmode, partitioned tables use partition-level statistics. Indynamicmode, partitioned tables use table-level GlobalStats.

  When switching fromstaticmode todynamicmode, you need to check and collect statistics manually. This is because after the switch todynamicmode, partitioned tables have only partition-level statistics but no table-level statistics. GlobalStats are collected only upon the nextauto-analyzeoperation.

  
                
  
                 setsession tidb_partition_prune_mode=
                 'dynamic';showstats_metawheretable_namelike"t";
  
                 
                 

  
                
  
                 +---------+------------+----------------+---------------------+--------------+-----------+ | Db_name | Table_name | Partition_name | Update_time | Modify_count | Row_count | +---------+------------+----------------+---------------------+--------------+-----------+ | test | t | p0 | 2022-05-27 20:23:34 | 1 | 2 | | test | t | p1 | 2022-05-27 20:23:34 | 2 | 4 | | test | t | p2 | 2022-05-27 20:23:34 | 2 | 4 | +---------+------------+----------------+---------------------+--------------+-----------+ 3 rows in set (0.01 sec)
  
                 
                 

  To make sure that the statistics used by SQL statements are correct after you enable globaldynamicpruning mode, you need to manually triggeranalyzeon the tables or on a partition of the table to obtain GlobalStats.

  
                
  
                 analyzetabletpartitionp1;showstats_metawheretable_namelike"t";
  
                 
                 

  
                
  
                 +---------+------------+----------------+---------------------+--------------+-----------+ | Db_name | Table_name | Partition_name | Update_time | Modify_count | Row_count | +---------+------------+----------------+---------------------+--------------+-----------+ | test | t | global | 2022-05-27 20:50:53 | 0 | 5 | | test | t | p0 | 2022-05-27 20:23:34 | 1 | 2 | | test | t | p1 | 2022-05-27 20:50:52 | 0 | 2 | | test | t | p2 | 2022-05-27 20:50:08 | 0 | 2 | +---------+------------+----------------+---------------------+--------------+-----------+ 4 rows in set (0.00 sec)
  
                 
                 

  If the following warning is displayed during theanalyzeprocess, partition statistics are inconsistent, and you need to collect statistics of these partitions or the entire table again.

  
                
  
                 | Warning | 8244 | Build table: `t` column: `a` global-level stats failed due to missing partition-level column stats, please run analyze table to refresh columns of all partitions
  
                 
                 

  You can also use scripts to update statistics of all partitioned tables. For details, seeUpdate statistics of partitioned tables in dynamic pruning mode.

  After table-level statistics are ready, you can enable the global dynamic pruning mode, which is effective to all SQL statements andauto-analyzeoperations.

  
                
  
                 set
                 globaltidb_partition_prune_mode=
                 dynamic
                
  
                 
                 

  Instaticmode, TiDB accesses each partition separately using multiple operators, and then merges the results usingUnion. The following example is a simple read operation where TiDB merges the results of two corresponding partitions usingUnion:

  
                
  
                 mysql>
                 create
                 tablet1(idint, ageint, key(id))partition
                 by
                 range(id) (partitionp0valuesless than (100),partitionp1valuesless than (200),partitionp2valuesless than (300),partitionp3valuesless than (400)); Query OK,0
                 rowsaffected (0.01sec) mysql>explainselect
                 *
                 fromt1whereid<
                 150;
  
                 
                 

  
                
  
                 +------------------------------+----------+-----------+------------------------+--------------------------------+ | id | estRows | task | access object | operator info | +------------------------------+----------+-----------+------------------------+--------------------------------+ | PartitionUnion_9 | 6646.67 | root | | | | ├─TableReader_12 | 3323.33 | root | | data:Selection_11 | | │ └─Selection_11 | 3323.33 | cop[tikv] | | lt(test.t1.id, 150) | | │ └─TableFullScan_10 | 10000.00 | cop[tikv] | table:t1, partition:p0 | keep order:false, stats:pseudo | | └─TableReader_18 | 3323.33 | root | | data:Selection_17 | | └─Selection_17 | 3323.33 | cop[tikv] | | lt(test.t1.id, 150) | | └─TableFullScan_16 | 10000.00 | cop[tikv] | table:t1, partition:p1 | keep order:false, stats:pseudo | +------------------------------+----------+-----------+------------------------+--------------------------------+ 7 rows in set (0.00 sec)
  
                 
                 

  Indynamicmode, each operator supports direct access to multiple partitions, so TiDB no longer usesUnion.

  
                
  
                 mysql>
                 set@@session.tidb_partition_prune_mode=
                 'dynamic'; Query OK,0
                 rowsaffected (0.00sec) mysql>explainselect
                 *
                 fromt1whereid<
                 150;+
                 -------------------------+----------+-----------+-----------------+--------------------------------+
                 |id|estRows|task|access object|operator info|
                 +
                 -------------------------+----------+-----------+-----------------+--------------------------------+
                 |TableReader_7|
                 3323.33
                 |root|
                 partition: p0,p1|data:Selection_6|
                 |└─Selection_6|
                 3323.33
                 |cop[tikv]|
                 |lt(test.t1.id,150)|
                 |└─一桌人lScan_5|
                 10000.00
                 |cop[tikv]|
                 table:t1|keeporder:false, stats:pseudo|
                 +
                 -------------------------+----------+-----------+-----------------+--------------------------------+
                 3
                 rows
                 in
                 set(0.00sec)
  
                 
                 

  From the above query results, you can see that theUnionoperator in the execution plan disappears while the partition pruning still takes effect and the execution plan only accessesp0andp1.

  dynamicmode makes execution plans simpler and clearer. Omitting the Union operation can improve the execution efficiency and avoid the problem of Union concurrent execution. In addition,dynamicmode also allows execution plans with IndexJoin which cannot be used instaticmode. (See examples below)

  Example 1: In the following example, a query is performed instaticmode using the execution plan with IndexJoin:

  
                
  
                 mysql>
                 create
                 tablet1 (idint, ageint, key(id))partition
                 by
                 range(id) (partitionp0valuesless than (100),partitionp1valuesless than (200),partitionp2valuesless than (300),partitionp3valuesless than (400)); Query OK,0
                 rowsaffected (0,08sec) mysql>
                 create
                 tablet2 (idint, codeint); Query OK,0
                 rowsaffected (0.01sec) mysql>
                 set@@tidb_partition_prune_mode=
                 'static'; Query OK,0
                 rowsaffected (0.00sec) mysql>explainselect
                 /*+ TIDB_INLJ(t1, t2) */t1.*
                 fromt1, t2wheret2.code=
                 0
                 andt2.id=t1.id;+
                 --------------------------------+----------+-----------+------------------------+------------------------------------------------+
                 |id|estRows|task|access object|operator info|
                 +
                 --------------------------------+----------+-----------+------------------------+------------------------------------------------+
                 |HashJoin_13|
                 12.49
                 |root|
                 |
                 inner
                 join, equal:[eq(test.t1.id, test.t2.id)]|
                 |├─TableReader_42(Build)|
                 9.99
                 |root|
                 |data:Selection_41|
                 |│ └─Selection_41|
                 9.99
                 |cop[tikv]|
                 |eq(test.t2.code,0),not(isnull(test.t2.id))|
                 |│ └─TableFullScan_40|
                 10000.00
                 |cop[tikv]|
                 table:t2|keeporder:false, stats:pseudo|
                 |└─PartitionUnion_15(Probe)|
                 39960.00
                 |root|
                 |
                 |
                 |├─TableReader_18|
                 9990.00
                 |root|
                 |data:Selection_17|
                 |│ └─Selection_17|
                 9990.00
                 |cop[tikv]|
                 |
                 not(isnull(test.t1.id))|
                 |│ └─TableFullScan_16|
                 10000.00
                 |cop[tikv]|
                 table:t1,partition: p0|keeporder:false, stats:pseudo|
                 |├─TableReader_24|
                 9990.00
                 |root|
                 |data:Selection_23|
                 |│ └─Selection_23|
                 9990.00
                 |cop[tikv]|
                 |
                 not(isnull(test.t1.id))|
                 |│ └─TableFullScan_22|
                 10000.00
                 |cop[tikv]|
                 table:t1,partition: p1|keeporder:false, stats:pseudo|
                 |├─TableReader_30|
                 9990.00
                 |root|
                 |data:Selection_29|
                 |│ └─Selection_29|
                 9990.00
                 |cop[tikv]|
                 |
                 not(isnull(test.t1.id))|
                 |│ └─TableFullScan_28|
                 10000.00
                 |cop[tikv]|
                 table:t1,partition: p2|keeporder:false, stats:pseudo|
                 |└─TableReader_36|
                 9990.00
                 |root|
                 |data:Selection_35|
                 |└─Selection_35|
                 9990.00
                 |cop[tikv]|
                 |
                 not(isnull(test.t1.id))|
                 |└─一桌人lScan_34|
                 10000.00
                 |cop[tikv]|
                 table:t1,partition: p3|keeporder:false, stats:pseudo|
                 +
                 --------------------------------+----------+-----------+------------------------+------------------------------------------------+
                 17
                 rows
                 in
                 set,1warning (0.00sec) mysql>
                 showwarnings;+
                 ---------+------+------------------------------------------------------------------------------------+
                 |Level|Code|Message|
                 +
                 ---------+------+------------------------------------------------------------------------------------+
                 |Warning|
                 1815
                 |Optimizer Hint/*+ INL_JOIN(t1, t2) */
                 or
                 /*+ TIDB_INLJ(t1, t2) */
                 isinapplicable|
                 +
                 ---------+------+------------------------------------------------------------------------------------+
                 1
                 row
                 in
                 set(0,00sec)
  
                 
                 

  From example 1, you can see that even if theTIDB_INLJhint is used, the query on the partitioned table cannot select the execution plan with IndexJoin.

  Example 2: In the following example, the query is performed indynamicmode using the execution plan with IndexJoin:

  
                
  
                 mysql>
                 set@@tidb_partition_prune_mode=
                 'dynamic'; Query OK,0
                 rowsaffected (0.00sec) mysql>explainselect
                 /*+ TIDB_INLJ(t1, t2) */t1.*
                 fromt1, t2wheret2.code=
                 0
                 andt2.id=t1.id;+
                 ---------------------------------+----------+-----------+------------------------+---------------------------------------------------------------------------------------------------------------------+
                 |id|estRows|task|access object|operator info|
                 +
                 ---------------------------------+----------+-----------+------------------------+---------------------------------------------------------------------------------------------------------------------+
                 |IndexJoin_11|
                 12.49
                 |root|
                 |
                 inner
                 join,inner:IndexLookUp_10,outerkey:test.t2.id,innerkey:test.t1.id, equal cond:eq(test.t2.id, test.t1.id)|
                 |├─TableReader_16(Build)|
                 9.99
                 |root|
                 |data:Selection_15|
                 |│ └─Selection_15|
                 9.99
                 |cop[tikv]|
                 |eq(test.t2.code,0),not(isnull(test.t2.id))|
                 |│ └─TableFullScan_14|
                 10000.00
                 |cop[tikv]|
                 table:t2|keeporder:false, stats:pseudo|
                 |└─IndexLookUp_10(Probe)|
                 12.49
                 |root|
                 partition:all
                 |
                 |
                 |├─Selection_9(Build)|
                 12.49
                 |cop[tikv]|
                 |
                 not(isnull(test.t1.id))|
                 |│ └─IndexRangeScan_7|
                 12.50
                 |cop[tikv]|
                 table:t1, index:id(id)|
                 range: decidedby[eq(test.t1.id, test.t2.id)], keeporder:false, stats:pseudo|
                 |└─TableRowIDScan_8(Probe)|
                 12.49
                 |cop[tikv]|
                 table:t1|keeporder:false, stats:pseudo|
                 +
                 ---------------------------------+----------+-----------+------------------------+---------------------------------------------------------------------------------------------------------------------+
                 8
                 rows
                 in
                 set(0.00sec)
  
                 
                 

  From example 2, you can see that indynamicmode, the execution plan with IndexJoin is selected when you execute the query.

  Currently, neitherstaticnordynamicpruning mode supports prepared statements plan cache.

  Update statistics of partitioned tables in dynamic pruning mode

  1. 找到所有的分区表:

     
                     
     
                      SELECT
                      DISTINCTCONCAT(TABLE_SCHEMA,'.', TABLE_NAME)FROMinformation_schema.PARTITIONSWHERETIDB_PARTITION_IDIS
                      NOT
                      NULL
                      ANDTABLE_SCHEMANOT
                      IN('INFORMATION_SCHEMA','mysql','sys','PERFORMANCE_SCHEMA','METRICS_SCHEMA');
     
                      
                      

     
                     
     
                      +-------------------------------------+ | concat(TABLE_SCHEMA,'.',TABLE_NAME) | +-------------------------------------+ | test.t | +-------------------------------------+ 1 row in set (0.02 sec)
     
                      
                      

  2. Generate the statements for updating the statistics of all partitioned tables:

     
                     
     
                      SELECT
                      DISTINCTCONCAT('ANALYZE TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' ALL COLUMNS;')FROMinformation_schema.PARTITIONSWHERETIDB_PARTITION_IDIS
                      NOT
                      NULL
                      ANDTABLE_SCHEMANOT
                      IN('INFORMATION_SCHEMA','mysql','sys','PERFORMANCE_SCHEMA','METRICS_SCHEMA');
     
                      
                      

     
                     
     
                      +----------------------------------------------------------------------+ | concat('ANALYZE TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' ALL COLUMNS;') | +----------------------------------------------------------------------+ | ANALYZE TABLE test.t ALL COLUMNS; | +----------------------------------------------------------------------+ 1 row in set (0.01 sec)
     
                      
                      

     You can changeALL COLUMNSto the columns you need.

  3. Export the batch update statements to a file:

     
                     
     
                      mysql --host xxxx --port xxxx -u root -p -e"SELECT DISTINCT CONCAT('ANALYZE TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' ALL COLUMNS;') \ FROM information_schema.PARTITIONS \ WHERE TIDB_PARTITION_ID IS NOT NULL \ AND TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA','mysql','sys','PERFORMANCE_SCHEMA','METRICS_SCHEMA');"|teegatherGlobalStats.sql
     
                      
                      

  4. Execute a batch update:

     Process SQL statements before executing thesourcecommand:

     
                     
     
                      sed -i "" '1d' gatherGlobalStats.sql --- mac sed -i '1d' gatherGlobalStats.sql --- linux
     
                      
                      

     
                     
     
                      SETsession tidb_partition_prune_mode=
                      dynamic; source gatherGlobalStats.sql
     
                      
                      

  Download PDF Request docs changes Ask questions on Discord

  Playground

  New

  One-stop & interactive experience of TiDB's capabilities WITHOUT registration.
  What's on this page
  Was this page helpful?