一、存储引擎

常用的存储引擎有以下：

Innodb

引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。

MyIASM

原本Mysql的默认引擎，不提供事务的支持，也不支持行级锁和外键。
MEMORY引擎：所有的数据都在内存中，数据的处理速度快，但是安全性不高。

MEMORY引擎

所有的数据都在内存中，数据的处理速度快，但是安全性不高。

MyISAM与InnoDB区别

	MyISAM	Innodb
存储结构	每张表被存放在三个文件：frm-表格定义、MYD(MYData)-数据文件、MYI(MYIndex)-索引文件	所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB
存储空间	MyISAM可被压缩，存储空间较小	InnoDB的表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引
可移植性、备份及恢复	由于MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作	免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了
文件格式	数据和索引是分别存储的，数据 `.MYD`，索引 `.MYI`	数据和索引是集中存储的， `.ibd`
记录存储顺序	按记录插入顺序保存	按主键大小有序插入
外键	不支持	支持
事务	不支持	支持
锁支持（锁是避免资源争用的一个机制，MySQL锁对用户几乎是透明的）	表级锁定	行级锁定、表级锁定，锁定力度小并发能力高
SELECT	MyISAM更优
INSERT、UPDATE、DELETE		InnoDB更优
select count(*)	myisam更快，因为myisam内部维护了一个计数器，可以直接调取。
索引的实现方式	B+树索引，myisam 是堆表	B+树索引，Innodb 是索引组织表
哈希索引	不支持	支持
全文索引	支持	不支持

	MyISAM	Innodb
存储结构	每张表被存放在三个文件：frm-表格定义、MYD(MYData)-数据文件、MYI(MYIndex)-索引文件	所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB
存储空间	MyISAM可被压缩，存储空间较小	InnoDB的表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引
可移植性、备份及恢复	由于MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作	免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了
文件格式	数据和索引是分别存储的，数据 `.MYD`，索引 `.MYI`	数据和索引是集中存储的， `.ibd`
记录存储顺序	按记录插入顺序保存	按主键大小有序插入
外键	不支持	支持
事务	不支持	支持
锁支持（锁是避免资源争用的一个机制，MySQL锁对用户几乎是透明的）	表级锁定	行级锁定、表级锁定，锁定力度小并发能力高
SELECT	MyISAM更优
INSERT、UPDATE、DELETE		InnoDB更优
select count(*)	myisam更快，因为myisam内部维护了一个计数器，可以直接调取。
索引的实现方式	B+树索引，myisam 是堆表	B+树索引，Innodb 是索引组织表
哈希索引	不支持	支持
全文索引	支持	不支持

二、索引

定义

一种帮助mysql提高查询效率的数据结构

优点

大大加快数据查询速度

缺点

维护索引需要耗费数据库资源
索引需要占用磁盘空间磁盘
当对表的数据进行增删改的时候，因为要维护索引，速度会受到影响

分类

主键索引

设定为主键后数据库会自动建立索引，innodb为聚族索引

优点：

保证数据的唯一性。

加速数据检索操作。
在连接操作中提高性能。

--建表 主键自动创建主键索引
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    email VARCHAR(50)
);
-- 修改表结构创建主键索引
ALTER TABLE users ADD PRIMARY KEY (id);
--查看索引
show index from t_user;

唯一索引

定义：唯一索引（Unique Index）是一种索引类型，用于确保表中的某一列或一组列的数值是唯一的，即不允许重复值。唯一索引在数据库表中起着重要作用，它可以确保数据的唯一性，避免数据重复，并提高数据的完整性。

优点：

确保数据的唯一性。
避免数据重复。
提高数据完整性。

CREATE TABLE users (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    UNIQUE (first_name, last_name)
);
ALTER TABLE users ADD UNIQUE (username);

单列索引

定义：只包含单个列的索引

CREATE TABLE users (
    id INT,
    username VARCHAR(50),
    INDEX idx_username (username)
);
ALTER TABLE users ADD INDEX idx_username (username);

复合索引(多列索引)

定义：包含多个列的索引

CREATE TABLE users (
    id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    INDEX idx_name (first_name, last_name)
);
ALTER TABLE users ADD INDEX idx_name (first_name, last_name);

最左匹配原则：最左匹配原则要求在复合索引查询中，查询条件必须从索引的最左列开始连续匹配，以确保索引的有效使用。

mysql 引擎在查询为了更了更好利用索引在查询过程中会动态调整查询字段顺序以便利用索引。

注意事项：

多列索引可以提高涉及这些列的查询效率，特别是在涉及这些列的联合查询或连接操作中。

在选择要创建多列索引的列时，通常选择经常一起使用的列或需要在联合查询中使用的列。

要注意多列索引的顺序，索引的顺序会影响查询的效率，应根据查询的情况选择合适的索引顺序。

全文索引

全文索引是一种特殊类型的索引，用于在文本数据上执行全文搜索。它允许你快速搜索表中的文本列，而不是仅仅匹配特定的值。全文索引在处理大量文本数据时非常有用，可以提高搜索性能和准确性。

CREATE TABLE articles (
    id INT,
    title VARCHAR(100),
    content TEXT,
    FULLTEXT idx_content (content)
) ENGINE=InnoDB;

ALTER TABLE articles ADD FULLTEXT idx_content (content) WITH PARSER ngram;

SELECT * FROM articles WHERE MATCH(content) AGAINST ('search keyword');

注意事项：

全文索引通常用于对文本数据进行搜索，适用于大段文本内容的搜索场景。

全文索引不支持对所有数据类型的列进行索引，通常用于 CHAR、VARCHAR、TEXT 等文本类型的列。

全文索引的性能取决于数据量和查询条件，需要根据具体情况进行优化和调整。

索引数据结构

定义

B+树（B+Tree）是一种常用的数据结构，通常用于数据库和文件系统中，用于实现索引结构。B+树是一种平衡多路查找树。

具有以下特点：

平衡性：B+树是一种平衡树，保持所有叶子节点具有相同的深度，从根节点到叶子节点的路径长度相同，确保高效的查找和插入操作。
多路查找：B+树每个节点可以存储多个键值对，通常称为阶（order），使得每个节点可以存储更多的数据，减少树的深度，提高查询效率。
顺序访问：B+树的所有叶子节点形成一个有序链表，便于范围查询和顺序访问，适合数据库中范围查询的场景。
非叶子节点：B+树的非叶子节点只存储键值信息，不存储具体数据，数据只存储在叶子节点上，提高了内部节点的利用率。
高效性能：B+树适用于磁盘存储和内存存储，对于数据库系统和文件系统等需要频繁读写的场景，B+树能够提供高效的插入、删除和查找操作。

