我以为我对MySQL很了解,直到我面试了字节跳动

barzxl
发布于 2022-4-17 13:04
浏览
0收藏

面试开始~~

面试官:说一下in关键字和exists关键字吧

我:好的,in关键字尽量用在内表小的地方,exists关键字用在外表小的地方,如果用not in ,则是内外表都全表扫描,无索引,效率低,可考虑使用not exists,也可以考虑用连接来优化。(内心OS,这能难得到我?)

接下来分析一波(面试回答问题一定要条理清晰!):

in关键字是把外表和内表做hash连接,先查询内表,再把内表的结果和外表匹配,对外表使用索引(外表效率高,可以用大表),而内表都需要查询,使用in关键字可以加快效率。exists关键字是对外表做loop循环,每次循环对内表进行查询(对内表可以使用索引,查询效率高,可以用大表),而外表有多大都需要遍历,使用exists关键字可以加快效率。

举个例子:select * from A where A.id in (select id from B);对A表使用索引效率高,建议A为大表。select * from A where exists (select * from B where A.id=B.id);对B使用索引效率高,因为外表A总是要全表,而且要循环,所以B表建议使用大表。

面试官:说一下count吧

我:count关键字是用来进行不为null的行数统计的,有三种用法分别是:count(列名)、count(常量)和count(*)。在《阿里巴巴Java开发手册》中强制要求不让使用 count(列名)或 count(常量)来替代 count(*)。

区别:列名、 常量 和 *这三个条件中,常量是一个固定值,肯定不为null。*可以理解为查询整行,所以肯定也不为null,那么就只有列名的查询结果有可能是null了,所以count(常量) 和 count(*)表示的是直接查询符合条件的数据库表的行数。而count(列名)表示的是查询符合条件的列的值不为null的行数。count(*)是SQL92定义的标准统计行数的语法,因为他是标准语法,所以mysql数据库对他进行过很多优化。

SQL92,是数据库的一个ANSI/ISO标准。它定义了一种语言(SQL)以及数据库的行为(事务、隔离级别等)。

面试官:你能说一下这些优化吗?

我:(……我还真是自找麻烦小能手~~)面带微笑,当然可以的。这里的介绍要区分不同的执行引擎,MySQL中比较常用的执行引擎就是InnoDB和MyISAM。我们知道MyISAM不支持事务,表级锁,而InnoDB支持事务,支持行级锁。

因为MyISAM是表级锁,所以在一张表上的操作是串行执行的。所以,MyISAM做了一个简单的优化,那就是它可以把表的总行数单独记录下来,如果从一张表中使用count(*)进行查询的时候,可以直接返回这个记录下来的数值就可以了,当然,前提是不能有where条件。为什么MyISAM可以这样做呢?因为它是表级锁,不会有并发的数据库修改记录的行为,查询的行数是准确的。

对于InnoDB则不适合这种缓存操作了,它是支持事务和行级锁,表的行数可能会被并发修改,那么缓存记录下来的行数就不准确了,那么InnoDB则不可避免的要进行扫表了。于是从mysql8.0.13开始,select count(*) 在扫表的过程中做了一些优化,前提是查询语句中不包含where或group by等条件。我们的目的只是为了统计总行数,并不关心查到的具体值,所以可以选择一个成本较低的索引进行,节省时间。而且InnoDB中索引分为聚簇索引(主键索引)和非聚簇索引(非主键索引),聚簇索引的叶子节点中保存的是整行记录,而非聚簇索引的叶子节点中保存的是该行记录的主键的值。所以,相比之下,非聚簇索引要比聚簇索引小很多,所以MySQL会优先选择最小的非聚簇索引来扫表。所以,当我们建表的时候,除了主键索引以外,创建一个非主键索引还是有必要的。

面试官:mysql中字段为什么要求定义为not null?

我:null值会占用更多的字节,且会在程序中造成很多与预期不符的情况。

