1、SQL 中 on 条件与 where 条件的区别

• 数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。
• 在使用 left join 时，on 和 where 条件的区别如下：
  1）on 条件是在生成临时表时使用的条件，它不管 on 中的条件是否为真，都会返回左边表中的记录。
  2）where 条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有 left join 的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

tbl_a

id	sid
1	110
2	111
3	112

tbl_b

sid	sname
110	张三
110	李四
111	王五

select a.*,b.sname from tbl_a a 
left join tbl_b b on a.sid=b.sid 
where b.sname='张三';

select a.*,b.sname from tbl_l a
left join tbl_b b on a.sid=b.sid and b.sname='张三';

总结： 其实以上结果的关键原因就是 left join,right join,full join 的特殊性。

• 不管 on 上的条件是否为真都会返回 left 或 right 表中的记录，full 则具有 left 和 right 的特性的并集。
• 而 inner jion 没这个特殊性，则条件放在 on 中和 where 中，返回的结果集是相同的。

2、拉链表的原理

参照该文章内容

3、今天想截取截止到昨天的历史数据，条件怎么设置

4、数据库三范式

• 第一范式(确保每列保持原子性)

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。

第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式。

• 第二范式(确保表中的每列都和主键相关)

第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键。

• 第三范式(确保每列都和主键列直接相关,而不是间接相关)

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。

5、truncate、delete 与 drop 区别？

• 相同点
  1、truncate和不带where条件的delete、以及drop都会删除表内数据
  2、drop、truncate都是DDL语句，执行后会自动提交
• 不同点
  1、truncate和delete只删除数据不删除表结构，drop将删除表的结构被依赖的约束、触发器、索引；
  2、一般来说，执行速度：drop>truncate>delete

6、怎么取前10条数据

• oracle – rownum

select * from DM_CALL_FEE where rownum<11;

• mysql limit

select * from user_behaviour_data limit 10;

7、存储过程

存储过程

8、什么是内连接、外连接、全连接、笛卡尔积

连接	描述
内连接（inner join）	返回满足条件的数据
外连接（left\right join）	除显示两表满足匹配关系的记录，还返回左边或者右边全部
全连接（full join）	返回左表和右表所有数据
笛卡尔积（cross join）	显示两张表所有记录一一对应，没有匹配关系进行筛选

9、什么是代理键？代理键的作用是什么？

10、主键与唯一索引的区别是什么？

11、如何给表person创建视图v_person并给user1用户赋查询权限，如何给一赋权视图v_person收回user1用户查询权限。

c_name	v1	v2
beijing	001	100
shanghai	002	90

12、编写一个函数实现对于某人年龄的精确计算，入参为某人的出生年月日（birthday）和当前日期（nowdate）

13、已知有三个表：

student(stu_no,stu_name)
course(c_no,c_name,teacher)
stu_cou(stu_no,c_no,grade)

• 找出没有选修过“张三”老师授课的所有学生姓名
• 列出A课程成绩比B课程成绩好的同学该门课成绩高的所有学生的学号
• 列出既学过A课程又学过B课程的所有学生姓名

14、当天ETL跑批数据失败怎样来处理这个问题？

15、某保险公司有A B C D E F G一共7个代理人，准备组成两个培训小组进行业务培训，其中第一小组3人，第二组4人，分组需要满足以下条件：

• F必须在第二组
• C和E至多有一个在第一组
• A和C不能同组
• 如果B在第一组，则D也必须在第一组
  问：如果A在第二组，则哪个代理人也一定在第二组，并写出理由

16、字符串分割函数 SUBSTRING_INDEX

select
    SUBSTRING_INDEX(profile,',',-1) as gender,
    count(*) as number
from user_submit
group by gender;

17、substring_index()

select 
    exam_id,
    substring_index(tag,',',1) as tag,
    substring_index(substring_index(tag,',',2),',',-1) as difficulty,
    substring_index(tag,',',-1) as duration
from
    examination_info
where duration=0;

18、char_length()、substr()、concat()

select 
	id,
	if(char_length(names)>13,concat(substr(names,1,10),'...'),names) as new_name
from orders
where char_length(names)>10;

1、什么是数据库？

使用数据结构存储数据的仓库。

2、什么是关系型数据库？

使用关系模型存储数据的数据库。

关系模型指表与表之间的关系，包含一对一、一对多、多读多。

3、什么是非关系型数据库？

使用非关系模型保存数据的数据库。例如列存储、键值对、文件形式。

4、说一说数据库三大范式？

第一范式，表中字段是原子的，不可再分；

第二范式，表中字段必须依赖主键的全部字段，不能只依赖一部分；

第三范式，表中字段必须直接依赖主键，不能存在传递依赖。

其实真正的数据库设计并没有遵循三大范式，特别是OLAP型数据库

5、说一说对DML、DDL、DCL语言的理解

DML：数据库操纵语言，例如update、insert、delete对表中数据操作的语言；

DDL：数据库定义语言，例如create、drop、alter、truncate等对数据库对象操作的语言；

DCL：数据库控制语言，例如grant、revoke、commit、rollback、savepoint对事物控制的语言。

6、delete与truncate的区别？

1）Delete是DML语言，truncate是DDL语言；

2）delete删除时写undo日志，truncate不写undo日志；

3）delete删除后可以找回数据，truncate是永久删除；

4）delete可以全删也可以删除部分数据，truncate只能全部删除数据；

5）delete删除高水位线不变，truncate删除会重置高水位线。

7、表的分类

1）堆组织表，我们最常用的一种表，分配的存储空间是不连续的，无组织的；

2）索引组织表，将表中所有字段有序存储的一张表，可以把整张表看作一个索引；关键字：

3）临时表，用来临时存储数据的表，可以用在存储过程中保存中间结果。关键字：

4）聚簇表，如果一些表有一些共同的列，则将这样一组表存储在相同的数据块中

聚簇还表示把相关的数据存储在同一个块上。分为：Hash clustered table与索引聚簇表。Hash clustered table与索引聚簇表非常相似,只有一个主要区别:聚簇键索引被一个hash函数所取代,表中的数据就是索引.

5）对象表，先定义一个对象类型，再创建一个对象类型的表

6）嵌套表，一个表作为另一个表的字段

7）外部表，使用外部文件存储数据，需要先在磁盘空间中指定一个目录存储数据文件

8、分区表的分类

范围分区、列表分区、哈希分区、复合分区（范围—列表、范围—哈希）

范围分区：每个分区给分区字段指定一个区间，最后需要创建一个maxvalue分区

列表分区：每个分区给分区字段设置一个或多个值，有时候需要创建一个default分区

哈市分区：分区的范围不可控，系统使用hash函数自动计算数据应存储在哪个分区

9、分区的优点和缺点

优点：

1）提高查询速度

2）减少IO操作

3）提高可用性，一个分区出错，不影响其他分区数据使用

4）减少恢复时间，一个分区出错，只对当前分区恢复

5）操作分区表可以像操作普通表一样简单

缺点：

数据如果没有映射到分区，将会报错

按时间划分的分区，需要定期增加分区

10、索引的是什么？使用索引的优缺点

索引是一种排好序的数据结构。

优点：提高查询速度

缺点：占用空间，insert、delete、update需要维护索引

11、索引分类

主键索引：作为主键的字段不能全空，主键必须唯一

唯一索引：作为索引的字段可以全为空，索引字段必须唯一

普通索引：

组合索引：一个索引包含两个及以上字段

函数索引：使用函数创建的索引

倒叙索引：将字段倒排序之后创建索引

反向键索引：将字段中数据从头到尾颠倒过来后创建索引

