最常问的MySQL面试题集合

　　最常问的MySQL面试题集合

　　除了基础题部分，本文还收集整理的MySQL面试题还包括如下知识点或题型：

　　问题1：char、varchar的区别是什么？

　　varchar是变长而char的长度是固定的。如果你的内容是固定大小的，你会得到更好的性能。

　　问题2: TRUNCATE和DELETE的区别是什么？

　　DELETE命令从一个表中删除某一行，或多行，TRUNCATE命令永久地从表中删除每一行。

　　问题3：什么是触发器，MySQL中都有哪些触发器？

　　触发器是指一段代码，当触发某个事件时，自动执行这些代码。在MySQL数据库中有如下六种触发器：

　　问题4：FLOAT和DOUBLE的区别是什么？

　　问题5：如何在MySQL种获取当前日期？

　　SELECTCURRENT_DATE();

　　问题6：如何查询第n高的工资？

　　SELECTDISTINCT(salary)fromemployeeORDERBYsalaryDESCLIMITn-1,1

　　问题7：请写出下面MySQL数据类型表达的意义（int(0)、char(16)、varchar(16)、datetime、text）

　　知识点分析

　　此题考察的是MySQL数据类型。MySQL数据类型属于MySQL数据库基础，由此延伸出的知识点还包括如下内容：

　　数据类型考点：

　　使用策略：

　　对于经常变更的数据来说，CHAR比VARCHAR更好，因为CHAR不容易产生碎片。

　　对于非常短的列，CHAR比VARCHAR在存储空间上更有效率。

　　使用时要注意只分配需要的空间，更长的列排序时会消耗更多内存。

　　尽量避免使用TEXT/BLOB类型，查询时会使用临时表，导致严重的性能开销。

　　答：int(0)表示数据是INT类型，长度是0、char(16)表示固定长度字符串，长度为16、varchar(16)表示可变长度字符串，长度为16、datetime表示时间类型、text表示字符串类型，能存储大字符串，最多存储65535字节数据）

　　MySQL基础操作：

　　常见操作

　　MySQL的连接和关闭：mysql -u -p -h -P

　　-u：指定用户名

　　-p：指定密码

　　-h：主机

　　-P：端口

　　进入MySQL命令行后：G、c、q、s、h、d

　　G：打印结果垂直显示

　　c：取消当前MySQL命令

　　q：退出MySQL连接

　　s：显示服务器状态

　　h：帮助信息

　　d：改变执行符

　　MySQL存储引擎：

　　1、InnoDB存储引擎，

　　2、MyISAM存储引擎，

　　3、其他表引擎，

　　Archive、Blackhole、CSV、Memory

　　使用策略

　　在大多数场景下建议使用InnoDB存储引擎。

　　MySQL锁机制

　　表锁是日常开发中的常见问题，因此也是面试当中最常见的考察点，当多个查询同一时刻进行数据修改时，就会产生并发控制的问题。共享锁和排他锁，就是读锁和写锁。

　　锁的粒度MySQL事务处理存储过程

　　使用策略

　　触发器

　　提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程。

　　使用场景

　　问题8：请说明InnoDB和MyISAM的区别

　　问题9：innodb引擎的特性

　　问题10：请列举3个以上表引擎

　　InnoDB、MyISAM、Memory

　　问题11：请说明varchar和text的区别

　　问题11：varchar(50)中50的含义

　　最多存放50个字符，varchar(50)和(200)存储hello所占空间一样，但后者在排序时会消耗更多内存，因为order by col采用fixed_length计算col长度(memory引擎也一样)。

　　问题12：int(20)中20的含义

　　是指显示字符的长度，不影响内部存储，只是当定义了ZEROFILL时，前面补多少个 0

　　问题13：简单描述MySQL中，索引，主键，唯一索引，联合索引的区别，对数据库的性能有什么影响？

　　知识点分析

　　此真题主要考察的是MySQL索引的基础和类型，由此延伸出的知识点还包括如下内容：

　　下面我们就来将这些知识一网打尽

　　索引的基础

　　创建索引的语法：

　　索引对性能的影响：

　　索引的使用场景：

　　索引的类型：

