MySQL 高频面试题,都在这了

[复制链接]
前言

本文首要受众为开辟职员,所以不触及到MySQL的办事摆设等操纵,且内容较多,大师预备好耐心和瓜子矿泉水.
前一阵系统的进修了一下MySQL,也有一些现实操纵经历,偶然看到一篇和MySQL相关的口试文章,发现其中的一些题目自己也回答欠好,虽然常识点大部分都晓得,可是没法将常识串联起来.
是以决议搞一个MySQL灵魂100问,试着用回答题目标方式,让自己对常识点的了解加倍深入一点.
此文不会事无巨细的从select的用法起头讲授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)
这就是上面所说的破例情况了,这个隔离级别下,其他事务可以看到本事务没有提交的部分点窜.是以会形成脏读的题目(读取到了其他事务未提交的部分,而以后该事务停止了回滚).
这个级此外性能没有充足大的上风,可是又有很多的题目,是以很少利用.
    已提交读(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 additional 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的默许存储引擎.
    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. 超大分页怎样处置?
超大的分页一般从两个偏向上来处理.
    数据库层面,这也是我们首要集合关注的(虽然见效没那末大),类似于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泄露且持续被人恶意进犯.
处理超大分页,实在主如果靠缓存,可猜测性的提早查到内容,缓存至redis等k-V数据库中,间接返回即可.
在阿里巴巴《Java开辟手册》中,对超大分页的处理法子是类似于上面提到的第一种.
5. 关心过营业系统里面的sql耗时吗?统计过慢查询吗?对慢查询都怎样优化过?
在营业系统中,除了利用主键停止的查询,其他的我城市在测试库上测试其耗时,慢查询的统计首要由运维在做,会定期将营业中的慢查询反应给我们.
慢查询的优化首先要搞大白慢的缘由是什么? 是查询条件没有射中索引?是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中的#和$有什么区分?
乱入了一个希奇的题目.....我只是想零丁记录一下这个题目,由于出现频次太高了.
会将传入的内容当做字符串,而$会间接将传入值拼接在sql语句中.
所以#可以在一定水平上防备sql注入进犯.
来历:掘金
作者:呼延十
原文:[灵魂拷问]MySQL口试高频一百问(工程师偏向)
温馨提示:
好向圈www.kuaixunai.com是各行业经验分享交流社区,你可以在这里发布交流经验,也可以发布需求与服务,经验圈子里面禁止带推广链接、联系方式、违法词等,违规将封禁账号,相关产品信息将永久不予以通过,同时有需要可以发布在自己的免费建站官网里面或者广告圈, 下载好向圈APP可以加入各行业交流群 本文不代表好向圈的观点和立场,如有侵权请下载好向圈APP联系在线客服进行核实处理。
审核说明:好向圈社区鼓励原创内容发布,如果有从别的地方拷贝复制将不予以通过,原创优质内容搜索引擎会100%收录,运营人员将严格按照上述情况进行审核,望告知!
回复

使用道具 举报

没找到任何评论,期待你打破沉寂

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

24小时热文