RDBMS之MySQL的学习 | 青训营笔记

llx
73 阅读10分钟

这是我参与「第五届青训营 」伴学笔记创作活动的第 12 天

RDBMS

存储系统

  • 块存储、文件存储、对象存储、key-value存储

数据库系统

  • 关系型数据库、非关系型数据库

分布式架构

  • 数据分布策略、数据复制协议、分布式事务算法

事务：由一组sql语句组成的程序执行单元，需要满足acid特性

a：原子性：事务要么都成功，要么都不成功

c：一致性：关系数据的完整性和业务逻辑的一致性

i：隔离性：多个事务并发访问，一个事务不能影响其他事务的运行

d：持久性：事务完成后，保存在数据库中，不会回滚

SQL执行流程

需要经历SQL引擎、存储引擎、以及事务引擎等模块。而其中SQL引擎又分为Parser、Optimizer、Executor几个部分

SQL引擎包括了：

  • Paser：经过词法分析、语法分析生成语法树，然后对语法树进行合法性校验。

  • Optimizer：根据Parser产生的语法树，根据规则或者代价产生执行计划树。

  • Executor：根据计划树进行执行，常见的执行方式是火山模型。每个Operator调用Next操作，访问下层Operator获得下层返回的一行数据，返回上层

    • 优点：每个算子独立抽象实现，相互之间没有耦合，逻辑结构简单
    • 缺点：每计算一条数据有多次函数调用开销，导致CPU效率不高

存储引擎

存储引擎负责了数据的底层存储、管理和访问工作。各大RDBMS存储引擎的设计都有不少的差异，这里选择MySQL的InnoDB存储引擎来向大家做一个介绍：

  • Buffer Pool：存储引擎位于内存中的重要结构，用于缓存数据，减少磁盘IO的开销。
  • Page：数据存储的最基本单位页，一般为16KB。
  • B+ Tree：InnoDB中最常用的索引结构。
  • 在内存中的：Buffer Pool，Change Buffer， Log Buffer，Adaptive Hash Index
  • 在Disk(磁盘)：.ibd，Undo Tablespaces(.ibu)，.ibt，Redo Log
  • 存储事务的日志：Undo Log（逻辑日志，记录数据的增量变化），Redo Log

事务引擎

事务引擎实现了数据库的ACID能力，这里还是以MySQL的InnoDB为例来介绍数据库内部是通过哪些技术来实现ACID：

  • Atomicity：InnoDB中通过undo日志实现了数据库的原子性，通过Undo Log，数据库可以回滚到事务开始的状态；
  • Isolation：通过Undo Log实现MVCC（多版本并发控制），降低读写冲突。
  • Durability：通过Redo Log（一种WAL实现方式）来保证事务在提交后一定能持久化到磁盘中。
  • Consistency：一致性本质上是一种业务层的限制。

【补充知识MVCC】

MVCC：实现对数据库的并发访问，在编程语言中实现事务内存

MVCC实现原理

  • 隐式字段
  • undo日志
  • Read View：事务进行快照读的时候产生的读视图
  • 整体流程

当前读：读取的是记录的最新版本，对读取的记录进行加锁，悲观锁的实现

快照读：如不加锁的select操作，不加锁的非阻塞读

MVCC的好处：解决读-写冲突的无锁并发控制，为事务分配单向增长的时间戳，为每个修改保存一个版本

  • 在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能
  • 同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题

【相关概念】

  • 脏读：事务A在读取数据时，事务B新尚未提交数据，如回滚了，事务A读到了事务B的数据
  • 幻读：事务A对数据多次读取的时候，事务B新增了数据，两次数据前后读取不一致
  • 不可重复读：事务A读取数据经历时间很长，事务B在此时更改了操作，事务A再次读取的时候数据不重复了

隔离级别：RU读未提交（存在脏读、幻读和不可重复读），RC读已提交（解决脏读问题），RR可重复读（解决脏读和不可重复读的问题），序列化（全解决）

  • MySQL默认采用RR隔离级别，其他数据库采用RC隔离级别
  • MySQL中SQL标准要求是解决不可重复读，MySQL菜哦也能够gap lock（间隙锁：会锁一定区域）解决幻读问题，MySQL的序列化对读和写都加锁

MySQL的知识点

局部性原理

由于CPU寄存器和内存之间的性能差异较大，逐个读数据的形式会导致CPU工作期间的大量时间处于等待数据状态，利用局部性原理将数据预读到高速区。

MySQL中的InnoDB引擎一次会读取16KB的数据到内存

英特尔的CPU中一次性读取当前操作数据附近的64K数据（16页）到高速缓存区

索引是什么？

索引是一种能提高数据库查询效率的数据结构。可以比作是一本字典的目录，帮助快速找到对应的记录。

索引一般存储在磁盘的文件中，占用一定的物理空间。

MySQL索引类型

按数据结构维度

  • B+树索引：所有数据存储在叶字节点（链表结构），非叶子节点存储索引数据（不存储具体数据），复杂度O(logn)，适合范围查询
  • 哈希索引：适合等值查询，检索效率高，一次到位
  • R-Tree索引：用来对GIS数据类型创建SPATIAL索引

