分区表

对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成。实现分区的代码实际上是对一组底层表的句柄对象（Handler Object）的封装。对分区表的请求，都会通过句柄对象转化成对存储引擎的接口调用。所以分区对于SQL层来说是一个完全封装底层实现的黑盒子，对应用是透明的，但是从底层的文件系统来看就很容易发现，每一个分区表都有一个使用#分隔命名的表文件。

什么场景下需要使用分区表

表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据
分区表的数据更容易维护，例如，想批量删除大量数据可以使用清除整个分区的方式，另外，还可以对一个独立分区进行优化，检查，修复等操作
分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备
如果需要，还可以备份和恢复独立的分区，这在非常大的数据集的场景下效果非常好

分区表的原理

分区表由多个相关的底层表实现，这些底层表也是由句柄对象（Handler object）表示，所以我们也可以直接访问各个分区。存储引擎管理分区的各个底层表和管理普通表一样，分区表的索引只是在各个底层表上各自加上一个完全相同的索引。从存储引擎的角度来看，底层表和一个普通表没有任何不同，存储引擎也无需知道这是一个普通表哈市一个分区表的一部分。

分区表上的操作按照下面的逻辑进行：

SELECT查询：当查询一个分区表的时候，分区层先打开并锁住所有的底层表，优化器先判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据。
INSERT操作：当写入一条记录时，分区表先打开并锁住所有的底层表，然后确定哪个分区接收这条记录，再将记录写入对应底层表
DELETE操作：当删除一条记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作
UPDATE操作：当更新一条记录时，分区层先打开并锁住所有的底层表，MySQL先确定需要更新的记录在哪个分区，然后取出数据并更新，再判断更新后的数据应该放在哪个分区，最后对底层表进行写入操作，并对原数据所在的底层表进行删除操作。

分区表的限制

1：表的分区表达式中使用的所有列必须是表可能具有的每个唯一键的一部分，包括任何主键。这意味着下面的 SQL 语句创建的表不能被分区:

mysql> CREATE TABLE tnp (
-> id INT NOT NULL AUTO_INCREMENT,
-> ref BIGINT NOT NULL,
-> name VARCHAR(255),
-> PRIMARY KEY pk (id),
-> UNIQUE KEY uk (name)
-> );
Query OK, 0 rows affected (0.05 sec)

下面我们添加分区

mysql> ALTER TABLE tnp PARTITION BY HASH(id) PARTITIONS 8;
ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table’s partitioning function (prefixed columns are not considered).

因为键pk和uk没有共同的列，所以在分区表达式中没有可用的列。在这种情况下，可能的解决方案包括将name列添加到表的主键，将id列添加到uk，或者简单地删除唯一的键。

