MySQL—DML执行流程揭秘(查询语句执行流程)

$一条sql查询语句怎么执行的$

基本架构

• 客户端
  service层包括核心服务功能，以及所有内置函数

  • 连接器：管理连接 权限验证

      1、修改权限，下一次登录才生效
      2、连接后没操作，连接处于空闲状态，show processlist 查看
      3、客户端时间太长，连接器自动断开 wait_timeout=8
      4、尽量使用长连接，减少连接动作
      5、长连接内存占用涨得快（执行过程中临时使用内存是管理在连接对象中）			
      	解决方法：
      	定时断开长连接
      	Mysql5.7以上，通过mysqlresetconnection 重新初始化连接资源
    

  • 查询缓冲：加快查询效率，但是表更新缓冲不在，8以后版本取消缓冲

      1、适用于查询量大，不经常更新的场景
      2、通过设置querycachetype=DEMAND 默认开启不使用缓存
      3、使用缓存时，使用SQL_CACHE使用缓存
    

  • 分析器：词法语法分析，判断列名，表名是否与真实表对应

  • 优化器：执行计划生成，索引选择

  • 执行器：操作引擎，返回结果

• 储存引擎
  • 数据存储 提供读写接口，储存过程，触发器，视图
  • 架构：插件式，支持InnoDB，MyISAM，Memory存储引擎，MySQL5.5.5版本开始使用InnoDB存储引擎

结合示例流程分析

示例sql

select city,name,age,from student where city = '北京' order by name limit 100;

其中有id主键，city是一个B+Tree索引

执行流程

1. 语句中有排序需求，第一步初始化sort_buffer，确认放入name，city，age三个字段
2. 从索引city中找到第一个满足city='北京'条件的主键id
3. 到主键id索引取出整行数据，取出name，city，age放在sort_buffer
4. 从索引city中取出下一条满足条件的主键id
5. 重复步骤3-4直到不满足查询条件为止
6. 对sort_buffer中数据按照name字段进行快速排序
7. 按照排序结果取出前100行数据返回给客户端

参数设置

其中按照name做排序可以在内存中完成，如果sort_buffer_size设置值较小，则会使用磁盘临时文件进行辅助排序

-- 以下方法可确定是否使用了临时文件
/* 打开optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on';

/* @a保存Innodbrowsread的初始值 */
select VARIABLE_VALUE into @a from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 执行语句 */
select city, name,age from t where city='杭州' order by name limit 1000;

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM information_schema.OPTIMIZER_TRACE\G

/* @b保存Innodb_rows_read的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 计算Innodb_rows_read差值 */
select @b-@a;

-- 结果
number_of_tmp_files 表示使用了多少个临时文件， 如果为0表示没有使用临时文件
examined_rows: 表示参与排序的行数
sort_mode:

开启优化器的执行跟踪

/* 打开 optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on';

/* 执行语句 */
select word from words order by rand() limit 3;

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM information_schema.OPTIMIZER_TRACE\G

服务器端执行流程

mysql处理查询是“边读边发，查询结果是分段发送给客户端的”

• 获取一行，写到net_buffer，这块内存的大小是由参数net_buffer_length定义，默认大小为16K
• 重复获取行，直到net_buffer写满，调用网络接口发出去
• 如果发送成功，将net_buffer清空，然后继续读取下一行，并写入net_buffer
• 如果发送函数返回EAGAIN或WSAEWOULDBLOCK，即本地网络栈写满，进入等待，直到网络栈可重新写入，再继续发送

查询中join

join优化器是比较合适的驱动表，straight_join则是让优化器按照我们的指定方法去join

示例sql

select * from t1 straight_join t2 on (t1.a = t2.a) 

执行流程

1. 从表t1中读取一行数据
2. 从数据中，取出a字段到表t2里去查找，会使用到t2表的a字段对应的索引
3. 取出表t2中满足条件的行，和之前的数据合并成一行，最为结果集的一部分
4. 重复步骤1-3，直到把表t1的末尾循环结束
   join_buffer的作用：表关联查询的时候，执行计划中有时会看到 join buffer， 表示会把分段读取的驱动表数据放在join_buffer中，然后根据join条件关联下张被驱动表作为结果集的一部分返回。由参数join_buffer_size 设定，默认： 256k

