一个简单的手艺人

0%

MySQL小表驱动大表

发表于 更新于 分类于 Disqus：

前言

我们查询小表的数据肯定是比查询大表的数据来的快，所以我们可以用查询小表得到的结果集，作为查询条件去查询大表，这样提前的一个结果范围，能够让我们更快的获取数据。

准备基础数据

我们准备一个部门表，在实际中一个公司的部门肯定是比人员少，如果部门比人员还多的公司，那么请大家看清楚再入坑，不然会有很多意想不到的惊喜。

1
-- 创建部门表
2
-- 生产环境不要用DROP! 生产环境不要用DROP! 生产环境不要用DROP!
3
DROP TABLE IF EXISTS `department`; 
4
CREATE TABLE IF NOT EXISTS `department` (
5
    `id` int(11) NOT NULL AUTO_INCREMENT,
6
    `dept_name` varchar(200) DEFAULT NULL,
7
    `address` varchar(200) DEFAULT NULL,
8
    PRIMARY KEY(`id`)
9
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
10
11
-- 插入测试数据
12
INSERT INTO `department` (`dept_name`, `address`) VALUES ('研发部(RD)', '2层');
13
INSERT INTO `department` (`dept_name`, `address`) VALUES ('人事部(HR)', '3层');
14
INSERT INTO `department` (`dept_name`, `address`) VALUES ('市场部(MK)', '4层');
15
INSERT INTO `department` (`dept_name`, `address`) VALUES ('后勤部(MIS)', '5层');
16
INSERT INTO `department` (`dept_name`, `address`) VALUES ('财务部(FD)', '6层');
17
18
-- 创建雇员表
19
DROP TABLE IF EXISTS `testemployee`;
20
CREATE TABLE `testemployee` ( 
21
    `id` int(11) NOT NULL AUTO_INCREMENT,
22
    `name` VARCHAR(20) DEFAULT NULL,
23
    `dep_id` int(11) DEFAULT NULL,
24
    `age` int(11) DEFAULT NULL,
25
    `salary` DECIMAL(10, 2) DEFAULT NULL,
26
    `cus_id` int(11) DEFAULT NULL,
27
    PRIMARY key(`id`)
28
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4;
29
30
-- 随机字符串函数
31
delimiter $$
32
DROP FUNCTION IF EXISTS `rand_str`;
33
CREATE FUNCTION rand_str(n int) RETURNS varchar(255)
34
BEGIN
35
	declare str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
36
	declare i int default 0;
37
	declare res_str varchar(255) default 'php_';
38
39
	while i < n do
40
		SET res_str = CONCAT(res_str, SUBSTR(str, FLOOR(1 + RAND()*52), 1));
41
		SET i = i + 1;
42
	end while;
43
44
	return res_str;
45
END$$
46
47
-- 存储过程
48
DROP PROCEDURE IF EXISTS `insert_emp`;
49
CREATE PROCEDURE `insert_emp`(in max_num int)
50
BEGIN
51
	DECLARE i INT DEFAULT 0;
52
	SET autocommit = 0;
53
	repeat
54
		set i = i + 1;
55
		insert into `testemployee` (name, dep_id, age, salary, cus_id) VALUES (rand_str(5), floor(rand()*(6-1) + 1), floor(20 + RAND()*10), floor(2000 + RAND()*10), FLOOR(1 + RAND()*10));
56
		UNTIL i = max_num
57
	end repeat;
58
	
59
	commit;
60
END$$
61
62
delimiter ;
63
64
-- 创建数据
65
CALL insert_emp(100000);

小表驱动大表

例子

我们先用直接操作，查询出 testemployee 的数据研发部和人事部的信息。

1
-- Time: 0.5s
2
SELECT * FROM `testemployee` WHERE dep_id = 2 OR dep_id = 3;

这里因为使用了 OR 即使是创建了索引，也会导致索引失效，全表查询。速度相对而言比较慢。

既然使用到了 OR 去做查询，testemployee 表全表查询肯定是比 department 表全表查询慢，所以我们可以使用小的数据表查询结果驱动大的数据集。

1
-- Time: 0.033s
2
SELECT * FROM `testemployee` WHERE dep_id IN (SELECT id FROM department WHERE dept_name='研发部(RD)' OR dept_name='人事部(HR)');

in与exists

其实两个可以互相替换，但是思想却大不相同。上面的语句我们看看如何改写

1
SELECT * FROM `testemployee` WHERE EXISTS (SELECT 2 FROM department WHERE dept_name='研发部(RD)' OR dept_name='人事部(HR)');

他和IN的区别在于，使用大表来驱动小表，子语句中是小表，至于什么时候使用，不言而喻了吧。

总结

这里的数据库表的例子可能不太恰当，但是主要是想说明一个观点。就是小表得到结果的数据肯定比大表快，通过小表我们缩小了确定数据的范围，而不用让大表逐条去比对数据。当然我们该做索引的地方还是要做索引。这个跑不掉。