数据库面经

数据库优化

1.选择合适的字段属性
2.尽量不使用select * from test 而使用精确到某些字段来查询
3.尽量少使用子（嵌套）查询，利用join内连接或者外连接来代替子查询
4.使用索引，避免全表扫描
5.不使用IN，NOT IN 和 != 操作
6.大多是时候使用exists代替in是一个好的选择

1.SQL注入

SQL注入就是通过把SQL命令插入到Web表单提交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。
1）SQL注入攻击的总体思路
​ ●寻找到SQL注入的位置
​ ●判断服务器类型和后台数据库类型
​ ●针对不同的服务器和数据库特点进行SQL注入攻击
2）应对方法
​ ●使用正则表达式过滤传入的参数
​ ●参数绑定
​ ●使用预编译手段，绑定参数是最好的防SQL注入的方法。

2.请你说明一下 left join 和 right join 的区别？

left join(左联接) ：返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) ：返回包括右表中的所有记录和左表中联结字段相等的记录

left join

right join

3.事务是什么

事务是应用程序中一系列严密的操作，所有操作必须成功完成，否则在每个操作中所作的所有更改都会被撤消。也就是事务具有原子性，一个事务中的一系列的操作要么全部成功，要么一个都不做。

4.数据库ACID的特性

原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性指事务前后数据的完整性必须保持一致。
隔离性指多个用户并发访问数据库时，一个用户的事务不能被其他用户的事务所干扰，多个并发事务之间数据要相互隔离。
持久性是指一个事务一旦提交，它对数据库中数据的改变就是永久性的，即便数据库发生故障也不应该对其有任何影响。

5.请你介绍一下，数据库的三个范式？

第一范式（1NF）：强调的是列的原子性，即列不能够再分成其他几列。

第二范式（2NF）：首先满足 1NF，另外包含两部分内容，一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。

第三范式（3NF）：首先是满足2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况
（范式理论是为了避免产生以下异常：1.冗余数据，2.修改异常，3.删除异常，3.插入异常）

6.数据库的脏读、幻读、不可重复读

1.脏读：指在一个事务处理过程中读取了另外一个未提交的事务中的数据。

指一个事务A正在访问数据，并且对该数据进行了修改，但是这种修改还没有提交到数据库中（也可能因为某些原因Rollback了）。这时候另外一个事务B也访问这个数据，然后使用了这个被A修改的数据，那么这个数据就是脏的，并不是数据库中真实的数据。这就被称作脏读。(事务A读到了事务B未提交的数据)

2.不可重复读：一个事务范围内多次查询数据库中的某个数据返回不同的数据值，读取了前一事务提交的数据。

指在一个事务A内，多次读同一个数据，但是事务A没有结束时，另外一个事务B也访问该同一数据。那么在事务A的两次读数据之间，由于事务B的修改导致事务A两次读到的数据可能是不一样的。这就发生了在一个事务内两次读到的数据不一样，这就被称作不可重复读。（事务A多次读数据，事务B访问数据，A读到了B修改的数据，导致两次读到的数据不一样）

3.幻读：事务非独立执行时发生的一种现象。第一个事务修改表中全部数据行，另外一个事务插入一行新数据，当操作第一个事务的用户发现表中还有没有修改的数据行，以为产生幻觉。

指一个事务A对一个表中的数据进行了修改，而且该修改涉及到表中所有的数据行；同时另一个事务B也在修改表中的数据，该修改是向表中插入一行新数据。那么经过这一番操作之后，操作事务A的用户就会发现表中还有没修改的数据行，就像发生了幻觉一样。这就被称作幻读。(事务A修改数据，事务B插入数据，A发现表中还没有修改的数据行)

级别高低：脏读 < 不可重复读 < 幻读

所以设置了最高级别的SERIALIZABLE_READ就不需要设置其他的了，即解决了幻读问题那么脏度和不可重复读自然就都解决了。

6.1数据锁的种类，加锁的方式？

（1）共享锁，共享锁的锁粒度是行或者元组（多行）简称为S锁，又称读锁。一个事务获取了共享锁之后，可以对锁定的范围内的数据执行读操作。
（2）排他锁，锁粒度和共享锁一样,简称X锁，又称写锁。一个事务获取了排他锁后，可以对锁定的范围内的数据执行写操作。
（3）更新锁，防止当多个会话在读取、锁定及其随后可能进行的资源更新时发生常见形式的死锁。
（3）意向锁，锁粒度是整张表，分为意向共享锁（IS）和意向排他锁（XS）两类。表示有意向执行锁操作但是还没有真正执行。

