经过这一篇解决Mysql的大多数基础问题

?abc!

已于 2022-02-18 09:30:57 修改

阅读量1.8k

点赞数 2

分类专栏： Mysql # Interview 文章标签： mysql 数据库 sql

于 2022-02-07 09:28:53 首次发布

本文链接：https://blog.csdn.net/yyuggjggg/article/details/122791749

版权

Interview 同时被 2 个专栏收录

52 篇文章 0 订阅

订阅专栏

Mysql

19 篇文章 0 订阅

订阅专栏

一:为什么要使用数据库

在这里将保存的位置进行对比
数据保存在内存

优点：存取速度快
缺点：数据不能永久保存

数据保存在文件

优点：数据永久保存
缺点：1）速度比内存操作慢，频繁的IO操作。2)查询数据不方便

数据保存在数据库

数据永久保存
使用SQL语句,查询方便效率高。
管理数据方便

二:什么是SQL？

结构化查询语言,是一种数据库查询语言

作用：用于存取数据、查询、更新和管理关系数据库系统。

三:什么是MySQL?

MySQL是一个关系型数据库管理系统

MySQL是最流行的关系型数据库管理系统之一，在WEB应用方面，MySQL是最好的关系数据库管理系统应用软件之一

MySQL是开源免费的，并且方便扩展。

四:数据库三大范式

第一范式：每个列都不可以再拆分。

第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。

第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。

五:mysql有关权限的表都有哪几个

这些权限表分别user，db，table_priv，columns_priv和host。

user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。

db权限表：记录各个帐号在各个数据库上的操作权限。

table_priv权限表：记录数据表级的操作权限。

columns_priv权限表：记录数据列级的操作权限。

host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

六:数据类型

在这里插入图片描述
图例说明:

整数类型:
包括TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别表示1字节、2字节、3字节、4字节、8字节整数。任何整数类型都可以加上UNSIGNED属性，表示数据是无符号的，即非负整数。
长度：整数类型可以被指定长度。长度在大多数场景是没有意义的，它不会限制值的合法范围，只会影响显示字符的个数，而且需要和UNSIGNEDZEROFILL属性配合使用才有意义。
1. 例如：INT(11)表示长度为11的INT类型。
2. 假定类型设定为INT(5)，属性为UNSIGNEDZEROFILL，如果用户插入的数据为12的话，那么数据库实际存储数据为00012。
实数类型，
包括FLOAT、DOUBLE、DECIMAL。DECIMAL可以用于存储比BIGINT还大的整型，能存储精确的小数。而FLOAT和DOUBLE是有取值范围的，并支持使用标准的浮点进行近似计算。

计算时FLOAT和DOUBLE相比DECIMAL效率更高一些，DECIMAL你可以理解成是用字符串进行处理。
字符串类型:
包括VARCHAR、CHAR、TEXT、BLOBVARCHAR用于存储可变长字符串，它比定长类型更节省空间
VARCHAR
1. 使用额外1或2个字节存储字符串长度。列长度小于255字节时，使用1字节表示，否则使用2字节表示。
2. 存储的内容超出设置的长度时，内容会被截断。
CHAR
1. 是定长的，根据定义的字符串长度分配足够的空间。
2. 会根据需要使用空格进行填充方便比较。
3. 适合存储很短的字符串，或者所有值都接近同一个长度。
4. 存储的内容超出设置的长度时，内容同样会被截断。
使用策略:
1. 对于经常变更的数据来说，CHAR比VARCHAR更好，因为CHAR不容易产生碎片。
2. 对于非常短的列，CHAR比VARCHAR在存储空间上更有效率。
日期和时间类型，
尽量使用timestamp，空间效率高于datetime，用整数保存时间戳通常不方便处理。
如果需要存储微妙，可以使用bigint存储。
枚举类型（ENUM），把不重复的数据存储为一个预定义的集合。

七:存储引擎

存储引擎 Storageengine：MySQL中的数据、索引以及其他对象是如何存储的，是一套文件系统的实现。

7.1 常用的存储引擎

