MySQL 8.0 SQL调优技巧手册

MySQL 8.0 SQL调优技巧手册

文档信息

项目 内容
文档标题 MySQL 8.0 SQL调优技巧手册
创建日期 2026-01-12
MySQL版本 8.0+
文档状态 正式版

一、索引优化

1.1 索引设计原则

1.1.1 选择合适的列建立索引

适合建立索引的列：

  • WHERE 子句中频繁出现的列
  • JOIN 关联的列
  • ORDER BY、GROUP BY 子句中的列
  • 区分度高的列（基数大的列）

不适合建立索引的列：

  • 频繁更新的列
  • 区分度低的列（如性别、状态等只有几个值）
  • 数据量很小的表（全表扫描可能更快）
  • TEXT、BLOB 等大字段
-- 好的索引设计
CREATE INDEX idx_user_status_createtime ON user(status, create_time);

-- 不好的索引设计（区分度太低）
CREATE INDEX idx_gender ON user(gender);  -- 只有男/女两个值

1.1.2 最左前缀原则

联合索引遵循最左前缀匹配原则，查询条件必须包含索引最左边的列才能使用索引。

-- 索引：idx_abc (a, b, c)

-- 能使用索引的查询
SELECT * FROM t WHERE a = 1;                    -- 使用索引 (a)
SELECT * FROM t WHERE a = 1 AND b = 2;          -- 使用索引 (a,b)
SELECT * FROM t WHERE a = 1 AND b = 2 AND c = 3; -- 使用索引 (a,b,c)
SELECT * FROM t WHERE a = 1 AND c = 3;          -- 使用索引 (a)

-- 不能使用索引的查询
SELECT * FROM t WHERE b = 2;                    -- 不能使用索引
SELECT * FROM t WHERE c = 3;                    -- 不能使用索引
SELECT * FROM t WHERE b = 2 AND c = 3;          -- 不能使用索引

1.1.3 覆盖索引

尽量使用覆盖索引（查询列都在索引中），避免回表查询。

-- 索引：idx_user_info (user_id, name, age)

-- 好：使用覆盖索引，不需要回表
SELECT user_id, name, age FROM user WHERE user_id = 1;

-- 差：需要回表查询address字段
SELECT user_id, name, age, address FROM user WHERE user_id = 1;

-- 优化方案：如果address也经常被查询，考虑加入索引
CREATE INDEX idx_user_detail (user_id, name, age, address);

1.1.4 索引列不要参与计算

在索引列上使用函数或计算会导致索引失效。

-- 错误示例：索引失效
SELECT * FROM user WHERE YEAR(create_time) = 2026;
SELECT * FROM user WHERE age + 1 = 25;
SELECT * FROM user WHERE CONCAT(first_name, last_name) = 'John Doe';

-- 正确示例：索引生效
SELECT * FROM user WHERE create_time >= '2026-01-01' AND create_time < '2027-01-01';
SELECT * FROM user WHERE age = 24;
SELECT * FROM user WHERE first_name = 'John' AND last_name = 'Doe';

1.1.5 前缀索引

对于字符串类型的长字段，可以使用前缀索引减少索引空间。

-- 分析字段的区分度
SELECT 
    COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS prefix_5,
    COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS prefix_10,
    COUNT(DISTINCT LEFT(email, 15)) / COUNT(*) AS prefix_15,
    COUNT(DISTINCT email) / COUNT(*) AS full_col
FROM user;

-- 创建前缀索引（假设10个字符的区分度足够）
CREATE INDEX idx_email_prefix ON user(email(10));

1.2 避免索引失效

1.2.1 避免使用 NOT、!=、<>

-- 索引失效
SELECT * FROM user WHERE status != 1;
SELECT * FROM user WHERE status <> 1;
SELECT * FROM user WHERE NOT status = 1;

-- 优化方案：使用 IN 或具体值
SELECT * FROM user WHERE status IN (0, 2, 3);

1.2.2 避免使用 OR（部分情况）

-- 如果OR条件中有列没有索引，整个查询索引失效
SELECT * FROM user WHERE id = 1 OR age = 25;  -- age没索引，索引失效

