mySql底层原理

逻辑架构

1、 MySQL逻辑架构整体分为三层，最上层为客户层，并非MySQL所独有，诸如，连接处理、授权认证、安全等功能均在这一层处理。

2、MySQL大多数核心服务均在中间这一层，包括查询解析、分析、优化、缓存、内置函数（时间、数学、加密等），所有的跨存储引擎的功能也在这一层实现：存储过程、触发器、视图等。

3、最下层为存储引擎，其负责MySQL中的数据存储和提取，中间的服务层通过API与存储引擎通信，这些API接口屏蔽了不同存储引擎的差异。

 

查询流程

 

查询缓存

在解析一个查询语句前，如果查询缓存是打开的，那么MySQL会检查这个查询语句是否命中查询缓存中的数据。如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。这种情况下，查询不会被解析，也不会生成执行计划，更不会执行。 MySQL将缓存存放在一个引用表（类似于HashMap的数据结构），通过一个哈希值索引，这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以两个查询在任何字符上的不同（空格、注释），都会导致缓存不会命中。

如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表，其查询结果都不会被缓存。比如函数NOW()或者CURRENT_DATE()会因为不同的查询时间，返回不同的查询结果，再比如包含CURRENT_USER或者CONNECION_ID()的查询语句会因为不同的用户而返回不同的结果，将这样的查询结果缓存起来没有任何的意义。

语法解析和预处理

MySQL通过关键字将SQL语句进行解析，并生成一颗对应的解析树。这个过程解析器主要通过语法规则来验证和解析。比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。预处理则会根据MySQL规则进一步检查解析树是否合法。比如检查要查询的数据表和数据列是否存在等等

MySQL查询优化器

1.重新定义表的关联顺序（多张表关联查询时，并不一定按照SQL中指定的顺序进行，但有一些技巧可以指定关联顺序）

2.优化MIN()和MAX()函数（找某列的最小值，如果该列有索引，只需要查找B+Tree索引最左端，反之则可以找到最大值）

3.提前终止查询（使用Limit时，查找到满足数量的结果集后会立即终止查询）

4.优化排序（在老版本MySQL会使用两次传输排序，即先读取行指针和需要排序的字段在内存中对其排序，然后再根据排序结果去读取数据行，而新版本采用的是单次传输排序，也就是一次读取所有的数据行，然后根据给定的列排序）

查询执行引擎

在完成解析和优化阶段以后，MySQL会生成对应的执行计划，查询执行引擎根据执行计划给出的指令逐步执行得出结果。整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成，这些接口被称为handler API。查询过程中的每一张表由一个handler实例表示，实际上，MySQL在查询优化阶段就为每一张表创建了一个handler实例，优化器可以根据这些实例的接口来获取表的相关信息，包括表的所有列名、索引统计信息等。存储引擎接口提供了非常丰富的功能，但其底层仅有几十个接口，这些接口像塔积木一样完成了一次查询的大部分操作。

数据库设计上做一些优化：

1.用多个小表代替一个大表，注意不要过度设计

2.批量插入代替循环单条插入

3.合理控制缓存空间大小，一般来说其大小设置为几十兆比较合适

4.可以通过SQL_CACHE和SQL_NO_CACHE来控制某个查询语句是否需要进行缓存

四大特性

1. 原子性（通过Undo log实现）：事务是数据库的逻辑工作单位，事务中包括的诸操作要么全做，要么全不做。
2. 一致性：事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
3. 隔离性：一个事务的执行不能被其他事务干扰。
4. 持久性（崩溃恢复：Redo log + double write 实现）：一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。

事务隔离级别（默认值为repeatable read）

脏读：读取了事务未提交的数据

不可重复读：两个并发的事务，A事务读取的数据被B事务修改

幻读：两次读取的数据不一致。

1、Read uncommitted 读未提交（可能存在【脏读】）

eg：脏读是两个并发的事务，“事务A：领导发工资”、“事务B：我查询工资账户”，事务B读取了事务A尚未提交的数据。

2、Read committed 读提交（可能存在【不可重复读】）：不可重复读是两个并发的事务，“事务A：消费”、“事务B：老婆网上转账”，事务A事先读取了数据，事务B紧接了更新了数据，并提交了事务，而事务A再次读取该数据时，数据已经发生了改变。

