架构示意图

server 层

• 连接器

  • 管理连接，权限验证
  • 一个用户成功建立连接后，即使你用管理员账号对这个用户的权限进行了修改，也不会影响已经存在连接的权限，修改完成后，只有再新建连接才会使用新的连接权限。
  • 空闲连接（show processlist; 显示为 Sleep ） 默认 8 小时断开（参数 wait_timeout 控制）
  • set GLOBAL interactive_timeout=10000; show GLOBAL VARIABLES like '%timeout%';
  • Mysql 在执行过程中，临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。Mysql5.7 或更新版本，每次进行一个比较大的操作后， 通过执行 mysql_reset_connection 来重新初始化连接资源，这个过程不需要重连和重新做权限验证。

• 分析器

  • 词法分析，语法分析
  • 查询表中没有的字段(如果表T中没有字段k，而你执行了这个语句 select * from T where k=1, 那肯定是会报“不存在这个列”的错误： “Unknown column ‘k’ in ‘where clause’”。)，在分析器阶段报错。 mysql --debug --console 可以验证

• 优化器

  • 执行计划生成，索引选择

• 执行器

  • 操作引擎，返回结果
  • 将 sql_cache_type 设置成 DEMOND。这样对于默认的SQL 语句都不是用查询缓存。对于需要使用查询缓存的语句显式指定。select SQL_CACHE * from T where ID=10;
  • 有些场景下，执行器调用一次，在引擎内部则扫描了多次，因此引擎扫描行数和慢查询日志中的 rows_examined 并不是完全相同的
  • 开始执行的时候，要先判断权限（工程实现上，如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证）

• 查询缓存

  • 只有静态表适合适用

存储引擎

• 存储数据，提供读写接口

• InnoDB

  • InnoDB是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值

日志模块

redo log

• WAL （Write-Ahead Logging）

• 数据库的flush过程 刷脏页

  • InnoDB的redo log写满了
  • 系统内存不足
  • MySQL认为系统“空闲”的时候
  • MySQL正常关闭

• InnoDB刷脏页的控制策略

  • 测试磁盘随机读写的命令

    • 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_io_capacity

  • innodb_flush_neighbors

    • 如果跟它相邻的数据页也还是脏页的话，也会被放到一起刷。值为1的时候会有上述的“连坐”机制，值为0时表示不找邻居，自己刷自己的
    • 使用的是SSD这类IOPS比较高的设备的话，建议你把innodb_flush_neighbors的值设置成0
    • 在MySQL 8.0中，innodb_flush_neighbors参数的默认值已经是0了

  • InnoDB的刷盘速度

    • 脏页比例

      • 脏页比例上限，默认值是75% innodb_max_dirty_pages_pct

      • 得到脏页比例

        • Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total
        • select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty'; select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total'; select @a/@b;

    • redo log写盘速度

• redo log一般设置多大

  • 如果是现在常见的几个TB的磁盘的话，就不要太小气了，直接将redo log设置为4个文件、每个文件1GB吧

• redo log buffer

  • 插入数据的过程中，生成的日志都得先保存起来，但又不能在还没commit的时候就直接写到redo log文件里。

所以，redo log buffer就是一块内存，用来先存redo日志的。也就是说，在执行第一个insert的时候，数据的内存被修改了，redo log buffer也写入了日志。 但是，真正把日志写到redo log文件（文件名是 ib_logfile+数字），是在执行commit语句的时候做的。(这里说的是事务执行过程中不会“主动去刷盘”，以减少不必要的IO消耗。但是可能会出现“被动写入磁盘”，比如内存不够、其他事务提交等情况)

• innodb_flush_at_trx_commit

  • 设置为0的时候，表示每次事务提交时都只是把redo log留在redo log buffer中

    • 我不建议你把innodb_flush_log_at_trx_commit 设置成0。因为把这个参数设置成0，表示redo log只保存在内存中，这样的话MySQL本身异常重启也会丢数据，风险太大。而redo log写到文件系统的page cache的速度也是很快的，所以将这个参数设置成2跟设置成0其实性能差不多，但这样做MySQL异常重启时就不会丢数据了，相比之下风险会更小

  • 设置为1的时候，表示每次事务提交时都将redo log直接持久化到磁盘。redo log在prepare阶段就要持久化一次。每秒一次后台轮询刷盘，再加上崩溃恢复这个逻辑，InnoDB就认为redo log在commit的时候就不需要fsync了，只会write到文件系统的page cache中就够了

  • 设置为2的时候，表示每次事务提交时都只是把redo log写到page cache

    • 将innodb_flush_log_at_trx_commit设置为2。这样做的风险是，主机掉电的时候会丢数据

  • InnoDB有一个后台线程，每隔1秒，就会把redo log buffer中的日志，调用write写到文件系统的page cache，然后调用fsync持久化到磁盘。

注意，事务执行中间过程的redo log也是直接写在redo log buffer中的，这些redo log也会被后台线程一起持久化到磁盘。也就是说，一个没有提交的事务的redo log，也是可能已经持久化到磁盘的

- 除了后台线程每秒一次的轮询操作外，还有两种场景会让一个没有提交的事务的redo log写入到磁盘中

    - 一种是，redo log buffer占用的空间即将达到 innodb_log_buffer_size一半的时候，后台线程会主动写盘
    - 另一种是，并行的事务提交的时候，顺带将这个事务的redo log buffer持久化到磁盘

binlog

• set sql_log_bin = 0 关闭 本线程 的 binlog 日志
• 两种格式： statement 和 row
• 恢复误删数据： 取最近的一次全量备份，恢复到临时库，重放 binlog 到删表前的一刻，将表数据从临时表中取出来，按需恢复到线上库
• sync_binlog=0的时候，表示每次提交事务都只write，不fsync
• sync_binlog=1的时候，表示每次提交事务都会执行fsync( 持久化到磁盘)；