位图索引：与以上所有的数据结构不同，是一张表，对索引字段值组合出所有情况，并比较每一行数据的索引字段值的分部。

12、创建索引的注意事项

1）一个表上的索引个数不应超过5个

2）对经常一起使用的字段，建组合索引

3）不要在频繁修改的字段上建索引

4）不要对识别率不高的字段建索引

5）不要对持续增加分区的分区表建全局索引

13、避免索引失效的注意事项

1）不要对非函数索引的字段使用函数

2）不要在索引字段上运算

3）防止索引字段的类型发生转换

4）使用like 时不要将通配符放在字符首位

5）非位图索引不要用is null、is not null

6）不要使用<>

6）不要使用not in

14、存储过程与函数的区别

存储过程和函数的本质区别是：存储过程是为了解决某个业务逻辑组合起来的plsql代码块；函数是为了实现某中计算并返回计算结果的plsql代码块；

1）函数能返回结果；存储过程不能返回结果

2）调用方式不通

15、什么是包

包是对一个业务模块的封装，为了提高代码的安全性，防止代码被其他人员删除。

包分为包头和包体

包头：定义包中的对象，包含存储过程、函数、游标、变量、数组等

包体：对包头中存储过程和函数的实现

16、什么是触发器

触发器是在做一个数据库操作时触发的一个事假，常用的就是对表中数据修改时的触发器

表级触发器：对表操作时触发的，一次操作只触发一次

行级触发器：对表中每一行数据操作都触发一次

事前触发器：在对表修改前触发，可以获得新值和旧值

事后触发器：在对表修改后触发，可以获得新值

对表的触发事件有insert、delete、update

17、触发器中可以commit吗？

不可以，因为触发器的提交是与操作提交是一起的，如果触发器可以提交，操作报错，将无法回滚到开始时的状态。

18、使用视图的优缺点

优点

1）隐藏内部实现环节，提高安全性

2）减少查询字段，减少IO

3）降低开发复杂度，前端开发可以像使用表一样使用视图

缺点

写的不好的视图将会降低查询效率

19）数据库优化

数据库优化是个工程量很大的工作包括很多方面

1）sql语句方面

2）数据库方面

3）业务方面

4）IO方面

5）硬件网络方面

下面分开来说

Sql语句优化

保证正确结果的情况下，提高sql语句的执行速度，SQL优化的好坏只能用实际执行速度衡量，以下只是减少写出劣质SQL的建议

a）避免索引失效（注意点比较多）

b）不要关联不需要的表

c）不要写多余的关联条件和筛选条件

d）尽量使用系统函数而不是自定义函数

e）规范书写where子句（注意点比较多）

   in替换or

   union all替换or

   union替换union all

   >=替换>、<=替换<

   Exists替换in、not exists替换not in

   能在where中过滤的条件不写到having中去

f）全表删除用truncate替换delete

g）去重时使用rowid做条件删除

h）较少解析时间

   给表取别名，全大写

   绑定变量

   不用动态sql

i）一条sql语句关联大表应少于5个

j）用with tab as减少重复表的复杂关联

k）组合索引的第一个字段要的出现在条件中

l）避免对那些经常会被删除的的字段建索引

m）避免为那些唯一度不高的字段创建B*树索引

数据库级别优化

a）创建索引

b）使用分区表

c）多张大表关联，使用并行

d）olap型数据库增加large pool大小、增加临时表空间大小

f）oltp型数据库增加database buffer cache命中率，减少磁盘IO

业务方面优化

要根据实际业务优化数据加工的过程，需要熟悉非常熟悉业务逻辑

IO方面

减少物理和网络IO

a）使用分区表

b）前端代码查询时不要用select *

c）使用存储过程，避免前端代码与数据库频繁交互

硬件网络方面

a）增加cpu和内存

b）使用千兆光纤和高性能交换机

20、事务的四大特性

1）原子性：事务要么全做，要么全不做

2）一致性：事务完成签后状态一致，不能产生错误结果

3）隔离性：各个事务见状态不可见，且互补影响

4）永久性：事务一旦完成，结果永远不会改变，即使机器故障，也能通过日志恢复

21、对数据操作时存在的问题

脏读、不可重复读、幻读、覆盖

22、事务隔离级别

读未提交：不解决问题

读提交：解决脏读、覆盖 （Oracle默认支持级别）

可重复读：解决脏读、覆盖、不可重复读

序列化：解决脏读、不可重复读、幻读、覆盖 （Oracle支持级别）

23、锁的分类

1）两种锁机制

独占锁（Exclusive Lock）：即X锁，又称排它锁，是用来防止同时共享相同资源的锁。加了独占锁的数据库对象不能被其它事务读取和修改。
共享锁（Share Lock）：即S锁，是通过对数据存取的高并行性来实现的。加了共享锁的数据库对象可以被其它事务读取，但是不能被其它事务修改。

2）锁类型

DML锁（data locks，数据锁）：用于保护数据的完整性，能够防止同步冲突的DML和DDL操作的破坏性交互。
DDL锁（dictionary locks，字典锁）：用于保护数据库对象的结构，如表、索引等的结构定义。
内部锁和闩（internal locks and latches）：保护数据库的内部结构，如数据文件，对用户是不可见的。

3）DML锁

DML锁主要包括TM锁和TX锁，其中TM锁称为表级锁，TX锁称为事务锁或行级锁

TM锁：当Oracle执行DML语句时，系统自动在所要操作的表上申请TM类型的锁。当TM锁获得后，系统再自动申请TX类型的锁，并将实际锁定的数据行的锁标志位进行置位

TX锁：Transaction Exclusive Lock行级排它锁，对一条记录加上TX锁后，其他用户不能修改、删除该记录

4）DDL锁

DDL锁包括三种类型：排它的DDL锁定、共享的DDL锁定、可打破的解析锁定。

排它的DDL锁定：大部分的DDL操作都会在被操作的对象上添加排他的DDL锁定，从而防止在DDL命令执行期间，对象被其他用户所修改。

共享的DDL锁定：用来保护被DDL的对象不被其他用户进程所更新，但是允许其他进程在对象上添加共享的DDL锁定。如果是对表进行DDL命令，则其他进程可以同时修改表里的数据。比如我们发出create view命令创建视图时，在视图的所引用的表(这种表也叫基表)上添加的就是共享的DDL命令。

5）内部锁和闩

内部锁保护内部数据库结构，如数据文件，对用户是不可见的。

24、实例与数据库的区别

实例是内存中一块空间和一些进程的组合，用来管理数据库，数据库是磁盘上的文件，为实例运行提供支持。实力和数据库关系，单机：一对一；RAC多对一

25、SGA的组成部分

数据库缓冲区缓存（Database buffer cache）、重做日志缓冲区（redo buffer）、共享池（share pool）、大池（large pool）、Java池（Java pool）、流池（stream pool）

共享池包含：库缓存（library cache）、数据字典缓存

数据库缓冲区缓存：存储从数据文件中读取到的数据块

重做日志缓冲区：保存重做日志

库缓存：存储sql语句解析以及执行计划

数据字典缓存：存储解析sql语句以及管理数据库的信息

大池：并行操作时、数据库恢复时使用

26、后台进程

SMON：系统管理进程，管理

PMON：进程管理进程，

DBWn：将数据库缓冲区缓存中的脏块写入数据文件

      每3秒一次

      遇到检查点进程执行时

      缓存满时

      脏块达到1/4时

      表空间只读或脱机时

LGWR：将重做日志缓冲区中的日志写入在线重做日志文件

Commit

日志1/3时

DBWn写脏数据时

