登陆

100道MySQL常见面试题总结

admin 2019-08-23 282人围观 ,发现0个评论

前语

本文首要受众为开发人员,所以不涉及到MySQL的服务布置等操作,且内容较多,咱们准备好耐性和瓜子矿泉水.

前一阵体系的学习了一下MySQL,也有一些实践操作经历,偶尔看到一篇和MySQL相关的面试文章,发现其间的一些问题自己也答复欠好,尽管常识点大部分都知道,可是无法将常识串联起来.

因而决议搞一个MySQL魂灵100问,试着用答复问题的办法,让自己对常识点的了解愈加深化一点.

此文不会事无巨细的从select的用法开端解说mysql,首要针对的是开发人员需求知道的一些MySQL的常识点,首要包括索引,事务,优化等方面,以在面试中高频的问句办法给出答案.

索引相关

关于MySQL的索引,从前进行过一次总结,文章链接在这儿 Mysql索引原理及其优化.

1. 什么是索引?

索引是一种数据结构,能够协助咱们快速的进行数据的查找.

2. 索引是个什么样的数据结构呢?

索引的数据结构和详细存储引擎的完结有关, 在MySQL中运用较多的索引有Hash索引,B+树索引等,而咱们常常运用的InnoDB存储引擎的默许索引完结为:B+树索引.

3. Hash索引和B+树一切有什么区别或许说好坏呢?

首要要知道Hash索引和B+树索引的底层完结原理:

hash索引底层便是hash表,进行查找时,调用一次hash函数就能够获取到相应的键值,之后进行回表查询取得实践数据.B+树底层完结是多路平衡查找树.关于每一次的查询都是从根节点动身,查找到叶子节点方能够取得所查键值,然后依据查询判别是否需求回表查询数据.

那么能够看出他们有以下的不同:

  • hash索引进行等值查询更快(一般状况下),可是却无法进行规模查询.

由于在hash索引中经过hash函数树立索引之后,索引的次第与原次第无法保持一致,不能支撑规模查询.而B+树的的一切节点皆遵从(左节点小于父节点,右节点大于父节点,多叉树也相似),天然支撑规模.

  • hash索引不支撑运用索引进行排序,原理同上.
  • hash索引不支撑含糊查询以及多列索引的最左前缀匹配.原理也是由于hash函数的不行猜测.AAAAAAAAB的索引没有相关性.
  • hash索引任何时分都避免不了回表查询数据,而B+树在契合某些条件(聚簇索引,掩盖索引等)的时分能够只经过索引完结查询.
  • hash索引尽管在等值查询上较快,可是不安稳.功用不行猜测,当某个键值存在许多重复的时分,发作hash磕碰,此刻功率或许极差.而B+树的查询功率比较安稳,关于一切的查询都是从根节点到叶子节点,且树的高度较低.

因而,在大多数状况下,直接挑选B+树索引能够取得安稳且较好的查询速度.而不需求运用hash索引.

4. 上面说到了B+树在满意聚簇索引和掩盖索引的时分不需求回表查询数据,什么是聚簇索引?

在B+树的索引中,叶子节点或许存储了其时的key值,也或许存储了其时的key值以及整行的数据,这便是聚簇索引和非聚簇索引. 在InnoDB中,只要主键索引是聚簇索引,假定没有主键,则挑选一个唯一键树立聚簇索引.假定没有唯一键,则隐式的生成一个键来树立聚簇索引.

当查询运用聚簇索引时,在对应的叶子节点,能够获取到整行数据,因而不用再次进行回表查询.

5. 非聚簇索引必定会回表查询吗?

不必定,这涉及到查询句子所要求的字段是否悉数射中了索引,假定悉数射中了索引,那么就不用再进行回表查询.

举个简略的比方,假定咱们在职工表的年纪上树立了索引,那么当进行select age from employee where age < 20的查询时,在索引的叶子节点上,现已包括了age信息,不会再次进行回表查询.

6. 在树立索引的时分,都有哪些需求考虑的要素呢?

