#### 1.连接验证及解析

客户端与MySQL Server建立连接，发送语句给MySQL Server，接收到后如果是查询语句会先去查询缓存中看，有的话就直接返回了，（新版本的MySQL已经废除了查询缓存，命中率太低了），如果是缓存没有或者是非查询语句，会创建一个解析树，然后进行优化，（解析器知道语句是要执行什么，会评估使用各种索引的代价，然后去使用索引，以及调节表的连接顺序）然后调用innodb引擎的接口来执行语句。

#### 2.写undo log

innodb 引擎首先开启事务，对旧数据生成一个UPDATE的语句(如果是insert会生成delete语句)，用于提交失败后回滚，写入undo log，得到回滚指针，并且更新这个数据行的回滚指针和事务id。（事务提交后，Undo log并不能立马被删除，而是放入待清理的链表，由purge 线程判断是否有其他事务在使用undo 段中表的上一个事务之前的版本信息，决定是否可以清理undo log的日志空间，简单的说就是看之前的事务是否提交成功，这个事务及之前的事务都提交成功了，这部分undo log才能删除。）

#### 3.从索引中查找数据

根据索引去B+树中找到这一行数据（如果是普通索引，查到不符合条件的索引，会把所有数据查找出来，唯一性索引查到第一个数据就可以了）

#### 4.更新数据

#### 4.1数据页在内存中

##### 4.1.1普通索引

##### 4.1.2唯一性索引

判断更新后是否会数据冲突，不会的话就更新内存中的数据页。

#### 4.2 数据页不在内存中

##### 4.2.1普通索引

将对数据页的更新操作记录到change buffer，change buffer会在空闲时异步更新到磁盘。

##### 4.2.2唯一性索引

因为需要保证更新后的唯一性，所以不能延迟更新，必须把数据页从磁盘加载到内存，然后判断更新后是否会数据冲突，不会的话就更新数据页。

#### 5.写undo log（prepare状态）

将对数据页的更改写入到redo log，将redo log设置为prepare状态。

#### 6.写bin log（commit状态）

通知MySQL server已经更新操作写入到redo log 了，随时可以提交，将执行的SQL写入到bin log日志，将redo log改成commit状态，事务提交成功。

#### undo log

主要是保证事务的原子性，事务执行失败就回滚，用于在事务执行失败后，对数据回滚。

是逻辑日志，记录的是SQL

在事务提交后，undo log日志不会立即删除，会放到一个待删除的链表中，有purge线程判断是否有其他事务在使用上一个事务之前的版本信息，然后决定是否可以清理，简单的来说就是前面的事务都提交成功了，这些undo才能删除。

##### change buffer是什么（就是将更新数据页的操作缓存下来）

在更新数据时，

如果数据行所在的数据页在内存中，直接更新内存中的数据页。

如果不在内存中，为了减少磁盘IO的次数，innodb会将这些更新操作缓存在change buffer中，在下一次查询时需要访问这个数据页时，在执行change buffer中的操作对数据页进行更新。

适合写多读少的场景，因为这样即便立即写了，也不太可能会被访问到，延迟更新可以减少磁盘I/O，只有普通索引会用到，因为唯一性索引，在更新时就需要判断唯一性，所以没有必要。

##### redo log

就是为了保证事务的持久性，在做数据更新操作时，先将对数据页的更改记录到redo log，然后再去更新内存中的数据页，在下次查询数据页或者空闲时间，将操作记录更新到磁盘。这样可以将随机I/O改为顺序I/O。

优点是减少磁盘I/O次数，即便发生故障也可以根据redo log来将数据恢复到最新状态。

缺点是会造成内存脏页，后台线程会自动对脏页刷盘，或者是淘汰数据页时刷盘，此时会暂时查询操作，影响查询。

#### 二段提交制

更新时，先改内存中的数据页，将更新操作写入redo log日志，此时redo log进入prepare状态，然后通知MySQL Server执行完了，随时可以提交，MySQL Server将更新的SQL写入bin log，然后调用innodb接口将redo log设置为提交状态，更新完成。

如果只是写了bin log就提交，那么忽然发生故障，主节点可以根据redo log恢复数据到最新，但是主从同步时会丢掉这部分更新的数据。

如果只是写binlog，然后写redo log，如果忽然发生故障，主节点根据redo log恢复数据时就会丢掉这部分数据。

##### 崩溃恢复时的判断规则（以redolog是否commit或者binlog是否完整来确定）

1. 如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交；

2. 如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整：

a. 如果是，则提交事务；

b. 否则，回滚事务。

参考资料：

https://gsmtoday.github.io/2019/02/08/how-update-executes-in-mysql/

https://www.infoq.cn/article/M6g1yjZqK6HiTIl_9bex

就是内存数据页与磁盘内存页的内容不一致时的内存页叫做脏页。内存页数据写入磁盘后，数据一致了，就是干净页了。

1.redo log 写满了，系统会停止所有更新操作，将checkpoint向前推进，将推进这部分日志的脏页更新到磁盘。

2.系统内存不够，需要将一部分数据页淘汰，如果是干净页，直接淘汰就行了，脏页的话，需要全部同步到磁盘。

3.MySQL自认为空闲时去刷脏页。

4.MySQL正常关闭之前，会将脏页刷入磁盘。