7.请你介绍一下数据库的隔离级别

未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据。
已提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)
可重复读(Repeated Read)：在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读。
串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞。

8.请你简单介绍一下，数据库水平切分与垂直切分

垂直拆分就是要把表按模块划分到不同数据库表中，单表大数据量依然存在性能瓶颈
水平切分就是要把一个表按照某种规则把数据划分到不同表或数据库里。
通俗理解：水平拆分行，行数据拆分到不同表中， 垂直拆分列，表数据拆分到不同表中。

9.请你讲讲 Statement 和 Prepared Statement 的区别？哪个性能更好？

与Statement相比，
①PreparedStatement接口代表预编译的语句，它主要的优势在于可以减少SQL的编译错误并增加SQL的安全性（减少SQL注射攻击的可能性）；
②PreparedStatement中的SQL语句是可以带参数的，避免了用字符串连接拼接SQL语句的麻烦和不安全；
③当批量处理SQL或频繁执行相同的查询时，PreparedStatement有明显的性能上的优势，由于数据库可以将编译优化后的SQL语句缓存起来，下次执行相同结构的语句时就会很快；

10.mysql数据库的索引类型

索引数据结构：二叉树、红黑树（在大规模数据存储的时候，红黑树往往出现由于树的深度过大而造成磁盘IO读写过于频繁，进而导致效率低下的情况。）、hash表（Hash索引仅仅能满足“=”，不能支持范围查询，比如大于某个范围的查询）、
B-tree：
b+数的数据都集中在叶子节点，分支节点只负责索引，b树的每个节点都存放数据
b+数更适合范围查询，叶子节点是顺序的双向链表，b数范围查询只能中序遍历
b+数索性节点没有数据，可以把索性完全加载进没错

1、普通索引这是最基本的索引，它没有任何限制。

2、唯一索引它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

3、主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。

4、组合索引（多列索引）指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。

5、全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较。

10.1索引作用优点：

1.索引大大减少了服务器需要扫描的数据量
2.索引可以帮助服务器避免排序和临时表
3.索引可以将随机I/O变为顺序I/O

10.2索引的使用条件

1.对于非常小的表，大部分情况下全表扫描比建立索引更高效
2.对于中到大型表建立索引就非常有效
3.对于特大型表，建立和维护索引的代价也会随之增长。这种情况下，就可以使用分区技术（可以直接区分处需要查询的一组数据，而不是一条一条记录去查询）

11.InnoDB索引实现（聚集）

数据文件本身就是索引文件
表数据文件本身就是按B+树组织的一个索引结构文件
聚集索引的叶子节点包含了完整的数据记录
表必须有主键，且推荐使用整型的自增主键
普通索引结构叶子节点存储的是主键值
InnoDB主键索引查找流程：通过.ibd文件找到对应的索引，索引的value即为那行对应的完整数据

12.聚集索引和非聚集索引的区别？

聚集索引：表中那行数据的索引和数据都合并在一起了。
非聚集索引：表中那行数据的索引和数据是分开存储的。

13.一个 SQL 执行的很慢，我们要分两种情况讨论：

1、偶尔很慢，则有如下原因
(1)、数据库在刷新脏页，例如 redo log 写满了需要同步到磁盘。
(2)、执行的时候，遇到锁，如表锁、行锁。
2、这条 SQL 语句一直执行的很慢，则有如下原因。
(1)、没有用上索引：例如该字段没有索引；由于对字段进行运算、函数操作导致无法用索引。
(2)、数据库选错了索引。

14.关系型数据库和非关系型数据库的区别？

关系型数据库，通过外键关联来建立表与表之间的关联。适合一般量级的数据（Oracle，mysql）
优点：数据之间的有关系，进行数据的增删改查的时候非常方便。
关系型数据库是有事务操作的，保证数据的完整性和一致性。
缺点：底层运行大量算***降低系统效率。
面对海量数据的增删改查无能为力。

15.非关系型数据库，通常指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定。适合海量数据，保证效率，不一定安全（统计数据，例如微博数据）

MongoDb，redis，Hbase
优点：可以在海量数据中增删改查
海量数据的维护和处理轻松
缺点：数据和数据之间没有关系，单独存在
没有强大的事务关系，没有保证数据的完整性和安全性

16.常用SQL语句（DDL，DML，DCL，TCL四种语言）

