首页  思维导图  详情

Mysql实战原理

2021-03-15 20:13:55   1144  举报





AI智能生成

为你推荐

查看更多



MySQL

Mysql

MySQL

MySQL实战原理主要涉及数据库的安装、配置、操作和管理。首先，需要安装MySQL服务器和客户端软件，然后通过配置文件（如my.cnf）进行参数设置，以满足实际需求。在操作方面，可以使用SQL语句进行数据的增删改查，同时利用存储过程、触发器等高级功能实现复杂的业务逻辑。此外，还需要掌握数据库的备份与恢复、性能优化、安全管理等方面的知识，以确保数据库的稳定运行。通过学习和实践MySQL实战原理，可以更好地理解数据库的工作原理，提高数据处理和管理能力。

mysql

作者其他创作

大纲/内容

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

IOPS

响应延迟

性能指标

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

磁盘随机读

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

延迟响应

磁盘顺序写

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

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

加锁的问题

性能问题总结

数据写入过程和原理

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

作用

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

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

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

初始化过程

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

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

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

特点

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

用途

free链表

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

flush链表

结构类似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）链表尾部的缓存页。

缓存数据的淘汰

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

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

触发条件

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

问题

MySQL预读机制

全表扫描

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

lru链表

内置链表结构

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

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

表空间

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

缓存页哈希表

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

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

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

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

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

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

频繁刷盘影响数据库性能

缺点

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

优化策略

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

刷盘机制

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

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

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

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

缓存页

默认大小16KB

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

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

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

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

特性

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

页分裂

ROW_FORMAT=COMPACT

指定行存储格式

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

变长字段的长度列表

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

null值列表

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

标识是否被删除

delete_mask

min_rec_mask

n_owned

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

heap_no

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

record_type

指向它下一个数据的指针

next_record

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

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

0x09 0x04 00000101 0000000000000000000000000000000000011001 616161 636320 6262626262

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

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

DB_ROW_ID

事务ID

DB_TRX_ID

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

DB_ROLL_PTR

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

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

行溢出的问题

行存储格式

数据页

见索引相关知识点

索引页

页结构

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数量

chunk机制的说明

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

动态调整buffer pool大小

chunk机制

[Server]innodb_buffer_pool_size=2147483648

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

推荐大小设置

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

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

问题描述

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

减少碎片的方法

碎片问题

-- 当前生命周期的设置 1GBset global innodb_buffer_pool_size=1073741824;# 用以上方式动态设置，在mysql重启服务器后恢复默认设置，需要修改对应的配置文件。# buffer pool size大小【这里注意的是，当size小于1GB的情况，instances设置不生效】# innodb_buffer_pool_size=268435456# buffer pool个数# 这个值可以设置为机器的核数，总内存空间占机器可用内存75%即可innodb_buffer_pool_instances=4

-- 查询sizesshow variables like '%innodb_buffer_pool_size%';-- 查询buffer pool个数show variables like '%innodb_buffer_pool_instances%';-- 查询chunk大小show variables like '%innodb_buffer_pool_chunk_size%';-- 当前生命周期的设置 1GB

相关参数查看

relay log

日志结构

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

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

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

page cleaner thread

purge thread

IO thread

Master thread

后台线程

核心组件

Show engine innodb status

查看innodb整体运行状态

innoDB

存储引擎

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

磁盘冗余技术

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

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

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

电池充放电原理

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

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

RAID 0

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

RAID 1

RAID 10

RAID技术方案

RAID存储架构

存储硬件

VFS层

NFS文件系统

Ext2文件系统

Ext3文件系统

文件系统层

Page Cache缓存层

通用Block层

CFQ公平调度算法

deadline IO调度算法

IO调度层

Block设备驱动层

Block设备层

Linux操作系统的存储系统

底层原理分析

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

形态

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

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

select * from table for update.

查询操作加互斥锁

