Mysql实战原理思维导图模板_ProcessOn思维导图、流程图

存储引擎

innoDB

数据写入过程和原理

性能问题总结

为什么不直接更新磁盘数据？

每次请求都直接操作磁盘文件的话，会导致执行请求的性能极差。因为磁盘随机读写性能很差，必然导致数据库无法抗下任何高并发的场景。所以MYSQL才设计了如此复杂的一套机制，通过内存更新数据，然后写redo log以及事务提交，后台线程不停定时刷新内存里的数据到磁盘文件中。尤其在数据库里其实也用了os cache机制，在redo log顺序写入磁盘之前，先进入os cache, 就是操作系统管理的内存缓存中。通过这种方式保证性能，因为更新内存性能极高，顺序写磁盘性能也很高。远高于随机读写磁盘文件。通过这套机制，才能让我们的数据库能在较高配的机器上，每秒抗下几千的读写请求。

磁盘随机读

性能指标

IOPS

响应延迟

对于核心业务数据库的生产环境规划，推荐使用SSD固态硬盘，而不是机械硬盘，因为SSD固态硬盘的随机读写并发能力和响应延迟要比机械硬盘好得多，可大幅度提升数据库的QPS和性能

磁盘顺序写

性能指标

磁盘每秒读写多少数据量的吞吐量指标

延迟响应

加锁的问题

多线程并发访问这个buffer pool，都是访问内存的一些共享的数据结构，比如说缓存页，各种链表，所以必然要加锁。当线程拿到锁之后进行IO操作，就会比较耗时。

如果机器内存很大，就可以给Buffer Pool分配一个较大的内存，此时同时可以设置多个buffer Pool，提高数据库对并发场景的支撑能力。

核心组件

Buffer Pool

作用

缓存了磁盘的真实数据，我们的Java系统对数据库执行的增删改查操作，其实就是对这个内存数据结构种的缓存数据执行的。

初始化过程

数据库一启动，就会按照设置的Buffer Pool大小，稍微加大一点，去找操作系统申请一块内存区域，作为Buffer Pool的内存区域。

当内存区域申请完毕后，数据库就会按照默认缓存页的16KB大小以及对应的800个字节左右的描述数据大小，在Buffer Pool中划分出来一个个缓存页和一个个他们对应的描述数据。这时候他们都是空的。

等数据库运行起来之后，才会把数据对应的页从磁盘文件中读取粗来，放入Buffer Pool中的缓存页。

内置链表结构

free链表

特点

由Buffer Pool里面的描述数据块组成。

有两个指针，一个是free_pre，一个是free_next，分别指向上一个free链表的节点，以及下一个free链表的节点。

只有一个基础节点不属于Buffer Pool，是40字节大小的一个节点，里面存放了free链表的头节点的地址和尾节点的地址，还有free链表里当前有多少个节点。

用途

数据库为Buffer Pool设计了一个free链表，是一个双向链表数据结构，这个链表每个节点对应的是一个空闲的缓存页的描述数据块地址。数据库刚启动时，可能所有缓存页都是空闲的，因为此时可能是一个空的数据库，一条数据都没有，所以此时所有的缓存页描述数据块，都会被放入这个链表中。这个链表可以用来获取空闲的缓存页。

flush链表

结构类似free链表，凡是被修改过的缓存页（脏页），都会把他的描述数据放入flush链表中，后续都要flush到磁盘上去。

lru链表

特点

结构类似free链表

采取冷热分离的设计，最近被加载数据的缓存页，都会放到LRU链表的头部。

会被拆成两个部分，一部分是热数据，一部分是冷数据。这个冷数据比例由innodb_old_block_pct参数控制的，默认是37，也就是说冷数据占比37%。

第一次加载数据的缓存页，放在冷数据区的链表头部。调节innodb_old_blocks_time参数，默认值1000，也就是1000毫秒，可以让一个数据页在被加载到缓存页之后，指定时间1s后，再次访问这个缓存页（冷数据区停留超过1s），它会被挪动到热数据区域的链表头部。1s内访问缓存页，是不会把这个缓存页放入热数据区域的头部。

访问规则的优化

热数据区域里的缓存页可能是经常被访问的，频繁的移动会影响性能，其访问规则就被优化了，只有在热数据区域后3/4的缓存页被访问了，才会移动到链表头部去。前1/4的缓存页被访问，不会移动到链表头部。

用途

缓存数据的淘汰

当free链表中没有空闲缓存页却还需要加载新的数据页到缓存页时，可以淘汰一些缓存页。淘汰的办法就是把这个缓存页被修改的数据刷到磁盘中去。让它重新空闲出来。然后你就可以重新在这个缓存页中写入你需要的新数据页了。被淘汰的缓存页是那些最近访问最少的缓存页。也就是LRU（Least Recently Used）链表尾部的缓存页。

频繁被访问的缓存页被淘汰的场景

MySQL预读机制

触发条件

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

2.如果buffer pool里缓存了一个区里的13个连续的数据页，而且数据页都是比较频繁会被访问的，此时就会直接触发预读机制，把这个区的其他的数据页都加载到缓存里去。同时innodb_random_read_ahead参数，默认值OFF，这个规则是关闭的。

