首页  思维导图  详情

MySql

2019-04-25 10:07:14   0  举报





AI智能生成

mysql笔记-持续更新中

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消耗(更新数据时若不需要读取数据，不用加载进内存）