MySQL 索引优化技巧

索引是数据库性能的关键，加对了查询快十倍，加错了反而更慢。

1. 索引类型

主键索引：主键自带索引，一张表只能一个。

普通索引：最基本的索引，加速查询。

CREATE INDEX idx_name ON user(name);

唯一索引：字段值不能重复。

CREATE UNIQUE INDEX idx_email ON user(email);

联合索引：多个字段组合的索引。

CREATE INDEX idx_name_age ON user(name, age);

2. 索引失效的情况

1）like 以 % 开头

-- 索引失效
SELECT * FROM user WHERE name LIKE '%张';

-- 索引生效
SELECT * FROM user WHERE name LIKE '张%';

2）对字段做函数运算

-- 索引失效
SELECT * FROM user WHERE LEFT(name,1) = '张';

-- 索引生效
SELECT * FROM user WHERE name LIKE '张%';

3）联合索引不满足最左前缀

联合索引 (name, age)：

-- 生效
SELECT * FROM user WHERE name = '张三';

-- 生效
SELECT * FROM user WHERE name = '张三' AND age = 25;

-- 失效
SELECT * FROM user WHERE age = 25;

4）隐式类型转换

-- phone 是 varchar
-- 索引失效
SELECT * FROM user WHERE phone = 13800001111;

-- 索引生效
SELECT * FROM user WHERE phone = '13800001111';

3. 查看索引使用情况

-- 查看执行计划
EXPLAIN SELECT * FROM user WHERE name = '张三';

-- 重点看 type 字段：
-- system > const > eq_ref > ref > range > index > ALL
-- 最差是 ALL（全表扫描）

4. 索引优化原则

• 为高频查询加索引：不要乱加
• 区分度高的字段优先：性别这种区分度低的不加
• 联合索引把最常用的放左边
• 索引不是越多越好：增删改会变慢
• 避免索引冗余：(a,b) 包含了 (a)

5. 慢查询排查

-- 开启慢查询
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;

-- 查看慢查询日志
SHOW VARIABLES LIKE 'slow_query_log_file';

总结

MySQL 索引优化核心：

• 联合索引满足最左前缀
• like 不要以 % 开头
• 不要对字段做函数运算
• 用 EXPLAIN 看执行计划
• 慢查询找出来再优化

搞懂这些，数据库性能提升一大截。

MySQL 事务隔离级别详解

事务是数据库并发控制的基础，搞懂四个隔离级别，才能解决脏读、幻读问题。

1. 事务 ACID 特性

• 原子性（Atomicity）：要么全成功，要么全回滚
• 一致性（Consistency）：事务前后数据一致
• 隔离性（Isolation）：事务之间互不干扰
• 持久性（Durability）：提交后永久保存

2. 四种隔离级别

MySQL 有四种隔离级别，从上到下越来越严格：

读未提交（READ UNCOMMITTED）

• 可以读到别人还没提交的数据
• 会出现脏读
• 几乎没人用

读提交（READ COMMITTED）

• 只能读到别人已经提交的数据
• 解决了脏读，但会出现不可重复读
• Oracle、SQL Server 默认级别

可重复读（REPEATABLE READ）

• 同一个事务里，读多次结果都一样
• 解决了脏读、不可重复读
• MySQL InnoDB 默认级别
• InnoDB 用间隙锁解决了幻读

串行化（SERIALIZABLE）

• 事务一个一个排队执行
• 最安全，但性能最差
• 几乎没人用

3. 三种读问题

脏读：读到了别人还没提交的数据，别人回滚了，你读到的就是脏数据。

不可重复读：同一个事务里，两次读同一行数据，结果不一样（别人中间改了并提交了）。

幻读：同一个事务里，两次查同一个范围，行数不一样（别人中间插了新行）。

4. 查看和设置隔离级别

-- 查看当前隔离级别
SELECT @@global.tx_isolation;
SELECT @@session.tx_isolation;

-- 设置全局隔离级别
SET GLOBAL tx_isolation = 'READ-COMMITTED';

-- 设置当前会话隔离级别
SET SESSION tx_isolation = 'READ-COMMITTED';

5. 实际应用建议

• 默认就好：MySQL 默认的可重复读已经够用
• 高并发场景：可以降到读提交，提升并发性能
• 金融级场景：用串行化，绝对安全
• 大多数业务：默认级别就行，不用折腾

6. 代码示例

