分类

MySQL Basic

Categories: mysql

一、MySql原理

语句执行流程

如图：

略

  1. 连接
    1. MYSQL服务默认监听的端口是3306，客户端连接服务器的方式有很多。可以是同步的也可以是异步，可以是长连接也可以是短连接，可以是TCP也可以是Unix Socket。
      1. 客户端每产生一个连接或者会话，在服务端都会创建一个线程来处理。MySql会把长时间不活动的连接自动断开，默认是28800秒，8小时
      2. MySql允许最大的连接数是：151个，最大可以设置成100000
  2. 读取缓存
    1. MYSQL 自带一个缓存模块，默认是关闭的。
    2. MYSQL 不推荐使用自带的缓存，第一个：是他要求SQL语句必须一模一样中间多一个空格、字母大小写不同都会被认定SQL不一致。第二个：表里面任意的一条数据发生改变，都会到时缓存失效，对大量数据更新的应用，也不合适
  3. 语法解析和预处理
    1. 词法解析，就是把一个完整的SQL打碎成一个个单词，记录每个单词的类型，重哪里开始和哪里结束
    2. 语法解析，根据MYSQL定义的语法规则，根据SQL语句生成一个数据结构，这个数据结构称做：解析树
  4. 预处理器
    1. 预处理器会检查生成的解析树，比如，它会检查表和列名是否存在，检查名字和别名，保证没有歧义。
    2. 预处理后会生成新的解析树
  5. 查询优化
    1. 查询优化器就是根据解析树生成不同的执行计划，然后选择一种最优的执行计划。MySQL里面使用的是基于开销的优化器，哪种执行计划开销最小，就选择哪种
    2. MySQL优化器可以执行哪些优化类型：
      1. 当我们对多张表进行关联查询时，以哪个表的数据作为基准表
      2. 有多个索引可以使用时，选择哪个索引
  6. 执行计划
    1. 查询执行计划是一个数据结构，MySQL中使用EXPLAIN就可以看到执行计划的信息
  7. 执行引擎
    1. 使用执行计划调用api去操作存储引擎，最后把数据返回给客户端
  8. 存储引擎
    1. 任何一个存储引擎都有一个frm文件，这个是表结构定义的文件
    2. 不同存储引擎存放数据的方式也不一样
    3. 略
    4. 一张表的存储引擎是在创建表的时候指定的，使用ENGINE关键字
    5. 常见存储引擎的比较：
      1. myisam存储引擎包含3个文件，特点：
        1. 支持表级别的锁

          （插入和更新会锁表），不支持事务

        2. 拥有较高的插入和查询速度
        3. 存储了表的行数（count的速度更快）
        4. 适合只读之类的数据分析系统
      2. innodb存储引擎包含2个文件，特点：
        1. MySQL5.7中默认的存储引擎
        2. 支持事务，支持外键，因此数据的完整性、一致性更高
        3. 支持行级别、表级别的锁
        4. 支持读写并发，写不阻塞读（MVCC）
        5. 特殊的索引存放方式，可以减少IO，提升查询效率
        6. 适合经常更新表，存在读写并发或者有事务处理的业务系统
      3. memory存储引擎包含1个文件，特点
        1. 把数据存储在内存里面，读写的速度很快，但是数据库重启或者崩溃数据会全部丢失。
        2. 只合适做临时表
      4. CSV存储引擎包含3个文件
        1. 带有逗号的分隔值的文本文件，允许以CSV格式导入或者转储文件，方便与读写相同格式的脚本或者程序之间交换数据
        2. 不允许空行，不支持索引。格式相通，可以直接编辑，适合在不同数据库之间导入导出
      5. Archive存储引擎包含2个文件
        1. 用于存储和检索大量很少引用的历史、存档或者安全审计的信息
        2. 不支持索引，不支持update和delete

MySQL架构分层

略

InnoDB存储引擎的一些原理

缓冲池 Buffer Pool

