MySQL基础总结

1. mysql是如何和数据库打交道的

1) 程序员对mysql认知的现状

普通程序员对mysql的掌握：建库建表建索引，增删改查

2) 工作中会遇到的问题

死锁、sql性能太差、异常报错

3) 为啥要mysql驱动

我们不可能自己去写代码和mysql服务器建立tcp、socket连接，因此mysql给各个语言都提供了mysql驱动，封装了最底层的网络通信，提供Connection连接对象。基于这个Connection连接就可以和mysql服务器通信了，比如增删改查。

4) 为啥要数据库连接池

tomcat容器是多线程的，他们去抢夺一个connection去访问数据库的话，性能肯定低。
每个线程都去创建使用和销毁connection的话，建立网络连接是耗时的。

那么池子的好处是：
一批建立好连接的connection扔到池子里面去，用的时候去池子里面拿，不用的时候还到池子里面，也不去销毁，后续可以继续使用。
解决了并发建立connection和connection销毁的问题。

5) server端为什么要连接池

很多客户端系统需要和mysql服务端建立连接，因此服务端要需要一个连接池，来维持和客户端的连接
还需要进行账号密码的认证

2. mysql架构设计

1) 服务端怎么处理connection发来的sql语句？

服务端肯定需要一个线程来监听connection，有数据来了，就从connection读取数据并解析数据，生成sql、参数之类。

2) sql语句交给谁来处理?

sql语句从connection读取出来了之后，就会交给SQL接口来处理，可以理解为一个门面。

3) 为什么需要查询解析器

一条sql语句，是人使用的语法，mysql没办法理解的，mysql需要转化成自己能理解的语法。
那么mysql就提供了一个查询解析器去解析sql语句，对sql进行拆解

比如：select id,name,age from user where id=1
mysql就拆解成
从user表查询数据
查询id=1那一行的数据
查出来的数据，提取出id、name、age三个字段

sql解析，就是对符合sql语言的sql语句进行分析和拆解

4) 为什么需要查询优化器

sql的执行，可能会有多种路径，比如遍历表，一条一条对比，比如直接根据id定位到那一条数据。查询优化器，要优化出一条最优的查询路径，提高查询的效率。

他会生成一个查询路径树，然后从里面选择一条最优查询路径，你就按照这个查询的步骤和顺序来执行操作就好了。

5) 为什么需要执行器

查询优化器给出来一条sql执行计划，就需要有人来执行这个执行计划。

执行器接下来就会根据这个执行计划，去多次调度存储引擎的接口

执行器是非常核心的一个组件，负责跟存储引擎配合完成一个SQL语句在磁盘与内存层面的全部数据更新操作。

6) 为什么需要存储引擎接口

存储引擎是真正的存储和处理数据的地方，数据主要是存储在内存和磁盘。
存储引擎接口，是一个Facade，对执行器提供简单的调用方法，屏蔽掉了内部复杂的处理逻辑。

7) 为什么要有不同的存储引擎

满足不同的场景需求，比如性能、事务、存储限制、索引的支持等等

三种模型：
完全基于内存存储的，要求速度快，性能高，但是存储的容量小，数据会丢失；
完全基于磁盘的，存储容量大，数据不丢失，但是速度慢，性能低；
基于内存+磁盘，兼顾上面两种的优缺点。

3. InnoDB存储引擎的架构设计

1) 为什么需要缓冲池buffer pool

直接去增删改查磁盘，性能上肯定会比在内存里操作慢的
因此弄了一个内存里面的缓冲池，把一些操作扔到内存里面去操作来提高性能
如果查询和更新都在内存里面完成就能提高性能

2) 怎么使用缓冲池

比如更新一条数据的时候，先把数据加载到缓冲池，然后对这条数据加独占锁，然后操作更新，在内存中修改这条数据。

3) 事务没提交，更新的数据要回滚怎么办

在内存修改了数据，但是后面需要回滚怎么办？
在更新数据之前，把这条记录需要改修的字段的信息记录到磁盘上的undo日志里面，后面回滚的时候在提取出来还原。

4) 事务没提交，为什么会出现脏数据？

事务没有提交，在缓冲池修改了数据，此时内存数据和磁盘上存储的数据不一样，这条数据就成了脏数据。

5) 事务没有提交，系统宕机了怎么办？

事务还没有提交，磁盘上的数据还没有修改，系统宕机了，客户端会收到一个数据库异常，此时对系统没有任何影响，等mysql恢复了，磁盘上的数据还是原来的数据。
此时恢复了对事务进行回滚

6) 事务提交过程中，系统宕机了怎么办？

redo log prepare阶段：

已经flush redo log了，没有flush binlog，此时，crash，系统恢复后，事务回滚
已经flush redo log了，也flush binlog了，此时，crash，系统恢复后，会继续提交

7) 事务提交之后，系统宕机了怎么办？

redo log commit了，commit字段更新了，此时crash，可能缓冲池里面的数据还没有刷盘，缓冲池里面的数据就没了。为了能够还原缓冲池的状态就引入了redo日志。

redo log，是一种偏向物理性质的重做日志，因为他里面记录的是类似这样的东西，
“对哪个数据页中的什么记录，做了个什么修改”。

8) redo日志如何保证数据能还原？

有三种模式

A. redolog实时刷盘强制更新到磁盘

事务里面更新数据的同时，把这条数据更新后的字段，记录到redo日志内存缓冲池里面。

事务提交的时候，策略innodb_flush_log_at_trx_commit=1，redo必须刷盘并强制更新到磁盘，然后事务才能成功。

系统再次恢复的时候，就能根据redo日志来重做缓冲区脏数据
但是，在这种策略下，事务的写入速度就会大大下降。

B. 其他2个策略：

如果innodb_flush_log_at_trx_commit=0，就代表mysql定时器每秒去刷盘并强制更新到磁盘

如果innodb_flush_log_at_trx_commit=2，就代表事务提交的时候先刷盘，由os自己去更新到磁盘。

0和2的模式下，很可能宕机会丢失redo日志数据。

C. flush和fsync理解如下：

innodb_flush_log_at_trx_commit = 0 就是定时器自己维护，每秒调用 flush + fsync。

innodb_flush_log_at_trx_commit = 1 就是实时调用 flush + fsync 没法批处理，性能很低。

innodb_flush_log_at_trx_commit = 2 就是实时flush ,定时 fsync 交给OS维护定时器。

如果要保证缓冲池的数据0丢失，则值为1；
如果高效的写入操作，不考虑数据的一致性，则值为0；
如果即想高速写入又保证数据丢失少，则值为2；

9) 什么是binlog？

binlog是属于mysql server自己的日志文件，叫做归档日志，不是InnoDB存储引擎特有的日志文件。

是一种逻辑性的日志，比如一条update 语句 wherer id=1

而redo日志记录的是对哪个数据页中的什么记录，做了个什么修改

10) binlog什么时候写入磁盘？

提交事务阶段：redo log flush，mysql执行器会将binlog flush到磁盘。

11) binlog刷盘策略

sync_binlog参数可以控制binlog的刷盘策略，他的默认值是0
把binlog写入磁盘的时候，其实不是直接进入磁盘文件，而是进入os cache内存缓存。
也就是实时flush ,定时 fsync 交给OS维护定时器，因此会丢数据

sync_binlog=1,就是flush+fsync，必须实时刷盘强制更新磁盘。

12) 如何才算事务提交成功？

redolog flush + binlog flush 之后，还需要把写入binlog的文件名和写入位置，以及commit标记更新到redolog。才算事务提交成功。

13) 为什么事务提交成功要在redo日志写commit标记？

保证redolog和binlog的一致性，只有redolog和binlog都写入成功了，才能判定事务提交成功。

否则只有redolog成功，binlog没有成功，或者binlog日志位置信息没有写入redolog，都判定事务不成功。

事务判定成功的三个步骤：redolog刷盘+binlog刷盘+commit标记写入redo日志

14) io线程刷盘内存缓冲区的脏数据

事务已经提交成功了，但是内存缓冲区里面的数据可能还没有刷新到磁盘。有一个io线程会在某个时间，把脏数据刷新到磁盘。

如果系统宕机了，系统恢复之后，可以根据redo日志重做缓冲区的脏数据。之后io线程又可以继续刷盘了。

15) 基于更新一条数据，总结下innodb架构原理

InnoDB存储引擎内存里面有 buffer pool、redo log buffer
磁盘上有undo日志文件，redo日志文件

同时mysql server自己还有binlog日志磁盘文件。

事务commit前：flush fsync undo log、write buffer pool、write redo log buffer

事务commit中：flush fsync redo log、flush fsync binlog，update commit

事务commit后：io flush fsync buffer pool脏数据

4. 数据库规划

1) 数据库的机器配置

一台机器能抗下每秒多少请求，往往是跟你每个请求处理耗费多长时间是关联的

根据经验值而言，Java应用系统部署的时候常选用的机器配置大致是2核4G和4核8G的较多一些

4核8g，按500个请求来配置，根据任务时长，从一两百到七八百都有可能

java应用的内存运算性能是很高的，压力都在和外部通信上。

数据库部署的时候常选用的机器配置最低在8核16G以上，正常在16核32G，推荐16核以及以上的配置。

8核16G，按一两千的请求来配置，再高可能有危险，因为数据库的cpu、磁盘、io、内存负载都会很高，数据库压力过大就会宕机。

16核32G，按两三千的请求来配置，甚至三四千也是有可能，再高的话，cpu、磁盘、io、内存、网络负载都会很高，数据库也有可能会扛不住宕机。

如果是ssd磁盘，能抗的并发可能会更高一点，数据库最大的复杂就在于磁盘的io，需要大量的读写磁盘文件。

2) 数据库的压测

数据库的压测和java系统的压测是两码事，首先得知道数据库能最大抗多大压力，才能去看java能抗多大压力。

有一种可能，比如你数据库可以抗下2000个请求，java才能抗下500个请求，所以不能只针对java系统，还必须对数据库进行压测，心里有个数。

3) QPS、TPS区分

QPS：每秒可以处理的请求数
TPS：每秒可以处理的事务数

TPS在事务中用的会比较多一些，他是说数据库每秒能处理多少次事务的commit和rollback。

QPS可以理解为每秒的请求数量，TPS可以理解为每秒完成的业务量，比如交易这个整体的业务行为。

4) IO压测指标

A. IOPS

机器随机IO并发处理的能力，比如说200IOPS，意思是每秒可以执行200个随机io读写请求。

这个是关键指标，因为后台IO线程去刷盘缓冲区的脏数据，是在不确定的时间里面刷回去的，是一个随机IO的过程。

如果说IOPS指标太低，说明刷回效率就比较低。

B. 吞吐量

机器每秒可以读写多少字节的数据量

这个也是关键指标,提交事务的时候，会大量的写redo log之类的日志，这些日志都要写磁盘。

一台机器它的存储可以每秒读写多少字节的数据量，就决定了可以把多少redo log之类的日志写到磁盘。一般来说我们写redolog、binlog都是顺序写，那么一般磁盘的顺序写入，每秒的吞吐量可以达到200MB左右

通常机器的磁盘吞吐量都是足够承载高并发请求的

C. latency延迟

往磁盘写入一条数据的延迟。

这个也是关键指标，执行sql的时候，写redo日志到磁盘，到底延迟到1ms还是多少，会影响到sql语句的执行性能。

磁盘写入延迟越低，数据库性能越好，sql的性能就越高。

5) CPU负载

6) 网络负载

看每秒网卡的输入输出多少MB的数据，一般1000MB的网卡，最大传输100MB的数据。

7) 内存负载

5. 数据库压测

1) 安装sysbench

curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
sudo yum -y install sysbench
sysbench —version

2) 构造测试表测试数据

sysbench —db-driver=mysql —time=300 —threads=10 —report-interval=1 —mysql-host=127.0.0.1 —mysql-port=3306 —mysql-user=test_user —mysql-password=test_user —mysql-db=test_db —tables=20 —table_size=1000000 oltp_read_write —db-ps-mode=disable prepare

