MySQL 多表索引优化&join关联查询的NLJ算法 (第十讲)

一.单表优化

• 建表

create table article(
    id int unsigned not null primary key auto_increment,
    -- int类型、无符号、非空、主键、自增
    author_id int unsigned not null,
    -- int类型、无符号、非空
    category_id int unsigned not null,
    -- int类型、无符号、非空
    views int unsigned not null,
    -- int类型、无符号、非空
    comments int unsigned not null,
    -- int类型、无符号、非空
    title varchar(255) not null,
    -- varchar类型保留、非空
    content text not null
    -- text可变长度类型、非空
);

• 插入数据

insert into article(`author_id`,`category_id`,`views`,`comments`,`title`,`content`) values 
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(1,1,3,3,'3','3');

• 需求：查询category_id为1且comments大于1的情况下,views最多的author_id

select author_id from article where category_id=1 and comments>1 order by views desc limit 1;
-- 也就是说他需要的只是author_id，那么select后面就只跟这个author_id，我们完成了category_id为1且comments大于1这个条件后，将views逆排序，那么第一条就是最大的，我们再选取第一条。

二.双表优化

• 建表

-- 商品类别表
create table class(
    id int unsigned not null primary key auto_increment,
    card int unsigned not null
);

-- 图书表
create table book(
    bookid int unsigned not null auto_increment primary key,
    card int unsigned not null
);

驱动表概念

    什么是驱动表？当我们进行表连接的时候，驱动表会驱动另一张表进行查询。

• 举个例子：

SELECT * FROM class a LEFT JOIN book b ON a.id=b.bookid HAVING a.id=1

• 这条SQL语句是以class表为驱动表，那么他会在我们的class表中先查询出主键索引 id 然后再去通过这个索引去book表中查找。
• 那么如果我们的 class 表中只有两条数据，那么我们是不是直接就找到了，再通过这条数据去 book 表中查找，是不是就很快。
• 那如果book表中有一万条数据，那我们要扫描完这一万条数据，再去class那里扫描那两条数据，就会慢很多。
• 所以驱动表一定要是小表。驱动表可以是人为指定，也可以是优化器自己选择。

人为指定驱动表

• 驱动表除了优化器自动选择之外也可以人为的去选择，通过以下指令：

straight_join

• 比如说以下代码，我们就把 t1 表指定为了驱动表

select * from t1 straight_join t2 on ti.id=t2.id;

三.join的NLJ算法

    在我们的数据库使用中，一张表可能满足不了我们的日常需求，那么就会用到表连接，今天我们就来讲一下join的一些优化，为了方便理解我们先建一个表：

