社区所有版块导航
Python
python开源   Django   Python   DjangoApp   pycharm  
DATA
docker   Elasticsearch  
aigc
aigc   chatgpt  
WEB开发
linux   MongoDB   Redis   DATABASE   NGINX   其他Web框架   web工具   zookeeper   tornado   NoSql   Bootstrap   js   peewee   Git   bottle   IE   MQ   Jquery  
机器学习
机器学习算法  
Python88.com
反馈   公告   社区推广  
产品
短视频  
印度
印度  
Py学习  »  DATABASE

MySQL学习 - 查询的执行过程

xiaohan.liang🥚 • 6 年前 • 185 次点击  
阅读 13

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. 查询的过程是怎样的

“MYSQL 查询过程”的图片搜索结果

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

  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 先简单介绍一下联表的执行过程 (如果你知道就可以跳过了)

“mysql join 泳道图”的图片搜索结果

-- 上图反应的就是下面个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 短,因此被自动优化成第一个出现的表



Python社区是高质量的Python/Django开发社区
本文地址:http://www.python88.com/topic/32410
 
185 次点击