mysql 可选配置文件，按照以下顺序查找：
/etc/my.cnf
/etc/mysql/my.cnf

配置说明
[mysqld] 和 [mysql] 组的配置项，分别应用于程序 mysqld 和 mysql client。
[client] 组配置项可以被所有（不同版本的）mysql client读取

[mysqldump] 组配置项应用于mysqldump

  1. [client]
  2. port=3306
  3. socket=/tmp/mysql.sock
  5. [mysqld]
  6. port=3306
  7. socket=/tmp/mysql.sock
  8. key_buffer_size=16M
  9. max_allowed_packet=128M
  11. [mysqldump]
  12. quick

mysql.server 是一个启动脚本

  1. mysql.server start
  2. mysql.server stop

查看系统配置

  1. mysqld --verbose --help
  3. show variables;
  4. show status;
  5. show engines;

InnoDB
优势：

  1. DML操作遵循ACID模型
  2. 行级锁
  3. 主键优化查询，每一个innodb表有一个主键索引叫做 cluster index
  4. innodb支持外检约束(foreign key constains)

ACID:
A: atomicity 原子性 (transaction)
C: consistency. 一致性 （doublewrite buffer. crash recovery)
I: isolation. 隔离性 (transaction: isolation level)
D: durability. 持久性

事务隔离级别：

buffer pool (cache table and index) in main memory.

change buffering

cluster index 就地更新，并且隐藏指向undo log record的系统列
secondary index 非原地更新，亦不隐藏

当更新辅助索引列时，旧的辅助索引记录被删除标记，新记录被插入，并最终清除删除标记的记录。当二级索引记录被删除标记或者二级索引页面被更新的事务更新时，InnoDB在聚集索引中查找数据库记录。在聚集索引中，检查记录的DB_TRX_ID，如果在读取事务启动后修改了记录，则从undo日志中检索到正确的记录版本。

InnoDB Architecture
image.png

内存结构

Buffer Pool

在主存中缓存表和索引
切分为Page来存储，结构为Page的链表，并且采用LRU算法（least recently used)

监控buffer pool状态
show engine innodb status \G

Change Buffer

Change Buffer是一种特殊结构，当二级索引页不在buffer pool中，它会缓存对二级索引页的更改。
insert、update、delete 这些dml操作会出发change buffer， 在其他读取操作将页面加载到缓冲池时合并。

The change buffer caches changes to secondary index entries when the relevant page is not in the buffer pool, thus avoiding expensive I/O operations by not immediately reading in the page from disk. The buffered changes are merged when the page is loaded into the buffer pool, and the updated page is later flushed to disk.

在内存中change buffer占据了buffer pool的部分内存； 在磁盘上，change buffer 是system tablespace的一部分，当数据库服务关闭时，change buffer缓存在这里。

Adaptive Hash Index

自适应Hash索引
观察者模式

Log Buffer

A large log buffer enables large transactions to run without the need to write redo log data to disk before the transactions commit. Thus, if you have transactions that update, insert, or delete many rows, increasing the size of the log buffer saves disk I/O.

磁盘结构

Tables

查看默认的存储引擎
select @@default_storage_engine;

默认是 file-per-table

主键：

  1. 不能有非空列
  2. 不能有重复值

Indexes

通常，cluster index 和 primary index 同义
InnoDB优化查询和dml操作：

  1. 如果定义了primary key，那么InnoDB使用primary key作为 cluster index.
  2. 如果没有定义primary key，那么InnoDB使用第一个UNIQUE索引以及所有非空列作为 cluster index.
  3. 如果即没有primary key也没有unique索引，InnoDB生成一个隐藏的cluster index 叫 GEN_CLUST_INDEX ，该列是包含了row id。

通过cluster index查询row会直接定位到包含该row的page，减少了I/O操作

除了cluster index 其他的索引都是 secondary index. 在secondary index中都会包含primary key

InnoDB的索引是 B-tree ， 特殊的索引使用 R-tree 索引多维度的数据。
默认的索引page大小是16kb

Sorted Index Builds
索引构建三个阶段：

  1. run: 扫描clustered index, 添加到sorted buffer, 当sorted buffer满了，则写入一个临时文件中
  2. 合并整个临时文件为一个有序的整体
  3. 查询合并后的有序整体到B-tree

Tablespaces

system tablespace

系统表空间是存储change buffer的区域。 也可能包含表和索引数据（如果在表空间创建的话）
系统表空间包含多个文件，如ibdata1

File-Per-Table tablespaces

配置中开启 file-per-table

  1. [mysqld]
  2. innodb_file_per_table=ON

file-per-table 的优势

  1. 当truncating或者dropping table 在file-per-table 表空间时，可以释放磁盘空间。 shared tablespace 不会。
  2. 对位于共享表空间中的表进行表复制 ALTER TABLE 操作会增加表空间占用的磁盘空间量； file-per-table 则不会
  3. truncate table性能更优

file-per-table的劣势

  1. 使用 file-per-table 表空间，每个表可能有未使用的空间，只能由同一表的行使用，如果管理不当，可能会导致空间浪费。
  2. 当每个表都有自己的数据文件时，需要更多的文件描述符。

General Tablespaces

