关于MYSQL 中left join 的sql优化的问题
最近碰到一个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毫秒
总结
- 表设计: 主键尽量int类型的字段进行关联,减少数据库查询条件,因为varchar将会有一个转换的过程;
- 关联字段最好创建索引;
- 数据量小的表尽量在前