上一篇 提到，最近有个需求，要修改现有存储结构，涉及查询条件和查询效率的考量，看了几篇索引和HBase相关的文章，回忆了相关知识，结合项目需求，说说自己的理解和总结。
总体目录如下，上篇介绍了前3小节，分析了索引为什么快，总结了它的优点和分类，以及索引的演化过程，中篇会重点介绍索引分析方法和常见索引优化。
- 为什么需要索引
- 索引的类别
- MySQL索引演化
- MySQL索引优化
- HBase介绍
- HBase存储结构
- HBase索引介绍
- 业务需求及设计
部分内容摘录了几个博友的文章，最后会给出文章链接，感谢他们的精彩分析。
通过中篇的介绍，你会了解到：
- MySQL查询过程
- 高级查询相关概念
- explain命令详细介绍
- 索引优化建议
MySQL查询过程
想要更好的优化查询，首先要了解其整体查询过程，从客户端发送查询请求，到接收到查询结果，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: 去重，默认选项；
又写多了，再加一篇吧，中篇未完待续。。。
参考文章：
欢迎扫描下方二维码，关注我的个人微信公众号，查看更多文章 ~
情情说
网友评论