就是系统故障后，根据redo来恢复数据时，不需要重做所有日志，只需要重做checkpoint点之后的日志，因为redo log也不能无限大，所以当redo log空间不足时，redo log中那部分被更新到磁盘的日志可以覆盖重用。

1、缩短数据库的恢复时间；

2、缓冲池不够用时，将脏页刷新到磁盘；

3、重做日志空间不足时，刷新脏页。

##### undo log 是什么？

undo log是一种逻辑日志，是旧数据的备份。有两个作用用于事务回滚和MVCC。

执行一条INSERT语句时，会记录一条相反的DELETE语句到日志，执行一条UPDATE语句时，会记录一条相反的UPDATE语句到日志中。

##### redo log是什么？

redo log用于保证数据的持久性。redo log记录的是数据页的物理变化，是新数据的备份，在事务提交前，将redo log 持久化就行，不需要将数据持久化，系统崩溃时，可以根据redo log将数据恢复到最新状态。

redo log只做顺序追加操作，当事务需要回滚时，在redo log中也不会删除之前的事务记录。

例如将A=1修改为A=2

将原始数据A=1从磁盘读取到内存，

修改A=2，

生成一条redo log 写入到redo log 缓冲区

调用fsync操作将redo log 缓冲区的内容写入到磁盘

事务提交。

##### Bin log 是什么？

保存的是逻辑日志，主要是存储每一条会修改数据的SQL。

由一系列数据库操作组成的逻辑过程，可以是一个SQL查询，也可以是一组SQL查询。

具备四个特性，也就是acid

原子性，一个事务就是最小的工作单元，要么执行成功提交，要么执行失败回滚。

一致性，就是事务的执行不会影响数据库的完整性，例如执行到一半时发生故障，那么已经进行的修改会回滚。

隔离性，就是事务在执行过程中，两个事务之间是隔离的，事务在执行成功之前，所做的修改对其他事务是不可见的。

持久性，事务执行成功后，对数据的修改是永久的，即便故障也不会丢失。

##### 未提交读

事务还没有提交的修改，其他事务都可以读取到。可能会有脏读的问题，就是读到一些未提交的脏数据。

##### 提交读

其他事务提交的修改，事务在执行过程中可以读取到，如果一个事务在执行过程中需要两次读取同一行数据，可能会不一致。一般发生在UPDATE和DELETE操作。（大部分数据库系统是采用的这个，但是mysql不是）

这个隔离级别下，读是不加锁的，写，更新，删除是加锁的，如果更新的行是可以通过索引查找到，那么是对这些行加行锁，否则会将所有行都加锁，然后返回给Mysql Server，让他来进行过滤，对于不满足条件的行解锁。

##### 可重复读

在事务开始时，记录当时的状态，在第二次读取同一行数据时，除非是本事务做的修改，否则读取的都是事务开始时的数据。可以解决脏读的问题，没法解决幻读的问题。这是mysql的默认事务隔离级别。但是还是会有幻读的问题(幻读就是事务A在读取和写入符合的条件的记录时，其他事务又插入了一条符合条件的记录，此时事务A读取时会产生幻行，一般发生在INSERT操作。)

##### 可串行化

强制事务串行执行，会让读取每一行都加锁，读用读锁，写用写锁，读写锁互斥，可以解决幻读的问题。并发比较多的话可能会造成大量的超时等待和锁竞争。如果业务并发的特别少或者没有并发，同时又要求数据及时可靠的话

mvcc主要适用于提交读，可重复读，可以解决幻读的问题。

innodb在解决幻读的问题主要是通MVVC 多版本并发版本控制来实现的

就是每一行数据中额外保存两个隐藏的列，创建时的版本号，删除时的版本号（可能为空），滚动指针(指向undo log中用于事务回滚的日志记录)

1.事务在对数据修改后，进行保存时，如果数据行的当前版本号与事务开始取得数据的版本号一致就保存成功，否则保存失败。

##### 插入操作

每次开始事务时，会对系统版本号+1作为当前事务的版本号。

插入数据后，将事务的版本号作为数据行的创建版本号。

##### 删除操作

将当前的事务版本号作为数据行的删除版本号。

##### 更新操作

插入一条新的数据，将事务的版本号作为数据行的创建版本号，并且将当前事务版本号作为旧数据的删除版本号。

##### 查询操作

数据行要被查询出来必须满足两个条件，

删除版本号为空或者>当前事务版本号的数据(否则数据已经被标记删除了)

创建版本号<=当前事务版本号的数据（否则数据是后面的事务创建出来的）

如果查询时，该行数据被加了X锁，那么读数据的事务不会进行等待，而是回去undo log端里面读之前版本的数据（这里存储的数据本身是用于回滚的），在可重复读的隔离级别下，从undo log中读取的数据总是事务开始时的快照数据(也就是版本号小于当前事务id的数据)，在提交读的隔离级别下，从undo log中读取的总是最新的快照数据。

幻读就是事务执行过程中，在查询一个范围的数据时，有新的数据插入到这个范围，导致两次查询的数据不一致。因为读分为快照读和实时读，

我们普通的SELECT语句都是普通读，也就是读取的数据都是事务开始时那个状态的数据，普通读的幻读问题主要是通过MVCC来解决的，具体可以看上面的MVCC中的查询操作。

SELECT *** FOR UPDATE 在查询时会先申请X锁

SELECT *** IN SHARE MODE 在查询时会先申请S锁