当隔离级别设置为Read committed时，避免了脏读，但是可能会造成不可重复读。

3、Repeatable read 重复读（可能存在幻读）：当隔离级别设置为Repeatable read时，可以避免不可重复读。当我拿着工资卡去消费时，一旦系统开始读取工资卡信息（即事务开始），我老婆就不可能对该记录进行修改，也就是不能在此时转账。例如：老婆工作在银行部门，她时常通过银行内部系统查看我的信用卡消费记录。有一天，她正查询到我当月信用卡的总消费金额（select sum(amount) from transaction where month = 本月）为80元，而我此时正好在外面吃完大餐后在收银台买单，消费1000元，即新增了一条1000元的消费记录（insert transaction ... ），并提交了事务，随后老婆将我的当月信用卡消费的明细打印到A4纸上，却发现消费总额为1080元，老婆很诧异，以为出现了幻觉，幻读就这样产生了。

虽然Repeatable read避免了不可重复读，但还有可能出现幻读。

 

总结：

　　·Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。

　　·Repeatable read (可重复读)：可避免脏读、不可重复读的发生。

　　·Read committed (读已提交)：可避免脏读的发生。

　　·Read uncommitted (读未提交)：最低级别，任何情况都无法保证。

以上四种隔离级别最高的是Serializable级别，最低的是Read uncommitted级别，级别越高，执行效率就越低。像Serializable这样的级别，就是以锁表的方式(类似于Java多线程中的锁)使得其他的线程只能在锁外等待，所以平时选用何种隔离级别应该根据实际情况。在MySQL数据库中默认的隔离级别为Repeatable read (可重复读)。

三大日志：

二进制日志( binlog )和事务日志(包括redo log 和 undo log )

1、bin log(归档日志)

binlog 用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中，由server层进行记录。对于 InnoDB 存储引擎而言，只有在事务提交时才会记录binlog。

使用场景

主从复制 ：在 Master 端开启 binlog ，然后将 binlog发送到各个 Slave 端， Slave 端重放 binlog 从而达到主从数据一致【relay log 中继日志】。

数据恢复 ：通过使用 mysqlbinlog 工具来恢复数据。

binlog刷盘时机（默认值为1）

mysql 通过 sync_binlog 参数控制 biglog 的刷盘时机，取值范围是 0-N：

0：不去强制要求，由系统自行判断何时写入磁盘；

1：每次 commit 的时候都要将 binlog 写入磁盘；

N：每N个事务，才会将 binlog 写入磁盘。

binlog 日志格式（默认值为row）

1、 STATMENT： 基于SQL 语句的复制( statement-based replication, SBR )，每一条会【修改】数据的sql语句会记录到binlog 中 。

优点：不需要记录每一行的变化，减少了 binlog 日志量，节约了 IO , 从而提高了性能；

缺点：在某些情况下会导致主从数据不一致，比如执行【函数】sysdate() 、 sleep() 等 。

2、 ROW ：基于行的复制(row-based replication, RBR )，不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了 。

优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题 ；

缺点：会产生大量的日志，尤其是alter table 的时候会让日志暴涨

3、MIXED：基于STATMENT 和 ROW 两种模式的混合复制(mixed-based replication, MBR )，一般的复制使用STATEMENT 模式保存 binlog ，对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 binlog。

2、redo log(重做日志)

1、使用redo log的原因：事务的四大特性里面有一个是 持久性 ，具体来说就是只要事务提交成功，那么对数据库做的修改就被永久保存下来了， Innodb 是以 页 为单位进行磁盘交互，一个事务很可能只修改一个数据页里面的几个字节，可能涉及修改多个数据页，并且这些数据页在物理上并不连续，使用随机IO写入性能太差！因此 mysql 设计了 redo log ， 具体来说就是只记录事务对数据页做了哪些修改，这样就能完美地解决性能问题了(相对而言文件更小并且是顺序IO)。

2、redo log 包括两部分：一个是内存中的日志缓冲( redo log buffer )，另一个是磁盘上的日志文件( redo logfile)。mysql 每执行一条 DML 语句，先将记录写入 redo log buffer，后续某个时间点再一次性将多个操作记录写到 redo log file。这种 先写日志，再写磁盘 的技术就是 MySQL里经常说到的WAL(Write-Ahead Logging) 技术。redo log buffer 写入 redo logfile 实际上是先写入 OS Buffer ，然后再通过系统调用 fsync() 将其刷到 redo log file。