树立索引的时分一般要考虑到字段的运用频率,常常作为条件进行查询的字段比较合适.假定需求树立联合索引的话,还需求考虑联合索引中的次第.此外也要考虑其他方面,比方避免过多的一切对表形成太大的压力.这些都和实践的表结构以及查询办法有关.

7. 联合索引是什么?为什么需求留意联合索引中的次第?

MySQL能够运用多个字段一同树立一个索引,叫做联合索引.在联合索引中,假定想要射中索引,需求依照树立索引时的字段次第挨个运用,不然无法射中索引.

详细原由于:

MySQL运用索引时需求索引有序,假定现在树立了"name,age,school"的联合索引,那么索引的排序为: 先依照name排序,假定name相同,则依照age排序,假定age的值也持平,则依照school进行排序.

当进行查询时,此刻索引仅仅依照name严厉有序,因而有必要首要运用name字段进行等值查询,之后关于匹配到的列而言,其依照age字段严厉有序,此刻能够运用age字段用做索引查找,,,以此类推.因而在树立联合索引的时分应该留意索引列的次第,一般状况下,将查询需求频频或许字段挑选性高的列放在前面.此外能够依据特例的查询或许表结构进行独自的调整.

8. 创立的索引有没有被运用到?或许说怎样才能够知道这条句子运转很慢的原因?

MySQL供给了explain指令来检查句子的履行计划,MySQL在履行某个句子之前,会将该句子过一遍查询优化器,之后会拿到对句子的剖析,也便是履行计划,其间包括了许多信息. 能够经过其间和索引有关的信息来剖析是否射中了索引,例如possilbe_key,key,key_len等字段,别离说明晰此句子或许会运用的索引,实践运用的索引以及运用的索引长度.

9. 那么在哪些状况下会发作针对该列创立了索引可是在查询的时分并没有运用呢?

  • 运用不等于查询,
  • 列参加了数学运算或许函数
  • 在字符串like时左面是通配符.相似于'%aaa'.
  • 当mysql剖析全表扫描比运用索引快的时分不运用索引.
  • 当运用联合索引,前面一个条件为规模查询,后边的即便契合最左前缀准则,也无法运用索引.

以上状况,MySQL无法运用索引.

事务相关

1. 什么是事务?

了解什么是事务最经典的便是转账的栗子,信任咱们也都了解,这儿就不再说一边了.

事务是一系列的操作,他们要契合ACID特性.最常见的了解便是:事务中的操作要么悉数成功,要么悉数失利.可是仅仅这样还不行的.

2. ACID是什么?能够详细说一下吗?

A=Atomicity

原子性,便是上面说的,要么悉数成功,要么悉数失利.不行能只履行一部分操作.

C=Consistency

体系(数据库)总是从一个一致性的状况转移到另一个一致性的状况,不会存在中间状况.

I=Isolation

阻隔性: 一般来说:一个事务在彻底提交之前,对其他事务是不行见的.留意前面的一般来说加了赤色,意味着有破例状况.

D=Durability

持久性,一旦事务提交,那么就永远是这姿态了,哪怕体系溃散也不会影响到这个事务的成果.

3. 一同有多个事务在进行会怎样样呢?

多事务的并发进行一般会形成以下几个问题:

  • 脏读: A事务读取到了B事务未提交的内容,而B事务后边进行了回滚.
  • 不行重复读: 当设置A事务只能读取B事务现已提交的部分,会形成在A事务内的两次查询,成果居然不相同,由于在此期间B事务进行了提交操作.
  • 幻读: A事务读取了一个规模的内容,而一同B事务在此期间刺进了一条数据.形成"错觉".

4. 怎样处理这些问题呢?MySQL的事务阻隔等级了解吗?

MySQL的四种阻隔等级如下:

  • 未提交读(READ UNCOMMITTED)

这便是上面所说的破例状况了,这个阻隔等级下,100道MySQL常见面试题总结其他事务能够看到本事务没有提交的部分修正.因而会形成脏读的问题(读取到了其他事务未提交的部分,而之后该事务进行了回滚).

这个等级的功用没有满足大的优势,可是又有许多的问题,因而很少运用.

  • 已提交读(READ COMMITTED)

