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

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)

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