mysql中leftjoin多表查询优化
关于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 '部门名称',it培训咨询顾问话术
`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,
mysql语句多表查询`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` (
getversionex`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`,
`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,eclipse安装不了弹出一堆英文
`user`.lock_flag AS lock_flag,caption2ass
`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毫秒
listbox控件的作用
总结
1. 表设计: 主键尽量int类型的字段进⾏关联，减少数据库查询条件，因为varchar将会有⼀个转换的过程；
2. 关联字段最好创建索引；
3. 数据量⼩的表尽量在前

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。