跪谢大佬链接:MySQL八股文背诵版-技术圈这篇文章是一篇高质量的MySQL面试相关文章,文章长一万五千字左右,很多同学和我说这是他看到过的总结的最好的MySQL面经,题目后面的(*)表示面试
https://jishuin.proginn.com/p/763bfbd64ca3
SQL语句主要分为哪几类 *
-
数据定义语言DDL(Data Definition Language):主要有CREATE,DROP,ALTER等对逻辑结构有操作的,包括表结构、视图和索引。
-
数据库查询语言DQL(Data Query Language):主要以SELECT为主
-
数据操纵语言DML(Data Manipulation Language):主要包括INSERT,UPDATE,DELETE
-
数据控制功能DCL(Data Control Language):主要是权限控制能操作,包括GRANT,REVOKE,COMMIT,ROLLBACK等。
SQL约束有哪些? **
-
主键约束:主键为在表中存在一列或者多列的组合,能唯一标识表中的每一行。一个表只有一个主键,并且主键约束的列不能为空。
-
外键约束:外键约束是指用于在两个表之间建立关系,需要指定引用主表的哪一列。只有主表的主键可以被从表用作外键,被约束的从表的列可以不是主键,所以创建外键约束需要先定义主表的主键,然后定义从表的外键。
-
唯一约束:确保表中的一列数据没有相同的值,一个表可以定义多个唯一约束。
-
默认约束:在插入新数据时,如果该行没有指定数据,系统将默认值赋给该行,如果没有设置没默认值,则为NULL。
-
Check约束:Check会通过逻辑表达式来判断数据的有效性,用来限制输入一列或者多列的值的范围。在列更新数据时,输入的内容必须满足Check约束的条件。
什么是子查询? **
子查询:把一个查询的结果在另一个查询中使用
子查询可以分为以下几类:
-
标量子查询:指子查询返回的是一个值,可以使用 =,>,<,>=,<=,<>等操作符对子查询标量结果进行比较,一般子查询会放在比较式的右侧。
SELECT * FROM user WHERE age = (SELECT max(age) from user) //查询年纪最大的人
-
列子查询:指子查询的结果是n行一列,一般应用于对表的某个字段进行查询返回。可以使用IN、ANY、SOME和ALL等操作符,不能直接使用。
SELECT num1 FROM table1 WHERE num1 > ANY (SELECT num2 FROM table2)
- 行子查询:指子查询返回的结果一行n列
SELECT * FROM user WHERE (age,sex) = (SELECT age,sex FROM user WHERE name="zhangsan")
- 表子查询:指子查询是n行n列的一个数据表
SELECT * FROM student WHERE (name,age,sex) IN (SELECT name,age,sex FROM class1) //在学生表中找到班级在1班的学生
了解MySQL的几种连接查询吗? ***
MySQl的连接查询主要可以分为外连接,内连接,交叉连接
-
外连接
外连接主要分为左外连接(LEFT JOIN)、右外连接(RIGHT JOIN)、全外连接。
左外连接:显示左表中所有的数据及右表中符合条件的数据,右表中不符合条件的数据为null。
-
右外连接:显示左表中所有的数据及右表中符合条件的数据,右表中不符合条件的数据为null。
-
MySQL中不支持全外连接。
-
内连接:只显示符合条件的数据
-
交叉连接:使用笛卡尔积的一种连接。
笛卡尔积,百度百科的解释:两个集合X和Y的笛卡尔积表示为X × Y,第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员 。例如:A={a,b},B={0,1,2},A × B = {(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}
mysql中in和exists的区别? **
in和exists一般用于子查询。
-
使用exists时会先进行外表查询,将查询到的每行数据带入到内表查询中看是否满足条件;使用in一般会先进行内表查询获取结果集,然后对外表查询匹配结果集,返回数据。
-
in在内表查询或者外表查询过程中都会用到索引。
-
exists仅在内表查询时会用到索引
-
一般来说,当子查询的结果集比较大,外表较小使用exist效率更高;当子查询寻得结果集较小,外表较大时,使用in效率更高。
-
对于not in和not exists,not exists效率比not in的效率高,与子查询的结果集无关,因为not in对于内外表都进行了全表扫描,没有使用到索引。not exists的子查询中可以用到表上的索引。
varchar和char的区别? ***
-
varchar表示变长,char表示长度固定。当所插入的字符超过他们的长度时,在严格模式下,会拒绝插入并提示错误信息,在一般模式下,会截取后插入。如char(5),无论插入的字符长度是多少,长度都是5,插入字符长度小于5,则用空格补充。对于varchar(5),如果插入的字符长度小于5,则存储的字符长度就是插入字符的长度,不会填充。
-
存储容量不同,对于char来说,最多能存放的字符个数为255。对于varchar,最多能存放的字符个数是65532。
-
存储速度不同,char长度固定,存储速度会比varchar快一些,但在空间上会占用额外的空间,属于一种空间换时间的策略。而varchar空间利用率会高些,但存储速度慢,属于一种时间换空间的策略。
MySQL中int(10)和char(10)和varchar(10)的区别? ***
int(10)中的10表示的是显示数据的长度,而char(10)和varchar(10)表示的是存储数据的大小。
drop、delete和truncate的区别? **
UNION和UNION ALL的区别? **
union和union all的作用都是将两个结果集合并到一起。
-
union会对结果去重并排序,union all直接直接返回合并后的结果,不去重也不进行排序。
-
union all的性能比union性能好。
什么是临时表,什么时候会使用到临时表,什么时候删除临时表? *
MySQL在执行SQL语句的时候会临时创建一些存储中间结果集的表,这种表被称为临时表,临时表只对当前连接可见,在连接关闭后,临时表会被删除并释放空间。
临时表主要分为内存临时表和磁盘临时表两种。内存临时表使用的是MEMORY存储引擎,磁盘临时表使用的是MyISAM存储引擎。
一般在以下几种情况中会使用到临时表:
-
FROM中的子查询
-
DISTINCT查询并加上ORDER BY
-
ORDER BY和GROUP BY的子句不一样时会产生临时表
-
使用UNION查询会产生临时表
大表数据查询如何进行优化? ***
-
索引优化
-
SQL语句优化
-
水平拆分
-
垂直拆分
-
建立中间表
-
使用缓存技术
-
固定长度的表访问起来更快
-
越小的列访问越快
了解慢日志查询吗?统计过慢查询吗?对慢查询如何优化? ***
慢查询一般用于记录执行时间超过某个临界值的SQL语句的日志。
相关参数:
-
slow_query_log:是否开启慢日志查询,1表示开启,0表示关闭。
-
slow_query_log_file:MySQL数据库慢查询日志存储路径。
-
long_query_time:慢查询阈值,当SQL语句查询时间大于阈值,会被记录在日志上。
-
log_queries_not_using_indexes:未使用索引的查询会被记录到慢查询日志中。
-
log_output:日志存储方式。“FILE”表示将日志存入文件。“TABLE”表示将日志存入数据库。
如何对慢查询进行优化?
-
分析语句的执行计划,查看SQL语句的索引是否命中
-
优化数据库的结构,将字段很多的表分解成多个表,或者考虑建立中间表。
-
优化LIMIT分页。
为什么要设置主键? **
主键是唯一区分表中每一行的唯一标识,如果没有主键,更新或者删除表中特定的行会很困难,因为不能唯一准确地标识某一行。
主键一般用自增ID还是UUID? **
使用自增ID的好处:
-
字段长度较uuid会小很多。
-
数据库自动编号,按顺序存放,利于检索
-
无需担心主键重复问题
使用自增ID的缺点:
-
因为是自增,在某些业务场景下,容易被其他人查到业务量。
-
发生数据迁移时,或者表合并时会非常麻烦
-
在高并发的场景下,竞争自增锁会降低数据库的吞吐能力
UUID:通用唯一标识码,UUID是基于当前时间、计数器和硬件标识等数据计算生成的。
使用UUID的优点:
-
唯一标识,不会考虑重复问题,在数据拆分、合并时也能达到全局的唯一性。
-
可以在应用层生成,提高数据库的吞吐能力。
-
无需担心业务量泄露的问题。
使用UUID的缺点:
-
因为UUID是随机生成的,所以会发生随机IO,影响插入速度,并且会造成硬盘的使用率较低。
-
UUID占用空间较大,建立的索引越多,造成的影响越大。
-
UUID之间比较大小较自增ID慢不少,影响查询速度。
最后说下结论,一般情况MySQL推荐使用自增ID。因为在MySQL的InnoDB存储引擎中,主键索引是一种聚簇索引,主键索引的B+树的叶子节点按照顺序存储了主键值及数据,如果主键索引是自增ID,只需要按顺序往后排列即可,如果是UUID,ID是随机生成的,在数据插入时会造成大量的数据移动,产生大量的内存碎片,造成插入性能的下降。
字段为什么要设置成not null? **
首先说一点,NULL和空值是不一样的,空值是不占用空间的,而NULL是占用空间的,所以字段设为NOT NULL后仍然可以插入空值。
字段设置成not null主要有以下几点原因:
-
NULL值会影响一些函数的统计,如count,遇到NULL值,这条记录不会统计在内。
-
B树不存储NULL,所以索引用不到NULL,会造成第一点中说的统计不到的问题。
-
NOT IN子查询在有NULL值的情况下返回的结果都是空值。
如何优化查询过程中的数据访问? ***
从减少数据访问方面考虑:
-
正确使用索引,尽量做到索引覆盖
-
优化SQL执行计划
从返回更少的数据方面考虑:
-
数据分页处理
-
只返回需要的字段
从减少服务器CPU开销方面考虑:
-
合理使用排序
-
减少比较的操作
-
复杂运算在客户端处理
从增加资源方面考虑:
-
客户端多进程并行访问
-
数据库并行处理
如何优化长难的查询语句? **
-
将一个大的查询分解为多个小的查询
-
分解关联查询,使缓存的效率更高
如何优化LIMIT分页? **
-
在LIMIT偏移量较大的时候,查询效率会变低,可以记录每次取出的最大ID,下次查询时可以利用ID进行查询
-
建立复合索引
如何优化UNION查询 **
如果不需要对结果集进行去重或者排序建议使用UNION ALL,会好一些。
如何优化WHERE子句 ***
-
不要在where子句中使用!=和<>进行不等于判断,这样会导致放弃索引进行全表扫描。
-
不要在where子句中使用null或空值判断,尽量设置字段为not null。
-
尽量使用union all代替or
-
在where和order by涉及的列建立索引
-
尽量减少使用in或者not in,会进行全表扫描
-
在where子句中使用参数会导致全表扫描
-
避免在where子句中对字段及进行表达式或者函数操作会导致存储引擎放弃索引进而全表扫描
SQL语句执行的很慢原因是什么? ***
-
如果SQL语句只是偶尔执行很慢,可能是执行的时候遇到了锁,也可能是redo log日志写满了,要将redo log中的数据同步到磁盘中去。
-
如果SQL语句一直都很慢,可能是字段上没有索引或者字段有索引但是没用上索引。
SQL语句的执行顺序? *
-
FROM:对SQL语句执行查询时,首先对关键字两边的表以笛卡尔积的形式执行连接,并产生一个虚表V1。虚表就是视图,数据会来自多张表的执行结果。
-
ON:对FROM连接的结果进行ON过滤,并创建虚表V2
-
JOIN:将ON过滤后的左表添加进来,并创建新的虚拟表V3
-
WHERE:对虚拟表V3进行WHERE筛选,创建虚拟表V4
-
GROUP BY:对V4中的记录进行分组操作,创建虚拟表V5
-
HAVING:对V5进行过滤,创建虚拟表V6
-
SELECT:将V6中的结果按照SELECT进行筛选,创建虚拟表V7
-
DISTINCT:对V7表中的结果进行去重操作,创建虚拟表V8,如果使用了GROUP BY子句则无需使用DISTINCT,因为分组的时候是将列中唯一的值分成一组,并且每组只返回一行记录,所以所有的记录都h是不同的。
-
ORDER BY:对V8表中的结果进行排序。