Mysql 原理
2019-09-09 13:17:04 0 举报AI智能生成
MySQL原理
作者其他创作
大纲/内容
MySQL基架
模块组件
（一）MySQL向外提供的交互接口（Connectors）
Connectors组件，是MySQL向外提供的交互组件，如java,.net,php等语言可以通过该组件来操作SQL语句，实现与SQL的交互。
（二）管理服务组件和工具组件(Management Service & Utilities)
提供对MySQL的集成管理，如备份(Backup),恢复(Recovery),安全管理(Security)等
（三）连接池组件(Connection Pool)
负责监听对客户端向MySQL Server端的各种请求，接收请求，转发请求到目标模块。每个成功连接MySQL Server的客户请求都会被
创建或分配一个线程，该线程负责客户端与MySQL Server端的通信，接收客户端发送的命令，传递服务端的结果信息等。
（四）SQL接口组件(SQL Interface)
接收用户SQL命令，如DML,DDL和存储过程等，并将最终结果返回给用户。
（五）查询分析器组件(Parser)
首先分析SQL命令语法的合法性，并尝试将SQL命令分解成数据结构，若分解失败，则提示SQL语句不合理。
（六）优化器组件（Optimizer）
对SQL命令按照标准流程进行优化分析。
（七）缓存主件（Caches & Buffers）
缓存和缓冲组件
（八）MySQL存储引擎
一条sql查询语句怎么执行的
基本架构
Server层：
涵盖MySQL的大多数核心服务器功能以及所有的内置函数
存储引擎
存储引擎负责数据的存储和提取
一条sql更新语句是如何执行的
redo log(重做日志),引擎日志
MySQL 里 WAL 技术，Write-Ahead Logging关键点就是先写日志，再写磁盘
数据块发生重启，之前提交的记录都不会丢失，称为crash-safe
InnoDB先把记录写到redo log 中，并更新内存，系统空闲时，引擎将记录更新到磁盘中
binlog(归档日志)，server 层日志
记录了所有的DDL和DML语句（除了数据查询语句select）,以事件形式记录,它描述数据更改，还包含语句所执行的消耗的时间
使用场景
a、mysql主从复制：mysql replication在master端开启binlog,master把它的二进制日志传递给slaves来达到master-slave数据一致的目的。
b、数据恢复：通过mysqlbinlog工具来恢复数据。
1)、二进制日志索引文件(文件名后缀为.index)用于记录所有的二进制文件。
2)、二进制日志文件(文件名后缀为.00000*)记录数据库所有的DDL和DML(除了数据查询语句select)语句事件。
redo log和binlog的区别
（1）redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
（2）redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
（3）redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志
数据库数据恢复
首先，找到最近的一次全量备份
然后，从备份的时间点开始，将备份的 binlog 依次取出来,对数据进行恢复
执行更新操作步骤（两阶段提交，保证两个状态逻辑上一致）
执行器找引擎取对应行，如果在内存中就直接返回给执行器，不然就从磁盘读入
执行器拿到数据，更新，调用引擎接口写入新数据
引擎将新数据更新到内存，将更新操作记录到redolog ，redolog处于prepare 状态
执行器生产这个操作的binlog，把binlog 写到磁盘
执行器调用引擎提交事务接口，将redo log 改成commit 状态
事务隔离;为什么数据读写不一致
事务特性
ACID
(Atomicity、Condidtrncy、Isolation、Durability),原子性、一致性、隔离性、持久性
MySQL默认的隔离级别是REPEATABLE-READ（可重复读）
事务出现问题
脏读
一个事务在处理过程中读取了另外一个事务未提交的数据
不可重复读
一个事务范围内，多次查询某个数据，却得到不同的结果
幻读
事务非独立执行时发生的一种现象。
解决事务问题，需设定事务的隔离级别
① Serializable（串行化）：对于同一行记录，写、读都会加锁，出现读写冲突，必须等到前一个事务完成才执行
② Repeatable read（可重复读，默认级别）：在事务执行过程中看到的数据，总是跟启动时看到的数据一致，意思是其他事务修改对他没影响
③ Read committed (读已提交)：保证一个事物提交后才能被另外一个事务读取。另外一个事务不能读取该事物未提交的数据。
④ Read uncommitted (读未提交)：最低的事务隔离级别，一个事务还没提交时，它做的变更就能被别的事务看到。任何情况都无法保证。
不同隔离级别的实现
可串行化（Serializable）
用加锁的方式来避免并行访问
可重复读（Repeatable Read）
称作快照（Snapshot）隔离级别。读不加锁，只有写才加锁，读写互不阻塞，并发度相对于可串行化级别要高
读已提交（Read Committed）
MySQL的读已提交实际是语句级别快照。
读未提交
事务相关
事务启动方式
显示启动 begin 或者 start transaction，配套语句commit,回滚语句是rollback
set autocommit=0，将线程自动提交关闭，执行语句事务就启动，等到手动commit或者rollback 时或断开连接时关闭事务
回滚原理
记录更新时会记录一条回滚操作
不同时刻启动事务有不同的read-view ，同一记录在系统中可以存在多个版本，就是数据库多版本并发控制
在系统不需要回滚日志时删除，就是在系统没有比这个回滚日志更早的read-view时
查看数据库中存在长事务
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60
索引
索引的类型
位图索引
位图索引适用于字段值为可枚举的有限个数值的情况
位图索引使用二进制的数字串（bitMap）标识数据是否存在，1标识当前位置（序号)存在数据，0则表示当前位置没有数据。
哈希索引
哈希索引适用于等值检索，通过一次哈希计算即可定位数据的位置。
倒排索引
反向索引则是指某关键词和该词所在的文档之间的对应关系
BTREE索引
BTREE: 有序平衡N叉树, 每个节点有N个键值和N+1个指针, 指向N+1个子节点
B+Tree索引
B+Tree是BTree的一个变种，设d为树的度数，h为树的高度
全文索引
聚簇索引和非聚簇索引
聚簇索引：聚簇索引的顺序就是数据的物理存储顺序
非聚簇索引：索引顺序与数据物理排列顺序无关
聚簇索引的优点
索引的原理
主键索引
1、主键索引：即主索引，根据主键pk_clolum（length）建立索引，不允许重复，不允许空值；
唯一索引
2、唯一索引：用来建立索引的列的值必须是唯一的，允许空值
普通索引
3、普通索引：用表中的普通列构建的索引，没有任何限制
全文索引
4、全文索引：用大文本对象的列构建的索引（下一部分会讲解）
组合索引
5、组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值
索引的使用策略
什么时候要使用索引？
主键自动建立唯一索引；
经常作为查询条件在WHERE或者ORDER BY 语句中出现的列要建立索引；
作为排序的列要建立索引；
查询中与其他表关联的字段，外键关系建立索引
高并发条件下倾向组合索引；
用于聚合函数的列可以建立索引
什么时候不要使用索引？
经常增删改的列不要建立索引；
有大量重复的列不建立索引；
表记录太少不要建立索引。
索引失效的情况：
索引的优化
1、最左前缀
2、带索引的模糊查询优化
3、为检索的条件构建全文索引
4、使用短索引
常见的索引数据
hash表、有序树、搜索树
hash适用于等值查找，不适合范围查找
有序数组在等值查询和范围查询中性能非常优秀，但是只适用于静态存储引擎
二叉搜索树为了保证查询复杂度为Ologn 需要维护为平衡二叉树
在数据库中，为了减少树的高度（索引需要存储到磁盘，每次读取需要访问磁盘）使用N叉树
InnoDB 索引模型
使用的是B+树，索引分为主键索引（聚簇索引）和非主键索引（二级索引）
InnoDB是索引组织表，一般使用自增主键，这样不会因为插入重排，提高效率，主键长度越小，普通索引的叶子节点越小，占用空间越小
基于主键索引和普通索引查询区别
只需要搜索一颗B+树
先搜索K索引树、得到ID,通过ID再搜索D树
覆盖索引：可以减少树搜索次数，显著提升查询性能
最左前缀原则
在模糊查询 name like 张%
建立联合索引，考虑如何安排索引内的字段顺序
索引下推：在索引遍历过程中，对索引中包含的字段做判断，直接过滤掉不符合条件的记录
原则：尽量少访问资源是数据库设计的重要原则之一，在使用数据库时，设计表时，尽量减少资源消耗为目标
全局锁和表锁和行锁
基本信息
设计初衷——解决并发问题
锁分成三类：全局锁、表级锁、行锁
全局锁
对整个数据库进行加锁
mysql 全局读锁方法：FTWRL(Flush tables with read lock)
针对于像MyLSAM不支持事务引擎
其他线程的DML、DDL、更新事务提交语句都会被阻塞
使用场景：全库备份
缺陷
全库只读，备份期间不能执行更新，业务基本停摆
如果从从库备份，不能执行主库同步的binlog,导致主从不一致
使用可重复读级别开启事务
Mysql备份工具：mysqldump，使用single-transaction，在备份是会启动事务，确保拿到一致性视图
不使用set global readonly=true 方式原因
readonly 会被用来做其他逻辑，判断库是否是主库，修改global 变量方式影响大，在从库的super权限readonly无效
异常处理机制异常,FTWRL客户端断开 Mysql自动释放全局锁，而readonly 不会
表级锁
mysql两种表级锁：表锁、元数据锁
表锁
lock tables ...read/write
可使用unlock tables 释放锁
InnoDb可支持行锁，一般不使用lock tables 控制并发
MDL (metadata lock) 元数据锁
在增删查改加入读锁
当对表结构变更加入MD写锁
读锁不互斥、写锁互斥、读写锁互斥
行锁
两段锁协议
在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。
如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
死锁和死锁检测
出现循环资源依赖出现死锁
解决死锁方法
直接进入等待，直到超时
通过：innodb_lock_wait_timeout 设置 默认50s<br>
发起死锁检测
发生死锁后，主动回滚死锁链条的某一事务，让其他事物得以执行
innodb_deadlock_detect 这种为on
死锁检测消耗：每个被阻塞的线程都会检测是否因为自己加入导致死锁，复杂度O(n),1000个线程死锁检测100万量级，消耗大量cpu
解决死锁检测cpu消耗问题
关闭死锁检测，死锁后通过业务重试，业务无损；但是出现大量超时，业务有损
控制并发度
控制并发量，限制并发数量
修改mysql源码，对于相同行的更新，在进入引擎前排队
考虑将一行的数据改成逻辑上的多行减少锁冲突
事务是否是隔离的
事务的启动时机
begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动。如果你想要马上启动一个事务，可以使用 start transaction with consistent snapshot 这个命令。
启动方式
第一种启动方式，一致性视图是在执行第一个快照读语句时创建的；
<br>第二种启动方式，一致性视图是在执行 start transaction with consistent snapshot 时创建的。
两个视图
view视图
它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是 create view … ，而它的查询方法与表一样
一致性视图
InnoDB 在实现 MVCC 时用到的一致性读视图，即 consistent read view，用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现。
“快照”在 MVCC 里是怎么工作的？
在可重复读隔离级别下，事务在启动的时候就“拍了个快照”。注意：这个快照是基于整库的。
数据表中的一行记录，其实可能有多个版本（row），每个版本有自己的 row trx_id。
快照
实现一致性视图
InnoDB 利用了“所有数据都有多个版本”的这个特性，实现了“秒级创建快照”的能力。
事务视图可见性
版本未提交，不可见
版本已提交、但是在视图创建后提交，不可见
版本已提交，但是视图在创建前提交，可见
更新逻辑
更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。
当前读
事务中更新操作时候，尽管有事务一致性视图，但是保证在事务未提交时候，别的事务已经修改数据丢失
在select 加锁 加lock in share mode 或者 for update 的时候也是 当前读，一致性视图可见性失效
两段锁协议
事务c' 未提交，更新数据时写锁占用，当前事务B是当前读，读最新版本的数据，这时就会等待c'锁释放
可重复读怎么实现的？
核心是一致性读，但是事务更新操作的时候是当前读，如果当前读的记录行锁被占用就进入锁等待
读提交和可重复读的区别
可重复读：
在事务开始创建一致性视图，之后查询都用这个视图
在读提交隔离级别下，每个语句执行前都会重新计算新的视图
选择普通索引和唯一索引
对查询语句影响
唯一索引在查到满足条件时会停止检索，而普通索引会进行判断
普通索引检索数据在本页最后一条时会检索下一页
对更新语句影响
普通索引
使用change buffer
唯一索引
每次更新判断唯一索引是否冲突
记录更新在内存中
唯一索引
判断冲突，插入数据
普通索引
插入值
记录更新不在内存中
唯一索引
将数据读入内存、判断是否冲突、插入值
普通索引
更新记录在change buffer 中，语句执行结束
数据库成本最大操作时读入内存涉及的随机IO访问，大量插入会导致数据库
change buffer
change buffer 的使用场景
只限于用在普通索引中，不适合用于唯一索引
适用于读多写少业务，并且在写完后马上被访问的概率小，changebuffer使用效果更好，比如账单类、日志类系统
change buffer 和redo log 区别
redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗
change buffer merge 过程
从磁盘读入数据页到内存
重changge buffer 里找出change buffer 记录，依次应用到数据页，生成新数据页
写redo log。这个redo log 包含数据变更和change buffer 变更
两个索引区别
在查询上区别不大，主要是在更新时对性能影响大
为什么Mysql 有时会选错索引
优化器逻辑
找到最优执行方案，用最小的代价执行语句，扫描行是影响执行代价的因素之一、除此之外还有是否使用临时表、是否排序
扫描行怎么判断
根据索引的基数判断，基数越大，区分度越好
通过采样统计，选择N个数据页，统计页面上不同的值，得到平均值，然后乘以索引页数
统计信息有两种存储方式：持久化存储、存储在内存中
可以使用 force index(a) 进行强制设置索引，但是优化器会对索引进行权衡，会把回表操作的时间算进入，所以扫描行数并不是唯一影响优化器判断的条件，使用analyze table t 重新统计索引
索引选择异常和处理
force index 强行选择一个索引
通过sql 引导优化器选择正确索引
在某些场景下，可以新建更适合的索引，提供优化器做选择，或者删除误用索引
mysql 怎么保证主备一致
主要通过binlog日志保证主从一致，高可用架构都依赖binlog
主备一致原理
分支主题
分支主题
主备流程
Mysql 主备切换，实际生产使用双M流程
binlog 三种格式
statement
在 limit 的时候，主备库使用索引不一致的情况下，就会导致操作数据不一致
row
占用空间大，删除10万行就是要写十万行日志
优点：恢复数据，在更新数据时会保存之前数据，只要交换数据位置就能恢复数据
mixed
mixed 存在场景：statement 格式可能导致主备不一致，但是row 占用空间大，就采用折中办法
解决循环复制问题
双M结构
在A更新一条语句，生产的binlog 发给节点B,B也生成binlog，同时A也是B的备库，怎么防止循环更新
通过serverId 不同来区分，备库生成的binlog 的serverID 与主库一致，主库执行binlog，发现serverID一致就不执行
mysql企业常用集群架构
高可用Keepalived
概念
keepalived是一款c语言写的实现在linux系统上实现负载均衡和高可用的软件。它遵从于GNU是一款优秀的开源软件。keepalived观其名可知，保持存活，在网络里面就是保持在线了，也就是所谓的高可用或热备，用来防止单点故障的发生。
负载均衡
keepalived内置了对ipvs函数的调用支持。可以直接在keepalived中按照语法配置ipvs然后keepalived就可以实现对ipvs的配置。
高可用
keepalived是以VRRPVirtual Router Redundancy Protocol协议为实现基础的即虚拟路由冗余协议
作用
读写分离
第三方工具实现 Atlas、cobar、TDDL、mysql-proxy、阿米巴等
开发在代码中写死
0 条评论
下一页
