常见数据库面试题

MYSQL的两种存储引擎区别

Innodb引擎：
提供了对数据库ACID事务的支持，并且实现了sql标准的四种隔离级别，事务安全的。如果在应用中执行大量的insert和update操作，应选择innodb引擎。
支持行级锁
不支持全文索引。
支持外键

MyIASM引擎：
没有提供对数据库事务的支持，非事务安全的，如果在应用中执行大量的select操作，应选择MYIASM引擎。
锁的粒度是表级的，插入数据时，锁定整个表。
支持全文索引类型
相对简单性能优。
不支持外键

数据库三范式

第一范式：1NF是对属性的原子性约束，要求属性具有原子性，不可再分解，“每一字段只存储一个值”。

第二范式：2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性;将1NF转换成2NF的方法是添加主键。例如：学号，姓名，课程名，成绩

第三范式：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。满足3NF的表中不包含传递依赖

三大范式是一级一级依赖的，第二范式建立在第一范式上，第三范式建立第一第二范式上。

基本表及其字段之间的关系, 应尽量满足第三范式。但是，满足第三范式的数据库设计，往往不是最好的设计。为了提高数据库的运行效率，常常需要降低范式标准：适当增加冗余，达到以空间换时间的目的。

例如，商品中的“单价、数量、金额”三个字段，“金额”就是由“单价”乘以“数量”派生出来的，它就是冗余，而且是一种高级冗余。冗余的目的是为了提高处理速度。只有低级冗余才会增加数据的不一致性，因为同一数据，可能从不同时间、地点、角色上多次录入。因此，我们提倡高级冗余(派生性冗余)，反对低级冗余(重复性冗余)。

数据库事务

事务就是被绑定在一起作为一个逻辑工作单元的SQL语句分组，如果任何一个语句操作失败那么整个操作就被失败，以后操作就会回滚到操作前状态，或者是上有个节点。为了确保要么执行，要么不执行，就可以使用事务。

事务的四个特性：
1、原子性，即不可分割性，事务要么全部被执行，要么就全部不被执行；
2、隔离性，事务在正确提交前，其它事务不能看到过程
3、一致性，事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。那转账举栗子，A账户和B账户之间相互转账，无论如何操作，A、B账户的总金额都必须是不变的。
4、持久性，一旦事务提交，其结果将永久保存在数据库中，对数据的改变就是永久的

数据库隔离级别

如果不考虑隔离性，会发生以下情况：
1、脏读：一个事务在处理数据的过程中，读取到另一个未提交事务的数据。

2、不可重复读：对于数据库中的某个数据，一个事务范围内的多次查询却返回了不同的结果，这是由于在查询过程中，数据被另外一个事务修改并提交了。

3、幻读：是事务非独立执行时发生的一种现象。例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。

幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。

为解决以上情况，数据库隔离级别有以下四种：

1、读未提交（Read uncommitted）：
这种事务隔离级别下，select语句不加锁。
此时，可能读取到不一致的数据，即“读脏 ”。这是并发最高，一致性最差的隔离级别。

2、读已提交（Read committed）：
可避免 脏读 的发生。Oracle默认隔离级别。
在互联网大数据量，高并发量的场景下，几乎不会使用 上述两种隔离级别。

3、可重复读（Repeatable read）：
MySql默认隔离级别。
可避免 脏读 、不可重复读 的发生。

4.串行化（Serializable ）：
以锁表的方式(类似于Java多线程中的锁)使得其他的线程只能在锁外等待。
可避免 脏读、不可重复读、幻读 的发生。

以上四种隔离级别最高的是 Serializable 级别，最低的是 Read uncommitted 级别，当然级别越高，执行效率就越低。

索引的优缺点

优点是提高查询速度。
缺点是更新数据时效率低，因为要同时更新索引，并且占用额外存储空间。

索引适用的场景：
对数据进行频繁查询，更新较少。

索引不适用的场景：
1、频繁更新的字段不要使用索引。
会增大数据库工作量，降低效率。

2、数据唯一性差（一个字段的取值只有几种时）的字段不要使用索引
比如性别，只有两种可能数据。意味着索引的二叉树级别少，多是平级。这样的二叉树查找无异于全表扫描。