其他事务只能读取到本事务现已提交的部分.这个阻隔等级有 不行重复读的问题,在同一个事务内的两次读取,拿到的成果居然不相同,由于别的一个事务对数据进行了修正.

  • REPEATABLE READ(可重复读)

可重复读阻隔等级处理了上面不行重复读的问题(看姓名也知道),可是依然有一个新问题,便是 幻读,当你读取id> 10 的数据行时,对涉及到的一切行加上了读锁,此刻破例一个事务新刺进了一条id=11的数据,由于是新刺进的,所以不会触发上面的锁的排挤,那么进行本事务进行下一次的查询时会发现有一条id=11的数据,而前次的查询操作并没有获取到,再进行刺进就会有主键抵触的问题.

  • SERIALIZABLE(可串行化)

这是最高的阻隔等级,能够处理上面说到的一切问题,由于他强制将所以的操作串行履行,这会导致并发功用极速下降,因而也不是很常用.

5. Innodb运用的是哪种阻隔等级呢?

InnoDB默许运用的是可重复读阻隔等级.

6. 对MySQL的锁了解吗?

当数据库有并发事务的时分,或许会发生数据的不一致,这时分需求一些机制来保证拜访的次第,锁机制便是这样的一个机制.

就像酒店的房间,假定咱们随意进出,就会呈现多人争夺同一个房间的状况,而在房间上装上锁,恳求到钥匙的人才能够入住而且将房间锁起来,其他人只要等他运用结束才能够再次运用.

7. MySQL都有哪些锁呢?像上面那姿态进行确认岂不是有点阻止并发功率了?

从锁的类别上来讲,有同享锁和排他锁.

同享锁: 又叫做读锁. 当用户要进行数据的读取时,对数据加上同享锁.同享锁能够一同加上多个.

排他锁: 又叫做写锁. 当用户要进行数据的写入时,对数据加上排他锁.排他锁只能够加一个,他和其他的排他锁,同享锁都相斥.

用上面的比方来说便是用户的行为有两种,一种是来看房,多个用户一同看房是能够承受的. 一种是真实的入住一晚,在这期间,不管是想入住的仍是想看房的都不能够.

锁的粒度取决于详细的存储引擎,InnoDB完结了行级锁,页级锁,表级锁.

他们的加锁开支从大巨细,并发才能也是从大到小.

表结构规划

1. 为什么要尽量设定一个主键?

主键是数据库保证数据行在整张表唯一性的保证,即便事务上本张表没有主键,也主张添加一个自添加的ID列作为主键.设定了主键之后,在后续的修正查的时分或许愈加快速以及保证操作数据规模安全.

2. 主键运用自增ID仍是UUID?

引荐运用自增ID,不要运用UUID.

由于在InnoDB存储引擎中,主键索引是作为聚簇索引存在的,也便是说,主键索引的B+树叶子节点上存储了主键索引以及悉数的数据(依照次第),假定主键索引是自增ID,那么只需求不断向后摆放即可,假定是UUID,由于到来的ID与本来的巨细不确认,会形成十分多的数据刺进,数据移动,然后导致发生许多的内存碎片,从而形成刺进功用的下降.

总归,在数据量大一些的状况下,用自增主键功用会好一些.

图片来源于《高功用MySQL》: 其间默许后缀为运用自增ID,_uuid为运用UUID为主键的测验,测验了刺进100w行和300w行的功用.

关于主键是聚簇索引,假定没有主键,InnoDB会挑选一个唯一键来作为聚簇索引,假定没有唯一键,会生成一个隐式的主键.

If you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index.

If you do not define a PRIMARY KEY for your table, MySQL picks the first UNIQUE index that has only NOT NULL columns as the primary key and InnoDB uses it as the clustered index.

3. 字段为什么要求界说为not null?

MySQL官网这样介绍:

NULL columns require add100道MySQL常见面试题总结itional space in the rowto record whether their values are NULL. For MyISAM tables, each NULL columntakes one bit extra, rounded up to the nearest byte.

null值会占用更多的字节,且会在程序中形成许多与预期不符的状况.

4. 假定要存储用户的暗码散列,应该运用什么字段进行存储?

