mysql中left join多表查询优化

最近碰到一个sql超级慢的案例示例如下

建表语句如下

CREATE TABLE `sys_dept` (
  `dept_id` int(10) NOT NULL,
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '部门名称',
  `sort` int(11) DEFAULT NULL COMMENT '排序',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `del_flag` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '0' COMMENT '是否删除  -1：已删除  0：正常',
  `parent_id` int(10) DEFAULT '0' COMMENT '父id',
  `tenant_id` int(11) DEFAULT '1',
  PRIMARY KEY (`dept_id`) USING BTREE,
  KEY `index_dept_name` (`name`) USING BTREE,
  KEY `index_parent` (`parent_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='部门管理';
CREATE TABLE `sys_user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `username` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '用户名',
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '密码',
  `mail` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '邮箱',
  `cn` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'cn',
  `distinguished_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '辨别名称',
  `salt` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '随机盐',
  `name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '姓名',
  `phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '手机号',
  `avatar` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '头像',
  `dept_id` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '部门ID',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `lock_flag` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT '0' COMMENT '0-正常，9-锁定',
  `del_flag` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT '0' COMMENT '0-正常，1-删除',
  `wx_openid` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '微信openid',
  `qq_openid` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'QQ openid',
  `tenant_id` int(11) DEFAULT '1' COMMENT '所属租户',
  PRIMARY KEY (`user_id`) USING BTREE,
  UNIQUE KEY `user_idx1_username` (`username`) USING BTREE,
  KEY `user_wx_openid` (`wx_openid`) USING BTREE,
  KEY `user_qq_openid` (`qq_openid`) USING BTREE,
  KEY `12312` (`dept_id`) USING BTREE,
  KEY `21322` (`user_id`,`username`,`dept_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=37549810 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='用户表';

我们对这两张表中,表一月2000条数据,表二8000条数据执行sql如下：

SELECT
		`user`.user_id,
		`user`.username,
		`user`.name,
		`user`.`password`,
		`user`.salt,
		`user`.distinguished_name,
		`user`.cn,
		`user`.mail,
		`user`.phone,
		`user`.avatar,
		`user`.wx_openid,
		`user`.qq_openid,
		`user`.dept_id AS deptId,
		`user`.create_time AS ucreate_time,
		`user`.update_time AS uupdate_time,
		`user`.del_flag AS udel_flag,
		`user`.lock_flag AS lock_flag,
		`user`.tenant_id AS tenantId ,
		d.name
		FROM
		sys_user AS `user`
		left join sys_dept d  
		on `user`.dept_id=d.dept_id
		ORDER BY `user`.create_time DESC

执行时间约4秒中,速度太慢了,体验太差

下面explain该sql语句如下图

也就是吧关联字段由原来的varchar改为int ,简直不是一个量级的,速度达到了17毫秒
总结

1. 表设计: 主键尽量int类型的字段进行关联,减少数据库查询条件,因为varchar将会有一个转换的过程；
2. 关联字段最好创建索引；
3. 数据量小的表尽量在前