　　索引很多种类型，是在MySQL的存储引擎实现的。

　　索引的区别：

　　-一个表只能有一个主键索引，但是可以有多个唯一索引。

　　MySQL索引的创建原则

　　MySQL索引的注意事项

　　1、联合索引遵循前缀原则

　　KEY(a,b,c)

　　WHEREa=1ANDb=2ANDc=3

　　WHEREa=1ANDb=2

　　WHEREa=1

　　#以上SQL语句可以用到索引

　　WHEREb=2ANDc=3

　　WHEREa=1ANDc=3

　　#以上SQL语句用不到索引

　　2、LIKE查询，%不能在前

　　WHEREnameLIKE"%wang%"

　　#以上语句用不到索引，可以用外部的ElasticSearch、Lucene等全文搜索引擎替代。

　　3、列值为空（NULL）时是可以使用索引的，但MySQL难以优化引用了可空列的查询,它会使索引、索引统计和值更加复杂。可空列需要更多的储存空间，还需要在MySQL内部进行特殊处理。

　　4、如果MySQL估计使用索引比全表扫描更慢，会放弃使用索引，例如：

　　表中只有100条数据左右。对于SQL语句WHERE id > 1 AND id

　　5、如果关键词or前面的条件中的列有索引，后面的没有，所有列的索引都不会被用到。

　　6、列类型是字符串，查询时一定要给值加引号，否则索引失效，例如：

　　列name varchar(16)，存储了字符串"100"

　　WHERE name = 100;

　　以上SQL语句能搜到，但无法用到索引。

　　MySQL索引的原理

　　注：

　　B+树是一种树数据结构，是一个n叉排序树，每个节点通常有多个孩子，一棵B+树包含根节点、内部节点和叶子节点。根节点可能是一个叶子节点，也可能是一个包含两个或两个以上孩子节点的节点。

　　B+树通常用于数据库和操作系统的文件系统中。NTFS, ReiserFS, NSS, XFS, JFS, ReFS 和BFS等文件系统都在使用B+树作为元数据索引。B+树的特点是能够保持数据稳定有序，其插入与修改拥有较稳定的对数时间复杂度。B+树元素自底向上插入。

　　InnoDB通过主键聚簇数据，如果没有定义主键，会选择一个唯一的非空索引代替，如果没有这样的索引，会隐式定义个主键作为聚簇索引。

　　下图形象说明了聚簇索引表(InnoDB)和普通的堆组织表(MyISAM)的区别：

　　最常问的MySQL面试题三——每个开发人员都应该知道

　　对于普通的堆组织表来说（右图），表数据和索引是分别存储的，主键索引和二级索引存储上没有任何区别。

　　而对于聚簇索引表来说（左图），表数据是和主键一起存储的，主键索引的叶结点存储行数据，二级索引的叶结点存储行的主键值。

　　聚簇索引表最大限度地提高了I/O密集型应用的性能，但它也有以下几个限制：

　　二级索引的叶节点存储的是主键值，而不是行指针，这是为了减少当出现行移动或数据页分裂时二级索引的维护工作，但会让二级索引占用更多的空间。

　　解题方法

　　在一些MySQL索引基础考题中，我们可以轻松的通过索引基础和类型来解决此类问题，对于一些索引创建注意事项方面的考点，我们可以通过索引创建原则和注意事项来解决。

　　问题14：创建MySQL联合索引应该注意什么？

　　需遵循前缀原则

　　问题15：列值为NULL时，查询是否会用到索引？

　　在MySQL里NULL值的列也是走索引的。当然，如果计划对列进行索引，就要尽量避免把它设置为可空，MySQL难以优化引用了可空列的查询,它会使索引、索引统计和值更加复杂。

　　问题16：以下语句是否会应用索引：SELECT FROM users WHERE YEAR(adddate)

　　不会，因为只要列涉及到运算，MySQL就不会使用索引。

　　问题17：MyISAM索引实现？

　　MyISAM存储引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。MyISAM的索引方式也叫做非聚簇索引的，之所以这么称呼是为了与InnoDB的聚簇索引区分。

　　问题17：MyISAM索引与InnoDB索引的区别？