暗码散列,盐,用户身份证号等固定长度的字符串应该运用char而不是varchar来存储,这样能够节约空间且进步检索功率.

存储引擎相关

1. MySQL支撑哪些存储引擎?

MySQL支撑多种存储引擎,比方InnoDB,MyISAM,Memory,Archive等等.在大多数的状况下,直接挑选运用InnoDB引擎都是最合适的,InnoDB也是MySQL的默许存储引擎.

  1. InnoDB和MyISAM有什么区别?
  • InnoDB支撑事物,而MyISAM不支撑事物
  • InnoDB支撑行级锁,而MyISAM支撑表级锁
  • InnoDB支撑MVCC, 而MyISAM不支撑
  • InnoDB支撑外键,而MyISAM不支撑
  • InnoDB不支撑全文索引,而MyISAM支撑。

零星问题

1. MySQL中的varchar和char有什么区别.

char是一个定长字段,假定恳求了char(10)的空间,那么不管实践存储多少内容.该字段都占用10个字符,而varchar是变长的,也便是说恳求的仅仅最大长度,占用的空间为实践字符长度+1,最终一个字符存储运用了多长的空间.

在检索功率上来讲,char > varchar,因而在运用中,假定确认某个字段的值的长度,能够运用char,不然应该尽量运用varchar.例如存储用户MD5加密后的暗码,则应该运用char.

2. varchar(10)和int(10)代表什么意义?

varchar的10代表了恳求的空间长度,也是能够存储的数据的最大长度,而int的10仅仅代表了展现的长度,缺乏10位以0填充.也便是说,int(1)和int(10)所能存储的数字巨细以及占用的空间都是相同的,仅仅在展现时依照长度展现.

3. MySQL的binlog有有几种录入格局?别离有什么区别?

有三种格局,statement,row和mixed.

  • statement方式下,记载单元为句子.即每一个sql形成的影响会记载.由于sql的履行是有上下文的,因而在保存的时分需求保存相关的信息,一同还有一些运用了函数之类的句子无法被记载仿制.
  • row等级下,记载单元为每一行的改动,基本是能够悉数记下来可是由于许多操作,会导致许多行的改动(比方alter table),因而这种方式的文件保存的信息太多,日志量太大.
  • mixed. 一种折中的计划,一般操作运用statement记载,当无法运用statement的时分运用row.

此外,新版的MySQL中对row等级也做了一些优化,当表结构发作变化的时分,会记载句子而不是逐行记载.

4.100道MySQL常见面试题总结 超大分页怎样处理?

超大的分页一般从两个方向上来处理.

  • 数据库层面,这也是咱们首要会集重视的(尽管收效没那么大),相似于select * from table where age > 20 limit 1000000,10这种查询其实也是有能够优化的地步的. 这条句子需求load1000000数据然后基本上悉数丢掉,只取10条当然比较慢. 其时咱们能够修正为select * from table where id in (select id from table where age > 20 limit 1000000,10).这样尽管也load了一百万的数据,可是由于索引掩盖,要查询的一切字段都在索引中,所以速度会很快. 一同假定ID接连的好,咱们还能够select * from table where id > 1000000 limit 10,功率也是不错的,优化的或许性有许多种,可是中心思维都相同,便是削减load的数据.
  • 从需求的视点削减这种恳求….首要是不做相似的需求(直接跳转到几百万页之后的详细某一页.只允许逐页检查或许依照给定的道路走,这样可猜测,可缓存)以及避免ID走漏且接连被人歹意进犯.

处理超大分页,其实首要是100道MySQL常见面试题总结靠缓存,可猜测性的提早查到内容,缓存至redis等k-V数据库中,直接回100道MySQL常见面试题总结来即可.

在阿里巴巴《Java开发手册》中,对超大分页的处理办法是相似于上面说到的榜首种.

5. 关怀过事务体系里边的sql耗时吗?计算过慢查询吗?对慢查询都怎样优化过三国群英传8?

在事务体系中,除了运用主键进行的查询,其他的我都会在测验库上测验其耗时,慢查询的计算首要由运维在做,会定时将事务中的慢查询反应给咱们.

