MySQL JOIN

SQL中JOIN的作用

SQL中的join可以根据某些条件把指定的表给结合起来并将数据返回给客户端

SQL中JOIN的连接方式

inner join 内连接
left join 左连接
right join 右连接
full join 全连接

MySQL中JOIN执行过程分析

在执行join语句的时候必然要有一个比较的过程，逐条比较两个表的语句是比较慢的，可以把两个表中数据依次读进一个内存块中, 以MySQL的InnoDB引擎为例，使用以下语句可以查到相关的内存区域show variables like '%buffer%'

  1. mysql> show variables like '%buffer%';
  2. +-------------------------------------+----------------+
  3. | Variable_name                       | Value          |
  4. +-------------------------------------+----------------+
  5. | bulk_insert_buffer_size             | 8388608        |
  6. | innodb_buffer_pool_chunk_size       | 134217728      |
  7. | innodb_buffer_pool_dump_at_shutdown | ON             |
  8. | innodb_buffer_pool_dump_now         | OFF            |
  9. | innodb_buffer_pool_dump_pct         | 25             |
  10. | innodb_buffer_pool_filename         | ib_buffer_pool |
  11. | innodb_buffer_pool_in_core_file     | ON             |
  12. | innodb_buffer_pool_instances        | 1              |
  13. | innodb_buffer_pool_load_abort       | OFF            |
  14. | innodb_buffer_pool_load_at_startup  | ON             |
  15. | innodb_buffer_pool_load_now         | OFF            |
  16. | innodb_buffer_pool_size             | 134217728      |
  17. | innodb_change_buffer_max_size       | 25             |
  18. | innodb_change_buffering             | all            |
  19. | innodb_log_buffer_size              | 16777216       |
  20. | innodb_sort_buffer_size             | 1048576        |
  21. | join_buffer_size                    | 262144         |
  22. | key_buffer_size                     | 8388608        |
  23. | myisam_sort_buffer_size             | 8388608        |
  24. | net_buffer_length                   | 16384          |
  25. | preload_buffer_size                 | 32768          |
  26. | read_buffer_size                    | 131072         |
  27. | read_rnd_buffer_size                | 262144         |
  28. | sort_buffer_size                    | 262144         |
  29. | sql_buffer_result                   | OFF            |
  30. +-------------------------------------+----------------+
  31. 25 rows in set, 1 warning (0.02 sec)
  33. mysql>

image.png
通过以上查询，可以看到join的缓冲区大小设置，所以可以得到JOIN缓冲是影响JOIN执行效果的因素。如果小规模的数据量可以直接加载到缓冲区进行对比操作，但是如果上线的项目一般数据量不会小，这样一次性加载进缓冲区，不利于调节，要么使用大量的内存作为SQL-JOIN的缓冲区，要么进行SQL优化分析，减少数据的加载又能获取需要的内容。

join_buffer的作用：在扫描过程中，数据库会选择一个表把他要返回以及需要进行和其他表进行比较的数据放进join_buffer

大部分数据库中的数据最终要保存到硬盘上,并且以文件的形式进行存储。
以MySQL的InnoDB引擎为例

  • InnoDB以页(page)为基本的IO单位，每个页的大小为16KB
  • InnoDB会为每个表创建用于存储数据的.ibd文件

数据库表对应的存储文件如下

  1. mysql> use competition;
  2. Database changed
  3. mysql> show tables;
  4. +-----------------------+
  5. | Tables_in_competition |
  6. +-----------------------+
  7. | c_advice              |
  8. | c_competition         |
  9. | c_file                |
  10. | c_group               |
  11. | c_honor               |
  12. | c_paper               |
  13. | c_role                |
  14. | c_score               |
  15. | c_society             |
  16. | c_user                |
  17. | campus_info           |
  18. | city_area             |
  19. | company_info          |
  20. | competition_stage     |
  21. | competition_type      |
  22. | join_competition      |
  23. | school_info           |
  24. | user_group            |
  25. +-----------------------+
  26. 18 rows in set (0.05 sec)
  28. mysql>

image.png
image.png
这意味着有多少表JOIN要连接就需要读多少个文件，虽然可以利用索引，但还是免不了频繁的移动硬盘的磁头，频繁的移动磁头会影响性能。

SQL中JOIN的优化

