一、基础概念题答案
- 什么是 SQL 优化？其核心目标是什么？
SQL 优化是通过调整 SQL 语句结构、数据库表结构、索引设计等方式，提升 SQL 语句执行效率的过程。核心目标有三个：减少数据库资源消耗（如 CPU、内存、IO）、缩短 SQL 执行时间（满足业务响应需求）、提升数据库并发处理能力（避免单条 SQL 阻塞大量请求）。
- 索引的本质是什么？在数据库中，索引主要有哪些数据结构，各有什么优缺点？
索引的本质是帮助数据库高效查询数据的数据结构，相当于书籍的 “目录”，避免全表扫描。
常见数据结构及优缺点：
-
B + 树：主流数据库（MySQL、PostgreSQL）默认索引结构。优点：① 平衡树结构，查询效率稳定（时间复杂度 O (log n)）；② 叶子节点有序且串联，支持范围查询和排序；③ 非叶子节点仅存索引键，内存利用率高。缺点：插入 / 删除需维护树平衡，性能略低于哈希索引。
-
哈希索引：基于哈希表实现。优点：等值查询效率极高（时间复杂度 O (1)）。缺点：① 不支持范围查询、排序；② 存在哈希冲突，需额外处理（如链表法）；③ 不适用于高频更新场景。
-
R 树：多用于空间数据索引（如 GIS 系统）。优点：支持多维数据范围查询。缺点：在非空间场景下效率低于 B + 树，维护成本高。
- 聚簇索引和非聚簇索引的区别是什么？以 InnoDB 存储引擎为例，说明其默认的索引类型及特点。
核心区别：数据是否与索引存储在一起。
| 对比维度 | 聚簇索引 | 非聚簇索引 |
|---|---|---|
| 数据存储位置 | 索引叶子节点存储完整数据 | 索引叶子节点存储主键值 |
| 查询效率 | 等值 / 范围查询效率高 | 需回表（通过主键查数据） |
| 数量限制 | 一张表仅能有一个 | 一张表可有多個 |
| 适用场景 | 主键查询、高频范围查询 | 非主键的高频查询 |
| InnoDB 默认索引类型：聚簇索引。特点：① 以主键（PRIMARY KEY）作为聚簇索引，若未显式定义主键，会选择唯一非空索引作为聚簇索引；若均无，则自动生成隐藏主键（6 字节 row_id）；② 非聚簇索引（如普通索引、联合索引）的叶子节点存储主键值，查询时需通过主键 “回表” 获取完整数据（覆盖索引可避免回表）。 |
二、SQL 语句优化题答案
- 以下 SQL 语句存在性能问题，请指出问题所在并给出优化方案：
sq取消自动换行复制
SELECT * FROM order_info WHERE user_id IN (SELECT user_id FROM user_info WHERE age > 30) AND order_time < '2024-01-01';
-
问题所在：① IN子查询可能导致数据库执行 “先查子查询，再逐行匹配主查询” 的低效逻辑，尤其当user_info表age > 30的记录较多时，会产生大量临时数据；② SELECT *会查询所有字段，若无需全部字段，会浪费 IO 资源，且无法利用覆盖索引；③ 未明确order_info表是否有user_id、order_time相关索引，可能导致全表扫描。
-
优化方案：
方案 1：将IN子查询改为JOIN，减少临时数据生成：
sql取消自动换行复制
SELECT oi.user_id, oi.order_time, oi.order_amount -- 只查需要的字段，避免SELECT *
FROM order_info oi
JOIN user_info ui ON oi.user_id = ui.user_id
WHERE ui.age > 30
AND oi.order_time < '2024-01-01';
方案 2：为user_info(age, user_id)、order_info(user_id, order_time)创建联合索引，进一步提升查询效率（user_info索引覆盖子查询字段，order_info索引支持条件过滤）。
- 当 SQL 语句中使用了OR条件时，可能会导致索引失效，请问为什么？如何避免这种情况，给出具体的优化示例。
- 索引失效原因：数据库优化器认为OR连接的多个条件若分别对应不同索引，需多次索引查询后合并结果，效率可能低于全表扫描；若部分条件无对应索引，会直接触发全表扫描。例如WHERE a = 1 OR b = 2，若仅a有索引，b无索引，优化器会放弃索引，选择全表扫描。
- 避免方案：
方案 1：为OR连接的所有条件字段创建联合索引（适用于条件字段固定的场景）。
示例：原 SQL（a有索引，b无索引，索引失效）：
sql取消自动换行复制
SELECT * FROM table WHERE a = 1 OR b = 2;
优化：创建联合索引(a, b)，或分别为a、b创建独立索引（MySQL 8.0 + 支持 “索引合并”，可利用多个独立索引），优化后 SQL 不变，可触发索引查询。
方案 2：将OR拆分为UNION（适用于条件字段不适合建联合索引的场景）：
sql取消自动换行复制
SELECT * FROM table WHERE a = 1
UNION -- 去重，若确认无重复可使用UNION ALL（效率更高）
SELECT * FROM table WHERE b = 2;
- 解释什么是 “慢查询”，如何开启 MySQL 的慢查询日志？拿到慢查询日志后，通常会使用哪些工具或方法来分析慢查询语句？
-
慢查询定义：执行时间超过预设阈值（默认long_query_time = 10秒）的 SQL 语句，或未使用索引的查询（需开启log_queries_not_using_indexes）。
-
开启 MySQL 慢查询日志（以 MySQL 5.7 + 为例）：
方式 1：临时开启（重启后失效）：
plaintext取消自动换行复制
SET GLOBAL slow_query_log = ON; -- 开启慢查询日志
SET GLOBAL long_query_time = 2; -- 设置阈值为2秒（建议根据业务调整）
SET GLOBAL slow_query_log_file = '/var/lib/mysql/slow.log'; -- 指定日志存储路径
SET GLOBAL log_queries_not_using_indexes = ON; -- 记录未使用索引的查询
方式 2：永久开启（修改配置文件my.cnf/my.ini）：
i取消自动换行复制
[mysqld]
slow_query_log = ON
long_query_time = 2
slow_query_log_file = /var/lib/mysql/slow.log
log_queries_not_using_indexes = ON
修改后重启 MySQL：systemctl restart mysqld。
- 慢查询日志分析工具 / 方法：
① mysqldumpslow（MySQL 自带工具）：快速统计慢查询类型，例如：
b取消自动换行复制
mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log -- 按查询次数排序，显示前10条
② pt-query-digest（Percona Toolkit 工具）：详细分析，生成可视化报告，支持过滤、分组，例如：
bash取消自动换行复制
pt-query-digest /var/lib/mysql/slow.log > slow_analysis.log -- 生成分析报告
③ 可视化工具：Navicat、MySQL Workbench 的 “慢查询日志分析” 功能，或第三方工具（如 Sqlyog）。
- 对于分页查询，当页码较大时（如LIMIT 10000, 20），SQL 语句执行效率会明显下降，分析其原因并提供至少两种优化方案，要求给出具体的 SQL 示例。
-
效率下降原因：LIMIT 10000, 20的逻辑是 “扫描前 10020 条数据，丢弃前 10000 条，返回后 20 条”，若无合适索引，会触发全表扫描，数据量越大，效率越低；即使有索引，也需遍历大量索引节点后回表。
-
优化方案：
方案 1：利用主键有序性，通过 “主键过滤” 减少扫描范围（适用于主键自增场景）：
原 SQL（低效）：
sql取消自动换行复制
SELECT * FROM order_info ORDER BY order_id LIMIT 10000, 20;
优化后（先查主键，再回表）：
sql取消自动换行复制
SELECT oi.*
FROM order_info oi
JOIN (SELECT order_id FROM order_info ORDER BY order_id LIMIT 10000, 20) AS t
ON oi.order_id = t.order_id;
原理：子查询仅扫描索引（order_id为主键索引），获取目标主键后，通过主键回表查询完整数据，减少扫描行数。
方案 2：使用 “条件分页”，通过上一页的最后一条数据的主键 / 索引字段，缩小查询范围（适用于已知上一页边界的场景，如滚动加载）：
假设上一页最后一条数据的order_id = 10000，优化后 SQL：
sql取消自动换行复制
SELECT * FROM order_info WHERE order_id > 10000 ORDER BY order_id LIMIT 20;
原理：直接通过order_id > 10000过滤，仅扫描 20 条数据，无需跳过前 10000 条，效率极大提升。
方案 3：创建覆盖索引（若分页查询仅需部分字段）：
若查询字段为order_id、order_time、user_id，创建覆盖索引(order_id, order_time, user_id)，SQL：
plaintext取消自动换行复制
SELECT order_id, order_time, user_id FROM order_info ORDER BY order_id LIMIT 10000, 20;
原理：索引包含所有查询字段，无需回表，直接从索引中获取数据，提升效率。
三、索引优化题答案
- 假设存在一张商品表（product），包含字段：product_id（主键）、category_id（商品分类 ID）、product_name（商品名称）、price（价格）、create_time（创建时间）。现有高频查询场景：“查询某一分类下价格大于 100 元且创建时间在近 30 天内的商品名称和价格”，请设计合适的索引，并说明设计理由。
补充：若create_time的范围过滤后数据量已很小，也可简化为(category_id, create_time, price)，但包含product_name的覆盖索引效率更高。
-
索引设计：创建联合索引(category_id, create_time, price, product_name)。
-
设计理由：
① 遵循 “最左前缀原则”：查询条件中category_id是 “等值条件”（某一分类），优先作为索引第一列，可快速过滤出目标分类的所有商品；
② create_time是 “范围条件”（近 30 天内），作为索引第二列，在category_id过滤后，进一步缩小时间范围；
③ price是 “范围条件”（大于 100 元），作为索引第三列，继续过滤价格符合条件的商品；
④ product_name和price是查询结果字段（商品名称和价格），将其加入索引，形成 “覆盖索引”，避免回表查询，直接从索引中获取结果，提升效率。
- 什么是 “索引失效”？列举至少 5 种导致索引失效的常见情况，并分别给出对应的避免方法。
-
索引失效定义：SQL 语句本应使用索引查询，但因条件或语句结构问题，数据库优化器放弃索引，转而使用全表扫描的现象。
-
常见情况及避免方法：
① 索引字段使用函数或运算：如WHERE SUBSTR(product_name, 1, 2) = '手机'（product_name有索引）。
避免方法：将函数运算转移到右侧，如WHERE product_name LIKE '手机%'（可触发前缀索引）；或创建函数索引（如INDEX idx_func (SUBSTR(product_name, 1, 2))）。
② 索引字段类型不匹配：如product_id是 int 类型，查询时用字符串WHERE product_id = '123'（隐式类型转换）。
避免方法：确保查询条件的值类型与索引字段类型一致，如WHERE product_id = 123。
③ 使用NOT IN、!=、<>：如WHERE category_id NOT IN (1,2,3)（category_id有索引）。
避免方法：用NOT EXISTS或范围查询替代，如WHERE category_id < 1 OR category_id > 3（若分类 ID 连续）。
④ LIKE以通配符开头：如WHERE product_name LIKE '%手机%'（product_name有索引）。
避免方法：尽量使用前缀匹配（LIKE '手机%'）；若需后缀或中间匹配，可使用全文索引（如 MySQL 的 FULLTEXT 索引）。
⑤ OR连接的条件部分无索引：如WHERE category_id = 1 OR price > 100（仅category_id有索引）。
避免方法：为所有OR条件字段创建索引（独立索引或联合索引），或拆分为UNION查询（见二.2 题）。
⑥ 查询条件包含IS NULL/IS NOT NULL：若索引字段允许为 NULL，WHERE price IS NULL可能失效（视数据库版本和数据分布而定）。
避免方法：字段设计时尽量避免 NULL（用默认值替代，如价格默认 0）；或创建包含 NULL 的索引（MySQL 8.0 + 对 NULL 的索引支持更友好）。
- 如何判断一张表的索引是否过多？过多的索引会带来哪些负面影响？在实际工作中，如何平衡查询性能和索引维护成本？
- 判断索引过多的方法：
① 索引数量与表字段数量对比：若索引数量超过表字段数量的 2-3 倍，需警惕（如 10 个字段的表，索引超过 20 个）；
② 索引使用率低：通过数据库工具查看索引使用情况（如 MySQL 的sys.schema_unused_indexes视图，或performance_schema的index_io_usage表），长期未使用（如 3 个月以上）的索引可视为冗余；
③ 索引重复或重叠：如已存在联合索引(a,b)，再创建索引(a)，则(a)为冗余索引（联合索引(a,b)可覆盖(a)的查询场景）。
- 过多索引的负面影响：
① 写入性能下降：插入、更新、删除数据时，需同步维护所有相关索引（如 B + 树的分裂、合并），索引越多，写入耗时越长；
② 占用存储空间：索引需单独存储，过多索引会浪费磁盘空间（尤其是大表，一个索引可能占用 GB 级空间）；
③ 优化器选择负担加重：索引过多时，数据库优化器需遍历更多索引组合，可能导致选择低效索引。
- 平衡查询性能与索引维护成本的策略：
① 按需创建索引：仅为高频查询（如 QPS>10）的条件字段创建索引，低频查询（如每日一次的统计查询）无需建索引；
② 优先使用联合索引：用一个联合索引覆盖多个查询场景（如(a,b)可覆盖a的等值查询、a+b的组合查询），减少索引数量；
③ 定期清理冗余索引：每季度或半年审计一次索引，删除未使用、重复或重叠的索引；
④ 分场景优化：写入密集型表（如订单表）尽量少建索引；查询密集型表（如商品详情表）可适当多建索引，但需控制总量。
- 联合索引的 “最左前缀原则” 是什么？假设创建了联合索引(a, b, c)，请判断以下 SQL 语句是否能使用该索引，并说明原因：
- 最左前缀原则定义：联合索引的查询效率取决于 “从左到右的连续匹配”，即查询条件需包含索引的最左列，且后续列需连续匹配（等
