连接查询-mysql详解(五)

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: 连接查询-mysql详解(五)

上篇文章说了,mysql5.6.6版本之前数据默认在系统表空间,之后默认在独立表空间,innodb因为索引和数据在一个b+树,所以两个文件,一个文件结构,一个存数据,myISAM则是三个文件。一个聚簇索引有两个段,叶子段和非叶子段,一个段有他专属的区,数据刚开始存在碎片区,不属于任何段,直属表空间。

系统表空间-mysql详解(四)


Mysql访问方法


Access method的概念主要通过两种方法获取数据,第一种是全表扫描获取,第二种是通过索引获取,至于采用什么方式访问代价更低,这是mysql优化器该做的事。

Const:当我们采用主键或者唯一键的时候,速度则是最高效的,但这是建立在查询条件没查询null的情况下,如果是查询null,因为就不是唯一,会查询到多条数据,这时候效率则是ref_null


Ref:当我们用普通索引等值查询时候,这时候效率则是ref,他的经过是先查询二级索引b+树,再回表查询主表,这就意味着回表的数据越多,效率则越低,所以取决于二次查询的数据量,太多不会采用ref,当然,查询的前提也是没有null的情况。

Ref_or_null:当我们用索引列查询null的时候,效率就没有上面的高,于是就是当前等级。

Range:前面介绍都是等值查询,当前这个是范围查询,

Index:当我们查询的条件没有符合最左索引,但是查询的列都包含复合索引,结果查询条件是从第二列开始,这时候他不会走复合索引,但是他会遍历复合索引。

All:这就是直接全表扫描。

当有两个二级索引出现的时候,一般只会用一个二级索引来查询,比如ab两个二级索引,mysql优化器,会看哪个索引查询的数据更少,再用更少的数据来回表查询聚簇索引真实数据,查到真实数据后在过滤其他条件。

联合索引,前面刚说了当两个二级索引出现的时候,只会有一个二级索引查询,这也不是百分百的,1、如果两个二级索引都是精确查找,没有区间查找,这时候也是会触发两个二级索引查询。2、当主键id区间查找,也会触发两个索引查询。

因为单独索引mysql优化器内部会有union或和intersection且的消耗,当时使用两个索引的时候,于是联合索引肯定是更优 的选择,在时间和空间都得到提升。


连接查询


当我们连接查询的时候,就有了驱动表的概念,mysql优化器会选择代价更小的作为驱动表,也就是第一个需要查询的表,而另一个表就是被驱动表,被驱动表查询的次数取决于驱动表查询数据的多少,驱动表查询一次,查询出来的数据,再多次查询被驱动表,多少条数据则查询多少次。


连接查询分为内连接和外连接,当查询的需求是驱动表有数据,而被驱动表没有数据,这时候则需要考虑用外连接,外连接则由on关键字需要使用,表示需要查询的结果即使没有查询到,也需要放入到结果集,而where查询的过滤条件不会放入结果集。Left join则是以左边表为驱动表,right join则是右边表为驱动表。


内连接和外连接最大的区别就是on后面的过滤条件,会不会在结果集返回,内连接的on可以直接理解为where


Nested_loop join:前面说了,驱动表查询的时候,只会访问一次,然后通过结果集查询被驱动表,这时候被驱动表会查询多次,那如果三个表四个表进行查询呢,就需要再次重复第二步操作,用结果集再次查询第三张表,依次嵌套查询下去,这个过程就像是嵌套循环,所以连接查询超过三次则会非常影响效率。


虽然被驱动表是需要查询多次的,但是也是可以走索引查询,因为mysql优化器会把sql优化成单表查询,然后走索引,当表1a字段和表2b字段相等的情况下,直接把驱动表表1确定常数,然后把值带入b字段单表查询表2,从而可以走索引。


Join buffer:扫描表的过程是先把磁盘上的数据刷新到内存上,然后在处理join,但实际场景中查询数据太大,这时候后面的数据磁盘io的情况下,前面的数据就需要释放。于是joinbuffer就出来了,这个参数可以用join buffer size来设置大小,他会把驱动表的结果集放入这个内存中,当然更好的效果还是在被驱动表的查询条件上加上索引,这样查询效率更高。