CKPT：检查点，定时执行，更新更新控制文件和数据文件头

ARCn：归档模式下，将在线重做日志写入归档日志

27、数据库文件包含

参数文件、控制文件、数据文件、重做日志文件、归档日志文件

28、数据库数据文件逻辑结构

表空间：一个表属于一个表空间，一个表空间包含多个数据文件

段：通常理解一张表就是一个段，分区表一个分区就是一个段

    数据段：对应数据表

索引段：对应索引

临时段：用来保存执行过程中的临时数据

LOB段：保存表中LOB字段类型的数据

undo段：update、delete时保存操作之前的数据

区：

块：IO读写的最小粒度

29、数据导入导出方式

Exp/imp

数据泵

Plsql工具

Sqlldr、spool

30、数据库冷备份与热备份以及他们的优缺点

冷备份：在数据库关闭状态下备份，拷贝参数文件、控制文件、数据文件、日志文件。

热备份：在数据库运行时，开启归档模式下进行备份

冷备份优缺点：

优点：

备份简单，出错率低。只需找到数据文件备份即可

缺点：

备份期间数据库需要关闭

只能恢复到某一时间点的数据

只能全库恢复

热备份优缺点

优点：

不需要关闭数据库，备份和恢复时时数据库也可以工作

可以按照数据库文件和表空间进行备份

可以恢复到秒级的数据

缺点：

备份不能出错，一旦出错此次备份不可用。

31、什么是游标？隐式游标与显式游标的区别？

游标是用例执行SQL时保存其数据结果集

32、Awr工具

1）授权

2）执行awr工具sql语句

@/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/awrrpt.sql

3）选择报错报告文件格式

Type Specified:  html

4）要查询近几天的快照

Enter value for num_days: 7

5）输入开始快照号

Enter value for begin_snap: 1457

6）输入结束快照号

Enter value for end_snap: 1458

7）输入报告文件名称

Enter value for report_name: /home/oracle/23.html

8）执行结束，找到文件

Report written to /home/oracle/123.html

33、Awr报告使用说明

主要查看

1）Top 5 Timed Foreground Events

2）CPU内存利用率

3）SQL ordered by Elapsed Time（执行时间排序）

34、sqlldr工具

1）ctl控制文件格式

·  LOAD DATA

·  TRUNCATE|APPEND|DELETE| REPLACE INTO TABLE MANAGER

·  FIELDS TERMINATED BY "," 外部文件的数据以“，”分隔

·  OPTIONALLY ENCLOSED BY '"' 部分字段可以用双引号包起来

·  railing nullcols  表中的字段没有对应的值时填充空值

·  (MGRNO,MNAME,JOB,REMARK char(100000))

2)sqlldr命令

Sqlldr user/passwd@22.22.22.22/ora11g data=1.txt control=1.ctl log=1.log bad=1.bad skip=1

35、spool命令

spool的作用可以用一句话来描述：在sqlplus中用来保存或打印查询结果。

方式一

set trimspool on

set linesize 120

set pagesize 2000

set newpage 1

set heading off

set term off

spool 路径+文件名

select col1||','||col2||','||col3||','||col4||'..' from tablename;

spool off

方式二

#!/bin/sh

DB_USER=zxdbm_ismp                                 #DB USER

DB_PWD=zxin_smap                                    #DB PASSWORD

DB_SERV=zx10_40_43_133                           #DB SERVICE NAME

sqlplus -s $DB_USER/$DB_PWD@$DB_SERV<

set trimspool on

set linesize 120

set pagesize 2000

set newpage 1

set heading off

set term off

spool promt.txt

select taskindex||'|'||commonindex||'|'||tasktype||'|'||to_number(to_char(sysdate,'YYYYMMDD')) from ssrv_sendsms_task;

spool off

EOF

参数说明：

set arraysize 5000;  //此参数可提高SPOOL卸载的速度，最大可以设置为5000
set autotrace on;    //设置允许对执行的sql进行分析
set colsep ',';　　　//域输出分隔符
set echo off;　　　　//显示start启动的脚本中的每个sql命令，缺省为on
set feedback off;　　//回显本次sql命令处理的记录条数，缺省为on，设置显示“已选择XX行”
set heading off;　　 //输出域标题，字段的名称，缺省为on
SET LINESIZE 2500;   //每行允许的最大字符数，设置大些，免得数据被截断，但不宜过大，太大会大大降低导出的速度(注意必须与trimspool结合使用防止导出的文本有太多的尾部空格)
set newpage 1;       //设置页与页之间的分隔{1|n|NONE};当值为0时在每页开头有一个小的黑方框;当值为n时在页和页之间隔着n个空行;当为none时，会在页和页之间没有任何间隔；
set newp none;       //设置查询出来的数据分多少页显示，如果需要连续的数据，中间不要出现空行就把newp设置为none，这样输出的数据行都是连续的，中间没有空行之类的
set num 18;          //设置数字的长度，如果不够大，则用科学记数法显示
set numwidth 12;　   //输出number类型域长度，缺省为10
SET NULL text;       //显示时,用text值代替NULL值
set pagesize 2000;　 //输出每页行数，页面大小，缺省为24,为了避免分页，可设定为0
set serveroutput on; //设置允许显示输出类似dbms_output;--编写存储过程时,大多会将必要的信息输出；
SET SPACE 0;
set term off;        //不在屏幕上输出执行结果
set termout off;　　 //显示脚本中的命令的执行结果，缺省为on
set timing on;       //显示每个sql语句花费的执行时间，设置显示“已用时间：XXXX”
set trimout on;　　　//去除标准输出每行的拖尾空格，缺省为off
set trimspool on;　　//去除重定向（spool）输出每行的拖尾空格，缺省为off
set verify off       //是否显示替代变量被替代前后的语句
SET wrap on;         //输出行长度大于设置行长度时(用set linesize n命令设置);值为on时,多余的字符另起一行显示,否则多余的字符将被切除，不予显示；

36、Rman工具

RMAN（Recovery Manager）恢复管理器,它是一种oracle的专用备份恢复工具。

RMAN实际上是物理备份,任意时间点的恢复可以做到。

archivelog模式,归档模式

noarchivelog模式,非归档模式

RMAN都可以执行脱机备份。

如果数据库处于归档模式,RMAN既可以做脱机备份（冷备份）也可以做联机备份（热备份）。

如果数据库处于非归档模式，RMAN是不能进行联机备份的。

热备份：数据库OPEN状态

冷备份：数据库MOUNT状态

37、RAC

集群分为高可用集群和负载均衡集群，下面我将不区分这两个概念，以防迷糊，我下面说的集群直接是指负载均衡集群。所谓集群就是通过配置搭建一个这样的环境，环境中有两个或两个以上的服务器（节点），这些节点上分别运行着同一个服务，对于服务请求，这些节点将均衡请求负载，而当一个节点down掉之后，原本在这个节点处理的请求会自动的转移到其他可用的节点上面。而对于用户而言，整个环境内部是透明的，就如同一台服务器一样。这就是集群的概念。

36、Grid

在oracle 10g中，grid方案的实现所需的软件包含在不同的地方，主要有clusterware集群软件，database中的RAC实现，以及database中dbca建库中的ASM的实现等。到了oracle  11g后，oracle公司讲实现grid的这些个组件整合到了一起，构成了Grid Infrastructure（GI），这样就像clusterware对于集群一样，GI就是用来实现grid的软件。这时候要注意，GI不等于grid。

GI主要包括了两个主要部分，即clusterware和ASM，当然还有一些其他组件，比如grid control、data pump等。从11gR2开始，如果用户想使用clusterware或者ASM的话，则必须下载安装这个软件。

