理解索引（中）：MySQL查询过程和高级查询

 

上一篇 提到，最近有个需求，要修改现有存储结构，涉及查询条件和查询效率的考量，看了几篇索引和HBase相关的文章，回忆了相关知识，结合项目需求，说说自己的理解和总结。

总体目录如下，上篇介绍了前3小节，分析了索引为什么快，总结了它的优点和分类，以及索引的演化过程，中篇会重点介绍索引分析方法和常见索引优化。

• 为什么需要索引
• 索引的类别
• MySQL索引演化
• MySQL索引优化
• HBase介绍
• HBase存储结构
• HBase索引介绍
• 业务需求及设计

部分内容摘录了几个博友的文章，最后会给出文章链接，感谢他们的精彩分析。

通过中篇的介绍，你会了解到：

• MySQL查询过程
• 高级查询相关概念
• explain命令详细介绍
• 索引优化建议

MySQL查询过程

想要更好的优化查询，首先要了解其整体查询过程，从客户端发送查询请求，到接收到查询结果，MySQL服务器做了很多工作。

逻辑架构

MySQL逻辑架构整体分为三层，分别为客户端层、核心服务层、存储引擎层，共同协作完成。

最上层为客户端层，比如：连接处理、授权认证、安全等功能等。

中间层是MySQL的核心服务，包括查询解析、分析、优化、缓存、内置函数(比如：时间、数学、加密等)，另外，所有的跨存储引擎的功能也在这一层实现：存储过程、触发器、视图等。

最下层为存储引擎，负责数据存储和提取，中间的服务层通过API与存储引擎通信，这些API接口屏蔽了不同存储引擎间的差异。

具体执行过程

重点看下MySQL是如何优化和执行查询的，很多的查询优化工作就是遵循一些原则让MySQL的优化器能够按照预想的方式运行而已。

先说下总体流程：

• 客户端发送一条查询SQL给服务器；
• 服务器先检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果；
• 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划；
• 查询执行引擎根据优化器生成的执行计划，调用存储引擎的API来执行查询；
• 将结果返回给客户端；

1.客户端/服务端通信协议

MySQL客户端和服务器之间的通信协议是「半双工」：在任何一个时刻，要么由服务器向客户端发送数据，要么由客户端向服务器发送数据，不能同时发生，这也就意味着没法进行流量控制。

客户端用一个单独的数据包将查询请求发送给服务器，服务器响应给用户的数据通常会很多，由多个数据包组成，需要注意的是当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。

2.查询缓存

如果查询缓存是打开的，会检查这个查询语句是否命中查询缓存中的数据，如果命中，在检查一次用户权限后直接返回缓存中的结果。

查询缓存系统会跟踪查询中涉及的每个表，在任何的写操作时，MySQL必须将对应表的所有缓存都设置为失效，如果查询缓存非常大或者碎片很多，这个操作就可能带来很大的系统消耗。

另外，任何的查询语句在开始之前都必须经过检查，即使这条SQL语句永远不会命中缓存，如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗。

所以，打开缓存要慎重，只有当缓存带来的资源节约大于其本身消耗的资源时，才会给系统带来性能提升，可以将query_cache_type设置为DEMAND，这时只有加入SQL_CACHE的查询才会走缓存，其他查询则不会。

3.语法解析和预处理

通过关键字将SQL语句进行解析，生成一颗解析树，预处理则会根据MySQL规则进一步检查解析树是否合法。

4.查询优化

一条查询可以有很多种执行方式，优化器的作用就是找到这其中最好的执行计划，MySQL使用基于成本的优化器，它尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。

5.查询执行引擎

存储引擎接口提供了非常丰富的功能，但其底层仅有几十个接口，这些接口像搭积木一样完成了一次查询的大部分操作。

6.返回结果给客户端

结果集返回客户端是一个增量且逐步返回的过程，这样服务端就无须存储太多结果而消耗过多内存，也可以让客户端第一时间获得返回结果。

SELECT执行顺序

下面来看看SQL查询语句的执行顺序，每一步都会生成一个虚拟临时表，作为下一步的输入。

标准的SQL语法如下：

SELECT DISTINCT
    < select_list >
FROM
    < left_table > < join_type >
JOIN < right_table > ON < join_condition >
WHERE
    < where_condition >
GROUP BY
    < group_by_list >
HAVING
    < having_condition >
ORDER BY
    < order_by_condition >
LIMIT < limit_number >

但执行顺序是这样的：

