1、 基础架构：一条SQL查询语句是如何执行的。

Mysql可以分为Server层和存储引擎两部分。
存储引擎：负责数据的存储和提取，架构模式是插件式的。

Server层

连接器、查询缓存、分析器、优化器、执行器，所有内置函数+跨存储引擎功能都在这一层实现。

连接器

负责和客户端建立连接、获取权限、维持和管理连接。
长连接是指：连接成功后，客户端持续有请求，则一直使用同一个连接。
全部使用长连接后，MySQL占用内存可能会变得特别大，MySQL在执行过程中临时使用的内存是管理在连接对象里面的。这些资源在连接断开时才会释放，内存占用过大，就会被系统kill掉（OOM），现象看就是MySQL重启了。

  1. 定期断开长连接。执行一个占用内存大的查询后，断开这个连接。

  2. MySQL5.7版本后，执行一个占用内存大的查询后，执行mysql_reset_connection初始化连接资源。

    查询缓存【不建议使用，8.0版本后移除了】

    MySQL拿到一个查询请求后，会先在查询缓存中查询。对于更新压力很大的数据库来说，查询缓存失效很快。

    分析器

  3. 词法分析。 识别字符串代表什么。（如果分析出来字段不存在，直接报错）

  4. 语法分析。 判断是否满足SQL语法。

    优化器

    在表里有多个索引时，决定用哪一个索引。
    一个语句有多个表关联（join）时，决定表的连接顺序。

    执行器

    执行前先会判断你对这个表有没有执行查询的权限。
    有权限的话，打开表，执行器会根据表的引擎定义去使用引擎提供的接口。

  5. 调用InnoDB引擎接口取表的第一行（有索引的话，就是调用满足条件的第一行），判断条件，满足则将这行结果存在结果集里。

  6. 调用InnoDB引擎接口取下一行，重复判断逻辑，直到这个表的最后一行。

  7. 执行器将上述遍历过程中所有满足结果的条件行组成记录集作为结果集返回给客户端。
    数据库慢查询日志会有一个rows_examined字段，执行器每次调用引擎获取数据行的时候累加的。
    【引擎扫描行数跟rows_examined不是完全相同的】

    2、日志系统：一条SQL更新语句是如何执行的。

    日志模块 redo log 【InnoDB引擎特有】

    有一条记录需要更新时，InnoDB引擎会把记录写到redo log（粉板）里，并更新内存。在合适的时间（掌柜打烊了或者粉板写满了），InnoDB会把这个记录更新到磁盘。
    有了redo log，InnoDB保证了即使数据库异常重启，之前提交的记录不会丢失。（crash-safe）

    日志模块 bin log 【Server层自己的日志，可以记Sql或者记行改动（更新前+更新后）】

  8. Redo log是InnoDB独有的。binlog是Server层实现的，所有引擎都能用。

  9. Redo log是物理日志，记录‘某个数据页上做了什么修改。’binlog是逻辑日志，记录这个语句的原始逻辑‘给xxx这行的x字段加一’。

  10. Redo log是循环写的，空间固定。binlog文件写道一定大小后会切到下一个，不会覆盖原有日志。

    update语句流程-写完redo log事务就成功了

  11. 执行器找引擎取到ID=2这一行。在内存的话直接返回给执行器，否则从磁盘读。

  12. 执行器拿到引擎的数据做运算，再调用引擎接口写入这行数据。
  13. 引擎将这行数据更新到内存中，同时更新操作记录到redo log中。Redo log处于perpare状态，告知执行器执行完成，可以提交事务。
  14. 执行器生成这个操作的binlog，并写入磁盘。

  15. 执行器调用引擎的提交事务接口，将Redo log改成commit状态。更新完成

    redo log为什么要两阶段提交

    由于redo log和binlog是两个完全独立的逻辑。先写redo log/binlog都会导致 数据库的状态和用它的日志恢复出来的状态不一致。
    两阶段提交就是让这两个状态保持逻辑上的一致。

    3、事务隔离：你改了我看不见

    隔离性与隔离级别

  16. 读未提交。一个事务未提交时，它的变更就能被其他事务看到。

  17. 读提交【Oracle默认】。一个事务提交之后，它做的变更才会被其它事务看到。（每一个SQL执行时都会创建新的视图）
  18. 可重复读。一个事务执行过程中看到的数据和它开始事务时看到的数据是一致的。（事务启动时创建一致性视图，事务中其它查询共用这个视图）

  19. 串行化。同一行记录，写锁+读锁。事务执行完才能走下一个。

    事务隔离的实现

    每条记录在跟新时都会同时记录一条回滚操作，同一条记录在系统中可以存在多个版本，这就是数据库的多版本并发控制（MVCC）。回滚日志会在不需要的时候删除（系统中没有比这个回滚日志更早的read-view）
    长事务提交之前，所有它可能用到的回滚记录都得保留，占用大量的存储空间/锁。无法避免的话，需要保证逻辑日志空间足够用，监控Innodb_trx表，长事务报警。

  20. 是否设置 set autocommit=0，需要改成1。

  21. 是否有不必要的只读事务。
  22. 通过对业务的评估，设置set max_execution_time控制语句最长执行时间。

4、索引

主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。
删除表数据它的索引记录还在，需要重建索引。（重建主键索引不合适，整个表都会重建）

索引下推
满足最左前缀原则的时候，最左前缀可以用于在索引中定位记录。Mysql5.6以后，可以在遍历索引的过程中，对索引字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

5、全局锁、表锁、行锁

全局锁

整个库处于只读状态。（全库逻辑备份）
Mysqldump 使用参数-single-transaction的时候，导数据就会启动一个事务，由于MVCC的支持（必须是InnoDB引擎），数据可以更新。
DML：增删改数据
DDL：修改表结构

