关于java:剑指-Java第-2-弹剑指大厂这份数据库面试总结请收好

1. 数据库根底

1.1 应用数据库的长处

最开始，咱们是将数据保留在 内存 中，这可能保障咱们非常 疾速存取，然而一旦断电，数据就失落了，无奈永恒保留。 于是咱们将数据寄存在 文件 中，这样一来咱们就 可能将数据永恒保留，但每次都要进行频繁的 IO 操作，绝对于内存来讲速度就慢了许多，而且进行查问操作也不不便。 于是，咱们转移到了 数据库 存储，通过这种形式岂但 能将永恒保留数据，而且查问治理也更加高效不便。

1.2 什么是 MySQL

MySQL 是一个关系型数据库管理系统，开源收费，且易扩大，是以后最风行的关系型数据库管理系统之一，在 Java Web 利用方面的利用非常宽泛。其默认端口为 3306。

1.3 数据库三大范式

1. 第一范式：属性原子性

最根本的范式，若数据库表中 所有字段值均为不可合成的原子值，则满足第一范式；

1. 第二范式：记录唯一性，确保表中每列均与主键相干

在第一范式的根底上更进一步，须要确保数据库表中的每列均与主键相干，而不能只与主键的某一部分相干（次要针对联结主键）。即 在一个数据库表中，一个表中只能保留一种数据，不能将多种数据保留在同一张数据库表中；

1. 第三范式：字段冗余性，确保每列均与主键列间接相干，不存在传递依赖

在第二范式的根底上，确保数据表中的 每列数据和主键间接相干，而不依赖于其余非主键，即任何字段不能由其余字段派生；

1.4 MySQL 中自带的权限表

MySQL 通过权限表来管制用户对数据库的拜访，个别是寄存在 mysql 表中，由 mysql_install_db 脚本进行初始化，别离包含：

• user：记录容许连贯服务器的用户账号信息，权限是全局性的；
• db：记录各个账号在不同数据库上的操作权限；
• table_priv：记录数据表级别的操作权限；
• columns_priv：记录数据列级别的操作权限；
• host：配合 db 表对给定主机上数据库级别的操作权限进行更进一步的管制，权限不受 GRANT 和 REVOKE 的影响；

2. 数据类型

次要能够分为 5 大类型，而大类型下又具体划分了不同的子类型：

3. 存储引擎

要查看 MySQL 中所提供的引擎，能够通过如下命令：

show engines;

3.1 罕用存储引擎

3.2 InnoDB vs MyISAM

• InnoDB 的 4 大个性

1. 插入缓冲（Insert Buffer）
2. 二次写（Double Write）
3. 自适应哈希索引（Ahi）
4. 预读（Read Ahead）

• 两者区别

两者次要区别如下：

1. InnoDB 索引是聚簇索引，而 MyISAM 是非聚簇索引；
2. InnoDB 的主键索引的叶子节点存储着行数据，因而主键索引效率高；MyISAM 索引的叶子节点存储的是行数据地址，须要多进行一次寻址操作才可能失去数据；
3. InnoDB 非主键索引的叶子节点存储的是主键和其余带索引的列数据，因而查问时做到笼罩索引更加高效；

3.3 如何抉择存储引擎

• MyISAM：默认的 MySQL 插件式存储引擎，适宜 以读写插入为主，是 Web、数据仓库和其余应用环境下最常应用的引擎之一；
• InnoDB：用于事务处理应用程序，如果 更新删除等操作频率也高 ，或者要 保障数据完整性 ，反对 高并发、外键和事务等；
• Memory：将所有数据保留在 RAM 中，在须要疾速查找援用和其余相似数据状况下，能提供较快的拜访；
• Merge：容许 MySQL DBA 或开发人员将一系列等同的 MyISAM 表以逻辑形式组合在一起并作为一个对象援用，适宜于数据仓库等 VLDB 环境；

4. 存储过程

4.1 定义

存储过程是一个可编程的函数，在数据库中创立并保留，由 SQL 语句和一些非凡的控制结构组成。长处是 容许模块化设计，即一次创立，屡次调用。 是一个预编译的 SQL 语句，当须要屡次执行 SQL 语句时，应用存储过程比单纯 SQL 语句效率更高。

4.2 优缺点

• 长处

1. 因为是预编译，所以执行效率高；
2. 存储过程的代码间接在数据库中，通过存储过程名间接调用，可能缩小网络通讯；
3. 安全性高，执行存储过程须要有肯定权限的用户；
4. 可能重复使用，进步开发效率；