独占锁(X锁/exclude锁)

select * from table lock in share mode

语法

共享锁(S锁)

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

互斥性

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

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

实用性

行锁

LOCK TABLES xxx READ

意向独占锁

LOCK TABLES xxx WRITE

意向共享锁

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

表锁

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

元数据锁/Metadata Locks

锁机制

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

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

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

生产问题

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

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

运用

主从复制架构

依赖于主从复制架构

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

读写分离架构

高可用架构

执行增删改查的SQL语句

SQL接口

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

查询解析器

选择最优的查询路径

查询优化器

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

执行器

InnoDB

MyISAM

Memory

SQL执行组件

每秒可处理的请求

QPS

每秒可处理的事务量

TPS

能抗住的请求

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

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

吞吐量

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

latency

IO相关的压测性能指标

CPU负载

网络负载

内存负载

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

--db-driver=mysql

连续访问300秒

--time=300

用10个线程模拟并发访问

--threads=10

每隔1秒输出下压测情况

--report-interval=1

连接到哪台机器的MYSQL库，用户密码是什么。

--mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user

在test_db这个库里，构造20个测试表，每个测试表构造100万条测试数据，测试表的名字会是类似于sbtest1这样

--mysql-db=test_db --tables=20 -- table_size=1000000

测试数据库的读写性能

oltp_read_write

测试数据库的只读性能

oltp_read_only

测试数据库的删除性能

oltp_delete

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

oltp_update_index

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

oltp_update_non_index

测试数据库的插入性能

oltp_insert

测试数据库写入性能

oltp_write_only

读写模式

禁止ps模式

--db-ps-mode=disable

准备压测数据

prepare

运行压测

run

清理数据

cleanup

命令

参数解析

sysbench

压测工具

有xx个线程在压测

thds

每秒执行了xx个事务

tps

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

qps

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

lat(ms，95%)

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

err/s:0.00 reconn/s:0.00

压测结果分析

top命令

CPU负载情况分析

内存负载情况分析

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

-dsk/total-read writ103k 211k0 11k

dstat -d命令

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

--io/total-read writ0.25 31.90 2530 39.0

dstat -r命令

磁盘IO情况分析

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

-net/total-recv send16k 17k

dstat -n命令

网卡流量情况分析

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

压测的原则

压测

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

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

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

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

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

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

检查Mysql启动日志

cat /etc/security/limits.confcat /etc/rc.localulimit -HSn 65535

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

open files

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

max user processes

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

网络缓存的限制

max locked memory

最大可以锁定的内存大小

core file size

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

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

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

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

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

原因

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

随机io性能非常好

采用SSD固态硬盘。

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

可以用fio工具进行测试。

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

配置参数innodb_io_capacity

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

innodb_flush_neighboors

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

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

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

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

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

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

set profiling = 1

执行show profiles命令，查看query id

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

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

MYSQL profiling工具

排查工具

其他影响查询速度的因素

优化实战案例

脏读

脏写

不可重复读

幻读

多个事务并发带来的问题

读未提交

read uncommited

读已提交

read committed

可重复读

repeatable read

串行化

serializable

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level

事务的隔离级别

Isolation.READ_COMMITTED

Isolation.READ_UNCOMMITTED

Isolation.REPEATABLE_READ

Isolation.SERIALIZABLE

mvcc多版本并发隔离机制

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

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

m_ids

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

min_trx_id

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

max_trx_id

你这个事务的id

creator_trx_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中，不过在，不可以访问，不在则可以访问，

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

判断规则

mvcc中的ReadView机制

每次查询都生成新的ReadView

Read Committed

事务

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

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

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条件中前面带%

索引失效小技巧

索引

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

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

const

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

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

速度也很快

ref

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

ref_or_null

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

range

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

index

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

效率很低

all

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

eq_ref

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

index_merge

检索方式

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

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

使用多个索引的硬性要求

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

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

