2020秋招_数据库（mysql）学习记录

MySQL 索引及查询优化总结

索引

索引是对数据库表中一列或者多列的值进行排序的一种结构，使用索引可以快速访问数据库表中的特定信息。

普通索引和唯一索引

唯一索引和普通索引使用的结构都是B-tree，执行时间复杂度都是O(logn)。

普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHEREcolumn=）或排序条件（ORDERBYcolumn）中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列（如一个整数类型的数据列）来创建索引。

普通索引允许被索引的数据列包含重复的值。如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。
这么做的好处：一是简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；二是MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，MySQL将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。

约束

主键约束和唯一约束区别

主键约束和唯一约束都会创建唯一索引。不同之处在于主键约束的索引键(唯一索引)在定义上不允许为NULL，而唯一约束的索引键(唯一索引)在定义上允许为NULL。

主键约束	唯一约束
所在的列不允许空值(NULL)	所在的列允许空值(NULL)
一个表上只能有一个主键约束	一个表上可以有多个唯一约束或一个

主键、外键和索引的区别

SQL的主键和外键的作用

• 主键是能确定一条记录的唯一标识。比如，一条记录包括身份证号码，姓名，年龄。身份证号码是唯一确认你这个人的，其他的都可能有重复，所以身份证号码是主键。
• 外键用于与另一张表相关联。是能确认另一张表记录的字段，用于保持数据的一致性。比如，A表中的一个字段，是B表的主键，那它就可以是A表的外键。

定义： 主键：唯一标识一条记录，不能有重复，不允许为空。
外键：表的外键是另一表的主键，外键是可以有重复的，可以是空值。
索引：该字段没有重复值，但可以有一个空值。

作用：
主键：用来保证数据完整性
外键：用来和其他表建立联系用
索引：用来提高查询排序的速度

个数：
主键：主键只能有一个。
外键：一个表可以有多个外键。
索引：一个表可以有多个唯一索引。

数据库三大设计范式

数据库三大范式（重要）
数据库三大范式最简单的解释

1. 第一范式：字段(列)的原子性，不可分。
   第一范式是所有关系型数据库的最基本要求。第一范式要求数据库的表是二维的，而不是三维的(即每个字段都不能再拆分)。

2. 第二范式：在满足第一范式的前提下。除主键外每一字段都必须完全依赖主键字段。

3. 第三范式：在满足第二范式的前提下。非主键字段不能相互依赖。 每列都与主键有直接关系，不存在传递的依赖。
   第二范式、第三范式主要是为了避免数据冗余、插入异常、删除异常的情况。一般进行拆分表格，使拆分后的多张表格满足第二、三范式。
   第一范式：不符合第一范式的例子：

   表：字段1、 字段2(字段2.1、字段2.2)、字段3 ......
   

第二范式：不符合第二范式的例子:

    表：学号、课程号、姓名、学分;

    这个表明显说明了两个事务:学生信息, 课程信息;由于非主键字段必须依赖主键，这里学分依赖课程号，姓名依赖与学号，所以不符合二范式。

第三范式：不符合第三范式的例子:

     表：学号、姓名、 年龄、 所在学院、学院联系电话、学院联系电话

      存在依赖传递: (学号) → (所在学院) → (学院地点, 学院电话)

查询

b树和b+树的区别

为什么文件系统和数据库用b/b+树做索引？

数据库的数据一般存在磁盘中，磁盘中数据的I/O相比较于内存中数据的I/O更消耗时间，因此应该减少磁盘I/O次数。

I/O次数取决于树的高度。相较于红黑树，b/b+树的节点可以有多个孩子（节点最大孩子的数目称为B树的阶），使树的高度远远小于红黑树，从而减少I/O次数。

B/B+树上操作的时间通常由存取磁盘的时间和CPU计算时间这两部分构成，而CPU的速度非常快，所以B树的操作效率取决于访问磁盘的次数，关键字总数相同的情况下B树的高度越小，磁盘I/O所花的时间越少。

为什么B+树比B树更适合数据库索引？

