MySQL是怎样运行的(二)：单表访问方法和连接查询

参考资料：[掘金] MySQL 是怎样运行的：从根儿上理解 MySQL
(https://juejin.cn/book/6844733769996304392/section)

第十一章- 条条大路通罗马 —— 单表访问方法
第十二章- 两个表的亲密接触 —— 连接的原理

单表访问方法

查询语句本质上只是一种声明式的语法，只是告诉MySQL我们要获取的数据符合哪些规则。至于MySQL如何将数据查出来，需要利用查询优化器进行分析，选出查询代价最低的方式。

下面介绍的单表访问方法以single_table表为例来说明

CREATE TABLE single_table (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    UNIQUE KEY idx_key2 (key2),
    KEY idx_key3 (key3),
    KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

我们为这个single_table表建立了1个聚簇索引和4个二级索引，分别是：

  • 为id列建立的聚簇索引。
  • 为key1列建立的idx_key1二级索引。
  • 为key2列建立的idx_key2二级索引，而且该索引是唯一二级索引。
  • 为key3列建立的idx_key3二级索引。
  • 为key_part1、key_part2、key_part3列建立的idx_key_part二级索引，这也是一个联合索引。

const

  • 通过主键或者唯一二级索引列来定位一条记录的访问方法定义为：const，意思是常数级别的，代价是可以忽略不计的。

  • 只适用于主键列或者唯一二级索引列和一个常数进行等值比较。

    SELECT * FROM single_table WHERE key2 = 3841;

  • 唯一二级索引列并不限制 NULL 值的数量，下列可能访问到多条记录，也就是说这个语句不可以使用const访问方法来执行。

    SELECT * FROM single_table WHERE key2 IS NULL;

查找过程的示意图如下：


无标题.png

ref

  • 适用于对某个普通的二级索引列与常数进行等值比较 
    SELECT * FROM single_table WHERE key1 = 'abc';
  • 二级索引列值为NULL的情况（唯一索引条件为key is null时，可以得到多条记录，使用的是ref访问方法，而不是const）。
  • 对于某个包含多个索引列的二级索引来说，只要是最左边的连续索引列是与常数的等值比较就可能采用ref的访问方法，比方说下边这几个查询： 
    SELECT * FROM single_table WHERE key_part1 = 'god like';
SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 = 'legendary';
SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 = 'legendary' AND key_part3 = 'penta kill';
    但是如果最左边的连续索引列并不全部是等值比较的话，它的访问方法就不能称为ref了，比方说这样： 
    SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 > 'legendary';

查找过程的示意图如下：


1.png

ref_or_null

有时候我们不仅想找出某个二级索引列的值等于某个常数的记录，还想把该列的值为NULL的记录也找出来，就像下边这个查询：

SELECT * FROM single_table WHERE key1 = 'abc' OR key1 IS NULL;

当使用二级索引而不是全表扫描的方式执行该查询时，这种类型的查询使用的访问方法就称为ref_or_null，这个ref_or_null访问方法的执行过程如下：

2.png

range

索引列需要匹配某个或某些范围的值，如下列查询

SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79);

从数学的角度看，每一个所谓的范围都是数轴上的一个区间，3个范围也就对应着3个区间：

  • 范围1：key2 = 1438
  • 范围2：key2 = 6328
  • 范围3：key2 ∈ [38, 79]，注意这里是闭区间。

我们可以把那种索引列等值匹配的情况称之为单点区间，上边所说的范围1和范围2都可以被称为单点区间，像范围3这种的我们可以称为连续范围区间。

range范围关键字：

对于B+树索引来说，只要索引列和常数使用=、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN、!=（不等于也可以写成<>）或者LIKE操作符连接起来，就可以产生一个所谓的区间。

利用LIKE进行范围查询只支持前缀查询，如 LIKE '%.com'

ref、ref_or_null、range 执行效率

ref、ref_or_null、range 查询能找到多条对应的记录，每条记录都需要回表。匹配到的记录越多，回表的代价越大，因此执行查询的代价取决于匹配到记录条数。
如果回表代价太大，MySQL可能会选择全表扫描的方式。

index

看下边这个查询：

  SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc';

由于key_part2并不是联合索引idx_key_part最左索引列，所以我们无法使用ref或者range访问方法来执行这个语句。但是这个查询符合下边这两个条件：

  • 它的查询列表只有3个列：key_part1, key_part2, key_part3，而索引idx_key_part又包含这三个列。
  • 搜索条件中只有key_part2列。这个列也包含在索引idx_key_part中。

也就是说我们可以直接通过遍历 idx_key_part 索引的叶子节点的记录来比较 key_part2 = 'abc' 这个条件是否成立，把匹配成功的二级索引记录的 key_part1, key_part2, key_part3 列的值直接加到结果集中就行了。

由于二级索引记录比聚簇索记录小的多（聚簇索引记录要存储所有用户定义的列以及所谓的隐藏列，而二级索引记录只需要存放索引列和主键），而且这个过程也不用进行回表操作，所以直接遍历二级索引比直接遍历聚簇索引的成本要小很多，设计MySQL的大叔就把这种采用遍历二级索引记录的执行方式称之为：index。

all

最直接的查询执行方式就是我们已经提了无数遍的全表扫描，对于InnoDB表来说也就是直接扫描聚簇索引，设计MySQL的大叔把这种使用全表扫描执行查询的方式称之为：all。

单表访问-多条件查询

多个索引间的选择

一般情况下只能利用单个二级索引执行查询，比方说下边的这个查询：

  SELECT * FROM single_table WHERE key1 = 'abc' AND key2 > 1000;

查询优化器会识别到这个查询中的两个搜索条件，分别对应idx_key1、idx_key2两个索引：

  • key1 = 'abc'
  • key2 > 1000

选择索引：

  • 优化器一般会根据single_table表的统计数据来判断到底使用哪个条件到对应的二级索引中查询扫描的行数会更少，选择那个扫描行数较少的条件到对应的二级索引中查询。一般来说等值查询比范围查询筛选出的数据条数少，这里假设优化器决定使用idx_key1索引。

查询步骤：

  • 步骤1：使用二级索引定位记录的阶段，也就是根据条件key1 = 'abc'从idx_key1索引代表的B+树中找到对应的二级索引记录。
  • 步骤2：回表阶段，也就是根据上一步骤中找到的记录的主键值进行回表操作，也就是到聚簇索引中找到对应的完整的用户记录，再根据条件key2 > 1000到完整的用户记录继续过滤。将最终符合过滤条件的记录返回给用户。

谨慎使用OR条件（无法使用索引的情况）

如何确定使用索引的区间：
把用不到索引的搜索条件替换为TRUE，进行化简。（因为我们不打算使用这些条件进行在该索引上进行过滤，所以先屏蔽这些条件，待到之后回表的时候再使用它们过滤）

以下SQL，key2添加了索引，common_filed没有添加索引，条件语句用AND连接。化简之后发现可以利用 idx_key2的范围区间 (100, +∞) 进行查询。

  SELECT * FROM single_table WHERE key2 > 100 AND common_field = 'abc';
  =>
  SELECT * FROM single_table WHERE key2 > 100 AND TRUE;
  =>
  SELECT * FROM single_table WHERE key2 > 100;

以下SQL，key2添加了索引，common_filed没有添加索引，条件语句用OR连接。化简之后发现需要进行全表搜索。

  SELECT * FROM single_table WHERE key2 > 100 OR common_field = 'abc';
  =>
  SELECT * FROM single_table WHERE key2 > 100 OR TRUE;
  =>
  SELECT * FROM single_table WHERE TRUE;

*索引合并

我们前边说过MySQL在一般情况下执行一个查询时最多只会用到单个二级索引，特殊情况下也可能在一个查询中使用到多个二级索引。这种使用到多个索引来完成一次查询的执行方法称之为：index merge，具体的索引合并算法有下边三种。

  • Intersection合并

    含义：某个查询使用多个二级索引，将从多个二级索引中查询到的结果取交集。

    只有在某些特定的情况才会使用Intersection合并。（相比使用单个索引，在A索引和B索引取完交集后结果集较小的情况效率较高）

    • 情况一：二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况。
    • 情况二：主键列可以是范围匹配

    例子：

    SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';

    创建联合索引可以替代Intersection合并。如上述SQL可以创建A和B的联合索引（此时A索引变成冗余索引，可以删除）

  • Union合并

    类似与Intersection合并，只不过Union合并处理的是OR连接的情形。

    SELECT * FROM single_table WHERE key1 = 'a' OR key3 = 'b'

    只有下列情形才可能会使用Union合并：

    • 情况一：二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只出现匹配部分列的情况。
    • 情况二：主键列可以是范围匹配。
    • 情况三：使用Intersection索引合并的搜索条件。

  • Sort-Union合并

    先按照二级索引记录的主键值进行排序，之后按照Union索引合并方式执行的方式称之为Sort-Union索引合并。这种Sort-Union索引合并比单纯的Union索引合并多了一步对二级索引记录的主键值排序的过程。

    例子：

    SELECT * FROM single_table WHERE key1 < 'a' OR key3 > 'z'
    • 先根据key1 < 'a'条件从idx_key1二级索引中获取记录，并按照记录的主键值进行排序
    • 再根据key3 > 'z'条件从idx_key3二级索引中获取记录，并按照记录的主键值进行排序
    • 合并记录和回表操作。

连接查询

连接过程简介

以下列查询语句为例：

SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';

  1. 首先确定第一个需要查询的表，这个表称之为驱动表。
    选择t1为驱动表，则执行查询 SELECT * FROM t1 WHERE t1.m1 > 1 ，得到两条记录。


    image.png

  2. 针对上一步骤中从驱动表产生的结果集中的每一条记录，分别需要到t2表中查找匹配的记录。

    • 当t1.m1 = 2时，过滤条件t1.m1 = t2.m2就相当于t2.m2 = 2，所以此时t2表相当于有了t2.m2 = 2、t2.n2 < 'd'这两个过滤条件，然后到t2表中执行单表查询。
    • 当t1.m1 = 3时，过滤条件t1.m1 = t2.m2就相当于t2.m2 = 3，所以此时t2表相当于有了t2.m2 = 3、t2.n2 < 'd'这两个过滤条件，然后到t2表中执行单表查询。

从上边两个步骤可以看出来，上边这个两表连接查询共需要查询1次t1表，2次t2表。在两表连接查询中，驱动表只需要访问一次，被驱动表可能被访问多次。

内连接和外连接

选取驱动表和被驱动表：

  • 对于内连接来说，驱动表和被驱动表是可以互换的。
  • 外连接的驱动表是固定的，也就是说左（外）连接的驱动表就是左边的那个表，右（外）连接的驱动表就是右边的那个表

ON 条件语句和 WHERE 条件语句：

  • 内连接中两种条件语句等价。
  • WHERE 条件语句：凡是不符合 WHERE 子句中的过滤条件的记录都不会被加入最后的结果集。
  • ON 条件语句：对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配 ON 子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用 NULL 值填充。

使用索引加快连接速度：
连接查询的步骤2中可能需要访问多次被驱动表，查询t2表其实就相当于一次单表扫描，我们可以利用索引来加快查询速度。还是以这条SQL为例，以t1表为驱动表，以t2表为被驱动表，步骤2可以从 t2.m2 和 t2.n2 两个索引中选择查询代价较小的使用。

SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';

基于块的嵌套循环连接（Block Nested-Loop Join）

由于驱动表查询出的记录较多，每条记录都要对应一次被驱动表的查询，比较耗费资源。如果被驱动表的记录加载到内存的时候，一次性和多条驱动表中的记录做匹配，这样就可以大大减少重复从磁盘上加载被驱动表的代价了。

join buffer是执行连接查询前申请的一块固定大小的内存，先把若干条驱动表结果集中的记录装在这个join buffer中，然后开始扫描被驱动表，每一条被驱动表的记录一次性和join buffer中的多条驱动表记录做匹配，因为匹配的过程都是在内存中完成的，所以这样可以显著减少被驱动表的I/O代价。

最好的情况是join buffer足够大，能容纳驱动表结果集中的所有记录，这样只需要访问一次被驱动表就可以完成连接操作了。这种加入了join buffer的嵌套循环连接算法称之为基于块的嵌套连接（Block Nested-Loop Join）算法。

这个join buffer的大小是可以通过启动参数或者系统变量join_buffer_size进行配置，默认大小为262144字节（也就是256KB），最小可以设置为128字节。当然，对于优化被驱动表的查询来说，最好是为被驱动表加上效率高的索引，如果实在不能使用索引，并且自己的机器的内存也比较大可以尝试调大join_buffer_size的值来对连接查询进行优化。

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 194,242评论 5赞 459
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 81,769评论 2赞 371
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 141,484评论 0赞 319
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 52,133评论 1赞 263
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 61,007评论 4赞 355
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 46,080评论 1赞 272
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 36,496评论 3赞 381
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 35,190评论 0赞 253
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 39,464评论 1赞 290
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 34,549评论 2赞 309
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 36,330评论 1赞 326
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 32,205评论 3赞 312
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 37,567评论 3赞 298
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 28,889评论 0赞 17
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 30,160评论 1赞 250
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 41,475评论 2赞 341
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 40,650评论 2赞 335

推荐阅读更多精彩内容