3、 字段不在where语句出现时不要添加索引,如果where后含IS NULL /IS NOT NULL/ like ‘%输入符%’等条件，不建议使用索引，只有在where语句出现，mysql才会去使用索引

4、 where 子句里对索引列使用不等于（<>），使用索引效果一般

索引的底层实现

1、hash索引，等值查询效率高，不能排序,不能进行范围查询
2、B+索引，数据有序,范围查询
（1）红黑树，增加，删除，红黑树会进行频繁的调整，来保证红黑树的性质，浪费时间
（2）B树，查询性能不稳定，查询结果高度不致，每个结点保存指向真实数据的指针，相比B+树每一层每屋存储的元素更多，显得更高一点。
（3）B+树相比较于另外两种树,显得更矮更宽，查询层次更浅

索引种类

聚集索引：数据按索引顺序存储，叶子结点存储真实的物理数据
非聚集索引：存储指向真正数据行的指针
普通索引：最基本的索引，没有任何限制
唯一索引：与"普通索引"类似，不同的是索引列的值必须唯一，但允许有空值。
主键索引：它是一种特殊的唯一索引，不允许有空值。
全文索引：针对较大的数据，生成全文索引很耗时好空间。
组合索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则

聚集索引确定表中数据的物理顺序，一个表只能有一个聚集索引，但是这个索引可以包含多个列（组合索引）。最好是在创建表的时候同时添加聚集索引，因为聚集索引有序，等数据有了再搞会耗费时间及性能（排序、移动数据行上的顺序）。

非聚集索引跟课本类似，数据存储在一个地方，索引存储在一个地方。叶子节点是一个指向最终数据的指针。

索引失效的条件

1、条件中用or，即使其中有条件带索引，也不会使用索引查询（这就是查询尽量不要用or的原因，用in吧）
注意：使用or，又想索引生效，只能将or条件中的每个列都加上索引。
2、复合索引未用左列字段;
3、like的模糊查询以%开头，索引失效。
4、存在索引列的数据类型隐形转换，比如列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不会使用索引。
5、如果MySQL预计使用全表扫描要比使用索引快，则不使用索引，比如数据很少时。
6、where中索引列有运算或者索引列使用了函数，索引失效

索引最左匹配原则

最左匹配原则是针对索引的。

举例来说：两个字段（name,age）建立联合索引，如果where age=12这样的话，是没有利用到索引的。
这里我们可以简单的理解为先是对name字段的值排序，然后对age的数据排序，如果直接查age的话，这时就没有利用到索引了，

查询条件where name=‘xxx’ and age=xx 这时的话，就利用到索引了

再来思考下where age=xx and name=’xxx‘ 这个sql会利用索引吗，按照正常的原则来讲是不会利用到的，但是优化器会进行优化，把位置交换下。这个sql也能利用到索引了

数据库的主从复制

mysql主从复制的原理就是把主服务器上的bin日志复制到从服务器上执行一遍，这样从服务器上的数据就和主服务器上的数据一致。

在mysql5.5版本之前，复制都是异步复制。

异步复制：
默认异步复制，容易造成主库数据和从库不一致。
slave两个线程：
一个线程去读master binlog日志，写到自己的中继日志
一个线程解析日志，执行sql
master启动一个线程，给slave传递binlog日志。

半同步复制：
只有把master发送的binlog日志写到slave的中继日志，这时主库,才返回操作完成的反馈，性能有一定降低。

同步复制：
Master提交事务，slave把master发送的binlog日志写入中继日志，直到事务在所有的Slave都已提交，此时才会返回客户端，事务执行完毕。缺点：完成一个事务可能会有很大的延迟。

慢查询

1、开启慢查询，可以让mysql记录下查询超过指定时间的语句，通过定位分析性能的瓶颈，才能更好地优化数据库系统的性能。
slow_query_log 慢查询开启状态
slow_query_log_file 慢查询日志的存放位置
long_query_time查询超过多少秒才记录

2、可以通过设置全局变量的方法设定：
例如：set gloable slow_query_long on 开启慢查询状态
service mysqld restart 即可

3、查询对应值：
show variables like’slow_query%’;
show variables like ‘long_query_time’;

