越看到后面, 越觉得和现在的工作内容差距太大, 待后续真正接触 MySQL 运维时, 再仔细挑着看看吧.

基础知识

Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。

全部使用长连接后，有些时候 MySQL 占用内存涨得特别快，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。

• 定期断开长连接
• mysql_reset_connection

日志系统

当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做

redo-log | bin-log

1. redo log 是 InnoDB 引擎特有的; binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
2. redo log 是物理日志，记录的是"在某个数据页上做了什么修改"; binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如 “update T set c=c+1 where id=2”
3. redo log 是循环写的，空间固定会用完; binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

update 流程

1. 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器;否则，需要先从磁盘读入内存，然后再返回。
2. 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
4. 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

binlog

事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。

1. sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync;
2. sync_binlog=1 的时候，表示每次提交事务都会执行 fsync;
3. sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

redolog

为控制 redo log 的写入策略，InnoDB 提供了 innodb_flush_log_at_trx_commit 参数，它有三种可能取值：

1. 设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;
2. 设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘;
3. 设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache。

redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。

redo log buffer, flush at commit stage. (prepare -> binlog -> commit)

索引

当不使用索引查询数据时，会执行 full table scan (explain type ALL)

• hash index 不适合范围查询
• sorted array 适合固定数据
• B-Tree 综合能力强

主键索引的叶子节点存储的是整行数据，非主键索引的叶子节点存储的是主键的值。[查询两次：回表]

覆盖索引是指，索引上的信息足够满足查询请求，不需要再回到主键索引上去取数据。

覆盖索引、前缀索引、索引下推

1
2
3
4
5

select a,b,c from t where a = 'abc' sort by b limit 10;

# 1. alter table t add index a_index(a) => for where
# 2. alter table t add index ab_index(a, b) => 优先全字段排序
# 3. alter table t add index abc_index(a, b, c) => 覆盖索引，无需回表

[!question] MySQL 为什么有时候会选错索引？ 对于每个索引，依据区分度，会计算出[基数]，这是判断索引效率的基准

在 MySQL 中，基数是通过采样计算的，即有可能产生问题描述的情况

1. 采用 force index 强行选择索引
2. 修改 SQL 语句，引导 MySQL 使用期望的索引
3. 新建更加合适的索引

锁

• 全局锁：对整个数据库实例加锁 全局锁的典型使用场景是，做全库逻辑备份。 Flush tables with read lock -> 全库逻辑备份
• 表级锁
  • lock tables ... read/write
  • MDL (metadata lock) 可能后阻塞后面的读操作，在 alter table 时最好加上超时时间
• 行级锁
  • 在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

行锁死锁

• 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
• 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

事务隔离

• 一致性读 [row tx_id]
  • select k from t where id = 1 仅能读取可见最新版本数据
• 当前读：更新数据都是先读后写的，而这个读 (最新 row tx_id 版本)，只能读当前的值，称为“当前读”（current read）。
  • update t set k = k + 1 where id = 1
  • select k from t where id = 1 lock for share mode
  • select k from t where id = 1 for update

可见性

一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：

1. 版本未提交：不可见
2. 版本已提交，但在视图创建后提交：不可见
3. 版本已提交，在视图创建前提交：可见

begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动。如果你想要马上启动一个事务，可以使用 start transaction with consistent snapshot 这个命令。

可重复读的核心就是一致性读（consistent read）;而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。

• 对于可重复读，查询只承认在事务启动前就已经提交完成的数据;
• 对于读提交，查询只承认在语句启动前就已经提交完成的数据;

脏页 flush 时机

1. redo log 写满: MySQL 会停止所有的更新操作，把 checkpoint 往前推进， redo log 才可以继续写入数据
2. 系统内存不足：当需要新的内存页、而内存不够用的时候，需要淘汰一些数据页，空出内存; 如果淘汰的是脏页，需要先进行 flush
3. 系统空闲时
4. 系统正常关闭

InnoDB 刷脏页的控制策略

• innodb_io_capacity 当前系统的 io 处理能力，直接影响 mysql 的脏页 flush 效率
  • 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_pages_pct
• redo log 写盘速度

如果一个高配的机器，redo log 设置太小，会发生什么情况

每次事务提交都要写 redo log，如果设置太小，很快就会被写满，也就是下面这个图的状态，这个“环”将很快被写满，write pos 一直追着 CP。

磁盘压力很小，但是数据库出现间歇性的性能下跌。

表空间

innodb_file_per_table 控制表数据存放在共享表空间，还是单独的文件。ON 表示每个 InnoDB 表数据存储在 *.idb 文件中 通过 drop table 命令可以直接删除该表文件。

[!question] 为什么表数据删掉一半，表文件大小不变？ InnoDB 是按页存储的，如果删除一整个数据页的记录，整个数据页也就可以被复用了; 问题出在，数据页的复用与记录的复用是不同的。

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