—db-driver=mysql：基于mysql的驱动去连接mysql数据库，你要是oracle，或者sqlserver，那自然就是其他的数据库的驱动了

—time=300：这个就是说连续访问300秒

—threads=10：这个就是说用10个线程模拟并发访问

—report-interval=1：这个就是说每隔1秒输出一下压测情况

—mysql-host=127.0.0.1 —mysql-port=3306 —mysql-user=test_user —mysql-password=test_user：这一大串，就是说连接到哪台机器的哪个端口上的MySQL库，他的用户名和密码是什么

—mysql-db=test_db —tables=20 —table_size=1000000：这一串的意思，就是说在test_db这个库里，构造20个测试表，每个测试表里构造100万条测试数据，测试表的名字会是类似于sbtest1，sbtest2这个样子的

oltp_read_write：这个就是说，执行oltp数据库的读写测试

—db-ps-mode=disable：这个就是禁止ps模式

最后有一个prepare，意思是参照这个命令的设置去构造出来我们需要的数据库里的数据，他会自动创建20个测试表，每个表里创建100万条测试数据

3) 压测结果分析

按照我们上面的命令，我们是让他每隔1秒都会输出一次压测报告的，此时他每隔一秒会输出类似下面的一段东西：

[ 22s ] thds: 10 tps: 380.99 qps: 7312.66 (r/w/o: 5132.99/1155.86/1321.35) lat (ms, 95%): 21.33 err/s: 0.00 reconn/s: 0.00

第22s输出的一段压测统计报告，然后是其他的一些统计字段：

thds: 10，这个意思就是有10个线程在压测

tps: 380.99，这个意思就是每秒执行了380.99个事务

qps: 7610.20，这个意思就是每秒可以执行7610.20个请求

(r/w/o: 5132.99/1155.86/1321.35)，这个意思就是说，在每秒7610.20个请求中，有5132.99个请求是读请求，1155.86个请求是写请求，1321.35个请求是其他的请求，就是对QPS进行了拆解

lat (ms, 95%): 21.33，这个意思就是说，95%的请求的延迟都在21.33毫秒以下

err/s: 0.00 reconn/s: 0.00，这两个的意思就是说，每秒有0个请求是失败的，发生了0次网络重连

4) 总的压测报告

SQL statistics:

queries performed:

read: 1480084 // 这就是说在300s的压测期间执行了148万多次的读请求

write: 298457 // 这是说在压测期间执行了29万多次的写请求

other: 325436 // 这是说在压测期间执行了30万多次的其他请求

total: 2103977 // 这是说一共执行了210万多次的请求

// 这是说一共执行了10万多个事务，每秒执行350多个事务

transactions: 105180( 350.6 per sec. )

// 这是说一共执行了210万多次的请求，每秒执行7000+请求

queries: 2103977 ( 7013.26 per sec. )

ignored errors: 0 (0.00 per sec.)

reconnects: 0 (0.00 per sec.)

// 下面就是说，一共执行了300s的压测，执行了10万+的事务

General staticstics:

total time: 300.0052s

total number of events: 105180

Latency (ms):

min: 4.32 // 请求中延迟最小的是4.32ms

avg: 13.42 // 所有请求平均延迟是13.42ms

max: 45.56 // 延迟最大的请求是45.56ms

95th percentile: 21.33 // 95%的请求延迟都在21.33ms以内

5) 总结

在硬件的一定合理的负载范围内，把数据库的QPS提高到最大，这就是数据库压测的时候最合理的一个极限QPS值

6. 机器的性能

压测的时候，需要机器的负载在比较合理的范围，28原则，不能超过80%。

1) CPU负载

按下 1，即可显示使用 CPU 核心数

top - 15:52:00 up 42:35, 1 user, load average: 0.15, 0.05, 0.01
时间 运行时长 用户数量 负载 1分钟 5分钟 15分钟

CPU负载是0.15，这就说明，4核CPU中连一个核都没用满，4核CPU基本都很空闲，没啥人在用。

如果你的CPU负载是1，那说明4核CPU中有一个核已经被使用的比较繁忙了，另外3个核还是比较空闲一些。

如果你的CPU负载是1.5，说明有一个核被使用繁忙，另外一个核也在使用，但是没那么繁忙，还有2个核可能还是空闲的。

2) 内存负载

Mem: 33554432k total, 20971520k used, 12268339 free, 307200k buffers

去掉末尾6个数字就是多少GB
去掉末尾3个数字就是多少MB

总内存大概有32GB，已经使用了20GB左右的内存，还有10多G的内存是空闲的，有大概300MB左右的内存用作OS内核的缓冲区了。

3) 磁盘IO负载

A. 随机读写IOPS

一般来说，随机磁盘读写每秒在两三百次都是可以承受的。

dstat -r

—io/total-
read writ
0.25 31.9
0 253
0 39.0

B. 吞吐量

dstat -d

-dsk/total-
read writ
639k 62k
32k 761k
0 0
16k 16k

每秒读取963kb，写入62kb的含义
像这个存储IO吞吐量基本上都不算多的，因为普通的机械硬盘都可以做到每秒钟上百MB的读写数据量。

4) 网卡负载

dstat -n

-net/total-
recv send
16k 17k

每秒钟网卡接收到流量有多少kb，每秒钟通过网卡发送出去的流量有多少kb

通常来说，如果你的机器使用的是千兆网卡，那么每秒钟网卡的总流量也就在100MB左右，甚至更低一些。

7. Buffer Pool

1) 为什么要有buffer pool

内存和磁盘结合，提高数据库的性能

2) 为什么需要配置buffer pool容量？

因为内存大小不是无限的，所以需要对buffer pool进行容量的配置

my.cnf配置文件里面可以配置大小，如下是配置了20GB，默认值是128MB
[server]
innodb_buffer_pool_size = 2147483648

3) 数据在磁盘是如何存放的？

逻辑上，我们有库、表、行的概念，在实际物理存储的时候，每个表里面的行的数据都是按照一页一页来存储的，一页的大小是16kb。

4) 数据如何存放在buffer pool？

有了buffer pool这样一个内存的容器，磁盘的数据必定需要按照一定的约定和格式存放进去。

mysql的数据在磁盘上是16kb的大小存放在一页上，定位一行数据，需要先找到这一页，那么对应的读取一页的数据，也是按照一页的形式存放在buffer pool里面的，物理和内存结构映射，就是一个一个的缓存页和物理页对应。

5) 数据放到buffer pool怎么和物理数据对应？

这就有了一个描述数据，里面存放这页数据，在buffer pool的地址，以及对应的物理表空间，页编号等信息。

6) 描述数据放哪里？怎么放，大小是多少？

放到bufferpool的最前面，大小是一页的5%，800个字节。

多出来的一个一个描述数据的大小，会导致buffer pool的总容量撑出来变大。

比如128MB的buffer pool可能最终大小就是130MB

7) bufferpool的初始化

mysql启动的时候，会安装buffer pool设置的大小，加上描述数据的大小，去申请一块内存作为bufferpool的内存区域。

接下来，会把一堆800字节的描述数据和16kb的缓存页在内存里面初始化，并且一字按顺序排好。

8) bufferpool会有内存碎片吗？

会有，因为buffer pool的大小是人设定的，很可能划分完所有的缓存页和描述数据块之后，还剩一点点的内存，这一点点内存放不下任何一个缓存页了，就没办法使用了。就变成了内存碎片。

同时，再划分缓存页和描述数据块的时候，会让它们紧密的挨在一起，这样能尽可能的减少内存浪费，就尽可能的减少内存碎片的产生了。

如果缓存页东一块西一块，必然有很多间隙，就形成了内存碎片。

9. Free链表

1) 读取一页的数据选择哪个缓存页存放？

这个就涉及到了一个问题，哪个缓存页是空闲的。为了解决这个问题，就设计了一个free链表，所有空闲的缓存页对应的描述数据，做成一个双向链表串在一起。

当数据库启动的时候，缓存页都是没有使用的，因此，所有的描述数据，都串在了一起，组成了一个free链表。

2) 如何找到free链表

要去使用free链表，就要有一块数据来描述它，就弄了一个基础节点，存储了free链表的数量、free链表的头节点，尾节点等信息。

对于free链表来说，这个基础node节点40字节，是不属于bufferpool的。相当于free链表的facade外观。

3) 数据从物理页到内存页放入的全部过程？

首先读取了一页数据之后，先去free链表的外观基础节点里面去找到一个空闲的描述数据。
然后把这页数据，写入描述数据对应的内存页地址，把数据灌装进去。
然后把数据的物理表空间、页编号，更新到描述数据，完成映射。
最后把这个描述数据从free链表中摘除。

4) 如何知道一页数据有没有缓存?

既然bufferpool的设计是为了提高性能，尽量在内存里面操作数据，那操作一条记录，得先去检查在bufferpool里面是否存在。

那如何快速的知道一条记录是否命中缓存呢？

mysql又设计了一个哈希表，写入kv对，key=表空间号+页编号，value=缓存页地址。
每次取数据，先从哈希表看一下，有没有对应的kv，有就直接取出来使用。

10. Flush链表

1) 为什么会有脏页、脏数据的产生

对数据修改都是基于bufferpool的缓存页来操作的，那么修改的时候，必然会存在脏数据，和数据库的数据不一致，也就是产生了脏页。

2) 怎么统计哪些缓存页产生了脏页？

修过缓存页的数据被修改了，那么它就是一个脏页，io线程去刷盘的时候，不可能所有缓存页都刷盘，而是只刷新脏页。

如果一个缓存页成为了脏页，它会把它的描述数据块加入到flush链表里面，flush链表也是一个双向链表，所有的脏页通过flush链表串起来。这样所有的脏页就能被统计到了。

3) 从哪个源头去处理脏页

flush链表和free链表一样，也有一个不属于bufferpool的外置node节点来提供facade外观，里面提供有多少size的脏页，第一个脏页和最后一个脏页。

11. LRU链表

1) free缓存页不够使用了怎么办？

缓存页不够使用了，接下来必须要淘汰掉一个缓存页，需要刷盘并清空，成为一个新的缓存页

2) 淘汰哪个缓存页呢？

原则上肯定是最少使用的先淘汰掉，因此引入LRU链表，最近最少使用算法。
有了LRU链表就知道哪些是不常用的缓存页了，需要淘汰缓存页的时候，从链表按顺序淘汰就可以了。

3) LRU工作机制

加载新缓存页和修改一个缓存页，都给它调到LRU的链表头部，让不经常修改的往后排。

4) 从哪个源头去操作LRU链表呢

同样的道理，一个外置Node节点，持有LRU链表的size，第一个LRU链表和最后一个LRU链表。

5) 单纯的LRU链表带来的问题

可能是不用的页被提前加载进来了，导致经常使用的缓存页排到最后面去了

1. 一种是预读机制，触发预读机制就会加载相邻的页数据，主要是为了提升性能。

比如：
a. 顺序的访问了一个区里的多个数据页，访问的数据页的数量超过了这个阈值，此时就会触发预读机制，把下一个相邻区中的所有数据页都加载到缓存里去
innodb_read_ahead_threshold，他的默认值是56

b. 一个区里的13个连续的数据页，而且这些数据页都是比较频繁会被访问的，此时就会直接触发预读机制，把这个区里的其他的数据页都加载到缓存里去

1. 另外一种是全表扫描，把整个表的页数据，全部加载进来了
   这个所谓的全表扫描，意思就是类似如下的SQL语句：SELECT * FROM USERS，没有where条件

   6) 如何解决简单LRU链表淘汰掉热点数据到问题

   为了解决简单LRU链表的问题，引入优化版的LRU链表，采用的思想是冷热数据分离。链表的头部区域是热数据区域，尾部区域是冷数据区域。innodb_old_blocks_pct=37，默认冷数据占37%

第一次加载数据的时候，加载到冷区域头部。如果这个缓存页1s后，还有被使用，说明它可能经常被使用，那么就移动到热数据区域头部。

通过冷热数据分离，每次淘汰，淘汰冷数据区域就可以了。放进来1s后都没有的数据，就会变成冷数据。

7) 热数据区域的链表频繁节点移动怎么优化?