表级锁

  1. 显示使用表锁语法lock tables …read/write。InnoDB支持行级锁

  2. 隐式表级的锁MDL。确保读写的正确性。DML时，加MDL读锁。DDL时，加MDL写锁。
    事务中的MDL锁，在语句执行时开始申请，整个事务提交后才会释放。【表结构变更一定要注意】

    如何安全的给小表加字段

  3. 解决长事务。事务不提交就会一直占用着MDL锁。做DDL变更时，需要kill掉长事务。

  4. 在alter table语句中设定等待时间。

    行锁

    两阶段锁协议

    InnoDB事务中，行锁在需要时才加上，事务结束时才释放。（如果你的事务中需要锁多个行，需要把最有可能冲突的的锁尽量往后放。）

    死锁和死锁检测

    事务A和事务B互相等待对方资源释放。

  5. 直接进入等待，直到超时。通过参数innodb_deadlock_timeout设置。（不可行）

  6. 发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务。innodb_deadlock_detect设置为0默认开启这个逻辑。【1000个并发线程更新同一行，死锁检测机制会消耗大量的CPU资源。CPU利用率很高，但确执行不了几个事务】

    如何解决热点行更新带来的性能问题呢？

  7. 确保业务不会出现死锁，临时将死锁检测机制关闭。

  8. 控制并发度。【1 对相同行的更新在进入引擎前排队处理。】【2 将逻辑上的一行改成多行，账户总额度等于10条记录加在一起】

    6、事务到底是隔离还是不隔离的？

    ‘快照’在MVCC里怎么工作的？

    可重复读。事务在启动的时候就“拍个快照”，这个快照是基于整个库的。
    一个事务启动的时候，能看到所有已经提交的事务结果。InnoDB里每一个事务都有唯一的事务ID transaction id，它是事务开始时向InnoDB申请的，按申请顺序严格递增。它为每个事务都构造了数组，用来保存整个事务的启动瞬间当前正在活动（启动了，未提交）的所有事务ID。
    事务的可见性原则，就是基于数据行的transaction id和这个一致性视图对比结果得到的。

InnoDB利用了所有数据都有多个版本的这个特性，实现了‘秒级’创建快照的能力。
update更新数据都是先读后写的，而这个读，叫做当前读【必须读最新版本，如果碰到两阶段锁，就必须等待】。（select语句如果加锁，也是当前读）
InnoDB中要保证这个规则：事务启动之前所有还没提交的事务，它都不可见。但只保存已经提交事务的最大值是不够的，比最大值小的事务之后也可能更新。所以事务启动时还要保存-现在正在执行的所有事务ID列表，如果一个row trx_id在这个列表中，那也要不可见。

事务的可重复读能力怎么实现？

可重复读的核心就是一致性读。事务更新数据时，只能使用当前读。如果记录的行锁被占用，它必须进入锁等待。

表结构为什么不支持可重复读？

表结构没有对应的行数据，没有对应的row trx_id，只能遵循当前读的规则。

7、 普通索引和唯一索引的选择-更新性能的影响

考虑对更新性能的影响,要尽量选择普通索引。

查询过程+更新过程

查询过程：引擎是按照数据页来读写的。查找K=5的记录对于普通索引来说就是会多做一个‘查找和判断下一条记录’的操作。
更新过程：唯一索引没办法使用change buffer，它的更新操作需要判断唯一性约束。对于目标页不在内存中的情况，普通索引直接将记录更新在change buffer中，语句执行结束。

change buffer使用场景

在一个数据页做merage之前change buffer记录的变更越多，收益越高。访问数据会触发merage过程。
对于写多读少的场景（账单类、日志类），页面被写完之后马上被访问的概率较小。比较合适。

change buffer 的 merage过程

  1. 从磁盘读入数据到内存。
  2. 从change buffer里找到这个数据页的change buffer记录，依次运用，得到新版数据页。

  3. 写redo log，它包含了数据的变更和change buffer的变更。

    分析语句 insert into t(id,k) values (id1,k1),(id2,k2)

    假设K1所在数据页在内存（InnoDB buffer pool）中，K2所在数据页不在内存。

  4. Page1 在内存中，直接更新内存。

  5. Page2 没在内存中，直接在内存的change buffer区域记录下“插入一行”的信息。

  6. 将上述两个动作记入redo log中【顺序写】，事务执行结束。【宕机不会影响change buffer，它会记录在redo log】

    然后立马执行语句 select * from t where k in (k1,k2)

  7. 读Page1直接从内存返回。

  8. 读Page2，需要把Page2从磁盘读入内存，然后应用change buffer里面的操作日志，生成一个正确的版本并返回结果。（Merage操作）

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

8、 Mysql为什么会选错索引-优化器

选择索引是优化器的工作。扫描行数、是否使用临时表、是否排序等因素。

怎样得到索引的基数呢？-采样统计

采样统计：InnoDB默认选择N个数据页，统计这些页面上的不同值，得到平均值。乘以索引的页面数买得到索引的基数。
对于一个具体的语句，优化器还要判断语句本身要扫描多少行数。普通索引需要回表，他也会考虑。
对于explain结果预估的rows值跟实际情况差距比较大，可以通过analyze table t命令重新统计索引信息。

索引选择异常和处理

  1. 使用force index强行选择一个索引
  2. 修改Sql语句，引导Mysql使用我们期望的索引。（它认为使用索引可以避免排序，那我就多加一个排序条件，使得扫描行数成为主要条件）

  3. 新建一个更合适的索引，提供给优化器做选择。

    9、怎么给字符串字段加索引？

    为邮箱字段加索引-前缀索引

    Mysql支持前缀索引，占用的空间会更小。但它可能会增加额外的记录扫描次数。（所以我们要定义好前缀长度）
    可以依次截取不同长度的前缀来观察这一列有多少个不同的值。Select count（distinct left（email，4）） from xxx
    前缀索引对覆盖索引的影响 - 使用了就无法用覆盖索引了。

    业务需求对身份证等值查询（不支持范围查询）

  4. 使用倒序存储【reverse函数CPU消耗低】。身份证后6位重复概率低。reverse（”xxxxx”）

  5. 使用hash字段【crc32函数计算复杂度高，但它的查询效率高】。在表上再创建一个整数字段，保存身份证的校验码 crc32（）函数。当然你还需要再查询语句中加上原有的值。

    10、 为什么SQL语句突然变慢了

    有可能在刷脏页。（flush）

    WAL(Write Ahead Log)预写日志，它的关键点就是先写日志，再写磁盘，是数据库系统中常见的一种手段，用于保证数据操作的原子性和持久性。

