《JAVA面经实录》- 数据库面试题

1.数据、数据库、数据库管理系统和数据库系统概念?

数据：是数据库中存储的基本对象，在计算机中的概念是广义的，描述事物的符号记录称为数据，比如你的音乐、文档和图形都是数据。

数据库：数据库（Database,DB）,简单的来讲就是存放数据的仓库，严格来讲，是长期存储在计算机内，有组织、可共享的大量数据集合。

数据库管理系统：数据库管理系统（Database Management System，DBMS）是位于用户与操作系统之间的一层数据管理软件，用来定义数据，管理数据。

数据库系统：数据库系统由数据、数据库、数据库管理系统（及其开发应用工具）、应用程序和数据库管理员（DBA）组成的存储、管理、处理和维护数据的系统，人们通常把它简称为数据库。

2.数据模型的分类有哪些？

物理模型是对数据最底层的抽象，它描述数据在系统内部的表示方式和存取方法，或在磁盘或磁带上的存储方式和存储方法，是面向计算机系统的。

概念模型实际上是现实世界到机器世界的一个中间层次，用于信息世界的建模，是现实世界到信息世界的第一层抽象。

逻辑模型是按计算机系统的观点对数据建模，主要用于数据库管理系统的实现。常用的逻辑模型主要有层次模型、网状模型、关系模型、面向对象数据模型、对象关系数据模型和半结构化数据模型。

3.数据库设计三范式？

第一范式：每个表都应该有主键，并且每个字段要求原子性不可再分。

第二范式：建立在第一范式基础之上，所有非主键字段必须完全依赖主键，不能产生部分依赖。

第三范式：建立在第二范式基础之上，所有非主键字段必须直接依赖主键，不能产生传递依赖。

4.左连接、右连接、笛卡尔积、全连接和内连接的区别?

(1)左连接（左外连接）：以左表作为基准进行查询，左表数据会全部显示出来，右表如果和左表数据不匹配则显示为null。

(2)右连接（右外连接）：以右表作为基准进行查询，右表数据会全部显示出来，左表如果和右表的数据不匹配则显示为null。

(3)全连接：先以左表进行左外连接，再以右表进行右外连接。

(4)内连接：显示表之间有连接匹配的所有行。

(5)笛卡尔积也叫交叉连接

5.SQL语言包括哪些类型？

数据定义：Create Table,Alter Table,Drop/Truncate Table, Create/Drop Index

数据操纵：Select ,Insert,Update,Delete

数据控制：Grant,Revoke

6.SQL 约束有哪几种？

NOT NULL: 用于控制字段的内容一定不能为空（NULL）。

UNIQUE: 控件字段内容不能重复，一个表允许有多个 Unique 约束。

PRIMARY KEY: 也是用于控件字段内容不能重复，但它在一个表只允许出现一个。

FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

CHECK: 用于控制字段的值范围。

7.E-R模型是什么？

E-R模型是使用E-R图来描述现实世界的概念模型，是描述概念模型的有力工具。

8.事务的概念？

事务是逻辑上的一组操作，要么都执行，要么都不执行。事务最经典的例子就是转账了。假如小明要给小红转账1000元，这个转账会涉及到两个关键操作就是：将小明的余额减少1000元，将小红的余额增加1000元。万一在这两个操作之间突然出现错误比如银行系统崩溃，导致小明余额减少而小红的余额没有增加，这样就不对了。事务就是保证这两个关键操作要么都成功，要么都要失败。

9.事务的特征？

数据库事务transanction正确执行的四个基本要素。ACID，原子性(Atomicity)、一致性(Correspondence)、隔离性(Isolation)、持久性(Durability)。

(1)原子性：事务是最小的执行单位，不允许分割。整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。

(2)一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。

(3) 隔离性：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；

(4) 持久性：在事务完成以后，该事务对数据库中数据的改变是持久的。即使数据库发生故障也不应该对其有任何影响。

10.事务的隔离级别？

数据库的隔离级别越高，并发性就越差，性能就越低。

oracle的隔离级别默认是READ COMMITED

mysql的隔离级别默认是REPEATABLE READ，mysql下的事务默认是自动提交的。

读未提交：事务A在事务B未提交前读取数据，如果事务B撤销了修改，那么A会读取到脏数据。因为事务B提交前的数据在缓存中

读已提交：事务A分别在事务B提交前和提交后读取数据，出现不可重复读。因为事务B提交前的数据在缓存，事务B提交后的数据在硬盘，而事务A一直读取的都是硬盘的数据从而导致不能够重复读的问题。

可重复读：事务A在事务B添加一条数据提交后，事务A读取记录数的时候不一致出现幻读。不可重复读的重点在于Update和Delete，而幻读在于Insert。

11.索引的优缺点，什么时候使用索引，什么时候不能使用索引

索引是数据库性能优化的核心，但它并非“银弹”。正确使用可以极大提升效率，滥用则可能导致性能下降。下面为你详细解析索引的优缺点以及适用场景。

✅ 索引的优点

1. 极大提升查询速度这是索引最核心的作用。它就像书的目录，让数据库无需扫描全表（全表扫描），就能快速定位到目标数据，将查询时间复杂度从 O(n) 降低到 O(log n)，尤其在数据量巨大时效果显著。
2. 加速数据排序和分组索引本身是有序的。当查询中包含 ORDER BY 或 GROUP BY 时，如果操作字段上有索引，数据库可以直接利用索引的顺序，避免了额外的、开销巨大的排序操作。
3. 保证数据唯一性通过创建唯一索引，可以强制要求某列（或多列组合）的数据在表中是唯一的，从而在数据库层面保证数据的完整性，例如用户的基本属性等。
4. 实现覆盖索引，避免回表如果一个查询所需的所有字段都包含在索引中，数据库可以直接从索引中获取数据，而无需再回到主表中查找。这种“覆盖索引”可以显著减少I/O操作，提升性能。

❌ 索引的缺点

1. 降低写操作性能每次对表进行 INSERT、UPDATE、DELETE 操作时，数据库不仅要修改数据，还需要同步更新所有相关的索引。索引越多，维护成本越高，写入速度就越慢。
2. 占用额外的存储空间索引需要存储在磁盘上，会占用额外的空间。对于大型表，索引文件的大小可能达到原表数据的20%~30%甚至更多。
3. 增加维护复杂度随着业务变化，可能需要频繁地创建、删除或调整索引。过多的索引会增加数据库的维护成本和复杂度，优化器也可能因索引过多而选择错误的执行计划。

🎯 什么时候应该使用索引

遵循“为查询服务”的原则，在以下场景的字段上建立索引通常能获得良好收益：

• 主键和唯一约束字段：必须建立索引，通常是主键索引或唯一索引。
• 频繁作为查询条件的字段：在 WHERE 子句中经常出现的列，例如 user_id、order_no 等。
• 用于表关联的字段：在 JOIN 操作中作为连接条件的列，建立索引可以大幅提升关联查询的效率。
• 用于排序、分组或去重的字段：在 ORDER BY、GROUP BY、DISTINCT 中出现的列。
• 区分度高的字段：字段中不同值的数量越多，索引的效果越好。

