MySQL一直是面试中的热点问题,也难道了很多的面试者。其实MySQL没那么难,只是大家没有系统化、实战性的过去学习、总结。同时很多开发者在实际的开发过程中也很少去接触一些偏向底层的知识。
今天这篇文章,将为大家总结MySQL中场景的面试题。围绕索引、事务、锁等几个方面的热点问题,系统化的总结。大致分为如下大纲:
索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她,则在表中搜索所有的行相比,索引有助于更快地获取信息通俗的讲,索引就是数据的目录,就像看书一样,假如我想看第三章第四节的内容,如果有目录,我直接翻目录,找到第三章第四节的页码即可。如果没有目录,我就需要将从书的开头开始,一页一页翻,直到翻到第三章第四节的内容。
MySQL中索引分为B+Tree索引、hash索引、空间索引(选择什么样的索引方式,需要看具体的存储引擎)。B+Tree又分为聚集索引和非聚集索引。聚集索引包含了主键索引。非聚集索引包含,唯一索引、前缀索引、联合索引、覆盖索引和普通索引。
索引的数据结构和具体存储引擎的实现有关, 在MySQL中使用较多的索引有Hash索引,B+树索引等。而我们经常使用的InnoDB存储引擎的默认索引实现为:B+Tree索引。
首先要知道Hash索引和B+树索引的底层实现原理:
那么可以看出他们有以下的不同:
因此,在大多数情况下,直接选择B+树索引可以获得稳定且较好的查询速度。而不需要使用hash索引。
在B+树的索引中,叶子节点可能存储了当前的key值,也可能存储了当前的key值以及整行的数据,这就是聚簇索引和非聚簇索引.。在InnoDB中,只有主键索引是聚簇索引,如果没有主键,则挑选一个唯一键建立聚簇索引,如果没有唯一键,则隐式的生成一个键来建立聚簇索引。当查询使用聚簇索引时,在对应的叶子节点,可以获取到整行数据,因此不用再次进行回表查询。
当数据库有并发事务的时候,可能会产生数据的不一致,这时候需要一些机制来保证访问的次序,锁机制就是这样的一个机制。就像酒店的房间,如果大家随意进出,就会出现多人抢夺同一个房间的情况,而在房间上装上锁,申请到钥匙的人才可以入住并且将房间锁起来,其他人只有等他使用完毕才可以再次使用。
有三种格式,statement,row和mixed。statement模式下,记录单元为语句。即每一个sql造成的影响会记录,由于sql的执行是有上下文的,因此在保存的时候需要保存相关的信息,同时还有一些使用了函数之类的语句无法被记录复制。row级别下,记录单元为每一行的改动,基本是可以全部记下来但是由于很多操作,会导致大量行的改动(比如alter table),因此这种模式的文件保存的信息太多,日志量太大。mixed,一种折中的方案,普通操作使用statement记录,当无法使用statement的时候使用row。此外,新版的MySQL中对row级别也做了一些优化,当表结构发生变化的时候,会记录语句而不是逐行记录。
索引下推的目的是为了减少回表次数,也就是要减少IO操作。对于InnoDB的聚簇索引来说,数据和索引是在一起的,不存在回表这一说。
索引条件下推默认是开启的,可以使用系统参数optimizer_switch来控制器是否开启。
假设有一个需求,要求匹配姓名第一个字为陈,年龄为20岁的用户,此时的sql语句如下:
SELECT * from user where name like '陈%' and age=20
5.6之前的版本是没有索引下推这个优化的,会忽略age这个字段,直接通过name进行查询,在(name,age)这课树上查找到了两个结果,id分别为2,3,然后拿着取到的id值一次次的回表查询,因此这个过程需要回表两次。
在5.6之后,InnoDB并没有忽略age这个字段,而是在索引内部就判断了age是否等于20,对于不等于20的记录直接跳过,因此在(name,age)这棵索引树中只匹配到了一个记录,此时拿着这个id去主键索引树中回表查询全部数据,这个过程只需要回表一次。
MyISAM的索引方式都是非聚簇的,与InnoDB包含1个聚簇索引是不同的。
*.sdi(描述表结构)
、*.MYD(数据)
,*.MYI(索引)
.ibd(表结构、索引和数据都存在一起)
假设有一个表index_demo,表中有2个INT类型的列,1个CHAR(1)类型的列,c1列为主键:
CREATE TABLE index_demo(c1 INT,c2 INT,c3 CHAR(1),PRIMARY KEY(c1)) ;
index_demo表的简化的行格式示意图如下:
我们只在示意图里展示记录的这几个部分:
record_type:
表示记录的类型, 0是普通记录、 2是最小记录、 3 是最大记录、1是B+树非叶子节点记录。next_record:
表示下一条记录的相对位置,我们用箭头来表明下一条记录。各个列的值:
这里只记录在 index_demo 表中的三个列,分别是 c1 、 c2 和 c3 。其他信息:
除了上述3种信息以外的所有信息,包括其他隐藏列的值以及记录的额外信息。将其他信息
项暂时去掉并把它竖起来的效果就是这样:
把一些记录放到页里的示意图就是(这里一页就是一个磁盘块,代表一次IO):
MySQL InnoDB的默认的页大小是16KB
,因此数据存储在磁盘中,可能会占用多个数据页。如果各个页中的记录没有规律,我们就不得不依次遍历所有的数据页。如果我们想快速的定位到需要查找的记录在哪些数据页中
,我们可以这样做 :
以页28
为例,它对应目录项2
,这个目录项中包含着该页的页号28
以及该页中用户记录的最小主键值 5
。我们只需要把几个目录项在物理存储器上连续存储(比如:数组),就可以实现根据主键值快速查找某条记录的功能了。比如:查找主键值为 20 的记录,具体查找过程分两步:
主键值为20的记录在目录项3中
(因为 12 ≤ 20 < 209 ),对应页9
。至此,针对数据页做的简易目录就搞定了。这个目录有一个别名,称为索引
。