利用WAL技术，数据库将随机写转化为了顺序写，但也带来了内存脏页问题。
内存页跟磁盘数据页内容不一致时，我们称这个内存页为“脏页”，内存数据写到磁盘后（flush），数据一致称为“干净页”。

什么时候触发flush？

  1. Redo log写满了，系统停止所有更新操作。将checkpoint往前推进，redo log留出来的空间可以继续写。
  2. （常态）系统内存不足，内存不够用时，需要淘汰一些数据页。淘汰的是脏页的话，需要将脏页写道磁盘。
  3. Mysql认为系统空闲。有机会就刷一点脏页。
  4. Mysql正常关闭时，会将内存中所有脏页flusg到磁盘。

InnoDB用缓存池（buffer pool）管理内存，内存页有3种状态

  1. 未被使用。
  2. 使用了且是干净页。
  3. 使用了且是脏页。（一个查询淘汰的脏页太多，导致查询时间边长。所以InnoDB有控制脏页比例机制）

InnoDB刷脏页的控制策略和避免Mysql查询太慢

  1. 脏页比例。
  2. Redo log写盘速度。

需要自己合理设置主机的IO能力 innodb_io_capacity 。（可以用fio工具测量。越低代表系统能力差，刷脏页速度就会很慢）
还要多关注脏页比例不要让它经常接近75%。
Innodb_flush_neighbors 1 能控制刷脏页时顺便刷入旁边的脏页，如果你的硬盘是SSD这个参数需要关掉 0。

如何让Mysql脏页刷慢一些？

写一个脚本，每隔0.5s发一个请求，告诉数据库我很忙。脏页就会刷慢一点。

11、为什么表数据删除一半，表文件大小不变？

参数innodb_file_per_table

  1. OFF表示表数据放在系统共享表空间内，和数据字典在一起。
  2. ON表示每个InnoDB表数据存储在一个以.ibd结尾的文件中。（默认）

    数据删除流程

    delete命令只是将记录的位置标记为‘可复用’，没被使用的空间像是‘空洞’。（插入不按索引排序的数据也会造成空洞，只需要插入递增的主键ID雪花算法就OK）

重建表 Alter table xxx engine=InnoDB

Mysql 5.6版本以后引入Online DDL(文件记录+重放)，在重建表时，允许了增删改操作。【但还是消耗CPU/IO的，对于大表推荐用gh-ost来做】

  1. 建立一个临时文件。扫描表A主键的所有数据页。
  2. 用数据页表A的记录生成B+树，存储到临时文件。
  3. 存储到临时文件的过程中，对A的所有操作记录在日志文件row log中。
  4. 临时文件后，将该日志文件操作应用到临时文件上。
  5. 用临时文件替换表A的数据文件。

    Online和inplace

    重建表的这个过程都是在InnoDB中完成的，对于server层来说，没有把数据挪动到临时表，所以是inplace的。
  • Analyze table t 不是重建表。只是对表的索引重新统计。加了MDL读锁。
  • Optimize table t 等于 recreate + analyze

    12、count（） 很慢怎么搞？

    count（）不带条件的实现方式

  1. MyISAM将表的总行数存在了磁盘上，count（）时会直接返回。
  2. InnoDB执行时需要一行一行从引擎取出，累计计数。（由于MVCC的存在，应该返回多少行也是不确定的。这个事务可见的行才用于计算）
  3. Show table status显示的行数时有误差的。
    从效率上来看 count(字段) < count(主键id) < count(1) == count() [Mysql优化器优化过这个语句]
    我们可以自己把count（*）的值放入Redis 或者 Mysql中。
  4. 在Redis中，不支持分布式事务，无法拿到一致性视图。
  5. 在Mysql中，解决了一致性视图的问题。（先插入操作记录，再更新计数表。更新计数表。涉及到了行锁的竞争，先插入再更新能最大程度减少事务之间的锁等待，提升并发度。）

13、补充问题

  1. Mysql怎么知道binlog是完整的？
  • Statement格式的binlog，最后会有COMMIT
  • Row格式的binlog，最后有一个XID event
    此外还有binlog-checksum参数，用于验证binlog内容的正确性，磁盘问题可以通过它发现。
  1. Redo log 和 bin log怎么关联？
    它们有一个共同字段XID，崩溃恢复的时候，会按顺序扫描redo log。
  • 有prepare+commit的redo log，直接提交。
  • 只有prepare，拿着XID去找binlog对应的事务。

  1. 处于prepare阶段的redo log加上完整的binlog，重启就恢复。为啥这么设计？
    数据与备份的一致性有关。binlog写完后数据库崩溃，从库会使用它。

  2. 为啥要两阶段提交？写完redo log直接写binlog多好？
    事务的持久性问题。binlog写入失败，redo log就没办法回滚了。两阶段提交给每个系统一个机会，都ok再提交。

  3. 为啥不只用binlog支持崩溃恢复？
    历史原因，InnoDB不是原生引擎。而且InnoDB使用了WAL技术，执行事务只需要写完内存和日志，事务就完成了。它的日志没有记录数据页的更改。

  4. 能只用redo log吗？

  • 归档。Redo log时循环写。历史日志无法保留。
  • Mysql系统依赖bin log。且很多异构系统依赖它。
  1. 数据写入后的磁盘落盘，是从redo log更新过来的还是从buffer pool更新过来的？
    Redo log并没有去记录数据页的完整数据。
  • 正常运行的实例，脏页刷盘只需要把内存中的数据页写盘，与redo log无关。
  • 崩溃恢复时，InnoDB会将数据页读到内存，让redo log去更新内存内容，然后变成脏页。
  1. Redo log buffer是什么？
    Redo log buffer是一块内存，存放redo log日志。是执行commit语句的时候才真正去写redo log文件。

Insert … on duplicate确保了事务内部执行，执行他以后，强行占住行锁，之后的select可以在行锁的保护下读取。
Update 一个不变的数据，Mysql也会去update它。该加锁加锁，该更新更新。

14、 Order by是如何工作的?
可以从explain的 Extra字段来看有没有Using filesort需不需要排序

