干货 一文看尽 mysql

如何写出高性能SQL语句？

B+Tree 索引是通过B+ 树实现的，是有序排列存储，所以在排序和范围查找方面都比较有优势

平衡二叉树、B树、B+树、B*树 理解其中一种你就都明白了

慢 SQL 语句的几种常见诱因：

InnoDB 默认创建的主键索引是聚族索引（Clustered Index），其它索引都属于辅助索引
（Secondary Index），也被称为二级索引或非聚族索引.

回表操作

从辅助索引中查询得到记录，而不需要通过聚族索引查询获得，MySQL 中将其称为覆盖索
引。使用覆盖索引的好处很明显，我们不需要查询出包含整行记录的所有信息，因此可以减
少大量的 I/O 操作

通常在 InnoDB 中，除了查询部分字段可以使用覆盖索引来优化查询性能之外，统计数量
也会用到， SELECT COUNT(*) 时，如果不存在辅助索引，此时会
通过查询聚族索引来统计行数，如果此时正好存在一个辅助索引，则会通过查询辅助索引来
统计行数，减少 I/O 操作。

自增字段作主键优化查询 的 原因 ？？？？

InnoDB 创建主键索引默认为聚族索引，数据被存放在了 B+ 树的叶子节点
上。也就是说，同一个叶子节点内的各个数据是按主键顺序存放的，因此，每当有一条新的
数据插入时，数据库会根据主键将其插入到对应的叶子节点中。

如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不
需要移动已有的数据，当页面写满，就会自动开辟一个新页面。因为不需要重新移动数据，
因此这种插入数据的方法效率非常高。

如果我们使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据
时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其它数据来满足新数据的
插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为

页分裂

页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。
因此，在使用 InnoDB 存储引擎时，如果没有特别的业务需求，建议使用自增字段作为主
键

如果查询条件中使用 or，且 or 的前后条件中有一个列没有索引，那么涉及的索引都不会被 使用到。

在大多数情况下，我们习惯使用默认的 InnoDB 作为表存储引擎。在使用 InnoDB 作为存
储引擎时，创建的索引默认为 B+ 树数据结构，如果是主键索引，则属于聚族索引，非主键
索引则属于辅助索引。基于主键查询可以直接获取到行信息，而基于辅助索引作为查询条
件，则需要进行回表，然后再通过主键索引获取到数据。

如果只是查询一列或少部分列的信息，我们可以基于覆盖索引来避免回表。覆盖索引只需要
读取索引，且由于索引是顺序存储，对于范围或排序查询来说，可以极大地极少磁盘 I/O
操作。

所以mysql一般判断在查询超过整个表20%的数据时，就会考虑使用聚族索引来查找数据，这种方
式顺序读取数据的可能性要大于使用辅助索引的随机读。

在查询少量数据的情况下，使用辅助索引性能更加，而查询大量数据时，就未必了。
如果我们发现在查询一定量数据使用辅助索引要比主键索引快，而数据库又没有按照我们期望的
去使用辅助索引，则我们可以通过子查询或force index来强制使用辅助索引。

由于是select *操作，所以每条记录都需进行回表，当server层分析器发现between的范围
太大时，使用辅助索引存在大量回表操作，所以觉得得不偿失，故而直接使用主键索引。
如果想使用我们期望的索引，需要给server层分析器一个hint，force index(idx_order_id)

对索引进行函数操作或者表达式计算也会导致索引的失效

1. 无索引、索引失效导致慢查询

2. 锁等待

存储引擎有 InnoDB 和 MyISAM，前者支持行锁和表锁，后者只支持表锁。
如果数据库操作是基于表锁实现的，试想下，如果一张订单表在更新时，需要锁住整张表，
那么其它大量数据库操作（包括查询）都将处于等待状态，这将严重影响到系统的并发性
能。时，InnoDB 存储引擎支持的行锁更适合高并发场景。但在使用 InnoDB 存储引擎时，我
们要特别注意行锁升级为表锁的可能。在批量更新操作时，行锁就很可能会升级为表锁。
MySQL 认为如果对一张表使用大量行锁，会导致事务执行效率下降，从而可能造成其它事
务长时间锁等待和更多的锁冲突问题发生，致使性能严重下降，所以 MySQL 会将行锁升
级为表锁。还有，行锁是基于索引加的锁，如果我们在更新操作时，条件索引失效，那么行
锁也会升级为表锁。

