MySQL 面试题 011 - 020

011、什么是快照读和当前读?

不加锁的 select 操作就是快照读(即不加锁的非阻塞读),它读取的是事务开始后首次快照读的版本,有可能不是最新版本,基于 MVCC 实现,避免了加锁操作,降低了开销。但快照读有一个前提,隔离级别不能是SERIALIZABLE(可串行化),否则也会成为当前读。

像 update,delete,insert,select lock in share mode(共享锁),select for update 这些操作都是当前读,读取的是记录的最新版本,会对读取的记录进行加锁,从而保证其他并发事务不能修改当前记录。

012、聚集索引与非聚集索引?

聚集索引是指索引结构和数据存放在一起的索引,聚集索引的查询速度非常快,定位到索引的节点,就相当于定位到了数据,主键索引属于聚集索引。聚集索引也有缺点,必须依赖于有序的数据,并且更新代价大。

非聚集索引指的是索引结构和数据分开存放的索引,唯一索引,普通索引,前缀索引都属于非聚集索引。

下图的辅助索引指的就是唯一索引,普通索引,前缀索引等。

image-20210301184323622

image-20210301184456173

MyISAM和InnoDB实现B+树索引方式的区别?

这道题就是把上图用自己的话描述一遍,参考答案如下:

MyISAM

B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候,首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其 data 域的值,然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。

InnoDB

其数据文件本身就是索引文件。相比MyISAM,索引文件和数据文件是分离的,其表数据文件本身就是按B+Tree组织的一个索引结构,树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主键索引。这被称为“聚簇索引(或聚集索引)”,而其余的索引都作为辅助索引,辅助索引的data域存储相应记录主键的值而不是地址,这也是和MyISAM不同的地方。在根据主键索引搜索时,直接找到key所在的节点即可取出数据;在根据辅助索引查找时,则需要先取出主键的值,再走一遍主键索引。 因此,在设计表的时候,不建议使用过长的字段作为主键,也不建议使用非单调的字段作为主键,这样会造成主索引频繁分裂。

013、索引都有哪几种?

主键索引(Primary Key):一张数据表只能有一个主键索引,并且值不能为 null,不可重复。

唯一索引(Unique Key):唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据,但是允许数据为 NULL,一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性,而不是为了查询效率。

普通索引(Index):普通索引的唯一作用就是为了快速查询数据,一张表允许创建多个普通索引,并允许数据重复和 NULL。

全文索引(Full Text) :全文索引主要是为了检索大文本数据中的关键字的信息,是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引,5.6 之后 InnoDB 也支持了全文索引。

联合索引

014、联合索引最左匹配原则?

下图的第三行就是一个联合索引。

image-20210301194222137

image-20210301194612059

015、索引是建立的越多越好吗?

  • 数据量小的时候全表查询和索引查询效率区别不大,但建立索引会增加额外的开销,所以数据量小的时候没必要用索引。
  • 数据变更需要维护索引,索引太多意味着更高的维护成本。
  • 索引是需要占用空间的,索引越多意味着需要越大的空间。

016、InnoDB 在 RR 隔离级别下是如何避免幻读的?

InnoDB 存储引擎在 RR 下使用的是 Next-Key锁(行锁 + Gap锁)

Gap锁就是间隙锁,间隙锁会锁定一个范围,在这个范围内的所有 insert 操作都会被阻塞,但其他操作不会。

对主键索引或者唯一索引会用 Gap 锁吗?

  • 如果 where 条件全部命中,则不会使用 Gap 锁,只会加行锁。
  • 如果 where 条件部分命中或者全未命中则会同时使用 Gap 锁和行锁。

Gap锁一般情况下用在哪里?

Gap锁通常用在普通索引(非唯一索引)或者不走索引的当前读中,如下图:

image-20210301222905827

image-20210301224547129

变相问答:如下图,如果进行 where c = 10 的操作,除了会对这一行加行锁外,还会有什么操作?

image-20210301174414051

答:还会在该行记录前后加上间隙锁,因为只有行锁的话是无法阻止另一个 c = 10 的数据的加入的,加上间隙锁就可以阻塞 insert 操作,防止幻读。

017、索引优化需要注意?

  • 经常被查询且区分度高的列做索引

  • 联合索引最左匹配原则

    image-20210301232304783

  • 回盘排序

    排序列(order by)和条件列建立一个联合索引

  • 覆盖索引

    如果一个索引包含(或者说覆盖)所有需要查询的字段的值,我们就称之为“覆盖索引”。我们知道InnoDB存储引擎中,如果不是主键索引,叶子节点存储的是主键值+当前索引值。最终还是要“回表”,也就是要通过主键再查找一次,这样就会比较慢,覆盖索引就是把要查询出的列和索引是对应的,不做回表操作!一般在联合索引中用的比较多。

  • 小表驱动大表

018、MySQL 为表添加索引的写法?

1.添加PRIMARY KEY(主键索引)

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 

2.添加UNIQUE(唯一索引)

ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 

3.添加INDEX(普通索引)

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4.添加FULLTEXT(全文索引)

ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 

5.添加联合索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

019、关系型数据库的六大范式?

第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。一般说来,数据库只需满足第三范式(3NF)就行了。

1NF:字段不可再分

2NF:在 1NF 的基础上,再满足有主键,非主键字段依赖主键

3NF:在 2NF 的基础上,再满足非主键字段不能相互依赖

020、优化思路?

在数据库设计层面,要允许适当的数据冗余。

image-20210313202801299

