大表驱动小表

定制下测试数据

DROP TABLE IF EXISTS employee;
CREATE TABLE IF NOT EXISTS employee (
    id INT PRIMARY KEY,
    name VARCHAR(40),
    dept_id INT
);

DROP TABLE IF EXISTS department;
CREATE TABLE IF NOT EXISTS department (
    id INT PRIMARY KEY,
    name VARCHAR(40)
);

delimiter $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255) deterministic
BEGIN
    DECLARE chars_str VARCHAR(100) DEFAULT 'qwertyuiopasdfghjklzxcvbnm';
    DECLARE return_str VARCHAR(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i<n DO
        SET return_str = concat(return_str, substring(chars_str, floor(1 + rand() * 52), 1));
        SET i = i + 1;
    END WHILE;
    RETURN return_str;
END $$

delimiter $$
CREATE FUNCTION rand_num() RETURNS INT(5) deterministic
BEGIN
    DECLARE i INT DEFAULT 0;
    SET i = floor(floor(rand() * 100000) + 1);
    RETURN i;
END $$

delimiter $$
CREATE PROCEDURE insert_employee(IN START INT(10), IN max_num INT(10))
BEGIN
    DECLARE i INT DEFAULT 0;
    /*把autocommit设置成0*/
    SET autocommit = 0;
    REPEAT
        SET i = i + 1;
        INSERT INTO employee(id, name, dept_id) VALUES((START + i), rand_string(30), rand_num());
    UNTIL i = max_num END REPEAT;
    COMMIT;
END $$

delimiter $$
CREATE PROCEDURE insert_dept(IN START INT(10), IN max_num INT(10))
BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
        SET i = i + 1;
        INSERT INTO department(id, name) VALUES((START + i), rand_string(10));
        UNTIL i = max_num END REPEAT;
    COMMIT;
END $$

#department插入1000条数据
CALL insert_dept(0, 1000);
#employee插入1000000条数据
CALL insert_employee(0, 1000000);

department表部门表1w条，为小表，employee100w条记录，大表

SELECT * FROM employee WHERE dept_id IN (SELECT id FROM department);

SELECT * FROM employee e WHERE EXISTS (SELECT 1 FROM department d WHERE e.dept_id = d.id);

分析.png
查询结果.png

建立了索引，可以看到 in的实际时间短一些，但是短的有限，多次查询取平均值和exists相当。

• in：mysql的执行顺序是先执行子查询，然后执行主查询，用子查询的结果按条匹配主查询。
  所以应该由 #大表# a in (#小表# b )
• exists：mysql的执行顺序是先执行主查询，将主查询的数据放在子查询中做条件验证。
  所以应该由 #小表# a exists(#大表# b )