MySQL数据库部分面试题，初级菜🦃，后面再补充。
MySQL基础部分学习：https://blog.csdn.net/JAYU_37/article/details/101800297

文章目录

1. 数据库基础知识

1.1 为什么要使用数据库

如果
数据保存在文件：
优点：数据永久保存
缺点：速度比内存操作慢，频繁的IO操作，查询数据不方面
数据保存在内存：
优点：存取速度快
缺点：数据不能永久保存
但是如果
数据保存在数据库：
1）数据可以永久保存
2）使用SQL语句查询方面，效率高
3）管理数据方便

1.2 什么是SQL

结构化(Structured Query Language)查询语言，简称SQL，作用：用于存储，查询，更新和管理数据库系统。

1.3 什么是MySQL

MySQL是时下最流行的一款关系型数据库，属于Oracle旗下产品，在Java企业级中开发非常常用，因为MySQL是开源的，免费的，易于扩展。

1.4 数据库的三大范式

第一范式：每个列都是不可分割的数据项。
第二范式：在第一范式的基础上，消除了非主属性对主属性的部分依赖
第三范式：在第二范式上，消除了非主属性对其它非主属性的的依赖，即消除了传递函数依赖。

2. 引擎

2.1 MyISAM引擎和InnoDB引擎有什么区别？

  1. InnoDB是聚簇索引，而MyISAM是非聚簇索引
  2. InnoDB主键索引的叶子节点存储着行数据，因此主键索引非常高效。
  3. MyISAM是MySQL的默认引擎，不提供事务的支持，也不提供行级锁和外键。
  4. InnoDB提供了数据库ACID的支持，还提供行级锁和外键，它的设计目的就是处理大数据容量的数据库管理系统。

2.2 InnoDB引擎的四大特性

  1. 插入缓存
  2. 二次写
  3. 自适应hash索引
  4. 预读

2.3 存储引擎的选择

如果没有特别的需求，使用默认的InnoDB即可。
MyISAM：以读写插入为主的应用程序，比如博客系统，新闻门户网站。
InnoDB：更新（更新删除）频率高的，或者要保证数据的完整性，并发量高，支持事务和外键，如OA管理系统。

3. 索引

3.1 什么是索引

索引是一种特殊的文件，InnoDB数据表上的索引是数据表空间的一部分，它包含了对数据表中所有记录的引用指针。
索引是一种数据结构，数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询，更新数据库中表数据，对于一本书而言，索引相当于目录，索引是一个文件，需要占据物理空间的。

3.2 索引有哪些优点

优点：

  • 通过使用索引，可以大大提高数据的检索速度，这也是创建索引的最主要原因。
  • 通过使用索引，可以在查询过程中，优化隐藏器，提高查询性能。

缺点：

  • 时间方面：创建索引需要耗费时间，对表中的数据进行增加，删除和修改的时候，索引也要动态维护，会减低增/改/删的性能。
  • 空间方面：索引需要占物理空间。

3.3 索引有哪几种类型

  1. 主键索引：一般不需要我们创建，数据列不允许重复，不允许为null，一个表只能有一个主键。
  2. 唯一索引：索引列值必须唯一，可以有null值
  3. 复合索引：一个索引包含多个列。
  4. 全文索引：是目前搜索引擎使用的一种关键性技术

3.4 索引的使用场景（重点）

  1. where：提高效率，如果有多个索引，就会选择一个最优的索引，指的是区分度最高的。

  2. order by：为order by的排序字段添加索引，可以提升排序的效率。

  3. join：对join的on涉及的字段建立索引可以提高效率。

  4. 索引覆盖：如果查询的字段都有建立索引，那么引擎会直接在索引表中查询而不会直接访问原始数据（如果其中有一个字段没有建立索引），就会进行全表扫描。这就叫所，索引覆盖。因此，我们需要尽可能的在select后写有必要查询的字段，避免使用select * 查询，以增加索引覆盖的几率。

    但是不要想着为每个字段创建索引，因为优先使用索引的优势在于其体积小。

3.5 索引的基本原理