为什么其他数据结构不可以

数据库存储数据结构要满足一下要求：

高效的插入、删除
支持范围查询
支持有序访问

数组

不适合频繁插入和删除操作：数组在插入和删除操作时需要移动大量元素，对于频繁的插入和删除操作效率较低。
无法支持范围查询：数组无法有效支持范围查询，需要遍历整个数组来进行范围查询，效率较低。

链表

查找效率低：链表查找元素的效率较低，需要从头开始逐个遍历，不适合高效的查找操作。
不支持范围查询：链表无法有效支持范围查询，需要遍历整个链表来进行范围查询。

二叉树（Binary Tree）

平衡性差：普通的二叉树可能因为数据的插入顺序不同而导致树的不平衡，影响查询效率。
不支持范围查询：普通的二叉树无法有效支持范围查询，需要进行全局遍历。

平衡二叉树（Balanced Binary Tree）

插入和删除开销大：虽然平衡二叉树解决了二叉树不平衡的问题，但在频繁插入和删除操作时，仍需要进行平衡调整，开销较大。
不支持范围查询：平衡二叉树也无法有效支持范围查询。

哈希表（Hash Table）

不支持范围查询：哈希表是根据键值进行快速查找的数据结构，不支持范围查询操作。
碰撞问题：哈希表可能存在碰撞（多个键映射到同一个哈希桶）问题，需要解决冲突，影响查询效率。

B Tree

定义：B Tree是一种自平衡的树数据结构

特点：

平衡性：B树是一种平衡树，保持所有叶子节点具有相同的深度，从根节点到叶子节点的路径长度相同，确保高效的查找和插入操作。
多路查找：B树每个节点可以存储多个键值对，通常称为阶（order），使得每个节点可以存储更多的数据，减少树的深度，提高查询效率。
节点存储：B树的节点中既存储数据也存储索引，使得每个节点可以容纳更多的键值对，减少磁盘 I/O 次数，提高查询效率。
范围查询：B树适合范围查询，由于B树是一种有序树，可以快速定位到范围的起始点，然后沿着叶子节点链表进行范围查询。
磁盘存储：B树适合在磁盘上存储，对于数据库系统和文件系统等需要频繁读写的场景，B树能够提供高效的插入、删除和查找操作。

缺点：范围查询效率比B+Tree低

B+Tree

定义：B+Tree是B Tree升级版，优化了范围查询效率。

特点：

具有B数的所有特点
只有叶子节点储存数据，叶子节点组成有序链表，更好的支持范围查询
其他节点只存储分路信息，可以存储更多数据，减少磁盘 I/O 次数

B+Tree页目录默认大小为16KB

聚簇索引和非聚簇索引

聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一且非空的索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键（类似oracle中的RowId）来作为聚簇索引。如果已经设置了主键为聚簇索引又希望再单独设置聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设置主键即可。

MyISAM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

对比

聚簇索引

数据存储方式：

数据行按照索引的顺序存储在磁盘上，即索引的叶子节点存储了实际的数据行。
表中的数据行按照聚簇索引的顺序进行物理排序，相邻的数据行存储在一起。

优势：

查询效率高：由于数据行与索引存储在一起，可以减少磁盘 I/O 操作，提高查询效率。
覆盖索引：如果查询的列正好是聚簇索引的一部分，可以直接从索引中获取数据，无需额外查找，提高查询性能。
访问同一数据页不同行记录时，已经把页加载到了Buffer中（缓存器），再次访问时，会在内存中完成访问，不必访问磁盘。
辅助索引的叶子节点，存储主键值，而不是数据的存放地址。好处是当行数据放生变化时，索引树的节点也需要分裂变化；或者是我们需要查找的数据，在上一次IO读写的缓存中没有，需要发生一次新的IO操作时，可以避免对辅助索引的维护工作，只需要维护聚簇索引树就好了
辅助索引存放的是主键值，减少了辅助索引占用的存储空间大小。

劣势：

更新代价高：插入、更新和删除操作可能导致数据移动和索引重构，开销较大。
数据碎片：频繁的插入和删除操作可能导致数据碎片化，影响性能。

非聚簇索引

数据存储方式：

索引的叶子节点存储了指向实际数据行的指针或引用，而不是实际的数据行。
表中的数据行在磁盘上存储的顺序与索引无关。

优势：

更新代价低：插入、更新和删除操作不会导致数据移动和索引重构的开销。
数据不会碎片化：由于数据行的物理存储顺序与索引无关，不会因为操作导致数据碎片化。

劣势：

查询效率相对较低：查询时需要先通过索引查找到数据行的位置，然后再根据指针或引用获取数据，可能需要多次磁盘访问。
覆盖索引效率低：无法直接从索引中获取所有需要的数据，可能需要额外的查找操作。

选择依据

读写比例：如果读操作远远多于写操作，聚簇索引可能更适合；反之，非聚簇索引可能更适合。
查询需求：如果经常进行范围查询或覆盖索引的查询，聚簇索引可能更合适；如果需要支持快速的插入和更新操作，非聚簇索引可能更合适。
数据表大小：对于小表，非聚簇索引可能更适合；对于大表，聚簇索引可能更有优势。

聚簇索引需要注意什么?

当使用主键为聚簇索引时，主键最好不要使用uuid，因为uuid的值太过离散，不适合排序且可能出线新增加记录的uuid，会插入在索引树中间的位置，导致索引树调整复杂度变大，消耗更多的时间和资源。
建议使用int类型的自增，方便排序并且默认会在索引树的末尾增加主键值，对索引树的结构影响最小。而且，主键值占用的存储空间越大，辅助索引中保存的主键值也会跟着变大，占用存储空间，也会影响到IO操作读取到的数据量。

为什么主键通常建议使用自增id

聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，那么可以想象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高

导致索引失效的情况

like模糊匹配以通配符开头导致索引失效

1	select * from class where className like '%abc';

使用函数导致索引失效

1	select * from class where left(name,1) = '张';

计算导致索引失效

1	select * from class where id + 1 = 666;

类型转换导致索引失效

1	select * from class where convert(id,char) = '666';

复合索引未遵守最左前缀原则

比如student表，新建一个复合索引 alter table student add index idx_id_classId_name(id,classId,name);

1	select * from student where classId =1 and id = 10001;

不等于(!= 或者<>)索引失效