Innodb引擎(Mysql的默认引擎)：Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。

MyIASM引擎：不提供事务的支持，也不支持行级锁和外键。

MEMORY引擎：所有的数据都在内存中，数据的处理速度快，但是安全性不高。

7.2 MyISAM与InnoDB区别

在这里插入图片描述
图例说明:

InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
InnoDB 非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效

7.3 InnoDB引擎的4大特性

插入缓冲（insertbuffer)

二次写(doublewrite)

自适应哈希索引(ahi)

预读(readahead)

7.4 存储引擎选择

如果没有特别的需求，使用默认的Innodb即可。

MyISAM：以读写插入为主的应用程序，比如博客系统、新闻门户网站。

Innodb：更新（删除）操作频率也高，或者要保证数据的完整性；并发量高，支持事务和外键。比如OA自动化办公系统。

八:索引

8.1 什么是索引

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

索引是一种数据结构,索引的实现通常使用B树及其变种B+树

数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。

索引就相当于目录。通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的

通过给字段添加索引可以提高数据的读取速度，提高项目的并发能力和抗压能力。

索引优化时mysql中的一种优化方式。

索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

8.2 索引的类型

MySQL目前主要有的索引类型为:普通索引、唯一索引、主键索引、组合索引、全文索引。
普通索引:基本的索引类型，没有唯一性的限制，允许为NULL值。仅加速查询

直接创建索引

CREATE INDEX index_name ON table(column(length))

修改表结构的方式添加索引

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

删除索引
```
DROP INDEX index_name ON table
```

唯一索引：唯一索引与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。简单来说：唯一索引是加速查询 + 列值唯一（可以有null）。

创建唯一索引

CREATE UNIQUE INDEX indexName ON table(column(length))

修改表结构的方式添加索引

ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

主键索引：数据列不允许重复，不允许为NULL，一个表只能有一个主键。简单来说：主键索引是：加速查询 + 列值唯一（不可以有null）+ 表中只有一个。

在建表的时候同时创建主键索引

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) );

用 ALTER 命令

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

组合索引是多列值组成的一个索引，专门用于组合搜索，其效率大于索引合并。
```
ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);
```

全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。全文索引是对文本的内容进行分词，进行搜索

fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。

在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。

创建表的适合添加全文索引

CREATE TABLE `table` (

    `id` int(11) NOT NULL AUTO_INCREMENT ,

    `title` char(255) CHARACTER NOT NULL ,

    `content` text CHARACTER NULL ,

    `time` int(10) NULL DEFAULT NULL ,

    PRIMARY KEY (`id`),

    FULLTEXT (content)

);

修改表结构添加全文索引

ALTER TABLE article ADD FULLTEXT index_content(content)

直接创建索引

CREATE FULLTEXT INDEX index_content ON article(content)

8.3 索引的数据结构

索引的数据结构和具体存储引擎的实现有关，在MySQL中使用较多的索引有Hash索引，B+树索引等，

经常使用的InnoDB存储引擎的默认索引实现为：B+树索引。

对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能快；其余大部分场景，建议选择BTree索引。

按照实现方式分，InnoDB的索引类型目前只有两种：BTREE（B树）索引和HASH索引。

B树索引是Mysql数据库中使用频繁的索引类型，基本所有存储引擎都支持BTree索引。

8.3.1 hash

类似于数据结构中简单实现的HASH表（散列表）一样，当我们在mysql中用哈希索引时，主要就是通过Hash算法（常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法），将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；

如果发生Hash碰撞（两个不同关键字的Hash值相同），则在对应Hash键下以链表形式存储。当然这只是简略模拟图

8.3.2 b树

B树索引（实际是用B+树实现的，因为在查看表索引时，mysql一律打印BTREE，所以简称为B树索引）

查询方式：

主键索引区:PI(关联保存的时数据的地址)按主键查询,
普通索引区:si(关联的id的地址,然后再到达上面的地址)。所以按主键查询,速度快

B+tree性质：

