第5章：索引和性能优化

5.1 索引基础

5.1.1 什么是索引

• 索引的定义
• 索引的工作原理
• 索引的重要性

5.1.2 索引类型

• 主键索引
• 唯一索引
• 普通索引
• 全文索引
• 组合索引

5.1.3 创建和管理索引

• CREATE INDEX语法
• ALTER TABLE添加索引
• 删除索引
• 索引维护

5.2 性能分析

5.2.1 EXPLAIN分析

• EXPLAIN基本用法
• 执行计划解读
• 关键指标分析

5.2.2 慢查询日志

• 开启慢查询日志
• 配置慢查询参数
• 分析慢查询

5.3 查询优化技巧

5.3.1 索引优化

• 选择合适的索引
• 避免索引失效
• 复合索引最佳实践

5.3.2 SQL语句优化

• 避免全表扫描
• 减少子查询
• 使用JOIN代替子查询
• 批量操作

5.3.3 表结构优化

• 字段类型选择
• 垂直拆分
• 水平拆分
• 冷热数据分离

5.4 高级性能优化

5.4.1 缓存策略

• MySQL查询缓存
• 第三方缓存方案
• 缓存穿透和雪崩

5.4.2 硬件优化

• 磁盘I/O优化
• 内存配置
• 服务器选型

代码实践

5.1 索引基础实践

5.1.1 创建测试数据表

-- 创建大数据量测试表
CREATE TABLE performance_test (id INT PRIMARY KEY AUTO_INCREMENT,username VARCHAR(50),email VARCHAR(100),age INT,city VARCHAR(50),registration_date DATE
);-- 插入大量测试数据
DELIMITER //
CREATE PROCEDURE insert_test_data()
BEGINDECLARE i INT DEFAULT 0;WHILE i < 1000000 DOINSERT INTO performance_test (username, email, age, city, registration_date) VALUES (CONCAT('user_', i),CONCAT('user_', i, '@example.com'),FLOOR(RAND() * 50 + 18),CASE FLOOR(RAND() * 5)WHEN 0 THEN '北京'WHEN 1 THEN '上海'WHEN 2 THEN '广州'WHEN 3 THEN '深圳'ELSE '杭州'END,DATE_SUB(CURRENT_DATE, INTERVAL FLOOR(RAND() * 3650) DAY));SET i = i + 1;END WHILE;
END //
DELIMITER ;-- 调用存储过程插入数据
CALL insert_test_data();

5.1.2 不同类型索引

-- 普通索引
CREATE INDEX idx_username ON performance_test(username);-- 唯一索引
CREATE UNIQUE INDEX idx_email ON performance_test(email);-- 复合索引
CREATE INDEX idx_age_city ON performance_test(age, city);-- 全文索引（需要FULLTEXT引擎）
CREATE FULLTEXT INDEX idx_fulltext_username ON performance_test(username);

5.2 性能分析实践

5.2.1 EXPLAIN分析查询

-- 未优化查询
EXPLAIN SELECT * FROM performance_test 
WHERE username = 'user_500000';-- 使用索引后的查询
EXPLAIN SELECT * FROM performance_test 
WHERE username = 'user_500000';

5.2.2 慢查询日志分析

-- 配置慢查询日志（需要在MySQL配置文件中设置）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 记录超过1秒的查询-- 模拟慢查询
SELECT * FROM performance_test 
WHERE age > 30 
ORDER BY registration_date 
LIMIT 10000;

5.3 查询优化技巧

5.3.1 索引优化

-- 不走索引的查询
EXPLAIN SELECT * FROM performance_test 
WHERE YEAR(registration_date) = 2022;-- 优化后的查询
CREATE INDEX idx_registration_date ON performance_test(registration_date);-- 避免在索引列使用函数
EXPLAIN SELECT * FROM performance_test 
WHERE registration_date >= '2022-01-01';

5.3.2 JOIN优化

-- 创建关联表
CREATE TABLE user_orders (order_id INT PRIMARY KEY AUTO_INCREMENT,user_id INT,order_amount DECIMAL(10, 2),order_date DATE
);-- 插入测试数据
INSERT INTO user_orders (user_id, order_amount, order_date)
SELECT id, ROUND(RAND() * 1000, 2), DATE_SUB(CURRENT_DATE, INTERVAL FLOOR(RAND() * 365) DAY)
FROM performance_test
LIMIT 100000;-- 未优化的连接查询
EXPLAIN SELECT pt.username, uo.order_amount 
FROM performance_test pt
JOIN user_orders uo ON pt.id = uo.user_id
WHERE pt.age > 30;-- 添加索引优化
CREATE INDEX idx_user_id ON user_orders(user_id);
CREATE INDEX idx_age ON performance_test(age);

5.3.3 分页优化

-- 传统分页（性能较差）
EXPLAIN SELECT * FROM performance_test 
ORDER BY id 
LIMIT 100000, 20;-- 优化的分页查询
EXPLAIN SELECT * FROM performance_test 
WHERE id > (SELECT id FROM performance_test ORDER BY id LIMIT 100000, 1
) 
ORDER BY id 
LIMIT 20;

5.4 高级性能优化

5.4.1 查询缓存（MySQL 8.0以下）

-- 查看查询缓存状态
SHOW VARIABLES LIKE 'query_cache%';-- 开启查询缓存
SET GLOBAL query_cache_type = 1;
SET GLOBAL query_cache_size = 67108864;  -- 64MB

5.4.2 表结构优化

-- 选择合适的数据类型
CREATE TABLE optimized_table (id INT UNSIGNED PRIMARY KEY,  -- 无符号整数username VARCHAR(50) CHARACTER SET utf8mb4,age TINYINT UNSIGNED,  -- 使用更小的整数类型registration_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;