// PDO 事务示例
try {
    $pdo->beginTransaction();

    $pdo->exec("UPDATE account SET money = money - 100 WHERE id = 1");
    $pdo->exec("UPDATE account SET money = money + 100 WHERE id = 2");

    $pdo->commit();
} catch (Exception $e) {
    $pdo->rollBack();
    echo "转账失败：" . $e->getMessage();
}

总结

MySQL 事务隔离级别核心：

• 读未提交：脏读
• 读提交：不可重复读
• 可重复读：默认级别，解决幻读
• 串行化：最安全最慢

大多数业务用默认的可重复读就够了。

MySQL 锁机制详解

MySQL 锁是并发控制的核心，搞懂锁，才能避免死锁和性能问题。

1. 表锁 vs 行锁

表锁：锁整个表，开销小，并发低。

• MyISAM 用的就是表锁
• 适合读多写少的场景

行锁：锁单行数据，开销大，并发高。

• InnoDB 用的就是行锁
• 适合写多的场景

2. 共享锁 vs 排他锁

共享锁（S锁）：读锁，多个读不互斥。

SELECT ... LOCK IN SHARE MODE

排他锁（X锁）：写锁，一个写独占。

SELECT ... FOR UPDATE

3. 间隙锁

InnoDB 用间隙锁防止幻读。锁的是一个范围，不是具体行。

比如你查 id > 10 AND id < 20，间隙锁会把这个区间锁住，别人插不进来。

4. 死锁

两个事务互相等对方的锁，都不放，就死锁了。

避免死锁的方法：

• 按相同顺序访问表
• 事务尽量小，快速提交
• 降低隔离级别
• 加索引，减少行锁范围

5. 查看锁状态

-- 查看当前锁
SHOW OPEN TABLES WHERE In_use > 0;

-- 查看 InnoDB 锁
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

-- 查看锁等待
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

6. 行锁什么时候会变成表锁

行锁失效就会升级成表锁：

• 没用索引
• 索引失效
• 全表扫描

所以加索引不仅是为了查得快，也是为了锁得准。

总结

MySQL 锁的核心：

• InnoDB 默认行锁，MyISAM 是表锁
• 共享锁读、排他锁写
• 间隙锁防幻读
• 死锁要避免：顺序访问、小事务、加索引

搞懂这些，数据库并发问题基本能解决一半。

MySQL 慢查询优化实战技巧

数据库慢查询是生产环境最常见的性能问题之一。掌握慢查询优化技巧，能让系统性能提升数倍。

1. 开启慢查询日志

先开启慢查询日志，找出哪些 SQL 慢：

slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/log/mysql/slow.log

超过1秒的查询就会被记录。

2. 用 Explain 分析

慢 SQL 拿到后，用 EXPLAIN 分析执行计划：

EXPLAIN SELECT * FROM users WHERE name = '张三';

重点看：

• type：访问类型，ALL 是全表扫描，要优化
• key：实际用到的索引
• rows：扫描行数
• Extra：额外信息，Using filesort、Using temporary 要警惕

3. 加索引

最直接的优化就是加索引：

ALTER TABLE users ADD INDEX idx_name(name);

加索引原则：

• WHERE、JOIN、ORDER BY 字段优先
• 区分度高的字段效果好
• 联合索引遵循最左前缀

4. 避免 SELECT *

只查需要的字段：

-- 不好
SELECT * FROM users WHERE id = 1;

-- 好
SELECT id, name FROM users WHERE id = 1;

5. 深分页优化

LIMIT 100000, 10 很慢，因为要扫描10万行：

-- 不好
SELECT * FROM users LIMIT 100000, 10;

-- 好，用子查询
SELECT * FROM users 
WHERE id >= (SELECT id FROM users LIMIT 100000, 1)
LIMIT 10;

6. 大表拆小

单表数据量超过千万行，性能会下降。可以：

• 按时间分表
• 按用户ID分库
• 归档历史数据

7. 总结

慢查询优化的核心思路：先找慢 SQL，再分析执行计划，然后加索引或改写 SQL。优化完记得对比前后效果，确保真的变快了。

MySQL 索引为什么能提速

索引是数据库性能优化的核心，搞懂原理才能用对。

1. 为什么慢查询慢

没有索引，MySQL 就得全表扫描，一行一行找。100万行数据就得扫100万次，当然慢。

有了索引，就像书有目录，直接定位到位置，查几百万行也很快。

2. B+ 树索引

MySQL InnoDB 默认用 B+ 树做索引。

