第5章：索引和性能优化

第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()
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < 1000000 DO
        INSERT 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;