问题

主要是第一个条件可能触发预读机制，一下子把很多相邻数据页放到LRU链表最前面，它们其实不怎么会被访问，导致本来在缓存里的一些频繁被访问的缓存页在LRU链表的尾部。

全表扫描

页结构

表空间

表，列和行是逻辑上的概念。表空间，数据页是物理上的概念，在物理层面，表数据都放在一个表空间中，表空间由一堆磁盘上的数据文件组成的，

在表空间中包含了很多数据区，一组数据区是256个数据区，每个数据区包含了64个数据页，是1mb，表空间第一组数据区的第一个数据区的头三个数据页，是固定的，存放特殊信息的：
比如FSP_HDR数据页存放了表空间，和这一组数据区的一些属性
IBUF_BITMAP数据页存放了这一组数据页的所有insert buffer的信息
INODE数据页存放了一些特殊信息。

缓存页

缓存页哈希表

数据库中有个哈希表数据结构，可用表空间号+数据页号，作为一个key，然后缓存的地址作为value。通过查找这个哈希表结构，可以知道数据页是否有被缓存。

磁盘页读取到缓存页的过程

从free链表中获取一个描述数据块。然后对应获取到这个描述数据块对应的空闲缓存页。

把磁盘上的数据页读取到对应缓存页中，同时把其相关的一些描述数据写入缓存页的描述数据块里去。

把描述数据块从free链表中移除。

刷盘机制

有一个后台线程会运行一个定时任务，每隔一段时间就会把LRU链表的冷数据区域的尾部一些缓存页，刷回磁盘，清空这几个缓存页，让他们回到free链表中。

在这个后台线程不怎么繁忙的时候，找个时间把flush链表中缓存页都刷入磁盘。同时将其从flush链表和lru链表中移除，然后加入到free链表中去。

如果实在没有空闲缓存页了，从LRU链表冷数据区的尾部找到一些最不经常使用的缓存页，刷入磁盘和清空。然后把数据页加载到腾出来的空闲缓存页中。

缺点

频繁刷盘影响数据库性能

优化策略

合理设置buffer pool大小，数量，以应对高并发的场景。

描述信息/控制数据/缓存页的元数据

包含这个数据页所属的表空间，数据页的编号，这个缓存页在Buffer Pool中的地址以及别的一些信息。

每个缓存页，实际上都会有一个描述信息，这个描述信息是用来描述这个缓存页的。

描述信息本身也是一块数据，在buffer pool中，每个缓存页的描述数据在最前面

数据页

特性

默认大小16KB

数据页包括了文件头，数据头，最小记录和最大记录，多个数据行，空闲空间，数据页目录，文件尾部。

数据页在磁盘中可能是二进制或者别的特殊格式的数据。包含两个指针，一个指向自己上一个数据页的物理地址，一个指针指向自己下一个数据页的物理地址，组成了双向链表。

数据页里面的每行数据会按照主键大小进行排序存储，同时每一行数据都有指针指向下一行数据的位置，组成单项链表。

数据页目录存放了各行数据的主键值和行的实际物理位置。

页分裂

在增加一个新的数据页的时候，如果你的主键是自己设置的，实际上会把前一个数据页里主键值较大的，挪动到新的数据页里面，然后把新插入的主键值较小的挪动到上一个数据页里去，保证新数据页的主键值一定比上一个数据页里主键值大。

行存储格式

指定行存储格式

ROW_FORMAT=COMPACT

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

变长字段的长度列表

（与实际字段位置相比较）逆叙的十六进制数字

null值列表

用二进制bit位（0或者1，其中0表示null，1表示非null）逆叙表示字段是否是null的列表，不够八位会在前面补0。

数据头由40个bit位构成，
作用是描述这行数据

2

第一位和第二位是预留位，没有任何含义。

1

delete_mask

标识是否被删除

1

min_rec_mask

4

n_owned

13

heap_no

当前这行数据在堆中的位置

3

record_type

数据类型：1.代表B+树非叶子节点 2.最小值数据 3.最大值数据

16

next_record

指向它下一个数据的指针

存储时，数据会根据数据库指定的字符集编码，进行编码后存储。

0x09 0x04 00000101 0000000000000000000000000000000000011001 616161 636320 6262626262

在实际存储一行数据时，会在它的真实数据部分，加入一些隐藏字段。
举例：
0x09 0x04 00000101 0000000000000000000000000000000000011001 616161(DB_ROW_ID) 636320(DB_TRX_ID) 6262626262(DB_ROL_PTR)

DB_ROW_ID

行的唯一标识，数据库内部给的一个标识，不同于主键ID字段，当我们没有指定
主键和unique key唯一索引的时候，内部自动加一个ROW_ID作为主键。

DB_TRX_ID

事务ID

DB_ROLL_PTR

回滚指针，用来进行事务回滚的。

行溢出的问题

行溢出是指一行的数据远超过一个数据页的大小。数据页大小为16KB，如果有个表字段为VARCHAR(65532)，可存储的数据就远大于数据页大小。当这样的值在这个数据页存储时，仅仅会存储它的部分数据，同时包含一个20个字节的指针。指向其他存储了剩余数据的数据页，这些数据页间用链表串联起来。