热数据区域的数据本来就是热数据，访问一次就提到头部，肯定效率不是最高的。
因此如果是热数据区域前1/4数据的访问，不再移动，只有后3/4的数据被访问了，才往前提。

8) 冷数据什么时候刷盘淘汰

定时io线程，刷盘冷数据区域链表尾部的几个数据，从lru链表和flush链表移除，还到free链表中。

几个链表都没有数据了，就从冷数据末端去flush一个缓存页，从lur链表和flush链表移除，还到free链表中。

9) 热点数据啥时候刷盘淘汰

冷数据知道如何淘汰了，热数据怎么处理呢，热数据必然是在flush链表里面的，是脏数据，flush链表有一个后台任务，在合适的时候，去刷盘脏页数据。从lur链表和flush链表移除，还到free链表中。

10) 如何避免crud的时候，缓存页用完了，频繁倒腾冷数据刷盘

给buffer pool的内存设置大一点，即使高峰期free消耗的速率比flush、lru刷盘的数据快，但是还是有很多内存可以使用，等高峰期过了，free又慢慢被还原了。

12. 三个链表动态的运行过程

Buffer Pool在运行中被使用的时候，实际上会频繁的从磁盘上加载数据页到他的缓存页里去，然后free链表、flush链表、lru链表都会在使用的时候同时被使用。

比如数据加载到一个缓存页，free链表里会移除这个缓存页，然后lru链表的冷数据区域的头部会放入这个缓存页。

然后如果你要是修改了一个缓存页，那么flush链表中会记录这个脏页，lru链表中还可能会把你从冷数据区域移动到热数据区域的头部去。

如果你是查询了一个缓存页，那么此时就会把这个缓存页在lru链表中移动到热数据区域去，或者在热数据区域中也有可能会移动到头部去。

你一边不停的加载数据到缓存页里去，不停的查询和修改缓存数据，然后free链表中的缓存页不停的在减少，flush链表中的缓存页不停的在增加，lru链表中的缓存页不停的在增加和移动。

另外一边，你的后台线程不停的在把lru链表的冷数据区域的缓存页以及flush链表的缓存页，刷入磁盘中来清空缓存页，然后flush链表和lru链表中的缓存页在减少，free链表中的缓存页在增加。

13. 多Buffer Pool来优化数据库的并发性能

1) buffer pool并发访问的时候需要加锁吗

多线程访问一个BufferPool，必然是要加锁的
比如，先加锁，然后加载数据页到缓存页，更新free链表，更新lru链表，再释放锁，接着下一个线程再执行一系列的操作。

2) buffer pool并发访问加锁影响性能吗？

大部分情况下，线程都是查询或者更新缓存里的数据，操作都是发生在内存的，微秒级，包括更新free、flush、lru链表都是内存操作，速度极快。

但是有些情况下，需要读取从磁盘读取数据到缓存页，发生了一次磁盘IO，耗时就长一些，后面排队的线程自然就多等一会。

3) 多个buffer pool优化并发能力

多个buffer pool类似于分段加锁，降低了锁的冲突，多核多线程访问就成倍的提高了性能

每一个缓存页，只会放在其中一个buffer pool里面

my.cnf配置如下
[server]
innodb_buffer_pool_instances = 8
innodb_buffer_pool_size = 8589934592

innodb_buffer_pool_instances代表了有几个buffer pool
innodb_buffer_pool_size代表了buffer pool总共占用多少字节

14. Buffer Pool动态调整

1) bufferpool能在运行期间调整吗

可以调整，但不这么做，因为性能太低下了，极为耗时。
主要是因为bufferpool需要连续的内存，比如你8g调整到16g，就需要把8g的数据拷贝到16gb去。因此最好不要这么做

2) 如何更高效的给bufferpool动态调整大小

将每个bufferpool分为n个chuck，这n个chuck共享free、flush、lru链表。需要扩容的时候，生成一个连续内存的chuck，然后交给bufferpool就可以了

每个buffer pool里多个chunk但是共用一套链表数据结构

3) buffer pool容量生成推荐配置成多少

推荐配置成总内存的50%-60%，系统和其他软件也要使用内存。
32GB，配置60%就是20GB。

buffer pool总大小= chunk个数 chunk容量 buffer pool个数

比如20GB buffer pool，16个buffer pool，128MBchunk容量，10个chunk数量
比如20GB buffer pool，32个buffer pool，128MBchunk容量，5个chunk数量

15. innodb状态参数查询

SHOW ENGINE INNODB STATUS

Total memory allocated xxxx;
Dictionary memory allocated xxx
Buffer pool size xxxx
Free buffers xxx
Database pages xxx
Old database pages xxxx
Modified db pages xx
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young xxxx, not young xxx
xx youngs/s, xx non-youngs/s
Pages read xxxx, created xxx, written xxx
xx reads/s, xx creates/s, 1xx writes/s
Buffer pool hit rate xxx / 1000, young-making rate xxx / 1000 not xx / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: xxxx, unzip_LRU len: xxx
I/O sum[xxx]:cur[xx], unzip sum[16xx:cur[0]
主要讲解这里跟buffer pool相关的一些东西。

（1）Total memory allocated，这就是说buffer pool最终的总大小是多少
（2）Buffer pool size，这就是说buffer pool一共能容纳多少个缓存页
（3）Free buffers，这就是说free链表中一共有多少个空闲的缓存页是可用的
（4）Database pages和Old database pages，就是说lru链表中一共有多少个缓存页，以及冷数据区域里的缓存页数量
（5）Modified db pages，这就是flush链表中的缓存页数量
（6）Pending reads和Pending writes，等待从磁盘上加载进缓存页的数量，还有就是即将从lru链表中刷入磁盘的数量、即将从flush链表中刷入磁盘的数量
（7）Pages made young和not young，这就是说已经lru冷数据区域里访问之后转移到热数据区域的缓存页的数量，以及在lru冷数据区域里1s内被访问了没进入热数据区域的缓存页的数量
（8）youngs/s和not youngs/s，这就是说每秒从冷数据区域进入热数据区域的缓存页的数量，以及每秒在冷数据区域里被访问了但是不能进入热数据区域的缓存页的数量
（9）Pages read xxxx, created xxx, written xxx，xx reads/s, xx creates/s, 1xx writes/s，这里就是说已经读取、创建和写入了多少个缓存页，以及每秒钟读取、创建和写入的缓存页数量
（10）Buffer pool hit rate xxx / 1000，这就是说每1000次访问，有多少次是直接命中了buffer pool里的缓存的
（11）young-making rate xxx / 1000 not xx / 1000，每1000次访问，有多少次访问让缓存页从冷数据区域移动到了热数据区域，以及没移动的缓存页数量
（12）LRU len：这就是lru链表里的缓存页的数量
（13）I/O sum：最近50s读取磁盘页的总数
（14）I/O cur：现在正在读取磁盘页的数量

buffer pool的千次访问缓存命中率，这个命中率越高，说明你大量的操作都是直接基于缓存来执行的，性能越高。

第二个是你的磁盘IO的情况，这个磁盘IO越多，说明你数据库性能越差。

16. 一行数据在磁盘上的物理结构

1) 为什么不能直接更新磁盘

磁盘io随机读写，性能必然很差

读写内存速度快，磁盘顺序写很快，通过操作内存和顺序写才能极大的提升mysql的性能。

2) 为什么要有数据页？

一条一条数据加载和写入性能低

如果操作到一页数据里面的其他数据，就可以不用再次去读取磁盘了，提高了性能

一页数据读取和后台线程flush数据页，相对单行读取和写入来说，降低了读取和写入的频率

3) 一行数据在磁盘上如何存储

A. 存储格式

涉及到一个概念，行格式，意思是一行存储的格式，这个格式有多个。

COMPACT格式紧凑型

CREATE TABLE table_name (columns) ROW_FORMAT=COMPACT
ALTER TABLE table_name ROW_FORMAT=COMPACT

它的存储格式如下：

变长字段的长度列表，null值列表，数据头，column01的值，column02的值，column0n的值……

B. 变长字段列表

逆序存储的是非null值的字段长度

变长字段大小最大不超过65535

C. NULL值列表

逆序存储的是每个字段是否为null，用bit来表示

D. 数据头

40个bit位

第1、2个bit位预留的没有含义

第3个bit位delete_mask，标记是否删除

接下来4个bit位是n_owned记录数

接下来是13个bit位是heap_no堆位置

接下来是3个bit的record_type行记录类型，0代表的是普通类型，1代表的是B+树非叶子节点，2代表的是最小值数据，3代表的是最大值数据

最后是16个bit的next_record，这个是指向他下一条数据的指针。

“0x09 0x04 00000101 0000000000000000000010000000000000011001 00000000094C（DB_ROW_ID）00000000032D（DB_TRX_ID） EA000010078E（DB_ROL_PTR） 616161 636320 6262626262”

E. 真实字段值

隐藏字段
每行字段的开头还有三个隐藏字段
DB_ROW_I D 行id
DB_TRX_ID 事务id
DB_ROL_PTR 回滚指针

真实字段
用户自己的字段

F. 如何读取一条数据

一行记录的指针指向这行数据的真实地址，往前读，是40个bit的数据头+null值列表+可变字段长度，往右读是三个隐藏字段+真实字段数据。

那么解析一个一个字段的内容，首先需要确认字段是否固定长度？

如果是固定长度，从真实地址直接往后读

如果是可变长度字段，那么存在null和非null两种情况，那么就从null值列表bit位中解析出当前是否为null，为null就不再处理。

如果这个字段不为null，就去逆序的从右往左读取该字段的长度。

知道长度了，再从真实数据，读取这个长度的数据。

G. 变长字段列表和null值列表逆序的好处

从数据的指针开始，从左往右读，是真实字段一个一个顺序，从右往左读，也刚好是和真实字段的顺序一一对应

H. 行溢出

如果一页存不下，怎么办？这种情况，存不下的数据要往下一页放，下一页也放不下，就再往下一页放，指针指向过去。指针占用20字节大小。

4) 数据页的格式

数据页就跟每一行数据一样，都是由MySQL开发人员设计出来的一个特殊的存储格式。
数据页和缓存页是一一对应的。内存里面写数据就写到空闲区域，flush链表、lru链表刷盘的时候，整个缓存页，刷如磁盘。

数据页包含如下几个部分：
文件头 38个字节
数据页头 56个字节
最大记录、最小记录 26个字节
多个数据行 不固定
空闲区域 不固定
数据页目录 不固定
文件尾部 8个字节

5) 什么是表空间？

我们每创建一个表，对应磁盘上就有一个表名.ibd 的磁盘数据文件

表空间里面存储了这个表的数据，里面存放着所有的页数据
通过表空间来管理不同的表。

6) 什么是数据区？

但是一个表空间管理所有的页数据，太庞大了，不便于管理，因此有了数据区和数据组的概念。

一个区里面存放64个页，是1MB
一个组里面放256个区，是256MB

表空间里面
第1组第1区里面的前3个页是存放特殊信息的。
其他组第1区里面的前2个页是存放特殊信息的。

查询数据的时候，就是从表组区里面找到页，把页信息加载到缓存页里面去。

17. 随机读写和顺序读写的是什么

mysql读取一页数据，可能在磁盘上的任何一个位置，所以读取磁盘的数据只能是随机读这种方式。
随机读关注两个指标，IOPS、响应延迟
IOPS是并发随机读写的能力，一般核心数据库我们会使用ssd，来提高这种能力。
响应延迟是一次操作花费多长时间。

顺序写是追加的方式，往文件末尾追加数据。redo log就是这种方式，顺序写的性能是很高的，几乎和内存随机读写的性能差不多。
顺序读写关注吞吐量的指标。

一笔更新操作，会涉及到至少一次的随机读和一次顺序写，所以IOPS并发能力和写入吞吐量就决定了整体数据库的性能。

18. mysql读写数据在linux操作系统上是什么流程

liunx存储系统分为VFS层，文件系统层，pagecache层，通用block层，io调度层，block设备驱动层，block设备层。