-- 优化方案1：都建索引
CREATE INDEX idx_age ON user(age);

-- 优化方案2：改写为UNION（如果合适）
SELECT * FROM user WHERE id = 1
UNION ALL
SELECT * FROM user WHERE age = 25 AND id != 1;

1.2.3 避免模糊查询前导通配符

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

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

-- 优化方案：使用全文索引
ALTER TABLE user ADD FULLTEXT INDEX idx_name_fulltext(name);
SELECT * FROM user WHERE MATCH(name) AGAINST('张三' IN NATURAL LANGUAGE MODE);

1.2.4 避免类型转换

-- 错误：字符串列与数字比较，发生隐式转换，索引失效
SELECT * FROM user WHERE phone = 13800138000;  -- phone是VARCHAR类型

-- 正确：保持类型一致
SELECT * FROM user WHERE phone = '13800138000';

1.3 索引监控与维护

1.3.1 查找未使用的索引

-- 查询未使用的索引
SELECT 
    object_schema AS db_name,
    object_name AS table_name,
    index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
    AND count_star = 0
    AND object_schema NOT IN ('mysql', 'performance_schema', 'sys')
ORDER BY object_schema, object_name;

1.3.2 查找重复索引

-- 查找冗余索引
SELECT 
    a.table_schema,
    a.table_name,
    a.index_name AS index_1,
    b.index_name AS index_2,
    a.column_name
FROM information_schema.statistics a
JOIN information_schema.statistics b 
    ON a.table_schema = b.table_schema
    AND a.table_name = b.table_name
    AND a.seq_in_index = b.seq_in_index
    AND a.column_name = b.column_name
WHERE a.index_name != b.index_name
    AND a.index_name < b.index_name
    AND a.table_schema NOT IN ('mysql', 'performance_schema', 'sys')
ORDER BY a.table_schema, a.table_name;

1.3.3 索引碎片整理

-- 分析表，更新索引统计信息
ANALYZE TABLE user;

-- 优化表，重建索引（会锁表，生产环境谨慎使用）
OPTIMIZE TABLE user;

-- 在线重建索引（MySQL 8.0支持）
ALTER TABLE user DROP INDEX idx_name, ADD INDEX idx_name(name), ALGORITHM=INPLACE, LOCK=NONE;

二、查询优化

2.1 SELECT 优化

2.1.1 避免 SELECT *

-- 不好：查询所有列，浪费资源
SELECT * FROM user WHERE id = 1;

-- 好：只查询需要的列
SELECT id, name, age FROM user WHERE id = 1;

原因：

  • 增加网络传输开销
  • 无法使用覆盖索引
  • 增加数据库IO
  • 应用程序解析更多数据

2.1.2 合理使用 DISTINCT

-- 不好：不必要的去重
SELECT DISTINCT id FROM user WHERE status = 1;  -- id本身就唯一

-- 好：确实需要去重时才使用
SELECT DISTINCT user_id FROM orders WHERE create_time > '2026-01-01';

2.1.3 避免在循环中执行查询（N+1问题）

-- 不好：N+1查询问题
-- 先查询用户ID列表
SELECT id FROM orders;
-- 然后在循环中查询每个用户信息
SELECT * FROM user WHERE id = ?;  -- 执行N次

-- 好：使用批量查询
SELECT id FROM orders;
SELECT * FROM user WHERE id IN (1, 2, 3, 4, 5);  -- 一次查询

-- 更好：使用JOIN
SELECT o.*, u.name, u.age 
FROM orders o
LEFT JOIN user u ON o.user_id = u.id;

2.2 JOIN 优化

2.2.1 小表驱动大表

-- user表100条，order表10000条

-- 不好：大表驱动小表
SELECT o.* FROM order o 
LEFT JOIN user u ON o.user_id = u.id
WHERE u.status = 1;

-- 好：小表驱动大表
SELECT o.* FROM user u
LEFT JOIN order o ON o.user_id = u.id
WHERE u.status = 1;

2.2.2 JOIN 列必须有索引