内存足够：全字段排序
（只有city 索引）Select city, name, age from A where city = ‘x’ order by name
（Mysql会给每个线程分配一块内存用于排序）

  1. 初始化sort_buffer，确认放入3个字段。
  2. 从索引city找到第一个满足city=’x’的主键ID
  3. 从主键id索引取出整行，3个字段值放入sort_buffer
  4. 从索引city取出下一个记录的主键id。重复3/4，直到条件不满足。
  5. 对sort_buffer中的数据按字段name快速排序。【可能会借助外部排序（使用临时文件，归并排序）】

内存小：rowid排序（只放要排序的列+主键Id，多一次回表）
如果sort_buffer里面要放的字段太多，借助外部存储的临时文件就会很多，排序性能变差。
SET max_length_for_sort_data = 16; // 当行长度超过这个值，会换一个排序算法。

优化点
创建联合索引city+name ，这样查询过程不需要借助临时表/排序。
当然，覆盖索引又能减少回表。

15、如何正确的显示随机消息
内存临时表
Order by rand() limit 3 //随机排序取前三个。Mysql会选择rowid排序（回表可以直接访问内存得到数据）。
它会使用内存临时表，内存临时表排序的时候会使用rowid排序方法。

磁盘临时表
会使用优先队列算法。因为它只需要前3个值。多的话会使用归并排序。

随机排序算法

  1. 取得整个表的行数，并记为C。
  2. 取得 Y = floor(C*rand()) floor函数就是取整数部分。
  3. 用 limit Y,1 取得一行。

16、 SQL语句逻辑相同，性能却差异很大 - 索引字段的参数可以玩函数，它自己不行。

  1. 条件字段函数操作。对索引字段做函数操作，可能会破坏索引值的有序性，优化器会放弃走树搜索功能。
  2. 隐式类型转换。本质上就是对索引字段做函数操作。
  3. 隐式字符串编码。两个表的字符集不同（例如：utf8和utf8mb4），会在连接过程中要求被驱动表的索引字段加上函数操作，做表连接查询的时候就用不上关键字索引。（我们可以修改字段字符集 或者 修改SQL语句，加上CONVERT(1，xxx USING utf8)这种函数，主动转换编码）

17、只查一行的数据
查询长时间不返回
大概率表锁。Show processlist看看当前语句的状态。

  1. 等待MDL锁。状态为waiting for table metadata lock。说明有一个线程正在表上持有MDL写锁，select被堵住。我们可以通过查看sys.schema_table_lock_waits这张表，找到造成阻塞的process id，把这个连接用kill断开就好。
  2. 等flush。有一个flush tables命令被别的语句堵住了。
  3. 等行锁。你用了select xx from table where xx lock in share mode.加了读锁。此时有线程做了写锁，那就要等待。

查询慢
Start transaction with consistent snapshot；
// 如果此时另外一个事务，执行很多次的update table set c=c+1 where id = 1;
Select from table where id = 1; // 很慢。一致性读，需要从最后一个开始，一次执行undo log。
Select from table where id = 1 lock in share mode; // 快。当前读。直接读取最新的数据。

18、 幻读有什么问题？
Select * from table where d = 5 for update. // 当前读，加写锁。
幻读是指一个事务在前后两次查询同一个范围的时候，后一次的查询看到了前一次查询没有看到的行。

  1. 语义问题。
  2. 数据不一致。即使将所有记录都加锁，还是阻止不了新插入的记录。、

如何解决幻读？
Gap lock【只在可重复读级别生效】
行锁只能锁住行，新插入数据时更新记录之间的间隙。InnoDB引入了间隙锁（Gap lock）
跟间隙锁存在冲突关系的，仅仅是“向间隙中插入一个记录”这个操作。间隙锁之间不会冲突。
next-key lock（先间隙锁+后行锁，前开后闭。有间隙的地方就会有它）

逻辑备份时，mysqldump会将备份线程设置为可重复读。

19、为什么只改一行的语句，能锁很多？（锁是加在索引上的）
加锁规则如下：

  1. 原则1：加锁的基本单位是next-key lock。前开后闭区间。
  2. 原则2：查找过程中访问到的对象才会加锁。（只使用覆盖索引，主键索引就没锁啦）
  3. 优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化成行锁。
  4. 优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件，next-key lock退化为间隙锁。
  5. bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。
    加锁过程是发生在语句执行过程中的，通过树搜索的方式定位记录时，用的就是‘等值查询’。（例如索引树搜索<15的索引）

两条语句要加锁相同的资源，但加锁顺序相反，当这两条语句并发执行，有可能出现死锁。
Show engine innodb status可以看到死锁信息。

20、Mysql提高性能的办法
短链接风暴

  1. 先处理占着连接不工作的线程。（事务外空闲的）
  2. 减少连接过程的消耗。可以让数据库跳过权限验证这个环节。Skip-grant-table

慢查询性能问题

  1. 索引没有设计好。紧急创捷索引（支持onlie DDL），在备库上关闭binlog，再执行alter table加上索引。然后主备切换。重复上个步骤。
  2. SQL语句没有写好。改写SQL语句。通过query_rewrite功能。
  3. Mysql选错了索引。使用query_rewrite功能给这个语句加上force index。
    全量回归测试工具：pt-query-digest

QPS突增问题

  1. 业务方下线功能，数据库端剔除白名单。
  2. 将用户删除，断开现有连接。
  3. 查询重写功能，limit 1。（备选）

21、Mysql如何保证数据不丢失？
只要redo log和binlog保证持久化到磁盘，就能确保数据恢复。

binlog写入机制
事务执行过程中，先把日志写到线程对应的binlog cache（内存存不下，就会借助磁盘），事务提交时，再将binlog cache写到共用的binlog文件中。（一个事务的binlog不能被拆开，必须连续写）
Sync_binlog 参数可以控制刷盘时机。将其值设置为N，会在N个事务结束后才执行fsync刷盘。

redolog写入机制
事务执行过程中，生成的redo log需要先写到redo log buffer（线程共用）的。
InnoDB中有一个后台线程，每隔1s，都会把redo log buffer的日志调用write写到文件系统的page cache，然后调用fsync持久化到磁盘。
innodb_flush_log_at_trx_commit参数能控制redo log是留在redo log buffer(0) 直接持久化到硬盘（1） 只将数据放在page cache（2）