🚫 什么时候不应该使用索引

在以下场景中，建立索引不仅无效，甚至可能拖慢性能：

• 数据量很小的表：对于只有几十、几百行的表，全表扫描的速度可能比走索引更快，因为索引本身也有I/O成本。
• 重复值多、区分度低的字段：例如“性别”（只有男/女）、“状态”（0/1）等字段，索引的筛选效果极差，优化器通常会放弃使用。
• 频繁更新的字段：每次更新都会触发索引的维护，如果字段更新非常频繁，会带来巨大的性能开销。
• 过长的文本字段：如 TEXT、LONGTEXT 类型，为其建立完整索引会占用大量空间。如果必须索引，可以考虑使用前缀索引。
• 查询条件中使用了函数或计算：例如 WHERE YEAR(create_time) = 2024，对索引列进行函数操作或计算会导致索引失效。
• 模糊查询以通配符开头：例如 WHERE nam LIKE '%三'，这种查询无法利用索引的有序性，会进行全表扫描。
• OR 条件中部分字段无索引：例如 WHERE id = 1 OR age = 20，如果 age 字段没有索引，整个查询可能会退化为全表扫描。
• 发生隐式类型转换：例如，phonem 字段是 VARCHAR 类型，但查询时写成了 WHERE phonem = 138000000XX（未加引号），MySQL会进行类型转换，导致索引失效。

12.什么情况下索引失效？

13.简述什么是聚簇索引与非聚簇索引？

聚簇索引与非聚簇索引是数据库（特别是 InnoDB 引擎）中最核心的索引概念。理解它们的区别，是掌握数据库性能优化的关键。

简单来说，它们的根本区别在于：索引结构和数据行的物理存储是否在一起。

🌳 聚簇索引

聚簇索引是一种将索引和数据行存储在一起的索引结构。

• 核心特点：数据即索引：在聚簇索引的 B+ 树中，叶子节点存储的不是指向数据的指针，而是完整的行数据。唯一性：一张表只能有一个聚簇索引，因为数据行本身只能按照一种顺序物理存储。主键索引：在 MySQL 的 InnoDB 引擎中，聚簇索引通常就是主键索引。如果你定义了主键，InnoDB 会优先使用主键构建聚簇索引；如果没有主键，它会选择一个唯一非空索引，或者隐式创建一个 rowid 来构建。
• 优点：查询速度极快：通过聚簇索引（如主键）查询数据时，可以直接在叶子节点获取到完整的行数据，无需额外的查找步骤，I/O 效率最高。范围查询高效：由于数据在物理上是按照索引顺序存储的，进行范围查询（如 WHERE id BETWEEN 10 AND 20）时性能非常出色。

🗂️ 非聚簇索引

非聚簇索引（在 InnoDB 中也常被称为二级索引或辅助索引）的索引结构与数据行的物理存储是相互独立的。

• 核心特点：索引是导航：非聚簇索引的 B+ 树叶子节点存储的是索引列
• 的值和对应行的主键值。它就像一个目录，告诉你数据在哪里，但不包含数据本身。数量不限：一张表可以创建多个非聚簇索引，用于优化不同字段的查询。
• 查询过程与“回表”：当你通过非聚簇索引查询数据时（例如 SELECT * FROM user WHERE nam = 'aAlice'，且 nam 上有非聚簇索引），查询过程分为两步：先在 nam 的非聚簇索引树中找到 'Alice'，并拿到她对应的主键 ID（比如 id=100）。再拿着这个主键 ID 100，去聚簇索引（主键索引）树中查找，才能获取到完整的用户信息。 这个从非聚簇索引跳转回聚簇索引查找完整数据的过程，就叫做“回表”。回表会增加额外的 I/O 开销，因此通过非聚簇索引查询通常比通过聚簇索引查询要慢。

📊 核心区别对比

💡 优化技巧：覆盖索引

为了避免“回表”带来的性能损耗，一个重要的优化手段是使用覆盖索引。

覆盖索引是指，一个非聚簇索引恰好包含了查询所需的所有字段。在这种情况下，MySQL 可以直接从非聚簇索引中获取全部数据，而无需再“回表”查询。

• 示例：对于查询 SELECT id, nam FROM user WHERE nam = 'Alice'，如果 nam 字段上建有非聚簇索引，由于查询的 id（主键值）和 nam（索引列值）都已经在该索引的叶子节点中，MySQL 就可以直接返回结果，避免了回表操作。

14.索引的应用场景

索引的应用场景可以理解为两个层面：一是在什么样的业务场景下应该创建索引，二是已经创建的索引在什么样的查询中能够被有效使用。

🎯 一、 应该创建索引的场景

在设计数据库表时，为合适的字段创建索引是性能优化的第一步。通常，在以下场景中，你应该考虑创建索引：

• 主键与唯一约束字段这是最基本的场景。主键（PRIMARY KEY）会自动创建聚簇索引，保证数据行的唯一性和快速查找。同样，业务上需要保证唯一性的字段（如用户的订单号）也应创建唯一索引（UNIQUE INDEX）。
• 频繁作为查询条件的字段在 WHERE 子句中高频出现的列是创建索引的首选。例如，在用户表中，user_id 或 email 经常用于登录查询，为其建立索引可以极大加速查询。
• 用于表连接（JOIN）的字段在多表关联查询中，作为连接条件的字段（通常是外键）应该建立索引。这能显著减少关联时的扫描次数，提升 JOIN 操作的性能。
• 用于排序、分组或去重的字段如果查询中频繁使用 ORDER BY、GROUP BY 或 DISTINCT，为这些字段创建索引可以利用索引自身的有序性，避免数据库进行额外的、开销巨大的排序或分组操作。
• 数据量大且区分度高的字段对于数据量大的表，索引效果更明显。同时，字段的区分度（或称选择性）越高，索引效果越好。区分度 = COUNT(DISTINCT 字段) / COUNT(*)。例如，nam 字段的区分度远高于 gender 字段，为 nam 建索引的效果会更好。

✅ 二、 索引能够被有效使用的场景

创建了索引不代表它一定会被使用。索引（尤其是B+树索引）在以下几种查询模式中能够发挥最大效用：

(1). 全值匹配

查询条件精确匹配了索引的全部列。这是最理想的场景。

-- 假设有联合索引 idx_user_status_time (user_id, status, create_time)
-- 以下查询能完美使用该索引
SELECT * FROM orders WHERE user_id = 101 AND status = 1 AND create_time = '2024-01-15 10:00:00';

(2). 最左前缀匹配

对于联合索引，查询条件从最左边的列开始，并且是连续的。

-- 使用索引的前两列 (user_id, status)
SELECT * FROM orders WHERE user_id = 101 AND status = 1;

-- 仅使用索引的第一列 (user_id)
SELECT * FROM orders WHERE user_id = 101;

(3). 范围查询

