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!



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

扫码支持
扫码打赏,你说多少就多少

打开支付宝扫一扫,即可进行扫码打赏哦

精彩评论

全部回复12人评论7,777人参与