Mysql 索引、锁与MVCC等相关知识点

---

Mysql锁的类型

MySQL中有哪些锁：

1. 乐观锁（Optimistic Locking）：假设并发操作时不会发生冲突，只在提交事务时检查数据是否被其他事务修改过。常用于读多写少的场景。

2. 悲观锁（Pessimistic Locking）：假设并发操作时会发生冲突，因此在操作期间持有锁来避免冲突。常用于写多读少的场景。

3. 全局锁（Global Lock）：对整个数据库实例加锁，限制除了超级用户外的所有查询和修改操作。一般用于备份、恢复等操作。

4. 表级锁（Table Lock）：对整个表加锁，其他连接无法修改或读取该表的数据，但可以对其他表进行操作。

5. 意向共享锁（Intention Shared Lock）：表级锁的辅助锁，表示事务要在某个表或页级锁上获取共享锁。

6. 意向排它锁（Intention Exclusive Lock）：表级锁的辅助锁，表示事务要在某个表或页级锁上获取排它锁。

7. 页级锁（Page Lock）：对数据页（通常是连续的几个行）加锁，控制并发事务对该页的访问。适用于数据较大且并发量较高的场景。

8. 行级锁（Row Lock）：对单个行加锁，只锁定需要修改的数据行，其他行可以被同时修改或读取。并发性高，但锁管理较复杂。

9. 记录锁（Record Lock）：行级锁的特定类型，锁定单个行，确保其他事务无法同时修改或读取该行。

10. 共享锁（Shared Lock）：也称为读锁，多个事务可以同时持有共享锁并读取数据，但不能修改数据。适用于同时读取同一数据的场景。

11. 排它锁（Exclusive Lock）：也称为写锁，事务持有排它锁时，其他事务无法同时持有共享锁或排它锁，用于保护数据的写操作。

12. 间隙锁（Gap Lock）：锁定一个范围的键，但不包括这些键的实际值。用于防止其他事务在范围内插入数据。

13. 临建锁（Next-key Lock）：锁定数据库对象的元数据，如表结构，用于保证数据定义的一致性。

14. 插入意向锁：一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事务加了间隙锁（next-key lock 也包含间隙锁）。如果有的话，插入操作就会发生阻塞，直到拥有间隙锁的那个事务提交为止（释放间隙锁的时刻），在此期间会生成一个插入意向锁，表明有事务想在某个区间插入新记录，但是现在处于等待状态。如果遇到其他事务的插入意向锁，那也会被锁住等待。

插入意向锁是一个间隙锁，它锁定的是索引之间的空白区域，而不是锁定任何具体的索引记录。这意味着它告诉其他事务，“我打算在这个位置插入数据，所以你们不能在这个时候插入或者读取”。
插入意向锁通常在以下情况下使用：

• 当使用可重复读或以上隔离级别时，在执行插入操作之前，MySQL会检查是否有插入意向锁阻止当前事务插入导致冲突。
• 当两个事务在同一索引间隔进行插入时，一个事务需要等待另一个事务完成插入操作并释放锁。

插入意向锁不需要手动管理，它是MySQL内部锁管理逻辑的一部分。

-- 事务A开始
BEGIN;
-- 事务B开始
BEGIN;
 
-- 事务A尝试插入id=5
INSERT INTO t VALUES(5);
 
-- 此时事务B尝试插入id=6，但是会被事务A的插入意向锁阻塞
INSERT INTO t VALUES(6);