就是实时读，就是读取的是实时的数据，而不快照数据，读的时候会加Next-Key Lock锁住当前的记录，以及左右两个区间的间隙，这样在读的时候就不能往我们的查询范围插入数据了。

#### 全局锁

就是对整个数据库加锁，让整个数据库处于只读状态，所有更新操作停止。（如果是主库就不能执行更新语句，从库也不能执行同步过来的bin log）

最常用的场景是对数据库加锁，让数据库只能读，然后对整个数据库做逻辑备份（就是将所有数据生成SQL写入备份文件。）

做逻辑备份有三种方式：

##### 1.全局锁

Flush tables with read lock命令让整个库处于只读状态。

##### 2.利用innodb的事务隔离性（可重复读）

就是通过官方自带的逻辑备份工具mysqldump来进行逻辑备份时，可以设置一个参数-single-transaction，这样导数据的时候就会开启一个事务，这样利用innodb的mvcc机制可以保证在事务执行过程中，读到的数据都跟事务开始时的一致，并且执行过程中，其他事务可以执行更新操作， 不会对他造成影响。这种方法必须要求数据库所有表的引擎都是innodb才行。

##### 3.set global readonly=true

执行这个命令也可以让全库只能读，但是第一有些系统会使用readonly来做一个操作，例如根据readonly是否为true判断数据库是否是从库，第二是如果执行这个命令后，客户端断开连接后，数据库会一直处于只读状态，如果是FTWRL命令发送异常会释放全局锁。（如果是从库，设置read-only对super user权限无效）

表级别的锁有两种，一种是表锁，一种是元数据锁MDL。

##### 表锁 lock table

就是使用lock table user_table read/write命令来对表进行加读锁或者写锁

加读锁后，表对所有线程都是只能读，即便是当前线程也只能读表，不然会数据不一致。

加写锁后，表是对当前线程写，其他线程不能读，不能回数据不一致。

可以通过unlock tables来解锁，客户端断开时也会自动释放锁，但是影响所有线程，影响面太大了。

##### 元数据锁MDL(MetaData Lock)

分为读锁和写锁，加读锁时，所有的线程都可以读表，加写锁时，只能一个线程写，其他的不能读。

锁不用显式使用，是访问一个表时，自动加上的。

对表进行增删改查时，会加读锁。

对表结构做修改时，会加写锁。

目的是为了在增删改查时不能修改表结构，修改表结构时不能去增删改查。

##### 自增锁

插入语句主要分为两种：

1.能确定插入行数的，例如插入一条或者多条数据，INSERT...

2.不能确定行数的，例如从一个表查询出满足条件的数据，然后插入另外一个表，INSERT...SELECT

在所有模式中，如果一个事务回滚，这些自增值将被“丢失”。

这种是tradition模式，每次执行一条插入语句时都会去申请表级别的auto_increment锁

这种是consecutive模式，执行不确定数量的插入语句时，才会去申请表级别的auto_increment锁，

执行确定数量的插入语句时，只需要执行前去获取 AUTO_INCREMENT 计数器的互斥锁并在获取主键后直接释放，

不需要等待当前语句执行完成。

交叉模式 所有的插入语句都不需要获取表级别的 AUTO_INCREMENT 锁，

如果binlog_format为statement模式，如果从服务器上的计数器的值可能会与主服务器不一致，

可能会有同一行数据在主从数据库上id不一样的情况，如果binlog_format为row模式，那么就不影响。

##### 意向锁

意向锁定的主要目的是表明有人正在锁定表中的行，或者打算锁定表中的行，这样再加表级别的排斥锁X，共享锁S时，避免了去查询每一行数据，判断是否加了行锁，减小了性能开销。

**意向共享锁（IS锁）**

事务让一行数据只能读，需要申请对这行数据加行级别的锁共享锁S，在申请S锁之前会主动申请表级别的共享意向锁IS锁。

**意向排斥锁(IX锁)**

事务在更新某一行数据时，需要申请对这行数据加行级别的锁排斥锁X，在申请X锁之前会申请IX

意向锁之间是兼容的，IS锁和IX是兼容，因为可能我们对第一行数据加S锁，那么会申请IS锁，对第二行数据加X锁，此时跟第一行的数据的S锁不冲突，所以也会先申请IX锁，由此可见，IS锁和IX之间不冲突，IS锁，IX锁与行级别的S，行级别的X之间也不冲突。

意向锁只是跟表级别的S，X锁可能会冲突。

| | 表级别的S锁 | 表级别的X锁 |

| ---- | ----------- | ----------- |

| IS | 兼容 | 不兼容 |

| IX | 不兼容 | 不兼容 |

##### 那么意向锁的作用是什么呢？

假如没有意向锁，我们执行lock table read命令来申请表锁，让整个表只能读，在获得表级别的只读锁之前，需要执行的步骤是：

1.数据库会先判断当前表是否加了排斥锁，因为这个时候要是加了排斥锁，是只能由加了那个排斥锁的事务来更新数据，其他事务都不能读数据，只能阻塞等待。

2.如果当前表没有加排斥锁，那么就需要对每一行数据进行判断，判断是否加了行级别的X锁，如果加了只能阻塞等待，这样需要对一行进行判断，性能开销太大了。

所以才有了意向锁，在获得表级别的只读锁之前，需要执行的步骤是：