-- 确保关联列有索引
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_id ON user(id);  -- 主键自带索引

-- 查询时才能高效
SELECT o.*, u.name 
FROM orders o
LEFT JOIN user u ON o.user_id = u.id;

2.2.3 避免过多 JOIN

-- 不好：JOIN过多，性能差
SELECT * FROM t1
JOIN t2 ON t1.id = t2.t1_id
JOIN t3 ON t2.id = t3.t2_id
JOIN t4 ON t3.id = t4.t3_id
JOIN t5 ON t4.id = t5.t4_id
JOIN t6 ON t5.id = t6.t5_id;  -- 6表JOIN

-- 优化方案：
-- 1. 拆分成多个查询
-- 2. 数据冗余，减少JOIN
-- 3. 使用缓存

2.2.4 选择合适的 JOIN 类型

-- INNER JOIN：只返回匹配的记录（推荐优先使用）
SELECT o.*, u.name FROM orders o
INNER JOIN user u ON o.user_id = u.id;

-- LEFT JOIN：返回左表所有记录
SELECT u.*, o.amount FROM user u
LEFT JOIN orders o ON u.id = o.user_id;

-- 注意：能用INNER JOIN就不用LEFT JOIN，性能更好

2.3 子查询优化

2.3.1 优先使用 JOIN 替代子查询

-- 不好：子查询
SELECT * FROM user 
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

-- 好：使用JOIN
SELECT DISTINCT u.* FROM user u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.amount > 1000;

-- 或使用EXISTS（适合大数据量）
SELECT * FROM user u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id AND o.amount > 1000
);

2.3.2 避免在 WHERE 中使用子查询

-- 不好：每行都执行子查询
SELECT * FROM user 
WHERE age > (SELECT AVG(age) FROM user);

-- 好：先计算结果
SELECT @avg_age := AVG(age) FROM user;
SELECT * FROM user WHERE age > @avg_age;

2.4 分页优化

2.4.1 深分页优化

-- 不好：深分页，扫描大量数据
SELECT * FROM user ORDER BY id LIMIT 1000000, 20;

-- 优化方案1：使用上次查询的最大ID
SELECT * FROM user 
WHERE id > 1000000 
ORDER BY id LIMIT 20;

-- 优化方案2：延迟关联
SELECT u.* FROM user u
INNER JOIN (
    SELECT id FROM user ORDER BY id LIMIT 1000000, 20
) t ON u.id = t.id;

-- 优化方案3：使用游标（适合导出场景）

2.4.2 使用覆盖索引优化分页

-- 索引：idx_status_createtime (status, create_time, id)

-- 不好：需要回表
SELECT * FROM user 
WHERE status = 1 
ORDER BY create_time DESC 
LIMIT 10000, 20;

-- 好：先用覆盖索引获取ID，再回表
SELECT u.* FROM user u
INNER JOIN (
    SELECT id FROM user 
    WHERE status = 1 
    ORDER BY create_time DESC 
    LIMIT 10000, 20
) t ON u.id = t.id;

三、WHERE 条件优化

3.1 范围查询优化

-- 不好：OR条件可能不走索引
SELECT * FROM user WHERE age = 20 OR age = 21 OR age = 22;

-- 好：使用BETWEEN
SELECT * FROM user WHERE age BETWEEN 20 AND 22;

-- 好：使用IN（值较少时）
SELECT * FROM user WHERE age IN (20, 21, 22);

3.2 IN 和 EXISTS 的选择

-- 当子查询结果集小时，使用IN
SELECT * FROM user 
WHERE id IN (SELECT user_id FROM vip_user);  -- vip_user表很小

-- 当外层查询结果集小时，使用EXISTS
SELECT * FROM vip_user v
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = v.user_id
);  -- vip_user表很小，orders表很大

3.3 避免负向查询

-- 不好：负向查询不走索引
SELECT * FROM user WHERE status NOT IN (1, 2);
SELECT * FROM user WHERE name NOT LIKE '张%';

-- 好：改为正向查询
SELECT * FROM user WHERE status IN (0, 3, 4, 5);

3.4 日期查询优化

