数据库常见题库

1.表设计

1.1存储引擎选择

数据库存储引擎：是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、
查询、更新和删除数据。MySQL的核心就是插件式存储引擎。

• 查看mysql引擎，命令：SHOW ENGINES;
  

• 常用的存储引擎

  1.MyISAM
  ​ MyISAM引擎是MySQL 5.1及之前版本的默认引擎，它的特点是：
  ​ 不支持行锁，读取时对需要读到的所有表加锁，写入时则对表加排它锁
  ​ 不支持事务
  ​ 不支持外键
  ​ 不支持崩溃后的安全恢复
  ​ 在表有读取查询的同时，支持往表中插入新纪录
  ​ 支持BLOB和TEXT的前500个字符索引，支持全文索引
  ​ 支持延迟更新索引，极大提升写入性能
  ​ 对于不会进行修改的表，支持压缩表，极大减少磁盘空间占用
  ​ 2.InnoDB
  ​ InnoDB在MySQL 5.5后成为默认索引，它的特点是：
  ​ 支持行锁，采用MVCC来支持高并发
  ​ 支持事务
  ​ 支持外键
  ​ 支持崩溃后的安全恢复
  不支持全文索引

1.2表结构设计

• 范式设计，消除冗余

 第一范式：属性(字段)的**原子性**约束，要求属性具有原子性，不可再分割；
 第二范式：记录的**惟一性**约束，要求记录有惟一标识，每条记录需要有一个属性来做为实体
 的唯一标识。
 第三范式：属性(字段)**冗余性**的约束，即任何字段不能由其他字段派生出来，在通俗点就是：
 主键没有直接关系的数据列必须消除(消除的办法就是再创建一个表来存放他们，当然外键除外)

• 反范式设计，适当冗余

很多情况下会为了提高数据库的运行效率，常常需要降低范式标  准：适当增加冗余，
达到以空间换时间的目的。比如我们有一个表，产品名称，单价，库存量，总价值。
这个表是不满足第三范式的，因为“总价值”可以由“单价”乘以“数量”得到，说明“金额”
是冗余字段。但是，增加“总价值”这个冗余字段，可以提高查询统计的速度，这就是
以空间换时间的作法。

1.3主键

主键：根据第二范式，需要有一个字段去标识这条记录，主键无疑是最好的标识，
需要满足唯一性、非空性，但是很多表也不一定需要主键，但是对于数据量大，
查询频繁的数据库表，一定要有主键，主键可以增加效率、防止重复等优点。

2.数据库约束

• 约束的作用：对表中的数据进行限定，保证数据的正确性、有效性和完整性。

• 分类：

  非空约束：not null

  唯一约束：unique

  主键约束：primary key

  外键约束：foreign key

  检查：check

  默认：DEFAULT

• 为数据表指定约束有如下两个时机：

​ 建表的同时为相应的数据列指定约束。

​ 建表后创建，以修改表的方式来增加约束。

3.索引

索引的出现就是为了提高数据的查询效率，就像书的目录一样。在书籍中，
用户不必翻阅完整个书就能根据目录迅速地找到所需要的信息。在数据库中，
索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。

3.1索引的分类

单值索引：一个索引只包含单个列，一个表中可以有多个单值索引

唯一索引：索引列的值必须唯一，可为空

复合索引：一个索引包括多个列

3.2索引的优势

1）提高数据检索效率，降低磁盘IO成本

2）通过对数据的排序，降低排序成本

3.3索引的劣势

1）索引虽提高了查询效率，但同时降低了更新、修改、删除的效率，因为MySQL不仅要保存数据，还要维护数据和索引的关系。

2）需要成本去维护索引。一个性能良好的索引需要不断的去尝试，以找到最优解。

3.3 什么情况下适合建立索引

1）主键自动建立唯一索引

2）频繁作为查询条件的字段（where后面的字段）

3）查询中与其他表关联的字段（各种join on后面的字段）

4）单值/复合索引选择？（高并发下倾向选择复合索引）

5）查询中排序的字段

6）查询中统计或分组的字段

3.4什么情况下不适合建立索引(重要)

1）表数据太少

2）频繁更新的字段

3）where后面用不到的字段

3.5 什么时候会出现索引失效（重要）

1）like以通配符开头(‘%abc’)会导致索引失效，违反最左前缀法则

最左前缀法则：

2）在索引列上做任何操作(计算、函数、类型转换)，会导致索引失效而转向全表扫描

3）存储引擎不能使用索引中范围条件右边的列，举例：select id，name from student where id > 50 and name = ‘张三’，会导致name索引失效

4）尽量使用覆盖索引，不要select *

5）MySQL在使用不等于(!=或<>)的时候无法使用索引会导致全表扫描，理由也很简单，B+Tree叶子节点用指针相连且是排好序的，这种数据结构只能解决有序的定值查询，像不等于这种无法利用索引查询。

6）IS NULL、IS NOT NULL无法使用索引，理由同上

7）字符串不加单引号索引失效

​ 隐式转换–>函数操作

8）用or连接时会导致索引失效

3.6索引的数据结构

索引常见的有三种数据结构：哈希表，有序数组和二叉树。

MySQL使用了B+树。
1、哈希表（散列表）
对索引的key进行一次hash计算就可以定位出数据存储的位置
很多时候Hash索引要比B+Tree索引更高效
仅能满足“=”，“in”，不支持范围查询
存在hash冲突
2、有序数组
有序数组在范围查找中可以使用二分法，能大大缩短查询时间，时间复杂度是O(log(N))。如果在中间插入数据时，需要移动后续数组，成本很高，所以有序数组索引只适用于静态存储引擎。
3、二叉树
二叉树：右侧元素大于父元素数据，左侧数据小于父元素数据
红黑树（平衡二叉树）：与二叉树结构一样，但是在生成的过程中红黑树会自动平衡节点

