数据库（mysql）相关

• 一条sql的执行过程

• 

• InnoDB和MyISAM区别

• InnoDB：高并发，高可靠

• • 1. 支持ACID的事务，支持事务的四种隔离级别；
• • 2. 支持行级锁及外键约束：因此可以支持写并发；
• • 3. 主键索引采用聚集索引，辅索引的数据域存储主键的值，查找主键非常快。
• • 4. 一个InnoDb表有两个文件，表结构文件frm，数据和索引文件idb
• • 5. 不存储总行数；

• MyISAM：

• • 1. 不支持事务，但是每次查询都是原子的；
• • 2. 支持表级锁，即每次操作是对整个表加锁；
• • 3. 采用非聚集索引。辅索引与主索引基本一致，但是辅索引不用保证唯一性，有重复。
• • 4. 一个MYISAM表有三个文件：索引文件、表结构文件、数据文件(引文采用的非聚簇索引)；(·“.frm”文件存储表定义·数据文件具有“.MYD”（MYData）扩展名索引文件具有“.MYI”（MYIndex）扩展名)
• • 5. 存储表的总行数；

• 

• Mysql是怎么存储数据的：

• 1. 每个数据库对应一个文件夹，文件夹名和库名相同；
• 2. (单独的表空间中) innodb引擎中对应两个文件，文件名和表名相同。

• 后缀名为：frm、ibd；

• • frm文件：存储的是表结构信息。
• • ibd文件：存储的是表里的数据、索引等

• ibd文件被分为连续的大小相同的区域，称为页（Page）

• 大小默认值为16KB，可以设置。页的大小固定，格式固定。

• 表空间在逻辑结构上可分为 段，区，页。

• 

#buffer pool既包含索引页又包含数据页 buffer pool缓存磁盘中的数据页（包括索引页和数据页），减少磁盘I/O，提升读写效率。

• buffer pool被划分为某干个数据页，其数据页大小和表空间使用的页大小一致，为了更好的管理buffer pool中的缓冲页，innoDB为每个缓冲页都创建了一个控制信息,这些控制信息主要包括该缓冲页的【表空间编号、页号、缓冲页在buffer pool中的地址】

• 用三个链表来管理这些页，链表的节点就是控制信息：

• • 1、 Free List 空闲的链表
• • 2、 Flush list 脏页链表
• • 3、 Lru list 组成LRU算法的链表

• 一个脏页既可以被fluh列表所引用，又可以被lru引用。所以Lru列表既包含脏页又包含非脏页。

• 它是一种lru的一种变形，数据进来先放入到MidpointInsertion,当有命中的数据会把命中的数据放入到头部，其他的数据下移。MySQL 通过在读取数据库页时预先将相关的数据块加载到内存中，从而减少磁盘I/O操作。

• 变形是为了解决“预读失效”与“缓冲池污染”的问题。

• • 预读失效:提前把页放入了缓冲池，但最终MySQL并没有从缓存页中读取数据，称为预读失效。
• • 缓冲池污染:当某一个SQL语句，要批量扫描大量数据时，可能导致把缓冲池的所有页都替换出去，导致大量热数据被换出，MySQL性能急剧下降，这种情况叫缓冲池污染。

查询数据时，优先在Buffer Pool中查找目标页。若命中（Page in Pool），直接返回内存中的数据；若未命中，从磁盘加载数据页到Buffer Pool。

修改数据时，如果要修改的数据在buffer pool里直接修改，如果没在，则先加载到buffer pool里在修改，而不会直接写入磁盘，此时就会出现Buffer Pool中的数据页内容和磁盘上的数据页的内容不一致的情况，此时称这样的数据页为脏页，因此InnoDB引擎提供了一个后台线程每隔一定时间将Buffer Pool中的内容写入磁盘，这样的过程叫做刷脏。

• innodb_buffer_pool_size 配置内存大小（通常设置为物理内存的50%-70%）。

• 刷脏的时机

• • 1、 数据库关闭
• • 2、 Buffer pool不够用，LRU淘汰脏页变成空闲页。
• • 3、 Redolog 写满，强制checkpoin前移，前移过程的redolog将会失效，为避免数据丢失，必须刷脏了。
• • 4、 后台线程异步刷脏。

ChangeBuffer(索引页，是buffer pool的一部分)

• 

• 注意和脏页的区别。他包含两部分，一部分bufferpool里的，一部分是磁盘里的。

• 核心作用：优化对非唯一二级索引（secondary index） 的写入效率，减少随机I/O。

• 当修改二级索引页，且目标页不在Buffer Pool时，将修改操作（如插入、删除记录）暂存到Change Buffer。 不立即从磁盘加载数据页，避免随机I/O。

• 当目标页首次被加载到Buffer Pool时（查询触发或其他操作），将Change Buffer中积累的修改操作合并（Merge）到该页。后台线程定期合并（例如空闲时或慢关闭）。

• Change Buffer是Buffer Pool的一部分（默认占25%，由innodb_change_buffer_max_size控制）。

