-
Notifications
You must be signed in to change notification settings - Fork 4
/
Copy path07-demo-cluster-index-02-show.sql
executable file
·129 lines (112 loc) · 5.32 KB
/
07-demo-cluster-index-02-show.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
/* source 07-demo-cluster-index-02-show.sql */
/* Compare the execution plans with CLUSTERED and NONCLUSTERED primary keys table */
/* Index_and_Region 1 */
select 'Table test.auto_random_t1_clustered' as Index_and_Region;
/* Show indexes */
select 'Table test.auto_random_t1_clustered' as Indexes;
SHOW INDEXES FROM test.auto_random_t1_clustered\G
/* Show regions */
select 'Table test.auto_random_t1_clustered' as Regions;
SHOW TABLE test.auto_random_t1_clustered regions\G
/* Index_and_Region 2 */
select 'Table test.t2_nonclustered' as Index_and_Region;
/* Show indexes */
select 'Table test.t2_nonclustered' as Indexes;
SHOW INDEXES FROM test.t2_nonclustered\G
/* Show regions */
select 'Table test.t2_nonclustered' as Regions;
SHOW TABLE test.t2_nonclustered regions\G
/* Index_and_Region 3 */
select 'Table test.t3_nonclustered' as Index_and_Region;
/* Show indexes */
select 'Table test.t3_nonclustered' as Indexes;
SHOW INDEXES FROM test.t3_nonclustered\G
/* Show regions */
select 'Table test.t3_nonclustered' as Regions;
SHOW TABLE test.t3_nonclustered regions\G
/* Index_and_Region 4 */
select 'Table test.t4_clustered' as Index_and_Region;
/* Show indexes */
select 'Table test.t4_clustered' as Indexes;
SHOW INDEXES FROM test.t4_clustered\G
/* Show regions */
select 'Table test.t4_clustered' as Regions;
SHOW TABLE test.t4_clustered regions\G
/* Index_and_Region 5 */
select 'Table test.t5_nonclustered' as Index_and_Region;
/* Show indexes */
select 'Table test.t5_nonclustered' as Indexes;
SHOW INDEXES FROM test.t5_nonclustered\G
/* Show regions */
select 'Table test.t5_nonclustered' as Regions;
SHOW TABLE test.t5_nonclustered regions\G
/* Index_and_Region 6 Composite */
select 'Table test.t6_nonclustered' as Index_and_Region;
/* Show indexes */
select 'Table test.t6_nonclustered' as Indexes;
SHOW INDEXES FROM test.t6_nonclustered\G
/* Show regions */
select 'Table test.t6_nonclustered' as Regions;
SHOW TABLE test.t6_nonclustered regions\G
/* Index_and_Region 7 Composite */
select 'Table test.t7_clustered' as Index_and_Region;
/* Show indexes */
select 'Table test.t7_clustered' as Indexes;
SHOW INDEXES FROM test.t7_clustered\G
/* Show regions */
select 'Table test.t7_clustered' as Regions;
SHOW TABLE test.t7_clustered regions\G
/* Execution Plan PointGet */
select 'select * from test.auto_random_t1_clustered where id=32471241834;' as "PointGet";
select "CLUSTETED" as Explain_t1;
explain select * from test.auto_random_t1_clustered where id=8646911284551360512;
select "NONCLUSTETED" as Explain_t2;
explain select * from test.t2_nonclustered where id=8646911284551360512;
select "NONCLUSTETED" as Explain_t3;
explain select * from test.t3_nonclustered where id='8646911284551360512';
select "CLUSTETED" as Explain_t4;
explain select * from test.t4_clustered where id='8646911284551360512';
select "NONCLUSTETED" as Explain_t5;
explain select * from test.t5_nonclustered where id=8646911284551360512;
select "NONCLUSTETED" as Explain_t6;
explain select * from test.t6_nonclustered where id=8646911284551360512 and id2=8646911284551360512;
select "CLUSTETED" as Explain_t7;
explain select * from test.t7_clustered where id=8646911284551360512 and id2=8646911284551360512;
/* Execution Plan Range */
select 'select * from test.auto_random_t1_clustered where id between 1230 and 1232;' as "Range Scan";
select "CLUSTETED" as Explain_t1;
explain select * from test.auto_random_t1_clustered where id between 1230 and 1232;
select "NONCLUSTETED" as Explain_t2;
explain select * from test.t2_nonclustered where id between 1230 and 1232;
select "NONCLUSTETED" as Explain_t3;
explain select * from test.t3_nonclustered where id between '1230' and '1232';
select "CLUSTETED" as Explain_t4;
explain select * from test.t4_clustered where id between '1230' and '1232';
select "NONCLUSTETED" as Explain_t5;
explain select * from test.t5_nonclustered where id between 1230 and 1232;
select "NONCLUSTETED" as Explain_t6;
explain select * from test.t6_nonclustered where id between 1230 and 1232;
select "CLUSTETED" as Explain_t7;
explain select * from test.t7_clustered where id between 1230 and 1232;
/* _tidb_rowid */
select 'test.auto_random_t1_clustered' as "_tidb_rowid";
select _tidb_rowid from test.auto_random_t1_clustered limit 1;
select min(_tidb_rowid), max(_tidb_rowid), count(*) from test.auto_random_t1_clustered;
select 'test.t2_nonclustered' as "_tidb_rowid";
select _tidb_rowid from test.t2_nonclustered limit 1;
select min(_tidb_rowid), max(_tidb_rowid), count(*) from test.t2_nonclustered;
select 'test.t3_nonclustered' as "_tidb_rowid";
select _tidb_rowid from test.t3_nonclustered limit 1;
select min(_tidb_rowid), max(_tidb_rowid), count(*) from test.t3_nonclustered;
select 'test.t4_clustered' as "_tidb_rowid";
select _tidb_rowid from test.t4_clustered limit 1;
select min(_tidb_rowid), max(_tidb_rowid), count(*) from test.t4_clustered;
select 'test.t5_nonclustered' as "_tidb_rowid";
select _tidb_rowid from test.t5_nonclustered limit 1;
select min(_tidb_rowid), max(_tidb_rowid), count(*) from test.t5_nonclustered;
select 'test.t6_nonclustered composite' as "_tidb_rowid";
select _tidb_rowid from test.t6_nonclustered limit 1;
select min(_tidb_rowid), max(_tidb_rowid), count(*) from test.t6_nonclustered;
select 'test.t7_clustered composite' as "_tidb_rowid";
select _tidb_rowid from test.t7_clustered limit 1;
select min(_tidb_rowid), max(_tidb_rowid), count(*) from test.t7_clustered;