面试官:(此时面试官点了点头,继续微笑着说到)你刚刚提到过引擎,除了你说的两点区别,还有别的吗?

我:接下来我来分点介绍

  • 上面刚刚介绍的,MyISAM不支持事务,表级锁,而InnoDB支持事务,支持行级锁。
  • InnoDB 支持外键,而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败;
  • InnoDB 是聚集索引,MyISAM 是非聚集索引。

面试官:那你能说说什么是索引吗?

我:索引其实是一种数据结构,能够帮助我们快速的检索数据库中的数据。在用法上索引大概分为以下几类:

  • 普通索引normal:仅仅加索查询;
  • 唯一索引unique:加索查询,列值唯一,可以有NULL。
  • 主键索引primary:加速查询,列值唯一,不可以为NULL,表中只有一个。
  • 组合索引:多列值组成一个索引,专门用于组合搜索,其效率大于索引合并。

全文索引full text:对文本的内容进行分词,进行搜索。

面试官:那索引具体底层是什么样子的?(言外之意就是数据结构)

我:索引的数据结构和具体存储引擎的实现有关,MySQL主要有两种结构:Hash索引和B+ Tree索引,我们使用的是InnoDB引擎,默认的是B+树。

面试官:为什么采用B+树呢,和Hash索引比较如何?

我:好的,接下来我来详细介绍下两者:

  • Hash索引:Hash索引底层是哈希表,哈希表是以一种Key-Value存储数据的结构,所以多个数据在存储关系上是没有顺序的,也就没办法进行范围查询,必须要全表扫描。哈希索引只适用于等值查询的场景。
  • B+树索引:B+树更适合外部存储。由于内结点不存放真正的数据(只是存放其子树的最大或最小的关键字,作为索引),一个结点可以存储更多的关键字,每个结点能索引的范围更大更精确,也意味着B+树单次磁盘IO的信息量大于B树,I/O的次数相对减少。B+树也是更容易进行区间访问的,因为叶子节点维护了一个链表。

我以为我对MySQL很了解,直到我面试了字节跳动-鸿蒙开发者社区

面试官:关于B+树的叶子节点,可以存放哪些东西?

我:在B+树的索引中,叶子节点可能存储了当前的key值,也可能存储了当前的key值以及整行的数据,这就是聚簇索引和非聚簇索引。在InnoDB引擎中,只有主键是聚簇索引,如果没有主键则挑选一个唯一键作为聚簇索引,如果没有唯一键,则隐式的生成一个键来建立聚簇索引。

面试官:聚簇索引和非聚簇索引特点?

我:聚簇索引和非聚簇索引,聚簇索引更快,因为主键索引树的叶子节点直接就是我们要查询的整行数据了。而非主键索引的叶子节点是主键的值,查到主键的值以后,还需要再通过主键的值再进行一次查询。当查询使用局促索引的时候,在对应的叶子结点上可以获取到整行的数据,不再需要回表查询,而非聚簇索引则需要回表查询。

面试官:那非聚簇索引一定要回表搜索吗?

我:不一定,这涉及到查询语句所要求的字段是否全部命中了索引,如果全部命中了索引,那么就不必再进行回表查询,即覆盖索引。

覆盖索引(covering index)指一个查询语句的执行只用从索引中就能够取得,不必从数据表中读取。也可以称之为实现了索引覆盖。 当一条查询语句符合覆盖索引条件时,MySQL只需要通过索引就可以返回查询所需要的数据,这样避免了查到索引后再返回表操作,减少I/O提高效率。 如表covering_index_sample中有一个普通索引 idx_key1_key2(key1,key2)。当我们通过SQL语句:select key2 from covering_index_sample where key1 = ‘keytest’;的时候,就可以通过覆盖索引查询,无需回表。

此时,面试官投来了一个肯定的眼神(这小伙子还可以~~),不过她好像没打算就此放过我,果不其然,又来了第二波!

面试官:在建立索引的时候,你一般考虑哪些因素?