因此，基于表锁的数据库操作，会导致 SQL 阻塞等待，从而影响执行速度。在一些更新操
作（insert\update\delete）大于或等于读操作的情况下，MySQL 不建议使用 MyISAM
存储引擎。
除了锁升级之外，行锁相对表锁来说，虽然粒度更细，并发能力提升了，但也带来了新的问
题，那就是死锁。因此，在使用行锁时，我们要注意避免死锁。

3. 不恰当的 SQL 语句

使用不恰当的 SQL 语句也是慢 SQL 最常见的诱因之一。例如，习惯使用 <SELECT >，
<SELECT COUNT()> SQL 语句，在大数据表中使用 <LIMIT M,N> 分页查询，以及对非
索引字段进行排序等等。

优化 SQL 语句的步骤

1. 通过 EXPLAIN 分析 SQL 执行计划

id：每个执行计划都有一个 id，如果是一个联合查询，这里还将有多个 id。

select_type：表示 SELECT 查询类型，常见的有 SIMPLE（普通查询，即没有联合查
询、子查询）、PRIMARY（主查询）、UNION（UNION 中后面的查询）、
SUBQUERY（子查询）等。
table：当前执行计划查询的表，如果给表起别名了，则显示别名信息。

partitions：访问的分区表信息。

type：表示从表中查询到行所执行的方式，查询方式是 SQL 优化中一个很重要的指标，
结果值从好到差依次是：system > const > eq_ref > ref > range > index > ALL。
system/const：表中只有一行数据匹配，此时根据索引查询一次就能找到对应的数据。
如果是 B + 树索引，我们知道此时索引构造成了多个层级的树，当查询的索引在树的底
层时，查询效率就越低。const 表示此时索引在第一层，只需访问一层便能得到数据

eq_ref：使用唯一索引扫描，常见于多表连接中使用主键和唯一索引作为关联条件。

ref：非唯一索引扫描，还可见于唯一索引最左原则匹配扫描。

range：索引范围扫描，比如，<，>，between 等操作。

ndex：索引全表扫描，此时遍历整个索引树。

ALL：表示全表扫描，需要遍历全表来找到对应的行。
possible_keys：可能使用到的索引。
key：实际使用到的索引。
key_len：当前使用的索引的长度。
ref：关联 id 等信息。
rows：查找到记录所扫描的行数。
filtered：查找到所需记录占总扫描记录数的比例。
Extra：额外的信息。

2. 通过 Show Profile 分析 SQL 执行性能

Profile 除了可以分析执行线程的状态和时间，还支持进一步选择 ALL、CPU、MEMORY、
BLOCK IO、CONTEXT SWITCHES 等类型来查询 SQL 语句在不同系统资源上所消耗的时
间。以下是相关命令的注释：
HOW PROFILE [type [, type] … ]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]
type 参数：
| ALL：显示所有开销信息
| BLOCK IO：阻塞的输入输出次数
| CONTEXT SWITCHES：上下文切换相关开销信息
| CPU：显示 CPU 的相关开销信息
| IPC：接收和发送消息的相关开销信息
| MEMORY ：显示内存相关的开销，目前无用
| PAGE FAULTS ：显示页面错误相关开销信息
| SOURCE ：列出相应操作对应的函数名及其在源码中的调用位置 (行数)
| SWAPS：显示 swap 交换次数的相关开销信息

值得注意的是，MySQL 是在 5.0.37 版本之后才支持 Show Profile 功能的，如果你不太确
定的话，可以通过 select @@have_profiling 查询是否支持该功能，如下图所示：

获取到 Query_ID 之后，我们再通过 Show Profile for Query ID 语句，就能够查看到对应
Query_ID 的 SQL 语句在执行过程中线程的每个状态所消耗的时间了：

常用的 SQL 优化

1. 优化分页查询

通常我们是使用 <LIMIT M,N> + 合适的 order by 来实现分页查询，这种实现方式在没有
任何索引条件支持的情况下，需要做大量的文件排序操作（file sort），性能将会非常得糟
糕。如果有对应的索引，通常刚开始的分页查询效率会比较理想，但越往后，分页查询的性
能就越差。