索引页

见索引相关知识点

Buffer Pool大小设置

chunk机制

chunk机制的说明

buffer pool是由很多chunk组成的，它的大小由参数innodb_buffer_pool_chunk_size参数控制，默认是128MB

chunk由一系列的的描述数据块和缓存页构成，每个buffer pool共用一套free，flush，lru链表。

buffer pool内存大小应该设置为机器的50%~60%

buffer pool总大小 = chunk大小 * 每个buffer pool包含的chunk数量 * buffer pool数量

动态调整buffer pool大小

只要申请一系列连续128MB内存的chunk即可动态增加buffer pool大小

推荐大小设置

默认是128MB，偏小，实际生产环境可对其进行调整，16C32G的机器，可以给分配个2GB的内存。

[Server]
innodb_buffer_pool_size=2147483648

碎片问题

问题描述

Buffer Pool中描述数据大概相当于缓存页5%左右，也就是说每个描述数据大概是800个字节左右的大小，假设你设置的buffer pool大小是128MB，实际上Buffer Pool真正的最终大小会超一些，可能有130多MB的样子，因为它里面还要存放每个缓存页的描述数据。

Buffer Pool大小自定义，如果Buffer Pool划分完全部缓存页和描述数据块后，还剩一点内存，放不下任何一个缓存页，就放着不能用，就产生了内存碎片。

减少碎片的方法

划分缓存页时，应让所有缓存页和描述数据块都紧密的挨在一起，这样尽可能减少内存浪费。就可减少碎片产生了。

相关参数查看

show variables like '%relay%'

后台线程

page cleaner thread

Page Cleaner Thread是在innodb 1.2x版本中引入的，其作用是将之前版本中脏页的刷新操作都放入单独的线程中完成。

需要检查LRU列表中是否有足够的可用空间操作，发生在用户查询过程中。

如果没有可用的空闲缓存页，会把LRU链表尾端的页移除

purge thread

IO thread

Master thread

查看innodb整体运行状态

Show engine innodb status

底层原理分析

MYSQL是用编程语言写的一套数据库管理软件，底层就是磁盘存储数据，基于内存提升数据读写性能，并设计了复杂的模型，帮助我们高效存储和管理数据。它在运行过程中，需要使用CPU，内存，磁盘和网卡这些硬件，但是不能直接使用，而是通过操作系统提供的接口，依托于操作系统来使用和运行，然后linux操作系统负责操作底层的硬件。

存储硬件

RAID存储架构

磁盘冗余技术

RAID是一个磁盘冗余阵列，在存储层面，机器里往往有多块磁盘，然后引入RAID这个技术，这时一种磁盘阵列技术，可以帮助我们选择磁盘写入，或者进行数据读取。还能实现数据冗余机制，对数据进行备份。RAID有软件层面的东西，也有硬件层面的东西，比如RAID卡这种设备。还可以分成不同的技术方案，比如RAID 0, RAID 1, RAID 0 + 1, RAID 2

电池充放电原理

服务器使用RAID阵列时，一般会有个RAID卡，RAID卡是带有一个缓存的，这个缓存是一种跟内存类似的SDRAM，我们可以把RAID的缓存模式设置为write back,这样写入磁盘阵列的数据，会先缓存在RAID卡的缓存里，然后再慢慢写入磁盘阵列里，这种写缓冲机制，可以大幅度提升我们的数据库磁盘写的性能。

为了防止出现突然宕机，RAID卡缓存里的数据突然丢失的问题，RAID卡一般都配置有独立的锂电池或者电容，如果服务器突然掉电了，RAID卡自己基于锂电池来供电运行的，然后他会赶紧把缓存里的数据写入到阵列的磁盘上去。

由于锂电池存在性能衰减问题，所以一般来说，锂电池需要配置定时充放电，每隔30~90天自动对锂电池充放电一次，可以延长锂电池的寿命和校准电池容量。电池充放电的过程中，RAID缓存级别会从write back变成write through，通过RAID写数据时，IO就直接写磁盘了，如果写内存的话，性能是0.1ms这个级别，如果直接写磁盘，性能就退化10倍到毫秒级别了。

对于那些在生产环境使用了RAID多磁盘阵列存储技术的公司来讲，通常会开启RAID卡缓存机制，此时就一定要注意RAID锂电池自动充放电的问题，只要用了RAID缓存机制，那么锂电池就必然会定时进行充放电延长寿命，这个就会导致RAID存储定期的性能出现几十倍的抖动。间接导致数据库每隔一段时间出现性能几十倍的抖动。

RAID技术方案

RAID 0

很多磁盘组成一个阵列，所有的数据分散写入不同磁盘，因为有有多块磁盘，磁盘阵列整体容量很大，同时写入多块磁盘，磁盘的并发能力很强。

缺点

磁盘坏了一块，就会丢失一部分数据。

RAID 1