按物理存储维度

聚集索引：聚集索引就是以主键创建的索引，在叶子节点存储的是表中的数据。

非聚集索引：以非主键创建的索引，在叶子节点存储的是主键和索引列

逻辑维度

主键索引：一种特殊的唯一索引，不允许有null值

普通索引：mysql中基本索引类型，允许null值和重复值

联合索引：多个字段创建的索引，使用时遵循最左前缀原则

唯一索引：索引列中的值必须是唯一的，但是允许为null值

全文索引：MyISAM和InnoDB都支持全文索引，一般是文本类型

整体过程

当手动创建索引后，MySQL会先看一下当前表的存储引擎是谁，再判断表中是否有数据，没有数据则构建一些索引的信息，如索引字段，索引键的大小，索引的名字，然后直接写入对应的磁盘文件

MyISAM引擎的表数据写入.MYD文件，表结构写入.frm，索引数据写入.MYI文件，不支持聚簇索引；InnoDB表结构写入.frm，表数据和索引数据写入.ibd，支持聚簇索引。

当表中有数据，会先根据逻辑维度判断要创建什么类型的索引，然后再根据数据结构再次处理索引字段

  • InnoDB：因为有聚簇索引存在，所以非聚簇索引在与行数据建立关联时，存放的是主键/聚簇索引的字段值。行数据关联的是聚簇索引的索引键，所以InnoDB的非聚簇索引在查询时需要回表，再查一次聚簇索引才能得到数据
  • MyISAM：由于表数据在单独的.MYD文件中，因此可以直接以指针的形式关联起来。

索引什么时候失效？

  • 查询条件包含or，可能导致索引失效
  • 如果字段类型是字符串，where时一定用引号括起来，否则索引失效
  • like通配符可能导致索引失效
  • 联合索引查询时的条件列不是联合索引中的第一列，索引失效
  • 在索引列上使用mysql的内置函数，索引失效
  • 对索引列运算（+，-，*，/）
  • 索引字段上使用（! = < > not in ）时
  • 索引字段上使用is null，is not null
  • 左连接查询或者右连接查询查询关联的字段编码格式不一致导致索引失效

哪些场景不适合建立索引？

  • 数据量少的表
  • 更新频繁的表
  • where，groupby，order by等后面没有使用到的字段
  • 区分度低的字段（性别）不适合建立索引
  • 已经有冗余的索引的情况（已经有a,b的联合索引，不需要再单独建立a索引）

创建索引的方式：

-- ①通过CREATE语句创建
CREATE INDEX indexName ON tableName (columnName(length) [ASC|DESC]);
-- ②通过ALTER语句创建
ALTER TABLE tableName ADD INDEX indexName(columnName(length) [ASC|DESC]);
-- ③建表时通过DML语句创建
CREATE TABLE tableName(  
  columnName1 INT(8) NOT NULL,   
  columnName2 ....,
  .....,
  INDEX [indexName] (columnName(length))  
);

为什么要用B+树，不用二叉树？

一般二叉树：链表结构，相当于全表扫描

平衡二叉树：每次查找数据都要从磁盘中读取一个节点（磁盘块），每个节点只存储一个键值和数据

B树：一个节点可以存储更多的键值和数据，树的高度就降低了

B+树：非叶子节点上不存储数据，仅存储键值，页的默认大小是16kB，如果不存储数据的话，非叶子节点上就能存储更多的键值，进行磁盘的IO次数就会降低，数据按照顺序排列

MySQL实际上是对B+树做了改变，最底层的叶子节点是双向链表，不是单向的

什么是回表，如何减少回表？

回表：当查询的数据在索引树中找不到的时候，需要回到主键索引树中去获取

什么是覆盖索引？

select的数据列只用从索引中就能够获取，不必回表

最左前缀原则

可以是联合索引的最左N个字段，比如建立一个组合索引(a,b,c)，相当于建了（a）,(a,b),(a,b,c)三个索引，大大提高索引的复用能力

大表如何添加索引？

添加索引的时候是对表会加锁的

  1. 先创建一张跟A表数据结构相同的B表
  2. 在B表上添加需要加上的新索引
  3. 把原表A数据导到B表
  4. B表renameA表名，A表名更换其他

聚簇索引和非聚簇索引的根本区别：

  • 聚簇索引中，表数据和索引数据是按照相同顺序存储的，非聚簇索引则不是。
  • 聚簇索引在一张表中是唯一的，只能有一个，非聚簇索引则可以存在多个。
  • 聚簇索引在逻辑+物理上都是连续的，非聚簇索引则仅是逻辑上的连续。
  • 聚簇索引中找到了索引键就找到了行数据，但非聚簇索引还需要做一次回表查询。

InnoDB-非聚簇索引与MyISAM-非聚簇索引的区别：

  • InnoDB中的非聚簇索引是以聚簇索引的索引键，与具体的行数据建立关联关系的。
  • MyISAM中的非聚簇索引是以行数据的地址指针，与具体的行数据建立关联关系的。
avatar
文章
阅读
粉丝