1	select * from class where className != "Java";

索引字段使用is not null导致失效

1	select * from class where className is null;

OR 前后存在非索引的列，索引失效

1	select * from class where id =1 or address = 'shenzheng';

关联查询时两张表字符编码不一致

范围查询太广

1	SELECT * FROM emp WHERE emp.deptId > 1000;

三、日志

redo log

背景

首先明确一下InnoDB修改数据的基本流程。当我们想要修改DB上某一行数据的时候，InnoDB是把数据从磁盘读取到内存的缓冲池上进行修改。这个时候数据在内存中被修改，与磁盘中相比就存在了差异，我们称这种有差异的数据为脏页。InnoDB对脏页的处理不是每次生成脏页就将脏页刷新回磁盘，因为这样会产生海量的I/O操作，严重影响InnoDB的处理性能。既然脏页与磁盘中的数据存在差异，那么如果在此期间DB出现故障就会造成数据丢失。为了解决这个问题，redo log就应运而生了。

结构

redo log本身由两部分所构成，即重做日志缓冲（redo log buffer）和重做日志文件（redo log file）。这样的设计同样也是为了调和内存与磁盘的速度差异。InnoDB写入磁盘的策略可以通过innodb_flush_log_at_trx_commit这个参数来控制。

内容

物理格式的日志，记录的是物理数据页面的修改的信息，其 redo log 是顺序写入 redo log file 的物理文件中去的。

数据宕机恢复过程

DB宕机后重启，InnoDB会首先去查看数据页中LSN的数值，即数据页被刷新回磁盘的LSN（LSN实际上就是InnoDB使用的一个版本标记的计数）的大小，然后去查看redo log的LSN大小。如果数据页中的LSN值大，就说明数据页领先于redo log刷新回磁盘，不需要进行恢复；反之，需要从redo log中恢复数据。

日志文件切换

当一个日志文件写满后，InnoDB会自动切换到另一个日志文件，但切换时会触发数据库检查点checkpoint（checkpoint所做的事就是把脏页刷新回磁盘，当DB重启恢复时只需要恢复checkpoint之后的数据即可），导致InnoDB缓存脏页的小批量刷新，明显降低InnoDB的性能。可以通过增大log file size避免一个日志文件过快被写满，但是如果日志文件设置得过大，恢复时将需要更长的时间，同时也不便于管理。一般来说，平均每半个小时写满一个日志文件比较合适。

redo log缓存池调整

参数innodb_log_buffer_size决定InnoDB重做日志缓冲池的大小。对于可能产生大量更新记录的大事务，增加innodb_log_buffer_size的大小，可以避免InnoDB在事务提交前就执行不必要的日志写入磁盘操作。因此，对于会在一个事务中更新、插入或删除大量记录的应用，可以通过增大innodb_log_buffer_size来减少日志写磁盘操作，提高事务处理性能。

redo log刷盘时机

通过调整innodb_flush_log_at_trx_commit参数设置

设置为0：在提交事务时，InnoDB不会立即触发将缓存日志log buffer写到磁盘文件的操作，而是每秒触发一次缓存日志回写磁盘操作，并调用操作系统fsync刷新I/O缓存。
设置为1：每次事务提交时MySQL都会立即把log buffer的数据写入redo log file，并且调用操作系统fsync刷新I/O缓存（刷盘操作）。值为1时，每次事务提交都持久化一次，当然是最安全的，但是数据库性能会受影响，I/O负担重，适合对安全要求极高的交易系统场景（建议配置SSD硬盘提高I/O能力）。
设置为2：每次事务提交时MySQL都会把redo log buffer的数据写入redo log file，但是flush（刷到磁盘）操作并不会同时进行，而是每秒执行一次flush（磁盘I/O缓存刷新）。注意，由于进程调度策略问题，并不能保证百分之百的“每秒”。

undo log

概念

回滚日志，用来记录数据被修改前的信息。正好跟前面的重做日志进行相反操作。undo log主要记录的是数据的逻辑变化，为了在发生错误时回滚之前的操作，需要将之前的操作都记录下来，然后在发生错误时才可以回滚。

作用

保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读。

内容

逻辑格式的日志，在执行 undo 的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，这一点是不同于 redo log 的。

例子

举个undo log作用的例子：Session1会话（以下简称S1）和Session2会话（以下简称S2）同时访问（不一定同时发起，但S1和S2事务有重叠）同一数据A，S1想要将数据A修改为数据B，S2想要读取数据A的数据。如果没有MVCC（Multi-Version Concurrency Control，多版本并发控制）机制就只能依赖锁了，谁拥有锁谁先执行，另一个等待，但是高并发下的效率很低。InnoDB存储引擎通过MVCC多版本控制的方式来读取当前执行时间数据库中行的数据，如果读取的行正在执行delete或update操作，这时读取操作不会因此等待行上锁的释放；相反，InnoDB会去读取行的一个快照数据（undo log），从历史快照（undo log链）中获取旧版本数据来保证数据一致性。由于历史版本数据存放在undo页当中，对数据修改所加的锁对于undo页没有影响，因此不会影响用户对历史数据的读，从而达到非一致性锁定读，提高并发性能。

如果出现了错误或者用户手动执行了rollback，系统可以利用undo log中的备份将数据恢复到事务开始之前的状态。与redo log不同的是，磁盘上不存在单独的undo log文件，它存放在数据库内部的特殊段（segment）中。

MySQL 5.6以后的版本支持把undo log分离到独立的表空间，并放到单独的文件目录下。采用独立undo表空间，再也不用担心undo会把ibdata1文件搞大，同时也给我们部署不同I/O类型的文件位置带来了便利。对于并发写入型负载，我们可以把undo文件部署到单独的高速存储设备上。

bin log

介绍

归档日志。记录了所有的DDL和DML语句（除查询语句外），以事件形式记录，是事务安全型。

作用

用于复制，在主从复制中，从库利用主库上的 binlog 进行重播，实现主从同步。用于数据库的基于时间点的还原。

日志模式

Statement（基于 SQL 语句的复制）：
- 优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。
- 由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同的结果。
Row（基于行的复制）
- 优点：可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，以及trigger的调用和触发无法被正确复制的问题。
- 缺点：所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。
Mixed（混合模式）
一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存bin log，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。

bin log 和 redo log区别

