MySQL

MyISAM

查询语句

       事务是基于重做日志(redo log) 和 回滚日志 (undo log)实现的。       每提交一个事务必须将改事务的所有日志提交到redo log 进行持久化，数据库可以通过redo log 来保证数据的原子性和持久性。       每当有修改事务时，还会产生undo log，如果需要回滚，则根据undo log的反向语句进行逻辑操作，比如Insert 一条数据就要delete 一条数据，undo log 要保证数据的一致性。

隔离级别与锁的关系？

MｙSQL

CSV

存储引擎与表结构

事　务

联合索引的最左前缀原则

B+树介绍

....

　1. 连接配置优化　2. 架构优化　3. SQL优化与分析　4. 存储引擎与表结构　5. 业务优化

事务

概述：        InnoDB事务主要包括redo log 和 undo log。        undo log 指事务开始之前，首先将数据备份到一个地方；redo log 指事务开始之后，操作的任何一个数据，将最新数据备份到一个地方。       事务的目的：实例或介质失败，事务日志就能派上用场。redo log：         redo log 不是随着事务的提交才写入的，而是在事务的执行过程中，便开始进行redo。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log 进行重做，从而达到事务未入磁盘数据进行持久化的特性。redo log 是为了事务的持久化的产物。undo log：            undo log 用来回滚记录到某个版本，事务未提交之前，undo 保存了未提交之前的版本数据，undo中的数据可作为数据旧版本供其他并发事务进行快照读。是为了事务的原子性的产物，在innodb引擎中用来控制多版本并发。

结构

数 据 库 连 接 池

B+树的概述：      B+树是B树的变体，它与B树的不同之处在于：　　1.所有关键字存储在叶子节点，非叶子节点不存储真正的数据　　2.它为叶子节点增加了一个链指针。　B树和B+树的区别：　　1.B+树非叶子节点不存储数据，搜索复杂度为固定ｌｏｇ(ｎ)，而B树的搜索复杂度不固定，与ｋｅｙ在树中的位置有关，最快为O(1)　　2.B+树叶子节点两两相连增加区间访问性，适合进行范围查找；而B树ｋｅｙ与ｄａｔａ在一起，无法进行区间查找　　3.B+树可以很好的利用空间预读性，减少磁盘的IO次数，同时由于非叶子节点不存储数据，所以每次预读的非叶子节点数目更多，当然B+树也可以更好的进行范围查找，比如查询50-70的数据

索引

索引  原理

架构优化

存储引擎层

执行器

CSV.csv

连接配置优化

一条查询语句是如何执行的

执行器调用引擎得到结果

数据文件

什么是索引？

优化器

性能优化

事务日志介绍

SQL优化与分析

Server

InnoDB

概述

查询缓存命中直接返回结果

1.服务端配置　　ａ.增加可用连接数，默认情况下服务端的最大连接数为151　　ｂ.及时释放不活动的连接，默认客户端超时时间为8小时，可以调小数值2.客户端优化　　ａ. 执行SQL的语句创建连接会造成资源浪费，采用连接池进行资源复用。

概述特征

分类

二进制文件binlog

业务优化，在业务开发的基础上进行优化，也可以有效的减少用户集中访问，从而避免了数据库的压力。

单列索引：在表中的某个字段上创建索引，单列索引只根据该字段进行索引，该索引只对应一个字段，一个表可以有多个单列索引。联合索引：在表中的多个字段上创建索引。只有查询时使用了这些字段中的第一个字段才会被使用到，同时联合索引对应着最左前缀组合原则。

优化锁的意见

连接管理、授权认证、安全

B树介绍

聚簇索引的回表机制

解析器语法分析、词法分析

        当多个用户并发的存取数据时，在数据库中就会产生多个事务同事存储同一事务的情况，若对并发操作不加控制的情况下，就会产生读取和存储不一致的情况，破坏数据的一致性。        保证多用户环境下数据库的一致性和完整性。