• 毛病

1. 调试艰难
2. 移植艰难
3. 从新编译问题，因为存储过程是运行前编译，因而如果带有援用关系的对象产生扭转时，受到影响的存储过程、包须要从新编译
4. 若在一个程序中大量应用存储过程，到交付使用时就会随着用户需要的扭转而导致数据结构变动，此时系统维护老本较高

5. 事务

5.1 事务定义

事务是一个不可分割的数据库操作序列，也是数据库并发管制的根本单位，其执行后果必须使数据库从一种一致性状态切换到另一中一致性状态。事务是逻辑上的一组操作，要么都执行，要么都不执行。事务可能在数据库提交工作时确保要么所有批改都保留，要么所有批改都不保留。即事务是逻辑上的一组操作，要么都执行，要么都不执行。

5.2 事务的 4 大个性

关系型数据库都须要遵循 ACID 规定：

1. 原子性（Atomicity）

原子性是整个数据库事务中不可分割的工作单位，只有事务中的所有的数据库操作都执行胜利，才代表整个事务胜利，如果其中任一环节执行失败，那么就算曾经执行胜利的 SQL 语句也必须撤销，回滚到事务执行前的状态。即原子性可能保障 动作要么全副实现，要么齐全不起作用。 即事务是最小的执行单位，不容许宰割。

1. 一致性（Consistency）

指事务将数据库从一种一致性状态变为另一种一致性状态。在事务开始前后，数据库的完整性束缚未被毁坏。在事务执行前后，数据可能保持一致，多个事务对对立数据读取的后果雷同。

1. 隔离性（Isolation）

并发拜访数据库时，隔离性要求每个读写事务对其余事务的操作对象可能互相拆散，即一个用户的事务不被其余事务所烦扰，各并发事务间数据库是独立的；

1. 持久性（Durability）

示意事务一旦被提交，其后果就是永久性的，它对数据库中数据的扭转是长久的，即使数据库产生故障也不应该对其产生影响；

5.3 事务隔离级别

5.3.1 脏读、幻读 & 不可反复读

理解事务隔离级别之前，先来看看这几个读的概念：

1. 脏读（Dirty Read）

示意某一事务曾经更新了一份数据，另一个事务在此时读取了同一份数据。以后一个事务撤销操作后，就会导致后一个事务所读取的数据不正确。

1. 幻读（Phantom Read）

在一个事务的两次查问中数据量不统一，如果有一个事务查问了几列数据，同时另一个事务中在此时查问了新的数据，则查问事务在后续查问中，就会发现数据比最开始的查问数据更丰盛。

1. 不可反复读（Non-repeatable Read）

一个事务中两次查问数据不统一，有可能是因为两次查问过程中插入了一个更新原有数据的事务。

留神：不可反复读和幻读的区别在于：

不可反复读的重点在于批改， 比方屡次读取一条记录发现其中某些列的值被批改，而 幻读的重点在于新增或删除，比方屡次读取一条记录发现记录增多或缩小了。

5.3.2 隔离级别

SQL 规范定义了 4 个隔离级别，隔离级别从低到高别离是：

1. READ-UNCOMMITTED（读取未提交）

最低的隔离级别，容许读取尚未提交的数据变更，可能导致脏读、幻读或不可反复读。

1. READ-COMMITTED（读取已提交）

容许读取并发事务曾经提交的数据，可能阻止脏读，但可能导致幻读或不可反复读。

1. REPEATABLE-READ（可反复读）

对同一字段的屡次读取后果时统一的，除非数据是被自身事务本人所批改，可能阻止脏读和不可反复读，但可能导致幻读。

1. SERIALIZABLE（可串行化）

最高的隔离级别，齐全遵从 ACID 的隔离级别，所有事务顺次一一执行，这样事务之间就齐全不可能产生烦扰，可能避免脏读、幻读以及不可反复读、

6. 锁

6.1 定义

当数据库中存在并发事务时，可能会导致数据库中的数据不统一，此时为了保障拜访秩序，咱们就须要用到锁机制。

锁是为了反对对共享资源进行并发拜访，提供数据的完整性和一致性，从而保障在高并发的状况下，拜访数据库时不会呈现问题；

6.2 事务隔离级别与锁的关系

6.3 数据库中死锁的定义及解决办法

