01 基础架构:一条SQL查询语句是如何执行的?

949 阅读4分钟

该系列文章借鉴丁齐《MySQL实战45讲》,作用是为了自我提升,加深理解和思考,也希望能和大家一起互动学习!

技术乃是能工巧匠汇聚智慧的结晶,一开始就深入细节容易被繁杂和精巧的技艺所迷惑,所以为了学好一门技术,我首先会高屋建瓴对其轮廓有个大致了解,顺着轮廓摸象,就算摸不出个三五八万也知道这是个麻将。

首先从最简单的查询看起,看看它到底趟了多少水,湿了谁的鞋。

mysql> select * from T where ID=10

附上流程图 image-20210611105336658.png

上面的查询sql,根据流程图,我做个简单梳理,整体轮廓从上至下大致分为两层,Server层存储引擎层,顺着流程走,第一步则是客户端通过连接器连接上MySQL服务器;连接后通过分析器判断sql语法是否正确;接着判断查询缓存中是否有数据,有则直接返回;在经过优化器来优化sql,像保姆一样还帮你整理一下;最终由执行器的来执行sql,通过存储引擎去查询数据,整体的流程就算结束了。

组件的简单介绍

  • MySQL被分为Sever层存储引擎层

  • 连接器:与客户端建立TCP连接后,验证身份、赋予权限

  • 查询缓存:命中直接返回,对表更新,那么缓存失效,对于频繁更改的表不适合开启查询缓存。

  • 分析器:分析sql关键字,以及拼写是否正确

  • 优化器:优化sql,决定走哪个索引

  • 执行器:这里会做权限判断。然后打开表执行sql,执行过程如下(无索引情况)

    •  ​ a.调用 InnoDB 引擎接口取这个表的第一行,判断值;遍历取;将结果封装成记录集返回
    •  ​ b.可以在慢查询日志中看到一个 rows_examined 的字段,表示这个语句执行过程中扫描了多少行。

课后扩展篇

提问

  1. 查询缓存放在哪儿,如何控制查询缓存区间大小?
  2. 如何查看慢查询日志?
  3. 如何给用户赋予权限?
  4. 优化sql如何决定走哪个索引?

解答


1.查询缓存放在哪儿,如何控制查询缓存区间大小?

MySQL的查询缓存是完全存储在内存中.

查看mysql查询缓存参数show variables like '%query_cache%';

image-20210611113326945.png (1) query_cache_type表示缓存类型,OFF表示关闭查询缓存,ON表示开启查询缓存DEMAND表示用户自定义查询缓存

(2) query_cache_limit表示支持的最大单条查询sql数据量

(3) query_cache_min_res_unit表示查询缓存最小单位

(4) query_cache_size表示查询缓存空间大小

(5) query_cache_wlock_invalidate表示查询缓存是否支持写锁,OFF表示不支持,即读取数据不考虑写锁,ON表示支持,即读取数据会被写锁阻塞

查询缓存变量只能在my.ini文件中设置

image-20210611113601928.png


2.如何查看慢查询日志?

慢查询日志:show variables like '%slow_query_log%';

image-20210611112628079.png 利用show variables like '%long_query_time%';在线查询参数选项配置值

利用set global slow_query_log=1;设置参数配置

重要的几个参数如下:

  • slow_query_log=1 ##表示是否开启慢查询,1代表开启
  • long_query_time=0.5
  • min_examined_row_limit=100 ##对于查询扫描行数小于此参数的SQL,将不会记录到慢查询日志中;
  • slow_query_log_file=/mysql/data/mysql_slow.log

3.如何给用户赋予权限?

创建用户:create user 用户名 identified by '密码';

给用户分配权限:grant 权限 on 数据库.数据表 to '用户' @ '主机名';

​ a. grant all on *.* to 'xiaogang'@'%'; ##给xiaogang 赋予所有权限

​ b. grant select on temp.temp1 to 'xiaogang'@'%'; ## 给xiaogang 赋予查询temp.temp1的权限。


4.优化sql如何决定走哪个索引?

​ 查询优化和地图导航的概念非常相似,我们通常只需要输入想要的结果(目的地),优化器负责找到最有效的实现方式(最佳路线)。

​ 优化器的工作过程从语义上可以分为四个阶段:

a.逻辑转换:包括否定消除、等值传递和常量传递、常量表达式求值、外连接转换为内连接、子查询转换、视图合并等; ​ b.优化准备,例如索引 ref 和 range 访问方法分析、查询条件扇出值(fan out,过滤后的记录数)分析、常量表检测; ​ c.基于成本优化,包括访问方法和连接顺序的选择等; ​ d.执行计划改进,例如表条件下推、访问方法调整、排序避免以及索引条件下推。

实战演练

​ 开启优化器跟踪:SET optimizer_trace="enabled=on";

TODOTODO 太深了!!!先空过。

aqi使用总结

--登入客户端
mysql -h$ip -P$port -u$user -p
--查看连接
show processlist;
--将参数query_cache_type设置为DEMAND,那么可以用 SQL_CACHE 显式指定使用查询缓存的语句
mysql> select SQL_CACHE * from T where ID=10;
--查看是否开启慢查询日志
show variables like '%slow_query_log%';

江湖是人情世故,留下一个小赞,让我也能关注到你呀。