• 为什么是secondary indexes？

• 但是对于唯一和主键索引： InnoDB必须进行唯一性检查。也就是说，索引页不在缓冲池，就无法检查索引的唯一性，所以必须把索引加载到磁盘里，既然加在磁盘里了，那就没必要用changebuffer了。

• Merge的时机，除了上述刷脏的时机，还包含当由于查询操作把要merge的页缓存到buffer里时。这个merge才是changbuffer真正想达到的效果。

• 适合开启的场景：

• • （1）数据库大部分是非唯一索引；
• • （2）业务是写多读少，或者不是写后立刻读取；
• • 不适合的场景：
• • （1）数据库都是唯一索引；
• • （2）或者，写入一个数据后，会立刻读取它；

• Redo 日志

• Redo log 容灾恢复过程MySQL的处理过程如下

MySQL 事务处理中 Redo Log 的写入与脏页刷新机制并非如问题所述那样简单直接。以下是基于 InnoDB 存储引擎的完整流程解析：

🔧 一、事务开启与 Redo Log 的写入

事务开启（BEGIN）

数据修改与 Redo Log 生成

执行数据修改（INSERT/UPDATE/DELETE）时，InnoDB 按顺序执行以下操作：

记录 Undo Log：保存数据修改前的状态，用于回滚和 MVCC 。

更新内存数据页：在 Buffer Pool 中修改数据，生成脏页（内存页与磁盘页不一致）。

写入 Redo Log Buffer：将数据页的物理变更（如“字段 A 从 1 改为 2”）写入内存中的 Redo Log Buffer（此时仍在内存，未落盘）。

⚙️ 二、事务提交与 Redo Log 刷盘

提交事务（COMMIT）

触发 Redo Log 两阶段写入：

Prepare 阶段：将 Redo Log Buffer 中的日志标记为 PREPARE 状态，并刷盘（根据配置决定是否立即刷盘）。

写入 Binlog：Server 层生成 Binlog 并写入磁盘（需保证 Binlog 落盘）。

Commit 阶段：将 Redo Log 标记为 COMMIT 状态（可能异步刷盘）。

只要 Redo Log 提交成功，即使脏页未刷盘，数据库崩溃后仍可通过 Redo Log 重放恢复数据（持久性保障）。

log是否完整，如果判断是完整（commit）的，直接用Redo log恢复。如果redo log只是预提交prepare但不是commit状态，这个时候就会去判断binlog是否完整，如果完整就提交Redo log，用Redo log恢复，不完整就回滚事务，丢弃数据。

• 只有在redo log状态为prepare时，才会去检查binlog是否存在，否则只校验redo log是否是 commit就可以啦。 怎么检查binlog：一个完整事物binlog结尾有固定的格式。

• InnoDB给出 innodb_flush_log_at_trx_commit参数，该参数控制 commit提交事务时，如何将 redo log buffer 中的日志刷新到 redo log file 中。它支持三种策略：

• • 设置为0 ：表示每次事务提交时不进行刷盘操作。（通过系统默认master thread每隔1s进行一次重做日志的同步）
• • 设置为1 ：表示每次事务提交时都将进行同步，刷盘操作（ 默认值 ）
• • 设置为2 ：表示每次事务提交时都只把 redo log buffer 内容写入 page cache（操作系统的缓存），不进行同步。由os自己决定什么时候同步到磁盘文件。

如果事务提交的时候不同步刷redolog，数据会丢失

• 既然redo log也需要在事务提交时将日志写入磁盘，为什么它比直接将Buffer Pool中修改的数据写入磁盘(即刷脏)要快呢？主要有以下两方面的原因，也称Write-Ahead Logging（）技术：

• • （1）刷脏是随机IO，因为每次修改的数据位置随机，但写redo log是追加操作，属于顺序IO。
• • （2）刷脏是以数据页（Page）为单位的，MySQL默认页大小是16KB，一个Page上一个小修改都要整页写入；而redo log中只包含真正需要写入的部分，无效IO大大减少。

• 另外不一定非要等到事务提交时进行redo日志进行写入磁盘。

• redo只是为了系统奔溃后恢复脏页用的，如果对应的脏页已经刷新到了磁盘。那个Redo日志就可以被覆盖了。redo日志文件组容量是有限的，我们不得不选择循环使用redo日志文件组中的文件，但是这会造成最后写的redo日志与最开始写的redo日志追尾。

• 

• 每个日志存储方式[spaceId|pageNumber|data]使用hash表，根据redo日志的space ID和page number属性计算出散列值，把space ID和page number相同的redo日志放到哈希表的同一个槽里，如果有多个space ID和page number都相同的redo日志，那么它们之间使用链表连接起来，按照生成的先后顺序链接起来的。恢复的时候就可以避免随机读。

• Binlog日志二进制日志（binlog）记录了所有的 DDL（数据定义语句）和 DML（数据操纵语句）不记录select。作用：

