SQL 索引与日志知识点详解及练习题
索引和日志在数据库的高效运行和数据安全中扮演着重要角色。下面我们详细梳理索引和日志的相关知识，并通过练习题加深理解。
一、知识点梳理
（一）索引
- 基本概念：索引是为了加速查询的数据结构，其数据结构为 B + 树。B 代表 Balance（平衡），数据保存在叶子结点中。
- 分类
- 主键索引：唯一标识一行，不允许为空，一张表只能有一个主键。
- 唯一索引：标识一行，允许为空，一张表可以有多个唯一索引。
- 普通索引：允许重复和空值。
- 联合索引（复合索引）：多列组合的索引，索引有编号 1、2、3 等。
- 全文索引：支持关键词索引。
- 创建索引
- 普通索引：create index 索引名称 on 表名 (列名)
- 唯一索引：create unique index 索引名称 on 表名 (列名)（值唯一）
- 复合索引：create index 索引名称 on 表名 (列名列表)
- 全文索引：create fulltext index 索引名称 on 表名 (列名列表)
- 使用场景
- 适合建立索引的列：where、join、order by 频繁使用的列；外键列，可加速表的连接。
- 避免建立索引的列：数据量较小的列；频繁更新的列（写性能低）；性别等只有 2 个数据值的低基数列。
- 前缀索引：对长文本字段（如 255 字符），取前面 N 个字符建立索引，平衡性能与空间。
- 覆盖索引：查询的字段全部在索引中，避免回表，性能更高。
- 全文索引
- 创建索引：create fulltext index 索引名称 on 表名 (列名列表)
- 使用：select .... from ... where match (全文索引对应的列) against (搜索内容 搜索模式)
- 搜索模式：in natural language mode（默认）；in boolean mode（支持通配符 + - *）；with query expansion
- 索引失效
- 可通过 explain select.... 查看索引使用情况。
- 失效的几种情况：使用 or；使用函数；like ‘% 内容’；隐式转换等。
- 相关列名含义：type 为 all 时索引失效；key 为空时索引失效；extra 为 using index 时表示使用覆盖索引。
（二）日志
- redo 日志
- 作用：实现事务的持久化操作，用于崩溃修复。
- 几个参数：innodb_log_file_size（日志大小）；innodb_log_buffer_size（日志缓冲区大小）；innodb_log_files_in_group（日志文件组的默认数量）；innodb_flush_log_at_trx_commit（日志是否提交）。
- 查看参数：show VARIABLES like ‘参数名’
- 日志文件位置：位于服务器安装目录下的 data 目录中。
- binary log（二进制日志）
- 作用：记录数据库的更改操作。
- 几个参数：log_bin（日志前缀）；expire_logs_days（自动清理过期日志，单位为天）；server_id（编号）。
- 查看参数：show VARIABLES like ‘参数名’
- 日志文件位置：位于服务器安装目录下的 data 目录中。
- 慢查询日志：用于记录执行时间超过指定阈值的 SQL 语句，帮助优化查询性能。
二、练习题及解析
（一）选择题
- 以下关于索引的数据结构，说法正确的是（ ）
A. 索引的数据结构是二叉树
B. 索引的数据结构是 B 树，数据保存在非叶子结点
C. 索引的数据结构是 B + 树，数据保存在叶子结点
D. 以上说法都不正确
答案：C
解析：索引的数据结构为 B + 树，其中 B 代表 Balance（平衡），数据保存在叶子结点中，所以 C 选项正确，A、B 选项错误。
- 下列关于主键索引的说法，错误的是（ ）
A. 主键索引可以唯一标识一行数据
B. 一张表只能有一个主键索引
C. 主键索引允许为空
D. 主键索引在数据唯一性方面有重要作用
答案：C
解析：主键索引的特点是唯一标识一行，不允许为空，一张表只能有一个主键索引，A、B、D 选项正确，C 选项错误。
- 下列哪种情况可能导致索引失效（ ）
A. where 子句中使用索引列
B. 使用 like ‘内容 %’
C. 使用 like ‘% 内容’
D. join 操作中使用索引列
答案：C
解析：like ‘% 内容’可能导致索引失效，A、B、D 选项在正常情况下不会导致索引失效，所以选 C。
- 关于 redo 日志的作用，说法正确的是（ ）
A. 记录数据库的更改操作
B. 实现事务的持久化，用于崩溃修复
C. 记录执行缓慢的 SQL 语句
D. 以上说法都不正确
答案：B
解析：redo 日志的作用是事务的持久化操作，崩溃修复，A 选项是 binary log 的作用，C 选项是慢查询日志的作用，所以 B 选项正确。
（二）简答题
- 简述索引的分类及各自特点。
答案：索引主要分为以下几类：
- 主键索引：唯一标识一行，不允许为空，一张表只能有一个主键。
- 唯一索引：标识一行，允许为空，一张表可以有多个唯一索引。
- 普通索引：允许重复和空值。
- 联合索引（复合索引）：多列组合的索引，有编号 1、2、3 等。
- 全文索引：支持关键词索引。
解析：不同类型的索引适用场景不同，主键索引和唯一索引保证数据唯一性，普通索引用于一般查询加速，联合索引适合多列查询，全文索引用于关键词搜索。
- 哪些情况下适合建立索引，哪些情况下应避免建立索引？
答案：适合建立索引的情况：where、join、order by 频繁使用的列；外键列，可加速表的连接。
应避免建立索引的情况：数据量较小的列；频繁更新的列（写性能低）；性别等只有 2 个数据值的低基数列。
解析：建立索引的目的是加速查询，但对于数据量小、频繁更新或低基数的列，建立索引可能不会带来明显性能提升，甚至影响写性能，所以需合理选择。
- 简述 binary log 的作用和相关参数。
答案：binary log（二进制日志）的作用是记录数据库的更改操作。
相关参数有：log_bin（日志前缀）；expire_logs_days（自动清理过期日志，单位为天）；server_id（编号）。
解析：binary log 主要用于数据恢复、主从复制等场景，通过相关参数可配置日志的存储和清理方式，server_id 用于标识服务器，在主从架构中很重要。
（三）案例分析题
现有一个员工表（employee），包含字段：id（员工编号）、name（姓名）、gender（性别）、age（年龄）、department（部门）、salary（工资）。
- 为员工表的 name 字段创建一个普通索引，索引名称为 idx_employee_name。
答案：create index idx_employee_name on employee (name);
解析：按照普通索引的创建格式，指定索引名称 idx_employee_name，表名为 employee，字段为 name。
- 分析为 gender 字段建立索引是否合适，并说明理由。
答案：不合适。因为 gender 字段只有男、女两个值，属于低基数列，建立索引不能有效提升查询性能，反而会增加数据维护成本，降低写性能。
解析：低基数列的索引选择性差，查询时可能还是需要扫描大量数据，所以不适合建立索引。
- 若要查询员工表中部门为 “技术部” 且工资大于 8000 的员工信息，如何通过索引优化该查询？
答案：可以为 department 和 salary 字段创建一个联合索引，如 create index idx_dept_salary on employee (department, salary);
解析：该查询涉及 department 和 salary 两个字段，创建联合索引后，查询时可利用索引快速定位到部门为 “技术部” 且工资大于 8000 的记录，提高查询效率，符合联合索引在多列查询中的优化作用。