Mysql运行机制
本文正在参加「技术专题19期 漫谈数据库技术」活动
sql执行过程
注：Mysql读取数据以数据页为单位，一页数据大小为16KB
模块与功能
- 连接器：管理连接，权限验证
- 连接器负责跟客户端建立连接、获取权限、维持和管理连接
- 查询缓存：MySQL8.0以后删除了该功能
- 执行过的语句会以KV形成缓存在内存中
- 查询之前先查缓存
- 不推荐使用缓存：数据表修改后，会删除所有相关缓存(表级别)
- MySQL8.0版本删除了查询缓存功能
- 分析器：词法分析和语法分析 —— 做什么
- 先做词法分析，识别SQL语句关键字
- 再做语法分析，判断SQL是否符合语法
- 优化器：执行计划生成，索引选择 —— 怎么做
- 决定如何使用索引以及关联表执行顺序
- 执行器：操作引擎，返回结果
- 主要工作是校验权限、调用执行引擎
- 首先校验用户对目标数据有无权限
- 执行器以行为粒度，调用存储引擎执行sql
- 在没有索引时，执行器会循环查询所有行
- 例：select * from t where age = 10; age设置为普通索引 normal
- 调用InnoDB引擎取表第一行数据，直接判断age是不是10，是则存入结果集，不是就跳过
- 接着调用引擎获取下一行，重复判断逻辑，直到最后一行。返回结果集给客户端。
- 存储引擎：存储数据，提供读写接口
- MyISAM
- MYSQL 5.5.5之前默认存储引擎
- 插入数据快
- 不支持事务
- 适用于查询效率要求高的
- InnoDB
- MYSQL 5.5.5之后默认存储引擎
- 支持事务、外键
- 支持崩溃修复能力和并发控制
- Memory
- 所有数据都在内存中，速度快
- 数据安全性差
- 适用于临时表
- Archive
- 数据压缩，空间利用率高
- 插入速度快
- 不支持索引，查询性能差
- 适用于日志信息归档
- MyISAM
存储结构和索引详解
存储结构
- 表空间 table space 包含了许多段；叶子节点段、非叶子节点段、rollback
- 表空间指的是数据表在硬盘上的存储空间
- 系统表空间 —— 系统用户都可以使用
- 独立表空间 —— 独立使用
- 每个表的数据默认放在独占表空间（ibd文件）
- 表空间指的是数据表在硬盘上的存储空间
- 段 segment 包含了许多区
- 数据段：B+树的叶子节点
- 索引段：B+树的非叶子节点
- InnoDB中，段由存储引擎自动管理
- 区 extent 区是页的分组，一个区包含了64个页 64*16 = 1024kb = 1mb
- 区是由连续页组成的空间，大小为 1MB
- 一次从磁盘申请4-5个区
- 页 page 一个页包含16kb数据
- 页是InnoDB中磁盘读写的最小逻辑单位，默认为16KB
- 一个数据页就是一个B+树的节点（B+Tree Node）
- 页的大小充分考虑了机械硬盘和SSD的最小单元（512B和4KB）， 16KB是它们最小公倍数
- 行 row
- InnoDB中的变长列
- 长度不固定的数据类型
- VARCHAR
- VARBINARY
- BLOB
- TEXT
- 长度不固定的数据类型
- 行溢出数据
- 由于InnoDB每个数据页容量有限，导致数据字段也是有限的
- 当数据字段过大时，InnoDB会使用行溢出机制
- 行溢出机制会把超长字段放入单独开辟的数据页
- 将超长字段指针指向新开辟的BLOB页
- 行记录格式
- InnoDB行记录格式主要分为两个时代
- Redundant / Compact (Antelope文件格式)
- Dynamic / Compressed (Barracuda文件格式)
- Redundant MySQL5.0之前的默认是Row Format
- 这里的col 1 2 记录的是字段的偏移量
- Header 存的是一些元数据，例如列的数量、偏移量单位、下一记录指针
- RowID 当数据库表没有建立索引，默认利用RowID建立索引，这是一个隐藏主键
- TxId 是事务ID
- Roll Pointer 是回滚指针
- Compact MySQL5.1默认Row Format 对原来的Redundant 做了优化
- 所有变长字段放在变长字段长度表
- NULL标志位用于记录某列为控
- Dynamic MySQL5.1默认Row Format
- Compressed
- 物理结构上与Dynamic类似
- 对表的数据行使用zlib算法进行了压缩存储
- 可以节约40%左右空间但对CPU的压力较大
索引详解
- 主流索引查找算法
- 线性查找 Linear Search
- 时间复杂度 O(n)
- 从第一个数据开始，逐个匹配
- 效率低
- 二分查找 Binary Search
- 时间复杂度 O(logN)
- 拿出有序数列中点位置作为比较对象
- 根据中点数据大小选取一半数据作为新的数列
- 每次缩小一半查找范围
- 无法应对数据存储不连续问题
- 二叉树查找 Binary Search Tree
- 时间复杂度 O(logN)
- 使用经典二叉树数据结构
- 由根节点开始查找
- 极端情况可能退化为链表，不平衡
- 平衡二叉树 AVL Tree
- 查找时与二叉搜索树相同
- 增删改时，通过旋转操作，维护树的平衡
- AVL树可以保证不会退化成线性查找
- 节点存放数据少
- B树 B Tree
- 线性数据结构和树的结合
- 通过多数据节点大大降低了树的高度
- 不需要旋转就可以保证平衡
- 查询连续数据较慢
- B+树 B+ Tree
- B+树由B树发展而来的一种数据结构
- B+树所有数据均在叶子节点
- B+树的所有数据形成一个线性表
- 非叶子节点都是索引
- 为什么说InnoDB索引即数据?
- InnoDB使用B+Tree作为索引数据结构
- B+Tree高度一般为2-4层，查找速度非常快
- InnoDB索引分为 聚簇索引(主索引)、辅助索引和联合索引
- 聚簇索引
- 在一颗B+Tree中，每个节点都是一个页，每次新建节点就会申请一个页空间
- 同一层的节点之间，通过页的结构构成了一个双向链表
- 非叶子节点，包括了多个索引行，每个索引行里存储索引键和指向下一层页面的指针
- 叶子节点中存储了关键字和行记录，在节点内部（也就是页结构的内部）记录之间是一个单向的链表
- 数据按照主键顺序存储
- 辅助索引
- 叶子节点并不包含行记录全部数据，叶子节点除包含键值以外包含了一个书签 bookmark
- InnoDB引擎通过书签找到索引对应的行数据，成为回表查询
- 组合索引
- 遵循最左匹配原则
- 组合索引单个索引值也可以生效，由于遵循最左匹配，建议将查询经常用到的索引字段放在组合索引的第一位，从而达到索引的优化。
- 覆盖索引
- 覆盖索引可以减少树的搜索次数，所以使用覆盖索引能提高查询效率，达到优化查询目的。
- 聚簇索引
组合索引
数据库执行计划分析与SQL优化
执行计划
- 如何确定所写的SQL语句最优？ 使用explain关键字可以模拟优化器执行SQL查询语句，分析查询语句效率以及原因。
- 通过Explain可以分析出的指标
- id 每个SELECT关键字对应的ID，唯一性
- id也决定了执行顺序
- 子查询ID自增，ID大的优先级高先执行
- 如果ID为null 表示一个结果集无需使用ID查询，一般不常见
- select_type 常见查询类型：SIMPLE、PRIMARK、UNION、SUBQUERY、DERIVED
- table 表别名 没有别名就取原表名
- type 表的访问方式，判断是否用到索引 常见类型（从优到劣） system > const > eq_ref > ref > range > index > ALL
- system 不常见，表中只有一条记录（等于系统表）
- const 表示通过索引一次扫描到，因为只匹配一行数据，mysql将这个查询转换成常量
- eq_ref 唯一索引，索引键在表中位移。通常出现于 primary、unique
- ref 查找和扫描混合，非唯一索引，匹配索引值的所有行
- range 检索给定范围行
- index 也算全表扫描 比all好点，因为index只遍历索引树(非叶子节点)，index从索引中读取，all从硬盘读取
- all 这个就是大家常说的全表扫描了，这个就不多做介绍，这个类型出现的越多代表sql性能越差
- possible_keys 可能涉及的索引
- key 实际使用到的索引
- key_len 索引长度
- ref
- rows 扫描读取的数据行数
- Extra 扩展信息
- id 每个SELECT关键字对应的ID，唯一性