n棵子tree的节点包含n个关键字，不用来保存数据而是保存数据的索引。
所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
所有的非终端结点可以看成是索引部分，结点中仅含其子树中的大（或小）关键字。
B+树中，数据对象的插入和删除仅在叶节点上进行。
B+树有2个头指针，一个是树的根节点，一个是小关键码的叶节点。

8.4 创建索引的原则

索引虽好，但也不是无限制的使用，需要符合以下几个原则:
1）左前缀匹配原则，组合索引非常重要的原则，mysql 会一直向右匹配直到遇到范围查询 (>、<、between、like)就停止匹配，

比如a=1 and b=2 and c>3 and d=4;

如果建立(a,b,c,d)顺序的索引，d是用不到索引的，
如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2）较频繁作为查询条件的字段才去创建索引

3）更新频繁字段不适合创建索引

4）若是不能有效区分数据的列不适合做索引列

(如性别，男女未知，多也就三种，区分度实在太低)

5）尽量的扩展索引，不要新建索引。

比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

6）定义有外键的数据列一定要建立索引。

7）对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

8）对于定义为text、image和bit的数据类型的列不要建立索引。

8.4.1 创建索引的三种方式

第一种方式：在执行CREATETABLE时创建索引

CREATE TABLE user_index2(
	id	INT	auto_increment	PRIMARYKEY,
	first_name	VARCHAR(16),
	last_name	VARCHAR(16),
	id_card		VARCHAR(18),
	information	text,
	KEYname(first_name,last_name),
	FULLTEXTKEY(information),
	UNIQUEKEY(id_card)
);

第二种方式：使用ALTER TABLE命令去增加索引

ALTER TABLE table_name ADD INDEX index_name(column_list);

ALTER TABLE用来创建普通索引(index)、UNIQUE索引或PRIMARYKEY索引。

table_name是要增加索引的表名，

column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。

索引名index_name可自己命名，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTERTABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引

第三种方式：使用CREATE INDEX命令创建

CREATE INDEX index_name ON table_name(column_list)

CREATEINDEX可对表增加普通索引或UNIQUE索引。（但是，不能创建PRIMARYKEY索引）

8.4.2 删除索引

根据索引名删除普通索引、唯一索引、全文索引：
语法:

alter table 表名 drop KEY索引名

alter table user_index drop KEYname;
alter table user_index drop KEYid_card;
alter table user_index drop KEYinformation;

删除主键索引：altertable表名dropprimarykey（因为主键只有一个）。这里值得注意的是，如果主键自增长，那么不能直接执行此操作（自增长依赖于主键索引）
解决: 取消自增长,再行删除

8.5 使用索引查询一定能提高查询的性能吗？为什么

通常，通过索引查询数据比全表扫描要快。

索引需要空间来存储，也需要定期维护，每当有记录在表中增减或索引列被修改时，索引本身也会被修改。
这意味着每条记录的INSERT，DELETE，UPDATE将为此多付出4，5次的磁盘I/O。

因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能，

8.6 百万级别或以上的数据如何删除

由于索引需要额外的维护成本，因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。

所以，在我们删除数据库百万级别数据的时候，查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。

解决方法:

所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）
然后删除其中无用数据（此过程需要不到两分钟）
删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。
这样的话与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。

8.7 B树和B+树的区别

区别一:在B树中，你可以将键和值存放在内部节点和叶子节点；但在B+树中，内部节点都是键，没有值，叶子节点同时存放键和值。

区别二: B+树的叶子节点有一条链相连，而B树的叶子节点各自独立。
在这里插入图片描述

8.8 使用B树的好处

B树可以在内部节点同时存储键和值

把频繁访问的数据放在靠近根节点的地方将会大大提高热点数据的查询效率。

这种特性使得B树在特定数据重复多次查询的场景中更加高效

8.9 使用B+树的好处

B+树的内部节点只存放键，不存放值

一次读取，可以在内存页中获取更多的键，有利于更快地缩小查找范围。

B+树的叶节点由一条链相连

当需要进行一次全数据遍历的时候，

B+树只需要使用O(logN)时间找到最小的一个节点，然后通过链进行O(N)的顺序遍历即可。
而B树则需要对树的每一层进行遍历，这会需要更多的内存置换次数，因此也就需要花费更多的时间

