sql优化(一)
---
title: sql语句的优化（一）
date: 2018-10-20
categories: 数据库优化
---
# Explain 命令
数据库查询效率的快慢往往是评价一个数据库是好是差其中的一个标准。
对于好的数据库而言，往往离不开良好的数据库设计，硬件配置，网络等诸多因数。
那么我们在日常开发中，提高数据库查询效率最简单可行的方式就是优化sql语句。
### 导致数据库查询缓慢的原因
1. 数据量过大
2. 表设计不合理
3. 没有合理使用索引
4. sql语句写得不好
5. 硬件性能低
6. 网速不给力，不稳定
#### 如何找出导致性能慢的原因
此博客主要是针对sql语句的优化，所以首先你要知道是否跟sql语句有关，我们可以使用数据库自带的性能检测工具，比如：**mysql 的 explain命令 查看 sql 的执行计划**
#### Explain
优化查询效率，主要原则就是应尽量避免**全表扫描**，应该考虑在**合适的列上建立索引**。
explain命令可以查看sql语句的执行计划，查看该sql语句**有没有使用索引，有没有做全表扫描***，这都可以通过explain命令来查看。
##### Explian的使用
---
***语法***：
EXPLAIN 表名
或：
EXPLAIN SELECT 语句 （**重要**）
> * 1. explain语句可以用作descibe的一个同义词.
代码示例：
> * 2. select语句前放上关键词explain，MySQL将解释它如何处理select，提供有关表如何联接和联接的次序。
代码示例：
借助于explain命令，我们可以知道什么时候必须为表加入索引,以得到一个使用索引来寻找记录的更快的select。
explain的每个输出行提供一个表的相关信息，并且每个行包括下面的10个列
###### 1. id: select识别符,select的查询序列号.
表示查询中**执行select子句或操作表的顺序**
* id相同，执行顺序由上至下
例如：
* 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
例如：
###### 2. select_type: 表示查询中每个select子句的类型
包含如下值：
1. SIMPLE ：查询中不包含 子查询 或者 UNION 
2. PRIMARY: 最外面的SELECT(查询中若包含任何复杂的子部分，最外层查询则被标记为：PRIMARY)
3. UNION：若第二个SELECT出现在UNION之后，则被标记为UNION
4. DEPENDENT UNION：UNION中的第二个或后面的SELECT语句,DEPENDENT意味着SELECT依赖于外层查询中发现的数据。
5. UNION RESULT: 从UNION表获取结果的SELECT被标记为：UNION RESULT
6. SUBQUERY: 在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY
7. DEPENDENT SUBQUERY: 子查询中的第一个SELECT，取决于外面的查询
8. DERIVED： 用来表示包含在from子句中的子查询的select语句（衍生表）
###### 3. table: 输出行所引用的表
###### 4. type: 表示mysql在扫描表中找到所需行的方式，又称“访问类型”。
常见类型如下:( 从左到右，性能从最差到最好 )
ALL, index,range, ref, eq_ref, const, system
> 完整的类型为:
>
> 结果值从好到坏依次是：
>
> system > const > eq_ref > ref > fulltext > ref_or_null
> index_merge > unique_subquery > index_subquery
> range > index > ALL
>
ALL: 全表扫描,mysql将遍历全表以找到匹配的行
index：使用索引扫描，index与ALL区别为index类型只遍历索引树
**在学生表中学生姓名类添加了索引**
range: 索引范围扫描,只检索给定范围的行，使用一个索引来选择行。
注意： 学生编号为主键列，主键列自定创建主键索引，所以按学生编号范围查询则使用了 range,
学生年龄没有创建索引，所以按学生年龄范围查找没有使用range,而是使用全表扫描。
ref：使用非唯一索引扫描或者唯一索引扫描，返回匹配某个单独值的记录行
eq_ref: 类似ref, 简单来说，就是多表连接中使用primary key或者 unique key 作为关联条件.
const: 表最多有一个匹配行，它将在查询开始时被读取。因为仅有一行，在这行的列值可被优化器剩 余部分认为是常数。const表很快，因为它们只读取一次！
const用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时.
system: 表仅有一行(系统表)。这是const联接类型的一个特例。
###### 5. possible_keys : 指出MySQL能使用哪个索引在该表中找到行,查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用
例如： 查询所有的学生信息，学生表中，学生姓名列添加了索引，但是此查询并不会使用该索引。
但是查询姓名叫李四的同学，则使用到了索引
###### 6. key ： 显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL
示例中查询所有的学生信息没有使用索引，所以为 null
查询姓名叫李四的同学使用用到的索引为 idx_name, 所以key的值显示为 idx_name
###### 7. key_len : 显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL
如： 上图的示例
###### 8. ref ：表示上述表的连接匹配条件，即哪些列或常量（主键）被用于查找索引列上的值
###### 9. rows ： 显示MySQL认为它执行查询时必须检查的行数。
该查询表示： 先扫描班级表，所以扫描检查3条数据，在扫描检查学生表，则需要检查4条数据
###### 10. Extra ：该列包含MySQL解决查询的详细信息（显示十分重要的额外信息）
该列可以显示的不同的文本字符串：
> Distinct
> Not exists
> range checked for each record
> Using filesort
> Using index
> Using temporary
> Using where
> Using join buffer
> Using sort_union(...)
> Using union(...)
> Using intersect(...)
> Using index for group-by
常见的额外信息：
Using index：该值表示相应的select操作中使用了覆盖索引（Covering Index）
Using where：表示mysql服务器将在存储引擎检索行后再进行过滤。许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where字句的查询都会显示"Using where"。有时"Using where"的出现就是一个暗示：查询可受益与不同的索引。
Using join buffer：该值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
Using temporary：为了解决查询，MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以 按不同情况列出列的GROUP BY和ORDER BY子句时。
Using filesort： MySQL中无法利用索引完成的排序操作称为“文件排序”
Impossible where：这个值强调了where语句会导致没有符合条件的行。
explain查看sql执行计划后:
1. type属性：如果是ALL,则表示sql需要优化了.
2. rows属性：表示mysql认为它执行查询时必须检查的行数,行数越多效率越低。
3. Extra属性：额外信息出现 using fileSort,useing tempoary,则表示sql需要优化了.