3、redo log记录形式， redo log 实际上记录数据页的变更，而这种变更记录是没必要全部保存，因此 redo log实现上采用了大小固定，循环写入的方式，当写到结尾时，会回到开头循环写日志

 

在innodb中，既有redo log 需要刷盘，还有 数据页 也需要刷盘， redo log存在的意义主要就是降低对 数据页 刷盘的要求

undo log(回滚日志)

1、使用undo log 的原因：数据库事务四大特性中有一个是 原子性 ，底层就是通过 undo log 实现的。undo log主要记录了数据的逻辑变化，比如一条 INSERT 语句，对应一条DELETE 的 undo log ，对于每个 UPDATE 语句，对应一条相反的 UPDATE 的 undo log ，这样在发生错误时，就能回滚到事务之前的数据状态。产生的Undo日志可以在事务提交后直接删除

同时， undo log 也是 MVCC(多版本并发控制)实现的关键。undo里面有按照顺序排列的read view，一个事务开始的时候，系统就会分给它一个read view，这样也就实现了MVCC。

MVCC（行级锁）

MVCC 是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。说白了 MVCC 就是为了实现读-写冲突不加锁，而这个读指的就是快照读, 而非当前读，当前读实际上是一种加锁的操作，是悲观锁的实现。

通过保存数据在某个时间点的快照来实现的。引入一个比较重要的概念——read view。我们知道，一个事务读取到的数据实际上是一个快照，这是MVCC的基本功能，只有这样，才能保证并发能力，即一个事务拿到记录的X锁之后，并不会阻塞其他事务读取数据，即便X锁和其他的锁是互斥的。每一次的数据更新，都会生成一个read view。

• 当前读

像 select lock in share mode (共享锁), select for update; update; insert; delete (排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁

• 快照读

像不加锁的 select 操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即 MVCC ,可以认为 MVCC 是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。

mvcc实现策略

在每一行数据中额外保存两个隐藏的列：当前行创建时的版本号和删除时的版本号，每开始新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询每行记录的版本号进行比较。每个事务又有自己的版本号，这样事务内执行CRUD操作时，就通过版本号的比较来达到数据版本控制的目的。

mvcc下的CRUD

1.插入数据（insert）:记录的版本号即当前事务的版本号

2、在更新操作的时候，采用的是先标记旧的那行记录为已删除，并且删除版本号是事务版本号，然后插入一行新的记录的方式。

3、删除操作的时候，就把事务版本号作为删除版本号。

4、查询操作：

在查询时要符合以下两个条件的记录才能被事务查询出来：

1) 删除版本号未指定或者大于当前事务版本号，即查询事务开启后确保读取的行未被删除。(即上述事务id为2的事务查询时，依然能读取到事务id为3所删除的数据行)

2) 创建版本号 小于或者等于 当前事务版本号 ，就是说记录创建是在当前事务中（等于的情况）或者在当前事务启动之前的其他事物进行的insert。

（即事务id为2的事务只能读取到create version

补充：

1.MVCC手段只适用于Msyql隔离级别中的读已提交（Read committed）和可重复读（Repeatable Read）.

2.Read uncimmitted由于存在脏读，即能读到未提交事务的数据行，所以不适用MVCC.

原因是MVCC的创建版本和删除版本只要在事务提交后才会产生。

3.串行化由于是会对所涉及到的表加锁，并非行锁，自然也就不存在行的版本控制问题。

4.通过以上总结，可知，MVCC主要作用于事务性的，有行锁控制的数据库模型。

是乐观锁的一整实现方式，就是每行都有版本号，保存时根据版本号决定是否成功。

锁机制（innodb默认行级锁）

• 共享锁（读锁）：其他事务可以读，但不能写。
• 排他锁（写锁） ：其他事务不能读取，也不能写。

InnoDB 存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

锁粒度

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

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

InnoDB 实现了以下两种类型的行锁： 

• 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。 
• 排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：

• 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。 
• 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。

InnoDB 行锁实现方式：

