优化案例
前面用过的
tbiguser
表有
10000000
条记录
创建
tuser1
表和
tuser2
表,并初始化若干的数据。
create table tuser1(
id int primary key auto_increment,
name varchar(255),
address varchar(255)
);
create table tuser2(
id int primary key auto_increment,
name varchar(255),
address varchar(255)
);
看到
tuser1
和
tuser2
表有重复的数据。
需求:
tbiguser表按照地区分组统计求和,要求是在tuser1表和tuser2表中出现过的地区
按照需求写出
SQL
:
mysql> select * from tuser1 ;
+----+----------+-----------+
| id | name | address |
+----+----------+-----------+
| 1 | zhangfei | tianjin |
| 2 | zhaoyun | tianjin |
| 3 | diaochan | guangzhou |
| 4 | diaochan | xianggang |
| 5 | diaochan | hebei |
| 6 | diaochan | dongbei |
| 7 | diaochan | dongbei |
| 8 | diaochan | dongbei |
| 9 | diaochan | dongbei |
| 10 | diaochan | dongbei |
| 11 | 1 | 1 |
| 12 | 1 | 1 |
| 13 | 1 | 1 |
| 14 | 1 | 1 |
| 15 | 1 | 1 |
| 16 | 1 | 1 |
| 17 | 1 | 1 |
| 18 | 1 | 1 |
| 19 | 1 | 1 |
| 20 | 1 | 1 |
+----+----------+-----------+
20 rows in set (0.00 sec)
mysql> select * from tuser2;
+----+----------+-----------+
| id | name | address |
+----+----------+-----------+
| 1 | zhangfei | shanghai |
| 2 | zhaoyun | shanghai |
| 3 | diaochan | guangzhou |
| 4 | diaochan | xianggang |
| 5 | diaochan | hebei |
| 6 | diaochan | dongbei |
| 7 | diaochan | dongbei |
| 8 | diaochan | dongbei |
| 9 | diaochan | dongbei |
| 10 | diaochan | dongbei |
| 11 | 1 | 1 |
| 12 | 1 | 1 |
| 13 | 1 | 1 |
| 14 | 1 | 1 |
| 15 | 1 | 1 |
| 16 | 1 | 1 |
| 17 | 1 | 1 |
| 18 | 1 | 1 |
| 19 | 1 | 1 |
| 20 | 1 | 1 |
+----+----------+-----------+
20 rows in set (0.00 sec)
通过
explain
可以看到:
type:
为
ALL
说明没有索引,全表扫描
Using temporary
:说明使用了临时表
Using filesort
:说明使用了文件排序
Using where
:没有索引下推,在
Server
层进行了全表扫描和过滤
Using join buffer(Block Nested Loop)
:关联没有索引,有关联优化
第一次优化:
给
address
加索引
mysql>
select count
(id) num , address
from
tbiguser
where
address
in
(
select
distinct
address
from
tuser1)
group by
address
union select count
(id) num ,
address
from
tbiguser
where
address
in
(
select distinct
address
from
tuser2)
group by
address ;
+-----+----------+
| num | address |
+-----+----------+
|
105
| tianjin |
|
100
| shanghai |
+-----+----------+
2
rows
in set
(
14.43
sec)
mysql> explain
select count
(id) num , address
from
tbiguser
where
address
in
(
select distinct
address
from
tuser1)
group by
address
union select
count
(id) num , address
from
tbiguser
where
address
in
(
select distinct
address
from
tuser2)
group by
address ;
+----+--------------+-------------+------+---------------+------+---------+-----
-+---------+----------------------------------------------------+
| id | select_type |
table
| type | possible_keys | key | key_len | ref
| rows | Extra |
+----+--------------+-------------+------+---------------+------+---------+-----
-+---------+----------------------------------------------------+
|
1
| PRIMARY | <subquery2> | ALL |
NULL
|
NULL
|
NULL
|
NULL
|
NULL
| Using temporary; Using filesort |
|
1
| PRIMARY | tbiguser | ALL |
NULL
|
NULL
|
NULL
|
NULL
|
9754360
| Using
where
; Using
join
buffer (Block Nested Loop) |
|
2
| MATERIALIZED | tuser1 | ALL |
NULL
|
NULL
|
NULL
|
NULL
|
20
|
NULL
|
|
3
|
UNION
| <subquery4> | ALL |
NULL
|
NULL
|
NULL
|
NULL
|
NULL
| Using temporary; Using filesort |
|
3
|
UNION
| tbiguser | ALL |
NULL
|
NULL
|
NULL
|
NULL
|
9754360
| Using
where
; Using
join
buffer (Block Nested Loop) |
|
4
| MATERIALIZED | tuser2 | ALL |
NULL
|
NULL
|
NULL
|
NULL
|
20
|
NULL
|
|
NULL
|
UNION
RESULT | <union1,
3
> | ALL |
NULL
|
NULL
|
NULL
|
NULL
|
NULL
| Using temporary |
+----+--------------+-------------+------+---------------+------+---------+-----
-+---------+----------------------------------------------------+
7
rows
in set
(
0.00
sec)
--
给
address
加索引
alter table
tbiguser add index idx_addr(address);
type
:
index
,说明用到了索引 : 覆盖索引
Using temporary
:有临时表
Using where
:没有索引下推,在
Server
层进行了全表扫描和过滤
第二次优化:
alter table
tuser1 add index idx_addr(address);
alter table
tuser2 add index idx_addr(address);
--
再次运行
SQL
select count
(id) num , address
from
tbiguser
where
address
in
(
select
distinct
address
from
tuser1)
group by
address
union select count
(id) num ,
address
from
tbiguser
where
address
in
(
select distinct
address
from
tuser2)
group by
address ;
+-----+----------+
| num | address |
+-----+----------+
|
105
| tianjin |
|
100
| shanghai |
+-----+----------+
2
rows
in set
(
13.61
sec)
--
查看执行计划
mysql> explain
select count
(id) num , address
from
tbiguser
where
address
in
(
select distinct
address
from
tuser1)
group by
address
union select
count
(id) num , address
from
tbiguser
where
address
in
(
select distinct
address
from
tuser2)
group by
address ;
+----+--------------+-------------+--------+---------------+------------+-------
--+-----------------------+---------+--------------------------+
| id | select_type |
table
| type | possible_keys | key |
key_len | ref | rows | Extra |
+----+--------------+-------------+--------+---------------+------------+-------
--+-----------------------+---------+--------------------------+
|
1
| PRIMARY | tbiguser | index | idx_addr | idx_addr |
768
|
NULL
|
9754360
| Using
where
; Using index |
|
1
| PRIMARY | <subquery2> | eq_ref | <auto_key> | <auto_key> |
768
| demo
.tbiguser.address
|
1
|
NULL
|
|
2
| MATERIALIZED | tuser1 | index | idx_addr | idx_addr |
768
|
NULL
|
20
| Using index |
|
3
|
UNION
| tbiguser | index | idx_addr | idx_addr |
768
|
NULL
|
9754360
| Using
where
; Using index |
|
3
|
UNION
| <subquery4> | eq_ref | <auto_key> | <auto_key> |
768
| demo
.tbiguser.address
|
1
|
NULL
|
|
4
| MATERIALIZED | tuser2 | index | idx_addr | idx_addr |
768
|
NULL
|
20
| Using index |
|
NULL
|
UNION
RESULT | <union1,
3
> | ALL |
NULL
|
NULL
|
NULL
|
NULL
|
NULL
| Using temporary |
+----+--------------+-------------+--------+---------------+------------+-------
--+-----------------------+---------+--------------------------+
--
修改
sql
select count
(id) num , address
from
tbiguser
where
address
in
(
select distinct
address
from
tuser1)
or
address
in
(
select distinct
address
from
tuser2)
group
by
address
order by
address;
+-----+----------+
| num | address |
type
:
index
没有了临时表
第三次优化:
从前面的执行计划可以看出,索引只是使用了覆盖索引,
rows=9754360
, 说明还是几乎扫描了全表的
行
利用
address
索引,先过滤数据
+-----+----------+
|
100
| shanghai |
|
105
| tianjin |
+-----+----------+
2
rows
in set
(
3.54
sec)
--
运行执行计划
explain
select count
(id) num , address
from
tbiguser
where
address
in
(
select
distinct
address
from
tuser1)
or
address
in
(
select distinct
address
from
tuser2)
group by
address
order by
address;
+----+-------------+----------+-------+---------------+----------+---------+----
--+---------+--------------------------+
| id | select_type |
table
| type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+----------+-------+---------------+----------+---------+----
--+---------+--------------------------+
|
1
| PRIMARY | tbiguser | index | idx_addr | idx_addr |
768
|
NULL
|
9754360
| Using
where
; Using index |
|
3
| SUBQUERY | tuser2 | index | idx_addr | idx_addr |
768
|
NULL
|
20
| Using index |
|
2
| SUBQUERY | tuser1 | index | idx_addr | idx_addr |
768
|
NULL
|
20
| Using index |
+----+-------------+----------+-------+---------------+----------+---------+----
--+---------+--------------------------+
3
rows
in set
(
0.00
sec)
mysql>
select distinct
b.*
from
tuser1 a,tbiguser b
where
a
.address
=b
.address
;
+-----+----------+------------+------+------+--------+---------+
| id | nickname | loginname | age | sex | status | address |
+-----+----------+------------+------+------+--------+---------+
|
101
| zy101 | zhaoyun101 |
23
|
1
|
1
| tianjin |
|
102
| zy102 | zhaoyun102 |
23
|
1
|
1
| tianjin |
|
103
| zy103 | zhaoyun103 |
23
|
1
|
1
| tianjin |
|
104
| zy104 | zhaoyun104 |
23
|
1
|
1
| tianjin |
|
105
| zy105 | zhaoyun105 |
23
|
1
|
1
| tianjin |
|
106
| zy106 | zhaoyun106 |
23
|
1
|
1
| tianjin |
|
107
| zy107 | zhaoyun107 |
23
|
1
|
1
| tianjin |
|
108
| zy108 | zhaoyun108 |
23
|
1
|
1
| tianjin |
|
109
| zy109 | zhaoyun109 |
23
|
1
|
1
| tianjin |
|
110
| zy110 | zhaoyun110 |
23
|
1
|
1
| tianjin |
|
111
| zy111 | zhaoyun111 |
23
|
1
|
1
| tianjin |
|
112
| zy112 | zhaoyun112 |
23
|
1
|
1
| tianjin |
|
113
| zy113 | zhaoyun113 |
23
|
1
|
1
| tianjin |
|
114
| zy114 | zhaoyun114 |
23
|
1
|
1
| tianjin |
|
115
| zy115 | zhaoyun115 |
23
|
1
|
1
| tianjin |
|
116
| zy116 | zhaoyun116 |
23
|
1
|
1
| tianjin |
|
117
| zy117 | zhaoyun117 |
23
|
1
|
1
| tianjin |
|
118
| zy118 | zhaoyun118 |
23
|
1
|
1
| tianjin |
|
119
| zy119 | zhaoyun119 |
23
|
1
|
1
| tianjin |
|
120
| zy120 | zhaoyun120 |
23
|
1
|
1
| tianjin |
|
121
| zy121 | zhaoyun121 |
23
|
1
|
1
| tianjin |
|
122
| zy122 | zhaoyun122 |
23
|
1
|
1
| tianjin |
|
123
| zy123 | zhaoyun123 |
23
|
1
|
1
| tianjin |
|
124
| zy124 | zhaoyun124 |
23
|
1
|
1
| tianjin |
|
125
| zy125 | zhaoyun125 |
23
|
1
|
1
| tianjin |
|
126
| zy126 | zhaoyun126 |
23
|
1
|
1
| tianjin |
|
127
| zy127 | zhaoyun127 |
23
|
1
|
1
| tianjin |
|
128
| zy128 | zhaoyun128 |
23
|
1
|
1
| tianjin |
|
129
| zy129 | zhaoyun129 |
23
|
1
|
1
| tianjin |
|
130
| zy130 | zhaoyun130 |
23
|
1
|
1
| tianjin |
|
131
| zy131 | zhaoyun131 |
23
|
1
|
1
| tianjin |
|
132
| zy132 | zhaoyun132 |
23
|
1
|
1
| tianjin |
|
133
| zy133 | zhaoyun133 |
23
|
1
|
1
| tianjin |
|
134
| zy134 | zhaoyun134 |
23
|
1
|
1
| tianjin |
|
135
| zy135 | zhaoyun135 |
23
|
1
|
1
| tianjin |
|
136
| zy136 | zhaoyun136 |
23
|
1
|
1
| tianjin |
|
137
| zy137 | zhaoyun137 |
23
|
1
|
1
| tianjin |
|
138
| zy138 | zhaoyun138 |
23
|
1
|
1
| tianjin |
|
139
| zy139 | zhaoyun139 |
23
|
1
|
1
| tianjin |
|
140
| zy140 | zhaoyun140 |
23
|
1
|
1
| tianjin |
|
141
| zy141 | zhaoyun141 |
23
|
1
|
1
| tianjin |
|
142
| zy142 | zhaoyun142 |
23
|
1
|
1
| tianjin |
|
143
| zy143 | zhaoyun143 |
23
|
1
|
1
| tianjin |
|
144
| zy144 | zhaoyun144 |
23
|
1
|
1
| tianjin |
|
145
| zy145 | zhaoyun145 |
23
|
1
|
1
| tianjin |
|
146
| zy146 | zhaoyun146 |
23
|
1
|
1
| tianjin |
|
147
| zy147 | zhaoyun147 |
23
|
1
|
1
| tianjin |
|
148
| zy148 | zhaoyun148 |
23
|
1
|
1
| tianjin |
|
149
| zy149 | zhaoyun149 |
23
|
1
|
1
| tianjin |
|
150
| zy150 | zhaoyun150 |
23
|
1
|
1
| tianjin |
|
151
| zy151 | zhaoyun151 |
23
|
1
|
1
| tianjin |
|
152
| zy152 | zhaoyun152 |
23
|
1
|
1
| tianjin |
|
153
| zy153 | zhaoyun153 |
23
|
1
|
1
| tianjin |
|
154
| zy154 | zhaoyun154 |
23
|
1
|
1
| tianjin |
|
155
| zy155 | zhaoyun155 |
23
|
1
|
1
| tianjin |
|
156
| zy156 | zhaoyun156 |
23
|
1
|
1
| tianjin |
|
157
| zy157 | zhaoyun157 |
23
|
1
|
1
| tianjin |
|
158
| zy158 | zhaoyun158 |
23
|
1
|
1
| tianjin |
|
159
| zy159 | zhaoyun159 |
23
|
1
|
1
| tianjin |
|
160
| zy160 | zhaoyun160 |
23
|
1
|
1
| tianjin |
|
161
| zy161 | zhaoyun161 |
23
|
1
|
1
| tianjin |
|
162
| zy162 | zhaoyun162 |
23
|
1
|
1
| tianjin |
|
163
| zy163 | zhaoyun163 |
23
|
1
|
1
| tianjin |
|
164
| zy164 | zhaoyun164 |
23
|
1
|
1
| tianjin |
|
165
| zy165 | zhaoyun165 |
23
|
1
|
1
| tianjin |
|
166
| zy166 | zhaoyun166 |
23
|
1
|
1
| tianjin |
|
167
| zy167 | zhaoyun167 |
23
|
1
|
1
| tianjin |
|
168
| zy168 | zhaoyun168 |
23
|
1
|
1
| tianjin |
|
169
| zy169 | zhaoyun169 |
23
|
1
|
1
| tianjin |
|
170
| zy170 | zhaoyun170 |
23
|
1
|
1
| tianjin |
|
171
| zy171 | zhaoyun171 |
23
|
1
|
1
| tianjin |
|
172
| zy172 | zhaoyun172 |
23
|
1
|
1
| tianjin |
|
173
| zy173 | zhaoyun173 |
23
|
1
|
1
| tianjin |
|
174
| zy174 | zhaoyun174 |
23
|
1
|
1
| tianjin |
|
175
| zy175 | zhaoyun175 |
23
|
1
|
1
| tianjin |
|
176
| zy176 | zhaoyun176 |
23
|
1
|
1
| tianjin |
type
:
ref
rows
:
2438590
说明使用了
address
索引做关联
同理:
|
177
| zy177 | zhaoyun177 |
23
|
1
|
1
| tianjin |
|
178
| zy178 | zhaoyun178 |
23
|
1
|
1
| tianjin |
|
179
| zy179 | zhaoyun179 |
23
|
1
|
1
| tianjin |
|
180
| zy180 | zhaoyun180 |
23
|
1
|
1
| tianjin |
|
181
| zy181 | zhaoyun181 |
23
|
1
|
1
| tianjin |
|
182
| zy182 | zhaoyun182 |
23
|
1
|
1
| tianjin |
|
183
| zy183 | zhaoyun183 |
23
|
1
|
1
| tianjin |
|
184
| zy184 | zhaoyun184 |
23
|
1
|
1
| tianjin |
|
185
| zy185 | zhaoyun185 |
23
|
1
|
1
| tianjin |
|
186
| zy186 | zhaoyun186 |
23
|
1
|
1
| tianjin |
|
187
| zy187 | zhaoyun187 |
23
|
1
|
1
| tianjin |
|
188
| zy188 | zhaoyun188 |
23
|
1
|
1
| tianjin |
|
189
| zy189 | zhaoyun189 |
23
|
1
|
1
| tianjin |
|
190
| zy190 | zhaoyun190 |
23
|
1
|
1
| tianjin |
|
191
| zy191 | zhaoyun191 |
23
|
1
|
1
| tianjin |
|
192
| zy192 | zhaoyun192 |
23
|
1
|
1
| tianjin |
|
193
| zy193 | zhaoyun193 |
23
|
1
|
1
| tianjin |
|
194
| zy194 | zhaoyun194 |
23
|
1
|
1
| tianjin |
|
195
| zy195 | zhaoyun195 |
23
|
1
|
1
| tianjin |
|
196
| zy196 | zhaoyun196 |
23
|
1
|
1
| tianjin |
|
197
| zy197 | zhaoyun197 |
23
|
1
|
1
| tianjin |
|
198
| zy198 | zhaoyun198 |
23
|
1
|
1
| tianjin |
|
199
| zy199 | zhaoyun199 |
23
|
1
|
1
| tianjin |
|
200
| zy200 | zhaoyun200 |
23
|
1
|
1
| tianjin |
|
201
| zy201 | zhaoyun201 |
23
|
1
|
1
| tianjin |
|
202
| zy202 | zhaoyun202 |
23
|
1
|
1
| tianjin |
|
203
| zy203 | zhaoyun203 |
23
|
1
|
1
| tianjin |
|
204
| zy204 | zhaoyun204 |
23
|
1
|
1
| tianjin |
|
205
| zy205 | zhaoyun205 |
23
|
1
|
1
| tianjin |
+-----+----------+------------+------+------+--------+---------+
105
rows
in set
(
0.00
sec)
--
查看执行计划
mysql> explain
select distinct
b.*
from
tuser1 a,tbiguser b
where
a
.address
=b
.address
;
+----+-------------+-------+-------+---------------+----------+---------+-------
---------+---------+-------------------------------------------+
| id | select_type |
table
| type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+-------
---------+---------+-------------------------------------------+
|
1
| SIMPLE | a | index | idx_addr | idx_addr |
768
|
NULL
|
20
| Using
where
; Using index; Using temporary |
|
1
| SIMPLE | b | ref | idx_addr | idx_addr |
768
|
demo
.a.address
|
2438590
|
NULL
|
+----+-------------+-------+-------+---------------+----------+---------+-------
---------+---------+-------------------------------------------+
2
rows
in set
(
0.00
sec)
mysql>
select distinct
b.*
from
tuser2 a,tbiguser b
where
a
.address
=b
.address
;
+-----+----------+------------+------+------+--------+----------+
| id | nickname | loginname | age | sex | status | address |
+-----+----------+------------+------+------+--------+----------+
|
1
| zy1 | zhaoyun1 |
23
|
1
|
1
| shanghai |
|
2
| zy2 | zhaoyun2 |
23
|
1
|
1
| shanghai |
|
3
| zy3 | zhaoyun3 |
23
|
1
|
1
| shanghai |
|
4
| zy4 | zhaoyun4 |
23
|
1
|
1
| shanghai |
|
5
| zy5 | zhaoyun5 |
23
|
1
|
1
| shanghai |
|
6
| zy6 | zhaoyun6 |
23
|
1
|
1
| shanghai |
|
7
| zy7 | zhaoyun7 |
23
|
1
|
1
| shanghai |
|
8
| zy8 | zhaoyun8 |
23
|
1
|
1
| shanghai |
|
9
| zy9 | zhaoyun9 |
23
|
1
|
1
| shanghai |
|
10
| zy10 | zhaoyun10 |
23
|
1
|
1
| shanghai |
|
11
| zy11 | zhaoyun11 |
23
|
1
|
1
| shanghai |
|
12
| zy12 | zhaoyun12 |
23
|
1
|
1
| shanghai |
|
13
| zy13 | zhaoyun13 |
23
|
1
|
1
| shanghai |
|
14
| zy14 | zhaoyun14 |
23
|
1
|
1
| shanghai |
|
15
| zy15 | zhaoyun15 |
23
|
1
|
1
| shanghai |
|
16
| zy16 | zhaoyun16 |
23
|
1
|
1
| shanghai |
|
17
| zy17 | zhaoyun17 |
23
|
1
|
1
| shanghai |
|
18
| zy18 | zhaoyun18 |
23
|
1
|
1
| shanghai |
|
19
| zy19 | zhaoyun19 |
23
|
1
|
1
| shanghai |
|
20
| zy20 | zhaoyun20 |
23
|
1
|
1
| shanghai |
|
21
| zy21 | zhaoyun21 |
23
|
1
|
1
| shanghai |
|
22
| zy22 | zhaoyun22 |
23
|
1
|
1
| shanghai |
|
23
| zy23 | zhaoyun23 |
23
|
1
|
1
| shanghai |
|
24
| zy24 | zhaoyun24 |
23
|
1
|
1
| shanghai |
|
25
| zy25 | zhaoyun25 |
23
|
1
|
1
| shanghai |
|
26
| zy26 | zhaoyun26 |
23
|
1
|
1
| shanghai |
|
27
| zy27 | zhaoyun27 |
23
|
1
|
1
| shanghai |
|
28
| zy28 | zhaoyun28 |
23
|
1
|
1
| shanghai |
|
29
| zy29 | zhaoyun29 |
23
|
1
|
1
| shanghai |
|
30
| zy30 | zhaoyun30 |
23
|
1
|
1
| shanghai |
|
31
| zy31 | zhaoyun31 |
23
|
1
|
1
| shanghai |
|
32
| zy32 | zhaoyun32 |
23
|
1
|
1
| shanghai |
|
33
| zy33 | zhaoyun33 |
23
|
1
|
1
| shanghai |
|
34
| zy34 | zhaoyun34 |
23
|
1
|
1
| shanghai |
|
35
| zy35 | zhaoyun35 |
23
|
1
|
1
| shanghai |
|
36
| zy36 | zhaoyun36 |
23
|
1
|
1
| shanghai |
|
37
| zy37 | zhaoyun37 |
23
|
1
|
1
| shanghai |
|
38
| zy38 | zhaoyun38 |
23
|
1
|
1
| shanghai |
|
39
| zy39 | zhaoyun39 |
23
|
1
|
1
| shanghai |
|
40
| zy40 | zhaoyun40 |
23
|
1
|
1
| shanghai |
|
41
| zy41 | zhaoyun41 |
23
|
1
|
1
| shanghai |
|
42
| zy42 | zhaoyun42 |
23
|
1
|
1
| shanghai |
|
43
| zy43 | zhaoyun43 |
23
|
1
|
1
| shanghai |
|
44
| zy44 | zhaoyun44 |
23
|
1
|
1
| shanghai |
|
45
| zy45 | zhaoyun45 |
23
|
1
|
1
| shanghai |
|
46
| zy46 | zhaoyun46 |
23
|
1
|
1
| shanghai |
|
47
| zy47 | zhaoyun47 |
23
|
1
|
1
| shanghai |
|
48
| zy48 | zhaoyun48 |
23
|
1
|
1
| shanghai |
|
49
| zy49 | zhaoyun49 |
23
|
1
|
1
| shanghai |
|
50
| zy50 | zhaoyun50 |
23
|
1
|
1
| shanghai |
|
51
| zy51 | zhaoyun51 |
23
|
1
|
1
| shanghai |
|
52
| zy52 | zhaoyun52 |
23
|
1
|
1
| shanghai |
|
53
| zy53 | zhaoyun53 |
23
|
1
|
1
| shanghai |
|
54
| zy54 | zhaoyun54 |
23
|
1
|
1
| shanghai |
|
55
| zy55 | zhaoyun55 |
23
|
1
|
1
| shanghai |
|
56
| zy56 | zhaoyun56 |
23
|
1
|
1
| shanghai |
|
57
| zy57 | zhaoyun57 |
23
|
1
|
1
| shanghai |
|
58
| zy58 | zhaoyun58 |
23
|
1
|
1
| shanghai |
|
59
| zy59 | zhaoyun59 |
23
|
1
|
1
| shanghai |
|
60
| zy60 | zhaoyun60 |
23
|
1
|
1
| shanghai |
|
61
| zy61 | zhaoyun61 |
23
|
1
|
1
| shanghai |
|
62
| zy62 | zhaoyun62 |
23
|
1
|
1
| shanghai |
|
63
| zy63 | zhaoyun63 |
23
|
1
|
1
| shanghai |
|
64
| zy64 | zhaoyun64 |
23
|
1
|
1
| shanghai |
|
65
| zy65 | zhaoyun65 |
23
|
1
|
1
| shanghai |
|
66
| zy66 | zhaoyun66 |
23
|
1
|
1
| shanghai |
|
67
| zy67 | zhaoyun67 |
23
|
1
|
1
| shanghai |
|
68
| zy68 | zhaoyun68 |
23
|
1
|
1
| shanghai |
|
69
| zy69 | zhaoyun69 |
23
|
1
|
1
| shanghai |
|
70
| zy70 | zhaoyun70 |
23
|
1
|
1
| shanghai |
|
71
| zy71 | zhaoyun71 |
23
|
1
|
1
| shanghai |
|
72
| zy72 | zhaoyun72 |
23
|
1
|
1
| shanghai |
|
73
| zy73 | zhaoyun73 |
23
|
1
|
1
| shanghai |
|
74
| zy74 | zhaoyun74 |
23
|
1
|
1
| shanghai |
|
75
| zy75 | zhaoyun75 |
23
|
1
|
1
| shanghai |
|
76
| zy76 | zhaoyun76 |
23
|
1
|
1
| shanghai |
|
77
| zy77 | zhaoyun77 |
23
|
1
|
1
| shanghai |
|
78
| zy78 | zhaoyun78 |
23
|
1
|
1
| shanghai |
|
79
| zy79 | zhaoyun79 |
23
|
1
|
1
| shanghai |
|
80
| zy80 | zhaoyun80 |
23
|
1
|
1
| shanghai |
|
81
| zy81 | zhaoyun81 |
23
|
1
|
1
| shanghai |
|
82
| zy82 | zhaoyun82 |
23
|
1
|
1
| shanghai |
|
83
| zy83 | zhaoyun83 |
23
|
1
|
1
| shanghai |
|
84
| zy84 | zhaoyun84 |
23
|
1
|
1
| shanghai |
|
85
| zy85 | zhaoyun85 |
23
|
1
|
1
| shanghai |
|
86
| zy86 | zhaoyun86 |
23
|
1
|
1
| shanghai |
|
87
| zy87 | zhaoyun87 |
23
|
1
|
1
| shanghai |
|
88
| zy88 | zhaoyun88 |
23
|
1
|
1
| shanghai |
|
89
| zy89 | zhaoyun89 |
23
|
1
|
1
| shanghai |
|
90
| zy90 | zhaoyun90 |
23
|
1
|
1
| shanghai |
|
91
| zy91 | zhaoyun91 |
23
|
1
|
1
| shanghai |
|
92
| zy92 | zhaoyun92 |
23
|
1
|
1
| shanghai |
|
93
| zy93 | zhaoyun93 |
23
|
1
|
1
| shanghai |
|
94
| zy94 | zhaoyun94 |
23
|
1
|
1
| shanghai |
|
95
| zy95 | zhaoyun95 |
23
|
1
|
1
| shanghai |
|
96
| zy96 | zhaoyun96 |
23
|
1
|
1
| shanghai |
|
97
| zy97 | zhaoyun97 |
23
|
1
|
1
| shanghai |
|
98
| zy98 | zhaoyun98 |
23
|
1
|
1
| shanghai |
|
99
| zy99 | zhaoyun99 |
23
|
1
|
1
| shanghai |
|
100
| zy100 | zhaoyun100 |
23
|
1
|
1
| shanghai |
+-----+----------+------------+------+------+--------+----------+
100
rows
in set
(
0.00
sec)
--
查看执行计划
mysql> explain
select distinct
b.*
from
tuser2 a,tbiguser b
where
a
.address
=b
.address
;
+----+-------------+-------+-------+---------------+----------+---------+-------
---------+---------+-------------------------------------------+
| id | select_type |
table
| type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+-------
---------+---------+-------------------------------------------+
type
:
ref
rows
:
2438590
说明使用了
address
索引做关联
合并结果集后再分组求和
|
1
| SIMPLE | a | index | idx_addr | idx_addr |
768
|
NULL
|
20
| Using
where
; Using index; Using temporary |
|
1
| SIMPLE | b | ref | idx_addr | idx_addr |
768
|
demo
.a.address
|
2438590
|
NULL
|
+----+-------------+-------+-------+---------------+----------+---------+-------
---------+---------+-------------------------------------------+
2
rows
in set
(
0.00
sec)
select count
(x
.id
),x
.address
from
(
select distinct
b.*
from
tuser1 a,tbiguser b
where
a
.address
=b
.address
union
all
select distinct
b.*
from
tuser2 a,tbiguser b
where
a
.address
=b
.address
) x
group by
x
.address
;
+-------------+----------+
|
count
(x
.id
) | address |
+-------------+----------+
|
100
| shanghai |
|
105
| tianjin |
+-------------+----------+
2
rows
in set
(
0.00
sec)
--
查看执行计划
mysql> explain
select count
(x
.id
),x
.address
->
from
-> (
select distinct
b.*
from
tuser1 a,tbiguser b
where
a
.address
=b
.address
union
all
select distinct
b.*
from
tuser2 a,tbiguser b
where
a
.address
=b
.address
) x
group by
x
.address
;
+----+--------------+------------+-------+---------------+----------+---------+-
---------------+----------+-------------------------------------------+
| id | select_type |
table
| type | possible_keys | key | key_len |
ref | rows | Extra |
+----+--------------+------------+-------+---------------+----------+---------+-
---------------+----------+-------------------------------------------+
|
1
| PRIMARY | <derived2> | ALL |
NULL
|
NULL
|
NULL
|
NULL
|
97543600
| Using temporary; Using filesort |
|
2
| DERIVED | a | index | idx_addr | idx_addr |
768
|
NULL
|
20
| Using
where
; Using index; Using temporary |
|
2
| DERIVED | b | ref | idx_addr | idx_addr |
768
|
demo
.a.address
|
2438590
|
Distinct
|
|
3
|
UNION
| a | index | idx_addr | idx_addr |
768
|
NULL
|
20
| Using
where
; Using index; Using temporary |
|
3
|
UNION
| b | ref | idx_addr | idx_addr |
768
|
demo
.a.address
|
2438590
|
Distinct
|
|
NULL
|
UNION
RESULT | <union2,
3
> | ALL |
NULL
|
NULL
|
NULL
|
NULL
|
NULL
| Using temporary |
+----+--------------+------------+-------+---------------+----------+---------+-
---------------+----------+-------------------------------------------+
6
rows
in set
(
0.00
sec)
DERIVED
:派生表
最终优化
将派生表写成视图
优化结果:从最初的将近
14
秒优化到不到
1
秒
。
优化总结:
开启慢查询日志,定位运行慢的
SQL
语句
利用
explain
执行计划,查看
SQL
执行情况
关注索引使用情况:
type
关注
Rows
:行扫描
关注
Extra
:没有信息最好
加索引后,查看索引使用情况,
index
只是覆盖索引,并不算很好的使用索引
如果有关联尽量将索引用到
eq_ref
或
ref
级别
复杂
SQL
可以做成视图,视图在
MySQL
内部有优化,而且开发也比较友好
对于复杂的
SQL
要逐一分析,找到比较费时的
SQL
语句片段进行优化