• 1. 恢复：数据恢复时可以使用二进制日志,必须删除了表中的数据。
• 2. 复制：通过传输二进制日志到从库，然后进行恢复，以实现主从同步。

• Binlog的三种模式（Statement Level模式，Row Level模式，Mixed模式（混合模式）

• Statement：每一条修改数据的sql都会记录到master的bin_log中，slave在复制的时候sql进程会解析成master端执行过的相同的sql在slave库上再次执行。

• • 优点：
• • 1、解决了row level的缺点，不需要记录每一行的变化。
• • 2、日志量少，节约IO，从库应用日志块。
• • 缺点：一些新功能同步可能会有障碍,比如函数、触发器等。

• Row：在row level的模式下，bin_log不在记录具体的执行sql，而是清楚的记录每一行数据修改的细节。比如某一行记录某个字段从a修改成了b。

• • 优点：
• • 1、记录详细
• • 2、解决statement level模式无法解决的复制问题。
• • 缺点：日志量大，因为是按行来拆分。
• • Mixed：在mixed模式下，mysql会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也是在statement和row之间选择一种。像遇到表结构变更的时候就会以statement模式来记录，如果sql语句确实就是update或者delete等修改数据的语句，那么还是会记录所有行的变更。

• 和redo 日志的区别

• 从使用场景角度来说：

• • redo log主要断电保证脏页不丢，保证事务的持久性
• • bin log 主要用于数据灾备、同步

• 从数据内容角度来说：

• • redo log是"物理日志", 记录的是具体数据页上做了什么修改，具体到页；
• • bin log是"逻辑日志", 记录内容是语句的原始逻辑，类似于“给记录ID=2 这一行的 name 改为alvin”。具体到记录行；

• 从生成范围角度来说：

• • redo log是InnoDB存储引擎生成的事务日志，其他存储引擎没有
• • bin log是MySQL Server生成的日志，所有的存储引擎都有

• 从生成时机角度来说：

• • redo log是在事务执行过程中就会write
• • bin log是在事务提交的时候write

• 主从同步复制有以下几种方式：

• • （1）同步复制，master的变化，必须等待slave-1,slave-2,...,slave-n完成后才能返回。
• • （2）异步复制，master只需要完成自己的数据库操作即可，至于slaves是否收到二进制日志，是否完成操作，不用关心。MYSQL的默认设置。
• • （3）半同步复制，master只保证slaves中的一个操作成功，就返回，其他slave不管。这个功能，是由google为MYSQL引入的。

• 主从同步过程：

• 

• master在每个事务更新数据完成之前，将该操作记录串行地写入到binlog文件中

• Master 开启一个I/O线层 发送binlog文件。

• Slave 开启一个I/O线程，把binlog文件写到中继日志中

• Slave SQL Thread会读取中继日志，并顺序执行该日志中的SQL事件，从而与主数据库中的数据保持一致。

• 从新做一个从库流程：

• • 在主库上执行备份操作，记录此时的binlog位点。通常可以使用 mysqldump 命令或者进行物理备份（如使用 xtrabackup 工具）来备份数据库的数据。但是在dump的时候，没有锁表，dump的时候数据已经发生变化
• • 将备份数据传输到新的从库服务器上。
• • 在新的从库服务器上，恢复备份数据到MySQL实例中，可以使用 mysql 命令行或者其他数据库管理工具来执行备份数据的导入操作。
• • 启动从库服务器，可以用指令指定位点消费主库的binlog，数据多次更新，幂等。[消费binlog是幂等的]达到数据一致性，但是如果是insert会报主键冲突。

• Undo 日志

• Innodb会为每张表隐藏建立DB_ROW_ID、DB_Deleted_Bit、DB_TRX_ID、DB_ROLL_PTR四个字段。

• • DB_ROW_ID：隐藏主键，没有索引或者主键，锁的就是他。
• • DB_Deleted_Bit：delete语句而言，当执行后并不会立马删除表的数据，而是将这条数据的Deleted_Bit删除标识改为1/true。后台purge线程自动删除。
• 1. • 性能优化：立即物理删除数据行会涉及大量的磁盘 I/O 操作，特别是在删除大量数据时。将数据行标记为删除可以减少这种开销。
• 2. • 数据一致性：标记为删除可以确保其他正在进行的事务仍然能够访问这些数据行，直到它们完成，从而保持数据一致性。（mvcc）
• 3. • 另外对于索引而言：①删除表数据时，有可能会破坏索引树原本的结构，导致出现叶子节点合并的情况。②事务回滚时，又需重新插入这条数据，再次插入时又会破坏前面的结构，导致叶子节点分裂。
• 4. • select语句扫描二级索引B+树：
• 5. • 被标记删除的行也会扫描到，扫描到后看到该行已被标记删除，就会直接忽略该行。扫描到的行数据还需要回表查看行数据的trx_id，根据read view判断该行数据是否可见，如不可见，就需要根据回滚链，向前寻找可见的数据版本。
• 6. • Purge线程：
• 7. • purge线程为MySQL的后台线程，用于清理undo log和标记删除的行。如果系统有活跃read view，就选取最老的read view作为purge view，该read view前的undo log都可以清理。
• • 事务id：Innodb按递增顺序给每个事务生成一个事务id。
• • DB_TRX_ID:最近一次更新这条记录的事务id。
• • DB_ROLL_PTR：回滚标识指针，每次更改数据后，就会把之前版本的数据放入到undo日志中，DB_ROLL_PTR指针旧数据的地址。而undo日志就是事务未提交更改某一记录形成的新旧版本链表。