作用不同：bin log用于基于时间点恢复、数据复制、审计；redo log用于崩溃恢复，保证事务持久性。
层次不同：bin log是MySQL的服务器层实现的，同时支持InnoDB和其他存储引擎；redo log是InnoDB存储引擎实现的。
内容不同：bin log的内容是逻辑格式的日志；redo log是物理日志。
写入时机不同：bin log在事务提交时写入；redo log的写入时机相对多元，通过修改innodb_flush_log_at_trx_commit参数可以改变该策略。

slow query log

概念

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10S以上的语句。默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

配置

修改my.cnf文件，增加或修改参数slow_query_log 和slow_query_log_file后，然后重启MySQL服务器，如下所示

slow_query_log =1

slow_query_log_file=/tmp/mysql_slow.log

查看慢查询数目

1	SHOW GLOBAL STATUS LIKE '%Slow_queries%';

四、缓存

Buffer Pool

作用

数据缓存：Buffer Pool用于缓存数据库中的数据页和索引页，减少对磁盘的读取操作，提高数据的访问速度。
减少I/O操作：通过将热点数据存储在内存中，减少了频繁的磁盘I/O操作，提高了数据库的性能。

特点

固定大小：Buffer Pool的大小是固定的，通常在配置文件中进行设置，一般建议设置为适当的大小以充分利用系统内存。
LRU算法：Buffer Pool使用LRU（Least Recently Used）算法来管理内存中的数据页，根据访问频率和时间戳来决定哪些数据页需要保留在内存中。
脏页处理：Buffer Pool中的数据页可能被修改，这些被修改但尚未写回磁盘的数据页称为脏页。InnoDB会定期将脏页刷新到磁盘，以确保数据持久化。

参数配置

在MySQL的配置文件中，可以通过参数配置InnoDB Buffer Pool的大小和其他相关设置，例如：

innodb_buffer_pool_size：指定Buffer Pool的大小，单位为字节。
innodb_buffer_pool_instances：指定Buffer Pool的实例数量，用于将Buffer Pool划分为多个实例，以提高并发性能。
其他相关参数如innodb_buffer_pool_chunk_size等。

性能优化

合理配置大小：根据系统内存情况和数据库负载来合理配置Buffer Pool的大小。
监控和调优：定期监控Buffer Pool的命中率、脏页比例等指标，进行性能调优。
其他优化：可以通过调整其他相关参数来进一步优化Buffer Pool的性能，如innodb_buffer_pool_instances等。

开机缓存预热

在MySQL 5.6之前的版本里，如果一台高负荷的机器重启后内存中大量的热数据被清空，buffer中的数据就需要重新预热。所谓预热，就是等待常用数据通过用户调用SQL语句从磁盘载入到内存，这样高峰期间性能就会变得很差，连接数就会很高。通常要手动写一个脚本或存储过程来预热。
MySQL 5.6之后的版本提供了一个新特性来快速预热buffer_pool缓冲池，如图2-4所示。参数innodb_buffer_pool_dump_at_shutdown=ON表示在关闭MySQL时会把内存中的热数据保存在磁盘里ib_buffer_pool文件中，其保存比率由参数innodb_buffer_pool_dump_pct控制，默认为25% 。参数 innodb_buffer_pool_load_at_startup=ON 表示在启动时会自动加载热数据到Buffer_Pool缓冲池里。这样，始终保持热数据在内存中。

MySQL LRU内存淘汰机制

LRU算法简介

LRU（Letest Recent Used，最近最少使用）算法来进行数据页的换进换出操作。也就是说，最频繁的页放在LRU列表的前端，而最少使用的页放在LRU列表的尾端。缓冲池不能存放新读取到的页时，会释放LRU列表中尾端的页。

其实所谓的lru链表本质上就是一个双向循环链表

LRU在MySQL存在的缺陷

如果是全表扫描，会把全表都加载到buffer pool中，有可能就把LRU链表中经常访问的都挤到后面去，就有可能被淘汰。

基于冷热数据分离的LRU链表

MySQL中设计LRU链表，是将冷热数据分离，链表分为两部分，一部分是冷数据，一部分是热数据，冷热数据占比由配置项innodb_old_blocks_pct控制，默认为37，即37%是冷数据。

数据分布

lru链表被Midpoint分成了new sublist和old sublist两部分。

其中new sublist大概占比5/8，old sublist占比3/8。

数据加载

数据页从磁盘加载到buffer时，先加入old sublist。

LRU列表中有一个Midpoint的位置，==新读取到的数据页并不是直接放入到LRU列表的首部，而是放入到LRU列表的Midpoint位置，这个操作称之为Midpoint insertion stategy，也叫中间点插入策略==。在默认配置下，该位置在LRU长度的5/8处，这也就是上面使用8个数据页的作用。

热数据加载

从old区到new区

数据页第一次被加载进BufferPool时在old区头部。当这个数据页在old区，再次被访问到，会做如下判断

访问的时间跟第一次访问的时间间隔相差1s以上，满足这个条件，则该页将会被放入new_lru列表的头部。

也就会从old区进入到new区了。
这个存在时间由innodb_old_blocks_time控制，默认1000毫秒。

从new区到old区

随着冷数据查询变多或者热数据查询变多，相应的他们对应的两个区的大小也不一样。

不管是old区变长还是new区变长，只要每次区间长度变化时保证Midpoint始终指向5/8的位置，那么就可以保证old区占用3/8的空间。

如果new区变长，当Midpoint指针移动时，那么这时new区的空间就会部分转移到old区中。

热数据内部的移动

在热数据区域，如果一个缓存页被访问，不一定会立马移动到热数据链表的表头，因为频繁的移动链表也是有性能消耗的，因此，MySQL中设计为，==如果热数据链表前25%(1/4)的缓存页被访问，他们是不会被移动的，只有在后75%(3/4)中的缓存页被访问，才会移动到表头，这样就能尽可能的减少链表中节点的移动，从而减小性能的损耗==

计算过程

每一次移动会产生一个lock

两个重要参考：

freed_page_clock：Buffer Pool淘汰页数

LRU_new长度1/4

移动时机

当前freed_page_clock - 上次移动到Header时freed_page_clock>LRU_new长度1/4

说明：

当热数据第一加入或者是上次移动到Header时会记录一个freed_page_clock
再次访问到该数据的时候有一个当前的freed_page_clock
freed_page_clock 减去上次移动到Header时freed_page_clock ,如果大于LRU_new长度1/4，则该页移动到LRU_new的表头

冷数据刷盘

什么时候将LRU链表中的冷热数据中的缓存页刷盘