InnoDB在RR的事务隔离级别下，使用插入意向锁来控制和解决并发插入。
插入意向锁是一种特殊的间隙锁。插入意向锁在锁定区间相同但记录行本身不冲突的情况(主键、唯一索引）下互不排斥。

在执行插入语句时，如果插入的记录在其他事务持有间隙锁范围内，插入语句就会被阻塞，因为插入语句在碰到间隙锁时，会生成一个插入意向锁，然后插入意向锁和间隙锁之间是互斥的关系。

如果两个事务分别向对方持有的间隙锁范围内插入一条记录，而插入操作为了获取到插入意向锁，都在等待对方事务的间隙锁释放，于是就造成了循环等待，满足了死锁的四个条件：互斥、占有且等待、不可强占用、循环等待，因此发生了死锁。

其中属于InnoDB引擎七种类型的锁，他们分别是：

• 共享排他锁（Shared and Exclusive Locks）
• 意向锁（Intention Locks）
• 记录锁（Record Locks）
• 间隙锁（Gap Locks）
• 临键锁（Next-Key Locks）
• 插入意图锁（Insert Intention Locks）
• 自增锁（AUTO-INC Locks）

各种锁解析

InnoDB的3种行锁

读已提交隔离级别下，行级锁的种类只有记录锁（Record-Lock）
可重复读隔离级别下，行级锁的种类有记录锁（Record-Lock）、间隙锁（Gap-Lock）、临键锁（Next-Key Lock）
MySQL中支持行锁的存储引擎有InnoDB和NDB Cluster,MyIASM只有表锁。

加锁的对象是索引(锁都是加在索引上的)，加锁的基本单位是 临键锁，但在能使用记录锁或者间隙锁就能避免幻读现象的场景下会退化成记录锁或间隙锁。
非唯一索引，相同数据可能有多条。用行锁无法阻塞新数据插入。所以必须用间隙锁（Gap-Lock）或者临键锁（Next-Key Lock）实现。

InnoDB支持3种行锁的算法，分别是：

• Record Lock（行锁）：单个行记录上的锁，确保索引记录的间隙不变。间隙锁是针对事务隔离级别为可重复读或以上级别而已的。可以防止幻读的发生。
• Gap Lock（间隙锁）：锁定当前索引N和下一个索引之间的范围(N, +∞)，但不包含记录本身(双开区间)
• Next-Key Lock（临键锁）：Gap Lock与Record Lock的结合，锁定上一个索引M和记录本身N之间的范围(M, N]，并且锁定记录本身(左开右闭区间)

默认情况下，InnoDB工作在可重复读隔离级别下，并且会以Next-Key Lock的方式对数据行进行加锁，这样可以有效防止幻读的发生。

生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR(可重复读) 隔离级别下。

1、当使用唯一索引来等值查询的语句时, 如果这行数据存在，不产生间隙锁，而是记录锁。

2、当使用唯一索引来等值查询的语句时, 如果这行数据不存在，会产生间隙锁。

3、当使用唯一索引来范围查询的语句时，对于满足查询条件但不存在的数据产生间隙(gap)锁，如果查询存在的记录就会产生记录锁，加在一起就是临键锁(next-key)锁。

4、当使用普通索引不管是锁住单条，还是多条记录，都会产生间隙锁；

5、在没有索引上不管是锁住单条，还是多条记录，都会产生表锁；

Record Lock / 行锁

Record Lock总是会去锁住索引记录，如果InnoDB存储引擎表在建立的时候没有设置任何一个索引，那么这时InnoDB会使用隐式的主键来进行锁定

Gap Lock / 间隙锁

为什么要有间隙锁?是为了防止幻读,其主要通过两个方面实现这个目的：
(1)防止间隙内有新数据被插入(2)防止已存在的数据，更新成间隙内的数据

间隙锁只会阻止insert语句，所以同样的索引数据，insert语句阻塞而select for update语句不阻塞的就是间隙锁，如果两条语句都阻塞就是索引记录锁。

RU和RC 不存在间隙锁!如果隔离级别为RuU和RC，无论条件列上是否有索引，都不会锁表，只锁行!而所谓的“锁表”，其原理是通过行锁+间隙锁来实现的。

何时使用行锁，何时产生间隙锁？

1. 只使用唯一索引查询，并且只锁定一条记录时，innoDB会使用行锁。
2. 只使用唯一索引查询，但是检索条件是范围检索，或者是唯一检索但检索结果不存在（试图锁住不存在的数
   据）时，会产生 Next-Key Lock（临键锁）。
3. 使用普通索引检索时，不管是何种查询，只要加锁，都会产生间隙锁（Gap Lock）。
4. 同时使用唯一索引和普通索引时，由于数据行是优先根据普通索引排序，再根据唯一索引排序，所以也会产生
   间隙锁

两个事务的间隙锁之间是相互兼容的，不会产生冲突。

Next-Key Lock / 临键锁

Next-key锁是记录锁和间隙锁的组合，它指的是加在这条记录以及这条记录前面间隙上的锁(左开右闭区间)。

也可以理解为一种特殊的间隙锁。通过临建锁可以解决幻读的问题。 每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。需要强调的一点是，InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。

间隙锁和临键锁区别如下：

• 锁定范围不同：临键锁锁定索引记录本身以及后面的间隙，而间隙锁锁定索引记录之间的间隙，不包括索引记录本身。
• 锁定粒度不同：临键锁是细粒度的锁定机制，每次锁定一个索引记录以及后面的间隙，而间隙锁是粗粒度的锁定机制，每次锁定多个索引记录之间的间隙。
• 锁定效果不同：临键锁能够保证在并发情况下，不会出现两个事务同时插入相同索引记录的情况；间隙锁能够保证在并发情况下，不会出现两个事务同时插入相同索引记录之间的间隙的情况。
• 适用场景不同：临键锁适用于读操作和插入操作，可以有效避免脏读和不可重复读的问题；间隙锁适用于插入和删除操作，可以有效避免幻读的问题。
• 间隙锁只会阻止insert语句，所以同样的索引数据，insert语句阻塞而select for update语句不阻塞的就是间隙锁，如果两条语句都阻塞就是索引记录锁。

加锁示例

解析原文

3种行锁加锁示例

间隙锁和临键锁的转换

记录锁、间隙锁、临键锁-知乎

---

总结：

以下都以RR隔离级别示例

当使用唯一索引来等值查询的语句时：

• 如果这行数据存在，不产生间隙锁，而是记录锁。
• 查询的记录是「不存在」的，在索引树找到第一条大于该查询记录和第一条小于该查询记录的记录后，使用临键锁锁住这个区间。 临键锁 退化成「间隙锁」，锁住查询记录前后的索引（双开区间）。

使用唯一索引查询时，不仅会在唯一索引上加锁，还在对应的主键索引上加上锁，锁住临键锁与间隙锁之间的主键或者记录锁对应的主键

当使用辅助索引（非唯一索引）来等值查询的语句时，示例有三条索引A，C，E：

• 如果这行数据存在（>C），在数据前一个索引与数据之间产生临建锁，在数据与数据之后一个索引加间隙锁。
• 如果这行数据不存在(>D)，在数据前与数据后加一个间隙锁。

非唯一索引一定会在前后都加间隙锁，主要看是否加记录锁，记录锁和间隙锁共同组成临键锁。

行锁的实现是在索引上加锁，二级索引会在二级索引和主键索引上都加锁。
如果没有用索引检索，那么将会锁住这张表的所有临键锁，也就是退化成了表锁！具体是否使用索引还得用EXPLAIN的分析结果：

对age属性加索引后：

主键>=，如果=的数据存在，会使用记录锁锁住当前记录，再使用间隙锁锁住后面符合条件的索引。详细示例看小林coding博客

如果是用二级索引（不管是不是非唯一索引，还是唯一索引）进行锁定读查询的时候，除了会对二级索引项加行级锁（如果是唯一索引的二级索引，加锁规则和主键索引的案例相同），而且还会对查询到的记录的主键索引项上加「记录锁」。

可以通过 select * from performance_schema.data_locks\G; 这条语句，查看事务执行 SQL 过程中加了什么锁。

如果在插入新记录时，插入了一个与「已有的记录的主键或者唯一二级索引列值相同」的记录（不过可以有多条记录的唯一二级索引列的值同时为NULL，这里不考虑这种情况），此时插入就会失败，然后对于这条记录加上了 S 型的锁。

• 如果主键索引重复，插入新记录的事务会给已存在的主键值重复的聚簇索引记录添加 S 型记录锁，还会加一个X型表意向锁。

• 如果唯一二级索引重复，插入新记录的事务都会给已存在的二级索引列值重复的二级索引记录添加 S 型 next-key 锁。

非主键索引加锁，都会造成主键索引上加锁；在普通索引和唯一索引行锁下的间隙锁和临键锁，不仅将锁上当前索引，还会把两条记录之间的主键索引上锁；例如 在 id=5，age=7和 id=9，age=7中，对age=7上锁，还会对5-9加上相同锁。

Mysql学习博客-小林coding

Record Lock、Gap Lock、Next-Key Lock加锁案例

锁使用

使用方式：
乐观锁示例：

悲观锁：
悲观锁的实现通常通过使用SELECT … FOR UPDATE或使用LOCK IN SHARE MODE语句来加锁。

-- 事务1：查询并修改订单状态
START TRANSACTION;
-- 查询订单状态，并持有排它锁
SELECT order_id, status FROM orders WHERE order_id = 1 FOR UPDATE;
-- 执行一些业务逻辑判断...
-- 修改订单状态
UPDATE orders SET status = 'completed' WHERE order_id = 1;
COMMIT;

全局锁：

-- 事务1：加全局锁
FLUSH TABLES WITH READ LOCK;
-- 执行一些需要全局锁的操作...
-- 解除全局锁
UNLOCK TABLES;

表级锁的使用：

-- Session 1
START TRANSACTION;
-- 在Session 1中加共享锁
#显式上锁（手动）
lock table tableName read;//读锁
lock table tableName write;//写锁
#隐式上锁（默认，自动加锁自动释放
insert、update、delete //上写锁
-- 解锁
UNLOCK TABLES;
COMMIT;

InnoDB引擎的页级锁的示例

-- Session 1
START TRANSACTION;
-- 获取某个数据页的共享锁
SELECT * FROM products WHERE id = 1 LOCK IN SHARE MODE;
-- 执行一些只读操作
-- 解锁
COMMIT;

行级锁：

-- Session 1
START TRANSACTION;
-- 在Session 1中对某个行加共享锁
SELECT * FROM products WHERE id = 1 FOR SHARE;
-- 执行一些只读操作，例如SELECT语句，可以读取被共享锁保护的行
-- 解锁
COMMIT;

共享锁：

-- Session 1
START TRANSACTION;
-- 在Session 1中对某个行加共享锁
SELECT * FROM products WHERE id = 1 FOR SHARE;
-- 执行一些只读操作，例如SELECT语句，可以读取被共享锁保护的行
-- 解锁
COMMIT;

排它锁：

-- Session 1
START TRANSACTION;
-- 在Session 1中对某个行加排它锁
SELECT * FROM products WHERE id = 1 FOR UPDATE;
-- 执行一些修改操作，例如UPDATE、INSERT、DELETE等
-- 解锁
COMMIT;

意向共享锁：

-- Session 1
START TRANSACTION;
-- 在Session 1中获取意向共享锁
LOCK TABLES products INTENTIONAL READ;
-- 执行一些只读操作，例如SELECT语句，对表进行读操作
-- 解锁
UNLOCK TABLES;

意向排它锁：

-- Session 1
START TRANSACTION;

-- 在Session 1中获取意向排它锁
LOCK TABLES products INTENTIONAL WRITE;

-- 执行一些修改操作，例如UPDATE、INSERT、DELETE等

-- 解锁
UNLOCK TABLES;

间隙锁：

-- Session 1
START TRANSACTION;
-- 在Session 1中使用范围查询，并对查询结果的间隙加锁
SELECT * FROM products WHERE price BETWEEN 10 AND 20 FOR UPDATE;
-- 执行一些需要对查询结果进行修改的操作，例如UPDATE、DELETE等
-- 解锁
COMMIT;

临建锁：

-- Session 1
SELECT GET_LOCK('my_lock', 10);

-- 执行一些需要加锁的操作

SELECT RELEASE_LOCK('my_lock');

记录锁：

-- Session 1
START TRANSACTION;

-- 获取某个记录的共享锁
SELECT * FROM products WHERE id = 1 FOR SHARE;

-- 执行一些读操作

-- 释放锁
COMMIT;

-- Session 2
START TRANSACTION;

-- 获取某个记录的排他锁
SELECT * FROM products WHERE id = 1 FOR UPDATE;

-- 执行一些写操作

-- 释放锁
COMMIT;

锁使用详细示例
锁解析及使用-微信

死锁的生成：

死锁的四个必要条件：互斥、占有且等待、不可强占用、循环等待。只要系统发生死锁，这些条件必然成立，但是只要破坏任意一个条件就死锁就不会成立。

在数据库层面，有两种策略通过「打破循环等待条件」来解除死锁状态：

• 设置事务等待锁的超时时间。当一个事务的等待时间超过该值后，就对这个事务进行回滚，于是锁就释放了，另一个事务就可以继续执行了。在 InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的，默认值时 50 秒。
  当发生超时后，就出现下面这个提示：

ERROR 1205(HY000): Lock wait timeout exceeded; try restarting transaction

• 开启主动死锁检测。主动死锁检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑，默认就开启。
  当检测到死锁后，就会出现下面这个提示：

ERROR 1213(40001): Deadlock found when trying to get lock; try restarting transaction

两种方式优缺点

• 超时：
  • 缺点：超时时间难以把控，时间过长系统难以接受，时间过短容易误伤正常的事务。回滚事务undo log日志可能较多。
  • 优点：底层逻辑简单，保底手段
• 死锁检测：
  • 缺点：一旦阻塞就会检测，大量事务更新同key可能会浪费大量CPU资源进行死锁检测
  • 优点：存在死锁时回滚undo log少的事务

如何产生一个死锁

MVCC

MVCC(Mutil-Version Concurrency Control)，多版本并发控制。是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问。用于支持读已提交(RC）和可重复读(RR）隔离级别的实现。其他两个隔离级别够和MVCC 不兼容 , 因为 READ UNCOMMITTED 总是读取最新的数据行 , 而不是符合当前事务版本的数据行。而 SERIALIZABLE 则会对所有读取的行都加锁

数据库通过加锁，可以实现事务的隔离性，串行化隔离级别就是加锁实现的，但是加锁会降低数据库性能。
因此，数据库引入了MVCC多版本并发控制，在读取数据不用加锁的情况下，实现读取数据的同时可以修改数据，修改数据时同时可以读取数据，只有在InnoDB引擎下存在。

MVCC主要是用来解决【读-写】冲突的无锁并发控制，可以解决以下问题：

在并发读写数据时，可以做到在读操作时不用阻塞写操作，写操作不用阻塞读操作，提高数据库并发读写的性能。
可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决【写-写】引起的更新丢失问题。

一般数据库中都会采用以上MVCC与锁的两种组合来解决并发场景的问题，以此最大限度的提高数据库性能。

MVCC + 悲观锁：MVCC解决读-写冲突，悲观锁解决写-写冲突。
MVCC + 乐观锁：MVCC解决读-写冲突，乐观锁解决写-写冲突。

在InnoDB存储引擎，针对每行记录都有固定的两个隐藏列【DB_TRX_ID】【DB_ROLL_PTR】以及一个可能存在的隐藏列【DB_ROW_ID】。

隐式字段	描述	是否必须存在
DB_TRX_ID	事物Id，也叫事物版本号，占用6byte的标识，事务开启之前，从数据库获得一个自增长的事务ID，用其判断事务的执行顺序	是
DB_ROLL_PTR	占用7byte，回滚指针，指向这条记录的上一个版本的undo log记录，存储于回滚段（rollback segment）中	是
DB_ROW_ID	隐含的自增ID（隐藏主键），如果表中没有主键和非NULL唯一键时，则会生成一个单调递增的行ID作为聚簇索引	否

MVCC实际上是使用的update undo log 实现的快照读。

当事务对某一行数据进行改动时，会产生一条Undo日志，多个事务同时操作一条记录时，就会产生多个版本的Undo日志，这些日志通过回滚指针（DB_ROLL_PTR）连成一个链表，称为版本链。

MVCC能否解决幻读问题：
首先可以明确的是，MVCC在快照读的情况下可以解决幻读问题，但是在当前读的情况下是不能解决幻读的。

快照读和当前读

快照读【Consistent Read】

也叫普通读，读取的是记录数据的可见版本（可能是过期的数据），不加锁，不加锁的普通select语句都是快照读，即不加锁的非阻塞读。
快照读的执行方式是生成 ReadView，直接利用 MVCC 机制来进行读取，并不会对记录进行加锁。

如下语句：

select * from tableName;

当前读
也称锁定读【Locking Read】，读取的是记录数据的最新版本，并且需要先获取对应记录的锁，并且当前读返回的记录都会加上锁，保证其他事务不会再并发的修改这条记录。update、insert、delete 都是当前读。排它锁。如下语句：

SELECT * FROM student LOCK IN SHARE MODE;  # 共享锁
SELECT * FROM student FOR UPDATE; # 排他锁
INSERT INTO student values ...  # 排他锁
DELETE FROM student WHERE ...  # 排他锁
UPDATE student SET ...  # 排他锁

当前读每次都会重新生成一个Read View，新增、删除、修改、排他锁、共享锁都是当前读。

当前读状态下可以解决幻读问题

读视图【Read View】

Read View提供了某一时刻事务系统的快照，主要是用来做可见性判断, 里面保存了对本事务不可见的其他活跃事务。

当事务在开始执行的时候，会产生一个读视图（Read View），用来判断当前事务可见哪个版本的数据，即可见性判断。
实际上在innodb中，每个SQL语句执行前都会生成一个Read View。

Read View重要的四个属性：

• creator_trx_id
  创建当前read view的事务ID
• m_ids
  当前系统中所有的活跃事务的 id，活跃事务指的是当前系统中开启了事务，但还没有提交的事务;
• m_low_limit_id
  表示在生成ReadView时，当前系统中活跃的读写事务中最小的事务id，即m_ids中的最小值。
• m_up_limit_id
  当前系统中事务的 id 值最大的那个事务 id 值再加 1，也就是系统中下一个要生成的事务 id。

ReadView 会根据这 4 个属性，结合 undo log 版本链，来实现 MVCC 机制，决定一个事务能读取到数据那个版本。

读已提交只能读Read View中比自己小的事务ID，可重复读能读取比自己大的已提交的事务ID

在读已提交(Read Committed)的隔离级别下实现MVCC，同一个事务里面，【每一次查询都会产生一个新的Read View副本】，这样可能造成同一个事务里前后读取数据可能不一致的问题（不可重复读并发问题）。
读已提交下只要数据的事务ID不在m_ids中，就能查到当前数据

在可重复读(Repeatable read)的隔离级别下实现MVCC，【同一个事务里面，多次查询，都只会产生一个共用Read View】，所有就算期间有事务已经提交m_ids也不会改变，以此解决不可重复读的并发问题。

原博客
MVCC解析-微信

mvcc案例解析

图文解析-公众号

串行化的解决

可重复读中的幻读现象：

在可重复读隔离级别下，事务 A 第一次执行普通的 select 语句时生成了一个 ReadView，之后事务 B 向表中新插入了一条 id = 5 的记录并提交。接着，事务 A 对 id = 5 这条记录进行了更新操作，在这个时刻，这条新记录的 trx_id 隐藏列的值就变成了事务 A 的事务 id，之后事务 A 再使用普通 select 语句去查询这条记录时就可以看到这条记录了，于是就发生了幻读。

读取的是记录数据的最新版本，并且当前读返回的记录都会加上锁，保证其他事务不会再并发的修改这条记录。update、insert、delete 都是当前读。排它锁

行记录格式

行记录格式就是Mysql将数据存储到磁盘文件中最终每行存储的格式内容。

InnoDB提供了Compact和Redundant两种格式来存放行记录数据，在MySQL 5.1及之后版本中，默认以Compact行格式。
用户可以通过下面的命令来查看当前表使用的行格式

-- 其中table_name为要查看的表的名称
show table status like 'table_name';

特别注意：

• 每行数据除了用户定义的列外，还有两个隐藏列：事务ID列(trx_id)（6字节）和回滚指针列(roll_pointer)（7字节）
• InnoDB如果没有定义主键，每行还会增加一个6字节的row_id列

隐藏列&row_id:

• row_id
  我们知道InnoDB存储引擎存储数据由一个B+树完成，而形成B+树最终要的就是要有一个可以作为主键的主键值，这个值应该是NOT NULL,UNIQUE（非空唯一）的，但是如果我们创建的表没有适合用来作为主键的字段，那么InnoDB就会隐式的添加一个字段，这个字段来作为主键值构造B+树，而这个隐式的数据就存放在row_id这个位置中，所以row_id字段不是必须的。

• trx_id
  这个字段记录事务id，表示这个数据是由哪个事务生成的，InnoDB存储引擎支持事务，这也是InnoDB安全性高的主要原因，这个字段必须存在。

• roll_pointer
  这个字段存储的是一个地址，该地址指向这行数据修改前的记录，因为每次对某条聚簇索引的数据进行改动时，都会把旧版本的记录写入到undo日志中，而这个隐藏字段的指针指向旧版本记录，也叫回滚指针。这也是事务的内容，也是InnoDB安全性高的原因之一。这个列也是必须存在的。

Compact行记录格式

Compact行记录是在MySQL 5.0中引入的，其设计目标是高效地存储数据。一个页中存放的行数据越多，其性能就越高。

Compact行记录格式如下：

从图中可以直观地看到一行数据可分为额外信息和真实数据两部分。

• 记录的额外信息为了更好的管理数据而添加的一些信息，分为变长字段长度列表，NULL值列表和记录头信息三部分。

变长字段长度列表：
首部是一个非NULL变长字段长度列表，并且其是按照列的顺序逆序放置的，其长度为：

• 1字节：若列的长度小于255字节
• 2字节：大列的长度大于255字节

变长字段的长度最大不可以超过2字节，因为MySQL数据库中varchar类型的最大长度限制为65535

NULL标志位：

• 该位用来指示该行数据中是否有NULL值（以二进制表示），哪一个列的值为NULL，其哪一位的之就为1，反之则为0
• 该部分所占的字节应该为1字节

记录头信息，其格式如下所示，共5个字节：

名称	大小 (bit)	描述
预留位1	1	没有使用
预留位2	1	没有使用
delete_mask	1	标记该记录是否被删除
min_rec_mask	1	B+树里每一层的非叶子节点里的最小值都有这个标记
n_owned	4	表示当前记录拥有的记录数
heap_no	13	表示当前记录在记录堆的位置信息
record_type	3	标识当前记录的类型：0代表的是普通类型，1代表的是B+树非叶子节点，2代表的是最小值数据，3代表的是最大值数据。
next_record	16	表示下一条记录的相对位置

接下来是存储的真实数据部分,其第一部分包含三个隐藏列，其格式如下所示：

• DB_ROW_ID：该字段占6个字节，用于标识一条记录
• DB_TRX_ID：该字段占6个字节，其值为事务ID
• DB_ROLL_PTR：该字段占7个字节，其值为回滚指针

compact格式分析

compact格式分析-知乎

Redundant行记录格式

---

Compact行记录格式

MySQL中的COMPACT行格式

索引

建一张index_demo做示例表：

CREATE TABLE index_demo(
    c1 INT,
    c2 INT,
    c3 CHAR(1),
    PRIMARY KEY(c1)
) ROW_FORMAT = Compact;

Mysql会为此表的数据创建以下列：

• record_type ：记录头信息的一项属性，表示记录的类型， 0 表示普通记录、2 表示最小记录、3 表示最
  大记录、1 为索引；
• next_record ：记录头信息的一项属性，表示下一条地址相对于本条记录的地址偏移量，为了方便大家理
  解，我们都会用箭头来表明下一条记录是谁。
• 各个列的值：这里只记录在index_demo 表中的三个列，分别是c1 、c2 和c3 。
• 其他信息：除了上述3种信息以外的所有信息，包括其他隐藏列的值以及记录的额外信息。

record_type属性，它的各个取值代表的意思如下：

• 0：普通的用户记录
• 1：目录项记录
• 2：当前页数据的最小记录
• 3：当前页数据的最大记录

InnoDB中的索引建立方案

InnoDB 数据页的7个组成部分，各个数据页可以组成一个双向链表，而每个数据页中的记录会按照主键值从小到大的顺序组成一个单向链表，每个数据页都会为存储在它里边儿的记录生成一个页目录。在通过主键查找某条记录的时候，可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。

index_demo 正实图示：

竖起来如下：

把一些记录放到页里面的示意图如下（见颜色知其意）：：

这些记录在一个数据页里，按照主键值的大小串联成一个单向链表。

页中的字段描述：

当一页的容量已经满了（16KB ），此时需要分配一个新的数据页：

有多列数据加入后，索引可能变成这样：

这样遍历查找起来页非常慢，相当于遍历一个链表。因此如果想从这么多页中根据主键值快速定位某些记录所在的页，就需要给它们做个目录，每个页对应一个目录项，每个目录项包括下边两个部分：

• 页的用户记录中最小的主键值，用key 来表示；
• 页号，用page_no 表示。

针对数据页做的简易目录搞定。这个目录有一个别名，称为索引。

将页目录单独作为一页数据：

目录项记录与普通用户记录的不同点：

• 目录项记录的record_type 值是1，而普通用户记录的record_type 值是0；
• 目录项记录只有主键值和页的编号两个列，而普通的用户记录的列包括额外的记录的额外信息（根据定义的行格式确定）与记录的真实数据（隐藏列+真实数据）；
• 记录头信息中的min_rec_mask 属性，只有在存储目录项记录的页中的主键值最小的目录项记录的min_rec_mask 值为1 ，其他别的记录的min_rec_mask 值都是0 。

有多列数据加入后，Innodb索引将提取出一个索引页目录项来加快检索的时间（record_type值是1）：

从图中可以看出来，我们新分配了一个编号为30的页来专门存储目录项记录。这里再次强调一遍目录项记录和普通的用户记录的不同点：

• 目录项记录的record_type值是1，而普通用户记录的record_type值是0
• 目录项记录只有主键值和页的编号两个列，而普通的用户记录的列是用户自己定义的，可能包含很多列，另外还有InnoDB自己添加的隐藏列
• 还记得我们之前在唠叨记录头信息的时候说过一个叫min_rec_mask的属性么，只有在存储目录项记录的页中的主键值最小的目录项记录的min_rec_mask值为1，其他别的记录的min_rec_mask值都是0？

当目录项记录页一页塞不下的时候，目录项记录页再继续分裂：

如果我们表中的数据非常多则会产生很多存储目录项记录的页，那我们怎么根据主键值快速定位一个存储目录项记录的页呢：最终为这些存储目录项记录的页再生成一个更高级的目录，就像是一个多级目录一样，大目录里嵌套小目录，小目录里才是实际的数据，所以现在各个页的示意图就是这样子：

最终B+树索引的数据结构简化：

MyISAM中的的索引建立方案

InnoDB 中索引即数据，也就是聚簇索引的那棵B+树的叶子节点中已经把所有完整的用户记录都包含了，而MyISAM 的索引方案虽然也使用树形结构，但是却将索引和数据分开存储：

MyISAM将表中的记录按照记录的插入顺序单独存储在一个文件中，称之为数据文件。这个文件并不划分为若干个数据页，有多少记录就往这个文件中塞多少记录。然后可以通过行号而快速访问到一条记录。

MyISAM 记录也需要记录头信息来存储一些额外数据，以index_demo 表为例，这个表中的记录使用MyISAM 作为存储引擎在存储空间中的表示：

由于在插入数据的时候并没有刻意按照主键大小排序，所以并不能在这些数据上使用二分法进行查找。使用MyISAM 存储引擎的表会把索引信息另外存储到另一个文件中，称为索引文件。

MyISAM 会单独为表的主键创建一个索引，只不过在索引的叶子节点中存储的不是完整的用户记录，而是主键值 + 行号的组合，也就是先通过索引找到对应的行号，再通过行号去找对应的记录！

这一点和InnoDB 是完全不相同的，在InnoDB 存储引擎中，只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录，而在MyISAM 中却需要进行一次回表操作，意味着MyISAM 中建立的索引相当于全部都是二级索引！

如果有需要的话，也可以对其它的列分别建立索引或者建立联合索引，原理和InnoDB 中的索引差不多，不过在叶子节点处存储的是相应的列 + 行号。这些索引也全部都是二级索引。

索引总结事项

• 建立索引的要求：下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值；给所有的页建立一个目录项。
• InnoDB索引分类：
  • 聚簇索引：以主键值的大小作为页和记录的排序规则，在叶子节点存储的是表中的所有列（真实数据）；
  • 二级索引（辅助索引）：以索引列的大小作为页和记录的排序规则，在叶子节点存储的是索引列+主键；
  • 联合索引：同时以多个列的大小作为排序规则，叶子节点存储的是索引列+主键，本质上也是一个二级索引。
• B+树索引注意事项：
  • 根页面万年不动窝：根节点一旦创捷就不会再移动；
  • 保证内节点中目录项记录的唯一性；
  • 一个页面最少存储2条记录，以避免存储空间浪费。
• MyISAM存储引擎：采用数据和索引分开存储这种存储引擎的索引全部是二级索引，叶子节点存储的是列+主键。

---

B+树索引的原理
B+树与B树对比
B+树索引介绍及对比(原文)

面试题-B+树高计算

索引

存储方式区分类型

根据存储方式的不同，MySQL 中常用的索引在物理上分为 B-树索引和 HASH 索引两类，两种不同类型的索引各有其不同的适用范围。

B-树索引

B-树索引又称为 BTREE 索引，目前大部分的索引都是采用 B-树索引来存储的。

B-树索引是一个典型的数据结构，其包含的组件主要有以下几个：

• 叶子节点：包含的条目直接指向表里的数据行。叶子节点之间彼此相连，一个叶子节点有一个指向下一个叶子节点的指针。
• 分支节点：包含的条目指向索引里其他的分支节点或者叶子节点。
• 根节点：一个 B-树索引只有一个根节点，实际上就是位于树的最顶端的分支节点。
  基于这种树形数据结构，表中的每一行都会在索引上有一个对应值。因此，在表中进行数据查询时，可以根据索引值一步一步定位到数据所在的行。

B-树索引可以进行全键值、键值范围和键值前缀查询，也可以对查询结果进行 ORDER BY 排序。但 B-树索引必须遵循左边前缀原则，要考虑以下几点约束：

• 查询必须从索引的最左边的列开始。
• 查询不能跳过某一索引列，必须按照从左到右的顺序进行匹配。
• 存储引擎不能使用索引中范围条件右边的列。

哈希索引

哈希（Hash）一般翻译为“散列”，也有直接音译成“哈希”的，就是把任意长度的输入（又叫作预映射，pre-image）通过散列算法变换成固定长度的输出，该输出就是散列值。

哈希索引也称为散列索引或 HASH 索引。MySQL 目前仅有 MEMORY 存储引擎和HEAP存储引擎支持这类索引。其中，MEMORY 存储引擎可以支持 B-树索引和 HASH 索引，且将 HASH 当成默认索引。

HASH 索引不是基于树形的数据结构查找数据，而是根据索引列对应的哈希值的方法获取表的记录行。哈希索引的最大特点是访问速度快，但也存在下面的一些缺点：

• MySQL 需要读取表中索引列的值来参与散列计算，散列计算是一个比较耗时的操作。也就是说，相对于 B-树索引来说，建立哈希索引会耗费更多的时间。
• 不能使用 HASH 索引排序。
• HASH 索引只支持等值比较，如“=”“IN()”或“”。
• HASH 索引不支持键的部分匹配，因为在计算 HASH 值的时候是通过整个索引值来计算的。

哈希索引简单概述

Hash索引和B+树索引对比：

• hash索引底层实现：hash索引底层是hash表，进行查找时，调用一次hash函数就可以获得相应的键值，之后进行回表查询获得实际数据。

• B+树索引底层实现：B+树索引底层是一个多路平衡查找树，对于每一次的查询都是从根节点出发，查找到叶子节点方可获的所查询的键值，然后根据查询判断是否需要回表查询。

Hash索引和B+树索引的区别：

1、hash索引使用的是hash表结构，它将索引的值通过hash函数映射到一个固定大小的bucket桶中，然后在桶内查找。B+树索引使用的是多路平衡树结构，将索引键的值按照顺序保存到树节点中，并通过节点之间的指针进行查找

2、查询效率方面：哈希索引在等值查询上具有较好的性能，可以在常数时间复杂度上直接定位到目标数据，但对于范围查询和排序操作较差，因为原先是有序的键值，经过hash算法之后，存储位置随机分布。而B+树索引在范围查找和排序操作上更加高效，因为他是按照顺序存储的数据，可以有效的支持范围查找，

3、哈希索引对磁盘存储的利用效率不高，因为数据存储是随机分布的，可能会导致磁盘间的随机访问。而B+树索引节点是有序存储的，有利于磁盘的顺序访问。减少磁盘的IO次数，提高查询效率

4、hash索引在插入和删除上比较简单，只需要hash函数确定桶的位置，插入删除记录即可。而B+树的插入删除需要维护树平衡性，可能需要进行节点的拆分和节点的合并，相对来说更加复杂

补充知识点：

InnoDB自适应哈希索引：InnoDB存储引擎内部自己去监控索引表，如果监控到某个索引经常用，那么就认为是热数据，然后内部自己创建一个hash索引，称之为自适应哈希索引。创建以后，如果下次又查询到这个索引，那么直接通过hash算法推导出记录的地址，直接一次就能查到数据，比重复去B+tree索引中查询三四次节点的效率高了不少（只能等值搜索）。

AHI是 InnoDB 存储引擎的一种索引优化技术，它可以根据访问模式实时地调整和优化哈希索引结构，提高查询速度和效率。AHI 运行时会动态调整它的大小，以便于它能够容纳正在访问的数据块的索引，并且自动调整索引的深度，以便于在存储数据块很多的情况下仍然能够保持高效的查询性能。
AHI 的分区个数是由 InnoDB 存储引擎内部计算和调整的，通常取决于数据库的访问热度、数据量、CPU 和内存等资源的使用情况。在大多数情况下，用户不需要手动地设定或调整 AHI 的分区个数，而是让 InnoDB 自行管理和优化。

-- 是否开启自适应哈希索引功能，默认值ON开启
show VARIABLES like 'innodb_adaptive_hash_index';

-- 开启
SET GLOBAL innodb_adaptive_hash_index = ON;

-- 关闭
SET GLOBAL innodb_adaptive_hash_index = OFF;

Hash索引寻址示例：

逻辑区分类型

普通索引

普通索引是 MySQL 中最基本的索引类型，它没有任何限制，唯一任务就是加快系统对数据的访问速度。

唯一索引

唯一索引与普通索引类似，不同的是创建唯一性索引的目的不是为了提高访问速度，而是为了避免数据出现重复。
唯一索引列的值必须唯一，允许有空值。如果是组合索引，则列值的组合必须唯一。
创建唯一索引通常使用 UNIQUE 关键字。

主键索引

顾名思义，主键索引就是专门为主键字段创建的索引，也属于索引的一种。
主键索引是一种特殊的唯一索引，不允许值重复或者值为空。
创建主键索引通常使用 PRIMARY KEY 关键字。不能使用 CREATE INDEX 语句创建主键索引。

前缀索引

前缀索引能有效减小索引文件的大小，让每个索引页可以保存更多的索引值，从而提高了索引查询的速度。但前缀索引也有它的缺点，不能在 order by 或者 group by 中触发前缀索引，也不能把它们用于覆盖索引。

空间索引

空间索引是对空间数据类型的字段建立的索引，使用 SPATIAL 关键字进行扩展。
创建空间索引的列必须将其声明为 NOT NULL，空间索引只能在存储引擎为 MyISAM 的表中创建。
空间索引主要用于地理空间数据类型 GEOMETRY。对于初学者来说，这类索引很少会用到。

全文索引

全文索引主要用来查找文本中的关键字，只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。仅支持InNoDB与MyISAM引擎。全文索引允许在索引列中插入重复值和空值。不过对于大容量的数据表，生成全文索引非常消耗时间和硬盘空间。
创建全文索引使用 FULLTEXT 关键字。

全文索引解析

聚集索引：聚集索引就是以主键创建的索引，在叶子节点存储的是表中的数据。（Innodb存储引擎）
非聚集索引：非聚集索引就是以非主键创建的索引，在叶子节点存储的是主键和索引列。（Innodb存储引擎）

前缀索引补充：

在MySQL中，使用前缀索引有以下几个主要原因：

• 节省存储空间：前缀索引允许我们只对列值的一部分进行索引，而不是对整个列值进行索引。对于长字符串列或文本列，完整列值可能会很长，如果对整个列值进行索引，将会占用更多的存储空间。使用前缀索引可以减少索引的长度，从而节省存储空间。

• 提高索引效率：较短的索引长度可以减少索引的高度和宽度，从而提高索引的效率。更短的索引长度意味着在索引中可以容纳更多的索引项，减少了磁盘I/O和内存消耗。这对于大型表和频繁查询的表格特别有益。

• 缓解索引选择性问题：索引的选择性是指索引中不同值的数量与总行数之间的比率。较低的选择性意味着索引中的重复值较多，可能导致索引的效率下降。对于较长的列值，选择性可能会降低。使用前缀索引可以减少索引的选择性问题，因为只对列值的一部分进行索引，减少了重复值的数量。

使用前缀索引也会带来一些潜在的问题。例如，较短的索引长度可能导致索引的精确性下降，查询结果可能不够准确。此外，较短的索引长度也可能导致索引的覆盖能力降低，需要进行更多的回表操作。因此，在使用前缀索引时，需要根据具体的数据和查询需求进行权衡和测试，以确保索引的有效性和性能提升。

MySQL之前缀索引的使用

实际使用区分类型

单列索引

单列索引就是索引只包含原表的一个列。在表中的单个字段上创建索引，单列索引只根据该字段进行索引。
单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。

多列索引

组合索引也称为复合索引或多列索引或联合索引。相对于单列索引来说，组合索引是将原表的多个列共同组成一个索引。多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。但是，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。
例如，在表中的 id、name 和 sex 字段上建立一个多列索引，那么，只有查询条件使用了 id 字段时，该索引才会被使用。

索引类型原文

索引失效情况

1. 不使用索引列进行查询：如果查询中没有使用到任何索引列，MySQL将无法使用索引进行优化查询，这样查询的效率可能会较低。为了避免这种情况，应该在查询中使用适当的索引列。

2. 数据类型不匹配：如果查询中使用了索引列但数据类型不匹配，MySQL将无法有效使用索引。例如，将字符串类型的列与数值类型进行比较，或将日期类型的列与文本进行比较。在设计表结构时，应该确保索引列和查询的数据类型匹配。

3. 前缀索引的使用不当：在某些情况下，为了提高索引的效率和节省存储空间，可以使用前缀索引。然而，如果使用前缀索引的长度过短，那么查询的结果可能会不准确。此外，如果使用前缀索引的列进行排序或分组操作，也会导致索引失效。

4. 使用函数或表达式进行查询：如果在查询中使用了函数或表达式，MySQL将无法使用索引进行优化查询。例如，select * from table where year(date_column) = 2021; 在这种情况下，将无法使用date_column上的索引。

5. 索引列的顺序不正确：复合索引是指包含多个列的索引。如果查询的条件中的列的顺序与复合索引的列的顺序不一致，MySQL将无法使用索引进行优化查询。因此，在建立复合索引时应该根据查询条件中最常用的列进行排序。

6. 数据更新频繁：索引是为了提高查询性能而创建的，但在数据更新频繁的情况下，索引会导致插入、更新和删除操作的性能下降。因此，在设计表结构时需要权衡查询和更新的频率，并根据实际情况来确定是否创建索引。

7. 索引过多或过少：如果表中的索引过多，可能会导致查询性能下降。每个索引都需要额外的存储空间，并且在数据更新时需要维护索引的一致性。另一方面，如果表中没有足够的索引，某些查询可能会变得很慢。因此，在设计表结构时需要合理地选择索引。

索引并不是一定会走,有一些特殊情况，索引能触发但是不会匹配:单次查询超过30%, IN子表数量过多

索失效及使用示例

联合索引在使用的时候一定要注意顺序，一定要注意符合最左匹配原则。

最左匹配原则：在通过联合索引检索数据时，从索引中最左边的列开始，一直向右匹配，如果遇到范围查询(>、<、between、like等)，就停止后边的匹配。

假如对字段 (a, b, c) 建立联合索引，现在有这样一条查询语句：

where a > xxx and b=yyy and c=zzz
where a like 'xxx%' and b=yyy and c=zzz

在这个条件语句中，只有a用到了索引，后面的b,c就不会用到索引。这就是“如果遇到范围查询(>、<、between、like等)，就停止后边的匹配。”的意思。

还是假如对字段 (a, b, c) 建立联合索引，

如下查询语句可以使用到索引：

where a = xxx
where a = xxx and b = xxx
where a = xxx and b = xxx and c = xxx
where a like 'xxx%'
where a > xxx
where a = xxx order by b
where a = xxx and b = xxx order by c
group by a

如下查询条件也会使用索引：

where b = xxx and a = xxx
where a = xxx and c = xxx and b = xxx

上面sql中，虽然b和a的顺序换了，但是mysql中的优化器会帮助我们调整顺序。

如下查询条件只用到联合索引的一部分：

where a = xxx and c = xxx   --可以用到 a 列的索引，用不到 c 列索引。

where a like 'xxx%' and b = xxx --可以用到 a 列的索引，用不到 b 列的索引。

where a > xxx and b = xxx --可以用到 a 列的索引，用不到 b 列的索引。

如下查询条件完全用不到索引：

where b = xxx
where c = xxx
where a like '%xxx'			-- 不满足最左前缀
where d = xxx order by a	-- 出现非排序使用到的索引列 d 
where a + 1 = xxx	-- 使用函数、运算表达式及类型隐式转换等

如何选择合适的联合索引

1. where a = xxx and b = xxx and c = xxx 如果我们的查询是这样的，建索引时，就可以考虑将选择性高的列放在索引的最前列，选择性低的放后边。

2. 如果是 where a > xxx and b = xxx 或 where a like 'xxx%' and b = xxx 这样的语句，可以对 (b, a) 建立索引。

c. 如果是 where a = xxx order by b 这样的语句，可以对 (a, b) 建立索引。

一张表最多支持多少个索引? 一个表最多16个索引,最大索引长度256字节。
一个sql中的一张表，最多只会走一个索引！

联合索引失效

索引下推

索引下推是索引下推是 MySQL 5.6 及以上版本上推出的，用于对查询进行优化。索引下推是把本应该在 server 层进行筛选的条件，下推到存储引擎层来进行筛选判断，这样能有效减少回表。

举例说明：首先建立联合索引（name，age），现在有这样一个查询语句：

select *  from t_user where name like 'L%' and age = 17;

这条语句从最左匹配原则上来说是不符合的，原因在于只有name用的索引，但是age并没有用到。

不用索引下推的执行过程：

• 第一步：利用索引找出name带’L’的数据行：LiLei、Lili、Lisa、Lucy 这四条索引数据
• 第二步：再根据这四条索引数据中的 id 值，逐一进行回表扫描，从聚簇索引中找到相应的行数据，将找到的行数据返回给 server 层。
• 第三步：在server层判断age = 17,进行筛选，最终只留下 Lucy 用户的数据信息。

举例：

不支持索引下推的联合索引：例如索引(name,age)，查询name like ‘z%’ and age=？，模糊查询导致age无序。在联合索引树查询时只会查name，后面的age乱序不能直接进行条件判断，必须回表后再判断age。

而支持索引下推的联合索引：例如索引(name,age)，查询name like ‘z%’ and age and address，在联合索引树查询时不止查name，还会判断后面的age，过滤后再回表判断address。

CREATE INDEX idx_name_age ON student(name,age);
#索引失败；非覆盖索引时，左模糊导致索引失效
EXPLAIN SELECT * FROM student WHERE name like '%bc%' AND age=30;
#索引成功；MySQL5.6引入索引下推，where后面的name和age都在联合索引里，可以又过滤又索引，不用回表，索引生效
EXPLAIN SELECT * FROM student WHERE `name` like 'bc%' AND age=30;
#索引成功；name走索引，age用到索引下推过滤，classid不在联合索引里，需要回表。
EXPLAIN SELECT * FROM student WHERE `name` like 'bc%' AND age=30 AND classid=2;

好处: 某些场景下ICP可以大大减少回表次数，提高性能。ICP可以减少存储引擎必须访问基表的次数和MySQL服务器必须访问存储引擎的次数。但是，ICP的 加速效果 取决于在存储引擎内通过 ICP筛选 的数据的比例。

ICP的使用条件

• 表的访问类型为 range 、 ref 、 eq_ref 或者 ref_or_null 。
• 存储引擎：ICP可以用于InnDB和MyISAM存储引擎
• 必须二级索引：对于InnoDB表，ICP仅用于二级索引。ICP的目标是减少全行读取次数，从而减少I/O操作。
• 必须不是覆盖索引：当SQL使用覆盖索引时，不支持ICP优化方法。因为这种情况下使用ICP不会减少I/O。
• 相关子查询的条件不能使用ICP
• 必须5.6版本及以上：MySQL 5.6版本引入并默认开启，之前版本不支持索引下推。
• 必须where字段在索引列中：并非全部where条件都可以用ICP筛选，如果where条件的字段不在索引列中，还是要读取整表的记录到server端做where过滤。

使用索引下推的执行过程：

• 第一步：利用索引找出name带’L’的数据行：LiLei、Lili、Lisa、Lucy 这四条索引数据
• 第二步：根据 age = 17 这个条件，对四条索引数据进行判断筛选，最终只留下 Lucy 用户的数据信息。
  （注意：这一步不是直接进行回表操作，而是根据 age = 17 这个条件，对四条索引数据进行判断筛选）
• 第三步：将符合条件的索引对应的 id 进行回表扫描，最终将找到的行数据返回给 server 层。

比较二者的第二步我们发现，索引下推的方式极大的减少了回表次数。

索引下推需要注意的情况：下推的前提是索引中有 age 列（包含在组合索引中）信息，如果是其它条件，如 gender = 0（不包含在组合索引中），这个即使下推下来也没用。

开启索引下推：

索引下推是 MySQL 5.6 及以上版本上推出的，用于对查询进行优化。默认情况下，索引下推处于启用状态。我们可以使用如下命令来开启或关闭。

set optimizer_switch='index_condition_pushdown=off';  -- 关闭索引下推
set optimizer_switch='index_condition_pushdown=on';  -- 开启索引下推

当使用索引条件下推是，EXPLAIN语句输出结果中Extra列内容显示为Using index condition。

联合索引、覆盖索引和索引下推

B+树索引详细解析
索引知识点

MySQL索引综合知识点-知乎DB哥

索引建立规范

1、索引的数量要控制：

• 单张表中索引数量不超过5个
• 单个索引中的字段数不超过5个
• 对字符串使⽤用前缀索引，前缀索引长度不超过8个字符
• 建议优先考虑前缀索引，必要时可添加伪列并建立索引

2、主键准则

• 表必须有主键
• 不使用更新频繁的列作为主键
• 尽量不选择字符串列作为主键
• 不使用UUID MD5 HASH这些作为主键(数值太离散了)
• 默认使⽤非空的唯一键作为主键
• 建议选择自增或发号器

3、重要的SQL必须被索引，比如：

• UPDATE、DELETE语句的WHERE条件列
• ORDER BY、GROUP BY、DISTINCT的字段

4、多表JOIN的字段注意以下：

• 区分度最大的字段放在前面
• 核⼼SQL优先考虑覆盖索引
• 避免冗余和重复索引
• 索引要综合评估数据密度和分布以及考虑查询和更新比例

5、索引禁忌

• 不在低基数列上建立索引，例如“性别”
• 不在索引列进行数学运算和函数运算

6、尽量不使用外键：外键用来保护参照完整性，可在业务端实现

8、新建的唯一索引必须不能和主键重复

9、索引字段的默认值不能为NULL，要改为其他的default或者空。NULL非常影响索引的查询效率

10、反复查看与表相关的SQL，符合最左前缀的特点建立索引。多条字段重复的语句，要修改语句条件字段的顺序，为其建立一条联合索引，减少索引数量

11、能使用唯一索引就要使用唯一索引，提高查询效率

12、研发要经常使用explain，如果发现索引选择性差，必须让他们学会使用

Mysql修改表结构、添加索引会锁表吗

当数据量较小时，即使锁表也没有关系，其他的DML等待执行即可，业务中可以以一千万作为一个判定值，可以直接执行修改表结构操作，短暂性锁表无伤大雅

当数据量大时，例如几千万、几亿数据，mysql建立索引默认需要锁住整个表，然后再建立索引，由于数据量巨大，从磁盘读取数据创建索引所需时间会比较长，此时业务是不能停的，因此不允许这样操作，会导致业务无法使用该数据库。

在mysql 5.7版本及以后支持了设置algrothm=default/copy/inplace
在mysql8.0版本支持了instant方式，直接修改元数据完成，不修改表数据，建议使用8.0版本mysql服务器（亲测8.0版本一千万条数据下，创建索引，查询数据不走索引不会阻塞，走索引就会阻塞等待，千万行数据增加字段45毫秒执行完了，真快，看来没有直接更新表内数据,创建索引同时还允许插入数据，不阻塞）

algrothm:

• default 默认算法，会锁表

• COPY 算法：允许DQL不支持DML，能读不能写，操作是创建一个新的空表，然后从旧表逐个读数据修改表结构写入新表，最后重命名

• inplace算法：操作避免复制表数据，但可能就地重建表。在操作的准备和执行阶段，表上的独占元数据锁可能会被短暂地占用。 支持DQL、DML，操作时能读写

• INSTANT：操作只修改数据字典中的元数据。在操作的执行阶段，表上的独占元数据锁可能会被短暂地占用。表数据不受影响，因此操作是即时的。允许并发DML。(在MySQL 8.0.12中引入)

algrothm参数为可选参数，默认使用default算法，若支持copy算法则默认用copy算法，若支持inplace算法则默认使用inplace算法，默认选取优先级 inplace>copy>default

添加索引algrothm参数使用示例

语法：ALTER TABLE 表名 ALGORITHM=算法, ADD INDEX 索引名称(字段名称)

使用INPLACE，NONE时不阻塞其他事务的DML操作。
使用COPY，EXCLUSIVE时，会阻塞其他事务的DML操作。当DDL事务提交后，其他事务才能正常DML操作。

SQL编写规范

(1) sql语句尽可能简单大的sql想办法拆成小的sql语句(充分利用QUERY CACHE和充分利用多核CPU)
(2) 事务要简单，整个事务的时间长度不要太长
(3) 避免使用触发器、函数、存储过程
(4) 降低业务耦合度，为sacle out、sharding留有余地
(5) 避免在数据库中进⾏数学运算(MySQL不擅长数学运算和逻辑判断)
(4) 不要用select *，查询哪几个字段就select 这几个字段
(5) sql中使用到OR的改写为用 IN() (or的效率没有in的效率高)
(6) in里面数字的个数建议控制在1000以内
(7) limit分页注意效率。Limit越大，效率越低。可以改写limit，比如例子改写：
select id from tlimit 10000, 10; => select id from t where id > 10000 limit10;
(9) 使用union all替代union
(10) 避免使⽤大表的JOIN
(11) 使用group by 分组、自动排序
(12) 对数据的更新要打散后批量更新，不要一次更新太多数据
(13) 减少与数据库的交互次数
(13) 注意使用性能分析工具
Sql explain / showprofile / mysqlsla
(14) SQL语句要求所有研发，SQL关键字全部是大写，每个词只允许有一个空格
(15) SQL语句不可以出现隐式转换，比如 select id from 表 where id=‘1’
(16) IN条件里面的数据数量要少，我记得应该是500个以内，要学会使用exist代替in，exist在一些场景查询会比in
快
(19) 不使用负向查询，如not in/like
(19) 关于分页查询：程序里建议合理使用分页来提高效率limit，offset较大要配合子查询使用
(20) 禁止在数据库中跑大查询
(21) 使⽤预编译语句，只传参数，比传递SQL语句更高效；一次解析，多次使用；降低SQL注入概率
(22) 禁止使⽤order by rand()
(23) 禁⽌单条SQL语句同时更新多个表

exlpain字段解析

通过EXPLAIN，可以分析出以下结果：

表的读取顺序
数据读取操作的操作类型
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询

• id表示查询语句的序号，自动分配，顺序递增，值越大，执行优先级越高。id相同时，优先级由上而下。

• select_type列
  select_type表示查询类型，常见的有SIMPLE简单查询、PRIMARY主查询、SUBQUERY子查询、UNION联合查询、UNION RESULT联合临时表结果等。

• table列
  table表示SQL语句查询的表名、表别名、临时表名。

• partitions列
  partitions表示SQL查询匹配到的分区，没有分区的话显示NULL。

• type列
  type表示表连接类型或者数据访问类型，就是表之间通过什么方式建立连接的，或者通过什么方式访问到数据的。具体有以下值，性能由好到差依次是：
  system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL

  • system
    当表中只有一行记录，也就是系统表，是 const 类型的特列。
  • const
    表示使用主键或者唯一性索引进行等值查询，最多返回一条记录。性能较好，推荐使用。
  • eq_ref
    表示表连接使用到了主键或者唯一性索引，下面的SQL就用到了user表主键id。
  • ref
    表示使用非唯一性索引进行等值查询。
  • ref_or_null
    表示使用非唯一性索引进行等值查询，并且包含了null值的行。
  • index_merge
    表示用到索引合并的优化逻辑，即用到的多个索引。
  • range
    表示用到了索引范围查询。
  • index
    表示使用索引进行全表扫描。
  • ALL
    表示全表扫描，性能最差。

• possible_keys列
  表示可能用到的索引列，实际查询并不一定能用到。

• key列
  表示实际查询用到索引列。

• key_len列
  表示索引所占的字节数。表示索引使用的字节数，根据这个值可以判断索引的使用情况，特别是在组合索引的时候，判断该索引有多少部分被使用到非常重要。

• ref列
  表示where语句或者表连接中与索引比较的参数，常见的有const（常量）、func（函数）、字段名。如果没用到索引，则显示为NULL。

• rows列
  表示执行SQL语句所扫描的行数。

• filtered列
  表示按条件过滤的表行的百分比。

• Extra列
  表示一些额外的扩展信息，不适合在其他列展示，却又十分重要。

  • Using filesort
    表示使用了外部排序，即排序字段没有用到索引。排序时没有按照建立复合索引字段的顺序进行，因此产生了外部的索引排序，效率低。
  • Using temporary
    表示用到了临时表，,一般出现于排序， 分组和多表 join 的情况。分组时没有按照建立复合索引字段的顺序进行，因此产生了临时表和外部的索引排序。效率低
  • Using where
    表示使用了where条件搜索，但没有使用索引。
  • Using index
    表示用到了覆盖索引，即在索引上就查到了所需数据，无需二次回表查询，性能较好。
  • Using join buffer
    表示在进行表关联的时候，没有用到索引，使用了连接缓存区存储临时结果。出现在当两个连接时驱动表(被连接的表,left join 左边的表。inner join 中数据少的表) 没有索引的情况下。
  • Using index condition
    表示用到索引下推的优化特性。
  • impossible where（一般重要指标）
    where子句的值总是false，不能用来获取任何元组。例如 where num > 1 and num < 0。
  • Distinct ：一旦mysql找到了与行相联合匹配的行，就不再搜索了。
  • Not exists ：mysql优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了。
  • Range checked for each Record（index map:#） ：没有找到理想的索引，因此对从前面表中来的每一个行组合，mysql检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一。

字段解析及sql优化手段

使用案例及字段解析

explain案例解析

explain案例解析

Explain信息中Extra字段解释

---

profile分析执行耗时

explain只是看到SQL的预估执行计划，如果要了解SQL真正的执行线程状态及消耗的时间，需要使用profiling。开启profiling参数后，后续执行的SQL语句都会记录其资源开销，包括IO，上下文切换，CPU，内存等等，我们可以根据这些开销进一步分析当前慢SQL的瓶颈再进一步进行优化。

EXPLAIN 可以看 INSERT INTO 的执行语句吗？
不可以 ,但是可以改写语句 绕一下。

一、 使用 SELECT INTO 语句：将 INSERT INTO 语句转换为 SELECT INTO 语句，然后使用 EXPLAIN 命令查看执行计划。

例如，将 INSERT INTO 语句：

INSERT INTO table1 (column1, column2, column3) 
VALUES (value1, value2, value3);

转换为 SELECT INTO 语句：

SELECT * FROM (SELECT value1 AS column1, value2 AS column2, value3 AS column3) AS temp 
INTO table1;

然后使用 EXPLAIN 命令查看执行计划：

EXPLAIN SELECT * FROM (SELECT value1 AS column1, value2 AS column2, value3 AS column3) AS temp 
INTO table1;

二、 使用 INSERT INTO … SELECT 语句：将 INSERT INTO 语句改写为 INSERT INTO … SELECT 语句，然后使用 EXPLAIN 命令查看执行计划。

例如，将 INSERT INTO 语句：

INSERT INTO table1 (column1, column2, column3) 
VALUES (value1, value2, value3);

改写为 INSERT INTO … SELECT 语句：

INSERT INTO table1 (column1, column2, column3) 
SELECT value1, value2, value3 FROM DUAL;

然后使用 EXPLAIN 命令查看执行计划：

EXPLAIN INSERT INTO table1 (column1, column2, column3) 
SELECT value1, value2, value3 FROM DUAL;

ACID的原理

mysql将数据存储到数据库之前都是先通过日志的方式来存储数据，因为日志的存储是顺序存储，可以通过偏移量来控制或者查找，而数据库的持久化存储，是见缝插针，这样可能最大化利用磁盘空间，存储完还需要记录数据的地址，所以相比日志存储比较慢。

ACID靠什么保证的？

• A：原子性通过Redo log和Undo log，重做和回滚。它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql。如果事务提交了，那么就会执行Redo log写到数据库，如果没有提交就会执行undo log。
• C：一致性由其他三大特性保证、程序代码要保证业务上的一致性。
• I： 隔离性由MVCC来保证。
• D ：持久性由内存+redo log来保证，mysql修改数据同时在内存和redo log记录这次操作，宕机的时候可以从redo log恢复。

日志

Mysql七种日志

日志类型	日志名称	解释
redo log	重做日志	用于恢复因宕机丢失未刷入磁盘的内存的数据
undo log	回滚日志	保证数据的原子性，保存事务发生前版本的数据，用于回滚
bin log	二进制日志	记录增删改时的日志，用于主从复制和数据库恢复
relay log	中继日志	主从复制过程中，从结点会将主节点的数据写到relaylog，然后
error log	错误日志	记录mysql启动、停止、运行过程中错误相关信息
slow query log	慢查询日志	记录执行成功的执行时间过长和没有使用索引的语句
general log	普通日志	记录服务器收到的命令

主要注意的是这三种日志：

• undo log（回滚日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC。
• redo log（重做日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复；
• binlog （归档日志）：是 Server 层生成的日志，主要用于数据备份和主从复制；

undo log

undo log 是一种用于撤销回退的日志。在事务没提交之前，MySQL 会先记录更新前的数据到 undo log 日志文件里面，当事务回滚时，可以利用 undo log 来进行回滚。

每当 InnoDB 引擎对一条记录进行操作（修改、删除、新增）时，要把回滚时需要的信息都记录到 undo log 里，比如：

• 在插入一条记录时，要把这条记录的主键值记下来，这样之后回滚时只需要把这个主键值对应的记录删掉就好了；
• 在删除一条记录时，要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了；
• 在更新一条记录时，要把被更新的列的旧值记下来，这样之后回滚时再把这些列更新为旧值就好了。

在发生回滚时，就读取 undo log 里的数据，然后做原先相反操作。

在使用 Undo log 时，需要注意：

• Undo log 的管理和清理：由于 Undo log 会占用存储空间，需要定期清理不再需要的 Undo log，以释放存储空间。

• 配置 Undo log 的大小：根据数据库的并发事务量和数据修改量，合理配置 Undo log 的大小，以确保足够的空间来存储 Undo log 信息。

• 定期备份 Undo log：为了避免数据丢失，定期备份 Undo log 文件，以便在需要恢复数据时使用。

undo log刷盘时机与配置都与redo log一致，可以通过修改MySQL配置文件my.cnf（在Unix/Linux系统中）或my.ini（在Windows系统中）来设置这个参数。例如：

[mysqld]
innodb_flush_log_at_trx_commit=1

redo log

redo log 是物理日志，记录了某个数据页做了什么修改，比如：对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新，每当执行一个事务就会产生这样的一条或者多条物理日志。

在事务提交时，只要先将 redo log 持久化到磁盘即可，可以不需要等到将缓存在 Buffer Pool 里的脏页数据持久化到磁盘。
当系统崩溃时，虽然脏页数据没有持久化，但是 redo log 已经持久化，接着 MySQL 重启后，可以根据 redo log 的内容，将所有数据恢复到最新的状态。

为了防止断电导致数据丢失的问题，当有一条记录需要更新的时候，InnoDB 引擎就会先更新内存（同时标记为脏页），然后将本次对这个页的修改以 redo log 的形式记录下来，这个时候更新就算完成了。

InnoDB 引擎会在适当的时候，由后台线程将缓存在 Buffer Pool 的脏页刷新到磁盘里，这就是 WAL （Write-Ahead Logging）技术。WAL 技术指的是， MySQL 的写操作并不是立刻写到磁盘上，而是先写日志，然后在合适的时间再写到磁盘上。

执行一个事务的过程中，产生的 redo log 也不是直接写入磁盘的，因为这样会产生大量的 I/O 操作，而且磁盘的运行速度远慢于内存。所以，redo log 也有自己的缓存—— redo log buffer，每当产生一条 redo log 时，会先写入到 redo log buffer。redo log buffer 默认大小 16 MB，可以通过 innodb_log_Buffer_size 参数动态的调整大小。

redo log buffer刷盘时机：

主要有下面几个时机：

• MySQL 正常关闭时；
• 当 redo log buffer 中记录的写入量大于 redo log buffer 内存空间的一半时，会触发落盘；
• InnoDB 的后台线程每隔 1 秒，将 redo log buffer 持久化到磁盘。
• 每次事务提交时都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘（这个策略可由 innodb_flush_log_at_trx_commit 参数控制）。

innodb_flush_log_at_trx_commit 可取的值有：0、1、2，默认值为 1，这三个值分别代表的策略如下：

• 当设置该参数为 0 时，表示每次事务提交时 ，还是将 redo log 留在 redo log buffer 中 ，该模式下在事务提交时不会主动触发写入磁盘的操作。
• 当设置该参数为 1 时，表示每次事务提交时，都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘，这样可以保证 MySQL 异常重启之后数据不会丢失。
• 当设置该参数为 2 时，表示每次事务提交时，都只是缓存在 redo log buffer 里的 redo log 写到 redo log 文件，注意写入到「 redo log 文件」并不意味着写入到了磁盘，因为操作系统的文件系统中有个 Page Cache，Page Cache 是专门用来缓存文件数据的，所以写入「 redo log文件」意味着写入到了操作系统的文件缓存。

参数值为 0 和 2 的刷盘时机

InnoDB 的后台线程每隔 1 秒会执行以下不同的操作：

• 针对参数 0 ：会把缓存在 redo log buffer 中的 redo log ，通过调用 write() 写到操作系统的 Page Cache，然后调用 fsync() 持久化到磁盘。所以参数为 0 的策略，MySQL 进程的崩溃会导致上一秒钟所有事务数据的丢失;
• 针对参数 2 ：调用 fsync，将缓存在操作系统中 Page Cache 里的 redo log 持久化到磁盘。所以参数为 2 的策略，较取值为 0 情况下更安全，因为 MySQL 进程的崩溃并不会丢失数据，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。

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

redo log的循环写：
默认情况下， InnoDB 存储引擎有 1 个重做日志文件组( redo log Group），「重做日志文件组」由有 2 个 redo log 文件组成，这两个 redo 日志的文件名叫 ：ib_logfile0 和 ib_logfile1 。在重做日志组中，每个 redo log File 的大小是固定且一致的

重做日志文件组是以循环写的方式工作的，从头开始写，写到末尾就又回到开头，相当于一个环形。
所以 InnoDB 存储引擎会先写 ib_logfile0 文件，当 ib_logfile0 文件被写满的时候，会切换至 ib_logfile1 文件，当 ib_logfile1 文件也被写满时，会切换回 ib_logfile0 文件。

binlog

MySQL 在完成一条更新操作后，Server 层还会生成一条 binlog，等之后事务提交的时候，会将该事物执行过程中产生的所有 binlog 统一写 入 binlog 文件。
binlog 文件是记录了所有数据库表结构变更和表数据修改的日志，不会记录查询类的操作，比如 SELECT 和 SHOW 操作。

binlog（二进制日志）有三种主要录入格式，分别是Statement、Row和Mixed格式。

Statement格式：

记录每个修改数据的SQL语句到binlog中，当从库执行这些binlog时，它会重放相同的SQL语句以达到与主库相同的数据状态。

• 优点：binlog文件相对较小，记录内容简洁。
• 缺点：由于SQL语句在不同环境下的执行可能产生不同的结果（如系统变量、函数值、时间戳等），存在一定的不一致性风险。此外，如果SQL包含不可重复读取的数据定义语言（DDL）或数据操作语言（DML），可能导致同步问题。

Row格式：

不记录SQL语句本身，而是记录每一行数据在变更前后的具体值。binlog包含了所有影响行的详细信息，包括表名、列名以及行的旧值和新值。

• 优点：完全避免了SQL语句在不同环境下的执行差异，保证了主从库间数据的一致性，尤其适用于复杂查询或者触发器导致的隐式更新。
• 缺点：binlog文件通常比Statement格式大得多，因为需要存储具体的行数据变化。

Mixed格式：

结合了Statement和Row两种格式的特点，是MySQL默认推荐的日志格式。
MySQL服务器根据SQL语句的具体类型和内容智能选择使用哪种格式记录binlog：
对于简单的SQL更新和不会导致数据不一致性的SQL语句，采用Statement格式记录；对于复杂的、可能引起数据不一致性的SQL，如包含用户自定义函数、不确定结果的查询，则采用Row格式记录。
此外,新版的MySQL中对row级别也做了一些优化,当表结构发生变化的时候,会记录语句而不是逐行记录。

binlog 刷盘时机

MySQL 给每个线程分配了一片内存用于缓冲 binlog ，该内存叫 binlog cache，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。

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

每个线程有自己 binlog cache，但是最终都写到同一个 binlog 文件

一个事务的 binlog 是不能被拆开的，因此无论这个事务有多大（比如有很多条语句），也要保证一次性写入。这是因为有一个线程只能同时有一个事务在执行的设定，所以每当执行一个 begin/start transaction 的时候，就会默认提交上一个事务，这样如果一个事务的 binlog 被拆开的时候，在备库执行就会被当做多个事务分段自行，这样破坏了原子性，是有问题的。

MySQL提供一个 sync_binlog 参数来控制数据库的 binlog 刷到磁盘上的频率：

• sync_binlog = 0 的时候，表示每次提交事务都只 write，不 fsync，后续交由操作系统决定何时将数据持久化到磁盘；
• sync_binlog = 1 的时候，表示每次提交事务都会 write，然后马上执行 fsync；
• sync_binlog =N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

write:将线程中的binlog cache刷到系统中的Page Cache；fsync将Page Cache持久化到磁盘文件

默认的设置是 sync_binlog = 0，为了提高写入的性能，一般会 sync_binlog 设置为 100~1000 中的某个数值。

redo log和undo log区别 ：

• redo log 记录了此次事务「完成后」的数据状态，记录的是更新之后的值；
• rundo log 记录了此次事务「开始前」的数据状态，记录的是更新之前的值；

redo log和binlog区别 ：

• redo log是属于innoDB层面，
  binlog属于MySQL Server层面的，这样在数据库用别的存储引擎时可以达到一致性的要求。

• redo log是物理日志，记录该数据页更新的内容；
  binlog是逻辑日志，记录的是这个更新语句的原始逻辑

• redo log是循环写，日志空间大小固定；
  binlog是追加写，是指一份写到一定大小的时候会更换下一个文件，不会覆盖。

• redo log作为异常宕机或者介质故障后的数据恢复使用。
  binlog可以作为恢复数据使用，主从复制搭建，

redo log(重做日志)和binlog(归档日志)。redo log是InnoDB存储引擎层的日志，binlog是MySQL Server层记录的日志， 两者都是记录了某些操作的日志(不是所有)自然有些重复（但两者记录的格式不同）。

两阶段提交的过程

在持久化 redo log 和 binlog 这两份日志的时候，如果出现半成功的状态(一方提交成功，一方提交失败)，就会造成主从环境的数据不一致性。这是因为 redo log 影响主库的数据，binlog 影响从库的数据，所以 redo log 和 binlog 必须保持一致才能保证主从数据一致。

MySQL 为了避免出现两份日志之间的逻辑不一致的问题，使用了「两阶段提交」来解决，两阶段提交其实是分布式事务一致性协议，它可以保证多个逻辑操作要不全部成功，要不全部失败，不会出现半成功的状态。

两阶段提交把单个事务的提交拆分成了 2 个阶段，分别是「准备（Prepare）阶段」和「提交（Commit）阶段」，每个阶段都由协调者（Coordinator）和参与者（Participant）共同完成。

MySQL 使用了内部 XA 事务，内部 XA 事务由 binlog 作为协调者，存储引擎是参与者。XA 事务将 redo log 的写入拆成了两个步骤：prepare 和 commit，中间再穿插写入binlog，具体如下：

• prepare阶段：将 XID（内部 XA 事务的 ID） 写入到 redo log，同时将 redo log 对应的事务状态设置为 prepare，然后将 redo log 持久化到磁盘（innodb_flush_log_at_trx_commit = 1 的作用）；
• commit阶段：把 XID 写入到 binlog，然后将 binlog 持久化到磁盘（sync_binlog = 1 的作用），接着调用引擎的提交事务接口，将 redo log 状态设置为 commit，此时该状态并不需要持久化到磁盘，只需要 write 到文件系统的 page cache 中就够了，因为只要 binlog 写磁盘成功，就算 redo log 的状态还是 prepare 也没有关系，一样会被认为事务已经执行成功；

在 MySQL如果异常重启后会按顺序扫描 redo log 文件，碰到处于 prepare 状态的 redo log，就拿着 redo log 中的 XID 去 binlog 查看是否存在此 XID：

• 如果 binlog 中没有当前内部 XA 事务的 XID，说明 redolog 完成刷盘，但是 binlog 还没有刷盘，则回滚事务。对应时刻 A 崩溃恢复的情况。
• 如果 binlog 中有当前内部 XA 事务的 XID，说明 redolog 和 binlog 都已经完成了刷盘，则提交事务。对应时刻 B 崩溃恢复的情况。

对于处于 prepare 阶段的 redo log，即可以提交事务，也可以回滚事务，这取决于是否能在 binlog 中查找到与 redo log 相同的 XID，如果有就提交事务，如果没有就回滚事务。这样就可以保证 redo log 和 binlog 这两份日志的一致性了。

事务没提交的时候，redo log 有可能会被持久化到磁盘

在早期的 MySQL 版本中，通过使用 prepare_commit_mutex 锁来保证事务提交的顺序，在一个事务获取到锁时才能进入 prepare 阶段，一直到 commit 阶段结束才能释放锁，下个事务才可以继续进行 prepare 操作。

通过加锁虽然完美地解决了顺序一致性的问题，但在并发量较大的时候，就会导致对锁的争用，性能不佳。
因此MySQL 引入了 binlog 组提交（group commit）机制，当有多个事务提交的时候，会将多个 binlog 刷盘操作合并成一个，从而减少磁盘 I/O 的次数

总结，Bin log、Redo log 和 Undo log 的区别：

• 数据记录方式

  • Bin log：以二进制格式记录数据库的修改操作。

  • Redo log：以固定大小的物理日志文件记录数据库页的物理修改。

  • Undo log：以逻辑方式记录事务执行过程中旧值的备份。

• 记录内容

  • Bin log：保存数据修改后的内容。

  • Redo log：保存数据修改后的内容。

  • Undo log：保存数据修改前的内容。

• 应用场景

  • Bin log：用于数据恢复、主从复制、数据审计和数据备份等。

  • Redo log：用于保证事务的持久性和故障恢复。

  • Undo log：用于事务的回滚和并发控制。

undo log、redo log、binlog区别和解析原文-小林coding

三种日志详解

三种日志持久化图解

学习博客

Buffer Pool

待更新。。。

写缓存（change buffer）

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话， 在不影响数据一致性的前提下， InooDB会将这些更新操作缓存在change buffer中 ，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

merge ：将change buffer中的操作应用到原数据页，得到最新结果的过程称为 merge 。除了访问这个数据页会触发merge外，系统有后台线程会定期merge。在数据库正常关闭（shutdown）的过程中，也会执行merge 操作。

如果能够将更新操作先记录在change buffer， 减少读磁盘 ，语句的执行速度会得到明显的提升。而且， 数据读入内存是需要占用 buffer pool 的，所以这种方式还能够 避免占用内存，提高内存利用率。

唯一索引的更新不能使用change buffer ，实际上也只有普通索引可以使用。

引擎

MySQL5.5版本后，MySQL的默认内置存储引擎已经从MyISAM变成InnoDB

InnoDB：

• 支持事务;

• 行级锁定(更新数据时一般指锁定当前行)：通过索引实现、全表扫描忍让时表锁、注意间隙所的影响;

• 读写阻塞与事务的隔离级别相关；

• 具有非常高的缓存特性(既能缓存索引、也能缓存数据)；

• 这个表和主键以组(Cluster)的方式存储、组成一颗平衡树；

• 所有的辅助索引(secondary indexes)都会保存主键信息；

• 支持分区、表空间类似与oracle 数据库；

• 支持外键约束、不支持全文检索(5.5.5之前的MyISAM支持全文检索、5.5.5之后就不在支持);

• 相对MyISAM而言、对硬件的要求比较高

MyISAM特性

• 不支持事务

• 表级锁定，数据更新时锁定整个表：其锁定机制是表级锁定，这虽然可以让锁定的实现成本很小但是也同时大大降低了其并发性能。

• 读写互相阻塞：不仅会在写入的时候阻塞读取，myisam还会在读取的时候阻塞写入，但读本身并不会阻塞另外的读。

• 只会缓存索引：MyISAM可以通过key_buffer_size缓存索引，以大大提高访问性能，减少产品IO，但是这个缓存区只会缓存索引，而不会缓存数据。

• 读取速度较快，占用资源相对少。

• 不支持外键约束，但支持全文索引。

Memory

慢SQL

查看慢SQL是否启用，查看命令：show variables like 'log_slow_queries';
如果结果为ON则是开启了，如果为OFF则表示禁用了。

1. 开启慢查询命令：set global log_slow_queries = on;
2. 查看是否开启：show variables like 'log_slow_queries';
3. 查看慢查询参数，即设置超过多少秒的查询归为了慢查询。参数为：long_query_time，查询命令： show global variables like ‘long_query_time’;
4. mysql默认时间为10秒，即10秒及以上的查询被归为了慢查询。我们的实际项目中根本就不可能这么包容你，所以得提供查询效率优化sql，让程序更快的执行。
5. 这里设置时间为1秒，即超过1秒就会被认为慢查询。设置命令：set global long_query_time =1;用命令设置的，会立即生效，不用重启mysql服务。但重启mysql服务后就会失效
6. 查看设置的时间， show global variables like ‘long_query_time’;即可看到现在已经变为1秒了
7. 查看慢查询存放日志，命令： show variables like 'slow_query_log_file';

可以使用mysql自带的mysqldumpslow解析慢日志，示例返回内容：

查询语句为：

SELECT * FROM sms_send WHERE service_id=10 GROUP BY content LIMIT 0, 1000;

mysqldumpslow 返回内容为 ：

Count: 1  Time=1.91s (1s)  Lock=0.00s (0s)  Rows=1000.0 (1000), vgos_dba[vgos_dba]@[10.130.229.196]
SELECT * FROM sms_send WHERE service_id=N GROUP BY content LIMIT N, N;

使用mysqldumpslow的分析结果不会显示具体完整的sql语句,只会显示sql的组成结构;

pt-query-digest是用于分析mysql慢查询的一个工具

配置示例博客
慢sql日志解析

慢sql案例及调优

页分裂

页分裂（Page Split）是指在插入或更新操作导致数据页（通常大小为 16KB）因为没有足够空间来容纳新的数据而必须被分裂成两个或多个数据页的过程。
InnoDB 存储引擎采用了 B+树索引结构来存储表中的行数据和索引。下面是页分裂过程的大致描述：

1. 数据插入：当你插入一行新数据，InnoDB 会尝试将其放入适当的索引页中。如果该页已经满了（即已经达到了其容量），新的数据就无法放入。

2. 页分裂：为了给新数据腾出空间，InnoDB 会将当前页的一些数据移动到新创建的页中，这个过程被称为页分裂。这通常会发生在以下情况：

3. 顺序插入：当数据按索引顺序插入，且最后一页已满，会创建一个新的页并在其上继续插入。
   非顺序插入：如果数据插入到中间的某个索引位置，导致中间的页溢出，那么会把一部分数据移动到新页中，以便为新数据腾出空间。

页分裂导致的问题：

• 效率降低：页分裂会增加 I/O 操作，因为需要写入新的页，并可能需要更新父页和附近的页来维护 B+树的结构。此外，页分裂可能会导致数据碎片，即数据在物理存储上的布局不再连续，这会影响查询性能。

• 空间利用率：页分裂可能导致数据页的空间利用率下降，因为分裂操作通常会尽量平均地分配原页和新页的数据，但随着时间的推移，难以保持平衡，可能会导致空间的浪费。

页分裂是数据库管理和性能调优中常见的问题。在设计数据库和执行数据操作时需要注意页分裂的影响，并采取措施减少其发生，例如：

• 使用适当的填充因子，预留一些空间以减少页分裂的次数。
• 避免使用随机值作为主键，例如 UUID，因为它们会导致频繁的非顺序插入和页分裂。
• 定期进行优化表操作，以重新组织数据并减少碎片。

理解和监控页分裂有助于维护数据库的性能和稳定性。

主从复制

MySQL 的主从复制依赖于 binlog ，也就是记录 MySQL 上的所有变化并以二进制形式保存在磁盘上。复制的过程就是将 binlog 中的数据从主库传输到从库上。

具体详细过程如下：

• MySQL 主库在收到客户端提交事务的请求之后，会先写入 binlog，再提交事务，更新存储引擎中的数据，事务提交完成后，返回给客户端“操作成功”的响应。
• 从库会创建一个专门的 I/O 线程，连接主库的 log dump 线程，来接收主库的 binlog 日志，再把 binlog 信息写入 relay log 的中继日志里，再返回给主库“复制成功”的响应。
• 从库会创建一个用于回放 binlog 的线程，去读 relay log 中继日志，然后回放 binlog 更新存储引擎中的数据，最终实现主从的数据一致性。

主从复制三种同步模式：

• 同步复制：MySQL 主库提交事务的线程要等待所有从库的复制成功响应，才返回客户端结果。这种方式在实际项目中，基本上没法用，原因有两个：一是性能很差，因为要复制到所有节点才返回响应；二是可用性也很差，主库和所有从库任何一个数据库出问题，都会影响业务。
• 异步复制（默认模型）：MySQL 主库提交事务的线程并不会等待 binlog 同步到各从库，就返回客户端结果。这种模式一旦主库宕机，数据就会发生丢失。
• 半同步复制：MySQL 5.7 版本之后增加的一种复制方式，介于两者之间，事务线程不用等待所有的从库复制成功响应，只要一部分复制成功响应回来就行，比如一主二从的集群，只要数据成功复制到任意一个从库上，主库的事务线程就可以返回给客户端。这种半同步复制的方式，兼顾了异步复制和同步复制的优点，即使出现主库宕机，至少还有一个从库有最新的数据，不存在数据丢失的风险。

MySQL主从同步延迟原因与解决方案

整合SpringBoot

整合博客

博客记录

mvcc解析

Mysql四层架构

覆盖索引、前缀索引、索引下推、SQL优化、主键设计

数据库三范式