MySQL实战45讲笔记

使用 docker 搭建测试环境。

docker pull mysql:5.7
docker network create test
docker run -d --network test --restart always --name mysql-test -e MYSQL_ROOT_PASSWORD=test mysql:5.7

连接 mysql

docker run -it --network test mysql:5.7 mysql -h mysql-test -p

• server 层，涵盖 mysql 大多数核心服务功能，以及所有内置函数，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
  • 连接器
    • 建立链接
      • 长连接问题：
        • 原因：执行过程中临时使用的内存是在连接对象中的。
        • 解决办法：mysql_reset_connection 初始化连接资源。
    • 身份验证
      • show processlist
    • 查询权限
      • 去系统表读取数据，结果放在变量，执行器从该变量中读取。
    • 查询缓存
      • 不要使用。
      • query_cache_type 设置为 DEMAND，按需使用。
      • select SQL_CACHE * from T where ID=10;
      • 8.0 删除了该功能。
  • 分析器
    • 词法分析：识别查询语句中数据库名、表名、列名等。
    • 语法分析
  • 优化器
    • 决定使用的索引、表的连接（join）顺序。
  • 执行器
    • 检查对表的执行权限。sql 执行过程中可能会有触发器这种在运行时才能确定的过程，分析器工作结束后的 precheck 是不能对这种运行时涉及到的表进行权限校验的，所以需要在执行器阶段进行权限检查。另外正是因为有 precheck 这个步骤，才会在报错时报的是用户无权，而不是 k 字段不存在（为了不向用户暴露表结构）
    • 使用对应的引擎打开表。
• 存储引擎层

redo log：

• 更新数据，先写 redo log，空闲时间再写磁盘，即 WAL（write-ahead logging）技术。
• 固定大小，1GB/文件 * 4
• 保证即使数据库发生异常重启，之前提交的记录都不会丢失。
• InnoDB 引擎特有日志
• innodb_flush_log_at_trx_commit=1
• redo log 是顺序写，记录落盘是随机写，前者要快很多所以，先写 redo log，然后记录落盘。

binlog

• server 层特有日志。
• sync_binlog=1。
• binlog 是有固定格式，也就是固定结尾的，可以用来判断一条 binlog 是否完整。

二者不同：

• redo log 是 InnoDB 特有的。binlog 是 MySQL 层实现的，所有引擎都可以使用。
• redo log 是物理日志，记录数据页的更改；binlog 是逻辑日志，记录 sql 语句的原始逻辑。二者通过事务 ID 进行对应。
• redo log 是循环写的，binlog 是追加写。
• redo log 用于服务异常重启恢复（异常发生时内存中的脏数据还没有落盘），binlog 主要用于备份。

update 语句二阶段提交。

• 执行器执行语句，获得新行，调用引擎接口。
• 引擎更新数据到内存，同时写操作记录到 redo log，redo log 处于 prepare 状态。
• 执行器执行 binlog，binlog 写磁盘。
• 执行器调用引擎提交事务接口，引擎将刚才的 redo log 改为提交状态。（prepare 写日志的最后 512 个字节，会在 commit 的时候被改掉）

事务特性：ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）。

隔离性常见问题：脏读、不可重复读、幻读

SQL 标准的事务隔离级别包括：

• 读未提交（read uncommitted），实现方式：直接返回记录上的值。
• 读提交（read committed），实现方式：开始执行 SQL 语句时候创建一致性视图。
• 可重复读（repeatable read），实现方式：启动事务时创建一致性视图。
• 串行化（serializable）,实现方式：加锁。

设置隔离级别：启动参数 transactio-isolation，查询 show variables like 'transaction_isolation'

事务实现原理-mvcc（多版本并发控制）：

• 每条记录在更新的时候都会同时记录一条回滚操作，即回滚日志。
• 当没有比回滚日志更早的 read-view（读视图） 时候，才删除回滚日志。
• 同一条记录在系统中可以存在多个版本，即数据库的多版本并发控制。

不要使用长事务。

• 长事务意味着会存在很多老的事务视图，以及大量回滚日志。
• 长事务还会占用锁资源。

避免使用长事务：

• 总是设置 autocommit = 1，开启事务的自动提交。