8.10 Hash索引和B+树区别和优劣

原理:
1.hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据。
2.B+树底层实现是多路平衡查找树。对于每一次的查询都是从根节点出发，查找到叶子节点方可以获得所查键值，然后根据查询判断是否需要回表查询数据。

对比:

hash索引进行等值查询更快(一般情况下)，但是却无法进行范围查询。

hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。而B+树的的所有节点皆遵循(左节点小于父节点，右节点大于父节点，多叉树也类似)，天然支持范围。
hash索引不支持使用索引进行排序。原理也是因为hash函数的不可预测。
hash索引不支持模糊查询以及多列索引的最左前缀匹配。原理也是因为hash函数的不可预测。
hash索引任何时候都避免不了回表查询数据，而B+树在符合某些条件(聚簇索引，覆盖索引等)的时候可以只通过索引完成查询。
hash索引虽然在等值查询上较快，但是不稳定。性能不可预测，当某个键值存在大量重复的时候，发生hash碰撞，此时效率可能极差。而B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。
在大多数情况下，直接选择B+树索引可以获得稳定且较好的查询速度。

8.11 数据库为什么使用B+树而不是B树

B树只适合随机检索，而B+树同时支持随机检索和顺序检索；

B+树空间利用率更高，可减少I/O次数，磁盘读写代价更低。

B+树的查询效率更加稳定。

B树搜索有可能会在非叶子结点结束，越靠近根节点的记录查找时间越短，只要找到关键字即可确定记录的存在，其性能等价于在关键字全集内做一次二分查找。
在B+树中，顺序检索比较明显，随机检索时，任何关键字的查找都必须走一条从根节点到叶节点的路，所有关键字的查找路径长度相同，导致每一个关键字的查询效率相当。

B-树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。B+树的叶子节点使用指针顺序连接在一起，只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作。

增删文件（节点）时，效率更高。因为B+树的叶子节点包含所有关键字，并以有序的链表结构存储，这样可很好提高增删效率。

8.12 什么是聚簇索引？非聚簇索引?

聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据

非聚簇索引：将数据存储与索引分开结构，

索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，
当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因

8.13 何时使用聚簇索引与非聚簇索引

在这里插入图片描述

8.14 非聚簇索引一定会回表查询吗

不一定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询

假设我们在员工表的年龄上建立了索引，那么当进行select age from employee where age < 20的查询时，在索引的叶子节点上，已经包含了age信息，不会再次进行回表查询。

8.15 联合索引是什么？

MySQL可以使用多个字段同时建立一个索引，叫做联合索引。

8.16 为什么需要注意联合索引中的顺序？

在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。

原因为:

MySQL使用索引时需要索引有序，

假设现在建立了"name，age，school"的联合索引，那么索引的排序为: 先按照name排序，如果name相同，则按照age排序，如果age的值也相等，则按照school进行排序。
当进行查询时，此时索引仅仅按照name严格有序，因此必须首先使用name字段进行等值查询，之后对于匹配到的列而言，其按照age字段严格有序，此时可以使用age字段用做索引查找，以此类推。
因此在建立联合索引的时候应该注意索引列的顺序，一般情况下，将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整。

九:事务

9.1 什么是数据库事务

事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。
事务就是保证这两个关键操作要么都成功，要么都要失败

9.2 事物的四大特性(ACID)

原子性：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；

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

隔离性：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；

持久性：一个事务被提交之后,它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响

9.3 什么是脏读？幻读？不可重复读？

脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。

不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。

幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

9.4 事务的隔离级别？

为了达到事务的四大特性，数据库定义了4种不同的事务隔离级别，由低到高依次为Read
uncommitted、Read committed、Repeatable read、Serializable，这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。

在这里插入图片描述

四个隔离级别

READ-UNCOMMITTED(读取未提交)：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
REPEATABLE-READ(可重复读)：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
SERIALIZABLE(可串行化)：最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读

事务隔离机制的实现基于锁机制和并发调度。