• 树的高度低，一般3-4层
• 一次查询只需3-4次磁盘IO
• 叶子节点存数据，非叶子节点存索引

3. 聚簇索引 vs 二级索引

聚簇索引：数据和索引放在一起，按主键组织。一张表只有一个聚簇索引。

二级索引：索引和数据分开，叶子节点存主键值。查二级索引需要回表查主键。

4. 覆盖索引

如果查询的字段都在索引里，就不用回表了，叫覆盖索引。

-- name有索引
SELECT name FROM users WHERE name = '张三';
-- 覆盖索引，不用回表

5. 最左前缀

联合索引 (a, b, c)，必须从最左列开始用：

• WHERE a = 1 能用
• WHERE a = 1 AND b = 2 能用
• WHERE b = 2 用不了

6. 索引失效场景

• 索引字段做函数运算
• 隐式类型转换
• like 以 % 开头
• 联合索引不满足最左前缀

总结

索引是空间换时间。不是越多越好，太多索引会影响写入性能，占用额外存储空间。

MySQL 8.0 有哪些重要更新

MySQL 8.0 是目前最主流的大版本，相比 5.7 提升很大。

1. 性能提升

8.0 的性能比 5.7 提升约 2 倍，尤其是读取和写入并发场景下提升明显。

2. 原子 DDL

8.0 支持原子 DDL，建表、删表操作要么全成功要么全失败，不会出现一半成功一半失败的情况。

3. 窗口函数

支持窗口函数，比如 ROW_NUMBER()、RANK()，做排名统计很方便：

SELECT name, score,
  RANK() OVER (ORDER BY score DESC) as rank
FROM students;

4. CTE 公共表表达式

支持 WITH 语法，复杂 SQL 写起来更清晰：

WITH top_users AS (
  SELECT id, name FROM users WHERE vip = 1
)
SELECT * FROM top_users;

5. JSON 增强

JSON 操作函数更丰富，性能也更好，基本可以替代 MongoDB 的简单场景。

6. 隐藏索引

可以把索引设为隐藏状态，不删除但暂时不用，用来测试索引是否真的有用。

7. 字符集变化

默认字符集从 latin1 改成了 utf8mb4，支持 Emoji 表情。

总结

MySQL 8.0 是目前新项目的首选，性能好、特性全。老项目从 5.7 升级要注意兼容性，尤其是 SQL 模式和函数变化。

前言

数据库备份是生产环境的生命线。很多人没出事的时候不备份，出事了才后悔。本文讲清楚 MySQL 最常用的备份工具 mysqldump。

一、为什么要备份

• 误操作：手滑删了表、删了数据
• 服务器挂了：硬盘坏了、机器报废
• 被攻击： ransomware 加密了数据库
• 升级失败：新版本不兼容，数据回退

记住：没有备份的数据库，就是在裸奔。

二、mysqldump 是什么

mysqldump 是 MySQL 自带的逻辑备份工具，把数据导出成 SQL 文件。

优点：简单、通用、跨版本
缺点：大数据量慢

适合：中小数据库（< 50G）

三、备份数据库

1. 备份整个数据库

mysqldump -u root -p 数据库名 > backup.sql

输入密码后，就会生成 backup.sql 文件。

2. 备份所有数据库

mysqldump -u root -p --all-databases > all_backup.sql

3. 备份单个表

mysqldump -u root -p 数据库名 表名 > table_backup.sql

4. 备份多个表

mysqldump -u root -p 数据库名 表1 表2 > multi_backup.sql

四、恢复数据库

1. 恢复整个数据库

mysql -u root -p 数据库名 < backup.sql

注意：数据库要先存在。

CREATE DATABASE 数据库名;

2. 恢复所有数据库

mysql -u root -p < all_backup.sql

五、常用参数

--single-transaction

InnoDB 引擎下，保证备份一致性，不锁表：

mysqldump -u root -p --single-transaction 数据库名 > backup.sql

生产环境一定要加这个！

--routines

备份存储过程和函数：

mysqldump -u root -p --routines 数据库名 > backup.sql

--triggers

备份触发器：

mysqldump -u root -p --triggers 数据库名 > backup.sql

--events

备份事件调度器：

mysqldump -u root -p --events 数据库名 > backup.sql

--no-data

只备份表结构，不备份数据：

mysqldump -u root -p --no-data 数据库名 > schema.sql

--where

按条件备份：

mysqldump -u root -p 数据库名 表名 --where="id > 100" > partial.sql

六、完整备份命令

生产环境推荐用这个：

