mirror of
https://github.com/MariaDB/server.git
synced 2025-10-06 07:49:14 +02:00
97fd0f2a71
This feature stores the basic stats of the base tables that are used in a query, to the optimizer trace. This feature is also controlled by optimizer_record_context, and is not enabled by default. The stats such as num_of_records present in the table, indexes if present then their names, along with the average number of records_per_key with in each index are dumped to the trace. Additionally, stats from range analysis of the queries are also dumped into the trace The approach taken here is to extend the existing function store_tables_context_in_trace() and add new dump_table_stats_to_trace() to opt_trace_ddl_info.cc. For storing the range analysis stats into the trace, they are first recorded in the hash defined in Optimizer_Stats_Context_Recorder, and is used later while dumping the stats into the trace from dump_table_stats_to_trace(). Several new tests are also added to opt_trace_store_stats.test
262 lines
9 KiB
Text
262 lines
9 KiB
Text
#enable both optimizer_trace, and optimizer_record_context
|
|
set optimizer_record_context=ON;
|
|
set optimizer_trace=1;
|
|
create database db1;
|
|
use db1;
|
|
create table t1
|
|
(
|
|
a int, b int,
|
|
index t1_idx_a (a),
|
|
index t1_idx_b (b),
|
|
index t1_idx_ab (a, b)
|
|
);
|
|
insert into t1 select seq%2, seq%3 from seq_1_to_20;
|
|
create table t2 (
|
|
a int,
|
|
index t2_idx_a (a)
|
|
);
|
|
insert into t2 select seq%6 from seq_1_to_30;
|
|
create view view1 as (
|
|
select t1.a as a, t1.b as b, t2.a as c from (t1 join t2) where t1.a = t2.a and t1.a = 5
|
|
);
|
|
# analyze all the tables
|
|
set session use_stat_tables='COMPLEMENTARY';
|
|
analyze table t1 persistent for all;
|
|
Table Op Msg_type Msg_text
|
|
db1.t1 analyze status Engine-independent statistics collected
|
|
db1.t1 analyze status Table is already up to date
|
|
analyze table t2 persistent for all;
|
|
Table Op Msg_type Msg_text
|
|
db1.t2 analyze status Engine-independent statistics collected
|
|
db1.t2 analyze status Table is already up to date
|
|
#
|
|
# simple query using one table
|
|
#
|
|
select count(*) from t1;
|
|
count(*)
|
|
20
|
|
set @trace= (select trace from information_schema.optimizer_trace);
|
|
set @records= (select JSON_DETAILED(JSON_EXTRACT(@trace, '$**.num_of_records')));
|
|
select * from json_table(@records, '$[*]' columns(num_of_records text path '$')) as jt;
|
|
num_of_records
|
|
0
|
|
set @indexes=(select JSON_DETAILED(JSON_EXTRACT(@trace, '$**.indexes')));
|
|
select * from json_table(@indexes, '$[*][*]' columns(index_name text path '$.index_name', rec_per_key json path '$.rec_per_key')) as jt;
|
|
index_name rec_per_key
|
|
t1_idx_a ["10"]
|
|
t1_idx_b ["7"]
|
|
t1_idx_ab [
|
|
"10",
|
|
"3"
|
|
]
|
|
#
|
|
# simple query using join of two tables
|
|
#
|
|
select count(*) from t1, t2 where t1.a = t2.a;
|
|
count(*)
|
|
100
|
|
set @trace= (select trace from information_schema.optimizer_trace);
|
|
set @records= (select JSON_DETAILED(JSON_EXTRACT(@trace, '$**.num_of_records')));
|
|
select * from json_table(@records, '$[*]' columns(num_of_records text path '$')) as jt;
|
|
num_of_records
|
|
30
|
|
20
|
|
set @indexes=(select JSON_DETAILED(JSON_EXTRACT(@trace, '$**.indexes')));
|
|
select * from json_table(@indexes, '$[*][*]' columns(index_name text path '$.index_name', rec_per_key json path '$.rec_per_key')) as jt;
|
|
index_name rec_per_key
|
|
t2_idx_a ["5"]
|
|
t1_idx_a ["10"]
|
|
t1_idx_b ["7"]
|
|
t1_idx_ab [
|
|
"10",
|
|
"3"
|
|
]
|
|
#
|
|
# negative test
|
|
# simple query using join of two tables
|
|
# there should be no result
|
|
#
|
|
set optimizer_record_context=OFF;
|
|
select count(*) from t1, t2 where t1.a = t2.a;
|
|
count(*)
|
|
100
|
|
set @trace= (select trace from information_schema.optimizer_trace);
|
|
set @records= (select JSON_DETAILED(JSON_EXTRACT(@trace, '$**.num_of_records')));
|
|
select * from json_table(@records, '$[*]' columns(num_of_records text path '$')) as jt;
|
|
num_of_records
|
|
set @indexes=(select JSON_DETAILED(JSON_EXTRACT(@trace, '$**.indexes')));
|
|
select * from json_table(@indexes, '$[*][*]' columns(index_name text path '$.index_name', rec_per_key json path '$.rec_per_key')) as jt;
|
|
index_name rec_per_key
|
|
set optimizer_record_context=ON;
|
|
#
|
|
# there should be no duplicate information
|
|
#
|
|
select * from view1 union select * from view1;
|
|
a b c
|
|
set @trace= (select trace from information_schema.optimizer_trace);
|
|
set @records= (select JSON_DETAILED(JSON_EXTRACT(@trace, '$**.num_of_records')));
|
|
select * from json_table(@records, '$[*]' columns(num_of_records text path '$')) as jt;
|
|
num_of_records
|
|
30
|
|
20
|
|
set @indexes=(select JSON_DETAILED(JSON_EXTRACT(@trace, '$**.indexes')));
|
|
select * from json_table(@indexes, '$[*][*]' columns(index_name text path '$.index_name', rec_per_key json path '$.rec_per_key')) as jt;
|
|
index_name rec_per_key
|
|
t2_idx_a ["5"]
|
|
t1_idx_a ["10"]
|
|
t1_idx_b ["7"]
|
|
t1_idx_ab [
|
|
"10",
|
|
"3"
|
|
]
|
|
#
|
|
# test for update
|
|
#
|
|
update t1 set t1.b = t1.a;
|
|
analyze table t1 persistent for all;
|
|
Table Op Msg_type Msg_text
|
|
db1.t1 analyze status Engine-independent statistics collected
|
|
db1.t1 analyze status OK
|
|
set @trace= (select trace from information_schema.optimizer_trace);
|
|
set @records= (select JSON_DETAILED(JSON_EXTRACT(@trace, '$**.num_of_records')));
|
|
select * from json_table(@records, '$[*]' columns(num_of_records text path '$')) as jt;
|
|
num_of_records
|
|
20
|
|
set @indexes=(select JSON_DETAILED(JSON_EXTRACT(@trace, '$**.indexes')));
|
|
select * from json_table(@indexes, '$[*][*]' columns(index_name text path '$.index_name', rec_per_key json path '$.rec_per_key')) as jt;
|
|
index_name rec_per_key
|
|
t1_idx_a ["10"]
|
|
t1_idx_b ["7"]
|
|
t1_idx_ab [
|
|
"10",
|
|
"3"
|
|
]
|
|
#
|
|
# test for insert as select
|
|
#
|
|
insert into t1 (select t2.a as a, t2.a as b from t2);
|
|
analyze table t1 persistent for all;
|
|
Table Op Msg_type Msg_text
|
|
db1.t1 analyze status Engine-independent statistics collected
|
|
db1.t1 analyze status OK
|
|
set @trace= (select trace from information_schema.optimizer_trace);
|
|
set @records= (select JSON_DETAILED(JSON_EXTRACT(@trace, '$**.num_of_records')));
|
|
select * from json_table(@records, '$[*]' columns(num_of_records text path '$')) as jt;
|
|
num_of_records
|
|
30
|
|
20
|
|
set @indexes=(select JSON_DETAILED(JSON_EXTRACT(@trace, '$**.indexes')));
|
|
select * from json_table(@indexes, '$[*][*]' columns(index_name text path '$.index_name', rec_per_key json path '$.rec_per_key')) as jt;
|
|
index_name rec_per_key
|
|
t2_idx_a ["5"]
|
|
t1_idx_a ["10"]
|
|
t1_idx_b ["10"]
|
|
t1_idx_ab [
|
|
"10",
|
|
"10"
|
|
]
|
|
#
|
|
# range analysis tests
|
|
#
|
|
#
|
|
# simple query with or condition on 2 columns
|
|
#
|
|
analyze select * from t1 where t1.a between 1 and 5 or t1.b between 6 and 10;
|
|
id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
|
|
1 SIMPLE t1 index t1_idx_a,t1_idx_b,t1_idx_ab t1_idx_ab 10 NULL 50 50.00 100.00 70.00 Using where; Using index
|
|
set @trace= (select trace from information_schema.optimizer_trace);
|
|
set @list_ranges= (select JSON_DETAILED(JSON_EXTRACT(@trace, '$**.list_ranges')));
|
|
select * from json_table(
|
|
@list_ranges,
|
|
'$[*][*]' columns(
|
|
index_name text path '$.index_name',
|
|
ranges json path '$.ranges',
|
|
num_rows int path '$.num_rows'
|
|
)
|
|
) as jt;
|
|
index_name ranges num_rows
|
|
t1_idx_a ["(1) <= (a) <= (5)"] 35
|
|
t1_idx_ab ["(1) <= (a) <= (5)"] 35
|
|
t1_idx_b ["(6) <= (b) <= (10)"] 1
|
|
#
|
|
# simple query with or condition on the same column
|
|
#
|
|
analyze select * from t1 where t1.a between 1 and 5 or t1.a between 6 and 10;
|
|
id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
|
|
1 SIMPLE t1 range t1_idx_a,t1_idx_ab t1_idx_ab 5 NULL 36 35.00 100.00 100.00 Using where; Using index
|
|
set @trace= (select trace from information_schema.optimizer_trace);
|
|
set @list_ranges= (select JSON_DETAILED(JSON_EXTRACT(@trace, '$**.list_ranges')));
|
|
select * from json_table(
|
|
@list_ranges,
|
|
'$[*][*]' columns(
|
|
index_name text path '$.index_name',
|
|
ranges json path '$.ranges',
|
|
num_rows int path '$.num_rows'
|
|
)
|
|
) as jt;
|
|
index_name ranges num_rows
|
|
t1_idx_a [
|
|
"(1) <= (a) <= (5)",
|
|
"(6) <= (a) <= (10)"
|
|
] 36
|
|
t1_idx_ab [
|
|
"(1) <= (a) <= (5)",
|
|
"(6) <= (a) <= (10)"
|
|
] 36
|
|
#
|
|
# negative test on the simple query with or condition on 2 columns
|
|
#
|
|
set optimizer_record_context=OFF;
|
|
analyze select * from t1 where t1.a between 1 and 5 or t1.b between 6 and 10;
|
|
id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
|
|
1 SIMPLE t1 index t1_idx_a,t1_idx_b,t1_idx_ab t1_idx_ab 10 NULL 50 50.00 100.00 70.00 Using where; Using index
|
|
set @trace= (select trace from information_schema.optimizer_trace);
|
|
set @list_ranges= (select JSON_DETAILED(JSON_EXTRACT(@trace, '$**.list_ranges')));
|
|
select * from json_table(
|
|
@list_ranges,
|
|
'$[*][*]' columns(
|
|
index_name text path '$.index_name',
|
|
ranges json path '$.ranges',
|
|
num_rows int path '$.num_rows'
|
|
)
|
|
) as jt;
|
|
index_name ranges num_rows
|
|
set optimizer_record_context=ON;
|
|
#
|
|
# simple query with or condition on 2 columns
|
|
# testing all the stats information
|
|
#
|
|
analyze select * from t1 where t1.a between 1 and 5 or t1.b between 6 and 10;
|
|
id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
|
|
1 SIMPLE t1 index t1_idx_a,t1_idx_b,t1_idx_ab t1_idx_ab 10 NULL 50 50.00 100.00 70.00 Using where; Using index
|
|
set @trace= (select trace from information_schema.optimizer_trace);
|
|
set @records= (select JSON_DETAILED(JSON_EXTRACT(@trace, '$**.num_of_records')));
|
|
select * from json_table(@records, '$[*]' columns(num_of_records text path '$')) as jt;
|
|
num_of_records
|
|
50
|
|
set @indexes=(select JSON_DETAILED(JSON_EXTRACT(@trace, '$**.indexes')));
|
|
select * from json_table(@indexes, '$[*][*]' columns(index_name text path '$.index_name', rec_per_key json path '$.rec_per_key')) as jt;
|
|
index_name rec_per_key
|
|
t1_idx_a ["8"]
|
|
t1_idx_b ["8"]
|
|
t1_idx_ab [
|
|
"8",
|
|
"8"
|
|
]
|
|
set @list_ranges= (select JSON_DETAILED(JSON_EXTRACT(@trace, '$**.list_ranges')));
|
|
select * from json_table(
|
|
@list_ranges,
|
|
'$[*][*]' columns(
|
|
index_name text path '$.index_name',
|
|
ranges json path '$.ranges',
|
|
num_rows int path '$.num_rows'
|
|
)
|
|
) as jt;
|
|
index_name ranges num_rows
|
|
t1_idx_a ["(1) <= (a) <= (5)"] 35
|
|
t1_idx_ab ["(1) <= (a) <= (5)"] 35
|
|
t1_idx_b ["(6) <= (b) <= (10)"] 1
|
|
drop view view1;
|
|
drop table t1;
|
|
drop table t2;
|
|
drop database db1;
|