你真的懂join吗?解决联表查询不使用索引问题

这篇具有很好参考价值的文章主要介绍了你真的懂join吗?解决联表查询不使用索引问题。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

前言

小弟最近在开发一个项目时遇到了有点困扰我的问题,很有意思,而且也值得记录一下,希望对大家有用

场景:

我们有两个表,一个订单表表示t1,一个是订单的明细表t2,t2表中包含用户购买的各个产品,他们是根据订单编号关联的,当我用t1作为驱动表left join 连接t2表时没用到索引,但是用t2表连接t1表时,就用到了全文检索,很奇怪!因为按照我们通常的想法都是小表驱动大表

业务要求:

1.根据产品名称或产品型号或订单编号模糊查询这个订单的所有信息

2:不能用like,所以用的是全文检索

说明:

这篇博客最大的作用不是教大家怎么解决,而是分析为什么会这样!

准备

MySQL:8.0
Java:1.8

创建t1表和t2表

两表联查的时候on后面的字段不走索引,项目问题,全文检索,数据库,sql

创建索引:

t1和t2表的关系:一对多

t1

order_number字段是一个唯一索引

status字段是普通索引

t2

除了主键id,其它字段全是全文检索

1、在已有的表中添加全文索引

  • WITH PARSER ngram 为全文检索的解析器
create fulltext index 索引名称 on 表名(字段名) WITH PARSER ngram;

2、搜索

  • IN BOOLEAN MODE 为查询中是否包含该语句
SELECT * FROM tab_name WHERE MATCH ('列名1,列名2...列名n') AGAINST('词1 词2 词3 ... 词m' IN BOOLEAN MODE);

问题

用t1作为驱动表left join 连接t2表时没用到索引,但是用t2表连接t1表时,就用到了全文检索,用没用到索引有多大的差距,不做多诉说,相信大家都知道

t1 left join t2

sql:

EXPLAIN 
SELECT t1.* FROM t1
	LEFT JOIN t2 on t1.order_number = t2.order_number where t1.`status` = "true" and 			MATCH(t2.`name`) AGAINST("华为" IN BOOLEAN MODE)

结果:

两表联查的时候on后面的字段不走索引,项目问题,全文检索,数据库,sql
两表联查的时候on后面的字段不走索引,项目问题,全文检索,数据库,sql

说明:

所用实际为100毫秒左右

t1和t2表的type都是ALL,证明全都是全表扫描,

possible_keys指预计会用到的索引

key为实际用到的索引

rows为11和15是预计扫描了11行(可能跟实际行数有差距,这里只是预计,所以只做参考数据)

结果值从好到坏依次是:system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。一般来说,得保证查询至少达到range级别,最好能达到ref,否则就可能会出现性能问题。所以这个sql要优化了。

t2 left join t1

sql:

EXPLAIN 
SELECT t1.* FROM t2
	LEFT JOIN t1 on t1.order_number = t2.order_number where t1.`status` = "true" and 			MATCH(t2.`name`) AGAINST("华为" IN BOOLEAN MODE)

结果:

两表联查的时候on后面的字段不走索引,项目问题,全文检索,数据库,sql

两表联查的时候on后面的字段不走索引,项目问题,全文检索,数据库,sql

说明:

所用实际为50毫秒左右

比没用到索引快一倍,这个倍数会随着数据的增多二增加,现在只有十几条数据就差一倍,后续可想而知(大家在测试时如果速度一样,则增多数据,就会有很明显的差距)

t1和t2表的type一个为fulltext,一个为eq_ref,细表是用到了全文检索,主表的索引等级也是很高

possible_keys指预计会用到的索引

key为实际用到的索引

rows都为1(可能跟实际行数有差距,这里只是预计,所以只做参考数据)

小结:

不言而喻,我们肯定会选择第二种,但是又会产生一些问题:

  1. 我们都在强调用小表驱动大表,我们现在选用第二种好像违背了这一点
  2. 为什么换了一下位置就用到了索引
  3. 索引的走向到底是怎么走的

说明

问题1:MySQL为什么推荐小表驱动大表

MySQL 表关联的算法是 Nest Loop Join,是通过驱动表的结果集作为循环基础数据,然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据,然后合并结果。

例如:t1有10条数据,t2表有1000条数据,t1表每条数据关联t2表100条数据

sql:

SELECT * FROM t2 LEFT JOIN t1 on t1.order_number = t2.order_number

这样则需要用t2表循环1000次才能查询出来,而如果用t1表驱动user表则只需要循环20次就能查询出来。

小表驱动大表主要是减少连接次数,增加速度

因此这个语句的执行流程是:

  1. 从表t2中读入一行数据
  2. 从数据行中,取出order_number字段到表t1里去查找
  3. 取出表t1中满足条件的行,跟t2的数据组成一行,作为结果集的一部分
  4. 重复上面操作,直到t2的数据取完为止

