MYSQL(06)-JOIN优化

JOIN查询原理
如果有两张数据结构一样的表(id-主键) ,(a有索引) ,(b无索引)。其中表t1(100条数据) 和t2(1000条数据),他们做join查询的时候,内部执行的原理是什么呢?

INLJ-(Index Nested-Loop Join)

// 使用straight_join表示,固定指定 t1是驱动表,t2是被驱动表,防止优化器优化
select * from t1 straight_join t2 on (t1.a=t2.a);
  • 1.从表t1中读取一行数据R
  • 2.从R中取出字段a去t2中查找
  • 3.取出t2中满足要求的数据,和R合并组成结果集中的一条数据
  • 4.重复1到3的步骤,流程图如下

上述因为被驱动表中使用了索引故,该join方法我们称之为(NLJ),这个流程中。对于表t1扫描的全表,故扫描了100行。对于表t2因为走了索引的树搜索,故t2表也是扫描了100行,索引这个join操作执行了200次扫描。这时如果反过来t2作为驱动表,则需要扫描2000次数据,故使用NLJ的时候,尽量使用小表作为驱动表

试想以下,如果上述t2没有使用索引,那么t1查询出的R对应查询t2的数据时,每次都要全表遍历1000次,那么查询的次数就要达到,100*1000=10W次查询了,这种查询方法叫做Simple Nested-Loop Join(SNLJ),因为效率实在太低,所以mysql根本没有使用这种方法。而是使用的Block Nested-Loop Join

BNLJ (Block Nested-Loop Join)

对于t1的数据并没有一条条读取,而是将t1的数据一次性加载到join_buffer的缓存中,然后扫描表t2与join_buffer中的每条数据做比对,最终一共扫描数据的次数是100+1000=1100次,大大增加了效率



不过join_buffer 的内存是有限的,如果join_buffer中放不下t1的表的所有数据,那么他会将数据分几次来放,所以驱动表t1的数据越小,分的次数也就越小,查询的效率就会越高
从上诉的BLJ还是NLJ算法得知,驱动表尽可能的要使用小表,但是什么数据条数少的表就是小表么?

案例一:

对于上面的数据,我们执行以下语句

select * from t1 straight_join t2 on(t1.b=t2.b) where t2.id<50
select * from t2 straight_join t1 on(t1.b=t2.b) where t2.id<50

这时,t2增加了where条件,那么t2作为驱动表,加载到join_buffer中的大小则只有50条,这时t2才是小表

案例二:

select t1.id ,t2.* from t1 straight_join t2 on(t1.b=t2.b) where t1.id<100 and t2.id < 100
select t1.id ,t2.* from t2 straight_join t1 on(t1.b=t2.b) where t1.id<100 and t2.id < 100

这时,t1 和t2都增加了where条件,条数都是100条,但是t1只查询了id列,所以这时t1是小表

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 151,829评论 1 331
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 64,603评论 1 273
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 101,846评论 0 226
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 42,600评论 0 191
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 50,780评论 3 272
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 39,695评论 1 192
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 31,136评论 2 293
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 29,862评论 0 182
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 33,453评论 0 229
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 29,942评论 2 233
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 31,347评论 1 242
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 27,790评论 2 236
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 32,293评论 3 221
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 25,839评论 0 8
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,448评论 0 181
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 34,564评论 2 249
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 34,623评论 2 249

推荐阅读更多精彩内容

  • --- layout: post title: "如果有人问你关系型数据库的原理,叫他看这篇文章(转)" date...
    蓝坠星阅读 742评论 0 3
  • MySQL技术内幕:SQL编程 姜承尧 第1章 SQL编程 >> B是由MySQL创始人之一Monty分支的一个版...
    沉默剑士阅读 2,345评论 0 3
  • 目录[TOC] MySQL的join到底能不能用 经常听到2种观点 join性能低,尽量少用 多表join时,变为...
    xcrossed阅读 546评论 0 4
  • 得知室友有一个半自动咖啡机后,今日终于见证了自制咖啡的全过程。首先把咖啡豆放入容器里,自动搅拌研磨后放入空置的小瓶...
    Little_Fairy66阅读 2,348评论 0 1
  • 2015年在心里种下了一颗种子 2016年7月10日那个给我自己的约定,来一次说走就走的旅行,只有我自己 2018...
    邓艳芝Rita阅读 444评论 0 1