MySQL原理

#面试题 #原理

MySQL的底层原理浅析

本着就实际出发，讨论一条查询语句的执行过程，
包括连接,解析,优化,执行,返回结果,日志记录等7个阶段

一、连接阶段

  • 本地连接

     在生产环境中，往往采用数据库和项目一同部署在同一服务器上，这种方式采用的连接则是本地连接。 而又因为生产服务器大都是Linux或者Unix服务器， 本地连接的通常是Unix套接字连接。 这种方式不需要发送网络请求，也不收网络请求限制。

  • 网络连接

     网络连接这种常出现在开发环境，多个开发者共用一个数据库，通过远程连接内网或外网的形式访问。 这种方式实现是基于TCP/IP协议访问，进行ACK标志多次握手构建长连接请求。

  • 身份验证

    校验链接地址中配置的账号密码是否正确

  • 开发人员需要关注的细节

    • 最大连接数
      -- 查看当前链接数 --
SHOW STATUS LIKE 'Threads_connected';
-- 查看最大链接容量数 --
SHOW VARIABLES LIKE 'max_connections';
-- 单个连接消耗的内存大小
-- 此值不可过大，也不可过小，过小会造成复杂查询无法实现，过大会导致并发时消耗大量内存
SHOW VARIABLES LIKE 'thread_stack'

      此处应满足当前连接数小于最大链接容量，不然就超出了最大连接数

    • 连接池

      常见连接池有HikariCP(SpringBoot)、C3P0(老项目)、Druid(阿里巴巴开源)

在SQL解析前，会有一层缓存层，如若命中缓存层，则会直接跳过解析，优化以及执行阶段

  1. 在 MySQL 8以前是默认开启的，在8版本之后默认是关闭的
  2. 查看缓存是否开启 SHOW VARIABLES LIKE '%query_cache',结果为YES开启反之关闭
  3. 缓存在表进行了更新（增删改）操作后失效
  4. 若当前SQL与缓存SQL字符不一也不会命中，特别的，注释不一样也不会命中
  5. 每条SQL解析前都会去查找缓存

解析阶段

  • 词法分析：将SQL语句分解成一系列的词法单元（tokens）比如识别查询语句SELECT为关键字，account为表名
  • 语法分析：检查这些词法单元是否符合SQL语言的语法规则，例如语法不对则抛出You have an error in your SQL syntax的报错

优化阶段

  • 查询优化器

    • 正常来说，一条sql往往有着多种执行方式，查询优化器的作用就是在诸多方式中选择其认为最优的查询方法
    • 首先是索引的选择，优化器会根据索引选择它认为最优的索引
    • 其次是表的重新组合，这部分常常是SQL优化的关键，通过一些技巧可以让表的组合指定

  • 生成执行计划

执行阶段

  • 一条查询SQL的执行过程

    • FROM -> ON -> JOIN -> WHERE -> GROUP BY
    • -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT

返回结果阶段

日志记录阶段

  • 二进制日志
    1. binLog 二进制日志
      • 是把对数据库操作有变更的操作都记录到日志来，即有增删改都会被记录到binLog日志中。
      • binLog 通常是作为数据恢复和主从同步的关键
        • 数据恢复 使用mysqlbinlog工具来恢复
        • 主从同步 主节点开启binlog，从节点只需要根据主节点的binlog重放即可同步数据
      • binlog 通常有三种格式：ROW,STATEMENT,MIXED

        在MySQL5.7以前默认的格式为STATEMENT,之后则是默认展示ROW格式的

        • ROW 模式：记录被修改数据行数，不记录SQL语句的上下文信息（即SQL语句）
        • STATEMENT ： 对进行增删改的每条SQL都进行记录
        • MIXED：以STATEMENT为主,ROW为辅，在多数情况下用前者记录，只有遇到一些无法记录的函数用ROW记录
        • 三种模式总结
           ROW: 虽然能够详细记录那些行受影响，并且在重放或者恢复的时候保证数据的一致性， 但是如果是一条SQL更新五十行的时候就会出现50条日志，会造成大量的空间浪费和性能问题
           STATEMENT 虽然性能和IO都比较优秀，但是一条SQL执行会取决于前面的时间，数据变化而导致在出现问题需要重复的时候会出现不一致的问题
           MIXED虽然糅合了ROW和STATEMENT,但是仍然会出现像STATEMENT一样的造成数据不一致的问题
      • binLog 写入策略
         首先我们肯定希望在日志写入的时候能保证有效和准确性， 因为在之后的故障恢复时或者日志查看时都能提供有力的保证，同时也需要考虑写入日志到磁盘时对数据库的性能效率考量。

        对于Innodb引擎来说，日志不是直接写入磁盘的，通常是先放入binLog Cache中，找合适时机再写入磁盘中。
        通过sync_binlog参数来控制实际binLog的刷盘时机。取值为0到N

      • 如果 sync_binlog 为0,那么每次提交事务并不会立马刷入盘中，而是先存入 Page Cache 中，由系统判断何时刷入盘中
      • 如果 sync_binlog 为1，那么每次事务提交之后都会立马写入到磁盘中，此为强一致性配置
      • 如果 sync_binlog 为N, 那么每次事务提交之后 都会在积累的个数达到了N个事务才会刷入磁盘
    2. redoLog
    3. undoLog
  • 慢查询日志

Theme Pure | Powered by Hexo Footer HTML