2.第二步只需要判断当前锁是否加了表级别的意向排斥锁，因为如果加了意向排斥锁，说明正在有事务在对数据加行锁，对数据进行更新，这样避免了对每一行数据进行判断，判断是否加了行锁。

####Innodb的锁

##### 行锁

* 共享锁 S锁，就是读锁，允许事务读一行数据，不能被修改。所以读锁之间不排斥

* 互斥锁 X锁，就是写锁，就是让当前事务可以修改这行数据，其他事务不能修改这行数据

##### 记录锁 record lock

记录锁定是对单条索引记录的锁定。例如， `SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;` 可以防止从插入，更新或删除行。

##### 间隙锁 gap lock

间隙锁就会对记录之间的间隙加锁，防止数据插入。就是我们在使用实时读(SELECT FOR … UPDATE)或者更新，为了防止读的过程中有新的数据插入，会对我们读的数据的左右区间进行加锁，防止其他事务插入数据，所以间隙锁之间是不排斥的，间隙锁排斥的只是插入数据的操作。

##### 下一键锁 next-key lock

next-key lock就是会锁记录以及记录之间的间隙，就是 record lock 和 gap lock的组合，就是会对索引记录加记录锁 + 索引记录前面间隙上的锁”，就是对要更新的数据的左右两个端点加间隙锁，

例如num是一个普通索引，非唯一性索引，已有数据是1，5，10，20，30

那么 next-key lock可以锁定的区间是

```

(负无穷,1]

(1,5]

(5,10]

(10,20]

(20,30]

(30,正无穷)

```

```

update table set note = '1' where num = 10;

SELECT * FROM table WHERE num = 10 for UPDATE;

```

如果num是唯一性索引，那么只需要对num为10的这条索引加锁就行了，因为不用担心其他事务再插入一条num为10的数据，因为会有唯一性判断。但是如果num是非唯一性索引，为了防止事务执行过程中有num为10的数据插入，那么会对（5，10]和（10，20]这两个区间加锁。

死锁就是两个或多个事务在同一资源上相互占用，并且请求对方的占用的资源。InnoDB目前处理死锁的方法就是将持有最少行级排他锁的事务进行回滚。

#### B树是什么？


平衡二叉树就是每个节点左右子树的高度差小于等于1，B树其实是一个平衡多路查找树，假设是M阶的，

2.非根非页节点的关键字数是 需要<=m-1并且>ceil(m/2)-1，

3.节点内的元素从左到右递增，左边节点的所有元素值<右边节点的所有元素值

4.叶子节点在同一层，高度一致。

跟二叉树相比，因为每个节点关键字会多很多，所以相同的关键字数时，层级会少很多，会减少查找时间和复杂度。

https://segmentfault.com/a/1190000020416577

https://blog.csdn.net/chai471793/article/details/99563704


1.B树每个节点会保存关键字，索引和数据。而B+树只有叶子节点保存数据，其他节点只保存关键字和索引。所以相同的内存空间可以容纳更多的节点元素。

2.B+树的所有数据都存在叶子节点上，所以查询会更加稳定，而且相邻的叶子节点都是连接在一起的，更加适合区间查找和搜索，

##### B+树与二叉树区别，优点？为什么不用红黑树？

红黑树是一个平衡的二叉查找树。即便在最坏的情况下，红黑树能够以O(log(N))的时间复杂度进行搜索、插入、删除操作，

##### 与红黑树的比较

红黑树等平衡树也可以用来实现索引，但是文件系统及数据库系统普遍采用 B+ Tree 作为索引结构，主要有以下两个原因：

（一）更少的查找次数

平衡树查找操作的时间复杂度和树高 h 相关，O(h)=O(logdN)，其中 d 为每个节点的出度。

红黑树的出度为 2，而 B+ Tree 的出度一般都非常大，所以红黑树的树高 h 很明显比 B+ Tree 大非常多，查找的次数也就更多。

（二）利用磁盘预读特性

为了减少磁盘 I/O 操作，磁盘往往不是严格按需读取，而是每次都会预读。而B+数中存储的叶子节点在内存中是相邻的，这样可以读取会快一些。

1.大大减少了服务器需要扫描的数据量。

2.帮助服务器避免排序带来的性能开销。

3.将随机IO变成顺序IO。

因为字符串索引在索引树上两个节点比较会比较慢，数字类型的索引会快一些，

如果非要用字符串索引可以采用以下解决方法。

1.对字符串的前n个字符建立前缀索引，前缀索引不能使用order by。

2.增加一列，对字符串转换为整型的hash值address_key=hashToInt(address)，对address_key建立索引，查询时同时限定hash值也限定地址。可以用如下查询where address_key = hashToInt(‘beijing,china’) and address = ‘beijing,china’;

效率的话，100万的数据量，字符串索引查询600ms，数字查询20ms。

union就是将两个SELECT语句查询的结果集合并(两个SELECT可以是同一个表，也可以是不同表)，如果需要排序，在第二个SELECT语句后加ORDER BY语句，会对所有结果进行排序。

union默认是会去除重复的数据的，会对结果集做去重处理，union all不会做去重处理。

所以union效率慢一些，如果能确定结果不会重复或者需要不去重的结果，那么应该使用union all，效率会高一些。


##### join的大概使用是怎么样的？

full outer join 会包含两个表不满足条件的行

left outer join 会包含左边的表不满足条件的行

