mysql innodb
2021-11-19 09:42:45 59 举报AI智能生成
mysql innodb 锁等知识点
作者其他创作
大纲/内容
核心组件
连接器
负责跟客户端建立连接，获取权限，维持和管理连接。
查询缓存
简介
mysql拿到一个查询请求指挥，会先查询缓存，之前的sql语句为key，结果为value缓存起来，如果命中缓存，就直接返回数据给客户端
存在问题
一般是不建议使用查询缓存的，因为大多数情况下命中率很低(除非是一个静态表，长时间才更新一次)，并且在8.0的mysql中已经删除了查询缓存模块
解释器(分析器)
进行语法分析，根据词法分析的结果，与语法分析器会根据语法规则，判断你输入的这个sql语句是否满足mysql的语法
优化器
在表里面多个索引的时候，选择决定使用那个索引，或者在一个语句多表关联时，决定各个表的连接顺序
执行器
根据表的引擎的定义使用引擎提供的接口执行查询流程
redo log
用来记录事物数据变更后的值(如在更新一条数据的时，innoDB引擎会先把记录记录到redo log中，并更新内存，在适当的时候才会把这个操作记录更新到磁盘里面)
undo log
用来记录事物数据变更前的值，用来回滚和其它事物的多版本读
binlog(归档日志)
是server提供的能力，与存储引擎无关，记录的是逻这个sql语句的原始逻辑,比如“给 ID=2 这一行的 c 字段加 1 ”
一条Select的执行轨迹
mysql的两阶段提交
执行update场景
执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。
为什么要两阶段提交
redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。
事物的特性
原子性
事物的所有操作，要么全部完成，要么全部不完成，不会结束在某个中间环节
隔离性
概念
当多个事物并发访问数据库中的同一数据时，所表现出来的相互关联
隔离级别
读未提交
一个事物没有提交就被另外一个事物读到了
读已提交(不可重复读)
一个事物提交后就可以被其它事物读取到
可重复度
一个事物执行过程中看到的数据是一致的，总是跟这个事物在启动时看到的数据时一致的
串行化
对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行
持久性
事物完成之后，事物所做的修改持久化保存，不会丢失
一致性
事物开始之前和事物结束之后，数据库的完整性限制未被破坏(原子性，隔离型，持久性，共同达到一致性)
MVCC
概念
多版本并发控制，让读写不冲突，读不需要加锁，作为一致性读视图，用于支持RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现。
MVCC下读的分类
当前读
sql读取的数据时最新版本的。通过锁机制在保证数据无法通过其他事务进行修改。除了普通的select其他的都是当前读
快照读
不主动加锁的select语句就是快照读，读取的是数据的快照版本。innodb快照读，数据的读取由cache(原本数据)与undo(当前事务修改或者插入之前的数据)两部分组成 (在不同的隔离级别下，快照读是有区别的，在RC下，每次读取都会重新生成一个快照，所以每次快照都是最新的，也因此事务中每次select也可以看到其他事务commit的数据的更改，也就是不可重复读。但是在RR级别下，快照会在事务中第一次select语句执行时生成，只有在本事务中对数据修改才会更新快照，因此，只能看到第一次select之前已经提交事务的数据)
实现原理
如图DATA部分代表了表中的六个字段的数据，当前有事物1跟事物2在执行更新操作，事物1 填写了事物编号,使回滚指针指向undo log中的修改前的行。事物2通过回滚指针与前一条记录连接起来。
InnoDB只查找版本早于当前事务版本的数据行，即行的系统版本号小于或等于事务的系统版本号，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过
锁
分类
表级锁
特点
开销小，加锁快
不会出现死锁
锁的粒度大，锁冲突概率大，并发度底
类型划分
意向共享锁(Intention Shared Locks)
简称IS锁，在事务准备给数据加共享锁之前，需要去获取表的IS锁，意向共享锁之间可以互相兼容
意向排它锁(Intention Exclusive Locks)
在事务准备给数据加排他锁之前，需要去获取表的IX锁，意向排他锁之间可以互相兼容 (意向锁是由Innodb自动加的，无法干预，它存在的意义在于，当你进行锁表操作时，如果发现意向锁已经被拿了，那么你需要等到锁的释放才能进行锁表)
自增锁(AUTO-INC Locks)
行级锁
特点
开销大，加锁慢
会出现死锁
锁粒度小，锁冲突概率低，并发高
类型划分
共享锁(Shared Locks)
读锁，简称S锁。在多个事务共同对同一数据可以共享一把锁，都可以访问到数据，但是不能修改数据;
加锁方式 : select * from xxx where id =x LOCK IN SHARE MODE
加锁方式 : select * from xxx where id =x LOCK IN SHARE MODE
排他锁(Exclusive Locks)
又叫做写锁，简称X锁。不能与其他事务共享，只有获得锁的事务才能对数据进行读取去修改
加锁方式 : delete/update/insert默认加上X锁，查询:select * from xxx where id =x for update;
加锁方式 : delete/update/insert默认加上X锁，查询:select * from xxx where id =x for update;
锁实现的分类
记录锁(Record locks)
锁住具体的索引项，当sql执行按照唯一索引进行数据检索的时候，查询条件等值匹配的时候并且数据存在的时候，这个sql加上的就是记录锁
间隙锁（Gap locks）
锁住数据不存在的区间(左开右开),在sq执行按照索引进行数据检索的时候，查询数据不存在，这时SQL加上的锁为间隙锁，锁住了不存在的区间
临键锁(Next-key locks)
等于(Record locks+Gap locks)(左开右闭(])，当sql执行按照索引进行数据检索时，查询条件为范围查找，并有数据命中，这个sql语句加上的锁就是临键锁，锁住记录+区间（这个锁解决了幻读问题）
死锁
概念
当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁
如何避免死锁
加锁顺序一致性
基于primary或者unique key更新数据
单次操作数据量不宜过多，表尽量少
减少表上的索引，减少单次定的资源
死锁的检测
innodb_deadlock_detect默认为on，50s发现死锁就会超时退出
索引
简介
索引是在存储引擎用于快速找到记录的一种数据结构。在数据量越来越大的时代，索引对性能的影响越来越严重。所以建立适当的索引非常重要，想要建立适当的索引，就需要深入索引的原理。
优缺点
优点
索引能大大减少服务器需要扫描的数据量
索引能帮助服务器避免排序与建立临时表
索引可以把随机I/O变成顺序I/O
缺点
加索引会降低记录插入，删除更新的速度
需要额外的存储空间
分类
数据结构分类
HASH
简介
哈希索引使用了哈希算法，把值通过哈希算法计算出哈希值进行定位
优缺点
优点
检索的时间复杂度理论上是O(1)，检索特别快
缺点
在数据量比较大的情况下，出现大量哈希碰撞，检索效率降低
不支持按照索引值的顺序存储，所以无法排序,无法进行范围查询
不支持最左匹配原则
BTREE
简介
mysql中默认的索引类型,通过多叉树的方式组织在一起
优缺点
优点
对比传统的二叉搜索树,当数据量比较大的时候，树的高度就会非常高，那么意味着I/O次数非常高，那么速度会很慢，B+TREE的树的高度非常低，寻找数据产生的I/O次数少
缺点
额外空间
添加，删除，修改索引列是，会伴随页分裂，页空洞等性能损耗
存储方式分类
聚簇索引(Clustered Index)
将索引与数据放在一起，并且在叶子结点存放数据(在Innodb中主键索引就是聚簇索引)
常见的聚簇索引
显示的主键列
第一个唯一索引
内置的6字节的ROWID
辅助索引(Secondary Index)
通过索引检索到行号，再通过行号找到数据（Innodb是找到对应的主键id，再通过主键id找到对应的数据）
功能分类 全文索引(FULLTEXT INDEX)
普通索引(INDEX)
这是最基本的索引类型，而且它没有唯一性之类的限制
唯一索引(UNIQUE INDEX)
索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
主键索引(PRIMARY KEY)
主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”，且不允许有空值。
索引创建规范
单张表中索引数量不超过五个，单个索引中的字段数不超过五个
表必须有主见，推荐使用unsigned自增作为主键。唯一键由3以下字段组成，并且字段都是整形时，可使用唯一键作为主键
禁止冗余索引(索引|(a,b,c),索引|(a,b))，禁止重复索引(索引|(a),索引|(a,主键ID))
联表查询时，join列的数据类型必须相同，并且要建立索引
不在低基数列上建立索引，如`性别`
选择区分度大的列建立索引，组合索引中，区分度大的字段放在最前面
合理创建组合索引,(a,b,c)相当于(a),（a,b),(a,b,c)
合理使用覆盖索引减少IO跟避免排序
索引优化分析
查看执行计划
执行方式
explain select * from xxx where xxxx=xxx
字段解释
id: 编号
id值相同,从上往下 顺序执行
id值越大越优先查询 (本质：在嵌套子查询时，先查内层 再查外层)
select_type ：查询类型
PRIMARY
包含子查询SQL中的 主查询 （最外层）
SUBQUERY
包含子查询SQL中的 子查询 （非最外层）
SIMPLE
简单查询（不包含子查询、union）
DERIVED
衍生查询(使用到了临时表)
table ：输出结果集的表
type ：访问类型
从左到右，性能由好到最差，system>const>eq_ref>ref>range>index>all （ref到range之间还有一些其他的不过不太常见）（要对type进行优化的前提：有索引）(一般优化后的type必须在range以上)
分类
system
只有一条数据的系统表 ；或 衍生表只有一条数据的主查询（基本不能达到可忽略）
const
仅仅能查到一条数据的SQL ,用于Primary key 或unique索引 （类型 与索引类型有关
eq_ref
唯一性索引：对于每个索引键的查询，返回匹配唯一行数据（有且只有1个，不能多 、不能0
ref
非唯一性索引，对于每个索引键的查询，返回匹配的所有行（0，多）
range
检索指定范围的行 ,where后面是一个范围查询(between ,> < >=, 特殊:in有时候会失效 ，从而转为 无索引all)
index
全索引扫描，查询全部索引中数据
all
全表扫描，查询全部表中的数据
possible_keys ：可能用到的索引
key ：实际使用的索引
key_len ：实际使用索引的长度
用于判断复合索引是否被完全使用 （a,b,c）
例子
在utf8编码中：1个字符占3个字节，如果索引的字段可以为null，则会使用一个字节用来标识 ，如果是varchar这种，会用两个字节表示可变
例如 ：某个字段 name（varchar（20））,如果用到了这个字段的索引那么lenth就是 20*3+1（null）+2（可变字符） =63
例如 ：某个字段 name（varchar（20））,如果用到了这个字段的索引那么lenth就是 20*3+1（null）+2（可变字符） =63
ref :表之间的引用
rows: 被索引优化查询的 数据个数 (实际通过索引而查询到的 数据个数)
Extra :额外的信息
using index
性能提升; 索引覆盖（覆盖索引）。原因：不读取原文件，只从索引文件中获取数据 （不需要回表查询）只要使用到的列 全部都在索引中，就是索引覆盖using index
using where
表示进行了回表查询
using filesort
性能消耗大；需要“额外”的一次排序（查询） 。常见于 order by 语句中。
using temporary
性能损耗大 ，用到了临时表。一般出现在group by 语句中。
impossible where
where子句永远为false
避免索引失效的一些原则
复合索引，不能跨列使用（index（a，b，c），你的索引使用就不要 where a=x，c=x）
复合索引尽量使用全索引匹配
不要在索引上进行任何操作（计算，函数，类型转换），否则索引失效
复合索引不要使用不等（！= ,<>，>），这样会导致自身以及右侧的索引失效**(这个不是一定的有概率的**)
like尽量以“常量”开头，不要以'%'开头，否则索引失效
尽量不要使用or，否则索引失效（字段都是单值索引是可以的）
常见问题
经常在数据量很大的情况下我们要把主键用整数代替字符串，并且是增长的？
在使用BigInt的时候比UUID(为保证不重复一般都是UUID),占用的字节少，那么在B+Tree的结构中，就能存储更多的值。
比字符串好排序
如果你生成的是自增的（分布式id生成策略中都是增加的）,在插入时，就可以尽可能减少页分裂
为什么我们经常被要求，只查询需要的字段？
在例如你查询只用到name时，select * from user where iphone=xxx，索引为（iphone，name）的时候，我们根据上面的innodb的索引结构学习到，你要查询索引中没有的字段的时候，需要先通过辅助索引找到主键索引的值，然后再找到对应的数据值（过程叫做回表），这样就多了I/O操作，如果你的sql改成select name from user where iphone=xxx，这个name值在辅助索引中已经有了，所以不需要再去主键索引中找数据，就提高了查询效率(这种情况叫做覆盖索引)
高性能表设计
范式与反范式
范式模型，数据没有冗余，更新容易，表的数量会比较多，查询数据需要多表关联时查询性能低下
反范式模型，采取适当的冗余带来很好的读取性能，当业务场景需要是应该适当采用反范式模式
基础规范
原则
回归存储的基本职能，只做存储，不做数据的复杂计算，不做业务逻辑处理
查询时，尽量单表查询，减少多表查询
杜绝大事物，大SQL，大批量，大字段等性能杀手
统一规范
使用innodb存储引擎
使用utf8mb4 字符集
关闭大小写
禁用功能
enum，set
blob，text
视图，event
存储过程，触发器
命名规范
字符范围
a-z,0-9和_(下划线)
禁止
所有表名小写，不允许使用-，空格，不允许使用其他的字符作为名称
常见规范
后缀命名规范
索引命名规范
组合索引
多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
WAL(Write-Ahead Logging): 先写日志，再写磁盘
主从复制
基本原理
master上的所有的修改都会保存在二进制Binary log中，Slave开启一个I/O thread，来读取Binary log然后写到本地的一个Realy log里面。同时Slave上开启一个SQL Thread进行执行获取到的Realy log。
主从复制延迟
产生的原因
Master写入BInlog,I/O thread读取都是顺序IO，速度比较快，但是SQL thread执行Relay log时需要对数据进行修改/删除/插入，随便分散在磁盘上，就时随机IO了，就会延迟问题(最有可能的也是最主要的原因)
主从的网络延迟比较高
大事物执行，如果Master一个事物执行一分钟，而binlog的写入必须要等待事物完成之后，才会传入备库，那么此时在开始执行的时候已经延迟1分支了
从库在同步数据的同时，其它线程在执行加锁的查询操作，发生了锁抢占的情况。
解决方案
架构方面
业务持久化层采用分库架构，让不同的业务请求分散到不同的数据库服务上，分散单台机器的压力
与业务服务之间添加缓存，减少mysql的读的压力
更好的CPU，SSD，内存等
从库配置方面
sync_binlog 设置的大一点
sync_binlog=0,代表每次提交事物都只writer，不fsync
sync_binlog=1,代表每次提交事物都writer和fsync
sync_binlog=M,代表每次提交事物都只writer,N个事物后fsync
直接禁用slave的binlog
设置innodb_flush_log_at_trx_commit=1或者0
innodb_flush_log_at_trx_commit=0 log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行.该模式下，在事务提交的时候，不会主动触发写入磁盘的操作
innodb_flush_log_at_trx_commit=1 每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去
innodb_flush_log_at_trx_commit=2 每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作
并行复制(mysql>5.6)
利用多线程能力并行执行，coordinator 就是原来的 sql_thread, 不过现在它不再直接更新数据了，只负责读取中转日志和分发事务。真正更新日志的，变成了 worker 线程。
复制模式(处理主从一致性)
异步复制
主节点push binlog到从节点，这样在主节点宕机时，可能从节点并没有获取到最新的binlog日志，导致数据不一致
半同步复制
主节点需要收到一台从节点已经写入了relay log后返回ACK消息给主节点，主节点才新型commit
全同步复制
主节点需要收到所有从节点已经写入了relay log后返回ACK消息给主节点，主节点才新型commit
GTID复制模式
主节点更新数据时，会在事务前产生GTID(server-id + transaction-id)，一起记录到binlog日志中
从节点的I/O线程将变更的bin log，写入到本地的relay log中
SQL线程从relay log中获取GTID，然后对比本地binlog是否有记录（所以MySQL从节点必须要开启binary log)（代替了原来的binlog+position的模式）
如果有记录，说明该GTID的事务已经执行，从节点会忽略。
如果没有记录，从节点就会从relay log中执行该GTID的事务，并记录到bin log。
0 条评论
下一页
