Does mysql support partitioning?

Mysql supports partitioning function starting from version 5.1. In MySQL 5.1, the partition expression must be an integer, or an expression that returns an integer; while MySQL 5.5 provides support for non-integer expression partitioning. The partition of the MySQL database is a local partition index. One partition stores both data and indexes; that is to say, the clustered index and non-clustered index of each zone are placed in their respective zones (different physical files). MySQL supports 4 partition types: RANGE partition, LIST partition, HASH partition, and KEY partition.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

mysql supports partitioning.

mysql partitioning overview

MySQL added support for horizontal partitioning in 5.1. Partitioning is the breaking down of a table or index into smaller, more manageable parts. Each zone is independent and can be processed independently or as part of a larger object. This is a function supported by MySQL, and the business code does not need to be changed. You must know that MySQL is OLTP-oriented data, unlike other DBs such as TIDB. Then you should be very careful when using partitions. If you don't know how to use partitions, it may have a negative impact on performance.

The partition of MySQL database is a local partition index. A partition stores both data and index. In other words, the clustered index and non-clustered index of each zone are placed in their respective zones (different physical files). Currently, MySQL database does not support global partitioning.

No matter what type of partitioning, if there is a primary key or unique index in the table, the partitioning column must be a component of the unique index.

Limiting factors of partitioned tables

(1). A table can only have a maximum of 1024 partitions.

(2). In MySQL5.1, the partition expression must be an integer, or an expression that returns an integer. Support for non-integer expression partitioning is provided in MySQL 5.5.

(3). If there are primary key or unique index columns in the partition field, then many primary key columns and unique index columns must be included. That is: the partition field either does not contain the primary key or index column, or contains all primary key and index columns.

(4). Foreign key constraints cannot be used in partitioned tables.

(5) MySQL partitioning applies to all data and indexes in a table. You cannot partition table data but not indexes, you cannot partition indexes but not tables, and you cannot partition tables only. part of the data partition.

Partition Type

Currently MySQL supports several types of partitions, RANGE partition, LIST partition, HASH partition, and KEY partition. If the table has a primary key or a unique index, the partition column must be a component of the unique index. In actual combat, RANGE partitioning is used most likely.

RANGE partition

RANGE partition is the most commonly used partition type in practice. Row data is placed into partitions based on column values ​​belonging to a given continuous interval. But remember, when the inserted data does not have a value defined in a partition, an exception will be thrown.

RANGE partitioning is mainly used for date column partitioning, such as transaction tables, sales tables, etc. Data can be stored according to year and month. If you partition date type data in the unique index, please note that the optimizer can only optimize functions such as YEAR(), TO_DAYS(), TO_SECONDS(), and UNIX_TIMESTAMP(). In actual combat, the int type can be used, so just store yyyyMM. You don’t have to worry about functions anymore.

CREATE TABLE `m_test_db`.`Order` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `partition_key` INT NOT NULL,
    `amt` DECIMAL(5) NULL,
    PRIMARY KEY (`id` , `partition_key`)
) PARTITION BY RANGE (partition_key) PARTITIONS 5 (
PARTITION part0 VALUES LESS THAN (201901) , 
PARTITION part1 VALUES LESS THAN (201902) , 
PARTITION part2 VALUES LESS THAN (201903) , 
PARTITION part3 VALUES LESS THAN (201904) , 
PARTITION part4 VALUES LESS THAN (201905));

At this time we insert some data first

INSERT INTO `m_test_db`.`Order` (`id`, `partition_key`, `amt`) VALUES ('1', '201901', '1000');
INSERT INTO `m_test_db`.`Order` (`id`, `partition_key`, `amt`) VALUES ('2', '201902', '800');
INSERT INTO `m_test_db`.`Order` (`id`, `partition_key`, `amt`) VALUES ('3', '201903', '1200');

Now we query it and find through the EXPLAIN PARTITION command that the SQL optimizer only searches the corresponding area and will not search all partitions

If there is a problem with the sql statement, all areas will be searched. It would be dangerous. Therefore, after partitioning the table, the select statement must use the partition key.

The following 3 types are not too commonly used, so I will mention them briefly.

LIST partition

LIST partition is very similar to RANGE partition, except that the values ​​of the partition column are discrete, not continuous. LIST partitioning uses VALUES IN because the value of each partition is discrete, so only values ​​can be defined.

HASH Partition

Speaking of hash, the purpose is obvious. Distribute the data evenly into the predefined partitions to ensure the number of each partition. Much the same.

KEY partition

KEY partition is similar to HASH partition. The difference is that HASH partition uses user-defined functions for partitioning, and KEY partition uses functions provided by the database for partitioning. .

分区和性能

一项技术，不是用了就一定带来益处。比如显式锁功能比内置锁强大，你没玩好可能导致很不好的情况。分区也是一样，不是启动了分区数据库就会运行的更快，分区可能会给某些sql语句性能提高，但是分区主要用于数据库高可用性的管理。

数据库应用分为2类，一类是OLTP（在线事务处理），一类是OLAP（在线分析处理）。对于OLAP应用分区的确可以很好的提高查询性能，因为一般分析都需要返回大量的数据，如果按时间分区，比如一个月用户行为等数据，则只需扫描响应的分区即可。在OLTP应用中，分区更加要小心，通常不会获取一张大表的10%的数据，大部分是通过索引返回几条数据即可。

比如一张表1000w数据量，如果一句select语句走辅助索引，但是没有走分区键。那么结果会很尴尬。如果1000w的B+树的高度是3，现在有10个分区。那么不是要(3+3)*10次的逻辑IO？（3次聚集索引，3次辅助索引，10个分区）。所以在OLTP应用中请小心使用分区表。

在日常开发中，如果想查看sql语句的分区查询结果可以使用explain partitions + select sql来获取，partitions标识走了哪几个分区。

mysql> explain partitions select * from TxnList where startTime>&#39;2016-08-25 00:00:00&#39; and startTime<&#39;2016-08-25 23:59:00&#39;;  
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+  
| id | select_type | table             | partitions | type | possible_keys | key  | key_len | ref  | rows  | Extra       |  
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+  
|  1 | SIMPLE      | ClientActionTrack | p20160825  | ALL  | NULL          | NULL | NULL    | NULL | 33868 | Using where |  
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+  
1 row in set (0.00 sec)

注：

1.MySQL Workbench下添加分区的截图

2. Table has no partition for value 12

在12月的某一天，我查看了生产的日志文件，忽然发现系统一直在报错：Table has no partition for value 12。仔细检查分区sql发现分区的时候用的是less than

也就是说我在注释1截图里面的分区是不包括12月的区的。执行以下命令增加分区：

ALTER TABLE table_name ADD PARTITION (PARTITION p_12 VALUES LESS THAN (13));

如果没有进行适当的处理，将会报错。所以在进行 RANGE 分区时，要思考这种情况。一般情况下，就时在最后添加一个 MAXVALUE 分区，如下：

PARTITION p_max VALUES LESS THAN MAXVALUE

【相关推荐：mysql视频教程】

The above is the detailed content of Does mysql support partitioning?. For more information, please follow other related articles on the PHP Chinese website!