两块磁盘互为镜像关系，写的所有数据在两块磁盘上都有，形成了数据冗余，一块磁盘坏了，另一块上还有数据。而且一块磁盘如果压力很大，可以让读请求路由到另外一块磁盘上去，分担压力，反正他两都是数据冗余的，是一样的。

RAID 10

RAID 0 + RAID 1组合起来，比如6块磁盘组成一个RAID 10阵列，每2块磁盘组成一个RAID 1互为镜像的架构，存放的数据都是冗余的，一共有3组RAID 1, 然后对于每一组RAID 1写入数据的时候，用RAID 0的思路，不同的磁盘数据不一样，但是同一组内的两块磁盘数据是冗余一致的。

Linux操作系统的存储系统

VFS层

文件系统层

NFS文件系统

Ext2文件系统

Ext3文件系统

Page Cache缓存层

通用Block层

IO调度层

CFQ公平调度算法

deadline IO调度算法

Block设备驱动层

Block设备层

锁机制

行锁

形态

更新一行数据必须把他所在的数据页从磁盘文件读取到缓存中才能更新，所以此时这行数据和关联的锁数据结构，都是在内存里面的。

类型

独占锁(X锁/exclude锁)

锁里包含了trx_id和等待状态（true/false）

A事务需要更新一行数据，给这行数据加了锁，B此时如果B事务也想更新数据时，会检查这行数据有没有被加锁，发现有锁后，此时B事务也会生成一个锁数据结构，里面有他的trx_id和锁等待状态（true），等事务A更新完数据后，会唤醒事务B继续执行，此时B事务就可以获取到锁了。

查询操作加互斥锁

select * from table for update.

共享锁(S锁)

语法

select * from table lock in share mode

互斥性

锁类型独占锁共享锁
独占锁互斥互斥
共享锁互斥不互斥

实用性

一般不会在数据库层面做复杂的手动加锁操作，反而会用基于redis/zookeeper的分布式锁来控制业务系统的锁逻辑。因为在SQL语句中加共享锁或者独占锁，会导致java业务系统层面不好维护。

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

表锁

类型

意向独占锁

LOCK TABLES xxx READ

意向共享锁

LOCK TABLES xxx WRITE

互斥性

锁机制独占锁意向独占锁共享锁意向共享锁
独占锁互斥不互斥互斥互斥
意向独占锁互斥不互斥互斥不互斥
共享锁互斥互斥不互斥不互斥
意向共享锁互斥不互斥不互斥不互斥

元数据锁/Metadata Locks

执行DDL时，会阻塞增删改操作，执行增删改操作时，会阻塞DDL操作。

高可用架构

主从复制架构

特点

让主节点复制数据到从节点，保证主从数据时一致的，万一主节点宕机，可以让java业务系统连接到从节点执行SQL语句，写入查询数据，

生产问题

主从节点数据不一致的问题：从节点数据通常落后一些。

主节点宕机，要能自动切换从节点对外服务，也需要一些中间件的支持。

运用

可以挂一个从库，专门用来跑一些报表SQL语句，那种SQL语句往往是上百行之多，运行要好几秒，所以专门给一个库来跑。

专门部署一个从库，进行数据同步之类的操作。

读写分离架构

特点

依赖于主从复制架构

主节点写入数据，从节点查询数据，读写操作分离到两台Mysql服务器上去。一台专门写入数据，再复制数据到从节点。另一台服务器专门查询数据。

运用

SQL执行组件

SQL接口

执行增删改查的SQL语句

查询解析器

对SQL语句进行解析，比如拆解啥的

查询优化器

选择最优的查询路径

执行器

执行器会根据我们的优化器生成的一套执行计划，不停调用存储引擎的各种接口去完成sql语句的执行计划。

存储引擎

InnoDB

MyISAM

Memory

压测

性能指标

能抗住的请求

QPS

每秒可处理的请求

TPS

每秒可处理的事务量

IO相关的压测性能指标

IOPS

机器随机IO并发处理的能力，指底层存储系统每秒可以支持多少次磁盘读写操作。

吞吐量

机器的磁盘存储每秒可以读写多少个字节的数据量

latency

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

CPU负载

网络负载

内存负载

压测工具

sysbench

参数解析

--db-driver=mysql

基于mysql驱动链接mysql数据库。

--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这样

读写模式

oltp_read_write

测试数据库的读写性能

oltp_read_only

测试数据库的只读性能

oltp_delete

测试数据库的删除性能

oltp_update_index

测试数据库更新索引字段的性能

oltp_update_non_index

测试数据库更新非索引字段的性能

oltp_insert

测试数据库的插入性能

oltp_write_only

测试数据库写入性能

--db-ps-mode=disable

禁止ps模式

命令

prepare

准备压测数据

run

运行压测

cleanup

清理数据

压测结果分析

thds

有xx个线程在压测

tps

每秒执行了xx个事务

qps

每秒执行了xx个请求，(r/w/o:xx/xx/xx)，在每秒xx个请求中，有xx个请求是读请求，xx个请求是写请求，xx个请求是其他的请求。

lat(ms，95%)

95%的请求延迟都在xx毫秒以下

err/s:0.00 reconn/s:0.00

每秒有0个请求是失败的，发生了0次网络重连

