珠海门户网站建设公司,宝宝身上出现很多小红疹怎么办,长沙关键词优化新行情报价,有那些网站目录 一、前言二、表数据准备三、表关联查询原理和两种算法3.1、研究关联查询算法必备知识点3.2、嵌套循环连接 Nested-Loop Join(NLJ) 算法3.3、基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法3.4、被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 Nested… 目录 一、前言二、表数据准备三、表关联查询原理和两种算法3.1、研究关联查询算法必备知识点3.2、嵌套循环连接 Nested-Loop Join(NLJ) 算法3.3、基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法3.4、被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 Nested-Loop Join 呢 四、多表关联查询优化实践4.1、使用左连接查询 全部订单列表信息返回订单编号和客户昵称4.2、使用内连接查询 全部订单列表信息返回订单编号和客户昵称4.3、使用内连接查询 客户编号C00000999全部订单列表信息返回订单编号和客户昵称 五、总结 一、前言 索引是为了高效查询排好序的数据结构当表数据量到达一个量级没有对应索引帮助查询耗时会很长MySQL资源开销也会非常大对于多表关联查询来说没有对应索引辅助查询资源开销是灾难级的当然索引也不能随意创建要做到尽量少的索引解决尽量多的问题这里会对一些业务场景做索引优化演示也会讲解多表关联查询的底层原理。
二、表数据准备
这里准备10w条订单数据和1000条用户数据数据量越大能看到的效果越明显。
订单信息表和数据准备
# 创建订单信息表
DROP TABLE IF EXISTS order_info;
CREATE TABLE order_info (id bigint(20) NOT NULL AUTO_INCREMENT COMMENT 订单ID,order_no varchar(100) NOT NULL COMMENT 订单编号,customer_id bigint(20) NOT NULL COMMENT 客户ID,customer_no varchar(100) NOT NULL COMMENT 客户编号,goods_id bigint(20) NOT NULL COMMENT 商品ID,create_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间,PRIMARY KEY (id)
) ENGINEInnoDB COMMENT订单信息表;## 创建一个插入数据的存储过程
DROP PROCEDURE IF EXISTS insert_order_procedure;
delimiter;;
CREATE PROCEDURE insert_order_procedure()
BEGINDECLARE i INT DEFAULT 1;DECLARE customer_id BIGINT;DECLARE t_error INTEGER DEFAULT 0;DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error1;START TRANSACTION;WHILE ( i 100000 ) DOSET customer_id CEIL(RAND() * 1000);INSERT INTO order_info(order_no,customer_id,customer_no, goods_id, create_time) VALUES (CONCAT(ON00000,i), customer_id, CONCAT(C00000,customer_id), CEIL(RAND() * 100), NOW());SET i i 1;END WHILE;IF t_error1 THENROLLBACK;ELSECOMMIT;END IF;
END;;
delimiter;# 调用存储过程插入数据 我本地插入10w条数据耗时20s
CALL insert_order_procedure();客户信息表和数据准备
# 创建客户信息表
DROP TABLE IF EXISTS customer_info;
CREATE TABLE customer_info (id bigint(20) NOT NULL AUTO_INCREMENT COMMENT 客户ID,customer_no varchar(100) DEFAULT NULL COMMENT 客户编号,nick_name varchar(20) DEFAULT NULL COMMENT 昵称,age int(11) DEFAULT NULL COMMENT 年龄,create_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间,PRIMARY KEY (id)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT客户信息表;## 创建一个插入数据的存储过程
DROP PROCEDURE IF EXISTS insert_customer_procedure;
delimiter;;
CREATE PROCEDURE insert_customer_procedure()
BEGINDECLARE i INT DEFAULT 1;DECLARE t_error INTEGER DEFAULT 0;DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error1;START TRANSACTION;WHILE ( i 1000 ) DOINSERT INTO customer_info(customer_no,nick_name, age, create_time) VALUES (CONCAT(C00000,i),CONCAT(Kerwin,i), CEIL(RAND() * 100), NOW());SET i i 1;END WHILE;IF t_error1 THENROLLBACK;ELSECOMMIT;END IF;
END;;
delimiter;# 调用存储过程插入数据
CALL insert_customer_procedure();三、表关联查询原理和两种算法 在MySQL中表关联查询有两种算法在关联字段有索引时使用的是 嵌套循环连接 Nested-Loop Join(NLJ) 算法在没有索引时使用的是 基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法下面会对两种算法做解释。
3.1、研究关联查询算法必备知识点
研究关联查询首先要知道什么是驱动表和什么是被驱动表MySQL常用三种关联查询分别为左外连接LEFT JOIN ON、右外连接RIGHT JOIN ON、内连接INNER JOIN ON对于这三种关联查询驱动表和被驱动表也是不同的下面举例说明。
左外连接LEFT JOIN ON
EXPLAIN SELECT * FROM order_info t1 LEFT JOIN customer_info t2 ON t1.customer_not2.customer_no;执行计划中如果id相同那么执行顺序是由上到下在执行计划里我们可以看到 t1(order_info)在上t2(customer_info)在下关联查询中谁在上那么谁就是驱动表所以 t1(order_info)为驱动表t2(customer_info)为被驱动表。
右外连接RIGHT JOIN ON
EXPLAIN SELECT * FROM order_info t1 RIGHT JOIN customer_info t2 ON t1.customer_not2.customer_no;执行计划中如果id相同那么执行顺序是由上到下在执行计划里我们可以看到t2(customer_info)在上 t1(order_info)在下关联查询中谁在上那么谁就是驱动表所以 t2(customer_info)为驱动表t1(order_info)为被驱动表。 内连接INNER JOIN ON 查看执行计划SQL EXPLAIN SELECT * FROM order_info t1 INNER JOIN customer_info t2 ON t1.customer_not2.customer_no;两张表的customer_no字段都没有索引 两张表的customer_no字段都有索引# 添加索引
ALTER TABLE customer_info ADD INDEX idx_customerNo(customer_no);
ALTER TABLE order_info ADD INDEX idx_customerNo(customer_no);customer_info表有customer_no字段索引order_info表没有# 删除索引
ALTER TABLE order_info DROP INDEX idx_customerNo;order_info表有customer_no字段索引customer_info表没有# 删除索引
ALTER TABLE customer_info DROP INDEX idx_customerNo;
# 添加索引
ALTER TABLE order_info ADD INDEX idx_customerNo(customer_no);总结 驱动表和被驱动表的辨认方式就在其执行顺序在上的是驱动表在下的是被驱动表左连接查询那么左边的表一定是驱动表右连接查询那么右边的表一定是驱动表内连接会根据表的大小还有是否有索引来选择驱动表和被驱动表因为customer_info只有1000条数据而order_info表有10w条数据在两张表都没有索引的情况下内连接选择了customer_info做驱动表在两张表都有索引的情况下会内连接选择小表做驱动表在一张表有索引一张表没有索引的情况下内连接会选择没有索引的表作为驱动表原因会在下面说明。
3.2、嵌套循环连接 Nested-Loop Join(NLJ) 算法 嵌套循环连接算法一次一行循环地从第一张表称为驱动表中读取行在这行数据中取到关联字段根据关联字段在另一张表被驱动表里取出满足条件的行然后取出两张表的结果合集嵌套循环连接中被驱动表关联字段一定是有索引如果没有索引那用的就是基于块的嵌套循环连接算法下面演示一下。
因为嵌套循环连接需要索引这里先对order_info表的customer_no创建一个普通索引
# 如果没有删除customer_info表中的idx_customerNo索引需要先删除
ALTER TABLE customer_info DROP INDEX idx_customerNo;
# 添加order_info表索引
ALTER TABLE order_info ADD INDEX idx_customerNo(customer_no);查看执行计划
EXPLAIN SELECT * FROM order_info t1 INNER JOIN customer_info t2 ON t1.customer_not2.customer_no;这里可以看到内连接使用了customer_info作为驱动表使用order_info作为被驱动表还使用到了我们刚刚创建的idx_customerNo索引。
上面sql的大致流程如下 1、从表 t2 中读取一行数据如果t2表有查询过滤条件的会从过滤结果里取出一行数据。2、从第 1 步的数据中取出关联字段 customer_no到表 t1 中查找。3、取出表 t1 中满足条件的行跟 t2 中获取到的结果合并作为结果返回给客户端。4、 重复上面 3 步。 整个过程会读取 t2 表的所有数据(扫描1000行)然后遍历这每行数据中字段 customer_no 的值根据 t2 表中 customer_no 的值索引扫描 t1 表中的对应行(扫描1000次 t1 表的索引因为我们这里是一对多关系1次扫描可能最终扫描 t1 表对应多行完整数据如果是一对一关系也就是总共 t1 表也扫描了1000行)为了方便计算假设一个用户编号只能下一个订单因此整个过程扫描了 2000 行。 如果被驱动表的关联字段没索引使用NLJ算法性能会比较低(下面有详细解释)MySQL会选择Block Nested-Loop Join算法。
3.3、基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法
基于块的嵌套循环连接算法和嵌套循环连接算法区别就在于被驱动表的关联字段没索引基于块的嵌套循环连接算法会把驱动表的数据读入到 join_buffer 中然后扫描被驱动表把被驱动表每一行取出来跟 join_buffer 中的数据做对比。
先将表中的二级索引全删除
# 删除索引
ALTER TABLE customer_info DROP INDEX idx_customerNo;
ALTER TABLE order_info DROP INDEX idx_customerNo;查看执行计划 这里可以看到内连接使用了customer_info作为驱动表使用order_info作为被驱动表而且在Extra中出现了Using join buffer (Block Nested Loop)说明该关联查询使用的是 BNL 算法。 上面sql的大致流程如下 1、把 t2 的所有数据放入到 join_buffer 中2、把表 t1 中每一行取出来跟 join_buffer 中的数据做对比3、返回满足 join 条件的数据 整个过程对表 t1 和 t2 都做了一次全表扫描因此扫描的总行数为100000(表 t1 的数据总量) 1000(表 t2 的数据总量) 101000。并且 join_buffer 里的数据是无序的因此对表 t1 中的每一行都要做 1000 次判断所以内存中的判断次数是1000 * 100000 1 亿次。 这个例子里表 t2 才 1000 行要是表 t2 是一个大表join_buffer 放不下怎么办呢join_buffer 的大小是由参数 join_buffer_size 设定的默认值是 256k。如果放不下表 t2 的所有数据话策略很简单就是分段放。 比如 t2 表有1000行记录 join_buffer 一次只能放800行数据那么执行过程就是先往 join_buffer 里放800行记录然后从 t1 表里取数据跟 join_buffer 中数据对比得到部分结果然后清空 join_buffer 再放入 t2 表剩余200行记录再次从 t1 表里取数据跟 join_buffer 中数据对比。所以就多扫了一次 t1 表。
3.4、被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 Nested-Loop Join 呢 如果上面第二条sql使用 Nested-Loop Join那么扫描行数为 1000 * 100000 1亿次这个是磁盘扫描。很显然用BNL磁盘扫描次数少很多相比于磁盘扫描BNL的内存计算会快得多。 因此MySQL对于被驱动表的关联字段没索引的关联查询一般都会使用 BNL 算法。如果有索引一般选择 NLJ 算法有索引的情况下 NLJ 算法比 BNL算法性能更高。
四、多表关联查询优化实践 在前面说明了表关联查询原理和两种算法其实基本优化思路就已经有了那就是一定要小表驱动大表给表关联字段加索引下面会对一些情况做举例说明。
PS这里例子使用的MySQL版本为5.7MySQL8.0连接查询看着是没什么变化但是底层好像做了一些优化我试了几个例子有索引反而会比没索引慢一点
4.1、使用左连接查询 全部订单列表信息返回订单编号和客户昵称
# 删除索引
ALTER TABLE customer_info DROP INDEX idx_customerNo;
ALTER TABLE order_info DROP INDEX idx_customerNo;SELECT t1.order_no,t2.nick_name FROM order_info t1 LEFT JOIN customer_info t2 ON t1.customer_not2.customer_no;无索引情况查询测试 使用左连接查询无索引时耗时12.497s因为使用了左连接所以会已左边的表为驱动表右连接则会用右边表作为驱动表。 添加order_info表中的customer_no字段索引查询测试 # 添加索引
ALTER TABLE order_info ADD INDEX idx_customerNo(customer_no);添加了order_info表的customer_no字段索引查询耗时和不添加基本一致也没有使用到创建的索引。 添加customer_info表中的customer_no字段索引查询测试 ALTER TABLE customer_info ADD INDEX idx_customerNo(customer_no);添加customer_info表中的customer_no字段索引查询耗时0.664s效率有大大提升这里也看到了被驱动表有使用到索引会使用NLJ算法。
4.2、使用内连接查询 全部订单列表信息返回订单编号和客户昵称
# 删除索引
ALTER TABLE customer_info DROP INDEX idx_customerNo;
ALTER TABLE order_info DROP INDEX idx_customerNo;SELECT t1.order_no,t2.nick_name FROM order_info t1 INNER JOIN customer_info t2 ON t1.customer_not2.customer_no;无索引情况查询测试 MySQL选择了小表customer_info作为驱动表在没有创建索引时查询耗时12.589s执行计划中显示使用了BNL算法下面我们加上索引试一下。 添加order_info表中的customer_no字段索引查询测试 # 添加索引
ALTER TABLE order_info ADD INDEX idx_customerNo(customer_no);添加上索引后查询耗时0.306s性能有明显提升MySQL还是选择了customer_info表做驱动表这里使用了NLJ算法。 添加customer_info表中的customer_no字段索引查询测试 # 添加索引
ALTER TABLE customer_info ADD INDEX idx_customerNo(customer_no);customer_info表中的customer_no字段索引查询耗时0.690s性能比不加索引高很多比在order_info表中加索引性能又差一些当只有小表有索引时MySQL也会将小表作为被驱动表这里大表驱动小表肯定会比上面小表驱动大表性能稍差一些。
4.3、使用内连接查询 客户编号C00000999全部订单列表信息返回订单编号和客户昵称
# 删除索引
ALTER TABLE customer_info DROP INDEX idx_customerNo;
ALTER TABLE order_info DROP INDEX idx_customerNo;SELECT t1.order_no,t2.nick_name FROM order_info t1 LEFT JOIN customer_info t2 ON t1.customer_not2.customer_no WHERE t1.customer_noC00000999;无索引情况查询测试 查询耗时0.078s在无索引时还是选择的t2表作为驱动表但是查询时间确是快了很多。 添加order_info表中的customer_no字段索引查询测试 # 添加索引
ALTER TABLE order_info ADD INDEX idx_customerNo(customer_no);这里查询耗时0.045s快了1倍而且加上order_info表中的索引后MySQL选择了order_info作为驱动表MySQL认为我们给了一个条件t1.customer_no‘C00000999’在order_info表中符合条件的数据会比较少小于customer_info中的1000条数据所以这里采用order_info作为驱动表并且使用到了加的customer_no索引。 删除order_info表中索引添加customer_info表中的customer_no字段索引查询测试 # 删除索引
ALTER TABLE order_info ADD INDEX idx_customerNo(customer_no);
# 添加索引
ALTER TABLE customer_info ADD INDEX idx_customerNo(customer_no);耗时0.066s比不加索引快一点这里还是选择了customer_info作为驱动表。 给order_info表和customer_info表中的customer_no字段都添加索引查询测试 # 添加索引
ALTER TABLE customer_info ADD INDEX idx_customerNo(customer_no);
# 添加索引
ALTER TABLE order_info ADD INDEX idx_customerNo(customer_no);耗时0.032s效率又有提升而且这里两个索引都使用到了customer_info表因为索引后数据还是比order_info少所有MySQL还是选择了customer_info做驱动表。
五、总结
对于关联sql的优化 关联字段加索引 对于关联字段来说被驱动表最好加上索引如果使用内连接不确定那张表是被驱动表时最好驱动表和被驱动表都加上。 小表驱动大表 在做左连接或右连接查询的时候如果业务允许一定要用小表驱动大表或者使用内连接交给where条件去判断业务。 where的条件也是要添加对应索引的 两张表关联查询不加条件t1 100w条数据t2 1000条数据内连接肯定选择t2做驱动表但是如果有where条件并且有索引t1表筛选后只剩10条t2还是1000条那么这个时候就会使用到t1做驱动表查询效率会有所提升。 关于MySQL5.7和MySQL8.0连接查询效率问题 我本地跑了一些连接查询的例子MySQL8.0在有些情况比MySQL5.7的确要快而且是快很多而且有些情况MySQL8.0没有索引时连接查询也很快甚至比我加了索引走NLJ算法还快看了执行计划和MySQL5.7是一样的不知道是不是因为我本地部署两个版本配置是否有什么区别MySQL8.0做了很多优化等以后研究明白再来详细说明。
