网站用户体验解决方案,强大的wordpress瀑布流主题,提升网站流量的方法,松江企业做网站5.1 单表访问之索引合并
我们前边说过 MySQL 在一般情况下执行一个查询时最多只会用到单个二级 索引#xff0c;但存在有特殊情况#xff0c;在这些特殊情况下也可能在一个查询中使用到多个二 级索引#xff0c;MySQL 中这种使用到多个索引来完成一次查询的执行方法称之为但存在有特殊情况在这些特殊情况下也可能在一个查询中使用到多个二 级索引MySQL 中这种使用到多个索引来完成一次查询的执行方法称之为索引 合并/index merge具体的索引合并算法有下边三种。
5.1.1. Intersection 合并
Intersection 翻译过来的意思是交集。这里是说某个查询可以使用多个二级 索引将从多个二级索引中查询到的结果取交集比方说下边这个查询
SELECT * FROM order_exp WHERE order_no a AND expire_time b;假设这个查询使用 Intersection 合并的方式执行的话那这个过程就是这样 的 从 idx_order_no 二级索引对应的 B树中取出 order_no a’的相关记录。 从 idx_insert_time 二级索引对应的 B树中取出 insert_time b’的相关记录。
二级索引的记录都是由索引列 主键构成的所以我们可以计算出这两个 结果集中 id 值的交集。 按照上一步生成的 id 值列表进行回表操作也就是从聚簇索引中把指定 id 值的完整用户记录取出来返回给用户。 为啥不直接使用 idx_order_no 或者 idx_insert_time 只根据某个搜索条件去读 取一个二级索引然后回表后再过滤另外一个搜索条件呢这里要分析一下两种 查询执行方式之间需要的成本代价。
只读取一个二级索引的成本 按照某个搜索条件读取一个二级索引根据从该二级索引得到的主键值进行 回表操作然后再过滤其他的搜索条件 读取多个二级索引之后取交集成本 按照不同的搜索条件分别读取不同的二级索引将从多个二级索引得到的主 键值取交集然后进行回表操作。 虽然读取多个二级索引比读取一个二级索引消耗性能但是大部分情况下读 取二级索引的操作是顺序 I/O而回表操作是随机 I/O所以如果只读取一个二级 索引时需要回表的记录数特别多而读取多个二级索引之后取交集的记录数非常 少当节省的因为回表而造成的性能损耗比访问多个二级索引带来的性能损耗更 高时读取多个二级索引后取交集比只读取一个二级索引的成本更低。 MySQL 在某些特定的情况下才可能会使用到 Intersection 索引合并哪些情 况呢
5.1.1.1. 情况一等值匹配
二级索引列是等值匹配的情况对于联合索引来说在联合索引中的每个列 都必须等值匹配不能出现只匹配部分列的情况。 而下边这两个查询就不能进行 Intersection 索引合并
SELECT * FROM order_exp WHERE order_no a AND insert_time a AND
order_status b AND expire_time c;
SELECT * FROM order_exp WHERE order_no a AND insert_time a;第一个查询是因为对 order_no 进行了范围匹配第二个查询是因为联合索 引u_idx_day_status中的order_status和expire_time列并没有出现在搜索条件中 所以这两个查询不能进行 Intersection 索引合并。
5.1.1.2. 情况二主键列可以是范围匹配
比方说下边这个查询可能用到主键和u_idx_day_status进行Intersection索引 合并的操作 SELECT * FROM order_exp WHERE id 100 AND insert_time a;对于 InnoDB 的二级索引来说记录先是按照索引列进行排序 如果该二级索引是一个联合索引那么会按照联合索引中的各个列依次排序。而二级索引的 用户记录是由索引列 主键构成的二级索引列的值相同的记录可能会有好多 条这些索引列的值相同的记录又是按照主键的值进行排序的。
所以重点来了之所以在二级索引列都是等值匹配的情况下才可能使用 Intersection 索引合并是因为只有在这种情况下根据二级索引查询出的结果集 是按照主键值排序的。
Intersection 索引合并会把从多个二级索引中查询出的主键值求交集如果 从各个二级索引中查询的到的结果集本身就是已经按照主键排好序的那么求交 集的过程就很容易。 假设某个查询使用 Intersection 索引合并的方式从 idx_order_no 和 idx_expire_time 这两个二级索引中获取到的主键值分别是 从 idx_order_no 中获取到已经排好序的主键值1、3、5 从 idx_expire_time 中获取到已经排好序的主键值2、3、4 那么求交集的过程就是这样逐个取出这两个结果集中最小的主键值如果 两个值相等则加入最后的交集结果中否则丢弃当前较小的主键值再取该丢 弃的主键值所在结果集的后一个主键值来比较直到某个结果集中的主键值用完 了时间复杂度是 O(n)。 但是如果从各个二级索引中查询出的结果集并不是按照主键排序的话那就 要先把结果集中的主键值排序完再来做上边的那个过程就比较耗时了。
按照有序的主键值去回表取记录有个专有名词叫Rowid Ordered Retrieval 简称 ROR。 另外不仅是多个二级索引之间可以采用 Intersection 索引合并索引合并 也可以有聚簇索引参加也就是我们上边写的情况二在搜索条件中有主键的范 围匹配的情况下也可以使用 Intersection 索引合并索引合并。为啥主键这就可以 范围匹配了还是得回到应用场景里
SELECT * FROM order_exp WHERE id 100 AND order_no a;假设这个查询可以采用 Intersection 索引合并我们理所当然的以为这个查 询会分别按照id 100这个条件从聚簇索引中获取一些记录在通过order_no ‘a’ 这个条件从 idx_order_no 二级索引中获取一些记录然后再求交集其实这样就 把问题复杂化了没必要从聚簇索引中获取一次记录。别忘了二级索引的记录中 都带有主键值的所以可以在从 idx_order_no 中获取到的主键值上直接运用条件 id 100 过滤就行了这样多简单。所以涉及主键的搜索条件只不过是为了从别 的二级索引得到的结果集中过滤记录罢了是不是等值匹配不重要。
当然上边说的情况一和情况二只是发生 Intersection 索引合并的必要条件 不是充分条件。也就是说即使情况一、情况二成立也不一定发生 Intersection 索引合并这得看优化器的心情。优化器只有在单独根据搜索条件从某个二级索 引中获取的记录数太多导致回表开销太大而通过 Intersection 索引合并后需 要回表的记录数大大减少时才会使用 Intersection 索引合并。
5.1.2. Union 合并
我们在写查询语句时经常想把既符合某个搜索条件的记录取出来也把符合 另外的某个搜索条件的记录取出来我们说这些不同的搜索条件之间是 OR 关系。 有时候 OR 关系的不同搜索条件会使用到不同的索引比方说这样
SELECT * FROM order_exp WHERE order_no a OR expire_time b Intersection 是交集的意思这适用于使用不同索引的搜索条件之间使用 AND 连接起来的情况Union 是并集的意思适用于使用不同索引的搜索条件之间使 用 OR 连接起来的情况。与 Intersection 索引合并类似MySQL 在某些特定的情 况下才可能会使用到 Union 索引合并
5.1.2.1. 情况一等值匹配
分析同 Intersection 合并
5.1.2.2. 情况二主键列可以是范围匹配 分析同 Intersection 合并5.1.2.3. 情况三使用 Intersection 索引合并的搜索条件
就是搜索条件的某些部分使用 Intersection 索引合并的方式得到的主键集合 和其他方式得到的主键集合取交集比方说这个查询
SELECT * FROM order_exp WHERE insert_time a AND order_status b AND
expire_time c OR (order_no a AND expire_time b);优化器可能采用这样的方式来执行这个查询 先按照搜索条件 order_no ‘a’ AND expire_time b’从索引 idx_order_no 和 idx_expire_time 中使用 Intersection 索引合并的方式得到一个主键集合。 再按照搜索条件 insert_time ‘a’ AND order_status ‘b’ AND expire_time ‘c’ 从联合索引 u_idx_day_status 中得到另一个主键集合。 采用 Union 索引合并的方式把上述两个主键集合取并集然后进行回表操作 将结果返回给用户。 当然查询条件符合了这些情况也不一定就会采用 Union 索引合并也得看 优化器的心情。优化器只有在单独根据搜索条件从某个二级索引中获取的记录数 比较少通过 Union 索引合并后进行访问的代价比全表扫描更小时才会使用 Union 索引合并。
5.1.3. Sort-Union 合并
Union 索引合并的使用条件太苛刻必须保证各个二级索引列在进行等值匹 配的条件下才可能被用到比方说下边这个查询就无法使用到 Union 索引合并
SELECT * FROM order_exp WHERE order_no a OR expire_time z这是因为根据 order_no a’从 idx_order_no 索引中获取的二级索引记录的主 键值不是排好序的根据 expire_time z’从 idx_expire_time 索引中获取的二级索 引记录的主键值也不是排好序的但是 order_no a’和 expire_time ‘z’这两个条 件又特别让我们动心所以我们可以这样
先根据 order_no a’条件从 idx_order_no 二级索引中获取记录并按照记录 的主键值进行排序再根据 expire_time z’条件从 idx_expire_time 二级索引中获取记录并按照 记录的主键值进行排序 因为上述的两个二级索引主键值都是排好序的剩下的操作和 Union 索引合 并方式就一样了。
上述这种先按照二级索引记录的主键值进行排序之后按照 Union 索引合并 方式执行的方式称之为 Sort-Union 索引合并很显然这种 Sort-Union 索引合并 比单纯的 Union 索引合并多了一步对二级索引记录的主键值排序的过程。
5.1.4. 联合索引替代 Intersection 索引合并
SELECT * FROM order_exp WHERE order_no a And expire_time z;这个查询之所以可能使用 Intersection 索引合并的方式执行还不是因为 idx_order_no 和 idx_expire_time 是两个单独的 B树索引要是把这两个列搞一个 联合索引那直接使用这个联合索引就把事情搞定了何必用啥索引合并呢就 像这样
ALTER TABLE order_exp drop index idx_order_no, idx_expire_time, add index idx_order_no_expire_time(order_no, expire_time);这样我们把 idx_order_no, idx_expire_time 都干掉再添加一个联合索引 idx_order_no_expire_time使用这个联合索引进行查询简直是又快又好既不用 多读一棵 B树也不用合并结果。
5.2. 连接查询
搞数据库一个避不开的概念就是 Join翻译成中文就是连接。使用的时候常 常陷入下边两种误区 误区一业务至上管他三七二十一再复杂的查询也用在一个连接语句中 搞定。 误区二敬而远之上次慢查询就是因为使用了连接导致的以后再也不敢 用了。 所以我们来学习一下连接的原理才能在工作中用好 SQL 连接。
5.2.1. 连接简介
5.2.1.1. 连接的本质
为了方便讲述我们建立两个简单的演示表并给它们写入数据
CREATE TABLE e1 (m1 int, n1 char(1));
CREATE TABLE e2 (m2 int, n2 char(1));
INSERT INTO e1 VALUES(1, a), (2, b), (3, c);
INSERT INTO e2 VALUES(2, b), (3, c), (4, d);连接的本质就是把各个连接表中的记录都取出来依次匹配的组合加入结果 集并返回给用户。 所以我们把 e1 和 e2 两个表连接起来的过程如下图所示 这个过程看起来就是把e1表的记录和e2的记录连起来组成新的更大的记录 所以这个查询过程称之为连接查询。连接查询的结果集中包含一个表中的每一条 记录与另一个表中的每一条记录相互匹配的组合像这样的结果集就可以称之为 笛卡尔积。因为表 e1 中有 3 条记录表 e2 中也有 3 条记录所以这两个表连接 之后的笛卡尔积就有 3×39 行记录。 在 MySQL 中连接查询的语法很随意只要在 FROM 语句后边跟多个表名 就好了比如我们把 e1 表和 e2 表连接起来的查询语句可以写成这样
SELECT * FROM e1, e2;5.2.1.2. 连接过程简介
我们可以连接任意数量张表但是如果没有任何限制条件的话这些表连接 起来产生的笛卡尔积可能是非常巨大的。比方说 3 个 100 行记录的表连接起来产 生的笛卡尔积就有 100×100×1001000000 行数据所以在连接的时候过滤掉特 定记录组合是有必要的在连接查询中的过滤条件可以分成两种比方说下边这 个查询语句
SELECT * FROM e1, e2 WHERE e1.m1 1 AND e1.m1 e2.m2 AND e2.n2 d;涉及单表的条件 比如 e1.m1 1 是只针对 e1 表的过滤条件e2.n2 d’是只针对 e2 表的过滤 条件。 涉及两表的条件 比如类似 e1.m1 e2.m2、e1.n1 e2.n2 等这些条件中涉及到了两个表。 看一下携带过滤条件的连接查询的大致执行过程在这个查询中我们指明了 这三个过滤条件 e1.m1 1 e1.m1 e2.m2 e2.n2 ‘d’ 那么这个连接查询的大致执行过程如下
步骤一首先确定第一个需要查询的表这个表称之为驱动表。单表中执行 查询语句只需要选取代价最小的那种访问方法去执行单表查询语句就好了就是 说从 const、ref、ref_or_null、range、index、all 等等这些执行方法中选取代价最 小的去执行查询。
此处假设使用 e1 作为驱动表那么就需要到 e1 表中找满足 e1.m1 1 的记 录因为表中的数据太少我们也没在表上建立二级索引所以此处查询 e1 表 的访问方法就设定为 all也就是采用全表扫描的方式执行单表查询。 很明显e1 表中符合 e1.m1 1 的记录有两条。
步骤二针对上一步骤中从驱动表产生的结果集中的每一条记录分别需要 到 e2 表中查找匹配的记录所谓匹配的记录指的是符合过滤条件的记录。因 为是根据 e1 表中的记录去找 e2 表中的记录所以 e2 表也可以被称之为被驱动 表。上一步骤从驱动表中得到了 2 条记录所以需要查询 2 次 e2 表。此时涉及 两个表的列的过滤条件 e1.m1 e2.m2 就派上用场了。
当 e1.m1 2 时过滤条件 e1.m1 e2.m2 就相当于 e2.m2 2所以此时 e2 表相当于有了 e2.m2 2、e2.n2 d’这两个过滤条件然后到 e2 表中执行单表查 询。
当 e1.m1 3 时过滤条件 e1.m1 e2.m2 就相当于 e2.m2 3所以此时 e2 表相当于有了 e2.m2 3、e2.n2 d’这两个过滤条件然后到 e2 表中执行单表查 询。 所以整个连接查询的执行过程就如下图所示 也就是说整个连接查询最后的结果只有两条符合过滤条件的记录 从上边两个步骤可以看出来这个两表连接查询共需要查询 1 次 e1 表2 次 e2 表。当然这是在特定的过滤条件下的结果如果我们把 e1.m1 1 这个条件 去掉那么从 e1 表中查出的记录就有 3 条就需要查询 3 次 e2 表了。也就是说 在两表连接查询中驱动表只需要访问一次被驱动表可能被访问多次。
5.2.1.3. 内连接和外连接
为了大家更好理解后边内容我们创建两个有现实意义的表并插入一些数 据
CREATE TABLE student (
number INT NOT NULL AUTO_INCREMENT COMMENT 学号,
name VARCHAR(5) COMMENT 姓名,
major VARCHAR(30) COMMENT 专业, PRIMARY KEY (number)
) EngineInnoDB CHARSETutf8 COMMENT 客户信息表;CREATE TABLE score (
number INT COMMENT 学号,
subject VARCHAR(30) COMMENT 科目,
score TINYINT COMMENT 成绩, PRIMARY KEY (number, subject)
) EngineInnoDB CHARSETutf8 COMMENT 客户成绩表;SELECT * FROM student; SELECT * FROM score;现在我们想把每个学生的考试成绩都查询出来就需要进行两表连接了因为 score 中没有姓名信息所以不能单纯只查询 score 表。连接过程就是从 student 表中取出记录在 score 表中查找 number 相同的成绩记录所以过滤条件就是 student.number socre.number整个查询语句就是这样
SELECT s1.number, s1.name, s2.subject, s2.score FROM student AS s1, score
AS s2 WHERE s1.number s2.number;从上述查询结果中我们可以看到各个同学对应的各科成绩就都被查出来了可 是有个问题King 同学也就是学号为 20200904 的同学因为某些原因没有参加 考试所以在 score 表中没有对应的成绩记录。 如果老师想查看所有同学的考试成绩即使是缺考的同学也应该展示出来 但是到目前为止我们介绍的连接查询是无法完成这样的需求的。我们稍微思考一 下这个需求其本质是想驱动表中的记录即使在被驱动表中没有匹配的记录 也仍然需要加入到结果集。为了解决这个问题就有了内连接和外连接的概念
对于内连接的两个表驱动表中的记录在被驱动表中找不到匹配的记录该 记录不会加入到最后的结果集我们上边提到的连接都是所谓的内连接。
对于外连接的两个表驱动表中的记录即使在被驱动表中没有匹配的记录 也仍然需要加入到结果集。
在 MySQL 中根据选取驱动表的不同外连接仍然可以细分为 2 种
左外连接选取左侧的表为驱动表。右外连接选取右侧的表为驱动表。 可是这样仍然存在问题即使对于外连接来说有时候我们也并不想把驱动 表的全部记录都加入到最后的结果集。 这就犯难了怎么办把过滤条件分为两种就可以就解决这个问题了所以 放在不同地方的过滤条件是有不同语义的
WHERE 子句中的过滤条件 WHERE 子句中的过滤条件就是我们平时见的那种不论是内连接还是外连 接凡是不符合 WHERE 子句中的过滤条件的记录都不会被加入最后的结果集。 ON 子句中的过滤条件 对于外连接的驱动表的记录来说如果无法在被驱动表中找到匹配 ON 子句 中的过滤条件的记录那么该记录仍然会被加入到结果集中对应的被驱动表记 录的各个字段使用 NULL 值填充。
需要注意的是这个 ON 子句是专门为外连接驱动表中的记录在被驱动表找 不到匹配记录时应不应该把该记录加入结果集这个场景下提出的所以如果把 ON 子句放到内连接中MySQL 会把它和 WHERE 子句一样对待也就是说内 连接中的 WHERE 子句和 ON 子句是等价的。
一般情况下我们都把只涉及单表的过滤条件放到 WHERE 子句中把涉及 两表的过滤条件都放到 ON 子句中我们也一般把放到 ON 子句中的过滤条件也 称之为连接条件。
左外连接的语法
左外连接的语法还是挺简单的比如我们要把 e1 表和 e2 表进行左外连 接查询可以这么写
SELECT * FROM e1 LEFT [OUTER] JOIN e2 ON 连接条件 [WHERE 普通过滤条 件];
其中中括号里的 OUTER 单词是可以省略的。对于 LEFT JOIN 类型的连接来说 我们把放在左边的表称之为外表或者驱动表右边的表称之为内表或者被驱动表。
所以上述例子中 e1 就是外表或者驱动表e2 就是内表或者被驱动表。需要注意 的是对于左外连接和右外连接来说必须使用 ON 子句来指出连接条 件。了解了左外连接的基本语法之后再次回到我们上边那个现实问题中来 看看怎样写查询语句才能把所有的客户的成绩信息都查询出来即使是缺考的考 生也应该被放到结果集中
SELECT s1.number, s1.name, s2.subject, s2.score FROM student AS s1 LEFT
JOIN score AS s2 ON s1.number s2.number;从结果集中可以看出来虽然 King 并没有对应的成绩记录但是由于采用 的是连接类型为左外连接所以仍然把她放到了结果集中只不过在对应的 成绩记录的各列使用 NULL 值填充而已。 右外连接的语法 右外连接和左外连接的原理是一样的语法也只是把 LEFT 换成 RIGHT 而已 SELECT * FROM e1 RIGHT [OUTER] JOIN e2 ON 连接条件 [WHERE 普通过滤 条件]; 只不过驱动表是右边的表 e2被驱动表是左边的表 e1。
内连接的语法
内连接和外连接的根本区别就是在驱动表中的记录不符合ON子句中的连接 条件时不会把该记录加入到最后的结果集一种最简单的内连接语法就是直接 把需要连接的多个表都放到 FROM 子句后边。其实针对内连接MySQL 提供了 好多不同的语法
SELECT * FROM e1 [INNER | CROSS] JOIN e2 [ON 连接条件] [WHERE 普通过滤 条件]; 也就是说在 MySQL 中下边这几种内连接的写法都是等价的
SELECT * FROM e1 JOIN e2;
SELECT * FROM e1 INNER JOIN e2;
SELECT * FROM e1 CROSS JOIN e2;上边的这些写法和直接把需要连接的表名放到 FROM 语句之后用逗号,分 隔开的写法是等价的
SELECT * FROM e1, e2;再说一次由于在内连接中 ON 子句和 WHERE 子句是等价的所以内连接 中不要求强制写明 ON 子句。 我们前边说过连接的本质就是把各个连接表中的记录都取出来依次匹配的 组合加入结果集并返回给用户。不论哪个表作为驱动表两表连接产生的笛卡尔 积肯定是一样的。而对于内连接来说由于凡是不符合 ON 子句或 WHERE 子句 中的条件的记录都会被过滤掉其实也就相当于从两表连接的笛卡尔积中把不符 合过滤条件的记录给踢出去所以对于内连接来说驱动表和被驱动表是可以互 换的并不会影响最后的查询结果。
但是对于外连接来说由于驱动表中的记录即使在被驱动表中找不到符合 ON 子句条件的记录时也要将其加入到结果集所以此时驱动表和被驱动表的关 系就很重要了也就是说左外连接和右外连接的驱动表和被驱动表不能轻易互换。
5.2.2. MySQL 对连接的执行
复习了连接、内连接、外连接这些基本概念后我们需要理解 MySQL 怎么 样来进行表与表之间的连接才能明白有的连接查询运行的快有的却慢。
5.2.2.1. 嵌套循环连接Nested-Loop Join
我们前边说过对于两表连接来说驱动表只会被访问一遍但被驱动表却 要被访问到好多遍具体访问几遍取决于对驱动表执行单表查询后的结果集中的 记录条数。
对于内连接来说选取哪个表为驱动表都没关系而外连接的驱动表是固定 的也就是说左外连接的驱动表就是左边的那个表右外连接的驱动表 就是右边的那个表。
如果有 3 个表进行连接的话那么首先两表连接得到的结果集就像是新的驱 动表然后第三个表就成为了被驱动表可以用伪代码表示一下这个过程就是这 样
for each row in e1 { #此处表示遍历满足对 e1 单表查询结果集中的每一条
记录N 条
for each row in e2 { #此处表示对于某条 e1 表的记录来说遍历满足
对 e2 单表查询结果集中的每一条记录M 条
for each row in t3 { #此处表示对于某条 e1 和 e2 表的记录组
合来说对 t3 表进行单表查询L 条
if row satisfies join conditions, send to client}}
}这个过程就像是一个嵌套的循环所以这种驱动表只访问一次但被驱动表 却可能被多次访问访问次数取决于对驱动表执行单表查询后的结果集中的记录 条数的连接执行方式称之为嵌套循环连接Nested-Loop Join这是最简单 也是最笨拙的一种连接查询算法时间复杂度是 ONML。
5.2.2.2. 使用索引加快连接速度
我们知道在嵌套循环连接的步骤 2 中可能需要访问多次被驱动表如果访问 被驱动表的方式都是全表扫描的话那酸爽不敢想象 但是查询 e2 表其实就相当于一次单表查询我们可以利用索引来加快查询 速度。回顾一下最开始介绍的 e1 表和 e2 表进行内连接的例子
SELECT * FROM e1, e2 WHERE e1.m1 1 AND e1.m1 e2.m2 AND e2.n2 d;我们使用的其实是嵌套循环连接算法执行的连接查询再把上边那个查询执 行过程表回顾一下 查询驱动表 e1 后的结果集中有两条记录嵌套循环连接算法需要对被驱动 表查询 2 次
当 e1.m1 2 时去查询一遍 e2 表对 e2 表的查询语句相当于
SELECT * FROM e2 WHERE e2.m2 2 AND e2.n2 d;
当 e1.m1 3 时再去查询一遍 e2 表此时对 e2 表的查询语句相当于
SELECT * FROM e2 WHERE e2.m2 3 AND e2.n2 d;可以看到原来的 e1.m1 e2.m2 这个涉及两个表的过滤条件在针对 e2 表做 查询时关于 e1 表的条件就已经确定了所以我们只需要单单优化对 e2 表的查询 了上述两个对 e2 表的查询语句中利用到的列是 m2 和 n2 列我们可以
在 m2 列上建立索引因为对 m2 列的条件是等值查找比如 e2.m2 2、e2.m2 3 等所以可能使用到 ref 的访问方法假设使用 ref 的访问方法去执行对 e2 表的查询的话需要回表之后再判断 e2.n2 d 这个条件是否成立。
这里有一个比较特殊的情况就是假设 m2 列是 e2 表的主键或者唯一二级 索引列那么使用 e2.m2 常数值这样的条件从 e2 表中查找记录的过程的代价 就是常数级别的。我们知道在单表中使用主键值或者唯一二级索引列的值进行等 值查找的方式称之为 const而 MySQL 把在连接查询中对被驱动表使用主键值或 者唯一二级索引列的值进行等值查找的查询执行方式称之为eq_ref。
在 n2 列上建立索引涉及到的条件是 e2.n2 ‘d’可能用到 range 的访问方 法假设使用 range 的访问方法对 e2 表的查询的话需要回表之后再判断在 m2 列上的条件是否成立。
假设 m2 和 n2 列上都存在索引的话那么就需要从这两个里边儿挑一个代 价更低的去执行对 e2 表的查询。当然建立了索引不一定使用索引只有在二 级索引 回表的代价比全表扫描的代价更低时才会使用索引。
另外有时候连接查询的查询列表和过滤条件中可能只涉及被驱动表的部分 列而这些列都是某个索引的一部分这种情况下即使不能使用 eq_ref、ref、 ref_or_null 或者 range 这些访问方法执行对被驱动表的查询的话也可以使用索 引扫描也就是 index(索引覆盖)的访问方法来查询被驱动表。
5.2.2.3. 基于块的嵌套循环连接Block Nested-Loop Join
扫描一个表的过程其实是先把这个表从磁盘上加载到内存中然后从内存中 比较匹配条件是否满足。
现实生活中的表成千上万条记录都是少的几百万、几千万甚至几亿条记录 的表到处都是。内存里可能并不能完全存放的下表中所有的记录所以在扫描表 前边记录的时候后边的记录可能还在磁盘上等扫描到后边记录的时候可能内存 不足所以需要把前边的记录从内存中释放掉。 而采用嵌套循环连接算法的两表连接过程中被驱动表可是要被访问好多次 的如果这个被驱动表中的数据特别多而且不能使用索引进行访问那就相当于 要从磁盘上读好几次这个表这个 I/O 代价就非常大了所以我们得想办法尽 量减少访问被驱动表的次数。
当被驱动表中的数据非常多时每次访问被驱动表被驱动表的记录会被加 载到内存中在内存中的每一条记录只会和驱动表结果集的一条记录做匹配之 后就会被从内存中清除掉。然后再从驱动表结果集中拿出另一条记录再一次把 被驱动表的记录加载到内存中一遍周而复始驱动表结果集中有多少条记录 就得把被驱动表从磁盘上加载到内存中多少次。
所以我们可不可以在把被驱动表的记录加载到内存的时候一次性和多条驱 动表中的记录做匹配这样就可以大大减少重复从磁盘上加载被驱动表的代价了。 所以 MySQL 提出了一个 join buffer 的概念join buffer 就是执行连接查询前申请 的一块固定大小的内存先把若干条驱动表结果集中的记录装在这个 join buffer 中然后开始扫描被驱动表每一条被驱动表的记录一次性和 join buffer 中的多 条驱动表记录做匹配因为匹配的过程都是在内存中完成的所以这样可以显著 减少被驱动表的 I/O 代价。使用 join buffer 的过程如下图所示 最最好的情况是 join buffer 足够大能容纳驱动表结果集中的所有记录。 这种加入了 join buffer 的嵌套循环连接算法称之为基于块的嵌套连接Block Nested-Loop Join算法。 这个 join buffer 的大小是可以通过启动参数或者系统变量 join_buffer_size 进 行配置默认大小为 262144 字节也就是 256KB最小可以设置为 128 字节。
show variables like join_buffer_size ;当然对于优化被驱动表的查询来说最好是为被驱动表加上效率高的索引 如果实在不能使用索引并且自己的机器的内存也比较大可以尝试调大 join_buffer_size 的值来对连接查询进行优化。
另外需要注意的是驱动表的记录并不是所有列都会被放到 join buffer 中 只有查询列表中的列和过滤条件中的列才会被放到 join buffer 中所以再次提醒 我们最好不要把*作为查询列表只需要把我们关心的列放到查询列表就好了 这样还可以在 join buffer 中放置更多的记录。
