数据库

MySQL逻辑架构

分为连接层、服务层、存储引擎层

连接层

系统（客户端）访问 MySQL 服务器前，需要建立 TCP 连接。经过三次握手建立连接成功后， MySQL 服务器对 TCP 传输过来的账号密码做身份认证、权限获取。

服务层

SQL Interface: SQL接口

接收用户的SQL命令，并且返回用户需要查询的结果。

Caches & Buffers： 查询缓存组件

因为查询缓存命中率不高，MySQL 8.0中删除。（相同的查询操作才会命中查询缓存）

Parser: 解析器

对 SQL 语句进行语法分析、语义分析。将SQL语句分解成数据结构。

Optimizer: 查询优化器

SQL语句在语法解析之后、查询之前会使用查询优化器确定 SQL 语句的执行路径，生成一个执行计划

存储引擎层

负责数据的存储和提取。MySQL>=5.5之后，默认采用InnoDB引擎（优先考虑InnoDB引擎）。

InnoDB 和 MyISAM的区别

一条SQL语句在数据库框架中的执行流程

1. 应用程序把查询SQL语句发送给服务器执行；
2. 查询缓存，如果缓存是打开的，服务器在收到查询请求后，并不会直接去数据库查询，而是在数据库的查询缓存中找是否有相同的查询数据，如果存在，直接返回给客户端。只有缓存不存在时，才会进行下面的操作
3. 查询优化处理，生成执行计划。包括解析SQL、预处理、优化SQL、执行计划
4. MySQL根据相应的执行计划完成整个查询
5. 将查询结果返回给客户端

数据库三大范式是什么？

第一范式

数据表的每一列都是不可分割的原子数据项（原子性）

第二范式

要求实体的属性完全依赖于主关键字（唯一性），所谓完全依赖是指不能存在依赖主关键字一部分的属性。（员工id号）

满足1NF，非主属性完全函数依赖于候选码

第三范式

任何主属性不依赖于其他非主属性（每列都与主键有直接关系，不存在传递依赖）。

符合2NF，并且消除了非主属性对于候选码的传递函数依赖。

总结：

第一范式：字段不可分。第二范式：有主键，非主键字段依赖主键。第三范式：非主键字段不能相互依赖。

查询性能的优化方法？

• 减少请求的数据量
  • 只返回必要的列：最好不要使用 SELECT * 语句
  • 只返回必要的行：使用 LIMIT 语句来限制返回的数据
  • 缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别在要查询的数据经常被重复查询时，缓存带来的查询性能是非常明显的
• 减少服务端扫描的行数：
  • 使用索引来覆盖查询

索引

索引（Index）是帮助MySQL高效获取数据的数据结构。

索引的优缺点

优点：降低数据库的IO成本、加速表和表之间的连接、减少查询中分组和排序的时间 ，降低了CPU的消耗、通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。

缺点：创建索引和维护索引要耗费时间、索引需要占磁盘空间、降低更新表的速度

索引的分类

从功能逻辑上说，索引主要有 4 种，分别是普通索引（允许在定义索引的列中插入重复值和空值）、唯一索引（唯一，允许有空值）、主键索引（唯一，不允许有空值）、全文索引（支持值的全文查找）。按照物理实现方式 ，索引可以分为 2 种：聚簇索引和非聚簇索引。 按照作用字段个数进行划分，分成单列索引和联合索引。

聚簇索引

• 聚簇索引是对磁盘上实际数据重新组织，然后按指定的一个或多个列的值排序的算法。

• 聚簇索引的特点是存储数据的顺序和索引顺序一致。

• 一般情况下主键会默认创建聚簇索引，而且一张表只允许存在一个聚簇索引

辅助索引（非聚簇索引）

辅助索引是根据索引键创建的一棵B+树，与聚簇索引不同的是，其叶子节点仅存放索引键值，以及该索引键值指向的主键。也就是说，如果通过辅助索引来查找数据，那么当找到辅助索引的叶子节点后，还需要根据主键值查找聚簇索引来得到数据，这个过程称为回表。

联合索引

同时为多个列建立索引，比方说我们想让B+树按照 c2和c3列 的大小进行排序，这个包含两层含义：先把各个记录和页按照c2列进行排序，在记录的c2列相同的情况下，采用c3列进行排序。

适合建立索引的情况：

• 在频繁使用的、需要排序的字段上建立索引
• 字段的数值有唯一性的限制
• 频繁作为 WHERE 查询条件的字段
• 经常 GROUP BY 和 ORDER BY 的列
• DISTINCT 字段需要创建索引