37、data guard

DATA GUARD的最主要的功能是冗灾。当然根据配置的不同,DATA GUARD还可以具备以下特点：高可用、性能提升、数据保护以及故障恢复等。

Data Guard运行要求:
1.主机必需运行在归档模式下。
2.主备数据库的版本必须一样，操作系统必须一样，版本可不同,主备机可使用不同的目录结构。
3.主备机必须都要运行在32位或64位下。
4.主库避免nologing的方式，这样会导致备机无法与主机同步。

MySQL数据库面试题（2022最新版）

🐶 程序猿周周
⌨️ 短视频小厂BUG攻城狮
🤺 如果文章对你有帮助，记得关注、点赞、收藏，一键三连哦，你的支持将成为我最大的动力

---

本文是 Java 面试总结系列的第1️⃣篇文章，该专栏将整理和梳理笔者作为 Java 后端程序猿在日常工作以及面试中遇到的实际问题，通过这些问题的系统学习，也帮助笔者顺利拿到阿里、字节、华为、快手等Offer，也祝愿大家能够早日斩获自己心仪的Offer。由于笔者能力有限，其中大多答案来自巨人的肩膀并引以出处，若有错误或疏忽还望各位大佬们不吝指出…

标题	地址
MySQL数据库面试题总结（2022版）	https://blog.csdn.net/adminpd/article/details/122910606
Redis面试题总结（2022版）	https://blog.csdn.net/adminpd/article/details/122934938
计算机网络面试题总结（2022版）	https://blog.csdn.net/adminpd/article/details/122973684
操作系统面试题总结（2022版）	https://blog.csdn.net/adminpd/article/details/122994599
Linux面试题总结（2022版）	https://blog.csdn.net/adminpd/article/details/122994862
Spring面试题总结（2022版）	https://blog.csdn.net/adminpd/article/details/123016872
Java基础面试题总结（2022版）	https://blog.csdn.net/adminpd/article/details/123080189
Java集合面试题总结（2022版）	https://blog.csdn.net/adminpd/article/details/123171501
Java并发面试题总结（2022版）	https://blog.csdn.net/adminpd/article/details/123266624
Java虚拟机面试题总结（2022版）	https://blog.csdn.net/adminpd/article/details/123412605
Java异常面试题总结（2022版）	https://blog.csdn.net/adminpd/article/details/123462676
设计模式面试题总结（2022版）	https://blog.csdn.net/adminpd/article/details/123490442
Dubbo面试题总结（2022版）	https://blog.csdn.net/adminpd/article/details/123538243
Netty面试题总结（2022版）	https://blog.csdn.net/adminpd/article/details/123564362

1 基础

1.1 基本概念

1、MySQL有哪些数据库类型？

• 数值类型

有包括 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别表示 1 字节、2 字节、3 字节、4 字节、8 字节的整数类型。

1）任何整数类型都可以加上 UNSIGNED 属性，表示无符号整数。

2）任何整数类型都可以指定长度，但它不会限制数据的合法长度，仅仅限制了显示长度。

还有包括 FLOAT、DOUBLE、DECIMAL 在内的小数类型。

• 字符串类型

包括 VARCHAR、CHAR、TEXT、BLOB。

注意：VARCHAR(n) 和 CHAR(n) 中的 n 并不代表字节个数，而是代表字符的个数。

• 日期和时间类型

常用于表示日期和时间类型为 DATETIME、DATE 和 TIMESTAMP。

尽量使用 TIMESTAMP，空间效率高于 DATETIME。

ref MySQL 数据类型

2、CHAR 和 VARCHAR 区别？

1）首先可以明确的是 CHAR 是定长的，而 VARCHAR 是可以变长。

CHAR 会根据声明的字符串长度分配空间，并会使用空格对字符串右边进行尾部填充。所以在检索 CHAR 类型数据时尾部空格会被删除，如保存的是字符串 'char '，但最后查询到的是 'char'。又因为长度固定，所以存储效率高于 VARCHAR 类型。

VARCHAR 在 MySQL 5.0 之后长度支持到 65535 字节，但会在数据开头使用额外 1~2 个字节存储字符串长度（列长度小于 255 字节时使用 1 字节表示，否则 2 字节），在结尾使用 1 字节表示字符串结束。

2）再者，在存储方式上，CHAR 对英文字符（ASCII）占用 1 字节，对一个汉字使用用 2 字节。而 VARCHAR 对每个字符均使用 2 字节。

虽然 VARCHAR 是根据字符串长度分配存储空间的，但在内存中依旧使用声明长度进行排序等作业，故在使用时仍需综合考量字段长度。

3、CHAR 和 VARCHAR 如何选择？

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

2）对于非常短的列或固定长度的数据（如 MD5），CHAR 比 VARCHAR 在存储空间上更有效率。

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

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

4、CHAR，VARCHAR 和 Text 的区别？

1）长度区别

• Char 范围是 0～255。

• Varchar 最长是 64k（注意这里的 64k 是整个 row 的长度，要考虑到其它的 column，还有如果存在 not null 的时候也会占用一位，对不同的字符集，有效长度还不一样，比如 utf-8 的，最多 21845，还要除去别的column），但 Varchar 在一般情况下存储都够用了。

• 如果遇到了大文本，考虑使用 Text，最大能到 4G（其中 TEXT 长度 65,535 bytes，约 64kb；MEDIUMTEXT 长度 16,777,215 bytes，约 16 Mb；而 LONGTEXT 长度 4,294,967,295 bytes，约 4Gb）。

2）效率区别

效率来说基本是 Char > Varchar > Text，但是如果使用的是 Innodb 引擎的话，推荐使用 Varchar 代替 Char。

3）默认值区别

Char 和 Varchar 支持设置默认值，而 Text 不能指定默认值。

1.2 数据库设计

1、什么是三大范式？

• 第一范式（1NF）：字段（或属性）是不可分割的最小单元，即不会有重复的列，体现原子性

• 第二范式（2NF）：满足 1NF 前提下，存在一个候选码，非主属性全部依赖该候选码，即存在主键，体现唯一性，专业术语则是消除部分函数依赖

• 第三范式（3NF）：满足 2NF 前提下，非主属性必须互不依赖，消除传递依赖

ref：如何理解关系型数据库的常见设计范式？

除了三大范式外，还有BC范式和第四范式，但其规范过于严苛，在生产中往往使用不到。

2、什么是范式和反范式，以及各自优缺点？

范式是符合某一种级别的关系模式的集合。构造数据库必须遵循一定的规则。在关系数据库中，这种规则就是范式。

名称	优点	缺点
范式	范式化的表减少了数据冗余，数据表更新操作快、占用存储空间少。	查询时通常需要多表关联查询，更难进行索引优化
反范式	反范式的过程就是通过冗余数据来提高查询性能，可以减少表关联和更好进行索引优化	存在大量冗余数据，并且数据的维护成本更高

所以在平时工作中，我们通常是将范式和反范式相互结合使用。

2 索引

首先了解一下什么是索引，索引是对数据库表中一列或多列的值进行排序的数据结构，用于快速访问数据库表中的特定信息。

1、索引的几种类型或分类？

1）从物理结构上可以分为聚集索引和非聚集索引两类：

• 聚簇索引指索引的键值的逻辑顺序与表中相应行的物理顺序一致，即每张表只能有一个聚簇索引，也就是我们常说的主键索引；

• 非聚簇索引的逻辑顺序则与数据行的物理顺序不一致。

2）从应用上可以划分为一下几类：

• 普通索引：MySQL 中的基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了提高查询效率。通过 ALTER TABLE table_name ADD INDEX index_name (column) 创建；

