MySql
2019-04-25 10:07:14 0 举报AI智能生成
mysql笔记-持续更新中
作者其他创作
大纲/内容
逻辑架构
引擎
存储数据，提供读写接口
server层
连接器
管理连接，权限验证
查询缓存
查询缓存以key-value的形式存在内存中,key是查询语句
查询缓存失效频繁，只要有对表的更新，则该表所有的缓存都会失效
mysql提供按需使用方式,通过设置query_cache_type为DEMAND并在查询时显式指定使用缓存select SQL_CACHE * from T where ID=10
分析器
词法分析，语法分析
优化器
执行计划生成，索引选择
执行器
操作引擎，返回结果
执行前会执行权限验证
日志
redo log
innodb引擎
WAL(write ahead logging)
当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log里面，并更新内存，这个时候更新就算完成了
文件大小固定,循环写,写满后会覆盖
crash safe 保证及时数据库异常重启，之前提交的记录不会丢失
物理日志，记录在某数据页上作了什么修改
innodb_flush_log_at_trx_commit 设置为1时，每次事务的redo log都直接持久化到磁盘
binlog
sever层
逻辑日志，记录语句的原始逻辑
大小不固定，可追加写入，写到一定大小后切换文件
sync_binlog设置为1时，每次事务的binglog都持久化到磁盘
更新一行记录的步骤
执行器找引擎获取要更新的行
通过id主键更新
引擎通过树搜索找到这一行
如果这一行记录在内存中，直接返回给执行器，否则先从磁盘读入内存再返回
执行器拿到这一行数据，对数据更新，再调用引擎接口写入这行数据
引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log里，此时redo log处于prepare状态。然后告知执行器执行完成了，可以提交事务。
执行器生成这个操作的binglog，并把binlog写入磁盘
执行器调用引擎的提交事务接口，引擎吧刚写入的redo log改成提交状态，更新完成
事务
隔离性
脏读
不可重复读
幻读
隔离级别
读未提交
读提交
可重复读
串行化
事务隔离的实现
MVCC
consistent read view 一致性读视图
不同于view
mvcc的实现
用于支持RC与RR级别
row trx_id
undo log回滚日志
current read
更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）
可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图
读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图
回滚日志
事务的开启
start transaction并不会立即开启事务，会在头一条语句执行时才开启事务
start transaction with consistent snapshot可以立即开启事务
索引
哈希表结构在等值查询场景比较快
非主键索引
非主键索引的叶子节点内容是主键的值
基于非主键索引查询时需要先查找到主键值，再到主键索引去查找记录，这一过程成为回表
主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小
主键索引
主键索引的叶子节点存的是整行数据
为什么使用自增主键
记录按顺序写入，减少b+数的分裂过程
int/bigint类型最大8字节，好过很多字符串类型做主键，主键长度短，可以节省非主键索引空间
在只有一个索引，且该索引唯一的情况下，也可使用该key值作索引，减少回表次数
覆盖索引
覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段
联合索引
创建原则
如果通过调加微信 ixuexi66 获取最新一手资源整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的
原则就是空间
最左前缀原则
索引下推index condition pushdown
MySQL 5.6 引入的索引下推优化可以在索引遍历过
程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数
唯一索引与非唯一索引
通过唯一索引查找，在查找到记录后即可返回
非唯一索引仍需要向后遍历索引
更新过程
当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在
内存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change
buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的
时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方
式就能保证这个数据逻辑的正确性
change buffer
hange buffer 在内存中有拷贝，也会被写入到磁盘上
将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge
除了在访问changebuffer中的数据页和数据库正常关闭过程会发生merge，后台有线程定期merge
唯一索引由于必须判断数据页中是否已有记录，故必须载入页不能使用changebuffer
change buffer 用的是 buffer pool 里的内存
通过innodb_change_buffer_max_size 动态设置 ，值代表占buffer pool的百分比
使用场景
一个数据页做 merge 之前，change buffer 记录的变更
越多（也就是这个页面上要更新的次数越多），收益就越大
写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时
change buffer 的使用效果最好,账单类、日志类的系统
假设某业务更新模式是写入后马上会查询，则changebuffer不会减少随机io次数，反而增加维护成本
redo log主要节省随机写磁盘的io消耗(转换成顺序写),change buffer主要节省随机读磁盘的io消耗(更新数据时若不需要读取数据，不用加载进内存）
如何选择
查询性能不考虑极端情况，差不多,故考虑更新性能
使用机械硬盘时,change buffer机制的收效显著
历史数据库且采用机械硬盘式，尽量使用普通索引并增大change buffer
错选索引
优化器选择索引的依据
扫描行数
基数cardinality
analyze table t 重新统计
强制使用索引 select * from t force index(a)
锁
全局锁
Flush tables with read lock (FTWRL)全局加读锁
逻辑备份,且引擎不支持统一视图
表级锁
lock tables … read/write
另一类表级的锁是 MDL（metadata lock)
事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会
马上释放，而会等到整个事务提交后再释放
修改表结构时尽量使用WAIT/NOWAIT语句，避免长时间获取不到写锁阻塞后续查询请求
ALTER TABLE tbl_name NOWAIT add column
行锁
InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议
分支主题
如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放
死锁和死锁检测
innodb_lock_wait_timeout 锁等待超时时间
设置太长容易造成业务阻塞，设置太短容易造成简单锁等待超时
innodb_deadlock_detect 死锁检测
死锁检测消耗性能
死锁检测会检测正在某一行的请求，因此如果能在引擎层控制针对行的更新并发度，则可控制死锁检测消耗
0 条评论
下一页