　　问题18：以下三条sql 如何建索引，只建一条怎么建？

　　WHEREa=1ANDb=1

　　WHEREb=1

　　WHEREb=1ORDERBYtimeDESC

　　以顺序b,a,time建立联合索引，CREATE INDEX table1_b_a_time ON index_test01(b,a,time)。因为最新MySQL版本会优化WHERE子句后面的列顺序，以匹配联合索引顺序。

　　问题19：有A(id,sex,par,c1,c2),B(id,age,c1,c2)两张表，其中A.id与B.id关联，现在要求写出一条SQL语句，将B中age>50的记录的c1,c2更新到A表中同一记录中的c1,c2字段中

　　考点分析

　　这道题主要考察的是MySQL的关联UPDATE语句

　　延伸考点：

　　针对刚才这道题，答案可以是如下两种形式的写法：UPDATEA,BSETA.c1=B.c1,A.c2=B.c2WHEREA.id=B.id

　　UPDATEAINNERJOINBONA.id=B.idSETA.c1=B.c1,A.c2=B.c2

　　再加上B中age>50的条件：

　　UPDATEA,BsetA.c1=B.c1,A.c2=B.c2WHEREA.id=B.idandB.age>50;

　　UPDATEAINNERJOINBONA.id=B.idsetA.c1=B.c1,A.c2=B.c2WHEREB.age>50

　　MySQL的关联查询语句

　　六种关联查询

　　SELECT*FROMA,B(,C)或者

　　SELECT*FROMACROSSJOINB(CROSSJOINC)

　　#没有任何关联条件，结果是笛卡尔积，结果集会很大，没有意义，很少使用

　　内连接（INNERJOIN）

　　SELECT*FROMA,BWHEREA.id=B.id或者

　　SELECT*FROMAINNERJOINBONA.id=B.id

　　多表中同时符合某种条件的数据记录的集合，INNERJOIN可以缩写为JOIN

　　内连接分为三类

　　外连接（LEFT JOIN/RIGHT JOIN）

　　联合查询（UNION与UNION ALL）

　　SELECT*FROMAUNIONSELECT*FROMBUNION...

　　全连接（FULL JOIN）

　　SELECT*FROMALEFTJOINBONA.id=B.idUNION

　　SELECT*FROMARIGHTJOINBONA.id=B.id

　　嵌套查询

　　用一条SQL语句得结果作为另外一条SQL语句得条件，效率不好把握

　　SELECT * FROM A WHERE id IN (SELECT id FROM B)

　　解题方法

　　根据考题要搞清楚表的结果和多表之间的关系，根据想要的结果思考使用那种关联方式，通常把要查询的列先写出来，然后分析这些列都属于哪些表，才考虑使用关联查询

　　问题20：

　　为了记录足球比赛的结果，设计表如下：

　　team：参赛队伍表

　　match：赛程表

　　其中，match赛程表中的hostTeamID与guestTeamID都和team表中的teamID关联，查询2006-6-1到2006-7-1之间举行的所有比赛，并且用以下形式列出：拜仁 2:0 不莱梅 2006-6-21

　　首先列出需要查询的列：其次列出结果列：

　　初步写一个基础的SQL：

　　SELECThostTeamID,matchResult,matchTimeguestTeamIDfrommatchwherematchTimebetween"2006-6-1"and"2006-7-1";

　　通过外键联表，完成最终SQL：

　　selectt1.teamName,m.matchResult,t2.teamName,m.matchTimefrommatchasmleftjointeamast1onm.hostTeamID=t1.teamID,leftjointeamt2onm.guestTeamID=t2.guestTeamIDwherem.matchTimebetween"2006-6-1"and"2006-7-1"

　　问题21：UNION与UNION ALL的区别？

　　问题22：一个6亿的表a，一个3亿的表b，通过外键tid关联，你如何最快的查询出满足条件的第50000到第50200中的这200条数据记录。

　　select*froma,bwherea.tid=b.idanda.tid>50000limit200;

　　select*fromb,(selecttidfromalimit50000,200)awhereb.id=a.tid;

　　问题23：拷贝表( 拷贝数据, 源表名：a 目标表名：b)

