MySQL 性能优化

慢查询分析

-- 开启慢查询日志
SHOW VARIABLES LIKE 'slow_query_log';
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 超过1秒记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 查看慢查询
SHOW GLOBAL STATUS LIKE 'Slow_queries';

-- 使用 EXPLAIN 分析
EXPLAIN SELECT * FROM users WHERE name = 'Alice';

-- 分析 UPDATE/DELETE
EXPLAIN UPDATE users SET age = 26 WHERE id = 1;

EXPLAIN 详解

EXPLAIN SELECT * FROM users WHERE name = 'Alice' AND age > 20;

-- 关键字段说明：
-- type: ALL(全表) < index < range < ref < eq_ref < const
--       全部扫描    索引扫描  范围    索引查找 唯一扫描 常量
-- key: 实际使用的索引
-- rows: 预计扫描的行数
-- Extra: Using index(覆盖索引) / Using where(需要回表) / Using filesort(需要排序)

索引优化

-- 避免索引失效
-- ❌ SELECT * FROM users WHERE LEFT(name, 1) = 'A';
-- ✅ SELECT * FROM users WHERE name LIKE 'A%';

-- ❌ SELECT * FROM users WHERE age + 1 = 26;
-- ✅ SELECT * FROM users WHERE age = 25;

-- ❌ SELECT * FROM users WHERE name = 'A' OR age = 25;
-- ✅ SELECT * FROM users WHERE name = 'A'
--    UNION ALL
--    SELECT * FROM users WHERE age = 25;

-- 复合索引遵循最左前缀
-- INDEX idx_a_b_c(a, b, c)
-- ✅ WHERE a = 1
-- ✅ WHERE a = 1 AND b = 2
-- ✅ WHERE a = 1 AND b = 2 AND c = 3
-- ❌ WHERE b = 2
-- ❌ WHERE c = 3

SQL 优化原则

-- 1. 避免 SELECT *
SELECT id, name, age FROM users WHERE id = 1;  -- 只取需要的列

-- 2. 批量插入替代循环单条
-- ❌ for item in items: INSERT INTO ...
-- ✅ INSERT INTO users(name, age) VALUES ('A', 1), ('B', 2), ('C', 3);

-- 3. 预编译语句（防止 SQL 注入）
PREPARE stmt FROM 'SELECT * FROM users WHERE id = ?';
SET @id = 1;
EXECUTE stmt USING @id;
DEALLOCATE PREPARE stmt;

-- 4. 避免 JOIN 过多表
-- 超过 5 个表 JOIN，考虑拆分查询

-- 5. 分页优化
-- ❌ SELECT * FROM users LIMIT 1000000, 10;
-- ✅ SELECT * FROM users WHERE id > 1000000 LIMIT 10;
--    或使用延迟关联
SELECT a.* FROM users a INNER JOIN (
    SELECT id FROM users ORDER BY id LIMIT 1000000, 10
) b ON a.id = b.id;

表结构优化

-- 1. 字段类型优化
-- 整型比字符串快
-- TINYINT(1字节) < SMALLINT(2) < INT(4) < BIGINT(8)
-- 使用 VARCHAR(n) 而非 TEXT（TEXT 会存储在外部）
-- 定长字段放前面，变动字段放后面

-- 2. 主键选择
-- AUTO_INCREMENT 整数主键性能最好
-- 避免 UUID（无序，索引碎片化）

-- 3. 避免 NULL
-- NOT NULL 字段性能更好
ALTER TABLE users MODIFY name VARCHAR(50) NOT NULL;

-- 4. 数值字段
-- 用 DECIMAL 而非 VARCHAR 存储金额
ALTER TABLE orders MODIFY price DECIMAL(10, 2) NOT NULL;

-- 5. 适当冗余
-- 订单表冗余用户名称，避免 JOIN

分区与分表

-- 按时间分区
ALTER TABLE logs PARTITION BY RANGE (YEAR(create_time)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 查看分区
SELECT PARTITION_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'logs';

-- 分库分表（应用层处理）
-- ShardingSphere / MyCat 等中间件

连接池配置

# HikariCP（Spring Boot 默认）
spring:
  datasource:
    hikari:
      maximum-pool-size: 20       # 最大连接数
      minimum-idle: 5             # 最小空闲连接
      connection-timeout: 30000   # 获取连接超时（ms）
      idle-timeout: 600000       # 空闲超时
      max-lifetime: 1800000      # 连接最大生命周期

缓存策略

-- 查询缓存（MySQL 8.0 已移除）
-- 推荐使用 Redis

-- Redis 缓存用户信息
-- 应用层：先查 Redis，没有再查 MySQL
-- SET user:1 '{"name":"Alice","age":25}'
-- GET user:1

[[返回 MySQL 首页|../index]]