SQL基本知识

野性酷女 2021-09-26 00:12 344阅读 0赞

一、SQL执行循序

SQL单表执行循序

SELECT \* FROM  XXX  WHERE  XX = XX  
  
1.先执行FROM的表  
2.在执行相应的WHERE条件  
3.在执行SELECT查询  
  
  
SQL多表执行循序  
SELECT DISTINCT(XX) FROM XXX(left\_table)  
\[join\_type(left/right/all)\] JOIN (right\_tanle) ON left.XX = right.XX (left/right 两表相等的条件)  
WHERE XX = XX  
GROUP BY XX(Group by 相应的字段)  
WITH\{XX\}  
HAVING XX (having 相应字段)  
ORDER BY XX (Order by 相应的字段)  
LIMIT XX  (Limit XX 分页)  
  
1.先执行FROM的表  
2.执行多表关联的ON条件  
3.执行多表关联的JOIN表  
4.执行相应的WHERE条件  
5.执行GROUP BY分组  
6.执行WITH子查询部分  
7.执行HAVING 聚合函数  
8.执行SELECT查询  
9.执行DISTINCT去重

10.执行ORDER BY排序

11.执行LIMIT分页

二、执行过程

不同的数据库执行过程有差异，下面介绍两种主流的数据库(Oracle、Mysql):

![Center][]

1.  用户提交了一条SQL语句
2.  数据库按照SQL语句的字面值计算出一个HASH值
3.  根据HASH值，判断一下在数据库缓冲区中是否存在此SQL的执行计划。
4.  如果不存在，则需要生成一个执行计划(硬解析过程)，然后将结果存入缓冲区。
5.  如果存在的话，判断是否为相同SQL（同样HASH值的语句，可能字符不相同；即使完全相同，也可能代表不同的语句。这块不展开说了）
6.  确认是同一条SQL语句，则从缓冲区中取出执行计划。
7.  将执行计划，交给执行器执行。
8.  结果返回给客户端。

![Center 1][]

1.  客户提交一条语句
2.  现在查询缓存查看是否存在对应的缓存数据，如有则直接返回(一般有的可能性极小，因此一般建议关闭查询缓存)。
3.  交给解析器处理，解析器会将提交的语句生成一个解析树。
4.  预处理器会处理解析树，形成新的解析树。这一阶段存在一些SQL改写的过程。
5.  改写后的解析树提交给查询优化器。查询优化器生成执行计划。
6.  执行计划交由执行引擎调用存储引擎接口，完成执行过程。这里要注意，MySQL的Server层和Engine层是分离的。
7.  最终的结果有执行引擎返回给客户端，如果开启查询缓存的话，则会缓存。

三、执行计划

Mysql执行计划  
  
Explain语法  
EXPLAIN  SELECT \* FROM  TABLE\_NAME ...  
  
变体：  
1. EXPLAIN EXTENDED SELECT  \* FROM  TABLE\_NAME ...  
将执行计划“反编译”成SELECT语句，运行SHOW WARNINGS 可得到被MySQL优化器优化后的查询语句  
  
2. EXPLAIN PARTITIONS SELECT \* FROM  TABLE\_NAME ...  
用于分区表的EXPLAIN  
  
  
ID  
包含一组数字，表示查询中执行select子句或操作表的顺序,id相同执行顺序由上至下;如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

![Center 2][]

id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

![Center 3][]

## **Select\_type ** ##

表示查询中每个select子句的类型（简单 OR复杂）

Select\_type：SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION、UNION RESULT

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** ##

表示MySQL在表中找到所需行的方式，又称“访问类型”，常见类型如下：

Type：ALL 、index、range、ref、er\_ref、const、system、null

由左至右，由最差到最好

a.ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

![Center 4][]

b.index：Full Index Scan，index与ALL区别为index类型只遍历索引树

![Center 5][]

c.range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询

![Center 6][]

![Center 7][]

range访问类型的不同形式的索引访问性能差异

![Center 8][]

d.ref：非唯一性索引扫描，返回匹配某个单独值的所有行。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找

![Center 9][]

f.const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量

![Center 10][]

system是const类型的特例，当查询的表只有一行的情况下， 使用system

g.NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引

![Center 11][]

## **Possible\_keys** ##

指出MySQL能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

## **Key** ##

显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL

TIPS：查询中若使用了覆盖索引，则该索引仅出现在key列表中

![Center 12][]

## **Key\_len** ##

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度

![Center 13][]

![Center 14][]

key\_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key\_len是根据表定义计算而得，不是通过表内检索出的

## **ref** ##

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

![Center 15][]

本例中，由key\_len可知t1表的idx\_col1\_col2被充分使用，col1匹配t2表的col1，col2匹配了一个常量，即 ’ac’

rows

表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

![Center 16][]

## **Extra** ##

包含不适合在其他列中显示但十分重要的额外信息

a.Using index

该值表示相应的select操作中使用了覆盖索引（Covering Index）

![Center 17][]

TIPS：覆盖索引（Covering Index）

MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件

包含所有满足查询需要的数据的索引称为  **覆盖索引**（Covering Index）

注意：

如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select \*，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降

b.Using where

表示MySQL服务器在存储引擎受到记录后进行“后过滤”（Post-filter）,

如果查询未能使用索引，Using where的作用只是提醒我们MySQL将用where子句来过滤结果集

![Center 18][]

c.Using temporary

表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询

![Center 19][]

d.Using filesort

MySQL中无法利用索引完成的排序操作称为“文件排序”

![Center 20][]

[Center]: /images/20210923/332edfa1ef974fbaa00413f8c73e1d23.png
[Center 1]: /images/20210923/2b386efbc80e4596bed2cc978efc64d2.png
[Center 2]: /images/20210923/952533e1850b44d1a8bc9147e39dc1db.png
[Center 3]: /images/20210923/4a776a2346884e24bff9c416b25b520d.png
[Center 4]: /images/20210923/42ca390c10cc4fa8a756a5ad4e82afdc.png
[Center 5]: /images/20210923/c6d22dc210204bb4a0bea330467cc2eb.png
[Center 6]: /images/20210923/21e41a86fe764748be08715d5f06ef0b.png
[Center 7]: /images/20210923/d284f5aeb1954045ade58a0e1fc034f2.png
[Center 8]: /images/20210923/2366658aa3b74dd3bb9500462e8d12bb.png
[Center 9]: /images/20210923/621a3e692a4f453e9eb41873be4137f1.png
[Center 10]: /images/20210923/9e4c445ca2a54b2281fc9e472af78c13.png
[Center 11]: /images/20210923/a6939019231f40c6af27c6e59234c68b.png
[Center 12]: /images/20210923/53744910f5014a4f97ac71b20fd21bc0.png
[Center 13]: /images/20210923/bd8d0df737ac4912a807a0e5e946ca1b.png
[Center 14]: /images/20210923/2b28750b965442459157a00d351c3151.png
[Center 15]: /images/20210923/20452dbf7078415f9dc3357b038478fe.png
[Center 16]: /images/20210923/cc9d9da16f164c6f86b7d9e89e006577.png
[Center 17]: /images/20210923/dec4efd2593d47d48b730b8f614d92eb.png
[Center 18]: /images/20210923/4f1f71161d2948e69fadec93c43756f0.png
[Center 19]: /images/20210923/25275dc800074d18815eec584ab65bce.png
[Center 20]: /images/20210923/5737f867398e45ba8d723c458a2506a1.png