　　insertintob(a,b,c)selectd,e,ffroma;

　　问题24：Student(S#,Sname,Sage,Ssex) 学生表 Course(C#,Cname,T#) 课程表 SC(S#,C#,score) 成绩表 Teacher(T#,Tname) 教师表 查询没学过“叶平”老师课的同学的学号、姓名

　　selectStudent.S#,Student.Sname

　　fromStudent

　　whereS#notin(selectdistinct(SC.S#)fromSC,Course,TeacherwhereSC.C#=Course.C#andTeacher.T#=Course.T#andTeacher.Tname=’叶平’);

　　问题25：随机取出10条数据

　　SELECT*FROMusersWHEREid>=((SELECTMAX(id)FROMusers)-(SELECTMIN(id)FROMusers))*RAND()+(SELECTMIN(id)FROMusers)LIMIT10

　　#此方法效率比直接用SELECT*FROMusersorderbyrand()LIMIT10高很多

　　问题26：请简述项目中优化SQL语句执行效率的方法，从哪些方面，SQL语句性能如何分析？

　　考点分析：

　　这道题主要考察的是查找分析SQL语句查询速度慢的方法

　　延伸考点：

　　如何查找查询速度慢的原因

　　记录慢查询日志，分析查询日志，不要直接打开慢查询日志进行分析，这样比较浪费时间和精力，可以使用pt-query-digest工具进行分析

　　使用show profilesetprofiling=1;开启，服务器上所有执行语句会记录执行时间，存到临时表中

　　showprofiles

　　showprofileforquery临时表ID

　　使用show status

　　show status会返回一些计数器，show global status会查看所有服务器级别的所有计数

　　有时根据这些计数，可以推测出哪些操作代价较高或者消耗时间多

　　show processlist

　　观察是否有大量线程处于不正常的状态或特征

　　

　　最常问的MySQL面试题五——每个开发人员都应该知道

　　使用explain

　　分析单条SQL语句

　　

　　优化查询过程中的数据访问

　　优化长难的查询语句

　　优化特定类型的查询语句

　　优化关联查询

　　优化子查询

　　优化LIMIT分页

　　优化UNION查询

　　优化WHERE子句

　　解题方法

　　对于此类考题，先说明如何定位低效SQL语句，然后根据SQL语句可能低效的原因做排查，先从索引着手，如果索引没有问题，考虑以上几个方面，数据访问的问题，长难查询句的问题还是一些特定类型优化的问题，逐一回答。

　　SQL语句优化的一些方法？

　　selectidfromtwherenumisnull可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：selectidfromtwherenum=

　　selectidfromtwherenum=10ornum=20可以这样查询：selectidfromtwherenum=10unionallselectidfromtwherenum=20

　　selectidfromtwherenumin(1,2,3)对于连续的数值，能用between就不要用in了：selectidfromtwherenumbetween1and3

　　selectidfromtwherenum=@num可以改为强制查询使用索引：selectidfromtwith(index(索引名))wherenum=@num

　　selectidfromtwherenum/2=100应改为:selectidfromtwherenum=100*2

　　selectidfromtwheresubstring(name,1,3)=’abc’，name以abc开头的id应改为:

　　selectidfromtwherenamelike‘abc%’

　　收集整理自网络

　　

　　最后给大家分享我写的SQL两件套：《SQL基础知识第二版》和《SQL高级知识第二版》的PDF电子版。里面有各个语法的解释、大量的实例讲解和批注等等，非常通俗易懂，方便大家跟着一起来实操。

　　有需要的读者可以下载学习，在下面的公众号「数据前线」(非本号)后台回复关键字：SQL，就行数据前线

　　

　　——End——

　　<p style="margin-right: 16px;margin-left: 16px;font-family: PingFangSC-Light;letter-spacing: 0.544px;white-space: normal;line-height: 2em;">后台回复关键字：1024，获取一份精心整理的技术干货

　　后台回复关键字：进群，带你进入高手如云的交流群。<br />推荐阅读一千行 MySQL 学习笔记<br />SQL自定义排序<br />SQL 性能优化梳理<br />一行SQL代码能做什么？<br />安利几款我常用的数据软件

</p>