不适合建索引的情况：

• 对于查询中很少涉及的列或重复值比较多的列，不适合建立索引
• 对于一些特殊的数据类型，不适合建立索引，比如文本字段 (text)等
• 在where中使用不到的字段，不要设置索引
• 数据量小的表最好不要使用索引
• 有大量重复数据的列上不要建立索引
• 避免对经常更新的表创建过多的索引

什么时候索引失效？

1、索引列参与表达式计算

SELECT 'sname' FROM 'stu' WHERE 'age' + 10 = 30;

2、函数运算：

SELECT 'sname' FROM 'stu' WHERE LEFT('date',4) < 1900;

3、%语句% 模糊查询:

SELECT * FROM 'manong' WHERE 'uname' LIKE '%码农%' -- 走索引
SELECT * FROM 'manong' WHERE 'uname' LIKE "%码农%" -- 不走索引

4、字符串与数字比较不走索引：

CREATE TABLE 'a' ('a' char(10));
EXPLAIN SELECT * FROM 'a' WHERE 'a' = "1"; -- 走索引
EXPLAIN SELECT * FROM 'a' WHERE 'a' = 1;   -- 不走索引，同样也是使用了函数运算

5、查询条件中有or，即时其中有条件带索引也不会使用（前后有非索引的列）

SELECT * FROM dept WHERE dname='xxx' or loc='xx' or deptno=45;

6、正则表达式不使用索引

7、MySQL内部优化器会对SQL语句进行优化，如果优化器估算使用全表扫描比使用索引快，则不使用索引。使用不等于（!=或者<>）

如何判断数据库的索引有没有生效？

使用Explain命令查看语句的执行计划，MySQL在执行某个语句之前，会将该语句过一遍查询优化器，之后会拿到对语句的分析，也就是执行计划，其中包含了很多信息。可以通过其中和索引有关的信息来分析是否命中了索引，例如：possible_key、key、key_len等字段，分别说明了此语句可能会使用的索引、实际使用的索引以及索引的长度。

索引的底层数据结构

索引的数据结构和具体存储引擎的实现有关，常用的有Hash索引，B+树索引等。InnoDB存储引擎默认索引实现为B+树索引。

b树（平衡多路查找树）

B树是一种平衡的多分树，通常我们说 m阶(B树中一个节点的子节点数目的最大值) 的B树，它必须满足如下条件：

• 每个节点最多只有m个子节点
• 每个非叶子节点（除了根）具有至少[m/2]子节点
• 如果根不是叶节点，则根至少有两个子节点
• 具有k个子节点的非叶节点包含k -1个键
• 所有叶子都出现在同一水平，没有任何信息（高度一致）

b+树

• 所有的叶子结点中包含了全部关键字的信息，及指向含有这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大的顺序链接。 (而B树的叶子节点并没有包括全部需要查找的信息)；
• 所有的非终端结点可以看成是索引部分，结点中仅含有其子树根结点中最大（或最小）关键字。 (而B树的非终节点也包含需要查找的有效信息)；

B树与B+树的差异

B+树由分块查找进化而来；B树由二叉排序树进来而来

B+树支持顺序查找和多路查找；B树只支持多路查找

在B+树中，仅叶子节点包含信息，非叶子节点仅起索引作用；在B树中，全部节点的关键字都包含信息。

在B+树中，叶结点包含了全部的关键字，非叶节点中出现的关键字一定会出现在叶子结点中；在B树中，任何节点的关键字都不会重复。

B树的每个节点，有m个key，m+1个指针，B+树的每个节点，有m+1个key，m+1个指针。

事务

事务的特性

原子性（atomicity）A

是指事务是一个不可分割的工作单位，要么全部提交，要么全部失败回滚。

一致性（consistency）C

一致性是指事务执行前后，数据从一个合法性状态变换到另外一个合法性状态 。这种状态是语义上的而不是语法上的，跟具体的业务有关。

隔离型（isolation）I

事务的隔离性是指一个事务的执行 不能被其他事务干扰 ，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

持久性（durability）D

持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的 ，接下来的其他操作和数据库故障不应该对其有任何影响。

事务的隔离级别

• 读未提交（READ UNCOMMITTED）最低的隔离级别，允许读取尚未提交的数据变更
• 读提交 （READ COMMITTED）允许读取并发事务已经提交的数据
• 可重复读 （REPEATABLE READ）对同一字段的多次读取结果都是一致的，除非数据是被本事务自己所修改
• 串行化 （SERIALIZABLE）最高隔离级别，完全服从ACID的隔离级别。所有的事务依次执行，这样事务之间就完全不可能产生干扰，严重影响程序的性能，通常情况都不会使用该级别。