这是因为我们在使用 LIMIT 的时候，偏移量 M 在分页越靠后的时候，值就越大，数据库检
索的数据也就越多。例如 LIMIT 10000,10 这样的查询，数据库需要查询 10010 条记录，
最后返回 10 条记录。也就是说将会有 10000 条记录被查询出来没有被使用到。

利用子查询优化分页查询

以上分页查询的问题在于，我们查询获取的 10020 行数据结果都返回给我们了，我们能否
先查询出所需要的 20 行数据中的最小 ID 值，然后通过偏移量返回所需要的 20 行数据给
我们呢？我们可以通过索引覆盖扫描，使用子查询的方式来实现分页查询

2. 优化 SELECT COUNT(*)

MySQL高并发场景下的数据库事务调优

索引的失效与优化

B+Tree 索引是通过
B+ 树实现的，是有序排列存储，所以在排序和范围查找方面都比较有优势

为什么 SELECT 要加 for update 排他锁，而不是使用共享锁呢？

试想下，如果是两个订单号一样的请求同时进来，就有可能出现幻读。也就是说，一开始事务
A 中的查询没有该订单号，后来事务 B 新增了一个该订单号的记录，此时事务 A 再新增一
条该订单号记录，就会创建重复的订单记录。面对这种情况，我们可以使用锁间隙算法来防
止幻读

行锁的具体实现算法有三种：record lock、gap lock 以及 next-key lock。

record lock是专门对索引项加锁；
gap lock 是对索引项之间的间隙加锁；
next-key lock 则是前面两种的组合，对索引项以其之间的间隙加锁。

在 MySQL 中，gap lock 默认是开启的，即 innodb_locks_unsafe_for_binlog 参数值是
disable 的，且 MySQL 中默认的是 RR 事务隔离级别。

解决死锁的最佳方式当然就是预防死锁的发生了，我们平时编程中，可以通过以下一些常规 手段来预防死锁的发生：

1. 在编程中尽量按照固定的顺序来处理数据库记录，假设有两个更新操作，分别更新两条
   相同的记录，但更新顺序不一样，有可能导致死锁；
2. 在允许幻读和不可重复读的情况下，尽量使用 RC 事务隔离级别，可以避免 gap lock 导
   致的死锁问题；
3. 更新表时，尽量使用主键更新；
4. 避免长事务，尽量将长事务拆解，可以降低与其它事务发生冲突的概率；
5. 设置锁等待超时参数，我们可以通过 innodb_lock_wait_timeout 设置合理的等待超时
   阈值，特别是在一些高并发的业务中，我们可以尽量将该值设置得小一些，避免大量事务等
   待，占用系统资源，造成严重的性能开销。

当我们面对一张海量数据的表时，通常有分区、NoSQL 存储、分表分库等优化方案

分区

的底层虽然也是基于分表的原理实现的，即有多个底层表实现，但分区依然是在单库下
进行的，在一些需要提高并发的场景中的优化空间非常有限，且一个表最多只能支持 1024
个分区。面对日益增长的海量数据，优化存储能力有限。不过在一些非海量数据的大表中，
我们可以考虑使用分区来优化表性能

分区表是由多个相关的底层表实现的，这些底层表也是由句柄对象表示，所
以我们也可以直接访问各个分区，存储引擎管理分区的各个底层表和管理普
通表一样（所有的底层表都必须使用相同的存储引擎），分区表的索引只是
在各个底层表上各自加上一个相同的索引，从存储引擎的角度来看，底层表
和一个普通表没有任何不同，存储引擎也无须知道这是一个普通表，还是一
个分区表的一部分。

什么时候要分表分库

我们知道，如果在单表单库的情况下，当数据库表的数据量逐渐累积到一定的数量时
（5000W 行或 100G 以上），操作数据库的性能会出现明显下降，即使我们使用索引优化
或读写库分离，性能依然存在瓶颈。此时，如果每日数据增长量非常大，我们就应该考虑分
表，避免单表数据量过大，造成数据库操作性能下降。

如何分表分库？

通常，分表分库分为垂直切分和水平切分两种。
垂直分库是指根据业务来分库，不同的业务使用不同的数据库。例如，订单和消费券在抢购
业务中都存在着高并发，如果同时使用一个库，会占用一定的连接数，所以我们可以将数据
库分为订单库和促销活动库。