inner join

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

outer join

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

nested-loop join

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

semi join

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

union

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

union all

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

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

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

行记录数*0.2 + 微调值

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

成本分析

表里的记录数

rows

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

data_length

show table status like \"表名\"

执行成本评估

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

常量替换

去除多余括号

比如 b = b and a = a

去除无意义的sql

SQL改写

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

物化表

查看SQL执行计划

explain

执行计划的id

SIMPLE

主查询

PRIMARY

子查询

SUBQUERY

DEPENDENT SUBQUERY

DEPENDENT UNION

UNION RESULT

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

derived

MATERIALIZED

查询类型

select_type

查询的表名

table

表分区的概念

partitions

就是上面的检索方式

type

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

possible_keys

实际选择的索引

key

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

key_len

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

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

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

filtered

nested loop

使用临时表

using temporary

using where

using index condition

using index

using filesort

一些额外的信息

extra

关键词

执行计划

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

join buffer

查看警告信息

show warnings

关闭半连接优化

SET optimizer_switch='semijoin=off'

其他知识点

SQL执行与优化

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

8C16G

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

16C32G

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

4C8G

生产配置

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

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

mysqld --skip-networking

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

mysqld --default-storage-engine=MyISAM

举例

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

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

mysqld -P 3307

mysqld -P3307

选项的长形式和短形式

命令行

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

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

推荐原因

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

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

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

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

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

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

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

变量说明

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

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

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

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

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

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

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

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

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

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

格式

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

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

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

defaults-file的使用

配置文件

启动选项

SHOW VARIABLES [LIKE 匹配的模式];

查看系统变量

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

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

[server]default-storage-engine=MyISAMmax-connections=10

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

设置系统变量

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

GLOBAL

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

SESSION

作用范围

系统变量

在服务器启动时，会将每个全局变量初始化为其默认值（可以通过命令行或选项文件中指定的选项更改这些默认值）。然后服务器还为每个连接的客户端维护一组会话变量，客户端的会话变量在连接时使用相应全局变量的当前值初始化。

生效场景

SET [GLOBAL|SESSION] 系统变量名 = 值;

SET [@@(GLOBAL|SESSION).]var_name = XXX;

SHOW VARIABLES语句，默认查看的是SESSION作用范围的系统变量。

SHOW [GLOBAL|SESSION] VARIABLES [LIKE 匹配的模式];

我们也可以在查看系统变量的语句上加上要查看哪个作用范围的系统变量

查看不同作用范围的系统变量

如果某个客户端改变了某个系统变量在`GLOBAL`作用范围的值，并不会影响该系统变量在当前已经连接的客户端作用范围为`SESSION`的值，只会影响后续连入的客户端在作用范围为`SESSION`的值。

小贴士

有些系统变量只具有GLOBAL作用范围，比方说max_connections，表示服务器程序支持同时最多有多少个客户端程序进行连接。

有些系统变量只具有SESSION作用范围，比如insert_id，表示在对某个包含AUTO_INCREMENT列的表进行插入时，该列初始的值。

有些系统变量的值既具有GLOBAL作用范围，也具有SESSION作用范围，比如我们前边用到的default_storage_engine，而且其实大部分的系统变量都是这样的，

并不是所有系统变量都具有GLOBAL和SESSION的作用范围

比方说version，表示当前MySQL的版本，我们客户端是不能设置它的值的，只能在SHOW VARIABLES语句里查看。

有些系统变量是只读的，并不能设置值。

注意事项

总结

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

定义

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

Threads_connected

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

Handler_update

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

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

说明

状态变量

启动选项和系统变量

Mysql实战原理

 收藏

立即使用

Nio/Netty笔记

 收藏

立即使用

Netty模型

 收藏

立即使用

Mysql实战原理

 收藏

立即使用

并发编程笔记

Alice

职业：本科

去主页





0 条评论

回复删除



取消

下一页