mirror of
https://github.com/MariaDB/server.git
synced 2025-04-05 14:55:32 +02:00
1965b2be16
If a query has many OR-ed constructs which can use multiple indexes (key1=1 AND key2=10) OR (key1=2 AND key2=20) OR (key1=3 AND key2=30) OR ... The range optimizer would construct and then discard a lot of potential index_merge plans. This process 1. is CPU-intensive 2. can hit the @@optimizer_max_sel_args limitation after which all potential range or index_merge plans are discarded. The fix is to apply a heuristic: if there is an OR clause with more than MAX_OR_ELEMENTS_FOR_INDEX_MERGE=100 branches (hard-coded constant), disallow construction of index_merge plans for the OR branches.
316 lines
12 KiB
Text
316 lines
12 KiB
Text
drop table if exists t1,t2;
|
|
#
|
|
# MDEV-21958: Query having many NOT-IN clauses running forever
|
|
#
|
|
create table t2 (
|
|
pk int primary key,
|
|
key1 int,
|
|
col1 int,
|
|
key (key1, pk)
|
|
);
|
|
insert into t2 (pk, key1) values (1,1),(2,2),(3,3),(4,4),(5,5);
|
|
set @tmp_21958=@@optimizer_trace;
|
|
set optimizer_trace=1;
|
|
explain select * from t2 where key1 in (1,2,3) and pk not in (1,2,3);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t2 range PRIMARY,key1 key1 5 NULL 3 Using index condition
|
|
# This should show only ranges in form "(1) <= (key1) <= (1)"
|
|
# ranges over "pk" should not be constructed.
|
|
select json_detailed(JSON_EXTRACT(trace, '$**.ranges'))
|
|
from information_schema.optimizer_trace;
|
|
json_detailed(JSON_EXTRACT(trace, '$**.ranges'))
|
|
[
|
|
[
|
|
"(1) <= (key1) <= (1)",
|
|
"(2) <= (key1) <= (2)",
|
|
"(3) <= (key1) <= (3)"
|
|
],
|
|
[
|
|
"(1) <= (key1) <= (1)",
|
|
"(2) <= (key1) <= (2)",
|
|
"(3) <= (key1) <= (3)"
|
|
]
|
|
]
|
|
set optimizer_trace=@tmp_21958;
|
|
drop table t2;
|
|
#
|
|
# MDEV-9750: Quick memory exhaustion with 'extended_keys=on'...
|
|
#
|
|
create table t1 (
|
|
kp1 int,
|
|
kp2 int,
|
|
kp3 int,
|
|
kp4 int,
|
|
key key1(kp1, kp2, kp3,kp4)
|
|
);
|
|
insert into t1 values (1,1,1,1),(2,2,2,2),(3,3,3,3);
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
show variables like 'optimizer_max_sel_arg_weight';
|
|
Variable_name Value
|
|
optimizer_max_sel_arg_weight 32000
|
|
set @tmp_9750=@@optimizer_trace;
|
|
set optimizer_trace=1;
|
|
explain select * from t1 where
|
|
kp1 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) and
|
|
kp2 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) and
|
|
kp3 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) and
|
|
kp4 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20)
|
|
;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index key1 key1 20 NULL 3 Using where; Using index
|
|
set @json= (select json_detailed(JSON_EXTRACT(trace, '$**.range_scan_alternatives'))
|
|
from information_schema.optimizer_trace);
|
|
# This will show 3-component ranges.
|
|
# The ranges were produced, but the optimizer has cut away kp4
|
|
# to keep the number of ranges at manageable level:
|
|
select left(@json, 500);
|
|
left(@json, 500)
|
|
[
|
|
[
|
|
{
|
|
"index": "key1",
|
|
"ranges":
|
|
[
|
|
"(1,1,1) <= (kp1,kp2,kp3) <= (1,1,1)",
|
|
"(1,1,2) <= (kp1,kp2,kp3) <= (1,1,2)",
|
|
"(1,1,3) <= (kp1,kp2,kp3) <= (1,1,3)",
|
|
"(1,1,4) <= (kp1,kp2,kp3) <= (1,1,4)",
|
|
"(1,1,5) <= (kp1,kp2,kp3) <= (1,1,5)",
|
|
"(1,1,6) <= (kp1,kp2,kp3) <= (1,1,6)",
|
|
"(1,1,7) <= (kp1,kp2,kp3) <= (1,1,7)",
|
|
"(1,1,8) <= (kp
|
|
## Repeat the above with low max_weight:
|
|
set @tmp9750_weight=@@optimizer_max_sel_arg_weight;
|
|
set optimizer_max_sel_arg_weight=20;
|
|
explain select * from t1 where
|
|
kp1 in (1,2,3,4,5,6,7,8,9,10) and
|
|
kp2 in (1,2,3,4,5,6,7,8,9,10) and
|
|
kp3 in (1,2,3,4,5,6,7,8,9,10) and
|
|
kp4 in (1,2,3,4,5,6,7,8,9,10)
|
|
;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index key1 key1 20 NULL 3 Using where; Using index
|
|
set @trace= (select trace from information_schema.optimizer_trace);
|
|
set @json= json_detailed(json_extract(@trace, '$**.range_scan_alternatives'));
|
|
select left(@json, 500);
|
|
left(@json, 500)
|
|
[
|
|
[
|
|
{
|
|
"index": "key1",
|
|
"ranges":
|
|
[
|
|
"(1) <= (kp1) <= (1)",
|
|
"(2) <= (kp1) <= (2)",
|
|
"(3) <= (kp1) <= (3)",
|
|
"(4) <= (kp1) <= (4)",
|
|
"(5) <= (kp1) <= (5)",
|
|
"(6) <= (kp1) <= (6)",
|
|
"(7) <= (kp1) <= (7)",
|
|
"(8) <= (kp1) <= (8)",
|
|
"(9) <= (kp1) <= (9)",
|
|
"(10) <= (kp1) <= (10)"
|
|
],
|
|
|
|
set @json= json_detailed(json_extract(@trace, '$**.setup_range_conditions'));
|
|
select left(@json, 2500);
|
|
left(@json, 2500)
|
|
[
|
|
[
|
|
{
|
|
"sel_arg_weight_heuristic":
|
|
{
|
|
"key1_field": "kp1",
|
|
"key2_field": "kp2",
|
|
"key1_weight": 10,
|
|
"key2_weight": 10
|
|
}
|
|
},
|
|
{
|
|
"sel_arg_weight_heuristic":
|
|
{
|
|
"key1_field": "kp1",
|
|
"key2_field": "kp3",
|
|
"key1_weight": 10,
|
|
"key2_weight": 10
|
|
}
|
|
},
|
|
{
|
|
"sel_arg_weight_heuristic":
|
|
{
|
|
"key1_field": "kp1",
|
|
"key2_field": "kp4",
|
|
"key1_weight": 10,
|
|
"key2_weight": 10
|
|
}
|
|
}
|
|
]
|
|
]
|
|
## Repeat the above with a bit higher max_weight:
|
|
set optimizer_max_sel_arg_weight=120;
|
|
explain select * from t1 where
|
|
kp1 in (1,2,3,4,5,6,7,8,9,10) and
|
|
kp2 in (1,2,3,4,5,6,7,8,9,10) and
|
|
kp3 in (1,2,3,4,5,6,7,8,9,10) and
|
|
kp4 in (1,2,3,4,5,6,7,8,9,10)
|
|
;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index key1 key1 20 NULL 3 Using where; Using index
|
|
set @json= (select json_detailed(JSON_EXTRACT(trace, '$**.range_scan_alternatives'))
|
|
from information_schema.optimizer_trace);
|
|
select left(@json, 1500);
|
|
left(@json, 1500)
|
|
[
|
|
[
|
|
{
|
|
"index": "key1",
|
|
"ranges":
|
|
[
|
|
"(1,1) <= (kp1,kp2) <= (1,1)",
|
|
"(1,2) <= (kp1,kp2) <= (1,2)",
|
|
"(1,3) <= (kp1,kp2) <= (1,3)",
|
|
"(1,4) <= (kp1,kp2) <= (1,4)",
|
|
"(1,5) <= (kp1,kp2) <= (1,5)",
|
|
"(1,6) <= (kp1,kp2) <= (1,6)",
|
|
"(1,7) <= (kp1,kp2) <= (1,7)",
|
|
"(1,8) <= (kp1,kp2) <= (1,8)",
|
|
"(1,9) <= (kp1,kp2) <= (1,9)",
|
|
"(1,10) <= (kp1,kp2) <= (1,10)",
|
|
"(2,1) <= (kp1,kp2) <= (2,1)",
|
|
"(2,2) <= (kp1,kp2) <= (2,2)",
|
|
"(2,3) <= (kp1,kp2) <= (2,3)",
|
|
"(2,4) <= (kp1,kp2) <= (2,4)",
|
|
"(2,5) <= (kp1,kp2) <= (2,5)",
|
|
"(2,6) <= (kp1,kp2) <= (2,6)",
|
|
"(2,7) <= (kp1,kp2) <= (2,7)",
|
|
"(2,8) <= (kp1,kp2) <= (2,8)",
|
|
"(2,9) <= (kp1,kp2) <= (2,9)",
|
|
"(2,10) <= (kp1,kp2) <= (2,10)",
|
|
"(3,1) <= (kp1,kp2) <= (3,1)",
|
|
"(3,2) <= (kp1,kp2) <= (3,2)",
|
|
"(3,3) <= (kp1,kp2) <= (3,3)",
|
|
"(3,4) <= (kp1,kp2) <= (3,4)",
|
|
"(3,5) <= (kp1,kp2) <= (3,5)",
|
|
"(3,6) <= (kp1,kp2) <= (3,6)",
|
|
"(3,7) <= (kp1,kp2) <= (3,7)",
|
|
"(3,8) <= (kp1,kp2) <= (3,8)",
|
|
"(3,9) <= (kp1,kp2) <= (3,9)",
|
|
"(3,10) <= (kp1,kp2) <= (3,10)",
|
|
|
|
set optimizer_max_sel_arg_weight= @tmp9750_weight;
|
|
set optimizer_trace=@tmp_9750;
|
|
drop table t1;
|
|
#
|
|
# MDEV-24739: Assertion `root->weight >= ...' failed in SEL_ARG::tree_delete
|
|
#
|
|
SELECT *
|
|
FROM mysql.help_relation
|
|
WHERE NOT (help_topic_id != 8 AND help_keyword_id != 0 OR help_keyword_id = 2 OR help_topic_id < 1900);
|
|
help_topic_id help_keyword_id
|
|
SELECT *
|
|
FROM mysql.help_relation ignore index (help_topic_id)
|
|
WHERE (help_topic_id = 8 OR help_keyword_id = 0) AND help_keyword_id != 2 AND help_topic_id >= 1900;
|
|
help_topic_id help_keyword_id
|
|
#
|
|
# MDEV-24953: 10.5.9 crashes with large IN() list
|
|
#
|
|
CREATE TABLE t1 (
|
|
notification_type_id smallint(4) unsigned NOT NULL DEFAULT 0,
|
|
item_id int(10) unsigned NOT NULL DEFAULT 0,
|
|
item_parent_id int(10) unsigned NOT NULL DEFAULT 0,
|
|
user_id int(10) unsigned NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (notification_type_id,item_id,item_parent_id,user_id)
|
|
);
|
|
insert into t1 values (1,1,1,1), (2,2,2,2), (3,3,3,3);
|
|
# Run crashing query
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range PRIMARY PRIMARY 2 NULL 3 Using where
|
|
drop table t1;
|
|
#
|
|
# MDEV-25069: Assertion `root->weight >= ...' failed in SEL_ARG::tree_delete #2
|
|
#
|
|
SELECT *
|
|
FROM mysql.help_relation
|
|
WHERE
|
|
(help_topic_id < '2' OR help_topic_id != 8 OR help_topic_id < 1) AND
|
|
help_keyword_id = help_topic_id;
|
|
help_topic_id help_keyword_id
|
|
#
|
|
# MDEV-29242: Assertion `computed_weight == weight' failed SEL_ARG::verify_weight
|
|
#
|
|
CREATE TABLE t1 (id INT, KEY (id));
|
|
INSERT INTO t1 VALUES (1),(5);
|
|
SELECT id FROM t1 WHERE id IS NULL OR id NOT BETWEEN 1 AND 4;
|
|
id
|
|
5
|
|
DROP TABLE t1;
|
|
#
|
|
# MDEV-34620: Many index_merge variants made and discarded for a big OR
|
|
#
|
|
CREATE TABLE t1 (
|
|
a1 int NOT NULL,
|
|
a2 int NOT NULL,
|
|
filler char(100),
|
|
KEY key1 (a1,a2),
|
|
KEY key2 (a2,a1)
|
|
);
|
|
insert into t1 (a1,a2) values (1,1),(2,2),(3,3);
|
|
set @query= concat(
|
|
"explain select * from t1 where\n",
|
|
(select
|
|
group_concat(concat("a1=", seq, " and a2=", seq, " ") separator "\nor " )
|
|
from seq_1_to_30)
|
|
);
|
|
set optimizer_trace=1;
|
|
prepare s from @query;
|
|
execute s;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL key1,key2 NULL NULL NULL 3 Using where
|
|
set @trace=json_extract((select trace from information_schema.optimizer_trace), '$**.range_analysis');
|
|
# Observe that "key1" is a a part of several index_merge_union:
|
|
select json_pretty(json_search(@trace, 'all', 'key1'));
|
|
json_pretty(json_search(@trace, 'all', 'key1'))
|
|
[
|
|
"$[0].potential_range_indexes[0].index",
|
|
"$[0].analyzing_range_alternatives.range_scan_alternatives[0].index",
|
|
"$[0].analyzing_range_alternatives.analyzing_index_merge_union[0].indexes_to_merge[0].range_scan_alternatives[0].index",
|
|
"$[0].analyzing_range_alternatives.analyzing_index_merge_union[0].indexes_to_merge[0].index_to_merge",
|
|
"$[0].analyzing_range_alternatives.analyzing_index_merge_union[0].indexes_to_merge[1].range_scan_alternatives[0].index",
|
|
"$[0].analyzing_range_alternatives.analyzing_index_merge_union[0].indexes_to_merge[1].index_to_merge",
|
|
"$[0].analyzing_range_alternatives.analyzing_index_merge_union[1].indexes_to_merge[0].range_scan_alternatives[0].index",
|
|
"$[0].analyzing_range_alternatives.analyzing_index_merge_union[1].indexes_to_merge[0].index_to_merge",
|
|
"$[0].analyzing_range_alternatives.analyzing_index_merge_union[1].indexes_to_merge[1].range_scan_alternatives[0].index",
|
|
"$[0].analyzing_range_alternatives.analyzing_index_merge_union[1].indexes_to_merge[1].index_to_merge",
|
|
"$[0].analyzing_range_alternatives.analyzing_index_merge_union[1].indexes_to_merge[2].range_scan_alternatives[0].index",
|
|
"$[0].analyzing_range_alternatives.analyzing_index_merge_union[1].indexes_to_merge[2].index_to_merge",
|
|
"$[0].analyzing_range_alternatives.analyzing_index_merge_union[2].indexes_to_merge[0].range_scan_alternatives[0].index",
|
|
"$[0].analyzing_range_alternatives.analyzing_index_merge_union[2].indexes_to_merge[0].index_to_merge",
|
|
"$[0].analyzing_range_alternatives.analyzing_index_merge_union[2].indexes_to_merge[1].range_scan_alternatives[0].index",
|
|
"$[0].analyzing_range_alternatives.analyzing_index_merge_union[2].indexes_to_merge[1].index_to_merge"
|
|
]
|
|
#
|
|
# Now, same as above but for a long IN-list
|
|
#
|
|
set @query= concat(
|
|
"explain select * from t1 where\n",
|
|
(select
|
|
group_concat(concat("a1=", seq, " and a2=", seq, " ") separator "\nor " )
|
|
from seq_1_to_120)
|
|
);
|
|
set optimizer_trace=1;
|
|
prepare s from @query;
|
|
execute s;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL key1,key2 NULL NULL NULL 3 Using where
|
|
set @trace=json_extract((select trace from information_schema.optimizer_trace), '$**.range_analysis');
|
|
# Observe that there are NO index_merge_union candidates. Only one potential range scan:
|
|
select json_pretty(json_search(@trace, 'all', 'key1'));
|
|
json_pretty(json_search(@trace, 'all', 'key1'))
|
|
[
|
|
"$[0].potential_range_indexes[0].index",
|
|
"$[0].analyzing_range_alternatives.range_scan_alternatives[0].index"
|
|
]
|
|
drop table t1;
|