MySQL
2020-10-11 14:15:37 0 举报AI智能生成
MYSQL
作者其他创作
大纲/内容
MySQL
架构和历史
逻辑架构
上层服务器层
包含了MySQL大多数的核心服务功能（查询解析、分析、优化、缓存、内置函数）
所有跨存储引擎的功能（存储过程、触发器、视图等）
下层存储引擎
负责MySQL中数据的存储和提取
行级锁
事务
索引
索引的统计信息
每个表、索引的磁盘页数量
每个表、索引的基数，数据行、索引长度
并发控制
锁粒度
MySQL提供了多种存储引擎，对应不同的锁策略和锁粒度但MySQL还是会使用各种有效的锁实现不同的目的
表锁
MySQL最基本的锁策略，也是开销最小的策略
最大程度的支持并发处理，带来了最大的锁开销
只在存储引擎层实现
ACID
原子性(atomicity)
一个事务必须被视为一个不可分割的最小工作单元
整个事务中的所有操作要么全部成功提交，要么全部失败回滚
一致性(consistency)
数据库从一个一致性状态转换到另一个一致性状态
隔离性(isolation)
一个事务所做的修改在最终提交前，对其他事务是不可见的
持久性(durability)
一旦事务提交，其所做的修改将被永久保存到数据库中
隔离级别
未提交读(Read Uncommitted)
事务中的修改，即使未提交，也对其他事务可见（脏读）
提交读(Read Committed)
一个事务的修改，只有提交后，才对其他事务可见
也被称为 不可重复读（两次查询结果内容不同，侧重于update、delete）
可重复读(Repeatable Read)
同一个事务中多次读取同样记录的结果是一致的（MySQL的默认隔离级别）
理论上会出现 幻读（第二次的查询结果数目比第一次多，侧重于insert，已被InnoDB通过MVCC机制解决）
该级别下解决了脏读、不可重复读、幻读
可串行化(Serializable)
强制事务串行执行
死锁
产生条件同Java死锁，而数据库实现了各种死锁检测和死锁超时机制
真正的数据冲突导致
由于存储引擎的实现方式导致
InnoDB目前的处理是：将持有最少行级排它锁的事务进行回滚
事务日志
介绍
存储引擎修改数据只需修改其内存拷贝，再把修改行为记录持久化到磁盘上的事务日志中，之后内存中被修改的数据慢慢刷回磁盘
写日志采用的是追加的方式，只需要操作一小块区域的顺序I/O（磁头），所以相对较快
通常称之为 预写式日志，修改数据需要写两次磁盘
快速写入日志，若发生宕机，存储引擎重启时能自动恢复这部分未修改的数据
具体的恢复方式由存储引擎而定
事务型存储引擎
NDB Cluster
InnoDB
第三方
XtraDB
PBXT
MySQL的事务
AutoCommit
MySQL默认采用自动提交模式（InnoDB类型表）
对于非事务型的表(MyISAM类型表、内存表)，无法修改该模式（无法回滚）
一些SQL语句命令在执行前会强制执行commit提交当前事务
alter table
lock table
事务是由下层存储引擎实现的，所以同一个事务中使用多种存储引擎是不可靠的
在同一个事务中混合使用了事务型表和非事务型表（InnoDB和MyISAM），如果该事务回滚，会导致数据库处于不一致的状态
锁
隐式锁定
InnoDB根据隔离级别在需要的时候自动加锁，只有在执行commit或者rollback时才会释放
所有的锁释放都是在同一时刻执行
显式锁定
Lock Table语句
除了事务禁用autocommit外，其余任何时刻不要显式使用该语句
Select ... for update语句
多版本并发控制（MVCC）
可重复读，事务第一次select时已经决定了本次事务能看到的数据提交读，事务每次select都会重新生成快照数据
通过保存数据在某个时间点的快照来实现的
不论需要执行多长时间，同一个事务看到的数据是一致的
参考可重复读
根据事务开始的时间不同，每个事务对同一个表看到的数据可以不同
只对提交读、可重复读两个隔离级别工作
类型
乐观并发控制
只读事务
悲观并发控制
写事务
InnoDB的实现
通过在每行数据记录后，增加两个隐藏列的保存
行的创建版本号？事务版本号？
行的删除版本号
回滚指针
DB_ROW_ID
隐藏列存储的是系统版本号
使用事务开启时的系统版本号作为事务版本号
每开启一个事务后，系统版本号都会自动递增
操作
select
创建时间 <= 当前事务版本 && （删除时间 == null || 删除时间 > 当前事务版本）
insert
为插入数据保存当前系统版本号作为创建标识
delete
为删除数据保存当前系统版本号作为删除标识
update
理论：插入一条新纪录，保存当前系统版本号作为创建标识，并更新原来行的删除标识为当前系统版本号
实际：由undo log备份旧数据，并将新数据中的回滚指针指向undo log中的旧数据
版本控制
在事务第一次select时生成一个快照，快照中保存了当前系列事务ID，用于判断数据可见性
目前即将被分配的事务版本号
未提交事务版本号列表
列表中最小的事务版本号
当前创建快照的事务版本号
判断当前事务版本号是否大于数据版本号 && 快照中变量的判断
读取方式
快照读
读取快照，走MVCC版本控制，也就是历史版本
即普通的Select
当前读
读取最新的数据
update、delete、insert、select...for update、select...lock in share mode
存储引擎
mysql默认的事务型引擎
采用mvcc来支持高并发，通过间隙锁防止幻读的出现
涉及概念
表空间
InnoDB的数据存储在表空间
由InnoDB管理的一个黑盒子，由一系列的数据文件组成
间隙锁
聚簇索引
二级索引
自适应哈希索引
覆盖索引
一致性视图
可预测性预读
注意
行级锁是针对通过索引检索的数据，不使用索引的修改是表锁
MyISAM
mysql5.1及之前的默认存储引擎
不支持事务和行级锁
崩溃后无法安全修复
无事务日志，所以断电宕机后无法恢复未写入磁盘的操作
延迟更新索引键
压缩表
其他
NDB集群引擎
基准测试
服务器性能剖析
Schema与数据类型优化
范式和反范式
范式
将一个大表拆分成多个小表（存放不同列），通过主键关联
优点
范式化，表更小且重复数据很少
缺点
需要关联才能获取完整的数据
可能导致一些索引失效
反范式
所有的数据都在一张表中，避免关联
建立一个组合索引，比关联更高效
基础
存储引擎用于快速找到记录的一种数据结构
优势
快速过滤、查找数据
更精确的查找下，锁定更少的行数据
额外的维护开销（插入删除更新）
占用额外的磁盘空间
页分裂、节点空间空余
B-Tree索引
InnoDB使用B+ Tree，使用B-Tree索引意味着值是有序的
每一个索引在InnoDB中对应一个B+ Tree
数据则都存储在B+ Tre中
有效范围
全值、前缀、最左前缀匹配
范围匹配、精确+范围匹配
主键索引（聚簇索引）
主键索引就是表数据文件
叶子节点中存放的是主键+整行数据
创建表时已建立该唯一索引
非主键索引（二级索引）
叶子节点存放的是非主键列+主键
先根据二级索引获取主键，通过主键和聚簇索引查找完整的行数据
比使用主键索引，多扫描一棵索引树（第二次扫描也称为回表）
通过add index 索引名(列名)创建
前缀索引
对于必须建立索引的长字段列，可以使用add key(列名(length))创建前缀索引
MySQL无法使用前缀索引做order by和group by，也无法做覆盖扫描
组合索引
索引合并策略
注意索引定义顺序和查询使用顺序
该索引的存储在物理上并不连续，所以在非叶子节点上建立双向链接是为了提高区间访问的性能，做到顺序访问
数据量大于2000，酌情考虑索引
最好使用一个与业务无关的自增字段作为主键
索引维护是需要开销的，索引越多开销越大
哈希索引
基于哈希表实现的，对索引列计算哈希码
索引中存放所有的哈希码
哈希表中存放哈希码、数据地址指针
只有Memory引擎显式支持，且支持非唯一哈希索引
补充
InnoDB的自适应哈希索引
对频繁访问的索引，基于B-Tree索引建立哈希索引
该功能是完全自动、内部调节的行为
索引列数据不能太长，尽量为整数型
为防止哈希冲突时，查询结果的不正确，where应该+哈希值+对应列值
哈希列可以通过触发器维护（还是使用AOP维护吧）
全文索引
查找文本中的关键字，同分词器Lucene
使用match(索列) against("***")操作，而不是普通的where条件操作
ADD FULLTEXT index_name (column_list)
独立的列
使用索引的列不能是表达式的一部分（即不是单独放在比较符号的一侧），也不能是函数的参数
前缀索引、索引选择性
使用索引做排序
冗余索引和重复索引
对于冗余索引，不需要考虑性能时，尽量删除旧索引再生成新的
重复索引，即相同列，相同列顺序，相同功能的索引
避免多个范围条件的使用
查询性能
真正重要的是响应时间
一个查询可以看成是一系列子任务组成的大任务
消除一些子任务（额外操作）
减少子任务的执行次数（重复操作）
让子任务执行的更快（执行太慢）
查询生命周期
慢查询
访问了太多的行数据 或太多的列
不需要的行数据
重复访问
取出所有列
查询扫描了过多的数据
响应时间
扫描行数和返回行数
扫描方式
全表扫描 all
索引扫描
范围访问
单值访问
重构查询
目标
找到一个更优的方法获得实际需要的结果
思路
分治法，切分问题
分解关联查询为多个单表查询
查询的执行
过程
客户端发送一条查询给MySQL服务器
服务器接受完整条消息后
先检查查询缓存，如果命中缓存，则立刻返回存储结果
否则进入下一阶段
服务器进行SQL解析，预处理，由优化器生成对应的执行计划
MySQL根据执行计划，调用存储引擎的API执行查询
返回结果给客户端，客户端全部接收后，缓存到内存中
查询的状态
查询缓存
查询优化
解析SQL
验证SQL语法，关键字顺序，引号
将SQL解析成一颗解析树
预处理
检查解析树
查询优化器生成最优的执行计划
优化特定查询
关联查询
join表A on的a列上有索引
group by和order by只涉及一张表的索引
子查询
可能的话 尽量使用关联查询
具体情况 具体分析 版本不同
分组和去重
分页
可以使用覆盖索引+延迟关联
记录书签位置，避免使用偏移量
使用用户自定义变量
高级特性
分区表
查询时优化器会根据分区定义过滤不需要的分区
避免大量数据下，索引维护困难且开销大、回表随机I/O的问题
分区表就是张普通表，创建开销小，分而治之
逻辑上是一张表，实际由多个物理子表组成
创建表时使用partition by子句定义每个分区存放的数据
将数据按照一个较粗的粒度分在不同的表中
创建索引只是在各个子表中分别创建一个相同的索引，所以没有全局索引！！
分区策略
依据
查询可以过滤大部分不需要的分区
分区本身不会带来很多额外代价
将数据存放不同分区，由where定位数据范围（少数分区），全量扫描数据
分离冷热数据，在热点分区中使用索引，有效使用内存中的缓存
可能遇到的问题
null导致至少扫描两个分区
5.5按列本身分区，可以忽略该问题
分区列和索引列不匹配
where中只有索引过滤，无分区过滤，导致扫描所有子表
选择分区的成本可能很高
（范围分区）确定数据所在分区，随着分区数增长 成本也越高
打开并锁住所有子表成本可能很高
当查询访问分区时，会进行打开并锁住的操作，该操作发生在过滤分区之前尽量批量操作，减少额外开销次数；或者限制分区个数
维护分区的成本可能很高
如重组分区
where中一定要带有分区列
查询时 只能使用分区列本身进行比较才能过滤分区基于分区列的表达式是无法过滤分区的
关联查询时，关联条件应为分区列这种过滤时运行时过滤，不是查询优化阶段的
视图
它是一个虚拟表，不存放任何数据
不支持触发器
不支持索引
数据来源于定义的查询SQL从其他表中生成的（中间商）
数据生产算法
合并算法
临时表算法
数据操作
凡是使用临时表的数据无法进行操作
被操作的列必须来自同一个表
存储代码
触发器
存储过程
函数
事件
自然语言全文索引
布尔全文索引
分布式事务
内部XA事务
协调存储引擎和二进制日志
MySQL本身是插件式架构，一个跨引擎的事务需要一个协调者
存储引擎提交的同时，需要将提交信息的SQL语句写入二进制日志
可以将日志写入看成一个独立的引擎
一次二进制日志持久化，两次事务日志持久化
外部XA事务
在多个服务器之间同步数据
保存查询返回的完整结果
监听查询中涉及的表，若表发生变量，则废除缓存中该表所有数据
不缓存
查询语句中包含不确定的数据，则不缓存；如时间、存储函数等
这种查询语句会查询缓存，但不会命中，因为这种查询结果不会被缓存
查缓存时，还未解析SQL，所以无法得知该查询是否包含某类函数
MySQL再此之前只会检查语句是否以SEL开头
查询缓存有一把全局排他锁，无论缓存命中、失效，这个动作都是串行的若缓存设置过大，缓存失效会导致数据库服务假死一会
缓存未命中
原因
该查询无法被缓存
该查询第一次执行
查询缓存的内存满了，某些缓存被踢出群聊
缓存有更新而失效了
特殊
查询结果还未来得及缓存
失效操作过多
服务器设置
配置原理
读取/etc/中的my.cnf
服务器端的配置在[mysqld]模块下，客服端[client]模块下
内存分配
innodb缓冲池
存放索引，数据页，锁，脏页数据等
innodb_buffer_pool_size
InnoDB事务日志
innodb_log_file_size
innodb_log_buffer_size
线程缓存
连接池的作用，为迅速响应连接请求
thread_cache_size
innodb表缓存
innodb_open_files
一次保持打开状态的最大.ibd文件数
清空表数据：TRUNCATE [TABLE] tbl_name
innodb的I/O行为
日志
用日志减少事务提交的开销
二进制日志
redo log
undo log
日志是追加操作，属于顺序IO，数据则需要修改对应的不同地址的信息，属于随机IO
表数据存放在表空间中，表空间里有当前表的数据、所有索引等数据
本质上是一个或多个磁盘文件组成的虚拟文件系统
即 .ibd后缀文件
种类
独立表空间
默认innodb_file_per_table 为 ON
共享表空间
data目录相当于共享表空间
合理设置表空间大小，扩展后的表空间无法自动缩小
双写缓冲
用于保证数据页持久化的原子性
Mysql并发
innodb_thread_concurrency控制事务提交的并发量
并发量用完后，有两段处理
第一阶段休眠innodb_thread_sleep_delay微秒，默认10000微妙
第二阶段放入一个等待队列
硬件
瓶颈
最常见的瓶颈是CPU、I/O资源
CPU数量、运行速度
磁盘I/O、网络吞吐
内存
I/O
随机I/O
多次寻道+旋转+传输
索引下需要多次遍历节点
顺序I/O
一次寻道+旋转+传输
读取同一个叶子节点或相邻叶子节点
不论内存还是磁盘，顺序I/O更快
按照硬件数据结构，每次读取的并不是单条数据
譬如缓存行、数据页等
所以随机读会造成空间浪费
能负担，加内存最好了
复制
方式
通过二进制日志同步
基于行的复制 row模式
基于语句的复制 statement模式
工作流程
主库上的dump线程用于读取二进制日志，给从库发送日志事件
事件传输完毕则wait
存在新事件后，唤醒dump线程
备库启动一个IO线程，用于连接主库并请求发送日志事件，读取事件，写入relay log
备库(SQL线程)coordinate读取日志、分发事务由多个Work线程数据更新
同一个事务必须放入同一个Work
更新同一行的多个事务必须放入同一个Work
原则
备库只能有一个主库
每个主备库都应该有一个唯一的server-id
log_slave_updates=1，可以将复制事件记录到自身bin log中
过滤器
在主库上过滤记录到binlog的事件
在备库上过滤记录到relaylog的事件
拓扑
一主多备
主主复制（双向复制）
主动 - 主动模式
主动 - 被动模式
问题
日志数据损坏或丢失
使用混合事务型和非事务型表
不确定语句
主备存储引擎不同
备库数据改变
不唯一或未定义的服务器ID
依赖可能丢失的临时表
过大的延迟复制
来自主库过大的包
可扩展性
向上扩展
增加硬件
向外扩展
结构
读写分离
主主、一主多备等
拆分
按功能拆分
数据分片
向内扩展
数据清除、转移，如冷热分离
分布式数据库
独立的键值数据库服务器，同NoSQL-Redis
Clustrix
备份
需求
恢复点目标 PRO
备份；数据丢的多
恢复时间点目标 RTO
备份+二进制日志备份
方案
在线/离线备份
逻辑/物理备份
全/增量/差异备份
差异备份 指对自上次全备份后所有改变的部分做的备份
增量备份 指从上次任意类型的备份后所有的修改做的备份
收藏
收藏
0 条评论
下一页
为你推荐
查看更多