CREATE TABLE `test_join` ( /* 创建表test_join */
`id` int(11) NOT NULL auto_increment,
-- id int类型、非空、自增
`a` int(11) DEFAULT NULL,
-- a int类型、默认为空
`b` int(11) DEFAULT NULL,
-- b int类型、默认为空
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
-- create_time datetime类型、非空、自动记录创始时间、注释为记录出创建时间
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
COMMENT '记录更新时间',
-- update_time datetime类型、非空、自动记录创始时间、自创始开始就不断更新时间戳、注释为记录更新时间
PRIMARY KEY (`id`),
-- 指定 id 为主键
KEY `idx_a` (`a`)
-- 创建一个名为 idx_a 的索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 指定引擎为InnoDB、指定字符集为utf8mb4

1.关联查询的算法

• 关联查询有两种算法

1. Nested-Loop Join 算法(简称NLJ)
2. Block Nested-Loop Join 算法(简称BNL)

2.Nested-Loop Join 算法

    我们在关联查询的时候，一个简单的 Nested-Loop Join(NLJ)算法是从驱动表中取出一条数据，然后用这一条数据在被驱动表中扫描一遍，不停重复。

    那么如果我们的驱动表中没有索引呢？那驱动表中有 n 条数据，被驱动表中有 m 条数据，那岂不是要扫描 n*m 条数据，那数据量大的时候直接原地爆炸

    不过好在 MySQL 只有在有索引的情况下才会使用 NLJ 算法，没有索引就会使用 Block Nested-Loop Join 算法，这个只能有MySQL决定，不能进行人为干预。

举例

• 那么首先我们创建两张表

CREATE TABLE t1(
	id INT(10) PRIMARY KEY auto_increment,
	name VARCHAR(5)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;
CREATE TABLE t2(
	id INT(10) PRIMARY KEY auto_increment,
	name VARCHAR(5)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

• 那么我们现在 name 字段是没有索引的

1. 执行关联查询的SQL语句

explain select * from t1 inner join t2 where t1.name=t2.name;

2. 结果

mysql> explain select * from t1 inner join t2 where t1.name=t2.name;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL                                               |
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.01 sec)

• 那么从这个执行计划中我们可以看出 t2 为驱动表，因为explain在分析 SQL 语句时第一行的就是驱动表。选择 t2 做驱动表的原因：如果没固定连接方式优化器会优先选择小表做驱动表
• 但这里并没有使用到 NLJ ，因为我们并没有创建索引，我们如何分辨呢？在分析结果中 Extra 中出现了 Using join buffer (Block Nested Loop) 就是没有使用，如果没出现就代表着使用了。就像这样：

mysql> explain select * from t1 inner join t2 where t1.name=t2.name;
+----+-------------+-------+------------+------+---------------+----------+---------+------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref              | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+------------------+------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL     | NULL    | NULL             |    1 |   100.00 | Using where |
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_name      | idx_name | 23      | demo0128.t2.name |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+----------+---------+------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

大致流程

1. 先从 t2 表中取出一行数据
2. 从第一步的数据中取出要关联的字段，放到 t1 表中筛选
3. 取出 t1 表中满足条件的行，与 t2 表中满足条件的行合并，返回给客户端
4. 不停重复上面三步

    假如 t2 有一百条记录， t1 有十万条记录

    在这个过程中会读取 t2 表的所有数据，因此这里扫描了 100 行，然后遍历这 100 行数据中字段 a 的值，根据 t2 表中 a 的值索引扫描 t1 表中的对应行，这里也扫描了 100 行。因此整个过程扫描了 200 行。

    前面我们也说了被驱动表里的关联字段没有索引就会自动选择 BNL 算法，至于为什么呢？下面我们也会说

3.Block Nested-Loop Join 算法

    Block Nested-Loop Join(BNL) 算法的思想是：把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比，如果满足 join 条件，则返回结果给客户端。

• 回到刚才那条 SQL 语句，被驱动表中并没有索引

explain select * from t1 inner join t2 where t1.name=t2.name;

• 运行结果如下

mysql> explain select * from t1 inner join t2 where t1.name=t2.name;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL                                               |
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.01 sec)

• 我们在 Extra 中发现了 Using join buffer (Block Nested Loop) ，那么也就是说这条关联查询使用了 BNL 算法。

大致流程

1. 把 t2 的所有数据放到 join_buffer 中
2. 把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比
3. 返回满足 join 条件的数据

    在这个过程中，对表 t1 和 t2 都做了一次全表扫描，因此扫描的总行数为10000(表 t1 的数据总量) + 100(表 t2 的数据总量) = 10100。并且 join_buffer 里的数据是无序的，因此对表 t1 中的每一行，都要做 100 次判断，所以内存中的判断次数是 100 * 10000= 100 万次。如果数据量一多，听着都吓人。

4.这两种算法为何这样选择

    如果被驱动表的关联字段没有索引,两种算法的区别是这样的：

• 如果使用 NLJ 算法那么扫描行数为 100 * 10000 = 100万次，这个是磁盘扫描。
• 如果使用 BNL 算法那么磁盘扫描是 100 + 10000=10100 次，在内存中判断 100 * 10000 = 100万次。
• 那么磁盘扫描是 100 + 10000=10100 次，在内存中判断 100 * 10000 = 100万次。

四.优化关联查询

    我们只需要在被驱动表的关联字段添加索引就可以了，当然最好驱动表的关联字段也添加索引。

小表做驱动表

    上面我们也讲过 NLJ 算法会扫描驱动表所有的数据，假设驱动表的总行数为 n ，然后遍历这 n 行数据中所有的关联字段的值，根据驱动表中关联字段的值索引扫描被驱动表中的对应行，这里又会扫描 n 行，因此整个过程扫描了 2n 行。当使用 Index Nested-Loop Join 算法时，扫描行数跟驱动表的数据量成正比。那么我们用小表来做驱动表的话扫描的总行数是不是就变小了呢？所以能用小表做为驱动表的，就尽量用小表。

• 我们可以用 straight_join 来指定驱动表，在指令前面的就是驱动表

select * from t2 straight_join t1 on t2.a = t1.a;

五.临时表

    如果有的时候不方便在被驱动表上添加索引，我们可以创建一个临时表

insert into '临时表' select * from t1;

• 通过这条 SQL 语句把 t1 表的数据传进去在创建索引，切记临时表的表结构要和被驱动表一致。