• 唯一索引：索引列中的值必须是唯一的，但是允许为空值。通过 ALTER TABLE table_name ADD UNIQUE index_name (column) 创建；

• 主键索引：特殊的唯一索引，也成聚簇索引，不允许有空值，并由数据库帮我们自动创建；

• 组合索引：组合表中多个字段创建的索引，遵守最左前缀匹配规则；

• 全文索引：只有在 MyISAM 引擎上才能使用，同时只支持 CHAR、VARCHAR、TEXT 类型字段上使用。

2、索引的优缺点？

先来说说优点：创建索引可以大大提高系统的性能。

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

• 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

• 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

• 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

既然增加索引有如此多的优点，为什么不对表中的每一个列都创建一个索引呢？这是因为索引也是有缺点的：

• 创建和维护索引需要耗费时间，这种时间随着数据量的增加而增加，这样就降低了数据的维护速度。

• 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需要的空间就会更大。

3、索引设计原则？

• 选择唯一性索引；

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。

• 为常作为查询条件的字段建立索引；

如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。

• 为经常需要排序、分组和联合操作的字段建立索引；

经常需要 ORDER BY、GROUP BY、DISTINCT 和 UNION 等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。

• 限制索引的数目；

每个索引都需要占⽤用磁盘空间，索引越多，需要的磁盘空间就越大，修改表时，对索引的重构和更新很麻烦。

• 小表不建议索引（如数量级在百万以内）；

由于数据较小，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。

• 尽量使用数据量少的索引；

如果索引的值很长，那么查询的速度会受到影响。此时尽量使用前缀索引。

• 删除不再使用或者很少使用的索引。

4、索引的数据结构？

索引的数据结构和具体存储引擎的实现有关，MySQL 中常用的是 Hash 和 B+ 树索引。

• Hash 索引底层就是 Hash 表，进行查询时调用 Hash 函数获取到相应的键值（对应地址），然后回表查询获得实际数据.

• B+ 树索引底层实现原理是多路平衡查找树，对于每一次的查询都是从根节点出发，查询到叶子节点方可以获得所查键值，最后查询判断是否需要回表查询.

5、Hash 和 B+ 树索引的区别？

Hash
1）Hash 进行等值查询更快，但无法进行范围查询。因为经过 Hash 函数建立索引之后，索引的顺序与原顺序无法保持一致，故不能支持范围查询。同理，也不支持使用索引进行排序。

2）Hash 不支持模糊查询以及多列索引的最左前缀匹配,因为 Hash 函数的值不可预测，如 AA 和 AB 的算出的值没有相关性。

3）Hash 任何时候都避免不了回表查询数据.

4）虽然在等值上查询效率高，但性能不稳定，因为当某个键值存在大量重复时，产生 Hash 碰撞，此时查询效率反而可能降低。

B+ Tree

1）B+ 树本质是一棵查找树，自然支持范围查询和排序。

2）在符合某些条件（聚簇索引、覆盖索引等）时候可以只通过索引完成查询，不需要回表。

3）查询效率比较稳定，因为每次查询都是从根节点到叶子节点，且为树的高度。

6、为何使用 B+ 树而非二叉查找树做索引？

我们知道二叉树的查找效率为 O(logn)，当树过高时，查找效率会下降。另外由于我们的索引文件并不小，所以是存储在磁盘上的。

文件系统需要从磁盘读取数据时，一般以页为单位进行读取，假设一个页内的数据过少，那么操作系统就需要读取更多的页，涉及磁盘随机 I/O 访问的次数就更多。将数据从磁盘读入内存涉及随机 I/O 的访问，是数据库里面成本最高的操作之一。

因而这种树高会随数据量增多急剧增加，每次更新数据又需要通过左旋和右旋维护平衡的二叉树，不太适合用于存储在磁盘上的索引文件。

7、为何使用 B+ 树而非 B 树做索引？

在此之前，先来了解一下 B+ 树和 B 树的区别：

• B 树非叶子结点和叶子结点都存储数据，因此查询数据时，时间复杂度最好为 O(1)，最坏为 O(log n)。而 B+ 树只在叶子结点存储数据，非叶子结点存储关键字，且不同非叶子结点的关键字可能重复，因此查询数据时，时间复杂度固定为 O(log n)。

• B+ 树叶子结点之间用链表相互连接，因而只需扫描叶子结点的链表就可以完成一次遍历操作，B 树只能通过中序遍历。

为什么 B+ 树比 B 树更适合应用于数据库索引？

• B+ 树减少了 IO 次数。

由于索引文件很大因此索引文件存储在磁盘上，B+ 树的非叶子结点只存关键字不存数据，因而单个页可以存储更多的关键字，即一次性读入内存的需要查找的关键字也就越多，磁盘的随机 I/O 读取次数相对就减少了。

• B+ 树查询效率更稳定

由于数据只存在在叶子结点上，所以查找效率固定为 O(log n)，所以 B+ 树的查询效率相比B树更加稳定。

• B+ 树更加适合范围查找

B+ 树叶子结点之间用链表有序连接，所以扫描全部数据只需扫描一遍叶子结点，利于扫库和范围查询；B 树由于非叶子结点也存数据，所以只能通过中序遍历按序来扫。也就是说，对于范围查询和有序遍历而言，B+ 树的效率更高。

ref 为什么 B+ 树比 B 树更适合应用于数据库索引？

8、什么是最左匹配原则？

顾名思义，最左优先，以最左边为起点任何连续的索引都能匹配上。同时遇到范围查询（>、<、between、like）就会停止匹配。

如建立 (a,b,c,d) 索引，查询条件 b = 2 是匹配不到索引的，但是如果查询条件是 a = 1 and b = 2 或 a=1 又或 b = 2 and a = 1 就可以，因为优化器会自动调整 a,b 的顺序。

再比如 a = 1 and b = 2 and c > 3 and d = 4，其中 d 是用不到索引的，因为 c 是一个范围查询，它之后的字段会停止匹配。

最左匹配的原理

上图可以看出 a 是有顺序的（1、1、2、2、3、3），而 b 的值是没有顺序的（1、2、1、4、1、2）。所以 b = 2 这种查询条件无法利用索引。

同时我们还可以发现在 a 值相等的情况下（a = 1），b 又是顺序排列的，所以最左匹配原则遇上范围查询就会停止，剩下的字段都无法使用索引。

ref 最左匹配原则

9、什么是覆盖索引？

在 B+ 树的索引中，叶子节点可能存储了当前的键值，也可能存储了当前的键值以及整行的数据，这就是聚簇索引和非聚簇索引。 在 InnoDB 中，只有主键索引是聚簇索引，如果没有主键，则挑选一个唯一键建立聚簇索引。如果没有唯一键，则隐式的生成一个键来建立聚簇索引。

当查询使用聚簇索引时，在对应的叶子节点，可以获取到整行数据，因此不用再次进行回表查询。

10、什么是索引下推？

索引下推（Index condition pushdown） 简称 ICP，在 Mysql 5.6 版本上推出的一项用于优化查询的技术。

在不使用索引下推的情况下，在使用非主键索引进行查询时，存储引擎通过索引检索到数据，然后返回给 MySQL 服务器，服务器判断数据是否符合条件。

而有了索引下推之后，如果存在某些被索引列的判断条件时，MySQL 服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合 MySQL 服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给 MySQL 服务器。

索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少 MySQL 服务器从存储引擎接收数据的次数。

ref Mysql性能优化：什么是索引下推？

3 存储

3.1 存储引擎

1、有哪些常见的存储引擎？

