PHP面试题（MYSQL篇）：

1、mysql里有200w的数据，redis中有20w的数据，如何保证redis中数据都是热点数据？ （要点：6种数据淘汰策略）
2、三种存储引擎（InnoDB、MyISAM、MEMORY）的区别？
3、事务四大特性（ACID）？

  • 原子性 要么全部做完，要么都不做
  • 一致性 a+b=10，若改变a，那么必须得改变b
  • 隔离性 事务之间不会互相影响，如A账户转100元至B账户，在这个交易还未完成的情况下，如果此时B查询自己的账户，是看不到新增加的100元的
  • 持久性 修改将会永久的保存在数据库

4、哪个文件是用于与数据库建立连接？（.env）
5、事务的并发？事务隔离级别，每个级别会引发什么问题，MySQL默认是哪个级别？

  • ​读未提交​ (Read Uncommitted)
  • 读已提交​ (Read Committed) 防止脏读（指一个事务读到了另一个事务未提交的数据修改。如果另一个事务之后回滚，那么第一个事务读到的数据就是无效的）
  • 可重复读​ (Repeatable Read) 防止脏读、不可重复读（指在同一个事务内，两次读取同一行数据，得到了不同的结果。这通常是因为在两次读取之间，该行数据被另一个已提交的事务修改了）
  • 可串行化​ (Serializable) 防止脏读和 不可重复读 和 幻读（指在同一个事务内，两次执行相同的范围查询，返回的记录数量不同，解决幻读需要锁表）

6、临时表什么时候删除?
7、数据库三范式？答：第一范式：数据表的每一列都要保持它的原子特性，也就是列不能再被分割。
第二范式：属性必须完全依赖于主键。 第三范式：非主属性不依赖于其他的非主属性。范式的目的：减小数据的冗余性、提高效率。
8、水平拆分（有分表，分库；事务一致性难以解决），垂直拆分（部分业务表无法join，只能通过接口方式解决）
9、索引底层是什么数据结构？（B+树；红黑树：高度过高）
10、你知道索引下推吗？（对普通索引需要回表的一种优化，减少磁盘IO）
11、误删除了很多数据，这时候能恢复吗？（一定要开启 bin-log，文件系统是否能恢复；类型用 row 或mixed ，不能statement；记录SQL语句，记录直接数据变更语句（BASE64方式记录），记录混合模式）

12、什么情况下会使索引失效？（​对索引列使用函数或运算、发生隐式类型转换、违反最左前缀原则、使用 OR连接条件(看情况)、LIKE 以 %开头、查询条件使用不等于、数据分布导致优化器放弃）见下文详细回答。

详细回答第1点答案：

在 MySQL 有 200 万数据而 Redis 仅能存储 20 万数据的场景下，要保证 Redis 中都是热点数据，核心在于正确配置和使用 Redis 的数据淘汰策略。下表汇总了 Redis 提供的 6 种主要内存淘汰策略，方便你快速了解。

策略名称 淘汰范围 淘汰规则 适用场景
volatile-lru 仅限设置了过期时间的键 淘汰最近最少使用的键 需要为部分数据设置不同过期时间的场景
allkeys-lru 所有键，无论是否设置过期时间 淘汰最近最少使用的键 业务中有明显冷热数据区分，希望保留最常访问的数据
volatile-lfu 仅限设置了过期时间的键 淘汰最不经常使用（访问频率最低）的键 更关注数据的访问频率，而非最近访问时间
allkeys-lfu 所有键 淘汰最不经常使用（访问频率最低）的键 希望根据访问频率保留热门数据，且访问模式相对稳定
volatile-random 仅限设置了过期时间的键 随机淘汰一个键 应用场景较少，数据重要性相对平均
allkeys-random 所有键 随机淘汰一个键 所有数据被访问的概率几乎相等，没有明显热点
volatile-ttl 仅限设置了过期时间的键 淘汰剩余生存时间最短的键 希望优先淘汰即将过期的数据
noeviction 不淘汰 当内存不足时，新写入操作会返回错误 仅适用于数据不允许丢失的场景，通常不推荐用于缓存

💡 如何选择与配置策略

