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！

聲明：禁止任何非法用途使用，凡因違規使用而引起的任何法律糾紛，本站概不負責。