查询 information_schema 的 innodb_trx 查找长事务：查找时间超过 60s 的长事务。

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

如何避免长事务：

• 业务端：
  • 确认是否使用了 set autocommit=0。
  • 确认是否有不必要的只读事务。
  • 业务连接数据库的时候，根据业务本身的预估，通过 SET MAX_EXECUTION_TIME 命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。
• 数据端
  • 监控 information_schema.Innodb_trx 表，设置长事务阈值，超过就报警或者 kill；
  • Percona 的 pt-kill 这个工具不错，推荐使用；
  • 在业务功能测试阶段要求输出所有的 general_log，分析日志行为提前发现问题；
  • 如果使用的是 MySQL 5.6 或者更新版本，把 innodb_undo_tablespaces 设置成 2（或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便。

索引目的：加速查询效率。

索引模型：

• 哈希表，只适合等值查询，不适合区间查询。
• 有序数组，支持区间查询和等值查询，但是插入困难，只适合静态存储引擎。
• 二叉树：每个节点都是需要读取的数据库，每次随机读取都差不多需要 10ms，树高多次读取耗时过久。
• B+树：
  • 对磁盘读写少。
  • InnoDB 的 N 叉树大约是 1200。
  • B+树的叶子节点是一个 page 页，每个 page 页里面存有多个行。每个页里面通过有序数组查找行。

主键索引：

• 聚簇索引：索引和数据保存在一起，删除主键其实就是重建整张表。

非主键索引：

• 二级索引：获取索引列以外的数据需要回表。

索引维护

• 页分裂
• 页合并

最佳实践：

• 使用自增字段做索引，减少页分裂和合并，一般建议设置成 bigint unsigned。
• 二级索引重建应该新建索引再做删除，如果有查询用到这个索引，此时索引已被删除，会导致业务抖动。
• 主键重建不能采用 drop 这种方式去按操作，因为所有数据都是以主键组织的，
  • 删了主键后，InnoDB 会自己找一个主键组织数据，再次添加主键又会重新组织数据，重建表的次已达二次，我们可以直接 Optimiz 这个表。
  • 第一个是整个数据库迁移，先 dump 出来再重建表（这个一般只适合离线的业务来做）。
  • 第二个是用空的 alter 操作，比如 ALTER TABLE t1 ENGINE = InnoDB 或者 optimize table 原地重建表结构。
  • 第三个是用 repaire table，不过这个是由存储引擎决定支不支持的（innodb 就不行）。

覆盖索引：索引上存储的值可以满足查询需求（查询列和条件列），就不需要回表了。对于高频查询使用覆盖索引。

索引下推：可以在索引遍历过程中，对索引中包含的字段现做判断，直接过滤掉不满足条件的值。

Percona 工具包

二级索引会默认和主键索引做联合索引。

最左前缀原则。

联合索引是依次按照联合字段的先后顺序，依次进行排序。如 a,b,c 三个字段是联合索引，则叶子节点存储的是三个字段的数据，且按照先后顺序进行排序；而非叶子节点存储的是第一个关键字的索引。故当执行查询的时候，因为联合索引中是先根据 a 进行排序的，如果 a 没有先确定，直接对 b 或 c 进行查询的话，就相当于是乱序查询，因此联合索引无法生效，此时就相当于是全表查询。

索引失效的情况：

• 在索引列上做任何操作（计算、函数、自动或手动的类型转换）
• 违反最左前缀原则。
• 使用 != 或 <> 导致无法使用索引。
• is null,is not null 无法使用索引。
• LIKE 和通配符开头的查询。
• 字符串不加单引号
• or 连接查询条件。

全局锁：整个数据库加锁（FTWRL）。

• 使用场景：全库逻辑备份。
• mysqldump 使用 –sing-transaction 启动事务，不用加全局锁。
• 这种方法与 read-only 的对比
  • read-only 可能用来判断主从库，而且对 super 权限无用。
  • 全局锁连接断开后可能会自动释放。

增删改数据（DML），修改表结构（DDL）。

表级锁：

• 表锁。
• 元数据锁（meta data lock， MDL），在访问表数据的时候自动加上。

全局锁和表锁是在 server 层实现的，行锁是在引擎自己实现的。

两阶段锁协议：

