最详细关系数据库(Mysql)面试（原理+实例）

关系数据库主要考点

主要考察的是索引、锁部分。

如何设计一个关系型数据库（RDBMS）？

1.存储（文件系统），即将数据持久化到硬盘中。2.程序实例模块，对存储进行逻辑上的管理，主要分为八个部分： 索引管理——优化查询效率 锁管理——支持并发 存储管理 ——逻辑关系转换成物理存储关系 缓存机制——优化执行效率 SQL解析——解析SQL语句 日志管理——记录操作 权限划分——进行多用户管理 容灾机制——灾难恢复

关系数据库Mysql

关系数据库主要考点一、索引模块1.1 为什么使用索引？1.2 什么样的信息能成为索引？1.3 索引的数据结构1.4 为何B+树适合做存储索引？1.5 hash索引的缺点？1.6 密集索引和稀疏索引1.7 InnoDB（必须要有一个密集索引）1.8 两种存储结构检索过程1.9 数据库常见问题（mysql为例）1.10 如何定位并优化慢查询Sql？1.11 为什么有联合索引的最左匹配原则？1.12 索引是否越多越好？（类似于书的内容和目录）1.13 扩展（主、外键，唯一键等约束） 二、锁模块2.1 MyISAM和InnoDB关于锁方面的区别2.2 MyISAM简介2.3 InnoDB简介2.4 MyISAM适合的场景2.5 InnoDB适合的场景2.6 数据库锁的分类2.7 事物的四大特性（ACID）2.8 事务的隔离性以及各级别下的并发访问问题2.8.1 并发问题及如何避免 2.9 锁模块之当前读和快照读2.10 InnoDB可重复读隔离级别下如何避免幻读（RR如何避免幻读）2.11 RC、RR级别下的InnoDB的非阻塞读如何实现 三、语法部分3.1 GROUP BY3.2 HAVING 四、如何介绍项目

一、索引模块

1.1 为什么使用索引？

查询数据方式有两种：

1.全局（表）扫描，将数据分块或分批次加载到内存，逐块进行查询，效率慢适合小规模数据。2.索引查找，能够避免全局扫描，将数据查找限定在一定的范围内，提升查找效率。

1.2 什么样的信息能成为索引？

主键、唯一键以及普通键等，能够使数据具备一定区分性的字段。

1.3 索引的数据结构

生成索引——二叉查找树进行二分查找，树的高度如果很高，在每个判断的树的结点都会进行一次I/O，效率较低。（因此可以在每个子结点存储更多的数据，即B-Tree）。

生成索引——建立B-tree结构进行查找，包含关键字、指向子结点的指针。结点最多能存储的数据取决于每个存储快的容量和数据库的相关配置。目的，尽可能的减少I/O次数。（m表示每个结点包含的孩子数）（前4条限定B-树的孩子树和深度，5条限定B-树的关键字数和大小）（查找效率O(logn)（插入或删除数据，分裂、合并、上升等操作保证下面5条规则）。

1.根结点至少包括两个孩子。 2.树中每个结点最多包含有m个孩子（m>=2）。 3.根结点和叶结点除外，其他每个结点至少有ceil(m/2)个子结点。 4.叶子结点位于同一层。 5.

生成索引——建立B+Tree结构进行查找（主要使用，更适合做存储索引）。B±树是B-树的变体，定义基本与B树相同。除了：

1.非叶子结点的子树指针与关键字个数相同。 2.非叶子结点的子树指针P[i]，指向关键字值(K[i],K[i+1])的子树。 3.非叶子结点只做索引，这样非叶子结点空间不存储数据，有更多的空间存储索引，数据只存储在叶子结点，搜索只在叶子结点终结。 4.所有叶子结点均有一个链指针指向下一个叶子结点（有序表存储），查找不会返回到叶子结点，而是横向跨子树统计（范围统计）。

生成索引—建立Hash结构进行查找。只需一次hash查找就能找到数据所在的buckets，再将该bucket加载到内存，因为bucket里面的数据存储结构为链表，通过指针查找，查找相应数据。

另外，还有BitMap位图索引。

1.4 为何B+树适合做存储索引？

1.B+树的磁盘读写代价更低，B+树的内部结构没有指向关键字具体信息的指针（不存放数据），只存放索引信息，其内部结点相对B-树更小，块中可以存放的关键字信息更多，I/O次数更少。2.B+树的查询效率更加稳定，任何查找路径都必须包含根结点-叶子结点，关键字查找长度相同，每个数据的查找效率几乎相同（O(logn)）。3.B+树更有利于对数据库的扫描。