B+树是应文件系统所需而产生的一种B树的变形树（文件的目录一级一级索引，只有最底层的叶子节点（文件）保存数据）非叶子节点只保存索引，不保存实际的数据，数据都保存在叶子节点中。
所有的非叶子节点都可以看成索引部分！

mysql为什么用B+树

• 为什么不用哈希 -> 无序
  无论读还是写，哈希都比树更快，那为什么索引结构要选用树型结构呢？因为对于分组、排序、比较，哈希型索引的时间复杂度会退化到O(n)，而这类查询实际业务中会经常出现。

• 为什么不用二叉树 -> 节点数多，树的高度高
  二叉树每个节点只分两个叉，每个节点只能存储一个记录，随着数据量的增大，树的高度会显著增高，而的高度越高，查询速度就越慢。而B树，每个节点可分多个叉，且可存储多条记录，因此树的高度降低了，它可充分发挥局部性原理。所谓局部性原理，就是大概率使用查询数据附近的数据，这个原理是基于磁盘预读的，这样可以减少磁盘IO。

• 为什么不用B-树 -> 每个节点都存储数据，查询需要中序遍历
  B-树每层节点数目非常多，层数很少，相比二叉树减少了磁盘IO次数，但每个节点都存储数据，查询需要进行中序遍历，并不是快速定位数据的最佳方式。

• 为什么用B+树，而不用B树
  B+树在B树基础上进行了改进，数据只存储在叶子节点上，且叶子节点之间增加了链表，这样获取节点时，不用中序遍历，这样便于快速定位数据，是减少磁盘IO的最佳方式。
  
  为什么MySQL数据库索引选择使用B+树？
  PS：我在知乎上看到有人是这样说的,我感觉说的也挺有道理的：
  他们认为数据库索引采用B+树的主要原因是：B树在提高了IO性能的同时并没有解决元素遍历的效率低下的问题，正是为了解决这个问题，B+树应用而生。B+树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作或者说效率太低。

事务

所谓事务就是针对数据库的一组操作(由一条或多条SQL语句组成)进行管控。如果其中任一条语句无法执行，那么所有的语句都不会执行。也就是说，事务中的语句要么都执行，要么都不执行（原子性）。

mysql数据库中使用事务的过程

• 开启事务，strart transaction;， begin;，set autocommit=1;(默认)。
• 控制事务，默认自动提交，即autocommit=1；关闭自动提交select @@autocommit; 或者 set autocommit=0;，关闭了自动提交后可以进行回滚操作，即rollback。
• 手动提交事务，commit;，事务一旦提交便无法回滚（持久性）。
• 手动回滚事务，rollback;。

事务的特性ACID

事务有着非常严格的定义，它必须同时满足4个特性，即：原子性（Atomicity)、一致性 (Consistency)、隔离性（Isolation)、持久性（Durability)，简称事务ACID标准。

• 原子性A：事务必须被视为一个不可分割的最小工作单元，只有事务中所有的数据库操作都执行成功才算整个事务执行成功。如果事务中有任何一个SQL语句执行失败，则已经执行成功的SQL语句也必须撤销，数据库的状态退回到执行事务前的状态。
  例如：假设用户A给用户B转账100元，该事务需要执行两条sql语句，即用户A扣除100元，用户B增加100元，这两条语句必须同时成功或者失败，不然会导致数据前后不一致。

• 一致性C：一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。
  一致性更偏向于事务执行前后数据库状态一致，原子性偏向于事务的不可分割，可以认为事务的一致性由原子性保证。
  例如：假设用户A和用户B两者的钱加起来一共为3000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还是3000。

• 隔离性I：数据库为每一个用户开启的事务不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
  例如：多个用户操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。
  例如：对于任意两个并发的事务T1和T2。从事务T1的角度看来：T2要么在T1开始之前就已经结束，要么T2在T1结束之后才开始。也就是说：每个事务都感觉不到有其它事务在并发地执行。