索引用来快速寻找哪些具体特定值的记录，如果没有索引，执行查询时需要遍历整张表
索引的原理很简单，就是将无序的数据变成有序的查询。

3.6 创建索引的原则（！！重要）

索引虽好，但不可无限制的使用它，最好符合下面的规则：

  1. 最左前缀匹配原则，这个是组合索引非常重要的原则，mysql会一直从左至右进行匹配查询，，遇到> < between like就停止匹配，如： 
a = 1 and b =2 and c>3 and d= 4,如果建立（a,b,c,d）顺序的组合组合索引，
d是用不到索引的，如果建立（a,b,c,d）的索引都用得到，则它们可以任意顺序.
  1. 创建索引的字段应选择频繁查询的字段
  2. 更新频繁的字段不适合用于创建索引
  3. 若是不能有效区分数据的列不适合用于索引
  4. 尽量使用扩展索引，如果表中有索引(a)，现在要交（a,b）索引，那么只需要修改原来的（a）索引即可。
  5. 定义有外键的数据列一定要建立索引。
  6. 对于定义我text,image和bit的数据类型，不要建立索引。
  7. 对于哪些查询中很少设计的列，重复值比较多的列不适合建立索引。

3.7 索引的设计原则

  1. 尽量使用短索引字段，如果对长的字符串进行索引，应指定一个前缀长度，这样可以节省大量索引时间。
  2. 表中的数据如果较少，没必要使用索引。
  3. 适合所有的列是出现在where子句中的列，或连接子句指定的列。
  4. 不要过度使用索引，索引需要额外的磁盘空间，并降低操作的性能，在修改更新表的时候，索引会对更新的进行重构，索引列越多，这个时间就越长，所以索引应更适合用于查询。