b-Tree:

叶节点具有相同的深度，叶节点的指针为空
所有索引元素不重复
节点中的数据索引从左到右递增排列
节点中存储data数据
非叶子节点不存储data，只存储索引，目的是可以放更多的索引。
叶子节点包含所有索引字段
叶子节点使用指针连接，提高区间访问的性能

4.数据库优化

• 合理使用索引
  索引少了查询慢；索引多了占用空间大，执行增删改语句的时候需要动态维护索引，影响性能 选择率高（重复值少）且被where频繁引用需要建立B树索引；一般join列需要建立索引；复杂文档类型查询采用全文索引效率更好；索引的建立要在查询和DML性能之间取得平衡；复合索引创建时要注意基于非前导列查询的情况

• 使用UNION ALL替代UNION
  UNION ALL的执行效率比UNION高，因为UNION执行时需要排重；

• 避免select * 写法
  执行SQL时优化器需要将 * 转成具体的列；每次查询都要回表，不能走覆盖索引。

• JOIN字段建议建立索引
  一般JOIN字段都提前加上索引

• 不要在列上进行运算
  select * from users where YEAR(adddate)<2007;
  将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成
  select * from users where adddate<‘2007-01-01’;

• 不使用NOT IN和<>操作
  NOT IN和<>操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id<>3则可使用id>3 or id<3来代替。

5.触发器

• 概念：

触发器（trigger）是个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。

• 作用：

触发器经常用于加强数据的完整性约束和业务规则等;

可在写入数据前，强制检验或者转换数据(保证安全性);

触发器发生错误时，前面用户已经执行成功的操作会被撤销，类似事务的回滚。

6.锁

MySQL的InnoDB锁机制分为表级锁和行级锁

① 行级锁
行级锁中有共享锁和排它锁。

共享锁又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

select * from t_user where id =10 lock in share mode;

排他锁又称为写锁(独占锁)，简称X锁，顾名思义，排他锁就是不能与其他锁并存。

如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，

包括共享锁和排他锁。

select * from t_user where id =10 for update;

MySQL InnoDB引擎默认的修改数据语句，update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型。

实例如下：

select * from t_user where id=10 for update; # 错误
select * from t_user where id=10 lock in share mode; # 错误
select * from t_user where id=1 # 正常获取数据

②InnoDB 中的两个表锁：

意向共享锁（IS）：表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁；

意向排他锁（IX）：类似上面，表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。

意向锁是 InnoDB 自动加的，不需要用户干预。

再强调一下，对于INSERT、UPDATE和DELETE，InnoDB 会自动给涉及的数据加排他锁；对于一般的SELECT语句，InnoDB 不会加任何锁，事务可以通过以下语句显式加共享锁或排他锁。

共享锁：SELECT … LOCK IN SHARE MODE;
排他锁：SELECT … FOR UPDATE;

③优劣

表锁的优势：开销小；加锁快；无死锁
表锁的劣势：锁粒度大，并发处理能力低

行锁的劣势：开销大；加锁慢；会出现死锁
行锁的优势：锁的粒度小，处理并发的能力强

7.事务

7.1 什么是事务？

事务由单独单元的一个或多个SQL语句组成，在这个单元中，每个MySQL语句是相互依赖的。
而整个单独单元作为一个不可分割的整体，如果单元中某条SQL语句一旦执行失败或产生错误，
整个单元将会回滚。所有受到影响的数据将返回到事物开始以前的状态。如果单元中的所有SQL
语句均执行成功，则事物被顺利执行。

7.2事务的四个特性

① 原子性(atomicity)
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
② 一致性(consistency)
事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
③ 隔离性(isolation)
事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
④ 持久性(durability)
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

7.3事务的隔离机制

一个事务与其他事务隔离的程度称为隔离级别。

隔离级别	存在问题
READ UNCOMMITTED read uncommitted	脏读、不可重复读、幻读
READ COMMITTED	不可重复读、幻读
REPEATABLE READ repeatable read	幻读
SERIALIZABLE serializable	无

• **产生脏读场景：**A事务读取一个字段，但是这个字段被另外一个事务更新却未提交，再次读取该字段时如果另外一个事务回滚则出现了脏读现象(读到的数据与第一次，数据库中的数据都不同)。

• 产生不可重复读场景：A事务读取一个字段，但是这个字段被另外一个事务更新并提交，再次读取该字段值不一样则出现了不可重复读现象(同一个事务中，不能保证读取的字段值相同)。

• 产生幻读场景：A事务读取一个字段集合，但是这个表被另外一个事务更新并提交(如插入了几行)，再次读取该表可能会多几行则出现了幻读现象。

Oracle数据库支持READ COMMITTED(默认) 和 SERIALIZABLE这两种事务隔离级别。
所以Oracle不会出现脏读。

MySQL 支持 4 种事务隔离级别：READ_UNCOMMITTED（读取未提交），READ_COMMITTED
（读取已提交），REPEATABLE_READ（可重复读-默认）和SERIALIZABLE （可串行化）。

Oracle 默认使用的是READ_COMMITTED。MySQL默认事务隔离级别为 REPEATABLE_READ。

• read uncommitted（读未提交）
  
  不管你事务是否提交，只要数据发生改变我就可以察觉到…
  

• repeatable read（可重复读）
  
  左边提交，右边才能修改