• sync_binlog=N(N>1)的时候，表示每次提交事务都write，但累积N个事务后才fsync

  • 在出现IO瓶颈的场景里，将sync_binlog设置成一个比较大的值，可以提升性能。在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成0，比较常见的是将其设置为100~1000中的某个数值
  • 将sync_binlog 设置为大于1的值（比较常见是100~1000）。这样做的风险是，主机掉电时会丢binlog日志

• binlog组提交

  • binlog_group_commit_sync_delay参数，表示延迟多少微秒后才调用fsync
  • binlog_group_commit_sync_no_delay_count参数，表示累积多少次以后才调用fsync
  • 这两个条件是或的关系，也就是说只要有一个满足条件就会调用fsync
  • 当binlog_group_commit_sync_delay设置为0的时候，binlog_group_commit_sync_no_delay_count也无效了

• 格式

  • mixed

    • mixed格式的意思是，MySQL自己会判断这条SQL语句是否可能引起主备不一致，如果有可能，就用row格式，否则就用statement格式

  • row

    • row格式的缺点是，很占空间

  • statement

• 解析和查看binlog中的内容

  • mysqlbinlog -vv data/master.000001 --start-position=8900;

  • 将 master.000001 文件里面从第2738字节到第2973字节中间这段内容解析出来，放到MySQL去执行

    • mysqlbinlog master.000001 --start-position=2738 --stop-position=2973 | mysql -h127.0.0.1 -P13000 -u$user -p$pwd;

崩溃恢复

• binlog写完，redo log还没commit前发生crash，那崩溃恢复的时候MySQL会怎么处理？

  • 如果redo log里面的事务是完整的，也就是已经有了commit标识，则直接提交；

如果redo log里面的事务只有完整的prepare，则判断对应的事务binlog是否存在并完整： a. 如果是，则提交事务； b. 否则，回滚事务。

• MySQL怎么知道binlog是完整的

  • 一个事务的binlog是有完整格式的：

statement格式的binlog，最后会有COMMIT； row格式的binlog，最后会有一个XID event。 另外，在MySQL 5.6.2版本以后，还引入了binlog-checksum参数，用来验证binlog内容的正确性。对于binlog日志由于磁盘原因，可能会在日志中间出错的情况，MySQL可以通过校验checksum的结果来发现。所以，MySQL还是有办法验证事务binlog的完整性的。

• redo log 和 binlog是怎么关联起来的

  • 它们有一个共同的数据字段，叫XID。崩溃恢复的时候，会按顺序扫描redo log：

如果碰到既有prepare、又有commit的redo log，就直接提交； 如果碰到只有parepare、而没有commit的redo log，就拿着XID去binlog找对应的事务

• binlog cache

  • 一个事务的binlog是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。系统给binlog cache分配了一片内存，每个线程一个，参数 binlog_cache_size用于控制单个线程内binlog cache所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。

事务提交的时候，执行器把binlog cache里的完整事务写入到binlog中，并清空binlog cache

• 数据库的crash-safe保证的是

  • 如果客户端收到事务成功的消息，事务就一定持久化了；
  • 如果客户端收到事务失败（比如主键冲突、回滚等）的消息，事务就一定失败了；
  • 如果客户端收到“执行异常”的消息，应用需要重连后通过查询当前状态来继续后续的逻辑。此时数据库只需要保证内部（数据和日志之间，主库和备库之间）一致就可以了。

“双1”配置

• sync_binlog和innodb_flush_log_at_trx_commit都设置成 1
• 一个事务完整提交前，需要等待两次刷盘，一次是redo log（prepare 阶段），一次是binlog

日志逻辑序列号（log sequence number，LSN）

• LSN是单调递增的，用来对应redo log的一个个写入点。每次写入长度为length的redo log， LSN的值就会加上length
• LSN也会写到InnoDB的数据页中，来确保数据页不会被多次执行重复的redo log

隔离性与隔离级别

ACID（Atomicity，Consistency，Isolation，Durability， 即 原子性，一致性，隔离性，持久性）

问题

• 脏读（dirty read）
• 不可重复读（non-repeatable read）
• 幻读（phantom read）

事务隔离的实现

• 每条记录在更新的时候都会同时记录一条回滚操作，同一条记录在系统中存在多个版本，这就是多版本并发控制（MVVC）。当系统没有比这个回滚日志更早的 read-view 的时候，回滚日志就会被删除
• Mysql5.5 之前，回滚日志和数据字典一起放在 ibdata 文件中，即使长事务最终提交，回滚段被清理，文件也不会变小，只好为了清理回滚段，重建整个库。

SQL标准的隔离级别

• 读未提交（read uncommitted）
• 读提交（read committed）

• 可重复读（repeatable read）

  • 比如说数据校对，事务启动时的视图可以认为是静态的，不受其它事务更新的影响

• 串行化（serializable）

• 设置方式： 启动参数 transaction-isolation ： READ-COMMITED
• 查询方式：show variables like ‘transaction_isolation’;

避免长事务

• 业务角度

  • 业务自身预估 SET MAX_EXECUTION_TIME 命令，来控制每个语句的最长时间。

• 数据库角度

  • set autocommit=1 开启自动提交。用 commit and chain 语法，提交事务则自动启动下一个事务

  • 查询长事务

    • information_schema 库中的 innodb_trx: select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(), trx_started)) > 60;

  • 业务功能测试阶段要求输出所有的 general_log

  • percona 的 pt-kill 工具
  • mysql5.6 或更新版本，把 innodb_undo_tablespaces 设置成 2。大事务导致回滚段过大，这样设置方便清理。

立刻开始一个事务