-- 不好：函数导致索引失效
SELECT * FROM orders 
WHERE DATE(create_time) = '2026-01-12';

-- 好：使用范围查询
SELECT * FROM orders 
WHERE create_time >= '2026-01-12 00:00:00' 
  AND create_time < '2026-01-13 00:00:00';

-- 不好：YEAR函数
SELECT * FROM orders WHERE YEAR(create_time) = 2026;

-- 好：范围查询
SELECT * FROM orders 
WHERE create_time >= '2026-01-01' 
  AND create_time < '2027-01-01';

四、INSERT 优化

4.1 批量插入

-- 不好：逐条插入
INSERT INTO user (name, age) VALUES ('张三', 20);
INSERT INTO user (name, age) VALUES ('李四', 21);
INSERT INTO user (name, age) VALUES ('王五', 22);

-- 好：批量插入（建议每批500-1000条）
INSERT INTO user (name, age) VALUES 
('张三', 20),
('李四', 21),
('王五', 22);

4.2 使用 LOAD DATA

-- 大批量数据导入使用LOAD DATA（速度最快）
LOAD DATA INFILE '/path/to/data.csv'
INTO TABLE user
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(name, age, email);

4.3 插入时的优化参数

-- 临时关闭唯一性检查（确保数据无重复）
SET unique_checks = 0;
-- 批量插入
INSERT INTO user ...;
-- 恢复检查
SET unique_checks = 1;

-- 临时关闭外键检查
SET foreign_key_checks = 0;
-- 批量插入
INSERT INTO orders ...;
-- 恢复检查
SET foreign_key_checks = 1;

-- 临时关闭自动提交
SET autocommit = 0;
-- 批量插入
INSERT INTO user ...;
-- 手动提交
COMMIT;
-- 恢复自动提交
SET autocommit = 1;

五、UPDATE 和 DELETE 优化

5.1 避免全表更新

-- 危险：全表更新
UPDATE user SET status = 1;

-- 安全：带条件更新
UPDATE user SET status = 1 WHERE id IN (1, 2, 3);

-- 分批更新大表
UPDATE user SET status = 1 WHERE id BETWEEN 1 AND 10000;
UPDATE user SET status = 1 WHERE id BETWEEN 10001 AND 20000;

5.2 WHERE 条件必须有索引

-- 不好：全表扫描，锁表时间长
UPDATE user SET status = 1 WHERE age = 20;  -- age没索引

-- 好：通过索引定位，快速更新
UPDATE user SET status = 1 WHERE id = 100;  -- id有索引

5.3 避免在更新语句中使用子查询

-- 不好：子查询可能很慢
UPDATE user SET level = (
    SELECT COUNT(*) FROM orders WHERE user_id = user.id
);

-- 好：先查询再更新，或使用JOIN
UPDATE user u
INNER JOIN (
    SELECT user_id, COUNT(*) as cnt 
    FROM orders 
    GROUP BY user_id
) o ON u.id = o.user_id
SET u.level = o.cnt;

5.4 DELETE 优化

-- 大批量删除，分批进行
DELETE FROM user WHERE status = 0 AND id < 10000;
DELETE FROM user WHERE status = 0 AND id BETWEEN 10000 AND 20000;

-- 清空表用TRUNCATE（无法回滚）
TRUNCATE TABLE tmp_table;  -- 比DELETE快得多

-- 需要保留表结构但清空数据
-- 方案1：DROP + CREATE（最快）
DROP TABLE user_bak;
CREATE TABLE user_bak LIKE user;

-- 方案2：TRUNCATE（次快）
TRUNCATE TABLE user_bak;

六、GROUP BY 和 ORDER BY 优化

6.1 GROUP BY 优化

-- 不好：没有索引，需要临时表和文件排序
SELECT user_id, COUNT(*) FROM orders 
GROUP BY user_id;

-- 好：在GROUP BY列上建索引
CREATE INDEX idx_user_id ON orders(user_id);
SELECT user_id, COUNT(*) FROM orders 
GROUP BY user_id;