然后就是进行索引优化,索引优化需要注意以下一些小细节:

  • 尽量使用主键查询而不是其他索引,因为主键查询不会有回表操作

  • 如果使用到排序的话,要让排序列(order by)和条件列建立一个联合索引,这样子就不用进行回表操作了

  • 如果要查询的字段不是很多的话,尽量考虑覆盖索引,同样可以减少回表次数

  • 在创建联合索引的时候要遵循最左匹配原则

  • 要使用经常被查询且数据区分度高的列做索引,对于经常修改且数据区分度不高的列不宜建立索引,索引列尽量不要为空

  • 索引不是越多越好,毕竟维护索引也是需要耗费时间和空间的,对于单表来说尽量控制在 5 个以内;联合索引所包含的字段也不要超过 5 个

  • 在进行关联查询的时候尽量使用小表驱动大表

  • 使用索引列进行查询的时候尽量不要使用表达式,把计算放到业务层面,不要放在数据库层面面

    select id from actor where id = 4;
    select id from actor where id + 1 = 5;
    
  • 不要过早优化,一般是碰到问题的时候才进行优化

什么是回表?

使用普通索引、唯一索引查询的时候,直接查出来的不是数据,而是主键值,需要根据主键值回表再次查询才能查到具体数据。

什么是索引下推?

在开始之前先先准备一张用户表(user),其中主要几个字段有:id、name、age、address。建立联合索引(name,age)。

假设有一个需求,要求匹配姓名第一个为陈的所有用户,sql语句如下:

SELECT * from user where  name like '陈%'

根据 “最左前缀” 的原则,这里使用了联合索引(name,age)进行了查询,性能要比全表扫描肯定要高。

问题来了,如果有其他的条件呢?假设又有一个需求,要求匹配姓名第一个字为陈,年龄为20岁的用户,此时的sql语句如下:

SELECT * from user where  name like '陈%' and age=20

不使用索引下推的话,会先使用联合索引查出符合条件的 name,然后拿着查出来的主键值回表查询所有数据再过滤掉和 age 不匹配的数据;使用索引下推的会在使用联合索引查询的时候直接过滤掉不符合条件的 age,可以有效地减少回表次数,大大提升了查询效率。

image-20210313204237050

已标记关键词 清除标记
相关推荐
<p> <span style="font-size:14px;color:#E53333;">限时福利1:</span><span style="font-size:14px;">购课进答疑群专享柳峰(刘运强)老师答疑服务</span> </p> <p> <br /> </p> <p> <br /> </p> <p> <span style="font-size:14px;"></span> </p> <p> <span style="font-size:14px;color:#337FE5;"><strong>为什么需要掌握高性能的MySQL实战?</strong></span> </p> <p> <span><span style="font-size:14px;"><br /> </span></span> <span style="font-size:14px;">由于互联网产品用户量大、高并发请求场景多,因此对MySQL的性能、可用性、扩展性都提出了很高的要求。使用MySQL解决大量数据以及高并发请求已经是程序员的必备技能,也是衡量一个程序员能力和薪资的标准之一。</span> </p> <p> <br /> </p> <p> <span style="font-size:14px;">为了让大家快速系统了解高性能MySQL核心知识全貌,我为你总结了</span><span style="font-size:14px;">「高性能 MySQL 知识框架图」</span><span style="font-size:14px;">,帮你梳理学习重点,建议收藏!</span> </p> <p> <br /> </p> <p> <img alt="" src="https://img-bss.csdnimg.cn/202006031401338860.png" /> </p> <p> <br /> </p> <p> <span style="font-size:14px;color:#337FE5;"><strong>【课程设计】</strong></span> </p> <p> <span style="font-size:14px;"><br /> </span> </p> <p> <span style="font-size:14px;">课程分为四大篇章,将为你建立完整的 MySQL 知识体系,同时将重点讲解 MySQL 底层运行原理、数据库的性能调优、高并发、海量业务处理、面试解析等。</span> </p> <p> <span style="font-size:14px;"><br /> </span> </p> <p> <span style="font-size:14px;"></span> </p> <p style="text-align:justify;"> <span style="font-size:14px;"><strong>一、性能优化篇:</strong></span> </p> <p style="text-align:justify;"> <span style="font-size:14px;">主要包括经典 MySQL 问题剖析、索引底层原理和事务与锁机制。通过深入理解 MySQL 的索引结构 B+Tree ,学员能够从根本上弄懂为什么有些 SQL 走索引、有些不走索引,从而彻底掌握索引的使用和优化技巧,能够避开很多实战中遇到的“坑”。</span> </p> <p style="text-align:justify;"> <br /> </p> <p style="text-align:justify;"> <span style="font-size:14px;"><strong>二、MySQL 8.0新特性篇:</strong></span> </p> <p style="text-align:justify;"> <span style="font-size:14px;">主要包括窗口函数和通用表表达式。企业中的许多报表统计需求,如果不采用窗口函数,用普通的 SQL 语句是很难实现的。</span> </p> <p style="text-align:justify;"> <br /> </p> <p style="text-align:justify;"> <span style="font-size:14px;"><strong>三、高性能架构篇:</strong></span> </p> <p style="text-align:justify;"> <span style="font-size:14px;">主要包括主从复制和读写分离。在企业的生产环境中,很少采用单台MySQL节点的情况,因为一旦单个节点发生故障,整个系统都不可用,后果往往不堪设想,因此掌握高可用架构的实现是非常有必要的。</span> </p> <p style="text-align:justify;"> <br /> </p> <p style="text-align:justify;"> <span style="font-size:14px;"><strong>四、面试篇:</strong></span> </p> <p style="text-align:justify;"> <span style="font-size:14px;">程序员获得工作的第一步,就是高效的准备面试,面试篇主要从知识点回顾总结的角度出发,结合程序员面试高频MySQL问题精讲精练,帮助程序员吊打面试官,获得心仪的工作机会。</span> </p>
©️2020 CSDN 皮肤主题: 书香水墨 设计师:CSDN官方博客 返回首页