通过VFS区分需要经过哪个文件系统（NFS、Ext3），如果缓冲区有则直接基于内存读写，没有则通往block层，把对文件的io请求转化为block io请求，把blockio请求转发给io调度层，有公平调度与最后期限调度，通过block驱动，对block设备进行读写

19. 什么是RAID磁盘冗余阵列

很多数据库在部署的时候，存储都是搭建的RAID存储架构，叫磁盘冗余阵列。

为什么会出现这个技术呢？

1）很多情况下，我们服务器上一块磁盘是不够使用的，为了扩大我们的存储空间，我们可能需要多块磁盘来存放数据。
那么就引入了RAID技术，可以管理多块磁盘的一种磁盘阵列技术。

2）RAID还可以实现数据冗余，比如写入数据的时候，可以写入到两块磁盘上去，第二块磁盘用来冗余，如果第一块磁盘坏掉了，还可以从第二块磁盘上读取出冗余的数据。这块都是RAID自动管理的，不需要人来操心。

3）RAID技术实际上就是管理多块磁盘的一种磁盘阵列技术，他有软件层面的东西，也有硬件层买的东西，比如有RAID卡这种硬件设备。

4）RAID还可以分成不同的技术方案，比如RAID 0、RAID 1、RAID 0+1、RAID2，等等，一直到RAID 10，很多种不同的多磁盘管理技术方案。

20. RAID存储定期性能抖动

1) 为了提高磁盘读写的写入性能，RAID卡提供了一个缓存

磁盘组成RAID阵列的时候，一般会有一个RAID卡，RAID卡是带有一个缓存的，它是类似于内存的SDRAM，大致就认为基于内存来存储吧。

RAID的缓存模式设置为write back的时候，所有写入磁盘的数据都是先写入到RAID卡的缓存里面，然后再慢慢的写入到磁盘里，这个写缓冲机制可以大幅度的提高磁盘的写入性能。

2) RAID卡缓存的问题

RAID卡好是非常好，但是有一个问题，服务器关闭或者服务器掉电了，缓存数据就会丢失，为了解决这个问题，就要单独给RAID卡提供独立的电池，一旦服务器关闭或者掉电，它就要基于自己的电池，把缓存数据写入到磁盘，保证数据不丢失。

3) 引入电池带来的问题

电池存在性能衰减，需要定时充电放电，大概每隔30-90天，不同厂商的不一样，就需要自动的充放电一次，来延长电池的寿命和校准电池的容量。

如果不这么做的话，一旦服务器掉电，电池启用，可能电池容量不够，没办法把缓存数据一次性写入磁盘，就会导致数据丢失。

4) 电池充放电会有什么问题？

电池充放电，RAID缓存的级别就从write back变成write through，这样就不会写缓存了，就变成了写磁盘，如果是写缓存的话，性能是0.1ms，写磁盘可能就变成了1ms，性能可能会出现10倍衰减。
那么这样就导致了，RAID存储性能存在定期的性能抖动，间接导致数据库定期的性能抖动。

5) RAID锂电池充放电导致性能抖动的优化

raid0，就是多块磁盘组成一个磁盘阵列，数据是分散写入到不同的磁盘的，磁盘的整体容量很大，同时可以写入多块磁盘，磁盘读写的并发能力很强。

但是raid0的问题是磁盘一旦坏了一块，数据就丢失了一部分。

raid1，两块磁盘互为镜像关系，数据会冗余一份，一块磁盘坏了，另外一块磁盘还有数据。一块磁盘压力太大，还可以让读请求路由到另外一块磁盘上去分担压力。

raid10，就是raid0+raid1组合起来，两块硬盘组成raid1，多个raid1再组成raid0，每一组两块硬盘互为冗余。

解决方案：

A. 给raid卡的锂电池换成电容

电容不用频繁重放电，支持透明充放电，自动检查电量，自动进行充电，缓存可以一直使用，io不用直接走磁盘。但是电容更换非常麻烦，而且容易老化，所以一般不常用。

B. 手动充放电

比较常用，关闭raid自动重放电，写一个脚本，每隔一段时间自动在晚上凌晨的业务低峰期，脚本触发充放电，这样避免在业务高峰期raid自动充放电，引起性能抖动。

充放电的时候不关闭write back缓存级别（写缓冲），做好UPS电源的支持

21. Too many connections故障

linux的文件句柄限制，导致了MySQL的最大连接数被限制。

linux为什么要有文件句柄的限制，主要是防止单进程消耗过多的机器上的资源。如果被限制了，就没办法创建大量的网络连接。

一般，在生产环境部署的系统，比如数据库、mq、存储系统、缓存系统，都需要调整linux系统的一些内核参数，这个文件句柄的数量一定要调整的，通常调整为65535。

linux文件句柄是1024的时候，mysql的最大连接数是214，mysql的最大连接数有个公式，源码里面写死了。

其实核心就是一行命令：
ulimit -HSn 65535

然后就可以用如下命令检查最大文件句柄数是否被修改了
设置之后，我们要确保变更落地到/etc/security/limits.conf文件里，永久性的设置进程的资源限制
cat /etc/security/limits.conf
cat /etc/rc.local

同时，修改mysql的my.cnf里面的max_connections参数，然后重启服务器，重启mysql
这样mysql最大连接数和linux最大文件句柄数就会生效。

22. redo log

1) redo log机制出现的原因

mysql buffer pool内存修改的脏数据，如果要同步刷盘的话，这个是随机写，而且是16kb的页大小，性能非常低。

redolog的好处，一是尺寸小，只记录表空间号，数据页号，磁盘文件偏移量，更新值这些信息，写入快；而是redolog是顺序写，一条一条记录往一个日志文件里面追加，速度快。

这样事务提交之后，即使内存脏数据丢失了，redolog里面还保存了这个数据更新后的信息，恢复也很方便。

2) redo log的存储格式

日志类型（就是类似MLOG_1BYTE之类的），表空间ID，数据页号，数据页中的偏移量，具体修改的数据

类型就告诉了你他这次增删改操作修改了多少字节的数据；MLOG_1BYTE、2、4、8
哪个表空间；
哪个数据页号；
在数据页的哪个偏移量开始执行，具体修改的数据是什么

有了上述信息，就可以还原一次数据增删改操作的遍历。

如果是MLOG_WRITE_STRING类型的日志，因为不知道具体修改了多少字节的数据，会多一个修改数据长度，就告诉你他这次修改了多少字节的数据，如下所示他的格式：

日志类型（就是类似MLOG_1BYTE之类的），表空间ID，数据页号，数据页中的偏移量，修改数据长度，具体修改的数据

3) 为什么需要redo log buffer

大量的事务执行的时候，都去写磁盘，性能肯定是比较低了，因此需要一个redo log buffer在内存里面存储先存储redo log日志。

redo log buffer 默认innodb_log_buffer_size=16MB

redo log buffer里面分成了一个一个小的redo log block，每个512kb，以块为单位向磁盘写入。

块里面，同一个事务的多条redo log先暂存起来，然后一组数据写入redo log block里面。

4) redo log buffer什么时间刷盘

写入的日志达到总量的一半，也就是超过了8MB（高并发场景）
事务提交的时候，必须把它所在的redo log block刷盘
后台线程每秒刷盘一次
mysql关闭的时候，刷盘

5) redo log 参数设置

通过show variables like ‘datadir’来查看

通过innodb_log_group_home_dir参数来设置日志存储的目录
通过innodb_log_file_size可以指定每个redo log文件的大小，默认是48MB
通过innodb_log_files_in_group可以指定日志文件的数量，默认就2个。

默认情况下，目录里就两个日志文件，分别为ib_logfile0和ib_logfile1，每个48MB，最多就这2个日志文件，就是先写第一个，写满了写第二个。那么如果第二个也写满了呢？继续写第一个，覆盖第一个日志文件里原来的redo log就可以了。循环使用。

23. undo log的格式

undo log主要是用来做事务回滚的。

比如你执行了INSERT语句，那么你的undo log必须告诉你插入数据的主键ID，让你在回滚的时候可以从缓存页里把这条数据给删除了；

如果你执行了DELETE语句，那么你的undo log必须记录下来被删除的数据，回滚的时候就得重新插入一条数据；

如果你执行了UPDATE语句，那么你必须记录下来修改之前的数据，回滚的时候就得把数据给更新回去

1) insert语句的undo log

这条日志的开始位置
主键的各列长度和值：单一主键或联合主键的id
表id：undo log属于哪个表
undo log日志编号：事务里面多个undo log，id从0递增
undo log日志类型：增删改
这条日志的结束位置

2) undo log内容

里面的内容有事务id，以及该事务id下，被修改字段的原始值

恢复到该事务id的时候，就把这些字段的原始值，覆盖回去就行了

24. 事务

1) 事务并发执行会产生什么问题？

业务系统是很可能多并发去执行事务的，会产生一些问题
事务并发执行对同一行数据更新，冲突怎么处理？
事务在更新一条数据，别的事务要查询这条数据，冲突怎么处理？

事务并执行的时候是通过MySQL事务的隔离级别、MVCC多版本隔离、锁机制

2) 脏写、脏读、不可重复读、幻读

A. 脏写

事务A对一条数据更新，事务B也对这条数据更新，事务A回滚，导致事务B的数据丢失

脏写就是两个事务没提交的状况下，都修改同一条数据，结果一个事务回滚了，把另外一个事务修改的值也给撤销了，所谓脏写就是两个事务没提交状态下修改同一个值。

B. 脏读

事务A对一条数据更新，还没有提交，事务B也查询了这条数据，读到了事务A还没提交的数据，这就是脏读。

无论是脏写还是脏读，都是因为一个事务去更新或者查询了另外一个还没提交的事务更新过的数据。

因为另外一个事务还没提交，所以他随时可能会反悔会回滚，那么必然导致你更新的数据就没了，或者你之前查询到的数据就没了，这就是脏写和脏读两种坑爹场景。

C. 不可重复读

一个事务多次查询一条数据读到的都是不同的值

一条数据，其他事务在一直修改，都提交了，事务A，在事务里面多次读取这条数据，每次读取的数据都是不一样的。

不可重复读，就是一条数据的值没法满足多次重复读值都一样，别的事务修改了值提交之后，就不可重复读了

D. 幻读

一个事务用一样的SQL多次查询，结果每次查询都会发现查到了一些之前没看到过的数据，出现了幻觉

E. 本质

都是数据库的多事务并发问题，那么为了解决多事务并发问题，数据库才设计了事务隔离机制、MVCC多版本隔离机制、锁机制，用一整套机制来解决多事务并发问题

3) SQL标准中的四个隔离级别

这4种级别包括了：read uncommitted（读未提交），read committed（读已提交），repeatable read（可重复读），serializable（串行化）

A. RU

解决脏写，不允许脏写，也就是不允许两个事务同事更新一条数据。
但是还存在脏读、不可重复读、幻读的问题。

B. RC

解决脏写和脏读，其他事务未提交的数据，看不到，提交了可以看到。
但是还存在不可重复读、幻读的问题。

C. RR

解决脏写、脏读、不可重复读
每次读读到的数据都是一样的，但是还可能是幻读的。

D. serializable

事务串行起来一个一个排队执行，一旦串行，数据库的并发可能就只有几十了，一般不会设置。

4) MySQL对四个隔离级别的支持

A. mysql对隔离级别的支持

mysql四种隔离级别都支持
默认事务隔离级别，RR
同时RR级别是可以避免幻读发生的

也就是说，MySQL里执行的事务，默认情况下不会发生脏写、脏读、不可重复读和幻读的问题，事务的执行都是并行的，大家互相不会影响，我不会读到你没提交事务修改的值，即使你修改了值还提交了，我也不会读到的，即使你插入了一行值还提交了，我也不会读到的，总之，事务之间互相都完全不影响！

实现如此多的效果，主要靠mvcc多版本并发控制隔离机制

mysql里面设置隔离级别
REPEATABLE READ，READ COMMITTED，READ UNCOMMITTED，SERIALIZABLE
SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;

B. spring事务对隔离级别的支持

@Transactional(isolation=Isolation.DEFAULT)，然后默认的就是DEFAULT值，这个就是MySQL默认支持什么隔离级别就是什么隔离级别。