索引对于 >、<、BETWEEN 等范围查询同样高效。

-- 范围查询也能有效利用索引
SELECT * FROM orders WHERE create_time BETWEEN '2024-01-01' AND '2024-01-31';

(4). 覆盖索引查询

当查询的所有字段都包含在索引中时，数据库无需“回表”，直接从索引中获取数据，性能极佳。

-- 假设查询只需要 user_id 和 status，这两个字段都在索引 idx_user_status_time 中
SELECT user_id, status FROM orders WHERE user_id = 101 AND status = 1;

(5). 用于排序和分组

当 ORDER BY 或 GROUP BY 的列与索引的列顺序一致时，可以直接利用索引的有序性。

-- 可以利用索引的有序性来避免额外的排序操作
SELECT * FROM orders WHERE user_id = 101 ORDER BY status;

🚫 三、 不适合创建索引的场景

盲目创建索引不仅浪费空间，还会拖慢写入性能。以下场景应避免或谨慎创建索引：

• 数据量很小的表：对于只有几十或几百行的表，全表扫描可能比维护索引的成本更低。
• 区分度极低的字段：例如“性别”（只有男/女）、“状态标志”（0/1）等，索引的过滤效果很差，优化器通常会选择全表扫描。
• 频繁更新的字段：每次 INSERT、UPDATE、DELETE 操作都需要同步更新索引。为频繁变化的字段建索引会带来巨大的维护开销。
• 很少在查询中出现的字段：如果一个字段几乎不作为查询、排序或连接的条件，为它创建索引就是一种资源浪费。
• 过长的文本字段：TEXT、BLOB 等大字段本身不适合建立普通索引。如果必须搜索，应考虑使用全文索引（FULLTEXT）或前缀索引。

15.存储过程和函数的区别？

存储过程是用户定义的一系列sql语句的集合，涉及特定表或其它对象的任务，用户可以调用存储过程，而函数通常是数据库已定义的方法，它接收参数并返回某种类型的值并且不涉及特定用户表。

16.什么是触发器？触发器的使用场景有哪些？

1）触发器，指一段代码，当触发某个事件时，自动执行这些代码。

2）使用场景：可以通过数据库中的相关表实现级联更改；实时监控某张表中的某个字段的更改而需要做出相应的处理。

17.什么叫视图？

视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，视图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。

18.视图的作用？以及视图可以更新数据吗？

1)使用视图可以简化复杂的sql操作，隐藏具体的细节，保护数据;

2)视图不能被索引，也不能有关联的触发器或默认值，如果视图本身内有order by则对视图再次order by将被覆盖。对于某些视图，例如，未使用联结子查询分组聚集函数Distinct Union等，是可以对其更新的，对视图的更新将对基表进行更新；但是视图主要用于简化检索，保护数据，并不用于更新，而且大部分视图都不可以更新。

19.什么是游标？

游标：是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。

20.MyISAM和InnoDB的区别？

MyISAM（3 个文件）特点：只读之类的数据分析的项目

1. 支持表级别的锁（插入和更新会锁表）。不支持事务

2. 拥有较高的插入（insert）和查询（select）速度

3. MyISAM 用一个变量保存了整个表的行数，执行select count(*) from table语句时只需要读出该变量即可，速度很快；

InnoDB（2 个文件）特点：经常更新的表，存在并发读写或者有事务处理的业务系统。

1. 支持事务，支持外键，因此数据的完整性、一致性更高

2. 支持行级别的锁和表级别的锁

3. 支持读写并发，写不阻塞读（MVCC）

4. 特殊的索引存放方式，可以减少IO，提升查询效率

MyISAM 和InnoDB 是我们用得最多的两个存储引擎，在 MySQL 5.5 版本之前，默认的存储引擎是MyISAM。5.5版本之后默认的存储引擎改成了InnoDB，为什么要改呢？最主要的原因还是InnoDB 支持事务，支持外键，支持行级别的锁，对于业务一致性要求高的场景来说更适合。

21.如何选择存储引擎？

InnoDB：适用对数据一致性要求比较高，需要事务支持

MyISAM：适用数据查询多更新少，对查询性能要求比较高。

Memory：适用一个用于查询的临时表。

22.为什么 MySQL 采用 B+ 树作为索引？

这几种数据结构都是为了解决数据高效查找而设计的，但它们的演进过程体现了针对不同场景（内存 vs 磁盘、读多写少 vs 读写频繁）的优化思路。

首先需要明确一点：B-树就是B树，两者是同一种数据结构，中间的横杠只是为了强调“B”和“减”的区别，实际上没有B-树这种独立的结构。

下面我将为你详细解析它们的演进逻辑、区别、优缺点以及适用场景。

🌳 核心演进逻辑

我们可以把这些树看作是一个不断“打补丁”和“进化”的过程：

1. 二叉树：基础形态。但如果数据有序插入，会退化成链表，查找效率极低。
2. 平衡二叉树：解决了退化问题，通过严格限制高度差，保证查找极快，但维护平衡太累（旋转多）。
3. 红黑树：放宽了平衡标准，牺牲了一点点查找速度，换取了插入/删除时的高效，是内存中有序数据的王者。
4. B树：为了解决海量数据存储在磁盘上的问题。通过“多叉”让树变矮，减少磁盘I/O次数。
5. B+树：B树的升级版，优化了范围查询和磁盘利用率，是数据库索引的绝对主力。

🔍 详细对比与解析

(1). 二叉搜索树

• 结构：每个节点最多有两个子节点，左小右大。
• 优点：结构简单，查找、插入、删除的平均时间复杂度为 O(log n)。
• 缺点：极度不稳定。如果插入的数据是有序的（如 1, 2, 3, 4, 5），树会退化成链表，查找效率降为 O(n)。
• 适用场景：现代工程中几乎不直接使用，通常作为学习基础,教学演示、简单有序结构。

(2). 平衡二叉树

• 结构：在二叉搜索树基础上，强制要求左右子树高度差的绝对值不超过1。
• 优点：查找效率极高。因为树的高度被严格控制，查找时间复杂度稳定在 O(log n)。
• 缺点：维护成本太高。为了维持严格的平衡，每次插入或删除节点后，可能需要进行多次旋转操作，导致写操作变慢。
• 适用场景：适用于读多写少的内存数据查找场景,如某些内存数据库索引。

(3). 红黑树

• 结构：一种“弱平衡”的二叉树。它通过给节点着色（红/黑）并遵循一系列规则，保证最长路径不超过最短路径的2倍。
• 优点：综合性能最优。它在查找、插入和删除之间取得了很好的平衡。虽然查找效率略低于平衡二叉树，但插入和删除时的旋转次数大大减少，性能更稳定。
• 缺点：实现逻辑相对复杂。
• 适用场景：广泛应用于内存中的有序集合。例如 Java 的 TreeMap、TreeSet，C++ 的 std::map，以及 JDK 8 之后的 HashMap。Linux 内核：进程调度、虚拟内存管理。大量插入删除的内存索引。

