897 lines
31 KiB
Plaintext
897 lines
31 KiB
Plaintext
explain format='plan_tree' select * from METRICS_SCHEMA.tidb_query_duration where time >= '2019-12-23 16:10:13' and time <= '2019-12-23 16:30:13' ;
|
|
id task access object operator info
|
|
MemTableScan root table:tidb_query_duration PromQL:histogram_quantile(0.9, sum(rate(tidb_server_handle_query_duration_seconds_bucket{}[60s])) by (le,sql_type,instance)), start_time:2019-12-23 16:10:13, end_time:2019-12-23 16:30:13, step:1m0s
|
|
explain format='plan_tree' select * from METRICS_SCHEMA.up where time >= '2019-12-23 16:10:13' and time <= '2019-12-23 16:30:13' ;
|
|
id task access object operator info
|
|
MemTableScan root table:up PromQL:up{}, start_time:2019-12-23 16:10:13, end_time:2019-12-23 16:30:13, step:1m0s
|
|
explain format='plan_tree' select * from information_schema.cluster_log where time >= '2019-12-23 16:10:13' and time <= '2019-12-23 16:30:13';
|
|
id task access object operator info
|
|
MemTableScan root table:CLUSTER_LOG start_time:2019-12-23 16:10:13, end_time:2019-12-23 16:30:13
|
|
explain format='plan_tree' select * from information_schema.cluster_log where level in ('warn','error') and time >= '2019-12-23 16:10:13' and time <= '2019-12-23 16:30:13';
|
|
id task access object operator info
|
|
MemTableScan root table:CLUSTER_LOG start_time:2019-12-23 16:10:13, end_time:2019-12-23 16:30:13, log_levels:["error","warn"]
|
|
explain format='plan_tree' select * from information_schema.cluster_log where type in ('high_cpu_1','high_memory_1') and time >= '2019-12-23 16:10:13' and time <= '2019-12-23 16:30:13';
|
|
id task access object operator info
|
|
MemTableScan root table:CLUSTER_LOG start_time:2019-12-23 16:10:13, end_time:2019-12-23 16:30:13, node_types:["high_cpu_1","high_memory_1"]
|
|
explain format='plan_tree' select * from information_schema.slow_query;
|
|
id task access object operator info
|
|
MemTableScan root table:SLOW_QUERY only search in the current 'tidb-slow.log' file
|
|
explain format='plan_tree' select * from information_schema.slow_query where time >= '2019-12-23 16:10:13' and time <= '2019-12-23 16:30:13';
|
|
id task access object operator info
|
|
MemTableScan root table:SLOW_QUERY start_time:2019-12-23 16:10:13.000000, end_time:2019-12-23 16:30:13.000000
|
|
set @@time_zone = '+00:00';
|
|
explain format='plan_tree' select * from information_schema.slow_query where time >= '2019-12-23 16:10:13' and time <= '2019-12-23 16:30:13';
|
|
id task access object operator info
|
|
MemTableScan root table:SLOW_QUERY start_time:2019-12-23 16:10:13.000000, end_time:2019-12-23 16:30:13.000000
|
|
set @@time_zone = default;
|
|
explain format='plan_tree' select * from information_schema.cluster_config where type in ('tikv', 'tidb');
|
|
id task access object operator info
|
|
MemTableScan root table:CLUSTER_CONFIG node_types:["tidb","tikv"]
|
|
explain format='plan_tree' select * from information_schema.cluster_config where instance='192.168.1.7:2379';
|
|
id task access object operator info
|
|
MemTableScan root table:CLUSTER_CONFIG instances:["192.168.1.7:2379"]
|
|
explain format='plan_tree' select * from information_schema.cluster_config where type='tidb' and instance='192.168.1.7:2379';
|
|
id task access object operator info
|
|
MemTableScan root table:CLUSTER_CONFIG node_types:["tidb"], instances:["192.168.1.7:2379"]
|
|
explain format='plan_tree' select * from information_schema.inspection_result where rule = 'ddl' and rule = 'config';
|
|
id task access object operator info
|
|
MemTableScan root table:INSPECTION_RESULT skip_inspection:true
|
|
explain format='plan_tree' select * from information_schema.inspection_result where rule in ('ddl', 'config');
|
|
id task access object operator info
|
|
MemTableScan root table:INSPECTION_RESULT rules:["config","ddl"], items:[]
|
|
explain format='plan_tree' select * from information_schema.inspection_result where item in ('ddl.lease', 'raftstore.threadpool');
|
|
id task access object operator info
|
|
MemTableScan root table:INSPECTION_RESULT rules:[], items:["ddl.lease","raftstore.threadpool"]
|
|
explain format='plan_tree' select * from information_schema.inspection_result where item in ('ddl.lease', 'raftstore.threadpool') and rule in ('ddl', 'config');
|
|
id task access object operator info
|
|
MemTableScan root table:INSPECTION_RESULT rules:["config","ddl"], items:["ddl.lease","raftstore.threadpool"]
|
|
explain format='plan_tree' select * from information_schema.inspection_rules where type='inspection';
|
|
id task access object operator info
|
|
MemTableScan root table:INSPECTION_RULES node_types:["inspection"]
|
|
explain format='plan_tree' select * from information_schema.inspection_rules where type='inspection' or type='summary';
|
|
id task access object operator info
|
|
MemTableScan root table:INSPECTION_RULES node_types:["inspection","summary"]
|
|
explain format='plan_tree' select * from information_schema.inspection_rules where type='inspection' and type='summary';
|
|
id task access object operator info
|
|
MemTableScan root table:INSPECTION_RULES skip_request: true
|
|
set tidb_enable_prepared_plan_cache=1;
|
|
drop table if exists t;
|
|
create table t(a int, b int, c int generated always as (a+b) stored);
|
|
insert into t(a,b) values(1,1);
|
|
begin;
|
|
update t set b = 2 where a = 1;
|
|
prepare stmt from 'select b from t where a > ?';
|
|
set @p = 0;
|
|
execute stmt using @p;
|
|
b
|
|
2
|
|
set @p = 1;
|
|
execute stmt using @p;
|
|
b
|
|
insert into t(a,b,c) values(3,3,3);
|
|
Error 3105 (HY000): The value specified for generated column 'c' in table 't' is not allowed.
|
|
rollback;
|
|
set tidb_enable_prepared_plan_cache=default;
|
|
explain format='plan_tree' select * from information_schema.TABLE_STORAGE_STATS where TABLE_SCHEMA = 'information_schema';
|
|
id task access object operator info
|
|
MemTableScan root table:TABLE_STORAGE_STATS schema:["information_schema"]
|
|
explain format='plan_tree' select * from information_schema.TABLE_STORAGE_STATS where TABLE_NAME = 'schemata';
|
|
id task access object operator info
|
|
MemTableScan root table:TABLE_STORAGE_STATS table:["schemata"]
|
|
explain format='plan_tree' select * from information_schema.TABLE_STORAGE_STATS where TABLE_SCHEMA = 'information_schema' and TABLE_NAME = 'schemata';
|
|
id task access object operator info
|
|
MemTableScan root table:TABLE_STORAGE_STATS schema:["information_schema"], table:["schemata"]
|
|
explain format='plan_tree' select * from information_schema.inspection_summary where rule='ddl';
|
|
id task access object operator info
|
|
Selection root eq(Column, "ddl")
|
|
└─MemTableScan root table:INSPECTION_SUMMARY rules:["ddl"]
|
|
explain format='plan_tree' select * from information_schema.inspection_summary where 'ddl'=rule or rule='config';
|
|
id task access object operator info
|
|
Selection root or(eq("ddl", Column), eq(Column, "config"))
|
|
└─MemTableScan root table:INSPECTION_SUMMARY rules:["config","ddl"]
|
|
explain format='plan_tree' select * from information_schema.inspection_summary where 'ddl'=rule or rule='config' or rule='slow_query';
|
|
id task access object operator info
|
|
Selection root or(eq("ddl", Column), or(eq(Column, "config"), eq(Column, "slow_query")))
|
|
└─MemTableScan root table:INSPECTION_SUMMARY rules:["config","ddl","slow_query"]
|
|
explain format='plan_tree' select * from information_schema.inspection_summary where (rule='config' or rule='slow_query') and (metrics_name='metric_name3' or metrics_name='metric_name1');
|
|
id task access object operator info
|
|
Selection root or(eq(Column, "config"), eq(Column, "slow_query")), or(eq(Column, "metric_name3"), eq(Column, "metric_name1"))
|
|
└─MemTableScan root table:INSPECTION_SUMMARY rules:["config","slow_query"], metric_names:["metric_name1","metric_name3"]
|
|
explain format='plan_tree' select * from information_schema.inspection_summary where rule in ('ddl', 'slow_query');
|
|
id task access object operator info
|
|
Selection root in(Column, "ddl", "slow_query")
|
|
└─MemTableScan root table:INSPECTION_SUMMARY rules:["ddl","slow_query"]
|
|
explain format='plan_tree' select * from information_schema.inspection_summary where rule in ('ddl', 'slow_query') and metrics_name='metric_name1';
|
|
id task access object operator info
|
|
Selection root eq(Column, "metric_name1"), in(Column, "ddl", "slow_query")
|
|
└─MemTableScan root table:INSPECTION_SUMMARY rules:["ddl","slow_query"], metric_names:["metric_name1"]
|
|
explain format='plan_tree' select * from information_schema.inspection_summary where rule in ('ddl', 'slow_query') and metrics_name in ('metric_name1', 'metric_name2');
|
|
id task access object operator info
|
|
Selection root in(Column, "ddl", "slow_query"), in(Column, "metric_name1", "metric_name2")
|
|
└─MemTableScan root table:INSPECTION_SUMMARY rules:["ddl","slow_query"], metric_names:["metric_name1","metric_name2"]
|
|
explain format='plan_tree' select * from information_schema.inspection_summary where rule='ddl' and metrics_name in ('metric_name1', 'metric_name2');
|
|
id task access object operator info
|
|
Selection root eq(Column, "ddl"), in(Column, "metric_name1", "metric_name2")
|
|
└─MemTableScan root table:INSPECTION_SUMMARY rules:["ddl"], metric_names:["metric_name1","metric_name2"]
|
|
explain format='plan_tree' select * from information_schema.inspection_summary where rule='ddl' and metrics_name='metric_NAME3';
|
|
id task access object operator info
|
|
Selection root eq(Column, "ddl"), eq(Column, "metric_NAME3")
|
|
└─MemTableScan root table:INSPECTION_SUMMARY rules:["ddl"], metric_names:["metric_name3"]
|
|
explain format='plan_tree' select * from information_schema.inspection_summary where rule in ('ddl', 'config') and rule in ('slow_query', 'config');
|
|
id task access object operator info
|
|
Selection root in(Column, "ddl", "config"), in(Column, "slow_query", "config")
|
|
└─MemTableScan root table:INSPECTION_SUMMARY rules:["config"]
|
|
explain format='plan_tree' select * from information_schema.inspection_summary where metrics_name in ('metric_name1', 'metric_name4') and metrics_name in ('metric_name5', 'metric_name4') and rule in ('ddl', 'config') and rule in ('slow_query', 'config') and quantile in (0.80, 0.90);
|
|
id task access object operator info
|
|
Selection root in(Column, "ddl", "config"), in(Column, "metric_name1", "metric_name4"), in(Column, "metric_name5", "metric_name4"), in(Column, "slow_query", "config")
|
|
└─MemTableScan root table:INSPECTION_SUMMARY rules:["config"], metric_names:["metric_name4"], quantiles:[0.800000,0.900000]
|
|
explain format='plan_tree' select * from information_schema.inspection_summary where metrics_name in ('metric_name1', 'metric_name4') and metrics_name in ('metric_name5', 'metric_name4') and metrics_name in ('metric_name5', 'metric_name1') and metrics_name in ('metric_name1', 'metric_name3');
|
|
id task access object operator info
|
|
Selection root in(Column, "metric_name1", "metric_name3"), in(Column, "metric_name1", "metric_name4"), in(Column, "metric_name5", "metric_name1"), in(Column, "metric_name5", "metric_name4")
|
|
└─MemTableScan root table:INSPECTION_SUMMARY skip_inspection: true
|
|
explain format='plan_tree' select * from information_schema.TIFLASH_TABLES where TIFLASH_INSTANCE = '192.168.1.7:3930';
|
|
id task access object operator info
|
|
MemTableScan root table:TIFLASH_TABLES tiflash_instances:["192.168.1.7:3930"]
|
|
explain format='plan_tree' select * from information_schema.TIFLASH_SEGMENTS where TIFLASH_INSTANCE = '192.168.1.7:3930';
|
|
id task access object operator info
|
|
MemTableScan root table:TIFLASH_SEGMENTS tiflash_instances:["192.168.1.7:3930"]
|
|
explain format='plan_tree' select * from information_schema.TIFLASH_TABLES where TIDB_DATABASE = 'test';
|
|
id task access object operator info
|
|
MemTableScan root table:TIFLASH_TABLES tidb_databases:["test"]
|
|
explain format='plan_tree' select * from information_schema.TIFLASH_SEGMENTS where TIDB_DATABASE = 'test';
|
|
id task access object operator info
|
|
MemTableScan root table:TIFLASH_SEGMENTS tidb_databases:["test"]
|
|
explain format='plan_tree' select * from information_schema.TIFLASH_TABLES where TIDB_TABLE = 't';
|
|
id task access object operator info
|
|
MemTableScan root table:TIFLASH_TABLES tidb_tables:["t"]
|
|
explain format='plan_tree' select * from information_schema.TIFLASH_SEGMENTS where TIDB_TABLE = 't';
|
|
id task access object operator info
|
|
MemTableScan root table:TIFLASH_SEGMENTS tidb_tables:["t"]
|
|
explain format='plan_tree' select * from information_schema.TIFLASH_TABLES where TIFLASH_INSTANCE = '192.168.1.7:3930' and TIDB_DATABASE = 'test' and TIDB_TABLE = 't';
|
|
id task access object operator info
|
|
MemTableScan root table:TIFLASH_TABLES tiflash_instances:["192.168.1.7:3930"], tidb_databases:["test"], tidb_tables:["t"]
|
|
explain format='plan_tree' select * from information_schema.TIFLASH_SEGMENTS where TIFLASH_INSTANCE = '192.168.1.7:3930' and TIDB_DATABASE = 'test' and TIDB_TABLE = 't';
|
|
id task access object operator info
|
|
MemTableScan root table:TIFLASH_SEGMENTS tiflash_instances:["192.168.1.7:3930"], tidb_databases:["test"], tidb_tables:["t"]
|
|
set tidb_enable_prepared_plan_cache=1;
|
|
set @@tidb_enable_collect_execution_info=0;
|
|
drop table if exists t1, t2;
|
|
CREATE TABLE `t1` (a int);
|
|
CREATE TABLE `t2` (a int);
|
|
insert into t1 values(1), (2);
|
|
insert into t2 values(1), (3);
|
|
prepare stmt from 'select * from t1 where a > ? union select * from t2 where a > ?;';
|
|
set @a=0, @b=1;
|
|
execute stmt using @a, @b;
|
|
a
|
|
1
|
|
2
|
|
3
|
|
execute stmt using @b, @a;
|
|
a
|
|
1
|
|
2
|
|
3
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
1
|
|
execute stmt using @b, @b;
|
|
a
|
|
2
|
|
3
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
1
|
|
execute stmt using @a, @a;
|
|
a
|
|
1
|
|
2
|
|
3
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
1
|
|
prepare stmt from 'select * from t1 where a > ? union all select * from t2 where a > ?;';
|
|
set @a=0, @b=1;
|
|
execute stmt using @a, @b;
|
|
a
|
|
1
|
|
2
|
|
3
|
|
execute stmt using @b, @a;
|
|
a
|
|
1
|
|
2
|
|
3
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
1
|
|
execute stmt using @b, @b;
|
|
a
|
|
2
|
|
3
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
1
|
|
execute stmt using @a, @a;
|
|
a
|
|
1
|
|
1
|
|
2
|
|
3
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
1
|
|
prepare stmt from 'select * from t1 where a > ? except select * from t2 where a > ?;';
|
|
set @a=0, @b=1;
|
|
execute stmt using @a, @a;
|
|
a
|
|
2
|
|
execute stmt using @b, @a;
|
|
a
|
|
2
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
1
|
|
execute stmt using @b, @b;
|
|
a
|
|
2
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
1
|
|
execute stmt using @a, @b;
|
|
a
|
|
1
|
|
2
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
1
|
|
prepare stmt from 'select * from t1 where a > ? union select * from t2 where a > ?;';
|
|
set @a=0, @b=1;
|
|
execute stmt using @a, @a;
|
|
a
|
|
1
|
|
2
|
|
3
|
|
execute stmt using @b, @a;
|
|
a
|
|
1
|
|
2
|
|
3
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
1
|
|
execute stmt using @b, @b;
|
|
a
|
|
2
|
|
3
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
1
|
|
execute stmt using @a, @b;
|
|
a
|
|
1
|
|
2
|
|
3
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
1
|
|
prepare stmt from 'select * from t1 union all select * from t1 intersect select * from t2;';
|
|
execute stmt;
|
|
a
|
|
1
|
|
1
|
|
2
|
|
prepare stmt from '(select * from t1 union all select * from t1) intersect select * from t2;';
|
|
execute stmt;
|
|
a
|
|
1
|
|
prepare stmt from '(select * from t1 union all select * from t1 intersect select * from t2) order by a limit 2;';
|
|
execute stmt;
|
|
a
|
|
1
|
|
1
|
|
set tidb_enable_prepared_plan_cache=default;
|
|
set @@tidb_enable_collect_execution_info=default;
|
|
set tidb_enable_prepared_plan_cache=1;
|
|
set @@tidb_enable_collect_execution_info=0;
|
|
drop table if exists t;
|
|
create table t(a int, index idx_a(a));
|
|
prepare stmt from 'select * from t;';
|
|
execute stmt;
|
|
a
|
|
execute stmt;
|
|
a
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
1
|
|
prepare stmt from 'select /*+ IGNORE_PLAN_CACHE() */ * from t;';
|
|
execute stmt;
|
|
a
|
|
execute stmt;
|
|
a
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
0
|
|
set tidb_enable_prepared_plan_cache=default;
|
|
set @@tidb_enable_collect_execution_info=default;
|
|
set tidb_enable_prepared_plan_cache=1;
|
|
set @@tidb_enable_collect_execution_info=0;
|
|
drop table if exists t;
|
|
CREATE TABLE t(c1 INT, index idx_c(c1));
|
|
prepare stmt from 'select * from t use index(idx_c) where c1 > 1;';
|
|
execute stmt;
|
|
c1
|
|
execute stmt;
|
|
c1
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
1
|
|
ALTER TABLE t ALTER INDEX idx_c INVISIBLE;
|
|
select * from t use index(idx_c) where c1 > 1;
|
|
Error 1176 (42000): Key 'idx_c' doesn't exist in table 't'
|
|
execute stmt;
|
|
Error 1176 (42000): Key 'idx_c' doesn't exist in table 't'
|
|
set tidb_enable_prepared_plan_cache=default;
|
|
set @@tidb_enable_collect_execution_info=default;
|
|
set tidb_enable_prepared_plan_cache=1;
|
|
set @@tidb_enable_collect_execution_info=0;
|
|
prepare stmt from 'with recursive cte1 as (select ? c1 union all select c1 + 1 c1 from cte1 where c1 < ?) select * from cte1;';
|
|
set @a=5, @b=4, @c=2, @d=1;
|
|
execute stmt using @d, @a;
|
|
c1
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
execute stmt using @d, @b;
|
|
c1
|
|
1
|
|
2
|
|
3
|
|
4
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
0
|
|
execute stmt using @c, @b;
|
|
c1
|
|
2
|
|
3
|
|
4
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
0
|
|
prepare stmt from 'with recursive cte1 as (select 1 c1 union all select 2 c1 union all select c1 + 1 c1 from cte1 where c1 < ?) select * from cte1 order by c1;';
|
|
set @a=10, @b=2;
|
|
execute stmt using @a;
|
|
c1
|
|
1
|
|
2
|
|
2
|
|
3
|
|
3
|
|
4
|
|
4
|
|
5
|
|
5
|
|
6
|
|
6
|
|
7
|
|
7
|
|
8
|
|
8
|
|
9
|
|
9
|
|
10
|
|
10
|
|
execute stmt using @b;
|
|
c1
|
|
1
|
|
2
|
|
2
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
0
|
|
prepare stmt from 'with recursive cte1 as (select 1 c1 union all select 2 c1 union all select c1 + 1 c1 from cte1 where c1 < ? union all select c1 + ? c1 from cte1 where c1 < ?) select * from cte1 order by c1;';
|
|
set @a=1, @b=2, @c=3, @d=4, @e=5;
|
|
execute stmt using @c, @b, @e;
|
|
c1
|
|
1
|
|
2
|
|
2
|
|
3
|
|
3
|
|
3
|
|
4
|
|
4
|
|
5
|
|
5
|
|
5
|
|
6
|
|
6
|
|
execute stmt using @b, @a, @d;
|
|
c1
|
|
1
|
|
2
|
|
2
|
|
2
|
|
3
|
|
3
|
|
3
|
|
4
|
|
4
|
|
4
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
0
|
|
drop table if exists t1;
|
|
create table t1(a int);
|
|
insert into t1 values(1);
|
|
insert into t1 values(2);
|
|
prepare stmt from 'SELECT * FROM t1 dt WHERE EXISTS(WITH RECURSIVE qn AS (SELECT a*? AS b UNION ALL SELECT b+? FROM qn WHERE b=?) SELECT * FROM qn WHERE b=a);';
|
|
show warnings;
|
|
Level Code Message
|
|
Warning 1105 skip prepared plan-cache: find table executor__explainfor.qn failed: [schema:1146]Table 'executor__explainfor.qn' doesn't exist
|
|
set @a=1, @b=2, @c=3, @d=4, @e=5, @f=0;
|
|
execute stmt using @f, @a, @f;
|
|
a
|
|
1
|
|
execute stmt using @a, @b, @a;
|
|
a
|
|
1
|
|
2
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
0
|
|
execute stmt using @a, @b, @a;
|
|
a
|
|
1
|
|
2
|
|
prepare stmt from 'with recursive c(p) as (select ?), cte(a, b) as (select 1, 1 union select a+?, 1 from cte, c where a < ?) select * from cte order by 1, 2;';
|
|
show warnings;
|
|
Level Code Message
|
|
Warning 1105 skip prepared plan-cache: find table executor__explainfor.cte failed: [schema:1146]Table 'executor__explainfor.cte' doesn't exist
|
|
execute stmt using @a, @a, @e;
|
|
a b
|
|
1 1
|
|
2 1
|
|
3 1
|
|
4 1
|
|
5 1
|
|
execute stmt using @b, @b, @c;
|
|
a b
|
|
1 1
|
|
3 1
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
0
|
|
set tidb_enable_prepared_plan_cache=default;
|
|
set @@tidb_enable_collect_execution_info=default;
|
|
set tidb_enable_prepared_plan_cache=1;
|
|
set @@tidb_enable_collect_execution_info=0;
|
|
drop table if exists t;
|
|
create table t(a int);
|
|
prepare stmt from 'select * from t;';
|
|
execute stmt;
|
|
a
|
|
execute stmt;
|
|
a
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
1
|
|
drop database if exists plan_cache;
|
|
create database plan_cache;
|
|
use plan_cache;
|
|
create table t(a int);
|
|
insert into t values(1);
|
|
execute stmt;
|
|
a
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
0
|
|
execute stmt;
|
|
a
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
1
|
|
prepare stmt from 'select * from t;';
|
|
execute stmt;
|
|
a
|
|
1
|
|
execute stmt;
|
|
a
|
|
1
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
1
|
|
execute stmt;
|
|
a
|
|
1
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
1
|
|
set tidb_enable_prepared_plan_cache=default;
|
|
set @@tidb_enable_collect_execution_info=default;
|
|
set tidb_enable_prepared_plan_cache=1;
|
|
set @@tidb_enable_collect_execution_info=0;
|
|
drop table if exists t;
|
|
create table t(a int, b int) PARTITION BY LIST (a) ( PARTITION p0 VALUES IN (1, 2, 3), PARTITION p1 VALUES IN (4, 5, 6));
|
|
set @@tidb_partition_prune_mode='static';
|
|
prepare stmt from 'select * from t;';
|
|
execute stmt;
|
|
a b
|
|
execute stmt;
|
|
a b
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
0
|
|
set tidb_enable_prepared_plan_cache=default;
|
|
set @@tidb_enable_collect_execution_info=default;
|
|
set @@tidb_partition_prune_mode=default;
|
|
drop table if exists t12, t97;
|
|
CREATE TABLE t12(a INT, b INT);
|
|
CREATE TABLE t97(a INT, b INT UNIQUE NOT NULL);
|
|
EXPLAIN SELECT t12.a, t12.b FROM t12 LEFT JOIN t97 on t12.b = t97.b;
|
|
id estRows task access object operator info
|
|
TableReader_8 10000.00 root data:TableFullScan_7
|
|
└─TableFullScan_7 10000.00 cop[tikv] table:t12 keep order:false, stats:pseudo
|
|
EXPLAIN SELECT t12.a, t12.b FROM t12 LEFT JOIN t97 use index () on t12.b = t97.b;
|
|
id estRows task access object operator info
|
|
TableReader_8 10000.00 root data:TableFullScan_7
|
|
└─TableFullScan_7 10000.00 cop[tikv] table:t12 keep order:false, stats:pseudo
|
|
set tidb_enable_prepared_plan_cache=1;
|
|
set @@tidb_enable_collect_execution_info=0;
|
|
drop table if exists t;
|
|
create table t(a int);
|
|
prepare stmt from 'select * from t;';
|
|
execute stmt;
|
|
a
|
|
execute stmt;
|
|
a
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
1
|
|
set tidb_enable_prepared_plan_cache=1;
|
|
execute stmt;
|
|
Error 8111 (HY000): Prepared statement not found
|
|
prepare stmt from 'select * from t;';
|
|
execute stmt;
|
|
a
|
|
execute stmt;
|
|
a
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
1
|
|
execute stmt;
|
|
a
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
1
|
|
set tidb_enable_prepared_plan_cache=default;
|
|
set @@tidb_enable_collect_execution_info=default;
|
|
set tidb_enable_prepared_plan_cache=1;
|
|
set @@tidb_enable_collect_execution_info=0;
|
|
drop view if exists view1, view2, view3, view4;
|
|
drop table if exists view_t;
|
|
create table view_t (a int,b int);
|
|
insert into view_t values(1,2);
|
|
create definer='root'@'localhost' view view1 as select * from view_t;
|
|
create definer='root'@'localhost' view view2(c,d) as select * from view_t;
|
|
create definer='root'@'localhost' view view3(c,d) as select a,b from view_t;
|
|
create definer='root'@'localhost' view view4 as select * from (select * from (select * from view_t) tb1) tb;
|
|
prepare stmt1 from 'select * from view1;';
|
|
execute stmt1;
|
|
a b
|
|
1 2
|
|
execute stmt1;
|
|
a b
|
|
1 2
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
1
|
|
prepare stmt2 from 'select * from view2;';
|
|
execute stmt2;
|
|
c d
|
|
1 2
|
|
execute stmt2;
|
|
c d
|
|
1 2
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
1
|
|
prepare stmt3 from 'select * from view3;';
|
|
execute stmt3;
|
|
c d
|
|
1 2
|
|
execute stmt3;
|
|
c d
|
|
1 2
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
1
|
|
prepare stmt4 from 'select * from view4;';
|
|
execute stmt4;
|
|
a b
|
|
1 2
|
|
execute stmt4;
|
|
a b
|
|
1 2
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
1
|
|
drop table view_t;
|
|
create table view_t(c int,d int);
|
|
execute stmt1;
|
|
Error 1356 (HY000): View 'plan_cache.view1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
|
|
execute stmt2;
|
|
Error 1356 (HY000): View 'plan_cache.view2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
|
|
execute stmt3;
|
|
Error 1356 (HY000): View 'plan_cache.view3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
|
|
drop table view_t;
|
|
create table view_t(a int,b int,c int);
|
|
insert into view_t values(1,2,3);
|
|
execute stmt1;
|
|
a b
|
|
1 2
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
0
|
|
execute stmt1;
|
|
a b
|
|
1 2
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
1
|
|
execute stmt2;
|
|
c d
|
|
1 2
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
0
|
|
execute stmt2;
|
|
c d
|
|
1 2
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
1
|
|
execute stmt3;
|
|
c d
|
|
1 2
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
0
|
|
execute stmt3;
|
|
c d
|
|
1 2
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
1
|
|
execute stmt4;
|
|
a b
|
|
1 2
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
0
|
|
execute stmt4;
|
|
a b
|
|
1 2
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
1
|
|
alter table view_t drop column a;
|
|
alter table view_t add column a int after b;
|
|
update view_t set a=1;
|
|
execute stmt1;
|
|
a b
|
|
1 2
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
0
|
|
execute stmt1;
|
|
a b
|
|
1 2
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
1
|
|
execute stmt2;
|
|
c d
|
|
1 2
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
0
|
|
execute stmt2;
|
|
c d
|
|
1 2
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
1
|
|
execute stmt3;
|
|
c d
|
|
1 2
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
0
|
|
execute stmt3;
|
|
c d
|
|
1 2
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
1
|
|
execute stmt4;
|
|
a b
|
|
1 2
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
0
|
|
execute stmt4;
|
|
a b
|
|
1 2
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
1
|
|
drop table view_t;
|
|
drop view view1,view2,view3,view4;
|
|
set @@tidb_enable_window_function = 1;
|
|
drop table if exists t;
|
|
create table t(a int, b int);
|
|
insert into t values (1,1),(1,2),(2,1),(2,2);
|
|
create definer='root'@'localhost' view v as select a, first_value(a) over(rows between 1 preceding and 1 following), last_value(a) over(rows between 1 preceding and 1 following) from t;
|
|
prepare stmt from 'select * from v;';
|
|
execute stmt;
|
|
a first_value(a) over(rows between 1 preceding and 1 following) last_value(a) over(rows between 1 preceding and 1 following)
|
|
1 1 1
|
|
1 1 2
|
|
2 1 2
|
|
2 2 2
|
|
execute stmt;
|
|
a first_value(a) over(rows between 1 preceding and 1 following) last_value(a) over(rows between 1 preceding and 1 following)
|
|
1 1 1
|
|
1 1 2
|
|
2 1 2
|
|
2 2 2
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
1
|
|
drop view v;
|
|
set @@tidb_enable_window_function = default;
|
|
set tidb_enable_prepared_plan_cache=default;
|
|
set @@tidb_enable_collect_execution_info=default;
|
|
drop table if exists t;
|
|
create table t(a int, index idx_a(a));
|
|
drop table if exists r;
|
|
create table r(a int);
|
|
prepare stmt from 'select * from t;';
|
|
create binding for select * from t using select /*+ use_index(t, idx_a) */ * from t;
|
|
execute stmt;
|
|
a
|
|
execute stmt;
|
|
a
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
1
|
|
execute stmt;
|
|
a
|
|
select @@last_plan_from_binding;
|
|
@@last_plan_from_binding
|
|
1
|
|
create binding for select * from t using select /*+ ignore_plan_cache() */ * from t;
|
|
execute stmt;
|
|
a
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
0
|
|
execute stmt;
|
|
a
|
|
select @@last_plan_from_binding;
|
|
@@last_plan_from_binding
|
|
1
|
|
create binding for select * from t using select /*+ use_index(t, idx_a) */ * from t;
|
|
execute stmt;
|
|
a
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
1
|
|
execute stmt;
|
|
a
|
|
select @@last_plan_from_binding;
|
|
@@last_plan_from_binding
|
|
1
|
|
prepare stmt_join from 'select * from t, r where r.a = t.a;';
|
|
create binding for select * from t, r where r.a = t.a using select /*+ straight_join() */* from t, r where r.a = t.a;
|
|
execute stmt_join;
|
|
a a
|
|
execute stmt_join;
|
|
a a
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
1
|
|
execute stmt_join;
|
|
a a
|
|
select @@last_plan_from_binding;
|
|
@@last_plan_from_binding
|
|
1
|
|
create binding for select * from t, r where r.a = t.a using select /*+ ignore_plan_cache() */* from t, r where r.a = t.a;
|
|
execute stmt_join;
|
|
a a
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
0
|
|
execute stmt_join;
|
|
a a
|
|
select @@last_plan_from_binding;
|
|
@@last_plan_from_binding
|
|
1
|
|
create binding for select * from t, r where r.a = t.a using select /*+ straight_join() */* from t, r where r.a = t.a;
|
|
execute stmt_join;
|
|
a a
|
|
select @@last_plan_from_cache;
|
|
@@last_plan_from_cache
|
|
1
|
|
execute stmt_join;
|
|
a a
|
|
select @@last_plan_from_binding;
|
|
@@last_plan_from_binding
|
|
1
|
|
create database s1;
|
|
drop table if exists t;
|
|
create table t(a int);
|
|
drop table if exists r;
|
|
create table r(a int);
|
|
explain format='plan_tree' select * from information_schema.tables where table_name='t';
|
|
id task access object operator info
|
|
MemTableScan root table:TABLES table_name:["t"]
|
|
explain format='plan_tree' select * from information_schema.tables where lower(table_name)='t';
|
|
id task access object operator info
|
|
MemTableScan root table:TABLES table_name:["t"]
|
|
explain format='plan_tree' select * from information_schema.tables where upper(table_name)=upper('t');
|
|
id task access object operator info
|
|
MemTableScan root table:TABLES table_name:["t"]
|
|
explain format='plan_tree' select * from information_schema.REFERENTIAL_CONSTRAINTS where table_name='t';
|
|
id task access object operator info
|
|
MemTableScan root table:REFERENTIAL_CONSTRAINTS table_name:["t"]
|
|
explain format='plan_tree' select * from information_schema.KEY_COLUMN_USAGE where table_name='t';
|
|
id task access object operator info
|
|
MemTableScan root table:KEY_COLUMN_USAGE table_name:["t"]
|
|
explain format='plan_tree' select * from information_schema.STATISTICS where table_name='t';
|
|
id task access object operator info
|
|
MemTableScan root table:STATISTICS table_name:["t"]
|
|
explain format='plan_tree' select * from information_schema.STATISTICS where table_name='t' and table_name='r';
|
|
id task access object operator info
|
|
MemTableScan root table:STATISTICS skip_request:true
|
|
explain format='plan_tree' select * from information_schema.STATISTICS where table_name='t' or table_name='r';
|
|
id task access object operator info
|
|
MemTableScan root table:STATISTICS table_name:["r","t"]
|
|
explain format='plan_tree' select * from information_schema.tables where table_schema='t';
|
|
id task access object operator info
|
|
MemTableScan root table:TABLES table_schema:["t"]
|
|
explain format='plan_tree' select * from information_schema.KEY_COLUMN_USAGE where table_schema='s1';
|
|
id task access object operator info
|
|
MemTableScan root table:KEY_COLUMN_USAGE table_schema:["s1"]
|
|
explain format='plan_tree' select * from information_schema.STATISTICS where table_schema='s1';
|
|
id task access object operator info
|
|
MemTableScan root table:STATISTICS table_schema:["s1"]
|
|
explain format='plan_tree' select * from information_schema.STATISTICS where table_schema='s1' and table_schema='test';
|
|
id task access object operator info
|
|
MemTableScan root table:STATISTICS skip_request:true
|
|
explain format='plan_tree' select * from information_schema.STATISTICS where table_name='s1' and table_schema='test';
|
|
id task access object operator info
|
|
MemTableScan root table:STATISTICS table_name:["s1"], table_schema:["test"]
|
|
explain format='plan_tree' select * from information_schema.STATISTICS where table_schema='s1' or table_schema='test';
|
|
id task access object operator info
|
|
MemTableScan root table:STATISTICS table_schema:["s1","test"]
|
|
explain format='plan_tree' SELECT column_name FROM information_schema.statistics WHERE index_name = 'idx1' AND table_schema = DATABASE () AND table_name = 't' ORDER BY seq_in_index;
|
|
id task access object operator info
|
|
Projection root Column
|
|
└─Sort root Column
|
|
└─MemTableScan root table:STATISTICS index_name:["idx1"], table_name:["t"], table_schema:["plan_cache"]
|
|
explain format='plan_tree' SELECT table_comment FROM information_schema.tables WHERE table_schema = DATABASE () AND table_name = 't';
|
|
id task access object operator info
|
|
MemTableScan root table:TABLES table_name:["t"], table_schema:["plan_cache"]
|
|
explain format='plan_tree' SELECT * FROM information_schema.referential_constraints rc JOIN information_schema.key_column_usage fk USING (constraint_schema, constraint_name) WHERE fk.referenced_column_name != 't' AND fk.table_schema = DATABASE () AND fk.table_name = 't' AND rc.constraint_schema = DATABASE () AND rc.table_name = 't';
|
|
id task access object operator info
|
|
Projection root Column, Column, Column, Column, Column, Column, Column, Column, Column, Column, Column, Column, Column, Column, Column, Column, Column, Column, Column, Column, Column
|
|
└─HashJoin root inner join, equal:[eq(Column, Column) eq(Column, Column)]
|
|
├─Selection(Build) root ne(Column, "t")
|
|
│ └─MemTableScan root table:KEY_COLUMN_USAGE constraint_schema:["plan_cache"], table_name:["t"], table_schema:["plan_cache"]
|
|
└─MemTableScan(Probe) root table:REFERENTIAL_CONSTRAINTS constraint_schema:["plan_cache"], table_name:["t"]
|