• InnoDB 行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。只有执行计划真正使用了索引，才能使用行锁由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然多个session是访问不同行的记录， 但是如果是使用相同的索引键， 是会出现锁冲突的（后使用这些索引的session需要等待先使用索引的session释放锁后，才能获取锁）。

InnoDB的间隙锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

InnoDB使用间隙锁的目的：

1、防止幻读，以满足相关隔离级别的要求；

2、满足恢复和复制的需要

MySQL 的恢复机制要求：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读。

死锁（Deadlock Free）

• 死锁产生：

• • 死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。 
  • 当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁。 
  • 锁的行为和顺序和存储引擎相关。以同样的顺序执行语句，有些存储引擎会产生死锁有些不会——死锁有双重原因：真正的数据冲突；存储引擎的实现方式。

InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。

一些优化锁性能的建议

• 尽量使用较低的隔离级别； 
• 精心设计索引， 并尽量使用索引访问数据， 使加锁更精确， 从而减少锁冲突的机会
• 选择合理的事务大小，小事务发生锁冲突的几率也更小
• 给记录集显示加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁
• 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会
• 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响
• 不要申请超过实际需要的锁级别
• 除非必须，查询时不要显示加锁。 MySQL的MVCC可以实现事务中的查询不用加锁，优化事务性能；MVCC只在COMMITTED READ（读提交）和REPEATABLE READ（可重复读）两种隔离级别下工作
• 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能

乐观锁、悲观锁

乐观锁(Optimistic Lock)：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。 乐观锁不能解决脏读的问题。

悲观锁(Pessimistic Lock)：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。

查询算法：哈希，二叉树，红黑树，AVL树【自平衡二叉树】，B树，B+数【mysql使用】

哈希：无法实现范围查找

二叉树：极端情况下【id自增】，退化为链表

红黑树：数据结构右倾【id自增】

AVL树：数据排序消耗性能，磁盘IO次数较多

*****B树，B+树设计原理：一次磁盘IO尽可能多加载数据到内存******

B树：每个节点限制最多存储两个 key，自动分裂，也会存在磁盘IO次数较多的情况，每个节点存的是数据，所以决定了每个节点保存的数据不会太多

B+树：每个节点存储的是索引，可以存储大量索引，有效减少磁盘IO次数，B+树叶子节点存放数据，并且呈链表排列，数据有序，范围查找时，效率更高。

【跳表？】

MySql数据库索引_爱学习的小奶狗的博客-CSDN博客

索引的数据结构：

MySQL中的索引数据结构类型主要包括两种B+树和HASH。索引的目的：减少随机读，增加顺序读。

索引的优点：

1. 索引可以加快数据的查询速度，这是创建索引的主要原因。

2. 在实现数据的参照完整性方面，可以加速表和表之间的连接

3. 在使用分组和排序语句进行数据查询时，可以减少查询中分组和排序的时间。

4. 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性

索引的缺点：

1.创建索引和维护索引要耗费时间，随着数据量的增加所耗费的时间

也会增加。

2. 索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引

还要占一定的物理空间，如果有大量的索引，索引文件可能比数

据文件更快达到最大文件尺寸。

3. 当对表中的数据进行增加，删除和修改的时候，索引页需要维护，

会降低数据维护的速度。

索引功能划分：

1. 普通索引、唯一索引、主键索引：

普通索引：是MySQL中的基本索引类型，允许在定义索引的列中插

入重复值和空值。

唯一索引：所索引列的值必须唯一，允许有空值。如果是组合索引，

则列值得组合必须唯一。

主键索引：必须保证唯一性，且不允许有空值。

2. 单列索引和组合索引：

单列索引：一个索引只能包含表中的单个列，一张表可以有多个

单列索引。

组合索引：可以将表中多个字段联合起来作为一个索引，只有在查询

条件中使用了这些字段的左边字段时，索引才会被使用。使用

组合索引是遵循最左前缀集合。

3. 全文索引：

全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些

索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。

MySQL中只有MyISAM存储引擎支持全文索引。

4. 空间索引

空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，

分别是GEOMETRY、POINT、LINESTRING和POLYGON。MySQL使用SPATIAL

关键字进行扩展，使得能够用创建正规索引类似的语法创建空间索引。创建

空间索引的列，必须将其声明为NOT NULL，

空间索引只能在存储引擎为MyISAM的表中创建。

5、mysql索引回表，索引条件下推，覆盖索引