巨推学院

笔记:深入浅出MySQL,细说MySQL知识点

MySQL执行流程与架构设计max_connections默认是151,最大是10万;mysql参数有2个级别:session级别global级别预读取:局部性原理,当访问磁盘上的一块数据的时候,附近的其他数据也可能将要访问InnoDB的page默认是16 ...

笔记:深入浅出MySQL,细说MySQL知识点

MySQL执行流程与架构设计

max_connections默认是151,最大是10万;

mysql参数有2个级别:

  1. session级别
  2. global级别

预读取:

局部性原理,当访问磁盘上的一块数据的时候,附近的其他数据也可能将要访问

InnoDB的page默认是16KB;如果一个page存不下数据,会存入行溢出;

Buffer Pool:存储page的内存区域,提升读写效率;默认是128MB,是Innodb数据库的关键指标,可以占到物理内存的80%;

脏页:page中的数据与DB file中的数据不一致的时候,后台有很多线程在进行刷脏;

双写缓冲:page页的备份;

  • redo log:顺序IO,比随机IO效率快(寻址)记录数据页的改动,属于物理日志;大小固定,前面的内容会被覆盖;在innodb存储引擎层实现;用于崩溃恢复;
  • undo log:回滚日志,事务日志;
  • bin log:记录DDL和DML的语句,属于逻辑日志没有固定大小限制,内容可以追加;server层实现,可以被所有存储引擎使用用于数据恢复和主从复制

sql执行流程

  • 查询缓存(Query cache):mysql 8.0废除了该特性,该特性存在局限性;
  • 语法解析(Parser):产生解析树;
  • 预处理器(Pre-processor):语义解析,权限校验;
  • 优化器(Optimizer):表关联查询的顺序,基于成本的优化器;
  • 执行计划(Execution plan):一种数据结构,explain;
  • 执行器(Execution engine):调用存储引擎
  • 存储引擎(Storage engine):存储和管理表的方式

一个更新语句的执行流程

  • 从存储引擎拿到数据,返回给server层;
  • server层把page里面的行数据进行修改;
  • 记录undo log,redo log;
  • 调用存储引擎接口,写入buffer pool;
  • commit提交事务;

redo log分为两阶段提交:prepare和commit,保证bin log(事务)数据的一致性;

索引原理

什么是索引

数据库索引,是数据库管理系统(DBMS)中一个排序数据结构,以协助快速查询,更新数据库表中数据。

主键索引:不允许存在空值;

唯一索引:允许出现一个空值;

索引的数据结构

B+Tree特性:

  • B Tree能解决的问题,B+ Tree都能解决;
  • 扫库,扫表能力更强;
  • 磁盘读写能力更强;
  • 排序,范围查找能力更强;
  • 效率更加稳定;
  • 关键字与分叉是N:N的关系;
  • 只有叶子节点才存储数据,IO非常稳定,IO的深度进一步降低;
  • 叶子节点存在双向指针;

红黑树的特性:最长路径不超过最短路径的2倍;

InnoDB索引

索引即数据,数据即索引,数据放在了主键索引的叶子节点;

聚集索引:索引的键值的逻辑顺序,跟数据行的物理存储顺序一致;主键索引是聚集索引;

二级索引:叶子节点存储的是主键索引的值和该列的索引;会回表去主键索引中查询数据;

如果没有主键索引,会使用唯一索引;如果不存在任何索引,会存在一个隐藏的字段row id创建聚集索引;

创建索引的原则

  • 在离散度高的字段创建索引
  • 联合索引的最左匹配原则:必须要从第一个字段开始,不能跳过
  • 覆盖索引:select的列全部包含在了用到的索引中

在什么字段上创建索引?

  • where,join,order by
  • 索引个数不要过度
  • 散列度低的字段,不要建立索引
  • 随机无序或频繁更新的值,不适合作为主键
  • 创建复合索引时避免冗余索引

什么时候索引会失效?

  • 索引列上使用函数,表达式,运算符;
  • 出现类型隐式转换;
  • like条件字符前面带(最左前缀)ICP
  • 负向查询 <> != NOT IN(不一定)

笔记:深入浅出MySQL,细说MySQL知识点

MySQL事务与锁机制

数据库事务的定义

事务时数据库管理系统(DBMS)执行过程中的一个逻辑单位,由一个有限的数据库操作序列构成;

数据库事务的4大特性

  • 原子性(Atomicity)
  • 持久性(Durability)
  • 隔离性(Isolation)
  • 一致性(Consistency)

幻读

事务A首先根据条件索引得到N条数据,然后事务B改变了这N条数据之外的M条或者增添了M条符合事务A搜索条件的数据,导致事务A再次搜索发现有N+M条数据了,就产生了幻读;

只有插入产生的读不一致性才成为幻读;

脏读

所谓的脏读,其实就是读到了别的事务回滚前的脏数据

不可重复读

事务A首先读取了一条数据,然后执行逻辑的时候,事务B将这条数据改变了,然后事务A再次读取的时候,发现数据不匹配了,就是所谓的不可重复读了

SQL92标准

Read Uncommitted(未提交读)--未解决任何并发问题

事务未提交的数据对其他事务也是可见的,会出现脏读

Read Committed(已提交读)--解决脏读问题

一个事务开始之后,只能看到已提交的事务所做的修改,会出现不可重复读

Repeatable Read(可重复读)--解决不可重复读问题

在同一个事务中多次读取同样的数据结果是一样的,这种隔离级别未解决幻读的问题

Serializable(串行化)

最高的隔离级别,通过强制事务的串行执行

InnoDB对事务隔离级别的支持程度


笔记:深入浅出MySQL,细说MySQL知识点

MVCC

InnoDB为每行记录都实现了三个隐藏字段:

  • DB_ROW_ID:6子节,行标识
  • DB_TRX_ID:6子节,插入或更新行的最后一个事务的事务ID,自动递增(创建版本号)
  • DB_ROLL_PTR:7子节,回滚指针(删除版本号)指向undo log

MySQL锁的基本类型

行锁与表锁的区别

  • 锁定粒度:表锁 > 行锁
  • 加锁效率:表锁 > 行锁
  • 冲突概率:表锁 > 行锁
  • 并发性能:表锁 < 行锁

行锁:共享锁(Shared Locks)

共享锁,又称为读锁,简称S锁

共享锁就是多个事务对于同一数据可以共享一把锁,都能访问到数据,但是只能读不能修改;

行锁:排他锁(Exclusive Locks)

排他锁又称为写锁,简称X锁

排他锁不能与其他锁并存,如一个事务获取了一个数据行的排他锁,其他事务就不能再获取该行的锁(共享锁,排他锁),只有该获取了排他锁的事务是可以对数据进行读取和修改;

InnoDB的行锁是通过锁住索引来实现的;


笔记:深入浅出MySQL,细说MySQL知识点

MySQL优化

  • SQL语句,索引的优化;
  • 对表结构和存储引擎进行优化;
  • 架构优化缓存基于主从复制实现读写分离分库分表;
  • 数据库配置优化;
  • 硬件,操作系统优化;



不积跬步,无以至千里;不积小流,无以成江海!
分享到:
版权声明:本网页内容(包含但不限于文字、图片、视频)由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至zhanzhangtoutiao@163.com举报,一经查实,本站将立刻删除。
投诉

登录后发表评论

已有 3 人参与

评论

相关推荐

朴者

2038篇文章

TA的文章
广告
广告
广告
  • 今日热文
  • 本周热文
  • 本月热文
小编推荐    
热门搜索     更多>>>
返回顶部