结论

• Simple Nested-Loop Join：如果on条件在被驱动表中不是一条索引， 那么就会出现O(M*N)的时间复杂度, MySQL也没有使用这种方式
• Block Nested-Loop Join(BNL): 先把驱动表的数据读取出来放在线程内存的join_buffer中，把被驱动表的每一行数据取出跟join_buffer中的数据做对比， 满足join条件的作为结果的一部分返回

1. 使用join语句，比直接强行拆成多个单标执行SQL语句的性能要好
2. 如果使用join语句的话， 需要让小表做驱动表，当然被驱动表需要能走索引
3. 如果使用Index Nested-Loop Join算法， 也就是可以用上被驱动表上的索引， 则join没有问题
4. 如果使用 Block Nested-Loop Join （BNL）算法， 扫描行数过多， 特别是大表上的join操作，这样可能要扫描的被驱动表多次，会占用大量的系统资源。主要关注 执行计划中是否有 “Block Nested Loop”字样

优化join

group by的优化

• 如果对结果没有排序的要求，可以在语句后面加上order by null 语句, 以省略最终的排序阶段
• 尽量让group by 过程用上索引， 确认explain中没有Using temporary 和 Using filesort
• 如果 group by 统计的数据量不大，尽量只使用内存表；也可以通过适当调大tmp_table_size参数，来避免使用磁盘临时表
• 如果数据量太大， 在使用SQL_BIG_RESULT这个提示，来告诉优化器直接使用排序算法得到group by 的结果

order by工作原理

count(id) count(*) count(cel)你真的懂吗

数据库设计法则
在保证逻辑正确前提下，尽量减少扫描的数量，减少磁盘的IO，对于IonoDB来说，主键索引保存的是数据，对于count(*)这种操作，优化器会找到最小的那棵树遍历，因为遍历那棵树得到的逻辑结果一致。

count()含义
count()是一个聚合函数， 对于返回的结果集，逐行进行判断，count中参数不是null， 则累加1， 否则不加。最后返回累加值,
count(*), count(1), count(主键id) 都表示返回满足条件的结果集总行数； 而count(其他字段) 则表示返回满足提交的数据行里面 参数不为null的数量

性能上的差别

• 原则
  server层要取那个字段就返回那个
  Innodb只会给必要的值
  目前优化器只针对count(*)做了优化，定义为取行数
• count(*)
  这是例外，MySQL专门做了优化，不会把全部字段读取出来， 直接按照行累加
• count(1)
  InnoDB遍历整张表，但不取值。server层对于返回的每行添加一个“1”列， 判定不会为null，按照行累加即可
• count(主键id)
  InnoDB遍历整张表， 把每行的id取出，返回我server层，server层拿到id后，判定id不会为null，按照行累加即可
• count(字段)
  若字段定义为not null,则一行行取出读取字段判定不为null， 按照行累加
  若字段定义允许null，取出字段判断不为null，进行累加

总结：按照效率排序：count(字段)<count(主键id)<count(1)≈count(*)

问题分析总结

查询长时间不返回

select * from student where id =1

这种情况可能是表被锁，我们可以使用show processlist查看当前sql语句执行的情况
三种可能性：等待MDL锁 等待flush 等待行锁

查询man

• sql写的不好
  select * from student where id + 1 = 10000
  错误的sql写法导致索引没有发挥作用

• 索引没有建好
  例如一个联合索引 但是查询的语句中字段顺序出现跟索引不对应

• mysql选择索引错误
  MySQL索引统计存在偏差 导致选择错误的索引

• 查询数据mvcc版本
  select * from student where id = 1 lock in share mode
  带有lock in share mode 的sql是当前读。如果有大量的写操作，此时直接返回的是MVCC中可见的最新值。而不加这个语句的sql是一致性读， 需要通过undo log 回滚到对应的位置，此时就会增加查询的负担