Day5：SQL 核心语法进阶 & 索引入门（性能优化第一步）

作为 MySQL 基础学习的第五天，我们从「增删改查」的基础语法，进阶到复杂查询和性能优化的核心 —— 索引。这篇内容既适合新手巩固 SQL 语法，也能让你理解 “为什么慢查询要加索引”，

一、先回顾：SQL 核心语法（基础回顾）

1. 建表（基础准备）

-- 创建用户表
CREATE TABLE `user` (
  `id` INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
  `username` VARCHAR(50) NOT NULL COMMENT '用户名',
  `age` TINYINT COMMENT '年龄',
  `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户基础表';

-- 创建订单表（关联用户表）
CREATE TABLE `order` (
  `id` INT PRIMARY KEY AUTO_INCREMENT COMMENT '订单ID',
  `user_id` INT NOT NULL COMMENT '关联用户ID',
  `amount` DECIMAL(10,2) NOT NULL COMMENT '订单金额',
  `status` TINYINT DEFAULT 0 COMMENT '订单状态：0-待支付 1-已支付 2-已取消',
  `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  -- 外键关联（可选，新手先理解逻辑关联即可）
  CONSTRAINT `fk_order_user` FOREIGN KEY (`user_id`) REFERENCES `user`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户订单表';

2. 基础 DML 语法（增删改）

-- 新增：批量插入用户
INSERT INTO `user` (username, age) VALUES 
('张三', 25), ('李四', 30), ('王五', 28);

-- 新增：插入订单（关联用户ID）
INSERT INTO `order` (user_id, amount, status) VALUES 
(1, 99.90, 1), (1, 199.00, 0), (2, 59.90, 1);

-- 修改：更新张三的年龄为26
UPDATE `user` SET age = 26 WHERE username = '张三';

-- 删除：删除已取消的订单（演示用，实际生产慎用DELETE）
DELETE FROM `order` WHERE status = 2;

二、SQL 进阶查询（核心重点）

基础查询（SELECT * FROM xxx）满足不了实际业务，这部分是日常开发高频用到的进阶语法：

1. 条件查询 + 排序 + 分页（最常用）

-- 需求1：查询25岁以上的用户，按创建时间倒序，分页显示第1页（每页10条）
SELECT id, username, age 
FROM `user` 
WHERE age > 25 
ORDER BY create_time DESC 
LIMIT 0, 10; -- LIMIT 起始偏移量, 每页条数（偏移量= (页码-1)*每页条数）

-- 需求2：查询用户ID=1的所有已支付订单，按金额倒序
SELECT o.id, o.amount, o.create_time 
FROM `order` o 
WHERE o.user_id = 1 AND o.status = 1 
ORDER BY o.amount DESC;

2. 聚合查询（统计类需求）

-- 需求1：统计所有用户的总数
SELECT COUNT(*) AS user_total FROM `user`;

-- 需求2：统计每个用户的订单总数、订单总金额
SELECT 
  u.id, 
  u.username, 
  COUNT(o.id) AS order_count,  -- 订单数
  SUM(o.amount) AS total_amount -- 总金额
FROM `user` u
LEFT JOIN `order` o ON u.id = o.user_id  -- 左连接：保证无订单的用户也显示
GROUP BY u.id, u.username; -- 按用户ID分组（GROUP BY必须跟聚合函数搭配）

-- 需求3：统计订单总金额>100的用户（HAVING过滤聚合结果）
SELECT 
  u.username, 
  SUM(o.amount) AS total_amount
FROM `user` u
JOIN `order` o ON u.id = o.user_id
GROUP BY u.username
HAVING total_amount > 100; -- WHERE过滤行，HAVING过滤分组结果

3. 子查询（嵌套查询）

-- 需求：查询有订单的用户（子查询版）
SELECT username 
FROM `user` 
WHERE id IN (SELECT DISTINCT user_id FROM `order`); -- 子查询：先查所有有订单的用户ID

-- 等价写法（JOIN版，性能更优，推荐）
SELECT DISTINCT u.username 
FROM `user` u
JOIN `order` o ON u.id = o.user_id;

三、索引入门：为什么你的 SQL 查得慢？

1. 索引是什么？（通俗理解）

索引就像书籍的 “目录”—— 没有目录时，找某章内容需要翻遍整本书（全表扫描）；有目录时，直接翻到对应页码（快速定位数据）。

• **优点 ** 极大提升查询效率（尤其是数据量大时）；

• **缺点 ** 增删改（INSERT/UPDATE/DELETE）会变慢（需要维护索引），占用额外存储。

2. 索引的核心语法

（1）创建索引（高频场景）

-- 1. 单字段索引：给订单表的user_id加索引（查询用户订单时常用）
CREATE INDEX idx_order_user_id ON `order`(user_id);

-- 2. 联合索引：给订单表的user_id+status加索引（多条件查询更优）
CREATE INDEX idx_order_user_status ON `order`(user_id, status);

-- 3. 唯一索引：保证用户名不重复（类似主键，但主键只能有1个）
CREATE UNIQUE INDEX idx_user_username ON `user`(username);

**（2）查看 / 删除索引 **

-- 查看表的所有索引
SHOW INDEX FROM `order`;

-- 删除索引（不需要时及时删除，减少性能损耗）
DROP INDEX idx_order_user_id ON `order`;

3. 索引使用的核心原则（新手必看）

适合加索引的场景：

查询条件常用的字段（WHERE、JOIN 后的字段，如 user_id、status）；

排序 / 分组字段（ORDER BY、GROUP BY 后的字段）；

唯一标识字段（如手机号、身份证号，加唯一索引）。

不适合加索引的场景：

数据量极小的表（比如只有几十条数据，全表扫描比索引更快）；

频繁更新的字段（如订单状态，改得越频繁，索引维护成本越高）；

低基数字段（如性别（男 / 女），索引筛选效果差）。

避坑：索引失效的常见情况：

-- 反例1：字段加了函数，索引失效（idx_order_user_id 会失效）
SELECT * FROM `order` WHERE SUBSTR(user_id, 1, 1) = '1';

-- 反例2：模糊查询以%开头，索引失效
SELECT * FROM `user` WHERE username LIKE '%三'; -- 失效
SELECT * FROM `user` WHERE username LIKE '张%'; -- 生效（前缀匹配）

四、实战案例：优化慢查询

假设我们有 10 万条订单数据，执行以下查询时很慢：

-- 慢查询：查询用户ID=1的已支付订单（无索引时全表扫描）
SELECT * FROM `order` WHERE user_id = 1 AND status = 1;

优化步骤：

给 user_id + status 加联合索引:

CREATE INDEX idx_order_user_status ON `order`(user_id, status);

今天的内容是 MySQL 基础到进阶的关键过渡 ——SQL 进阶语法是日常开发的 “基本功”，索引是性能优化的 “第一步”。建议大家把案例中的 SQL 手动敲一遍，重点体会 “加索引前后的查询速度差异”（可以用 EXPLAIN 命令分析 SQL 执行计划：EXPLAIN SELECT * FROM order WHERE user_id=1;）。

下一篇我们会讲 MySQL 事务和锁机制，关注我的博客，持续解锁 MySQL 核心知识点～