重建表：alter table t engine=innodb,ALGORITHM=inplace

如果要收缩一个表，只是 delete 掉表里面不用的数据的话，表文件的大小是不会变的，你还要通过 alter table 命令重建表，才能达到表文件变小的目的。

count (*)

• MyISAM 虽然 count(*) 很快，但不支持事务 [count 额外存储在指定位置]
• show table status 命令很快，但是不准确
• InnoDB count(*) 时会遍历全表，有性能损耗

count 行为

• count(id) innodb 遍历整张表, 把 id 取出来, 返回给 server 层
• count(1) innodb 遍历整张表, 但不取值
• count(字段) 按行取字段, 且要判断 null
• $count(*)$ 不取值

按照效率排序的话，$count(字段)<count(主键 id)<count(1)≈count(*)$

order by

MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。

• 全字段排序
• rowid 排序 (max_length_for_sort_data), 相比全字段多一次回表操作

如何确定一个排序语句是否使用了临时文件 (超出 sort_buffer 容量)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

set optimizer_trace='enabled=on';
select VARIABLE_VALUE into @a from performance_schema.session_status where variable_name = 'Innodb_rows_read';

-- your selection
select city, name, age from T where city = 'LA' order by name limit 10000;

select * from `information_schema`.`OPTIMIZER_TRACE`\G

select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

-- Innodb_rows_read
select @b-@a;

tmp_table_size 这个配置限制了内存临时表的大小，默认值是 16M。如果临时表大小超过了 tmp_table_size，那么内存临时表就会转成磁盘临时表。

持久化

binlog 的写入逻辑比较简单：事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。

每个线程有自己 binlog cache，但是共用同一份 binlog 文件。

• 图中的 write，指的就是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快。
• 图中的 fsync，才是将数据持久化到磁盘的操作。一般情况下，我们认为 fsync 才占磁盘的 IOPS。

write 和 fsync 的时机，是由参数 sync_binlog 控制的：

• sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；
• sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；
• sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

为了控制 redo log 的写入策略，InnoDB 提供了 innodb_flush_log_at_trx_commit 参数，它有三种可能取值：

• 设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;
• 设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘；
• 设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache。

WAL 机制主要得益于两个方面：

• redo log 和 binlog 都是顺序写，磁盘的顺序写比随机写速度要快；
• 组提交机制，可以大幅度降低磁盘的 IOPS 消耗。

主备

binlog 的三种格式

• statement (sql 原本的内容)
• row (将 where 条件转换为 ID, 保证数据一致性) 占用很大空间 -> 便于恢复数据
• mixed (根据当前语句选用不同的格式)

有些语句的执行结果是依赖于上下文命令的，直接执行的结果很可能是错误的。

读写分离 - 延迟

• 强制走主库
• sleep
• 判断主备无延迟
  • show slave status - seconds_behind_master
  • Retrieved_Gtid_set / Executed_Gtid_set
• 配合 semi-sync replication
• 等主库位点 select master_pos_wait(file, pos[, timeout]);
• 等 GTID select wait_for_executed_gtid_set(gtid_set, 1);

数据库是否正常

建议把 innodb_thread_concurrency 设置为 64~128 之间的值。(并发查询数量限制, 不影响并发连接数)

在线程进入锁等待以后，并发线程的计数会减一，也就是说等行锁（也包括间隙锁）的线程是不算数的。

select * from mysql.health_check;

MySQL 5.6 版本以后提供的 performance_schema 库，在 file_summary_by_event_name 表里统计了每次 IO 请求的时间。

误删数据对应

• 把 sql_safe_updates 参数设置为 on
• 上线前的 SQL 审计

即使配置了 binlog_format=row，执行 truncate/drop 时，记录的 binlog 还是 statement 格式。binlog 里面就只有一个 truncate/drop 语句，这些信息是恢复不出数据的。

全表查询

MySQL 是 “边读边发的”, net_buffer (16K); 当 net_buffer 写满了, 就向客户端发送数据. (sending_to_client 状态)

mysql query 线程的 sending_data 仅代表 query 开始执行, 不一定正在发送数据, 可能在等待锁. 仅当一个线程处于“等待客户端接收结果”的状态，才会显示"Sending to client"；

• MySQL 查询语句进入执行阶段后，首先把状态设置成 “Sending data”；
• 然后，发送执行结果的列相关的信息 (meta data) 给客户端；
• 再继续执行语句的流程；
• 执行完成后，把状态设置成空字符串。

buffer_pool 的数据采用了魔改的 LRU 算法, 区分出 young/old 区域 (分代). 以防止大量查询旧数据, 导致整个 buffer_pool 的原始数据都失效.

何时 join

能不能使用 join 语句？