慢查询的优化首要要搞了解慢的原因是什么? 是查询条件没有射中索引?是load了不需求的数据列?仍是数据量太大?

所以优化也是针对这三个方历来的,

  • 首要剖析句子,看看是否load了额定的数据,或许是查询了剩余的行而且扔掉掉了,或许是加载了许多成果中并不需求的列,对句子进行剖析以及重写.
  • 剖析句子的履行计划,然后取得其运用索引的状况,之后修正句子或许修正索引,使得句子能够尽或许的射中索引.
  • 假定对句子的优化现已无法进行,能够考虑表中的数据量是否太大,假定是的话能够进行横向或许纵向的分表.

6. 上面说到横向分表和纵向分表,能够别离举一个合适他们的比方吗?

横向分表是按行分表.假定咱们有一张用户表,主键是自增ID且一同是用户的ID.数据量较大,有1亿多条,那么此刻放在一张表里的查询作用就不太抱负.咱们能够依据主键ID进行分表,不管是按尾号分,或许按ID的区间分都是能够的. 假定依照尾号0-99分为100个表,那么每张表中的数据就仅有100w.这时的查询功率无疑是能够满意要求的.

纵向分表是按列分表.假定咱们现在有一张文章表.包括字段id-摘要-内容.而体系中的展现办法是刷新出一个列表,列表中仅包括标题和摘要,当用户点击某篇文章进入概况时才需求正文内容.此刻,假定数据量大,将内容这个很大且不常常运用的列放在一同会拖慢原表的查询速度.咱们能够将上面的表分为两张.id-摘要,id-内容.当用户点击概况,那主键再来取一次内容即可.而添加的存储量仅仅很小的主键字段.价值很小.

当然,分表其实和事务的关联度很高,在分表之前必定要做好调研以及benchmark.不要依照自己的猜测盲目操作.

7. 什么是存储进程?有哪些优缺点?

存储进程是一些预编译的SQL句子。1、愈加直白的了解:存储进程能够说是一个记载集,它是由一些T-SQL句子组成的代码块,这些T-SQL句子代码像一个办法相同完结一些功用(对单表或多表的增修正查),然后再给这个代码块取一个姓名,在用到这个功用的时分调用他就行了。2、存储进程是一个预编译的代码块,履行功率比较高,一个存储进程代替许多T_SQL句子 ,能够下降网络通讯量,进步通讯速率,能够必定程度上保证数据安全

可是,在互联网项目中,其实是不太引荐存储进程的,比较知名的便是阿里的《Java开发手册》中制止运用存储进程,我个人的了解是,在互联网项目中,迭代太快,项目的生命周期也比较短,人员活动比较于传统的项目也愈加频频,在这样的状况下,存储进程的办理确实是没有那么便利,一同,复用性也没有写在服务层那么好.

8. 说一说三个范式

榜首范式: 每个列都不能够再拆分. 第二范式: 非主键列彻底依赖于主键,而不能是依赖于主键的一部分. 第三范式: 非主键列只依赖于主键,不依赖于其他非主键.

在规划数据库结构的时分,要尽量恪守三范式,假定不恪守,有必要有满足的理由.比方功用. 事实上咱们常常会为了功用而退让数据库的规划.

9. MyBatis中的#

乱入了一个古怪的问题…..我仅仅想独自记载一下这个问题,由于呈现频率太高100道MySQL常见面试题总结了.

# 会将传入的内容作为字符串,而有什么区别?∗∗乱入了一个古怪的问题.....我仅仅想独自记载一下这个问题,由于呈现频率太高了.#会将传入的内容作为字符串,而会直接将传入值拼接在sql句子中.

所以#能够在必定程度上防备sql注入进犯.

mysql55题答案

由于头条原因不能直接发,材料现已打包整理好:

转发 !重视我 “私信” 回复关键词:【MySQL】 即可免费收取PDF!

转发 !重视我 “私信” 回复关键词:【MySQL】 即可免费收取PDF!

转发 !重视我 “私信” 回复关键词:【MySQL】 即可免费收取PDF!

请关注微信公众号
微信二维码
不容错过
Powered By Z-BlogPHP