而垂直分表则是指根据一张表中的字段，将一张表划分为两张表，其规则就是将一些不经常
使用的字段拆分到另一张表中。例如，一张订单详情表有一百多个字段，显然这张表的字段
太多了，一方面不方便我们开发维护，另一方面还可能引起跨页问题。这时我们就可以拆分
该表字段，解决上述两个问题。

水平分表则是将表中的某一列作为切分的条件，按照某种规则（Range 或 Hash 取模）来
切分为更小的表。

水平分表只是在一个库中，如果存在连接数、I/O 读写以及网络吞吐等瓶颈，我们就需要考
虑将水平切换的表分布到不同机器的库中，这就是水平分库分表了。

结合以上垂直切分和水平切分，我们一般可以将数据库分为：单库单表 - 单库多表 - 多库
多表。在平时的业务开发中，我们应该优先考虑单库单表；如果数据量比较大，且热点数据
比较集中、历史数据很少访问，我们可以考虑表分区；如果访问热点数据分散，基本上所有
的数据都会访问到，我们可以考虑单库多表；如果并发量比较高、海量数据以及每日新增数
据量巨大，我们可以考虑多库多表.

可能会涉及到多表的分页查询、多表的 JOIN 查询，从而增加业务的复杂度。而一
旦分库了，除了跨库分页查询、跨库 JOIN 查询，还会存在跨库事务的问题。这些问题无疑
会增加我们系统开发的复杂度。

我们解决分布式事务有两种通用的方式：
两阶事务提交（2PC）以及补偿事务提交（TCC）。

2. 跨节点 JOIN 查询问题

用户在查询订单时，我们往往需要通过表连接获取到商品信息，而商品信息表可能在另外一
个库中，这就涉及到了跨库 JOIN 查询。

通常，我们会冗余表或冗余字段来优化跨库 JOIN 查询。对于一些基础表，例如商品信息
表，我们可以在每一个订单分库中复制一张基础表，避免跨库 JOIN 查询。而对于一两个字
段的查询，我们也可以将少量字段冗余在表中，从而避免 JOIN 查询，也就避免了跨库
JOIN 查询。

3. 跨节点分页查询问题

我们知道，当用户在订单列表中查询所有订单时，可以通过用户 ID 的 Hash 值来快速查询
到订单信息，而运营人员在后台对订单表进行查询时，则是通过订单付款时间来进行查询
的，这些数据都分布在不同的库以及表中，此时就存在一个跨节点分页查询的问题了。

通常一些中间件是通过在每个表中先查询出一定的数据，然后在缓存中排序后，获取到对应
的分页数据。这种方式在越往后面的查询，就越消耗性能。

通常我们建议使用两套数据来解决跨节点分页查询问题，一套是基于分库分表的用户单条或
多条查询数据，一套则是基于 Elasticsearch、Solr 存储的订单数据，主要用于运营人员根
据其它字段进行分页查询。为了不影响提交订单的业务性能，我们一般使用异步消息来实现
Elasticsearch、Solr 订单数据的新增和修改

4. 全局主键 ID 问题

在分库分表后，主键将无法使用自增长来实现了，在不同的表中我们需要统一全局主键
ID。因此，我们需要单独设计全局主键，避免不同表和库中的主键重复问题。

使用 UUID 实现全局 ID 是最方便快捷的方式，即随机生成一个 32 位 16 进制数字，这种
方式可以保证一个 UUID 的唯一性，水平扩展能力以及性能都比较高。但使用 UUID 最大
的缺陷就是，它是一个比较长的字符串，连续性差，如果作为主键使用，性能相对来说会比
较差。

我们也可以基于 Redis 分布式锁实现一个递增的主键 ID，这种方式可以保证主键是一个整
数且有一定的连续性，但分布式锁存在一定的性能消耗。

我们还可以基于 Twitter 开源的分布式 ID 生产算法——snowflake 解决全局主键 ID 问
题，snowflake 是通过分别截取时间、机器标识、顺序计数的位数组成一个 long 类型的主
键 ID。这种算法可以满足每秒上万个全局 ID 生成，不仅性能好，而且低延时.

5. 扩容问题

随着用户的订单量增加，根据用户 ID Hash 取模的分表中，数据量也在逐渐累积。此时，
我们需要考虑动态增加表，一旦动态增加表了，就会涉及到数据迁移问题。

