mirror of
https://github.com/MariaDB/server.git
synced 2025-04-06 23:35:33 +02:00
195 lines
6.5 KiB
Text
195 lines
6.5 KiB
Text
UPDATE performance_schema.setup_instruments SET enabled = 'NO', timed = 'YES';
|
|
UPDATE performance_schema.setup_instruments SET enabled = 'YES'
|
|
WHERE name in ('wait/io/table/sql/handler',
|
|
'wait/lock/table/sql/handler');
|
|
drop procedure if exists before_payload;
|
|
drop procedure if exists after_payload;
|
|
create procedure before_payload()
|
|
begin
|
|
TRUNCATE TABLE performance_schema.table_io_waits_summary_by_index_usage;
|
|
TRUNCATE TABLE performance_schema.table_io_waits_summary_by_table;
|
|
TRUNCATE TABLE performance_schema.events_waits_history_long;
|
|
TRUNCATE TABLE performance_schema.events_waits_history;
|
|
TRUNCATE TABLE performance_schema.events_waits_current;
|
|
end
|
|
$$
|
|
create procedure after_payload()
|
|
begin
|
|
select count(1) as number_seen,
|
|
OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME,
|
|
OPERATION, NUMBER_OF_BYTES
|
|
from performance_schema.events_waits_history_long
|
|
where OBJECT_SCHEMA = "test"
|
|
group by OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, OPERATION, NUMBER_OF_BYTES;
|
|
select OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME,
|
|
COUNT_STAR, COUNT_READ, COUNT_WRITE
|
|
from performance_schema.table_io_waits_summary_by_index_usage
|
|
where OBJECT_SCHEMA = "test"
|
|
order by OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME;
|
|
select OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME,
|
|
COUNT_STAR, COUNT_READ, COUNT_WRITE
|
|
from performance_schema.table_io_waits_summary_by_table
|
|
where OBJECT_SCHEMA = "test"
|
|
order by OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME;
|
|
end
|
|
$$
|
|
drop table if exists t1;
|
|
drop table if exists t2;
|
|
drop table if exists t3;
|
|
create table t0(v int);
|
|
create table t1(id1 int, a int);
|
|
create table t2(id1 int, id2 int, b int);
|
|
create table t3(id2 int, id3 int, c int);
|
|
insert into t0 values
|
|
(0), (1), (2), (3), (4),
|
|
(5), (6), (7), (8), (9);
|
|
insert into t1(id1, a)
|
|
select v, 100*v from t0;
|
|
insert into t2(id1, id2, b)
|
|
select X.v, 10*X.v + Y.v, 100*X.v + 10*Y.v
|
|
from t0 X, t0 Y;
|
|
insert into t3(id2, id3, c)
|
|
select 10*X.v + Y.v, 100*X.v + 10*Y.v + Z.v, 100*X.v + 10*Y.v + Z.v
|
|
from t0 X, t0 Y, t0 Z;
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
analyze table t2;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 analyze status Engine-independent statistics collected
|
|
test.t2 analyze status OK
|
|
analyze table t3;
|
|
Table Op Msg_type Msg_text
|
|
test.t3 analyze status Engine-independent statistics collected
|
|
test.t3 analyze status OK
|
|
select * from t1 order by a;
|
|
id1 a
|
|
0 0
|
|
1 100
|
|
2 200
|
|
3 300
|
|
4 400
|
|
5 500
|
|
6 600
|
|
7 700
|
|
8 800
|
|
9 900
|
|
select * from t2
|
|
where (b >= 180) and (b <= 220)
|
|
order by b;
|
|
id1 id2 b
|
|
1 18 180
|
|
1 19 190
|
|
2 20 200
|
|
2 21 210
|
|
2 22 220
|
|
select * from t3
|
|
where (c >= 587) and (c <= 612)
|
|
order by c;
|
|
id2 id3 c
|
|
58 587 587
|
|
58 588 588
|
|
58 589 589
|
|
59 590 590
|
|
59 591 591
|
|
59 592 592
|
|
59 593 593
|
|
59 594 594
|
|
59 595 595
|
|
59 596 596
|
|
59 597 597
|
|
59 598 598
|
|
59 599 599
|
|
60 600 600
|
|
60 601 601
|
|
60 602 602
|
|
60 603 603
|
|
60 604 604
|
|
60 605 605
|
|
60 606 606
|
|
60 607 607
|
|
60 608 608
|
|
60 609 609
|
|
61 610 610
|
|
61 611 611
|
|
61 612 612
|
|
explain extended select t1.*, t2.*, t3.*
|
|
from t1 join t2 using (id1) join t3 using (id2);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 100 100.00 Using where; Using join buffer (flat, BNL join)
|
|
1 SIMPLE t3 ALL NULL NULL NULL NULL 1000 100.00 Using where; Using join buffer (incremental, BNL join)
|
|
Warnings:
|
|
Note 1003 select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a` AS `a`,`test`.`t2`.`id1` AS `id1`,`test`.`t2`.`id2` AS `id2`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`id2` AS `id2`,`test`.`t3`.`id3` AS `id3`,`test`.`t3`.`c` AS `c` from `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t3`.`id2` = `test`.`t2`.`id2` and `test`.`t2`.`id1` = `test`.`t1`.`id1`
|
|
call before_payload();
|
|
select t1.*, t2.*, t3.*
|
|
from t1 join t2 using (id1) join t3 using (id2);
|
|
call after_payload();
|
|
number_seen OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME INDEX_NAME OPERATION NUMBER_OF_BYTES
|
|
11 TABLE test t1 NULL fetch 1
|
|
1 TABLE test t1 NULL read external NULL
|
|
1 TABLE test t1 NULL read normal NULL
|
|
101 TABLE test t2 NULL fetch 1
|
|
1 TABLE test t2 NULL read external NULL
|
|
1 TABLE test t2 NULL read normal NULL
|
|
1 TABLE test t3 NULL fetch 1000
|
|
1 TABLE test t3 NULL read external NULL
|
|
1 TABLE test t3 NULL read normal NULL
|
|
OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME INDEX_NAME COUNT_STAR COUNT_READ COUNT_WRITE
|
|
TABLE test t0 NULL 0 0 0
|
|
TABLE test t1 NULL 11 11 0
|
|
TABLE test t2 NULL 101 101 0
|
|
TABLE test t3 NULL 1000 1000 0
|
|
OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME COUNT_STAR COUNT_READ COUNT_WRITE
|
|
TABLE test t0 0 0 0
|
|
TABLE test t1 11 11 0
|
|
TABLE test t2 101 101 0
|
|
TABLE test t3 1000 1000 0
|
|
alter table t1 add unique index(id1);
|
|
alter table t2 add unique index(id2);
|
|
alter table t2 add index(id1);
|
|
alter table t3 add unique index(id3);
|
|
alter table t3 add index(id2);
|
|
explain extended select t1.*, t2.*, t3.*
|
|
from t1 join t2 using (id1) join t3 using (id2);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL id1 NULL NULL NULL 10 100.00 Using where
|
|
1 SIMPLE t2 ref id2,id1 id1 5 test.t1.id1 10 100.00 Using where
|
|
1 SIMPLE t3 ref id2 id2 5 test.t2.id2 10 100.00
|
|
Warnings:
|
|
Note 1003 select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a` AS `a`,`test`.`t2`.`id1` AS `id1`,`test`.`t2`.`id2` AS `id2`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`id2` AS `id2`,`test`.`t3`.`id3` AS `id3`,`test`.`t3`.`c` AS `c` from `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t3`.`id2` = `test`.`t2`.`id2` and `test`.`t2`.`id1` = `test`.`t1`.`id1`
|
|
call before_payload();
|
|
select t1.*, t2.*, t3.*
|
|
from t1 join t2 using (id1) join t3 using (id2);
|
|
call after_payload();
|
|
number_seen OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME INDEX_NAME OPERATION NUMBER_OF_BYTES
|
|
11 TABLE test t1 NULL fetch 1
|
|
1 TABLE test t1 id1 read external NULL
|
|
1 TABLE test t1 id1 read normal NULL
|
|
110 TABLE test t2 id1 fetch 1
|
|
1 TABLE test t2 id2 read external NULL
|
|
1 TABLE test t2 id2 read normal NULL
|
|
100 TABLE test t3 id2 fetch 10
|
|
1 TABLE test t3 id3 read external NULL
|
|
1 TABLE test t3 id3 read normal NULL
|
|
OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME INDEX_NAME COUNT_STAR COUNT_READ COUNT_WRITE
|
|
TABLE test t0 NULL 0 0 0
|
|
TABLE test t1 NULL 11 11 0
|
|
TABLE test t1 id1 0 0 0
|
|
TABLE test t2 id1 110 110 0
|
|
TABLE test t2 id2 0 0 0
|
|
TABLE test t3 id2 1000 1000 0
|
|
TABLE test t3 id3 0 0 0
|
|
OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME COUNT_STAR COUNT_READ COUNT_WRITE
|
|
TABLE test t0 0 0 0
|
|
TABLE test t1 11 11 0
|
|
TABLE test t2 110 110 0
|
|
TABLE test t3 1000 1000 0
|
|
drop table t0;
|
|
drop table t1;
|
|
drop table t2;
|
|
drop table t3;
|
|
drop procedure before_payload;
|
|
drop procedure after_payload;
|
|
UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES';
|