• MVCC原理：

• 当事务第一次执行seletc操作的时候，会生成一个readView。一个readView通常会包含（

• creator_trx_id：代表创建当前这个ReadView的事务ID。

• trx_ids：表示在生成当前ReadView时，系统内活跃的事务ID列表。

• up_limit_id：活跃的事务列表中，最小的事务ID。

• low_limit_id：表示在生成当前ReadView时，系统中要给下一个事务分配的ID值。

• ）

• 首先判断行数据中的隐藏列trx_id与ReadView.creator_trx_id是否相同：

• 相同：代表创建ReadView和修改行数据的事务是同一个，自然可以读取最新版数据。

• 不相同判断隐藏列trx_id是否小于ReadView.up_limit_id最小活跃事务ID：

• 小于代表改动行数据的事务在创建快照前就已结束，可以读取最新版本的数据。

• 大于 判断隐藏列trx_id是否大于等于ReadView.low_limit_id这个值：

• 如果大于或等于：代表改动行数据的事务是生成快照后才开启的，因此不能访问最新版数据。

• 继续判断隐藏trx_id是否在trx_ids中：

• 不在：表示改动行数据的事务已经结束，可以访问最新版的数据。

• 在：表示改动行数据的事务目前依旧在执行，不能访问最新版数据。继续拿redo日志链表的下一行记录继续上述判断。

• 在InnoDB存储引擎中，undo日志、redo日志和binlog日志的写入时机各不相同，它们共同保障了MySQL的事务持久性（Durability）、原子性（Atomicity）和崩溃恢复能力。以下是详细的触发时机：

在InnoDB存储引擎中，undo日志、redo日志和binlog日志的写入时机各不相同，它们共同保障了MySQL的事务持久性（Durability）、原子性（Atomicity）和崩溃恢复能力。以下是详细的触发时机：

作用：记录事务修改前的数据状态，用于事务回滚和MVCC（多版本并发控制）。

写入时机：

在事务修改数据之前：当执行INSERT/UPDATE/DELETE时，InnoDB会先拷贝修改前的数据到undo log中。

写入位置：先写入内存中的undo log buffer，后续按策略刷盘到undo tablespace（系统表空间或独立表空间）。

触发刷盘的条件：

事务提交时（undo log不会立即删除，由后台线程purge）；

undo log buffer空间不足时；

后台线程周期性刷盘。

📌 注意：Undo日志本身也会被Redo保护（修改undo页的操作会记录redo），确保崩溃恢复时能重建undo。

无论哪种日志，事务提交后不会立刻被删除；

undo，mvcc，其他事务回滚会使用

redo ，脏页可能还没刷新到磁盘

binlog， 重库指定位点消费

事务：

• A原子性：原子性是指事务包含的所有操作要么全部执行，成功要么全部失败回滚。 底层主要实现 Undo日志

• C一致性：一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态。[通过AID保证]

• I隔离性：隔离性是指当多个用户并发访问数据库时，比如同时访问一张表。数据库为每一个用户开启一个事务，多个并发事务之间相互隔离，互不干扰。底层有隔离级别来实现。

• D持久性：持久性是指事务的操作，一旦提交，对于数据库中的数据改变是永久的，即使数据库发生故障也不能丢失已提交事务所完成的改变。 底层实现主要 Redo 日志数据库隔离级

• • 脏数据：A事务读取到了B事务还未提交的数据。（数据可能还要变或许回滚）
• • 不可重复读：在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了个事务更新的原有的数据
• • 幻读：在一个事务的两次查询中数据个数不一致。例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据。（虽然可重复读增加了s锁的范围，但是新增的数据并没有添加锁）

• 为了达到事务的四大特性，数据库定义了4种不同的事务隔离级别

• 

• • 一种锁定读取、更新（UPDATE）或删除（DELETE）操作通常会对SQL语句处理中扫描到的每个索引记录设置记录锁。无论语句中是否有排除该行的WHERE条件都无关紧要。InnoDB不会记住确切的WHERE条件，但只知道哪些索引范围被扫描过。

• 可重复读（REPEATABLE READ）：

• 这是InnoDB的默认隔离级别。在同一事务内的一致性读取会读取首次读取建立的快照。这意味着，如果你在同一事务内发出几个普通的（非锁定的）SELECT语句，这些SELECT语句也会相互一致。参见第15.7.2.3节，“一致性非锁定读取”。

