SQL详细语法教程（六）存储+索引-EW帮帮网

一、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 NULL
  AND COUNT_STAR = 0;

通过以上内容，我们系统梳理了 MySQL 存储引擎和索引的核心知识，从基础概念到实战优化，涵盖了日常开发和运维中的关键场景。合理运用这些知识，能显著提升 MySQL 数据库的性能和稳定性。

SQL详细语法教程（六）存储+索引