1. InnoDB设计的一个内存的缓冲区，专属的名字叫Buffer Pool
2. 读取数据时，先判断是不是在这个内存区域内，如果是则直接获取。如果不是则读取后写入这个内存缓冲区；修改数据时，优先写入到Buffer Pool，而不是直接写入磁盘。
3. InnoDB设定了存储引擎从磁盘读取数据到内存的最小单位，叫做页。最小的单位默认是16K
4. 内存的数据页和磁盘的数据不一致时，我们把它叫做脏页，InnoDB有专门的后台线程把Buffer Pool中的数据写入磁盘，每隔一段时间就一次性把多次修改写入到磁盘，这个动作叫刷脏
5. Buffer Pool的作用就是提高读写效率

Redo log

6. 前提因为刷脏不是实时的，如果Buffer Pool里面的脏数据还没刷入磁盘时，数据宕机或者重启了，这些数据就会丢失。这种情况，InnoDB把所有对页面的修改操作写入一个日志文件。这个日志文件就是磁盘的redo log（重做文件）
7. 略
8. <font style="color:#DF2A3F;">疑问：同样是写磁盘，为什么不直接写到db file？为什么要先写到日志再写到磁盘？直接写磁盘（刷盘）是随机IO，而记录日志是顺序IO（连续写的），顺序IO的效率更高。本质上是数据的集中存储和分散存储的区别。先把修改写入日志文件，在保证内存数据的安全性下，可以延迟刷盘时机，进而提升系统的吞吐量。</font>
9. <font style="color:#000000;">redo log的特点：</font>
    1. <font style="color:#000000;">redo log是InnoDB存储引擎实现的，并非所有存储引擎都有。支持崩溃恢复是InnoDB的一个特性</font>
    2. <font style="color:#DF2A3F;">redo log是记录的是“在某个数据页做了什么修改”，属于物理操作</font>
    3. <font style="color:#000000;">redo log 大小是固定的，默认有2个文件：ib_logfile0和ib_logfile1，每个文件大小48M。前面的内容会被覆盖，</font><font style="color:#DF2A3F;">一旦写满就会触发Buffer Pool同步到磁盘</font>

Undo log

10. <font style="color:#DF2A3F;">undo log（撤销日志或回滚日志）记录事务发生前的数据状态</font>，分为insert undo log和update undo log。
11. <font style="color:#DF2A3F;">可以理解undo log记录的是反向操作</font>。<font style="color:#DF2A3F;">如果修改数据记录时出现异常，可以用undo log来实现回滚操作</font>
12. redo log和undo log与事务密切相关，统称为事务日志

MySQL 更新过程

13. 事务开始，从内存（Buffer Pool）或者磁盘（data file）读取包含这条数据的数据页，返回给Server的执行器
14. Server的执行器修改数据页的这一行数据
15. 记录修改前的值到undo log
16. 记录修改后的值到redo log
17. 调用存储引擎接口，记录数据页到Buffer Pool（修改后的值）
18. 事务提交

InnoDB总体架构

19. **Buffer Pool**
    1. <font style="color:#DF2A3F;">Buffer Pool缓存的是页面信息，包括数据页、索引页，默认的大小是128M</font>
    2. Buffer Pool使用LRU算法来管理缓冲池（链表的表现，不是传统的LRU，分成了young和old区）
    3. LRU的List分成两部分，靠近head的叫做new sublist用来存储热数据（我们称作为热区）；靠近tail的叫做old sublist（我们称作冷区）
    4. 新数据加入到Buffer Pool时，一律先放到冷区。如果一些预读数据没有被用到，会在old sublist（冷区）直接被淘汰。放到LRU list后，如果再次被访问，都把他移动到热区的head。如果热区的数据长时间没有被访问，会被先移动到冷区的head，最后慢慢在tail淘汰
20. **<font style="color:#DF2A3F;">待定</font>**

binlog

1. binlog以事件的形式记录了所有的DDL和DML语句（记录的是操作而不是数据，因此binlog属于逻辑日志）。binlog一般用来做主从复制和数据恢复
2. 和Redo log不一样，binlog文件内容是可以追加的，没有固定大小限制
3. 日志记录顺序
    1. 略
4. 上面流程的重点
    1. 先记录到内存，再写日志文件
    2. <font style="color:#DF2A3F;">先记录redo log日志，分为两个阶段prepare和commit</font>
    3. 存储引擎和Server层记录不同的日志，binlog是在Server层记录的
    4. <font style="color:#DF2A3F;">先记录redo log，再记录binlog</font>
