asp多表查询并显示_mysql explain的使用及sql查询性能调优

【问题背景】

工作中遇到了一个需要用到多表联合查询数据的问题，因为担心线上运行该sql时会出现性能问题，所以就开始查找sql语句的查询的一些优化方式，最后找到了explain，可以提供mysql的查询语句的执行信息，为优化提供方向。

【explain介绍】

用法：

explain 

输出结果字段如下图所示：

各输出字段释义如下：

1、id：标识select所属行，如无子查询和union，则每一行的id都为1

2、select_type:对应行是简单的或者复杂的select，有simple、primary、subquery、union、 derived等类型。

3、table：对应行所访问的数据表

4、type：对应行的访问类型，mysql以何种方式去查找该行，访问方法从最差到最优列举如下：

ALL：全表扫描

index：按索引次序扫描表

range：范围扫描，一个有限制的索引扫描

ref：索引访问，返回所有匹配某个单个值的行。只有使用非唯一性索引或者唯一性索 引的非唯一性前缀才可能发生。

eq_ref：索引查找，至多只返回一条符合条件的记录

const：mysql能够对查询的部分进行优化，并转换成一个常量时就会使用该访问类型

NULL：mysql能够在优化阶段分解查询语句，从而在执行时就不用再访问表或者索引

5、possible_keys：显示了查询可以使用哪些索引

6、key：mysql决定采用哪个索引优化对该表的访问、

7、key_len：mysql在索引里使用的字节数

ref：显示了之前的表在key列记录的索引中查找值所用的列或常量

9、rows：mysql估计为找到所需的行而要读取的行数

filtered：针对表里符合某个条件的记录数的百分比做的悲观的估算

11、extra：不适合在其他列显示的额外信息，如using index表示mysql使用覆盖索引，避免访问表

【explain使用】

在数据表未建立索引前执行下述sql语句：

explain select  (t1.id),t1.phone,t1.address as city,t1.created_at as registration_date,t1.source_id as channel,t2.name as channel_name,
t1.phone as invited_phone,t1.id as invited_id,t4.phone as inviter_phone,
min(t5.used_at) as rewarded_at,t1.created_at as register_at
from  t1 left join source as t2 on t1.source_id=t2.id 
LEFT JOIN  t3 on t3.invited_phone = t1.phone
LEFT JOIN t4 on t4.id=t3.inviter_id
LEFT JOIN  t5 on t5.member_id = t1.id
WHERE 1=1
GROUP BY id
order by id desc

运行结果如图所示：

优化前

发现执行该查询时t1和t3表的访问类型均为all，查询效率过低，t3表的possible_keys为空，说明未在t3表上建立索引，因此果断在t3表上建立了索引。

之后再运行下述语句：

explain select  (t1.id),t1.phone,t1.address as city,t1.created_at as registration_date,t1.source_id as channel,t2.name as channel_name,
t1.phone as invited_phone,t1.id as invited_id,t4.phone as inviter_phone,
min(t5.used_at) as rewarded_at,t1.created_at as register_at
from  t1 left join source as t2 on t1.source_id=t2.id 
LEFT JOIN  t3 on t3.invited_phone = t1.phone
LEFT JOIN t4 on t4.id=t3.inviter_id
LEFT JOIN  t5 on t5.member_id = t1.id
WHERE id=3
GROUP BY id
order by id desc

执行结果如下图所示：

优化后

到这里，感觉工作就完成了好一部分了，至于优化前后sql语句的执行时间的对比也可以参照gui客户端自带的剖析工具查看、对比。