CPU负载情况分析

top命令

top - 15:52:00 up 42:35, 1 user, load average: 0.15, 0.05, 0.01

15:52:00 指当前时间，up 42:35 指机器已经运行了多长时间， 1 user是指当前机器有1个用户在使用，load average: 0.15, 0.05, 0.01说的时cpu在1分钟，5分钟，15分钟内的负载情况，代表有几核是占用了的

内存负载情况分析

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

当前机器的使用情况，总内存32GB, 已使用20GB左右内存, 10多GB内存是空闲的，就大概300MB的内存用作OS内存的缓冲区了。

磁盘IO情况分析

dstat -d命令

-dsk/total-
read writ
103k 211k
0 11k

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

dstat -r命令

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

读IOPS和写IOPS分别是多少，也就是说随机磁盘读取每秒钟多少次，随机磁盘写入每秒钟执行多少次，大概就是这个意思，一般来说，随机磁盘读写每秒在两三百次都是可以承受的。在压测时，要密切观察机器的磁盘io情况，如果磁盘IO吞吐量已经太高了，都达到极限的每秒上百MB，或者随机磁盘读写每秒都达到极限的两三百次了，此时就不要继续增加线程数量了，否则磁盘IO负载就太高了。

网卡流量情况分析

dstat -n命令

-net/total-
recv send
16k 17k

每秒钟网卡接收到流量有多少kb，每秒钟通过网卡发送出去流量有多少kb，通常来说，如果你的机器使用的是千兆网卡，那么每秒钟网卡总流量也就在100MB左右，甚至更低一点。

压测的原则

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

优化实战案例

RAID锂电池充放电导致性能抖动问题的解决方案

给RAID卡把锂电池换成电容，电容式不用频繁充放电的，不会导致充放电的性能抖动，还有电容可以支持透明充放电，就是自动检查电量，自动进行充电，不会说在充放电的时候让写IO直接走磁盘，但是更换电容很麻烦，而且电容比较容易老化，这个其实一般不常用

手动充放电，这个比较常用，包括一些大家知道的顶尖互联网大厂的数据库服务器的RAID就是用了这个方案避免性能抖动，就是关闭RAID自动充放电，然后写一个脚本，脚本每隔一段时间自动在晚上凌晨的业务低峰时期，脚本手动触发充放电，这样可以避免业务高峰期的时候RAID自动充放电引起性能抖动

充放电的时候不要关闭write back，就是设置一下，锂电池充放电的时候不要把缓存级别从write back修改为write through，这个也是可以做到的，可以和第二个策略配合起来使用

数据库无法连接故障的定位（Too many connections）

my.cnf 关键参数max_connections，表示MYSQL能建立的最大连接数

show variables like 'max_connections' 当前mysql建立的连接数

检查Mysql启动日志

查看linux的文件句柄数量。句柄数量会影响MYSQL最大连接数。

cat /etc/security/limits.conf
cat /etc/rc.local
ulimit -HSn 65535

总结: Linux默认会限制你每个进程对机器资源的使用，包括很多参数，比如

可以打开的文件句柄的限制

open files

可以打开的子进程数的限制

max user processes

网络缓存的限制

最大可以锁定的内存大小

max locked memory

进程崩溃时转储文件大小限制

core file size

数据（脏页/redo log）刷盘造成的性能抖动问题

原因

执行一个查询语句的时候，需要查询大量数据到缓存页中，为了腾出足够内存，触发内存里大量缓存页被淘汰刷入磁盘。

redo log日志文件写满了，重新从第一个日志文件开始写的时候，判断是否你第一个日志文件里的一些redo log对应之前更新过的缓存页，迄今为止都没刷入过磁盘，此时必然要把那些马上要被覆盖的redo log更新的缓存页都刷入磁盘。

解决方法

尽量减少缓存页flush到磁盘的频率。

尽量提升缓存页flush到磁盘的速度。

采用SSD固态硬盘。

随机io性能非常好

配置参数innodb_io_capacity

这个参数告诉数据库采用多大的IO速率把缓存页flush到磁盘中，合理配置可以使SSD固态硬盘随机IO性能最大化。

可以用fio工具进行测试。

实际刷盘时，按照innodb_ip_capacity乘以一个百分比进行刷盘，这个百分比就时脏页的比例，是innodb_max_dirty_pages_pct参数控制的，默认是75%，这个一般会参考你的redo log日志来计算。

innodb_flush_neighboors

如果采用的是SSD固态硬盘，没有必要让他同时刷临近的缓存页，设置为0时，禁止刷临近的缓存页。这样就把每次刷新的缓存页数量降低到最少了。

选错索引带来的慢查询问题

问题描述

数据库突然涌现大量慢查询，由于选错索引，以至于每个数据库连接执行慢查询耗费很久，数据库就会开辟更多的连接，而且每个连接都会执行一个慢查询，最终数据库连接全部打满。没法开辟新的连接。导致有持续的新查询发送过来时，数据库没法处理，很多查询发到数据库直接阻塞就超时了，这也导致线上频繁报警，出现大量数据库查询超时的错的异常。

