mysql数据库面试题（分享几道关于MySQL索引的重点面试题）

本文目录

• 分享几道关于MySQL索引的重点面试题
• MySQL中关于B+树的一道面试题，99%的候选人回答错误！
• 一道关于mysql的面试题，求助
• 一个面试题 mysql中 使用explain时应注意那些字段或者应该注意什么
• php面试题（假设有一个博客系统，数据库存储采用mysql）
• mysql数据库面试题（学生表_课程表_成绩表_教师表）
• Mysql数据库中sql语句常见的面试题和例子

分享几道关于MySQL索引的重点面试题

前言
索引是对数据库中一或多个列值的排序，帮助数据库高效获取数据的数据结构
假如我们用类比的方法，数据库中的索引就相当于书籍中的目录一样，当我们想找到书中的摸个知识点，我们可以直接去目录中找而不是在书中每页的找，但是这也抛出了索引的一个缺点，在对数据库修改的时候要修改索引到导致时间变多。
但MySQL 索引你真的懂吗？这几道题带你了解索引的几个重要知识点
1. 什么是最左前缀原则？
以下回答全部是基于MySQL的InnoDB引擎
例如对于下面这一张表
如果我们按照 name 字段来建立索引的话，采用B+树的结构，大概的索引结构如下
如果我们要进行模糊查找，查找name 以“张"开头的所有人的ID，即 sql 语句为
select ID from table where name like ’张%’
由于在B+树结构的索引中，索引项是按照索引定义里面出现的字段顺序排序的，索引在查找的时候，可以快速定位到 ID 为 100的张一，然后直接向右遍历所有张开头的人，直到条件不满足为止。
也就是说，我们找到第一个满足条件的人之后，直接向右遍历就可以了，由于索引是有序的，所有满足条件的人都会聚集在一起。
而这种定位到最左边，然后向右遍历寻找，就是我们所说的最左前缀原则。
2. 为什么用 B+ 树做索引而不用哈希表做索引?
1、哈希表是把索引字段映射成对应的哈希码然后再存放在对应的位置，这样的话，如果我们要进行模糊查找的话，显然哈希表这种结构是不支持的，只能遍历这个表。而B+树则可以通过最左前缀原则快速找到对应的数据。
2、如果我们要进行范围查找，例如查找ID为100 ~ 400的人，哈希表同样不支持，只能遍历全表。
3、索引字段通过哈希映射成哈希码，如果很多字段都刚好映射到相同值的哈希码的话，那么形成的索引结构将会是一条很长的链表，这样的话，查找的时间就会大大增加。
3. 主键索引和非主键索引有什么区别？
例如对于下面这个表(其实就是上面的表中增加了一个k字段),且ID是主键。
主键索引和非主键索引的示意图如下：
其中R代表一整行的值。
从图中不难看出，主键索引和非主键索引的区别是：非主键索引的叶子节点存放的是主键的值，而主键索引的叶子节点存放的是整行数据，其中非主键索引也被称为二级索引，而主键索引也被称为聚簇索引。
根据这两种结构我们来进行下查询，看看他们在查询上有什么区别。
1、如果查询语句是 select * from table where ID = 100,即主键查询的方式，则只需要搜索 ID 这棵 B+树。
2、如果查询语句是 select * from table where k = 1，即非主键的查询方式，则先搜索k索引树，得到ID=100,再到ID索引树搜索一次，这个过程也被称为回表。
现在，知道他们的区别了吧？
4. 为什么建议使用主键自增的索引？
对于这颗主键索引的树
如果我们插入 ID = 650 的一行数据，那么直接在最右边插入就可以了
但是如果插入的是 ID = 350 的一行数据，由于 B+ 树是有序的，那么需要将下面的叶子节点进行移动，腾出位置来插入 ID = 350 的数据，这样就会比较消耗时间，如果刚好 R4 所在的数据页已经满了，需要进行页分裂操作，这样会更加糟糕。
但是，如果我们的主键是自增的，每次插入的 ID 都会比前面的大，那么我们每次只需要在后面插入就行， 不需要移动位置、分裂等操作，这样可以提高性能。也就是为什么建议使用主键自增的索引。
总结

MySQL中关于B+树的一道面试题，99%的候选人回答错误！

问题：MySQL的索引B+树叶子节点上的数据记录是通过单向链表还是双向链表组织起来的？