-- 避免不必要的排序
SELECT user_id, COUNT(*) FROM orders 
GROUP BY user_id 
ORDER BY NULL;  -- 不排序

6.2 ORDER BY 优化

-- 不好：排序列没有索引
SELECT * FROM user ORDER BY age;

-- 好：排序列有索引
CREATE INDEX idx_age ON user(age);
SELECT * FROM user ORDER BY age;

-- 利用覆盖索引避免回表
CREATE INDEX idx_age_name ON user(age, name);
SELECT age, name FROM user ORDER BY age;  -- 不需要回表

6.3 避免文件排序

-- 查看是否使用了文件排序
EXPLAIN SELECT * FROM user ORDER BY age;
-- 如果Extra显示"Using filesort"，说明使用了文件排序

-- 优化方案：
-- 1. 在排序列上建索引
-- 2. 使用覆盖索引
-- 3. 增大sort_buffer_size参数

6.4 多字段排序

-- 建立联合索引支持多字段排序
CREATE INDEX idx_status_createtime ON user(status, create_time);

-- 能使用索引的排序
SELECT * FROM user WHERE status = 1 ORDER BY create_time;  -- 可以用索引
SELECT * FROM user ORDER BY status, create_time;  -- 可以用索引

-- 不能使用索引的排序
SELECT * FROM user ORDER BY status ASC, create_time DESC;  -- 排序方向不同
SELECT * FROM user ORDER BY create_time, status;  -- 顺序与索引不符

七、使用 EXPLAIN 分析查询

7.1 EXPLAIN 关键字段

EXPLAIN SELECT * FROM user WHERE id = 1;

重要字段说明：

字段 说明 优化目标
type 访问类型 至少达到range，最好ref或const
key 实际使用的索引 不为NULL
rows 扫描的行数 越少越好
Extra 额外信息 避免Using filesort、Using temporary

7.2 type 类型（从好到坏）

-- system/const：主键或唯一索引查询（最优）
EXPLAIN SELECT * FROM user WHERE id = 1;

-- eq_ref：唯一索引JOIN
EXPLAIN SELECT * FROM orders o 
INNER JOIN user u ON o.user_id = u.id;

-- ref：非唯一索引查询
EXPLAIN SELECT * FROM user WHERE status = 1;

-- range：范围查询
EXPLAIN SELECT * FROM user WHERE id > 100 AND id < 200;

-- index：索引全扫描
EXPLAIN SELECT id FROM user;

-- ALL：全表扫描（最差，需优化）
EXPLAIN SELECT * FROM user WHERE age = 20;  -- age没索引

7.3 Extra 字段分析

-- Using where：使用WHERE过滤
-- Using index：使用了覆盖索引（好）
-- Using index condition：索引条件下推（好）
-- Using filesort：需要额外排序（差，需优化）
-- Using temporary：需要临时表（差，需优化）
-- Using join buffer：JOIN缓冲（差，需优化）

7.4 MySQL 8.0 新特性：EXPLAIN ANALYZE

-- 实际执行查询并显示详细的执行信息
EXPLAIN ANALYZE 
SELECT * FROM user WHERE status = 1 ORDER BY create_time LIMIT 10;

-- 输出包括：
-- 1. 实际执行时间
-- 2. 返回的行数
-- 3. 循环次数
-- 4. 执行计划的每个步骤的详细信息

八、表结构优化

8.1 选择合适的数据类型

-- 整数类型：根据范围选择
TINYINT     -- 1字节，-128~127
SMALLINT    -- 2字节，-32768~32767
MEDIUMINT   -- 3字节
INT         -- 4字节，-21亿~21亿
BIGINT      -- 8字节

-- 字符串类型：根据长度选择
CHAR(10)    -- 定长，适合长度固定的字段（如手机号、身份证号）
VARCHAR(50) -- 变长，适合长度不固定的字段

-- 时间类型
DATETIME    -- 8字节，'1000-01-01 00:00:00' ~ '9999-12-31 23:59:59'
TIMESTAMP   -- 4字节，'1970-01-01 00:00:01' ~ '2038-01-19 03:14:07'（推荐）

