mysql总结
2019-10-28 18:43:12 7 举报AI智能生成
极客时间mysql45讲笔记
作者其他创作
大纲/内容
小块理论知识
分层
service层
连接器
分析器
优化器
执行器
存储引擎层
表空间结构(innodb)
表空间(tablespace)
段(segment)
区(extent)
页(page或block)
innodb引擎
索引
数据模型：B+树
分类
主键索引（聚簇索引）：叶子节点存储着一整行数据（也就是说整张表的数据都存在该索引上）
非主键索引（二级索引）：叶子节点存储的是主键（意味着若使用二级索引查询，若该查询还需要主键、该索引所存字段之外的其他表字段，则必须在通过主键值在主键索引再查次数据）
一些小知识点：
覆盖索引
前缀索引
索引下推（mysql5.6才有）：在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数
设计优化：
主键长度越小，普通索引锁占空间也就越小，所以一般用自增主键（除了空间，还能避免插数据时树结构频繁变更，详细看B+树）
如何优雅地重建主键索引：alter table T engine =InnoDB
事务隔离级别
读未提交（read uncommitted）：一个事务还没提交时，它做的变更就能被别的事务看到
读提交（read committed）：一个事务提交后，它做的变更才能被其他事务看到（oracle默认隔离级别）
可重复读（repeatable-read）：一个事务执行过程中看到的数据，总是跟这个事务启动时看到的数据一致
mysql默认隔离级别
不同时刻启动的事务会有不同的视图，通过MVCC（多版本并发控制）实现
串行化（serializable）：对于同一行记录，读会加读锁，写会加写锁，当出现读写锁冲突，后访问的书屋需要等前一个事务执行完成
mvcc
使用场景
读未提交，不能使用它的原因是不能读取符合事物版本的行版本（事务要的一直是最新的数据）
可提交读可以使用
可重复读可以使用
可序列化不能使用，它总是要锁定行。
隔离级别RR下的MVCC知识点
每个事务都有一个唯一的事务id（transaction id），是在事务开始时向Innodb申请的，按申请顺序递增
每行数据是有多个版本的，每次事务更新数据，都会生成一个新的数据版本，并把事务id作为版本号（其实只存储最新版数据，需要旧版数据时再根据undo log 计算出对应旧版本的值）
一个事务中，select语句查到数据一般是事务开始时所记下那个版本号的数据，但如果本事务有更新过，则查询出本事务id版本的数据
一个事务中，update语句则是当前读(当前读，只能读当前(最新版本)的数据)。如果select语句加锁(加for update或lock in share mode)，也是当前读
undo log是不会持久化的，如果持久化了，那原本表所占空间会随着数据修改无限膨胀。那么事务怎么快速知道它该读哪个版本数据的？
（1）Innodb为每个事务构造了一个存储当前‘活跃’的所有事务id的数组，这里将数组命名为$id_arr（活跃指的是启动了但还没提交）
（2）$id_arr中最小值记为低水位，这里命名为$min_id。最大值再+1，则记为高水位，命名为$max_id
（3）访问一条数据时，快速判断该版本(row trx_id)数据是否可见？row trx_id有几种可能
落在绿色部分，代表是已提交事务或者当前事务自己生成的，数据可见
落在红色部分，代表这版本是由将来启动的事务生成的，数据不可见
落在黄色部分，有俩种情况
row trx_id在$id_arr数组中，表示这个版本是由还没提交的事务生成，不可见
row trx_id不在$id_arr数组中，表示这个版本是已经提交了的事务生成，可见
读提交和可重复读的区别
在可重复读下，只需要在事务开始的时候创建一致性视图
在读提交下，每一个语句执行前都会重新算出一个新的视图来
change buffer
功能：
当更新一条不在内存中的数据时，在不影响数据一致性的前提下，innodb会将更新操作缓存在change buffer中，这样就不需要从磁盘读入数据。下次查询需要访问这个数据页时，将数据页读入缓存，然后执行chang buffer中与这个页有段的操作
使用场景：
无唯一索引
写操作多查询少的（或者说修改完一条数据后，不会马上去查询它）
存储
使用的是buffer pool里的内存，不能无限增大，可通过参数innodb_change_buffer_max_size来控制最多占用buffer pool的百分比
目标：
节省随机读磁盘的IO消耗（注意是读）
命令：
通过参数innodb_change_buffering来控制change_buffer的是否启用
事务的回滚日志undo log（MVCC就是通过Undo log实现）
定义：每条记录在更新的时候都会同时记录一条回滚日志。记录上的最新值，通过回滚操作可以得到前一状态的值
日志什么时候删除：不同时刻启动的事务会有不同的数据视图，当系统里没有比这个回滚日志更早的数据视图时，回滚就会被删除
尽量不要使用长事务：事务越长，存在的数据视图也就越老，回滚日志就一直不能被删除，会大量占用存储空间
存放：
与redo log存放在日志文件不同，存放在数据库内部的一个特殊段，叫做undo 段（undo segment）
undo 段位于共享表空间，可通过py_innodb_page_info.py工具查看当前undo的数量
如何避免长事务对业务的影响
开发端
set autocommit=1
去除不必要的只读事务
设置每个语句执行的最长时间（SET MAX_EXECUTION_TIME命令）
数据库端
监控information_schema.Innodb_trx表，设置长事务阈值，超过就报警或kill
Percona的pt-kill工具（百度：监控mysql链接，自动kill有问题的链接）
业务测试阶段输出general_log，分析日志提前发现问题（该日志会记录所有到达server的sql）
mysql 5.6或更高版本：innodb_undo_tablespaces设置成2或更大的值（该参数设置构成rollback segment文件的数量）
误解
容易理解成：undo log 用于将数据库物理地恢复到执行语句或事务之前的样子
事实：undo log是逻辑日志，因此只是将数据库逻辑地恢复到原来的样子，所有的修改都被逻辑地取消了（因为会有多事务并发）
造成主从延迟的几种情况
1.主库DML语句并发大，从库qps高
2.从库服务器配置差或一台服务器上几台从库（资源竞争激烈，特别是IO）
3.主库和从库参数配置不一样
4.大事务（例如一次性delete了大量数据）,或者大表DDL
5.从库上再进行备份操作
6.表上无主件
7.设置的是延迟备库
8.备库空间不足
redo log(重做日志)
特点
引擎层，且innodb特有
物理日志,记录的是：数据页的修改、以及change buffer新写入的信息
循环写，空间固定，会用完，用完就需要把日志里的操作持久化到磁盘（不一定是用完才持久化），然后清理
可通过参数innodb_flush_log_at_trx_commit设置每次事务都持久化到磁盘
为0时，每次都只是把redo log留在redo log buffer
为1时，每次事务提交都把日志持久化到磁盘（即执行了flush）
为2时，每次事务提交都把日志存到page cache
日志是按数据页（主键索引数据页）来存储的（binlog则是按顺序存储的）
功能描述：当有一条记录需要更新时，先把记录写到redo log，并更新内存。适当的时候，引擎再将操作记录更新到磁盘
小知识点：
redo log的存储空间可以想象成是一个环
checkpoint： 当前mysql同步redo log的刻度点，即checkpoint之后的redo log都还没写进磁盘
writepoint ：当前mysql写redo log写日志的刻度点
redo log buffer
作用：这是一块用于临时存储redo log的内存，在事务未commit时，redo log是不会"主动"写进log文件的，以减少不必要的的IO消耗
WAL技术(write-ahead logging)：就是redo log和磁盘表数据配合的整个过程。关键点：先写日志，再写磁盘
crash-safe:有了redo log ,InnoDB就可以保证技术数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe
脏页
Innodb_buffer_pool_pages_dirty：当前脏页数
Innodb_buffer_pool_pages_total：内存总页数。
脏页比例是通过 Innodb_buffer_pool_page_dirty/Innodb_buffer_pool_pages_total得到的
select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
select @a/@b;
select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
select @a/@b;
innodb_max_dirty_pages_pct：脏页比例上限，默认为75%。到达这个上限后，必须开始刷脏页
innodb_io_capacity：刷脏页时，用于告诉InnoDB你的磁盘能力，作者建议设置成磁盘的IOPS。
设置不当的问题
过小，mysql写入速度很慢，但服务器io压力不大
过大，影响其他服务的IO
用法
redo log buffer持久化
每一秒轮询
innodb_log_buffer_size使用到了一半
并行事务，顺带把redo log buffer持久化到磁盘
目标：节省随机写磁盘的IO消耗（注意是写）
为什么偶尔sql语句执行变慢了？(flush,刷脏页)
变慢原因：当时mysql在刷脏页进磁盘
发生flush的四个场景
redo log写满了
当write point要追上check point时，mysql会将check point往后移，移的过程中会把redo log里的操作更新到磁盘
内存不够用了
mysql要淘汰一些内存页，如果淘汰的是脏页，就要先将脏页写到磁盘。（不是直接淘汰）
拓展点：
脏页里所做操作在redo log里不一定是连续的，所以有其他机制来保证redo log进行“重放”时，识别出这个数据页已经刷过了，然后跳过
不直接淘汰脏页是为了性能，保证每个数据页有两种状态（疑问：change buffer呢？数据文件上有，但不是对的）
内存里存在，内存里的肯定是正确的结果，直接返回（个人理解这里包括change buffer）
内存没数据，数据文件上是正确的结果，读入内存后返回
mysql空闲时
mysql正常关闭时
innodb刷脏页的控制策略
锁
全局锁
对整个数据库实例加锁，
命令Flush tables with read lock(FTWRL)
作用：整个库处于只读状态，其他线程的这些语句会被阻塞：数据更新语句（增删改），数据定义语句（建表改表等），更新类事务的提交语句
使用场景：全库逻辑备份
话题延伸：如果该库所有表使用的引擎都支持RR事务隔离级别(例如Innodb)，则可以利用一致性视图特性，使用mysqldump -single-transaction
为什么要用FTWRL，而不是set global radonly = true
有些系统中readonly的值会被用来做其他逻辑，例如判断库是主库还是备库
执行FTWRL命令后若客户端有异常断开，mysql会自动释放该锁，而readonly则会使数据库一直保持readonly状态
在slave上，如果用户有超级权限，readonly是失效的
表级锁
表锁
语法：lock tables t1 read, t2 write
作用：与FTWRL相似，除了限制其他线程的读写，也限制了本线程的操作。例如上面的语句执行后，本线程只能执行读t1,读写t2的操作
元数据锁（meta data locl ,MDL,mysql 5.5引入，处于server层）
访问表时会自动加上，作用是保证变更表结构操作的安全性
场景：对表数据进行增删查改时加读锁，对表结构做变更操作时，加写锁
容易踩的坑：小表加字段，挂了整个库？？
模拟场景(sql语句上下代表顺序)：
session A ：begin; select * from t limit 1;(未提交)
session B：select * from t limit 1;
session C：alter table t add f int;(block)
session D：select * from t limit 1;(block)
总结：
MDL锁是在语句执行开始时申请，但语句结束后并不会马上释放，而会等到整个事务提交后再释放
人话：session A读锁未释放，session C请求了写锁被阻塞，session D请求的读锁也被锁塞了
挂库推测：
session A、B的查询是复杂的查询，所有在C之后来的session都会被阻塞。
假如该表查询语句频繁，且客户端有重试机制（即超时后会再起一个新session再请求），这个库的线程很快就会爆满
防止措施：
有DDL变更时，查看是否有长事务，若有：暂停DDL变更 or kill长事务（请求频繁的话，kill也没用）
MariaDB和AliSQL这俩个mysql开源分支支持DDL NOWAIT/WAIT n 这这个语法（意思是在指定等待时间内拿不到MDL锁就先放弃）
online ddl中mdl锁的流程
拿MDL写锁（上面就是卡在这一步）
降级成MDL读锁
真正做DDL
升级成MDL写锁
释放MDL锁
行级锁(主要针对Innodb)
注意事项
由引擎层自己实现，不是所有引擎都支持行锁（例如MyIsam）
两阶段锁协议：在Innodb事务中，行锁是在需要的时候才加上，但释放是在事务结束时才释放
如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放
该协议针对可重复读隔离级别，读提交是执行完就释放
两阶段锁引起的死锁
（1）session A: begin; update t set k=k+1 where id=1; session B: begin;
（2）session B: update t set k=k+2 where id=2;
（3）session A: update t set k=k+1 where id=2;
（4）session B: update t set k=k+1 where id=1;
解释：事务A在等id=2的行锁，而事务B在等id=1的行锁，死锁了
死锁解决策略
（1）通过innodb_lock_wait_timeout来设置超时时间
缺点：非死锁超时（例如普通的锁等待）也会被结束掉，另外时间不好控制长短，因此很容易误伤，
（2）死锁检测，将innodb_deadlock_detect=on。发现死锁后，主动回滚死锁链条中的某一个事务
缺点：每个新来的线程(它要加锁访问的行上有锁，才需检测)都要进行判断是否导致了死锁，假设有1000个并发线程，则死锁检测操作达到100W(复杂度为O(n))
（3）应用设计层面自己去优化，减小发生死锁的概率
间隙锁gap lock
作用：锁住数据之间的空隙，防止幻读。（注意幻读是专指新插入的数据，不包过update的）
冲突：间隙锁之间不存在冲突关系，冲突的是“往这个间隙中插入一个记录”
next-key lock(间隙锁和行锁的组合)
特点
都是前开后闭区间 （a,b]，该区间表示锁住a,b之间的间隙，并锁住b数据本身
所有的锁都是加在索引上的（有加在普通索引但没加到主键索引上的情况）
间隙锁是在可重复读隔离级别下才有
在分析加锁时都是按next-key lock来分析的，但本质上还是行锁+间隙锁
例如 执行 update table set a=1 where c=10;该语句首先会加间隙锁，再加行锁，在加行锁时被卡住了
缺点
可能会导致同样语句锁住更大的范围，这其实是影响并发度的
加锁规则：
两个原则，两个优化，一个bug
原则1：加锁的基本单位是next-key lock。next-key lock是前开后闭区间
例子：3条数据0 5 10，查询条件 where a = 5 ;则会加上锁(0,5] (5,10]
原则2：查找过程中访问到的对象才会加锁
例如某个加锁的select语句采用了覆盖索引，不需要访问主键索引，所以主键上没有加任何锁
优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁（前提是这条等值的数据时存在的）
优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件时，next-key lock退化为间隙锁
BUG:唯一索引上的范围查询会访问到不满足条件的第一个值为止
加limit时的加锁规则：只会锁操作到的数据
delete from table where c=10
delete from table where c=10 limit 2
binlog（归档日志）
参数
sync_binlog=1 每次事务的binlog都fsync到硬盘;值为0则每次只write,不fsync;值为N（N>1)，积累N个事务后才fsync
小知识点
service层，所有引擎都可以使用
逻辑日志，记录语句的原始逻辑
可以追加写入，某个文件写到一定大小后会自动切换写到下一个文件
binlog的写入机制：
事务执行过程中，先把日志写到binlog cache,事务提交的时候再把binlog写到binlog文件
每个线程都会分配到一片内存作为binlog cache
binlog_cache_size参数用于控制单个线程内binlog cache所占内存大小，如果超过规定大小，就要暂存到磁盘
三种模式
statement格式，记sql语句
缺点：主从数据同步时可能会导致主从不一致
delete from table where a=1 and b=1 limit 2
在主机上使用了索引a，但在从机上使用了索引b
row格式，会记录行的内容，记两条，更新前，更新后都有
优点：
不会像statement那样出现不一致
方便进行数据恢复（因为详细记录了修改的数据）
缺点
日志比较占空间，例如一条删了5000行数据的sql，statement值需要记录条sql，而row需要记录下所有数据
mix模式：mysql自己判断一条语句要用statement格式还是row
区别举例：delete * from table where name = 'abc'
statement:直接记录该语句
row:会记录所有删除的数据（如果是update语句，则会记录修改前和修改后的数据）
作用：数据备份，主从同步数据等
sql查询
连接器
建立连接、获取权限、维持和管理连接
连接器会去权限表里查出你所有的权限，之后这个链接里面的权限判断逻辑，都将依赖于此时读到的权限（即使对用户权限做了修改，也不会影响已存在链接的权限）
show processlist 命令可看到当前各链接的情况，其中sleep为空闲链接，空闲连接默认超过8小时会自动断开
长连接：
问题：mysql在执行过程中临时使用的内存在连接断开时才会释放，当连接太长，内存占用太大，会被系统强行杀掉（现象是Mysql异常重启）
解决方案：
定期断开长连接
若mysql版本为5.7或之后，执行mysql_reset_connection来重新初始化连接资源。这个过程不需要重连和重新做权限校验，但是会将连接恢复到感刚刚创建的状态
分析器（要做什么）
词法分析、从字符串中识别出字段名、表名
语法分析：判断语句语法是否正确，以及知道你要做什么
优化器（该怎么做）
确定执行方案(使用什么索引)
决定使用哪个索引
有多表关联(join)时，决定各个表的链接顺序
扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断
执行器
判断表权限
操作引擎拿数据（一行行拿），判断数据是否满足条件（本次查询所用索引无关的where条件）
order by排序
不需要排序，或者使用了索引本身的排序
需排序
排序空间
sort_buffer大小够用，内存内排序
sort_buffer不够用，临时文件内排序(using filesort)
临时文件可以有多个，采用归并排序算法最后将多个文件合并成一个大文件
sort_buffer_size控制内存大小
排序算法
一行数据字节是否大于max_length_for_sort_data（直接根据表定义字段大小）
小于，采用全字段排序
只去存储引擎拿一次数据，每行数据都走上层需要的
大于，采用rowid排序
会去引擎层拿俩次数据，第一次只拿排序用到的字段，第二次根据最后排序结果拿出sql需返回的字段
若sort_buffer够用,并且有limit子句（若有limit则只要内存满足最后返回数据的空间即可），mysql 5.6之后会采用优先队列排序。其余情况都是采用归并排序
查询需生成临时表的流程（using temporary）
生成临时表也是执行器去调引擎层接口去创建
若临时表大小小于tmp_table_size(默认是16M)，才采用内存临时表，采用memory引擎。若大于tmp_table_size，则采用磁盘临时表，引擎采用Innodb引擎
select count()性能：count(字段) < count(主键) < count(1) ≈ count(*)
存储引擎
查找某条数据
数据在内存，直接将数据返回给执行器；数据不在内存，从磁盘读入内存，再返回
若命中索引，则按照索引的where条件拿，否则全表一条条拿
引擎层按需返回表字段给上层
sql update语句（查询同上，只写多出）
执行器
(1)判断权限
(2)操作引擎拿数据（一行行拿），判断该条数据是否满足条件
(3)执行数据操作，调用引擎接口写入
(6)生成操作的binlog,并写入磁盘
(7)调用引擎的提交事务接口
innodb存储引擎
查数据(属于操作(2)里面的)
(4)数据更新操作的俩种情况：
有唯一索引
a.数据在内存中，判断到没有冲突，则执行这个操作
b.数据不在内存中，读取数据到内存，判断到没有冲突，则执行这个操作
无唯一索引
a.数据在内存中，直接更新内存
b.数据不在内存中，将所需做的操作记录到change buffer(后面redo log记录是新增change buffer这个操作)
产生上面俩个区别的原因：有唯一索引时，进行update操作时是一定要去内存读数据判断是否冲突了的
(5)添加记录到redo log,并处于prepare状态（二阶段提交）
(7)将刚写入的redo log改为commit状态
流程图
职业：本科
0 条评论
下一页
