Description:
Currently if an auto increment field is part of the partition expression, the optimizer won't do partition pruning when inserting values. But actually, if the values of auto increment field are explicitly assigned in all insert rows and they are neither NULL nor 0(not with NO_AUTO_VALUE_ON_ZERO sql_mode), we can do partition pruning to improve performance.
This is also a TODO thing left by the developer in function partition_info::can_prune_insert():
if (table->found_next_number_field) {
/*
If the field is used in the partitioning expression, we cannot prune.
TODO: If all rows have not null values and
is not 0 (with NO_AUTO_VALUE_ON_ZERO sql_mode), then pruning is possible!
*/
if (bitmap_is_set(&full_part_field_set,
table->found_next_number_field->field_index()))
return false;
How to repeat:
CREATE TABLE `sbtest1` (
`id` int NOT NULL AUTO_INCREMENT,
`k` int NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB
PARTITION BY RANGE (`id`)
(PARTITION p0 VALUES LESS THAN (1001) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (2001) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (3001) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (4001) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (5001) ENGINE = InnoDB);
mysql-8.0.30 > EXPLAIN INSERT INTO sbtest1 VALUES (5, 10, 'aaz', 'kox'), (100, 92, 'hope', 'carmen');
+----+-------------+---------+----------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+----------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | INSERT | sbtest1 | p0,p1,p2,p3,p4 | ALL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+----+-------------+---------+----------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
We know the above two records will be inserted to partition p0, but the explained result shows this SQL will involve all partitions.
Suggested fix:
Apply partition pruning when all rows have non-null values on the auto increment field. See the following patch.
Description: Currently if an auto increment field is part of the partition expression, the optimizer won't do partition pruning when inserting values. But actually, if the values of auto increment field are explicitly assigned in all insert rows and they are neither NULL nor 0(not with NO_AUTO_VALUE_ON_ZERO sql_mode), we can do partition pruning to improve performance. This is also a TODO thing left by the developer in function partition_info::can_prune_insert(): if (table->found_next_number_field) { /* If the field is used in the partitioning expression, we cannot prune. TODO: If all rows have not null values and is not 0 (with NO_AUTO_VALUE_ON_ZERO sql_mode), then pruning is possible! */ if (bitmap_is_set(&full_part_field_set, table->found_next_number_field->field_index())) return false; How to repeat: CREATE TABLE `sbtest1` ( `id` int NOT NULL AUTO_INCREMENT, `k` int NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`) ) ENGINE=InnoDB PARTITION BY RANGE (`id`) (PARTITION p0 VALUES LESS THAN (1001) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (2001) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (3001) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (4001) ENGINE = InnoDB, PARTITION p4 VALUES LESS THAN (5001) ENGINE = InnoDB); mysql-8.0.30 > EXPLAIN INSERT INTO sbtest1 VALUES (5, 10, 'aaz', 'kox'), (100, 92, 'hope', 'carmen'); +----+-------------+---------+----------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+----------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | INSERT | sbtest1 | p0,p1,p2,p3,p4 | ALL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +----+-------------+---------+----------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) We know the above two records will be inserted to partition p0, but the explained result shows this SQL will involve all partitions. Suggested fix: Apply partition pruning when all rows have non-null values on the auto increment field. See the following patch.