1.5 hash索引的缺点？

1.仅仅能满足‘=’，‘IN’之类的查询，不能使用范围查询。hash查找是比较hash运算后的hash值，只能用于‘=’值的过滤，不能用于基于范围的查询2.无法进行数据的排序操作。hash索引中存放的是经过hash运算后的值，hash值大小关系并不一定和hash运算前的键值完全一样，所以数据库无法进行排序操作。不能利用部分索引键进行查询。对于组合索引，hash索引在进行hash值的时候，将组合索引键合并之后，再进行hash值，而不是单独计算hash值。不能避免表扫描。由于不同的主键可能存在相同的hash值，hash索引不能直接从一个bucket中得到相应的值，因此还是需要对bucket的值进行扫描比较。遇到大量hash值相等的情况，性能并不一定就会比B-Tree索引高。

1.6 密集索引和稀疏索引

1.密集索引文件中的每个搜索码值都对应一个索引值。叶子结点中不仅包含了键值，还包含了位于同一行记录里的其他列的信息，由于密集索引决定了表的物理排列顺序，一个表只能拥有一个物理排列顺序，所以一个表只能创建一个密集索引。2.稀疏索引只为索引码的某些值建立索引项。叶子结点仅保存了键位信息和该行数据的地址，有的稀疏索引仅包含了键位信息和其主键。定位到叶子结点后，仍然需要通过地址和主键信息，进一步定位到数据。

mysql有两种数据的存储格式：InnoDB和MyISAM

1.7 InnoDB（必须要有一个密集索引）

1.若一个主键被定义，该主键作为密集索引。2.若没有主键被定义，该表的第一个唯一非空索引作为密集索引。3.若不满足以上条件，InnoDB内部会生成一个隐藏主键作为密集索引。4.非主键索引存储相关键位和其对应的主键值，包含两次查找。稀疏索引的叶子结点并不存储行数据的物理地址，而是存储该行的主键值，所以非主键索引包含了两次查找，一是查找次级索引自身，二是查找主键。（这就是为什么需要密集索引？）

1.8 两种存储结构检索过程

1.InnoDB使用的是密集索引（包含.frm表结构信息），将主键组织到一个B+树中，行数据存储在叶子结点上。因为InnoDB的主键索引和对应的数据是存储在同一个文件中（.ibd），所以检索过程中，在加载叶子结点的过程中，主键进入内存时，对应的数据也被加载到内存。

例如：若使用where id = 14这样的条件查询主键，按照B+树的查找规则即可查到对应的叶子结点并获得对应的主键和行数据。若对稀疏索引进行主键筛选，需经历两个步骤：在稀疏索引的B+树中检索该键，定位到主键信息，再利用主键信息在主见索引B+树中再次进行检索操作。

2.MyISAM使用的是稀疏索引（包含.frm），主键索引和辅助键索引结构都一样，只是存储的信息不同一个存储的是主键，另一个是辅助键两个索引结构都存储在一个文件中（.MYI），表数据存储在另一个文件中（.MYD）。索引树是独立的，对于表数据而言主键索引和辅助键索都一样。

1.9 数据库常见问题（mysql为例）

1.如何定位并优化慢查询Sql2.为什么有联合索引的最左匹配原则3.索引是否越多越好

1.10 如何定位并优化慢查询Sql？

1.根据慢查询日志定位慢查询sql2.使用explain等工具分析sql。3.修改sql或者尽量让sql走索引。

1.11 为什么有联合索引的最左匹配原则？

1.联合索引：有多列（字段）组成的索引。如：KEY ‘index_area_title’(‘area’,‘title’);2.最左匹配原则：非常重要的原则，mysql会一直向右匹配直到遇到范围查询（>，<，berween，like）就停止匹配，这样若是在联合索引飞截止位置停止匹配，就不是联合索引了。‘=’和‘in’可以乱序，mysql的查询优化器会优化索引为可以识别的形式。3.mysql创建联合索引的规则，首先会对联合索引的第一个字段的数据进行排序（绝对有序），在此基础上再对第二个索引字段进行排序（无序），……，因此通常情况下，如果直接使用第二个索引字段进行条件判断，用不到索引（可能没办法走B+树的索引），所以必须从最左开始进行条件判断，即最左匹配原则。

1.12 索引是否越多越好？（类似于书的内容和目录）