解决方法

使用force index语法选择正确的索引

其他影响查询速度的因素

原因

有些慢查询不一定时SQL导致的，有可能时磁盘，网络以及CPU负载打满导致的。

排查工具

MYSQL profiling工具

set profiling = 1

执行show profiles命令，查看query id

使用show profile cpu, block io for query xx(xx表示query id)查看profiling信息

也可以查看sql语句执行时候的各种耗时，比如磁盘io耗时，cpu等待耗时，发送数据耗时，拷贝数据到临时表的耗时，等等。

分析最耗时的部分，各个击破。

事务

多个事务并发带来的问题

脏读

脏写

不可重复读

幻读

事务的隔离级别

read uncommited

读未提交

read committed

读已提交

repeatable read

可重复读

serializable

串行化

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level

mvcc多版本并发隔离机制

Isolation.READ_COMMITTED

Isolation.READ_UNCOMMITTED

Isolation.REPEATABLE_READ

Isolation.SERIALIZABLE

mvcc中的ReadView机制

作用

保证更新操作不影响查询操作

关键点

m_ids

此时有哪些事务在MYSQL里执行还没有提交的。

min_trx_id

m_ids里面最小的值，未提交事务最小id

max_trx_id

mysql下一个要生成的事务id，就是最大事务id

creator_trx_id

你这个事务的id

机制

开始一个查询的时候，便会创建一个ReadView视图，可以保证你读到你事务开启前，别的提交事务更新的值，还有自己事务更新的值。别的事务正在运行，然后你的事务开启之后，别的事务更新了的值，你是读不到的。或者是你事务开启了之后，比你晚开启的事务更新了的值，你也是读不到的。就解决了幻读和不可重复读的问题。整个过程是通过ReadView+undo log日志链条的机制实现的。

判断规则

从数据的最早版本开始判断（undo log）

数据版本trx_id = creator_trx_id，当前事务修改，可以访问。

数据版本trx_id < min_trx_id，这个版本在生成readview已经提交，可以访问。

数据版本trx_id > max_trx_id，这个版本是生成read_view之后才开启的事务建立的，不能访问。

数据版本trx_id在min_trx_id和max_trx_id之间，看是否在m_ids中，不过在，不可以访问，不在则可以访问，

如果当前版本不可见，如果不在，可以。

Read Committed

每次查询都生成新的ReadView

SQL执行与优化

索引

主键索引

特点

针对主键的索引其实就是主键目录，把每个数据页的页号，还有数据页最小的键值放在一起，组成一个索引目录，即索引页。

索引页里保存了下级索引页的页号和最小主键值。通过二分查找可以找到下级索引页。如果最下层的索引页的页号太多，可以再次分裂，再加一层索引页。

B+树结构

从小到大排序

聚簇索引/一级索引

特点

索引页+数据页组成的B+树

同层级的索引页，互相之间基于指针组成双向链表。

最下层的索引页，有指针引用数据页。即在大的B+树索引数据结构里，叶子节点是数据页本身。

当数据页开始分裂时，会维护上层索引数据结构，在上层索引页里维护索引条目，不同数据页和最小键值。

当数据页越来越多，一个索引页放不下，就会拉出新的索引页，同时加一个上层索引页，上层索引页存放的条目时下层索引页页号和最下主键值。

数据量越大，索引页层级越多，一般索引页可以放很多索引条目，所以通常而言，即使是亿级大表，基本上大表建的索引层级也就三四层。

主键以外字段索引/二级索引

特点

独立于聚簇索引之外的另一个B+树结构

从小到大排序，下一个数据页索引字段值大于上一个数据页索引字段值

叶子节点仅仅存放主键和索引字段的值

回表

在索引页，根据索引字段找到数据后，还需要通过主键信息在聚簇索引里从根节点开始查找，一路找到叶子节点的数据页，才能定位到主键对应的完整数据行。

联合索引

特点

原理同二级索引，只不过排序的顺序是从靠左边的索引字段开始排序。

查找过程中遇到范围查找的时候，后面的索引字段无法排序。因此设计索引的时候，要把需要做范围查询的索引字段放在最右边。

匹配规则

等值匹配规则

最左侧匹配规则

从索引最左侧值开始匹配，不能跳过左边索引字段直接匹配右边索引字段。

最左前缀匹配规则

可以用like 'xxx%'的方式匹配

范围查找规则

可以用select * from table where x1 > xx and x1 < yy这种方式匹配

等值匹配+范围匹配规则

排序/分组走索引提升查询速度

order by 后面字段顺序和联合索引从最左侧开始字段一致。

group by 后面字段顺序和联合索引从最左侧开始字段一致。

索引是否越多越好

索引太多，也许查询速度可以提高，但增删改速度比较差。占用磁盘空间也越多，所以索引并不是越多越好。

覆盖索引

select 后面跟的字段，即查询的字段，在索引树中，不需要回表去聚簇索引中找其他字段了。

索引设计小技巧

java代码开发之后再考虑设计索引

针对where条件，order by条件，group by条件，join条件on连接字段，去设计索引