隔离级别越低，事务请求的锁越少

9.5 MySQL的默认隔离级别

Mysql 默认采用的 REPEATABLE_READ(可重复读)隔离级别

Oracle默认采用的READ_COMMITTED(读取未提交)隔离级别

十:锁

当数据库有并发事务的时候，可能会产生数据的不一致，这时候需要一些机制来保证访问的次序，锁机制就是这样的一个机制。

10.1 隔离级别与锁的关系

在Read Uncommitted级别下，读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突

在Read Committed级别下，读操作需要加共享锁，但是在语句执行完以后释放共享锁；

在Repeatable Read级别下，读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁。

SERIALIZABLE 是限制性最强的隔离级别，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成。

10.2 按照锁的粒度分数据库锁有哪些？

在关系型数据库中，可以按照锁的粒度把数据库锁分为行级锁(INNODB引擎)、表级锁(MYISAM引擎)和页级锁(BDB引擎 )。

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

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

10.3 行级锁(INNODB引擎)、表级锁(MYISAM引擎)和页级锁(BDB引擎 )

行级锁 ::: 行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁和排他锁。

特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

表级锁::: 表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。

特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。

页级锁::: 页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。

特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

10.4 什么是死锁？

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。

10.5 解决死锁的方法

1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。

2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；

3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；如果业务处理不好可以用分布式事务锁或者使用乐观锁

10.6 数据库的乐观锁和悲观锁是什么和实现方式

数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。

乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。
实现方式：使用数据库中的锁机制

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。
实现方式：一般会使用版本号机制或CAS算法实现。

十一:视图

11.1 视图的作用

为了提高复杂SQL语句的复用性和表操作的安全性，MySQL数据库管理系统提供了视图特性

视图使开发者只关心感兴趣的某些特定数据和所负责的特定任务，只能看到视图中所定义的数据，而不是视图所引用表中的数据，从而提高了数据库中数据的安全性

11.2 什么是视图？

视图，本质上是一种虚拟表，在物理上是不存在的，其内容与真实的表相似，包含一系列带有名称的列和行数据。

但是，视图并不在数据库中以储存的数据值形式存在。行和列数据来自定义视图的查询所引用基本表，并且在具体引用视图时动态生成。

11.3 什么是游标？

游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果，每个游标区都有一个名字。

用户可以通过游标逐一获取记录并赋给主变量，交由主语言进一步处理

十二:存储过程与函数

12.1 什么是存储过程

存储过程是一个预编译的SQL语句

12.2 存储过程的优缺点

优点是允许模块化的设计，

就是说只需要创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。

优点

存储过程是预编译过的，执行效率高。
存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。
安全性高，执行存储过程需要有一定权限的用户。
存储过程可以重复使用，减少数据库开发人员的工作量。

缺点

调试麻烦，但是用 PL/SQL Developer 调试很方便！弥补这个缺点。
移植问题，数据库端代码当然是与数据库相关的。但是如果是做工程型项目，基本不存在移植问题。
重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。
如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，然后如果用户想维护该系统可以说是很难很难、而且代价是空前的，维护起来更麻烦

十三:触发器

13.1 什么是触发器

触发器是用户定义在关系表上的一类由事件驱动的特殊的存储过程

触发器是指一段代码，当触发某个事件时，自动执行这些代码。

13.2 触发器的使用场景

可以通过数据库中的相关表实现级联更改。

实时监控某张表中的某个字段的更改而需要做出相应的处理。

例如可以生成某些业务的编号。

注意不要滥用，否则会造成数据库及应用程序的维护困难。

13.3 MySQL中都有哪些触发器？

Before Insert

After Insert

Before Update

After Update

Before Delete

After Delete

十四:常用SQL语句

14.1 SQL语句分类

数据定义语言DDL（Data Ddefinition Language）CREATE，DROP，ALTER 主要为以上操作即对逻辑结构等有操作的，其中包括表结构，视图和索引。

数据查询语言DQL（Data Query Language）SELECT这个较为好理解即查询操作，以select关键字。各种简单查询，连接查询等都属于DQL。

