mysql数据库sql优化（二）看这里之关联查询优化

最新推荐文章于 2024-05-07 09:27:28 发布

喜欢编程的夏先生

最新推荐文章于 2024-05-07 09:27:28 发布

阅读量2.2k

点赞数

分类专栏： Java开发 mysql 文章标签： mysql 数据库服务器 database sql

本文链接：https://blog.csdn.net/qq_39746820/article/details/123837565

版权

Java开发同时被 2 个专栏收录

187 篇文章 10 订阅

订阅专栏

mysql

37 篇文章 1 订阅

订阅专栏

前面我们我们介绍了索引的优化，这篇文章我们介绍一下关联查询的优化。

我们首先准备一下数据库表，然后各插入20条数据

采用左外连接

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

我们可以看到上面的type字段为all即为全表扫描。

添加索引优化

ALTER TABLE book ADD INDEX Y ( card); #【被驱动表】，可以避免全表扫描 
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

可以看到第二行的 type 变为了 ref ， rows 也变成了优化比较明显。这是由左连接特性决定的。 LEFT JOIN 条件用于确定如何从右表搜索行，左边一定都有，所以右边是我们的关键点,一定需要建立索引。

ALTER TABLE `type` ADD INDEX X (card); #【驱动表】，无法避免全表扫描 
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

紧接着我们做如下操作

DROP INDEX Y ON book; 
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

所以，我们一定要在被驱动表上加上索引。

采用内连接

drop index X on type; 
drop index Y on book;（如果已经删除了可以不用再执行该操作）

换成 inner join （ MySQL 自动选择驱动表）

EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card=book.card;

添加索引优化

ALTER TABLE book ADD INDEX Y ( card); 
EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card=book.card;

ALTER TABLE type ADD INDEX X (card); 
EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card=book.card;

DROP INDEX X ON `type`; 
EXPLAIN SELECT SQL_NO_CACHE * FROM TYPE INNER JOIN book ON type.card=book.card;

ALTER TABLE `type` ADD INDEX X (card);
 EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card=book.card;

join 语句原理

join方式连接多个表，本质就是各个表之间数据的循环匹配。mysql5.5版本之前，mysql只支持一种表间关联方式，就是嵌套循环。如果关联表中的数据量很大，则join关联的执行时间会非常长。在mysql5.5以后的版本中，mysql通过BNLJ算法来优化嵌套执行。

1.驱动表和被驱动表

驱动表就是主表，被驱动表就是从表、非驱动表

①对于内连接来说

select * from A join B on...

A一定是驱动表吗？不一定，优化器会根据你查询语句做优化，决定先查询哪张表，先查询的那张表就是驱动表，反之就是被驱动表。通过explain关键字可以查看。

②对于外连接来说

select * from A left join B on...

或select * from B right A on...

通常，大家会认为A就是驱动表，B就是被驱动表，但也未必。测试如下：

2.Simple Nested-Loop Join（简单嵌套循环连接）

算法相当简单，从表A中取出一条数据1，遍历表B，将匹配到的数据放到result。以此类推，驱动表A中的每一条记录与被驱动表B的记录进行判断

3.Index Nested-Loop Join(索引嵌套循环)

其优化的思路主要是为了减少内层表数据的匹配次数，所以要求被驱动表上必须有索引才行。通过外层表匹配条件直接与内存表索引进行匹配，避免和内层表的每条记录去进行比较，这样极大的减少了对内层表的匹配次数。

驱动表中的每条记录通过被驱动表的索引进行访问，因为索引查询的成本比较固定，故mysql优化器倾向于使用记录数少的表作为驱动表。

如果被驱动表加上索引，效率是非常高的，但如果索引不是主键索引，所以还得进行一次回表查询。相比，被驱动表的索引是主键索引，效率会更高。

两个结论：

1. 使用 join 语句，性能比强行拆成多个单表执行 SQL 语句的性能要好；

2. 如果使用 join 语句的话，需要让小表做驱动表。

4.Block Nested-Loop Join（块嵌套循环连接）

如果存在索引，那么会使用index的方式进行join，如果join的列没有索引，被驱动表要扫描的次数太多了。每次访问被驱动表，其表中的记录都会被加载到内存中，然后再从驱动表中取出一条与其匹配，匹配结束后清楚内存，然后再从驱动表中加载一条记录，然后把被驱动表的记录加载到内存匹配，这样周而复始，大大增加了IO的次数。为了减少被驱动表IO次数，就出现了BNLJ。

不再是逐条获取驱动表的数据，而是一块一块的获取，引入join buffer缓冲区，将驱动表join 相关的部分数据列（大小受join buffer的限制）缓存到join buffer中，然后全表扫描被驱动表，被驱动表的每一条记录一次性和join buffer中所有的驱动表记录进行匹配（内存中操作）。将简单嵌套循环中的多次比较合并成一次，降低了备驱动表的访问频率。

注意：

这里缓存的不只是关联表的列，select后面的列也会缓存起来。

在一个有N个join关联的sql中会分配N-1个join buffer。所以查询的时候尽量减少不必要的字段，可以让join buffer中可以存放更多的列。