mysql nested loop join怎么用

这种是 MySQL 里最简单、最容易理解的表关联算法。

比如，拿语句 select * from p1 join p2 using(r1) 来说，

先从表 p1 里拿出来一条记录 ROW1，完了再用 ROW1 遍历表 p2 里的每一条记录，并且字段 r1 来做匹配是否相同，以便输出；再次循环刚才的过程，直到两表的记录数对比完成为止。

那看下实际 SQL 的执行计划，

mysql>explain format=json select * from p1 inner join p2 as b using(r1)\G*************************** 1. row ***************************EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "1003179606.87" }, "nested_loop": [ { "table": { "table_name": "b", "access_type": "ALL", "rows_examined_per_scan": 1000, "rows_produced_per_join": 1000, "filtered": "100.00", "cost_info": { "read_cost": "1.00", "eval_cost": "100.00", "prefix_cost": "101.00", "data_read_per_join": "15K" }, "used_columns": [ "id", "r1", "r2" ] } }, { "table": { "table_name": "p1", "access_type": "ALL", "rows_examined_per_scan": 9979810, "rows_produced_per_join": 997981014, "filtered": "10.00", "cost_info": { "read_cost": "5198505.87", "eval_cost": "99798101.49", "prefix_cost": "1003179606.87", "data_read_per_join": "14G" }, "used_columns": [ "id", "r1", "r2" ], "attached_condition": "(`ytt_new`.`p1`.`r1` = `ytt_new`.`b`.`r1`)" } } ] }}1 row in set, 1 warning (0.00 sec)

从上面的执行计划来看，表 p2 为第一张表（驱动表或者叫外表），第二张表为 p1，那 p2 需要遍历的记录数为 1000，同时 p1 需要遍历的记录数大概 1000W 条，那这条 SQL 要执行完成，就得对表 p1（内表）匹配 1000 次，对应的 read_cost 为 5198505.87。那如何才能减少表 p1 的匹配次数呢？那这个时候 JOIN BUFFER 就派上用处了

在客户的运营系统中，当客户执行某个条件的订单查询时，接口响应超时，同时服务器的cpu利用率高达100%

备注

上述俩种表中的数据量分别是：4万 和七万

对order_bill表建立索引，避免全表扫描

可以看到此时，已经使用了索引，同时mysql也不再使用Block Nested Loop

对order表也建立索引

可以看到，此时对order表的查询也不再走全面扫描，而是使用了索引

sql如下

mysql官方文档

stackoverflow

stackoverflow

博客

---