我:建立索引的时候一般要考虑字段的使用频率,经常作为查询条件的字段比较适合索引。当然也不能过度建立索引,因为索引也是占据内存的,而且修改表会导致索引更新,所以在建立索引的时候也要考虑表结构。

在一些场合使用联合索引是比较好的效果,比如我们可以建立一个(学校-班级-ID)的联合索引,这样会比建立三个索引效果好,但是如果我们只使用其中一个索引ID不会走联合索引,会导致全表扫描,所以要分业务情况。使用联合索引时需要注意顺序,尽量把区分度大的索引放在前面。

面试官:为什么建立联合索引?(区分度大的索引放在前面)

我:在联合索引使用中,如果想要命中索引需要按照建立索引时的字段顺序挨个使用,否则无法命中索引。联合索引中有个最左匹配原则,当我们建立联合索引(A,B,C),实际上已经建立了(A)、(A,B)、(A,B,C)三个联合索引。

比如我们上面说的(学校-班级-ID)联合索引,b+树是按照从左到右的顺序来建立搜索树的,b+树优先比较学校来确定下一步的搜索方向,如果还未达到条件则继续执行搜索。如果只有学校字段,班级字段缺失,只能找到这个学校的所有字段,然后再匹配相应ID的学生,此种情况无法用到联合索引。

面试官:如何判断创建的索引是否使用到,或者说如何分析Sql语句?

我:我一般都是通过explain命令来查看语句的执行计划,使用explain关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

面试官:简单说一下Explain的字段?

我:(下面是相关字段,没必要全说,说熟悉的即可)

  • id:select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序
  • select_type:查询的类型,主要是用于区别普通查询、联合查询、子查询等的复杂查询。
  • table:指的就是当前执行的表。
  • type:type所显示的是查询使用了哪种类型。查询性能从最好到最差依次是:system > const > eq_ref > ref > range > index > all,一般来说,得保证查询至少达到range级别,最好能达到ref。
  • possible_keys 和 key:possible_keys 显示可能应用在这张表中的索引,一个或多个。查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用。key则表示实际使用的索引,如果为NULL,则没有使用索引。(可能原因包括没有建立索引或索引失效)。
  • key_len:表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度,在不损失精确性的情况下,长度越短越好。
  • ref:显示索引的那一列被使用了,如果可能的话,最好是一个常数。哪些列或常量被用于查找索引列上的值。
  • rows:根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数,也就是说,用的越少越好
  • Extra:包含不适合在其他列中显式但十分重要的额外信息。

面试官:什么情况下针对列创建了索引,查询的时候却没有使用?

我:比如索引列参与了数学运算或者函数。字符串like时的左边是通配符,类似于"%aa"这种,当mysql分析全表扫描比使用索引快的时候不使用索引。

面试官:那你知道在MySQL 5.6中,对索引做了哪些优化吗?

我:好像知道有个索引下推,默认是开启的。

官方文档中给的例子和解释如下:

SELECT * FROM people WHERE zipcode=‘95054’ 
AND lastname LIKE ‘%etrunia%’ AND address LIKE ‘%Main Street%’

people表中(zipcode,lastname,firstname)构成一个索引,如果没有使用索引下推技术,则MySQL会通过zipcode='95054’从存储引擎中查询对应的数据,返回到MySQL服务端,然后MySQL服务端基于lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断数据是否符合条件。如果使用了索引下推技术,则MYSQL首先会返回符合zipcode='95054’的索引,然后根据lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断索引是否符合条件。如果符合条件,则根据该索引来定位对应的数据,如果不符合,则直接reject掉。有了索引下推优化,可以在有like条件查询的情况下,减少回表次数。

面试官:MySql的架构流程了解吗?

我:(听见这种问题我就拿起笔来边说边画),客户端会先通过连接器连接,然后查询缓存中是否有我们想要的数据,即是否缓存命中。命中则直接返回数据,否则进入分析器和优化器,分析Sql语句和优化Sql语句,然后执行器选择相应的引擎执行。 我以为我对MySQL很了解,直到我面试了字节跳动-鸿蒙开发者社区