了解了各种策略后，你可以通过以下步骤来实施：

  1. 选择策略：对于你的场景，如果业务数据有明显的冷热区分（即少数数据被频繁访问），allkeys-lru 通常是最佳选择。它可以确保最近最常被访问的20万条数据保留在内存中。如果你的业务需要根据访问频率而非最近访问时间来淘汰（例如，某个历史数据总体访问量很大，但最近一段时间没被访问），可以考虑 allkeys-lfu。

  2. 配置内存上限：这是触发淘汰策略的前提。你需要在 Redis 配置文件中设置最大内存限制。根据你的需求（20万条数据），需要先估算这20万条数据的大致内存占用，例如，若每条数据约200字节，则总内存约为40MB。你可以通过以下方式设置：

    • 修改配置文件：在 redis.conf 中找到 maxmemory 项并修改：
      1.   maxmemory 50mb  # 设置为略高于估算值，留出缓冲
      2.   maxmemory-policy allkeys-lru  # 设置淘汰策略
    • 运行时动态配置（立即生效，重启后失效）：
      1.   CONFIG SET maxmemory 50mb
      2.   CONFIG SET maxmemory-policy allkeys-lru

  3. 考虑设置过期时间：即使采用了 allkeys-lru，也建议为数据设置合理的过期时间（TTL）。这提供了一个额外的保障，可以自动清理那些不再活跃的“伪热点”数据，避免它们长期占用空间。

🚀 超越基础配置的优化思路

除了正确配置Redis，还可以结合业务逻辑进行优化：

  • 热点数据动态统计与预热：可以通过分析MySQL的慢查询日志或使用监控工具，统计出最近一段时间内（如过去一小时）访问最频繁的数据。然后通过定时任务，将这些明确的热点数据主动加载（预热）到Redis中。
  • 架构设计：对于极热点的数据，可以考虑采用多级缓存架构。例如，将最热的少量数据（如1万条）同时应用本地缓存（如Caffeine、Guava Cache）和Redis缓存，进一步减轻Redis压力并提升响应速度。

详细回答第2点答案：

。了解 MySQL 存储引擎的区别对于数据库设计至关重要。下面我通过一个表格汇总 InnoDB、MyISAM 和 MEMORY 的核心特性，并附上详细的场景说明，帮助你快速把握它们的精髓。

📊 三种存储引擎核心对比

特性 InnoDB MyISAM MEMORY
事务支持 支持 (ACID特性) 不支持 不支持
锁机制 行级锁 (默认) 表级锁 表级锁
外键约束 支持 不支持 不支持
崩溃恢复 优秀 (通过 Redo Log) 较差 无 (数据丢失)
索引类型 聚簇索引 (B+Tree) 非聚簇索引 (B+Tree) 默认 Hash索引，也支持 B+Tree
数据持久化 持久化到磁盘 持久化到磁盘 数据存于内存，重启丢失
COUNT(*) 性能 需扫描全表或索引 极快 (直接读取存储的行数) 快 (数据在内存)
全文索引 MySQL 5.6+ 原生支持 支持 不支持
适用场景 核心业务、高并发写、需事务（如银行、电商） 读多写少、静态数据（如日志、报表） 临时表、缓存、会话存储

💡 如何选择存储引擎？

选择哪个存储引擎，完全取决于你的具体业务需求。你可以参考以下思路：

  • 绝大多数情况下的首选：InnoDB 自 MySQL 5.5 版本后，InnoDB 已成为默认存储引擎。如果你需要事务支持来保证数据一致性（如转账、订单系统），或者应用涉及频繁的更新、删除操作和高并发访问，那么 InnoDB 是最合适的选择。它的行级锁和 MVCC（多版本并发控制）能有效处理并发问题 。对于大多数现代应用，尤其是在意数据可靠性和并发性能的场景，从 InnoDB 开始评估通常不会错。

  • 考虑 MyISAM 的场景 MyISAM 的用武之地已经大大缩小，但在一些只读或读远大于写（例如读写比超过100:1），且对数据一致性要求不高的场景下，它依然有其价值 。例如，用于存放日志或进行复杂的报表分析查询，其高效的全文索引和快速的 COUNT(*) 操作可能带来性能优势 。但务必注意其表锁在并发写时的瓶颈以及崩溃后可能损坏的风险 。

  • 考虑 MEMORY 的场景 MEMORY 引擎将数据完全存放在内存中，因此读写速度极快。它非常适合用作临时存储，例如存放会话（Session）信息、缓存中间结果或作为查询用的临时表 。它的最大缺点是服务器重启后所有数据都会丢失，且表级锁不适合高并发写入 。所以，绝不能用它存储需要持久化的业务数据。

⚠️ 实践中的关键细节

