SQL详细语法教程（六）存储+索引
一、MySQL 存储引擎
1. 存储引擎基础概念
存储引擎是 MySQL 的 "数据存储引擎"，决定了表的存储格式、索引方式、事务支持等核心特性。就像不同的文件格式（.docx/.pdf）适用于不同场景，MySQL 的存储引擎也可按需选择。
2. 主流存储引擎对比
| 特性 | InnoDB 🛡️ | MyISAM 📄 | Memory ⚡ |
|---|---|---|---|
| 事务支持 | ✅ 支持（ACID） | ❌ 不支持 | ❌ 不支持 |
| 锁粒度 | 行级锁（高并发友好） | 表级锁（写入阻塞） | 表级锁 |
| 索引结构 | B+Tree（聚簇索引） | B+Tree（非聚簇索引） | 哈希 / BTree |
| 存储位置 | 磁盘（持久化） | 磁盘（持久化） | 内存（重启丢失） |
| 外键支持 | ✅ 支持 | ❌ 不支持 | ❌ 不支持 |
| 适用场景 | 电商交易 / 金融系统 | 日志 / 报表系统 | 临时缓存 / 会话数据 |
语法示例：
-- 创建InnoDB表（默认引擎）
CREATE TABLE orders (id INT PRIMARY KEY AUTO_INCREMENT,user_id INT,amount DECIMAL(10,2),FOREIGN KEY (user_id) REFERENCES users(id) -- 支持外键
) ENGINE=InnoDB;-- 创建MyISAM表
CREATE TABLE logs (id INT PRIMARY KEY AUTO_INCREMENT,content TEXT
) ENGINE=MyISAM;-- 创建Memory表
CREATE TABLE session_data (session_id VARCHAR(32) PRIMARY KEY,data JSON
) ENGINE=Memory;
二、MySQL 索引
索引是数据表的 "目录"，能加速查询（类似书籍目录快速定位章节）。
（一）索引结构
B-tree 🌳
- 多路平衡查找树，每个节点存储数据
- 支持范围查询和等值查询，但叶节点无链表关联
B+tree 🌲（MySQL 默认）
- B-tree 变种，仅叶节点存储数据
- 叶节点形成双向链表，更适合范围查询（如
BETWEEN/ 排序）
Hash 🔍
- 基于哈希表实现，等值查询（
=）极快 - 不支持范围查询（
>,<）和排序
- 基于哈希表实现，等值查询（
（二）索引分类
| 类型 | 特点 | 图标 | 语法示例 |
|---|---|---|---|
| 普通索引 | 无唯一性限制，仅加速查询 | 🔖 | CREATE INDEX idx_name ON users(name); |
| 唯一索引 | 列值唯一，允许多个 NULL | 🔒 | CREATE UNIQUE INDEX idx_email ON users(email); |
| 主键索引 | 特殊唯一索引，无 NULL，表唯一 | 🏠 | CREATE TABLE t(id INT PRIMARY KEY); |
| 全文索引 | 文本内容搜索（CHAR/VARCHAR/TEXT） | 📚 | CREATE FULLTEXT INDEX idx_content ON articles(content); |
| 组合索引 | 多列组合，遵循最左前缀原则 | 🔗 | CREATE INDEX idx_name_age ON users(name, age); |
（三）索引管理语法
- 创建索引
-- 普通索引
CREATE INDEX idx_phone ON users(phone);-- 唯一索引
ALTER TABLE users ADD UNIQUE INDEX idx_idcard(idcard);-- 全文索引（查询时用MATCH...AGAINST）
SELECT * FROM articles
WHERE MATCH(content) AGAINST('MySQL 索引');
- 删除索引
DROP INDEX idx_name ON users;
- 查看索引
SHOW INDEX FROM users; -- 查看users表所有索引
（四）索引性能分析工具
慢查询日志 ⏱️
记录执行时间超过阈值的 SQL：-- 开启慢查询日志 SET GLOBAL slow_query_log = ON; -- 设置阈值（1秒） SET GLOBAL long_query_time = 1; -- 日志位置 SHOW VARIABLES LIKE 'slow_query_log_file';EXPLAIN 分析执行计划 📊
查看 SQL 是否使用索引：EXPLAIN SELECT * FROM users WHERE name = '张三';type列显示访问类型（ref/range为使用索引，ALL为全表扫描）key列显示实际使用的索引
（五）索引使用规则
最左前缀原则 🔗
组合索引(a,b,c)仅支持：WHERE a=?WHERE a=? AND b=?WHERE a=? AND b=? AND c=?
不支持WHERE b=?（跳过最左列）
索引失效场景 ❌
- 函数操作：
WHERE SUBSTR(name,1,1)='张' - 隐式转换：
WHERE phone='13800138000'（若 phone 为 INT 类型） - 前导通配符：
WHERE name LIKE '%三'
- 函数操作：
覆盖索引 ✅
索引包含查询所需全部字段，无需回表查询：-- 索引(idx_name_age)包含name和age SELECT name, age FROM users WHERE name = '张三'; -- 无需回表
三、索引性能优化进阶
（一）执行计划深度解析（EXPLAIN 输出详解）
使用EXPLAIN分析 SQL 时，重点关注以下字段：
| 字段 | 含义说明 | 优化提示 |
|---|---|---|
id | 查询序列号，标识 SQL 执行顺序（值越大越先执行） | 子查询过多时可考虑拆分为 JOIN 提升效率 |
select_type | 查询类型（SIMPLE/PRIMARY/SUBQUERY 等） | SUBQUERY 过多可能导致性能问题 |
table | 涉及的表名 | 可通过别名简化复杂查询 |
type | 访问类型（从优到差：system > const > eq_ref > ref > range > ALL） | 出现 ALL（全表扫描）时需检查是否缺少索引 |
possible_keys | 可能使用的索引列表 | 为空说明无合适索引 |
key | 实际使用的索引 | 为空说明索引未被使用 |
rows | 预估扫描行数（值越小越好） | 行数远大于实际数据时需优化索引 |
Extra | 额外信息（Using index/Using where/Using filesort 等） | Using filesort（文件排序）需优化索引排序 |
示例：
EXPLAIN
SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.age > 25;
（二）慢查询日志实战配置
- 永久生效配置（my.cnf/my.ini）：
[mysqld]
slow_query_log = 1 # 开启慢查询日志
slow_query_log_file = /var/log/mysql/slow.log # 日志路径
long_query_time = 0.5 # 慢查询阈值（秒）
log_queries_not_using_indexes = 1 # 记录未使用索引的查询
- 分析慢查询日志工具：
# 使用mysqldumpslow分析（MySQL自带）
mysqldumpslow -s t -n 10 /var/log/mysql/slow.log # 按时间排序，显示前10条
（三）索引失效典型场景及解决方案
| 失效场景 | 问题 SQL 示例 | 优化方案 |
|---|---|---|
| 函数操作索引列 | WHERE SUBSTR(phone, 1, 3) = '138' | 创建前缀索引：CREATE INDEX idx_phone ON users(phone(3)); |
| 隐式类型转换 | WHERE user_id = '123'（user_id 为 INT） | 统一数据类型：WHERE user_id = 123 |
| 前导通配符 | WHERE name LIKE '%张三' | 改用后导通配符：WHERE name LIKE '张三%' |
| 组合索引不满足最左前缀 | WHERE age = 25（组合索引为 (name, age)） | 补充左列条件或单独创建 age 索引 |
使用OR连接非索引列 | WHERE name = '张三' OR email = 'a@b.com' | 确保 OR 两边字段都有索引 |
四、存储引擎实战选择指南
（一）引擎选择决策树
是否需要事务支持？
├─ 是 → 是否需要外键？
│ ├─ 是 → InnoDB
│ └─ 否 → InnoDB（仍为最佳选择）
└─ 否 → 数据是否需要持久化？├─ 是 → 读写比例如何？│ ├─ 读多写少 → MyISAM│ └─ 读写均衡 → InnoDB（并发更好）└─ 否 → 数据量是否小且临时？├─ 是 → Memory└─ 否 → InnoDB（避免内存溢出）
（二）特殊场景优化方案
日志系统：
使用 MyISAM + 分区表（按时间分区），示例：CREATE TABLE app_logs (id INT PRIMARY KEY AUTO_INCREMENT,log_time DATETIME,content TEXT ) ENGINE=MyISAM PARTITION BY RANGE (TO_DAYS(log_time)) (PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')) );高并发临时缓存：
Memory 引擎 + 定期持久化，示例：-- 创建内存表 CREATE TABLE hot_data (id INT PRIMARY KEY,count INT ) ENGINE=Memory;-- 定时同步到磁盘表 CREATE EVENT sync_hot_data ON SCHEDULE EVERY 5 MINUTE DO INSERT INTO hot_data_disk SELECT * FROM hot_data ON DUPLICATE KEY UPDATE count = VALUES(count);
五、索引设计最佳实践
（一）高选择性索引优先
选择性公式：选择性 = distinct(列值) / 总记录数
- 高选择性（接近 1）：身份证号、手机号等（适合建索引）
- 低选择性（接近 0）：性别、状态等（不适合单独建索引）
示例：
-- 计算选择性
SELECT COUNT(DISTINCT email)/COUNT(*) AS email_selectivity,COUNT(DISTINCT gender)/COUNT(*) AS gender_selectivity
FROM users;
（二）联合索引字段顺序原则
- 基数优先：基数高（不同值多）的字段放前面
- 范围查询放最后：范围查询（>、<、BETWEEN）后的字段无法使用索引
反例：(age, name) 不如 (name, age)（name 基数更高）
正例：(status, create_time)（status 过滤后再按时间范围查询）
（三）索引维护技巧
定期重建碎片化索引：
-- 查看索引碎片 SHOW TABLE STATUS LIKE 'users';-- 重建索引（InnoDB） ALTER TABLE users ENGINE=InnoDB; -- 会重建所有索引-- 优化单个索引 REBUILD INDEX idx_name ON users;监控索引使用率：
-- 查看未使用的索引（需开启performance_schema） SELECT OBJECT_NAME AS table_name,INDEX_NAME AS index_name FROM performance_schema.table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NOT NULLAND COUNT_STAR = 0;
通过以上内容，我们系统梳理了 MySQL 存储引擎和索引的核心知识，从基础概念到实战优化，涵盖了日常开发和运维中的关键场景。合理运用这些知识，能显著提升 MySQL 数据库的性能和稳定性。