mysql查询优化

---

概述

explain 命令获取 select 语句的执行计划，通过 explain 我们可以知道以下信息：表的读取顺序，数据读取操作的类型，哪些索引可以使用，哪些索引实际使用了，表之间的引用，每张表有多少行被优化器查询等信息。

在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询时，会返回执行计划的信息，而不是执行这条SQL（如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中）。

explain extended：会在 explain 的基础上额外提供一些查询优化的信息。紧随其后通过 show warnings 命令可以 得到优化后的查询语句，从而看出优化器优化了什么。

explain partitions：相比 explain 多了个 partitions 字段，如果查询是基于分区表的话，会显示查询将访问的分区。

---

执行计划属性

---

id

含义：select 的序列号

---

select_type

MySQL将 select 查询分为简单查询simple和复杂查询。

复杂查询分为三类：

• 简单子查询 subquery
• 派生表drived（from语句中的子查询）
• union 查询

a.SIMPLE：查询中不包含子查询或者UNION
b.查询中若包含任何复杂的子部分，最外层查询则被标记为：PRIMARY
c.在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY
d.在FROM列表中包含的子查询被标记为：DERIVED（衍生）
e.若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在 FROM子句的子查询中，外层SELECT将被标记为：DERIVED
f.从UNION表获取结果的SELECT被标记为：UNION RESULT

---

type

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

如：在索引列中选取最小值，可以单独查找索引来完成，不需要 访问表

explain select min(id) from user 

依次从最优到最差分别为：

system:系统查询

const:常量查询

explain select * from user where id = 1 

eq_ref: 使用唯一索引查找(主键或唯一索引)

ref: 非唯一索引访问(只有普通索引)
在联合查询中，一般为REF

ref_or_null ：类似ref，但是可以搜索值为NULL的行。

range:以范围的形式扫描

explain  select * from user u  where id  > 10

index : 按索引次序扫描，先读索引，再读实际的行，结果还是全表扫描 ，但进行了排序

all : 即全表扫描

---

possible_keys

---

key

实际用到的索引

---

ref

显示哪个字段或常数与key一起被使用。

---

子查询

MySQL里面的子查询

子查询分为如下几类：
　　　　1. 标量子查询：返回单一值的标量，最简单的形式。 可以使用 = > < >= <= <>
　　　　2. 列子查询：返回的结果集是 N 行一列。 可以使用 IN、ANY、SOME 和 ALL ,exists
　　　　3. 行子查询：返回的结果集是一行 N 列。 可使用（?,?,?）=（）
　　　　4. 表子查询：返回的结果集是 N 行 N 列的表数据。

子查询的位置：
　　　　select 中、from 后、where 中.

---

子查询优化

在MySQL中可以使用连接查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快。

实验验证如下：

查询每个用户的最大订单金额订单 500000条数据

子查询

 select u.username, o.money from user u left join 
 (select user_id ,max(money) as money from order_ group by user_id ) 
  as o on u.id = o.user_id

时间: 5.002s

连接查询

  select u.username,max(o.money) from user u left join order_ o 
  on u.id = o.user_id group by u.id;

时间: 2.76s

时间明显减少

---