普通索引：在创建普通索引时，不添加任何限制条件，只用于提高查询效率。这类索引可以建立在任何数据类型中，其值是否非空或唯一，要由字段本身的约束性来决定。唯一索引：使用unique参数可以设置索引为唯一索引，在创建唯一索引时，限制该索引的值必须是唯一的，但允许有空值，一个表里可以设置多个唯一索引。主键索引：主键索引是一种特殊的唯一索引，在唯一索引的基础上添加了不为空的约束，同时一张表只能有一个主键索引。

为什么要加锁

锁

- 优先使用较低的隔离级别- 设计索引，尽量使用索引去访问数据，加锁更加精准，从而减少锁冲突- 选择合理的事务大小，给记录显示加锁时，最好一次性请求足够级别的锁。例如，修改数据的话，最好申请排他锁，而不是申请共享锁，修改时再申请排他锁，这样会导致死锁。- 不同的程序访问一组表的时候，应尽量约定一个相同的顺序去访问各表，对于一个表而言，尽可能的固定顺序的获取表中的行。这样大大减少了死锁的机会。- 尽量使用相等条件访问数据，这样避免间隙锁对并发插入的影响。- 不要申请超过实际需要的锁级别- 数据查询的时候不是必要，不要使用加锁。MySQL的MVCC可以实现事务中的查询不用加锁，优化事务性能：MVCC只在committed read和repeatable read两种隔离级别- 对于特定的事务，可以使用表锁来提高处理速度或者减少死锁的可能。

MVCC实现原理

解析器

MVCC

错误日志

业务优化

B树的特点如下：      1. 所有键值对分布在整个树中(索引值和具体数据都在某个节点中)      2. 任何一个关键字只出现在某个节点中      3. 搜索有可能在非叶子节点结束(最好情况O(1)就能结束搜索)      4. 在关键字全集内做一次查找，性能直逼二分查找B树的定义：      B树是一颗自平衡的搜索树，它类似普通的平衡二叉树，不同的是它允许每个节点有更多的子节点疑问：为什么索引使用B树而不使用二叉树或者红黑树呢？       当数据量很大内存不够用时，数据就会存储到磁盘上，数据只有读取的时候才会加载到内存，而平衡二叉树旋转是针对整个树的，加载到内存中的数据则无法进行旋转。同时平衡二叉树的是两个节点区间一直分割，可能会导致树高非常高，看似相近的两个数据可能相距的距离非常远，这也就导致无法进行空间预读（空间预读指的是如果一个存储器的某个位置被访问，那么它附近的位置也将被访问）。       上面介绍到了B树允许每个节点拥有更多的子节点，而在Mysql数据查询的过程中，需要进行内存与磁盘的IO交互，索引的快速查找目的就是为了减少磁盘的IO次数。B树将范围分为每个区间，区间越多，定位数据越快越精准，而以节点为区间，则会导致树高非常高

在Read Uncommitted级别下，读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突。在Read Committed级别下，读操作需要加共享锁，但是在语句执行完以后释放共享锁。在Repeatable Read级别下，读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕后才释放共享锁。SERIALIZABLE是限制级别最高的隔离级别，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成。

慢查询日志

      按照功能：普通索引、唯一索引、主键索引、全文索引

底层原理

概述：       MVCC，即多版本并发控制，是通过数据保存在某个时间点的快照来实现的，根据事务开始的时间不同，每个事务对同一个表，同一个时刻看到的数据是不一样的。对数据进行多版本处理，并通过事务的可见性来保证事务能看到自己应该看到的数据版本。作用：         如果某条数据被修改，则可以并发读取该记录的历史版本而不必阻塞等待读锁的释放，其实现是通过undo log 和readView控制的，可以读取undo log 里面的历史版本，readview用来控制哪个历史版本是对当前事务可见的。重要：          MVCC使每个连接到数据库的读者，在某个瞬间看到的是数据库的readView，不同的隔离级别生成的快照粒度不同，读取已提交生成的redaview粒度是每个select单位，所以A事务前后的select不同，造成了不可重复读。可重复读生成的readview的粒度是每个事务，同一个事务生成一个readView避免重复读的问题。快照读：           读取的记录数据的可见版本(可能是过期的数据)，不同加锁，select时未快照读，MVCC实现，不需要加锁。当前读：读取的是当前记录的最新数据，并且当前读返回的记录都会加锁，保证不会有其他事务并发的修改记录，update、delete、insert都是当前读，排他锁。