NLJ

这个过程是先遍历表t2,然后根据表t1中取出的每行数据中的order_number值,去表t1中查询满足条件的记录。这就是"Index Nested-Loop Join",简称NLJ。

两表联查的时候on后面的字段不走索引,项目问题,全文检索,数据库,sql

因此建议内表走索引,也叫INLJ,但是如果内表是二级索引,效率也低,因为要回表查主键。

索引MySQL会判断使用索引和不使用的差别,如果检测全表扫描比使用索引还快,它就会选择ALL

所以一般情况下我们是建议都是小表驱动大表

BNL

这里我们把t2表的order_number字段的索引删除

sql:

SELECT * FROM t2 LEFT JOIN t1 on t1.order_number = t2.order_number

两表联查的时候on后面的字段不走索引,项目问题,全文检索,数据库,sql

结果就是两个表都要全表扫描,这里我们看到,Extra显示的是(Using where; Using join buffer (Block Nested Loop))

这个其实是MySQL对join不走索引全表扫描做了一个优化,简称BNL。

因此这个语句的执行流程是:

  1. 把表t1的数据读入线程内存join_buffer中,这里我们是把整个表t1放入内存中。
  2. 扫描表t2,把表t2中的每一行取出来,跟join_buffer中的数据做对比,满足join条件的,作为结果集的一部分返回。
    两表联查的时候on后面的字段不走索引,项目问题,全文检索,数据库,sql

这里,我们两个表都是做的全表扫描,所以不管是哪个表做驱动表都是执行消耗都是一样的。

如果一个表的数据太大了,根本装不下所有数据的话,就采用分段放。也可以修改join_buffer_size。

MRR

MySQL5.6版本开始支持的Multi-Range Read(MRR)优化。MRR目的是为了减少磁盘的随机访问,并且将随机访问转换为较为顺序的数据访问,MRR可适用于range,ref,eq_ref类型的查询

MRR优化有以下几个好处:

  1. MRR使数据访问变的较为顺序。在查询辅助索引时,首先根据得到的查询结果,按照主键进行排序,并按照主键排序的顺序进行书签查找
  2. 减少缓冲池中页被替换的次数
  3. 批量处理对键值的查询操作

MRR的设计思路是因为大多数的数据都是按照主键递增顺序插入得到的,所以我们可以认为,如果按照主键的递增顺序查询的话,对磁盘的读比较接近顺序读,能够提升读性能。

MRR 能够提升性能的核心在于,这条查询语句在索引 a 上做的是一个范围查询(也就是说,这是一个多值查询),可以得到足够多的主键 id。这样通过排序以后,再去主键索引查数据,才能体现出“顺序性”的优势。

BKA

针对于有索引的被驱动表,MySQL5.6版本开始增加了Batched Key Access(BKA)的新特性

对于多表join语句,当MySQL使用索引访问第一个join表的时候,使用join_buffer来收集第一个操作对象生成的相关列的值。BKA构建好key之后,通过MRR接口提交给引擎做查询。

BKA步骤:

  1. 将驱动表相关的列放入join_buffer中。
  2. 批量的将Key(索引键值)发送到MRR接口。
  3. MRR通过收到的key,根据其对应的ROWID进行排序,然后再进行数据的读取操作。

这里来看,BKA和BNL其实是差不多的,主要区别就是BKA是针对被驱动表是走索引的情况下,索引是非主键索引的时候,按照索引字段进行排序,因此减少了随机IO,提高性能。

问题2:为什么换了一下位置就用到了索引?

我开始在网上搜索的时候得到一种答案,说的是两个表的字符集不一样

两表联查的时候on后面的字段不走索引,项目问题,全文检索,数据库,sql

我检查了我的两个表,字符集都是一致的

虽然不适用这篇文章,但是确实是一种情况,当字符集不一致,是会出现的,所以只要改成一样就能解决

那么:

为什么我们的一致还是会出现这种情况呢?

  1. 最好的解释就是MySQL认为不走索引比走索引快
  2. where条件的干扰

t1 left join t2

没有where条件

sql:

EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 on t1.order_number = t2.order_number

两表联查的时候on后面的字段不走索引,项目问题,全文检索,数据库,sql

加上where条件

sql:

EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 on t1.order_number = t2.order_number  where t1.`status` = "true" and MATCH(t2.`name`) AGAINST("华为" IN BOOLEAN MODE)

两表联查的时候on后面的字段不走索引,项目问题,全文检索,数据库,sql

分析