双1策略
Mysql的双1策略，就是Sync_binlog 和 innodb_flush_log_at_trx_commit 都设置成1。即：一个事务完整提交前，需要等待两次刷盘，一次是redo log（prepare 阶段），一次是binlog。

什么时候改成非双1
innodb_flush_log_at_trx_commit =2 【只将数据放在page cache】
Sync_binlog = 1000 【1000个事务结束后才执行fsync刷盘】

  1. 业务高峰期。主库设置为非双1。
  2. 备库延迟，让备库尽快赶上主库。
  3. 用备份恢复主库的副本，应用binlog的过程。
  4. 批量导入数据。

Mysql如何实现磁盘的高TPS？-组提交机制
日志逻辑序列号LSN：每次写入长度为length的redo log，LSN就会加上它。一次组提交中，组员(事务)越多，节约磁盘IOPS效果越好。

  • 只有单线程压测的话，只能一个事务对应一个持久化操作了。
  • 并发更新时，第一个事务写完redo log buffer，接下来的fsync越晚调用，组员就越多。

WAL机制的优势？

  1. redo log与binlog 都是顺序写。
  2. 组提交机制，降低磁盘的IOPS消耗。

Mysql提升IO性能的办法

  1. 设置binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_conunt参数。会增加“额外的故意等待”，增加了语句的响应时间。
  2. Sync_binlog设置为大于1的值。主机掉电时，会丢失binlog日志。
  3. innodb_flush_log_at_trx_commit设置为2。主机掉电时，会丢失数据。（设置成0的话，Mysql自己重启就会丢数据）

22、Mysql怎么保证主备一致
备库B与主库A之间维持了一个长连接。

  1. 备库B通过change master命令，设置主库A的IP、端口、用户名、密码等，从那个位置开始请求binlog。
  2. 备库B执行start slave内存，备库启动io_thread（与主库建立连接） 和sql_thread 两个线程。
  3. 主库A按照B发来的位置，从本地读取binlog发送给B。
  4. B拿到binlog后，写到本地文件，称为中转日志。
  5. sql_thread读取中转日志，解析日志中的命令并执行。

binlog三种格式对比

  1. Binlog_format = statement（可能导致主备不一致），记录的是SQL语句的原文。
  2. Binlog_format = row（占空间）【推荐】，记录了真实删除行的主键id，不会有主备删除不同数据的问题。
  3. Binlog_format =mixed，Mysql自己判断这条SQL语句是否会导致主备不一致，有的话就用row，否则就是statement。
    binlog格式为row时，方便恢复数据。

循环复制问题
Mysql在binlog中记录了这个命令第一次执行时，所在实例的server id。（A发现server id是自己的，就不会执行了）

23、Mysql怎么保证高可用？
主备延迟
同一个事务，在备库执行完成的时间和主库执行完成时间的差值。在备库执行 show slave status 可以查看备库延迟的时间s。
主备延迟最直接的表现就是，备库消费中转日志（relay log）的速度，比生产binlog的速度要慢。

主备延迟的来源

  1. 备库机器性能比主库差。一般要将备库设置为非双1模式。
  2. 备库的压力大。备库的查询耗费了大量CPU资源，影响到同步速度。一般一主多从或者将binlog输出到Hadoop系统查询。
  3. 大事务。主库上必须等待事务执行完成才会写入binlog。
  • 一次性删除大量数据。控制每个事务删除的数据，分批删除。
  • 大表DDL。计划内的DDL建议使用gh-ost方案。
  1. 备库的并行复制能力。

可靠性优先策略【建议】
可用性优先策略

数据库运维系统都有备库延迟监控，通过在备库执行show slave status，采集seconds behind master的值。
主备延迟变成标准的45度曲线？

  1. 大事务。（大表DDL，事务操作很多行）
  2. 备库起了一个长事务。

24、备库为什么会延迟好几个小时
备库执行日志的速度持续低于主库生成日志的速度。单线程复制能力全面低于多线程复制，对于更新压力大的主库，备库一直追不上它。

多线程复制机制-Mysql5.6版本后支持
Sql_thread（变成coordinator）现在不直接更新数据了，他只负责中转日志和分发事务。真正更新日志的变成了worker线程。
coordinator在分发时，满足以下要求

  1. 不能造成更新覆盖。更新同一行的两个事务，必须分在同一个worker中。
  2. 同一个事务不能被拆开，必须分在同一个worker中。

Mysql5.7版本的并行复制策略

  1. DATABASE 按库并行（不常用）：理论上你要创建不同的DB，把相同热度的表均匀分到不同的DB中。
  2. LOGICAL_CLOCK 思想如下
    思想：
  3. 同时处于prepare状态【已经通过了锁冲突检验】的事务，在备库执行时可以并行。
  4. 处于prepare状态的事务与处于commit状态的事务执行时可以并行。

Mysql5.7.22版本的并行复制策略

  1. COMMIT_ORDER 根据同时进入prepare和commit来判断是否可以并行。【更新同一行的事务不能同时进入commit状态】
  2. WRITESET 对于事务更新的每一行，计算这一行的hash值（由库名+表名+索引名+值），组成集合writeset（主库直接计算好了，修改了binlog内容）。两个事务没有操作相同的行，writeset就没交集，可并行。
  3. WRITESET_SESSION WRITESET多了一个约束，在主库同一个线程先后执行的两个事务，在备库执行时，也需要遵循。

主库是单线程模式写入数据，压力很大。从库追主库过程中应该设置位什么参数？-WRITESET
单线程模式，导致每个事务的commit_id不一致，COMMIT_ORDER从库只能单线程执行。

25、 主库出问题了，从库怎么办？
一主多从，主库从A切换到A’

基于位点的主备切换
节点B要设置成A’的从库，需要执行change master命令，设置位点的两个参数（主库对于的文件名和日志偏移量）。

  1. 等到新主库A’把中转日志（relay log）全部同步完成。
  2. A’执行show master status命令，得到最新的文件名和日志偏移量。
  3. 取出A的故障时刻T。
  4. mysqlbinlog工具解析A’的File，得到T时刻的位点。
    这里的问题是，T时刻A可能已经将binlog传给了A’和B（例如一条插入语句），导致从库B拿数据的时候又插一次，导致主键冲突。
    设置slave skip errors参数，设置跳过指定的错误。