面试官:说说数据库的事务~

我:事务是一系列的操作,他们要符合ACID特性。

  • 原子性(Atomicity):事务必须是原子工作单元,对于数据修改,要么全都执行,要么全部不执行。
  • 一致性(Consistency):系统(数据库)总是从一个一致性的状态转移到另一个一致性的状态,不会存在中间状态。
  • 隔离性(Isolation):一个事务在完全提交之前,对其他事务是不可见的。
  • 持久性(Durability):一旦事务提交,那么就永远是这样子了,哪怕系统崩溃也不会影响到这个事务的结果。

面试官:事务是否存在问题呢在并发情况下?

我:事务在并发下存在脏读、不可重复读、虚读等问题。

  • 脏读:事务A读取到事务B未提交的数据,结果事务B回滚了,造成错误。
  • 不可重复读:事务A执行中,读取数据num=10,此时事务B执行完成并提交,修改了num=11。事务A再读取num为11,这种情况叫做不可重复读。
  • 虚读:事务A读取了一个范围的数据(比如10<NUM<20),读取到3条,结果事务B插入了一条数据成功提交,事务A读取到这个范围变成4条,即虚读。< p>

不可重复读的重点是修改,同样的条件,你读取过的数据,再次读取出来发现值不一样;(主要在于update和delete)幻读的重点在于新增或者删除,同样的条件,第 1 次和第 2 次读出来的记录数不一样。(主要在于insert)

面试官:如何解决这些问题呢?

我:为了解决以上事务并发时出现的一系列问题,就需要设置事务的隔离级别。隔离级别就是多个线程开启各自事务操作数据库中数据时,数据库系统要负责隔离操作,以保证各个线程在获取数据时的准确性。

MySql中定义了4中隔离级别:

  • 未提交读(READ UNCOMMITTED):事务A可以读取到事务B未提交的数据。最低级别,会造成脏读的情况。
  • 已提交读(READ COMMITTED):事务A只能读取到事务B已经提交的数据,解决了脏读的问题,但是存在不可重复读和虚读的问题。
  • 可重复读(REPEATABLE READ):解决了事务A在执行中前后读取数据不一致的问题,即不可重复读的问题,不会出现刚刚读取num=10,过会再读取num变为11的情况。但是还是会存在虚读的问题,即事务A读取一个范围的数据量可能会发生变化造成“幻觉”。
  • 可串行化(SERIALIZABLE):这是最高的隔离级别,可以解决上面提到的所有问题,因为他强制将所以的操作串行执行,这会导致并发性能极速下降,因此也不是很常用。

在MySQL数据库中,支持上面四种隔离级别,默认的为Repeatable read (可重复读);而在Oracle数据库中,只支持Serializable (串行化)级别和Read committed (读已提交)这两种级别,其中默认的为Read committed级别。

面试官:能不能详细介绍下MySql是如何控制隔离级别的?

我:(还要详细,上面的难道还不够?)是通过排它锁和共享锁。

  • 排它锁:被加锁的对象只能被持有锁的事务读取和修改,其他事务无法在该对象上加其他锁,也不能读取和修改该对象。
  • 共享锁:被加锁的对象可以被持锁事务读取,但是不能被修改,其他事务也可以在上面再加共享锁。

在对不论什么数据进行读操作之前要申请并获得S锁(共享锁,其他事务能够继续加共享锁,但不能加排它锁),在进行写操作之前要申请并获得X锁(排它锁,其他事务不能再获得不论什么锁)。加锁不成功,则事务进入等待状态,直到加锁成功才继续运行。

面试官:你刚刚提到了数据库的锁,简单说一下锁吧

我:对数据的操作其实只有两种,也就是读和写,而数据库在实现锁时,也会对这两种操作使用不同的锁;InnoDB 实现了标准的行级锁,也就是共享锁(Shared Lock)和排它锁(Exclusive Lock)。

