MySQL学习 - 查询的执行过程

想要获得更好的表现，你需要:  合理的表结构 + 出色的索引 + [ 不错的查询语句 ]

1. 慢查询是怎么回事

1.1 我怎么才能知道我的查询很慢

• 开启慢查询日志，比如你认为查询超过1秒就是慢，MySQL会记录下超过1秒的查询记录
• 看看是不是因为 MySQL 任务太多导致你查询很慢
• 使用EXPLAIN , 仔细研究每一条语句，看看是不是执行了不该执行的内容

1.2 你是不是向数据库请求了多余的数据

很多时候你其实在向数据库请求了超多资源，可能你并没有意识到，这些多余的数据会被抛弃，并给MySQL服务器端增加额外的压力。 一些场景：

• 你取了所有数据，但是你的程序却只用到前面10行:   
• 如果你真的只需要10行，学会使用 LIMIT 10
• 取了全部的列，但是实际用不到。这可能会使你丧失使用覆盖索引的机会
• 什么是覆盖索引？   假设你的索引是 A+B ，对应B-Tree内节点，有A+B的情况下可以查找到完整数据储存位置。 但是如果你 select A,B from users , 你直接读B-Tree就完事了，你甚至都不用去找完整数据，更快。但是你还是选择了 select * ，我们就需要回去找完整数据，去找你根本用不到的剩余列
• 除了覆盖索引，你还可能给服务器带来许多不必要的IO压力
• 重复查询的数据
• 比如查找头像这种请求，完全可以通过缓存，不一定每次都要重新请求

1.3 MySQL有没有扫描多余的行

-- 使用索引
EXPLAIN SELECT * FROM users WHERE id = 1\G
********************** 1.row **********************
             type: ref    
             key : id     
             rows: 10     

-- 删除索引
EXPLAIN SELECT * FROM users WHERE id = 1\G
********************** 1.row ***********************
             type:  ALL        
             rows:  5073       
             extra: Using Where
复制代码

我们可以通过"EXPLAIN" 命令看看这条命令是怎么执行的，有没有索引扫描内容真的差别太大

• 如果你有索引，并且通过索引检索，我们使用 key=id 的 ref 方式，这种情况下，MySQL大概需要 扫描10行 ，能得到你想要的数据
• 但是如果你没有索引了，我们现在只能通过先全表扫描 + USING WHERE 的方式来筛选了，这种情况下预计得 扫描5073行 才能得到你想要的数据
• 真的扫描了太多本不需要的东西

在表面上，我们都直接在 SQL 语句加上WHERE就完事儿，并不过多的去关心性能问题，但是即使大家都是WHERE, 在"索引" 的辅助下也会存在很大的优劣之分

由好到差:

• WHERE 筛选项 即 索引， 这个在存储引擎层就能完成  ->  图一所示案例
• 索引能覆盖扫描项目(使用覆盖索引)， 标志是EXPLAIN显示Extra=Using Index 操作手法为MySQL服务器拿着索引前往B-Tree读数据就结束，不用数据库读数

• 没有使用索引，直接使用WHERE，数据库引擎需要先从表中读出数据，返回给MySQL服务器，然后MySQL用WHERE过滤，这样一来数据库引擎一定扫描了很多数据 -> 图二所示

总结一下，无论是方法1.存储引擎能直接访问需要的行，还是2.直接前往B-Tree读数，都好过全表扫描，返回所有数据然后由MySQL做过滤。 为了达到这样的效果， 尽可能把要用到的WHERE筛选项放到索引中去

2. 查询的过程是怎样的

为了更好的做出优化，以及后面会提到的"缓存命中"，我们必须也要先知道查询过程是怎样的。 关于详细的步骤，我们会在下面的环节描述一下各个部件是怎么工作的

1. 客户端发送一条请求给服务器
2. 服务器先检查缓存，如果命中缓存则直接返回结果，否则进入下一阶段
3. MySQL服务器进行SQL解析，预处理，再由优化器生成执行计划
4. MySQL服务器根据执行计划调用存储引擎API接口
5. 将结果缓存 并 返还给客户端

2.1 客户端 & MySQL 服务器之间的交互