• 定义

所谓死锁，指的是两个或多个以上过程在同一资源上互相占用，并申请锁定对方的资源，从而导致恶性循环的景象。

• 解决办法

1. 若不同程序间并发存取多个表，则尽量约定以雷同的程序来拜访表，从而大大降低死锁产生的概率；
2. 同一事务中，尽量一次性锁定所需的所有资源，升高死锁产生的概率；
3. 对于易产生死锁的业务局部，尝试应用降级锁定颗粒度；

6.4 乐观锁 & 乐观锁

• 定义

并发管制可能确保多个事务同时存取数据库中同一数据时不毁坏事务的隔离性和统一性，以及数据库的统一性，而并发管制次要可分为乐观锁（乐观并发管制）和乐观锁（乐观并发管制）。

1. 乐观锁

假设不会产生并发抵触，只在提交事务时查看时候违反数据完整性。批改数据时将事务加锁，通过 version 的形式来进行锁定，个别应用版本号机制或 CAS 算法来实现；

1. 乐观锁

假设会产生并发抵触，屏蔽所有可能违反数据完整性的操作。查问完数据时将事务加锁，直到提交事务，个别应用数据库中的锁机制来实现；

• 应用场景

乐观锁 适宜于 读操作频繁，但写操作较少 的状况，即抵触很少产生的场景，这样可能省去锁的开销，同时加大零碎的吞吐量；

乐观锁 适宜于 写操作频繁，但读操作较少 的状况，即抵触频发的场景；

7. 索引

7.1 定义

所谓索引，就是一种非凡的文件，蕴含数据表中所有记录的援用指针。它是一种数据结构，数据库索引是数据库管理系统中一个排序的数据结构，可能帮助疾速查问、更新数据库表中数据，同时应用 B 树及其变种 B+ 树来实现。用艰深的话来讲就是相当于咱们日常字典中的目录，可能帮忙咱们疾速找到想要的字或词。

7.2 基本原理

应用索引的最终目录是疾速查找具备特定值的记录，如果没有索引，当咱们须要查找某一个值时，只能遍历整张表来查找，这样做查找效率就会大打折扣。

索引的原理也很简略，即 将无序数据变为有序的查问，依据索引查问数据的步骤如下：

1. 将创立了索引的列的内容进行排序
2. 对排序后果生成倒排表
3. 在倒排内容上拼上数据地址链
4. 在查问时，先拿到倒排表内容，而后取出数据地址链，从而取出具体数据

7.3 索引优缺点

• 长处

1. 第一点毫无疑问是 放慢数据的检索速度；
2. 第二点则是 通过应用索引，可能在查问过程中应用优化暗藏器，进步性能。

• 毛病

1. 工夫方面：尽管可能放慢检索速度，然而创立和保护索引也须要工夫，而且随着数据的增多，索引也须要动静保护，这样将会升高增 / 删 / 改的执行效率；
2. 空间方面：索引也是须要占据独立空间的，所以会随着数据的增多而占用更多的物理空间；

7.4 索引类型

7.4.1 逻辑角度

索引从逻辑角度次要可分为 4 种索引，别离是：

1. 主键索引

数据列不容许反复，不容许为 NULL，一个表中只能有一个主键；

1. 惟一索引

数据列不容许反复，容许为 NULL 值，一个表中容许多个列创立惟一索引，能够通过如下两种形式进行创立惟一索引：

• 创立惟一索引：ALTER TABLE table_name ADD UNIQUE(column)
• 创立惟一组合索引：ALTER TABLE table_name ADD UNIQUE(column1, column2)；

1. 一般索引

最根本的索引类型，没有唯一性的限度，容许为 NULL 值，通过如下两种形式来创立惟一索引：

• 创立一般索引：ALTER TABLE table_name ADD INDEX index_name (column)；
• 创立一般索引组合：ALTER TABLE table_name ADD INDEX index_name (column1, column2)；

1. 全文索引

搜索引擎中也在应用的一种技术，通过 ALTER TABLE table_name ADD FULLTEXT (column) 来创立全文索引；

1. 组合索引

多列值组成一个索引，专门用于组合搜寻，其效率大于索引合并；

7.4.2 物理存储角度

1. 汇集索引（clustered index）
2. 非汇集索引（non-clustered index）

7.4.3 数据结构角度

1. BTREE
2. HASH
3. FULLTEXT
4. R-Tree