那MySQL默认是RR级别，自然你开发的业务系统的事务也都是RR级别的了。

5) mvcc多版本并发控制隔离机制

A. undo log版本链

每条数据都有两个隐藏字段

一个是trx_id事务id，操作这条数据的事务id

一个是roll_pointer 指向上一个版本数据的undo log

事务操作数据之前，会创建undolog

里面的内容有事务id，以及该事务id下，被修改字段的原始值

然后操作数据，然后更新回滚指针

undo log还指向了上一个undo log

所有的undolog就形成了一个undo log版本链。

B. ReadView

事务在开始执行的时候，会生成一个 ReadView

里面比较关键的东西有4个

一个是m_ids，未提交的事务id

一个是min_trx_id，最小事务id

一个是max_trx_id，最大事务id，还没有生成（<的关系）

一个是creator_trx_id，当前事务id

C. ReadView+UndoLog实现MVCC

举个例子：
假设数据库有一行记录，事务id=32，原始值=1

接着有两个事务来并发操作这条记录。

事务A(id=45)，事务A来读取数据
事务B(id=59)，事务B来修改数据

事务A开启ReadView，里面有两个活跃事务id(45、59)，最小事务id(45)，最大事务id(60)，当前事务id(45)
此时事务A，查询到的数据是事务id=32,原始值=1的那条记录

事务B接着开启ReadView，把值修改为2，数据行的事务id设置成自己，回滚指针指向了对应的undo log，之后提交数据。

此时事务A继续查询，此时数据行的事务id=59，小于最大事务id60，大于最小事务id45，同时活跃列表里面有45和59这两个事务id，
因此确认是并发执行的，事务A只能查询比自己视图最小事务id之前的事务，因此顺着undolog版本链继续去查询，找到事务id=32的数据，查询出来。

如果此时事务A修改数据，数据行的事务id=45，之后再来查询数据，发现这个数据行的数据是自己修改的，那么可以查出来。

如果此时事务C(事务id=70)，也来修改数据，数据行的事务id=70，此时事务A再来查询，发现事务id比自己的最大事务id=60还大，因此判定是自己事务
之后的数据，不能读取，按照undo log向上追溯，找到了undo log 事务id=45，自己修改的数据。

D. mvcc实现原理总结

undo log版本链

数据行记录了事务id和它对应的上一版本数据的undolog的指针

事务的每次修改都拷贝上一个数据版本的事务id，本次修改字段的原始值到undolog里面

undo log日志有指针指向上一个版本undo log日志，形成了一个undo log版本链。

读取数据的时候，如果一个undolog对应的事务id的数据需要还原。
还原的过程是最新数据行+ 包括这个undolog以及到这个数据行中间的所有undolog，从数据行链条过来，一个一个还原，直至还原到自己这条数据，才会形成那个事务id对应的原始快照数据。

版本 tid v1 v2 v3 v4
now 100 2 3 4 4
11 99 6 1
10 98 3 2

事务id为10的原始数据就是这样的
10 3 2 1 4

ReadView

以RR模式为例

事务开启的时候会开启ReadView，记录当前活跃的事务id列表、最小事务id、最大事务id、当前事务id。

之后本事务查询，只能查看到最小事务id之前的数据（绝对执行完了）和当前事务id修改的数据。

最大事务id之后的数据，绝对是当前事务之后的事务，不可以查询出来。

最小事务id和最大事务id之间的数据，说明是并发执行的，如果这个数据的事务id，还在活跃事务id列表是不可以查询出来的，不再则可以查询出来。

E. RR模式的原理

RR模式一开启事务就只创建一次ReadView，后面再读数据，不会再生成ReadView，
因此活跃的事务id列表始终是不变的，这些事务id数据是不可以查询出来的。
解决了不可重复读。

同时，新插入的数据，事务id>最大事务id，因此也不能查询出来，解决了幻读的问题。

F. RC模式的原理

RC模式下，每次读取数据都要重新创建一次ReadView，那么数据的事务id虽然落到最大最小事务id范围内，但是不在活跃事务id列表中，就可以判定事务已经提交了。
因此可以读取到已经提交的数据。同时修改提交的数据和新增的数据已经提交了，就可以读取到。

1. 锁机制

1) 多个事务更新同一行数据时，是如何加锁避免脏写的

一个事务来更新数据，会给缓存页创建一个锁结构，都是在内存里面的。
锁里面包含了事务id和等待状态。

数据更新的时候都要先去拿一下锁，锁里面记录了事务id和等待状态，拿到了等待状态就是false。拿不到就要排队，等待状态为true。

拿到锁的事务执行完了，就会去唤醒后面排队的事务，并把它的等待状态设置是false，后续拿到锁的事务就可以执行了。

独占锁
当事务拿到了独占锁，其实是X锁，也就是Exclude独占锁，其他锁就不能读写了

在多个事务并发更新数据的时候，都是要在行级别加独占锁的，这就是行锁，独占锁都是互斥的，所以不可能发生脏写问题，一个事务提交了才会释放自己的独占锁，唤醒下一个事务执行。

2) mysql 事务独占锁更新的时候，其他事务读这个数据需要加锁吗？

默认情况是不用加锁的，因为mysql是基于mvcc快照读，对一行数据的读和写操作是不会加锁互斥的，mvcc避免了读写加锁的互斥，提高了性能

3) msyql手动加锁怎么操作

共享锁，就是S锁，这个共享锁的语法如下：select * from table lock in share mode，你在一个查询语句后面加上lock in share mode，意思就是查询的时候对一行数据加共享锁。

如果别的事务加了独占锁，此时加不了共享锁，互斥的，只能等待
如果你加了共享锁，别的事务就不能加独占锁了，但是别的事务可以加共享锁

for update 加写锁

规律：
更新数据的时候必然加独占锁，独占锁和独占锁是互斥的，此时别人不能更新；
但是此时你要查询，默认是不加锁的，走mvcc机制读快照版本
但是你查询是可以手动加共享锁的，共享锁和独占锁是互斥的
但是共享锁和共享锁是不互斥的

业务开发中，一般不手动使用数据库的锁，一般会基于redis/zookeeper的分布式锁来控制业务系统的锁逻辑。在sql里面加锁会导致加锁逻辑隐藏在sql语句里面，业务系统层面是不好维护的，一般不建议这么做。

比较正常的情况而言，其实还是多个事务并发运行更新一条数据，默认加独占锁互斥，同时其他事务读取基于mvcc机制进行快照版本读，实现事务隔离。

4) Metadata Locks

ddl语句操作的时候，会加元数据锁，这个时候，会阻塞所有增删改操作。
执行增删改的时候，会阻塞ddl操作。
二者是互斥的

这是通过元数据所来实现的，和表锁不是一个概念，表锁是InnoDb存储引擎提供的表级锁。

5) 哪些操作会导致表级别加锁

表锁是非常鸡肋的一个东西，很少会使用，表现为两种，一种就是表锁，一种是表级意向锁。

A. 手动加表锁

LOCK TABLES xxx READ：这是加表级共享锁
LOCK TABLES xxx WRITE：这是加表级独占锁

B. 事务自动加表级意向锁

事务在表里执行增删改操作，会在行级加独占锁，会在表级加一个意向独占锁
事务在表里执行查询操作，会在表级加一个意向共享锁

意向锁之间是不会互斥的，比如你两个事务，分别更新id=1和id=10两条数据，互斥没有任何意义。你查询一个数据和更新一个数据，互斥也没有任何意义。

关系表如下；

其实更新数据自动加的表级意向独占锁，会跟你用 LOCK TABLES xxx WRITE 手动加的表级独占锁是互斥的，所以说，假设你手动加了表级独占锁，此时任何人都不能执行更新操作了！

或者你用LOCK TABLES xxx READ手动加了表级共享锁，此时任何人也不能执行更新操作了，因为更新就要加意向独占锁，此时是跟你手动加的表级共享锁，是互斥的！

如果手动加了表锁和自动加的表锁是互斥的，会阻塞掉其他事务的一些正常的读写操作。

一般来说，根本就不会手动加表级锁，所以一般来说读写操作自动加的表级意向锁，互相之间绝对不会互斥。

基本上都是对同一行数据的更新操作加的行级独占锁是互斥，跟读操作都是不互斥的，读操作默认都是走mvcc机制读快照版本的！

6) mysql是如何解决实时读的幻读问题

行锁+间隙锁=next key lock，只在rr隔离级别下生效。
实时读的时候手动加锁，会对索引的左右间隙加gap间隙锁，这样锁定期间，该间隙就无法插入新值，能够保证筛选的字段，再次筛选的时候，结果还是一致的。

比如age=20，前后分别有值10 30，那么锁定范围就是10-30，这个范围内就不能插入数据，或者修改数据更新到这里。再次查询的时候，还是原来的数据。

间隙锁的目的是为了防止幻读，其主要通过两个方面实现这个目的：
（1）防止间隙内有新数据被插入
（2）防止已存在的数据，更新成间隙内的数据（例如防止numer=3的记录通过update变成number=5）

7) 行锁、gap锁、表锁

mysql加锁，是在索引上加锁的，RR隔离级别下的加锁行为如下
主键加锁，加的是行锁
唯一键加锁，加的是行锁
非唯一键加锁，加的是行锁+gap锁（行两边的gap）
没有索引是加锁，锁的是全表

rc模式下没有gap锁

2. 大量脏页刷盘带来的性能抖动

1) 查询语句查出来的数据量巨大

查询语句查出来的数量量比较大，需要大量淘汰脏页刷盘，flush脏页到磁盘上去耗时较长。原来20ms可能变成几秒。

2) redo日志文件写满，换文件复写

redo日志文件写满的情况下，会触发刷盘，redo日志文件有两个，一个写满了就要写另外一个，另外一个，如果没有刷盘，数据就会丢失，因此需要强制把马上要覆盖的redo log对应的脏页全部刷盘。导致数据库直接hang死，无法处理任何请求。

3) 解决方案

提升flush的速度

flush缓存页到磁盘是随机io的行为，需要在磁盘上找到各个缓存页所在的位置，写入数据。
采用ssd固态硬盘，随机io并发能力强。

配置innodb_io_capacity=2000，刷盘是刷新多少脏页。
把innodb_io_capacity调整为ssd固态硬盘的iops

单盘 sas/sata：200左右（10000rpm转速）
sas*12/raid10 ：2000
ssd 硬盘：8000
fusion-io（闪存卡）：25000~50000

配置innodb_flush_neighbors=0
禁止刷盘临近的缓存页，降低了刷盘缓存页的数量

3. 索引

1) 磁盘数据页的存储结构

数据页组成双向链表，一页里面的数据行组成单向链表，根据主键id从小到大排序

每个数据页都对应一个页目录，页目录里面存储的主键id和槽位的关系。

2) 没有索引如何查询数据

根据双向链表依次把磁盘上的数据页加载到缓存页里去，然后在一个缓存页内部来查找那条数据，也就是全表扫描

3) 页分裂

一页数据写满了，就要换下一页去写，下一页要求主键值是比上一页大的。

自增id是很容易做到这一点的，但是有些情况下，不是自增id，比如uuid，那么下一页写数据的时候，就很可能产生页分裂，把值小的数据行移动到前面的页去，把前面页较大的数据行，移动到后面页去。这就是页分裂的现象。

页分裂核心是要保证下一页的数据主键值要比上一页的要大。

4) 主键索引的设计

每个数据页的最小主键和页号做成一个kv，就是主键目录

比如有如下目录

最小主键=2 页号=2
最小主键=5 页号=5

去找id=4，结合最小主键id，通过二分法去找属于哪个页，然后去这一页去找这个数据

5) 索引的页存储结构，b+树

A. 为什么要做索引页？

表里面存在大量的数据，几百万，几千万，几亿，就会出现大量的数据页，主键目录里面就会存储大量的最小主键值和数据页号的kv对。

因此需要给主键目录，做索引页，分开存储数据。

B. 去哪个索引页找数据？

因此增加一个更高的层级，里面存储了每个索引页编号和索引页最小主键值。