-- 金额类型
DECIMAL(10,2)  -- 精确数值，适合存储金额

8.2 避免使用NULL

-- 不好：NULL值的问题
-- 1. 索引统计复杂
-- 2. NULL值比较需要IS NULL/IS NOT NULL
-- 3. 聚合函数会忽略NULL值

CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(50) NULL  -- 不推荐
);

-- 好：使用NOT NULL + 默认值
CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL DEFAULT '',
    age INT NOT NULL DEFAULT 0,
    status TINYINT NOT NULL DEFAULT 1
);

8.3 合理使用冗余字段

-- 场景：订单表需要经常显示用户名

-- 方案1：每次JOIN查询（性能差）
SELECT o.*, u.name FROM orders o 
LEFT JOIN user u ON o.user_id = u.id;

-- 方案2：冗余用户名到订单表（推荐）
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    user_name VARCHAR(50) NOT NULL,  -- 冗余字段
    amount DECIMAL(10,2),
    create_time DATETIME
);

8.4 表拆分

8.4.1 垂直拆分

-- 将不常用的列拆分到另一张表

-- 拆分前
CREATE TABLE user (
    id BIGINT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    avatar TEXT,          -- 大字段，不常用
    description TEXT,     -- 大字段，不常用
    login_time DATETIME
);

-- 拆分后
CREATE TABLE user (
    id BIGINT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    login_time DATETIME
);

CREATE TABLE user_detail (
    user_id BIGINT PRIMARY KEY,
    avatar TEXT,
    description TEXT,
    FOREIGN KEY (user_id) REFERENCES user(id)
);

8.4.2 水平拆分（分表）

-- 按时间分表
CREATE TABLE orders_2025_01 ( ... );
CREATE TABLE orders_2025_02 ( ... );
CREATE TABLE orders_2025_03 ( ... );

-- 按ID分表（取模）
CREATE TABLE user_0 ( ... );  -- id % 10 = 0
CREATE TABLE user_1 ( ... );  -- id % 10 = 1
...
CREATE TABLE user_9 ( ... );  -- id % 10 = 9

九、事务与锁优化

9.1 事务优化

-- 不好：事务时间过长
START TRANSACTION;
-- 执行大量SQL
-- 复杂的业务逻辑
-- 外部API调用
COMMIT;

-- 好：缩短事务时间
-- 1. 外部调用放在事务外
-- 2. 只在必要时使用事务
-- 3. 减少事务内的SQL数量
START TRANSACTION;
-- 只包含必须的数据库操作
COMMIT;

9.2 避免死锁

-- 死锁场景：两个事务相互等待

-- 事务1
START TRANSACTION;
UPDATE user SET status = 1 WHERE id = 1;
UPDATE user SET status = 1 WHERE id = 2;
COMMIT;

-- 事务2（同时执行）
START TRANSACTION;
UPDATE user SET status = 1 WHERE id = 2;  -- 等待事务1
UPDATE user SET status = 1 WHERE id = 1;  -- 死锁！
COMMIT;

-- 避免方案：统一更新顺序
-- 两个事务都按id升序更新

9.3 选择合适的隔离级别

-- 查看当前隔离级别
SELECT @@transaction_isolation;

-- MySQL 8.0默认：REPEATABLE-READ

-- 根据业务需求选择：
-- READ-UNCOMMITTED：脏读，性能最好（不推荐）
-- READ-COMMITTED：避免脏读（Oracle默认）
-- REPEATABLE-READ：避免脏读和不可重复读（MySQL默认）
-- SERIALIZABLE：完全串行化（性能最差）

-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

9.4 锁优化

-- 使用乐观锁（版本号）
UPDATE user 
SET balance = balance - 100, version = version + 1
WHERE id = 1 AND version = 10;

-- 使用SELECT ... FOR UPDATE时尽量使用索引
-- 不好：锁住所有status=1的记录
SELECT * FROM user WHERE status = 1 FOR UPDATE;

-- 好：只锁住id=1的记录
SELECT * FROM user WHERE id = 1 FOR UPDATE;

十、配置优化

10.1 连接相关参数

# my.cnf 或 my.ini