7.5 索引算法

罕用的索引算法有 Hash 算法 和 B 树算法，别离多两个算法进行简略介绍：

1. B+ 树算法

最罕用的 MySQL 算法，也是 MySQL 默认算法，既可能用于比拟操作符（=、>、<、between 等），也可能用于 like 操作符，只有其查问条件是一个 不以通配符结尾的常量 。底层实现的是 多路均衡查找树，每次查问都要从根节点登程，查找到叶子节点方可取得所查问的键值，而后依据查问判断是否须要回表查问数据。

1. Hash 算法

Hash 算法索引只能用于对等比拟（=、>=、<=），而且不像 B 树索引须要从根节点到枝节点，最初能力范文到页节点进行屡次读写操作，它只须要一次定位数据，所以检索效率远高于 B 树索引。其底层是 Hash 表，进行查找时，调用一次 Hash 函数就能获取相应键值，而后进行回表查问取得理论数据。

1. 两者比照

• Hash 索引进行等值查问更快，然而不能进行范畴查问；
• Hash 索引不反对应用索引进行排序；
• Hash 索引不反对含糊查问以及多列索引的最左前缀匹配，因为 Hash 函数的后果不可预测；
• Hash 索引无奈防止回表查问数据，但 B+ 树在肯定条件下（聚簇索引、笼罩索引等）只须要通过索引实现查问；
• Hash 索引在等值查问时较快，但不稳固，性能不可预测；但 B+ 树的查问效率较稳固，对所有查问均是从根节点到叶子节点，且树的高度较低；

7.6 设计和创立索引的准则

7.6.1 设计准则

1. 抉择唯一性索引

唯一性索引的值惟一，可能更加疾速地通过该索引来确定某条记录；

1. 为常常须要排序、分组和联合操作的字段建设索引

对于常常须要 ORDER BY、GROUP BY、DISTINCT、UNION 等操作的字段，排序时会节约许多工夫，因而咱们能够为其建设索引，防止排序操作；

1. 为常常作为查问条件的字段建设索引

若某个字段常常作为查问条件，则该字段的查问速度将影响整个表的查问速度，此时能够给该字段建设索引，从而进步整个表的查问速度；

1. 限度索引数目

索引并非越多越好，每个索引都须要占用物理空间，索引越多占用的物理空间越大，批改表时对索引的重构和更新将非常麻烦；

1. 尽量应用数据量少的索引

如果索引值较长，查问速度也会受到影响；

1. 应用短索引，尽量应用前缀来索引

如果某索引字段值较长，最好应用值的前缀来进行索引；

1. 删除不再应用或很少应用的索引

表中数据被大量更新，或者数据应用形式被扭转落后，原有的一些索引可能不再须要，此时须要对这些索引进行删除，缩小索引对更新操作的影响；

7.6.2 创立准则

应用索引可能在肯定水平上进步检索效率，但也不能无限度的应用，创立索引时，最好可能满足如下准则：

1. 最左前缀匹配准则
2. 频繁查问的字段才创立索引
3. 更新频繁的字段不适宜创立索引
4. 区分度不高的字段不适宜做索引
5. 尽量扩大索引，而不必去创立新的索引
6. 定义有外键的数据列肯定要建设索引
7. 对于查问中很少波及，而且反复值较多的字段无需建设索引
8. 对于 text、image、bit 类型的字段不要建设索引

7.7 B+ 树索引 和 Hash 索引底层实现

1. Hash 索引

Hash 索引底层其实就是 Hash 表，进行查找时，调用一次 Hash 函数就能获取到响应的键值，而后进行回表查问获取数据库中的数据；

1. B+ 树索引

B+ 树底层实现是多路均衡查找树，对每次的查问均从根节点登程，查找到叶子节点就取得所要查问的键值，而后依据查问判断是否须要回表查问数据；

1. Hash 索引与 B+ 树的不同

• Hash 索引进行等值查问更快，但无奈进行范畴查问。因为 Hash 索引中通过 hash() 函数建设索引后，索引程序与与原程序无奈保持一致，不能反对范畴查问；而 B+ 树的所有节点皆遵循（左节点小于父节点，父节点小于右节点），人造反对范畴查问；
• Hash 索引不反对应用索引进行排序；
• Hash 索引不反对含糊查问以及多列索引的最左前缀匹配，原理也是因为 hash() 函数的不可预测；
• Hash 索引任何时候都必须进行回表查问，但 B+ 树在合乎某些条件时能够只通过索引实现查问；
• Hash 索引尽管等值查问较快，然而极其不稳固，性能不可预测，但某一键值存在大量反复时，会产生 Hash 碰撞，此时效率可能非常低下；而 B+ 树的查问效率比较稳定，对于所有的查问均是从根节点到叶子节点，且树的高度较低；