• 对于锁定读取（SELECT与FOR UPDATE或FOR SHARE），UPDATE和DELETE语句，锁定取决于语句是否使用具有唯一搜索条件的唯一索引，或者范围类型的搜索条件。对于具有唯一搜索条件的唯一索引，InnoDB只锁定找到的索引记录，而不是它之前的间隙。对于其他搜索条件，InnoDB锁定扫描的索引范围，使用间隙锁或下一个键锁来阻止其他会话向范围覆盖的间隙中插入。

（查找时走二级索引，如果要回表查聚簇索引，则还会在聚簇索引上加锁） （修改时如果二级索引上也存在要修改的值，则还要去二级索引中查找加锁并修改）

• • 读已提交（READ COMMITTED）：

• 即使在同一事务内，每个一致性读取也会设置并读取自己的新快照。

• 对于锁定读取（SELECT与FOR UPDATE或FOR SHARE），UPDATE语句和DELETE语句，InnoDB只锁定索引记录，而不是它们之前的间隙，因此允许在锁定记录旁边自由插入新记录。间隙锁定仅用于外键约束检查和重复键检查。

• 因为间隙锁定被禁用，可能会发生幻影行问题，因为其他会话可以在间隙中插入新行。只有基于行的二进制日志记录与READ COMMITTED隔离级别兼容。如果你使用READ COMMITTED与binlog_format=MIXED，服务器会自动使用基于行的日志记录。使用READ COMMITTED还有额外的影响：对于UPDATE或DELETE语句，InnoDB只为其更新或删除的行持有锁。MySQL评估WHERE条件后，非匹配行的记录锁会被释放。这大大减少了死锁的可能性，但它们仍然可能发生。对于UPDATE语句，如果一行已经被锁定，InnoDB执行一个“半一致性”读取，将最新提交的版本返回给MySQL，以便MySQL确定该行是否与UPDATE的WHERE条件匹配。如果行匹配（必须更新），MySQL再次读取该行，这一次InnoDB要么对其加锁，要么等待对其加锁。

• • 读未提交（READ UNCOMMITTED）：SELECT语句以非锁定方式执行，但可能使用行的较早版本。因此，使用这个隔离级别，这样的读取是不一致的。这也被称为脏读。否则，这个隔离级别的工作方式类似于READ COMMITTED。
• • 可串行化（SERIALIZABLE）：这个级别类似于REPEATABLE READ，但InnoDB隐式地将所有普通SELECT语句转换为SELECT ... FOR SHARE，如果autocommit被禁用的话。如果autocommit被启用，SELECT是它自己的事务。因此，它被认为是只读的，并且如果作为一致性（非锁定）读取执行，不需要为其他事务阻塞而可以被串行化。（要强制一个普通SELECT在其他事务修改了选定行时阻塞，禁用autocommit。）

• 【todo待补充具体示例，死锁案例分析】

• juejin.cn/post/733734…

• juejin.cn/post/729555…

• 发生死锁后，InnoDB一般都可以检测到，并使一个事务释放锁回退，另一个获取锁完成事务。

• （表级锁的死锁情况，表级锁就不应该存在）

• A事务执行的时候给某一个表加锁x，然后去访问另一个表y，B事务此时对表y进行了加锁，然后去访问表X此时，两个事务之间互相等待，产生死锁。这种死锁无法通过这种死锁情况需要从业务逻辑的层面进行改变

• 表锁