共享锁(读锁),允许事务读一行数据。排它锁(写锁),允许事务删除或更新一行数据。而它们的名字也暗示着各自的另外一个特性,共享锁之间是兼容的,而互斥锁与其他任意锁都不兼容,如下图:我以为我对MySQL很了解,直到我面试了字节跳动-鸿蒙开发者社区

Lock锁根据粒度主要分为表锁、页锁和行锁。不同的存储引擎拥有的锁粒度都不同。我以为我对MySQL很了解,直到我面试了字节跳动-鸿蒙开发者社区

面试官:那悲观锁和乐观锁了解吗

我:悲观锁和乐观锁是一种思想,一种处理方式,不可和上面的锁机制(表锁,行锁,排他锁,共享锁)混为一谈。

  • 悲观锁:即对于数据的处理持悲观态度,总认为会发生并发冲突,获取和修改数据时,别人会修改数据。所以在整个数据处理过程中,需要将数据锁定。悲观锁的实现,通常依靠数据库提供的锁机制实现,比如mysql的排他锁,select .... for update来实现悲观锁。
  • 乐观锁:顾名思义,就是对数据的处理持乐观态度,乐观的认为数据一般情况下不会发生冲突,只有提交数据更新时,才会对数据是否冲突进行检测。如果发现冲突了,则返回错误信息给用户,让用户自已决定如何操作。乐观锁的实现不依靠数据库提供的锁机制,需要我们自已实现,实现方式一般是记录数据版本,一种是通过版本号,一种是通过时间戳。

给表加一个版本号或时间戳的字段,读取数据时,将版本号一同读出,数据更新时,将版本号加1。当我们提交数据更新时,判断当前的版本号与第一次读取出来的版本号是否相等。如果相等,则予以更新,否则认为数据过期,拒绝更新,让用户重新操作。

面试官:数据库一个连接多久,每次都要释放吗?(言外之意就是数据库的池化思想。)

我:数据库连接是一种有限的昂贵的资源,对数据库连接的管理能影响到整个应用程序的伸缩性和健壮性,数据库连接池正式针对这个问题提出来的。数据库连接池负责分配、管理和释放数据库连接,它允许应用程序重复使用一个现有的数据库连接,而不是重新建立一个。(数据库连接池思想和线程池思想一样)常用的三种连接池:

  • C3p0连接池:开源的JDBC连接池,实现了数据源和JNDI绑定,支持JDBC3规范和JDBC2的标准扩展。目前使用它的开源项目有Hibernate、Spring等。单线程,性能较差,适用于小型系统,代码600KB左右。
  • Dbcp连接池:由Apache开发的一个Java数据库连接池项目, Tomcat使用的连接池组件就是DBCP。预先将数据库连接放在内存中,应用程序需要建立数据库连接时直接到连接池中申请一个就行,用完再放回。单线程,并发量低,性能不好,适用于小型系统。
  • Druid连接池:Druid不仅是一个数据库连接池,还包含一个ProxyDriver、一系列内置的JDBC组件库、一个SQL Parser。

面试官:MySql数据丢失了怎么办?(持久化机制)

我:这个我记得在InnoDB中有个redo 日志是用来保证 MySQL 持久化功能的。MySql的操作是要写入到日志中 ,并不会直接刷新到硬盘上进行持久化。如果我们每一次的操作都要写入到硬盘中再更新,整个过程IO成本、查找成本都很高。

日志即起到一个中间转折的作用,当有一条记录需要更新的时候,InnoDB 引擎就会先把记录写到 redo log(粉板)里面,并更新内存,这个时候更新就算完成了。同时,InnoDB 引擎会在适当的时候,将这个操作记录更新到磁盘里面,而这个更新往往是在系统比较空闲的时候做。

面试官:今天的面试先到这儿吧,你的MySQL掌握的还不错,明天有时间吗?进行下一轮面试。

分类
收藏
回复
举报
回复
    相关推荐