范式
码：表中可以唯一确定一个元组（数据表中的一行）的某个属性（或者属性组），如果这样的码有不止一个，那么大家都叫候选码，我们从候选码中挑一个出来做老大，它就叫主码。【标识数据表行的列，一般来说就是唯一值】
- 第一范式：原子性，属性不可分。保证数据库表中的所有列都不可再分。
- 第二范式：1NF基础上，非主属性完全依赖于码。所有其他的列必须完全依赖主键，而不能只依赖于主键的一部分。
- 第三范式：2NF基础上，消除依赖传递。所有其他的列必须直接依赖主键，不能存在传递依赖于主键的情况。
- BCNF：3NF的基础上，不存在任何字段对候选关键字段的传递依赖，消除主键的传递依赖。
- 第四范式：BCNF基础上，消除表中独立的多值。
- 第五范式：4NF上，消除依赖的多值。
存储引擎问题
一张表，里面有ID自增主键，当insert了17条记录之后，删除了第15,16,17条记录，再把Mysql重启，再insert一条记录，这条记录的ID是18还是15 ？
- 如果表的类型是MyISAM，那么是18。
- 因为MyISAM表会把自增主键的最大ID记录到数据文件里，重启MySQL自增主键的最大ID也不会丢失。
- 如果表的类型是InnoDB，那么是15。
- InnoDB表只是把自增主键的最大ID记录到内存中，所以重启数据库或者是对表进行OPTIMIZE操作，都
会导致最大ID丢失。
- InnoDB表只是把自增主键的最大ID记录到内存中，所以重启数据库或者是对表进行OPTIMIZE操作，都
InnoDB和MyISAM的区别
-
InnoDB支持外键，MyISAM不支持外键。
-
InnoDB更适合大量的修改和插入，MyISAM更适合大量查询。原因是MyISAM存储了整个表的具体行数，在检索的时候读取变量获取表行数，查询更快。
-
InnoDB支持行级锁，MyISAM支持表锁。也就是说InnoDB支持锁的粒度更小，对于高并发的支持更好，但是在执行范围不确定的情况下，InnoDB也会锁全表。
-
InnoDB在磁盘上存储两个文件，表空间数据文件和日志文件；MyISAM在磁盘上存放三个文件，.frm(存储表定义） .myd（存储表数据） .myi（存储表索引）。
-
InnoDB默认支持事务管理，MyISAM默认不支持。InnoDB会将每条SQL语句封装成事务，自动提交。封装和提交会影响速度，所以一般在begin和commit之间添加多条语句进行批量式提交。
-
这样的操作涉及一个原子性的操作，将会导致出现问题：如果其中某条语句执行失败，事务会不会提交？这个答案涉及到事务的四大特性中的原子性。事务的四大属性：原子性（事务执行要么成功要么失败）、一致性（实际和预想结果一致，事务的目的）、隔离性（事务互不影响）、持久性（事务被提交就是持久性的）
- 多个事务同时操作一条数据，导致的数据问题：脏读（读取临时无效的数据）、不可重复读（读取修改后的字段）、幻读（数据增删操作导致二次读取字段条数不一致）
-
关于原子性，发散思维：
-
i++属于原子操作吗？答案是不属于。第一步，内存到寄存器（高速缓冲区L1，L2，L3）；第二步，寄存器自增；第三步，写回内存。
-
原子性由什么保证？一般实际开发中，解决办法就是使用同步
synchronized()。synchronized(object){ // code here }
-
-
MySQL对事务的支持
在缺省模式下，MySQL是autocommit模式的，所有的数据库更新操作都会即时提交，所以在缺省情况下，mysql是不支持事务的。但是如果数据库存储引擎是InnoDB或者BDB（BerkeleyDB），就可以支持事务管理，使用 SET AUTOCOMMIT=0关闭自动提交。
索引失效的场景
- 没有查询条件，或者在查询条件上没有建立索引或者使用引导列
- 索引字段中使用了运算或者函数，或者使用is null、is not null、!=、<>
- or语句中前后字段没有同时都为索引
- 数据类型出现隐式转化， 例如字符串比较没有使用单引号
- 查询结果大于全表的30%（会走全表扫描）
- 复合索引中没有遵循最左前缀原则
- 使用非后置Like通配符，如**Like ”%02“**不会生效，%只有写在最后面才会索引生效
全值匹配我最爱，最左前缀要遵守
带头大哥不能死，中间兄弟不能断
索引列上少计算，范围之后全失效
LIKE符号写最右，覆盖索引不写星
不等空值还有or，索引失效要少用
var的引号不能丢，SQL高级也不难
分组之前必排序，一定要加上索引
联合索引的最左前缀匹配原则
参考文档：【大话Mysql面试】-如何通俗易懂的了解Mysql的索引最左前缀匹配原则_Mind_programmonkey的博客-CSDN博客_最左前缀匹配原则
MySQL的索引的底层使用的是B+树，那么联合索引使用的也是B+树，只不过联合索引的键值对不是一个，而是多个。例如创建一个（a，b）的索引：
①这个时候可以看到a的序列是1，1，2，2，3，3有序的，b索引1，2，1，4，1，2无序的。②在a相等的时候，b是有序的，所以一旦前面定义的索引是范围查询，那么后面的索引将会失效，这个现象解释了最左前缀匹配。
所以可以得知只使用B索引的情况下是不走索引的，而a = 1 and b = 2这样的查询是可以使用索引的；如果是a > 1 and b = 4这样的条件，因为a是范围，所以b的索引不会生效；如果使用的是a = 1 and b > 1这样的查询条件，a索引和b索引都会生效。
总而言之：①按照定义顺序，前面的索引需要使用到，后面的索引才可能生效；②一旦碰到范围查询，那么生效的索引截至到范围查询的索引字段；③如果建立索引的字段是字符串，那么前缀匹配用的索引（有且仅有一个%放最后面），其余情况不走索引；④
- 联合索引生效的问题，遵循最左前缀匹配原则，与索引定义的顺序有关。
- 对于联合索引，MySQL 会一直向右匹配直到遇到范围查询（> ， < ，between and，like，in）就停止匹配（但是该字段也走索引）。如果创建联合索引（a，b，c，d），那么查询条件为
a = 1 ans b = 2 and c > 3 and d = 4，这样d就不走索引。 - 如果采用的索引顺序为（a，b），查询条件为
b = 1，这样的条件下也不走索引。 - 排序使用
order by语句，要按照索引定义的顺序编写才会索引生效。
索引的原理
- 索引是一种用来对数据表中一个或多个字段进行排序的数据结构，一般底层使用B+树来实现。
- 索引可以类别与书籍目录，要查找哪一章的内容只需要查找目录即可。又因为数据库存储数据顺序一般不存在规律，所以在创建索引的时候要进行一个排序，类似于图书在出版前进行顺序编辑。
索引使用B+树原因
索引之所以使用B+树而不使用B树，主要取决于B+树的数据结构特点。
- B树每个根节点和叶子节点都存储value。
- B+树只有叶子节点存储value，根节点不存储value只有key。
这样的特点之下，同一层的树中，B+树就能存储更多的key值，而B树因为要存储value值所以存储的key更少，因此B树就要增加树的高度。B树层序遍历次数就要更多，增加IO的次数；相比之下B+树的IO次数就要更少。
- B+树的所有value都存储在叶子节点，且叶子节点之间有指针进行相连
这样在遍历value时只需要遍历叶子节点即可，所以更加高效。
索引提高查询效率原理
索引之所以能提高查询效率，主要是因为它减少了数据库的IO次数。
操作系统对于磁盘IO做了一定的优化。根据局部预读性原理，一个地址的数据被访问到，那么相邻的数据也很快会被访问到。所以一般IO操作的时候是一页一页的形式来读取的，32位操作系统一页就是32bit，即4K（64位操作系统就是8K）。
由于使用了B+树来实现索引，所以在查询的时候很轻易得知应该从何处查找，可以减少传统的逐行扫描而频繁IO造成的效率低下。
MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构。
聚簇索引和非聚簇索引
-
聚簇索引，clustered index，也叫聚集索引。这种索引的优点很明显，数据表行的物理顺序和索引顺序一致，与非聚簇索引相比，检索速度更快。
-
因为一个数据表的数据行的物理顺序唯一，所以一个数据表只能有一个聚簇索引。
-
InnoDB中，主键索引默认就是聚簇索引。如果没有主键，那么第一个唯一非空索引就是聚簇索引；如果没有合适的唯一索引，那么InnoDB将会生成一个隐藏的6字节的列作为聚簇索引。
-
对于使用自增主键和UUID作为主键，这两种情况下，UUID的效率相较于主键自增更加低下。自增主键表示了主键的添加顺序，所以在磁盘上通常相邻的行就是物理上相邻的；而UUID因为生成的数据不确定性，访问的时候要频繁移动磁盘块，所以效率相较更低。
-
UUID，Universally Unique Identifier，全局唯一标识符。为了保证这个唯一性，UUID一般交由机器根据特定的算法生成，用来生成的信息一般包括网卡MAC地址、时间戳、名字空间（Namespace）、随机或伪随机数、时序等。
-
-
-
非聚簇索引，non-clustered index / secondary index，也称非聚集索引、辅助索引或普通索引。每个表最多可以建立249个非聚簇索引。
- 在聚簇索引之上创建的索引被称为非聚簇索引，非聚簇索引要经过二次查找。有别于聚簇索引记录的是表行的物理位置，非聚簇索引叶子节点存储的是主键值，只不过有一个指针指向对应的数据块。
- InnoDB非聚簇索引叶子节点存储的不包含行记录的全部数据，仅仅包含索引键值以及聚簇索引键值（主键索引）。所以会产生一个回表查询问题。
- 如果在查询过程中，使用的是非聚簇索引，但是查询内容包含聚簇索引和非聚簇索引之外的值。整个查询过程中，会根据非聚簇索引找到聚簇索引（主键索引），再根据聚簇索引找到目标值。这样的过程就叫做回表查询或二次查询。【根据MySQL优化器，可能在数据量少的情况下直接进行全表扫描，放弃走索引，不会发生回表查询】
- 因为回表查询多了一层检索的过程，降低了查询效率。所以为了解决回表查询的问题，需要使用联合索引。
-
联合索引，Joint index，也称多列索引。联合索引是联合多个字段的值来创建的索引，与单列索引相对。
- 联合索引的数据结构也是使用B+树。在使用B+树的时候，只能以一个值作为构建基准，所以在使用联合索引的时候，要遵循最左前缀匹配原则。
索引的创建
-
create index直接创建：CREATE <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC]); -
create table建表时创建：CREATE TABLE student(id INT NOT NULL,name CHAR(45) DEFAULT NULL,INDEX(name)); -
alter table修改表时创建：ALTER TABLE student ADD INDEX index_name(name); -
注意：①索引能加快查询，但是不能随意加索引，容易造成维护困难；②多列数据经常被查询到，需要使用联合索引，注意最左前缀匹配原则；③不能在重复度很高的字段上加索引；④注意索引失效。
执行计划
当用户发给MySQL服务器一条sql查询指令之后，数据库优化器会对这条指令进行一个优化，产生一个执行计划。
使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道MySQL是如何执行这条查询语句的，进而根据执行方式可以进行sql调优。执行计划主要是针对开发者优化 SQL 语句而产生的，使用执行计划
语法：
Explain + sql查询语句
执行之后，获得的数据有以下：
其中，id表示执行顺序（从大到小，相等则从上至下），select_type表示查询类别，table表示查询的表格，type表示访问类型（一般需要达到ref(非唯一索引扫描)/range(检索给定行)级别），possible_keys表示可能使用到的索引，key表示实际用到的索引，key_len表示索引字节数，ref显示索引哪一列被使用到，rows表示大致所需要读取的行数，extra表示额外信息。
覆盖索引
MySQL可以直接通过索引获取字段的值。如果索引的叶子节点包含要查询的字段值，就可以直接通过索引查询到目标值，而不再需要进行二次查询或回表查询。
这种情况下，索引包含了要查询的目标值，也就是说索引覆盖了目标值，称为覆盖索引。
数据完整性
数据完整性分为四种类型：
- 实体完整性（Entity Integrity）：数据行不能存在重复行，否则为冗余数据
- 域完整性（Domain Integrity）：对输入到特定列的数值的范围限制
- 参照完整性（Referential Integrity）：要求子表中的相关项必须在主表中存在
- 用户定义完整性（User-defined Integrity）：要求某一列中的数据必须满足用户需求中的语义（比如性别取值只能为男或女）
书写顺序和执行顺序
常见的 sql 语句存在一个书写顺序和执行顺序的问题。比如group by、having、order by、where。
书写顺序上：
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY [desc]
执行顺序上，语法顺序就是执行顺序。通常先分组，之后再having过滤条件，最后进行排序。
MVCC多版本并发控制
MVCC，Multi-Version Concurrency Control，多版本并发控制。MVCC是一种并发控制的办法，一般实现在数据库管理系统中，实现数据访问的并发控制。
对于MySQL来说，主要是在InnoDB数据引擎下，在读已提交（READ_COMMITTED）和可重复读（REPEATABLE_READ）的隔离级别之下，执行SELECT操作会访问版本链中数据的过程。
这样的设定之下，其他事务就可以在该事务正在进行读取的时候进行写操作，因为反正数据的修改过程都会记录在版本链中，只需要找到自己想要读取的数据应该处于版本链中的位置即可。
MVCC的实现
MVCC主要是利用了版本链来实现并发控制的功能，对于每一次修改记录应该存放在版本链中，便于其他事务能够在已经修改的数据中还能读到修改之前的版本的值。
这就需要在实现MVCC的时候记录一些必要的数据，该数据是在聚簇索引记录的隐藏列中的数据，分为两个：
- row_trx_id事务ID：主要用来存储每次对于聚簇索引记录修改的事务的ID，表明是哪个事务修改了这个记录。
- roll_pointer回滚指针：每次对哪个聚簇索引记录做出修改之后，前一次修改的老版本记录就会存放到undo日志中，roll_pointer就是存储了一个指针，指向了这个记录上一个版本存储的位置。
ReadView机制
当事务开始执行的时候，会给每一个事务生成一个ReadView，这个ReadView会记录4个非常重要的属性：
- creator_trx_id: 当前事务的 id;
- m_ids: 当前系统中所有的活跃事务的 id，活跃事务指的是当前系统中开启了事务，但是还没有提交的事务;
- min_trx_id: 当前系统中，所有活跃事务中事务 id 最小的那个事务，也就是 m_id 数组中最小的事务 id;
- max_trx_id: 当前系统中事务的 id 值最大的那个事务 id 值再加 1，也就是系统中下一个要生成的事务 id。
左右连接、内连接
Eg.存在两张数据表，A和B数据表，字段分别为a、b、c和a、d、e，匹配条件是字段a相等。
/**
* A表 B表
* a b c a d e
*
* 1 2 3 1 2 3
* 4 5 6 4 7 8
* 7 8 9 5 8 9
*
* 内连接结果
* select * from a INNER JOIN b on a.a = b.b;
* a b c a d e
* 1 2 3 1 2 3
* 4 5 6 4 7 8
* 左连接查询结果
* select * from a LEFT OUTER JOIN b on a.a = b.b;
* a b c a d e
* 1 2 3 1 2 3
* 4 5 6 4 7 8
* 7 8 9
* 右连接查询结果
* select * from a RIGHT OUTER JOIN b on a.a = b.b;
* a d e a b c
* 1 2 3 1 2 3
* 4 7 8 4 5 6
* 5 8 9
* 全连接查询结果
* select * from a FULL OUTER JOIN b on a.a = b.b;
* a b c a d e
* 1 2 3 1 2 3
* 4 5 6 4 7 8
* 7 8 9
* 5 8 9
*/
左连接就是以左边的表的数据列为主，并且左边的表查询的结果是完整的，右边的表查询的结果仅仅是匹配的字段值。左表的所有行匹配到右表的结果。
右连接则是以右边的表的数据列为主，并且右边的表查询的结果是完整的，左边的表查询的结果仅仅是匹配的字段值。右表的所有行匹配到左表的结果。
内连接查询的结果仅仅是两者相匹配的数据列。
全连接查询的结果是两个表全部的数据写到一起，如果有数据就全部写出来，如果没有就返回null值。
数据隔离级别
并发事务中可能产生的问题：脏读、不可重复读、幻读
概念：通过设置隔离级别可以解决并发事务中可能产生的问题。
- 脏读
- 一个事务，读取了另一个事务未提交的数据，会导致读取的数据不一致
@Transactional(Isolation.READ_COMMITTED)- 设置读已提交，规定其他事务只能读取该事务已经提交的数据
- 不可重复读
- 一个事务中，多次读取相同的字段，读取的数据不一致
@Transactional(Isolation.REPEATABLE_READ)- 设置可重复读，即在一个事务进行中，禁止其他事务对该事务中的字段进行修改，可以保证该事务在进行中多次读取同一字段值都是相同的，解决了不可重复读的问题。（行锁）
- 幻读
- 一个事务中，多次读取全表中的数据，但是前后读取结果不一致，数据表的行数有变化（可能多行或者少行的情况）。区别于上面的不可重复读，前者是规定在字段上的级别，后者是在数据表的级别。
@Transactional(Isolation.SERIALIZABLE)- 设置事务串行化，在这个事务执行期间，禁止其他事务对该表格进行更新删除增加操作，这样就可以解决幻读的问题。因为这种方式相互于在全表的级别上进行加锁操作，所以执行的效率比较低下。（表锁）
不可重复读，指的是在事务中多次读取同一字段值，所以只需要锁行
幻读，指的是在事务中多次读取全表中的数据，所以需要对整个表加锁
总结来说，三者的解决方案，由前至后执行效率越来越低；但是并发安全性上却越来越高。
- 当不设置隔离级别的时候，默认的数据隔离级别：
- MySQL：可重复读
- Oracle：读已提交
数据库中常见的锁
- 间隙锁，Gap Lock
- 间隙锁指的是在两个索引键值之间加的锁。如果我们使用的是范围条件而不是相等条件索引数据，并请求排他锁或共享锁时，InnoDB 会给符合条件的已有数据的索引项加锁，对于键值在条件范围内但并不存在的记录，叫做“间隙”（Gap）。InnoDB 在加锁的时候也会对这些间隙进行上锁处理，这种锁机制就是"间隙锁"。
- 间隙锁的主要目的：① 防止在间隙中插入新的数据； ② 防止已经存在的数据，更新成间隙内的数据。
- 由于间隙锁给不存在的范围值也上了锁，那么会造成在上锁的时候可能无法添加范围内的数据。这样的特性会给性能带来一定的损失。
- 死锁，Dead Lock
- 数据库中的死锁产生必须符合四个条件（互斥访问、占有且等待、不可抢占、循环等待）。例如：两个事务互相持有对方需要的锁，并且在等待对方释放，并且双方均不会释放自己的锁，这样的情况就会造成死锁。
- MySQL 中死锁有两种处理方式：① 设置等待锁的超时时间（innodb_lock_wait_timeout），如果超出预设时间将会释放自己持有的锁；② 打开死锁检测（innodb_deadlock_detect=on），主动回滚一条事务，让另一条事务先执行。通常在使用的时候，考虑到处理的效率，直接使用第二种方式会更快。
参考文档：数据库常用的锁有哪些_Michaeles的博客-CSDN博客_数据库锁有哪些
数据库中的乐观锁与悲观锁 - murphy_gb - 博客园 (cnblogs.com)
创建视图 View
创建视图语法格式：
CREATE VIEW <视图名> AS <SELECT语句>
修改视图：
CREATE OR REPLACE VIEW <视图名> AS SELECT [...] FROM [...];
查看视图：
show tables; // 查看所有的表格，也相当于是一种视图
desc <视图名>; // 查看视图
show fields from <视图名> // 查看视图
视图可以理解为给一个复杂的查询语句起的别名，虽然每一次使用视图都需要重新执行 sql 查询语句，但是简化了调用方式，使用更加方便。这个模式就相当于：公司管理层想知道财务的情况，每次只需要找到财务负责人即可，然后负责人每次上报汇总。
**视图是虚拟表，本身不存储数据，而是按照指定的方式进行查询。**视图的作用就是不必每次都重新编写查询的 sql 代码，而是通过视图直接查询即可。
游标 Cursor
游标是系统为用户开设的一个数据缓冲区，存放 SQL 语句的执行结果。如果使用事务对于数据库进行 select / insert 操作，返回一个包含多个结果的结果集，如果想要针对结果集进行逐一读取记录，就需要使用到游标。常见的场景比如 JDBC 中对于结果集的遍历。
为什么针对结果集进行输出需要游标？用户可以用 SQL 语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理。主语言是面向记录的，一组主变量一次只能存放一条记录，仅使用主变量并不能完全满足 sql 语句向应用程序输出数据的要求，所以这个时候就需要游标的参与来完成这个任务：每次更改主变量对于结果集中数据的指向即可。
MS SQL SERVER 支持三种类型的游标： Transact_SQL 游标、 API 服务器游标和客户游标。
参考文档：[数据库中cursor游标的概念_yxwb1253587469的博客-CSDN博客_数据库游标的概念](https://blog.csdn.net/yxwb1253587469/article/details/52249174?ops_request_misc=&request_id=&biz_id=102&utm_term=游标 cursor&utm_medium=distribute.pc_search_result.none-task-blog-2allsobaiduweb~default-1-52249174.nonecase&spm=1018.2226.3001.4187)
游标的使用包括定义游标、打开游标、推进游标指向下一条记录以及关闭游标的操作。
定义游标：
EXEC SQL DECLARE<游标名> CURSOR FOR <SELECT 语句>;
打开游标：
EXEC SQL OPEN <游标名>;
推进游标指向下一条记录：
EXEC SQL FETCH <游标名>
INTO <主变量>[<指示变量>][,<主变量>[<指示变量>]]...;
关闭游标：
EXEC SQL CLOSE <游标名>;
2055
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
