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

最新推荐文章于 2024-09-22 18:06:26 发布

aoerqileng

最新推荐文章于 2024-09-22 18:06:26 发布

阅读量2k

点赞数

CC 4.0 BY-SA版权

分类专栏： MYSQL

本文链接：https://blog.youkuaiyun.com/aoerqileng/article/details/77448105

MYSQL 专栏收录该内容

274 篇文章

订阅专栏

本文介绍了一个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)