mysql的复习总结

mysql的小总结

基础篇

执行一条 SQL 查询语句，期间发生了什么

• 

• 连接器：建立连接，管理连接、校验用户身份；

• 查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块；

• 解析 SQL，通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；

• 执行 SQL：执行 SQL 共有三个阶段：

  • 预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。
  • 优化阶段：基于查询成本的考虑， 选择查询成本最小的执行计划；
  • 执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；

innodb和myisam的区别

• InnoDB支持事务，MyISAM不支持
• InnoDB支持外键，而MyISAM不支持
• InnoDB是聚集索引，MyISAM是非聚集索引
• InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁
• InnoDB表必须有唯一索引（如主键），而Myisam可以没有
• Innodb不支持全文索引，而MyISAM支持全文索引

索引篇

什么是索引

• 那换到数据库中，索引的定义就是帮助存储引擎快速获取数据的一种数据结构，形象的说就是索引是数据的目录

索引的分类 4个角度

• 四个角度来分类索引。

  • 按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
  • 按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。
  • 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
  • 按「字段个数」分类：单列索引、联合索引。

• 按数据结构分类

• 从数据结构的角度来看，MySQL 常见索引有 B+Tree 索引、HASH 索引、Full-Text 索引

• 按物理存储分类

• 从物理存储的角度来看，索引分为聚簇索引（主键索引）、二级索引（辅助索引）。

• 这两个区别在前面也提到了：

  • 主键索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里；
  • 二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据。

• 所以，在查询时使用了二级索引，如果查询的数据能在二级索引里查询的到，那么就不需要回表，这个过程就是覆盖索引。如果查询的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点，获取到主键值后，然后再检索主键索引，就能查询到数据了，这个过程就是回表

• 按字段特性分类

从字段特性的角度来看，索引分为主键索引、唯一索引、普通索引、前缀索引。

• 按字段个数分类

从字段个数的角度来看，索引分为单列索引、联合索引（复合索引）。

• 建立在单列上的索引称为单列索引，比如主键索引；
• 建立在多列上的索引称为联合索引；

为什么 MySQL InnoDB 选择 B+tree 作为索引的数据结构

• 1、B+Tree vs B Tree

  B+Tree 只在叶子节点存储数据，而 B 树 的非叶子节点也要存储数据，所以 B+Tree 的单个节点的数据量更小，在相同的磁盘 I/O 次数下，就能查询更多的节点。

  另外，B+Tree 叶子节点采用的是双链表连接，适合 MySQL 中常见的基于范围的顺序查找，而 B 树无法做到这一点。

  2、B+Tree vs 二叉树

  对于有 N 个叶子节点的 B+Tree，其搜索复杂度为O(logdN)，其中 d 表示节点允许的最大子节点个数为 d 个。

  在实际的应用当中， d 值是大于100的，这样就保证了，即使数据达到千万级别时，B+Tree 的高度依然维持在 3~4 层左右，也就是说一次数据查询操作只需要做 3~4 次的磁盘 I/O 操作就能查询到目标数据。

  而二叉树的每个父节点的儿子节点个数只能是 2 个，意味着其搜索复杂度为 O(logN)，这已经比 B+Tree 高出不少，因此二叉树检索到目标数据所经历的磁盘 I/O 次数要更多。

  3、B+Tree vs Hash</