280 lines
14 KiB
Plaintext
280 lines
14 KiB
Plaintext
set tidb_cost_model_version=1;
|
|
DROP TABLE IF EXISTS person;
|
|
CREATE TABLE person (
|
|
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
address_info JSON,
|
|
city VARCHAR(64) AS (JSON_UNQUOTE(JSON_EXTRACT(address_info, '$.city'))) STORED,
|
|
KEY (city)
|
|
);
|
|
EXPLAIN format = 'plan_tree' SELECT name, id FROM person WHERE city = 'Beijing';
|
|
id task access object operator info
|
|
Projection root generated_columns.person.name, generated_columns.person.id
|
|
└─IndexLookUp root
|
|
├─IndexRangeScan(Build) cop[tikv] table:person, index:city(city) range:["Beijing","Beijing"], keep order:false, stats:pseudo
|
|
└─TableRowIDScan(Probe) cop[tikv] table:person keep order:false, stats:pseudo
|
|
DROP TABLE IF EXISTS `sgc`;
|
|
CREATE TABLE `sgc` (
|
|
`j1` JSON DEFAULT NULL,
|
|
`j2` JSON DEFAULT NULL,
|
|
`a` int(11) GENERATED ALWAYS AS (JSON_EXTRACT(`j1`, "$.a")) STORED,
|
|
`b` int(2) GENERATED ALWAYS AS (JSON_CONTAINS(j2, '1')) STORED,
|
|
KEY `idx_a` (`a`),
|
|
KEY `idx_b` (`b`),
|
|
KEY `idx_a_b` (`a`,`b`)
|
|
);
|
|
EXPLAIN format = 'plan_tree' SELECT a FROM sgc where a < 3;
|
|
id task access object operator info
|
|
IndexReader root index:IndexRangeScan
|
|
└─IndexRangeScan cop[tikv] table:sgc, index:idx_a(a) range:[-inf,3), keep order:false, stats:pseudo
|
|
EXPLAIN format = 'plan_tree' SELECT a, b FROM sgc where a < 3;
|
|
id task access object operator info
|
|
IndexReader root index:IndexRangeScan
|
|
└─IndexRangeScan cop[tikv] table:sgc, index:idx_a_b(a, b) range:[-inf,3), keep order:false, stats:pseudo
|
|
EXPLAIN format = 'plan_tree' SELECT a, b from sgc where b < 3;
|
|
id task access object operator info
|
|
IndexReader root index:Selection
|
|
└─Selection cop[tikv] lt(generated_columns.sgc.b, 3)
|
|
└─IndexFullScan cop[tikv] table:sgc, index:idx_a_b(a, b) keep order:false, stats:pseudo
|
|
EXPLAIN format = 'plan_tree' SELECT a, b from sgc where a < 3 and b < 3;
|
|
id task access object operator info
|
|
IndexReader root index:Selection
|
|
└─Selection cop[tikv] lt(generated_columns.sgc.b, 3)
|
|
└─IndexRangeScan cop[tikv] table:sgc, index:idx_a_b(a, b) range:[-inf,3), keep order:false, stats:pseudo
|
|
DROP TABLE IF EXISTS sgc1,
|
|
sgc2;
|
|
CREATE TABLE `sgc1` (
|
|
`j1` JSON,
|
|
`j2` JSON,
|
|
`a` INT AS (JSON_EXTRACT(j1, "$.a")) STORED,
|
|
`b` VARCHAR(20) AS (JSON_KEYS(j2)) STORED,
|
|
KEY `idx_a` (`a`),
|
|
KEY `idx_b` (`b`),
|
|
KEY `idx_a_b` (`a`, `b`)
|
|
);
|
|
CREATE TABLE `sgc2` (
|
|
`j1` JSON,
|
|
`j2` JSON,
|
|
`a` INT AS (JSON_EXTRACT(j1, "$.a")) STORED,
|
|
`b` VARCHAR(20) AS (JSON_KEYS(j2)) STORED,
|
|
KEY `idx_a` (`a`),
|
|
KEY `idx_b` (`b`),
|
|
KEY `idx_a_b` (`a`, `b`)
|
|
);
|
|
INSERT INTO sgc1(j1, j2)
|
|
VALUES ('{"a": 1}', '{"1": "1"}'),
|
|
('{"a": 1}', '{"1": "1"}'),
|
|
('{"a": 1}', '{"1": "1"}'),
|
|
('{"a": 1}', '{"1": "1"}'),
|
|
('{"a": 1}', '{"1": "1"}');
|
|
INSERT INTO sgc2(j1, j2)
|
|
VALUES ('{"a": 1}', '{"1": "1"}');
|
|
ANALYZE TABLE sgc1, sgc2 predicate columns;
|
|
EXPLAIN format = 'plan_tree' SELECT /*+ TIDB_INLJ(sgc1, sgc2) */ * from sgc1 join sgc2 on sgc1.a=sgc2.a;
|
|
id task access object operator info
|
|
Projection root generated_columns.sgc1.j1, generated_columns.sgc1.j2, generated_columns.sgc1.a, generated_columns.sgc1.b, generated_columns.sgc2.j1, generated_columns.sgc2.j2, generated_columns.sgc2.a, generated_columns.sgc2.b
|
|
└─IndexJoin root inner join, inner:IndexLookUp, outer key:generated_columns.sgc2.a, inner key:generated_columns.sgc1.a, equal cond:eq(generated_columns.sgc2.a, generated_columns.sgc1.a)
|
|
├─TableReader(Build) root data:Selection
|
|
│ └─Selection cop[tikv] not(isnull(generated_columns.sgc2.a))
|
|
│ └─TableFullScan cop[tikv] table:sgc2 keep order:false
|
|
└─IndexLookUp(Probe) root
|
|
├─Selection(Build) cop[tikv] not(isnull(generated_columns.sgc1.a))
|
|
│ └─IndexRangeScan cop[tikv] table:sgc1, index:idx_a(a) range: decided by [eq(generated_columns.sgc1.a, generated_columns.sgc2.a)], keep order:false
|
|
└─TableRowIDScan(Probe) cop[tikv] table:sgc1 keep order:false
|
|
EXPLAIN format = 'plan_tree' SELECT * from sgc1 join sgc2 on sgc1.a=sgc2.a;
|
|
id task access object operator info
|
|
Projection root generated_columns.sgc1.j1, generated_columns.sgc1.j2, generated_columns.sgc1.a, generated_columns.sgc1.b, generated_columns.sgc2.j1, generated_columns.sgc2.j2, generated_columns.sgc2.a, generated_columns.sgc2.b
|
|
└─HashJoin root inner join, equal:[eq(generated_columns.sgc2.a, generated_columns.sgc1.a)]
|
|
├─TableReader(Build) root data:Selection
|
|
│ └─Selection cop[tikv] not(isnull(generated_columns.sgc2.a))
|
|
│ └─TableFullScan cop[tikv] table:sgc2 keep order:false
|
|
└─TableReader(Probe) root data:Selection
|
|
└─Selection cop[tikv] not(isnull(generated_columns.sgc1.a))
|
|
└─TableFullScan cop[tikv] table:sgc1 keep order:false
|
|
set @old_prune_mode = @@tidb_partition_prune_mode;
|
|
set @@tidb_partition_prune_mode='static';
|
|
DROP TABLE IF EXISTS sgc3;
|
|
CREATE TABLE sgc3 (
|
|
j JSON,
|
|
a INT AS (JSON_EXTRACT(j, "$.a")) STORED
|
|
)
|
|
PARTITION BY RANGE (a) (
|
|
PARTITION p0 VALUES LESS THAN (1),
|
|
PARTITION p1 VALUES LESS THAN (2),
|
|
PARTITION p2 VALUES LESS THAN (3),
|
|
PARTITION p3 VALUES LESS THAN (4),
|
|
PARTITION p4 VALUES LESS THAN (5),
|
|
PARTITION p5 VALUES LESS THAN (6),
|
|
PARTITION max VALUES LESS THAN MAXVALUE);
|
|
EXPLAIN format = 'plan_tree' SELECT * FROM sgc3 WHERE a <= 1;
|
|
id task access object operator info
|
|
PartitionUnion root
|
|
├─TableReader root data:Selection
|
|
│ └─Selection cop[tikv] le(generated_columns.sgc3.a, 1)
|
|
│ └─TableFullScan cop[tikv] table:sgc3, partition:p0 keep order:false, stats:pseudo
|
|
└─TableReader root data:Selection
|
|
└─Selection cop[tikv] le(generated_columns.sgc3.a, 1)
|
|
└─TableFullScan cop[tikv] table:sgc3, partition:p1 keep order:false, stats:pseudo
|
|
EXPLAIN format = 'plan_tree' SELECT * FROM sgc3 WHERE a < 7;
|
|
id task access object operator info
|
|
PartitionUnion root
|
|
├─TableReader root data:Selection
|
|
│ └─Selection cop[tikv] lt(generated_columns.sgc3.a, 7)
|
|
│ └─TableFullScan cop[tikv] table:sgc3, partition:p0 keep order:false, stats:pseudo
|
|
├─TableReader root data:Selection
|
|
│ └─Selection cop[tikv] lt(generated_columns.sgc3.a, 7)
|
|
│ └─TableFullScan cop[tikv] table:sgc3, partition:p1 keep order:false, stats:pseudo
|
|
├─TableReader root data:Selection
|
|
│ └─Selection cop[tikv] lt(generated_columns.sgc3.a, 7)
|
|
│ └─TableFullScan cop[tikv] table:sgc3, partition:p2 keep order:false, stats:pseudo
|
|
├─TableReader root data:Selection
|
|
│ └─Selection cop[tikv] lt(generated_columns.sgc3.a, 7)
|
|
│ └─TableFullScan cop[tikv] table:sgc3, partition:p3 keep order:false, stats:pseudo
|
|
├─TableReader root data:Selection
|
|
│ └─Selection cop[tikv] lt(generated_columns.sgc3.a, 7)
|
|
│ └─TableFullScan cop[tikv] table:sgc3, partition:p4 keep order:false, stats:pseudo
|
|
├─TableReader root data:Selection
|
|
│ └─Selection cop[tikv] lt(generated_columns.sgc3.a, 7)
|
|
│ └─TableFullScan cop[tikv] table:sgc3, partition:p5 keep order:false, stats:pseudo
|
|
└─TableReader root data:Selection
|
|
└─Selection cop[tikv] lt(generated_columns.sgc3.a, 7)
|
|
└─TableFullScan cop[tikv] table:sgc3, partition:max keep order:false, stats:pseudo
|
|
set @@tidb_partition_prune_mode='dynamic';
|
|
DROP TABLE sgc3;
|
|
CREATE TABLE sgc3 (
|
|
j JSON,
|
|
a INT AS (JSON_EXTRACT(j, "$.a")) STORED
|
|
)
|
|
PARTITION BY RANGE (a) (
|
|
PARTITION p0 VALUES LESS THAN (1),
|
|
PARTITION p1 VALUES LESS THAN (2),
|
|
PARTITION p2 VALUES LESS THAN (3),
|
|
PARTITION p3 VALUES LESS THAN (4),
|
|
PARTITION p4 VALUES LESS THAN (5),
|
|
PARTITION p5 VALUES LESS THAN (6),
|
|
PARTITION max VALUES LESS THAN MAXVALUE);
|
|
analyze table sgc3;
|
|
EXPLAIN format = 'plan_tree' SELECT * FROM sgc3 WHERE a <= 1;
|
|
id task access object operator info
|
|
TableReader root partition:p0,p1 data:Selection
|
|
└─Selection cop[tikv] le(generated_columns.sgc3.a, 1)
|
|
└─TableFullScan cop[tikv] table:sgc3 keep order:false, stats:pseudo
|
|
EXPLAIN format = 'plan_tree' SELECT * FROM sgc3 WHERE a < 7;
|
|
id task access object operator info
|
|
TableReader root partition:all data:Selection
|
|
└─Selection cop[tikv] lt(generated_columns.sgc3.a, 7)
|
|
└─TableFullScan cop[tikv] table:sgc3 keep order:false, stats:pseudo
|
|
set @@tidb_partition_prune_mode = @old_prune_mode;
|
|
DROP TABLE IF EXISTS t1;
|
|
CREATE TABLE t1(a INT, b INT AS (a+1) VIRTUAL, c INT AS (b+1) VIRTUAL, d INT AS (c+1) VIRTUAL, KEY(b), INDEX IDX(c, d));
|
|
INSERT INTO t1 (a) VALUES (0);
|
|
EXPLAIN format = 'plan_tree' SELECT b FROM t1 WHERE b=1;
|
|
id task access object operator info
|
|
IndexReader root index:IndexRangeScan
|
|
└─IndexRangeScan cop[tikv] table:t1, index:b(b) range:[1,1], keep order:false, stats:pseudo
|
|
EXPLAIN format = 'plan_tree' SELECT b, c, d FROM t1 WHERE b=1;
|
|
id task access object operator info
|
|
Projection root generated_columns.t1.b, generated_columns.t1.c, generated_columns.t1.d
|
|
└─IndexLookUp root
|
|
├─IndexRangeScan(Build) cop[tikv] table:t1, index:b(b) range:[1,1], keep order:false, stats:pseudo
|
|
└─TableRowIDScan(Probe) cop[tikv] table:t1 keep order:false, stats:pseudo
|
|
EXPLAIN format = 'plan_tree' SELECT * FROM t1 WHERE b=1;
|
|
id task access object operator info
|
|
IndexLookUp root
|
|
├─IndexRangeScan(Build) cop[tikv] table:t1, index:b(b) range:[1,1], keep order:false, stats:pseudo
|
|
└─TableRowIDScan(Probe) cop[tikv] table:t1 keep order:false, stats:pseudo
|
|
EXPLAIN format = 'plan_tree' SELECT c FROM t1 WHERE c=2 AND d=3;
|
|
id task access object operator info
|
|
Projection root generated_columns.t1.c
|
|
└─IndexReader root index:IndexRangeScan
|
|
└─IndexRangeScan cop[tikv] table:t1, index:IDX(c, d) range:[2 3,2 3], keep order:false, stats:pseudo
|
|
DROP TABLE IF EXISTS person;
|
|
CREATE TABLE person (
|
|
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
address_info JSON,
|
|
city_no INT AS (JSON_EXTRACT(address_info, '$.city_no')) VIRTUAL,
|
|
KEY(city_no));
|
|
INSERT INTO person (name, address_info) VALUES ("John", CAST('{"city_no": 1}' AS JSON));
|
|
EXPLAIN format = 'plan_tree' SELECT name FROM person where city_no=1;
|
|
id task access object operator info
|
|
Projection root generated_columns.person.name
|
|
└─Projection root generated_columns.person.name, generated_columns.person.city_no
|
|
└─IndexLookUp root
|
|
├─IndexRangeScan(Build) cop[tikv] table:person, index:city_no(city_no) range:[1,1], keep order:false, stats:pseudo
|
|
└─TableRowIDScan(Probe) cop[tikv] table:person keep order:false, stats:pseudo
|
|
DROP TABLE IF EXISTS t1;
|
|
CREATE TABLE t1 (a INT,
|
|
b INT GENERATED ALWAYS AS (-a) VIRTUAL,
|
|
c INT GENERATED ALWAYS AS (-a) STORED,
|
|
index (c));
|
|
INSERT INTO t1 (a) VALUES (2), (1), (1), (3), (NULL);
|
|
EXPLAIN format = 'plan_tree' SELECT sum(a) FROM t1 GROUP BY b;
|
|
id task access object operator info
|
|
HashAgg root group by:Column, funcs:sum(Column)->Column
|
|
└─Projection root cast(generated_columns.t1.a, decimal(10,0) BINARY)->Column, generated_columns.t1.b->Column
|
|
└─TableReader root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t1 keep order:false, stats:pseudo
|
|
EXPLAIN format = 'plan_tree' SELECT sum(a) FROM t1 GROUP BY c;
|
|
id task access object operator info
|
|
HashAgg root group by:generated_columns.t1.c, funcs:sum(Column)->Column
|
|
└─TableReader root data:HashAgg
|
|
└─HashAgg cop[tikv] group by:generated_columns.t1.c, funcs:sum(generated_columns.t1.a)->Column
|
|
└─TableFullScan cop[tikv] table:t1 keep order:false, stats:pseudo
|
|
EXPLAIN format = 'plan_tree' SELECT sum(b) FROM t1 GROUP BY a;
|
|
id task access object operator info
|
|
HashAgg root group by:Column, funcs:sum(Column)->Column
|
|
└─Projection root cast(generated_columns.t1.b, decimal(10,0) BINARY)->Column, generated_columns.t1.a->Column
|
|
└─TableReader root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t1 keep order:false, stats:pseudo
|
|
EXPLAIN format = 'plan_tree' SELECT sum(b) FROM t1 GROUP BY c;
|
|
id task access object operator info
|
|
HashAgg root group by:Column, funcs:sum(Column)->Column
|
|
└─Projection root cast(generated_columns.t1.b, decimal(10,0) BINARY)->Column, generated_columns.t1.c->Column
|
|
└─Projection root generated_columns.t1.b, generated_columns.t1.c
|
|
└─TableReader root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t1 keep order:false, stats:pseudo
|
|
EXPLAIN format = 'plan_tree' SELECT sum(c) FROM t1 GROUP BY a;
|
|
id task access object operator info
|
|
HashAgg root group by:generated_columns.t1.a, funcs:sum(Column)->Column
|
|
└─TableReader root data:HashAgg
|
|
└─HashAgg cop[tikv] group by:generated_columns.t1.a, funcs:sum(generated_columns.t1.c)->Column
|
|
└─TableFullScan cop[tikv] table:t1 keep order:false, stats:pseudo
|
|
EXPLAIN format = 'plan_tree' SELECT sum(c) FROM t1 GROUP BY b;
|
|
id task access object operator info
|
|
HashAgg root group by:Column, funcs:sum(Column)->Column
|
|
└─Projection root cast(generated_columns.t1.c, decimal(10,0) BINARY)->Column, generated_columns.t1.b->Column
|
|
└─Projection root generated_columns.t1.b, generated_columns.t1.c
|
|
└─TableReader root data:TableFullScan
|
|
└─TableFullScan cop[tikv] table:t1 keep order:false, stats:pseudo
|
|
DROP TABLE IF EXISTS tu;
|
|
CREATE TABLE tu (a INT, b INT, c INT GENERATED ALWAYS AS (a + b) VIRTUAL, primary key (a), unique key uk(c));
|
|
INSERT INTO tu(a, b) VALUES(1, 2);
|
|
EXPLAIN format = 'plan_tree' SELECT * FROM tu WHERE c = 1;
|
|
id task access object operator info
|
|
Point_Get root table:tu, index:uk(c)
|
|
EXPLAIN format = 'plan_tree' SELECT a, c FROM tu WHERE c = 1;
|
|
id task access object operator info
|
|
Projection root generated_columns.tu.a, generated_columns.tu.c
|
|
└─Point_Get root table:tu, index:uk(c)
|
|
EXPLAIN format = 'plan_tree' SELECT * FROM tu WHERE c in(1, 2, 3);
|
|
id task access object operator info
|
|
Batch_Point_Get root table:tu, index:uk(c) keep order:false, desc:false
|
|
EXPLAIN format = 'plan_tree' SELECT c, a FROM tu WHERE c in(1, 2, 3);
|
|
id task access object operator info
|
|
Projection root generated_columns.tu.c, generated_columns.tu.a
|
|
└─Batch_Point_Get root table:tu, index:uk(c) keep order:false, desc:false
|
|
set @@sql_mode=default;
|
|
drop table if exists t1;
|
|
create table t1(a int);
|
|
insert into t1 values(0);
|
|
alter table t1 add index i((100/a));
|
|
Error 1365 (22012): Division by 0
|
|
drop table t1;
|
|
set @@sql_mode='';
|
|
create table t1(a int);
|
|
insert into t1 values(0);
|
|
alter table t1 add index i((100/a));
|
|
drop table t1;
|
|
set @@sql_mode=default;
|