尽量使用基数较大的字段（也可以称为散列性高），就是值比较多的字段建立索引，那样才能发挥出B+树快速二分查找的优势来

对那些字段的类型比较小的列来设计索引，这样占用的磁盘空间小，搜索性能好

如果某个较大的字段要建立索引，又不想索引树占太多磁盘空间，可以考虑用这个字段的前20个字符前缀建立索引

避免在索引字段里套函数，进行计算

索引不要设计太多，建议两三个联合索引就应该覆盖掉某个表的全部查询

主键一定要是自增的，别用UUID之类的

设计低基数字段索引时，可以加个复杂辅助字段。

索引失效小技巧

索引列上使用函数（replace/substr/concat/sum/count/avg）表达式

字符串不加引号，出现隐式转换

like条件中前面带%

执行计划

检索方式

const

通过聚簇索引或者唯一二级索引（即unique key）+聚簇索引回表快速查询数据的过程

性能超高，速度很快，常量级的

ref

通过普通二级非唯一索引（如果是联合索引，须从索引最左侧开始连续多个列都是等值比较才属于ref）+聚簇索引回表快速查询数据的过程

普通索引，查询条件里面包含is null或者is not null

速度也很快

ref_or_null

select xx from table where yy = ii or yy is null

range

查询条件中，范围查询用到了索引字段

index

遍历二级索引就能拿到想要的数据，而不需要回表查询

all

全表扫描

根据双向链表依次把磁盘上的数据页加载到缓存页里去。然后在缓存页内部查找那条数据

效率很低

eq_ref

在连接查询时，被驱动表如果基于主键进行等值匹配

index_merge

基于多个索引提取数据后进行合并

使用多个索引的硬性要求

如果有联合索引，必须把联合索引里面每个字段都放SQL里，而且必须是等值匹配。

通过主键+其他二级索引等值匹配，也有可能做一个多索引查询和交集。

多表关联查询/结果集聚合

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

inner join

两个表里的数据必须是完全能关联上的，才能返回回来，这就是内连接。

outer join

outer join分为左外连接和右外连接，左外连接的意思是，在左侧的表里面的某条数据，如果在右侧的表里关联不到任何数据，就得把左侧表这个数据返回回来，右外连接则反之。在右侧表里如果关联不到左侧表里的任何数据，就得把右侧表里面的数据返回出来。

nested-loop join

嵌套循环关联就是从驱动表里面查出来一波数据后，对每一条数据循环一次去驱动表里查询数据。

semi join

实际并没有提供semi join这种语法，这是MYSQL内核里面使用的一种方式，和IN语句+子查询语义完全一样。（章节99）

union

把两个结果集合合并起来并进行去重

union all

把两个结果集合合并起来不用进行去重

执行计划

执行成本评估

成本分析

把数据页一页页从磁盘读到内存的是IO成本，读一页的成本是1.0

IO的成本是：数据页数量*1.0 + 微调值

对数据做运算，比如验证是否符合搜索条件，做排序分组，消耗CPU资源时，属于CPU成本，一般读取和检验一条数据是否符合条件的成本是0.2

行记录数*0.2 + 微调值

show table status like "表名"

rows

表里的记录数

data_length

表的聚簇索引的字节数大小，data_length/1024就是kb为单位的大小，再除以16kb（默认一页的大小），就得到了数据页的数量，可以计算全表扫描的成本了。

SQL改写

常量替换

比如i = 5 and j>i 改成 j>5

去除多余括号

去除无意义的sql

比如 b = b and a = a

关键词

物化表

物化表可能会基于memory存储引擎来通过内存存放，如果结果集太大，则可能通过普通b+树聚簇索引的方式存在磁盘中，这个物化表会建立索引。

explain

查看SQL执行计划

id

执行计划的id

select_type

查询类型

SIMPLE

PRIMARY

主查询

SUBQUERY

子查询

DEPENDENT SUBQUERY

DEPENDENT UNION

UNION RESULT

derived

针对子查询的结果会物化成一个内部临时表，然后外层查询时针对这个临时表的物化表执行的

MATERIALIZED

物化表

table

查询的表名

partitions

表分区的概念

type

就是上面的检索方式

possible_keys

和type结合起来，可提供选择的索引

key

实际选择的索引

key_len

当key里选择使用某个索引后，那个索引里的最大值长度

ref

使用某个字段的索引进行等值匹配搜索的时候，跟索引列进行等值匹配的那个目标值的一些信息

rows

预估通过索引或者别的方式访问这个表的时候，大概会读取到的数据量。

filtered

经过搜索条件过来之后剩余数据的百分比

extra

一些额外的信息

nested loop

using temporary

使用临时表

using where

using index condition

using index

using filesort

基于磁盘文件排序，性能很差。如果我们用order by, group by, union, distinct之类的语法，如果没法直接利用索引进行，那么会基于临时表完成，会有大量的磁盘操作，性能非常低。

其他知识点

join buffer

一种内存技术，在内存里做一些优化，减少全表扫描次数。

show warnings

查看警告信息

SET optimizer_switch='semijoin=off'

关闭半连接优化

生产配置

8C16G

每秒可以抗一两千并发请求

16C32G