鉴于以上不同点，因而在大多数状况下，间接选用 B+ 树索引可能取得稳固且较好的查问速度，而不须要应用 Hash 索引；

8. 视图

8.1 视图定义

为了进步简单 SQL 语句的复用性和表操作的安全性，MySQL 数据库管理系统提供了视图。

视图的实质是 一种虚构表，在物理上不存在，其内容与实在的表类似，蕴含一系列带有名称的列和行数据。 但视图并不在数据库中以存储的数据值模式存在，行和列数据来自定义视图的查问所援用根本表，且在具体援用视图时动静生成；

视图的操作个别包含如下四局部：

• 创立视图
• 查看视图
• 删除视图
• 批改视图

8.2 视图特点

1. 视图的列能够来自不同的表，是表的形象在逻辑意义上建设的新关系；
2. 视图是有根本表（实表）产生的表（虚表）；
3. 视图的建设和删除不会对根本表造成影响；
4. 对视图内容的更新（增加、删除和批改）会间接影响到根本表；
5. 当视图来自多个根本表时，不容许增加和删除数据；

8.3 视图优缺点

8.3.1 长处

1. 查问简单化，视图可能简化用户操作，数据所见即所得；
2. 数据安全性，视图使用户能从多个角度对待同一数据，用户只能查问或批改他们所能见到失去的数据，可能对秘密数据提供平安爱护；
3. 逻辑数据独立性，视图对重构数据库提供了肯定水平的逻辑独立性，屏蔽实在表构造变动所带来的影响；

8.3.2 毛病

1. 性能绝对较差，简略的查问也会变得很简单；
2. 批改限度，尝试批改视图时，必须将其转化为比照本表的某些行的批改。对于简单的聚合视图，根本无奈扭转；

9. 优化

9.1 大表优化

当 MySQL 单表记录数过大时，数据库的 CURD 性能会显著降落，此时能够采取如下的优化措施：

1. 限定数据范畴

务必禁止不带任何限度数据范畴条件的查问语句，此时会查问整个数据库，效率极低；

1. 读 / 写拆散

最经典的数据库拆分计划，主库负责写，从库负责读；

1. 垂直分区

即依据数据库中数据表的相关性进行拆分，简略来讲就是指数据表的拆分，将一张列较多的表分为多张表。这样操作使得 列数据变小，在查问时缩小了读取的 Block 数，缩小了 I/O 次数。同时，垂直分区也可能简化表的构造，易于保护 。然而，垂直拆分也存在肯定毛病。首先拆分将 导致主键呈现冗余，此时就须要治理冗余列，同时会引起 Join 操作，能够通过在应用层进行 Join 来解决。此外，拆分还会让事务变得更加简单。

1. 程度分区

放弃数据表构造不变，通过某一策略存储数据分片。这样一来每一片数据扩散到不同表或库中，从而达到分布式的目标，而且通过程度切分可能撑持十分大的数据量。

程度拆分是将数据表的行进行拆分，它可能 撑持十分大的数据量存储 ，利用端革新也少，然而 分片事务难以解决，跨节点 Join 性能较差，逻辑简单。

一般来讲数据库分片操作的两种常见计划如下：

• 客户端代理：分片逻辑在利用端，封装在 jar 包中，通过批改或封装 JDBC 层来实现。
• 中间件代理：在利用和数据间加一个代理层，分片逻辑对立保护在中间件服务中。

10. 其余

10.1 sql 注入

用户传入的参数中合乎 sql 的语法，从而毁坏原有 sql 构造语义，从而达到攻打成果；

10.2 NULL 和空串

NULL 是没有值的，不是空串，如果只指定‘’（两个单引号，两头无任何字符），对于 NOT NULL 列是容许的，空串也是一个无效的值；

要对 NULL 进行判断，则须要应用 IS NULL 或者 IS NOT NULL；

10.3 如何创立用户并受权

1. 创立用户

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

1. 受权

GRANT privileges ON databasename.tablename TO 'username'@'host';

10.4 如何删除表