right outer join 会包含右边的表不满足条件的行

inner join 就是只包含满足条件的行

cross join 从表A循环取出每一条记录去表B匹配，cross join 后面不能跟on，只能跟where

##### 工作流程

在使用join时，默认会选择满足条件的行数少的表作为驱动表，然后将它作为外层循环，取驱动表的结果集中的每一行数据去下一个表中查询符号条件的数据，然后合并结果，得到结果集，如果还有第三个表，那么继续将结果集中的每一行去第三个表中查询符号条件的数据。

在进行多表查询时，

* 如果没有使用join，那么默认选择满足条件的行数少的表作为驱动表。

* 如果使用了join，如果是left join，那么左边的表作为驱动表，如果是right join，那么右边的表作为驱动表，否在默认满足条件的行数少的表作为驱动表。

##### 1.排序时尽量使用驱动表中的字段

因为如果使用的是非驱动表中的字段会对非驱动表（的字段排序）需要对循环查询的合并结果（临时表）进行排序，比较耗时，使用Explain时会发现出现Using temporary。

##### 2.去掉join，直接进行多表查询

此时MySQL会自己选择小表作为驱动表，减少查询耗时。

##### 3.增大join_buffer_size的值

##### Index Nested-Loop Join

就是查询时，被驱动表上的索引可以使用，这样驱动表作为外层循环，对驱动表结果集遍历时，每次去被驱动表查询时可以使用索引。

##### Block Nested-Loop Join

就是查询时，每次可以将驱动表的一部分加载到内存缓冲区中join_buffer中去，这样每次对被驱动表进行遍历，每次取一个元素与join_buffer中的元素进行对比，这样被驱动表的遍历次数就等于 被驱动表的行数*(驱动表的行/每次能加到join_buffer中的行数)，这样join_buffer越大，被驱动表被扫描的次数就越少，所以可以增大join_buffer的大小。

(join_buffer的大小是由参数join_buffer_size设定的，默认值是256k)

##### exits 和in，join的区别是什么？

exists是拿外表的数据做循环，每次循环去内表中查询数据，使用适内表比较大的情况

而 in的话正好相反，是那内表的数据做循环，每次循环去外表查询数据，适合内表比较小的情况。

```

select * from A where cc in (select cc from B)

-- 效率低，用到了A表上cc列的索引；

select * from A where exists(select cc from B where cc=A.cc)

-- 效率高，用到了B表上cc列的索引。

```

not in 和not exists如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

join的实现其实是先从一个表中找出所有行（或者根据where子句查出符号条件的行)，然后去下一个表中循环寻找匹配的行，依次下去，直到找到所有匹配的行，使用join不会去创建临时表，使用in的话会创建临时表，销毁临时表

所以不管是in子查询，exists子查询还是join连接查询，底层的实现原理都是一样的，本质上是没有任何区别的，关键的点在关联表的顺序，如果是join连接查询，MySQL会自动调整表之间的关联顺序，选择最好的一种关联方式。和上面in和exists比较的结论一样，小表驱动大表才是最优的选择方式

如果没有设置主键，innodb会选择第一个非空唯一索引作为聚集索引。

如果没有设置主键，也没有合适的唯一索引，那么会生成一个隐藏的id作为索引的主键，这个值会随着插入而自增。

主键如果是自增的，那么插入数据的位置是已知的，而且不用移动已有数据。如果是非自增的，首先需要查找到要插入的位置，近似于随机查找，然后将后面的数据向后移动。

聚集索引与非聚集索引的最主要的区别是：叶节点是否存放一整行记录

##### innodb

innodb的索引是聚簇索引，就是所有数据都存在聚集索引的的叶子节点中，其他二级索引的的叶子节点值存储KEY字段加对应列的主键值，如果使用二级索引查找数据，先根据索引查到二级索引对应的行的主键id，然后根据主键id去聚集索引中查找对应的行的数据。（所以innodb必须要有主键）



1.对于**聚簇索引表**来说（左图），表数据是和主键一起存储的，主键索引的叶结点存储行数据(包含了主键值)，其他列，事务ID，回滚指针，二级索引的叶结点存储行的主键值。使用的是B+树作为索引的存储结构，非叶子节点都是索引关键字，但非叶子节点中的关键字中不存储对应记录的具体内容或内容地址。叶子节点上的数据是主键与具体记录(数据内容)。

2.对于**非聚簇索引表**来说（右图），表数据和索引是分成两部分存储的，主键索引和二级索引存储上没有任何区别。使用的是B+树作为索引的存储结构，所有的节点都是索引，叶子节点存储的是索引+指向索引对应的记录的数据的指针。

##### 聚簇索引的优点

1.当你需要取出一定范围内的数据时，用聚簇索引也比用非聚簇索引好。

2.当通过聚簇索引查找目标数据时理论上比非聚簇索引要快，因为非聚簇索引定位到对应主键时还要多一次目标记录寻址,即多一次I/O。

3.使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

###### 聚簇索引的缺点

1.**插入速度严重依赖于插入顺序**，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。

2.**更新主键的代价很高，因为将会导致被更新的行移动**。因此，对于InnoDB表，我们一般定义主键为不可更新。

3.**二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。**

二级索引的叶节点存储的是主键值，而不是行指针（非聚簇索引存储的是指针或者说是地址），这是为了减少当出现行移动或数据页分裂时二级索引的维护工作，但会让二级索引占用更多的空间。