5. 为什么没要两阶段提交？

二、MySql 索引

数据库索引

1. 是数据库管理系统（DBMS）中一个<font style="color:#DF2A3F;">排序</font>的<font style="color:#DF2A3F;">数据结构</font>，以协助快速查询、更新数据库表中的数据
2. 略 #### 索引的类型
1. InnoDB中索引的类型一共有3种
    1. 普通索引：也叫非唯一索引，是最普通的索引，没有任何限制
    2. 唯一索引：唯一索引要求键值不能重复。主键索引是特殊的唯一索引，多了一个限制要求键值不能为空
    3. 全文索引（fulltext）：针对比较大的数据，要解决like查询在全文匹配的时候效率低下的问题，可以创建全文索引，只有文本类型（char、varchar、text）才能创建全文索引

索引存储模型推演

1. **二叉查找树**
    1. 左子树的所有节点都小于父节点，右子树所有节点都大于父节点。投影到水平面后就是一个有序的线性表
    2. 能够实现快速的查找和快速的插入。但是，在最坏的情况下会退化成链表，时间复杂度O(n)。主要的原因是左右子树深度差太大，不够平衡
    3. 
    4. 略
2. **平衡二叉树（AVL Tree）**
    1. <font style="color:#DF2A3F;">左右子树的深度差的绝对值不超过1</font>
    2. 他是怎么保证左右子树的深度差不超过1 的呢？AVL Tree会进行<font style="color:#DF2A3F;">左旋和右旋操作</font>
    3. 略
    4. 略
    5. <font style="color:#DF2A3F;">平衡二叉树用于存储索引数据存在问题？</font>
        1. InnoDB操作磁盘的最小单位是一页，大小是16KB。平衡二叉树一个节点值存储一个键值+数据+引用，远远达不到16KB的容量。<font style="color:#DF2A3F;">在读取相等的数据下，IO操作比较多，不合理</font>
3. **多路平衡查找树（B Tree）**
    1. B Tree就能解决上面的问题
    2. <font style="color:#DF2A3F;">B Tree在枝节点和叶子节点同样存储键值、数据地址、节点引用</font>。<font style="color:#DF2A3F;">有一个特点：分叉数（路数）永远比关键字数多1，</font>例如：
    3. 略
    4. B Tree 在子节点超过定义的最大Max Degree时，会进行分裂和合并。节点的分裂和合并，其实就是InnoDB页的分裂和合并。因此为什么我们不建议在更新频繁的列上建索引，或者不要更新主键
    5. 
4. **B+ Tree**
    1. B Tree 的效率已经很高了，为什么还要使用B+Tree 呢？因为B+Tree有以下特点：
    2. 略
    3. 特点：
        1. <font style="color:#DF2A3F;">关键字的数量和路数是相等的</font>
        2. B+ Tree的根节点和枝节点不会存储数据，只有叶子节点才会存储数据
        3. B+ Tree的每个叶子节点增加了一个指向相邻叶子节点的指针，形成一个有序的链表结构
    4. <font style="color:#DF2A3F;">性能优势</font>：
        1. 是B Tree变种，B Tree能解决的问题，B+ Tree也能解决。<font style="color:#DF2A3F;">每个节点存储更多关键字，路数更多</font>
        2. <font style="color:#DF2A3F;">扫库，扫表能力更强</font>。<font style="color:#DF2A3F;">如果需要对表进行全表扫描，只需要遍历叶子节点就行</font>
        3. B+ Tree的磁盘读写能力相对于B Tree更强，原因是：<font style="color:#DF2A3F;">根节点和枝节点不保存数据，所以一个节点保存更多的关键字，一次磁盘IO加载更多的关键字</font>
        4. <font style="color:#DF2A3F;">排序能力更强</font>，因为叶子节点有下一个数据区的指针，数据形成了链表
        5. <font style="color:#DF2A3F;">效率更稳定，B+ Tree永远都是在叶子节点拿到数据，所以IO次数更稳定</font>
    5. 为什么不用红黑树：
        1. 红黑树也是BST树，通过变色和旋转赖保持平衡。
        2. 红黑树的特点：
            1. 节点分为红色和黑色
            2. 根节点必须是黑色
            3. 叶子节点都是黑色的NULL节点
            4. 红色节点的两个子节点都是黑色（不允许两个相邻的红色节点）
            5. 从任意的节点出发，到其叶子节点的路径中都包含相同数量的黑色节点
            6. 从根节点到叶子节点的最长路径不大于最短路径的2倍
        3. <font style="color:#DF2A3F;">为什么不用红黑树：1、只有两条路  2、不够平衡</font>
    6. 红黑树一般只放在内存里面用，例如TreeMap，可以用它来实现一致性哈希