相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
23天前
|
关系型数据库 MySQL 数据库连接
Unity连接Mysql数据库 增 删 改 查
在 Unity 中连接 MySQL 数据库,需使用 MySQL Connector/NET 作为数据库连接驱动,通过提供服务器地址、端口、用户名和密码等信息建立 TCP/IP 连接。代码示例展示了如何创建连接对象并执行增删改查操作,确保数据交互的实现。测试代码中,通过 `MySqlConnection` 类连接数据库,并使用 `MySqlCommand` 执行 SQL 语句,实现数据的查询、插入、删除和更新功能。
|
4月前
|
缓存 关系型数据库 MySQL
MySQL索引策略与查询性能调优实战
在实际应用中,需要根据具体的业务需求和查询模式,综合运用索引策略和查询性能调优方法,不断地测试和优化,以提高MySQL数据库的查询性能。
357 66
|
2月前
|
关系型数据库 MySQL 数据库连接
数据库连接工具连接mysql提示:“Host ‘172.23.0.1‘ is not allowed to connect to this MySQL server“
docker-compose部署mysql8服务后,连接时提示不允许连接问题解决
|
15天前
|
关系型数据库 MySQL 网络安全
如何排查和解决PHP连接数据库MYSQL失败写锁的问题
通过本文的介绍,您可以系统地了解如何排查和解决PHP连接MySQL数据库失败及写锁问题。通过检查配置、确保服务启动、调整防火墙设置和用户权限,以及识别和解决长时间运行的事务和死锁问题,可以有效地保障应用的稳定运行。
80 25
|
6天前
|
算法 关系型数据库 MySQL
join查询可以⽆限叠加吗?MySQL对join查询有什么限制吗?
大家好,我是 V 哥。本文详细探讨了 MySQL 中 `JOIN` 查询的限制及其优化方法。首先,`JOIN` 查询不能无限叠加,存在资源(CPU、内存、磁盘 I/O)、性能和语法等方面的限制。过多的 `JOIN` 操作会导致数据库性能急剧下降。其次,介绍了三种常见的 `JOIN` 查询算法:嵌套循环连接(NLJ)、索引嵌套连接(INL)和基于块的嵌套循环连接(BNL),并分析了它们的触发条件和性能特点。最后,分享了优化 `JOIN` 查询的方法,包括 SQL 语句优化、索引优化、数据库配置调整等。关注 V 哥,了解更多技术干货,点赞👍支持,一起进步!
|
3月前
|
SQL 关系型数据库 MySQL
【MySQL基础篇】多表查询(隐式/显式内连接、左/右外连接、自连接查询、联合查询、标量/列/行/表子查询)
本文详细介绍了MySQL中的多表查询,包括多表关系、隐式/显式内连接、左/右外连接、自连接查询、联合查询、标量/列/行/表子查询及其实现方式,一文全面读懂多表联查!
【MySQL基础篇】多表查询(隐式/显式内连接、左/右外连接、自连接查询、联合查询、标量/列/行/表子查询)
|
2月前
|
缓存 关系型数据库 MySQL
【深入了解MySQL】优化查询性能与数据库设计的深度总结
本文详细介绍了MySQL查询优化和数据库设计技巧,涵盖基础优化、高级技巧及性能监控。
398 0
|
3月前
|
存储 Oracle 关系型数据库
索引在手,查询无忧:MySQL索引简介
MySQL 是一款广泛使用的关系型数据库管理系统,在2024年5月的DB-Engines排名中得分1084,仅次于Oracle。本文介绍MySQL索引的工作原理和类型,包括B+Tree、Hash、Full-text索引,以及主键、唯一、普通索引等,帮助开发者优化查询性能。索引类似于图书馆的分类系统,能快速定位数据行,极大提高检索效率。
83 8
|
3月前
|
SQL 关系型数据库 MySQL
MySQL 窗口函数详解:分析性查询的强大工具
MySQL 窗口函数从 8.0 版本开始支持,提供了一种灵活的方式处理 SQL 查询中的数据。无需分组即可对行集进行分析,常用于计算排名、累计和、移动平均值等。基本语法包括 `function_name([arguments]) OVER ([PARTITION BY columns] [ORDER BY columns] [frame_clause])`,常见函数有 `ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`, `SUM()`, `AVG()` 等。窗口框架定义了计算聚合值时应包含的行。适用于复杂数据操作和分析报告。
168 11
|
3月前
|
存储 关系型数据库 MySQL
mysql怎么查询longblob类型数据的大小
通过本文的介绍,希望您能深入理解如何查询MySQL中 `LONG BLOB`类型数据的大小,并结合优化技术提升查询性能,以满足实际业务需求。
194 6