数据操纵语言DML（Data Manipulation Language）INSERT，UPDATE，DELETE;对数据进行操作的，对应上面所说的查询操作 DQL与DML共同构建了多数初级程序员常用的增删改查操作。而查询是较为特殊的一种被划分到DQL中

数据控制功能DCL（Data Control Language）GRANT，REVOKE，
COMMIT，ROLLBACK;;主要为以上操作即对数据库安全性完整性等有操作的，可以简单的理解为权限控制等

14.2 SQL约束有哪几种

NOT NULL: 用于控制字段的内容一定不能为空（NULL）。

UNIQUE: 控件字段内容不能重复，一个表允许有多个 Unique 约束。

PRIMARY KEY: 也是用于控件字段内容不能重复，但它在一个表只允许出现一个。

FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

CHECK: 用于控制字段的值范围。

14.3 六种关联查询

r表
在这里插入图片描述

s表
在这里插入图片描述

内连接（INNER JOIN）

等值连接：ON A.id=B.id
不等值连接：ON A.id > B.id

自连接：

select r,s from r inner join s on r.c=s.c

外连接（LEFT JOIN/RIGHT JOIN）

左外连接：LEFT OUTER JOIN, 以左表为主，先查询出左表，按照ON后的关联条件匹配右表，没有匹配到的用NULL填充，可以简写成LEFT JOIN
```
select r,s from r left join s on r.c=s.c
```
右外连接：RIGHT OUTER JOIN, 以右表为主，先查询出右表，按照ON后的关联条件匹配左表，没有匹配到的用NULL填充，可以简写成RIGHT JOIN
```
select r,s from r right join s on r.c=s.c
```

联合查询（UNION与UNION ALL）

就是把多个结果集集中在一起，UNION前的结果为基准，需要注意的是联合查询的列数要相等，相同的记录行会合并
如果使用UNION ALL，不会合并重复的记录行效率 UNION 高于UNION ALL

SELECT * FROM A UNION SELECT * FROM B UNION ...

全连接（FULL JOIN）

MySQL不支持全连接
可以使用LEFT JOIN 和UNION和RIGHT JOIN联合使用
```
select r,s from r full join s on r.c=s.c
```

交叉连接（CROSS JOIN）

14.4 什么是子查询

条件：一条SQL语句的查询结果做为另一条查询语句的条件或查询结果

嵌套：多条SQL语句嵌套使用，内部的SQL查询语句称为子查询。

14.5 子查询的三种情况

子查询是单行单列的情况：结果集是一个值，父查询使用：=、 <、 > 等运算符
语法:

SELECT 列名 FROM 表名 WHERE 列名=(SELECT 列名 FROM 表名 [WHERE 条件]);

-- 查询工资最高的员工是谁？
select * from employee where salary=(select max(salary) from employee);

子查询是多行单列的情况：结果集类似于一个数组，父查询使用：in 运算符,not in 运算符
语法:

SELECT 列名 FROM 表名 WHERE 列名 [NOT]IN (SELECT 列名 FROM 表名 [WHERE 条件]);

-- 查询张三1和张三2的订单信息
SELECT id FROM USER WHERE NAME IN ('张三1','张三2');

子查询是多行多列的情况：结果集类似于一张虚拟表，不能用于where 条件，用于select子句中做为子表
语法:

SELECT 列名 FROM 表名 [别名] , (SELECT 列名 FROM 表名 [WHERE条件]) 别名] [WHERE 条件]};


SELECT 
	u.name,
	o.number
FROM
	USER u,
	(SELECT * FROM orderlist WHERE id > 4) o
WHERE
	u.id = o.uid;

14.6 mysql中in和exists区别

mysql中的in语句是把外表和内表作hash 连接，

exists语句是对外表作loop循环，每次loop循环再对内表进行查询。

一直大家都认为exists比in语句的效率要高，这种说法其实是不准确的。这个是要区分环境的。

如果查询的两个表大小相当，那么用in和exists差别不大。
如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。
not in 和not exists：如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引；而not extsts的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。