定时刷盘，MySQL会起一个后台线程，运行定时任务，每隔一定的时间就将LRU链表的冷数据区域尾部的一些缓存页刷盘，然后清空这些缓存页，并放入free链表，从LRU链表删除，从Flush链表删除

Query Cache

前言

在这个“Cache为王”的时代，我们总是通过不同的方式去缓存我们的结果，从而提高响应效率，但是一个缓存机制是否有效、效果如何是需要好好思考的问题。在MySQL中的QueryCache是一个适用较少情况的缓存机制。

什么时候开启

如果你的应用对数据库的更新很少，那么Query Cache将会作用显著。例如，比较典型的博客系统，一般博客更新相对较慢，数据表相对稳定不变，这时Query Cache的作用会比较明显。Query Cache有如下规则：如果数据表被更改，那么和这个数据表相关的全部Cache都会无效，并会被删除。这里“数据表更改”包括insert、update、delete、truncate、alter table、drop table、drop database等。

什么时候关闭

如果数据表posts访问频繁，那么意味着它的很多数据会被Query Cache缓存起来，但是每一次posts数据表的更新，无论是不是影响Cache的数据，都会将全部和posts表相关的Cache清除。如果数据表更新频繁，那么Query Cache将会成为系统的负担，会降低系统13%的处理能力。在OLTP的业务场景下，Query Cache建议关闭。如果可以应用层实现缓存，那么Query Cache可以忽略。

缓存规则

查询缓存会将查询语句和结果集保存到内存（一般是 key-value 的形式，key 是查询语句，value 是查询的结果集），下次再查直接从内存中取。
缓存的结果是通过 sessions 共享的，所以一个 client 查询的缓存结果，另一个 client 也可以使用。
SQL 必须完全一致才会导致查询缓存命中（大小写、空格、使用的数据库、协议版本、字符集等必须一致）。检查查询缓存时，MySQL Server 不会对 SQL 做任何处理，它精确的使用客户端传来的查询。
不缓存查询中的子查询结果集，仅缓存查询最终结果集。
不确定的函数将永远不会被缓存, 比如 now()、curdate()、last_insert_id()、rand() 等。
不缓存产生告警（Warnings）的查询。
太大的结果集不会被缓存 (< query_cache_limit)。
如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、MySQL 库中的系统表，其查询结果也不会被缓存。
缓存建立之后，MySQL 的查询缓存系统会跟踪查询中涉及的每张表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。
MySQL 缓存在分库分表环境下是不起作用的。
不缓存使用 SQL_NO_CACHE 的查询。

优点和缺点

优点：

查询缓存的查询，发生在 MySQL 接收到客户端的查询请求、查询权限验证之后和查询 SQL 解析之前。也就是说，当 MySQL 接收到客户端的查询 SQL 之后，仅仅只需要对其进行相应的权限验证之后，就会通过查询缓存来查找结果，甚至都不需要经过 Optimizer 模块进行执行计划的分析优化，更不需要发生任何存储引擎的交互。
由于查询缓存是基于内存的，直接从内存中返回相应的查询结果，因此减少了大量的磁盘 I/O 和 CPU 计算，导致效率非常高。

缺点：

MySQL 会对每条接收到的 SELECT 类型的查询进行 Hash 计算，然后查找这个查询的缓存结果是否存在。虽然 Hash 计算和查找的效率已经足够高了，一条查询语句所带来的开销可以忽略，但一旦涉及到高并发，有成千上万条查询语句时，hash 计算和查找所带来的开销就必须重视了。
查询缓存的失效问题。如果表的变更比较频繁，则会造成查询缓存的失效率非常高。表的变更不仅仅指表中的数据发生变化，还包括表结构或者索引的任何变化。
查询语句不同，但查询结果相同的查询都会被缓存，这样便会造成内存资源的过度消耗。查询语句的字符大小写、空格或者注释的不同，查询缓存都会认为是不同的查询（因为他们的 Hash 值会不同）。
相关系统变量设置不合理会造成大量的内存碎片，这样便会导致查询缓存频繁清理内存。

缓存对性能的影响

读查询开始之前必须检查是否命中缓存。
如果读查询可以缓存，那么执行完查询操作后，会查询结果和查询语句写入缓存。
当向某个表写入数据的时候，必须将这个表所有的缓存设置为失效，如果缓存空间很大，则消耗也会很大，可能使系统僵死一段时间，因为这个操作是靠全局锁操作来保护的。
对 InnoDB 表，当修改一个表时，设置了缓存失效，但是多版本特性会暂时将这修改对其他事务屏蔽，在这个事务提交之前，所有查询都无法使用缓存，直到这个事务被提交，所以长时间的事务，会大大降低查询缓存的命中。

change buffer & insert buffer

命名历史

在MySQL 5.5之前的版本中，由于只支持缓冲insert操作，因此最初叫作insert buffer；后来的版本中支持了更多的操作类型缓冲，所以才改叫change buffer。

主要作用

二级索引的修改是随机I/O，插入性能低；将二级索引的数据操作缓存下来，以减少二级索引的随机I/O，并达到操作合并的效果。

工作原理

将非聚簇、非唯一的二级索引，先判断非聚集索引页是否在缓冲池中，若在则直接插入；若不在，则先放入一个插入缓冲区，再以一定的频率执行插入缓冲和非聚簇索引页子节点的合并操作。

优势

提高性能：通过减少磁盘I/O操作，提高了写入性能。
降低延迟：将随机写转换为顺序写，降低了写入操作的延迟。
减少碎片：减少了索引页的分裂和碎片化。

参数

innodb_change_buffering：all值表示缓存insert、delete、purges操作。
innodb_change_buffer_max_size：用于配置change buffer在Buffer Pool中所占的最大百分比，默认是25%，最大可以设置为50%。

查询信息

1	SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE '%ibuf%' \G;

1	show engine innodb status\G

redo log buffer

作用

redo log buffer会周期性地刷新到磁盘的redo log file中。一个大的redo log buffer允许大事务在提交之前不写入磁盘的redo log文件。因此，如果有事务需要update、insert、delete许多记录，则可增加redo log buffer来节省磁盘I/O。

参数

innodb_flush_log_at_trx_commit

控制redo log flush的频率，默认为1

0：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作。
1：每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去，该模式为系统默认。
2：每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作。

注意：写入日志（调用系统调用write），只是简单地把数据移到操作系统缓存（内存）中，并没有实际持久化数据。

刷入日志（调用系统用flush），真正的将数据持久化进磁盘。

double write buffer

背景