Mysql5.6版本引入：GTID（全局事务ID）
一个事务提交时会生成这个唯一标识。由server_uuid和gno构成。
server_uuid：实例第一次启动时自动生成的，全局唯一的值。
gno：初始值1，每次提交事务都会分配给这个事务，并+1.
每个Mysql实例都会维护一个GTID集合，对应“这个实例执行过的所有事务”。

在基于GTID的主备关系中，系统认为只要建立主备关系，必须保证主库发给备库的日志是完整的。（A’来计算与B的事务差集，找到不存在的事务给B）

GTID和在线DDL
两个互为主备关系的库实例X和实例Y，当前主库X，打开了GTID模式。

  1. 在实例X上执行stop slave。
  2. 在实例Y上执行DDL语句。执行完成后查到DDL语句对应的GTID
  3. 在实例X上执行GITD NEXT等于Y的那个GITD 。（确保不会让实例X执行这条更新）
  4. 执行主备切换。照上述流程再执行一次。

26、读写分离的坑
在从库中有可能会读到系统的一个过期状态。不能接受过期读怎么办？

  1. 强制走主库
    必须要拿到最新结果的请求，直接强制走主库。
  2. sleep方案
    等一会再去查询从库的数据。
  3. 判断主备无延迟方案
    查询前先从从库判断同步是否完成。
  4. 配合semi-sync方案
  • 一主多从时，某些从库还是存在过期读。
  • 持续延迟的情况下，可能出现过度等待问题。
  1. 等主库位点方案
    等主库事务更新完，立马执行show master status拿到主库执行到的File和Position。从库执行select master_pos_wait（File，Position，1）返回值只要》=0，就从从库执行查询语句。
  2. 等GTID方案
    与等待主库位点方案类似，只不过Mysql支持在更新语句后直接返回这个事务的GTID。通过mysql session track get first

大表如何去做DDL？（Gh-ost方案去做，也很好）
在各个从库先 SET sql_log_bin = OFF，然后在从库做DDL。等所有从库做完后，主从切换，在原来的主库用这个方法做DDL。
从库如何执行DDL？
将从库上执行DDL产生的GTID，在主库利用生成一个空事务GTID的方式再主库生成出来。从库做完后，主库再做一样的事情。（有双master的主库的slave需要先停掉）

29、如何去判断数据库是不是出问题了
外部检测-Select 1判断-不行
Innodb_thread_concurrency表示InnoDB仅允许x个线程并行执行。【建议设置为64-128之间，锁等待不会占用这个线程】
同时执行的语句超过了Innodb_thread_concurrency，select 1还是能正常返回。

并发连接和并发查询
Show processlist看到的是并发连接。
“当前正在执行”的语句（Innodb_thread_concurrency），才是并发查询。并发查询高是CPU杀手。

查表判断-不行
在系统表里创建一个表，且只放一行数据，定期执行查询语句。
更新事务会写binlog，binlog所在磁盘100%的话，更新事务的commit语句会被堵塞。

更新判断（优先考虑）
去更新上一张表的时间戳，但备库检测也需要更新时间戳。双M结构下，可能出现行冲突。可以更新不同id的数据。（用server_id做主键）
这个update请求需要的IO资源很少，有可能执行成功后数据库就挂了，有随机性。

内部统计（配合检测这个）
Performance_schema库中，在file summary by event name表统计了每次IO请求的时间。只需要打开自己需要的项进行统计。
通过max_time的值来判断数据库是否出问题了。发现异常后再把之间的值清空，加入监控累积值。

  1. 锁是一个一个加的，要避免死锁，对同一组资源，要按照尽量相同的顺序访问。
  2. ‘for update’占用资源更多，回滚成本更大。InnoDB选择了回滚成本小的lock in share mode语句。

31、误删数据后怎么办

  1. Delete 语句删除行
    使用flashback工具闪回数据【在备库做】。（修改binlog内容，拿回原库重放，需要确保binlog_format=row和binlog_row_image=FULL）
  • 把sql_safe_updates参数设置为on。
  • 代码上线前，必须经过SQL审计。
  1. Drop table或者truncate table误删数据表/库
    要求线上有定期的全量备份，并且实时备份binlog。
  • 账号分离。只给开发同学DML权限。
  • 删除表只去改表名，后期观察无影响后再删除。
  1. rm命令误删整个Mysql实例
    备份跨机房，跨省市。

生产数据库操作的脚本：
备份脚本+执行脚本+验证脚本+回滚脚本
32、为什么有kill不掉的语句

  1. Kill query+线程id 终止这个线程正在执行的语句
  2. Kill connection+线程id 断开这个线程的连接

收到kill命令后，线程做什么？
发送kill命令的客户端，没有强行停止对线程的执行，而是设置了个状态，唤醒对应的线程。被kill的线程需要执行到判断状态的埋点，才会进入终止逻辑阶段。

  1. 把session的运行状态改成THD::kill_QUERY、
  2. 给session的执行线程发一个信号

kill不掉，显示Killed

  • InnoDB并发度问题。临时调大Innodb_thread_concurrency，停掉别的线程，让位给killed的线程执行。
  • 线程没有执行到判断线程状态的逻辑。（IO压力大，读写IO的函数一直无法返回，不能及时的判断线程的状态-》减少系统压力）
  • 终止逻辑耗时较长。（超大事务被kill / 大查询回滚 / DDL命令执行到最后阶段）

关于客户端的误解
库里面表很多，连接就会变得很慢
其实是客户端慢。（花时间在了构建本地表名哈希表）加上参数-A，可以跳过它，自动补全功能消失。
加上-quick参数会使服务端变慢，它不使用本地缓存，不耗费本地内存存储。