我们在最开始设计表数据量时，尽量使用 2 的倍数来设置表数量。当我们需要扩容时，也
同样按照 2 的倍数来扩容，这种方式可以减少数据的迁移量。

你使用过哪些分库分表中间件呢？欢迎分享其中的实现原理以及优缺点

数据库参数设置优化

数据库主要是用来存取数据的，而存取数据涉及到了磁盘 I/O 的读写操作，所以数据库系统主要的性能瓶颈就是 I/O 读写的瓶颈了。

MySQL 数据库为了减少磁盘 I/O 的读写操作，应用了大量内存管理来优化数据库操作，包括内存优化查询、排序以及写入操作。

也许你会想，我们把内存设置得越大越好，数据刷新到磁盘越快越好，不就对了吗？其实不
然，内存设置过大，同样会带来新的问题。例如，InnoDB 中的数据和索引缓存，如果设置
过大，就会引发 SWAP 页交换。还有数据写入到磁盘也不是越快越好，我们期望的是在高
并发时，数据能均匀地写入到磁盘中，从而避免 I/O 性能瓶颈。

SWAP 页交换：SWAP 分区在系统的物理内存不够用的时候，就会把物理内
存中的一部分空间释放出来，以供当前运行的程序使用。被释放的空间可能
来自一些很长时间没有什么操作的程序，这些被释放的空间的数据被临时保
存到 SWAP 分区中，等到那些程序要运行时，再从 SWAP 分区中恢复保存
的数据到内存中

所以，这些参数的设置跟我们的应用服务特性以及服务器硬件有很大的关系。MySQL 是一
个高定制化的数据库，我们可以根据需求来调整参数，定制性能最优的数据库。

2. InnoDB 存储引擎参数设置调优

InnoDB Buffer Pool（简称 IBP）是 InnoDB 存储引擎的一个缓冲池，与 MyISAM 存储引
擎使用 key buffer 缓存不同，它不仅存储了表索引块，还存储了表数据。查询数据时，IBP
允许快速返回频繁访问的数据，而无需访问磁盘文件。InnoDB 表空间缓存越多，MySQL
访问物理磁盘的频率就越低，这表示查询响应时间更快，系统的整体性能也有所提高。

我们一般可以通过多个设置参数来调整 IBP，优化 InnoDB 表性能。

innodb_buffer_pool_size

IBP 默认的内存大小是 128M，我们可以通过参数 innodb_buffer_pool_size 来设置 IBP
的大小，IBP 设置得越大，InnoDB 表性能就越好。但是，将 IBP 大小设置得过大也不好，
可能会导致系统发生 SWAP 页交换。所以我们需要在 IBP 大小和其它系统服务所需内存大
小之间取得平衡。MySQL 推荐配置 IBP 的大小为服务器物理内存的 80%

我们也可以通过计算 InnoDB 缓冲池的命中率来调整 IBP 大小：

(1-innodb_buffer_pool_reads/innodb_buffer_pool_read_request)*100

但如果我们将 IBP 的大小设置为物理内存的 80% 以后，发现命中率还是很低，此时我们就
应该考虑扩充内存来增加 IBP 的大小。

innodb_buffer_pool_instances

InnoDB 中的 IBP 缓冲池被划分为了多个实例，对于具有数千兆字节的缓冲池的系统来说，
将缓冲池划分为单独的实例可以减少不同线程读取和写入缓存页面时的争用，从而提高系统
的并发性。该参数项仅在将 innodb_buffer_pool_size 设置为 1GB 或更大时才会生效。

而在其它操作系统中，如果 innodb_buffer_pool_size 大小超过 1GB，
innodb_buffer_pool_instances 值就默认为 8；否则，默认为 1

为了获取最佳效率，建议指定 innodb_buffer_pool_instances 的大小，并保证每个缓冲池
实例至少有 1GB 内存。通常，建议 innodb_buffer_pool_instances 的大小不超过
innodb_read_io_threads + innodb_write_io_threads 之和，建议实例和线程数量比例为
1:1。

innodb_read_io_threads / innodb_write_io_threads