• 在 InnoDB 事务 中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束才会释放。
• 实践：如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

死锁处理策略：

• 直接等待，直到超时，超时时间通过 innodb_lock_wait_timeout 设置。
  • 缺点：如何合理设置超时时间，太短可能是锁等待，又不能太长。
• 死锁检测，主动回滚。
  • 实践：合理设计事务中的语句顺序，避免语句造成的死锁。
  • 缺点：每个阻塞的进程都要进行死锁检测，会消耗时间。

热点行更新，导致死锁检测耗时过久：

• 控制并发度。
• 将一行改成逻辑上的几行。

InnoDB 行锁是通过给索引上的索引项加锁来实现的，这一点 MySQL 与 Oracle 不同，后者是通过在数据块中对相应数据行加锁来实现的。 InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！ 在实际应用中，要特别注意 InnoDB 行锁的这一特性，不然容易导致大量的锁冲突，从而影响并发性能。

事务启动方式：

• begin/start transaction: 执行第一个快照语句时创建一致性读视图。
• start transaction with cosistent snapshot：立马创建一致性读视图。

InnoDB 通过 MVCC 实现秒级创建快照（一致性视图）：

• 每个事务都有一个数组，用来保存启动事务瞬间，当前正在 活跃 的所有事务 ID。活跃是指启动了但是没有提交。
• 更新数据都是先读后写，读只能读当前值，这种行为称为“当前读”。

select 使用 lock in share mode 或者 for update，支持当前读。

可重复读的核心就是一致性读。

普通索引和唯一索引在查询能力上没有差别，主要是对更新性能的影响。

如果数据不在内存中：

• 普通索引：将记录更新在 change buffer 中。
• 唯一索引：从磁盘读取数据，判断唯一性。

change buffer 中的数据在读取数据、后台更新、关闭 mysql 时候落盘。

普通索引可以使用 change buffer 加快更新流程，建议尽量使用普通索引。

对于写多读少的业务，change buffer 的使用效果最好。

change buffer 和 redo log 区别：

• change buffer 是减少随机读磁盘的 IO。
• redo log 是将随机写变为顺序写，减少随机写磁盘的 IO。

优化器会结合扫描行数、是否使用临时表、是否排序等因素进行综合判断。

根据索引的区分度来判断索引上的值有多少，这是一个统计信息，不是很准确。

索引异常处理办法：

• 使用 analyze table 命令重新统计索引信息。
• 使用 force index 强制选择索引。
• 修改语句，引导 mysql 使用期望的索引。

使用前缀索引可以减少索引长度。

如何选择字符串前缀索引长度：

• 通过字符串不同长度的前缀区分度来判断。

前缀索引不能使用覆盖索引对查询性能的优化。

前缀区分度低怎么办？

• 倒序存储 reverse()
• hash crc32()

可能是在写内存和刷脏页。 redo log 何时写：

• redo log 满了
• 系统内存不足，需要淘汰数据页，而数据也恰好是脏页。
• 系统空闲。
• mysql 关闭。

InnoDB 刷脏页的控制策略：

• 磁盘速度：设置 innodb_io_capacity 参数

  fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest
  

• 脏页比例：innodb_max_dirty_page_pct
• 相邻脏页 innodb_flush_neghbors

innodb_file_per_table=ON

数据页的复用与记录的复用是不同的：

• 记录的复用，只限于符合范围条件的数据。
• 整个页从 B+树里里面摘掉以后，可以复用到任何位置。

delete 命令其实只是把记录的 位置，或者数据页标记为“可复用”，但是磁盘的大小是不会变的。

插入数据也会造成数据空洞。

重建表可以用来收缩表空间。alter table A engine=InnoDB.

推荐使用 github 开源的 gh-ost 操作。

Online 与 Inplace：

• Inplace 是指数据有没有在表间移动。
• Online 是指操作期间是否可以继续更新数据。
• DDL 过程如果是 Online 的，那一定是 inplace 的。
• 反过来未必，截止到 mysql 8.0，添加全文索引和空间索引属于这种情况。

数据库保存技术：利用事务的特性自己实现 count(*)和 count(column)区别：

• count(*) 做了特殊优化，不取值，直接返回行数，建议使用。
• count(column) 返回列不为 null 的数量。