-
Notifications
You must be signed in to change notification settings - Fork 6
/
Copy pathoptimization_sql_statements.sql
188 lines (175 loc) · 10.6 KB
/
optimization_sql_statements.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
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
-- 表结构设计， select , from , join, where, order , limit , sub query, with,
# select , where , limit 尽力要有
# 能sub query的尽力sub query， 即
# index， index lookup优于index Range Scan优于Nested loop semijoin优于
# Index lookup
# Index Range Scan , range查找
# Inner hash join ， join
# Nested loop inner join
show databases;
# Table scan
# select , where , limit
create table if not exists t
(
c TINYINT UNSIGNED NOT NULL
);
insert into t
values (1),
(2),
(3);
explain analyze
select *
from t
where c << 256;
# | EXPLAIN |
# +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
# | -> Filter: (0 <> (t.c << 256)) (cost=0.55 rows=3) (actual time=0.027..0.027 rows=0 loops=1)
# -> Table scan on t (cost=0.55 rows=3) (actual time=0.020..0.024 rows=3 loops=1)
explain analyze
select *
from t
where c = 1;
# | EXPLAIN |
# +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
# | -> Filter: (t.c = 1) (cost=0.55 rows=1) (actual time=0.020..0.025 rows=1 loops=1)
# -> Table scan on t (cost=0.55 rows=3) (actual time=0.019..0.023 rows=3 loops=1)
# |
# +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
# 1 row in set (0.00 sec)
# range optimization
drop table t;
create table if not exists t
(
c TINYINT UNSIGNED NOT NULL,
key_col_1 int,
key_col_2 int
);
create index t_key_col_1 on t (key_col_1);
create index t_key_col_2 on t (key_col_2);
insert into t
values (1, 1, 1),
(2, 2, 2),
(3, 3, 3);
explain analyze
select *
from t
where key_col_1 > 1;
explain analyze
select *
from t
where key_col_1 > 1
and key_col_2 < 3;
# | EXPLAIN |
# +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
# | -> Filter: (t.key_col_2 < 3) (cost=2.06 rows=3) (actual time=0.025..0.040 rows=2 loops=1)
# -> Index range scan on t using t_key_col_1, with index condition: (t.key_col_1 > 1) (cost=2.06 rows=4) (actual time=0.024..0.037 rows=4 loops=1)
# |
drop index t_key_col_1 on t;
drop index t_key_col_2 on t;
create index t_key_col on t (key_col_1, key_col_2);
explain analyze
select *
from t
where key_col_1 > 1
and key_col_2 < 3;
# | EXPLAIN |
# +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
# | -> Index range scan on t using t_key_col, with index condition: ((t.key_col_1 > 1) and (t.key_col_2 < 3)) (cost=2.06 rows=4) (actual time=0.022..0.029 rows=2 loops=1)
# table scan when or
explain analyze
select *
from t
where key_col_1 > 1
or key_col_2 < 3;
# | EXPLAIN |
# +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
# | -> Filter: ((t.key_col_1 > 1) or (t.key_col_2 < 3)) (cost=0.85 rows=3) (actual time=0.017..0.024 rows=6 loops=1)
# -> Table scan on t (cost=0.85 rows=6) (actual time=0.015..0.021 rows=6 loops=1)
# hash join optimization
drop table if exists t1, t2, t3;
create table if not exists t1
(
c1 int,
c2 int
);
create table if not exists t2
(
c1 int,
c2 int
);
create table if not exists t3
(
c1 int,
c2 int
);
insert into t1(c1, c2)
values (1, 1),
(2, 2),
(3, 3),
(4, 4);
insert into t2
select *
from t1;
insert into t3
select *
from t1;
# Inner hash join
explain analyze
select *
from t1
join t2
where t1.c1 = t2.c1;
# | EXPLAIN |
# +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
# | -> Inner hash join (t2.c1 = t1.c1) (cost=2.50 rows=4) (actual time=0.033..0.035 rows=4 loops=1)
# -> Table scan on t2 (cost=0.09 rows=4) (actual time=0.003..0.005 rows=4 loops=1)
# -> Hash
# -> Table scan on t1 (cost=0.65 rows=4) (actual time=0.016..0.020 rows=4 loops=1)
create index t1_c1 on t1 (c1);
create index t2_c1 on t2 (c1);
create index t3_c1 on t3 (c1);
# Index lookup
explain analyze
select c1
from t1
where c1 = 1;
# | EXPLAIN |
# +---------------------------------------------------------------------------------------------------------+
# | -> Index lookup on t1 using t1_c1 (c1=1) (cost=0.35 rows=1) (actual time=0.012..0.014 rows=1 loops=1)
# Nested loop inner join
explain analyze
select *
from t1
join t2
where t1.c1 = t2.c1;
# | EXPLAIN |
# +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
# | -> Nested loop inner join (cost=2.05 rows=4) (actual time=0.064..0.078 rows=4 loops=1)
# -> Filter: (t1.c1 is not null) (cost=0.65 rows=4) (actual time=0.049..0.052 rows=4 loops=1)
# -> Table scan on t1 (cost=0.65 rows=4) (actual time=0.048..0.050 rows=4 loops=1)
# -> Index lookup on t2 using t2_c1 (c1=t1.c1) (cost=0.28 rows=1) (actual time=0.005..0.006 rows=1 loops=4)
# |
explain analyze
select *
from t1
left join t2 on t1.c1 = t2.c1
where t1.c1 = t2.c1;
# | EXPLAIN |
# +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
# | -> Nested loop inner join (cost=2.05 rows=4) (actual time=0.027..0.040 rows=4 loops=1)
# -> Table scan on t1 (cost=0.65 rows=4) (actual time=0.015..0.018 rows=4 loops=1)
# -> Index lookup on t2 using t2_c1 (c1=t1.c1) (cost=0.28 rows=1) (actual time=0.004..0.005 rows=1 loops=4)
explain analyze
select *
from t1
join t2 on (t1.c1 = t2.c1 and t1.c2 < t2.c2)
join t3 on (t2.c1 = t3.c1);
# | EXPLAIN |
# +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
# | -> Nested loop inner join (cost=2.52 rows=1) (actual time=0.054..0.054 rows=0 loops=1)
# -> Nested loop inner join (cost=2.05 rows=1) (actual time=0.053..0.053 rows=0 loops=1)
# -> Filter: (t1.c1 is not null) (cost=0.65 rows=4) (actual time=0.019..0.024 rows=4 loops=1)
# -> Table scan on t1 (cost=0.65 rows=4) (actual time=0.018..0.022 rows=4 loops=1)
# -> Filter: (t1.c2 < t2.c2) (cost=0.26 rows=0) (actual time=0.007..0.007 rows=0 loops=4)
# -> Index lookup on t2 using t2_c1 (c1=t1.c1) (cost=0.26 rows=1) (actual time=0.005..0.006 rows=1 loops=4)
# -> Index lookup on t3 using t3_c1 (c1=t1.c1) (cost=0.33 rows=1) (never executed)