• 持久性：事务一旦提交，其所做的修改就会永久保存到数据库中，即使数据库发生故障也不应该对其有任何影响。需要注意的是，事务的持久性不能做到100%的持久，只能从事务本身的角度来保证永久性，而一些外部原因导致数据库发生故障，如硬盘损坏，那么所提交的数据可能都会丢失。

mysql事务的隔离级别

MySQL数据库实操教程(18)——数据库事务及其隔离级别 -> 举了很多不错的例子！

事务为什么需要设置隔离级别

通常情况下数据库是多线程并发访问的，所以很容易出现多个线程同时开启事务的情况。在该情况下和可能出现脏读、重复读以及幻读的情况，为了避免这种情况的发生，就需要为事务设置隔离级别。

事务的四种隔离级别

• READ UNCOMMITTED，读未提交（存在脏读），实际开发中应该避免使用
• READ COMMITTED，读已提交（可以避免脏读，但不可重复读）
• REPEATABLE READ，可重复读（解决了不可重复读），mysql默认隔离级别
  PS：但理论上，该级别会出现幻读的情况。不过，MySQL的存储引擎通过多版本并发控制(MVCC)机制解决了该问题，因此该级别是可以避免幻读的。 mysql在RR级别下，Innodb使用MVCC和next-key locks解决幻读。
• SERIALIZABLE，可串行化
  PS：事务的最高隔离级别，它会强制对事务进行排序，使之不会发生冲突，从而解决脏读、幻读、重复读的问题。但是，该级别可能导致大量的超时现象和锁竞争，实际应用中很少使用。

查看和修改操作

查看数据库隔离级别（mysql 8.0版本）:
系统级别 select @@global.transaction_isolation;
会话级别 select @@transaction_isolation;或者select @@session.transaction_isolation;
修改隔离级别:
set <global/session> transaction isolation level <READ UNCOMMITTED/READ COMMITTED/REPEATABLE READ/SERIALIZABLE>

脏读

一个事务读到了另外一个事务没有提交的数据。

在线程乙中读到了线程甲在事务操作中未提交的数据！！此时，线程甲的事务可以使用回滚ROLLBACK撤销之前的操作！这是相当危险的！这就好比：线程甲是客户，线程乙是卖家；因为脏读卖家误以为收到了客户的钱款于是立马发货，但是客户却通过回滚撤销了之前的转账。

不可重复读

不可重复读就是在事务内重复读取别的线程已经提交的数据时读取的结果不一致。导致该问题的原因是查询的过程中其它事务做了更新操作。例如，银行在做统计报表时，第一次查询编号为9527的账户中有1000元钱，第二次查询9527账户时有900元钱；原因是银行做报表的统计期间9527账户的主人取出了100元，这样就会导致多次统计报表的结果不一致。
不可重复读和脏读有点类似，但是脏读是读取其它线程的事务未提交的脏数据，不可重复读是在该事务内重复读取其他线程已提交的数据但数据并不一致的情况。

幻读

幻读是指在一个事务内两次查询中数据行数不一致。导致该问题的原因是查询的过程中其它的事务做了添加操作。例如，银行在做统计报表时统计account表中所有用户的总额时，总共有三个账户总共金额有3000。此时，新增了一个账户并且存人了 1000元；在该情况下，银行再统计时发现账户的总金额变为4000造成了幻读。

mysql的MVCC机制

介绍

MySQL InnoDB MVCC 机制的原理及实现
MVCC (Multiversion Concurrency Control) ，多版本并发控制，是现代数据库（包括 MySQL、Oracle、PostgreSQL 等）引擎实现中常用的处理读写冲突的手段，目的在于提高数据库高并发场景下的吞吐性能。

与MVCC相对的，是基于锁的并发控制（Lock-Based Concurrency Control)。MVCC最大的优势：读不加锁，读写不冲突。在读多写少的OLTP（在线事务处理）应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能。

• MySQL 中 InnoDB 引擎支持 MVCC；
• 应对高并发事务，MVCC 比单纯的加行锁更有效，开销更小；
• MVCC 在读已提交（Read Committed）和可重复读（Repeatable Read）隔离级别下起作用；
• MVCC 既可以基于乐观锁又可以基于悲观锁来实现。

