mysql驱动表优化_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 

{

for($j = 0; $j 

{

// do some thing

}

}

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

//如果我们将上面代码反过来看一下,如下(对应的sql语句也就脱离了 小表驱动大表了)：

for($i = 0; $i 

{

for($j = 0; $j 

{

// do some thing

}

}

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

尾声

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

Fighting！

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