# 存储引擎

  • MySQL 常见的几种存储引擎:
    • MyISAM :一个传统引擎。不支持外键、事务、行级锁,只支持表级锁。
    • InnoDB :从 MySQL 5.5 开始成为默认引擎,支持外键、事务、行级锁。
  • MySQL 同一个数据库中的不同数据表,可采用不同的存储引擎。

# 事务

  • InnoDB 引擎支持事务,可以将连续执行的多个 SQL 命令声明为一个事务:
    BEGIN;           -- 开始一个事务,也可写作 START TRANSACTION
    ...
    ROLLBACK;        -- 回滚到 BEGIN 时的状态
    ...
    SAVEPOINT p1;    -- 声明一个回滚点
    ...
    ROLLBACK TO p1;  -- 回滚到指定位置
    ...
    COMMIT;          -- 提交当前事务
    
  • 相关命令:
    SELECT * FROM information_schema.innodb_trx;   -- 查询当前的所有事务
    SET AUTOCOMMIT=0`       -- 取消自动提交事务。否则默认每执行一条 SQL 命令就自动作为一个事务提交
    

# 数据库锁

  • 关于表级锁的命令:

    LOCK TABLES [<tb> <lock_type>],...    -- 给指定表加锁,使当前客户端会话获得表锁,例如 LOCK TABLES tb1 WRITE, tb2 READ;
    UNLOCK TABLES                         -- 释放当前会话的所有表锁
    
    FLUSH TABLES                          -- 强制关闭所有被打开的表,并将写缓存中的数据写入磁盘
                tb,...                    -- 只处理指定的表
                WITH READ LOCK            -- flush 之后加上全局只读锁。这是全局锁,因此不需要获得 lock tables 的表锁
    
    SHOW STATUS LIKE 'Table_locks%';      -- 统计获得表锁的耗时
    
    • 客户端执行 LOCK TABLESSTART TRANSACTION 时,都会隐式地释放已获得的表锁。
      • 客户端断开会话时,服务器会释放其获得的表锁。
  • 关于行级锁的命令:

    SELECT * FROM ... WHERE ... LOCK IN SHARE MODE      -- 给查询到的所有数据行加行级共享锁
    SELECT * FROM ... WHERE ... FOR UPDATE              -- 加行级排它锁
    
    SHOW STATUS LIKE 'InnoDB_row_lock%';                -- 统计获得行锁的耗时
    SELECT * FROM performance_schema.data_locks;        -- 显示所有获取行锁的请求,包括已经获得的、等待获得的
    SELECT * FROM performance_schema.data_lock_waits;   -- 显示 data_lock_waits 中的哪些请求在被哪些请求阻塞
    
    • InnoDB 的行锁是通过给索引中的索引键加锁来实现的。

      • 如果不使用索引进行查询,则行锁不起作用,只能使用表锁。
      • 如果针对不同的数据行加行锁,却使用相同的索引键,则也会发生锁冲突。
    • InnoDB 在申请行锁时,会先隐式地申请该表的意向锁(intention lock),类型也为共享或排它。

      • 意向锁是一种特殊的表锁,表示意图对该表加行锁。
        • 意向锁不与意向锁冲突。
        • 意向共享锁与表级共享锁不冲突,其它意向锁与表级锁都冲突。
      • 意向锁由 InnoDB 自动获得、释放,客户端不能控制。
        • 使用意向锁,InnoDB 能更快地发现表级锁是否冲突。
      • 例:
        1. 事务 A 执行 SELECT * FROM tb1 WHERE id=1 FOR UPDATE; ,先请求获得对 tb1 表的意向排它锁,成功之后再请求获得对 id=1 的数据行的排它锁。
        2. 事务 B 执行 SELECT * FROM tb1 WHERE id=2 FOR UPDATE; ,与事务 A 不冲突,能够获得意向排它锁、行级排它锁。
        3. 事务 C 执行 LOCK TABLES tb1 READ; ,请求获得表级只读锁,但 tb1 表已有意向排它锁,因此阻塞等待。
    • InnoDB 提供的行锁属于悲观锁,用户可以自己编程实现乐观锁。如下:

      SELECT name FROM tb1 WHERE id = 1;                    -- 先查询下修改之前的值,这里假设此时 name 的值为 'one'
      UPDATE tb1 SET name='two' WHERE id=1 AND name='one';  -- 执行之后,根据返回值判断是否修改成功
      
      • 可以根据 timestap 等字段来判断数据是否被修改。
  • 相关配置:

    innodb_lock_wait_timeout = 50     # 事务请求获取 row lock 时,等待的超时时间,默认为 50s 。超时则报错:Lock wait timeout exceeded
    innodb_rollback_on_timeout = OFF  # innodb_lock_wait_timeout 时,是否回滚整个事务。默认为 OFF ,只回滚最后一条语句,可能破坏事务原子性
    

# DDL

  • MySQL 的 DDL 有多种实现算法:

    • copy
      • :拷贝一个新表,步骤如下:
        1. 新建一个新表。
        2. 锁定原表,禁止 DML ,允许 DQL 。
        3. 将原表的数据逐行拷贝到新表。
        4. 删除原表,将新表 RENAME 为原表。
      • 缺点:
        • 需要两倍的存储空间。
        • 会阻塞 DML 操作,可能中断业务服务。
    • inplace
      • :在原表中进行修改,分为两种方式:
        • rebuild-table :需要重建数据表,因此会消耗大量时间、磁盘 IO 。
        • not-rebuild-table :默认尽量采用这种。
      • 缺点:
        • 会锁定原表,阻塞 DML 操作。
      • 于 MySQL v5.5 加入。
    • instant
      • :于 MySQL v8.0 加入,只支持少量 DDL 操作。
  • Online DDL :用于在不中断服务的情况下执行 DDL 操作。

    • MySQL v5.6 给 InnoDB 引擎增加了 Online DDL 功能,并默认启用。
    • 可以主动指定 Online DDL 策略,如下:
      ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;
      
      • ALGORITHM 表示 DDL 算法,可取值:
        • DAFAULT :自动选择最高效的算法。
        • COPY
        • INPLACE
      • LOCK 表示加锁类型:
        • DAFAULT :尽量选择最低的锁,从而允许并发 DML 。
        • NONE :不加锁。
        • SHARED :共享锁。
        • EXCLUSIVE :排它锁。
      • 如果指定的策略不支持当前的 DDL 操作,则会报错:
        ALTER TABLE tb1 RENAME TO tb2, LOCK=NONE;
        > 1845 - LOCK=NONE/SHARED is not supported for this operation. Try LOCK=EXCLUSIVE.
        

# innodb_data

  • InnoDB 引擎的大部分数据都保存在系统表空间(System Tablespace)中,包括:
    • 每个表的数据页、索引
    • change buffer
    • data dictionary
    • doublewrite buffer
    • undo log
  • 系统表空间在磁盘保存为一个或多个文件。
    • 早期的 InnoDB 引擎,将系统表空间都保存在 $datadir/ibdata1 文件中。
      • InnoDB 引擎不会减小 ibdata1 文件的体积。即使 delete 了一些数据,也不会释放磁盘空间,而是留给未来使用。
      • 可以通过 mysqldump 重建数据库,从而减小 ibdata1 文件。
    • MySQL v5.6.6 开始,默认设置了 innodb_file_per_table=ON ,将每个数据表的数据页、索引分别保存到 $datadir/<db_name>/<table_name>.ibd 文件中,而 ibdata1 文件保存其它数据。
      • 这样 DROP TABLE 时会自动释放磁盘空间。

# 表碎片

  • InnoDB 表以 B+ 树结构存储数据。删除一行数据时,并不会释放存储空间,而是标记为 free ,留待以后写入新数据。这些 free 空间称为碎片(fragment)。

    • 执行 delete、UPDATE 操作可能产生碎片。
    • 如果碎片空间比新数据行体积小,则一直不会用于写入新数据。
    • 一般按 data_free / (data_length + index_length) 计算碎片率。
    • 如果碎片率长时间较高,建议手动清理碎片,从而减少占用的存储空间、减少读取表的耗时。
    • 每个数据表占用的磁盘空间等于:全部数据行 + 索引 + 碎片空间。
  • 相关命令:

    -- 显示所有数据表的状态,其中 Data_free 表示 free 数据占用的存储空间,单位 bytes
    SHOW TABLE STATUS;
    
    -- 统计所有表的碎片率
    SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,
        DATA_LENGTH/1024/1024 AS data_length,
        INDEX_LENGTH/1024/1024 AS index_length,
        DATA_FREE/1024/1024 AS data_free,
        DATA_FREE/(DATA_LENGTH+INDEX_LENGTH) AS free_rate
        FROM information_schema.tables WHERE DATA_FREE/1024/1024 > 10;
    
    -- MyISAM 表可用该命令清理碎片,这会暂时锁定表
    OPTIMIZE TABLE <tb>...;
    
    -- InnoDB 表可用该命令清理碎片。这会以 Online DDL 方式拷贝出一个无碎片的新表,然后重命名为原表
    ALTER TABLE <tb> engine=InnoDB;
    ANALYZE TABLE <tb>;
    
  • mysqlcheck 命令的用法:

    mysqlcheck
        -h <host> -P <port> -u <user> -p
        -o                    # --optimize ,优化表
          <db> <tb>           # 指定数据库中的数据表
          --databases <db>... # 指定多个数据库的数据表
          --all-databases     # 指定所有数据库