默认隔离级别：

MySQL：REPEATABLE_READ

ORACLE：READ_COMMITTED

MySQL 在 REPEATABLE READ 隔离级别下是可以解决幻读问题的，解决方案有两种，可以使用 MVCC 方案解决，也可以采用加锁方案解决。（间隙锁）

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

1. 脏读：当前事务(A)中可以读到其他事务(B)未提交的数据（脏数据），这种现象是脏读。
2. 不可重复读：在事务A中先后两次读取同一个数据，两次读取的结果不一样，这种现象称为不可重复读。脏读与不可重复读的区别在于：前者读到的是其他事务未提交的数据，后者读到的是其他事务已提交的数据。
3. 幻读：在事务A中按照某个条件先后两次查询数据库，两次查询结果的条数不同，这种现象称为幻读。不可重复读与幻读的区别可以通俗的理解为：前者是数据变了，后者是数据的行数变了。

MVCC（Multi-Version Concurrency Control, 多版本并发控制）

通过数据行的多个版本管理来实现数据库的并发控制，简单来说就是保存数据的历史版本。可以通过比较版本号决定数据是否显示出来。读取数据的时候不需要加锁可以保证事务的隔离效果。

• 读——读：不存在任何问题，也不需要并发控制
• 读——写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读，幻读，不可重复读
• 写——写：有线程安全问题，可能会存在更新丢失的问题

MVCC 可以为数据库解决以下问题：

• 在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发的读写性能
• 可以解决脏读、不可重复读、幻读等事务隔离问题，但不能解决更新丢失问题。

锁

操作类型划分

• 共享锁（S Lock），读锁，针对同一份数据，多个事务的读操作可以同时进行而不会互相影响。
• 排他锁（X Lock），写锁，当前写操作没有完成前，它会阻断其他写锁和读锁。

粒度划分

表级锁： 开销⼩，加锁快，不会出现死锁。锁定粒度大，发⽣锁冲突的概率最⾼，并发量最低。

行锁：开销大，加锁慢，会出现死锁。锁力度小，发生锁冲突的概率小，并发度最高。

页级锁

意向锁（表级别）

• 意向共享锁（IS Lock），事务有意向对表中的某些行加共享锁（S锁）
• 意向排他锁（IX Lock），事务有意向对表中的某些行加排他锁（X锁）

InnoDB 存储引擎的锁算法有哪些？

• Record Lock：单个行记录上的锁。
• Gap Lock：间隙锁，锁定一个范围，但不包含记录本身。
• Next-Key Lock∶Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身。

其他

主从复制是什么？主从复制中涉及到哪三个线程？

主从复制是什么？

主从复制是用来建立一个和主数据库完全一样的数据库环境，称为从数据库；主数据库一般是实时的业务数据库，从数据库的作用和使用场合一般有几个：

• 一是作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作；
• 二是可在从数据库作备份、数据统计等工作，这样不影响主数据库的性能；

主从复制中涉及到哪三个线程？

• binlog线程：负责将主服务器上的数据更改写入二进制日志（Binary log）中
• I/O线程：负责从主服务器上读取二进制日志，并写入从服务器的重放日志（Relay log）中
• SQL线程：负责读取重放日志并重放其中的SQL数据

内存、磁盘

索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

内存存取的时间仅与存取次数呈线性关系，因为不存在机械操作，两次存取的数据的“距离”不会对时间有任何影响。

机械硬盘结构：磁盘转动，磁头沿半径方向移动（实际是斜切向运动）。磁盘的每个同心环叫做一个磁道，所有半径相同的磁道组成一个柱面。磁道被沿半径线划分成一个个小的段，每个段叫做一个扇区，每个扇区是磁盘的最小存储单元。

当需要从磁盘读取数据时，系统会将数据逻辑地址传给磁盘，磁盘的控制电路按照寻址逻辑将逻辑地址翻译成物理地址，即确定要读的数据在哪个磁道，哪个扇区。为了读取这个扇区的数据，需要将磁头放到这个扇区上方，为了实现这一点，磁头需要移动对准相应磁道，这个过程叫做寻道，所耗费时间叫做寻道时间，然后磁盘旋转将目标扇区旋转到磁头下，这个过程耗费的时间叫做旋转时间。

局部性原理与磁盘预读

由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。