InnoDB-Basic

数据库

背景

面MSRA的时候翻车了，果然应该投点时间给项目经历的，之前时间都投给算法题了，失误失误。好好看一下InnoDB的文档和实现细节好了，说不定什么时候能用上。虽然以前用的是5.6？还是5.7？忘了，反正世博不换8.0.看最新的8.0好了，顺便学习一下全新的Transaction调度算法，对身体好。

InnoDB关键特性

  • ACID和事务

  • 行级锁，Oracle-sytle consistent reads

  • 自动化主键
  • 外键

消耗

  • 高CPU
  • 高内存
  • 高硬盘

ACID

ACID主要是针对事务而言的，每一项功能都有与之对应的MySQL设置或特性。

Atomicity

事务是不可分割的最小工作单位

  • Autocommit setting.
  • COMMIT statement.
  • ROLLBACK statement.
  • Operational data from the INFORMATION_SCHEMA tables.

Consistency

事务后不应当出现结构性错误，主要是防Crash

  • DoubleWrite Buffer in InnoDB
  • Crash recovery in InnoDB

Isolation

并发事务隔离

  • Autocommit setting.
  • SET ISOLATION LEVEL statement.
  • The low-level details of InnoDB locking. During performance tuning, you see these details through INFORMATION_SCHEMA tables.

Durability

内容提交后将不再修改，实现起来相当麻烦，且于硬件性能关系密切，例如CPU性能，磁盘性能，对于异地部署而言还需要考虑网络性能。

  • InnoDB doublewrite buffer, turned on and off by the innodb_doublewrite configuration option.
  • Configuration option innodb_flush_log_at_trx_commit.
  • Configuration option sync_binlog.
  • Configuration option innodb_file_per_table.
  • Write buffer in a storage device, such as a disk drive, SSD, or RAID array.
  • Battery-backed cache in a storage device.
  • The operating system used to run MySQL, in particular its support for the fsync() system call.
  • Uninterruptible power supply (UPS) protecting the electrical power to all computer servers and storage devices that run MySQL servers and store MySQL data.
  • Your backup strategy, such as frequency and types of backups, and backup retention periods.
  • For distributed or hosted data applications, the particular characteristics of the data centers where the hardware for the MySQL servers is located, and network connections between the data centers.

ref