我这里有两张表，一张用户表：100万条数据，一张部门表，1000条数据
初始化数据
用户表结构：有一个主键user_id，没有在其它字段加索引
CREATE TABLE `sys_user` (
`user_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
`dept_id` bigint(20) DEFAULT NULL COMMENT '部门ID',
`user_name` varchar(30) NOT NULL COMMENT '用户账号',
`nick_name` varchar(30) NOT NULL COMMENT '用户昵称',
`user_type` varchar(2) DEFAULT '00' COMMENT '用户类型（00系统用户）',
`email` varchar(50) DEFAULT '' COMMENT '用户邮箱',
`phonenumber` varchar(11) DEFAULT '' COMMENT '手机号码',
`sex` char(3) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '0' COMMENT '用户性别（0男 1女 2未知）',
`avatar` varchar(100) DEFAULT '' COMMENT '头像地址',
`password` varchar(100) DEFAULT '' COMMENT '密码',
`status` char(1) DEFAULT '0' COMMENT '帐号状态（0正常 1停用）',
`del_flag` char(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
`login_ip` varchar(128) DEFAULT '' COMMENT '最后登录IP',
`login_date` datetime DEFAULT NULL COMMENT '最后登录时间',
`create_by` varchar(64) DEFAULT '' COMMENT '创建者',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_by` varchar(64) DEFAULT '' COMMENT '更新者',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
`remark` varchar(500) DEFAULT NULL COMMENT '备注',
`a` bigint(20) DEFAULT NULL,
`auth_flag` bit(1) DEFAULT NULL,
`price` decimal(20,2) DEFAULT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1430742948 DEFAULT CHARSET=utf8 COMMENT='用户信息表';
部门表结构：有一个主键dept_id，没有在其它字段加索引
CREATE TABLE `sys_dept` (
`dept_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '部门id',
`parent_id` bigint(20) DEFAULT '0' COMMENT '父部门id',
`ancestors` varchar(50) DEFAULT '' COMMENT '祖级列表',
`dept_name` varchar(30) DEFAULT NULL,
`order_num` int(4) DEFAULT '0' COMMENT '显示顺序',
`leader` varchar(20) DEFAULT NULL COMMENT '负责人',
`phone` varchar(11) DEFAULT NULL COMMENT '联系电话',
`email` varchar(50) DEFAULT NULL COMMENT '邮箱',
`status` char(1) DEFAULT '0' COMMENT '部门状态（0正常 1停用）',
`del_flag` char(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
`create_by` varchar(64) DEFAULT '' COMMENT '创建者',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_by` varchar(64) DEFAULT '' COMMENT '更新者',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`dept_id`)
) ENGINE=InnoDB AUTO_INCREMENT=101014 DEFAULT CHARSET=utf8 COMMENT='部门表';
初始化数据：
创建了两个存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `ry-vue`.`GenerateUsers`(IN userCount INT)
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE userName VARCHAR(30);
DECLARE nickName VARCHAR(30);
DECLARE deptId BIGINT;
DECLARE userType VARCHAR(2);
DECLARE userEmail VARCHAR(50);
DECLARE phoneNumber VARCHAR(11);
DECLARE sex CHAR(3);
DECLARE avatarUrl VARCHAR(100);
DECLARE password VARCHAR(100);
DECLARE status CHAR(1);
DECLARE delFlag CHAR(1);
DECLARE loginIp VARCHAR(128);
DECLARE loginDate DATETIME;
DECLARE remark VARCHAR(500);
DECLARE authFlag BIT(1);
DECLARE price DECIMAL(20, 2);
WHILE i < userCount DO
SELECT dept_id INTO deptId FROM sys_dept ORDER BY RAND() LIMIT 1; -- Randomly select a department ID
SET userName = CONCAT('user', LPAD(i, 6, '0'));
SET nickName = CONCAT('Nickname', LPAD(i, 6, '0'));
SET userType = '00';
SET userEmail = CONCAT(SUBSTRING(MD5(RAND()), 1, 8), '@gmail.com');
SET phoneNumber = CONCAT('13', LPAD(RAND()*100000000, 8, '0'));
SET sex = CASE FLOOR(RAND()*3) WHEN 0 THEN '0' WHEN 1 THEN '1' ELSE '2' END; -- Randomly assign sex
SET avatarUrl = CONCAT('https://randomuser.me/api/portraits/men/', LPAD(FLOOR(RAND()*99)+1, 2, '0'), '.jpg'); -- Random avatar URL
SET `password` ='123456'; -- For simplicity, using SHA1 of a random number as password
SET status = IF(RAND() < 0.9, '0', '1'); -- 90% chance of being active
SET delFlag = '0';
SET loginIp = INET_NTOA(RAND()*4294967295); -- Random IP address
SET loginDate = DATE_SUB(NOW(), INTERVAL FLOOR(RAND()*365) DAY); -- Random login date within the last year
SET remark = CONCAT('Remark for user ', LPAD(i, 6, '0'));
SET authFlag = CAST(RAND()*1 AS UNSIGNED); -- Random auth flag
SET price = ROUND(RAND()*9999+1, 2); -- Random price between 1 and 9999.99
INSERT INTO sys_user (dept_id, user_name, nick_name, user_type, email, phonenumber, sex, avatar, password, status, del_flag, login_ip, login_date, remark, auth_flag, price)
VALUES (deptId, userName, nickName, userType, userEmail, phoneNumber, sex, avatarUrl, password, status, delFlag, loginIp, loginDate, remark, authFlag, price);
SET i = i + 1;
END WHILE;
END
CREATE DEFINER=`root`@`localhost` PROCEDURE `ry-vue`.`GenerateDepartments`(IN deptCount INT)
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE ancestorsStr VARCHAR(50);
DECLARE leaderName VARCHAR(20);
DECLARE phoneNumber VARCHAR(11);
DECLARE email VARCHAR(50);
DECLARE status CHAR(1);
DECLARE delFlag CHAR(1);
DECLARE createBy VARCHAR(64);
DECLARE createTime DATETIME;
DECLARE updateBy VARCHAR(64);
DECLARE updateTime DATETIME;
SET createBy = 'System';
SET createTime = NOW();
SET updateBy = createBy;
SET updateTime = createTime;
WHILE i < deptCount DO
SET ancestorsStr = '0';
SET leaderName = CONCAT('Leader', LPAD(i, 3, '0'));
SET phoneNumber = CONCAT('13', LPAD(RAND()*100000000, 8, '0'));
SET email = CONCAT(SUBSTRING(MD5(RAND()), 1, 8), '@example.com');
SET status = IF(RAND() < 0.9, '0', '1'); -- 90% chance of being active
SET delFlag = '0';
INSERT INTO sys_dept (ancestors, dept_name, leader, phone, email, status, del_flag, create_by, create_time, update_by, update_time)
VALUES (ancestorsStr, CONCAT('Dept', LPAD(i, 3, '0')), leaderName, phoneNumber, email, status, delFlag, createBy, createTime, updateBy, updateTime);
SET i = i + 1;
END WHILE;
END
查询
我的联表查询语句：
select
count(1)
from
sys_dept d
left join sys_user u on
d.dept_id = u.dept_id
where
u.del_flag = '0'
耗时：1.138s
使用explain查看执行过程：
id|select_type|table|partitions|type |possible_keys|key |key_len|ref |rows |filtered|Extra |
--+-----------+-----+----------+------+-------------+-------+-------+----------------+------+--------+-----------+
1|SIMPLE |u | |ALL | | | | |941080| 10.0|Using where|
1|SIMPLE |d | |eq_ref|PRIMARY |PRIMARY|8 |ry-vue.u.dept_id| 1| 100.0|Using index|
发现驱动表是sys_user，我们想要小表驱动大表，但是实际是大表驱动小表
第一次优化：
在sys_user表dept_id字段上加索引：
create index idx_dept_id on sys_user(dept_id)
再次执行sql:
select
count(1)
from
sys_dept d
left join sys_user u on
d.dept_id = u.dept_id
where
u.del_flag = '0'
耗时：1.245s
发现查询效率并没有提高
使用explain查看执行过程：
id|select_type|table|partitions|type |possible_keys|key |key_len|ref |rows |filtered|Extra |
--+-----------+-----+----------+------+-------------+-------+-------+----------------+------+--------+-----------+
1|SIMPLE |u | |ALL |idx_dept_id | | | |941080| 10.0|Using where|
1|SIMPLE |d | |eq_ref|PRIMARY |PRIMARY|8 |ry-vue.u.dept_id| 1| 100.0|Using index|
发现驱动表还是sys_user，并且我加的idx_dept_id索引再实际查询时也没有用到
第二次优化：
删掉第一次优化加的索引：idx_dept_id
drop index idx_dept_id on sys_user;
在sys_user表dept_id，del_flag字段上加联合索引：
create index idx_dept_id_del_flag on sys_user(dept_id,del_flag)
再次执行sql:
select
count(1)
from
sys_dept d
left join sys_user u on
d.dept_id = u.dept_id
where
u.del_flag = '0'
耗时：200ms
查询效率明显提高
使用explain查看执行过程：
id|select_type|table|partitions|type |possible_keys |key |key_len|ref |rows|filtered|Extra |
--+-----------+-----+----------+-----+--------------------+--------------------+-------+----------------------+----+--------+-----------+
1|SIMPLE |d | |index|PRIMARY |PRIMARY |8 | |1019| 100.0|Using index|
1|SIMPLE |u | |ref |idx_dept_id_del_flag|idx_dept_id_del_flag|13 |ry-vue.d.dept_id,const| 922| 100.0|Using index|
发现驱动表是sys_dept，符合小表驱动大表的规则，并且使用了覆盖索引