B+ Tree的落地

1. MySQL的数据存储
    1. InnoDB有2个文件（.frm和.ibd），MyISAM有3个文件（.frm/.MYD/.MYI）。.frm文件是MySQL中表结构定义的文件
    2. MyISAM
        1. 一个是.MYD是数据文件，存储数据记录
        2. 一个是.MYI是索引文件，存储的是索引。一个索引就会有一颗B+ Tree，所有的B+ Tree都在MYI文件里面。
        3. MyISAM中，数据和索引是两个独立的文件，如下图：
        4. 略
    3. InnoDB
        1. InnoDB里面叶子节点直接存储了数据，索引即数据，数据即索引
        2. <font style="color:#DF2A3F;">聚集索引（Clustered Index）</font>是一种特殊的索引类型，它决定了数据在磁盘上的物理存储顺序，<font style="color:#DF2A3F;">聚集索引的叶子节点直接包含了表的数据行，而不是指向数据行的指针</font>。
        3. <font style="color:#DF2A3F;">InnoDB中如果有主键索引，那么主键索引就是聚集索引</font>。其他索引统一叫做“二级索引”。二级索引的叶子节点存储的是这条记录对应的主键的值。因此，主键索引比二级索引少扫描了一颗B+ Tree（避免了<font style="color:#DF2A3F;">回表</font>）
        4. 略
        5. <font style="color:#DF2A3F;">没有主键怎么办？？</font>
            1. 如果我们定义了主键（Primary Key）InnoDB会选择主键作为聚集索引
            2. 如果没有显示定义主键，InnoDB会选择第一个不包含NULL值的唯一索引作为主键索引
            3. 如果也没有这样的唯一索引，则InnoDB会选择内置的6个字节长的ROWID作为隐藏的聚集索引。

索引的使用规则

2. 索引不是越多越好
3. 创建索引原则：
    1. 列的离散度：列的重复值越多，离散度就越低；列的重复度越低，离散度就越高。<font style="color:#DF2A3F;">一般在离散度高的字段建立索引</font>
    2. 联合索引最左匹配原则：
        1. B+ Tree 是按照从左往右的顺序来建立索引树的，需要把常用的列放在最左边
        2. 例如创建：index(a,b,c)相当于创建index(a)/index(a,b)/index(a,b,c)
    3. 覆盖索引
        1. <font style="color:#DF2A3F;">回表</font>：非主键索引，我们先通过索引找到主键索引的键值，再通过键值查找出索引里面的数据。它比主键索引的扫描多扫描了一棵树，这个过程叫做回表
        2. 在二级索引中，不管是单列索引或者联合索引，如果select的数据列从索引中就能够取得，不必从数据区读取，这时候使用的索引就叫**<font style="color:#DF2A3F;">覆盖索引</font>**。覆盖索引减少了IO次数，减少了数据的访问量
    4. <font style="color:#DF2A3F;">索引条件下推（ICP）</font>
        1. <font style="color:#DF2A3F;">ICP默认开启，不需要认为干预</font>。<font style="color:#DF2A3F;">只适用于二级索引</font>，ICP的目标是减少访问表的完整行的读数量从减少IO操作，<font style="color:#DF2A3F;">意思是把过滤的动作在存储引擎做完，而不需要到Server层过滤</font>。
        2. 索引的比较是在存储引擎进行的，数据记录的比较是Server层进行。
        3. ICP，存储引擎过滤时的EXPLAIN