double write（两次写）技术的引入是为了提高数据写入的可靠性。这里先说明一下page页坏的问题。因为数据库中一个page的大小是16KB，数据库往存储上写数据是以更小的单位进行的，这就产生了一个问题：当发生数据库宕机时，可能InnoDB存储引擎正在写入某个页到表中，而这个页只写了一部分，比如16KB的页，只写了前4KB，之后就发生了宕机，这种情况被称为部分写失效（partial page write）。在InnoDB存储引擎未使用double write技术前，曾经出现过因为部分写失效而导致数据丢失的情况。

原理

镜像备份思想：

1.每次写入一个page时，先把page写到double write buffer中。

2.如果在写double write buffer时发生了意外，但是数据文件中原来的page不受影响，这样在下次启动时可以通过InnoDB的redo log进行恢复。

3.在写double write buffer成功后，MySQL会把double write buffer的内容写到数据文件中

4.如果在这个过程又出现了意外，没有关系，重启后MySQL可以从double write buffer找到好的page，再用好的page去覆盖磁盘上坏的page，解决page坏的问题。

注意点

redo无法修复这类“页数据损坏”的异常，修复的前提是“页数据正确”并且redo日志正常。
如果SSD设备支持原子写，那么在MySQL中可以通过设置参数innodb_doublewrite=0关掉double write的功能。

查看命令

1	show global status like 'innodb_dblwr%'\G

五、事务和锁

什么是事务？

在数据库管理系统中，事务（Transaction）是指作为单个逻辑工作单元执行的一系列操作，这些操作要么全部成功执行，要么全部失败回滚，保证数据库的一致性和完整性。事务是数据库管理系统中用来确保数据操作的一致性、隔离性和持久性的重要概念。

对于 MySQL 数据库来说，事务是指以执行start transaction命令开始，到执行commit或者rollback命令结束之间的全部 SQL 操作。
如果这些 SQL 操作全部执行成功，则执行commit命令提交事务，表示事务执行成功。
如果这些 SQL 操作中任一操作执行失败，则执行rollback命令回滚事务，表示事务执行失败，并将数据库回滚到执行start transaction命令之前的状态。

事务四特性(ACID)

原子性 Atomicity
一致性 Consistency
隔离性 Isolation
持久性 Durability

原子性

定义

整个事务中的所有操作要么全部执行成功，要么全部执行失败后回滚到最初状态。

原理

当程序正常，事务提交时，MySQL正常执行，执行日志记录在redo log中。

当程序异常，事务回滚时，MySQL通过undo log反向补偿，将数据库回退到事务开始的状态。

持久性

定义

一旦提交，事务所做出的修改就会永久保存，即使数据库崩溃，修改的数据也不会丢失。

原理

redo log

隔离性

定义

一个事务所做的修改操作在提交事务之前，对于其他事务来说是不可见的。

原理

锁和MVCC

一致性

定义

事务执行结束后，数据库的完整性约束没有被破坏，事务执行的前后都是合法的数据状态

原理

原子性、持久性、隔离性，都是为了保证数据库状态的一致性。

锁

读锁（共享锁）

当会话持有读锁时，其他会话可以同时获取读锁，但不能获取写锁。

写锁（排他锁）

当会话持有写锁时，其他会话既不能获取读锁也不能获取写锁。

行锁

锁定一行的数据。

表锁

锁定整个表的数据。

间隙锁

锁定一个范围的数据。

Next-key Lock

锁定一个范围的记录并包含记录本身。Next-key Lock是记录锁与区间锁的组合，当InnoDB扫描索引记录时，会先对选中的索引记录加上记录锁，再对索引记录两边的区间加上区间锁。

乐观锁

乐观锁的思想是：假设在数据被修改期间不会发生冲突，只在更新数据时检查是否有其他事务对数据进行了修改。
实现方式：通常通过版本号（Versioning）或时间戳（Timestamp）等方式来实现，更新数据时比较版本号或时间戳，如果发生冲突则回滚操作。
优点：不会阻塞其他事务的访问，适用于读操作频繁、写操作较少的场景，性能较好。
缺点：需要额外的版本号或时间戳字段，可能需要重试操作以处理冲突。

悲观锁

悲观锁的思想是：假设在数据被修改期间会发生冲突，因此在访问数据之前会先获取锁，确保在整个操作过程中数据不会被其他事务修改。
实现方式：通常通过数据库提供的锁机制（如行锁或表锁）来实现，例如使用SELECT … FOR UPDATE语句获取行级锁。
优点：能够确保数据的一致性，适用于数据更新频繁的场景。
缺点：可能导致性能下降，因为悲观锁会阻塞其他事务的访问，特别是在高并发环境下。

MVCC

定义

全称Multi-Version Concurrency Control，即多版本并发控制，主要是为了提高数据库的并发性能。

同一行数据平时发生读写请求时，会上锁阻塞住。但mvcc用更好的方式去处理读—写请求，做到在发生读—写请求冲突时不用加锁。

快照读和当前读

实现原理：快照读用MVCC实现，当前读用Next-Key Lock锁实现

快照读(ReadView)：就是select操作。
当前读：特殊的读操作，先读后改，Select ... for update、insert、update、delete操作，属于当前读，处理的都是当前的数据，需要加锁。

快照读ReadView结构

ReadView_ids：当前活跃的事务编号集合
min_trx_id：最小活跃事务编号
max_trx_id：预分配事务编号当前最大事务编号+1
creator_trx_id：ReadView创建者的事务编号

行数据隐藏列与undo log版本链

TRX ID：事务ID（版本号）
DBROLLPTR：指向undo log的指针

undo log查找流程

TRX_ID = creatortrxid（显示）成立说明数据就是自己这个事务更改的，可以访问。

TRX_ID < mintrxid（显示）成立说明数据已经提交了，可以访问。

TRX_ID > max_trx_id（不显示）成立说明该事务是在ReadView生成以后才开启，不充许访问。

not in ReadView_ids（显示）代表数据是已提交的，可以访问。

死锁排查

来查看锁等待超时时间

1	show variables like '%innodb_lock_wait%'

查看最近死锁的日志

1	show engine innodb status

查询是否锁表

1	SHOW OPEN TABLES where In_use > 0

显示所有进程

1	show full processlist

杀进程

kill id

查看正在进行中的事务

select * from information_schema.innodb_trx