general tablespace 是一个共享的InnoDB表空间。 创建使用 create tablespace 语法.

Undo Tablespace

undo tablespace 包含了undo logs

查看undo file

  1. SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES
  2.   WHERE FILE_TYPE LIKE 'UNDO LOG';

Temporary Tablespace

两种
Session temporary tablespace: 存用户创建的临时表和内部临时表

Global temporary tablespace: 存用户创建临时表的rollback segments

查看临时表

  1. SELECT * FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME='innodb_temporary'\G

Doublewrite buffer

双写缓冲区是一个存储区域，InnoDB 在将页面写入 InnoDB 数据文件中的适当位置之前，在其中写入从缓冲池中flush的页面。如果在页面写入过程中存在操作系统、存储子系统或意外的 mysqld 进程退出，InnoDB 可以在崩溃恢复期间从双写缓冲区中找到页面的良好副本。

Redo Log

redo log 是基于磁盘的数据结构，在crash recovery期间使用。 redo log对修改表数据的请求进行编码并调用sql语句或底层api。

Undo Logs

撤消日志是与单个读写事务相关联的撤消日志记录的集合。 undo log存储在 undo log segments. 即包含在 rollback segements中，外部是undo tablespaces.

Locking And Transaction Model

Shared and Exclusive Locks

InnoDB实现行级锁有两种类型：
共享锁(S) : 共享 (S) 锁允许持有锁的事务读取一行。
排他锁(X) : 排他 (X) 锁允许持有锁的事务更新或删除行。

如果事务T1在row1上持有排他锁，则不能立即授予来自某个不同事务 T2 的对 r 上任一类型锁的请求。（即不论读或者写）

Intention Locks

意向锁

InnoDB支持多个颗粒度的锁，允许行级锁和表级锁共存。
意向锁是表级锁，指示事务稍后需要对表中的行使用哪种类型的锁（共享锁或独占锁），有两种类型的意向锁：

  • Intention shared lock (IS) 意向共享锁 (IS) 表示事务打算在表中的各个行上设置共享锁。
  • Intention exclusive lock (IX) 意图排他锁 (IX) 表示事务打算对表中的各个行设置排他锁

例如： SELECT … FOR SHARE 设置一个IS锁， SELECT … FOR UPDATE 设置一个IX锁。

意向锁协议如下：

  • 在事务获得表中某行的共享锁之前，它必须首先获得表上的 IS 锁或更强的锁。
  • 在事务获得表中行的排他锁之前，它必须首先获得表的 IX 锁。

意图锁的主要目的是表明有人正在锁定一行，或者打算锁定表中的一行。

image.png

Record Locks

记录锁是对索引记录的锁。 例如 select c1 from t where c1=10 for update; 阻塞其他插入、更新事务； 即使表没有索引列，InnoDB会自动创建一个隐藏的cluster index来用于记录锁。

Gap Locks

间隙锁是对索引记录之间的间隙的锁，或者是对第一个索引记录之前或最后一个索引记录之后的间隙的锁。例如：
select c1 from t where c1 between 10 and 20 for update;

READ_COMMITED 事务隔离级别下， gap lock 被禁用。

Next-Key Locks

next-key 锁是索引记录上的记录锁和索引记录之前的间隙上的间隙锁的组合。

InnoDB 执行行级锁定的方式是，当它搜索或扫描表索引时，它会在遇到的索引记录上设置共享锁或排他锁。因此，行级锁实际上是索引记录锁。

默认情况下，InnoDB的事务隔离级别是 REPEATABLE READ ,在这种情况下，InnoDB 使用 next-key 锁进行搜索和索引扫描，从而防止幻像行

InnoDB next-key浅析 https://www.cnblogs.com/zhoujinyi/p/3435982.html

Insert Intention Locks

入意向锁是一种在行插入之前由 INSERT 操作设置的间隙锁.

AUTO-INC Locks

AUTO-INC 锁是一种特殊的表级锁，由插入到具有 AUTO_INCREMENT 列的表中的事务获取。

InnoDB Transaction Model

Transaction Isolation Level

事务隔离级别

InnoDB提供了四种隔离级别

  • READ_UNCOMMITTED
  • READ_COMMITTED
  • REPEATABLE_READ 默认级别
  • SERIALIZABLE

InnoDB 使用不同的锁定策略支持这里描述的每个事务隔离级别。

REPEATABLE READ

可重复读

  • For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it.
  • For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into the gaps covered by the range.

READ COMMITED

gap lock被禁用了， 可能出现幻读

READ UNCOMMITED

SELECT 语句以非锁定方式执行，但可能会使用行的早期版本。 造成读取结果不一致，即脏读

start transaction or begin

commit or rollback

set autocommit = 0 禁用自动提交

Locking Read

InnoDB支持两种locking read

  • select … for share 在读取的任何行上设置共享模式锁。其他会话可以读取这些行，但在您的事务提交之前无法修改它们。

  • select … for update 对于搜索遇到的索引记录，锁定行和任何关联的索引条目，就像您为这些行发出 UPDATE 语句一样。其他事务被阻止更新这些行


    子查询不会锁定，除非也指定了
    例如：
    SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2) FOR UPDATE;