mysqldump -u root -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --default-character-set=utf8mb4 \
  数据库名 > backup.sql

七、压缩备份

备份文件太大？压缩一下：

# 备份并压缩
mysqldump -u root -p 数据库名 | gzip > backup.sql.gz

# 解压并恢复
gunzip < backup.sql.gz | mysql -u root -p 数据库名

八、定时备份

写个脚本每天自动备份：

#!/bin/bash

# 备份目录
BACKUP_DIR="/data/backup/mysql"
DATE=$(date +%Y%m%d)

# 备份
mysqldump -u root -p密码 --single-transaction 数据库名 | gzip > $BACKUP_DIR/db_$DATE.sql.gz

# 删除 7 天前的备份
find $BACKUP_DIR -name "db_*.sql.gz" -mtime +7 -delete

加到 crontab：

# 每天凌晨 2 点备份
0 2 * * * /bin/bash /path/to/backup.sh

九、常见坑

1. 备份不锁表：大表备份会锁表，一定要加 --single-transaction
2. 字符集不对：备份文件乱码，加 --default-character-set=utf8mb4
3. 没备份存储过程：导出后丢了，加 --routines
4. 备份没验证：备份完一定要试恢复，不然真出事的时候哭都来不及

十、备份策略

• 每天全量备份：小数据库
• 每周全量 + 每天增量：大数据库
• 异地备份：备份文件要存到别的服务器
• 定期演练恢复：每个月测一次能不能恢复

总结

MySQL 备份的核心要点：

1. mysqldump 是最常用的备份工具
2. 生产环境加 --single-transaction，不锁表
3. 备份要压缩，节省空间
4. 定时备份，自动删除旧备份
5. 备份一定要验证，能恢复才算备份成功

数据是公司最值钱的资产，备份这事儿千万别偷懒。

前言

事务和锁是 MySQL 最核心也最容易出问题的部分。很多人写了很多年 SQL，却还是搞不清隔离级别和死锁。本文用最直白的方式讲清楚。

一、事务的 ACID

事务就是一组操作，要么全部成功，要么全部失败。四个特性：

• A 原子性：一组操作是一个整体，不能拆分
• C 一致性：事务前后，数据库从一个一致状态到另一个一致状态
• I 隔离性：多个事务之间互不干扰
• D 持久性：事务提交后，数据就永久保存了

二、并发问题

多个事务同时操作数据，会出什么问题？

1. 脏读

事务 A 读到了事务 B 还没提交的数据。

事务 A：修改了余额为 1000，但还没提交
事务 B：读到了余额 1000
事务 A：回滚了，余额变回 500
事务 B：拿着 1000 的错误数据继续操作

2. 不可重复读

事务 A 两次读同一行数据，结果不一样。

事务 A：第一次读余额是 500
事务 B：修改了余额为 1000，提交了
事务 A：第二次读余额是 1000

3. 幻读

事务 A 两次查询，结果集的行数不一样。

事务 A：第一次查询 age > 20 的用户，有 10 条
事务 B：插入了一条 age = 25 的用户，提交了
事务 A：第二次查询 age > 20 的用户，有 11 条

三、四种隔离级别

MySQL 用隔离级别来解决这些并发问题：

MySQL 默认是可重复读（REPEATABLE READ），InnoDB 在这个级别下用间隙锁解决了幻读。

查看当前隔离级别

SELECT @@tx_isolation;

修改隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

四、锁的类型

1. 共享锁（S 锁）

读锁，多个事务可以同时持有。

SELECT ... LOCK IN SHARE MODE;

2. 排他锁（X 锁）

写锁，只有一个事务能持有。

SELECT ... FOR UPDATE;

3. 表锁 vs 行锁

• 表锁：锁住整张表，开销小，但并发低
• 行锁：锁住一行数据，开销大，但并发高

五、死锁

什么是死锁

两个事务互相等待对方释放锁。

事务 A：锁住了 id = 1，等待 id = 2
事务 B：锁住了 id = 2，等待 id = 1

两个事务都卡住了，谁也不让谁。

怎么避免死锁

1. 按相同顺序访问表和行：所有事务都按 id 从小到大操作
2. 事务尽量短：事务越长，锁持有时间越长
3. 降低隔离级别：隔离级别越低，锁越少
4. 加索引：没有索引会走表锁，更容易死锁

查看死锁

-- 查看最近一次死锁
SHOW ENGINE INNODB STATUS;

六、实战：转账

