数据库入门：全面复习与知识梳理

1、数据库底层

MySQL数据库的底层是B+树。说到B+树，先说下B树，B树也叫多路平衡查找树，所有的叶子节点位于同一层，具有以下特点：1）一个节点可以容纳多个值；2）除非数据已满，不会增加新的层，B树追求最少的层数；3）子节点中的值与父节点的值有严格的大小对应关系。一般来说，如果父节点有a个值，那么就有a+1个子节点；4）关键字集合分布在整棵树中；5）任何一个关键字出现且只出现在一个节点中；6）搜索可能在叶子结点结束，其搜索性能等价于在关键字全集做一次二分查找。

B+树是基于B树和叶子节点顺序访问指针进行实现，它具有B树的平衡性，并且通过顺序访问指针来提高区间查询的性能，一个叶子节点中的key从左至右非递减排列。特点在于：1）非叶子节点中含有n个关键字，关键字不保存数据，只作为索引，所有数据都保存在叶子结点；2）有的叶子节点中包含了全部关键字的信息及只想这些关键字记录的指针，即叶子节点包含链表结构，能够方便进行区间查询；3）所有的非叶子结点可以看成是索引部分，节点中仅包含其子树中的最大（或最小）关键字；4）同一个数字会在不同节点中重复出现，根节点的最大元素就是B+树的最大元素。

MySQL中的InnoDB引擎是以主键ID为索引的数据存储引擎。InnoDB通过B+树结构对ID建立索引，在叶子节点存储数据。若建索引的字段不是主键ID，则对该字段建索引，然后再叶子节点中存储的是该记录的主键，然后通过主键索引找到对应的记录。因为不再需要全表扫描，只需要对树进行搜索即可，所以查找速度很快，还可以用于排序和分组。

InnoDB和MyISAM引擎都是基于B+树，InnoDB是聚簇索引，数据域存放的是完整的数据记录；MyISAM是非聚簇索引，数据域存放的是数据记录的地址。InnoDB支持表锁、行锁、间隙锁、外键以及事务，MyISAM仅支持表锁，同时不支持外键和事务。InnoDB注重事务，MyISAM注重性能。

2、SQL语言之DQL、DML、DDL和DCL

DQL指的是Data Query Language，数据库查询语言，主要是select命令；

DML指的是Data Manipulation Language，数据库操作语言，主要有insert、delete、update等命令；

DDL指的是Data Defined Language，数据库定义语言，主要是对数据库的某些对象，如database和table进行管理，主要有create、alter、drop等命令，比如创建数据库和表格、更改表结构和设置约束、删除表和数据库；

DCL指的是Data Control Language，数据库控制语言，主要是用于授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，主要有grant、rollback等命令。

DML和DDL的区别：DML操作可以手动控制事务的开启、提交和回滚，而DDL是隐形提交不能回滚。

3、数据库连接协议

JDBC驱动协议：默认TCP协议

客户端和Oracle服务器之间通信协议是TCP的，但是一个数据库连接也会其一个UDP端口。

MySQL的连接方式有两种：Socket和TCP/IP

-- Socket连接方式
mysql -uroot -padmin -S/application/mysql/tmp/mysql.sock
-- TCP/IP连接方式
mysql -uoot -padmin -h192.1.1.20

4、索引和主键的区别

主键用于标识数据库记录的唯一性，不允许记录重复且键值不能为空。主键是特殊索引，但索引不一定是主键。

索引可以提高查询速度，可以不需要进行全表扫描而快速查询到结果。

使用主键，数据库会自动创建主键索引，同时也可以再非主键上创建索引。

数据表中只能由一个主键，但可以有多个索引。

5、数据库四范式

范式：创建数据库的过程中必须遵循的准则。其作用在于减少数据库中的数据冗余，以增加数据的一致性。

候选键：唯一识别该表的属性或属性表。

第一范式（1NF）：属性不可拆分或无重复的列；

第二范式（2NF）：数据库中的每一行必须被唯一地区分，即表中字段必须完全依赖于全部主键而非部分主键；

第三范式（3NF）：消除传递依赖，数据库非主键外的所有字段仅能以来于候选键，不存在与其他非主键关联；

第四范式（4NF）：一个表的主键只对应一个多值，即消除多值依赖。

6、SQL基础

数据库的创建与使用：

create database test;
use test;

创建表：

create table mytable (
id int not null auto_increment,
name varchar(20)
);

修改表：

alter table mytable add sex varchar(20);

增删改：