每秒可以抗两三千，甚至三四千并发请求

4C8G

每秒差不多可以抗五百请求

启动选项和系统变量

启动选项

命令行

命令行启动选项的通用格式

--启动选项1[=值1] --启动选项2[=值2] ... --启动选项n[=值n]

举例

mysqld --skip-networking

在启动服务器程序的时候，禁止各客户端使用TCP/IP网络进行通信

mysqld --default-storage-engine=MyISAM

如果在创建表的语句中没有显式指定表的存储引擎的话，那就会默认使用InnoDB作为表的存储引擎。如果我们想改变表的默认存储引擎的话，可以这样写启动服务器的命令行

选项的长形式和短形式

长形式短形式含义
--host -h 主机名
--user -u 用户名
--password -p 密码
--port -P 端口
--version -V 版本信息

举例

mysqld -P 3307

mysqld -P3307

配置文件

推荐原因

把需要设置的启动选项都写在这个配置文件中，每次启动服务器的时候都从这个文件里加载相应的启动选项。

由于这个配置文件可以长久的保存在计算机的硬盘里，所以只需我们配置一次，以后就都不用显式的把启动选项都写在启动命令行中了，所以我们推荐使用配置文件的方式来设置启动选项。

类Unix操作系统中的配置文件

在类UNIX操作系统中，MySQL会按照这些路径来寻找配置文件

路径名备注
/etc/my.cnf
/etc/mysql/my.cnf
SYSCONFDIR/my.cnf
$MYSQL_HOME/my.cnf 特定于服务器的选项（仅限服务器）
defaults-extra-file 命令行指定的额外配置文件路径
~/.my.cnf 用户特定选项
~/.mylogin.cnf 用户特定的登录路径选项（仅限客户端）

变量说明

使用CMake构建MySQL时使用SYSCONFDIR选项指定的目录。默认情况下，这是位于编译安装目录下的etc目录。

MYSQL_HOME是一个环境变量，代表一个路径，我们可以在该路径下创建一个my.cnf配置文件，那么这个配置文件中只能放置关于启动服务器程序相关的选项

以~开头的路径是用户相关的，类UNIX 系统中都有一个当前登陆用户的概念，每个用户都可以有一个用户目录，~就代表这个用户目录，大家可以查看HOME环境变量的值来确定一下当前用户的用户目录

我们在启动程序时可以通过指定defaults-extra-file参数的值来添加额外的配置文件路径

mysqld --defaults-extra-file=/Users/xiaohaizi/my.cnf

格式

配置文件中的启动选项被划分为若干个组，每个组有一个组名，用中括号[]扩起来

组名分别是server、mysqld、mysqld_safe、client、mysql、mysqladmin，每个组下边可以定义若干个启动选项，我们以[server]组为例来看一下填写启动选项的形式（其他组中启动选项的形式是一样的）

配置文件中不同的选项组是给不同的启动命令使用的，如果选项组名称与程序名称相同，则组中的选项将专门应用于该程序。例如， [mysqld]和[mysql]组分别应用于mysqld服务器程序和mysql客户端程序。

不过有两个选项组比较特别

[server]组下边的启动选项将作用于所有的服务器程序。

[client]组下边的启动选项将作用于所有的客户端程序。

mysqld_safe和mysql.server这两个程序在启动时都会读取[mysqld]选项组中的内容。

同一个配置文件中多个组的优先级

同一个命令可以访问配置文件中的多个组，比如mysqld可以访问[mysqld]、[server]组，如果在同一个配置文件中，比如~/.my.cnf，在这些组里出现了同样的配置项，那么，将以最后一个出现的组中的启动选项为准

defaults-file的使用

如果我们不想让MySQL到默认的路径下搜索配置文件（就是上表中列出的那些），可以在命令行指定defaults-file选项

mysqld --defaults-file=/tmp/myconfig.txt

这样，在程序启动的时候将只在/tmp/myconfig.txt路径下搜索配置文件。如果文件不存在或无法访问，则会发生错误。

系统变量

查看系统变量

SHOW VARIABLES [LIKE 匹配的模式];

设置系统变量

通过命令行添加启动选项。

mysqld --default-storage-engine=MyISAM --max-connections=10

通过配置文件添加启动选项。

[server]
default-storage-engine=MyISAM
max-connections=10

作用范围

GLOBAL

全局变量，影响服务器的整体操作。

SESSION

会话变量，影响某个客户端连接的操作。（注：SESSION有个别名叫LOCAL）

状态变量

定义

为了让我们更好的了解服务器程序的运行情况，MySQL服务器程序中维护了好多关于程序运行状态的变量，它们被称为状态变量

举例

Threads_connected

表示当前有多少客户端与服务器建立了连接

Handler_update

表示已经更新了多少行记录

说明

由于状态变量是用来显示服务器程序运行状况的，所以它们的值只能由服务器程序自己来设置，我们程序员是不能设置的

与系统变量类似，状态变量也有GLOBAL和SESSION两个作用范围的，所以查看状态变量的语句可以这么写：SHOW [GLOBAL|SESSION] STATUS [LIKE 匹配的模式];