• • LOCK TABLE 表名 read（读锁/共享锁）
• • LOCK TABLE 表名 write（写锁/排它锁）
• • 【Innodb支持表锁和行锁，如果一个事务仅对表中的行加锁，那么另一个事务要对表加上锁。就要判断该表是否有锁，该表中是否锁（遍历效率较差）】
• • 所以引入共享锁快速判断表中是否有锁 。
• • 意向共享锁(IS)：当我们在对使用InnoDB存储引擎的表的某些记录加S锁之前，那就需要先在表级别加一个IS锁
• • 意向独占锁(IX)：当我们在对使用InnoDB存储引擎的表的某些记录加X锁之前，那就需要先在表级别加一个IX锁
• • 当事务去加X的时候，若表有意向锁[IS,IX]，则不能加X锁。当事务去加s锁的时候，如表有IS锁，则加锁失败.
• • 行级锁：
• • SELECT … LOCK IN SHARE MODE ：共享锁(S锁, share locks)。当前事务加上共享锁，其他事务可以读取数据，但不能对该数据进行修改，直到所有的共享锁被释放。 。
• • SELECT … FOR UPDATE：排他锁(X锁, exclusive locks)。如果事务对数据加上排他锁之后，则其他事务不能对该数据加任何的锁。获取排他锁的事务既能读取数据，也能修改数据。
• • MySQL InnoDB默认Row-Level Lock，加锁的对象是索引。所以只有「明确」地指定主键，MySQL 才会执行Row lock (只锁住被选取的数据) ，否则MySQL 将会执行Table Lock (将整个数据表单给锁住)。
• • 实现行锁的四种算法：
• • 1.record lock：总是会锁住索引记录，如果InnoDB存储引擎表在建立的时候没有设置任何一个索引，那么这时InnoDB存储引擎会使用隐式的主键来进行锁定。分为行级共享锁和行级排他锁(lock_mode X locks rec but not gap）
• • 2.gap lock：范围锁，但是不锁定行记录本身(lock_mode X locks gap before rec)
• • 3.next-key lock：范围锁加行锁，即范围锁并锁定记录本身，gap lock + record lock。（lock_mode X）
• • 4.Insert Intention Lock，插入意向锁，特殊的间隙锁，在insert时产生，需要判断插入位置是否已被其他事务加了间隙锁，若加了间隙锁将会等待。（LOCK_INSERT_INTENTION）
• • 索引：
• • 索引结构:
• • B+ 和 B的区别：
• • B树定义，一个m阶B树：
• • • 根节点至少一个元素
• • • 非根节点元素范围：m/2 <= k <= m-1
• • • 每个节点中的关键字都按照从小到大的顺序排列，每个关键字的左子树中的所有关键字都小于它，而右子树中的所有关键字都大于它。子树的个数比关键字个数多1
• • • 所有叶子节点都位于同一层，或者说根节点到每个叶子节点的长度都相同。
• • • 每个节点都存有索引和数据，也就是对应的key和value。
• • B+树其实和B树是非常相似的，我们首先看看相同。
• • • 根节点至少一个元素
• • • 非根节点元素范围：m/2 <= k <= m-1
• • 不同
• • • 内部结点中的key都按照从小到大的顺序排列，对于内部结点中的一个key，左树中的所有key都小于它，右子树中的key都大于等于它。叶子结点中的记录也按照key的大小排列。关键码的个数和子树的个数相同
• • • B+树有两种类型的节点：内部结点（也称索引结点）和叶子结点。内部节点就是非叶子节点，内部节点不存储数据，只存储索引，数据都存储在叶子节点
• • • 每个叶子结点都存有相邻叶子结点的指针，叶子结点本身依关键字的大小自小而大顺序链接。形成单链表设置头指针（双链表）【有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点】
• • Hash:等值查询效率极高（1），但是不能进行排序，不能进行范围查找【memory存储引擎】
• • 红黑树或者平衡树：
• • 1、平衡树树中一个节点只包含一个元素，而B+树每个节点包含多个元素，平衡树每个节点并不能填满一个页，而页是 InnoDB存储引擎管理数据库的最小磁盘单位，填不满造成资源浪费。
• • 2、由于 B+ 树分支比二叉树更多，所以相同数量的内容，B+ 树的深度更浅。这意味着B+树的磁盘io次数要少。
• • [红黑树增删要比B+/B效率要高，所以内存中主要用红黑树，外存中用B+/B树]
• • 相对于B树：
• • 1、由于B树每个节点都包含数据，所以相当于一个节点存储的元素少，树就变的更高。对应的IO次数就会越多。
• • 2、B+ 必须查询到叶子节点，B树只要匹配到即可不用管元素位置，B+树查找更加稳定。
• • 3、在进行遍历的时候，B+只需要扫一遍叶子结点即可。但是B树因为其分支结点同样存储着数据，要找到具体的数据，需要进行一次按序来扫。
• • 索引类型：
• • 聚簇索引：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据,聚簇索引的顺序就是物理存储顺序。
• • INNODB主键采用这种索引，所以innodb推荐采用主键为自增：

• • 自增：那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，主键的顺序按照数据记录的插入顺序排列，自动有序。当一页写满，就会自动开辟一个新的页
    

• • 非自增：由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。
    

• • 非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置,索引顺序与数据物理排列顺序无关。INNODB二级索引就是非聚簇索引
• • 唯一索引：加速查询 + 列值唯一（可以有null）
• • 主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个
• • 唯一索引和普通索引的区别：
• • 1）查询
• • 1、普通索引,查找到第一个满足条件的记录后，继续向后遍历，直到第一个不满足条件的记录;2、唯一索引,由于索引定义了唯一性，查找到第一个满足条件的记录后，直接停止继续检索。
• • （2）更新
• • 1、普通索引,直接在changebuffer操作; 2、唯一索引,必须先判断索引的唯一性，用不到changebuffer
• • 组合索引：设计原则把区分度高的索引放前面。区分度计算公式：count(distinct col)/count(*)。具体看使用场景，不是一概而论。
• • 但是尽量建立联合索引，而不是为每个字段单独建立一个索引。防止indexMerge导致死锁
• • 联合索引底层存储结构
• • www.bilibili.com/video/av361…

• 

• • 最左匹配规则：
• • 最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。
• • 它类似建立了a; a,b;a,b,c;a,b,c,d索引 直接b=2 将不会用到索引
• • 比如如果建立(a,b,c,d)顺序的索引，a = 1 and b = 2 and c > 3 and d = 4 。d是用不到索引的，因为c字段是一个范围查询，它之后的字段会停止匹配。z如果建立（a,b,d,c）就可以全部用到索引。优化器会自动调整d,c的顺序。
• • 索引的优缺点：
• • 优点：
• • 加速查询，减少服务器需要扫描的数据量，可以避免服务器进行排序和产生临时表，可以把随机io变为顺序io。
• • 索引的缺点：
• • 1、 索引要占用额外的磁盘空间
• • 2 、降低了增删改的性能，因为需要调整索引
• • 3、过多的索引，会是生产执行计划变慢
• • 一般建立索引（如果能贴合业务，以下当做没说）：
• • 1、索引字段要尽量的小【B+树越低，查找效率越高】
• • 2、尽量选择区分度高的列作为索引【有效的避免死锁】
• • 3、尽量的扩展索引，不要新建索引。【有效避免死锁】
• • 一条 SQL 执行的很慢，我们要分两种情况讨论：
• • 偶尔很慢，则有如下原因
• • (1)、数据库在刷新脏页，例如 redo log 写满了需要同步到磁盘。
• • (2)、执行的时候，遇到锁，如表锁、行锁。
• • SQL 语句一直很慢就需要真的优化了。一般是要压索引和解决死锁问题了。
• • 慢sql的分析步骤：
• • 通过trace分析优化器如何选着执行计划
• • 通过explian分析效率低sql的执行计划
• • 通过show profile分析sql的执行过程。
• • Explain 命令介绍：
• • Id,越大优先级越高，越先被执行。Id相同时候顺序网上执行
• • select_type(查询类型，主要用于区别普通查询，联合查询，子查询等的复杂查询)
• • ,table(输出行所用的表)（patition）
• • ,possible_keys,key,key_len,,
• • rows 预计要查询的行数,filter.过滤了多少行
• • ref，显示索引的哪一列被使用了，如果可能的话，是一个常数。
• • Extra（Using temporary，使用了临时表保存中间结果，常见于order by 和group by，临时表对系统性能损耗很大。需要优化掉，using filesort 说明mysql中无法利用索引完成的排序操作称为文件内排序）
• • 索引提示：
• • 使用sql提示
• • Use index //如果使用这个索引还没全盘扫描效率高，则不使用索引
• • Ignore index，Force index//必须使用这个索引(跟在表后面)
• • select * from ws_shop a force index(create_time)。
• • 优化器进行索引选择
• • Mysql 会依据成本最小原则来选择使用相应的索引，这里的成本主要包括两个方面 IO 成本和CPU 成本
• • IO成本：
• • 即从磁盘把数据加载到内存的成本，默认情况下，读取数据页的 IO 成本是 1，MySQL 是以页的形式读取数据的，即当用到某个数据时，并不会只读取这个数据，而会把这个数据相邻的数据也一起读到内存中，这就是有名的程序局部性原理
• • CPU 成本：
• • 将数据读入内存后，还要检测数据是否满足条件或者排序等 CPU 操作的成本，显然它与行数有关，默认情况下，检测记录的成本是 0.2。
• • 一．避免不走索引的场景（主要是IO+CPU成本比全表扫描的成本还高）：
• • 1 以%开头的like
• • （1）%A% 改为locate (‘A’ , table.field) > 0 返回字符串A第一次出现的子串table.field的位置
• • （2）reverse(i.C_LCN_NO) like reverse(‘%245′) 建立反向索引走反向索引
• • 2避免not in ,not exist，is not null,‘!=，<>'
• • 3隐式转换
• • 4 函索运算不走索引‘||’，“+”
• • 二．小结果集驱动大结果集：
• • 1.当使用left join时，左表是驱动表，右表是被驱动表 ;
• • 2.当使用right join时，右表时驱动表，左表是驱动表 ;
• • 3.当使用inner join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表 ;
• • 如果没有where 条件，很可能小表不走索引，大表走索引
• • 三．深分页问题

SELECT * FROM orders where a="A" ORDER BY createtime DESC LIMIT 1000000, 10; -- 查询第100000页的10条数据

​1、资源浪费​ 数据库需先扫描满足条件的前100万+10条记录（全表扫描或索引扫描）。 在OFFSET 1000000时，​实际跳过并丢弃100万行数据，只取最后10条。 ​2、​执行成本高 假设a没索引，需要大量的回表进行判断。 假设createtime 没索，临时磁盘文件排序，非常耗cpu【order by 字段没索引】 即使ORDER BY createtime用到索引，但扫描起始位置过深时，数据可能已换页到磁盘外，效率骤降。

游标分页 -- 第一页 SELECT * FROM orders ORDER BY createtime DESC LIMIT 10; -- 第二页（假设上一页最大id=100） SELECT * FROM orders WHERE createtime < “2025-09-01”and a="A" ORDER BY createtime DESC LIMIT 10; ​IO恒定​：仅扫描目标区段，与页数无关。 连续顺序分页（例如App瀑布流），​不支持跳页

方案2：延迟关联（Deferred Join） ​原理​：先查分页主键ID，再回表取明细 SELECT t.* FROM orders t JOIN (SELECT id FROM orders where a="A" ORDER BY createtime DESC LIMIT 1000000, 10) AS tmp ON t.id = tmp.id; ​减少回表量​：子查询仅查10条目标数据的ID。 ​利用覆盖索引​：子查询无需回表。 复杂表结构且需跳页时

• • index merge
• • 我们的 where 中可能有多个条件(或者join)涉及到多个字段，它们之间进行 AND 或者 OR，那么此时就有可能会使用到 index merge 技术。index merge 技术如果简单的说，其实就是：对多个索引分别进行条件扫描，然后将它们各自的结果进行合并(intersect[and]/union[or]/sort union) 。merge 的是主键值，然后根据要查的主键值去回表查数据。
• • 如果所有的sql都慢就要看看是不是数据量和并发的问题了。
• • 数据库分区，分表，分库的区别
• • 当访问量不大，数据量很大的情况下，优化sql外，要提高查询速度，还要对表建分区，突破磁盘I/O瓶颈。因为不同的分区可以放在不同的磁盘上。虽然不同的分区在不同的磁盘上，但是逻辑上仍是一张表，而且对应用程序透明，无需修改任何代码。
• • 当访问量大，数据量的很大的时候。优化sql外，要提高查询速度，可以对表进行分表，这样可以提高并发能力，操作的数据量也少（alter 表的时候会锁表，锁的时间和表大小成正比），而且磁盘I/O性能也提高了。
• • 当数据库的数据量很大，或者单个数据库连接能力不够时需要分库。很明显提高了数据库的连接能力，同样提高并发能力，操作的数据量也少，而且磁盘I/O性能也提高了
• • 分库分表的缺点：
• • 在执行了分库分表之后，难以避免会将原本逻辑关联性很强的数据划分到不同的表、不同的库上。这时，表的关联操作将受到限制，我们无法join位于不同分库的表，也无法 join分表粒度不同的表，结果原本一次查询能够完成的业务，可能需要多次查询才能完成。

• mysql的水平分表和垂直分表的区别

• • #####################################################################
• • 增加可用连接数，修改环境变量 max_connections，及时释放不活动的连接 修改环境变量wait_timeout
• • innodb_thread_concurrency 并发线程数参数设置不合理，导致在高并发的场景下可能会产生较多的上下文切换，导致CPU升高
• • #####################################################################
• • 其他的一些知识：
• • drop,delete,Truncate 的区别
• • drop table 表名（数据库定义语言DLL）操作立刻生效：
• • 删除内容和定义，并释放空间。也就是表的结构一起删除
• • Truncate table 表名（数据库定义语言DLL）操作立刻生效；
• • 删除内容，释放空间但不删除定义（也就是保留表的数据结构）。也就是只是清空表数据而已
• • delet from 表名（where列名=值）
• • ·与truncate 类似，但是delete 即可对行数据进行删除，也可以对整表数据进行删除
• • 执行速度 drop>truncate>delete

• • 另外 delet是数据库操作语言(DML)，会放在rollback segment中，事务提交后才生效(删除某行数据的时候，会留下空洞，需要调optimize table 表名来进行优化)
    

• • char 和varchar 区别
• • char是一种固定长度的类型，varchar则是一种可变长度的类型
• • char(M)类型的数据列里，每个值都占用M个字节，如果某个长度小于M，MySQL就会在它的右边用空格字符补足．（在检索操作中那些填补出来的空格字符将被去掉），从查询上看更易于检索，从存储容量上看，浪费内存;
• • 在varchar(M)类型的数据列里，每个值只占用刚好够用的字节再加上一个用来记录其长度的字节（即总长度为L+1字节）;
• • 编码方式的差异 ascii,unicode,utf-8，utf8mb4区别
• • ASCII ：只占用了一个字节的后面7位，最前面的1位统一规定为0。
• • UNICODE： 将世界上所有的文字统一编码。比如用三个字节，但是对于英文他只占一个字节就行了，其他的字节造成浪费。
• • UTF-8：是UNICODE实现的一种方式。
• • 1）对于单字节的符号，字节的第一位设为0，后面7位为这个符号的unicode码。因此对于英语字母，UTF-8编码和ASCII码是相同的。
• • 2）对于n字节的符号（n>1），第一个字节的前n位都设为1，第n+1位设为0，后面字节的前两位一律设为10。剩下的没有提及的二进制位，全部为这个符号的unicode码。
• • Mysql 端口：3306
• • 查询语句的执行顺序
• • from—join-- on---where--groupby—聚合函数--having--select--distinct—oder by—limit
• • （优化点）按以上顺序group by中where高于having，能写在where中的限定条件就不要去having中限定

• InnoDB采用行式存储结构，- 数据以完整行记录为单位连续存储在数据页中，InnoDB 以页为基本单位 从磁盘加载数据，而不是按行或按列加载：最小I/O单元：即使只需要一行或一列数据，也必须加载整个页到内存。

• mysql select * 和 select 字段A ，A 无索引，对磁盘影响是一样的。