DDL，数据库定义语言
CREATE创建，ALTER修改，DROP删除，TRUNCATE删除表中全部行，快。COMMENT注释，
DML数据操作语言
SELECT查询，INSERT添加，UPDATE更新，DELETE删除，CALL调用一个PL/SQL或Java子程序，EXPLAIN PLAN解释分析访问路径，LOCK TABLE锁，用于控制并发。
DCL数据控制语言
GRANT授权，REVOKE收回已授予的权限。
TCL事务控制语言
COMMIT提交，保存已完成的工作，SAVEPOINT在事务中设置保存点，可以回滚到此处，ROLLBACK回滚，SET TRANSACTION设置当前事务特性

18.为什么进行子查询（嵌套查询）？

两张表数据比较大，进行左连接或者右连接操作时会造成死机。为了解决这个问题，通过子查询实现多表查询。子查询，指在一个查询之中嵌套了其他的若干查询，也被称为嵌套查询。

19.数据库的索引类型？

什么是索引？索引是创建在数据库表对象上的，由表中的一个字段或多个字段生成的键组成，这些键存储在数据结构（B-树或哈希表）中。根据索引的存储类型，可以将索引分为B型树索引（BTREE）和哈希索引（HASH）。
索引类型：
唯一索引，unique。每一个索引值只对应唯一的数据记录，对于单列唯一性所有，保证单列不包含重复的值。对于多列唯一性索引，保证多个值的组合不重复。
主键索引，promary key。主键索引是唯一索引的特定类型。数据库表有一列或列组合，其值唯一标识表中的每一行。
聚集索引，cluster。表中行的物理顺序与键值的逻辑顺序相同，一个表只能包含一个聚集索引。

20.聚集索引和非聚集索引的区别？

聚集索引一个表只能有一个，非聚集索引一个表可以存在多个。
聚集索引存储记录是物理上连续存在（字典拼音排序），而非聚集索引是逻辑上连续，物理上不连续（字典笔画排序）。

21.唯一索引和主键索引的区别？

（1）主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。
（2）唯一性索引列允许空值，主键列不允许为空值。
（3）主键可以被其他表用于外键，而唯一索引不能。
（4）一个表最多只能创建一个主键，但可以创建多个唯一索引。

22.索引的优缺点，什么时候用索引？什么时候不适合用索引？

创建索引可以提高查询速度，但过多的创建索引会占据许多的磁盘空间。
以下情况适合创建索引：
（1）经常被查的字段，即在where子句中出现的字段
（2）在分组的字段，即在group by子句中出现的字段
（3）存在依赖关系的子表和父表之间的联合查询，即主键或外键字段
（4）设置唯一完整性约束的字段

以下情况不适合创建索引：
（1）在查询中很少被使用的字段
（2）拥有许多重复值的字段

23.索引的底层实现（B+树，为何不采用红黑树，hash表）

索引底层由B+ 树实现。
B树（1.b+数的数据都集中在叶子节点，分支节点只负责索引，b树的每个节点都存放数据
2.b+数更适合范围查询，叶子节点是顺序的双向链表，b数范围查询只能中序遍历
3.b+数索性节点没有数据，可以把索性完全加载进没错）
红黑树（在大规模数据存储的时候，红黑树往往出现由于树的深度过大而造成磁盘IO读写过于频繁，进而导致效率低下的情况。）
hash表（Hash索引仅仅能满足“=”，不能支持范围查询，比如大于某个范围的查询）

24.mysql的优化（高频，性能优化，索引优化）

（1）性能优化，（2）索引优化，（3）配置优化（设置连接请求数和缓冲区变量）

性能优化，通过show命令查看mysql状态及变量。使用慢查询分析，可以通过打开log文件查看得知哪些SQL执行效率低下。日志记录查询时间超过5秒的sql。explain分析查询，可以模拟优化器执行sql查询语句，从而制定mysql是如何处理你的sql语句的，帮助分析你的查询语句或是表结构的性能瓶颈。profiling分析查询，通过慢日志查询可以知道哪些SQL语句执行效率低下，通过explain我们可以得知SQL语句的具体执行情况，索引使用等，还可以结合show命令查看执行状态。
如果觉得explain的信息不够详细，可以同通过profiling命令得到更准确的SQL执行消耗系统资源的信息。

索引优化，越小的数据类型通过更好；简单的数据类型更好，整型数据比起字符处理开销更小；尽量避免NULL，应该指定列为NOT NULL，除非想存储NULL。

25.数据库引擎介绍，innodb和myisam的特点和区别？