本文的观点是基于MySQL使用Innodb存储引擎的情况下进行的！ 很多渠道说：MySQL数据按照主键大小依次排列，记录之间是双向链表连起来。如果说我告诉你这种说法很大程度上是错的，你肯定说我在胡扯。

我们先看看MySQL的B+树索引结构是什么样的

是的，这种图和网上的很多图都是类似的，我们可以看到每个节点上会有多个记录或者数据，MySQL使用Innob引擎时，这个节点就代表的是Innodb页，Innodb页是最小的存储单元。

通过上图或者你以前就知道，一个Innodb页存储很多条数据。 但是Innodb页可不只有数据记录，还会有其他数据
同样每条记录也不单单只有数据本身，还包含其他额外的数据，我的上篇文章有这么一幅图
其中额外信息中，有记录头信息部分，这部分到底长什么样呢

记录头占 5 个字节，40 位，下图是对应代表的含义

这里我们着重看一下下条记录的相对位置部分： 什么是相对位置呢？简要回答就是我离你有多远，而不是起点离你有多远。 放在MySQL里代表记录之间的距离。

下面我们借助另一张图看一下，看一下Innodb页中记录之间是怎么存储的

上图中我们看到，记录之间是亲密无间的排列的。 那我们怎么找下一条记录呢，还是看上面的出现的一张图，里面的下条记录的相对位置

比如，记录1的中下条记录的相对位置为38，意味着从记录1的真实数据开始往后找38个字节就是下一条记录的真实数据的开始。如果记录1的下条记录的相对位置是-38，代表从记录1的真实数据部分往前找38个字节就是下一条记录的真实数据部分。

如果你了解数据结构，你一定明白，这特么就是链表啊！ 是的，Innodb页中的数据之间，是通过单向链表来实现的。

但是，Innodb页之间是双向链表关联的。

一个Innodb页中的文件头中有这么两个属性：上一页的页号、下一页的页号

所以MySQL叶子节点之间是通过双向链表完成的。

Mysql中记录间既有单向链表，又有双向链表！

一道关于mysql的面试题，求助

非常简单的：
SELECT `class`,
SUM(CASE WHEN score》=60 THEN 1 ELSE 0 END),
SUM(CASE WHEN score》=60 THEN 0 ELSE 1 END)
FROM tb1
GROUP BY 1
主要是考试GROUP BY分组统计，CASE WHEN THEN ELSE END分情形功能，这个是非常典型的应用，几乎所有的数据库都支持上面的语句。

一个面试题 mysql中 使用explain时应注意那些字段或者应该注意什么

type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和all
possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从where语句中选择一个合适的语句
key： 实际使用的索引。如果为null，则没有使用索引。很少的情况下，mysql会选择优化不足的索引。这种情况下，可以在select语句中使用use index（indexname）来强制使用一个索引或者用ignore index（indexname）来强制mysql忽略索引
key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
rows：mysql认为必须检查的用来返回请求数据的行数
extra：关于mysql如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是using temporary和using filesort，意思mysql根本不能使用索引，结果是检索会很慢.

php面试题（假设有一个博客系统，数据库存储采用mysql）

• 让我来告诉你吧！那就是传说中的xp系统！SPIRIT ARE ALWAYS WITH YOU

• 这种试题，一点用都没有，每天访问量为5000万 全球排名30以内了……

mysql数据库面试题（学生表_课程表_成绩表_教师表）

Student(Sid,Sname,Sage,Ssex)学生表
Sid：学号
Sname：学生姓名
Sage：学生年龄
Ssex：学生性别
Course(Cid,Cname,Tid)课程表
Cid：课程编号
Cname：课程名称
Tid：教师编号
SC(Sid,Cid,score)成绩表
Sid：学号
Cid：课程编号
score：成绩
Teacher(Tid,Tname)教师表
Tid：教师编号：
Tname：教师名字

1、插入数据

2、删除课程表所有数据

3、将学生表中的姓名 张三修改为张大山

或者

4、查询姓’李’的老师的个数：

5、查询所有课程成绩小于60的同学的学号、姓名：

6、查询没有学全所有课的同学的学号、姓名

7、查询平均成绩大于60分的同学的学号和平均成绩

8、查询学过“100”并且也学过编号“101”课程的同学的学号、姓名

9、查询“100”课程比“101”课程成绩高的所有学生的学号

10、查询课程编号“100”的成绩比课程编号“101”课程高的所有同学的学号、姓名