33、查这么多数据，会把数据库内存打爆吗
全表扫描对server层的影响-查询结果是分段返回的，不在server保存完整结果集。
InnoDB的数据保存在主键索引上，全表扫描实际上就是扫描表的主键。
Mysql是“边读边发”的，如果客户端接受的很慢，会导致Mysql服务端由于结果发不出去，事务执行时间变长。（show processlist 状态为sending to client）

  1. 对于正常的线上业务，如果一个查询的返回结果不是很多，建议使用mysql_store_result接口，直接把查询结果保存在本地内存。
  2. 大查询的话（show processlist 状态为sending to client），可以调大内存大小。或者用mysql_use_result，读一行处理一行
    客户端由于压力过大，迟迟不能接受事务，服务端有什么影响？
  3. 前面有语句在更新。别的语句更新会被锁。
  4. 磁盘空间受影响。Undo log不能被回收，回滚段空间膨胀。

全表扫描对InnoDB引擎内部的影响-有淘汰机制，LRU策略时优化过的
在WAL中，InnoDB内存可以保存更新的结果，配合redo log避免随机写盘。
同时Buffer Pool能加速查询，查询可以直接从内存拿数据（内存命中率）。
InnoDB内存管理使用最近最少使用算法。（LRU）淘汰最久未使用的数据。【InnoDB使用链表实现】

InnoDB LRU算法
它将LRU链表分成了youg区域（链表头部）和old区域（链表末尾）。

  1. 新插入链表的数据页都先放在old区域。（一个数据页有多条数据，顺序扫描时间不会超过1s，所以保留在old区域）
  2. 只有在old区域超过1s停留时间的数据页，再次访问才会把它放到young区域。
    这个策略使得InnoDB在遍历大表时，虽然也用到Buffer Pool，但对youg区域没有任何影响，保证了正常业务的查询命中率。

34、可不可以用join
能不能用join，要看explain结果集中，Extra字段里面有没有出现”Block Nested Loop”字段。
BKA-被驱动表能用索引

  1. 使用join语句，性能比强行拆成多个单表执行SQL的性能要好。
  2. 需要让小表做驱动表。(join前面的那个)

BNL-被驱动表无法用索引
Mysql会对两个表都做全表扫描，放入join_buffer中。（顺序读取表1的行，如果放不下，就停止。然后与表2进行join对比，返回结果集的一部分。最后清空join_buffer，再进行第一步。）【会扫描多次被驱动表】

  1. join_buffer_size 足够大，驱动表是谁都无所谓。
  2. join_buffer_size不够，需要让小表做驱动表。

Simple Nested Loop Join 性能问题
它会顺序遍历被驱动表的所有行，每一行数据都跟join_buffer中的数据进行匹配，匹配成功则作为结果集的一部分返回。

  • InnoDB 的Buffer Pool的youg区域被这个被驱动表占满。影响内存命中率。BNL性能比它好。

在决定哪个表做驱动表时，应该让每个表按自己的条件过滤，过滤完成后，计算参与join的各个字段的总数据量，数据量小的作为驱动表。

35、join语句怎么优化
BKA
把表1的数据取出来一部分，放入一个临时内存（join_buffer），然后再一起给表2进行匹配。

BNL
可能会对被驱动表多次扫描，占用IO资源。

BNL转BKA

  1. BKA优化Mysql内部支持，建议默认使用。
  2. BNL算法效率低，建议给被驱动表的关联字段加上索引，转成BKA算法。
  3. 如果加不了索引，使用临时表，过滤出小数据的join语句。
    多表join的话，尽量让每一次参与join的驱动表数据集小一些。（它是直接嵌套查询的，并不是先算两个表的join再去和第三个表join）

Left join的话，左表一定是驱动表吗？
执行过程有可能还不是从左到右，左边的表不一定是驱动表。

两个表join包含多个条件得到等值匹配，是都要写到on里面吗？还是说一个写在on里面，其它的写道where里？

  1. 需要left join的语义，必须把条件都放在on里面匹配。
  2. join语句，优化器都会把这两种写法改成 select xx from a join b where a.x = b.x,执行计划一模一样。

36、为什么临时表可以重名？
临时表的特性

  1. 建表语法create tempory table
  2. 一个临时表只能被它的session访问，对其它线程不可见。【很适合join优化场景】
  3. 临时表可以与普通表重名。
  4. Show tables不显示临时表。
  5. 临时表session结束后自己会删除。（连接池情况下，需要自己删除）

临时表的应用-分库分表的场景
分库分表系统有一个中间层proxy。分区key的选择-》减少跨库和跨表查询 为依据。proxy解析完SQL语句后，就能确定SQL路由到哪个分表做查询。
但是当查询字段里没有用到分区字段，只能去所有分区表去查找满足条件的行，然后统一做后续操作order by。

  1. 在proxy中做order by。（开发工作量大。对proxy压力大。）
  2. 各个分库拿到的数据放到一个Mysql的临时表里处理。

为什么临时表可以重名？
当create tempory table时，Mysql会给这个InnoDB表创建frm文件保存表结构的定义。这个frm文件放在临时文件目录下，文件名后缀是.frm，前缀是 #sql{进程id}{线程id}序列号 。可以使用 select @@tmpdir 命令显示实例文件目录。
内存中也会table_def_table在库名+表名基础上增加了server id和thread id。
session结束时，对链表中的每个临时表，都会执行DROP tempory table。

临时表和主备复制
只在 binlog_format = statement/mixed 时候，binlog才会记录临时表的操作。
Drop table可以一次删除多个表，在写入binlog时server需要对它进行转义。但selcet/update不需要。
Mysql在记录binlog时，还会把主库执行这个语句的线程id写道binlog中。

37、什么时候会使用内部临时表-using temporary
Union 执行 - 子查询结果的并集
内存临时表起到了暂存数据的作用，计算过程还会用到临时表主键id唯一性约束，实现union。（union all不需要临时表）

Group by
由于group by出来的数据是无序的，所以需要临时表来记录并统计结果。
无论是内存临时表或者是磁盘临时表，group by逻辑都需要构造一个带唯一索引的表，执行代价很高。

如果对排序没有要求，一定要在Group by后面加order by null
这样会跳过临时表的排序阶段，直接从临时表取数据返回。

Group by优化-索引
建立一个索引满足扫描过程保证出现的数据有序。
Mysql5.7以后支持了generated column机制，可以实现列数据的关联更新。

Group by优化-直接排序
如果一个Group by语句需要放到临时表的数据量特别大，它却还是按照“先在内存临时表放，放不下再去磁盘临时表”策略。
在Group by语句加入 SQL_BIG_RESULT 这个提示，就能告诉优化器直接用磁盘临时表。