-- 开启事务
BEGIN;

-- 扣款（加行锁）
UPDATE account SET balance = balance - 100 WHERE id = 1;

-- 检查余额
SELECT balance FROM account WHERE id = 1;

-- 如果余额不够，回滚
-- ROLLBACK;

-- 加钱
UPDATE account SET balance = balance + 100 WHERE id = 2;

-- 提交
COMMIT;

七、常见坑

1. 事务太长：锁持有时间太长，容易死锁
2. 没有索引：行锁变表锁，并发暴跌
3. 隔离级别太高：用串行化，性能很差
4. 忘记提交：事务一直开着，锁一直占着

总结

MySQL 事务与锁的核心思路：

1. 事务保证 ACID，一组操作要么全成功要么全失败
2. 隔离级别越高越安全，但性能越差
3. MySQL 默认可重复读，InnoDB 解决了幻读
4. 行锁比表锁并发高，但一定要有索引
5. 按相同顺序操作，能避免大部分死锁

搞懂这些，你对 MySQL 的理解就超过 80% 的开发者了。

前言

数据库查询慢是 Web 开发中最常见的性能问题之一。而绝大多数慢查询都可以通过合理使用索引来解决。本文将从 Explain 执行计划入手，带你一步步掌握 MySQL 索引优化的实战技巧。

一、认识 Explain 执行计划

要优化查询，首先得知道 MySQL 是怎么执行你的 SQL 的。EXPLAIN 命令可以展示 MySQL 执行查询的详细步骤：

EXPLAIN SELECT * FROM orders WHERE user_id = 10086 AND status = 'paid';

重点关注以下几个字段：

• type：访问类型，从好到差依次为 const > eq_ref > ref > range > index > ALL。出现 ALL 就是全表扫描，必须优化。
• key：实际使用的索引。如果为 NULL，说明没有用到索引。
• rows：预估需要扫描的行数。这个值越大越慢。
• Extra：额外信息。Using filesort 和 Using temporary 都是危险信号。

二、最左前缀原则

联合索引 (a, b, c) 的匹配规则：

-- 用到索引 a,b,c
SELECT * FROM t WHERE a = 1 AND b = 2 AND c = 3;

-- 用到索引 a,b
SELECT * FROM t WHERE a = 1 AND b = 2;

-- 只用到索引 a（中间断了）
SELECT * FROM t WHERE a = 1 AND c = 3;

-- 完全用不到索引
SELECT * FROM t WHERE b = 2 AND c = 3;

实战要点：把等值查询的列放在前面，范围查询的列放在后面。

三、常见索引失效场景

1. 对索引列使用函数或运算

-- 失效：对索引列用了函数
SELECT * FROM users WHERE DATE(created_at) = '2026-01-01';

-- 有效：改成范围查询
SELECT * FROM users WHERE created_at >= '2026-01-01' AND created_at < '2026-01-02';

2. 隐式类型转换

-- 失效：phone 是 varchar，用数字查
SELECT * FROM users WHERE phone = 13800138000;

-- 有效：加引号
SELECT * FROM users WHERE phone = '13800138000';

3. LIKE 以通配符开头

-- 失效
SELECT * FROM users WHERE name LIKE '%张%';

-- 有效
SELECT * FROM users WHERE name LIKE '张%';

四、实战优化案例

问题 SQL（扫描 10 万行，耗时 1.2 秒）：

SELECT id, title, created_at FROM articles
WHERE category_id = 5 AND status = 'published'
ORDER BY created_at DESC LIMIT 20;

Explain 结果：type=index，rows=120000，Using filesort。

优化方案：创建联合索引

ALTER TABLE articles ADD INDEX idx_cat_status_time (category_id, status, created_at DESC);

优化后：type=ref，rows=20，Extra 无 filesort。查询时间从 1.2 秒降到 0.005 秒。

五、索引不是越多越好

索引虽然能加速查询，但也有代价：

1. 写入变慢：每次 INSERT/UPDATE 都要维护索引
2. 占用空间：索引也是要存磁盘的
3. 优化器选错：索引太多可能让优化器犯迷糊

建议：单表索引数量控制在 5 个以内，避免重复索引和冗余索引。

总结

MySQL 索引优化的核心思路：

1. 用 EXPLAIN 看执行计划，找到慢的原因
2. 遵循最左前缀原则设计联合索引
3. 避免常见的索引失效写法
4. 定期清理无用索引

掌握这些技巧，你就能解决 80% 以上的慢查询问题。