mysql —案例复杂查询+索引使用+DBeaver中创建索引-Toy模板网

这篇具有很好参考价值的文章主要介绍了mysql —案例复杂查询+索引使用+DBeaver中创建索引。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

前言

接上章我们对一个简单的选课功能进行设计分析

实际上在工作中拿到一个需求，也是这样的一个分析过程

一个份需求文档+原型出来，只要是你负责这个模块，就需要你自己建表建库，设计接口文档，也许现在有的公司会有一个带你的大哥会帮你建表建库【甚至接口文档都帮你写好了，你只需要按照接口进行开发】，那也只是初步的【实习生】，到后面还是得靠自己

再有的公司是需要开发人员写概要设计（里面就包含数据库设计）的 , 但总的来说是八九不离十，分析步骤还是一样的

案例

我们一步一步来，先造一些数据

1. teacher_info

--  Auto-generated SQL script #202211150937
INSERT INTO elective.teacher_info (id,code,name,sex,professional,create_id,create_time,update_id,update_time)
  VALUES (2,'LS20221115001','李四',0,'副教授',1,'2022-11-15 10:11:25',1,'2022-11-15 10:11:25');

mysql —案例复杂查询+索引使用+DBeaver中创建索引,mysql,mysql,数据库

2. student_info

INSERT INTO elective.student_info (id,code,name,sex,class_name,create_id,create_time,update_id,update_time,age)
  VALUES (2,'XS20221115001','学生2',0,'物联网工程二班',1,'2022-11-15 10:11:25',1,'2022-11-15 10:11:25',23);

mysql —案例复杂查询+索引使用+DBeaver中创建索引,mysql,mysql,数据库

3.course_info

比如

老师张三有两门课一门微积分一门 java基础

老师李四有一门课 SpringCloud概论

INSERT INTO elective.course_info (id,teacher_id,course_name,course_code,course_type,course_score,create_id,create_time,update_id,update_time)
VALUES (1,1,‘微积分’,‘WJF2022’,‘数学’,4,1,‘2022-11-15 10:11:25’,1,‘2022-11-15 10:11:25’);

mysql —案例复杂查询+索引使用+DBeaver中创建索引,mysql,mysql,数据库

4 .student_course_info

选课表

学生1 选了张三老师的微积分 + 李四老师的 SpringCloud概论

学生2 选了张三老师的 java基础

--  Auto-generated SQL script #202211150955
INSERT INTO elective.student_course_info (id,stu_id,cou_id,score,create_id,create_time,update_id,update_time)
  VALUES (2,1,3,95,1,'2022-11-15 10:11:25',1,'2022-11-15 10:11:25');

mysql —案例复杂查询+索引使用+DBeaver中创建索引,mysql,mysql,数据库

5. 模拟查询

1. 列出学生1选择的课程详细信息（包括学生名字,课程名称,授课老师,分数）

select
  si.name as stuName,
  ci.course_name as courseName,
  ti.name  as teacherName,
  sci.score 
from
  student_course_info as sci  
left join student_info si on sci.stu_id = si.id
left join course_info ci on ci .id  = sci.cou_id
left join teacher_info ti on ti.id  = ci.teacher_id 
where sci.stu_id = 1

as：取别名可省略

mysql —案例复杂查询+索引使用+DBeaver中创建索引,mysql,mysql,数据库

2. 查询张三老师的所有课程

select 
  ci.course_name as teacherName,
  ti.name 
   from   course_info ci  
   left join teacher_info ti on ci.teacher_id  = ti.id 
   where ci.teacher_id =1

mysql —案例复杂查询+索引使用+DBeaver中创建索引,mysql,mysql,数据库
3. 查询所有学生选修的总学分，并且从高到低排序

desc：降序

asc: 升序

select
  si.name,
  sum(ci.course_score) as sumScore
from
  student_course_info sci
left join student_info si on sci.stu_id = si.id
left join course_info ci on ci.id = sci.cou_id
group by
  sci.stu_id
order by
  sumScore desc

mysql —案例复杂查询+索引使用+DBeaver中创建索引,mysql,mysql,数据库
6. 索引Type类型

我们可以使用explain来查看索引类型

就比如 2. 查询张三老师的所有课程这个sql 语句
mysql —案例复杂查询+索引使用+DBeaver中创建索引,mysql,mysql,数据库

type 列有 all 跟 const 两个类型，possible keys 就是我们使用到的索引，可以看到类型为 all 的索引为 null ，const 就是主键

这边有个索引类型的级别：

system > const > eq_ref > ref > range > index > all

越左边查询效率越快，all效率最低

没有索引的就是all,需要进行优化

这时候我们就需要给 course_info 加上索引了

where 条件后面的 teacher_id 需要加上索引

mysql —案例复杂查询+索引使用+DBeaver中创建索引,mysql,mysql,数据库

记得 Ctrl+S 保存

mysql —案例复杂查询+索引使用+DBeaver中创建索引,mysql,mysql,数据库

再来查询看看

mysql —案例复杂查询+索引使用+DBeaver中创建索引,mysql,mysql,数据库
可以看到级别变了，变成了ref ，可能现在数据量少没看出效率变化，只要数据量一多，就可以明显的感受到效率的提升不是一星半点文章来源地址https://www.toymoban.com/news/detail-542945.html