日志文件

      按照物理实现：聚簇索引和非聚簇索引

隔离级别

优化器选择最优执行方案

MyISAM.MYD .MYI

MySQL逻辑架构

　　由于索引底层是使用B+树进行检索数据的，为了更快的检索数据，非叶子节　点不存储数据，所以当我们预加载叶子节点到内存中找到对应的ｋｅｙ之后，还需要根据ｋｅｙ值再到加载叶子节点到内存中，相当于进行了两次IO，这就是所谓的回表机制。而聚簇索引是否回表，取决于此次SQL所要求的字段是否全部命中了索引值，如果索引值全部命中，则直接根据索引的值获取字段数据即可，不需要重新进行回表查询数据。　　所以，在进行编写SQL语句时，不建议使用SELECT　*，不方便维护索引，增加IO次数。

什么是脏读、幻读、不可重复读？          脏读：事务A读取了事务B更新的数据，然后事务B回滚，事务A读取到的就是脏数据。              不可重复读：事务A多次读取同一数据，事务B在事务A多次读取的过程中，对数据进行更新操作并提交，导致事务多次读取数据时，结果不一致。       幻读：系统管理员将数据库的所有学生从具体成绩改为ABCDE几个等级，但是系统管理员B在这个时候插入了具体分数的记录，当系统管理员A改结束以后发现还有一条记录没改过来，就好像发生了幻读一样，称之为幻读。

按照锁的粒度分数据库锁有哪些？在关系型数据库中，可以按照锁的粒度吧数据库分为行级锁(InnoDB引擎)、表级锁(MYISAM引擎)和页级锁(BDB引擎)。行级锁- 行级锁是MySQL中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也大。行级锁分为共享锁和排他锁。- 开销大，加锁慢；会出现死锁；锁粒度最小，发生锁冲突的概率最低，并发度也最高。表级锁- 表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持，但加锁的开销也大，被大部分MySQL引擎支持。最常使用的MyISAM与InnoDB都支持表级锁定。表级锁定分为表共享读锁(共享锁)与表独占写锁(排他锁)。- 开销小，加锁快；不会出现死锁；锁粒度最大，发出锁冲突的概率最高，并发度最低。页级锁- 页级锁是MySQL中锁定粒度介于比行锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以去了折中的页级锁，一次锁定相邻的一组记录。BDB支持页级锁。- 开销和加锁时间界于表锁和行锁之间，会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般。从锁的类别上来说，有共享锁和排他锁。1. 共享锁：又叫读锁，当用户要进行数据读取时，对数据加上共享锁，共享锁可以同时加上多个。2. 排他锁：又叫写锁。当用户要进行数据的写入时，对数据加上排他锁，排他锁只可以加一个，他和其他的排他锁，共享都排斥。锁的粒度取决于具体的存储引擎，InnoDB实现了行级锁，页级锁和表级锁。他们的加锁开销从大到小，并发能力也从大到小。

MySQL物理组成

索引失效的情况

InnoDBredo log

InnoDB.idb  .bdata

查询缓存

      按照字段个数：单列索引和联合索引

聚簇索引：将数据存储与索引放到了一起，索引结构的叶子节点保存了行数据非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置

N

Y

输出结果

概述：　　事务是一个不可分割的数据库操作序列，也是控制并发的基本单位，其执行的结果必须是使数据库从一种一致性状态变到另一种一致性状态。事务是逻辑上的一组操作，要么同时成功，要么同时失败。特征：　　原子性：事务是指数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做。　　一致性：事务执行的结果必须是使数据库从一种一致性状态转变到另一种一致性状态。如果数据库运行系统中发生故障，有些事务尚未完成发生中断，这些未完成的事务所做的一部分操作已经写入到数据库，这时数据库就处于一种不正确的状态，或者说是不一致的状态。　　隔离性：一个事务的执行不能被另一个事务所干扰。即一个事务内所执行的操作和使用数据对其他并发事务是隔离的，并发执行的各个事务之间不能互相干扰。　　持久性：事务一旦提交，它对数据库的数据操作接下来是永久性的，接下来的其他操作或故障不会对其有任何影响。