ref 几种MySQL数据库引擎优缺点对比

2、MyISAM 和 InnoDB 的区别？

1）InnoDB 支持事务，而 MyISAM 不支持。

2）InnoDB 支持外键，而 MyISAM 不支持。因此将一个含有外键的 InnoDB 表 转为 MyISAM 表会失败。

3）InnoDB 和 MyISAM 均支持 B+ Tree 数据结构的索引。但 InnoDB 是聚集索引，而 MyISAM 是非聚集索引。

4）InnoDB 不保存表中数据行数，执行 select count(*) from table 时需要全表扫描。而 MyISAM 用一个变量记录了整个表的行数，速度相当快（注意不能有 WHERE 子句）。

那为什么 InnoDB 没有使用这样的变量呢？因为InnoDB的事务特性，在同一时刻表中的行数对于不同的事务而言是不一样的。

5）InnoDB 支持表、行（默认）级锁，而 MyISAM 支持表级锁。

InnoDB 的行锁是基于索引实现的，而不是物理行记录上。即访问如果没有命中索引，则也无法使用行锁，将要退化为表锁。

6）InnoDB 必须有唯一索引（如主键），如果没有指定，就会自动寻找或生产一个隐藏列 Row_id 来充当默认主键，而 Myisam 可以没有主键。

ref MyISAM与InnoDB 的区别（9个不同点）

3、InnoDB 的四大特性?

• 插入缓冲insert buffer)
• 二次写(double write)
• 自适应哈希索引(ahi)
• 预读(read ahead)

4、InnoDB 为何推荐使用自增主键？

自增 ID 可以保证每次插入时 B+ 树索引是从右边扩展的，因此相比自定义 ID （如 UUID）可以避免 B+ 树的频繁合并和分裂。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。

5、如何选择存储引擎？

默认使用 InnoDB，MyISAM 适用以插入为主的程序，比如博客系统、新闻门户。

3.2 存储结构

1、什么是 InnoDB 的页、区、段？

• 页（Page）

首先，InnoDB 将物理磁盘划分为页（page），每页的大小默认为 16 KB，页是最小的存储单位。页根据上层应用的需要，如索引、日志等，分为很多的格式。我们主要说数据页，也就是存储实际数据的页。

• 区（Extent）

如果只有页这一个层次的话，页的个数是非常多的，存储空间的分配和回收都会很麻烦，因为要维护这么多的页的状态是非常麻烦的。

所以，InnoDB 又引入了区（Extent) 的概念。一个区默认是 64 个连续的页组成的，也就是 1MB。通过 Extent 对存储空间的分配和回收就比较容易了。

• 段（Segment）

为什么要引入段呢，这要从索引说起。我们都知道索引的目的是为了加快查找速度，是一种典型的用空间换时间的方法。

B+ 树的叶子节点存放的是我们的具体数据，非叶子结点是索引页。所以 B+ 树将数据分为了两部分，叶子节点部分和非叶子节点部分，也就我们要介绍的段 Segment，也就是说 InnoBD 中每一个索引都会创建两个 Segment 来存放对应的两部分数据。

Segment 是一种逻辑上的组织，其层次结构从上到下一次为 Segment、Extent、Page。

2、页由哪些数据组成？

首先看数据页的基本格式，如下图：

• File Header

用于描述数据页的外部信息，比如属于哪一个表空间、前后页的页号等。

• Page Header

用来描述数据页中的具体信息，比如存在多少条纪录，第一条纪录的位置等。

• infimum 和 supremum 纪录

infimum 和 supremum 是系统生成的纪录，分别为最小和最大纪录值，infimum 的下一条是用户纪录中键值最小的纪录，supremum 的上一条是用户纪录中键值最大的纪录，通过 next_record 字段来相连。

• User Records

用户纪录，也就是数据库表中对应的数据，这里我们说常用的 Compact 格式。

InnoDB 除了我们插入的数据外，还有一些隐藏列，transaction_id（事务ID）、roll_pointer（回滚指针）是一定添加的。

row_id 则不一定，根据以下策略生成：优先使用用户建表时指定的主键，若用户没有指定主键，则使用unique键。若unique键都没有，则系统自动生成row_id，为隐藏列。

• Free Space

页中目前空闲的存储，可以插入纪录。

• Page Dictionary

类似于字典的目录结构，根据主键大小，每隔 4-8 个纪录设置一个槽，用来纪录其位置，当根据主键查找数据时，首先一步到位找到数据所在的槽，然后在槽中线性搜素。这种方法比从前到后遍历页的链表的效率更快。

• Page Tailer

File Header存储刷盘前内存的校验和，Page Tailer储存刷盘后的校验和。当刷盘的时候，出现异常，Page Tailer和File Header中的校验和不一致，则说明出现刷盘错误。

3、页中插入记录的过程？

1）如果 Free Space 的空间足够的话，直接分配空间来添加纪录，并将插入前最后一条纪录的 next_record 指向当前插入的纪录，将当前插入纪录的 next_record 指向 supremum 纪录。

2）如果 Free Space的 空间不够的话，则首先将之前删除造成的碎片重新整理之后，按照上述步骤插入纪录。

3）如果当前页空间整理碎片之后仍然不足的话，则重新申请一个页，将页初始化之后，按照上述步骤插入纪录

ref MySQL之InnoDB物理存储结构

4 事务

1、什么是数据库的事务？

数据库的事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务是逻辑上的一组操作，要么都执行，要么都不执行。

事务的典型应用场景，如转账。

2、什么是事务的四大特性（ACID）？

• 原子性： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用
• 一致性： 事务执行前后，数据保持一致，多个事务对同一个数据读取的结果是相同的
• 隔离性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的
• 持久性： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

4、事务的并发问题？

脏读、幻读和不可重复读。

ref 并发事务带来的问题

5、什么是脏读、幻读和不可重复度？

• 脏读：一个事务读取到另一个事务尚未提交的数据。 事务 A 读取事务 B 更新的数据，然后 B 回滚操作，那么 A 读取到的数据是脏数据。

• 不可重复读：一个事务中两次读取的数据的内容不一致。 事务 A 多次读取同一数据，事务 B 在事务 A 多次读取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，结果 不一致。

• 幻读：一个事务中两次读取的数据量不一致。 系统管理员 A 将数据库中所有学生的成绩从具体分数改为 ABCDE 等级，但是系统管理员 B 就在这个时候插入了一条具体分数的记录，当系统管理员 A 改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。 解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

ref MySQL的四种事务隔离级别

6、事务的隔离级别有哪些？

串行化的隔离级别最高，读未提交的级别最低，级别越高，则执行效率就越低，所以在选择隔离级别时应该结合实际情况。

MySQL 支持以上四种隔离级别，默认为 Repeatable read (可重复读)；而 Oracle 只支持 Serializeble(串行化) 级别和 Read committed(读已提交) 两种，其中默认为读已提交。

ref 事务的隔离级别

7、ACID 特性是如何实现的？

分四个维度去理解，如原子性是 undo 日志，持久性是 redo 日志。（PS 日志具体原理在后续章节讲述。）

ref ACID特性的实现原理

5 锁

1、数据库锁的作用以及有哪些锁？

当数据库有并发事务的时候，可能会产生数据的不一致，这时候需要一些机制来保证访问的次序，锁机制就是这样的一个机制。即锁的作用是解决并发问题。

从锁的粒度划分，可以将锁分为表锁、行锁以及页锁。

• 行级锁：是锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。

行级锁开销大，加锁慢，且会出现死锁。但锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

• 表级锁：是粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。

• 页级锁：是粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折中的页级，一次锁定相邻的一组记录。

