神奇的 SQL 之 联表细节 → MySQL JOIN 的执行过程(二)

  • 时间:
  • 浏览:0
  • 来源:大发时时彩官网_去哪玩大发时时彩_哪里可以玩大发时时彩

开心一刻

  一头母牛在吃草,无缘无故 一头公牛从远处狂奔而来说:“快跑啊!!楼主来了!”

  母牛说:“楼主来了关我屁事啊?”

  公牛急忙说:“楼主吹牛逼呀!”

  母牛大惊,拔腿就跑,边跑边问:“你是公牛你怕那此啊?”

  公牛无奈道:“现在的楼主不仅吹牛逼,还扯蛋!”

  过后 小牛也在跟着跑,公牛和母牛问:“儿子你跑那此呢?”

  小牛说:“楼主还扯犊子啊”

前情回顾

  神奇的 SQL 之 联表细节 → MySQL JOIN 的执行过程(一)中,大伙儿讲到了 JOIN 的部分内容,像:驱动表、JOIN 大致流程等。那此,还没看?赶紧去看呀,啊? 你都知道呀,那你走吧

  走就走,你把欠的内容还上让我走;我欠那此了? 我欠,我欠...  我好像是欠点东西

    1、BKA(Batched Key Access)

    2、ON 和 WHERE

  请各位坐好,我想 开始表演了

环境准备

  数据库: MySQL 5.7.1 

  存储引擎: InnoDB 

  建表和初始化数据

-- 查看版本和存储引擎
SELECT VERSION();
SHOW ENGINES;
SHOW VARIABLES LIKE '%storage_engine%';

-- 表创建与数据初始化
DROP TABLE IF EXISTS tbl_user;
CREATE TABLE tbl_user (
  id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  user_name VARCHAR(80) NOT NULL COMMENT '用户名',
  sex TINYINT(1) NOT NULL COMMENT '性别, 1:男,0:女',
  create_time datetime NOT NULL COMMENT '创建时间',
  update_time datetime NOT NULL COMMENT '更新时间',
    remark VARCHAR(255) NOT NULL DEFAULT '' COMMENT '备注',
  PRIMARY KEY (id)
) COMMENT='用户表';