了解一些引擎的底层细节，能帮你更好地使用和避坑：

  • InnoDB 的聚簇索引：它的表数据文件本身就是按主键顺序组织的聚簇索引。因此，定义一个逻辑清晰的主键（如自增ID）对性能很有帮助。二级索引的叶子节点存储的是主键值，这意味着通过二级索引查询可能需要一次“回表”操作 。
  • MyISAM 的压缩能力：对于从不更新的静态表，可以使用 myisampack 工具进行压缩，极大节省存储空间，但压缩后的表是只读的 。这对于归档历史数据非常有用。
  • MEMORY 引擎的隐式转换：它不支持 BLOB 和 TEXT 类型，并且即使你定义了 VARCHAR，也会被转换为 CHAR 类型存储，这可能会浪费一些内存空间 。

详细回答第5点答案：

事务的并发控制是数据库确保多个事务同时执行时数据一致性的关键技术。下面我们详细探讨事务并发可能引发的问题、四种隔离级别及其特点，以及MySQL的默认设置。

🔍 理解事务并发与隔离级别

当多个事务并发执行时，如果缺乏有效的隔离机制，可能会引发一系列数据一致性问题。为了解决这些问题，SQL标准引入了四种事务隔离级别，每种级别通过不同的锁机制或并发控制策略（如MVCC）在性能和数据一致性之间进行权衡。

下表概述了各隔离级别及其可能遇到的问题：

隔离级别 脏读 不可重复读 幻读 简要说明
读未提交 (Read Uncommitted) ❌ 可能发生 ❌ 可能发生 ❌ 可能发生 事务能读取其他事务未提交的数据。
读已提交 (Read Committed) ✅ 防止 ❌ 可能发生 ❌ 可能发生 事务只能读取其他事务已提交的数据。
可重复读 (Repeatable Read) ✅ 防止 ✅ 防止 ⚠️ 部分防止/可能发生 保证事务内多次读取同一数据结果一致。
可串行化 (Serializable) ✅ 防止 ✅ 防止 ✅ 防止 最严格隔离，事务串行执行。

表注：

  • 脏读：指一个事务读到了另一个事务未提交的数据修改。如果另一个事务之后回滚，那么第一个事务读到的数据就是无效的。
  • 不可重复读：指在同一个事务内，两次读取同一行数据，得到了不同的结果。这通常是因为在两次读取之间，该行数据被另一个已提交的事务修改了。
  • 幻读：指在同一个事务内，两次执行相同的范围查询，返回的记录数量不同。这通常是因为在两次查询之间，另一个已提交的事务插入了符合该查询条件的新行。幻读关注的是数据行数量的变化。

🧩 MySQL的默认隔离级别与幻读处理

在MySQL中，默认的事务隔离级别是 可重复读（REPEATABLE READ）。

这与一些其他数据库（如Oracle，默认是读已提交）不同。MySQL选择可重复读作为默认级别，部分原因是为了在基于语句的二进制日志复制时保证主从数据的一致性。

值得注意的是，MySQL的InnoDB存储引擎通过 多版本并发控制（MVCC） 和 间隙锁（Gap Lock） 机制，在可重复读隔离级别下已经可以在很大程度上防止幻读的发生。

  • 在快照读（普通的SELECT语句）时，MVCC通过维护数据的历史版本，保证事务在整个执行过程中看到的数据视图是一致的，从而避免了幻读。
  • 但在当前读（如SELECT ... FOR UPDATE）时，间隙锁会锁定一个索引范围，防止其他事务在这个范围内插入新的数据，从而也避免了幻读。

因此，MySQL的可重复读隔离级别提供了比SQL标准所要求的更强大的保证。

💡 如何选择和修改隔离级别

你可以通过以下SQL语句查看和修改MySQL的事务隔离级别：

  • 查看当前隔离级别：

    1. SELECT @@global.tx_isolation; -- 全局隔离级别
    2. SELECT @@session.tx_isolation; -- 当前会话隔离级别

    在MySQL 8.0及以上版本中，变量名更改为 transaction_isolation。

  • 修改隔离级别（例如，修改为读已提交）：

    1. SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 修改全局级别
    2. SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 仅修改当前会话级别

    也可以通过修改MySQL配置文件（如my.cnf或my.ini）并重启服务来永久生效。

选择隔离级别的考量：

  • 读已提交：在数据一致性要求和高并发性能之间取得较好平衡，是许多业务场景的常见选择，能避免脏读，同时并发性能较好。
  • 可重复读：适用于对同一数据的一致性读取有严格要求的场景，如财务报表生成。
  • 可串行化：提供最高的数据一致性保障，但并发性能损耗最大，仅用于对一致性要求极高且并发冲突严重的场景。

详细回答第6点答案：

MySQL临时表会在以下特定情况下被自动删除，当然你也可以手动操作。具体的关键时机和方式，我汇总在下面的表格里，方便你快速了解：