1.数据量小的表不需要建立索引，建立会增加额外的索引开销。2.数据表更需要维护索引，因此更多的索引意味着更多的维护成本。3.更多的索引也意味着需要更多的空间。

1.13 扩展（主、外键，唯一键等约束）

二、锁模块

2.1 MyISAM和InnoDB关于锁方面的区别

1.MyISAM默认使用的是表级锁，不支持行级锁。2.InnoDB默认的是行级锁，也支持表级锁。

2.2 MyISAM简介

MyISAM结构的数据库中，一个session对某一表进行操作时，会给该表进行加锁，并block其他session对该表的操作。当对数据进行读（select）的时候，会自动加上表级别的读锁；增删改（insert、delete、update）的时候，会自动加上表级别的写锁。当表的读锁未被释放时，想要对表进行写锁，会被阻塞，直到所有的读锁释放。

1.读锁为共享锁，当一个表先上了读锁还能再加读锁，不能上排他锁，故称之为共享锁。2.写锁为排他锁，当一个表先上了写锁就不能对其加读锁（select）或者写锁（insert、delete、update），故称之为排他锁。3.也可以手动改变共享锁为排他锁，即在每个select的后面加上‘for update’。手动加共享锁‘lock in share mode’。4.上述两种锁的情况同样适用于InnoDB。5.MyISAM不支持事务，sql语句执行完成后，会自动释放锁。

2.3 InnoDB简介

InnoDB默认是自动提交数据

1.InnoDB默认支持行级锁。

2.InnoDB支持事务，用的是二段锁（加锁和解锁分为两个步骤，先对同一个事务里的一批操作进行加锁，然后commit的时候，再对事务里加上的锁统一进行解锁。当commit自动提交时，看起来和MyISAM差不多）。可以通过sessio获取锁，暂时不自动提交（‘set autocommit = 0’ 关闭当前session的自动提交，只针对当前session）的方式，模拟并发访问的过程。InnoDB对共享锁进行了优化，需要自己显示加锁。

3.MyISAM一旦操作了表的数据，就会默认加表级锁，因此与索引无关。而InnoDB中默认使用的是行级锁，只有sql用到了索引，涉及到的行都会被上共享锁或排他锁。

4.主键id用到的是密集索引，‘index_area_title’是联合索引，‘motto’无索引。以该无索引的字段进行sql操作（注意，该sql中不涉及任何索引，只以motto为操作条件），如果一个session先在A行上共享锁，另一个session在B行上排他锁，还是会被bolck。当不走索引的时候，整张表都会被锁住。即InnoDB在sql没有用到索引的时候，用到的是表级锁。而sql用到索引的时候，用到的是行级锁和gap（部分）锁。

5.InnoDB还支持意向的表级锁，共享读锁（IS）、共享写锁（IX），作用于MyISAM的表级锁类似，在InnoDB中的IS、IX作用是：在表级别操作的时候，不用轮询每一行是否有行锁。

6.行级锁是否一定比表级锁好？不一定，锁的粒度越细，代价越高。表级锁在表的头部直接加锁，而行级锁还要在扫描到某行的时候，对其进行上锁。

7.InnoDB支持事务，比不支持事务的MyISAM的开销更大。

2.4 MyISAM适合的场景

1.InnoDB有且仅有一个密集索引，数据文件和索引绑在一起，必须要有主键，通过主键索引效率较高，但是辅助索引需要查两次，先查到主键，再通过主键查询到数据。而MyISAM采用的是非密集索引，数据文件和索引是分离的，索引保存的是数据文件的指针，主键索引和辅助索引是独立的。因此，MyISAM引擎在执行检索系统中，性能优于InnoDB。2.频繁执行全表count的语句。InnoDB不保存表的具体行树，在执行count的时候需要全表扫描统计。MyISAM用一个字段保存了表的行数，只需要读一次就能得出表的行数。3.对数据进行增删改的频率不高，查询非常频繁。因为，增删改会涉及到锁表操作。4.不需要事务的场景。

2.5 InnoDB适合的场景

1.数据增删改查都相当频繁，增删改的时候只是某些行被锁，大多数情况下避免了阻塞。2.可靠性要求比较高。3.支持事务的场景。

2.6 数据库锁的分类

1.按锁的粒度划分，表级锁、行级锁、页级锁

2.按锁级别划分，共享锁、排他锁

3.按锁的方式，自动锁、显示锁

4.按操作划分，DML锁（针对数据）、DDL锁（针对表结构）

5.按使用方式划分，乐观锁、悲观锁