开销和加锁时间界于表锁和行锁之间，会出现死锁。锁定粒度界于表锁和行锁之间，并发度一般。

从使用性质划分，可以分为共享锁、排它锁以及更新锁。

• 共享锁（Share Lock）：S 锁，又称读锁，用于所有的只读数据操作。

S 锁并非独占，允许多个并发事务对同一资源加锁，但加 S 锁的同时不允许加 X 锁，即资源不能被修改。S 锁通常读取结束后立即释放，无需等待事务结束。

• 排他锁（Exclusive Lock）：X 锁，又称写锁，表示对数据进行写操作。

X 锁仅允许一个事务对同一资源加锁，且直到事务结束才释放，其他任何事务必须等到 X 锁被释放才能对该页进行访问。

使用 select * from table_name for update; 语句产生 X 锁。

• 更新锁：U 锁，用来预定要对资源施加 X 锁，允许其他事务读，但不允许再施加 U 锁或 X 锁。

当被读取的页将要被更新时，则升级为 X 锁，U 锁一直到事务结束时才能被释放。故 U 锁用来避免使用共享锁造成的死锁现象。

ref 数据库锁分类和总结

从主观上划分，又可以分为乐观锁和悲观锁。

• 乐观锁（Optimistic Lock）：顾名思义，从主观上认定资源是不会被修改的，所以不加锁读取数据，仅当更新时用版本号机制等确认资源是否被修改。

乐观锁适用于多读的应用类型，可以系统提高吞吐量。

• 悲观锁（Pessimistic Lock）：正如其名，具有强烈的独占和排它特性，每次读取数据时都会认为会被其它事务修改，所以每次操作都需要加上锁。

2、隔离级别和锁的关系？

1）在 Read Uncommitted 级别下，读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突；

2）在 Read Committed 级别下，读操作需要加共享锁，但是在语句执行完以后释放共享锁；

3）在 Repeatable Read 级别下，读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁；

4）在 SERIALIZABLE 级别下，限制性最强，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成。

3、InnoDB 中的锁算法？

• Record lock：单个行记录上的锁
• Gap lock：间隙锁，锁定一个范围，不包括记录本身
• Next-key lock：record + gap 锁定一个范围，包含记录本身

ref 锁机制与InnoDB锁算法

4、什么是快照读和当前读？

快照读就是读取的是快照数据，不加锁的简单 Select 都属于快照读。

SELECT * FROM player WHERE ...

当前读就是读的是最新数据，而不是历史的数据。加锁的 SELECT，或者对数据进行增删改都会进行当前读。

SELECT * FROM player LOCK IN SHARE MODE;
SELECT FROM player FOR UPDATE;
INSERT INTO player values ...
DELETE FROM player WHERE ...
UPDATE player SET ...

5、什么是 MVCC 以及实现？

MVCC 的英文全称是 Multiversion Concurrency Control，中文意思是多版本并发控制，可以做到读写互相不阻塞，主要用于解决不可重复读和幻读问题时提高并发效率。

其原理是通过数据行的多个版本管理来实现数据库的并发控制，简单来说就是保存数据的历史版本。可以通过比较版本号决定数据是否显示出来。读取数据的时候不需要加锁可以保证事务的隔离效果。

ref MVCC 原理
ref MVCC详解

6 进阶功能

6.1 视图

6.2 存储过程

1、什么是存储过程？

存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需要创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。

2、存储过程和函数的区别？

1）返回值的区别：函数有1个返回值，而存储过程是通过参数返回的，可以有多个或者没有。

2）调用的区别：，函数可以在查询语句中直接调用，而存储过程必须单独调用。

ref 存储过程（procedure）和函数（Function）的区别

6.3 触发器

7、集群

7.1 日志

1、MySQL 中有哪些常见日志？

• 重做日志（redo log）：物理日志

作用是确保事务的持久性。 redo 日志记录事务执行后的状态，用来恢复未写入 data file 的已提交事务数据。

• 回滚日志（undo log）：逻辑日志

作用是保证数据的原子性。 保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读。

• 二进制日志（binlog）：逻辑日志

常用于主从同步或数据同步中，也可用于数据库基于时间点的还原。

• 错误日志（errorlog）

记录着 MySQL 启动和停止，以及服务器在运行过程中发生的错误的相关信息。在默认情况下，系统记录错误日志的功能是关闭的，错误信息被输出到标准错误输出。

• 普通查询日志（general query log）

记录了服务器接收到的每一个命令，无论命令语句是否正确，因此会带来不小开销，所以也是默认关闭的。

• 慢查询日志（slow query log）

记录执行时间过长和没有使用索引的查询语句（默认 10s），同时只会记录执行成功的语句。

• 中继日志（relay log）

在从节点中存储接收到的 binlog 日志内容，用于主从同步。

ref MySQL中的几种日志了解

7.2 主从复制

1、什么是主从复制？

主从复制是用来建立一个与主数据库完全一样的数据库环境，即从数据库。主数据库一般是准实时的业务数据库。

2、主从复制的作用？

• 读写分离，使数据库能支撑更大的并发。
• 高可用，做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。

3、主从复制的架构？

• 一主一从或一主多从

在主库的请求压力非常大时，可通过配置一主多从复制架构实现读写分离，把大量对实时性要求不是很高的请求通过负载均衡分发到多个从库上去读取数据，降低主库的读取压力。而且在主库出现宕机时，可将一个从库切换为主库继续提供服务。

• 主主复制

双主复制架构适用于需要进行主从切换的场景。 两个数据库互为主从，当主库宕机恢复后，由于它还是原来从库（现在主库）的从机，所以它还是会复制新的主库上的数据。那么无论主库的角色怎么切换，原来的主库都不会脱离复制环境。

• 多主一从（5.7 开始支持）
• 联级复制

因为每个从库在主库上都会有一个独立的 Binlog Dump 线程来推送 binlog 日志，所以随着从库数量的增加，主库的 IO 压力和网络压力也会随之增加，这时，联级复制架构应运而生。

联级复制架构只是在一主多从的基础上，再主库和各个从库之间增加了一个二级主库 Master2，这个二级主库仅仅用来将一级主库推送给它的 Binlog 日志再推送给各个从库，以此来减轻一级主库的推送压力。

4、主从复制的实现原理？

数据库有个 binlog 二进制文件，记录了数据可执行的所有 SQL 语句。主从同步的目标就是把主数据库的 binlog 文件中的 SQL 语句复制到从数据库，让其在从数据的 relaylog 文件中再执行一次这些 SQL 语句即可。

具体实现需要三个线程：

• binlog 输出线程：每当有从库连接到主库的时候，主库都会创建一个线程然后发送 binlog内 容到从库。

在从库里，当复制开始的时候，从库就会创建两个线程进行处理：

• 从库 IO 线程：当 START SLAVE 语句在从库开始执行之后，从库创建一个 IO 线程，该线程连接到主库并请求主库发送 binlog 里面的更新记录到从库上。从库 IO 线程读取主库的 binlog 输出线程发送的更新并拷贝这些更新到本地文件，其中包括 relaylog 文件。

• 从库 SQL 线程：从库创建一个 SQL 线程，这个线程读取从库 IO 线程写到 relaylog 的更新事件并执行。

ref MySQL主从复制面试之和原理

5、什么是异步复制和半同步？

MySQL 的主从复制有两种复制方式，分别是异步复制和半同步复制：

• 异步复制

MySQL 默认的主从复制方式就是异步复制，因为 Master 根本不考虑数据是否达到了 Slave，或 Slave 是否成功执行。