insert into mytable(id,name,sex) values(1,'zhangsan','male');
delete from mytable where id = 1;
update mytable set name = 'lisi' where id = 1;

清空表：

truncate table mytable;

distinct：用于返回唯一不同的值

select distinct name from mytable;

limit：检索记录行

-- 检索前5个记录行
select * from mytable limit 5;
-- 检索记录行1-5行
select * from mytable limit 0, 5;
-- 检索6-last行
select * from mytable limit 5, -1;

排序：

-- 默认升序
select * from mytable order by col1 DESC， col2 ASC;

like匹配：

-- %匹配大于等于1个任意字符
select * from mytable where name = 'z%';
-- _匹配1个任意字符
select * from mytable where name = 'l_s_';
-- []匹配集合内的字符
select * from mytable where name = '[zl]%';
-- [!]匹配除开集合内的字符
select * from mytable where name = '[!z]%';

concat()：用于将多个字符串连接成一个字符串

select concat(trim(col1), '(', trim(col2), ')') as new from mytable;

函数：

-- 均值
select avg(col) as col_avg from mytable;
-- 计数
select count(col) as col_count from mytable;
-- 类似的sum()、max()、min()等
-- 时间
select now();

分组：

select col, count(*) as num from mytable where col > 2 group by col having by num > 2;

子查询中只能返回一个字段的数据：

select * from mytable1 where col1 in (select col2 from mytable2);

组合查询：

select * from mytable where col = 1 union select col from mytable where col = 2;

视图：

create view as myview as select * from mytable where id > 2;

存储过程：

create procedure myprocedure(out ret int)
begin
...
end

7、 删除操作delete、truncate和drop

delete：直接删除表中的某一行数据，并且同时将该行的删除操作作为事务记录在日志中保存便于进行回滚，因此delete操作更加占用资源，数据空间不释放。delete可以对table和view对象进行操作。

delete from mytable where id = 1;

truncate：一次性从数据表中删除所有数据（释放存储表数据所用的数据页来删除数据），因此不能回滚，占用资源更加少，速度更快。数据空间释放后，表和索引所占用的空间会回复到初始大小。只能对没有关联视图的table进行操作，对于外键约束引用的表，不能使用truncate，需要使用delete。

truncate mytable；

drop：删除整个表，包括表的结构、数据、定义等。属于永久抹去，空间会释放，无法恢复，对table和view都能操作。

drop mytable;

总结：

在速度上，drop > truncate > delete；

在操作对象上，delete和drop可以对table和view操作，truncate只能对table操作；

在表和索引所占空间上，delete操作不会减少表和索引占用的空间，truncate操作后表和索引所占用的空间会恢复至初始大小，drop将表所占用的空间全部释放；

在回滚上方面，delete操作为DML语句可以回滚，truncate和drop为DDL语句，隐式提交无法回滚；

在删除限制上，delete可以操作带有外键约束引用的表，而truncate不可以；

8、char和varchar的区别

char的长度是不可变的，而varchar的长度是可变的。例如，创建表时定义一个char[10]和varchar[10]，当存入一个字符串sql时，char所占的长度依然为10，除了字符sql外后面跟7个空格，而varchar所占的长度变为字符串的实际长度3。在取数据时，char类型的要用trim()函数去掉多余的空格，而varchar类型不需要。

char类型的存取速度比varchar快得多，因为其长度固定方便存储与查找；但char类型会付出空间的代价，是以空间换时间来争取高的时间效率，而varchar是以空间效率为首。

char对英文字符（ASCII）占用1个字节，对汉字占用2个字节；varchar对英文字符和汉字都是占用2个字节。

9、 数据库的冷备份与热备份

冷备份（off，慢，时间点上恢复）：需要数据库正常关闭，会提供一个完整的数据库；将关键性文件拷贝到另外位置；对于备份数据库信息而言，冷备份是最快最安全的方法。

优点：易归档、能够回复到某个时间点；

缺点：数据库必须处于关闭状态；

热备份（on，块）：数据库运行的情况下，备份数据库操作的sql语句，当数据库发生问题时可以重新执行一遍备份的sql语句。

优点：备份时数据库仍可以使用、快速恢复、属于表或数据库级别的备份，并且时间短

缺点：不能出错

10、数据库的事务及ACID属性

事务：逻辑上的一组操作，要么都执行，要么都不执行。

四大特性：

A原子性：事务是最小的执行单位，不可分割，要么全部执行，要么都不执行；

C一致性：事务执行前后，数据库的数据保持一致，多个事务对同一数据读取的结果是相同的；

