mysql优化原则之小表驱动大表

本篇博文的mysql版本：5.7

准备相关表和测试数据

-- 部门表
CREATE TABLE `department`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `deptName` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `address` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- 部门表测试数据（共5条）
INSERT INTO `department` VALUES (1, '研发部(RD)', '2层');
INSERT INTO `department` VALUES (2, '人事部(HR)', '3层');
INSERT INTO `department` VALUES (3, '市场部(MK)', '4层');
INSERT INTO `department` VALUES (4, '后勤部(MIS)', '5层');
INSERT INTO `department` VALUES (5, '财务部(FD)', '6层');


-- 员工表（共8条）
CREATE TABLE `employee`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `dep_id` int(11) NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  `salary` decimal(10, 2) NULL DEFAULT NULL,
  `cus_id` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_name_dep_age`(`name`, `dep_id`, `age`) USING BTREE,
  INDEX `idx_salary`(`salary`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- 员工表测试数据
INSERT INTO `employee` VALUES (1, '鲁班', 1, 10, 1000.00, 1);
INSERT INTO `employee` VALUES (2, '后裔', 1, 20, 2000.00, 1);
INSERT INTO `employee` VALUES (3, '孙尚香', 1, 20, 2500.00, 1);
INSERT INTO `employee` VALUES (4, '凯', 4, 20, 3000.00, 1);
INSERT INTO `employee` VALUES (5, '露娜', 4, 40, 3500.00, 2);
INSERT INTO `employee` VALUES (6, '李白', 5, 20, 5000.00, 1);
INSERT INTO `employee` VALUES (7, '韩信', 3, 50, 5000.00, 1);
INSERT INTO `employee` VALUES (8, '蔡文姬', 30, 35, 4000.00, 1);

什么是小表驱动大表？

所谓的小表驱动大表：即小表的数据集驱动大表的数据集，在本文中也就是让 部门表（小表）来驱动 员工表（大表）。那我们的sql语句可以这么写：

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

-- 该条sql语句的执行顺序是：先查询department 然后外层查询employee的时候 是根据department查询结果进行查询的，即完成了小表驱动大表。

为什么要遵循小表驱动大表的这种原则呢？

这里就是mysql底层实现的机制问题了。上面这条sql语句，相当于我们在php中写了如下2个循环语句（嵌套循环）：

for($i = 0; $i < 5; $i++)
{
    for($j = 0; $j < 8; $j++)
    {
        // do some thing
    }
}

//上面这样写(小表驱动大表) 相当于做了5次连接，然后每一次连接都在里面做了8次操作。


//如果我们将上面代码反过来看一下,如下(对应的sql语句也就脱离了 小表驱动大表了)：
for($i = 0; $i < 8; $i++)
{
    for($j = 0; $j < 5; $j++)
    {
        // do some thing
    }
}

//上面这样写(脱离了小表驱动大表的原则) 相当于做了8次连接，然后每一次连接都在里面做了5次操作。

尾声

我们知道mysql连接这个操作是非常耗时的，所以说我们写sql语句的时候，要尽量遵循小表驱动大表的原则。

Fighting！

声明：禁止任何非法用途使用，凡因违规使用而引起的任何法律纠纷，本站概不负责。