+—————————-+———————+——+—–+———————+——-+
| Field                      | Type                | Null | Key | Default             | Extra |
+—————————-+———————+——+—–+———————+——-+
| trx_id                     | varchar(18)         | NO   |     |                     |       |#事务ID
| trx_state                  | varchar(13)         | NO   |     |                     |       |#事务状态：
| trx_started                | datetime            | NO   |     | 0000-00-00 00:00:00 |       |#事务开始时间；
| trx_requested_lock_id      | varchar(81)         | YES  |     | NULL                |       |#innodb_locks.lock_id
| trx_wait_started           | datetime            | YES  |     | NULL                |       |#事务开始等待的时间
| trx_weight                 | bigint(21) unsigned | NO   |     | 0                   |       |#
| trx_mysql_thread_id        | bigint(21) unsigned | NO   |     | 0                   |       |#事务线程ID
| trx_query                  | varchar(1024)       | YES  |     | NULL                |       |#具体SQL语句
| trx_operation_state        | varchar(64)         | YES  |     | NULL                |       |#事务当前操作状态
| trx_tables_in_use          | bigint(21) unsigned | NO   |     | 0                   |       |#事务中有多少个表被使用
| trx_tables_locked          | bigint(21) unsigned | NO   |     | 0                   |       |#事务拥有多少个锁
| trx_lock_structs           | bigint(21) unsigned | NO   |     | 0                   |       |#
| trx_lock_memory_bytes      | bigint(21) unsigned | NO   |     | 0                   |       |#事务锁住的内存大小（B）
| trx_rows_locked            | bigint(21) unsigned | NO   |     | 0                   |       |#事务锁住的行数
| trx_rows_modified          | bigint(21) unsigned | NO   |     | 0                   |       |#事务更改的行数
| trx_concurrency_tickets    | bigint(21) unsigned | NO   |     | 0                   |       |#事务并发票数
| trx_isolation_level        | varchar(16)         | NO   |     |                     |       |#事务隔离级别
| trx_unique_checks          | int(1)              | NO   |     | 0                   |       |#是否唯一性检查
| trx_foreign_key_checks     | int(1)              | NO   |     | 0                   |       |#是否外键检查
| trx_last_foreign_key_error | varchar(256)        | YES  |     | NULL                |       |#最后的外键错误
| trx_adaptive_hash_latched  | int(1)              | NO   |     | 0                   |       |#
| trx_adaptive_hash_timeout  | bigint(21) unsigned | NO   |     | 0                   |       |#
+—————————-+———————+——+—–+———————+——-+
————————————————

查看正在锁的事务

select * from information_schema.INNODB_LOCKS

+————-+———————+——+—–+———+——-+
| Field       | Type                | Null | Key | Default | Extra |
+————-+———————+——+—–+———+——-+
| lock_id     | varchar(81)         | NO   |     |         |       |#锁ID
| lock_trx_id | varchar(18)         | NO   |     |         |       |#拥有锁的事务ID
| lock_mode   | varchar(32)         | NO   |     |         |       |#锁模式
| lock_type   | varchar(32)         | NO   |     |         |       |#锁类型
| lock_table  | varchar(1024)       | NO   |     |         |       |#被锁的表
| lock_index  | varchar(1024)       | YES  |     | NULL    |       |#被锁的索引
| lock_space  | bigint(21) unsigned | YES  |     | NULL    |       |#被锁的表空间号
| lock_page   | bigint(21) unsigned | YES  |     | NULL    |       |#被锁的页号
| lock_rec    | bigint(21) unsigned | YES  |     | NULL    |       |#被锁的记录号
| lock_data   | varchar(8192)       | YES  |     | NULL    |       |#被锁的数据
+————-+———————+——+—–+———+——-+

查看等待锁的事务

select * from information_schema.INNODB_LOCK_WAITS

+——————-+————-+——+—–+———+——-+
| Field             | Type        | Null | Key | Default | Extra |
+——————-+————-+——+—–+———+——-+
| requesting_trx_id | varchar(18) | NO   |     |         |       |#请求锁的事务ID
| requested_lock_id | varchar(81) | NO   |     |         |       |#请求锁的锁ID
| blocking_trx_id   | varchar(18) | NO   |     |         |       |#当前拥有锁的事务ID
| blocking_lock_id  | varchar(81) | NO   |     |         |       |#当前拥有锁的锁ID
+——————-+————-+——+—–+———+——-+

并发情况下出现的问题

脏读

当前事务（A）中可以读到其他事务（B）未提交的数据（脏数据）。

不可重复读

当前事务A中先后两次读取同一个数据，两次读取的结果不一样。

幻读

当前事务A中按照某个条件先后两次查询数据库，两次查询结果的条数不同

事务隔离级别

MySQl：InnoDB 默认的隔离级别是Repeatable Read可重复读。
Oracle：默认隔离级别是Read Committed读已提交。

要注意的是，在 SQL 标准中，可重复读是无法避免幻读问题的，但是 InnoDB 实现的RR通过MVCC避免了幻读问题。

-- 查询全局隔离级别
select @@global.tx_isolation
-- 查询本次会话隔离级别
select @@tx_isolation

MVCC解决过程

脏读

时间线

undo log链

当事务TRX_ID=1在 T3 时间节点读取zhangsan的余额时，生成快照读ReadView。

ReadView_ids={1,2}

min_trx id=1

max_trx_id=3

creator_trx_id=1

第一步

TRX_ID = creatortrxid（显示）否

TRX_ID < mintrxid（显示）否

TRX_ID > max_trx_id（不显示）否

not in ReadView_ids（显示）否

下一步
第二步

TRX_ID = creatortrxid（显示）否

TRX_ID < mintrxid（显示）否

TRX_ID > max_trx_id（不显示）否

not in ReadView_ids（显示）是

返回

不可重复读

时间线

undo log链

当事务TRX_ID=1在 T5时间节点读取zhangsan的余额时，生成快照读ReadView。

ReadView_ids={1}

min_trx id=1

max_trx_id=2

creator_trx_id=1

第一步

TRX_ID = creatortrxid（显示）否

TRX_ID < mintrxid（显示）否

TRX_ID > max_trx_id（不显示）是

下一步

第二步

TRX_ID = creatortrxid（显示）否

TRX_ID < mintrxid（显示）否

TRX_ID > max_trx_id（不显示）否

not in ReadView_ids（显示）是

返回

幻读

时间线

undo log链

解决：

类Next-Key Lock，此时的类next-key lock并不是真的加锁，只是为读取的数据增加了标记（标记内容包括数据的版本号等），准确起见姑且称之为类next-key lock机制。

当事务TRX_ID=1在 T2 节点第一次读取8870<id<8875数据时，标记的不只是id=1的数据，而是将范围(8870, 8875)进行了标记。