MySQL客户端与MySQL服务器之间的交互是半双工的，也就是说同一时刻内只有其中一方向另一方发送请求，这个请求可以是客户端向服务器发送SQL语句，也可以是服务器向客户端返回所请求的数据，一旦客户端发送了请求以后，它所能做的就只有等待服务器返回所请求的数据

• 等待服务器查询并返回的结果比较漫长，所以一个比较好的办法是把查询返回的数据进行缓存，下次需要用的时候直接从缓存读取即可，可以减轻服务器的压力
• 当我们使用很多个链接发送请求的时候，表面上看我们是从MySQL服务器获取数据，其实都是缓存获取数据，这样可以很大程度提升效率

2.2 如何使用缓存

在解析一个SQL语句之前服务器会先看看是否有命中缓存中的数据，也就是看看是否已经有缓存上了。检查的标准是通过对查询语句的哈希实现的，如果哈希出的结果是一样的就算命中，并且这个哈希是对大小写敏感的，也就是说哪怕是大小写不一致都不能算命中

2.3 SQL解析 & 预处理

MySQL服务器通过关键字将客户端发来的SQL语句进行解析，解析器通过MYSQL语法对这条语句进行验证，例如它将验证是否使用了错误的关键字，关键字顺序是否正确等。

预处理器则会去检查它所请求的数据表以及列是否存在，并验证权限。

2.4 查询优化器的优化原则

走到了这一步说明你的语句没有问题，能执行，问题就是怎么执行。 所以查询优化器，会先找出很多个可能的做法，并尝试找出最优解 优化原则? 找出成本最低的

mysql > SELECT SQL_NO_CACHE COUNT(*) FROM users
+----------+
| count(*) |
+----------+
|     5462 |
+----------+

mysql > SHOW STATUS LIKE 'Last_query_cost'
+-----------------+-------------+
| Variable_name   | Value       |
+-----------------+-------------+
| Last_query_cost | 1040.59     |
+-----------------+-------------+复制代码

• 你可以使用以上的方式查看一下，上一条执行语句的 ” 执行成本 “ 是多少
• " 执行成本 " 说明MySQL服务器认为需要加载1040个数据页，并在其中做随机查找才可以
• 这个 " 执行成本 " 是这么考量出来的
• 索引节点，涉及多少个页面
• 索引以及数据行的长度，索引的分布情况
• 但是优化器并不考虑有"缓存" 这种东西，它假设每取一次数 == 一次磁盘IO
• " 执行成本 "  一定是准确的吗？
• 一定不是，考量很多时候就无法考虑到全部情况，并且他也不知道什么在内存里什么在磁盘上，在内存里的都不用做磁盘IO
• 但是大部分时候计算出来的执行成本会比人思考的更准确

2.5 MySQL优化器 - 重新定义关联顺序

2.5.1 先简单介绍一下联表的执行过程 (如果你知道就可以跳过了)

-- 上图反应的就是下面个SQL语句的联表过程
SELECT tbl1.col1 , tbl2.col2 
FROM tbl1 JOIN tbl2 USING(col3)
WHERE tbl1.col1 IN(5,6)复制代码

表之间的关联遵循一种"嵌套" 的规则，用最简单的话说就是先取表[tbl1]的第一行，去表[tbl2]中做匹配，对于我们，我们自然是希望执行的步骤越少越好:

• 我们先从[tbl1]中挑出 col1在(5,6)范围内的所有记录
• 对于每一条这样的记录，去遍历 tbl2, 找出匹配，成为符合的输出
• 所以，如果第一个表，它满足条件的记录，越少，是不是我们在tbl2中遍历，也就越少？因此一个大原则诞生了，我们一定希望，第一张表符合条件的越少越好。 这个原则会成为联表优化最重要的原则

2.5.2 联表优化实战分析

SELECT tbl1.col1, tbl2.col2, tbl3.col3
FROM tbl1
INNER JOIN tbl2 USING(tbl1.col1)
INNER JOIN tbl3 USING(tbl3.col3);复制代码

• tbl1 一共 1000行，tbl3 一共200行， 三张表都有索引
• 虽然第一个出现在SQL语句里的tbl1， 在经过联表优化以后第一个出现的是tbl3，原因:
• 如果我们拿着第一张表的索引前往第二张表匹配，根据索引查询都很快
• 所以问题变成了如果第一张表会越短，我们匹配的次数就越少
• tbl3 比 tbl1 短，因此被自动优化成第一个出现的表