4.**采用聚簇索引插入新值比采用非聚簇索引插入新值的速度要慢很多**，因为插入要保证主键不能重复，判断主键不能重复，采用的方式在不同的索引下面会有很大的性能差距，聚簇索引遍历所有的叶子节点，非聚簇索引也判断所有的叶子节点，但是聚簇索引的叶子节点除了带有主键还有记录值，记录的大小往往比主键要大的多。这样就会导致聚簇索引在判定新记录携带的主键是否重复时进行昂贵的I/O代价。

联合索引就是多列索引，就是可以多个字段建立一个索引，并且是最左前缀匹配元素，

```

create index a_b_c on user(a,b,c)

a,b

a,b,c

```

就是在非聚簇索引对应的B+树中，索引的排序是先比较a的大小，再比较b的大小然后再比较c的大小。并且是遇到范围比较时就会停止匹配。

1. 最左前缀匹配原则，MySQL会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配，比如 a="3" and="" b="4" c="">5 and d=6,如果建立(a,b,c,d)顺序的索引，d是无法使用索引的，如果建立(a,b,d,c)的索引则都可以使用到，a、b、d的顺序可以任意调整。

2. =和in可以乱序，比如 a=1 and b=2 and c=3 建立(a,b,c)索引可以任意顺序，MySQL的查询优化器会帮你优化成索引可以识别的形式。

例如：

```

select * from user where a > 1;

SELECT * FROM user WHERE a='2222' OR b='13281899972'//这个就不会走联合索引，因为只是查询b时不能根究索引查询，所以还是需要全表扫描，所以干脆a也不用索引了。

如果a，b都有索引，那么会单独根据a，b来查询，然后将结果集合并

关于or查询的真相是：

所谓的索引失效指的是：假如or连接的俩个查询条件字段中有一个没有索引的话,引擎会放弃索引而产生全表扫描。

```

这样的语句可以走联合索引，但是不会走联合索引，因为符号这个范围的数据很多，查出之后，只能获得这些数据的主键，还需要根据主键去聚集索引中查，效率比较低，还不如直接全部扫描，所以直接去聚集索引下顺序得对全表进行扫描。

```

SELECT * FROM user WHERE age=1 and height = 2

SELECT * FROM user WHERE age=1 and weight=7

SELECT * FROM user WHERE weight=7 and age=1

```

这些是可以走联合索引的，

```

SELECT * FROM user WEHRE age=1 and height>2 and weight=7

```

这个也会走联合索引，查出age=1的索引，然后查出height>2的所有数据，因为height是一个范围查找，所以到weight就不会用索引，会将将这些数据载入内存，根据weight进行筛选。

索引没有被采用？

1、根据搜索条件，找出所有可能使用的索引

2、计算全表扫描的代价

3、计算使用不同索引执行查询的代价

4、对比各种执行方案的代价，找出成本最低的那一个 。

就是假如有一个联合索引(a,b,c)，如果我们只是需要a,b,c这几个字段的数据，查询时就不需要根据主键id去聚集索引里面回表查询了

```

SELECT a,b,c FROM user where a = 1

```

这个就是覆盖索引。

https://mp.weixin.qq.com/s？src=11&timestamp=1586078743&ver=2260&signature=s1B5IvEAHXmba8XNgw2q4VXKTN8CpXfX1gjQA*Rcm1M4VpRlWzXAbExHcyLoI2hqJQWNSFZCJ3xdeZsRaph1ZpIAlYt-uTvlgX93gGXhKNiMTm1LCyGosANoDYRHe49N&new=1

哪些情况需要建索引：

1. 主键，唯一索引

2. 经常用作查询条件的字段需要创建索引

3. 经常需要排序、分组和统计的字段需要建立索引

4. 查询中与其他表关联的字段，外键关系建立索引

1. 表的记录太少，百万级以下的数据不需要创建索引

2. 经常增删改的表不需要创建索引

3. 数据重复且分布平均的字段不需要创建索引，如 true,false 之类。

4. 频发更新的字段不适合创建索引

5. where条件里用不到的字段不需要创建索引

一个表中可以有多个唯一性索引，但只能有一个主键。

1.在查询时，如果是使用的是主键，或者是唯一性索引查询，查到后就返回了，普通索引还会继续向后遍历，直到第一个不满足条件的才会返回，普通索引会多检索几次。几乎没有什么影响。

2.更新时普通索引需要判断唯一性。

3.主键是聚集索引，普通所以是非聚集索引。

###### InnoDB

是 MySQL 默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其它存储引擎。

实现了四个标准的隔离级别，默认级别是可重复读（REPEATABLE READ）。在可重复读隔离级别下，通过多版本并发控制（MVCC）+ Next-Key Locking 防止幻影读。

主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。

内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。

支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。

##### MyISAM

不支持事务，不支持行级锁，只能全表加锁，读取时会对所有表加共享锁，写入时会对表加排他锁。

一般认为单表数据量在1000万时，查询效率是最合适的，如果超过1000万，执行性能就会下降，可以考虑分库分表

##### 垂直切分

就是根据列进行分表，例如根据业务，来对表进行拆分成不同的表，或者根据使用频率，将常用的列分在一个表里面，将不常用的列分在一个表里面。

##### 水平切分

