180 lines
5.8 KiB
Plaintext
180 lines
5.8 KiB
Plaintext
drop table if exists t;
|
|
create table t(a bigint unsigned primary key, b int, c int, index idx(a, b));
|
|
insert into t values(1, 1, 1), (9223372036854775807, 2, 2);
|
|
select * from t use index(idx) order by a;
|
|
a b c
|
|
1 1 1
|
|
9223372036854775807 2 2
|
|
set sql_mode='STRICT_TRANS_TABLES';
|
|
drop table if exists t;
|
|
create table t(a int primary key, b int, c int, index idx(b));
|
|
insert into t values(1, 1, 1), (2, 2 ,2), (3, 3, 3), (4, 4, 4), (5, 5, 5), (6, 6, 6), (7, 7, 7), (8, 8, 8), (9, 9, 9);
|
|
analyze table t;
|
|
select t.c in (select count(*) from t s ignore index(idx), t t1 where s.a = t.a and s.a = t1.a) from t order by 1 desc;
|
|
t.c in (select count(*) from t s ignore index(idx), t t1 where s.a = t.a and s.a = t1.a)
|
|
1
|
|
0
|
|
0
|
|
0
|
|
0
|
|
0
|
|
0
|
|
0
|
|
0
|
|
select t.c in (select count(*) from t s use index(idx), t t1 where s.b = t.a and s.a = t1.a) from t order by 1 desc;
|
|
t.c in (select count(*) from t s use index(idx), t t1 where s.b = t.a and s.a = t1.a)
|
|
1
|
|
0
|
|
0
|
|
0
|
|
0
|
|
0
|
|
0
|
|
0
|
|
0
|
|
select t.c in (select count(*) from t s use index(idx), t t1 where s.b = t.a and s.c = t1.a) from t order by 1 desc;
|
|
t.c in (select count(*) from t s use index(idx), t t1 where s.b = t.a and s.c = t1.a)
|
|
1
|
|
0
|
|
0
|
|
0
|
|
0
|
|
0
|
|
0
|
|
0
|
|
0
|
|
set sql_mode=default;
|
|
drop table if exists t;
|
|
create table t (id int default null, c varchar(20), unique id (id));
|
|
insert t (c) values ('a'), ('b'), ('c');
|
|
select * from t where id is null;
|
|
id c
|
|
NULL a
|
|
NULL b
|
|
NULL c
|
|
drop table t;
|
|
create table t (id int default null, b int default 1, c varchar(20), unique id_c(id, b));
|
|
insert t (c) values ('a'), ('b'), ('c');
|
|
select * from t where id is null and b = 1;
|
|
id b c
|
|
NULL 1 a
|
|
NULL 1 b
|
|
NULL 1 c
|
|
drop table t;
|
|
create table t (id int default null, c varchar(20), key id (id));
|
|
insert t (c) values ('a'), ('b'), ('c');
|
|
select * from t where id is null;
|
|
id c
|
|
NULL a
|
|
NULL b
|
|
NULL c
|
|
drop table if exists t;
|
|
create table t(a bigint unsigned primary key);
|
|
insert into t values(9223372036854775807), (18446744073709551615);
|
|
select max(a) from t;
|
|
max(a)
|
|
18446744073709551615
|
|
select * from t where a > 9223372036854775807;
|
|
a
|
|
18446744073709551615
|
|
select * from t where a < 9223372036854775808;
|
|
a
|
|
9223372036854775807
|
|
drop table if exists t;
|
|
create table t (a int, b int, key(a))
|
|
partition by range (a) (
|
|
partition p1 values less than (10),
|
|
partition p2 values less than (20),
|
|
partition p3 values less than (30),
|
|
partition p4 values less than (40));
|
|
insert into t values (1, 1), (2, 2), (11, 11), (12, 12), (21, 21), (22, 22), (31, 31), (32, 32);
|
|
set tidb_partition_prune_mode='dynamic';
|
|
analyze table t;
|
|
select * from t where a>=1 and a<=1;
|
|
a b
|
|
1 1
|
|
select * from t where a>=1 and a<=2;
|
|
a b
|
|
1 1
|
|
2 2
|
|
select * from t where a>=1 and a<12;
|
|
a b
|
|
1 1
|
|
11 11
|
|
2 2
|
|
select * from t where a>=1 and a<15;
|
|
a b
|
|
1 1
|
|
11 11
|
|
12 12
|
|
2 2
|
|
select * from t where a>15 and a<32;
|
|
a b
|
|
21 21
|
|
22 22
|
|
31 31
|
|
select * from t where a>30;
|
|
a b
|
|
31 31
|
|
32 32
|
|
select * from t where a>=1 and a<15 order by a;
|
|
a b
|
|
1 1
|
|
2 2
|
|
11 11
|
|
12 12
|
|
select * from t where a>=1 and a<15 order by a limit 1;
|
|
a b
|
|
1 1
|
|
select * from t where a>=1 and a<15 order by a limit 3;
|
|
a b
|
|
1 1
|
|
2 2
|
|
11 11
|
|
select * from t where a between 1 and 15 order by a limit 3;
|
|
a b
|
|
1 1
|
|
2 2
|
|
11 11
|
|
select * from t where a between 1 and 15 order by a limit 3 offset 1;
|
|
a b
|
|
2 2
|
|
11 11
|
|
12 12
|
|
set tidb_partition_prune_mode=default;
|
|
drop table if exists tbl;
|
|
create table tbl(a int, b int, c int, key idx_a(a));
|
|
insert into tbl values (0,0,0), (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5), (6,6,6), (7,7,7), (8,8,8), (9,9,9), (10,10,10), (11,11,11), (12,12,12), (13,13,13), (14,14,14), (15,15,15), (16,16,16), (17,17,17), (18,18,18), (19,19,19), (20,20,20), (21,21,21), (22,22,22), (23,23,23), (24,24,24), (25,25,25), (26,26,26), (27,27,27), (28,28,28), (29,29,29), (30,30,30), (31,31,31), (32,32,32), (33,33,33), (34,34,34), (35,35,35), (36,36,36), (37,37,37), (38,38,38), (39,39,39), (40,40,40), (41,41,41), (42,42,42), (43,43,43), (44,44,44), (45,45,45), (46,46,46), (47,47,47), (48,48,48), (49,49,49), (50,50,50), (51,51,51), (52,52,52), (53,53,53), (54,54,54), (55,55,55), (56,56,56), (57,57,57), (58,58,58), (59,59,59), (60,60,60), (61,61,61), (62,62,62), (63,63,63), (64,64,64), (65,65,65), (66,66,66), (67,67,67), (68,68,68), (69,69,69), (70,70,70), (71,71,71), (72,72,72), (73,73,73), (74,74,74), (75,75,75), (76,76,76), (77,77,77), (78,78,78), (79,79,79), (80,80,80), (81,81,81), (82,82,82), (83,83,83), (84,84,84), (85,85,85), (86,86,86), (87,87,87), (88,88,88), (89,89,89), (90,90,90), (91,91,91), (92,92,92), (93,93,93), (94,94,94), (95,95,95), (96,96,96), (97,97,97), (98,98,98), (99,99,99), (100,100,100);
|
|
select * from tbl use index(idx_a) where a > 99 order by a asc limit 1;
|
|
a b c
|
|
100 100 100
|
|
select * from tbl use index(idx_a) where a > 10 order by a asc limit 4,1;
|
|
a b c
|
|
15 15 15
|
|
drop table if exists t;
|
|
create table t(a int, b int, index k(b)) PARTITION BY HASH(a) partitions 4;
|
|
insert into t(a, b) values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);
|
|
explain format='plan_tree' select b from t use index(k) where b > 2 order by b limit 1 for update;
|
|
id task access object operator info
|
|
Projection root executor__distsql.t.b
|
|
└─SelectLock root for update 0
|
|
└─Limit root offset:0, count:1
|
|
└─IndexReader root partition:all index:Limit
|
|
└─Limit cop[tikv] offset:0, count:1
|
|
└─IndexRangeScan cop[tikv] table:t, index:k(b) range:(2,+inf], keep order:true, stats:pseudo
|
|
select b from t use index(k) where b > 2 order by b limit 1 for update;
|
|
b
|
|
3
|
|
analyze table t;
|
|
explain format='plan_tree' select b from t use index(k) where b > 2 order by b limit 1 for update;
|
|
id task access object operator info
|
|
Projection root executor__distsql.t.b
|
|
└─SelectLock root for update 0
|
|
└─Limit root offset:0, count:1
|
|
└─IndexReader root partition:all index:Limit
|
|
└─Limit cop[tikv] offset:0, count:1
|
|
└─IndexRangeScan cop[tikv] table:t, index:k(b) range:(2,+inf], keep order:true
|
|
select b from t use index(k) where b > 2 order by b limit 1 for update;
|
|
b
|
|
3
|