大厂面试-百度二面-关联查询时为什么建议小表驱动大表？

题目描述

假如Mysql数据库有两张表，一张表A，里面有10W条数据，一张表B，里面有100W条数据，此时需要你用左连接关联这两个表查询。

问题

把哪个表放在左边比较好？为什么呢？比如讲下比如表A在左边会扫描多少行？表B在左边会扫描多少行？具体原理讲讲

答：小表放在左边比较好，小表放在左边扫描的行数会少。

动手实验&实践是检验真理的唯一标准

SQL语句

-- 创建表A
CREATE TABLE table_a (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    age INT
);

-- 创建表B
CREATE TABLE table_b (
    id INT PRIMARY KEY,
    full_name VARCHAR(255),
    weight INT
);

-- 初始化10W条数据的存储过程
DELIMITER //
CREATE PROCEDURE init_10w_data()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 100000 DO
        INSERT INTO table_a (id, name, age) VALUES (i, CONCAT('Value', i), i*2);
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

-- 初始化100W条数据的存储过程
DELIMITER //
CREATE PROCEDURE init_100w_data()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 1000000 DO
        INSERT INTO table_b (id, full_name, weight) VALUES (i, CONCAT('Value', i), i*2);
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

由于创建100W条数据太慢，改成10W条和100条

大表驱动小表

SELECT * FROM table_a LEFT JOIN table_b ON table_a.id = table_b.id;

查询结果10W，查询时间0.260s。（id=主键时）

EXPLAIN（id=主键时）

• 扫描：100031行*1行
• 查询时间：0.260s

EXPLAIN（id不等于主键时）

• 扫描：100264行*100行
• 查询时间：0.497s

小表驱动大表

SELECT * FROM table_b LEFT JOIN table_a ON table_a.id = table_b.id;

查询结果100，查询时间0.060s。（id=主键时）

EXPLAIN（id=主键时）

• 扫描：100行*1行
• 查询时间：0.060s

EXPLAIN（id不等于主键时）

• 扫描：100行*100264行
• 查询时间：0.459s

结论

1. 如果不走索引，不管大表驱动小表还是小表驱动大表都一样，都是全扫描。
2. 如果走索引，假如全部扫描大表m，全部扫描小表n，则大表驱动小表大概是mlgn，小表驱动大表大概是nlgm。lg可以大概理解成走索引的扫描次数（可能不准），所以左边的数量越小扫描次数越少，由此一般都建议小表驱动大表，可以有效地利用索引。
3. 如果需要相等的数据的时候，小表驱动大表可以减少返回的数据，减少内存消耗，提高查询性能。