SQL知识图谱

1 SQL基础知识

SQL基础概念

1 触发器和事件

1.1 触发器（Trigger）

  是用户定义在基本表上的一类由事件驱动的特殊过程。由服务器自动激活， 能执行更为复杂的检查和操作，具有更精细和更强大的数据控制能力。使用 CREATE TRIGGER 命令建立触发器。

1.2 事件（event）

  是MySQL在相应的时刻调用的过程式数据库对象。一个事件可调用一次，也可周期性的启动，它由一个特定的线程来管理的，也就是所谓的“事件调度器”。

1.3 两者区别和联系

  事件和触发器类似，都是在某些事情发生的时候启动。当数据库上启动一条语句的时候，触发器就启动了，而事件是根据调度事件来启动的。由于他们彼此相似，所以事件也称为临时性触发器

2 什么是存储过程(Procedure)？

模块化，可以多次调用，速度更快。

3 索引

3.1 什么是索引

  索引（Index）是帮助MySQL高效获取数据的数据结构，通俗来讲索引就好比书本的目录，加快数据库的查询速度。

3.2 索引的作用

• 提高查询效率

• 消除数据分组、排序

• 避免“回表”查询（索引覆盖）

• 优化聚合查询

• 用于多表JOIN关联查询

• 利用唯一性约束，保证数据唯一性

• InnDB行锁实现

3.3 索引的缺点

• 增加I/O成本

• 增加磁盘空间

• 不合适的索引或索引过多，会降低增删改的效率

3.4 索引的分类

1. 存储结构

   • BTREE：InnoDB & MyISAM

   • HASH：HEAP，NDB，InnoDB AHI

   • Fractal Tree：TokuDB

   • RTREE

   • FULLTEXT

2. 数据的存储方式

   • 聚集索引：聚簇索引的顺序就是数据的物理存储顺序，索引与数据存放在同一个文件中。

   • 非聚集索引：非聚簇索引的顺序与数据的物理存储顺序不同，索引与数据存放在不同的文件。

3. 应用层次

   • 单列：主键索引、唯一索引、普通索引

   • 多列：复合索引

4 什么内存泄露？

5 什么是事务(transaction)？

5.1 定义
  一个事务由多个sql语句组成，必须作为一个整体执行，这些sql语句作为一个整体一起向系统提交，要么都执行、要么都不执行 。

5.2 事务的ACID特性：

  事务是作为单个逻辑工作单元执行的一系列操作。一个逻辑工作单元必须有四个属性，称为原子性、一致性、隔离性和持久性 (ACID) 属性，只有这样才能成为一个事务。

1. 原子性

   事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。

2. 一致性

   事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构（如 B 树索引或双向链表）都必须是正确的。

3. 隔离性

   由并发事务所作的修改必须与任何其他并发事务所作的修改隔离。事务识别数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是第二个事务修改它之后的状态，事务不会识别中间状态的数据。这称为可串行性，因为它能够重新装载起始数据，并且重播一系列事务，以使数据结束时的状态与原始事务执行的状态相同。

4. 持久性

   事务完成之后，它对于系统的影响是永久性的。该修改即使出现系统故障也将一直保持。

5.3 什么是锁？

  数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。

常见的SQL并发问题：

1. 脏读::一个事务读取另一个事务未提交的问题
2. 不可重复读:: 在同一事务中，两次读取同一数据，得到内容不同
3. 幻读::同一事务中，用同样的操作读取两次，得到的记录数不相同

  加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁。加锁后事务就对该数据对象有了一定的控制，在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作。

6 什么是视图？

7 什么是游标（cursor）？

可以理解为就是一个标识，用来标识数据取到什么地方了。你也可以把它理解成数组中的下标

8 什么是主键？什么是外键？

9 数据库三范式

  范式指符合某一种级别的关系模式的集合。在设计关系数据库时，根据满足依赖关系要求的不同定义为不同的范式。

9.1第一范式（1NF）无重复的列

  第一范式（1NF）中数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值。即实体中的某个属性不能有多个值或者不能有重复的属性。简而言之，第一范式就是无重复的列。

  在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。

9.2 第二范式（2NF）属性完全依赖于主键[消除部分子函数依赖]

实体和属性：客观存在并可相互区别的事物称为实体。实体所具有的某一特性称为属性。

满足第二范式（2NF）必须先满足第一范式（1NF）。

  第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。

  为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。 第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。简而言之，第二范式就是属性完全依赖于主键。

9.3 第三范式（3NF）属性不依赖于其它非主属性[消除传递依赖]

  满足第三范式（3NF）必须先满足第二范式（2NF）。

  简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。消除数据冗余。

  例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在的员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。简而言之，第三范式就是属性不依赖于其它非主属性。

2 SQL优化

  开发项目上线初期，由于业务数据量相对较少，一些SQL的执行效率对程序运行效率的影响不太明显，而随着时间的积累，业务数据量的增多，SQL的执行效率对程序的运行效率的影响逐渐增大，此时对SQL的优化就很有必要。

2.1 导致查询变慢的原因

1、数据量过大
2、表设计不合理
3、sql语句写得不好
4、没有合理使用索引

2.2 针对SQL语句的优化

1. 查询语句少使用*
2. 尽量减少子查询，使用连接代替（left\right join， inner join）
3. 减少使用in，使用exists代替。因为in会导致全表扫描。如：

select id from t where num in(1,2,3)    
对于连续的数值，能用 between 就不要用 in 了：    
select id from t where num between 1 and 3  

使用exists代替
select num from a where num in(select num from b)    
用下面的语句替换：    
select num from a where exists(select 1 from b where num=a.num)    

4. 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写

5. or尽量替换成Union或者union all，则将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num=10 or num=20    
可以这样查询：    
select id from t where num=10    
union all    
select id from t where num=20    . 

6. 尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节省存储空间，
   其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。