mysql 经典面试题以及优化过程
程序员文章站
2022-03-24 09:00:36
CREATE TABLE `t_dept` (`id` INT(11) NOT NULL AUTO_INCREMENT,`deptName` VARCHAR(30) DEFAULT NULL,`address` VARCHAR(40) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;CREATE TABLE `t_emp` (`id` INT(11) NOT NULL A....
CREATE TABLE `t_dept` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`deptName` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `t_emp` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`deptId` INT(11) DEFAULT NULL,
empno int not null,
PRIMARY KEY (`id`),
KEY `idx_dept_id` (`deptId`)
#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO t_dept(deptName,address) VALUES('华山','华山');
INSERT INTO t_dept(deptName,address) VALUES('丐帮','洛阳');
INSERT INTO t_dept(deptName,address) VALUES('峨眉','峨眉山');
INSERT INTO t_dept(deptName,address) VALUES('武当','武当山');
INSERT INTO t_dept(deptName,address) VALUES('明教','光明顶');
INSERT INTO t_dept(deptName,address) VALUES('少林','少林寺');
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('风清扬',90,1,100001);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('岳不群',50,1,100002);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('令狐冲',24,1,100003);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('洪七公',70,2,100004);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('乔峰',35,2,100005);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('灭绝师太',70,3,100006);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('周芷若',20,3,100007);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张三丰',100,4,100008);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张无忌',25,5,100009);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('韦小宝',18,null,100010);
ALTER TABLE `t_dept` add CEO INT(11) ;
update t_dept set CEO=2 where id=1;
update t_dept set CEO=4 where id=2;
update t_dept set CEO=6 where id=3;
update t_dept set CEO=8 where id=4;
update t_dept set CEO=9 where id=5;
下面的emp 和 dept表是用存储过程向 emp表添加了50w条数据,dept添加了10w条数据
1),列出自己的掌门比自己年龄小的人员
select e.name,e.age,e1.name,e1.age from t_emp e
left join t_dept d on e.deptId = d.id
left join t_emp e1 on d.ceo =e1.id
where e.age > e1.age;
换成大表explain 优化查询
explain select e.name,e.age,e1.name,e1.age from emp e
left join dept d on e.deptId = d.id
left join emp e1 on d.ceo =e1.id
where e.age > e1.age;
2),列出所有年龄低于自己门派平均年龄的人员
-- 求出所有门派平均年龄
select deptId,avg(age) ag from t_emp group by deptId;
select e.name,e.age,e1.ag,e1.age from t_emp e inner join (select deptId,avg(age) ag,age from t_emp where deptId is not null group by deptId) e1 on e.deptId=e1.deptId where e.age < e1.age;
优化:
2-1), 执行explain
explain select SQL_NO_CACHE e.name,e.age,e1.ag,e1.age from emp e
inner join (select deptId,avg(age) ag,age from emp where deptId is not null group by deptId) e1
on e.deptId=e1.deptId
where e.age < e1.age;
2-2), 为dept 表的 group by 后面字段增加索引
create index idx_emp_deptId on emp(deptId);
2-3),为emp表的 deptId 建立索引和 age有了范围查询索引也建立索引
create index idx_emp_deptIdAge on emp(deptId,age);
2-4),再次执行explain ,优化完成
3),列出至少有2个年龄大于40岁的成员的门派(少林寺有南少林和北少林索引分组时应该注意)
select d.deptName,d.id,count(1) from t_emp e
inner join t_dept d
on e.deptId = d.id
where e.age>40
group by d.deptName,d.id
having count(1) >= 2 ;
优化:
3-1),explain 查看 使用 STRAIGHT_JOIN
explain
select d.deptName,d.id,count(1) from dept d
STRAIGHT_JOIN emp e
on e.deptId = d.id
where e.age>40
group by d.deptName,d.id
having count(1) >= 2 ;
STRAIGHT_JOIN 什么时候可以用?
-
1明确STRAIGHT_JOIN的概念(指定谁是驱动表谁是被驱动表),例如 a表 STRAIGHT_JOIN b表 这里 a表是驱动表 b表是北驱动表,注意 小表驱动大表
-
明确前后俩个表的数量级关系
3-2), 给 dept表的 再给 group by 字段建立索引,因为id 为主键索引不用添加索引
create index idx_dept_deptName on dept(deptName);
3-3), 给emp表的deptId 和 age 也添加索引
create index idx_dept_deptIdAge on emp(deptId,age);
3-4),执行 explain 语句 优化完成
4),至少有俩个非掌门人成员的门派
select d.deptName,d.id from t_emp e
inner join dept d1 on e.deptId = d1.id
left join t_dept d on e.id = d.ceo
where d.id is null
group by d.deptName,d.id
having count(1)>=2
优化:
4-1), explain 使用 STRAIGHT_JOIN 指定那个表为驱动表
explain select d.deptName,d.id from dept d1
STRAIGHT_JOIN emp e on e.deptId = d1.id
left join dept d on e.id = d.ceo
where d.id is null
group by d.deptName,d.id
having count(1)>=2
4-2),为 dept 表添加添加索引
create index idx_dept_deptName on dept(deptName);
create index idx_dept_ceo on dept(ceo);
4-3),为 emp表添加索引
create index idx_emp_deptId on emp(deptId);
4-4),再次使用 explain 执行sql语句,优化完成
5),列出全部人员,并增加一列备注为 是否为 掌门人 如果是掌门人显示是 否则 否 条件是
#方式1
select e.name,if(d.id is null ,'否','是') 是否为掌门人
from t_emp e
left join t_dept d
on e.id=d.ceo;
#方式2
select
e.name,
case when d.id is null then '否' else '是' end 是否为掌门人
from t_emp e
left join t_dept d
on
e.id=d.ceo;
6),列出全部门派,增加一列备注 老鸟or菜鸟 门派平均年龄大于50显示老鸟否则菜鸟
select d.deptName, if(avg(e.age)>50,'老鸟','菜鸟') 老鸟or菜鸟 from t_dept d
inner join t_emp e
on d.id=e.deptId
group by d.deptName,d.id
7),显示每个门派年龄最大的人
select e.name,e.age, max(age) mx from t_emp e
inner join t_dept d
on e.deptId=d.id
group by d.deptName,d.id
having mx
8),显示每个门派年龄第二大的人(sql编程)
set @rank =0; #定义变量 -排名
set @last_deptId=0; #下一个部门id
set @last_age=0; #定义年龄变量
select e.name,e.age,e.deptId from (
select e.*,
if(
@last_deptId = deptId,
if(@last_age = age,@rank,@rank:=@rank+1),
@rank:=1) as rk,
@last_deptId:=deptId as last_deptId,
@last_age:=age as last_age
from t_emp e order by e.deptId,e.age desc) e
where e.rk =2;
本文地址:https://blog.csdn.net/qq_40646143/article/details/108993956
上一篇: 小程序自定义圆形进度条
下一篇: 如何用sql 计算每一天的xxx率问题
推荐阅读
-
MySQL查询语句过程和EXPLAIN语句基本概念及其优化
-
MySQL中(JOIN/ORDER BY)语句的查询过程及优化方法
-
CentOS7.x卸载与安装MySQL5.7的操作过程以及编码格式的修改
-
MySQL分页Limit的优化过程实战
-
详解MySQL数据库优化的八种方式(经典必看)
-
ES 18 - (底层原理) Elasticsearch写入索引数据的过程 以及优化写入过程
-
淘宝卖家在优化店铺搜索流量过程中,比较常见的误区以及应对方法
-
详解MySQL索引原理以及优化
-
【Java高频面试题】--类的初始化过程以及实例的初始化过程
-
[Django] Django 连接 MySQL数据库 以及 makemigrations&migrate 过程详解总结