最近有个需求, 要修改现有存储结构, 涉及查询条件和查询效率的考量, 看了几篇索引和 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 语句 1Union [union 选项]Select 语句 2...
Union 选项:
All: 保留所有;
Distinct: 去重, 默认选项;
又写多了, 再加一篇吧, 中篇未完待续...
参考文章:
MySQL 优化原理
步步深入: SQL 解析顺序
END
来源: https://juejin.im/entry/5beb6fd4e51d451402495088