mysql> alter table tnp drop index uk;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE tnp PARTITION BY HASH(id) PARTITIONS 8;
Query OK, 0 rows affected (0.27 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW CREATE TABLE tnp;
+——-+—————————————————————–+
| Table | Create Table |
+——-+—————————————————————–+
| tnp | CREATE TABLE tnp (
id int NOT NULL AUTO_INCREMENT,
ref bigint NOT NULL,
name varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (id)
PARTITIONS 8 */ |
+——-+——————————————————————+
1 row in set (0.00 sec)

2：分区名通常遵循管理其他MySQL标识符的规则，例如表和数据库。但是，您应该注意到分区名称不区分大小写。例如，下列CREATE TABLE语句失败如下:

mysql> CREATE TABLE t2 (val INT)
-> PARTITION BY LIST(val)(
-> PARTITION mypart VALUES IN (1,3,5),
-> PARTITION MyPart VALUES IN (2,4,6)
-> );
ERROR 1517 (HY000): Duplicate partition name MyPart

故障发生是因为MySQL认为分区名称mypart和MyPart没有区别。

分区表的类型

RANGE
LIST
COLUMNS
- RANGE COLUMNS
- LIST COLUMNS
HASH
KEY

不管您使用的分区类型是什么，都必须记住,在创建时，分区总是自动编号，从0开始。当将新行插入到分区表中时，将使用这些分区号来标识正确的分区。例如，如果您的表使用4个分区，那么这些分区的编号为0，1，2和3。

对于RANGE和LIST分区类型，必要确保为每个分区号定义了一个分区。对于HASH分区，用户提供的表达式必须计算为大于0的整数值。对于KEY分区，这个问题由MySQL服务器内部使用的哈希函数自动处理。

RANGE分区

按范围分区的表以这样的方式分区，即每个分区包含分区表达式值位于给定范围内的行。范围应该是连续的，但不是重叠的，并使用VALUES LESS THAN运算符定义。

mysql> create table order_log(

-> id int not null auto_increment,

-> user_id int not null default 0 comment ‘用户ID’,

-> goods_id int not null default 0 comment ‘商品ID’,

-> add_time int not null default 0 comment ‘订单创建时间’,

-> order_year smallint not null default 0 comment ‘订单创建时间的对应年份’,

-> primary key(`id`,`order_year`)

-> )engine=innodb charset=utf8 comment=”订单表”

-> PARTITION BY RANGE(order_year) (

-> PARTITION p0 VALUES LESS THAN (2018),

-> PARTITION p1 VALUES LESS THAN (2019),

-> PARTITION p2 VALUES LESS THAN (2020),

-> PARTITION p3 VALUES LESS THAN (2021),

-> PARTITION p4 VALUES LESS THAN (2022),

-> PARTITION p5 VALUES LESS THAN (2023),

-> PARTITION p6 VALUES LESS THAN (2024),

-> PARTITION p7 VALUES LESS THAN MAXVALUE

-> );

上面的分区的意思是: 2018年以前的数据都存在p0分区中，2019年以前的都存在p1分区中，一直到p7,大于等于2024年的数据都存在p7中。

我们创建一个按年份分区的分区表，大家可能会有疑问，为什么我单独用了order_year字段来单独表示年份，而不是直接用add_time来进行分区，这是因为innodb的索引机制导致的，innodb的所有辅助索引都会默认加上主键索引，所以主键索引越小越好，如果用id+add_time那就是8个字节，现在就是6个字节了

我们先查看分区表对应的数据

mysql> select TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,PARTITION_EXPRESSION,TABLE_ROWS from information_schema.PARTITIONS where TABLE_NAME=’order_log’;

+————–+————+—————-+———————-+————+

+————–+————+—————-+———————-+————+

+————–+————+—————-+———————-+————+

8 rows in set (0.00 sec)

我们可以看到TABLE_ROWS都是0行，这时候我们插入三条数据看一下

mysql> insert into order_log select 1,1,1,unix_timestamp(‘2018-12-12 12:12:12’),2018;

mysql> insert into order_log select 1,1,1,unix_timestamp(‘2019-12-12 12:12:12’),2019;

mysql> insert into order_log select 1,1,1,unix_timestamp(‘2025-12-12 12:12:12’),2025;

按照我们前面的分区算法来看的话，应该是p1有1条，p2有1条，p7有一条，那么这时候我们再看下

mysql> select TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,PARTITION_EXPRESSION,TABLE_ROWS from information_schema.PARTITIONS where TABLE_NAME=’order_log’;

+————–+————+—————-+———————-+————+

+————–+————+—————-+———————-+————+

+————–+————+—————-+———————-+————+

8 rows in set (0.00 sec)

的确跟我们的预期一样,我们可以在查询中直接利用到分区，为了看到效果，我们插入一些测试数据

mysql> DELIMITER $$

mysql> CREATE PROCEDURE insert_test_val(in num_limit int,in rand_limit int)

-> BEGIN

-> DECLARE i int default 1;

-> DECLARE user_id int default 1;

-> DECLARE goods_id int default 1;

-> DECLARE add_time int default 1;

-> DECLARE order_year int default 1;

-> WHILE i<=num_limit do

-> set user_id = FLOOR(rand()*rand_limit);

-> set goods_id = FLOOR(rand()*rand_limit);

-> set add_time = unix_timestamp(now())+(86400*floor(730*rand()));

-> set order_year=from_unixtime(add_time,’%Y’);

-> INSERT into order_log values (null,user_id,goods_id,add_time,order_year);

-> set i = i + 1;

-> END WHILE;

-> END;

-> $$

Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;

mysql> call insert_test_val(100000,10);

这时候我们再看一下数据

mysql> select TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,PARTITION_EXPRESSION,TABLE_ROWS from information_schema.PARTITIONS where TABLE_NAME=’order_log’;

+————–+————+—————-+———————-+————+

+————–+————+—————-+———————-+————+

+————–+————+—————-+———————-+————+

8 rows in set (0.00 sec)

我们针对order_year=2020的去查询

mysql> explain select * from order_log where order_year=2020 order by id asc limit 20;

+—-+————-+———–+————+——-+—————+———+———+——+——+———-+————-+

+—-+————-+———–+————+——-+—————+———+———+——+——+———-+————-+

| 1 | SIMPLE | order_log | p3 | index | NULL | PRIMARY | 6 | NULL | 20 | 10.00 | Using where |

+—-+————-+———–+————+——-+—————+———+———+——+——+———-+————-+

1 row in set, 1 warning (0.00 sec)

这时候我们可以看到partitions列有p3，那么说明查询的时候过滤了其他分区

这种类型的分区的一个变体是 RANGE COLUMNS分区。通过RANGE COLUMNS进行分区，可以使用多个列来定义分区范围，既适用于分区中的行的放置，也适用于在执行分区修剪时确定包含或排除特定的分区。

LIST分区

MySQL中的List分区在许多方面类似于RANGE分区。在按RANGE进行分区时，必须显式地定义每个分区。这两种类型的分区之间的主要区别是，在LIST分区中，每个分区都是根据一组值列表中的一个列值的成员来定义和选择的，而不是在一系列连续的值范围内。

这是通过用PARTITION BY LIST(expr)来完成的，其中expr是一个列值或基于列值的表达式，返回一个整数值，然后用一个 VALUES IN (value_list)来定义每个分区，其中value_list是一个逗号分隔的整数列表

例如我是某个商品在某个省的总代理，然后我要看下我每个子代理下面的数据

mysql> create table goods_sales(

-> id int not null auto_increment ,

-> num int not null default 0 comment ‘数量’,

-> money int not null default 0 comment ‘金额’,

-> agency_id tinyint unsigned not null default 0 comment ‘代理ID’,

-> primary key (`id`,`agency_id`)

-> )engine=innodb charset=utf8

-> PARTITION BY LIST(agency_id) (

-> PARTITION p0 VALUES IN (1,2,3,4,5),

-> PARTITION p1 VALUES IN (6,7,8,9,10),

-> PARTITION p2 VALUES IN (11,12,13,14,15),

-> PARTITION p3 VALUES IN (16,17,18,19,20)

-> );

与RANGE分区的情况不同，没有“catch-all”例如MAXVALUE;分区表达式的所有期望值都应该包含在PARTITION … VALUES IN (…) 子句中，包含一个未匹配的分区列值的INSERT 语句会出现错误，如本例所示:

mysql> insert into goods_sales select 1,20,10000,21;

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

您可以使用IGNORE关键字来忽略此类型的错误。如果这样做, 则不会插入包含不匹配的分区列值的行, 但会插入具有匹配值的任何行, 并且不会报告任何错误。例如

mysql> insert IGNORE into goods_sales (`id`,`num`,`money`,`agency_id`) values (1,2,3,4),(2,3,4,5),(1,2,3,100);

Query OK, 2 rows affected, 1 warning (0.01 sec)

Records: 3 Duplicates: 1 Warnings: 1

mysql> select * from goods_sales;

+—-+—–+——-+———–+

| id | num | money | agency_id |

+—-+—–+——-+———–+

| 1 | 2 | 3 | 4 |

| 2 | 3 | 4 | 5 |

+—-+—–+——-+———–+

2 rows in set (0.00 sec)

在这里，我们可以看到(1,2,3,100)这条数据并没有插入成功，但也没影响到前面的两条数据

MySQL 5.7 提供对 LIST COLUMNS分区的支持。这是 LIST分区的变体, 使您可以使用非整数类型的列来分区列, 也可以使用多列作为分区键

COLUMNS分区

接下来的两部分讨论COLUMNS分区，它们是RANGE和LIST分区的变体.. COLUMNS分区允许在分区键中使用多个列。所有这些列都将被考虑在哪个分区中放置行, 以及确定在分区修剪中要检查哪些分区以匹配行。

此外，RANGE COLUMNS分区和 LIST COLUMNS分区支持使用非整数列来定义值范围或列表成员。允许的数据类型如下表所示:

所有的数字类型 : TINYINT, SMALLINT, MEDIUMINT, INT (INTEGER), and BIGINT（这与按RANGE和 LIST进行分区相同），其他数值数据类型(如DECIMAL or FLOAT)不能作为分区列。
日期类型：DATE 和 DATETIME.
字符串类型：CHAR, VARCHAR, BINARY和 VARBINARY, 不支持TEXT和BLOB列

RANGE COLUMNS分区

Range columns分区类似于range分区，但是允许您使用基于多个列值的范围来定义分区。此外，您可以使用除整数类型以外的类型的列来定义范围。

RANGE COLUMNS分区与RANGE 分区有很大不同，有以下几种方式:

RANGE COLUMNS不接受表达式，只接受列的名称
RANGE COLUMNS接受一个或多个列的列表。
RANGE COLUMNS分区基于元组之间的比较（列值列表），而不是标量值之间的比较。在RANGE COLUMNS分区中放置行也是基于元组之间的比较。
RANGE COLUMN分区列不限制为integer列。string、DATE和DATETIME列也可以用作分区列。

下面我们创建一个RANGE COLUMNS分区

mysql> CREATE TABLE test1(

-> id int not null auto_increment,

-> key_a int not null default 0,

-> key_b int not null default 0,

-> PRIMARY KEY (`id`,`key_a`,`key_b`)

-> )engine=innodb charset=utf8

-> PARTITION BY RANGE COLUMNS(key_a, key_b) (

-> PARTITION p0 VALUES LESS THAN (1, 20),

-> PARTITION p1 VALUES LESS THAN (2, 15),

-> PARTITION p2 VALUES LESS THAN (2, 20),

-> PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE)

-> );

然后我们插入几条数据看一下

mysql> insert into test1 (`key_a`,`key_b`) values (1,15),(2,10),(2,20),(2,30)

mysql> SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = ‘test1’;

+—————-+————+

| PARTITION_NAME | TABLE_ROWS |

+—————-+————+

| p0 | 1 |

| p1 | 1 |

| p2 | 0 |

| p3 | 2 |

+—————-+————+

4 rows in set (0.01 sec)

我们对比一下，看下这数据是怎么进去的,多列分区我们比较的是行而不是标量值。

我们可以将插入的行值与用于在表test1中定义分区的VALUES THAN LESS THAN子句中插入的行值进行比较，如下所示:

mysql> SELECT (1,15) < (1,20), (2,10) < (1,20), (2,20) < (1,20),(2,30)< (1,20);

+—————–+—————–+—————–+—————-+

| (1,15) < (1,20) | (2,10) < (1,20) | (2,20) < (1,20) | (2,30)< (1,20) |

+—————–+—————–+—————–+—————-+

| 1 | 0 | 0 | 0 |

+—————–+—————–+—————–+—————-+

1 row in set (0.01 sec)

说明p0有一条数据

mysql> SELECT (1,15) < (2,15), (2,10) < (2,15), (2,20) < (2,15),(2,30)< (2,15);

+—————–+—————–+—————–+—————-+

| (1,15) < (2,15) | (2,10) < (2,15) | (2,20) < (2,15) | (2,30)< (2,15) |

+—————–+—————–+—————–+—————-+

| 1 | 1 | 0 | 0 |

+—————–+—————–+—————–+—————-+

1 row in set (0.00 sec)

这里看p1有两条数据，但(1,15)已经在p0了，所以也只有一条数据

mysql> SELECT (1,15) < (2,20), (2,10) < (2,20), (2,20) < (2,20),(2,30)< (2,20);

+—————–+—————–+—————–+—————-+

| (1,15) < (2,20) | (2,10) < (2,20) | (2,20) < (2,20) | (2,30)< (2,20) |

+—————–+—————–+—————–+—————-+

| 1 | 1 | 0 | 0 |

+—————–+—————–+—————–+—————-+

1 row in set (0.00 sec)

p2一条数据都没有，因为(1,15),(2,10)分别存在p0和p1里面去了

LIST COLUMNS分区

Mysql 5.7支持 LIST COLUMNS分区，这是LIST分区的一种变体，它允许使用多列作为分区键, 以及用非整数类型的列作为分区列的类型，您可以使用string类型、 DATE和DATETIME列。

下面我们来一个案例：

mysql> CREATE TABLE test3(

-> id int not null auto_increment,

-> key_a int not null default 0,

-> key_b int not null default 0,

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

-> )engine=innodb charset=utf8

-> PARTITION BY LIST COLUMNS(key_a) (

-> PARTITION p0 VALUES IN(1, 2,3,4,5),

-> PARTITION p1 VALUES IN(6, 7,8,9,10),

-> PARTITION p2 VALUES IN(11,12,13,14, 15)

-> );

这个LIST COLUMNS分区，我目前感觉跟LIST分区没什么两样，最多就是可以string,DATE和DATETIME列分区，根本就看不出多列的感觉，远远没有RANGE COLUMNS分区和RANGE分区的效果差异

HASH分区

通过HASH分区主要用于确保预定数量的分区之间的数据的均匀分布。

使用range或list分区，您必须明确指定要存储给定列值或列值集合的分区; 使用hash分区，MySQL会为您处理此问题，并且您只需根据要进行hash的列值和分区表的分区数量指定列值或表达式即可。

使用HASH分区来对表进行分区，需要在 CREATE TABLE语句上面追加一个PARTITION BY HASH (expr)子句。

expr是一个表达式，返回一个整数，这也可以简单是一个列的名称, 其类型是 MySQL 的整数类型之一。

另外，您很可能希望使用PARTITIONS num来执行这个操作，其中num是一个正整数，表示表要划分的分区数。

下面我们开始一个案例

mysql> CREATE TABLE test4(

-> id int not null auto_increment,

-> key_a int not null default 0,

-> key_b int not null default 0,

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

-> )engine=innodb charset=utf8

-> PARTITION BY HASH(key_a)

-> PARTITIONS 4;

Query OK, 0 rows affected (0.28 sec)

如果不包含PARTITIONS子句，那么分区的数量默认为1。使用PARTITIONS关键字而不使用数字，会导致语法错误。

哈希分区是均匀分布的，那么我们测试一下，先添加一万条数据看一下,直接把RANGE分区下的存储过程改下，具体改成什么样我就不发了，然后调用一下插入了一万条数据，

mysql> SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = ‘test4’;

+—————-+————+

| PARTITION_NAME | TABLE_ROWS |

+—————-+————+

| p0 | 3039 |

| p1 | 3003 |

| p2 | 2012 |

| p3 | 1946 |

+—————-+————+

4 rows in set (0.02 sec)

感觉差异有点大，我们尝试着再插入十万条数据看一下

mysql> SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = ‘test4’;

+—————-+————+

| PARTITION_NAME | TABLE_ROWS |

+—————-+————+

| p0 | 28017 |

| p1 | 27469 |

| p2 | 27016 |

| p3 | 26785 |

+—————-+————+

4 rows in set (0.00 sec)

这个时候我们可以看到每个分区的条件基本相差不是很大了。

KEY分区

key分区与通过hash分区类似，不同之处在于hash分区采用用户定义的表达式，用于key分区的hash函数由MySQL服务器提供。

NDB集群为此目的使用MD5();对于使用其他存储引擎的表，服务器使用它自己的内部哈希函数，该函数基于与PASSWORD()相同的算法。

CREATE TABLE … PARTITION BY KEY 的语法规则类似于创建一个由散列分区的表。主要区别在这里:

使用KEY而不是HASH
KEY仅包含零个或多个列名称的列表。作为分区键的任何列必须包含表的主键的部分或全部，如果该表有一个。如果没有指定列名称作为分区键，如果有主键，则使用表的主键。
如果没有主键，但有一个唯一键，那么分区键使用唯一键，但是，如果惟一键列没有定义为 NOT NULL，那么就会创建分区失败

与其他分区类型不同，用于按KEY分区的列不限于整数或NULL值。例如下面的create_table语句是有效的

mysql> create table tm1(
-> s1 char(32) primary key
-> )partition by key(s1) partitions 10
-> ;
Query OK, 0 rows affected (0.16 sec)

针对key分区表，你不能执行ALTER TABLE DROP PRIMARY KEY，如果执行了会报一个错误 ERROR 1466 (HY000): Field in list of fields for partition function not found in table.

下面我们看一下如何创建

mysql> create table test5(

-> id char(11) not null primary key,

-> add_time int not null default 0

-> )engine=innodb charset=utf8

-> PARTITION BY LINEAR KEY ()

-> PARTITIONS 3;

如何使用分区表

在数据量超大的时候，B-Tree索引就无法起作用了。除非是索引覆盖查询，否则数据库服务器需要根据索引扫描的结果回表，查询所有符合条件的记录，如果数据量巨大，这将产生大量随机I/O，随之，数据库的响应时间将大到不可接受的程度。

另外，索引维护（磁盘空间、I/O操作）的代价也非常高。有些系统，如Infobright，意识到这一点，于是就完全放弃使用B-Tree索引，而选择了一些更粗粒度的但消耗更少的方式检索数据，例如在大量数据上只索引对应的一小块元数据。

而这正是分区要做的事情。理解分区时还可以将其当作索引的最初形态，以代价非常小的方式定位到需要的数据在哪一片“区域”。在这片“区域”中，你可以做顺序扫描，可以建索引，还可以将数据都缓存到内存，等等。

因为分区无须额外的数据结构记录每个分区有哪些数据——分区不需要精确定位每条数据的位置，也就无须额外的数据结构——所以其代价非常低。只需要一个简单的表达式就可以表达每个分区存放的是什么数据。

为了保证大数据量的可扩展性，一般有下面两个策略：

1：全量扫描数据，不要任何索引

可以使用简单的分区方式存放表，不要任何索引，根据分区的规则大致定位需要的数据位置。只要能够使用WHERE条件，将需要的数据限制在少数分区中，则效率是很高的。

2：索引数据，并分离热点

如果数据有明显的“热点”,而且除了这部分数据，其他数据很少被访问到，那么可以将这部分热点数据单独放在一个分区中，让这个分区的数据能够有机会都缓存在内存中。这样查询就可以只访问一个很小的分区表，能够使用索引，也能够有效地使用缓存。

什么情况下会出问题

1：NULL值会使分区过滤无效

关于分区表一个容易让人误解的地方就是分区的表达式的值可以是NULL：第一个分区是一个特殊分区。假设按照PARTITION BY RANGE YEAR（order_date）分区，那么所有order_date为NULL或者是一个非法值的时候，记录都会被存放到第一个分区。

2：分区列和索引列不匹配

如果定义的索引列和分区列不匹配，会导致查询无法进行分区过滤。假设在列a上定义了索引，而在列b上进行分区。因为每个分区都有其独立的索引，所以扫描列b上的索引就需要扫描每一个分区内对应的索引。如果每个分区内对应索引的非叶子节点都在内存中，那么扫描的速度还可以接受，但如果能跳过某些分区索引当然会更好。要避免这个问题，应该避免建立和分区列不匹配的索引，除非查询中还同时包含了可以过滤分区的条件。

3：打开并锁住所有底层表的成本可能很高

当查询访问分区表的时候，MySQL需要打开并锁住所有的底层表，这是分区表的另一个开销。这个操作在分区过滤之前发生，所以无法通过分区过滤降低此开销，并且该开销也和分区类型无关，会影响所有的查询。这一点对一些本身操作非常快的查询，比如根据主键查找单行，会带来明显的额外开销。可以用批量操作的方式来降低单个操作的此类开销，例如使用批量插入或者LOAD DATA INFILE、一次删除多行数据，等等。当然同时还是需要限制分区的个数。

查询优化

引入分区给查询优化带来了一些新的思路（同时也带来新的bug）。分区最大的优点就是优化器可以根据分区函数来过滤一些分区。根据粗粒度索引的优势，通过分区过滤通常可以让查询扫描更少的数据（在某些场景下）。

所以，对于访问分区表来说，很重要的一点是要在WHERE条件中带入分区列，有时候即使看似多余的也要带上，这样就可以让优化器能够过滤掉无须访问的分区。

参考资料：

https://dev.mysql.com/doc/refman/5.7/en/partitioning.html

《高性能MySQL》

分区表

相关文章

衡量SQL查询的三个指标

如何删除大表

备份与恢复

字符集设置

数据类型：JSON

发表评论取消回复

相关文章

衡量SQL查询的三个指标

如何删除大表

备份与恢复

字符集设置

数据类型：JSON

发表评论 取消回复

发表评论取消回复