mysql多列分区表,查询只使用第二个字段是否能自动分区裁剪测试

本文介绍了一个MySQL中的分区表tb2的设计及其使用情况。该表通过id和name进行范围分区,并探讨了不同条件查询下分区裁剪的效果。文章还展示了如何通过SQL语句查询分区信息及数据。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

CREATE TABLE `tb2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) DEFAULT NULL,
  `name` varchar(20) not null,
  `birthday` date DEFAULT NULL,
  PRIMARY KEY (id,name),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 PARTITION BY RANGE COLUMNS(id,name)
(
PARTITION p1 VALUES LESS THAN(1000,'ab'),
PARTITION p2 VALUES LESS THAN(10000,'ef'),
PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE));

向tb2里面插入一些测试数据

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'tb2';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p1             |        996 |
| p2             |       9000 |
| p3             |     204907 |
+----------------+------------+
3 rows in set (0.00 sec)

mysql> explain partitions select count(*) from tb2 where id=2;
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | tb2   | p1         | ref  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)


mysql>  select * from tb2 where id=2;
+----+--------+------------+------------+
| id | userid | name       | birthday   |
+----+--------+------------+------------+
|  2 | 144834 | UjnoZmAncn | 1988-11-03 |
+----+--------+------------+------------+
1 row in set (0.00 sec)

mysql> explain partitions select count(*) from tb2 where name='UjnoZmAncn';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | tb2   | p1,p2,p3   | ref  | idx_name      | idx_name | 62      | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)

mysql>  select count(*) from tb2 where name='UjnoZmAncn';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

看到只是根据分区字段的第二个字段查询是无法做到自动分区裁剪的。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值