mirror of
https://github.com/MariaDB/server.git
synced 2025-04-06 07:15:33 +02:00
43c5d1303f
Backport of commit 74f70c3944 to 10.11.
The new logic is disabled by default, to enable, use
optimizer_adjust_secondary_key_costs=fix_derived_table_read_cost.
== Original commit comment ==
Fixed costs in JOIN_TAB::estimate_scan_time() and HEAP
Estimate_scan_time() calculates the cost of scanning a derivied table.
The old code did not take into account that the temporary table heap table
may be converted to Aria.
Things fixed:
- Added checking if the temporary tables data will fit in the heap.
If not, then calculate the cost based on the designated internal
temporary table engine (Aria).
- Removed MY_MAX(records, 1000) and instead trust the optimizer's
estimate of records. This reduces the cost of temporary tables a bit
for small tables, which caused a few changes in mtr results.
- Fixed cost calculation for HEAP.
- HEAP costs->row_next_find_cost was not set. This does not affect old
costs calculation as this cost slot was not used anywhere.
Now HEAP cost->row_next_find_cost is set, which allowed me to remove
some duplicated computation in ha_heap::scan_time()
256 lines
8.8 KiB
Text
256 lines
8.8 KiB
Text
create table t1 (
|
|
pk int primary key auto_increment,
|
|
nm varchar(32),
|
|
fl1 tinyint default 0,
|
|
fl2 tinyint default 0,
|
|
index idx1(nm, fl1),
|
|
index idx2(fl2)
|
|
) engine=myisam;
|
|
create table name (
|
|
pk int primary key auto_increment,
|
|
nm bigint
|
|
) engine=myisam;
|
|
create table flag2 (
|
|
pk int primary key auto_increment,
|
|
fl2 tinyint
|
|
) engine=myisam;
|
|
insert into name(nm) select seq from seq_1_to_1000 order by rand(17);
|
|
insert into flag2(fl2) select seq mod 2 from seq_1_to_1000 order by rand(19);
|
|
insert into t1(nm,fl2)
|
|
select nm, fl2 from name, flag2 where name.pk = flag2.pk;
|
|
analyze table t1 persistent for all;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status Table is already up to date
|
|
set optimizer_trace="enabled=on";
|
|
set optimizer_switch='rowid_filter=on';
|
|
set statement optimizer_adjust_secondary_key_costs=0 for
|
|
explain select * from t1 where nm like '500%' AND fl2 = 0;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range idx1,idx2 idx1 35 NULL 1 Using index condition; Using where
|
|
set @trace=(select trace from information_schema.optimizer_trace);
|
|
select json_detailed(json_extract(@trace, '$**.considered_access_paths'));
|
|
json_detailed(json_extract(@trace, '$**.considered_access_paths'))
|
|
[
|
|
[
|
|
{
|
|
"access_type": "ref",
|
|
"index": "idx2",
|
|
"used_range_estimates": true,
|
|
"rowid_filter_skipped": "worst/max seeks clipping",
|
|
"rows": 492,
|
|
"cost": 492.3171406,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"access_type": "range",
|
|
"resulting_rows": 0.492,
|
|
"cost": 1.448699097,
|
|
"chosen": true
|
|
}
|
|
]
|
|
]
|
|
|
|
The following trace should have a different rowid_filter_key cost
|
|
|
|
set statement optimizer_adjust_secondary_key_costs=2 for
|
|
explain select * from t1 where nm like '500%' AND fl2 = 0;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range idx1,idx2 idx1 35 NULL 1 Using index condition; Using where
|
|
set @trace=(select trace from information_schema.optimizer_trace);
|
|
select json_detailed(json_extract(@trace, '$**.considered_access_paths'));
|
|
json_detailed(json_extract(@trace, '$**.considered_access_paths'))
|
|
[
|
|
[
|
|
{
|
|
"access_type": "ref",
|
|
"index": "idx2",
|
|
"used_range_estimates": true,
|
|
"rowid_filter_key": "idx1",
|
|
"rows": 492,
|
|
"cost": 3.814364688,
|
|
"chosen": true
|
|
},
|
|
{
|
|
"access_type": "range",
|
|
"resulting_rows": 0.492,
|
|
"cost": 1.448699097,
|
|
"chosen": true
|
|
}
|
|
]
|
|
]
|
|
drop table t1, name, flag2;
|
|
select @@optimizer_adjust_secondary_key_costs;
|
|
@@optimizer_adjust_secondary_key_costs
|
|
fix_reuse_range_for_ref,fix_card_multiplier
|
|
set @@optimizer_adjust_secondary_key_costs=7;
|
|
select @@optimizer_adjust_secondary_key_costs;
|
|
@@optimizer_adjust_secondary_key_costs
|
|
adjust_secondary_key_cost,disable_max_seek,disable_forced_index_in_group_by
|
|
set @@optimizer_adjust_secondary_key_costs=default;
|
|
#
|
|
# MDEV-33306 Optimizer choosing incorrect index in 10.6, 10.5 but not in 10.4
|
|
#
|
|
create table t1 (a int primary key, b int, c int, d int, key(b),key(c)) engine=innodb;
|
|
insert into t1 select seq, mod(seq,10), mod(seq,2), seq from seq_1_to_50000;
|
|
explain select b, sum(d) from t1 where c=0 group by b;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index c b 5 NULL # Using where
|
|
select b, sum(d) from t1 where c=0 group by b;
|
|
b sum(d)
|
|
0 125025000
|
|
2 124985000
|
|
4 124995000
|
|
6 125005000
|
|
8 125015000
|
|
set @@optimizer_adjust_secondary_key_costs="disable_forced_index_in_group_by";
|
|
explain select b, sum(d) from t1 where c=0 group by b;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL c NULL NULL NULL # Using where; Using temporary; Using filesort
|
|
select b, sum(d) from t1 where c=0 group by b;
|
|
b sum(d)
|
|
0 125025000
|
|
2 124985000
|
|
4 124995000
|
|
6 125005000
|
|
8 125015000
|
|
drop table t1;
|
|
#
|
|
# MDEV-34664: fix_innodb_cardinality
|
|
#
|
|
set @save_userstat=@@global.userstat;
|
|
set @save_ispsp=@@global.innodb_stats_persistent_sample_pages;
|
|
set @@global.innodb_stats_persistent_sample_pages=20;
|
|
set @@global.userstat=on;
|
|
set use_stat_tables=PREFERABLY_FOR_QUERIES;
|
|
create or replace table t1 (a int primary key, b int, c int, d int, key(b,c,d)) engine=innodb;
|
|
insert into t1 select seq,seq/100,seq/60,seq/10 from seq_1_to_1000;
|
|
create or replace table t2 (a int);
|
|
insert into t2 values (1),(2),(3);
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
select count(distinct b),count(distinct b,c), count(distinct b,c,d) from t1;
|
|
count(distinct b) count(distinct b,c) count(distinct b,c,d)
|
|
11 25 125
|
|
show index from t1;
|
|
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
|
|
t1 0 PRIMARY 1 a A 1000 NULL NULL BTREE NO
|
|
t1 1 b 1 b A 22 NULL NULL YES BTREE NO
|
|
t1 1 b 2 c A 50 NULL NULL YES BTREE NO
|
|
t1 1 b 3 d A 250 NULL NULL YES BTREE NO
|
|
explain select * from t1,t2 where t1.b=t2.a;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where
|
|
1 SIMPLE t1 ref b b 5 test.t2.a 45 Using index
|
|
set @@optimizer_adjust_secondary_key_costs=8;
|
|
explain select * from t1,t2 where t1.b=t2.a;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where
|
|
1 SIMPLE t1 ref b b 5 test.t2.a 45 Using index
|
|
show index from t1;
|
|
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
|
|
t1 0 PRIMARY 1 a A 1000 NULL NULL BTREE NO
|
|
t1 1 b 1 b A 11 NULL NULL YES BTREE NO
|
|
t1 1 b 2 c A 25 NULL NULL YES BTREE NO
|
|
t1 1 b 3 d A 125 NULL NULL YES BTREE NO
|
|
flush tables;
|
|
explain select * from t1,t2 where t1.b=t2.a;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where
|
|
1 SIMPLE t1 ref b b 5 test.t2.a 90 Using index
|
|
show index from t1;
|
|
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
|
|
t1 0 PRIMARY 1 a A 1000 NULL NULL BTREE NO
|
|
t1 1 b 1 b A 11 NULL NULL YES BTREE NO
|
|
t1 1 b 2 c A 25 NULL NULL YES BTREE NO
|
|
t1 1 b 3 d A 125 NULL NULL YES BTREE NO
|
|
connect user2, localhost, root,,;
|
|
show index from t1;
|
|
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
|
|
t1 0 PRIMARY 1 a A 1000 NULL NULL BTREE NO
|
|
t1 1 b 1 b A 22 NULL NULL YES BTREE NO
|
|
t1 1 b 2 c A 50 NULL NULL YES BTREE NO
|
|
t1 1 b 3 d A 250 NULL NULL YES BTREE NO
|
|
connection default;
|
|
disconnect user2;
|
|
drop table t1,t2;
|
|
set global userstat=@save_userstat;
|
|
set global innodb_stats_persistent_sample_pages=@save_ispsp;
|
|
#
|
|
# MDEV-35958: Cost estimates for materialized derived tables are poor
|
|
#
|
|
set optimizer_trace=1;
|
|
create table t1 (
|
|
a int
|
|
);
|
|
insert into t1 select seq from seq_1_to_10000;
|
|
explain
|
|
select *
|
|
from
|
|
t1 as t1_base,
|
|
(select a from t1 limit 10000) as TBL;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t1_base ALL NULL NULL NULL NULL 10000
|
|
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 10000 Using join buffer (flat, BNL join)
|
|
2 DERIVED t1 ALL NULL NULL NULL NULL 10000
|
|
set @trace=(select trace from information_schema.optimizer_trace);
|
|
# BEFORE, without fix_derived_table_read_cost: derived2 has cost=rows=10000
|
|
select json_detailed(
|
|
json_extract(json_extract(@trace, '$**.rows_estimation'), '$[1]')
|
|
) as Trace;
|
|
Trace
|
|
[
|
|
{
|
|
"table": "t1_base",
|
|
"table_scan":
|
|
{
|
|
"rows": 10000,
|
|
"cost": 19.08984375
|
|
}
|
|
},
|
|
{
|
|
"table": "<derived2>",
|
|
"table_scan":
|
|
{
|
|
"rows": 10000,
|
|
"cost": 10000
|
|
}
|
|
}
|
|
]
|
|
set optimizer_adjust_secondary_key_costs='fix_derived_table_read_cost';
|
|
explain
|
|
select *
|
|
from
|
|
t1 as t1_base,
|
|
(select a from t1 limit 10000) as TBL;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t1_base ALL NULL NULL NULL NULL 10000
|
|
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 10000 Using join buffer (flat, BNL join)
|
|
2 DERIVED t1 ALL NULL NULL NULL NULL 10000
|
|
set @trace=(select trace from information_schema.optimizer_trace);
|
|
# AFTER, with fix_derived_table_read_cost: derived2 has more realistic cost
|
|
select json_detailed(
|
|
json_extract(json_extract(@trace, '$**.rows_estimation'), '$[1]')
|
|
) as Trace;
|
|
Trace
|
|
[
|
|
{
|
|
"table": "t1_base",
|
|
"table_scan":
|
|
{
|
|
"rows": 10000,
|
|
"cost": 19.08984375
|
|
}
|
|
},
|
|
{
|
|
"table": "<derived2>",
|
|
"table_scan":
|
|
{
|
|
"rows": 10000,
|
|
"cost": 501
|
|
}
|
|
}
|
|
]
|
|
drop table t1;
|
|
set @@optimizer_adjust_secondary_key_costs=default;
|