# 最大连接数（根据业务调整）
max_connections = 500

# 连接超时时间
wait_timeout = 28800
interactive_timeout = 28800

# 最大允许的包大小（处理大字段时需调大）
max_allowed_packet = 64M

10.2 缓冲区参数

# InnoDB缓冲池大小（建议设置为物理内存的50%-70%）
innodb_buffer_pool_size = 8G

# 缓冲池实例数（建议与CPU核心数相同，最多64个）
innodb_buffer_pool_instances = 8

# 排序缓冲区大小
sort_buffer_size = 2M

# JOIN缓冲区大小
join_buffer_size = 2M

# 读缓冲区大小
read_buffer_size = 1M
read_rnd_buffer_size = 2M

10.3 日志参数

# 慢查询日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1  # 超过1秒的查询记录

# 记录未使用索引的查询
log_queries_not_using_indexes = 1

# Redo日志大小（增大可提高写性能，但恢复时间变长）
innodb_log_file_size = 1G
innodb_log_files_in_group = 2

# 刷盘策略（1最安全但性能差，2性能最好但可能丢数据）
innodb_flush_log_at_trx_commit = 1  # 生产环境推荐

10.4 查询缓存（MySQL 8.0已移除）

-- MySQL 8.0已完全移除查询缓存功能
-- 原因：维护成本高，命中率低，建议使用应用层缓存（Redis）

十一、监控与诊断

11.1 慢查询分析

# 使用mysqldumpslow分析慢查询日志
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 参数说明：
# -s：排序方式（t=时间，c=次数，l=锁时间）
# -t：显示前N条

11.2 实时查看正在执行的SQL

-- 查看当前所有连接
SHOW PROCESSLIST;

-- 查看完整SQL
SHOW FULL PROCESSLIST;

-- 查询执行时间超过1秒的SQL
SELECT * FROM information_schema.PROCESSLIST 
WHERE TIME > 1 AND COMMAND != 'Sleep';

-- 杀掉慢查询
KILL 12345;  -- 12345是进程ID

11.3 使用 Performance Schema

-- 开启Performance Schema（MySQL 8.0默认开启）
-- 在my.cnf中配置
performance_schema = ON

-- 查询最耗时的SQL
SELECT 
    DIGEST_TEXT AS query,
    COUNT_STAR AS exec_count,
    AVG_TIMER_WAIT/1000000000 AS avg_time_ms,
    SUM_TIMER_WAIT/1000000000 AS total_time_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- 查询最耗时的表
SELECT 
    OBJECT_SCHEMA AS db_name,
    OBJECT_NAME AS table_name,
    COUNT_STAR AS total_access,
    SUM_TIMER_WAIT/1000000000 AS total_time_ms
FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA NOT IN ('mysql', 'performance_schema', 'sys')
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

11.4 使用 sys schema（MySQL 8.0内置）

-- 查询未使用的索引
SELECT * FROM sys.schema_unused_indexes;

-- 查询冗余索引
SELECT * FROM sys.schema_redundant_indexes;

-- 查询全表扫描的语句
SELECT * FROM sys.statements_with_full_table_scans
LIMIT 10;

-- 查询执行时间最长的SQL
SELECT * FROM sys.statement_analysis
ORDER BY total_latency DESC
LIMIT 10;

-- 查询占用临时表的SQL
SELECT * FROM sys.statements_with_temp_tables
LIMIT 10;

十二、MySQL 8.0 新特性

12.1 窗口函数

