MySQL Knowledge Graph

  1. Performance
  2. MySQL cluster
  3. Sharding
  4. Indexing
  5. MySQL architecture
  6. Lock
  7. MySQL事务 （ACID）

Architecture

  1. 最上层：connector

  2. 中间：SQL Layer

    包含：connection pool （包括，Auth，thread，check memory，cache），以及 Management Services & Utilities

    1. SQL interface 接受sql语句
    2. Parser 词法分析，判断能否执行 （语法树）
    3. Optimizer 优化器，你写的不够完美，通过：执行查询计划（explain）
      eg 多表关联：小结果集，驱动大结果集
    4. Cache and Buffer （MySql 8.0之后弃用）

  3. 下层：storage layer

    1. 就是跟文件打交道的。
    2. 多种引擎，可选：MyISAM（快），InnoDB（默认，功能更全），Memory（极快，易丢失）等等

对比 MyISAM 和 InnoDB

skip

现在基本都是 InnoDB。并发 + 支持事务 + 隔离性。

如果基本就是读写，数据量巨大，无并发，可以选MyIsam。

Physical 结构

MySQL 的文件，分2中：

  1. 索引文件
  2. 日志文件

其实都是存在 /var/lib/mysql/ 中。

日志文件

  1. errorlog

    默认开启，默认记录为 mysqld.log

  2. binlog（binary log）

    记录所有ddl和dml语句（数据的变化），但是不记录select语句。

    是一种压缩日志，以“事件”的形式保存。

    有好多个文件，因为每次 restart MySQL，生成一个新的 binlog。

  3. general query log

    增删改查，全都存。一般不开启。

  4. slow query log

    如果差的很慢，存一下。用于调优。

    long_query_time = 3, 意思是超过3s的，就记录到慢查询日志中。

  5. 重做日志

  6. 回滚日志

  7. 中继日志（用作：主从复制）

查询log的开启状况：

查看所有log On/Off: 

show varibles like ‘log_%’;

查看数据位置：

show varibles like ‘%datadir%’;

Indexing

索引的作用：

  1. 搜索
  2. 排序
  3. 索引下推 ICP
  4. 覆盖索引 （select字段作为索引）

查看 索引：show index from tuser；

索引的种类：

  1. 主键索引
  2. 唯一索引 （Unique Index）
  3. 组合索引
  4. 单列索引（Index），或普通索引

原理

MyISAM / InnoDB ： B+ tree 索引。

Memery：Hash 索引

B+ tree

B Tree 高度一般在 2～4 层。（其中，3 层就可以支持 20G，4层可以支持到 20TB）

平衡多路查找树（B-Tree）

B tree和B+ tree区别：

  1. 非leaf节点 是否存储数据。（B tree 存，B+ tree 不存）
  2. leaf 节点是一个 linked list

MySQL locking

  1. 乐观锁

    1. 程序实现：版本号 + 时间戳。
    2. 通常是Redis实现的。这里先不讲。

  2. 悲观锁

    我用了，你就不能用。（MySQL系统默认都是悲观锁）

    1. 表级锁（expensive，少用） 只有当 行锁升级为表锁时，才会用到表锁。
    2. 行级锁（InnoDB）
      1. 共享读锁 - 手动加
      2. 排他写锁 - 自动加

eg. session 1 write 时候，拿到 写锁，session2 连读都读不了。

Transaction 事务

四大特性ACID：

  1. Atomicity
  2. Consistency：执行前后，是稳定一致的。
  3. Isolation：多个tx之间，互不影响
  4. Durabilitity：一定全部写入磁盘。