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