• start transaction with consistent snapshot
• InnoDB的行数据有多个版本，每个数据版本有自己的row trx_id，每个事务或者语句有自己的一致性视图。普通查询语句是一致性读，一致性读会根据row trx_id和一致性视图确定数据版本的可见性。

对于可重复读，查询只承认在事务启动前就已经提交完成的数据； 对于读提交，查询只承认在语句启动前就已经提交完成的数据； 而当前读，总是读取已经提交完成的最新版本。

索引

索引类型

• 主键索引（聚簇索引 clustered index）

  • 主键索引的叶子节点存储的是页，一个页里面可以包含多个行。

• 非主键索引（二级索引 secondary index）

回表

• 非主键索引的查询需要多扫描一颗索引树，我们在应用中应该尽量使用主键索引。

索引维护

• NOT NULL PRIMARY KEY AUTO_INCREMENT
• 自增主键的插入数据模式，都不涉及到挪动其它记录，也不会触发叶子结点的分裂。而有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。

• 业务字段做主键，由于没有其它索引，也就不用考虑其它索引叶子节点大小的问题

  • 只有一个索引
  • 该索引必须是唯一索引

• 重建索引

  • 不论是删除主键还是创建主键，都会将整个表重建。所以重建索引需要用指令 alter table engine=InnoDB;
  • 重建普通索引 alter table T drop index k; alter table t add index(k);

索引优化

• 覆盖索引

• 前缀索引

  • 最左前缀原则：不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。
  • 这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。
  • 统计业务查询语句的访问频度：pt-query-digest

• 索引下推（index condition pushdown）

  • Mysql5.6 之后，在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

where 查询

• 查询语句中，调换 where 里面各个判断顺序是没有关系的
• select * from T where k in (1,2,3,4,5); --> 搜索 5 次
• select * from T where k between 1 and 5; --> 搜索 1 次

索引选择

• 唯一索引

  • 唯一索引的更新不能使用change buffer，只有普通索引可以使用

• 普通索引

  • 这个记录要更新的目标页在内存中

    • 对于唯一索引来说，找到3和5之间的位置，判断到没有冲突，插入这个值，语句执行结束； 对于普通索引来说，找到3和5之间的位置，插入这个值，语句执行结束。

  • 这个记录要更新的目标页不在内存中

    • 将更新记录在change buffer，语句执行就结束了

      • 对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时change buffer的使用效果最好。这种业务模型常见的就是账单类、日志类的系统

change buffer

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

前缀索引

• 使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本

• 创建前缀索引

  • alter table SUser add index index1(email);
  • alter table SUser add index index2(email(6));