如过需要实现完全同步方式，即 Master 需要等待一个或所有 Slave 执行成功后才响应成功，那集群效率可想而知。故 MySQL 5.6 之后出现了一种折中的方式——半同步。

• 半同步复制

一主一从，一主多从情况下，Master 节点只要确认至少有一个 Slave 接受到了事务，即可向发起请求的客户端返回执行成功的操作。同时 Master 是不需要等待 Slave 成功执行完这个事务，Slave 节点接受到这个事务，并成功写入到本地 relay 日志中就算成功。

另外，在半同步复制时，如果主库的一个事务提交成功了，在推送到从库的过程当中，从库宕机了或网络故障，导致从库并没有接收到这个事务的Binlog，此时主库会等待一段时间（这个时间由rpl_semi_sync_master_timeout的毫秒数决定），如果这个时间过后还无法推送到从库，那 MySQL 会自动从半同步复制切换为异步复制，当从库恢复正常连接到主库后，主库又会自动切换回半同步复制。

半同步复制的“半”体现在，虽然主从库的Binlog是同步的，但主库不会等待从库执行完Relay-log后才返回，而是确认从库接收到Binlog，达到主从Binlog同步的目的后就返回了，所以从库的数据对于主库来说还是有延时的，这个延时就是从库执行Relay-log的时间。所以只能称为半同步。

refMySQL主从复制（异步复制与半同步复制）

6、主从中常见问题以及解决？

问题
1）主库宕机后，数据可能丢失。

2）从库只有一个sql Thread，主库写压力大，复制很可能延时。

解决
1）半同步复制：确保事务提交后 binlog 至少传输到一个从库 ，解决数据丢失的问题。

2）并行复制：从库多线程apply binlog，解决从库复制延迟的问题。

8 SQL

8.1 语法

SQL 是一门 ANSI 标准计算机语言，用来访问和操作数据库系统。通常 SQL 语句可以分为两类：

• 数据操作语言（DML）：SELECT、DELETE、INSERT INTO、UPDATE
• 数据定义语言（DDL）：CREATE、DROP、ALTER

实践中，还有一种

• 数据控制语言（Data Control Language）：GRANT，REVOKE，COMMIT，ROLLBACK

1、常见的聚合查询？

使用聚合函数的查询就是聚合查询。所有的聚合函数（UDAF）都应该支持分组查询，内置的聚合函数有：

sum(列名) 求和  　　　　
max(列名) 最大值 　　　　
min(列名) 最小值 　　　　
avg(列名) 平均值 　　　　
first(列名)   第一条记录   
last(列名)    最后一条记录  
count(列名)   统计记录数   注意和count(*)的区别

ref SQL 聚合查询

2、几种关联查询？

1） 内连接（自然连接）：只返回匹配的行，如 Inner Join、Union Join。

2）外连接：返回一个表的全集，如 Left、Right、Full 和 Cross。

ref SQL多表查询

3、Where 和 Having 的区别？

where 子句的作用是在对查询结果进行分组前，将不符合条件的行去掉，即在分组之前过滤数据，where条件中不能包含聚组函数，使用where条件过滤出特定的行。

having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件过滤出特定的组，也可以使用多个分组标准进行分组。

总结一下条件的过滤顺序：on->join->where->group by->having。

4、SQL 关键字的执行顺序？

ref SQL的执行顺序

5、In 和 Exists 的区别？

in 适合内表比外表数据小的情况，exists 适合内表比外表数据大的情况。如果查询的内外表大小相当，则二者效率差别不大。

ref SQL语句中exists和in的区别

6、Union 和 Union All 的区别？

• Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序

• Union All：对两个结果集进行并集操作，包括重复行，不进行排序

Union 因为要进行重复值扫描，所以效率低。如果合并没有刻意要删除重复行，那么就使用Union All。

ref union和union all的区别

7、Drop、Delete 和 Truncate 的区别？

虽然通过 delete、truncate、drop 这三个关键字都可以用来删除数据，但场景不同。

从执行速度上讲：drop > truncate >> DELETE。

区别	Delete	Truncate	Drop
SQL类型	属于DML	属于DDL	属于DDL
支持回滚	支持	不支持	不支持
删除内容	表结构还在，删除表的全部或部分数据	表结构还在，删除表中所有数据	从数据库中删除表的所有数据，包括索引和权限
执行速度	速度慢，需要逐行删除	速度快	速度最快

ref delete、truncate、drop的区别有哪些，该如何选择

8.2 优化

1、一条 SQL 是如何执行的？

ref sql执行过程

2、如何判断 SQL 是否走了索引？

EXPLAIN 命令是查看查询优化器如何决定执行查询的主要方法，使用 EXPLAIN 只需在查询语句开头增加 EXPLAIN 这个关键字即。

其结果中的几个重要参数：

• id

ID 代表执行 select 子句或操作表的顺序，如果包含子查询，则会出现多个 ID。值越大，优先级越高，越先被执行。值相同的按照由上至下的顺序执行。

• select_type（查询类型）

查询类型主要用于区别普通查询、联合查询以及子查询等复杂查询。

• table

• type

查询扫描情况，最好到最差依次是：system>const>eq_ref>ref>range>index>All，一般情况下至少保证达到 range 级别，最好能达到 ref。

• possible_keys

显示可能应用在这张表中的索引，一个或多个。查询到的索引不一定是真正被使用。

• key

实际使用的索引，如果为 null 则表示没有使用索引。因此会出现 possible_keys 列有可能被用到的索引，但是 key 列为 null。

• key_len

表示索引中使用的字节数，在不损失精确性的情况下长度越短越好。key_len 显示的值为索引字段的最大可能长度，并非实际使用长度。即 key_len 是根据表定义计算而来。

• ref

显示索引的哪一列被使用了,如果可能的话是一个常数,哪些列或常量被用于查找索引列上的值。

• rows

根据表统计信息及索引选用情况，估算出找到所需的记录所需要读取的行数。

• Extra

ref EXPLAIN的参数解析及简单应用

3、索引失效的几种情况？

1）like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效；

2）or 语句前后没有同时使用索引。当 or 左右查询字段只有一个是索引，该索引失效，只有左右查询字段均为索引时，才会生效；

3）联合索引不使用第一列，索引失效；

4）数据类型出现隐式转化。如 varchar 不加单引号的话可能会自动转换为 int 型，使索引无效，产生全表扫描；

5）在索引列上使用 IS NULL 或 IS NOT NULL操作。最好给列设置默认值。

6）在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0。

7）对索引字段进行计算操作、字段上使用函数。

8）当 MySQL 觉得全表扫描更快时（数据少）;

ref Mysql索引查询失效的情况

4、Where 子句如何优化？

5、超大分页或深度分页如何处理？

说道 MySQL 的分页，我们首先想到的就是 offset、limit 操作，但随着页数的增加，查询性能指数级增大。

这是由于 MySQL 并不是跳过 offset 的行数，而是取 offset + limit 行，然后丢弃前 offset 行，返回 limit 行，当offset特别大的时候，效率就非常的低下。

此处我们就可以采用覆盖索引+延迟关联技术来减少偏移量的定位进行优化：

##查询语句
select id from product limit 10000000, 10
##优化方式一
SELECT * FROM product WHERE ID > =(select id from product limit 10000000, 1) limit 10
##优化方式二
SELECT * FROM product a JOIN (select id from product limit 10000000, 10) b ON a.ID = b.id

ref mysql优化：覆盖索引（延迟关联）

6、大表查询如何优化？

可以从分库分表、读写分离以及缓存三个维度分别阐述。

8.3 实践

几种常见名次问题

ref MySQL排名函数实现