4、测试慢查询是否正确开启：
select sleep(2); 执行慢查询语句，查看是否有对应的慢查询日志生成。

SQL语言包括哪些？

数据定义、数据操纵(Data Manipulation),数据控制(Data Control)
数据定义：Create Table,Alter Table,Drop Table, Craete/DropIndex等
数据操纵：Select ,insert,update,delete,
数据控制：grant,revoke,commit,rollback

常见的sql语句

创建数据库
create database westos；
删除数据库
drop database westos;
查询user表中的Host,User,Password信息
select host,user,password from user;
查询user表的数据结构
desc user;
新建数据库westos中的表格
use westos;
create table linux(
username varchar(50) not null,
password varchar(50) noe null
);
在linux表格中插入信息
insert into linux values(‘xyy’,‘123’);
修改linux表格中的信息
update linux set password ‘456’ where username=‘xyy’;
在linux表格中添加age字段
alter table linux add age varchar(4) after username;
在linux表格中移除age字段
alter table linux drop age;
修改表格名字
alter table linux rename haha;
删除表格中的某一行
delete from haha where username=‘xyy’;
删除表格
drop table haha;

truncate与 delete区别

TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。
但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。

DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。

TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

TRUNCATE,DELETE,DROP 放在一起比较：
TRUNCATE TABLE ：删除内容、释放空间但不删除定义。
DELETE TABLE: 删除内容不删除定义，不释放空间。
DROP TABLE ：删除内容和定义，释放空间。

char和Varchar使用场景

char：字符长度固定的
varchar：字符长度经常变的
char的效率要比varchar的效率高。

比如，存储字符串“abc”对于char(20)，表示你存储的字符将占20个字节，包含17个空，而同样的varchar2（20）只占了3个字节，20只是最大值，当你存储的字符小于20时，按实际长度存储。

目前varchar是varchar2的同义词，但是oracle不能这样做，尽管它保留以后这样做的权利。

工业标准的varchar类型可以存储空字符串
Oracle自己开发了一个数据类型varchar2，他将在数据库中varchar列可以存储空字符串的特性改为存储null值。

数据库连接池的作用

维护一定数量的连接，减少创建连接的时间
更快的响应时间
统一的管理

数据模型

数据模型是一种标识实体类型及其实体间联系的模型。典型的数据模型有网状模型、层次模型和关系模型。

关系型数据库：
优点
1、容易理解：二维表结构是非常贴近逻辑世界一个概念，关系模型相对网状、层次等其他模型来说更容易理解；
2、使用方便：通用的SQL语言使得操作关系型数据库非常方便；
3、易于维护：丰富的完整性(实体完整性、参照完整性和用户定义的完整性)大大减低了数据冗余和数据不一致的概率；
4、支持SQL，可用于复杂的查询。
5.支持事务

缺点
1、为了维护一致性所付出的巨大代价就是其读写性能比较差；
2、固定的表结构；
3、不支持高并发读写需求；
4、不支持海量数据的高效率读写

非关系型数据库：
1、使用键值对存储数据；
2、分布式；

优点
无需经过sql层的解析，读写性能很高
基于键值对，数据没有耦合性，容易扩展
存储数据的格式：nosql的存储格式是key,value形式

缺点
不提供sql支持

从关系数据库的表中，除去冗余数据的过程称为规范化。包括：精简数据库的结构，从表中删除冗余的列，标识所有依赖于其它数据的数据

存储过程

SQL语句执行的时候要先编译，然后执行。存储过程就是编译好了的一些SQL语句，应用程序用的时候直接调就行，效率高。

存储过程的优点：
1、能够将代码封装起来，保存在数据库之中，让编程语言进行调用，
2、存储过程是一个预编译的代码块，执行效率比较高
3、保证数据安全性、完整性。
4、一个存储过程替代大量T_SQL语句 ，可以降低网络通信量，提高通信速率

存储过程的缺点：
1、每个数据库的存储过程语法几乎都不一样，十分难以维护（不通用）
2、业务逻辑放在数据库上，难以迭代

参考：
https://blog.csdn.net/lyfqyr/article/details/83351594
https://blog.csdn.net/u010960184/article/details/82557978