-- 排名函数
SELECT 
    name,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
    RANK() OVER (ORDER BY score DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM student;

-- 分组排名
SELECT 
    class,
    name,
    score,
    RANK() OVER (PARTITION BY class ORDER BY score DESC) AS rank_in_class
FROM student;

-- 聚合窗口函数
SELECT 
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

12.2 公用表表达式（CTE）

-- 递归查询（查询部门树）
WITH RECURSIVE dept_tree AS (
    -- 锚点成员：顶级部门
    SELECT id, name, parent_id, 1 AS level
    FROM department
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- 递归成员：子部门
    SELECT d.id, d.name, d.parent_id, dt.level + 1
    FROM department d
    INNER JOIN dept_tree dt ON d.parent_id = dt.id
)
SELECT * FROM dept_tree;

-- 非递归CTE（提高可读性）
WITH top_users AS (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
    HAVING total > 10000
)
SELECT u.name, t.total
FROM top_users t
JOIN user u ON t.user_id = u.id;

12.3 隐藏索引

-- 测试索引是否有用前，可以先隐藏而不删除
ALTER TABLE user ALTER INDEX idx_age INVISIBLE;

-- 如果性能没问题，再删除
DROP INDEX idx_age ON user;

-- 恢复索引可见
ALTER TABLE user ALTER INDEX idx_age VISIBLE;

12.4 降序索引

-- MySQL 8.0支持真正的降序索引
CREATE INDEX idx_create_time_desc ON orders(create_time DESC);

-- 可以高效执行降序查询
SELECT * FROM orders ORDER BY create_time DESC LIMIT 10;

12.5 函数索引

-- 在函数结果上创建索引
CREATE INDEX idx_upper_name ON user((UPPER(name)));

-- 查询时可以使用索引
SELECT * FROM user WHERE UPPER(name) = 'ZHANG SAN';

十三、最佳实践清单

13.1 开发规范

13.2 表设计规范

13.3 索引规范

13.4 SQL审核工具推荐

  • Sonar：SQL代码审查工具
  • SQLAdvisor：美团开源的SQL优化工具
  • pt-query-digest：Percona提供的慢查询分析工具

十四、常见问题与解决方案

14.1 查询慢的排查流程

1. 开启慢查询日志，找到慢SQL
   ↓
2. 使用EXPLAIN分析执行计划
   ↓
3. 检查索引是否存在、是否被使用
   ↓
4. 检查WHERE条件、JOIN条件
   ↓
5. 检查是否有函数、类型转换等导致索引失效
   ↓
6. 优化SQL或添加索引
   ↓
7. 再次EXPLAIN验证优化效果

14.2 数据量大时的应对方案

表数据量超过500万：

  • 考虑分表（按时间、按ID取模）
  • 使用归档策略，定期归档历史数据
  • 冷热数据分离

单表数据量超过1000万：

  • 必须分表
  • 考虑使用分库分表中间件（ShardingSphere）

查询结果集大：

  • 使用分页
  • 使用流式查询
  • 异步导出

14.3 锁等待超时

-- 查看当前锁等待
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- 查看正在执行的事务
SELECT * FROM information_schema.INNODB_TRX;

-- 杀掉阻塞的事务
KILL trx_mysql_thread_id;

附录：性能测试命令

A.1 使用 mysqlslap 压测

# 并发100个连接，执行1000次查询
mysqlslap --user=root --password=password \
  --host=localhost \
  --concurrency=100 \
  --iterations=1000 \
  --query="SELECT * FROM user WHERE id = 1" \
  --create-schema=test_db

# 自动生成测试数据
mysqlslap --user=root --password=password \
  --host=localhost \
  --concurrency=100 \
  --iterations=10 \
  --auto-generate-sql \
  --auto-generate-sql-load-type=mixed \
  --auto-generate-sql-add-autoincrement \
  --number-of-queries=1000

A.2 使用 sysbench 压测

# 准备测试数据
sysbench /usr/share/sysbench/oltp_read_write.lua \
  --mysql-host=127.0.0.1 \
  --mysql-port=3306 \
  --mysql-user=root \
  --mysql-password=password \
  --mysql-db=test_db \
  --tables=10 \
  --table-size=100000 \
  prepare

# 执行压测
sysbench /usr/share/sysbench/oltp_read_write.lua \
  --mysql-host=127.0.0.1 \
  --mysql-port=3306 \
  --mysql-user=root \
  --mysql-password=password \
  --mysql-db=test_db \
  --tables=10 \
  --table-size=100000 \
  --threads=16 \
  --time=60 \
  --report-interval=10 \
  run
posted @ 2026-01-16 11:12  菜鸟~风  阅读(0)  评论(0)    收藏  举报