MySql 优化实例：修改 cross join 方式为子查询方式，以求改变执行计划

**MySQL优化实例：修改 Cross Join 方式为子查询方式**

在 MySQL 中，Cross Join（也称为 Cartesian Product）是一种用于连接两个表的方法，它会将每一行都与另一个表中的所有行进行匹配，从而产生一个巨大的结果集。然而，这种方法通常会导致性能问题，因为它需要扫描整个表，并且可能会产生大量的数据。

在本文中，我们将展示如何修改 Cross Join 方式为子查询方式，以求改变执行计划，提高性能。

**原有 Cross Join 方式**

假设我们有两个表：`orders` 和 `customers`。我们想找出每个订单对应的客户信息。

sqlCREATE TABLE orders (
 id INT PRIMARY KEY,
 customer_id INT,
 order_date DATE);

CREATE TABLE customers (
 id INT PRIMARY KEY,
 name VARCHAR(255),
 email VARCHAR(255)
);

sqlSELECT o.id, c.name, c.emailFROM orders oCROSS JOIN customers c;

sqlSELECT id, name, emailFROM (
 SELECT o.id, c.name, c.email FROM orders o JOIN customers c ON o.customer_id = c.id) AS subquery;

sqlEXPLAIN SELECT id, name, emailFROM (
 SELECT o.id, c.name, c.email FROM orders o JOIN customers c ON o.customer_id = c.id) AS subquery;

+----+-------------+-------+--------+---------------+---------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+------+------+----------------+
|1 | PRIMARY | NULL | ALL | NULL | NULL | NULL | NULL |0 | NULL |
|2 | SUBQUERY | o | eq_ref | PRIMARY | PRIMARY |4 | c.id |10 | Using where; |
|3 | SUBQUERY | c | eq_ref | PRIMARY | PRIMARY |4 | o.id |5 | NULL |
+----+-------------+-------+--------+---------------+---------+---------+------+------+----------------+

EXPLAIN SELECT id, name, emailFROM orders oCROSS JOIN customers c;

+----+-------------+-------+--------+---------------+---------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+------+------+----------------+
|1 | SIMPLE | o | ALL | PRIMARY | NULL | NULL | NULL |10 | Using filesort |
|2 | SIMPLE | c | ALL | PRIMARY | NULL | NULL | NULL |5 | NULL |
+----+-------------+-------+--------+---------------+---------+---------+------+------+----------------+