第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 创建测试数据表

1. -- 创建大数据量测试表
2. CREATE TABLE performance_test (
3.     id INT PRIMARY KEY AUTO_INCREMENT,
4.     username VARCHAR(50),
5.     email VARCHAR(100),
6.     age INT,
7.     city VARCHAR(50),
8.     registration_date DATE
9. );
11. -- 插入大量测试数据
12. DELIMITER //
13. CREATE PROCEDURE insert_test_data()
14. BEGIN
15.     DECLARE i INT DEFAULT 0;
16.     WHILE i < 1000000 DO
17.         INSERT INTO performance_test
18.         (username, email, age, city, registration_date)
19.         VALUES (
20.             CONCAT('user_', i),
21.             CONCAT('user_', i, '@example.com'),
22.             FLOOR(RAND() * 50 + 18),
23.             CASE FLOOR(RAND() * 5)
24.                 WHEN 0 THEN '北京'
25.                 WHEN 1 THEN '上海'
26.                 WHEN 2 THEN '广州'
27.                 WHEN 3 THEN '深圳'
28.                 ELSE '杭州'
29.             END,
30.             DATE_SUB(CURRENT_DATE, INTERVAL FLOOR(RAND() * 3650) DAY)
31.         );
32.         SET i = i + 1;
33.     END WHILE;
34. END //
35. DELIMITER ;
37. -- 调用存储过程插入数据
38. CALL insert_test_data();

复制代码

5.1.2 不同类型索引

1. -- 普通索引
2. CREATE INDEX idx_username ON performance_test(username);
4. -- 唯一索引
5. CREATE UNIQUE INDEX idx_email ON performance_test(email);
7. -- 复合索引
8. CREATE INDEX idx_age_city ON performance_test(age, city);
10. -- 全文索引（需要FULLTEXT引擎）
11. CREATE FULLTEXT INDEX idx_fulltext_username ON performance_test(username);

复制代码

5.2 性能分析实践

5.2.1 EXPLAIN分析查询

1. -- 未优化查询
2. EXPLAIN SELECT * FROM performance_test
3. WHERE username = 'user_500000';
5. -- 使用索引后的查询
6. EXPLAIN SELECT * FROM performance_test
7. WHERE username = 'user_500000';

复制代码

5.2.2 慢查询日志分析

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

复制代码

5.3 查询优化技巧

5.3.1 索引优化

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

复制代码

5.3.2 JOIN优化

1. -- 创建关联表
2. CREATE TABLE user_orders (
3.     order_id INT PRIMARY KEY AUTO_INCREMENT,
4.     user_id INT,
5.     order_amount DECIMAL(10, 2),
6.     order_date DATE
7. );
9. -- 插入测试数据
10. INSERT INTO user_orders (user_id, order_amount, order_date)
11. SELECT
12.     id,
13.     ROUND(RAND() * 1000, 2),
14.     DATE_SUB(CURRENT_DATE, INTERVAL FLOOR(RAND() * 365) DAY)
15. FROM performance_test
16. LIMIT 100000;
18. -- 未优化的连接查询
19. EXPLAIN SELECT
20.     pt.username,
21.     uo.order_amount
22. FROM performance_test pt
23. JOIN user_orders uo ON pt.id = uo.user_id
24. WHERE pt.age > 30;
26. -- 添加索引优化
27. CREATE INDEX idx_user_id ON user_orders(user_id);
28. CREATE INDEX idx_age ON performance_test(age);

复制代码

5.3.3 分页优化

1. -- 传统分页（性能较差）
2. EXPLAIN SELECT * FROM performance_test
3. ORDER BY id
4. LIMIT 100000, 20;
6. -- 优化的分页查询
7. EXPLAIN SELECT * FROM performance_test
8. WHERE id > (
9.     SELECT id FROM performance_test
10.     ORDER BY id
11.     LIMIT 100000, 1
12. )
13. ORDER BY id
14. LIMIT 20;

复制代码

5.4 高级性能优化

5.4.1 查询缓存（MySQL 8.0以下）

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

复制代码

5.4.2 表结构优化

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

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。