MySQL索引、事务与常见问题

一、索引（Index）入门：让查询“有导航”

1.1 索引是什么？

简单类比：

• 没有索引：查字典时只能一页一页翻，从头翻到尾。
• 有索引：先翻目录或拼音表，快速定位到某一页。

在 MySQL 中：

• 索引 = 一种特殊的数据结构（类似有序目录）；
• 作用是：加快查询速度。

如果没有索引，查询 WHERE name = '张三' 时，MySQL 可能需要从第一行开始，一行一行对比，直到找到（这叫全表扫描）。

1.2 创建索引的基本语法

假设我们有 users 表：

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

可以给 name 和 age 建索引：

-- 普通索引
CREATE INDEX idx_users_name ON users(name);

-- 组合索引（联合索引）
CREATE INDEX idx_users_age_name ON users(age, name);

小提示：一般索引名习惯写成 idx_表名_列名 的形式。

1.3 什么时候需要索引？

• 经常被用来做查找条件的列：例如 WHERE email = ?。
• 经常用来排序的列：例如 ORDER BY created_at DESC。
• 经常参与连接（JOIN）的列：例如 articles.user_id 对应 users.id。

什么时候不要乱建索引：

• 表很小（几十行、几百行），索引意义不大。
• 经常变动的列（频繁更新），索引维护代价会比较大。

---

二、事务（Transaction）：保证一组操作“要么都成功，要么都失败”

2.1 什么是事务？

典型场景：转账

• 从 A 账号扣 100 元；
• 给 B 账号加 100 元。

这两个操作必须要么都成功，要么都失败，不能出现只扣了 A 没给 B 的情况。

事务（Transaction）就是把多条 SQL 当成一个整体来执行：

• 全都执行成功 → 提交（COMMIT）；
• 其中有问题 → 回滚（ROLLBACK）。

2.2 手动控制事务

-- 开始事务
START TRANSACTION;

-- 1. 从 A 账户扣钱
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- 2. 给 B 账户加钱
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 如果上面两条都成功
COMMIT;

-- 如果中间发现有问题（比如余额不足），可以：
ROLLBACK;

注意：

• 事务通常在支持事务的存储引擎上使用（比如 InnoDB）。
• 开启事务后，直到 COMMIT 之前，其他连接可能还看不到你的修改（跟隔离级别有关，入门先知道有这个现象即可）。

---

三、常见存储引擎：InnoDB vs MyISAM（了解）

• InnoDB
  • 支持事务。
  • 支持行级锁，并发性能更好。
  • 是 MySQL 里目前最常用、默认的存储引擎。
• MyISAM
  • 不支持事务。
  • 以前用得较多，现在新项目基本都首选 InnoDB。

在创建表时可以显式指定：

CREATE TABLE demo_table (
    id INT PRIMARY KEY AUTO_INCREMENT
) ENGINE = InnoDB;

---

四、初学者常见问题与排查方向

4.1 中文乱码问题

症状：

• 命令行或程序里插入中文，查出来是一堆 ??? 或乱码。

排查思路：

• 确认数据库、表的字符集（建议使用 utf8mb4）。

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

• 创建库、表时显式指定：

CREATE DATABASE demo
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

4.2 “忘写 WHERE” 导致误操作

症状：

• 一条 UPDATE 或 DELETE 把整张表都改了 / 删了。

避免方法：

• 养成习惯：先写 WHERE，再写 SET/DELETE。
• 在线上环境开发时，可以让运维开启安全模式（例如不允许无条件 UPDATE/DELETE，具体看工具/客户端）。

4.3 唯一约束冲突（Duplicate entry）

症状：

• 插入或更新时报错：Duplicate entry 'xxx' for key 'xxx'。

原因：

• 违反了 UNIQUE 或 PRIMARY KEY 约束，例如：邮箱列设置了唯一约束，而两条记录邮箱相同。

排查方式：

• 查看表结构，确认哪些字段有唯一约束：

SHOW CREATE TABLE users\G

---

五、EXPLAIN：看看 SQL 是怎么跑的（入门级）

当一条 SQL 很慢时，可以用 EXPLAIN 来大致看看 MySQL 是怎么执行的：

EXPLAIN
SELECT * FROM users WHERE email = 'zhangsan@test.com';

你会看到一行输出，包含 type、key、rows 等字段：

• key：表示使用了哪个索引（如果是 NULL，说明可能没用上索引）。
• rows：大概扫描了多少行（越小越好）。

入门阶段你只需要知道：

• 慢查询通常可以通过加合适的索引、调整 SQL 写法来优化；
• EXPLAIN 是用来帮助你理解“这条 SQL 是怎么跑的”的工具。

---

六、本篇小结（基础 MySQL 完成度检查）

到目前为止，如果你能做到：

• 会用增删改查 SQL 操作单表；
• 能写出简单的多表 JOIN 查询（特别是 INNER JOIN 和 LEFT JOIN）；
• 理解主键、唯一约束、大致知道外键是干什么的；
• 知道索引可以加速查询，事务可以保证一组操作的一致性；

那么你可以认为：MySQL 的“基础部分”已经学完一轮了。

接下来更重要的是多结合实际项目：

• 用 MySQL 给一个小项目（比如博客、记账本）设计几张表；
• 写出项目中真实会用到的查询语句；
• 当感觉某条查询越来越慢时，再回来深入学习索引、执行计划和性能调优。+