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.主库和从库参数配置不一样
5.从库上再进行备份操作
6.表上无主件
7.设置的是延迟备库
8.备库空间不足
redo log(重做日志)
特点
引擎层，且innodb特有
循环写，空间固定，会用完，用完就需要把日志里的操作持久化到磁盘（不一定是用完才持久化），然后清理
可通过参数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和磁盘表数据配合的整个过程。关键点：先写日志，再写磁盘
脏页
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;
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消耗（注意是写）
变慢原因：当时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是失效的
表级锁
表锁
访问表时会自动加上，作用是保证变更表结构操作的安全性
场景：对表数据进行增删查改时加读锁，对表结构做变更操作时，加写锁
容易踩的坑：小表加字段，挂了整个库？？
模拟场景(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(间隙锁和行锁的组合)
所有的锁都是加在索引上的（有加在普通索引但没加到主键索引上的情况）
间隙锁是在可重复读隔离级别下才有
在分析加锁时都是按next-key lock来分析的，但本质上还是行锁+间隙锁
例如 执行 update table set a=1 where c=10;该语句首先会加间隙锁，再加行锁，在加行锁时被卡住了
缺点
可能会导致同样语句锁住更大的范围，这其实是影响并发度的
加锁规则：
两个原则，两个优化，一个bug
原则1：加锁的基本单位是next-key lock。next-key lock是前开后闭区间
原则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（归档日志）
参数
小知识点
service层，所有引擎都可以使用
逻辑日志，记录语句的原始逻辑
可以追加写入，某个文件写到一定大小后会自动切换写到下一个文件
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语句，则会记录修改前和修改后的数据）
作用：数据备份，主从同步数据等
mysql
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需返回的字段
查询需生成临时表的流程（using temporary）
生成临时表也是执行器去调引擎层接口去创建
若临时表大小小于tmp_table_size(默认是16M)，才采用内存临时表，采用memory引擎。若大于tmp_table_size，则采用磁盘临时表，引擎采用Innodb引擎
select count()性能：count(字段) < count(主键) < count(1) ≈ count(*)
存储引擎
查找某条数据
数据在内存，直接将数据返回给执行器；数据不在内存，从磁盘读入内存，再返回
若命中索引，则按照索引的where条件拿，否则全表一条条拿
引擎层按需返回表字段给上层
sql update语句（查询同上，只写多出）
(1)判断权限
(2)操作引擎拿数据（一行行拿），判断该条数据是否满足条件
(3)执行数据操作，调用引擎接口写入
(7)调用引擎的提交事务接口
innodb存储引擎
查数据(属于操作(2)里面的)
(4)数据更新操作的俩种情况：
有唯一索引
a.数据在内存中，判断到没有冲突，则执行这个操作
b.数据不在内存中，读取数据到内存，判断到没有冲突，则执行这个操作
a.数据在内存中，直接更新内存
b.数据不在内存中，将所需做的操作记录到change buffer(后面redo log记录是新增change buffer这个操作)
产生上面俩个区别的原因：有唯一索引时，进行update操作时是一定要去内存读数据判断是否冲突了的
(7)将刚写入的redo log改为commit状态
流程图
职业：本科
0 条评论
回复 删除
下一页