我们可以看到结果差不多的,那可以分析一下各种情况:

  1. 没有where条件时,主表就是全表扫描,为啥?

    因为MySQL在此认为走ALL更快,所以没选,或者说,就应该全表扫描,(还有一些特殊情况会用到索引, 例如主表关联字段是多个,也就是多对一的情况下会用到索引,大家可以试试,然后可以想想为什么)

  2. 没有where条件时:细表预计用到order_number_idx索引,但最后也没用到,为啥?

    因为主表是全表扫描,一样的道理,t1表需要全部查出来,然后拿查出来的order_number字段去t2表匹配,然后组成一行数据返回,可想而知;

    t2全部扫描用索引不是更快吗?为啥不用?

    因为t2表的order_number_idx是全文检索,全文检索是啥大家可以去自行了解一下,它跟es一样,是把字段内容分词处理,每个分词都是索引,索引联表时如果是全文检索,那比ALL更慢

    一旦把order_number字段换成别的索引,比如普通索引或者唯一索引,结果是有惊喜的,它S是会用到索引的

    两表联查的时候on后面的字段不走索引,项目问题,全文检索,数据库,sql

  3. 有where条件时:主表预计用到status_idx索引,但实际还是没用到,为啥?

    首先要知道为什么会预计用到status_idx 索引;是因为where后面有t1.status = "true" 这个条件,但最后没用到的原因是:

    1. status_idx 是二级索引,也就是非聚集索引,所以它会造成一个回表操作
    2. 还有就是t1表中status这个字段的值都是ture,一但把一半的数据改成fasel,那么就会用到索引
    3. 当status这个字段的结果都是一样时,MySQL认为这个回表操作比ALL更慢,所以选择了ALL
  4. 有where条件时:细表预计使用order_number_idx,但最后也没用到,为啥?

    我们先把order_number改成普通索引,这种情况下,没有where条件时是会用到order_number_idx索引的,但是当我们加上where条件后,结果还是跟上面一摸一样,为什么呢?而且我们where的条件还是name字段的索引,就连预计都不会使用name这个索引

    1. 我们假设用到了name_idx这个索引,当一条数据从t1表过来,MySQL需要先根据order_number_idx索引找到主键索引,然后根据这个主键再去主键索引也就是id去查找name字段,然后才能使用name_idx这个索引,看到这里,大家应该懂了为什么MySQL连预计都没有

    2. 至于最后为什么还是没用到order_number_idx索引,主要是where后面使用了name这个字段,需要像上面一样繁琐,一旦我们后面的条件改成 where t1.status= "true" and t2.order_number = "heusjd23eu",那么结果将大大不一样

      两表联查的时候on后面的字段不走索引,项目问题,全文检索,数据库,sql

    为什么会这样呢?就连主表也用到了索引,大家可以自行想想,其实很有意思,哈哈

在有索引的情况下,MySQL会尝试去使用Index Nested-Loop Join算法,在有些情况下,可能Join的列就是没有索引,那么这时MySQL的选择Block Nested-LoopJoin。

t2 left join t1

没有where条件

sql:

EXPLAIN SELECT t1.* FROM t2 LEFT JOIN t1 on t1.order_number = t2.order_number

两表联查的时候on后面的字段不走索引,项目问题,全文检索,数据库,sql

加上where条件

sql:

EXPLAIN SELECT t1.* FROM t2 LEFT JOIN t1 on t1.order_number = t2.order_number  where t1.`status` = "true" and MATCH(t2.`name`) AGAINST("华为" IN BOOLEAN MODE)

两表联查的时候on后面的字段不走索引,项目问题,全文检索,数据库,sql

分析

可以看到结果的区别主要就是在t2表用到了索引,我们不去分析没有加where条件的情况,上面已经说得很清楚了,没看懂的同学可以仔细琢磨琢磨,下面我们将分析一下有条件的情况:

  1. 其实当你理清楚上面用t1 left join t2的情况时,就很容易理解为什么会这样了

  2. 主表也就是驱动表为啥使用了name_idx索引?

    其实主要是有whereand MATCH(t2.name) AGAINST("华为" IN BOOLEAN MODE)这个条件,我们可以想象一下,当一条数据进来时,MySQL可以根据name_idx这个索引快速定位到具体哪一行数据,所以连它的rows预计扫描行数都只有1条,然后根据这行数据的order_number字段去扫描t1表,而t1表正好也有order_number_idx这个索引,所以结果不言而喻

其实到了这一步,已经不需要分析太多,估计大家懂了,懂得都懂,不懂得可以再看看

结束语

相信看了这篇博客可以很好的帮助你设计索引,还有最后一句话:

任何执拗都会成为过往,只有时间会告诉你对错。文章来源地址https://www.toymoban.com/news/detail-797060.html