在默认情况下，MySQL 后台线程包括了主线程、IO 线程、锁线程以及监控线程等，其中
读写线程属于 IO 线程，主要负责数据库的读取和写入操作，这些线程分别读取和写入
innodb_buffer_pool_instances 创建的各个内存页面。MySQL 支持配置多个读写线程，
即通过 innodb_read_io_threads 和 innodb_write_io_threads 设置读写线程数量。

读写线程数量值默认为 4，也就是总共有 8 个线程同时在后台运行。
innodb_read_io_threads 和 innodb_write_io_threads 设置的读写线程数量，与
innodb_buffer_pool_instances 的大小有关，两者的协同优化是提高系统性能的一个关键
因素。

在一些内存以及 CPU 内核超大型的数据库服务器上，我们可以在保证足够大的 IBP 内存的
前提下，通过以下公式，协同增加缓存实例数量以及读写线程

( innodb_read_io_threads + innodb_write_io_threads ) =
innodb_buffe_pool_instances

如果我们仅仅是将读写线程根据缓存实例数量对半来分，即读线程和写线程各为实例大小的
一半，肯定是不合理的。例如我们的应用服务读取数据库的数据多于写入数据库的数据，那
么增加写入线程反而没有优化效果。我们一般可以通过 MySQL 服务器保存的全局统计信
息，来确定系统的读取和写入比率。

如果读大于写，我们应该考虑将读线程的数量设置得大一些，写线程数量小一些；否则，反 之。

innodb_log_file_size

除了以上 InnoDB 缓存等因素之外，InnoDB 的日志缓存大小、日志文件大小以及日志文件
持久化到磁盘的策略都影响着 InnnoDB 的性能。 InnoDB 中有一个 redo log 文件，
InnoDB 用它来存储服务器处理的每个写请求的重做活动。执行的每个写入查询都会在日志
文件中获得重做条目，以便在发生崩溃时可以恢复更改。

当日志文件大小已经超过我们参数设置的日志文件大小时，InnoDB 会自动切换到另外一个
日志文件，由于重做日志是一个循环使用的环，在切换时，就需要将新的日志文件脏页的缓
存数据刷新到磁盘中（触发检查点）

理论上来说，innodb_log_file_size 设置得越大，缓冲池中需要的检查点刷新活动就越少，
从而节省磁盘 I/O。那是不是将这个日志文件设置得越大越好呢？如果日志文件设置得太
大，恢复时间就会变长，这样不便于 DBA 管理。在大多数情况下，我们将日志文件大小设
置为 1GB 就足够了。

innodb_log_buffer_size

这个参数决定了 InnoDB 重做日志缓冲池的大小，默认值为 8MB。如果高并发中存在大量
的事务，该值设置得太小，就会增加写入磁盘的 I/O 操作。我们可以通过增大该参数来减
少写入磁盘操作，从而提高并发时的事务性能.

innodb_flush_log_at_trx_commit

这个参数可以控制重做日志从缓存写入文件刷新到磁盘中的策略，默认值为 1

当设置该参数为 0 时，InnoDB 每秒种就会触发一次缓存日志写入到文件中并刷新到磁盘
的操作，这有可能在数据库崩溃后，丢失 1s 的数据。

当设置该参数为 1 时，则表示每次事务的 redo log 都会直接持久化到磁盘中，这样可以保
证 MySQL 异常重启之后数据不会丢失。

当设置该参数为 2 时，每次事务的 redo log 都会直接写入到文件中，再将文件刷新到磁
盘。

在一些对数据安全性要求比较高的场景中，显然该值需要设置为 1；而在一些可以容忍数据
库崩溃时丢失 1s 数据的场景中，我们可以将该值设置为 0 或 2，这样可以明显地减少日志
同步到磁盘的 I/O 操作。

InnoDB 的 IBP 的内存大小是有限的，你知道 InnoDB 是如何将热点数据留在 内存中，淘汰非热点数据的吗？

回答：通过在内存中维护一个链表，并使用LRU(最近最少使用)算法淘汰非热点数据
回复: 对的，MySQL基于LRU算法来实现淘汰非热点数据，但与我们熟悉的LRU算法不同的
是，MySQL新增了一个midpoint insertion startegy策略，就是默认情况下，读取到的新页并不
是直接放入的LRU列表的首部，而是LRU列表长度的5/8处，目的是为了避免由于一些不常查询SQ
L偶尔一次查询就把之前热点数据淘汰的情况。
回答：mysql的缓冲池（buffer_pool）使用改进版的lru算法来缓存数据，如果使用传统的lru算
法，会存在预读失效和缓冲池污染的问题，为了解决预读失效问题，mysql将lru分为新生
代和老生代，为了解决缓冲池污染的问题，引入老生代停留时间窗口，只有大于设置的
值，才能加入新生代头部

