mysql

· 2085 words · 5 minute read

mysql 起源&基础 🔗


创建表时的存储引擎 🔗

 存储引擎-是创建表时设置，存储引擎是表级别的使用

 mysql常见可配置的存储引擎：

  • innodb
    • 只有数据文件也是索引文件，聚集索引，一个表对应一个文件
    • innodb存储引擎的文件结构
      • 数据按行存储 类似网络协议栈，由页头，区头，段头，表头
      • 表结构文件（user.frm， 表名 + .frm组成整个名称）
      • 日志文件（redo文件等）
  • mysiam
    • 既有数据文件，又有索引文件
    • 不支持事务

一般索引的结构 🔗

数据库系统内幕：存储引擎
    单机角度介绍 磁盘存储格式，索引数据结构，事务处理。
    其他的查询计划，查询优化等不介绍，
    负责内存和磁盘上的存储、检索。

    数据库是构建在存储引擎之上的，提供了表结构，sql，索引，事务等特性。
    数据库使用文件来存储数据，但不依赖于目录和文件系统来定位数据。
  • 红黑树，AVL等二叉树导致树的深度可能很深。
  • 为了降低树的高度，多叉树。
    • B树 mongodb，允许一个树节点中存多个数据，中间节点存了数据
      • etcd
    • B+树 innodb
      • 只有叶子节点存数据
      • 叶子节点根据key从小到大，叶子之间有指针，方便区间查询

sql优化 🔗

  • 搜索字段上建索引，同时不在索引字段上使用聚合函数
  • 避免select *，这样不能使用覆盖索引
    • 在innodb引擎下，select a,b,c 可以走辅助索引找到所有的数据，避免走到聚集索引上，减少一次聚集索引查询过程
    • 减少数据传输量，limit
  • 不使用like ‘%xx’, 多用区间操作, 能用 between 就不要用 in
  • 复合索引最左前缀 (不是指SQL语句的where顺序要和复合索引一致)
    • 复合索引底层存储也是一颗B+树，只不过排序key=（a,b,c）, 这样是复合索引使用时需要满足最左前缀的原理
  • 避免使用子查询， 子查询产生的临时表再扫描无索引可走，会全表扫描
  • explain 显示select语句的执行计划
    • select_type: simple等
    • type： 判断查询是否高效，const, ref, range, index,all
    • extra： using index, using filesort(不能通过索引达到排序), using temporary(使用了临时表)

锁 🔗

参考 官网 mysql reference manual, innoDB Locking。

  • InnoDB 检索数据走索引是行锁，不走索引是表锁。

  • 行级锁

    • Record Lockx 对索引项加锁，锁定一条记录
    • Gap Lockx 锁定一个范围，不包含记录本身
    • Next-Key Lock = gap + record lock, 锁定一个范围 并锁定记录本身 左开右闭 (]

  • 表级锁

  • 死锁的必要条件：互斥，请求并保留，不剥夺，循环等待。

    • show engine innodb status
    • show open tables where in_use>0;
    • show full processlist;

事务原理 🔗

  • mysql transaction （ACID 锁、mvcc保证隔离性， undo log 保证原子和一致性，relog 保证持久性）
    • 隔离级别在终端进行模拟
    • 隔离级别：事务之间的可见性。 select @@global.tx_isolation
    • read uncommited （tranx will read dity data）
    • read committed (no repeatable read)
      • 不可重复读（事务中两次读的结果不一致，事务执行select时没有获得读锁，或者释放了读锁）
      • 事务1等读到事务2的commit， 从数据库对事务ACID角度上讲，违背了隔离性
    • repeatable read (default mode)
      • 在RR上事务启动时拿到数据库的一个静止快照
      • 备份时需要一致性的读
      • insert，delete存在幻读（innodb的间隙锁解决）
    • seriablizable
    • mvcc （ read-view 回滚段）
      • Read View生成时机的不同，从而造成RC, RR级别下快照读的结果的不同
      • 在RR级别下对第一次快照读会快照Read View，之后的快照读获取的都是同一个Read View。
      • 在RC级别下的事务中可以看到别的事务提交的更新，每次快照读都会新生成一个快照和Read View。
  • 重要的日志: undo log, redo log
    • undo log:
      • 记录某数据被修改前的值，可以用来在事务失败时进行rollback，帮助事务回滚
      • undo log（undo data）中记录了历史版本，帮助实现MVCC功能, 保证原子性，一致性
    • redo log:
      • InnoDB将数据缓存到内存的buffer pool中，为保证事务的持久性，事务提交前先写redo log（WAL）持久化
      • 记录的是页的物理修改操作（pageid, offset, field, value）
      • log writer 线程确保redo log fsync操作写入磁盘

高可用与数据同步 🔗

  • Master-Slave 原理 （Binlog, Relay log）
    • 异步复制
      • 主库
        • 当从库连接时，binlog dump thread发送binlog event,并持有event的lock
        • IO thread 读完后释放lock
      • 从库
        • IO thread读binlog event，生成relay log
        • SQL thread 读relay log，执行
    • Binlog： SQL(增删改查，DDL)语句按 提交顺序 生成的日志，用于数据回滚，异步复制
      • Binlog的3种存储格式：binlog_format(row, statement, mixed)
        • statement格式： 每一条会修改数据的sql都会记录在binlog中
        • row： 不记录sql语句上下文相关信息，仅保存哪条记录被修改
        • mixed： 混合 （Binlog记录数据库发生的变化,用于replication）
    • Relay log： 通过异步复制Master中的binlog，生成slave的执行日志

使用时注意 🔗

  数据库使用自增主键作为唯一key时可能的问题： 当需要分库分表时，会出现主键重复。

  • explain 看mysql的执行计划，是否走索引
  • 字符类
    • varchar(50)表示最大存放50个字符, char
    • int(10)表示最大显示宽度为10,存储还是占4字节
  • 时间日期类
    • date, '1000-01-01' to '9999-12-31'
    • datetime, '1000-01-01 00:00:00' t0 '9999-12-31 00:00:00',范围广
    • timestamp, '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC

reference 🔗

《深入浅出MySQL 数据库开发、优化与管理维护》

CMU 15-445/645，MIT 6.172 Performance Engineering of Software Systems, Fall 2018

存储