PS：悲观锁的实现方式是加锁；乐观锁的实现方式主要有两种：CAS机制（原子操作）和版本号机制。

实现原理

MYSQL MVCC实现原理
MVCC是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。当然存储的并不是实际的时间值，而是系统版本号（system version number)。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

总结：MVCC(Multiversion concurrency control) 就是同一份数据临时保留多版本的一种方式，进而实现并发控制。

RR隔离级别解决当前读和快照读情况下的幻读

当前读与快照读 -> 快照度和当前读区别解释比较形象
在一个支持MVCC的并发系统中， 我们需要支持两种读， 一个是快照读， 一个是当前读。

• 快照读：简单的select操作。读取的是记录数据的可见版本（可能是过期的数据），不用加锁。
• 当前读：特殊的读操作，插入/更新/删除操作。读取的是记录数据的最新版本，并且当前读返回的记录都会加上锁，保证其他事务不会再并发的修改这条记录。

在RR（读可重复）级别下，快照读是通过MVVC(多版本控制)和undo log来实现的，当前读是通过加record lock(记录锁)和gap lock(间隙锁)来实现的。

InnoDB怎么解决幻读的？
结论：在RR的隔离级别下，Innodb使用MVCC和next-key locks解决幻读，MVCC解决的是普通读（快照读）的幻读，next-key locks解决的是当前读情况下的幻读。

PS：在RC的模式下，MVCC解决不了幻读和不可重复读，因为每次读都会读它自己刷新的快照版本，简单来说就是另一个事务提交，他就刷新一次，去读最新的。

mysql引擎和区别

mysql引擎

数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并快速处理事务，从而满足企业内大多数需要处理大量数据的应用程序的要求。 使用数据库引擎创建用于联机事务处理或联机分析处理数据的关系数据库。这包括创建用于存储数据的表和用于查看、管理和保护数据安全的数据库对象（如索引、视图和存储过程）。

最常用的mysql引擎InnoDB和MyISAM。

InnoDB和MyISAM区别

mysql数据库引擎切换（InnoDB,MyISAM）
系统安装的mysql 5.7版本，show engines;用于查看mysql的引擎。可以看到默认支持的引擎是InnoDB，它的特点：Supports transactions（事务）, row-level locking（行锁）, and foreign keys（外键）。
update、insert、delete
Mysql分布式事务XA（跨数据库事务）MySQL XA 介绍
savepoint是在数据库事务处理中实现“子事务”（subtransaction），也称为嵌套事务的方法。事务可以回滚到savepoint而不影响savepoint创建前的变化。不需要放弃整个事务。 MySQL 基础 ———— SAVEPOINT 的应用

mysql锁

面试官问我知不知道 MySQL 的锁，接下来的 15 分钟让他刮目相看

• 表锁是mysql锁中粒度最大的一种锁，表示当前的操作对整张表加锁，资源开销比行锁少，不会出现死锁的情况，但是发生锁冲突的概率很大。
• 行锁的是mysql锁中粒度最小的一种锁，因为锁的粒度很小，所以发生资源争抢的概率也最小，并发性能最大，但是也会造成死锁，每次加锁和释放锁的开销也会变大。

行锁按照使用方式也分为共享锁（S锁或者读锁）和排它锁（X锁或者写锁）。

• 共享锁

使用说明：若事务A对数据对象1加上S锁，则事务A可以读数据对象1但不能修改，其他事务只能再对数据对象1加S锁，而不能加X锁，直到事务A释放数据对象1上的S锁。这保证了其他事务可以读数据对象1，但在事务A释放数据对象1上的S锁之前不能对数据对象1做任何修改。
用法： select … lock in share mode;
共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

• 排它锁

使用说明：若事务A对数据对象1加上X锁，事务A可以读数据对象1也可以修改数据对象1，其他事务不能再对数据对象1加任何锁，直到事务A释放数据对象1上的锁。这保证了其他事务在事务A释放数据对象1上的锁之前不能再读取和修改数据对象1。
select … for update;
排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁。

redis数据库

redis与mysql的区别