那么现在先通过最小主键值二分法找到对应的索引页，再去索引页二分法找到对应的主键值对应的数据页，再进入数据页找数据行。

此时可以再次分裂，再加一层索引页，这就是一颗b+树。查找的时候从顶层二分查找，一层一层往下定位索引页，最终定位到一个数据页里面，再二分查找找到对应的那条记录。

6) 聚簇索引是什么

如果一颗大的B+树索引数据结构里，叶子节点就是数据页自己本身，那么此时我们就可以称这颗B+树索引为聚簇索引！

所有的索引页+数据页组成的B+树就是聚簇索引

7) 聚簇索引为什么自动维护

在InnoDB里面，聚簇索引包含了数据，增删改的时候就是操作这个聚簇索引的数据页。

如果数据页分裂了，它会调整数据页内部的数据行，保证主键值都是顺序的，下一个数据页的所有主键值要比上一个要大。

同时页分裂的时候，会维护上层索引数据结构，不同的数据页和最小主键id。

同时索引页分裂的时候，会维护上层的索引页，数据量越大，索引页的层级就越多。但是一般索引页可以放很多索引条目，一般亿级大表，索引的层级也就3-4层。

这个聚簇索引默认是按照主键来组织的，所以你在增删改数据的时候，一方面会更新数据页，一方面其实会给你自动维护B+树结构的聚簇索引，新增和更新索引页。

有主键的情况下，聚簇索引是默认给建立的

8) 主键字段之外建立的二级索引

A. 单字段二级索引

给主键字段之外的字段建立索引，称之为二级索引。
二级索引和聚簇索引的原理是一样的，也是一颗b+树，区别是叶子节点虽然是数据页，但存放的是主键id+索引字段值

同时，叶子节点的上一层节点存的是最低层索引页，存放索引字段最小值和数据页编号

再上一层节点页是索引页，存放的是索引字段最小值和索引页编号，索引页还可以再次增加层级。

查询数据的时候先根据索引字段二分法往下找，从数据页，找到了主键id，再回表，根据主键id从主键的聚簇索引里面去找到这条数据

B. 多字段二级索引

索引字段是两个或者多个也是一样的道理，数据页存储的是主键id+索引1+索引2
最低层索引页存的是索引1+索引2 最小值和数据页
再向上是索引1+索引2 最小值和索引页

联合索引的运行原理也是一样的，只不过是建立一颗独立的B+树，叶子节点的数据页里放了id+name+age，然后默认按照name排序，name一样就按照age排序，不同数据页之间的name+age值的排序也如此。

C. 总结

innodb存储引擎的索引的完整实现原理了，也没那么难，不过就是建立B+树，根据B+树一层一层二分查找罢了，然后不同的索引就是建立不同的B+树，然后你增删改的时候，一方面在数据页里更新数据，一方面就是维护你所有的索引。

9) 插入数据索引字段最小值一样怎么处理？

插入一条数据，从根节点二分法一个一个往下找，如果下一个节点的索引字段最小值都相同，怎么比较？

为了解决这个问题，索引页里面放的是索引字段最小值 + 主键id

插入数据的时候，索引字段最小值相同的情况下，就是找主键id大的那个。

10) 索引的好处

你可以直接根据某个字段的索引B+树来查找数据，不需要全表搜索，性能提升是很高的。

11) 索引的坏处

空间：
每个索引都是一棵b+树，每一棵b+树都要占用磁盘空间，索引太多，耗费磁盘空间。

时间：
需要维护各个索引的有序性，数据页内的有序性，索引页的有序性，不停的增删改，必然导致数据在页之间的移动，不停的增加索引页。如果索引太多，必然导致增删改的速度就下降了。查询速度是提高了，但是增删改的速度下降了，因此，不建议一个表里搞太多的索引。

12) 设计联合索引的原因

设计系统的时候一般都是设计联合索引，很少用单个字段做索引，原因之前讲过，我们还是要尽可能的让索引数量少一些，避免磁盘占用太多，增删改性能太差。

13) 联合索引全值匹配的原理

涉及到了一个索引使用的规则，那就是你发起的SQL语句里，where条件里的几个字段都是基于等值来查询，都是用的等于号！

而且where条件里的几个字段的名称和顺序也跟你的联合索引一模一样！此时就是等值匹配规则，上面的SQL语句是百分百可以用联合索引来查询的。

那么查询的过程也很简单了，首先到索引页里去找，索引页里有多个数据页的最小值记录，此时直接在索引页里基于二分查找法来找就可以了，直接可以定位到他所在的数据页

然后在数据页内部本身也是一个单向链表，你也是直接就做二分查找就可以了，先按第一个字段的值来找，你会发现几条数据都是一样的，此时就可以按照第二个字段来二分查找，此时会发现多条数据都是一样的，接着就按照第三个字段来二分查找。

对于联合索引而言，在数据页里面就是依次按照各个字段来进行二分查找，先定位到第一个字段对应的值在哪个页里，然后如果第一个字段有多条数据值都一样，就根据第二个字段来找，以此类推，一定可以定位到某条或者某几条数据！

14) 常见和最基本的索引使用规则

A. 等值匹配规则

就是你where语句中的几个字段名称和联合索引的字段完全一样，而且都是基于等号的等值匹配，那百分百会用上我们的索引，这个大家是没有问题的，即使你where语句里写的字段的顺序和联合索引里的字段顺序不一致，也没关系，MySQL会自动优化为按联合索引的字段顺序去找。

B. 最左侧列匹配

假设我们联合索引是KEY(class_name, student_name, subject_name)，那么不一定必须要在where语句里根据三个字段来查，其实只要根据最左侧的部分字段来查，也是可以的。

比如你可以写select * from student_score where class_name=’’ and student_name=’’，就查某个学生所有科目的成绩，这都是没有问题的。

但是假设你写一个select * from student_score where subject_name=’’，那就不行了，因为联合索引的B+树里，是必须先按class_name查，再按student_name查，不能跳过前面两个字段，直接按最后一个subject_name查的。

另外，假设你写一个select * from student_score where class_name=’’ and subject_name=’’，那么只有class_name的值可以在索引里搜索，剩下的subject_name是没法在索引里找的，道理同上。

所以在建立索引的过程中，你必须考虑好联合索引字段的顺序，以及你平时写SQL的时候要按哪几个字段来查。

C. 最左前缀匹配原则

如果你要用like语法来查，比如select * from student_score where class_name like ‘1%’，查找所有1打头的班级的分数，那么也是可以用到索引的。

因为你的联合索引的B+树里，都是按照class_name排序的，所以你要是给出class_name的确定的最左前缀就是1，然后后面的给一个模糊匹配符号，那也是可以基于索引来查找的，这是没问题的。

但是你如果写class_name like ‘%班’，在左侧用一个模糊匹配符，那他就没法用索引了，因为不知道你最左前缀是什么，怎么去索引里找啊？

D. 范围查找规则

我们可以用select * from student_score where class_name>’1班’ and class_name<’5班’这样的语句来范围查找某几个班级的分数。

这个时候也是会用到索引的，因为我们的索引的最下层的数据页都是按顺序组成双向链表的，所以完全可以先找到’1班’对应的数据页，再找到’5班’对应的数据页，两个数据页中间的那些数据页，就全都是在你范围内的数据了！

但是如果你要是写select * from student_score where class_name>’1班’ and class_name<’5班’ and student_name>’’，这里只有class_name是可以基于索引来找的，student_name的范围查询是没法用到索引的！

这也是一条规则，就是你的where语句里如果有范围查询，那只有对联合索引里最左侧的列进行范围查询才能用到索引！

E. 等值匹配+范围匹配的规则

如果你要是用select * from student_score where class_name=’1班’ and student_name>’’ and subject_name<’’，那么此时你首先可以用class_name在索引里精准定位到一波数据，接着这波数据里的student_name都是按照顺序排列的，所以student_name>’’也会基于索引来查找，但是接下来的subject_name<’’是不能用索引的。

所以综上所述，一般我们如果写SQL语句，都是用联合索引的最左侧的多个字段来进行等值匹配+范围搜索，或者是基于最左侧的部分字段来进行最左前缀模糊匹配，或者基于最左侧字段来进行范围搜索，这就要写符合规则的SQL语句，才能用上我们建立好的联合索引！

15) 排序的时候怎么样才能使用上索引

A. 为什么排序要使用索引

排序不用索引的话，把一堆数据放到一个临时磁盘文件里，然后直接硬上各种排序算法在磁盘文件里搞一通排序，接着按照你指定的要求走limit语句拿到指定分页的数据，速度会非常的慢。

B. 怎么使用索引

SQL语句里，应该尽量最好是按照联合索引的字段顺序去进行order by排序，这样就可以直接利用联合索引树里的数据有序性，到索引树里直接按照字段值的顺序去获取你需要的数据了。

举例：
按照xx1,xx2,xx3三个字段来进行排序罢了，在联合索引的索引树里都排序好了，直接就按照索引树里的顺序，把xx1,xx2,xx3三个字段按照从小到大的值获取前面100条就可以了。

然后拿到100条数据的主键再去聚簇索引里回表查询剩余所有的字段。

所以说，在你的SQL语句里，应该尽量最好是按照联合索引的字段顺序去进行order by排序，这样就可以直接利用联合索引树里的数据有序性，到索引树里直接按照字段值的顺序去获取你需要的数据了。

C. 限定规则

1）多字段的排序方向一致
因为联合索引里的字段值在索引树里都是从小到大依次排列的，所以你在order by里要不然就是每个字段后面什么都不加，直接就是order by xx1,xx2,xx3，要不然就都加DESC降序排列，就是order by xx1 DESC,xx2 DESC,xx3 DESC。

如果都是升序排列，直接就从索引树里最小的开始读取一定条数就可以了，要是都是降序排列，就是从索引树里最大的数据开始读取一定的条数就可以了，但是你不能order by语句里有的字段升序有的字段降序，那是不能用索引的

2）orderby字段需要在索引
要是你order by语句里有的字段不在联合索引里，不能使用索引

3）不能使用复杂函数
order by语句里的字段用了复杂的函数，也不能使用索引

D. 使用索引的排序

ORDER BY 子句索引列组合满足索引最左前列
explain select id from user order by id; //对应(id)、(id,name)索引有效

WHERE子句+ORDER BY子句索引列组合满足索引最左前列
explain select id from user where age=18 order by name; //对应(age,name)索引

E. 使用filesort方式的排序

对索引列同时使用了ASC和DESC
explain select id from user order by age asc,name desc; //对应(age,name)索引

WHERE子句和ORDER BY子句满足最左前缀，但where子句使用了范围查询（例如>、<、in等）
explain select id from user where age>10 order by name; //对应(age,name)索引

ORDER BY或者WHERE+ORDER BY索引列没有满足索引最左前列
explain select id from user order by name; //对应(age,name)索引

使用了不同的索引，MySQL每次只采用一个索引，ORDER BY涉及了两个索引
explain select id from user order by name,age; //对应(name)、(age)两个索引

WHERE子句与ORDER BY子句，使用了不同的索引
explain select id from user where name=’tom’ order by age; //对应(name)、(age)索引

WHERE子句或者ORDER BY子句中索引列使用了表达式，包括函数表达式
explain select id from user order by abs(age); //对应(age)索引

16) 分组使用索引

对于group by后的字段，最好也是按照联合索引里的最左侧的字段开始，按顺序排列开来，这样的话，其实就可以完美的运用上索引来直接提取一组一组的数据，然后针对每一组的数据执行聚合函数就可以了。

这个group by和order by用上索引的原理和条件都是差不多的，本质都是在group by和order by之后的字段顺序和联合索引中的从最左侧开始的字段顺序一致，然后就可以充分利用索引树里已经完成排序的特性，快速的根据排序好的数据执行后续操作了。

17) 回表查询对性能的损害

A. 大批量回表查询，性能也会降低

不管是单列索引还是联合索引，其实一个索引就对应着一颗独立的索引B+树，索引B+树的节点仅仅包含了索引里的几个字段的值以及主键值。

