写在前面

MySQL

存储结构

  • B-Tree：所有节点都存放键和数据（不采用）
  • B+Tree：非叶子节点存放的是键，只有叶子节点存放数据，另外叶子节点有一条指向兄弟节点的指针

索引

  • 聚簇索引

    • 依赖有序数据、更新代价大，因为B+树是多路平衡树，所以保证插入数据的有序性，可以减少排序的时间消耗，且如果更新的话，由于叶子节点存放数据
    • 主键索引就是一种聚簇索引，而主键索引一般都是自增的，且不允许修改的，那么更新代价也会有所保证
    • 只有Innodb存储引擎存在

  • 非聚簇索引

    • 同样依赖有序，叶节点存放的是ID与索引字段，需要回表查询数据
    • 如果是覆盖索引，就不需要回表了，因为叶节点存放的就是需要查的数据（注意：上述是Innodb，而Myisam叶节点存放的是指针，覆盖索引只有查Id）
    • 聚簇索引得名来自于，在Innodb存储引擎中，只有.idb文件，其索引和数据存放在一个文件，而Myisam中右MYI和MY文件分开存储索引和数据

索引失效情况

  • 单列索引无法存储null值，联合索引是无法存储全null值

  • 失效

    • 前导索引失效（’%A’）
    • 最左匹配原则（A，B）单独查B失效
    • 如果mysql估计使用全表扫描要比使用索引快,则不使用索引（字段值少，就几个值，数据量再大也无所谓）
    • varchar字段，拼接整数条件，索引失效（MySQL 要会自动把字符串转为数字）
    • 拼接条件时，对索引字段进行了计算，函数
    • or条件两边有一个没建索引，就会索引失效
    • （a，b）联合索引，a用了范围查询，b就不会走索引，因为a的值此时是一个范围，不是固定的，在这个范围内b的值不是有序的，因此b字段用不上联合索引

事务与锁

事务概念

  • ACID 原子性Atomicity、一致性consistencey、隔离性Isolation、持久性Durability

  • 并发事务问题

    • 脏读

      • 读取了未提交事务的修改

    • 丢失修改：事务A修改，被事务B的修改给冲掉，两事务读取到的数据一致

    • 不可重复读: 事务A读取后，事务B修改了数据，事务A再读取发现和第一次不一致了

    • 幻读：和不可重复读很像，但是为新增或者删除导致的幻觉一样，读的不一致

事务级别

  • READ UNCOMMIT

    • 脏读、幻读、不可重复读

  • READ COMMIT

    • 解决脏读

  • READ REPEAT

    • 解决脏读、不可重复读，快照读通过MVCC机制解决幻读，当前读上解决幻读（通过临建锁 ）

  • SERIALIZABLE

    • ALL KILL

锁

  • 按加锁机制分

    • 乐观锁
    • 悲观锁

  • 按锁粒度分

    • 行锁(记录锁)

      • 间隙锁Gap Lock：存在于非唯一索引，锁定时，不包含where条件本身（not_unique = 24）,会锁住（24，X）
      • 临键锁Next-key-Lock：存在于非唯一索引中，锁定时，包含where条件本身（not_unique = 24）,会所住[24,X)的区间（下一个区间）
      • Innodb的行锁依赖于索引，如果加锁时没有使用到索引，就会退化为表锁

    • 表锁

    • 页锁

  • 按照兼容性分

    • 共享锁S

      • lock in share mode

    • 排他锁X

      • for update

    • 意向锁

      • 为表级锁
      • 与行的X锁和S锁兼容，并且在给行记录加S/X锁时，会给表加IS/IX，这样来防止加表锁时检查行锁情况的大量消耗

事务实现

  • 通过buffer pool logBuffer RedoLog 和Undo Log以及mvcc来实现事务，以Update为例：
    1. 首先会根据where条件找到数据所在页，然后放入Buffer Pool中.
    2. 执行update语句，修改Buffer Pool中的数据，也就是内存中的数据
    3. 针对这条update语句，会生成RedoLog对象，存入LogBuffer中，然后也会生成undolog用于事务回滚
    4. 如果事务提交，那么就会把RedoLog对象持久化，后续会对BufferPool落盘
    5. 如果事务回滚，则会利用undolog日志回滚
  • 当然，ACID的保障，原子性A主要是通过UndoLog回滚来保证，通过RedoLog来保证其持久性，然后通过MVCC与 锁机制保证其隔离性，自然最后就能保证一致性

MVCC

READ COMMIT级别下

  • 每次都会生成一个ReadViewer，来解决脏读