将表根据行来进行分表，将一个表拆分成多个表结构相同的表。

第一种 一般是用主键ID对数据表的数量进行取模运算，得到的余数就是新表的位置。（如果是字段是字符串，那么就根据字符串计算出哈希值，然后除以表的数量，得到新库的位置）

第二种 根据时间来拆分，主表只存储最近两个月的数据，副表存储之前的数据。这种主要是适合哪种访问的数据跟时间相关性比较大的情况，例如统计，我有看搜狐网他们的技术文档，文章PV，UV统计表，就是每天大概有200万的文章有PV，UV，也就是数据库每天会新增200万行的数据，一般来说查文章近期的UV，PV会多一些，查昨日PV，一周PV，或者一个月的PV会多一些。所以是安装时间来划分热库和冷库，一月一个表，或者一天一表。

问题

事务问题，如果在事务里面操作一个表，然后再操作另外一个表，效率会比较低，然后也比较麻烦。

在拆分之前，系统中很多列表和详情页所需的数据是可以通过sql join来完成的。而拆分后，数据库可能是分布式在不同实例和不同的主机上，join将变得非常麻烦。而且基于架构规范，性能，安全性等方面考虑，一般是禁止跨库join的。那该怎么办呢？首先要考虑下垂直分库的设计问题，如果可以调整，那就优先调整。（就是尽量不要去分库）如果无法调整的情况，下面有几种解决方案：

1. 全局表

所谓全局表，就是有可能系统中所有模块都可能会依赖到的一些表。比较类似我们理解的“数据字典”。为了避免跨库join查询，我们可以将这类表在其他每个数据库中均保存一份。同时，这类数据通常也很少发生修改（甚至几乎不会），所以也不用太担心“一致性”问题。

##### 分库分表具体实施

2.迁移历史数据，新的更新发kafka消息

就是对updatet_time小于某个时间点的数据全部拷贝出来，迁移到新的数据库，同时项目在执行增删改相关的SQL时，同时往Kafka中发一份，迁移结束后，用订阅程序消费kafka消息，更新新库，消费完之后，然后校验一致性切数据库。

缺点就算侵入性太强了，所有更新数据库的业务项目都需要改动，然后给kafka发消息。

3.迁移历史数据，订阅bin log接受更新

还是先迁移数据，迁移完之后用订阅程序消费bin log消息，更新新库，消费完之后，然后校验一致性，切数据库。

这里大概介绍一下吧，这篇的篇幅太长了，大家心里有底就行。

(1)先验数量是否一致，因为验数量比较快。

至于验具体的字段，有两种方法:

(2.1)有一种方法是，只验关键性的几个字段是否一致。

随机取小批量数据，编码后比较。

(2.2)还有一种是 ，一次取50条(不一定50条，具体自己定，我只是举例),然后像拼字符串一样，拼在一起。用md5进行加密，得到一串数值。新库一样如法炮制，也得到一串数值，比较两串数值是否一致。如果一致，继续比较下50条数据。如果发现不一致，用二分法确定不一致的数据在0-25条，还是26条-50条。以此类推，找出不一致的数据，进行记录即可。

##### 全局视野法

就是例如要查询第三页的数据，那么把每个库里面前三页的数据都查询出来，然后排序，

优点是比较准确，也比较简单

问题就是每个库返回的页数很多，网络传输量很大，而且收到数据后的需要二次排序，排序的性能消耗也比较大。

```

要查order by time offset offset_value limit num时

对每个库查 order by time offset 0 limit offset_value + num然后进行排序

```

##### 最大时间法

就是禁止跳页查询，就是如果业务不需要跳页查询的话，一开始查第一页的时候，从每个库取一页数据回来进行排序后返回。

查第二页数据时根据第一页结果中最大的时间去每个库再取一页数据，然后进行排序得到第二页数据。

优点是是每次直插一页，传输数据量小，排序的数据量也小，

缺点是由于查第二页及以后的页数都需要知道上一页最大时间。

```

要查order by time offset offset_value limit num时

对每个库查 where time >lastTime order by time offset 0 limit num然后进行排序

```

##### 每库平均取法

假设数据分布足够平均，每个库的数据分布都是平均的，假设有四个库，查第一页数据时，每个库都取前0.25页数据，然后合并后返回，取第二页数据时，每个库再都取0.25页。但是这样取得是不准的数据

```

将order by time offset X limit Y，改写成order by time offset X/N limit Y/N

```

##### 二次查询法

假如要取offset X后，Y个数据，

1.假设有四个库，需要去每个库查offset X/4 后Y个的数据，然后得到四个结果集中time最小值time_min，

2.然后去每个库去查between time_min到之前每个库的time_max的结果集，

3.因为之前每个库的time_max的offset都是X/4+Y，所以可以反推出time_min在每个库里的offset，然后得到全局offset，

4.然后根据全局offset可以计算我们想到的offset X后Y个数据，就是以time_min为起点到距离我们offset的差值。

关键在于第二步，如果数据库删除过数据，time_min到time_max之间删除过数据，这样就不准，

```

（1）将order by time offset X limit Y，改写成order by time offset X/N limit Y

（2）找到最小值time_min

（3）between二次查询，order by time between time_min and time_i_max

（4）设置虚拟time_min，找到time_min在各个分库的offset，从而得到time_min在全局的offset

（5）得到了time_min在全局的offset，自然得到了全局的offset X limit Y

```

