MySQL查询原理,看这一篇就够了!

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL查询原理,看这一篇就够了!

查询原理篇

上篇文章MySQL索引篇熟悉了MySQL的索引,这篇文章来熟悉查询相关的原理

连接原理

selecte * from t1,t2 where t1.m1 > 1 and t1.m1 = t2.m2 and t2.n2 < 'd'

image.png

先在驱动表t1上使用二级索引或者聚簇索引根据t1.m1 > 1寻找符合的记录(如图中2号位置),再根据这条记录 t1.m1 = 2 去匹配另一个查询条件 2 = t2.m2 ,然后再匹配其他查询条件再被驱动表t2上查找,找到还可能需要回表查询所有记录,然后再从驱动表上寻找下一条记录

访问一次驱动表,根据驱动表的结果集来访问多次被驱动表

因此驱动表结果集越小访问被驱动表的次数就越少,因此最好小表驱动大表

这种嵌套循环连接的方式时间复杂度是指数级别的,如果联多个表时间开销会指数级增长

由于访问多次被驱动表,在联表查询时可以适当的在被驱动表上建立索引来加快连接

联表查询中使用索引的情况下,可能索引列不满足查询列表,因此会回表,这种情况下回表会产生随机IO,innodb会通过MRR缓冲排序主键值再回表,以此来避免随机IO

在嵌套循环连接且无法使用索引的情况下,时间开销会很大,innodb使用基于块的嵌套循环连接来优化:申请一块join buffer,将驱动表记录放在join buffer中,再访问被驱动表时就能够用join buffer中的记录与被驱动表记录对比,join buffer够大的情况下,完全可以只访问一次被驱动表

join buffer中存储查询列表和搜索条件,因此查询列表不要用*,在无法使用索引的情况下也可以适当增大join buffer

查询优化

优化器会对SQL的查询条件进行优化,比如移除永远为true或false的表达式、移除一些不必要的括号等

查询排序

在使用order by、group by的情况下会对某个列进行排序,如果这个列是索引列,那么使用该索引查询时,它天然就是有序的;但如果这个列不是索引列,则可能先使用二级索引或者聚簇索引查出结果集,在将这些结果放在sort buffer中对要排序的列进行排序。

对于内存充足,查询量不大的情况下使用全字段排序,sort buffer中存在完整的记录,再对需要排序的列排序后直接返回(图中对name排序,先使用city二级索引查询再排序)

image.png

对于内存不足,查询量大的情况下使用rowid排序,sort buffer中只存需要排序的列和主键值,排序好后再通过回表的方式获取完整记录

image.png

外连接消除

外连接与内连接的区别就是内连接的驱动表与被驱动表可以由优化器来选择最优的结果,而外连接由编写SQL来指定

外连接使用on来针对驱动表记录没在被驱动表中匹配时将被驱动表中的值填充为NULL加入结果集,因此只要在where条件中隐式的指定被驱动表其他列不能为NULL,就不能加入结果集,这样就能够消除外连接与内连接区别,从而让优化器来选择最优的驱动表与被驱动表

in子查询半连接优化

in 子查询半连接优化:在一定条件下会将子查询物化成一个临时表(数据量小在内存中使用哈希索引;数据量大在磁盘中使用B+树索引),由临时表与外部查询的表进行内连接,并通过一些措施防止取重复值(唯一索引保证不重复、去重等)

总结

驱动表访问一次,被驱动表访问多次,适当为被驱动表建立索引;不使用索引加快的情况下,会使用join buffer,适当增大join buffer;不要在查询列表中使用*,可能导致不使用覆盖索引、占用join buffer空间等

查询中使用order by或group by时会排序,尽量为要排序的列建立索引,否则还要使用临时表sort buffer去排序;数据量小时使用全字段排序,数据量大的情况下使用rowid排序,排序完还要进行回表查询完整记录

联表查询使用外连接时,如果能够隐式的让被驱动表其他列不为null就可以触发外连接消除,由优化器来选择最优驱动表与被驱动表

in子查询使用半连接优化,一定条件下将子查询物化为临时表,根据防重复措施来与外部查询进行内连接


相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2天前
|
存储 算法 关系型数据库
*MYSQL--索引--内部原理
*MYSQL--索引--内部原理
|
4天前
|
关系型数据库 MySQL 索引
蓝易云 - 如何解决MySQL查询问题
以上是一些常见的解决MySQL查询问题的步骤,但每个问题都是独特的,可能需要不同的解决方法。
4 0
|
7天前
|
分布式计算 DataWorks 关系型数据库
MaxCompute产品使用合集之DataWorks是否支持通过SQL方式在MaxCompute中查询数据,并通过数据集成服务将查询结果同步至MySQL数据库
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
8天前
|
SQL 关系型数据库 MySQL
MySQL数据库——视图-介绍及基本语法(创建、查询、修改、删除、演示示例)
MySQL数据库——视图-介绍及基本语法(创建、查询、修改、删除、演示示例)
19 0
|
8天前
|
SQL 关系型数据库 MySQL
MySQL数据库——索引(5)-索引使用(上),验证索引效率、最左前缀法则、范围查询、索引失效情况、SQL提示
MySQL数据库——索引(5)-索引使用(上),验证索引效率、最左前缀法则、范围查询、索引失效情况、SQL提示
11 0
|
8天前
|
SQL 关系型数据库 MySQL
MySQL数据库——SQL(3)-DQL(基本查询、条件查询、聚合函数、分组查询、排序查询、分页查询、案例练习)
MySQL数据库——SQL(3)-DQL(基本查询、条件查询、聚合函数、分组查询、排序查询、分页查询、案例练习)
14 0
|
10天前
|
SQL 存储 关系型数据库
MySQL数据库案例实战教程:数据类型、语法与高级查询详解
MySQL数据库案例实战教程:数据类型、语法与高级查询详解
28 3
|
10天前
|
SQL 存储 关系型数据库
MySQL的查询计划(EXPLAIN)
MySQL的查询计划(EXPLAIN)
15 2
|
10天前
|
关系型数据库 MySQL
13. Mysql 使用WITH进行复杂和递归查询
13. Mysql 使用WITH进行复杂和递归查询
21 4
|
10天前
|
关系型数据库 MySQL
10. Mysql 分组或汇总查询
10. Mysql 分组或汇总查询
12 1