READ REPEAT级别下

  • 最开始读取的事务生成ReadView，在这个事务内沿用一个，这样可以实现重复读
  • 在当前读下，读取的都是最新的，如果是锁住单行，那么其他事务对行上下进行操作时，可能会产生幻读，此时借用Next-key-lock来锁住一个范围，解决幻读

三个辅助

  • ReadView

    • m_low_limit_id：trix_jd小于这个，说明当前修改的事务已经提交了，可以被读到
    • m_creator_trx_id：trix_id等于这个说明当前修改时自己所在的事务产生的
    • m_up_limit_id:trix_id小于up的话，是可见的
    • m_ids：创建readView时，当前活跃的事务ID列表，没有被提交的，是不可见的

  • undo-log

    • insert undo log

      • 提交后就删除，因为只对事务本身可见，对其他事务不可见

    • update undo log

      • 配合MVVC机制，实现不加锁解决一些事务问题

  • 辅助字段

    • 用于没有唯一主键，默认生成来代替主键的：DB_ROW_ID
    • 指向UndoLog的指针：DB
    • trix_id当前记录的事务版本id

Mysql执行流程

存储架构分为Server层 和 存储引擎层，其中Server曾主要负责建立连接、分析和执行SQL。存储层则负责数据的存储与提取

连接器：负责应用与数据库之间建立连接，比如用户名密码校验，和读写权限的校验

查询缓存：如果是Select语句那么就会进行读取缓存（鸡肋功能，8.0之后直接给去掉了）

解析SQL：通过解析器对SQL进行词法分析 和 语法分析，然后如果校验不过，就会直接返回语法错误

执行SQL

  • 预处理器：检查SQL中的表和类是否存在，将*转换为全字段

  • 优化器：为SQL执行指定一个执行计划，走不走索引也是在这一步来确定

  • 执行器：执行SQL从存储引擎中获取数据返回

注：在执行器与存储层交互的时候，有几个查询细节，比如索引下推(5.7之后)减少二级索引回表查询的次数，举个例子：将（a,b）索引a范围查询导致b失效的，推到存储引擎来判断，这样存储引擎直接判断失效，不再为了查询到a的数据而进行回表

主从复制

概念：通过binlog，slave会从master读取该文件的内容，然后进行数据的同步操作

为什么Mysql采用B+树作为其索引结构，而不采用B树？

  • B树上非叶节点需要存储数据，那么就减少了存储索引的数量，需要通过增加层数来支持，而B+树只用叶子节点来存储，虽然有数据冗余，但是能够减少高度，着用就能够减少IO次数（因为IO读取时，是分页读取，所以如果将叶节点通过三次IO就读取到的话，肯定是更加高效的）

Mysql底层引擎之间的区别？

Innodb与Myisam之间的区别：
- 第一，行级锁上，M是不支持的，I是支持的
- 第二，事务上，M是不支持的，I是支持的（展开讲事务隔离级别，默认是RR）
- 第三，外键上，M不支持，I支持
- 第四，在数据库发生异常崩溃后，M不支持恢复，I支持，主要是通过redolog
- 第五，关于MVCC，M是不支持的，而I是支持的
- 第六，比较重要的一点，两者在索引实现有点不一样，M的索引是非聚簇索引，并且其索引的B+树叶节点存放的是数据的指针地址，而I有非聚簇和聚簇索引，其主键索引B+树上叶节点存放的就是数据本身，然后其他的非聚簇索引则存放的主键和索引字段

聊聊如何进行SQL优化

创建索引需要注意

  • 尽量给where后的列，order by,group by,distinc中的字段加索引
  • 尽量加联合索引，不要建冗余索引,吧区分度最高的放在联合索引左侧，字段小的往左放，使用最频繁的往左放
  • 表中的索引不能太多，建议是不超过5个，mysql优化器会根据统一信息，对用到的索引进行评估，制定方案，太多了反而影响优化器的执行时间

写SQL需要注意

  • 不要使用select * ，消耗更多的cpu和io，无法使用覆盖索引，尽量列出所需列来
  • 避免使用子查询，尽量把子查询优化为join操作，这样可以使用索引
  • 避免关联太多的表，mysql最多支持60个，建议不超过5个，但实际财务类可能就超过了。
  • 能用union all就不要用union，性能更好，因为union要去重

当表达到百万级，如何优化分页查询

  • 利用主键索引，按照顺序存储的主键id，可以先查出来分页的id集合有哪些，再根据id回表查询，有联合索引也可以用联合索引，查出最大覆盖列的数据，再回表
  • 倒叙查询，如果数据位于后方