首先需要查询两次，而且如果表数据是按照分段来查询的，会有问题。

就是将主节点的数据复制到从节点。

用途：

1.可以读写分离，主库用来写数据，从库用来读数据。在更新数据时，会对整个表加锁，如果是读写分离的，可以去从表读取数据，这样就不会有问题。

2.做备份，主库出故障后，进行故障转移，让从库代替主库，提供服务。

##### 实现原理

当主节点和从节点建立连接后，主服务器上会起一个bin log dump线程，用于给从节点发送bin log日志(日志所包含的信息之外，还包括本次返回的信息的bin-log file 的以及bin-log position)，在读取bin log日志时，会对日志文件加锁，读取完成后会解锁。

从节点I/O线程

从节点与主节点建立连接后会起一个I/O线程来接受主节点发送过来的bin log日志内容，并且保存在从节点的relay log文件中。

从节点同时会起一个SQL线程，来读取 relay log 中的内容，解析成SQL，并且在从节点上执行，保证和主节点的数据一致性。



##### 异步模式（默认的模式）

主节点不会主动push bin log给从节点，也不会管从节点的同步情况，默认就是这种模式。

##### 半同步模式（MySQL 5.5之后提供）

主节点给从节点发送bin log 之后，会一直等待回应，只要一个从节点接受bin log，并且写入relay log 成功，给主节点返回接受成功的ACK信息，主节点就认为成功，提交事务。

##### 全同步模式

就是需要所有的从节点接受日志，并且写入relay log 成功，给主节点返回接受成功的ACK信息，主节点才认为成功，提交事务。

bin log格式

Statement-base Replication (SBR)语句模式

就是执行什么更新的SQL，就将这些SQL保存到 bin log日志文件中。

缺点在于可能某些情况下导致数据不一致，例如根据now()当前服务器的时间，可能从节点执行语句时的时间跟主节点的语句时间不一样。

Row-based Relication(RBR) 数据行模式

就是更新那些数据行，将这些更新的数据行生成SQL，保存在bin log日志文件中。

优点是能确保数据的精准，缺点是会产生大量的日志，日志内容会变大，尤其是一行SQL对大量数据行更新时，而且也不能通过bin log解析当时执行的SQL语句。

Mixed-format Replication(MBR)，MySQL NDB cluster 7.3 和7.4 使用的MBR。是以上两种模式的混合，对于一般的复制使用STATEMENT模式保存到binlog，对于STATEMENT模式无法复制的操作则使用ROW模式来保存，MySQL会根据执行的SQL语句选择日志保存方式


#### 死锁

除了单条更新语句外，事务获取行锁都是逐步获取的，所以有可能会造成死锁。

解决死锁有两种策略：

innodb_lock_wait_timeout,默认是50s,超时会报错

innodb会有死锁检测，但是会消耗一些cpu资源，检测到死锁会让占有锁最少的事务会滚，释放锁。

就是控制访问相同资源的并发事务量。例如将长事务拆分成短事务，这样每次事务占用时间也少，也可以减少其他事务的等待时间。

在innodb引擎下，

##### COUNT(*)和Count(id)

SELECT Count(\*)其实是跟SELECT Count(id)是等价的，会去主键的聚集索引下扫描每一行，然后判断行是否为Null，不为Null计入Count。

##### Count(col)

也是全表扫描，判断这一行的col值是否为null，不为null，计入Count

怎么优化count(\*)？

可以使用查询一个非空的唯一索引键的数量来替代count(\*),因为count(\*)需要遍历主键的聚集索引的叶子节点，读取每一行的数据，而Count(unique_key)会去unique_key的索引下读取每个叶子的节点，因为每个叶子节点只包含unique_key和主键id，数据大小比聚集索引下的叶子节点下，IO会小一些。

##### Myisam可以缓存count，而innodb不能缓存count

因为innodb有事务的概念，如果是在PR的隔离级别下，每个事务查询的count应该等于事务开始时count+本事务执行过程中对count的改变，但是由于每个事务可以单独设置会话隔离级别，所以很难实现对count的缓存。

##### 怎么优化慢查询？

1.首先根据explain+SELECT语句执行，查看结果，

```

EXPLAIN SELECT * FROM res_user ORDER BYmodifiedtime LIMIT 0,1000

```

```

得到如下结果：

table | type | possible_keys | key |key_len | ref | rows | Extra EXPLAIN列的解释：

- table 显示这一行的数据是关于哪张表的

- type 这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL

- rows 显示需要扫描行数

- key 使用的索引

const就是针对主键或者唯一性索引的等值查询，通过索引查找一次就行了。仅仅是查一条数据。

eq_ref 唯一性索引键扫描，对于每个索引键，表中只有一条数据与其对应，例如join查询时，对于前表查出的结果集，每个结果在第二个表只有一条数据对应。

ref 非唯一性索引查询

ref : 此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了 `最左前缀` 规则索引的查询. 关键字:非唯一性索引

ref_or_null：与ref方法类似，只是增加了null值的比较。

`range`: 表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中.

`index`: 表示全索引扫描(full index scan), 关键字:查询字段和条件都是索引本身

index_merge：表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引.效率不是很高 关键字:索引合并

unique_subquery：用于where中的in形式子查询，子查询返回不重复值唯一值

index_subquery：用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。

fulltext：全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引

all：这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。

```