• 决定前缀索引长度

  • select count(distinct left(email,4)）as L4, count(distinct left(email,5)）as L5, count(distinct left(email,6)）as L6, count(distinct left(email,7)）as L7, from SUser;

• 倒序存储

  • select field_list from t where id_card = reverse('input_id_card_string');

• 使用hash字段

  • alter table t add id_card_crc int unsigned, add index(id_card_crc);
  • select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

MySql最多创建多少列索引

• 16

索引失效

• 对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。需要注意的是，优化器并不是要放弃使用这个索引。

• 隐式类型转换

  • select “10” > 9
  • 在MySQL中，字符串和数字做比较的话，是将字符串转换成数字

  • 字符集不同只是条件之一，连接过程中要求在被驱动表的索引字段上加函数操作，是直接导致对被驱动表做全表扫描的原因

    • select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;
    • alter table trade_detail modify tradeid varchar(32) CHARACTER SET utf8mb4 default null;
    • select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2;

锁

全局锁

• 全局读锁

  • flush tables with read lock(FTWRL)

    • 之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句
    • 客户端发生异常断开，那么MySQL会自动释放这个全局锁

  • set global readonly=true

    • 客户端发生异常，则数据库就会一直保持readonly状态

表级锁

• 表锁

  • lock tables ... read/write

• 元数据锁(meta data lock, MDL)

  • 安全地给小表加字段

    • 首先我们要解决长事务，事务不提交，就会一直占着MDL锁。在MySQL的information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。如果你要做DDL变更的表刚好有长事务在执行，要考虑先暂停DDL，或者kill掉这个长事务。

    • 要变更的表是一个热点表，虽然数据量不大，但是上面的请求很频繁。需要指定的等待时间

      • ALTER TABLE tbl_name NOWAIT add column ...
      • ALTER TABLE tbl_name WAIT N add column ...

  • 用 –single-transaction 方法做逻辑备份的过程中，如果主库上的一个小表做了一个DDL，比如给一个表上加了一列。这时候，从备库上会看到什么现象呢

  • Online DDL

    • 1. 拿MDL写锁

1. 降级成MDL读锁
2. 真正做DDL
3. 升级成MDL写锁
4. 释放MDL锁

行锁

• innodb_lock_wait_timeout

  • 默认值是50s

• innodb_deadlock_detect

  • 默认值 on，表示开启死锁检测

• 减少锁冲突

  • 修改MySQL源码，基本思路就是，对于相同行的更新，在进入引擎之前排队。这样在InnoDB内部就不会有大量的死锁检测工作
  • 通过将一行改成逻辑上的多行来减少锁冲突

• 删除一个表里面的前10000行数据

  • 在一个连接中循环执行20次 delete from T limit 500;

next-key lock(前开后闭区间，锁是加在索引上的)

• 间隙锁

  • 读提交没有间隙锁，只有可重复读级别才有间隙锁
  • 读提交+binlog_format=row 解决没有间隙锁可能出现的数据不一致问题
  • innodb_locks_unsafe_for_binlog=1 表示不加间隙锁

• 行锁

间隙锁

可重复读隔离级别下的加锁规则

• 两个“原则”、两个“优化”和一个“bug”

  • 原则1：加锁的基本单位是next-key lock。希望你还记得，next-key lock是前开后闭区间。
  • 原则2：查找过程中访问到的对象才会加锁。
  • 优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。
  • 优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。
  • 一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

• 在删除数据的时候尽量加limit

概念

数据查询语言DQL

• SELECT <字段名表>
• FROM <表或视图名>
• WHERE <查询条件>

数据操纵语言DML

• INSERT
• UPDATE
• DELETE

数据定义语言DDL

• CREATE TABLE/VIEW/INDEX/SYN/CLUSTER

数据控制语言DCL

• GRANT：授权
• ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点

• COMMIT [WORK]：提交

  • 显式提交

    • COMMIT

  • 隐式提交

    • 用SQL命令间接完成的提交，这些命令是： ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP， EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME

  • 自动提交

    • AUTOCOMMIT设置为ON

事务控制语句TCL

• SAVEPOINT：保存点
• ROLLBACK：回退到某点
• COMMIT：提交事务

3NF

• 1NF 指的是数据库表中的任何属性都具有原子性的，不可再分解
• 2NF 是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性
• 3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余

DDL

innodb_file_per_table

• OFF表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起
• ON表示的是，每个InnoDB表数据存储在一个以 .ibd为后缀的文件中
• 从MySQL 5.6.6版本开始，它的默认值就是ON了

重建表

• MySQL 5.6版本开始引入的Online DDL

  • alter table t engine=InnoDB(相当于 alter table t engine=innodb,ALGORITHM=inplace;)
  • 强制拷贝表 alter table t engine=innodb,ALGORITHM=copy;

• gh-ost 来做 Online DDL

• 三种重建表方式的区别

  • optimize table

    • 等于recreate+analyze

  • analyze table t

    • analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了MDL读锁

  • alter table t engine = InnoDB

    • alter table t engine = InnoDB（也就是recreate）

• 一个表t文件大小为1TB； 对这个表执行 alter table t engine=InnoDB； 发现执行完成后，空间不仅没变小，还稍微大了一点儿，比如变成了1.01TB

  • 在重建表的时候，InnoDB不会把整张表占满，每个页留了1/16给后续的更新用。也就是说，其实重建表之后不是“最”紧凑的。

假如是这么一个过程： 将表t重建一次； 插入一部分数据，但是插入的这些数据，用掉了一部分的预留空间； 这种情况下，再重建一次表t，就可能会出现问题中的现象

非 Online

• inplace的DDL，有可能不是Online的。截止到MySQL 8.0，添加全文索引（FULLTEXT index）和空间索引(SPATIAL index)就属于这种情况

count()

show table status

• TABLE_ROWS 是从采样估算得来的，官方文档说误差可能达到40%到50%。所以，show table status命令显示的行数不能直接使用。

MyISAM表虽然count(*)很快，但是不支持事务；

show table status命令虽然返回很快，但是不准确； InnoDB表直接count(*)会遍历全表，虽然结果准确，但会导致性能问题

count(主键id)

• InnoDB引擎会遍历整张表，把每一行的id值都取出来，返回给server层。server层拿到id后，判断是不可能为空的，就按行累加

count(1)

• InnoDB引擎遍历整张表，但不取值。server层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加

count(字段)

• 如果这个“字段”是定义为not null的话，一行行地从记录里面读出这个字段，判断不能为null，按行累加；

如果这个“字段”定义允许为null，那么执行的时候，判断到有可能是null，还要把值取出来再判断一下，不是null才累加

count(*)

• 并不会把全部字段取出来，而是专门做了优化，不取值。count(*)肯定不是null，按行累加

业务设计

用户互相关注

字段为什么要求建议为not null

• null值会占用更多的字节,且会在程序中造成很多与预期不符的情况
• NULL columns require additional space in the rowto record whether their values are NULL. For MyISAM tables, each NULL columntakes one bit extra, rounded up to the nearest byte.

排序

sort_buffer_size

• 如果sort_buffer_size超过了需要排序的数据量的大小，number_of_tmp_files就是0，表示排序可以直接在内存中完成。
• 否则就需要放在临时文件中排序。sort_buffer_size越小，需要分成的份数越多，number_of_tmp_files的值就越大

rowid排序

• max_length_for_sort_data

  • 控制用于排序的行数据的长度的一个参数。它的意思是，如果单行的长度超过这个值，MySQL就认为单行太大

• 外部排序

  • 归并排序
  • 确定一个排序语句是否使用了临时文件

• MySQL的表是用什么方法来定位“一行数据”的

  • 对于有主键的InnoDB表来说，这个rowid就是主键ID；
  • 对于没有主键的InnoDB表来说，这个rowid就是由系统生成的；
  • MEMORY引擎不是索引组织表。在这个例子里面，你可以认为它就是一个数组。因此，这个rowid其实就是数组的下标。
  • order by rand()使用了内存临时表，内存临时表排序的时候使用了rowid排序方法。

• mysql rowid 为什么6个字节

全字段排序

排序临时表

• tmp_table_size这个配置限制了内存临时表的大小，默认值是16M。如果临时表大小超过了tmp_table_size，那么内存临时表就会转成磁盘临时表
• 磁盘临时表使用的引擎默认是InnoDB，是由参数internal_tmp_disk_storage_engine控制的
• 把tmp_table_size设置成1024，把sort_buffer_size设置成 32768, 把 max_length_for_sort_data 设置成16

常见操作

查看表结构

• desc table_name;

查看隔离级别

• select @@global.tx_isolation;

创建用户

• CREATE USER 'username'@'host' IDENTIFIED BY 'password';

授权

• GRANT privileges ON databasename.tablename TO 'username'@'host';

删除表

• delete from table_name;

  • 仅删除表数据，支持条件过滤，支持回滚。记录日志。因此比较慢。

• truncate table table_name;

  • 仅删除所有数据，不支持条件过滤，不支持回滚。不记录日志，效率高于delete。

• drop table table_name;

  • 删除表数据同时删除表结构。将表所占的空间都释放掉。删除效率最高

查看当前表所有索引

• show index from table_name;

设置慢查询时间

• set long_query_time=0

性能问题

饮鸩止渴”提高性能

• 第一种方法：先处理掉那些占着连接但是不工作的线程

  • show processlist;
  • 查information_schema库的innodb_trx表 select * from information_schema.innodb_trx;
  • 从数据库端主动断开连接可能是有损的，尤其是有的应用端收到这个错误后，不重新连接，而是直接用这个已经不能用的句柄重试查询。这会导致从应用端看上去，“MySQL一直没恢复”。

• 第二种方法：减少连接过程的消耗

  • 重启数据库，并使用–skip-grant-tables参数启动。这样，整个MySQL会跳过所有的权限验证阶段，包括连接过程和语句执行过程在内
  • 在MySQL 8.0版本里，如果你启用–skip-grant-tables参数，MySQL会默认把 --skip-networking参数打开，表示这时候数据库只能被本地的客户端连接。可见，MySQL官方对skip-grant-tables这个参数的安全问题也很重视

只查一行数据查询很慢(长时间不返回)

• 等MDL锁

• 等flush

  • flush tables t with read lock; flush tables with read lock; 这两个flush语句，如果指定表t的话，代表的是只关闭表t；如果没有指定具体的表名，则表示关闭MySQL里所有打开的表

• 等行锁

  • select * from t where id=1 lock in share mode;

• undo log 太多

慢查询性能问题

• 第一种可能是，索引没有设计好

  • 在备库B上执行 set sql_log_bin=off，也就是不写binlog，然后执行alter table 语句加上索引；

执行主备切换；

这时候主库是B，备库是A。在A上执行 set sql_log_bin=off，然后执行alter table 语句加上索引

- 用文中提到的DDL方案，会导致binlog里面少了这个DDL语句，后续影响备份恢复的功能。由于需要另一个知识点（主备同步协议），我放在后面的文章中说明

- gh-ost

• 第二种可能是，语句没写好

  • 查询重写

    • mysql> insert into query_rewrite.rewrite_rules(pattern, replacement, pattern_database) values ("select from t where id + 1 = ?", "select from t where id = ? - 1", "db1");

call query_rewrite.flush_rewrite_rules();

• 第三种可能是，MySQL选错了索引

  • 查询重写

    • 使用查询重写功能，给原来的语句加上force index

• 预防

  • 上线前，在测试环境，把慢查询日志（slow log）打开，并且把long_query_time设置成0，确保每个语句都会被记录入慢查询日志；

在测试表里插入模拟线上的数据，做一遍回归测试；

观察慢查询日志里每类语句的输出，特别留意Rows_examined字段是否与预期一致

- 在review项目的时候，不止要review我们自己业务的代码，也要review连接器的行为。一般做法就是在测试环境，把general_log打开，用业务行为触发连接，然后通过general log分析连接器的行为

QPS突增问题

• 规范的运维体系：虚拟化、白名单机制、业务账号分离

  • 单独的数据库用户，可以用管理员账号把这个用户删掉，然后断开现有连接。这样，这个新功能的连接不成功，由它引发的QPS就会变成0。
  • 如果这个新增的功能跟主体功能是部署在一起的，那么我们只能通过处理语句来限制。这时，我们可以使用上面提到的查询重写功能，把压力最大的SQL语句直接重写成"select 1"返回

主备

把节点B（也就是备库）设置成只读（readonly）模式

• 有时候一些运营类的查询语句会被放到备库上去查，设置为只读可以防止误操作；
• 防止切换逻辑有bug，比如切换过程中出现双写，造成主备不一致；
• 可以用readonly状态，来判断节点的角色
• readonly设置对超级(super)权限用户无效

双M结构

• log_slave_updates设置为on，表示备库执行relay log后生成binlog

• 循环复制

  • MySQL在binlog中记录了这个命令第一次执行时所在实例的server id
  • 规定两个库的server id必须不同，如果相同，则它们之间不能设定为主备关系；
  • 一个备库接到binlog并在重放的过程中，生成与原binlog的server id相同的新的binlog；
  • 每个库在收到从自己的主库发过来的日志后，先判断server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志。

• 什么情况下双M结构会出现循环复制

  • 一种场景是，在一个主库更新事务后，用命令set global server_id=x修改了server_id。等日志再传回来的时候，发现server_id跟自己的server_id不同，就只能执行了
  • 另一种场景是，有三个节点的时候，如图7所示，trx1是在节点 B执行的，因此binlog上的server_id就是B，binlog传给节点 A，然后A和A’搭建了双M结构，就会出现循环复制。

主备延迟的来源

• seconds_behind_master

  • show slave status命令，它的返回结果里面会显示seconds_behind_master
  • 主备延迟最直接的表现是，备库消费中转日志（relay log）的速度，比主库生产binlog的速度要慢

• 主备延迟的来源

  • 首先，有些部署条件下，备库所在机器的性能要比主库所在的机器性能差
  • 第二种常见的可能，即备库的压力大

  • 第三种可能，即大事务

    • 一次性地用delete语句删除太多数据

  • 造成主备延迟还有一个大方向的原因，就是备库的并行复制能力

主备切换

• 基于位点的主备切换

• 主备切换策略

  • 可用性优先策略

  • 可靠性优先策略

    • 可靠性优先策略必须得等到备库B的seconds_behind_master=0之后，才能切换。

• GTID

MySQL 5.7并行复制策略

• 同时处于prepare状态的事务，在备库执行时是可以并行的；
• 处于prepare状态的事务，与处于commit状态的事务之间，在备库执行时也是可以并行的
• binlog_group_commit_sync_delay参数，表示延迟多少微秒后才调用fsync;

binlog_group_commit_sync_no_delay_count参数，表示累积多少次以后才调用fsync。

这两个参数是用于故意拉长binlog从write到fsync的时间，以此减少binlog的写盘次数。在MySQL 5.7的并行复制策略里，它们可以用来制造更多的“同时处于prepare阶段的事务”。这样就增加了备库复制的并行度。也就是说，这两个参数，既可以“故意”让主库提交得慢些，又可以让备库执行得快些。在MySQL 5.7处理备库延迟的时候，可以考虑调整这两个参数值，来达到提升备库复制并发度的目的。

MySQL 5.7.22版本里，MySQL增加了一个新的并行复制策略，基于WRITESET的并行复制

• 新增了一个参数binlog-transaction-dependency-tracking，用来控制是否启用这个新策略

  • COMMIT_ORDER，表示的就是前面介绍的，根据同时进入prepare和commit来判断是否可以并行的策略。
  • WRITESET，表示的是对于事务涉及更新的每一行，计算出这一行的hash值，组成集合writeset。如果两个事务没有操作相同的行，也就是说它们的writeset没有交集，就可以并行。
  • WRITESET_SESSION，是在WRITESET的基础上多了一个约束，即在主库上同一个线程先后执行的两个事务，在备库执行的时候，要保证相同的先后顺序。

当然为了唯一标识，这个hash值是通过“库名+表名+索引名+值”计算出来的。如果一个表上除了有主键索引外，还有其他唯一索引，那么对于每个唯一索引，insert语句对应的writeset就要多增加一个hash值。

- writeset是在主库生成后直接写入到binlog里面的，这样在备库执行的时候，不需要解析binlog内容（event里的行数据），节省了很多计算量；

不需要把整个事务的binlog都扫一遍才能决定分发到哪个worker，更省内存；

由于备库的分发策略不依赖于binlog内容，所以binlog是statement格式也是可以的。

因此，MySQL 5.7.22的并行复制策略在通用性上还是有保证的。

当然，对于“表上没主键”和“外键约束”的场景，WRITESET策略也是没法并行的，也会暂时退化为单线程模型。

过期读

• 强制走主库方案；
• sleep方案；
• 判断主备无延迟方案；
• 配合semi-sync方案；
• 等主库位点方案；

• 等GTID方案

  • select wait_for_executed_gtid_set(gtid_set, 1);
  • 怎么能够让MySQL在执行事务后，返回包中带上GTID呢？

你只需要将参数session_track_gtids设置为OWN_GTID，然后通过API接口mysql_session_track_get_first从返回包解析出GTID的值即可

- 当然了，这只是一个例子。你要使用这个方案的时候，还是应该在你的客户端代码中调用mysql_session_track_get_first这个函数

判读数据库是否正常

innodb_thread_concurrency

• 设置innodb_thread_concurrency参数的目的是，控制InnoDB的并发线程上限。也就是说，一旦并发线程数达到这个值，InnoDB在接收到新请求的时候，就会进入等待状态，直到有线程退出。

这里，我把innodb_thread_concurrency设置成3，表示InnoDB只允许3个线程并行执行

• 通常情况下，我们建议把innodb_thread_concurrency设置为64~128之间的值。
• 在线程进入锁等待以后，并发线程的计数会减一，也就是说等行锁（也包括间隙锁）的线程是不算在128里面的

select 1判断

查表判断

更新判断

内部统计

数据恢复

delete命令删除的数据

• Flashback

  • binlog_format=row
  • binlog_row_image=FULL

• sql_safe_updates参数设置为on

  • 如果我们忘记在delete或者update语句中写where条件，或者where条件里面没有包含索引字段的话，这条语句的执行就会报错。
  • 真的要把一个小表的数据全部删掉，可以在delete语句中加上where条件，比如where id>=0

truncate/drop 误删库/表

• 在用备份恢复出临时实例之后，将这个临时实例设置成线上备库的从库
• 在start slave之前，先通过执行 change replication filter replicate_do_table = (tbl_name) 命令，就可以让临时库只同步误操作的表；

这样做也可以用上并行复制技术，来加速整个数据恢复过程

搭建延迟复制的备库

• CHANGE MASTER TO MASTER_DELAY = N

预防误删库/表的方法

• 在删除数据表之前，必须先对表做改名操作。然后，观察一段时间，确保对业务无影响以后再删除这张表。
• 改表名的时候，要求给表名加固定的后缀（比如加_to_be_deleted)，然后删除表的动作必须通过管理系统执行。并且，管理系删除表的时候，只能删除固定后缀的表。

kill

• 当用户执行kill query thread_id_B时

  • 把session B的运行状态改成THD::KILL_QUERY(将变量killed赋值为THD::KILL_QUERY)；
  • 给session B的执行线程发一个信号。

• kill无效

  • 第一类情况，即：线程没有执行到判断线程状态的逻辑

  • 另一类情况是，终止逻辑耗时较长。这时候，从show processlist结果上看也是Command=Killed，需要等到终止逻辑完成，语句才算真正完成

    • 超大事务执行期间被kill。这时候，回滚操作需要对事务执行期间生成的所有新数据版本做回收操作，耗时很长
    • 大查询回滚。如果查询过程中生成了比较大的临时文件，加上此时文件系统压力大，删除临时文件可能需要等待IO资源，导致耗时较长
    • DDL命令执行到最后阶段，如果被kill，需要删除中间过程的临时文件，也可能受IO资源影响耗时较久

• 两个关于客户端的误解

  • 第一个误解是：如果库里面的表特别多，连接就会很慢

    • 如果在连接命令中加上-A，就可以关掉这个自动补全的功能，然后客户端就可以快速返回了
    • 我们感知到的连接过程慢，其实并不是连接慢，也不是服务端慢，而是客户端慢

  • –quick是一个更容易引起误会的参数，也是关于客户端常见的一个误解。客户端接收服务端返回结果的方式有两种

    • • 一种是本地缓存，也就是在本地开一片内存，先把结果存起来。如果你用API开发，对应的就是mysql_store_result 方法。
      • 另一种是不缓存，读一个处理一个。如果你用API开发，对应的就是mysql_use_result方法。
      • MySQL客户端默认采用第一种方式，而如果加上–quick参数，就会使用第二种不缓存的方式。
      • 采用不缓存的方式时，如果本地处理得慢，就会导致服务端发送结果被阻塞，因此会让服务端变慢。关于服务端的具体行为，我会在下一篇文章再和你展开说明。

    • 为什么要给这个参数取名叫作quick呢

      • 第一点，就是前面提到的，跳过表名自动补全功能。
      • 第二点，mysql_store_result需要申请本地内存来缓存查询结果，如果查询结果太大，会耗费较多的本地内存，可能会影响客户端本地机器的性能；
      • 第三点，是不会把执行命令记录到本地的命令历史文件。
      • 所以你看到了，–quick参数的意思，是让客户端变得更快

四个脚本

• 备份脚本
• 执行脚本
• 验证脚本
• 回滚脚本

后来通过 chatrr +i 命令给所有重要的文件增加了 i 权限属性，这样哪怕 root 用户都无法直接删除文件

结果集

取数据和发数据的流程

• 获取一行，写到net_buffer中。这块内存的大小是由参数net_buffer_length定义的，默认是16k。
• 重复获取行，直到net_buffer写满，调用网络接口发出去。
• 如果发送成功，就清空net_buffer，然后继续取下一行，并写入net_buffer。
• 如果发送函数返回EAGAIN或WSAEWOULDBLOCK，就表示本地网络栈（socket send buffer）写满了，进入等待。直到网络栈重新可写，再继续发送。

对于正常的线上业务来说，如果一个查询的返回结果不会很多的话，我都建议你使用mysql_store_result这个接口，直接把查询结果保存到本地内存。

当然前提是查询返回结果不多。大查询就需要改用mysql_use_result接口

innodb_buffer_pool_size

• 一般建议设置成可用物理内存的60%~80%

改进的LRU算法

• 在InnoDB实现上，按照5:3的比例把整个LRU链表分成了young区域和old区域。图中LRU_old指向的就是old区域的第一个位置，是整个链表的5/8处。也就是说，靠近链表头部的5/8是young区域，靠近链表尾部的3/8是old区域

• 处于old区域的数据页，每次被访问的时候都要做下面这个判断

  • 若这个数据页在LRU链表中存在的时间超过了1秒，就把它移动到链表头部；
  • 如果这个数据页在LRU链表中存在的时间短于1秒，位置保持不变。1秒这个时间，是由参数innodb_old_blocks_time控制的。其默认值是1000，单位毫秒。
  • 扫描过程中，需要新插入的数据页，都被放到old区域;
  • 一个数据页里面有多条记录，这个数据页会被多次访问到，但由于是顺序扫描，这个数据页第一次被访问和最后一次被访问的时间间隔不会超过1秒，因此还是会被保留在old区域；
  • 再继续扫描后续的数据，之前的这个数据页之后也不会再被访问到，于是始终没有机会移到链表头部（也就是young区域），很快就会被淘汰出去。
  • 可以看到，这个策略最大的收益，就是在扫描这个大表的过程中，虽然也用到了Buffer Pool，但是对young区域完全没有影响，从而保证了Buffer Pool响应正常业务的查询命中率。

join

Simple Nested-Loop Join(NLJ)

• 应该选择小表做驱动表

Block Nested-Loop Join(BNL)

• join_buffer_size
• 在join_buffer_size足够大的时候，是一样的；
• 在join_buffer_size不够大的时候（这种情况更常见），应该选择小表做驱动表

• BNL算法对系统的影响主要包括三个方面

  • 可能会多次扫描被驱动表，占用磁盘IO资源；
  • 判断join条件需要执行M*N次对比（M、N分别是两张表的行数），如果是大表就会占用非常多的CPU资源；
  • 可能会导致Buffer Pool的热数据被淘汰，影响内存命中率

• 常见做法是，给被驱动表的join字段加上索引，把BNL算法转成BKA算法

原则

• 决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。
• 如果可以使用被驱动表的索引，join语句还是有其优势的；
• 不能使用被驱动表的索引，只能使用Block Nested-Loop Join算法，这样的语句就尽量不要使用；
• 在使用join的时候，应该让小表做驱动表。

优化

• MRR优化

  • 如果你想要稳定地使用MRR优化的话，需要设置set optimizer_switch="mrr_cost_based=off"。（官方文档的说法，是现在的优化器策略，判断消耗的时候，会更倾向于不使用MRR，把mrr_cost_based设置为off，就是固定使用MRR了
  • read_rnd_buffer的大小是由read_rnd_buffer_size参数控制的

• Batched Key Access(BKA)

  • 如果要使用BKA优化算法的话，你需要在执行SQL语句之前，先设置

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on'; 其中，前两个参数的作用是要启用MRR。这么做的原因是，BKA算法的优化要依赖于MRR。

• BNL 转 BKA

  • 直接在被驱动表上建索引
  • 被驱动表放入临时表加索引

表

内存表和临时表

• 内存表与临时表的区别

  • 内存表，指的是使用Memory引擎的表，建表语法是create table … engine=memory

• 临时表

  • 而临时表，可以使用各种引擎类型 。如果是使用InnoDB引擎或者MyISAM引擎的临时表，写数据的时候是写到磁盘上的。当然，临时表也可以使用Memory引擎

  • 临时表在使用上有以下几个特点

    • 建表语法是create temporary table …。
    • 一个临时表只能被创建它的session访问，对其他线程不可见。所以，图中session A创建的临时表t，对于session B就是不可见的。
    • 临时表可以与普通表同名。
    • session A内有同名的临时表和普通表的时候，show create语句，以及增删改查语句访问的是临时表。
    • show tables命令不显示临时表。
    • 由于临时表只能被创建它的session访问，所以在这个session结束的时候，会自动删除临时表。

  • 临时表就特别适合我们文章开头的join优化这种场景

    • 不同session的临时表是可以重名的，如果有多个session同时执行join优化，不需要担心表名重复导致建表失败的问题。
    • 不需要担心数据删除问题。如果使用普通表，在流程执行过程中客户端发生了异常断开，或者数据库发生异常重启，还需要专门来清理中间过程中生成的数据表。而临时表由于会自动回收，所以不需要这个额外的操作。

  • 用户临时表

    • 为什么临时表可以重名

      • 这个frm文件放在临时文件目录下，文件名的后缀是.frm，前缀是“#sql{进程id}_{线程id}_序列号”。你可以使用select @@tmpdir命令，来显示实例的临时文件目录。
      • 在5.6以及之前的版本里，MySQL会在临时文件目录下创建一个相同前缀、以.ibd为后缀的文件，用来存放数据文件； 而从 5.7版本开始，MySQL引入了一个临时文件表空间，专门用来存放临时文件的数据。因此，我们就不需要再创建ibd文件了。 从文件名的前缀规则，我们可以看到，其实创建一个叫作t1的InnoDB临时表，MySQL在存储上认为我们创建的表名跟普通表t1是不同的，因此同一个库下面已经有普通表t1的情况下，还是可以再创建一个临时表t1的。

      • 备库执行逻辑

        • session A的临时表t1，在备库的table_def_key就是：库名+t1+“M的serverid”+“session A的thread_id”;
        • session B的临时表t1，在备库的table_def_key就是 ：库名+t1+“M的serverid”+“session B的thread_id”。
        • 由于table_def_key不同，所以这两个表在备库的应用线程里面是不会冲突的。

      • 在binlog_format='row’的时候，临时表的操作不记录到binlog中

  • 内部临时表

  • group by

    • 内存临时表的大小是有限制的，参数tmp_table_size就是控制这个内存大小的，默认是16M

    • 优化

      • 有序输入

      • 索引

        • alter table t1 add column z int generated always as(id % 100), add index(z);
        • select z, count(*) as c from t1 group by z;

      • 直接排序

    • 指导原则

      • 如果对group by语句的结果没有排序要求，要在语句后面加 order by null；
      • 尽量让group by过程用上表的索引，确认方法是explain结果里没有Using temporary 和 Using filesort；
      • 如果group by需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大tmp_table_size参数，来避免用到磁盘临时表；
      • 如果数据量实在太大，使用SQL_BIG_RESULT这个提示，来告诉优化器直接使用排序算法得到group by的结果

  • 为什么不能用rename修改临时表的改名

    • 在实现上，执行rename table语句的时候，要求按照“库名/表名.frm”的规则去磁盘找文件，但是临时表在磁盘上的frm文件是放在tmpdir目录下的，并且文件名的规则是“#sql{进程id}_{线程id}_序列号.frm”，因此会报“找不到文件名”的错误。

内存表和Innodb的区别

• InnoDB引擎把数据放在主键索引上，其他索引上保存的是主键id。这种方式，我们称之为索引组织表（Index Organizied Table）。
• 而Memory引擎采用的是把数据单独存放，索引上保存数据位置的数据组织形式，我们称之为堆组织表（Heap Organizied Table）。
• InnoDB支持变长数据类型，不同记录的长度可能不同；内存表不支持Blob 和 Text字段，并且即使定义了varchar(N)，实际也当作char(N)，也就是固定长度字符串来存储，因此内存表的每行数据长度相同
• 内存表不支持行锁，只支持表锁

分区表

• 第一次访问的时候需要访问所有分区
• 共用MDL锁

自增

AUTO_INCREMENT

自增主键id不连续

• 事务回滚
• 唯一键冲突

auto_increment_offset

auto_increment_increment

自增锁的优化

• innodb_autoinc_lock_mode(MySQL 5.1.22版本引入, 默认值 1)

  • 设置为 0

    • 表示采用之前MySQL 5.0版本的策略，即语句执行结束后才释放锁；

  • 设置为 1

    • 普通insert语句，自增锁在申请之后就马上释放
    • 类似insert … select这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放；
    • 批量插入数据，包含的语句类型是insert … select、replace … select和load data语句

  • 设置为 2

    • 所有的申请自增主键的动作都是申请后就释放锁

批量申请自增id的策略

• 语句执行过程中，第一次申请自增id，会分配1个；
• 1个用完以后，这个语句第二次申请自增id，会分配2个；
• 2个用完以后，还是这个语句，第三次申请自增id，会分配4个；
• 依此类推，同一个语句去申请自增id，每次申请到的自增id个数都是上一次的两倍

一个表的数据导入到另外一个表

物理拷贝

select … into outfile

mysqldump

权限

grant语句会同时修改数据表和内存，判断权限的时候使用的是内存数据。因此，规范地使用grant和revoke语句，是不需要随后加上flush privileges语句的

grant操作对于已经存在的连接的影响，在全局权限和基于db的权限效果是不同的

表权限和列权限