1.数据规模较小 全部加载进内存
2.数据规模较大

  • 可以通过增加索引来优化join语句的执行速度
  • 可以通过冗余信息来减少join的次数
  • 尽量减少表连接的次数，一个SQL语句表连接的次数不要超过5次

    JOIN算法

    无索引-循环嵌套
    有索引-使用索引优化

    无索引的循环嵌套Nested Loop Join（太慢已废弃）

    ```plsql sql = ‘selcet * from outerTable left join innerTable on outerTable.id = innerTable.id’ for(outerRow in outerTable){ for(innerRow in innerTable){
    1. if(compare(outerRow, innerRow){
    2.  resultRow = combine(outerRow, innerRow)
    3.  sendToReplyBuffer(resultRow)
    } } }
  1. 嵌套循环，每次只读取表中的一行数据，也就是说如果outerTable有10万行数据, innerTable有100行数据，需要读取10000000次(假设这两个表的文件没有被操作系统给缓存到内存, 称之为冷数据表)
  2. <a name="zTXDj"></a>
  3. ## Block nested loop
  4. ```plsql
  5. byte[] joinBuffer = new byte[joinBufferSize]
  6. for(;fill(joinBuffer, outerTable) != EOF;){
  7.     for(innerRow in innerTable){
  8.       for(outerRow in joinBuffer){
  9.         resultRow = combine(outeRow, innerRow)
  10.       sendToReplyBuffer(resultRow)
  11.     }
  12.   }
  13. }

Block 块，也就是说每次都会取一块数据到内存以减少I/O的开销，当没有索引可以使用的时候，MySQL InnoDB 就会自动使用Block nested loop算法。

MySQL中JOIN的使用分析

  1. 对于MySQL，不推荐使用子查询和join是因为本身join的效率就是硬伤，一旦数据量很大效率就很难保证，强烈推荐分别根据索引单表取数据，然后在程序里面做join，merge数据。
  2. 子查询就更不推荐用了，效率太差，执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响，这里多了一个创建和销毁临时表的过程。
  3. 如果是JOIN的话，它是走嵌套查询的。小表驱动大表，且通过索引字段进行关联。如果表记录比较少的话，还是OK的。大的话业务逻辑中可以控制处理。
  4. 数据库是最底层的，瓶颈往往是数据库。建议数据库只是作为数据store的工具，而不要添加业务上去。

    1、应用层关联的优势

    让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。如果关联中的某个表发生了变化，那么就无法使用查询缓存了，而拆分后，如果某个表很少改变，那么基于该表的查询就可以重复利用查询缓存结果了。
  • 将查询分解后，执行单个查询可以减少锁的竞争。
  • 在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
  • 查询本身效率也可能会有所提升。查询id集的时候，使用IN（）代替关联查询，可以让MySQL按照ID顺序进行查询，这可能比随机的关联要更高效。
  • 可以减少冗余记录的查询。在应用层做关联查询，意味着对于某条记录应用只需要查询一次，而在数据库中做关联查询，则可能需要重复地访问一部分数据。从这点看，这样的重构还可能会减少网络和内存的消艳。

  • 更进一步，这样做相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套循环关联。某些场景哈希关联的效率要高很多。

    2、应用层关联的使用场景

  • 当应用能够方便地缓存单个查询的结果的时候

  • 当可以将数据分布到不同的MySQL服务器上的时候
  • 当能够使用IN（）的方式代替关联查询的时候
  • 并发场景多，DB查询频繁，需要分库分表

    3、不推荐使用join的原因

    1.DB承担的业务压力大，能减少负担就减少。当表处于百万级别后，join导致性能下降；
    2.分布式的分库分表。这种时候是不建议跨库join的。目前mysql的分布式中间件，跨库join表现不良。
    3.修改表的schema，单表查询的修改比较容易，join写的sql语句要修改，不容易发现，成本比较大，当系统比较大时，不好维护。

    4、不使用join的解决方案

    在业务层，单表查询出数据后，作为条件给下一个单表查询。也就是子查询。会担心子查询出来的结果集太多。mysql对in的数量没有限制，但是mysql限制整条sql语句的大小。
    通过调整参数max_allowed_packet ，可以修改一条sql的最大值。建议在业务上做好处理，限制一次查询出来的结果集是能接受的。

    5、join查询的优势

    关联查询的好处是可以做分页，可以用副表的字段做查询条件，在查询的时候，将副表匹配到的字段作为结果集，用主表去in它。
    但是问题来了，如果匹配到的数据量太大就不行了，也会导致返回的分页记录跟实际的不一样，解决的方法可以交给前端，一次性查询，让前端分批显示就可以了，这种解决方案的前提是数据量不太，因为sql本身长度有限。