即使我们根据索引树按照条件找到了需要的数据，那也仅仅是索引里的几个字段的值和主键值，万一你搞了一个select *还需要很多其他的字段，那还得走一个回表操作，根据主键跑到主键的聚簇索引里去找，聚簇索引的叶子节点是数据页，找到数据页里才能把一行数据的所有字段值提取出来。

类似select * from table order by xx1,xx2,xx3的语句，可能你就是得从联合索引的索引树里按照顺序取出来所有数据，接着对每一条数据都走一个主键的聚簇索引的查找，其实性能也是不高的。

有的时候MySQL的执行引擎甚至可能会认为，你要是类似select * from table order by xx1,xx2,xx3的语句，相当于是得把联合索引和聚簇索引，两个索引的所有数据都扫描一遍了，那还不如就不走联合索引了，直接全表扫描得了，这样还就扫描一个索引而已。

B. 小量数据回表查询，性能高

但是你如果要是select * from table order by xx1,xx2,xx3 limit 10这样的语句，那执行引擎就知道了，你先扫描联合索引的索引树拿到10条数据，接着对10条数据在聚簇索引里查找10次就可以了，那么就还是会走联合索引的。

C. 覆盖索引

类似select xx1,xx2,xx3 from table order by xx1,xx2,xx3这样的语句，这种情况下，你仅仅需要联合索引里的几个字段的值，那么其实就只要扫描联合索引的索引树就可以了，不需要回表去聚簇索引里找其他字段了。

所以这个时候，需要的字段值直接在索引树里就能提取出来，不需要回表到聚簇索引，这种查询方式就是覆盖索引。

D. 联合索引使用建议

在写SQL语句的时候，一方面是你要注意一下也许你会用到联合索引，但是是否可能会导致大量的回表到聚簇索引，如果需要回表到聚簇索引的次数太多了，可能就直接给你做成全表扫描不走联合索引了；

一方面是尽可能还是在SQL里指定你仅仅需要的几个字段，不要搞一个select *把所有字段都拿出来，甚至最好是直接走覆盖索引的方式，不要去回表到聚簇索引。

即使真的要回表到聚簇索引，那你也尽可能用limit、where之类的语句限定一下回表到聚簇索引的次数，就从联合索引里筛选少数数据，然后再回表到聚簇索引里去，这样性能也会好一些。

18) 设计索引考虑的因素

A. 表结构设计完毕，不要着急设计索引

等待开发完毕，sql语句都写出来了，再设计索引

B. 第一个索引设计原则 where order by group by最左匹配联合索引

针对你的SQL语句里的where条件、order by条件以及group by条件去设计索引

你的where条件里要根据哪些字段来筛选数据？order by要根据哪些字段来排序？group by要根据哪些字段来分组聚合？

设计一个或者两三个联合索引，每一个联合索引都尽量去包含上你的where、order by、group by里的字段，接着你就要仔细审查每个SQL语句，是不是每个where、order by、group by后面跟的字段顺序，都是某个联合索引的最左侧字段开始的部分字段？

比如你有一个联合索引是INDEX(a,b,c)，此时你一看发现有三个SQL，包含了where a=? and b=?，order by a,b，group by a这些部分，那么此时where、order by、group by后续跟的字段都是联合索引的最左侧开始的部分字段，这就可以了，说明你的每个SQL语句都会用上你的索引了。

所以在设计索引的时候，首先第一条，就是要按照这个原则，去保证你的每个SQL语句的where、order by和group by都可以用上索引。

如果有范围字段的查询，范围字段要放在最后

C. 第二索引设计原则 使用那些基数比较大、类型较小的字段、前缀索引

基数大 值较多，才能发挥B+树快速查找的优势

类型小，比如tinyint，占用磁盘小，搜索的时候性能也会好一点

varchar(256)太大，可以KEY my_index(name(20),age,course)，对字段的前缀建立索引

此时你在where条件里搜索的时候，如果是根据name字段来搜索，那么此时就会先到索引树里根据name字段的前20个字符去搜索，定位到之后前20个字符的前缀匹配的部分数据之后，再回到聚簇索引提取出来完整的name字段值进行比对就可以了。

同时，order by，group by是无法使用上前缀索引的，要注意。

D. 第三设计原则，where条件里面不要函数和计算

E. 建议

设计索引别太多，建议两三个联合索引就应该覆盖掉你这个表的全部查询了

否则索引太多必然导致你增删改数据的时候性能很差，因为要更新多个索引树。

19) 社交app实战案例

A. 场景

有一个user表

大致会包含你的地区（你在哪个省份、哪个城市，这个很关键，否则不在一个城市，可能线上聊的好，线下见面的机会都没有），性别，年龄，身高，体重，兴趣爱好，性格特点，还有照片，当然肯定还有最近一次在线时间（否则半年都不上线APP了，你把他搜出来干什么呢？）

需要筛选，排序，分页
最终SQL语句可能是类似于：select xx from user_info where xx=xx order by xx limit xx,xx。

这里首先就给我们出了一个难题，之前学习索引使用规则的时候，我们都知道，你在where条件里必须是使用联合索引里最左侧开始的连续多个字段进行筛选，然后排序的时候也必须是用联合索引里的最左侧开始的多个连续字段进行排序。

那问题来了，假设你的SQL需要按照年龄进行范围筛选，同时需要按照用户的评分进行排序，类似下面的SQL：select xx from user_info where age between 20 and 25 order by score，那就有问题了。

假设你就一个联合索引，age在最左侧，那你的where是可以用上索引来筛选的，但是排序是基于score字段，那就不可以用索引了。那假设你针对age和score分别设计了两个索引，但是在你的SQL里假设基于age索引进行了筛选，是没法利用另外一个score索引进行排序的。

针对这个实际场景，你要明白的第一个难题就是，往往在类似这种SQL里，你的where筛选和order by排序实际上大部分情况下是没法都用到索引的！

B. where和order by冲突怎么建立索引

让where条件去使用索引来快速筛选出来一部分指定的数据，接着再进行排序，最后针对排序后的数据拿出来一页数据。
因为基于索引进行where筛选往往可以最快速度筛选出你要的少部分数据，如果筛选出来的数据量不是太大的话，那么后续排序和分页的成本往往不会太大！

C. 索引字段如何选择

用户在搜索潜在好友的时候，一般会用上哪些条件呢？我们到底要把哪些字段包含到索引里去？到底在联合索引里，字段的顺序要如何排列呢？

我们首先应该在联合索引里包含省份、城市、性别，这三个字段！

因为这三个字段都是在搜索里几乎必定包含的三个字段，假设你要搜索潜在好友，那么必定是会搜索跟你同一个地方的，然后搜索某个性别的的其他用户，这几个条件在APP里完全可以做成必选项，用户也几乎必定会指定。

但是此时有人就会说了，之前不是说过么，基数太低的字段最好别放到索引里去，那省份、城市和性别，都是基数非常小的几个字段，可选的值就那么几个，为什么要放到索引里去？

这是个好问题，但是规则是死的，人是活的。

假设你就因为省份、城市和性别几个字段的基数太小了，此时就不把他们几个包含到联合索引里去，那么你实际查询的时候都要基于这几个字段去搜索，此时你就只能把这几个字段放在where条件的最后，那么最后每次查询都必须要先用联合索引查询出来一部分数据，接着数据加载到内存里去，再根据where条件最后的省份、城市和性别几个字段进行过滤筛选，每次查询都得多这么一个步骤。

所以与其如此，还不如就把省份、城市和性别三个字段，放在联合索引的最左侧，这样跟其他字段组合联合索引后，让大部分的查询都可以直接通过索引树就可以把where条件指定的数据筛选出来了。

可以把基数较低但是频繁查询（几乎每次查询都会指定）的省份、城市和性别几个字段放到联合索引的最左侧去，此时就可以让每次查询时指定的省份、城市和性别，都直接从索引树里进行筛选。

D. 联合索引带范围查询

针对频繁使用的包含枚举值范围的一些字段，也完全可以加入到联合索引里去，可以设计成（province, city, sex, hobby, character, age）这样的一个联合索引，此时假设出现了这样一个查询，按照省份、城市、性格和年龄进行搜索，此时SQL怎么写？

还是用之前的那个策略和思路，就是写成where province=xx and city=xx and sex in(xx, xx) and hobby in (xx, xx, xx, xx) and character=xx and age>=xx and age<=xx

也就是说，即使你不需要按性别和爱好进行筛选，但是在SQL里你可以对这两个字段用in语句，把他们所有的枚举值都放进去。这样的话，就可以顺利的让province，city，character和age四个真正要筛选的字段用上索引，直接在索引里进行筛选都是没有问题的。

假设你where语句里有等值匹配，还有范围匹配，此时必须是先让联合索引最左侧开始的多个字段使用等值匹配，接着最后一个字段是范围匹配。

就比如上面的语句where province=xx and city=xx and sex in(xx, xx) and hobby in (xx, xx, xx, xx) and character=xx and age>=xx and age<=xx，他们完全是按照联合索引最左侧开始的，province、city、sex、hobby、character都是联合索引最左侧开始的多个字段，他们都是等值匹配，然后最后一个age字段使用的是范围匹配，这种就是可以完全用上索引的。

但是如果age放到了中间，就无法使用索引了，一旦你的一个字段做范围查询用到了索引，那么这个字段接下来的条件都不能用索引了，这是规则

所以说，实际设计索引的时候，必须把经常用做范围查询的字段放在联合索引的最后一个，才能保证你SQL里每个字段都能基于索引去查询。

E. 增加其他字段 7天内是否登录

可以设计一个联合索引为：（province, city, sex, hobby, character, does_login_in_latest_7_days, age）

一般来说，假设你要是where语句里通过上述联合索引就可以过滤掉大部分的数据，就保留小部分数据下来基于磁盘文件进行order by语句的排序，最后基于limit进行分页，那么一般性能还是比较高的。

F. 低基数字段筛选+其他字段排序

设计辅助的（sex, score）索引

针对一些低基数字段筛选+评分排序的查询场景，可以设计类似（sex, score）的辅助索引来应对，让他快速定位到一大片低基数字段对应的数据，然后按照索引顺序去走limit语句获取指定分页的数据，速度同样会很快。

此时因为where条件里的字段是等值匹配，而且还是等于某个常量值，所以虽然order by后跟的score字段是（sex, score）索引里的第二个字段，order by没有从索引最左侧字段开始排列，但是他也可以使用到索引来排序。

因为具体到使用索引的层面，他会先对where条件里的sex=’female’在索引树里筛选到这部分数据，接着在sex=’female’的数据里，这些数据实际上都是排列在一起的，因为在索引里，会按照sex和score两个字段去进行排序，所以sex=’female’的数据都是在一块儿的。

然后找到这部分数据之后，接着就可以确定，这部分数据肯定是按照score字段进行排序的，此时就可以按照score字段值的顺序，去读取你的limit语句指定的数据分页出来就可以了

所以此时你这种针对sex低基数的字段的筛选和基于评分排序的语句，整体运行的效率是非常高的，完全可以基于辅助索引来实现。

G. 总结

核心重点就是，尽量利用一两个复杂的多字段联合索引，抗下你80%以上的 查询，然后用一两个辅助索引抗下剩余20%的非典型查询，保证你99%以上的查询都能充分利用索引，就能保证你的查询速度和性能！

4. SQL执行计划

1) const

通过聚簇索引 或者 唯一索引来查询 (唯一索引是：二级索引+聚簇索引回源)

性能非常高
等值查询

2) ref

普通二级索引，列等值才是ref，无论是单列还是多列

查询速度也较快，先根据索引去筛选，再回源。
等值查询

3) ref or null

如果查询的时候，还带上了 or name is null，is not null，这种，则还要把null的值查出来，再回源
等值查询 加上null数据

4) range

利用索引作了范围查询，一般性能都ok，除非数据量超级大。
范围匹配

5) index

遍历二级索引，就可以拿到数据，不需要回源。

假设我们有一个表，里面完整的字段联合索引是KEY(x1,x2,x3)，好，现在我们写一个SQL语句是select x1,x2,x3 from table where x2=xxx，相信大多数同学看到这里，都会觉得，完蛋了，x2不是联合索引的最左侧的那个字段啊！