myisam：每个myisam在磁盘上存储成三个文件（非聚集索引）
innodb：所有的表都保存在同一个数据文件中，innodb表的大小受限于操作系统文件的大小，一般为2GB。（聚集索引）
事务：InnoDB是事务型的，可以使用Commit和Rollback语句
并发：MyISAM只支持表级锁，而InnoDB还支持行级锁
外键：InnoDB支持外键（一个表中的主键，在另外一个表中不是主键，则该字段是另外一个表的外键）
备份：InnoDB支持在线热备份
崩溃恢复：MyISAM崩溃后发生损坏的概率比InnoDB高很多，而且恢复速度也更慢

26.数据库中事务的四大特性。

（1）原子性A：事务中所有的操作视为一个原子单元，即对于事务所进行的数据修改等操作只能是完全提交或者完全回滚。
（2）一致性C：事务在完成时，必须使所有的数据从一种一致性状态变更为另外一种一致性状态，所有的变更都必须应用于事务的修改，以确保数据的完整性。（两用户转钱，一共5000，不管如何转账，总和不变）
（3）隔离性I：当多个用户并发访问数据库时，比如操作同一张表，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。
（4）持久性D：事务完成之后，所做的修改对数据的影响是永久的，即使系统重启或者出现系统故障数据仍可以恢复。

27.数据库不考虑事务的隔离会出现什么问题？

更新丢失：事务A，B并发执行，A事务执行更新后提交，B事务在A事务更新后，B事务结束前也对数据更新，然后回滚，则两次更新操作都丢失了。
脏读：指在一个事务处理过程中读取了另外一个未提交的事务中的数据。
不可重复读：一个事务范围内多次查询数据库中的某个数据返回不同的数据值，读取了前一事务提交的数据。
虚读（幻读）：事务非独立执行时发生的一种现象。第一个事务修改表中全部数据行，另外一个事务插入一行新数据，当操作第一个事务的用户发现表中还有没有修改的数据行，以为产生幻觉。

28.数据库的隔离级别，mysql和oracle默认隔离级别分别是什么？

标准sql规范定义了四个事务隔离级别：
（1）read-uncommitted未授权读取/未提交读
允许脏读取但不允许更新丢失。
（2）read-committed授权读取/提交读
允许不可重复读取但不运行脏读取。
（3）repeatable-read可重复读取
禁止不可重复读取和脏读取，但有时可能会出现幻影数据。
（4）serializable串行/可串行读
事务只能一个接着一个地执行，不能并发执行。
mysql默认隔离级别：repeatable-read
oracle支持read-committed和serializable两种事务隔离级别。默认为read-committed

29.数据库连接池作用？

连接池是创建和管理多个连接的一种技术，这些连接可被需要使用它们的任何线程使用。
连接池提供一套高效的连接分配、使用策略，最终实现连接的高效、安全复用。数据库连接池技术带来的优势：
（1）资源复用（2）更快的系统响应速度（3）统一的连接管理，避免数据库连接的泄漏。

30.mysql的表空间方式，各自特点？

（1）共享表空间：所有的表数据，索引文件全部放在一个文件中。
（2）独占表空间：每一个表都会生成独立的文件进行存储。
相比之下，独占表空间效率及性能会更好一些。

31.分布式事务？

单数据源的一致性依靠单机事务来保证，多数据源的一致性就要依靠 分布式事务。（例如，库存数据库和订单数据库）
分布式事务用于在分布式系统中保证不同节点之间的数据一致性。分布式事务的实现有多种，最具有代表性的是由oracle tuxedo系统提出的XA分布式事务协议，该协议包含两阶段提交和三阶段提交两种实现。
XA两阶段提交有以下不足：（1）性能问题，所有节点提交后才释放资源（2）协调者单点故障，协调者挂掉，参与者会一直处在中间状态（3）丢失消息导致的不一致问题，发生局部网络问题，部分参与者未收到消息，导致节点之间数据不一致。

32.数据库的范式

第一范式（1NF）：数据表中的每一列，必须是不可拆分的最小单元，也就是确保每一列的原子性。
第二范式（2NF）：满足第一范式后，表中所有列，都必须依赖主键，不能有任何一列与主键没有关系。
第三范式（3NF）：满足第二范式后，要求表中的每一列都有与主键直接相关，而不能间接相关。
第二范式和第三范式本质区别是：有没有分出两张表。
第二范式是说一张表中包含了多种不同实体的属性，那么必须要分成多张表，蛋散范式是要求已经分好了多张表，一张表中只能有另一张表的ID，而不含有其他信息。

33.数据锁的种类，加锁的方式？