I隔离性：并发访问数据库时，事务之间互不打扰，各并发事务之间的数据库是独立的；

D持久性：一个事务被提交之后，该事务对数据库所做的改变是持久的，不会被回滚。

11、并发事务的问题

脏读：一个事务读取到了另一个事务还未提交的修改数据，如果另一个事务进行了回滚，这个数据就是脏数据。

修改丢失：一个事务读取数据并进行了修改，另一个事务页读取了该数据进行了修改，这样第一个事务的修改结果就丢失了，也就是修改丢失问题。

不可重复读：一个事务对同一数据进行多次修改，期间另一事务也读取了该数据并进行了修改，这样第一个事务读取到的数据可能不一致，称为不可重复读。

幻读：一个事务在读取多行数据时，另一个并发事务插入了一些新数据，后续查询中第一个事务会查找到一些原本不存在的数据记录，称为幻读。在Mysql中利用MVCC解决了快照读幻读，利用间隙锁解决了当前读幻读。

12、隔离级别

读未提交：最低隔离级别，允许读取尚未提交的数据变更，会导致脏读、不可重复读和幻读；

读已提交：允许读取并发事务已经提交的数据，可以阻止脏读，会导致不可重复读和幻读；

可重复读：对同一字段多次读取的结果都是一样的，可以阻止脏读和不可重复读，会导致幻读；

串行化：最高隔离界别，可以阻止脏读、不可重复读和幻读。

MySQL中InnoDB引擎默认支持的隔离级别是可重复读，使用的是next-key Lock算法，可以避免幻读的产生，可以完全保证事务的隔离性要求

13、多表查询

一对多关系：从表使用主表的主键作为外键；主表中有的数据，从表中可以没有；主表必须有数据，才能向从表中添加数据；要先删除从表的相应数据才能删除主表的数据；

多对多关系：老师与学生，一个老师可以教多个学生，一个学生也可以从多个老师那里学习知识。创建表格时，将多对多的关系拆分为多个一对多关系。

14、数据库表的连接方式

内连接：inner join……on…… ：取交集

外连接：

1）left join……on…… ：以左表为准，查询出左表的所有数据，右表中有对应的则显示出来，没有对应的则显示为null；

2）right join……on…… ：以右表为准，查询出右表的所有数据，左表中有对应的则显示出来，没有对应的则显示为null；

3）full join……on…… ：left和right的集合，某表中某一行在另一表中无匹配行，则相应列的内容为null；

交叉连接：cross join…… ：笛卡尔积，相当于两个表中的所有行进行排列组合。

15、存储过程与存储函数

存储过程：为以后的使用而保存的一条或多条SQL语句的集合，相当于批处理。存储过程被编译后会被直接保存在数据库中，成为数据库的一部分，以后就可以反复调用、运行速度快。

-- in表示输入变量，out表示输出变量，inout输入输出均可
create procedure myprocedure([IN|OUT|INOUT] 参数名 数据类型, [IN|OUT|INOUT] 参数名 数据类型, ...)
begin
...
end

存储函数：

create function myfunction(参数名 数据类型, ...) returns 返回类型
begin
...
end

二者区别：

存储函数限制较多，例如不能使用临时表，只能用表变量，而存储过程限制较少；

存储过程可以实现复杂的功能，存储函数针对性比较强；

返回值不同，存储过程可以没有返回值，也可以返回单个或多个结果集，而存储函数有且仅有一个返回值；

调用不同。存储过程通过call语句调用，存储函数通过select调用；

参数不同。存储过程的参数类型可以是in、out、inout，而存储函数的参数类型只有in类型。

16、触发器

SQL触发器是一种特殊类型的存储过程，不由用户调用。它在指定的表中的数据发生变化时自动生效。唤醒调用触发器以响应Insert、Update和Delete语句。他可以查询其它表，并可以包含复杂的Transact-SQL语句。将触发器和触发它的语句作为可在触发器内回滚的单个事务对待。如果检测到严重错误，整个事务自动回滚。

优点：

触发器可通过数据库中的相关表实现级联更改；

触发器可以强制比用check约束定义的约束更为复杂的约束；

在约束所支持的功能无法满足应用程序的功能要求时，触发器就极为有用。

create trigger trigger_order after insert on orders for each row
begin
update product set pnum = pnum - new.onum where pid = new.pid;
end

17、数据库的锁机制

MyISAM和InnoDB存储引擎使用的锁：

MyISAM采用表级锁（table-level locking），InnoDB支持行级锁（row-level locking）和表级锁，默认为行级锁。