悲观锁：对外界的操作持保守态度，在整个数据的处理过程中，将数据处于锁定状态。悲观锁的实现，基于数据库提供的锁机制。全程用排他锁实现，即悲观锁。悲观并发控制，实际上是先上锁、再访问的保守策略，但是也会有额外的开销、增加产生死锁的概率。在只读的事务中，不会产生数据的冲突，加锁会降低事务的并发性。

乐观锁：认为数据一般情况下不会产生冲突，所以只在数据进行commit的时候才会对数据进行冲突检测。如果发现冲突，返回用户信息由用户决定。实现机制不依靠数据库的锁机制，而是记录数据的版本（版本号、时间戳）。因此不会产生死锁，但是也会产生不可预料的冲突，例如：两个事务都读取数据的某一行，进行版本号的更新，再进行回写，就会产生数据冲突。

2.7 事物的四大特性（ACID）

1.原子性（Atomic），事物包含的所有操作，要么全部执行，要么全部失败回滚。2.一致性（Consistency），数据库的数据应该满足完整性约束。3.隔离性（Isolation），多个事务并发执行时，事物之间应该互不影响。4.持久性（Durability），一个事务的提交，对数据库的修改，应永久保存在数据库中。当数据库发生故障时，对数据库的更新不能丢失。DBMS应该提供适当的冗余，持久性保证数据库的可恢复性。

2.8 事务的隔离性以及各级别下的并发访问问题

REPEATABLE-READ是数据库mysql默认的隔离级别，最高级别是SERIALIZABLE（所以的sql操作都会默认加上锁）。隔离级别越高，虽然越能避免并发的问题，但是会导致串行化严重、并发效率不高。

2.8.1 并发问题及如何避免

1.更新丢失——mysql所有事物隔离级别在数据库层面上均可避免这种情况。

2.脏读——READ-COMMITTED事物隔离级别及以上可避免。

脏读：一个事务读取到另一个事务未提交（执行sql操作，但是因为故障，又rollback回滚的情况）的数据。

3.不可重复读——REPEATABLE-READ事物隔离级别以上可避免。

不可重复读：一个事物对数据的多次读取，结果不一致，即读取的数据不可靠。

4.幻读——SERIALIZABLE事物隔离级别可避免。

幻读：一个事务进行sql操作时，影响行的数目变了，因为另一个事务对该表进行了（insert、delete）sq操作。

2.9 锁模块之当前读和快照读

2.10 InnoDB可重复读隔离级别下如何避免幻读（RR如何避免幻读）

2.11 RC、RR级别下的InnoDB的非阻塞读如何实现

三、语法部分

1.GROUP BY （GROUP BY sid from course——即根据sid对course进行分组。）2.HAVING3.统计相关：COUNT（求总数）、SUM（求和）、MAX、MIN、AVG

3.1 GROUP BY

1.对于单个表，如果用GROUP BY，那么select语句中选出来的列要么是GROUP BY里用到的列，要么就是带有聚合函数的列。（满足select子句中的列名必须为分组列或列函数 ）

2.列函数对于GROUP BY子句定义的每个组各返回一个结果。

例子：

select count(course_id), sum(score) from score GROUP BY sid 1.即根据sid对score表进行分组，分组完成之后可以通过count、sum等聚合函数对每个组进行运算。2.GROUP BY会把分组的结果集，缓存到临时表里，再通过统计函数对结果集进行统计并展示。 select s.student_id, stu.name, count(s.course_id),sum(s.score) from score s, student stu where s.student_id = stu.student_id GROUP BY s.student_id 1.对于多表联查，不受规则（满足select子句中的列名必须为分组列或列函数 ）限制。

3.2 HAVING

1.通常与GROUP BY子句一起使用，如果未与GROUP BY一起使用，则功能与WHERE一样。2.WHERE过滤行、HAVING过滤组。3.sql顺序：WHERE>GROUP BY>HAVING select student_id,avg(score) from score GROUP BY student_id HAVING avg(score)>60

例子

select student_id,count(course_id),sum(score) from score GROUP BY student_id; select student_id,avg(score) from score GROUP BY student_id HAVING avg(score)>60 #查询没有学全所有课的同学的学号、姓名 select s.student_id,stu.name from student stu,score s WHERE stu student_id= s.student_id GROUP BY s.student_id HAVING count(*) < (select count(*) from course)

四、如何介绍项目

1.项目用途、自己的角色、如何解决难题。2.找不到难点，则谈改进3.事前用图形将项目勾画清楚。

最后修改于时间：2020年10月15号