回复: redo log是根据innodb_flush_log_at_trx_commit参数来设置刷新到磁盘的策略

回复: 是的，MySQL8.0版本已经不支持查询缓存，我们文中也提到了其中的弊端。MySQL官
方建议使用服务器端使用缓存或ProxySQL作为中间缓存。

InnoDB 主要包括了内存池、后台线程以及存储文件。

内存池

又是由多个内存块组成的，主要包括缓存磁盘数据、redo log 缓冲等；
客户端读取数据时，如果数据存在于缓冲池中，客户端就会直接读取缓冲池中的数据，否则
再去磁盘中读取；对于数据库中的修改数据，首先是修改在缓冲池中的数据，然后再通过
Master Thread 线程刷新到磁盘上。

理论上来说，缓冲池的内存越大越好。缓冲池中不仅缓存索引页和数据页，还包括了 undo 页，插入缓存、自适应哈希索引以及InnoDB 的锁信息等等。InnoDB 允许多个缓冲池实例，从而减少数据库内部资源的竞争，增强数据库的并发处理能力。

InnoDB 存储引擎会先将重做日志信息放入到缓冲区中，然后再刷新到重做日志文件中。

后台线程

则包括了 Master Thread、IO Thread以及 Purge Thread 等；
Master Thread 主要负责将缓冲池中的数据异步刷新到磁盘中，除此之外还包括插入缓
存、undo 页的回收等，IO Thread 是负责读写 IO 的线程，而 Purge Thread 主要用于回
收事务已经提交了的 undo log，Pager Cleaner Thread 是新引入的一个用于协助 Master
Thread 刷新脏页到磁盘的线程，它可以减轻 Master Thread 的工作压力，减少阻塞。

存储结构文件

由 InnoDB 存储引擎实现的表的存储结构文件一般包括表结构文件（.frm）、共享表空间文件（ibdata1）、独占表空间文件（ibd）以及日志文件（redo文件等）等。

在 MySQL 中建立一张表都会生成一个.frm 文件，该文件是用来保存每个表的元数据信息
的，主要包含表结构定义。

在 InnoDB 中，存储数据都是按表空间进行存放的，默认为共享表空间，存储的文件即为
共享表空间文件（ibdata1）。若设置了参数 innodb_file_per_table 为 1，则会将存储的
数据、索引等信息单独存储在一个独占表空间，因此也会产生一个独占表空间文件
（ibd）。如果你对共享表空间和独占表空间的理解还不够透彻，接下来我会详解。

而日志文件则主要是重做日志文件，主要记录事务产生的重做日志，保证事务的一致性。

InnoDB 逻辑存储结构

InnoDB 逻辑存储结构分为表空间（Tablespace）、段 (Segment)、区 (Extent)、页
Page) 以及行 (row)。

1. 表空间（Tablespace）
   InnoDB 提供了两种表空间存储数据的方式，一种是共享表空间，一种是独占表空间。
   InnoDB 默认会将其所有的表数据存储在一个共享表空间中，即 ibdata1。
   我们可以通过设置 innodb_file_per_table 参数为 1（1 代表独占方式）开启独占表空间模
   式。开启之后，每个表都有自己独立的表空间物理文件，所有的数据以及索引都会存储在该
   文件中，这样方便备份以及恢复数据

2. 段 (Segment)
   表空间是由各个段组成的，段一般分为数据段、索引段和回滚段等。我们知道，InnoDB 默
   认是基于 B + 树实现的数据存储。
   这里的索引段则是指的 B + 树的非叶子节点，而数据段则是 B + 树的叶子节点。而回滚段
   则指的是回滚数据，之前我们在讲事务隔离的时候就介绍到了 MVCC 利用了回滚段实现了
   多版本查询数据

3. 区 (Extent) / 页（Page）
   区是表空间的单元结构，每个区的大小为 1MB。而页是组成区的最小单元，页也是
   InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续
   性，InnoDB 存储引擎每次从磁盘申请 4-5 个区