(4). B树

• 结构：一种多路搜索树。每个节点可以包含多个键（Key）和多个子节点指针。所有节点（包括内部节点和叶子节点）都存储数据。
• 优点：磁盘I/O效率高。因为一个节点可以存很多键，树变得“矮胖”，查询时需要的磁盘读取次数（I/O）大大减少。
• 缺点：范围查询效率一般。因为数据分散在所有节点中，进行范围查询（如 WHERE id > 10）时，需要遍历不同层级的节点，甚至进行中序遍历，效率不如B+树。
• 适用场景：早期文件系统、部分非关系型数据库（如MongoDB）。

(5). B+树

• 结构：B树的改进版。核心区别有两点：数据只在叶子节点：内部节点只存索引（键），不存数据。这使得单个节点能存更多索引，树更矮。叶子节点相连：所有叶子节点通过指针连成一个双向链表。
• 优点：范围查询极快：只需找到起点，顺着叶子节点的链表往后读即可。磁盘I/O更少：树的高度更低，查询性能更稳定。空间利用率高：内部节点不存数据，能容纳更多索引键。
• 缺点：点查询（精确查找）必须走到叶子节点，比B树多一次IO（但在数据库页缓存机制下影响不大）。
• 适用场景：关系型数据库索引的首选（如 MySQL 的 InnoDB 引擎、MongoDB ），几乎所有现代数据库、文件系统。

📊 总结对比表

23.什么叫回表？

如果一个查询是先走辅助索引（聚簇索引外的索引都叫辅助索引）的，那么通过这个辅助索引（innodb中的辅助索引的data存储的是主键）没有获取到我们想要的全部数据，那么MySQL就会拿着辅助索引查询出来的主键去聚簇索引中进行查询，这个过程就是叫回表；

24.什么是索引覆盖？

所谓的索引覆盖是索引高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。

注意：id 字段是聚簇索引，age 字段是普通索引（二级索引）

select id,age from user where age = 30;

上面的这个sql是不用回表查询的，因为在非聚簇索引的叶子节点上已经有id和age的值。所以根本不需要拿着id的值再去聚簇索引定位行记录数据了。也就是在这一颗索引树上就可以完成对数据的检索，这样就实现了覆盖索引。

select id,age,nam from user where age = 30;

而上面的这个sql不能实现索引覆盖了，因为nam的值在age索引树上是没有的，还是需要拿着id的值再去聚簇索引定位行记录数据。但是如果我们对age和nam做一个组合索引idx_age_nam(age,nam),那就又可以实现索引覆盖了。

25.谈一下你对MySQL索引的理解？

索引的b+树结构,为什么使用b+树说一下,然后再说一下聚簇索引，回表和索引覆盖；

然后再谈一下索引失效；

26.count(1)、count(*) 与 count(列名) 的区别？