• 如果可以使用 Index Nested-Loop Join 算法，也就是说可以用上被驱动表上的索引，其实是没问题的；
• 如果使用 Block Nested-Loop Join 算法，扫描行数就会过多。尤其是在大表上的 join 操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。这种 join 尽量不要用。

如果要使用 join，应该选择大表做驱动表还是选择小表做驱动表？

• 如果是 Index Nested-Loop Join 算法，应该选择小表做驱动表；
• 如果是 Block Nested-Loop Join 算法：
  • 在 join_buffer_size 足够大的时候，是一样的
  • 在 join_buffer_size 不够大的时候（这种情况更常见），应该选择小表做驱动表

在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

临时表

• 一个临时表只能被创建它的 session 访问, 对其他线程不可见.
• 临时表经常会被用在复杂查询的优化过程中。
• 内部临时表
  • union
  • group by

自增主键

主键自增不连续的可能性原因

• 唯一键冲突是导致自增主键 id 不连续的第一种原因。
• 事务回滚也会产生类似的现象，这就是第二种原因。
• 批量申请自增 id 的策略

表数据复制

• mysqldump
  • mysqldump -h$host -P$port -u$user --add-locks=0 --no-create-info --single-transaction --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql
• export csv
  • select * from db1.t where a>900 into outfile '/server_tmp/t.csv';
  • load data infile '/server_tmp/t.csv' into table db2.t;
• physical copy (.frm, .ibd)

自增 ID

• 表的自增 id 达到上限后，再申请时它的值就不会改变，进而导致继续插入数据时报主键冲突的错误。
• row_id 达到上限后，则会归 0 再重新递增，如果出现相同的 row_id，后写的数据会覆盖之前的数据。
• Xid 只需要不在同一个 binlog 文件中出现重复值即可。虽然理论上会出现重复值，但是概率极小，可以忽略不计。
• InnoDB 的 max_trx_id 递增值每次 MySQL 重启都会被保存起来，所以我们文章中提到的脏读的例子就是一个必现的 bug，好在留给我们的时间还很充裕。
• thread_id 是我们使用中最常见的，而且也是处理得最好的一个自增 id 逻辑了。($2**32-1$ - 0 循环使用)

实践建议

1. 避免使用 order by rand()，会使用 using temporary 和 using filesort，查询代价巨大。
2. 如果对字段做了函数计算，就用不上索引了，这是 MySQL 的规定。
3. 隐式类型转换会导致全表扫描。[在 MySQL 中，字符串和数字做比较的话，是将字符串转换成数字。]
4. 隐式字符编码转换会导致全表扫描。(utf8 -> utf8bm4) 在 join 不同的表时可能发生

questions

普通索引, 唯一索引

• 普通索引会使用 change_buffer 来加速更新操作 (buffer_pool 中一种 buffer, 通过 innodb_change_buffer_max_size 来动态设置)
  • 如果数据在内存, 操作内存
  • 如果数据不在内存, 操作 change_buffer
• 唯一索引在更新时
  • 如果数据已在内存中, 校验唯一性后, 执行更新操作
  • 如果数据不在内存, 则会先进行一次 IO 操作, 再对内存进行操作

如果所有的更新后面，都马上伴随着对这个记录的查询，那么应该关闭 change buffer。而在其他情况下，change buffer 都能提升更新性能。

mysql 如何选择索引

优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”(cardinality).

• 对于由于索引统计信息不准确导致的问题，你可以用 analyze table 来解决。
• 对于其他优化器误判的情况，你可以在应用端用 force index 来强行指定索引，也可以通过修改语句来引导优化器，还可以通过增加或者删除索引来绕过这个问题。

字符串索引

1. 直接创建完整索引，这样可能比较占用空间;
2. 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引;
3. 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题;
4. 创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。

查询常见问题

• 长时间不返回
  • 大概率是表锁了, show processlist 查看语句执行情况
  • 等待 flush
  • 等待行级锁
• 慢查询
  • 引入读锁, 当前读, 查询速度更快
  • $select * from t where id = 1$ -> 800ms
  • $select * from t where id = 1 lock in share mode$ -> 0.2ms

加锁规则

• 加锁的基本单位是 next-key lock
• 查找过程中访问到的对象才加锁
• 索引上的等值查询, 给唯一索引加锁时, next-key lock 退化成行锁
• 索引上的等值查询, 向右遍历时且最后一个值不满足等值条件时, next-key lock 退化成间隙锁
• 唯一索引上的范围查询会访问到不满足条件的第一个值为止

如果 MySQL 出现了性能瓶颈, 且在 IO 上, 如何提升性能

• 设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数, 减少 binlog 的写盘次数.
• 将 sync_binlog 设置为大于 1 的值 (主机重启会丢失数据)
• 将 innodb_flush_log_at_trx_commit 设置为 2 (主机重启会丢失数据)

References

• MySQL 实战 45 讲 - 极客时间