MySQL笔记

发表于 更新于 分类于 阅读次数：
记录学习mysql面经过程中的心得体会

宏观定义

关系型数据库与非关系型数据的区别

  • 是否采用关系模型组织数据
  • 关系型能够保持数据一致性，非关系不能，但其基于键值对，具备较好的拓展性能够支持多种类型（图片文档）的数据存储
  • 关系型数据更新开销更小，非关系型读写效率更高
  • 关系型支持复杂查询（带 where 的子句查询）

什么是非关系型数据库

  • NoSQL 采用键值对形式存储数据
  • 读写性能很高，易于扩展，可分为内存型（Redis、Memcached）、文档型（MongoDB），或分布式的 HBase
  • 适用场景：日志系统、地理位置存储、数据量巨大、高可用

基本定义

为什么使用索引

  • 唯一性索引可以保证每一行数据的唯一性
  • 最主要的是可以大大加快数据的检索速度
  • 将随机 IO 变为顺序 IO，避免排序和临时表
  • 加速表与表之间的连接，实现数据的参考完整性

MySQL 执行 SQL 的流程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
flowchart TD
	客户端 --> 连接器
	subgraph A[ ]
		连接器 --> 分析器
		连接器 --> 查询缓存
		分析器 --> 查询缓存
		分析器 --> 优化器 --> 执行器
	end
 	查询缓存 -->命中缓存直接返回结果
	subgraph B[ ]
		存储引擎1
		存储引擎2
		存储引擎3
	end
	A --> B
  • 连接器：管理连接、权限验证
  • 查询缓存：命中缓存直接返回结果
  • 分析器：SQL 词法分析、语法分析（字段是否存在就在这一步判断）
  • 优化器：执行计划生成、选择索引，sql优化选择执行方案
  • 执行器：操作引擎、返回结果，判断用户执行权限后调用引擎接口
  • 存储引擎：存储数据、提供读写接口，若开启查询缓存则会缓存查询结果

MySQL 内部构造

  • 服务层，包括连接器、查询缓存、分析器、优化器、执行器，涵盖大多数核心服务功能，以及所有内置函数（日期、时间、数字和加密函数等），跨存储引擎功能（存储过程、触发器、视图）在此实现
  • 存储引擎层，负责数据的存储与提取，插件式架构，支持 Innodb、MyISAM、Memory 等多个存储引擎

核心定义

Innodb 为什么要用自增 id 作为主键

  • 自增 id 下新记录能顺序添加到当前索引节点的后续位置，能够保证当前页写满后自动开辟新的页
  • 非自增新记录会导致主键位置随机，需要对现有索引页频繁移动、分页，产生大量碎片，形成不够紧凑的索引结构，需要 optimize table 来重建并优化填充页面

数据库索引为什么采用 B+树而不是 B 树

  • B+树只要遍历叶子节点就可以实现整棵树的遍历
  • 数据库中基于范围的查询较频繁，B 树只能中序遍历所节点，效率太低

文件索引和数据库索引为什么使用 B+树

  • 文件和数据库都需要较大的存储，需要存储到磁盘上，

MyISAM 和 Innodb 实现 B 树索引的区别

  • data 域存储数据不一致，MyISAM 中 B+Tree 叶子节点 data 域存放数据记录地址，索引文件和数据文件是分离的，被称为“非聚集索引”，而 Innodb 中数据文件本身就是主索引，是按 B+Tree 组织的一个索引结构，索引的 key 就是主键，data 域存放完整的数据记录，其余皆是辅助索引，辅助索引 data 域同样存储的是主键的值不是地址，被称为“聚集索引”
  • 索引检索逻辑不一致，MyISAM 采用 B+Tree 搜索算法搜索索引并根据对应索引的 data 域地址读取数据记录，而 Innodb 在主索引搜索时只需找到索引的位置即可取出数据，在使用辅助索引搜索时则需要先取出主键的值再走一遍主索引（因此设计表时不建议使用过长的、非单调的字段作为主键，避免产生主索引频繁分裂）

数据库隔离级别

  • 未提交读，修改事务发生，但还没有提交前能够被其他事务读到这种修改，会导致脏读、幻读或不可重复读
  • 提交读，事务提交前其他事务对其不可见，可以阻止脏读，但依旧会导致幻读和不可重复读
  • 重复读，对一个记录读取多次记录都是相同的，可以阻止脏读和不可重复读，但依旧可能会存在幻读
  • 可串行化读，在并发下和串行化读取结果一致，不会发生脏读和幻读，可以防止脏读、不可重复读和幻读
隔离级别 脏读 不可重复读 幻读
READ-UNCOMMITTED 未提交读 √ √ √
READ-COMMITTED 提交读 × √ √
REPEATABLE-READ 重复读 × × √
SERIALIZABLE 可串行化读 × × ×

[!tip] 注意

  • MySQL InnoDB 引擎默认使用 REPEATABLE-READ重复读，但是使用了 Next-Key Lock 锁算法用于避免幻读，所以默认状态下可以完全满足事务的隔离性，可以达到可串行化读的标准，但不会造成性能损失
  • SQL Server 的重复读未做如上处理
  • 隔离级别越低，事务请求锁越少，大部分数据库系统采用提 READ-COMMITTED交读
  • 分布式事务下 InnoDB 依旧要使用 SERIALIZABLE 可串行化隔离级别

操作定义

Drop、Delete、Truncate 的相同与不同

  • delete 用来删除表的全部或一部分数据，执行后需要 commit 提交或 rollback 回滚，能够触发表的 delete 触发器
  • truncate 用来删除表的所有数据，可以清空自增序列，不能回滚，不触发触发器，比 delete 更快，占用空间更小
  • drop 用来从数据库中删除表，表中的所有数据、索引、权限都会被删除，不能回滚，不触发触发器
  • 删除表用 drop，删除表中部分数据用 delete，清空表而不删除表用 truncate

[!note]+ 详解

  1. delete 执行时是每次从表中删除一行并记录事务以便回滚，truncate 一次性清空表，不记录删除日志，无法恢复，不会触发触发器，执行速度快
  2. 表和索引空间占用，truncate 执行后表和索引空间占用会恢复到初始大小，delete 不会减少空间占用，drop 释放空间
  3. 一般情况下执行效率，drop > truncate > delete
  4. 应用范围，truncate 只针对表，delete 适用表和视图
  5. truncate、delete 只删数据，drop 删表结构和数据
  6. truncate 和不带 where 的 delete 只删除数据，不删结构，drop 删除表结构、被依赖约束 constrain、触发器 trigger、索引 index，但保留依赖于该表的存储过程/函数，其状态变更为 invalid
  7. delete 属于数据操作语言(DML)，会放到 roolback segment 回滚分片中，事务提交后才生效，会触发相应的触发器
  8. truncate、drop是数据定义语言(DDL)，操作立即生效，原数据不放到回滚分片中，不能回滚
  9. 没有备份情况下谨慎使用 drop、truncate
  10. truncate 速度快效率高，通过释放存储表数据所用的数据页来删除数据，并只在事务日志中记录页的释放
  11. 对由 foreign key 约束引用的表，不能使用 truncate，而是应当使用不带 where 的 delete，由于 truncate table 不记录在日志所以不能触发触发器

性能优化

MYSQL 性能优化

  • 为搜索字段创建索引
  • 避免使用 select * ，列出需要查询的字段
  • 垂直分割分表
  • 选择正确的存储引擎