二者对比：

表级锁：MySQL中粒度最大的锁，对当前操作的整张表加锁，实现简单，资源消耗少，加锁快，不会出现死锁。锁冲突的概率高，并发度低。

行级锁：MySQL中粒度最小的锁，只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。加锁慢，开销大，并发度高，会出现死锁。

InnoDB引擎的锁算法：

Record lock：记录锁。条件为精准匹配时，锁住具体的索引项；

Gap lock：间隙锁。锁定一个范围，不包括记录本身。

Next-key lock：临键锁。锁定一个范围，包含记录本身。

按思想分为乐观锁和悲观锁

乐观锁：事务并发操作时认为不会发生冲突，对数据进行更新并提交，如果检测到冲突就返回。

悲观锁：事务并发操作使认为会发生冲突，先进行加锁操作。

18、日志

bin log：二进制日志（服务层）：涉及到主从复制；

redo log：重做日志（引擎层）：数据的灾后重新提交，物理日志。包括两部分，内存中的日志缓存（redo log buffer）（易失性）和磁盘上的重做日志（redo log file）（持久性），需要将redo log buffer通过操作系统内核空间的OS buffer刷到磁盘上的log file中。

undo log：回滚日志（引擎层）：主要用于数据修改的回滚，逻辑日志。会给予与操作相反的语句，当事务回滚时从undo log中反向读取内容。

MVCC：Multiversion concurrency control，多版本并发控制，实现并发和回滚的重要功能。它指的是数据库中的每一条数据，会存在多个版本。对同一条数据而言，MySQL 会通过一定的手段（ReadView 机制）控制每一个事务看到不同版本的数据，这样也就解决了不可重复读的问题。

19、解决幻读

在Innodb引擎中，每条聚集索引都会有两个隐藏字段：trx_id和roll_pointer，每次事务对一条记录进行改动时，就会将事务id赋值给trx_id，并且会将旧数据写入一条undo日志，每条undo日志都有roll_pointer属性，可以将这些undo日志都连起来，串成一个链表，undo日志的写入采用头插法，新数据在前。

快照读： MVCC中有一个ReadView的概念，其中记录了生成ReadView时的活跃事务id列表:m_ids、最小事务id:min_trx_id、将要分配给下一个事务的id:max_trx_id、生成ReadView的事务id:creator_trx_id。如果被访问版本的trx_id与creator_trx_id相同或者小于min_trx_id，则可以访问；如果被访问版本的trx_id大于等于max_trx_id，则不能访问；如果被访问版本的trx_id在min_trx_id和max_trx_id之间，则当trx_id不在m_ids中时才能访问。

当前读： InnoDB存储引擎有三种锁：Record lock：单个行记录上的锁；Gap lock：间隙锁，锁定一个范围，不包括记录本身；Next-key lock：record+gap 锁定一个范围，包含记录本身。innodb对于行的查询使用next-key lock，当查询的索引含有唯一属性时，将next-key lock降级为record key。

20、数据库的索引机制

需要创建索引的情况：

主键自动建立唯一索引；

频繁作为查询条件的字段；

查询与其它表关联的字段，外键关系建立索引；

查询中排序的字段（通过索引访问将大大提高排序速度）；

查询中统计或分组的字段。

不需要创建索引的情况：

表记录太少；

经常增删改的表；

频繁更新的字段；

where条件里用不到的字段；

数据重复且分布平均的字段

21、explain关键字

id：选择标识符；select_type：查询的类型；table：输出结果集的表；type：表的连接类型；possible_keys：可能使用的索引；key：实际使用的索引；key_len：索引字段的长度；ref：列与索引的比较；rows：扫描出的行数；extra：执行情况的描述和说明。

22、索引优化思路

开启慢查询日志设置阈值；explain做慢SQL分析。

查询截取分析：using filesort效率低，using index效率高；最左前缀原则

23、索引创建规则

最左前缀匹配原则：mysql会一直向右匹配指导遇到范围查询（betwee、like）就停止查询；选择区分度高的列作为索引；选择唯一性索引；尽量使用数据量少的索引；尽量使用前缀来索引；索引列不能参与计算；尽量的扩展索引不要新建索引；限制索引的数目。

24、索引过多的问题

一般一个表对应5个索引左右，索引过多会导致：查找数据变慢；对insert语句影响很大，尤其是无序插入；删除数据多的情况下索引也需要更新；索引文件过大，占用存储空间，寻址的查询时间长；mysql优化器需要评估更多的组合。