当前位置:   article > 正文

MySql优化——索引优化与查询优化(2) 关联查询优化_mysql 连表查询数量优化

mysql 连表查询数量优化

1、关联查询优化

1 数据准备

  1. #分类
  2. CREATE TABLE IF NOT EXISTS `type` (
  3. `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  4. `card` INT(10) UNSIGNED NOT NULL,
  5. PRIMARY KEY (`id`)
  6. );
  7. #图书
  8. CREATE TABLE IF NOT EXISTS `book` (
  9. `bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  10. `card` INT(10) UNSIGNED NOT NULL,
  11. PRIMARY KEY (`bookid`)
  12. );
  1. #向分类表中添加20条记录
  2. INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() *20)));
  3. #执行20
  4. #向图书表中添加28条记录
  5. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  6. #执行20

我们先放一张关于各种多表查询 连接的图

连接分为以下几种

  • 内连接

  • 外连接
    •  左外连接
    •  右外连接
  • 满外连接

使用UNION 失效满外连接

  • union

     

UNION 操作符返回两个查询的结果集的并集,去除重复记录。

  •        union ALL

             

 

UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分,不去重。

 注意:执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据 不存在重复数据,或者不需要去除重复的数据,则尽量使用UNION ALL语句,以提高数据查询的效 率。

      

2、采用左外连接

##左表中,满足ON条件的,或不满足条件的都要

 

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

根据分析看出,type在上方,是驱动表,而book是被驱动表

驱动表里根据连接条件,一次拿出一条数据,进入被驱动表里查找数据,进行遍历比较

如果驱动表数据是n条数据,被驱动表m条数据,在没有索引的情况下(type =ALL) ,比较的次数是 n*m

和 java的双层嵌套循环类似,时间复杂度是 On^2

优化:

在被驱动表里添加索引

  1. #[被驱动表]添加索引,避免全表扫描
  2. ALTER TABLE book ADD INDEX Y (card);

 再次执行

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

我们分析此次执行的效率:
被驱动表 book中有了 card的索引,card在book中是依据B+树进行的二分查找,时间复杂度变成了 Olog2m

总时间复杂度为 On*log2m

结论: 

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

 此处我就不再给左表建立card的索引,大家可以自己验证一下,即使驱动表建立索引,也无法避免驱动表的全表扫描。

另外要注意一点,多表连接的连接条件字段类型一定要一致,如果两者类型不一致,在给两个表该字段建立索引时,会导致索引失效(发生类型转换)

3、内连接

#只保留两表满足ON条件的部分

 内连接时,Mysql自动选择驱动表(一般小表驱动大表)

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

 没有任何索引的情况

 #type是驱动表 book是被驱动表

添加索引优化

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

 

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

 注意:此时驱动表 到底是book还剩 type 是由优化器决定的,它回衡量谁作为驱动表的成本更低,一般来说驱动表是要进行全表遍历的,所以,驱动表数据记录越少越好,这就是所谓的小表驱动大表,比如我尝试给type多添加几条数据(当前book和type都是20条)

现在我让book数据还是20条,但是type数据变成了26条,内连接查询语句不变

执行结果如下:

 

 优化器 选择了book作为驱动表。

另外,注意这么一件事:

如果删除了当前只有一个表有索引,优化器自动会把当前有索引的表,作为被驱动表,即使它当前可能数据量较小

比如,我删除了 当前索引X,X原本是被驱动表的索引,type的记录数也多于book,但优化器会把当前拥有索引的book 转换为被驱动表:

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

 

结论

  • 对于内连接来说,查询优化器会自动决定谁作为 驱动表,谁作为被驱动表;
  • 如果表的连接条件中只能有一个字段有索引,则有索引的字段所在的表会作为被驱动表出现(避免出现全表遍历)
  • 对于内连接来说,两个表条件相同的情况(同字段都有索引,或者都没有索引)普遍情况是小表驱动大表(因为驱动表是避免不了要全表遍历的)

4、JOIN语句原理

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


1. 驱动表和被驱动表
驱动表就是主表,被驱动表就是从表、非驱动表

从EXPLAIN 分析语句的角度来说,上边的表是驱动表,下面的是被驱动表


对于内连接来说:

SELECT * FROM A JOIN B ON ...

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

对于外连接来说:

  1. SELECT * FROM A LEFT JOIN B ON ...
  2. SELECT * FROM B RIGHT JOIN A ON ...

通常大家认为 A是驱动表,B是被驱动表。但也未必:

因为查询优化器会调整查询语句,比如下面测试1,就被优化器调整成了内连接,驱动表到底是谁,就由优化器决定了

  1. CREATE TABLE a(f1 INT, f2 INT,INDEX(f1))ENGINE=INNODB;
  2. CREATE TABLE b(f1 INT, f2 INT)ENGINE=INNODB;
  3. INSERT INTO a VALUES(1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
  4. INSERT INTO b VALUES(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);
  5. SELECT * FROM b;
  6. #测试1
  7. EXPLAIN SELECT * FROM a LEFT JOIN b ON(a.f1=b.f1) WHERE (a.f2=b.f2);
  8. #测试2
  9. EXPLAIN SELECT * FROM a LEFT JOIN b ON(a.f1=b.f1) AND (a.f2=b.f2);

2、Simple Nested-Loop Join (简单嵌套循环连接)

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

此种效率非常低,以上述表A数据 100条,表B数据1000条计算,则A*B =10万次,开销统计如下:(AB表数据条数是经过where筛选剩余的条数)

 

 此处没有索引,所以不会有回表操作。

3、Index Nested-Loop Join (索引嵌套循环连接)

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

 如果是非主键索引,还需要进行回表操作

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

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

4、Block Nested-Loop Join

如果存在索引,那么会使用index的方式进行join,如果join的列没有索引,被驱动表要扫描的次数太多了。每次访问被驱动表,其表中的记录都会被加载到内存中,然后再从驱动表中取一条与其匹配,匹配结束后清除内存,然后再从驱动表中加载一条记录,然后把被驱动表的记录在加载到内存匹配,这样周而复始,大大增加了10的次数。为了减少被驱动表的IO次数,就出现了Block Nested-Loop Join的方式。
不再是逐条获取驱动表的数据,而是一块一块的获取,引入了 join buffer缓冲区,将驱动表ioin相关的部分数据列(大小受join buffer的限制)缓存到join buffer中,然后全表扫描被驱动表,被驱动表的每一条记录一次性和joinbufer中的所有驱动表记录进行匹配(内存中操作),将简单嵌套循环中的多次比较合并成一次,降低了被驱动表的访问频率。

注意:
这里缓存的不只是关联表的列,select 后面的列也会缓存起来。在一个有N个join关联的sq中会分配N-1个join buffer。所以查询的时候尽量减少不必要的字段,可以让joinbuffer中可以存放更多的列。

## 这就是为什么常说的 SELECT * 避免使用。尽量写明要查找的具体字段,防止将表全部字段都进行缓存

 

 

参数设置:
block_nested_loop
通过 show variables like%optimizer_switch%'查看lock_nested_loop 状态。默认是开启的.

join_buffer_size
驱动表能不能一次加载完,要看join buffer能不能存储所有的数据,默认情况下 join_buffer_size=256k。

 join_bufer_size的最大值在32位系统可以申请4G,而在64位操做系统下可以申请大于4G的Join Bufer空间 (64位 Windows除外,其值会被截断为4GB并发出警告。

5.Join小结


1、整体效率比较: INLJ > BNLJ > SNLJ
2、永远用小结果集驱动大结果集(其本质就是减少外层循环的数据数量) (小的度量单位指的是 表行数*每行大小)

#straight_join 不让查询优化器破坏顺序,此时 t1是驱动表,t2为被驱动表
select t1.b,t2.* from t1 straight_join t2 on (t1.b=t2.b) where t2.id<=100; #推荐

 
select t1.b,t2.* from t2 straight_join t1 on (t1.b=t2.b) where t2.id<=100; #不推


3、为被驱动表匹配的条件增加索引(减少内层表的循环匹配次数)


4、增大join bufer size的大小 (一次缓存的数据越多,那么内层包的扫表次数就越少)

5、减少驱动表不必要的字段查询 (字段越少,join buffer 所缓存的数据就越多)

 6. Hash Join
从MySQL的8.0.20版本开始将废弃BNLJ,因为从MySQL8.0.18版本开始就加入了hash join默认都会使用hash join

Nested Loop                                                                                                                                                                                                  对于被连接的数据子集较小的情况,Nested Loop是个较好的选择。

Hash Join是做 大数据集连接 时的常用方式,优化器使用两个表中较小(相对较小)的表利用Join Key在内存中建立 散列表,然后扫描较大的表并探测散列表,找出与Hash表匹配的行。

 这种方式适用于较小的表完全可以放于内存中的情况,这样总成本就是访问两个表的成本之和。

 在表很大的情况下并不能完全放入内存,这时优化器会将它分割成 若千不同的分区,不能放入内存的部分就把该分区写入磁盘的临时段,此时要求有较大的临时段从而尽量提高I/O 的性能.


它能够很好的工作于没有索引的大表和并行查询的环境中,并提供最好的性能。大多数人都说它是Join的重型升降机。Hash Join只能应用于等值连接(如WHERE A.COL1  B.COL2),这是由Hash的特点决定的。

 

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/小舞很执着/article/detail/847821
推荐阅读
相关标签
  

闽ICP备14008679号