这样当 T5 时刻再次读取8870<id<8875数据时，便可以发现id=8872的数据比之前标记的版本号更高，此时再结合undo log执行回滚操作，避免了幻读。

六、SQL语句性能优化

SQL语句执行过程

解析器（Parser）：

功能：解析器负责解析SQL语句，将用户提交的SQL语句转换为内部数据结构，以便后续处理。

子阶段：

词法分析（Lexical Analysis）：将SQL语句分解为词法单元（tokens）。
语法分析（Syntax Analysis）：验证SQL语句的语法结构是否正确，并构建语法树。

预处理器（Preprocessor）：

功能：预处理器负责对解析后的SQL语句进行初步处理，包括表名解析、列名解析等。

子阶段：

表名解析：解析SQL语句中的表名，确定表的真实位置。
列名解析：解析SQL语句中的列名，确定列的真实位置。

查询优化器（Query Optimizer）：

功能：查询优化器负责对经过解析和预处理的查询进行优化，选择最优执行计划以提高查询性能。

优化技术：

索引选择：选择适当的索引以加速查询。
连接顺序：确定表的连接顺序，减少连接操作的成本。
子查询优化：优化子查询的执行方式。
统计信息利用：利用表的统计信息来选择最佳执行计划。

查询执行引擎（Query Execution Engine）：

功能：查询执行引擎根据查询优化器选择的最优执行计划，执行实际的查询操作。
执行计划：执行引擎根据执行计划逐步执行查询操作，包括扫描表、应用过滤条件、执行连接操作等。
结果返回：执行引擎将查询结果返回给客户端应用程序

开启慢SQL日志

参数

slow_query_log：是否开启慢查询日志on/off
slow_query_log_file：指定慢查询日志存储于哪个日志文件中
long_query_time：超时多长认定为慢查询
log_queries_not_using_indexes：表示如果运行的SQL语句没有使用到索引，是否也被当作慢查询语句记录到慢查询日志中，OFF表示不记录，ON表示记录
log_throttle_queries_not_using_indexes：没有使用索引时限制这种语句每分钟记录到慢查询日志中的次数

用mysqldumpslow命令查询慢SQL

explain分析

id：查询的序列号，表示查询中执行顺序，如果是子查询会有多个id。
select_type：查询类型，如SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）等。
table：查询涉及的表名。
type：访问类型，表示MySQL选择了怎样的方式来访问表，常见的有ALL、index、range、ref等。
possible_keys：可能应用在这张表中的索引。
key：实际使用的索引。
key_len：索引中使用的字节数。
ref：显示索引的哪一列被使用了，如果可能的话。
rows：MySQL认为必须检查的行数。
Extra：包含MySQL解决查询的详细信息，如使用了临时表、文件排序等。

show profile

在MySQL中，SHOW PROFILES是一个用于显示最近查询的性能信息的命令。它可以用来查看MySQL服务器中最近执行的查询的性能统计数据，包括每个查询的执行时间、CPU消耗、磁盘I/O等信息。通过SHOW PROFILES命令，可以帮助开发人员和数据库管理员分析查询的性能，找出潜在的性能瓶颈和优化空间

Query_ID：查询的唯一标识符。
Duration：查询执行时间，以秒为单位。
Query：查询语句。
State：查询的状态。
Info：查询的附加信息。

索引使用的原则

表一定要有主键，显式定义主键且采用与业务无关的列以避免修改。InnoDB表在有主键时会自动将主键设为聚集索引，建议采用自增列来使数据顺序插入。
关于合理添加索引，有一个通常的法则，即对于经常被查询的列、经常用于表连接的列、经常排序分组的列，需要创建索引。
创建索引之前，还要查看索引的选择性（不重复的索引值和表的记录总数的比值）来判断这个字段是否合适创建索引。索引的选择性越接近于1，说明选择性越高，非常适合创建索引。
组合索引（表中两个或两个以上的列上创建的索引），一般把选择性高的列放在前面。组合索引字段数不建议超过5个，如果5个字段还不能极大地缩小row范围，那么肯定是设计有问题。
合理利用覆盖索引（只需通过索引就可以返回查询所需要的数据，不必在查到索引之后再回表查询数据）。禁止使用select *，只获取必要字段，指定字段能有效利用索引覆盖。
使用explain判断SQL语句是否合理使用了索引，尽量避免Extra列出现Using File Sort、UsingTemporary。
单张表的索引数量建议控制在5个以内，索引太多也会浪费空间且降低修改数据的速度，影响性能。
不建议在频繁更新的字段上建立索引。
Where条件中的索引列不能是表达式的一部分，避免在Where条件中在索引列上进行计算或使用函数，因为这将导致索引不被使用而进行全表扫描。
如果要进行join查询，那么被join的字段必须类型相同并建立索引，因为join字段类型不一致会导致全表扫描。
隐式类型转换会使索引失效，导致全表扫描。

深入理解MySQL

一、存储引擎

Innodb

MyIASM

MEMORY引擎

MyISAM与InnoDB区别

二、索引

定义

优点

缺点

分类

主键索引

唯一索引

单列索引

复合索引(多列索引)

全文索引

索引数据结构

定义

为什么其他数据结构不可以

数组

链表

二叉树（Binary Tree）

平衡二叉树（Balanced Binary Tree）

哈希表（Hash Table）

B Tree

B+Tree

聚簇索引和非聚簇索引

对比

聚簇索引需要注意什么?

为什么主键通常建议使用自增id

导致索引失效的情况

like模糊匹配以通配符开头导致索引失效

使用函数导致索引失效

计算导致索引失效

类型转换导致索引失效

复合索引未遵守最左前缀原则

不等于(!= 或者<>)索引失效

索引字段使用is not null导致失效

OR 前后存在非索引的列，索引失效

关联查询时两张表字符编码不一致

范围查询太广

三、日志

redo log

背景

结构

内容

数据宕机恢复过程

日志文件切换

redo log缓存池调整

redo log刷盘时机

undo log

概念

作用

内容

例子

bin log

介绍

作用

日志模式

bin log 和 redo log区别

slow query log

概念

相关参数

配置

查看慢查询数目

四、缓存

Buffer Pool

作用

特点

参数配置

性能优化

开机缓存预热

MySQL LRU内存淘汰机制

LRU算法简介

LRU在MySQL存在的缺陷

基于冷热数据分离的LRU链表

数据分布

数据加载

热数据加载

热数据内部的移动