到了这里,关于你真的懂join吗?解决联表查询不使用索引问题的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 三表左连接(LEFT JOIN) — 两表之间的关系

    问题:   最近遇到需求多表连接,要求A表所有数据均展示,B表和C表关联A表,若A表有的B,C表没有则展示为null,由此引发以下问题: A表作为基表,先关联B表,关联条件为A.userId=B.userId; 再关联C表,关联条件是A.userId=C.userId还是B.userId=C.userId呢?这两个条件有什么区别吗? 测

    2023年04月08日
    浏览(28)
  • Pandas Power Play: 构建数据帝国的两表JOIN和group by之旅

    在数据分析的过程中,常常需要把多张表格进行合并,或者按照某些规则进行分组统计。Pandas是Python中一个非常强大的数据处理库,其中的JOIN和group by函数能够帮助我们轻松地完 成这些任务。本教程将详细介绍如何使用这两个函数,让你在数据分析的道路上更加游刃有余!

    2024年02月05日
    浏览(36)
  • Mybatis Plus一对多联表查询及分页解决方案

    查询用户信息列表,其中包含用户对应角色信息,页面检索条件有根据角色名称查询用户列表; 一个用户对应多个角色,用户信息和角色信息分表根据用户id关联存储,用户和角色一对多进行表连接查询, 创建对应表: 对应实体类: 在使用一对多连接查询并且分页时,发现

    2023年04月11日
    浏览(50)
  • 「 MySQL 入门」使用联表查询代替子查询的原因(附案例分析)

    参考鸣谢 MySQL Documentation PostgreSQL Documentation SQL Server Documentation 在关系型数据库中,查询是一项常见而重要的操作。在实际应用中,我们经常会遇到需要在多个表之间进行查询和关联的情况。传统的子查询是一种常见的解决方案,但随着数据量和复杂性的增加,使用联表查询

    2024年02月12日
    浏览(39)
  • 两表查询常用SQL

    1、两个表:table_a和table_b,求两表的交集,:INNER JOIN SELECT a.*,b.* FROM table_a AS a INNER JOIN table_b AS b ON a.id=b.id; 2、两个表:table_a和table_b,table_a为主表,关联查询table_b,table_b有数据就显示,没有数据就显示null,:LEFT JOIN SELECT a.*,b.* FROM table_a AS a LEFT JOIN table_b A

    2024年02月03日
    浏览(36)
  • Mysql 复杂查询丨联表查询

    💗wei_shuo的个人主页 💫wei_shuo的学习社区 🌐Hello World ! 联表查询(Join)是一种在数据库中使用多个表进行关联查询的操作。它通过使用 JOIN 将多个表连接在一起,并根据关联条件从这些表中检索数据 INNER JOIN(内连接) 内连接(Inner Join)是一种在联表查询中使用的

    2024年02月13日
    浏览(38)
  • SqlSugar 5.联表查询

    用到的几个实体类 使用 CodeFirst 创建表 1.Join用法 1.1 语法糖1 优点:好理解,五个表以内的联表比较推荐 1.1.1 表和表的连接 若将 .Select((o, cus) = new ViewOrder { Id = o.Id, CustomName =cus.Name }) 修改成.Select((o, cus) = new ViewOrder()) 那就是查询所有字段 1.1.2 表和Queryable的连接 (主表左连了一

    2023年04月08日
    浏览(35)
  • Django 联表查询操作

    在日常的开发中,常常需要对多张数据表同时进行数据查询。多表查询需要在数据表之间建立表关系才能够实现。一对多或一对一的表关系是通过外键实现关联的,而多表查询分为正向查询和反向查询。 以歌手表、专辑表、单曲表查询为例子。 歌手与专辑为一对多关系;歌

    2024年02月07日
    浏览(36)
  • HIVE 3 使用 MR 引擎多表关联 (JOIN) 导致丢数的问题复现、问题根源及解决方案 (附代码)

    本文意图解决 HIVE 3 版本中使用 MR 作为运算引擎进行 JOIN 操作时导致的丢数情况。 Apache Hive 在 2.3 版本后宣布放弃维护 MapReduce 作为底层执行引擎,并转而使用 Tez 作为默认的查询引擎。但是由于 Tez 在大作业量和高并发时的严重性能问题,导致许多任务不得不继续使用 MapRe

    2024年02月13日
    浏览(36)
  • MySQL多表查询内连接外连接详解,join、left join、right join、full join的使用

    目录 1、多表查询概览 1.1、分类 1.2、外连接的分类 1.3、常用的SQL语法标准 2、内外联接案例 2.1、初始化表 2.2、内连接 2.3、外连接案例 2.4、全连接案例 2.5、union和union all 2.6、实现MySQL全连接 2.7、内外连接面试基础 2.8、SQL99多表查询新特性 1.1、分类 可以根据3个角度进行分类

    2024年02月05日
    浏览(60)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包