（1）共享锁，共享锁的锁粒度是行或者元组（多行）简称为S锁，又称读锁。一个事务获取了共享锁之后，可以对锁定的范围内的数据执行读操作。
（2）排他锁，锁粒度和共享锁一样,简称X锁，又称写锁。一个事务获取了排他锁后，可以对锁定的范围内的数据执行写操作。
（3）更新锁，防止当多个会话在读取、锁定及其随后可能进行的资源更新时发生常见形式的死锁。
（3）意向锁，锁粒度是整张表，分为意向共享锁（IS）和意向排他锁（XS）两类。表示有意向执行锁操作但是还没有真正执行。

35.如何避免全表扫描？（什么情况下设置了索引但无法使用）

1.对查询进行优化，应考虑在 where 及 order by 涉及的列上建立索引。
2.应尽量避免在 where 子句中对字段进行 null 值判断
3.应尽量避免在 where 子句中使用!=或<>操作符
4.in 和 not in 要慎用否则将导致引擎放弃使用索引而进行全表扫描
5.应尽量避免使用模糊查询like
6.count（*）会导致全表扫面

36.mysql主从复制过程，binlog记录格式，异步复制、同步复制、半同步复制模式区别

MySQl主从复制：
原理：将主服务器的binlog日志复制到从服务器上执行一遍，达到主从数据的一致状态。
过程：从库开启一个I/O线程，向主库请求Binlog日志。主节点开启一个binlog dump线程，检查自己的二进制日志，并发送给从节点；从库将接收到的数据保存到中继日志（Relay log）中，另外开启一个SQL线程，把Relay中的操作在自身机器上执行一遍
优点：
作为备用数据库，并且不影响业务
可做读写分离，一般是一个写库，一个或多个读库，分布在不同的服务器上，充分发挥服务器和数据库的性能，但要保证数据的一致性

binlog记录格式：statement、row、mixed
​ 基于语句statement的复制、基于行row的复制、基于语句和行（mix）的复制。其中基于row的复制方式更能保证主从库数据的一致性，但日志量较大，在设置时考虑磁盘的空间问题

异步复制：
​ 在异步复制中，主库执行完操作后，写入binlog日志后，就返回客户端，这一动作就结束了，并不会验证从库有没有收到，完不完整，所以这样可能会造成数据的不一致。

半同步复制：
​ 当主库每提交一个事务后，不会立即返回，而是等待其中一个从库接收到Binlog并成功写入Relay-log中才返回客户端，所以这样就保证了一个事务至少有两份日志，一份保存在主库的Binlog，另一份保存在其中一个从库的Relay-log中，从而保证了数据的安全性和一致性。

全同步复制：
​ 指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。

37.分页和分段的区别？

页是信息的物理单位，段是逻辑段位
页的大小固定且由系统决定，段的长度不固定
分页的作业地址是一维的，分段的作业地址是二维的，既要段号，又要段地址

38.CHAR和VARCHAR的区别？

char:定长，即可容纳字符数，char的字符如果跟给定的长度不符合，用空格补齐，取出的时候再把空格去掉，所以如果内容有空格，会被自动消除掉。速度上，定长快一点，效率高一点
varchar:变长，如果经常修改，就比较适合。

39.锁的优化策略

1、读写分离:因为读的次数远远大于写的次数，所以读写锁分离

2、分段加锁：减小锁的粒度。但也不能将锁的粒度过于细化，不然可能会出现线程的加锁和释放次数过多，反而效率不如一次加一把大锁。

3、减少锁持有的时间：在需要锁竞争的之后在加锁，减少锁的占有时间，以减少线程间互斥的可能。

4.锁粗化：一个线程如果多次请求一个锁，则将多次锁操作整合成对锁的一次请求，从而减小对锁请求的同步次数。
JVM:锁偏向 ，轻量级锁,重量级锁，自旋锁，锁消除

40.什么情况下不宜建立索引？

1.数据频繁修改的字段
2.where语句查询用不到的
3.有重复且均分布均匀的字段不适合；
4.数据已经很少也不适合建索引；
5.参与列计算的列不适合建索引。

41.MVCC的含义，如何实现的

MVCC是一种多版本并发控制机制。MVCC是通过保存数据在某个时间点的快照来实现的。(相当于cas机制)
在每个表后添加创建版本号和删除版本号，版本号为修改该项的事务版本号。
读取只能读取创建版本号小于该事务版本号以及无删除版本号或删除版本号大于事务版本号的项。
update操作将旧数据标记为删除，插入相同的新数据就新创建版本号。