11、查询学过“鲁迅”老师所教的所有课的同学的学号、姓名

12、查询所有同学的学号、姓名、选课数、总成绩

13、查询至少有一门课与学号为“1”同学所学相同的同学的学号和姓名

14、把“SC”表中“鲁迅”老师教的课的成绩都更改为此课程的平均成绩，
错误

15、查询和“2”学号的同学学习的课程完全相同的其他同学学号和姓名

16、删除学习“鲁迅”老师课的SC表记录

17、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号、002号课的平均成绩

18、查询各科成绩最高和最低的分：以如下的形式显示：课程ID，最高分，最低分

19、按各科平均成绩从低到高和及格率的百分数从高到低顺序

20、查询如下课程平均成绩和及格率的百分数(用”1行”显示): 数学（100），语文（101），英语（102）

22、查询不同老师所教不同课程平均分从高到低显示

23、查询如下课程成绩第3名到第6名的学生成绩单：数学（100），语文（101），英语（102）

23、统计下列各科成绩，各分数段人数：课程ID，课程名称，

24、查询学生平均成绩及其名次

25、查询各科成绩前三名的记录（不考虑成绩并列情况）

26、查询每门课程被选修的学生数

27、查询出只选修一门课程的全部学生的学号和姓名

28、查询男生、女生人数

29、查询姓“张”的学生名单

30、查询同名同姓的学生名单，并统计同名人数

31、1981年出生的学生名单（注：student表中sage列的类型是datetime）

32、查询平均成绩大于85的所有学生的学号、姓名和平均成绩

33、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列

34、查询课程名称为“英语”，且分数低于60的学生名字和分数

35、查询所有学生的选课情况

36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数

37、查询不及格的课程，并按课程号从大到小的排列

38、查询课程编号为“101”且课程成绩在80分以上的学生的学号和姓名

39、求选了课程的学生人数：

40、查询选修“鲁迅”老师所授课程的学生中，成绩最高的学生姓名及其成绩

41、检索至少选修两门课程的学生学号

42、查询全部学生都选修的课程的课程号和课程名(1.一个课程被全部的学生选修，2.所有的学生选择的所有课程)

43、查询没学过“鲁迅”老师讲授的任一门课程的学生姓名

44、查询两门以上不及格课程的同学的学号及其平均成绩

45、检索“101”课程分数小于60，按分数降序排列的同学学号

46、删除“2”同学的“101”课程的成绩

Mysql数据库中sql语句常见的面试题和例子

Sql语句常见实例以及解决办法；
基于索引的SQL语句优化
常见的Mysql面试题
SQL中LEFTJOIN的含义是__,如果tbl_user记录了学生的姓名(name)和学号(ID)， tbl_score记录了学生（有的学生考试以后被开除了，没有其记录）的学号(ID)和考试成绩(score)以及考试科目(subject)，要想打印出各个学生姓名及对应的的各科总成绩，则可以用SQL语句____. 答：自然左外连接 createdatabasephpinterview; usephpinterview createtabletbl_user ( IDintnotnull, namevarchar(50)notnull, primarykey(ID) ); createtabletbl_score ( IDintnotnull, scoredec(6,2)notnull, subjectvarchar(20)notnull ); insertintotbl_user(ID,name)values(1,’beimu’); insertintotbl_user(ID,name)values(2,’aihui’); insertintotbl_score(ID,score,subject)values(1,90,’语文’); insertintotbl_score(ID,score,subject)values(1,80,’数学’); insertintotbl_score(ID,score,subject)values(2,86,’数学’); insertintotbl_score(ID,score,subject)values(2,96,’语文’); selectA.id,sum(B.score)assumscore fromtbl_userAleftjointbl_scoreB onA.ID=B.ID groupbyA.id
计选修课的学生总数和考试的平均成绩。SELECTCOUNT(DISTINCT(Sno))AS学生总数,AVG(Grade)AS平均成绩FROMSC--//用DISTINCT消除重复的行
查询选课门数超过2门的学生的平均成绩和选课门数。SELECTStudent.Sname,AVG(Sc.Grade)平均成绩,COUNT(SC.Sno)选课门数FROMSC--内联接join或innerjoin,内联系是比较运算符，只返回符合条件的行JOINStudentON(SC.Sno=Student.Sno)JOINCourseON(SC.Cno=Course.Cno)GROUPBYStudent.SnameHAVINGCOUNT(distinctCourse.Cno)2--分组条件