对的，这个SQL是没办法直接从联合索引的索引树的根节点开始二分查找，快速一层一层跳转的，那么他会怎么执行呢？不知道大家是否发现这个SQL里要查的几个字段，就是联合索引里的几个字段，巧了！

所以针对这种SQL，在实际查询的时候，就会直接遍历KEY(x1,x2,x3)这个联合索引的索引树的叶子节点，大家还记得聚簇索引和普通索引的叶子节点分别存放了什么吗？

聚簇索引的叶子节点放的是完整的数据页，里面包含完整的一行一行的数据，联合索引的叶子节点放的也是页，但是页里每一行就x1、x2、x3和主键的值！

所以此时针对这个SQL，会直接遍历KEY(x1,x2,x3)索引树的叶子节点的那些页，一个接一个的遍历，然后找到 x2=xxx 的那个数据，就把里面的x1，x2，x3三个字段的值直接提取出来就可以了！这个遍历二级索引的过程，要比遍历聚簇索引快多了，毕竟二级索引叶子节点就包含几个字段的值，比聚簇索引叶子节点小多了，所以速度也快！

也就是说，此时只要遍历一个KEY(x1,x2,x3)索引就可以了，不需要回源到聚簇索引去！针对这种只要遍历二级索引就可以拿到你想要的数据，而不需要回源到聚簇索引的访问方式，就叫做index访问方式！

6) all

all意思就是直接全表扫描，扫描你的聚簇索引的所有叶子节点，也就是一个表里一行一行数据去扫描，如果一个表就几百条数据那还好，如果是有几万条，或者几十万，几百万数据，全表扫描性能非常差。

7) 查询的类型总结

const、ref和range，本质都是基于索引树的二分查找和多层跳转来查询，所以性能一般都是很高的，然后接下来到index这块，速度就比上面三种要差一些了，因为他是走遍历二级索引树的叶子节点的方式来执行了，那肯定比基于索引树的二分查找要慢多了，但是还是比全表扫描好一些的。

8) 索引的交集 index_merge

select * from table where x1=xx and x2=xx，然后x1和x2两个字段分别都有一个索引，那么执行计划很可能对两个索引取交集，再回表，提升了性能

类似x1=xx or x2=xx的语句，也可能会用多个索引，只不过查多个大索引树之后，会取一个并集，而不是交集罢了。

在执行SQL语句的时候，有可能是会同时查多个索引树取个交集，再回表到聚簇索引的，这个可能性是有的

9) 多表关联查询

先从一个表里查一波数据，这个表叫做驱动表，再根据这波数据去另外一个表里去查一波数据进行关联，另外一个表叫做被驱动表

10) 多表关联查询 - 嵌套循环关联

多个表join，第一个驱动表，查出一波数据后，循环遍历，带着数据去第二个表再查一波数据。
第二个表的数据出来了和第一个表关联后，再循环遍历去下一个表

11) 执行计划的成本计算

成本分为2块

A. 数据页IO成本

数据从磁盘读取到内存，读一页的成本约定为1.0

B. 数据监测的CPU成本

验证是否符合搜索条件，排序分组等，一条数据的成本约定为0.2

C. 怎么找出多少页，多少行

show table status like “表名”，可以拿到表的统计信息，包括rows数据行、data_length聚簇索引的字节数大小，
data_length/1024得到kb单位大小，再除以16kb，得到多少页。
知道了页数和rows记录数就知道了全表扫描的成本

D. 全表扫描的成本计算

IO成本就是：数据页数量 1.0 + 微调值，CPU成本就是：行记录数 0.2 + 微调值，他们俩相加，就是一个总的成本值

比如你有数据页100个，记录数有2万条，此时总成本值大致就是100 + 4000 = 4100，在这个左右。

E. 走索引的成本

io 成本：二级索引一个范围区间算一个数据页成本为1.0
cpu 成本：二级索引内存里面估算数据量，每行数据cpu成本为0.2
回表的io成本和cpu成本
回表的io成本认为一条数据一个数据页成本为1.0
Cpu成本为记录数*0.2

范围区间 1.0 + 估算记录数(1+0.2+0.2)

F. 多表关联的成本

驱动表计算一波成本，数据带入第二个表，再计算一波成本。最后合计

12) 执行计划的优化

1) sql执行时的常量替换

他本质都是在优化SQL语句的清晰语义

要是MySQL觉得你的SQL里有很多括号，那么无关紧要的括号他会给你删除了，其次比如你有类似于i = 5 and j > i这样的SQL，就会改写为i = 5 and j > 5，做一个常量替换。

还有比如x = y and y = k and k = 3这样的SQL，都会给你优化成x = 3 and y = 3 and k = 3，本质也是做个常量替换。或者是类似于什么b = b and a = a这种一看就是乱写的SQL，一看就是没意义的，就直接给你删了。

还有改写的案例

select * from t1 join t2 on t1.x1=t2.x1 and t1.id=1

这个SQL明显是针对t1表的id主键进行了查询，同时还要跟t2表进行关联，其实这个SQL语句就可能在执行前就先查询t1表的id=1的数据，然后直接做一个替换，把SQL替换为：

select t1表中id=1的那行数据的各个字段的常量值, t2.* from t1 join t2 on t1表里x1字段的常量值=t2.x1

上面的SQL就是直接把t1相关的字段都替换成了提前查出来的id=1那行数据的字段常量值了。

2) 子查询的优化

A. 先查子查询的值再带入查询语句

select * from t1 where x1 = (select x1 from t2 where id=xxx)

会被拆分为两个步骤：第一个步骤先执行子查询，也就是：select x1 from t2 where id=xxx，直接根据主键定位出一条数据的x1字段的值。接着再执行select * from t1 where x1=子查询的结果值，这个SQL语句。

B. 先主查询执行，数据带入子查询验证是否匹配规则

select * from t1 where x1 = (select x1 from t2 where t1.x2=t2.x2)

C. 先查子查询的值优化

select * from t1 where x1 in (select x2 from t2 where x3=xxx)

先把子查询查询出来，数据写入临时表，也叫物化表，临时表依据大小，可能放内存，可能b+树放磁盘，同时给这个临时表增加索引。

同时会看临时表数据是否很小，如果很小，直接全表扫描临时表，带入主查询语句去匹配

D. 半连接优化

把子查询转换成半连接，但是要看场景确定是否可以优化

select * from t1 where x1 in (select x2 from t2 where x3=xxx)，此时其实可能会在底层把他转化为一个半连接，有点类似于下面的样子：

select t1.* from t1 semi join t2 on t1.x1=t2.x2 and t2.x3=xxx

5. Explain

1) explain结果

id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          
|+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+|  
1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |

id
每个select子句对应一个id

select_type 表示查询的类型
SIMPLE 单表查询或者是多表连接查询
primary 主查询
subquery 子查询
union union的查询
union_result union 去重结果
derived2 临时表、物化表

table 输出结果集的表

partitions 匹配的分区

type 表示表的连接类型
const 主键/唯一索引查询
ref 根据二级索引等值查询
eq_ref 主表的数据，作为子表的主键来关联查询
ref_or_null 除了等值查询还有null的数据
range 根据二级索引范围查询
index 对二级索引进行全索引扫描，直接扫描二级索引的叶子节点
index_merge 基于多个二级索引提取数据后进行合并
all 对聚簇索引进行全表扫描

possible_keys 可以被使用的索引

key 真实被使用的索引

key_len key选定索引后，最大索引长度

ref 索引列等值匹配类型const、ref、eq_ref、ref_or_null
你跟索引列等值匹配的是什么？
是等值匹配一个常量值？
还是等值匹配另外一个字段的值？
rows 扫描出的行数(估算的行数)
filtered 按表条件过滤的行百分比
比如rows =1000，filtered=10%,按条件过滤后=100条数据

Extra 执行情况的描述和说明
Using index 无需回表
Using index condition 先不回表查第一个，在回表条件筛选
Using where 全表扫描或者按索引匹配到数据后，where条件可以过滤数据
Using temporary 表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询， 常见 group by ; order by
Using filesort orderby没有索引，只能内存或磁盘上排序
Using join buffer 改值强调了在获取连接条件时没有使用索引
并且需要连接缓冲区来存储中间结果
如果出现了这个值
需要根据查询的具体情况可能需要添加索引来改进能。
No tables used Query语句中使用from dual 或不含任何from子句

2) 全表扫描案例

explain select * from t1
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 3457 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

select_type是SIMPLE，单表查询
table是t1
type是all，代表全表扫描直接会扫描表的聚簇索引的叶子节点，按顺序扫描过去拿到表里全部数据。
rows是3457，这说明全表扫描会扫描这个表的3457条数据，说明这个表里就有3457条数据，此时你全表扫描会全部扫描出来。
filtered是100%，这个也很简单了，你没有任何where过滤条件，所以直接筛选出来的数据就是表里数据的100%占比

3) 表join案例

explain select * from t1 join t2
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 3457 |   100.00 | NULL |
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4568 |   100.00 | Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+

执行计划分为了两条，也就是会访问两个表

针对第一个表就是t1，明显是先用ALL方式全表扫描他了，而且扫出了3457条数据

对第二个表的访问，也就是t2表，同样是全表扫描，因为他这种多表关联方式，基本上是笛卡尔积的效果

t1表的每条数据都会去t2表全表扫描所有4568条数据，跟t2表的每一条数据都会做一个关联

extra里说了是Nested Loop，也就是嵌套循环的访问方式，跟我们之前讲解的关联语句的执行原理都是匹配的。

上面两条执行计划的id都是1，是一样的，实际上一般来说，在执行计划里，一个SELECT会对应一个id，因为这两条执行计划对应的是一个SELECT语句，所以他们俩的id都是1，是一样。

4) 子查询案例

EXPLAIN SELECT * FROM t1 WHERE x1 IN (SELECT x1 FROM t2) OR x3 = 'xxxx';
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | t1    | NULL       | ALL   | index_x3      | NULL     | NULL    | NULL | 3457 |   100.00 | Using where |
|  2 | SUBQUERY    | t2    | NULL       | index | index_x1      | index_x1 | 507     | NULL | 4687 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+

第一条执行计划的id是1，第二条执行计划的id是2，这是为什么？
因为这个SQL里有两个SELECT，主查询SELECT的执行计划的id就是1
子查询SELECT的执行计划的id就是2

第一条执行计划里，select_type是PRIMARY，不是SIMPLE了，说明第一个执行计划的查询类型是主查询的意思，对主查询而言，他有一个where条件是x3=’xxx’，所以他的possible_keys里包含了index_x3，就是x3字段的索引，但是他的key实际是NULL，而且type是ALL，所以说他最后没选择用x3字段的索引，而是选择了全表扫描

这是为什么呢？其实很简单，可能他通过成本分析发现，使用x3字段的索引扫描xxx这个值，几乎就跟全表扫描差不多，可能x3这个字段的值几乎都是xxx，所以最后就选择还不如直接全表扫描呢。

第二条执行计划，他的select_type是SUBQUERY，也就是子查询，子查询针对的是t2这个表，当然子查询本身就是一个全表查询，但是对主查询而言，会使用x1 in 这个筛选条件，他这里type是index，说明使用了扫描index_x1这个x1字段的二级索引的方式，直接扫描x1字段的二级索引，来跟子查询的结果集做比对。

5) Union案例

EXPLAIN SELECT * FROM t1  UNION SELECT * FROM t2
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | t1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 3457 |   100.00 | NULL            |
|  2 | UNION        | t2         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4687 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+

两个SELECT子句对应两个id，就是分别从t1表和t2表里进行全表扫描

接着第三条执行计划是什么呢？其实union字句默认的作用是把两个结果集合并起来还会进行去重，所以第三条执行计划干的是个去重的活儿。

所以上面他的table是，这就是一个临时表的表名，而且你看他的extra里，有一个using temporary，也就是使用临时表的意思，他就是把结果集放到临时表里进行去重的，就这么个意思。当然，如果你用的是union all，那么就不会进行去重了。