MySQL——小表驱动大表

桃扇骨 2021-09-02 09:20 518阅读 0赞

#### 小表驱动大表 ####

准备两站表：

CREATE TABLE `student` (
      `id` int(11) NOT NULL,
      `no` varchar(20) DEFAULT NULL,
      `name` varchar(20) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    INSERT INTO student VALUES(1, '0001', 'tom');
    INSERT INTO student VALUES(2, '0002', 'jerry');
    INSERT INTO student VALUES(3, '0003', 'acton');
    
    CREATE TABLE `score` (
      `id` int(11) NOT NULL,
      `no` varchar(20) DEFAULT NULL,
      `chinese` double(4,0) DEFAULT NULL,
      `math` double(4,0) DEFAULT NULL,
      `engilsh` double(4,0) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    INSERT INTO score VALUES(1, '0001', 70, 80, 90);
    INSERT INTO score VALUES(4, '0004', 11, 22, 33);

连接查询中的驱动表与被驱动表：

1.  当连接查询没有where条件时，左连接查询中，前面的表是驱动表，后面的表是被驱动表；右连接查询则相反；内连接查询中，哪张表的数据比较少，哪张表就是驱动表。
2.  当连接查询有where条件时，在对最终结果没有影响的前提下，优先选择结果集最小的那张表作为驱动表。

第一种情况：(student 3条数据，score 2条数据)

#左连接
    explain select * from student s1 left join score s2 on s1.no = s2.no;

驱动表为前面的，即student：  
![在这里插入图片描述][20200831125529743.png_pic_center]

#右连接
    explain select * from student s1 right join score s2 on s1.no = s2.no;

驱动表为后面的，即score：  
![在这里插入图片描述][20200831125603812.png_pic_center]

#内连接
    explain select * from student s1 inner join score s2 on s1.no = s2.no;

驱动表为数据少的，即score：  
![在这里插入图片描述][2020083112570187.png_pic_center]

第二种情况，加上where条件：

#where在score上，左外连接
    explain select * from student s1 left join score s2 on s1.no = s2.no where s2.no = 1;

![在这里插入图片描述][20200831130733422.png_pic_center]

#where在score上，右外连接
    explain select * from student s1 right join score s2 on s1.no = s2.no where s2.no = 1;

![在这里插入图片描述][20200831161321682.png_pic_center]

#内连接，where加在student上
    explain select * from student s1 inner join score s2 on s1.no = s2.no where s1.no = 1;

![在这里插入图片描述][20200831161411507.png_pic_center]

#### 连接查询优化 ####

Simple Nested-Loop Join Algorithms （简单嵌套循环连接算法）：

for (row1 : 驱动表) { 
        for (row2 : 被驱动表){ 
            if (conidtion == true){ 
                send client
            }
        }
    }

Index Nested-Loop Join Algorithms （索引嵌套循环连接算法）:

for (row1 : 驱动表) { 
        索引在被驱动表中命中，不用再遍历被驱动表了
    }

Block Nested-Loop Join Algorithm(基于块的连接嵌套循环算法):

其实很简单就是把一行变成了一批，块嵌套循环(BNL) 嵌套算法使用对在外部循环中读取的行进行缓冲，以减少必须读取内部循环中的表的次数。例如，如果将10行读入缓冲区并将缓冲区传递到下一个内部循环，则可以将内部循环中读取的每一行 与缓冲区中的所有10行进行比较。这将内部表必须读取的次数减少了一个数量级。
    
    MySQL连接缓冲区大小通过这个参数控制: join_ buffer_ size
    
    MySQL连接缓冲区有一些特征，只有无法使用索引时才会使用连接缓冲区;联接中只有感兴趣的列存储在其联接缓冲区中，而不是整个行;为每个可以缓冲的连接分配一个缓冲区，因此可以使用多个连接缓冲区来处理给定查询;在执行连接之前分配连接缓冲区，并在查询完成后释放连接缓冲区。
    
    所以查询时最好不要把*作为查询的字段，而是需要什么字段查询什么字段，这样缓冲区能够缓冲足够多的行。

算法的优先级：  
第一种算法忽略，MySQL不会采用这种的，当我们对被驱动表创建了索引，那么MySQL一定使用的第二种算法，当我们没有创建索引或者对驱动表创建了索引，那么MySQL一定使用第三种算法。

[20200831125529743.png_pic_center]: /images/20210813/739447cabfe245fdb4046cc4194dbdb3.png
[20200831125603812.png_pic_center]: /images/20210813/0e897eb2d74a4023a001b82013089b88.png
[2020083112570187.png_pic_center]: /images/20210813/d25088c6c15f4c908812874402a27f38.png
[20200831130733422.png_pic_center]: /images/20210813/c9bbcbded9614745867322a212bcd70b.png
[20200831161321682.png_pic_center]: /images/20210813/c747f57d1f5a48ebba074c85bb462bf4.png
[20200831161411507.png_pic_center]: /images/20210813/e1bde6cb9ec44dcc93ef8638951d4b9b.png