4. 行（Row）
   InnoDB 存储引擎是面向列的（row-oriented)，也就是说数据是按行进行存放的，每个页
   存放的行记录也是有硬性定义的，最多允许存放 16KB/2-200 行，即 7992 行记录

InnoDB 事务之 redo log 工作原理

InnoDB 是一个事务性的存储引擎，而 InnoDB 的事务实现是基于事务日志 redo log 和
undo log 实现的。
redo log 是重做日志，提供再写入操作，实现事务的持久性；
undo log 是回滚日志，提供回滚操作，保证事务的一致性。

redo log 又包括了内存中的日志缓冲（redo log buffer）以及保存在磁盘的重做日志文件
（redo log file），前者存储在内存中，容易丢失，后者持久化在磁盘中，不会丢失。

InnoDB 的更新操作采用的是

Write Ahead Log 策略

，即先写日志，再写入磁盘。当一条
记录更新时，InnoDB 会先把记录写入到 redo log buffer 中，并更新内存数据。我们可以
通过参数 innodb_flush_log_at_trx_commit 自定义 commit 时，如何将 redo log buffer
中的日志刷新到 redo log file 中。

在这里，我们需要注意的是 InnoDB 的 redo log 的大小是固定的，分别有多个日志文件采
用循环方式组成一个循环闭环，当写到结尾时，会回到开头循环写日志。我们可以通过参数
innodb_log_files_in_group 和 innodb_log_file_size 配置日志文件数量和每个日志文件的
大小。

Buffer Pool 中更新的数据未刷新到磁盘中，该内存页我们称之为脏页。最终脏页的数据会
刷新到磁盘中，将磁盘中的数据覆盖，这个过程与 redo log 不一定有关系。

只有当 redo log 日志满了的情况下，才会主动触发脏页刷新到磁盘，而脏页不仅只有
redo log 日志满了的情况才会刷新到磁盘，以下几种情况同样会触发脏页的刷新：

系统内存不足时，需要将一部分数据页淘汰掉，如果淘汰的是脏页，需要先将脏页同步到
磁盘；
MySQL 认为空闲的时间，这种情况没有性能问题；
MySQL 正常关闭之前，会把所有的脏页刷入到磁盘，这种情况也没有性能问题

在生产环境中，如果我们开启了慢 SQL 监控，你会发现偶尔会出现一些用时稍长的 SQL。
这是因为脏页在刷新到磁盘时可能会给数据库带来性能开销，导致数据库操作抖动。

LRU 淘汰策略

InnoDB 存储引擎是基于集合索引实现的数据存储，也就是除了索引列以及主键是存储在 B

• 树之外，其它列数据也存储在 B + 树的叶子节点中。而这里的索引页和数据页都会缓存
  在缓冲池中，在查询数据时，只要在缓冲池中存在该数据，InnoDB 就不用每次都去磁盘中
  读取页，从而提高数据库的查询性能。

虽然缓冲池是一个很大的内存区域，但由于存放了各种类型的数据，加上存储数据量之大，
缓冲池无法将所有的数据都存储在其中。因此，缓冲池需要通过 LRU 算法将最近且经常查
询的数据缓存在其中，而不常查询的数据就淘汰出去。

InnoDB 对 LRU 做了一些优化，我们熟悉的 LRU 算法通常是将最近查询的数据放到 LRU
列表的首部，而 InnoDB 则是将数据放在一个 midpoint 位置，通常这个 midpoint 为列
表长度的 5/8。

这种策略主要是为了避免一些不常查询的操作突然将热点数据淘汰出去，而热点数据被再次
查询时，需要再次从磁盘中获取，从而影响数据库的查询性能。

如果我们的热点数据比较多，我们可以通过调整 midpoint 值来增加热点数据的存储量，从
而降低热点数据的淘汰率。

密集索引和稀疏索引的区别

MyISAM–》 主键索引，唯一键索引，还是普通索引 —都是 稀疏索引

InnoDB聚簇表分布

myisam在磁盘存储上有三个文件，每个文件名以表名开头，扩展名指出文件类型。
.frm 用于存储表的定义
.MYD 用于存放数据
.MYI 用于存放表索引

可以看到-----》 Innodb索引与数据放在一起