FROM 
    <left_table>
ON <join_condition> <join_type> 
JOIN <right_table>
WHERE 
    <where_condition>
GROUP BY 
    <group_by_list>
HAVING 
    <having_condition>
SELECT 
DISTINCT 
    <select_list>
ORDER BY 
    <order_by_condition>
LIMIT 
    <limit_number>

1.FROM

当涉及多个表的时候，左边表的输出会作为右边表的输入，之后会生成一个虚拟表VT1：

• 计算两个相关联表的笛卡尔积(CROSS JOIN) ，生成虚拟表VT1-J1；
• 基于虚拟表VT1-J1进行过滤，过滤出所有满足ON谓词条件的行，生成虚拟表VT1-J2；
• 如果使用了外连接(LEFT,RIGHT,FULL)，主表（保留表）中的不符合ON条件的列也会被加入到VT1-J2中，生成虚拟表VT1-J3；

2.WHERE

对VT1过程中生成的临时表进行过滤，满足WHERE子句的列被插入到VT2表中：

• 与ON的区别：如果有外连接，ON针对过滤的是关联表，主表会返回所有的列，如果没有外连接，效果相同；
• 对主表的过滤应该放在WHERE；
• 于关联表，先条件查询后连接则用ON，先连接后条件查询则用WHERE；

3.GROUP BY

这个子句会把VT2中生成的表按照GROUP BY中的列进行分组，生成VT3表：

• 其后处理过程的语句，如SELECT,HAVING，所用到的列必须包含在GROUP BY中，对于没有出现的，得用聚合函数；

4.HAVING

对VT3表中的不同的组进行过滤，只用于分组后的数据，满足HAVING条件的子句被加入到VT4表中。

5.SELECT

这个子句对SELECT子句中的元素进行处理，生成VT5表：

• 计算SELECT子句中的表达式，生成VT5-J1；
• DISTINCT：寻找重复列，并删掉，会创建一张内存临时表VT5-J2，和虚拟表VT5-J1一样，不同的是对DISTINCT的列增加唯一索引，以此来除重复数据；

6.ORDER BY

从VT5-J2中的表中，根据ORDER BY 子句的条件对结果进行排序，生成VT6表，这是唯一可使用SELECT中别名的地方。

7.LIMIT

从上一步得到的VT6虚拟表中选出从指定位置开始的指定行数据。

高级查询相关概念

本小节介绍下常用的高级查询概念。

连接查询

将多张表按照某个指定的条件进行数据拼接，SQL中将连接查询分成四类: 内连接、外连接、自然连接、交叉连接，其中自然连接和交叉连接很少用到，就不过多介绍了。

1.内连接 inner join

从左表中取出每一条记录，分别与右表中所有的记录进行匹配，匹配必须左表和右表中都满足条件，匹配的会保留结果，否则不保留。

2.外连接 left/right join

外连接分为两种:

• left join: 左外连接(左连接)，以左表为主表
• right join: 右外连接(右连接)，以右表为主表

以某张表为主，取出里面的所有记录，不管能不能匹配上条件，主表最终都会保留，然后与另外一张表进行连接，如果不能匹配，其他表的字段都置空NULL。

子查询

是在某个查询结果之上再进行查询，也就是一条select语句内部包含了另外一条select语句。

按子查询所在位置，可以划分为：

• From子查询：子查询跟在from之后；
• Where子查询: 子查询出where条件中；
• exists子查询: 子查询出现在exists里面；

下面举几个例子：

查找部门名称前缀为「小米」的所有员工：

 SELECT name , sex ,  sal
        FROM emp
        WHERE no in ( 
            SELECT no FROM dept 
                WHERE name LIKE '小米%'
        )；

查看所有员工的薪水，并按薪水排序：

SELECT name , sal
       FROM (
           SELECT name , sal 
              FROM emp ORDER BY sal
       )；

联合查询

将多次查询, 将结果进行拼接，字段不会增加，每一条select语句获取的字段数必须严格一致。

语法如下：

Select 语句1

Union [union选项]

Select语句2...

Union选项:

• All: 保留所有；
• Distinct: 去重，默认选项；

又写多了，再加一篇吧，中篇未完待续。。。

参考文章：

1. MySQL优化原理
2. 步步深入：SQL解析顺序

欢迎扫描下方二维码，关注我的个人微信公众号，查看更多文章 ~

 

 

​

 

评论

1 楼 情情说 2018-05-29  

喜欢的可以关注我的微信公众号：情情说