触发条件 删除方式 效果 备注
会话连接关闭 自动 临时表及其数据被完全销毁 最常见情况，包括关闭客户端或脚本执行完毕
手动执行命令 手动 临时表被立即删除 使用 DROP TEMPORARY TABLE 语句
MySQL服务器重启 自动 所有临时表被清除 服务异常重启时需注意

💡 拓展了解

  • 会话独立性：即使在不同的会话中创建了同名的临时表，它们也互不影响。临时表仅对创建它的会话可见，其他会话无法访问。
  • 与内存表的区别：注意不要将临时表（Temporary Table）与内存表（Memory Table，使用ENGINE=MEMORY创建）混淆。内存表的表结构存储在磁盘上，数据存储在内存中，重启MySQL服务后，内存表的数据会丢失，但表结构依然存在；而临时表在会话结束或连接关闭后，表结构和数据都会完全被移除。

详细回答第12点答案：

索引失效是数据库查询性能的常见“杀手”。为了让你快速建立整体认知，我先将最常见的失效场景、原因和应对思路汇总在下表中。

失效场景 关键原因简述 主要优化思路
对索引列使用函数或运算 索引存储的是原始值，计算后的值无法直接匹配B+树结构。 重写查询，将运算移至条件值一侧。
发生隐式类型转换 数据库需要自动转换类型，相当于对列应用了转换函数。 确保查询条件与列数据类型完全一致。
违反最左前缀原则 复合索引像电话簿，必须按顺序（姓→名）使用，跳过左边列就无法有效定位。 创建/调整索引顺序，查询时包含最左列。
使用 OR 连接条件 若OR两侧条件并非都有索引，优化器可能认为合并结果集成本高，不如全表扫描。 为所有相关列建索引或改用UNION。
LIKE 以 % 开头 索引按前缀组织，前导通配符使其失去有序性，如同查无目录的字典。 尽量避免前导通配符；考虑全文检索。
查询条件使用不等于 !=, NOT IN 等条件通常需要筛选出大部分数据，优化器可能认为全表扫描更快。 评估业务是否可用更精确的条件（如IN）替代。
数据分布导致优化器放弃 当查询可能返回超过表总数据量约20%-30%时，使用索引（涉及随机IO）的成本可能已高于直接顺序扫描全表。 增加更多过滤条件缩小结果集；使用覆盖索引。

🔧 如何诊断索引失效问题

当怀疑查询没有使用索引时，可以借助数据库自带的工具进行诊断。

  1. 使用 EXPLAIN 分析执行计划 这是最核心的命令。在SQL语句前加上 EXPLAIN（如 EXPLAIN SELECT * FROM ...），数据库会返回该语句的执行计划，而非真实数据。你需要重点关注以下几个字段：

    • type：表示连接类型，从优到劣有 const、eq_ref、ref、range、index、ALL。如果出现 index 或 ALL，通常意味着性能不佳（全索引扫描或全表扫描）。
    • key：表示实际使用的索引。如果为 NULL，则说明未使用索引。
    • rows：表示预估需要扫描的行数，数值越小越好。
    • Extra：包含额外信息。如出现 Using filesort（需要额外排序）或 Using temporary（需要创建临时表），则需警惕。

  2. 开启慢查询日志 通过配置慢查询日志，可以自动记录执行时间超过指定阈值的SQL语句，这有助于发现那些因索引失效而导致性能低下的“罪魁祸首”。

💎 核心原则与最佳实践

要避免索引失效，关键在于理解其工作原理并遵循一些最佳实践：

  • 理解B+树结构：索引通常采用B+树结构，它像一本有序的字典。对索引列的计算、函数处理或类型转换，相当于破坏了字典的排序规则，导致无法快速查找。
  • 设计高性能索引
    • 选择性原则：为选择性高（即不同值多，如用户ID、手机号）的列创建索引效果更好。像“性别”这种只有几个枚举值的低选择性列，通常不适合单独建索引。
    • 覆盖索引：如果索引包含了查询所需的所有字段（例如 SELECT name, age FROM users WHERE name = ...，索引是 (name, age)），数据库可以直接从索引中获取数据，避免回表查询，效率极高。在无法避免 %xxx% 模糊查询时，覆盖索引是常用解决方案。
    • 联合索引的列顺序：遵循 “最左前缀匹配原则” 。将查询中最常用、选择性最高的列放在联合索引的左边。
  • 编写索引友好的SQL：在编写SQL时，要有意识地避免对索引列进行“加工”，确保查询条件与索引列的类型严格匹配。