3.8 创建索引的三种方式

  1. 第一种，在创建表的时候创建
    格式：
    CREATE TABLE 表名( 属性名 数据类型[完整性约束条件],
    属性名 数据类型[完整性约束条件],
    …
    属性名 数据类型
    [ UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
    [ 别名] ( 属性名1 [(长度)] [ ASC | DESC] )
    );

    [ UNIQUE | FULLTEXT | SPATIAL ] 可选字段：
    UNIQUE ：表示唯一
    FULLTEXT ：全文搜索
    SPATIAL ：空间索引
    INDEX | KEY ：二选一即可，都表示创建索引

例子：

CREATE TABLE user_index2 (
	id INT auto_increment PRIMARY KEY,
	first_name VARCHAR (16),
	last_name VARCHAR (16),
	id_card VARCHAR (18),
	information text,
	KEY name (first_name, last_name),
	FULLTEXT KEY (information),
	UNIQUE KEY (id_card)
);

查询表中索引：show index from 表名

  1. 建立表之后，开始建立索引 
create index 索引名 on 表名(列名);   //创建索引
show index from 表名;
drop index 索引名 on 表名;
  1. 使用alter table命令增加索引 
alter table 表名 add index 索引名(列名)；

alter table可以创建普通索引，唯一索引和主键索引，索引名如果不写时，MySQL默认以第一个列名作为索引名，另外alter table允许在单个语句中修改多个表。

3.9 创建索引需要注意什么？

非空字段：应指定列为NOT NULL，除非你想存储NULL值，在mysql中，含有空值的列很难进行查询优化，因为它们使得索引运算更加复杂，可以用0或空串替换空值。
应取值离散大的字段:离散大的字段说明列变量之间的差异程度就越大，查询到的数据就越精确。
索引的字段越小越好：数据库存储数据以页为单位，一页存储的数据越多，一次IO操作获取的数据效率越低。

3.10 使用索引一定能提高查询效率吗？

通过索引查询数据比全表扫描要快，但是：
索引需要空间来存储，也需要时间来维护，每当有记录在表中增减，索引本身也会被修改，这也是需要消耗性能，所以索引多用于查询。

3.11 百万级数据如何删除

  1. 先删除索引（此时大约耗时3分钟）
  2. 再删除数据库中无用的数据
  3. 删除后重新建立索引，此时因为数据少了，所以创建索引非常快
  4. 再去删除就很快了

4. 事务

4.1 什么是事务

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

4.2 事务的四大特性（ACID）

关系型数据库需要遵循ACID规则

原子性：事务是最小的执行单位，不允许分割，要么全部执行成功，要么全部执行不起作用。
一致性:执行事务前后，数据不变，多个事务对同一数据的读取时相同的,如转账业务，无论事务执行成功与否，参与转账的两个账户余额之和应该保持不变。
隔离性：并发访问数据库时，一个用户的事务不被其它事务所干扰，各个并发事务之间数据库是独立的。
持久性：一个事务提交后，它对数据库中的数据改变是持久的，即使数据库发生故障也不应该对其有任何的影响。

4.3 什么是脏读，幻读，不可重复读

脏读：A事务读取B事务尚未提交的数据，如果此时B回滚，则A事务读取的数据就是脏数据。
幻读：事务A执行，查询第一次数据总量有100条，事务A还没结束，此时事务B介入，多添加了100条数据并提交，事务A再次查询发现数据量变成200条，造成一次事务两次读取的数据总量不一致。
不可重复读：事务A执行，查询第一次的数数据为a = 10，事务A还没结束,此时事务B介入将a改为a = 20并提交，事务A再次查询a = 20，造成一次事务内两次读取数据不一致。

4.4 什么是事务的隔离级别，MySQL的默认隔离级别是什么？

为达到事务的四大特性，数据库定义了4种不同的隔离级别，由低到高依次为

Read Uncommited  ---读未提交
Read Commmited   ---读已提交
Repeatable Read  ---可重复读
Serializable	 ---可串行化

这四个隔离级别可以逐个解决脏读，不可重复读，幻读这几类问题。

隔离级别 脏读 不可重复读 幻读
Read Uncommited √ √ √
Read Commmited × √ √
Repeatable Read × × √
Serializable × × ×

可见
Read Uncommited：允许读未提交是最低的隔离级别，可能会造成解决脏读，不可重复读，幻读。
Read Commmited：允许读并发事务已提交的数据，解决了脏读问题。
Repeatable Read：可重复读，对同一字段的多次读取都是一样的，可以解决脏读，不可重复读，当仍然可能出现幻读。
Serializable：可串行化，最高的隔离级别，完全服从ACID的隔离级别，所有的事务依次逐个执行，互不干扰，该级别可以解决脏读，不可重复读，幻读。

5. 锁

5.1 数据库的乐观锁和悲观锁是什么？怎么实现？

数据库管理系统的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制采取的主要手段。

<mark>乐观锁</mark>：假设不会发生并发冲突，只是在提交操作时检查是否违法数据完整性，在修改数据的时候把事务锁起来，通过version的方式来进行锁定。实现方式：一般使用版本号机制或CAS算法。
<mark>悲观锁</mark>：假设会发生并发冲突，屏蔽一切可能违反数据完整性的操作，在查询的时候就把事务锁起来，直到提交事务。实现方式：使用数据库的锁机制。
<mark>使用场景</mark>：
如果是多写的情况，一般容易引起冲突，悲观锁从一开始就加上锁，从而保证是事务的安全性，所以悲观锁比较适合用于多写。
如果是多读，则冲突会很少发生，这样就省去了锁的开销，于是乐观锁会更适合。

6. 视图

6.1 什么是视图，为什么使用视图？

视图：为了提高复杂SQL语句的复用性和表操作的安全性，MySQL数据库管理系统引入了视图，所谓视图，是一张虚拟表，在物理空间上并不存在，但但内容与真实表相似，包含一系列带有名称的行和列。但是，视图并不在数据库中以存储的数值存在，行和列的数据来于自定义视图的查询所引用的基本表，并在具体引用视图时动态生成。

6.2 视图有哪些特点

  • 视图的列可以来自不同的表，是表的抽象和逻辑意义上建立的新关系。
  • 视图是基本表产生出来的虚表。
  • 视图的建立和删除不影响基本表。
  • 对试图内容的增删改直接影响基本表。
  • 当视图来自多个基本表的时候，不允许添加和删除数据。

6.3 视图的使用场景

视图的根本用途：简化SQL查询，提高开发效率，兼容老的表结构。
使用场景

  1. 重用SQL语句
  2. 简化复杂的SQL语句，在编写SQL后，可以方面的重用它而不必知道它的基本查询细节。
  3. 使用表的部分，而不是整个表
  4. 保护数据，可以给用于授权表的部分访问权限而不是整个表的访问权限。
  5. 更改数据格式和表示，视图可返回与底层表的表示和格式不同的数据。

6.4 视图的优缺点

优点：

  • 查询简单化，视图能简化用户得操作。
  • 数据安全性。视图使用户能够以多种角度看待同一数据，能够对机密数据提供安全保护。
  • 逻辑数据独立性。视图对重构数据库提供一定程度得逻辑独立性。

缺点：

  • 性能。数据库必须把视图的查询转成基本表得查询，如果这个视图是由一个复杂的多表查询所定义，那么即使是一个视图的简单查询，数据库也把他变成一个复杂的结合体，需要花费一定的时间。
  • 修改限制：当用户试图修改视图的某些行时，数据库需要把它转换为对基本表的某些行的修改。对于简单视图，可以对其进行插入和删除数据，但是对复杂的视图，可能是不可以修改的。
    这些视图有以下特征：
  1. 有union集合操作符的视图
  2. 有group by子句的视图
  3. 有聚合函数的视图，如AVG/SUM/MAX
  4. 使用DISTINCT关键字的视图
  5. 连接表的视图

6.5 什么是游标

游标是系统为用户开设的一个缓冲区，存放SQL语句的执行结果，每个游标都有一个名字，用户可以通过游标逐一获取记录并赋值给主变量，交由主语言进一步处理。

7. 存储过程与函数

8. 常用的SQL语句

8.1 SQL语句主要分为哪几类

数据库定义语言(DDL)：增删改，create drop alter
数据库查询语言(DQL)： 查询 select
数据库操纵语言(DML)：操作数据 insert update delete
数据库控制功能(DCL)：权限控制 grant revoke commit rollback

alert和update有什么区别？
alter是修改表结构的，例如增加，修改和删除表字段名
update是修改某一行或者某一列的值

8.2 超键，候选键，主键，外键

超键：在关系中能够唯一标识元组的属性集称为关系模式的超键
候选键：最小的超键，没有多余属性的超键。
主键：数据库表中对存储数据对象给予唯一和完整性标识的数据列或属性的组合，一个数据列只能有一个主键，而且主键不能没有，不能为空。
外键：在一张表中存在的属性字段，在另一张表中是主键。

8.3 SQL约束有几种？

<mark>有5种</mark>：

  1. NOT NULL：字段不能为空

  2. UNIQUE：字段内容唯一

  3. PRIMARY KEY：主键约束，字段在一张表中只允许出现一次

  4. FOREIGN KEY：外键约束，预防破坏表之间的关系也能防止非法数据插入外键列。

    left outer join == left join 
right outer join == right join

  5. 联合查询：union 相同记录合并，union all不会合并相同的记录行

  6. 全连接：MySQL不支持全连接，可以使用外连接

例子：https://blog.csdn.net/weixin_43781127/article/details/89180734

8.5 什么是子查询

条件：一条SQL语句的查询结果作为另一条SQL语句的查询条件或查询结果
嵌套：多条SQL语句嵌套使用，内部的SQL查询称为子查询。

8.6 子查询的三种情况

  1. 查询是单列的情况，结果集是一个值，父查询使用 > < =等运算符： 
select * from emp where eid = (select max(salary) from emp);
  1. 子查询是单列的情况，结果集类似于一个数组，父查询使用in 
select * from emp where eid in (select * from max(salary) fromemp );
  1. 子查询是一个多行多列的情况：结果集类似于一张虚表，不能用于where条件，用于select 子句中作为子表 
1. 查询2011年后入职的员工信息
2. 查询所有的部门信息，与上面的虚表中信息的对比，找出所有部门id相同的员工
select * from dept d,(select * from emp where join_data > 2011) e,where e.dept_id = d.id;
3. 使用表连接
select d.* e.* from dept d inner join emp e where d.id = e.dept_id and e,join_data > 2011;

8.7 MySQL中的in与exists区别？

MySQL的in语句是把外表和内表做hash连接，而exists语句是对外表做loop循环，每次loop再对内标进行查询。
exists的效率并不一定比in高，分如下场景：

  1. 如果两张表的大小相当，那么in和exists的效率相当
  2. 如果一张表大，一张表小，则子查询大的用exists，小的用in
  3. 如果子查询查询语句中用到了not in，那么内外表都进行了全表扫描，并且没有用到索引。
    而not exists查询内外表都会用到索引，因此效率比not in快

8.8 varchar与char有什么区别？

varchar：

  • varchar是可变长字符串，长度可变的。
  • 插入的数据有多大就按照多长来存储。
  • 因为长度不固定，所以存取比较慢，但是不占多余的空间，以时间换空间的做法
  • 对于varchar来做，最多能存放65532个字符

char：

  • 表示的是长字符串，字符串长度固定
  • 如果插入的数据长度 < char的固定长度，空闲位置用空格表示
  • 因为长度固定，唯一存取比较快，但是会占据多余的空间
  • 对于char来说，最多能存放的字符字数255与编码无关

总之，结合性能角度，char(更快)，varchar(更省空间)，所以具体场景具体使用。

8.9 varchar(50)，其中50的含义？

50代表的是50个字符。

8.10 mysql中int(10)，char(10)，以及varchar(10)的区别

  1. int（10）表示的是<mark>显示数据长度</mark>，不是存储数据的大小，char(10)，varchar(10)，表示的是存储10个字符的数据。 
int(10)：的数据长度为占32个字节
char(10)：10为固定长度，不足补空格，最多10个字符。
varchar(10)：10个可变长度，不足补空格，最多10个字符。
  1. char(10)表示存储定长10个字符，不足10个就用空格补齐，占用更多的存储空间。
  2. varchar(10)表示存储10个可变长度的字符，存储几个就是多少个，空格也算一个，这点和char(10)的空格不一样的地方

8.11 float和double的区别

<mark>float：</mark>
float的数据可以最多存储8位10进制，并且在内存中占4个字节
<mark>double：</mark>
float的数据可以最多存储8位16进制，并且在内存中占8个字节

8.12 drop，delete与truncate的区别？

三者都表示删除

delete truncate drop
类型 DML DDL DDL
回滚 可回滚 不可回滚 不可回滚
删除内容 表结构还在，只是删除表的一部分行数据 表结构还在，删除表的数据 从数据库中删除表，连同表的索引和权限一并删除
删除速度 删除速度慢，需要逐行删除 删除速度快 删除速度最快

8.13 union与union all的区别？

union all ：会合并重复的记录行
union：不会合并重复的记录行，效率比union all高

9. SQL优化

9.1 MySQL分页limit

limit用法：

# 查询6-5条数据行数据，5是起始位置，10是查询的条数
select * from table limit 5,10
# 查询一个偏移量到结束所以的记录行
select * from table limit 5,-1
# 如果只给一个参数表示的是返回最大记录行
select * from table 5 
= select * from table 0,5 # 表示返回前5行

如果遇到从2000000行开始查询10条数据

explain  select *from table limit 2000000,10

这种效率很低，可以使用优化方案：

explain select * from table t,(select * from table order by id limit 2000000,10)  a where a.id = .t.id

另外一种方案适用于主键自增

explain select * from table where id>2000000 limit 10

9.2 MySQL慢查询日志

慢查询日志用于为我们快速定位慢查询，用于记录执行时间超过某个临界值的SQL日志，为我们的优化做参考。

# 开启慢查询
	#配置项
	slow_query_log
	# 查看是否开启慢查询
	show variable like 'slow_query_log';
	# 开启慢查询
	set global slow_query_log = on
#设置临界时间
	# 配置项
	long_query_time
	# 查看临界时间
	show variable like 'long_query_time';
	# 设置临界时间
	show variable like 'long_query_time';

实际操作的时候，应该从长时间设置到短时间，将最慢的SQL优化掉。

9.3 MySQL为什么要尽量设置一个主键

主键是保证数据行在数据表中唯一性的保障，设置主键后，可以提高对后续的增删改查的效率和范围的安全性。

9.4 主键使用自增ID还是UUID

使用主键自增，不要使用UUID。
因为在InnoDB存储引擎中，主键是以聚簇索引存在的，在数据量大的情况下，使用主键id的性能要优于使用UUID。
关于主键是聚簇索引，如果没有主键InnoDB会选择一个唯一键作为主键使用，如果没有唯一键，会生成一个隐式的主键。

9.5 为什么字段要求定义约束为not null

null 会占用更多的字符，而且有可能出现很多预期不符的情况。

9.6 如果要存储密码散列，应该使用什么字段进行存储？

密码散列，盐，用户身份证等固定长度的应该使用char来存储，可以节省空间和提高检索效率。

9.7 优化子查询

explaint select * from t_user where id in (select user_id from user_role);

使用join优化

explaint select * from t_user u, user_role r where u.id = r.user_id;

连接join效率更高一点，因为MySQL不需要在内存中创建临时表来完成这个需要两个步骤的查询步骤。

9.8 优化关联查询

  • 确定on或者using子句是否有索引。
  • 确保group by 或 order by只有一个表中的列，这样MySQL才能使用索引。

9.9 优化union查询

union all的效率高于union

9.10 优化where子句

对于此类问题，首先应该定位低效SQL，然后根据SQL语句可能低效的原因做排查，先从索引入手，如果索引没问题考虑一下几方面：
SQL优化的一下方法：

  1. 对查询进行优化，尽量避免使用全表扫描，首先考虑在where 或者oder by后面字段建立索引。
  2. 应尽量避免在where子句中的字段进行null值得判断，这样将使引擎放弃索引而进行全表扫描： 
select * from t where num is null
# 可以在创建表得时候为字段添加默认值 0 表示空，查询得时候可以‘
select * from t where num  = 0;
  1. 应尽量避免在where子句得列中使用 != > <等操作符进行运算，否则引擎会泛起索引而进行全表扫描，在字段类型为varchar的时候，如果要传1，不要直接写1，而是要写'1'，否则,mysql底层会将字段进行运算，导致所有失效。
  2. 应避免在where条件使用or来连接条件，很容易造成引擎放弃所有来进行全表扫描，应使用union替换来优化，参考9.11
  3. in 和 not in也要慎用，否则也可能造成索引失效
    not in会使索引失效，也尽量避免使用in，可以使用between替换 
select * from t where num in(1,2,3);
-- 使用between替换
select * from t where num = 1 and 3;
  1. 下面的查询也将导致全表扫描 
select * from t where name like "%李"

若要提高效率，可以考虑全文检索。
7. 如果在where子句中使用参数，也会导致全表扫描。因为SQL在运行使才会解析全局变量，但优化程序不能将访问计划推迟到运行时，所以它必须在编译时选择，，然而如果在编译时建立访问计划，变量的值还是未知的，因此无法作为索引的选择输入项：

select  id from t where num = @num;
# 优化
select id from t with(index(索引名)) where num = @num;
  1. 应尽量避免使用在where语句中对字段进行表达式操作，将导致全表扫描 
select * from user where id/2= 100;
# 改为
select * from user where id = 100 * 2;
  1. 尽量避免在where子句中对字段进行函数操作，将导致全表扫描 
select  id from t where substring(name,1,3) = 'abc';
# name以abc开头的id 可以改为
select id from t where name  like 'abc%';
  1. 不要再where语句的左边进行 = 函数或算术运算的操作，否则会导致无法正确使用索引。

9.11 优化OR条件

对于包含or的查询子句，如果要利用索引，则or之间的每个列都必须有索引，如果有一个列没有索引，将导致所有索引失效，而且不能使用复合索引，如果没有索引要考虑增加索引

select * from emp where id = 30 or id = 10;

优化

select * from emp where id = 30 union select * from emp where id = 10;