什么时候会使用内部临时表？

  1. 执行过程能一边读数据，一边直接得到结果，就不需要额外内存。否则需要。
  2. Join_buffer是无序数组，sort_buffer是有序数组，临时表是二维表结构。
  3. 执行逻辑需要二维表特性，优先考虑临时表。例如：union需要唯一性约束，Group by需要另一个字段存储累计计数。

38、还要不要使用Memory引擎
内存表的数据组织结构
Memory引擎数据和索引分开的。索引只保存数据位置。（堆组织表）【hash索引】
内存表的数据行被删除后，空出的位置能被接下来插入的数据复用。
当然内存表也能选b tree索引，使它支持范围查询。

不建议生产使用

  1. 内存表的锁-》只支持表级锁
  2. 数据的持久性 -》数据丢失
  3. 内存临时表可以一用。（数据量可控，不耗费太多内存。join语句优化）

39、自增主键为什么不是连续的？
表结构定义存放在以.frm后缀的文件中，但它不会保存自增值。

  1. MyISAM引擎自增值保存在数据文件中。
  2. InnoDB引擎自增值保存在内存里，重启后使用max id+1。（8.0版本以后记录在redo log中）

自增值修改机制

  1. 插入数据时id指定为0，null，就把这个表当前的AUTO_INCREMENT值填到自增字段中。（每次递增AUTO_INCREMENT_OFFSET）
  2. 插入数据指定了值，直接用指定的值。

自增值的修改时机

  1. 唯一键冲突会导致自增主键id不连续。
  2. 事务回滚也会导致自增主键id不连续。
  3. 批量插入数据，Mysql会批量申请自增id，有可能申请多了，导致自增主键id不连续。
    InnoDB放弃了自增值回退功能，只保证自增id递增，但不保证连续。

自增锁的优化
Mysql5.1.22版本引入参数 innodb_autoinc_lock_mode，默认为1。

  1. 0 代表语句执行完才释放锁
  2. 1 代表普通insert语句申请后会立即释放，但类似insert…select这类语句需要等到语句执行完才释放锁。
  3. 2 代表 所有的申请自增锁动作都是被申请后会立即释放，允许别的事务申请。

为什么默认设置下，类似insert…select这类语句需要等到语句执行完才释放锁？-保证从库数据一致性
当你的binlog_format=statement时，不同的session生成的id在主库可能不连续，但在从库同一个session的id都是连续的，产生数据不一致。
在生产上，有insert…select这种批量插入数据的场景时，需要设置innodb_autoinc_lock_mode=2，binlog_format=row。【提升并发性，保持一致性】

40、insert语句的锁为什么这么多
insert…select 语句
在RR可重复读级别下，binlog_format=statement时，需要对表所有行和间隙加锁。（不加锁可能出现insert…select 语句先执行，但它后写入binlog了。导致主备不一致）【日志和数据的一致性】postgresql 没有这个锁，原理未知xxx。

insert into t(xx) (select XX from t order by c limit 1)语句

  1. 创建临时表，表里面有字段xx
  2. 按照索引c扫描表t，依次取值后回表，再读到值后插入到表t
  3. 因为limit 1存在，只会取临时表的第一行。
    它需要对t做全表扫描，并且给索引c所有的间隙都加上共享的next-key lock。
    一边遍历数据，一边更新数据的情况，如果读出的数据直接写回原表，就可能在遍历过程中督导刚插入的记录。
    我们可以使用用户临时表优化这个insert语句，内存临时表也可以。

Insert 语句出现唯一键冲突
会在冲突的唯一值上加共享的next-key lock（S锁）。要尽快提交或者回滚事务，避免加锁时间过长。

insert into… on duplicate key update
插入一行数据，如果碰到唯一性约束，就执行后面的update语句。（修改第一个索引冲突的行）

41、怎么最快的复制一张表
可以控制扫描行数和加锁范围很小的话，用insert…select 语句。

mysqldump方法
使用mysqldump将数据导出成一组insert语句，然后在另一个库执行source命令。

导出csv文件
Mysql能将查询结果以.csv导出到服务端本地目录。在另一个库执行load data命令。
mysqldump加上tab参数，能同时导出表结构定义文件和csv数据文件。

物理拷贝方法（不可跨引擎）
直接把表的frm文件和ibd文件拷贝到另一个库可以吗？-》不行。还需要在数据字典中注册。
Mysql5.6版本引入了可传输表空间，导出+导入表空间的方式，实现物理拷贝。

42、 grant之后要跟着flush privileges吗？
grant命令

  1. 磁盘上，将mysql.user表里，用户’ua’@’%’这一行所有表示权限的字段的值都修改为xx
  2. 内存里，从数组acl_users中找到这个用户对应的对象，将access值修改。
    如果内存的权限数据和磁盘数据相同的话，不需要执行flush privileges。【正常情况】

flush privileges使用场景
flush privileges语句用来重建内存数据，达到内存的权限数据和磁盘数据相同。（你用了DML语句操作了系统权限表）

43、要不要使用分区表？
分区表是什么

  • 对于引擎层来说，这是4个表。4个.ibd文件（每个分区对应一个ibd文件）
  • 对于Server层来说，这是1个表。1个.frm文件

分区表的引擎层行为
从引擎层看，分区表和手工分表，两种方式没有实质的差别。

分区表的Server层行为
从Server层来说，一个分区表就是一个表。即使你只需要操作一个分区，也要等整个表的MDL锁。

  1. Mysql在第一次打开分区表时，需要访问所有的分区。
  2. 在server层，认为这是同一张表，因此所有的分区共用同一个MDL锁。
  3. 引擎层，认为是不同的表，MDL锁之后的执行过程，只会访问必要的分区。
    分区表的应用场景-对业务透明
    业务按时间删除历史数据。按时间进行分区的分区表，可以通过alter table t drop partition删掉分区。
  4. 分区表不需要太细。
  5. 分区表不要提前预留太多。
  6. 分区表的规则需要预先设置好。

Mysql学习路径
了解每个参数的意义和实现原理。（会用-》发现问题）