MySQL知识点

MySQL

  • 存储引擎
    • InnoDB（ 默认引擎，支持外键；支持事务；支持在线热备份）
    • MyISAM（不支持外键、事务和在线热备份；不支持行锁，只支持表锁；支持压缩表和空间数据索引）
    • Memory（存在内存中 一但机器重启 数据会丢失）
    • MERGE（用于一系列MyISAM表以逻辑方式组合在一起，作为一个对象去引用。（相当于union all））
  • 事务的四大特性
    • 原子性：所有操作要么全部成功，要么全部回滚
    • 一致性：事务执行前和执行后必须处于一致性状态
    • 隔离性：并发访问数据时，多个事务互相不干扰
    • 持久性：事务一旦提交，对数据库的更改就是永久的
  • 事务会出现的问题
    • 脏读：指的是当前事务可以读取到另外事务未提交的数据。
    • 不可重复读：同一事务内多次读取同一数据集合，读取到的数据是不一样的情况。
    • 幻影读：同一事务连续执行两次同样的 sql 语句，第二次的 sql 语句可能会返回之前不存在的行（特殊的不可重复读问题）
  • 四种事务隔离级别
    • 未提交读（READ UNCOMMITTED）
      • 事务中的修改，即使没有提交，对其他事务也是可见的。
      • 会出现如下问题：脏读 不可重复读 幻读
    • 提交读RC（READ COMMITTED）
      • 一个事务所做的修改在提交之前对其他事务是不可见的。
      • 会出现如下问题：不可重复读 幻读
    • 可重复读RR（REPEATABLE READ）
      • 保证在同一个事务中多次读取同样数据的结果是一样的。
      • 会出现如下问题：幻读
    • 可串行化（SERIALIZABLE）
      • 强制事务串行执行。
      • 没有问题
  • 锁
    • 行锁 和 表锁
      • 行锁
        • 记录锁 （锁定一个记录上的索引，而不是记录本身）
        • 间隙锁（锁定索引之间的间隙，但是不包含索引本身）
        • 临键锁（它是前两者的结合，不仅锁定一个记录上的索引，也锁定索引之间的间隙）
      • 表锁（意向锁（意向共享锁 意向排他锁 共享意向排他锁））
    • 排他锁 和 共享锁
      • 排他锁（其它事务想要读取或者写入一行数据，要等待事务释放锁）意向排他锁（获取多个排他锁）
      • 共享锁（其它事务只能并行读取一行数据）意向共享锁（读取多行数据）
  • 事务的实现
    • MVCC（多版本并发控制）（用于实现提交读和可重复读这两种隔离级别）
      • 相关概念：
        • 版本号（系统版本号，事务版本号）：每有一个事务，系统版本号就会加1；事务版本号指当前事务的版本。
        • 隐藏的列：每行记录都有两个隐藏的列 存储 创建版本号 和 回滚指针
        • Undo 日志：快照存储在 Undo 日志中，通过回滚指针把一个数据行的所有快照连接起来
      • 实现可重复读隔离级别（此时的事务版本号大于所有数据快照的创建版本号）
        • SELECT：必须读取小于等于当前事务版本的数据行快照。
          INSERT：将当前系统版本号作为数据行快照的创建版本号。
          DELETE：将当前系统版本号作为数据行快照的删除版本号。
          UPDATE：将当前系统版本号作为更新前的数据行快照的删除版本号，并将当前系统版本号作为更新后的数据行快照的创建版本号。可以理解为先执行 DELETE 后执行 INSERT。
        • 快照读（读取的是历史数据）：MVCC 的 SELECT 操作是快照中的数据（所以不用加锁）RC中每次都会生成快照
        • 当前读（读取的是数据库当前版本的数据）：
          MVCC 对修改操作（INSERT、UPDATE、DELETE）加锁，读取最新的数据。
        • SELECT 操作的不可重复读问题通过 MVCC 得到了解决
          UPDATE、DELETE 的不可重复读问题通过 记录锁 解决
          INSERT 的不可重复读问题是通过 临键锁（记录锁 + 间隙锁）解决的。
  • 索引
    • 数据结构：B+ Tree（Balance Tree 平衡树也是一棵查找树，所有叶子节点位于同一层）
      • B+树是B树的一种变形，它是基于B树和叶子节点顺序访问指针进行实现的
      • B+非叶子节点不存储数据，只存储索引，数据都存在叶子节点
      • B树的实现特性：AVL树，红黑树，跳表
      • B+树能更好的支持范围查询
    • InnoDB 的 B+Tree 索引分为主索引和辅助索引
      • 主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引。
      • 辅助索引的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找，这个过程也被称作回表。
    • 哈希索引：以 O(1) 时间进行查找，但是失去了有序性，无法用于排序、分组和范围查找。
    • 索引失效：
      • 在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引。
    • 索引优化：
      • 让选择性（区分度）最强的索引列放在前面。
      • 前缀索引（对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符。）
      • 索引覆盖（索引包含所有需要查询的字段的值，则无需访问主索引（回表操作））
      • 尽量减少请求的数据量（只返回必要的列和行）
      • 将大查询分割
      • 查询模糊匹配优化
        • 索引下推（ICP）（只能用于二级索引）（核心思想是将部分过滤条件下推到存储引擎层）
          允许 MySQL 在存储引擎层（如 InnoDB）直接利用索引来过滤数据，而不是将所有数据行全部返回到服务器层再进行过滤。减少从存储引擎到服务器层的数据传输量，从而提高查询效率。
      • 最左匹配原则：
        最左匹配原则要求查询条件必须从复合索引的最左列开始匹配，才能有效利用索引
    • 索引不适合的场景：
      • 数据量过少
      • 表数据需要频繁的插入删除
      • 字段区分度很低
    • 索引的优点：
      • 最重要的就是大大减少了要扫描的数据行
      • 同时避免服务器进行排序操作（索引本身是有序的）
      • 将随机I/O读取变成顺序I/O读取（索引中的相邻数据会存储在一起）
  • explain 分析 Select语句
  • 分库分表数据切分
    • 水平切分：将同一个表中的记录拆分到多个结构相同的表中。
    • 垂直切分：将一张表按列分成多个表，通常是按照列的关系（或热度）密集程度进行切分
  • 主从复制：binlog 线程、I/O 线程和 SQL 线程。
    • binlog 线程 ：负责将主服务器上的数据更改写入日志（Binary log）中。
    • I/O 线程 ：负责从主服务器上读取日志，并写入从服务器的中继日志（Relay log）。
    • SQL 线程 ：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放（Replay）。
  • 读写分离：主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。
  • 一句SQL语句的执行过程
    • 首先客户端发送请求到服务端，建立连接。
    • 服务端先看下查询缓存，对于更新某张表的SQL，该表的所有查询缓存都失效。（MySQL8.0取消）
    • 接着来到解析器，进行语法分析，一些系统关键字校验，校验语法是否合规。
    • 然后优化器进行SQL优化，比如怎么选择索引之类，然后生成执行计划。
    • 执行引擎去存储引擎查询需要更新的数据。
    • 存储引擎判断当前缓冲池中是否存在需要更新的数据，存在就直接返回，否则去从磁盘加载数据。
    • 执行引擎调用存储引擎API去更新数据。
    • 存储引擎更新数据，同时写入undo_log、redo_log信息。
    • 执行引擎写binlog，提交事务，流程结束。
  • SQL优化