DROP TABLE IF EXISTS tbl_user_login_log;
CREATE TABLE tbl_user_login_log (
  id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  user_name VARCHAR(80) NOT NULL COMMENT '用户名',
  ip VARCHAR(15) NOT NULL COMMENT '登录IP',
  client TINYINT(1) NOT NULL COMMENT '登录端, 1:android, 2:ios, 3:PC, 4:H5',
  create_time datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (id)
) COMMENT='登录日志';
INSERT INTO tbl_user(user_name,sex,create_time,update_time,remark) VALUES
('何天香',1,NOW(), NOW(),'朗眉星目,一表人材'),
('薛沉香',0,NOW(), NOW(),'天星楼的总楼主薛摇红的女儿,也是天星楼的少总楼主,体态丰盈,乌发飘逸,指若春葱,袖臂如玉,风姿卓然,高贵典雅,人称“天星绝香”的武林第一大美女'),
('慕容兰娟',0,NOW(), NOW(),'武林东南西北四大世家之北世家慕容长明的独生女儿,生得玲珑剔透,粉雕玉琢,脾气却是刚烈无比,又喜着火红,什么都有人送绰号“火凤凰”,是除天星楼薛沉香之外的武林第二大美女'),
('苌婷',0,NOW(), NOW(),'当今皇上最宠爱的侄女,北王府的郡主,腰肢纤细,遍体罗绮,眉若墨画,唇点樱红;虽无沉香之雅重,兰娟之热烈,却别现出五种空灵'),
('柳含姻',0,NOW(), NOW(),'武林四绝之一的添愁仙子董婉婉的徒弟,体态窈窕,姿容秀丽,真个是秋水为神玉为骨,芙蓉如面柳如腰,眉若墨画,唇若点樱,不弱西子半分,更胜玉环一筹; 摇红楼、听雨轩,琵琶一曲值千金!'),
('李凝雪',0,NOW(), NOW(),'李相国的女儿,神采奕奕,英姿飒爽,爱憎分明'),
('周遗梦',0,NOW(), NOW(),'音神传人,湘妃竹琴的拥有者,云髻高盘,穿了一身黑色蝉翼纱衫,愈觉得

冰肌玉骨,粉面樱唇,格外娇艳动人'),
('叶留痕',0,NOW(), NOW(),'圣域圣女,肤白如雪,白衣飘飘,宛如仙女一般,微笑中带着说都没办法

的柔和之美'),
('郭疏影',0,NOW(), NOW(),'扬灰右使的徒弟,秀发细眉,玉肌丰滑,娇润脱俗'),
('钟钧天',0,NOW(), NOW(),'天界,玄天九部 - 钧天部的部主,超凡脱俗,仙气逼人'),
('王雁云',0,NOW(), NOW(),'尘缘山庄二小姐,刁蛮任性'),
('许侍霜',0,NOW(), NOW(),'药王谷谷主女儿,医术高明'),
('冯黯凝',0,NOW(), NOW(),'桃花门门主,娇艳如火,千娇百媚');
INSERT INTO tbl_user_login_log(user_name, ip, client, create_time) VALUES
('薛沉香', '10.53.56.78',2, '2019-10-12 12:23:45'),
('苌婷', '10.53.56.78',2, '2019-10-12 22:23:45'),
('慕容兰娟', '10.53.56.12',1, '2018-08-12 22:23:45'),
('何天香', '10.53.56.12',1, '2019-10-19 10:23:45'),
('柳含姻', '198.11.132.198',2, '2018-05-12 22:23:45'),
('冯黯凝', '198.11.132.198',2, '2018-11-11 22:23:45'),
('周遗梦', '198.11.132.198',2, '2019-06-18 22:23:45'),
('郭疏影', '220.181.38.148',3, '2019-10-21 09:45:56'),
('薛沉香', '220.181.38.148',3, '2019-10-26 22:23:45'),
('苌婷', '104.69.180.80',4, '2019-10-12 10:23:45'),
('王雁云', '104.69.180.61',4, '2019-10-16 20:23:45'),
('李凝雪', '104.69.180.62',4, '2019-10-17 20:23:45'),
('许侍霜', '104.69.180.63',4, '2019-10-18 20:23:45'),
('叶留痕', '104.69.180.64',4, '2019-10-19 20:23:45'),
('王雁云', '104.69.180.65',4, '2019-10-20 20:23:45'),
('叶留痕', '104.69.180.66',4, '2019-10-21 20:23:45');

SELECT * FROM tbl_user;
SELECT * FROM tbl_user_login_log;


DROP TABLE IF EXISTS tbl_range_access;
CREATE TABLE tbl_range_access (
  id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  a INT(11) NOT NULL COMMENT '测试索引', 
    name VARCHAR(80) NOT NULL COMMENT '姓名',
    age TINYINT(3) NOT NULL COMMENT '年龄',
  PRIMARY KEY (id),
    INDEX i_a(a)
) COMMENT='mrr测试';
INSERT INTO tbl_range_access(a,name,age) VALUES
(5,'123654', 23),
(8, 'asdf',20),
(1,'lljl',19),
(4, '98459',64),
(7,'zhangsan', 45),
(9,'lisi',46),
(2,'zhaoqian',25),
(6,'hello', 23),
(3,'world',80),
(10,'666',66),
(88, '888',88);

SELECT * FROM tbl_range_access;
View Code

  表 tbl_range_access 的数据要多某些,像中间示例不都还都上能 11 条记录,不都还都上能即使 a 字段上有索引, SELECT * FROM tbl_range_access WHERE a BETWEEN 4 AND 9; 什么都有会走索引,执行计划如下

  数据不要 ,优化器觉得 走索引,过后 回表查询数据,还不如直接走聚簇索引全表查询来的快,什么都有不都还都上能挑选走索引 i_a 

  既然数据不要 ,大伙儿就多造点数据,运行 data-init 下的 RangeAccessTest.java 中的 batchAddData 最好的法律法律依据就好,轻轻松松 10W 到手! 此时执行计划如下

MRR

  讲 BKA 不都还都上能 了,大伙儿不得不先看下 MRR,它是 BKA 的重要支柱

  全称 Multi-Range Read ,是对多行 IO 查询进行优化的五种策略,详情可看 MySQL 的 mrr-optimization 机会 MariaDB 的 Multi Range Read Optimization(MySQL 和 MariaDB 是那此关系? 呃,不都还都上能说吧,大伙儿是另有4个爹的儿子)。简单点来说,MRR 是优化器将随机 IO 转化为顺序 IO 以降低查询过程中 IO 开销的五种手段

那此是读盘与落盘(IO)

    当前绝大好多个情况下,MySQL 的数据是地处机械硬盘(SATA 盘)上的,极少数情况下是地处固态硬盘(SSD)上的;读盘指的是从磁盘读取数据的过程,落盘指的是从内存持久化到磁盘的过程

为那此顺序读盘比随机读盘快

    这与否

绝对的,多数情况下是不都还都上能

的;至于为那此,这涉及到机械硬盘的硬件知识了,包括其组织形态学

,以及磁盘的读盘过程,另外还需用了解 MySQL 数据的落盘与读盘(页为单位),内容不要

,就都没办法

本篇讲了(觉得

是触及到了楼主的知识盲区,楼主不让!)
    MariaDB 中说明了如下 3 个导致


    1、Rotating drives do not have to move the head back and forth
    2、One can take advantage of IO-prefetching done at various levels
    3、Each disk page will be read exactly once, which means we won't rely on disk cache (or buffer pool) to save us from reading the same page multiple times

  使用场景

    与否 任何情况下 MySQL 还会 使用 MRR 的,什么都有在某些情况下会用 MRR 来进行优化

摘自 Multi Range Read Optimization

    MySQL 中的 NDB 也会用到 MRR,一般而言,大伙儿不让关注,大伙儿只关注上图中的情况就行了

  理论不都还都上能 来点案例,完美!

  range access

    表 tbl_range_access 的 a 字段上大伙儿机会建了索引 i_a ,大伙儿来个范围查询,看下执行计划 EXPLAIN SELECT * FROM tbl_range_access WHERE a BETWEEN 4 AND 9;  如下

    此时不都还都上能用到 MRR,执行此查询时,磁盘 IO 访问模式将遵循下图中的红线

    机会是 SELECT * ,什么都有通过索引 i_a 先找到主键 ID,过后 通过主键 ID 回表(从聚簇索引)查询完整性记录;a 在索引 i_a 中是有序的,但不保证主键在 i_a 中也是有序的(关于 MySQL 的索引,推荐大伙儿去看:MySQL的索引),这就导致 回表的过程是随机 IO 

    为那此 MySQL 不都还都上能采用 MRR 来保证回表的过程是顺序 IO 呢?mrr-optimization 蕴含不都还都上能语句

Two optimizer_switch system variable flags provide an interface to the use of MRR optimization. The mrr flag controls whether MRR is enabled. If mrr is enabled (on), the mrr_cost_based flag controls whether the optimizer attempts to make a cost-based choice between using and not using MRR (on) or uses MRR whenever possible (off). By default, mrr is on and mrr_cost_based is on

     mrr 和 mrr_cost_based 的默认值是 on ;我简单画个图,大伙儿就明白这另有4个开关的作用了

    中间的示例难能可贵没使用 MRR,是优化器觉得 使用 MRR 反而提升了成本,还不如不使用

    大伙儿强制优化器使用 MRR:

-- 查看所有开关及其默认值
SELECT @@optimizer_switch;

-- mrr_cost_based设置成off,强制优化器使用 mrr
SET optimizer_switch='mrr_cost_based=off'; 

    大伙儿再来看看执行计划是那此样的

    此时用到 MRR,执行此查询时,磁盘 IO 访问模式将遵循下图中的红线

    此时回表查询的主键是有序的,会采用顺序 IO 来读取数据,从而提高查询传输速率

MySQL 蕴含个 rowids_buffer,用来缓存从索引 i_a 中查询到的数据记录(蕴含字段 a 和主键 ID),缓存满了机会索引查完了,再对缓存中记录按照主键 id 进行排序,再用排序后的主键 id 进行回表,使得回表查询的过程是顺序 IO

  与否 感觉 MRR 很重像二级索引与主键的 JOIN 操作,有这感觉就对了,中间的 BKA 也就好理解了

BKA

  BKA 全称是: Batched Key Access ,是对  INL 优化后的五种联表算法,类式与 BNL 对 SNL 的优化,但又某些不同,具体大伙儿往下看

  先在表 tbl_user 新增另有4个索引  ALTER TABLE tbl_user ADD index i_aaa(user_name); ,此时查看执行计划 EXPLAIN SELECT * FROM tbl_user_login_log tl LEFT JOIN tbl_user tu ON tl.user_name = tu.user_name; 如下图

  此时的联表算法什么都有 INL,机会表 tbl_user_login_log 的 user_name 是无索引的,不都还都上能从表 tbl_user_login_log 取出的 user_name 的值什么都与否 序的,再去关联 tbl_user ,就会随机匹配索引 i_aaa ,类式下图

  与否 很重类式于前面讲过的回表随机 IO ?

  BKA 功能默认是关闭的( batched_key_access=off ),开启它 

SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

  大伙儿再来看执行计划

   从tbl_user_login_log 查询到的 user_name 的值先装进去去 join buffer,当 join buffer 满了机会数据查完了,再对 join buffer 中间的值进行排序,过后 再去关联 tbl_user ,此时就会顺序匹配索引 i_aaa ,类式下图

  机会需用回表,不都还都上能 MySQL 会按不都还都上能 讲到过的回表流程再优化一次

默认值的思考

  MRR 相关的 3 个开关的默认值是不都还都上能 的 mrr=on,mrr_cost_based=on,batched_key_access=off 

   mrr=on 表示 mrr 功能是开启的,开启不须代表还会 使用,但不开启则一定享受不都还都上能 mrr 带来的优化; 

  mrr_cost_based=on 表示优化器会基于成本考虑来决定与否 使用 mrr,使用 mrr 反而使成本变高,那为那此使用 mrr ?不都还都上能 mrr 觉得 是带来了传输速率上的提升,不都还都上能使用它才有意义,过后 成本的计算又是优化器来完成的,过后 是另有4个比较简化的过程,一定能保证优化器的成本计与否 准确的吗? 80%准确肯定不敢保证,但经过不都还都上能多年的沉淀,绝大多数情况下,优化器的成本计与否 准确的,什么都有 mrr_cost_based 建议就采用默认值 on ,由优化器来决定与否 采用 mrr

   batched_key_access=off 表示默认不启用 BKA,说实话,我没太理解不都还都上能做的意图;既然与否 使用 mrr 交由优化器来决定了,没那此不把与否 使用 BKA 也交由优化器来决定?我想 猜到的机会导致 之一是 基本用不都还都上能 ,为那此不都还都上能说? 大伙儿回想下 BKA 会在那此情况下使用: 驱动表在关联的字段上无索引,而被驱动表在关联的字段上有索引 ,而机会驱动表在关联的字段上有索引了,还有必要进行缓存、排序、再关联被驱动表吗 ? 很显然不须了,机会索引的字段不都还都上能 什么都有有序的了;而实际应用中,关联的字段,不管是驱动表还是被驱动表,往往是一起地处索引的,而与否 另有4个地处索引而不都还都上能 不地处索引。这什么都有我买车人的猜想,望知道的大神能解惑下,小弟不胜感激!

总结

  1、mrr 带来的性能上的提升什么都有将随机 IO 优化成 顺序 IO,从而提高查询传输速率

  2、mrr 的使用场景比较有限, range access 和基于 req、eq_ref access 的 BKA,至于某些不适用的场景,大伙儿需用结合 mrr 的形态学 分溶于 导致

  3、mrr 相关的 3 个开关的默认值不建议改动,这不都还都上能 MySQL 不都还都上能多年的经验总结

    有人机会会不都还都上能 说了,既然这 3 个开关不推荐改,那看与不看这篇博文没那此区别,额...,你好像说的对

  4、关于 ON 和 WHERE,我不都还都上能说真的抱歉了,又要往后拖了,觉得 是不行,你,你......,你来打我呀