(1）从含义上讲，count(1) 与 count(*) 都表示对全部数据行的查询。count(*) 包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL ；count(1) 用1代表代码行，在统计结果的时候不会忽略列值为NULL 。

(2）执行效率上：

count(*)对行的数目进行计算，包含NULL，count(1)这个用法和count(*)的结果是一样的。如果表没有主键，那么count(1)比count(*)快。表有主键，count(*)会自动优化到主键列上。如果表只有一个字段，count(*)最快。

count(1)跟count(主键)一样，只扫描主键。count(*)跟count(非主键)一样，扫描整个表。明显前者更快一些。

count(1)和count(*)基本没有差别，但在优化的时候尽量使用count(1)。

27.MySQL中int(20)和char(20)以及varchar(20)的区别?

1、 int(20) 表示字段是int类型，显示长度是 20

2、 char(20)表示字段是固定长度字符串，长度为 20

3、 varchar(20) 表示字段是可变长度字符串，长度为 20

28.SQL语句中关键字的执行顺序？

SQL的执行顺序：from---where--group by---having---select---order by

29.SQL的生命周期？

第一步：客户端请求

第二步：连接器（负责跟客户端建立连接、获取权限、维持和管理连接）

第三步：查询缓存（存在缓存则直接返回，不存在则执行后续操作）

第四步：分析器（对SQL进行词法分析和语法分析操作）

第五步：优化器（主要对执行的sql优化选择最优的执行方案方法）

第六步：执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口）

第七步：去引擎层获取数据返回（如果开启查询缓存则会缓存查询结果）

30.drop、delete与truncate的区别？

delete和truncate只删除表的数据不删除表的结构

速度,一般来说: drop> truncate >delete

delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效，如果有相应的trigger,执行的时候将被触发.

 truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger.

31.MySQL有哪些锁?

MySQL 的锁机制非常复杂，但它是保证数据库在高并发下数据一致性的核心。为了让你更清晰地理解，我们可以从粒度、性质和算法三个维度来对锁进行分类。

这里主要以 MySQL 最常用的 InnoDB 存储引擎为例进行解析。

📊 核心分类总览

我们可以把 MySQL 的锁体系看作一个金字塔：

1️⃣ 按锁的粒度划分

这是最直观的分类方式，决定了锁的影响范围。

全局锁

• 定义：锁定整个数据库实例。
• 命令：FLUSH TABLES WITH READ LOCK (FTWRL)。
• 影响：加锁后，整个库的所有表都处于“只读”状态，禁止增删改和 DDL 操作。
• 场景：主要用于全库逻辑备份，保证备份期间数据不发生变化。

表级锁

• 定义：锁定整张表。
• 特点：加锁快，不会出现死锁，但并发度最低（一锁锁死全表）。
• 常见类型：显式表锁：通过 LOCK TABLES ... READ/WRITE 手动加锁（极少使用）。元数据锁：这是 MySQL 自动维护的。当你查询表时，会自动加 MDL 读锁；当你修改表结构（如 ALTER TABLE）时，会加 MDL 写锁。坑点：如果一个长事务正在查询表，此时你去修改表结构，MDL 写锁会被阻塞，导致后续所有对该表的查询都被阻塞，引发“雪崩”。意向锁：这是一种特殊的表级锁，用于配合行锁。作用：事务在获取行锁之前，需要先在表上申请一个“意向锁”（意向共享锁 IS 或 意向排他锁 IX）。它的作用是告诉其他事务：“我准备锁住表里的某些行了，你们别来锁整张表”。意义：它让表锁和行锁能够共存，不需要扫描全表就能判断是否有行被锁定。

行级锁

• 定义：锁定具体的某一行数据。
• 特点：并发度最高，但锁的管理开销大，容易出现死锁。
• 注意：InnoDB 的行锁是基于索引实现的。如果你的 SQL 没有走索引（例如 WHERE 条件字段没索引），InnoDB 会退化为锁住全表（虽然逻辑上是行锁，但效果等同于表锁）。

2️⃣ 按锁的性质划分

这决定了事务之间是否兼容。

• 共享锁：又称读锁。事务 A 加了 S 锁，事务 B 也可以加 S 锁（大家都能读）。事务 B 不能加 X 锁（不能写）。
• 排他锁：又称写锁。事务 A 加了 X 锁，事务 B 既不能加 S 锁，也不能加 X 锁（别人既不能读也不能写）。普通的 UPDATE、DELETE、INSERT 操作都会自动加 X 锁。

3️⃣ 按锁的算法划分（InnoDB 特有）

这是 InnoDB 为了解决幻读和实现 MVCC 而设计的精妙之处。

记录锁

• 最普通的行锁，锁住具体的某一行索引记录。
• 例如：SELECT * FROM t WHERE id = 1 FOR UPDATE; 锁住 id=1 这一行。

间隙锁

• 锁住索引记录之间的“空隙”，或者第一条记录之前的范围。
• 目的：防止其他事务在这个间隙插入数据，从而解决幻读问题。
• 例如：表中有 id=10 和 id=20，间隙锁可以锁住 (10, 20) 这个区间，禁止插入 id=15 的数据。

临键锁

• 记录锁 + 间隙锁 的组合。
• 它不仅锁住记录本身，还锁住该记录之前的间隙。
• 场景：在 可重复读（RR） 隔离级别下，进行范围查询（如 WHERE id > 10）时，InnoDB 默认会使用临键锁。这既防止了别人修改已有数据，也防止了别人插入新数据。

💡 总结与避坑指南

1. 索引至关重要：InnoDB 的行锁是锁在索引上的。不走索引 = 锁全表。
2. 死锁风险：行锁虽然并发高，但如果两个事务以不同的顺序获取锁（A 锁 1 等 2，B 锁 2 等 1），就会发生死锁。
3. MDL 锁陷阱：在线上环境执行 DDL（修改表结构）一定要小心，检查是否有长事务正在访问该表，否则容易造成数据库卡死。
4. 间隙锁的影响：在 RR 级别下，范围查询会锁住一大片范围（临键锁），这可能会导致并发插入性能下降。如果业务允许，可以考虑将隔离级别降级为读已提交（RC），此时间隙锁会失效（除了外键检查）。

32.什么情况下导致死锁？如何避免死锁？

在 MySQL（特别是 InnoDB 引擎）中，死锁（Deadlock）是指两个或多个事务在执行过程中，因争夺资源而造成的一种互相等待的现象。若无外力干涉（如数据库自动回滚），这些事务都将无法推进。

这就好比两辆车在单行道上迎面相遇，谁也不肯倒车，导致交通彻底瘫痪。

导致死锁的情况主要分为经典场景和InnoDB 特有机制场景两大类。

🔑 死锁产生的四个必要条件

在深入具体场景前，你需要知道死锁发生必须同时满足以下四个条件：

1. 互斥条件：资源是独占的（如排他锁 X 锁）。
2. 请求与保持条件：事务持有了资源 A，同时还在请求资源 B。
3. 不剥夺条件：已获得的资源在未使用完之前，不能被强制剥夺。
4. 循环等待条件：事务之间形成了头尾相接的循环等待链（A 等 B，B 等 A）。

💥 常见的死锁场景

(1). 并发事务对资源访问顺序不一致（最经典场景）

这是最常见的原因，通常被称为“ABBA 死锁”。当两个事务以相反的顺序更新同一组数据时，死锁几乎必然发生。

• 场景描述：事务 A：先锁住 id=1 的行，然后试图更新 id=2 的行。事务 B：先锁住 id=2 的行，然后试图更新 id=1 的行。
• 过程：事务 A 执行 UPDATE ... WHERE id=1，获得 id=1 的锁。事务 B 执行 UPDATE ... WHERE id=2，获得 id=2 的锁。事务 A 执行 UPDATE ... WHERE id=2，被阻塞（因为 B 持有锁）。事务 B 执行 UPDATE ... WHERE id=1，被阻塞（因为 A 持有锁）。
• 结果：A 等 B，B 等 A，死锁发生。

(2). 间隙锁（Gap Lock）与插入意向锁冲突

这是 InnoDB 在**可重复读（RR）**隔离级别下特有的死锁原因，通常涉及范围查询和插入操作。

• 场景描述：两个事务试图在同一个“间隙”中插入数据，或者一个事务持有间隙锁，另一个事务试图插入。
• 过程：事务 A 执行范围查询 SELECT * FROM t WHERE id > 10 FOR UPDATE。InnoDB 会对 (10, +∞) 这个范围加上间隙锁（或临键锁）。事务 B 执行 INSERT INTO t VALUES (15)。插入操作需要获取插入意向锁。由于事务 A 的间隙锁与事务 B 的插入意向锁冲突，事务 B 被阻塞。如果此时事务 A 也试图插入一条数据（或者有其他复杂交互），就可能形成死锁。

(3). 索引缺失导致锁升级（全表扫描）

虽然这不是严格意义上的“死锁”（更多是锁等待），但极易诱发死锁。

• 原因：如果 SQL 语句没有走索引（例如 WHERE 条件字段没有索引），InnoDB 会进行全表扫描。
• 后果：全表扫描会导致 MySQL 锁住表中所有扫描过的行（甚至整张表）。当多个并发事务都进行这种操作时，锁冲突的概率呈指数级上升，极易造成互相阻塞。

(4). 唯一键冲突（插入死锁）

• 场景：两个事务同时尝试插入相同的唯一键值。
• 过程：事务 A 插入 id=10，持有 id=10 的排他锁（未提交）。事务 B 插入 id=10，被阻塞，等待事务 A 释放锁。如果事务 A 在提交前，又尝试插入另一条数据，而这条数据又被事务 B（在某种特殊等待链下）间接持有或阻塞，可能形成死锁。

🛠️ 如何避免死锁？

理解了成因，解决方案也就有了方向：

1. 保持访问顺序一致：这是最有效的手段。在代码中规定好更新多行数据的顺序（例如永远按 id 从小到大更新），打破循环等待条件。
2. 缩短事务持有锁的时间：避免长事务，将大事务拆分为小事务。在事务中避免进行耗时操作（如 RPC 调用、复杂计算）。
3. 优化索引：确保查询和更新语句都能利用索引，避免全表扫描，从而减小锁的粒度。
4. 降低隔离级别：如果业务允许，可以将隔离级别从“可重复读（RR）”降级为“读已提交（RC）”。在 RC 级别下，InnoDB 不加间隙锁（Gap Lock），能大幅减少死锁概率。
5. 设置超时时间：通过配置 innodb_lock_wait_timeout（默认 50 秒），让事务在等待过久时自动回滚，避免无限期阻塞。

📊 场景对比总结

33.如何查看死锁？

1）使用命令 show engine innodb status 查看最近的一次死锁。

2）InnoDB Lock Monitor 打开锁监控，每 15s 输出一次日志。使用完毕后建议关闭，否则会影响数据库性能。

34.数据库死锁如何处理？

1：通过innodblockwait_timeout来设置超时时间，一直等待直到超时。其中innodb默认是使用设置死锁时间来让死锁超时的策略，默认innodblockwait_timeout设置的时长是50s。

2：发起死锁检测，发现死锁之后，主动回滚死锁中的事务，不需要其他事务继续。

35.什么是主从复制?

主从复制就是用来建立一个或多个和主库一样的数据库,称为从库,然后可以在这两者之上进行一个读写分离,主库少写,从库多读的操作,这样就能大大缓解数据库的并发压力。

36.主从复制作用 ？

1）做数据的热备份，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。

2）架构的扩展。业务量越来越大,I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的评率，提高单个机器的I/O性能。

3）读写分离，使数据库能支持更大的并发。在线上环境中,一般都是读多写少,那么我们可以在主库中实现写操作,然后在从库实现读操作,这样就能很好的分担压力.

37.MySQL的主从复制如何做的？

1. 首先从库创建I/O线程去请求主库 的binlog

2. 然后主库创建一个binlog dump线程将数据同步到binlog文件中.

3. 然后从库I/O线程将binlog文件数据同步到自身的redo log文件中.

4. 然后从库创建一个sql线程将redo log文件里的数据同步到数据库里.

38.什么是当前读和快照读吗？

简单来说在高并发情况下当前读是获取最新的记录并且其他事务不能修改这个记录、快照读获取的有可能是老的数据。当前读是加了锁的，并且加的是悲观锁。而快照读是没加锁的。

39.什么是MVCC？

MVCC,全称Multi-Version Concurrency Control,即多版本并发控制，是一种高并发版本控制器，一般用于数据库中对数据的并发访问。Mysql中的innoDB中就是使用这种方法来提高读写事务的并发性能。因为MVCC是一种不采用锁来控制事务的方式，是一种非堵塞、同时还可以解决脏读，不可重复读，幻读等事务隔离问题，但不能解决更新丢失问题。

总之：就是MVCC是通过保存数据的历史版本，根据比较版本号来处理数据是否显示，从而达到读取数据的时候不需要加锁就可以保证事务隔离性的效果。

40.MVCC 实现的原理？

MVCC（Multi-Version Concurrency Control，多版本并发控制）是 MySQL（InnoDB 引擎）实现高并发性能的核心机制。

简单来说，它的核心思想是：“用空间换时间”。通过保存数据的历史版本，让读写操作互不阻塞，从而极大地提升了数据库的并发处理能力。

如果没有 MVCC，数据库在处理读写冲突时通常需要加锁（读加共享锁，写加排他锁），这会导致“读阻塞写，写阻塞读”。而 MVCC 让读操作可以读取数据的“旧版本快照”，写操作生成数据的“新版本”，两者并行不悖。

🛠️ MVCC 的实现原理

MVCC 的实现依赖于三个核心组件的紧密配合：隐藏字段、Undo Log（版本链） 和 Read View（读视图）。

1. 隐藏字段：数据的“证”

在 InnoDB 中，每一行数据（聚簇索引记录）除了我们定义的列，还包含两个关键的隐藏列：

• DB_TRX_ID：记录最后一次修改该行数据的事务 ID。
• DB_ROLL_PTR：回滚指针，指向该行数据在 Undo Log 中的上一个版本。

2. Undo Log 与版本链：数据的“时光机”

当你更新一行数据时，InnoDB 不会直接覆盖原数据，而是：

1. 将旧数据写入 Undo Log。
2. 生成新数据，并将新数据的 DB_TRX_ID 设为当前事务 ID。
3. 通过 DB_ROLL_PTR 将新数据指向 Undo Log 中的旧数据。

这样，所有的历史版本就被串联成了一条版本链。查询时，MySQL 可以从最新版本顺着指针回溯，找到任意一个历史版本。

3. Read View：数据的“透视镜”

这是 MVCC 的核心。当事务进行“快照读”（普通的 SELECT）时，InnoDB 会生成一个 Read View。它记录了当前系统中活跃的事务列表（即那些已经开始但还没提交的事务 ID）。

Read View 主要包含以下信息：

• m_ids：活跃事务 ID 列表。
• min_trx_id：活跃事务中最小的 ID。
• max_trx_id：下一个将要分配的事务 ID。

可见性判断规则（核心逻辑）：当事务读取数据时，拿着数据的 DB_TRX_ID 与 Read View 进行比对：

1. 自己改的可见：如果 DB_TRX_ID == 当前事务 ID，数据可见。
2. 老版本可见：如果 DB_TRX_ID < min_trx_id，说明修改者早已提交，数据可见。
3. 新版本不可见：如果 DB_TRX_ID >= max_trx_id，说明修改者是在当前事务之后才启动的，数据不可见。
4. 活跃事务不可见：如果 DB_TRX_ID 在 m_ids 列表中，说明修改者还没提交，数据不可见。

如果当前版本不可见，MySQL 就会顺着 DB_ROLL_PTR 去找 Undo Log 里的旧版本，直到找到一个可见的版本为止。

⚖️ 隔离级别的影响

MVCC 在不同隔离级别下的表现不同，主要区别在于 Read View 生成的时机：

📌 总结

MVCC 的本质就是：写操作创建新版本并保留旧版本（版本链），读操作根据规则（Read View）去版本链中挑选合适的历史版本。 这使得 MySQL 能够在保证数据一致性的同时，实现极高的并发读写性能。

41.实践中如何优化MySQL？

最好是按照以下顺序优化：

1.SQL语句及索引的优化

2. 数据库表结构的优化

3.系统配置的优化

4.硬件的优化

这是一份基于实战经验的 MySQL 优化指南。遵循“先软件后硬件，先应用后架构”的原则，我们可以将优化工作分为四个层级。

1️⃣ SQL 语句及索引的优化（性价比最高）

这是优化工作的“主战场”，通常能解决 80% 的性能问题。

🕵️‍♂️ 第一步：定位慢查询

在优化之前，必须先找到“病灶”。

• 开启慢查询日志：设置 slow_query_log = 1 和 long_query_time = 1（秒），记录执行时间超过阈值的 SQL。
• 分析工具：使用 mysqldumpslow 或 pt-query-digest 分析日志，找出出现频率最高或耗时最长的 Top N SQL。

🎯 第二步：索引优化（核心武器）

• 利用覆盖索引：尽量让查询的字段都包含在索引中，避免“回表”查询，大幅提升性能。
• 遵循最左前缀原则：对于联合索引 (a, b, c)，查询条件必须从 a 开始才能命中索引。
• 避免索引失效：不在索引列上做运算或使用函数（如 WHERE YEAR(create_time) = 2024）。避免隐式类型转换（如字符串字段不加引号）。LIKE 查询避免以 % 开头（LIKE '%abc' 会导致全表扫描）。慎用 OR 连接非索引列，建议改为 UNION ALL。

📝 第三步：SQL 语句重写

• 拒绝 SELECT *：只查询需要的字段，减少网络传输和内存消耗，增加覆盖索引命中率。
• 优化深分页：LIMIT 1000000, 10 性能极差。优化方案：使用“延迟关联”或记录上次查询的最大 ID（WHERE id > last_max_id LIMIT 10）。
• 小表驱动大表：在使用 JOIN 时，确保驱动表（结果集较小的表）数据量少，且关联字段必须有索引。
• 批量操作：避免在循环中单条插入/更新，使用批量插入或 INSERT ... ON DUPLICATE KEY UPDATE。

2️⃣ 数据库表结构的优化

良好的表结构设计是高性能的基石。

🛠️ 字段类型选择

• 越小越好：在满足需求的前提下，选择最小的数据类型。例如，用 TINYINT 代替 INT，用 VARCHAR 代替 TEXT。
• 定长优于变长：对于存储哈希值或固定长度的字符串，使用 CHAR 效率更高。

📏 范式与反范式

• 适度反范式：虽然第三范式能减少冗余，但在高并发读场景下，适当的冗余字段（如订单表中冗余商品名称）可以减少 JOIN 操作，提升查询速度。

📈 分库分表（架构层面的表优化）

当单表数据量超过千万级（如 1000w+）时，需要考虑拆分：

• 垂直分表：将大字段（如文章内容）拆分到扩展表中，主表只保留热点字段。
• 水平分表：按照某种规则（如 User ID 取模）将数据分散到多个结构相同的表中，解决单表数据量过大的瓶颈。

3️⃣ 系统配置的优化

调整 MySQL 的“神经系统”，使其更好地利用服务器资源。

💾 内存配置（最关键）

• innodb_buffer_pool_size：这是 InnoDB 最重要的参数，用于缓存数据和索引。建议：设置为物理内存的 70% - 80%。如果设置过小，会导致频繁的磁盘 I/O。

⚙️ 连接与线程

• max_connections：根据应用并发量调整，避免连接数耗尽导致 Too many connections 错误。
• thread_cache_size：适当调大，用于缓存线程，减少频繁创建/销毁线程的开销。

💾 日志配置

• innodb_log_file_size：适当增大重做日志文件的大小，可以减少检查点刷新频率，提升写入性能。

🔧 辅助工具

• 可以使用 mysqltuner 脚本，它会自动分析你的运行状态并给出配置参数的优化建议。

4️⃣ 硬件的优化（最后的防线）

当软件和配置优化都达到瓶颈时，才考虑升级硬件。

💿 磁盘 I/O（最大瓶颈）

• SSD 是必须的：数据库性能通常受限于 IOPS。将机械硬盘（HDD）升级为 SSD 或 NVMe 固态硬盘，性能会有数量级的提升。
• RAID 10：如果预算允许，使用 RAID 10 阵列，既提供了数据冗余，又提供了较高的读写性能。

🧠 内存

• 内存越大，innodb_buffer_pool_size 就能设置得越大，从而将更多的热数据缓存在内存中，减少磁盘读取。

🚀 CPU

• 选择核心数较多的 CPU，有助于处理高并发下的线程调度和复杂的查询计算。

📌 总结检查清单

建议按照此顺序逐步排查，通常在第 1 和第 2 阶段就能解决大部分性能问题。

42.怎么利用EXPLAIN分析sql执行计划

EXPLAIN 是 MySQL 中用于分析 SQL 语句执行计划的“X光机”。它能告诉你 MySQL 优化器打算如何执行你的查询，帮助你发现是否使用了索引、扫描了多少行数据以及是否存在性能瓶颈。

以下是利用 EXPLAIN 分析 SQL 的实战指南，按重要程度排序：

1. 基本用法

在 SELECT、DELETE、INSERT、REPLACE 或 UPDATE 语句前加上 EXPLAIN 关键字。

EXPLAIN SELECT * FROM user WHERE id = 1;

执行后，你会得到一张包含多列信息的表格。

2. 核心字段详解（重点关注）

虽然输出列很多，但你只需要重点关注以下几个核心指标，它们直接反映了查询的性能：

🎯 type（访问类型）—— 最重要的指标

这一列反映了 MySQL 在表中找到所需行的方式，也是判断查询效率的最直观标准。

性能从好到坏依次是：

优化目标：保证查询至少达到 range 级别，最好能达到 ref 或 const。

🔑 key & possible_keys

• possible_keys：MySQL 理论上可以使用哪些索引来查找数据。
• key：MySQL 实际决定使用哪个索引。如果 key 为 NULL，说明没有使用索引。如果 possible_keys 有值但 key 为 NULL，说明 MySQL 优化器认为全表扫描比走索引更快（通常发生在数据量极小或索引区分度极低时），或者索引失效了。

🔢 rows

MySQL 预估需要扫描的行数。

• 这个数值越小越好。
• 它不是精确值，但能直观反映查询的代价。如果 type 是 ALL 且 rows 很大，这就是优化的重点。

📝 Extra（额外信息）—— 诊断细节

这一列包含了 MySQL 解决查询的额外细节，包含很多关键信息：

• Using index：好现象。表示使用了“覆盖索引”，无需回表查询数据，性能很高。
• Using where：表示在存储引擎层检索数据后，在 Server 层又进行了一次过滤。
• Using temporary：坏现象。表示 MySQL 需要创建临时表来处理查询（常见于 GROUP BY 或 ORDER BY），应尽量避免。
• Using filesort：坏现象。表示 MySQL 无法利用索引完成排序，需要在内存或磁盘中进行额外的排序操作，消耗 CPU 和 I/O。

3. 实战分析流程

当你拿到 EXPLAIN 的结果时，建议按照以下步骤进行“体检”：

1. 看 type：如果是 ALL（全表扫描），必须优化。检查 WHERE 条件字段是否有索引。
2. 看 key：确认是否使用了你预期的索引。如果没有，分析是索引缺失还是索引失效（如对字段进行了函数运算）。
3. 看 Extra：如果出现 Using temporary 或 Using filesort，尝试建立联合索引来优化排序和分组。尝试优化 SQL 使其出现 Using index（覆盖索引）。

4. 进阶：EXPLAIN ANALYZE (MySQL 8.0.18+)

如果你使用的是 MySQL 8.0.18 或更高版本，强烈推荐使用 EXPLAIN ANALYZE。

• 区别：普通的 EXPLAIN 只是“预估”执行计划；而 EXPLAIN ANALYZE 会真正执行这条 SQL，并返回实际的执行时间、实际扫描行数等精确数据。

示例：

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 100;

输出亮点：

• actual time=0.015..0.234：显示启动时间和总执行时间。
• rows=100：显示实际返回的行数。

总结

利用 EXPLAIN 优化的核心口诀：一看 type 避全表 (ALL)，二看 key 确索引，三看 rows 估代价，四看 Extra 找排序 (filesort) 和临时表 (temporary)。

43.结合具体例子分析explain结果

我们将通过三个最经典的案例，带你一步步看懂 EXPLAIN 的输出，并学会如何优化。

🛠️ 准备工作

假设我们有一张电商订单表 orders，包含以下字段：

• id: 主键
• user_id: 用户ID（有普通索引 idx_user_id）
• amount: 金额
• status: 订单状态（有普通索引 idx_status）
• create_time: 创建时间

案例一：全表扫描的“惨案” (type: ALL)

场景：运营人员想查询所有状态为“已完成”的订单。

SQL 语句：

SELECT * FROM orders WHERE status = 5;

EXPLAIN 结果：

深度分析：

1. type: ALL：这是最糟糕的情况！MySQL 正在全表扫描。它像无头苍蝇一样把整张表读了一遍。
2. key: NULL：虽然 possible_keys 显示 MySQL 知道有 idx_status 这个索引存在，但它最终决定不使用（key 为 NULL）。这通常是因为优化器估算全表扫描比走索引更快（例如表中数据量很少，或者状态为 5 的数据占比极高，比如 90% 都是 5）。
3. rows: 100000：MySQL 预估要扫描 10 万行数据，代价巨大。

优化方案：

• 如果 status = 5 的数据很少（例如只占 1%），强制走索引或优化索引。
• 如果数据量确实很大且查询频繁，考虑覆盖索引优化：SELECT id, user_id FROM orders WHERE status = 5;（只查索引里有的字段）。

案例二：索引失效的“陷阱” (Using filesort)

场景：我们需要按创建时间倒序排列，查看前 10 个“已完成”的订单。

SQL 语句：

SELECT * FROM orders WHERE status = 5 ORDER BY create_time DESC LIMIT 10;

(假设 create_time 没有索引)

EXPLAIN 结果：

深度分析：

1. type: ref：不错，用到了 idx_status 索引，扫描行数降到了 5000。
2. Extra: Using filesort：警报拉响！ 这意味着 MySQL 无法利用索引的有序性来完成排序。它必须先把所有 status=5 的数据查出来，放到内存或磁盘中进行额外的排序操作。如果数据量大，这会非常慢。

优化方案：建立一个联合索引(status, create_time)。这样 MySQL 可以直接顺着索引树找到 status=5 的数据，并且这些数据在索引中本身就是按 create_time 排好序的，瞬间消除 Using filesort。

案例三：覆盖索引的“完美” (Using index)

场景：统计某个用户的所有订单 ID 和金额。

SQL 语句：

SELECT id, amount FROM orders WHERE user_id = 1001;

(假设我们建立了一个联合索引 idx_user_amount (user_id, amount))

EXPLAIN 结果：

深度分析：

1. type: ref：使用了普通索引查找。
2. Extra: Using index：太棒了！ 这就是传说中的覆盖索引。你要查的字段是 id (主键，自带) 和 amount。你的索引是 (user_id, amount)。MySQL 发现：“哎？你要的数据都在索引树里，我不用去查那张巨大的主表（回表）了！”这极大地减少了 I/O 操作，性能极高。

案例四：函数导致的“隐形杀手”

场景：查询 2023 年下的所有订单。

SQL 语句：

SELECT * FROM orders WHERE YEAR(create_time) = 2023;

EXPLAIN 结果：

深度分析：

1. type: ALL：全表扫描。
2. 原因：你在 create_time 字段上包了一层 YEAR() 函数。MySQL 必须把每一行的时间都拿出来算一下年份，这就导致索引完全失效。

优化方案：将计算移到等号右边，保持左边字段纯净。

SELECT * FROM orders 
WHERE create_time BETWEEN '2023-01-01 00:00:00' AND '2023-12-31 23:59:59';

此时再看 EXPLAIN，type 就会变成 range，key 也会显示使用了时间索引。

📌 总结：一眼定生死

下次看 EXPLAIN 结果时，按这个顺序扫视：

1. 先看 type：是不是 ALL？是就赶紧改。
2. 再看 Extra：有没有 Using filesort 或 Using temporary？有就考虑加联合索引。有没有 Using index？有就偷着乐。
3. 最后看 key：是不是用了你以为它该用的那个索引？如果不是，检查是不是索引失效了。

44.如何优化深分页查询？

深分页（Deep Pagination）是 MySQL 性能优化中的经典难题。简单来说，就是当你的分页页码非常大时（例如查询第 10001 页），查询速度会呈指数级下降。

核心原因在于：MySQL 为了获取最后需要的 10 条数据，必须先扫描并丢弃前面的 100,000 条数据。

针对这个问题，我为你总结了从“应用层改造”到“数据库层优化”的几套实战方案，按推荐程度排序：

1. 游标分页 / 书签分页（性能最优解）

这是互联网大厂（如 Facebook、Twitter、知乎）处理信息流最常用的方案。

• 核心思路：放弃 OFFSET，改用 WHERE 条件记录上一页最后一条数据的位置（即“游标”）。
• 适用场景：APP 下拉刷新、无限滚动加载（不支持随机跳页）。
• 原理：优化前：SELECT * FROM orders ORDER BY id LIMIT 100000, 10;（MySQL 需扫描 100,010 行）优化后：记录上一页最后一条数据的 id 为 100000，查询语句变为：
• 优势：无论翻到第几页，MySQL 都能直接利用索引定位，扫描行数恒定为 10 行，性能极稳。
• 注意点：如果排序字段（如 create_time）有重复值，需要使用“排序字段 + 主键”的组合作为游标，防止数据遗漏或重复。

2. 延迟关联 / 覆盖索引（兼容跳页需求）

如果你的业务必须支持“跳转到第 N 页”（如后台管理系统），无法使用游标分页，那么这是最佳方案。

• 核心思路：将查询分为两步。先在索引中通过 OFFSET 快速定位到需要的 ID（因为索引树比数据行小得多，扫描快），然后再回表查询完整数据。
• SQL 改写：
• 优势：子查询只扫描索引（Index Only），不需要读取整行数据，大大减少了 I/O 开销。
• 前提：ORDER BY 的字段必须有索引，且子查询中最好只查主键 ID。

3. 业务层限制（成本最低）

很多时候，深分页是伪需求。

• 核心思路：从产品层面限制最大页码。
• 实战：电商商品列表：用户通常只看前 10-20 页。超过 50 页或 100 页直接提示“暂无更多数据”。搜索引擎：Google 和百度通常也只允许翻到前几十页。
• 优势：零代码优化成本，直接从根源切断深分页问题。

4. 预计算 / 搜索引擎（海量数据方案）

对于亿级数据或极其复杂的筛选分页，MySQL 可能力不从心。

• 核心思路：搜索引擎：将数据同步到 Elasticsearch (ES)，利用 ES 强大的倒排索引和 search_after 机制处理深分页。冗余表/物化视图：预先计算好分页结果或 ID 列表，存入 Redis 或单独的表中。

方案对比总结

💡 避坑建议

1. 拒绝 SELECT *：在深分页场景下，SELECT * 会导致大量的回表操作，极易拖垮数据库。
2. 索引覆盖：确保 ORDER BY 的字段有索引，否则无论哪种方案，MySQL 都会进行 Filesort，导致性能崩塌。
3. 首选游标：如果是做 APP 或小程序，强烈建议推动前端改造，采用游标分页（传入 last_id），这是治本之策。