MySQL查询性能优化完全指南

🎯 前言

在实际的生产环境中，随着数据量的增长和业务复杂度的提升，SQL查询性能往往成为系统的瓶颈。一条优化良好的SQL语句和一条糟糕的SQL语句，其性能差异可能达到成百上千倍。本文将从零开始，深入浅出地讲解MySQL查询性能优化的核心技术，包括执行计划分析、慢查询优化以及SQL编写的最佳实践。

为什么查询优化如此重要？

1. 提升用户体验：快速响应用户请求，减少等待时间
2. 节约服务器资源：降低CPU、内存、磁盘I/O消耗
3. 增强系统稳定性：避免慢查询阻塞其他操作
4. 支撑业务增长：确保系统能够处理更大的数据量和并发量

1. EXPLAIN执行计划详解

1.1 什么是执行计划？

执行计划是MySQL优化器为SQL查询选择的具体执行策略，它展示了MySQL如何执行查询，包括使用哪些索引、以什么顺序访问表、采用什么连接方式等关键信息。

-- 基本语法
EXPLAIN SELECT * FROM table_name WHERE condition;

-- 详细格式输出
EXPLAIN FORMAT=JSON SELECT * FROM table_name WHERE condition;

-- 查看实际执行统计（MySQL 8.0+）
EXPLAIN ANALYZE SELECT * FROM table_name WHERE condition;

1.2 EXPLAIN输出字段详解

让我们通过实际示例来理解每个字段的含义：

-- 创建示例表
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10,2),
    hire_date DATE,
    INDEX idx_department (department),
    INDEX idx_salary (salary),
    INDEX idx_name_dept (name, department)
);

-- 插入测试数据
INSERT INTO employees (name, department, salary, hire_date) VALUES
('张三', '技术部', 8000.00, '2020-01-15'),
('李四', '销售部', 6000.00, '2019-03-20'),
('王五', '技术部', 9000.00, '2018-07-10'),
('赵六', '人事部', 5500.00, '2021-05-08');

-- 执行EXPLAIN分析
EXPLAIN SELECT * FROM employees WHERE department = '技术部' AND salary > 7000;

字段详解表格

字段	含义	重要性
id	查询序列号，标识执行顺序	⭐⭐⭐
select_type	查询类型（SIMPLE、PRIMARY、SUBQUERY等）	⭐⭐⭐
table	当前行涉及的表名	⭐⭐
partitions	匹配的分区（分区表时显示）	⭐
type	连接类型，性能从好到坏排序	⭐⭐⭐⭐⭐
possible_keys	可能使用的索引	⭐⭐⭐
key	实际使用的索引	⭐⭐⭐⭐
key_len	使用索引的长度	⭐⭐⭐
ref	与索引比较的列或常数	⭐⭐
rows	估计需要扫描的行数	⭐⭐⭐⭐
filtered	按表条件过滤的行百分比	⭐⭐⭐
Extra	额外信息，包含重要的执行细节	⭐⭐⭐⭐

1.3 关键字段深度解析

1.3.1 type字段（最重要）

type字段表示连接类型，性能从好到坏依次为：

-- 1. system：表中只有一行数据（系统表）
-- 这种情况很少见

-- 2. const：通过主键或唯一索引访问，最多返回一行
EXPLAIN SELECT * FROM employees WHERE id = 1;
-- type: const，性能最优

-- 3. eq_ref：连接查询中，驱动表的每行在被驱动表中最多匹配一行
EXPLAIN SELECT e.*, d.name as dept_name 
FROM employees e 
JOIN departments d ON e.department_id = d.id;
-- type: eq_ref，性能很好

-- 4. ref：通过非唯一索引访问
EXPLAIN SELECT * FROM employees WHERE department = '技术部';
-- type: ref，性能良好

-- 5. fulltext：使用全文索引
-- 需要先创建全文索引
-- type: fulltext

-- 6. ref_or_null：类似ref，但额外搜索NULL值
EXPLAIN SELECT * FROM employees WHERE department = '技术部' OR department IS NULL;
-- type: ref_or_null

-- 7. index_merge：使用索引合并优化
EXPLAIN SELECT * FROM employees WHERE department = '技术部' OR salary > 8000;
-- type: index_merge

-- 8. unique_subquery：子查询中使用唯一索引
-- 9. index_subquery：子查询中使用非唯一索引

-- 10. range：索引范围扫描
EXPLAIN SELECT * FROM employees WHERE salary BETWEEN 6000 AND 8000;
-- type: range，性能尚可

-- 11. index：全索引扫描
EXPLAIN SELECT department FROM employees;
-- type: index，性能较差

-- 12. ALL：全表扫描
EXPLAIN SELECT * FROM employees WHERE name LIKE '%张%';
-- type: ALL，性能最差，需要优化

1.3.2 Extra字段重要信息

-- Using index：使用覆盖索引，无需回表查询
EXPLAIN SELECT department, salary FROM employees WHERE department = '技术部';
-- Extra: Using index

-- Using where：使用WHERE条件过滤
EXPLAIN SELECT * FROM employees WHERE salary > 7000;
-- Extra: Using where

-- Using temporary：使用临时表
EXPLAIN SELECT department, COUNT(*) FROM employees GROUP BY department;
-- Extra: Using temporary

-- Using filesort：使用文件排序（性能较差）
EXPLAIN SELECT * FROM employees ORDER BY name;
-- Extra: Using filesort

-- Using index condition：使用索引条件下推
EXPLAIN SELECT * FROM employees WHERE department = '技术部' AND name LIKE '张%';
-- Extra: Using index condition

1.4 执行计划分析实战

1.4.1 性能问题诊断示例

-- 问题查询：全表扫描
EXPLAIN SELECT * FROM employees WHERE name LIKE '%张%';

分析结果：

• type: ALL（全表扫描）
• rows: 1000+（扫描大量行）
• Extra: Using where

优化方案：

-- 1. 如果必须模糊查询，考虑全文索引
ALTER TABLE employees ADD FULLTEXT(name);
EXPLAIN SELECT * FROM employees WHERE MATCH(name) AGAINST('张' IN NATURAL LANGUAGE MODE);

-- 2. 改为前缀匹配
EXPLAIN SELECT * FROM employees WHERE name LIKE '张%';

1.4.2 复杂查询优化示例

-- 原始查询：性能较差
EXPLAIN SELECT e.name, e.salary, d.name as dept_name
FROM employees e, departments d
WHERE e.department_id = d.id
AND e.salary > (SELECT AVG(salary) FROM employees)
ORDER BY e.salary DESC;

分析问题：

1. 使用旧式连接语法
2. 子查询可能重复执行
3. 排序字段可能需要索引

优化方案：

-- 优化后的查询
EXPLAIN SELECT e.name, e.salary, d.name as dept_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
INNER JOIN (SELECT AVG(salary) as avg_sal FROM employees) avg_table
WHERE e.salary > avg_table.avg_sal
ORDER BY e.salary DESC;

-- 添加必要索引
CREATE INDEX idx_salary_desc ON employees(salary DESC);

2. 慢查询日志分析与优化

2.1 慢查询日志配置

2.1.1 启用慢查询日志

-- 查看慢查询日志状态
SHOW VARIABLES LIKE '%slow_query%';
SHOW VARIABLES LIKE '%long_query_time%';

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

-- 记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- 记录管理语句
SET GLOBAL log_slow_admin_statements = 'ON';

2.1.2 配置文件永久设置

# my.cnf配置文件
[mysqld]
# 启用慢查询日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

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

# 限制未使用索引查询的记录频率（每分钟最多记录10次）
log_throttle_queries_not_using_indexes = 10

# 记录管理语句（如ALTER TABLE）
log_slow_admin_statements = 1

# 最小扫描行数阈值
min_examined_row_limit = 100

2.2 慢查询日志分析

2.2.1 日志格式解读

# 慢查询日志示例
# Time: 2024-01-15T10:30:45.123456Z
# User@Host: app_user[app_user] @ [192.168.1.100]
# Thread_id: 12345  Schema: ecommerce  QC_hit: No
# Query_time: 3.456789  Lock_time: 0.000123  Rows_sent: 1500  Rows_examined: 45000
# Rows_affected: 0  Bytes_sent: 150000
SET timestamp=1705319445;
SELECT o.order_id, o.total_amount, c.customer_name, p.product_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-01-31'
ORDER BY o.total_amount DESC;

字段解释：

• Query_time: 查询执行时间（3.46秒）
• Lock_time: 等待锁的时间（0.12毫秒）
• Rows_sent: 返回给客户端的行数（1500行）
• Rows_examined: 扫描的行数（45000行）
• 扫描效率: 1500/45000 = 3.3%（效率较低）

2.2.2 使用mysqldumpslow工具分析

# 分析慢查询日志的基本命令
mysqldumpslow /var/log/mysql/slow.log

# 显示查询时间最长的10条SQL
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 显示扫描行数最多的10条SQL
mysqldumpslow -s r -t 10 /var/log/mysql/slow.log

# 显示访问次数最多的10条SQL
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

# 结合grep过滤特定表的慢查询
mysqldumpslow /var/log/mysql/slow.log | grep -i "orders"

# 获取时间范围内的慢查询
mysqldumpslow -t 20 -s t -g "FROM orders" /var/log/mysql/slow.log

2.2.3 使用pt-query-digest工具（推荐）

# 安装percona-toolkit
# Ubuntu/Debian
sudo apt-get install percona-toolkit

# CentOS/RHEL
sudo yum install percona-toolkit

# 分析慢查询日志
pt-query-digest /var/log/mysql/slow.log > slow_analysis.txt

# 实时分析慢查询
pt-query-digest --processlist h=localhost,u=root,p=password

# 按查询时间排序并限制输出
pt-query-digest --limit 10 --order-by Query_time:sum /var/log/mysql/slow.log

2.3 慢查询优化实战

2.3.1 案例1：JOIN查询优化

问题查询：

-- 慢查询：缺少适当索引的多表连接
SELECT o.order_id, c.customer_name, p.product_name, oi.quantity
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= '2024-01-01'
ORDER BY o.order_date DESC;

分析与优化：

-- 1. 分析执行计划
EXPLAIN SELECT o.order_id, c.customer_name, p.product_name, oi.quantity
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= '2024-01-01'
ORDER BY o.order_date DESC;

-- 2. 创建必要索引
CREATE INDEX idx_orders_date_customer ON orders(order_date, customer_id);
CREATE INDEX idx_order_items_order_product ON order_items(order_id, product_id);
CREATE INDEX idx_customers_id_name ON customers(customer_id, customer_name);
CREATE INDEX idx_products_id_name ON products(product_id, product_name);

-- 3. 优化后的查询（使用覆盖索引）
EXPLAIN SELECT o.order_id, c.customer_name, p.product_name, oi.quantity
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= '2024-01-01'
ORDER BY o.order_date DESC;

2.3.2 案例2：子查询优化

问题查询：

-- 慢查询：相关子查询
SELECT customer_id, customer_name,
       (SELECT COUNT(*) FROM orders WHERE customer_id = c.customer_id) as order_count,
       (SELECT SUM(total_amount) FROM orders WHERE customer_id = c.customer_id) as total_spent
FROM customers c
WHERE customer_type = 'VIP';

优化方案：

-- 方案1：改为JOIN查询
SELECT c.customer_id, c.customer_name, 
       COALESCE(o.order_count, 0) as order_count,
       COALESCE(o.total_spent, 0) as total_spent
FROM customers c
LEFT JOIN (
    SELECT customer_id, 
           COUNT(*) as order_count,
           SUM(total_amount) as total_spent
    FROM orders 
    GROUP BY customer_id
) o ON c.customer_id = o.customer_id
WHERE c.customer_type = 'VIP';

-- 方案2：使用窗口函数（MySQL 8.0+）
SELECT DISTINCT c.customer_id, c.customer_name,
       COUNT(o.order_id) OVER (PARTITION BY c.customer_id) as order_count,
       SUM(o.total_amount) OVER (PARTITION BY c.customer_id) as total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_type = 'VIP';

2.4 慢查询监控与预警

2.4.1 创建监控脚本

#!/bin/bash
# slow_query_monitor.sh - 慢查询监控脚本

MYSQL_USER="monitor_user"
MYSQL_PASS="monitor_pass"
SLOW_LOG="/var/log/mysql/slow.log"
THRESHOLD=5  # 查询时间阈值（秒）

# 统计最近1小时的慢查询
SLOW_COUNT=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -e "
SELECT COUNT(*) as slow_queries 
FROM mysql.slow_log 
WHERE start_time >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
AND query_time > $THRESHOLD;" -s -N)

# 如果慢查询超过阈值，发送告警
if [ "$SLOW_COUNT" -gt 10 ]; then
    echo "告警：最近1小时内有 $SLOW_COUNT 条慢查询" | mail -s "MySQL慢查询告警" admin@company.com
fi

# 生成每日慢查询报告
pt-query-digest --since '1d' $SLOW_LOG > /tmp/daily_slow_report.txt

2.4.2 Performance Schema监控

-- 启用Performance Schema（MySQL 5.6+）
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES' 
WHERE NAME LIKE '%statement%';

UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' 
WHERE NAME LIKE '%events_statements%';

-- 查询最耗时的SQL语句
SELECT sql_text,
       COUNT_STAR as exec_count,
       AVG_TIMER_WAIT/1000000000 as avg_time_sec,
       SUM_TIMER_WAIT/1000000000 as total_time_sec,
       AVG_ROWS_EXAMINED as avg_rows_examined
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- 查询特定时间段的慢查询
SELECT event_name, sql_text, timer_wait/1000000000 as exec_time_sec
FROM performance_schema.events_statements_history_long
WHERE timer_wait > 1000000000  -- 超过1秒
AND event_time BETWEEN '2024-01-15 10:00:00' AND '2024-01-15 11:00:00'
ORDER BY timer_wait DESC;

3. SQL优化技巧和最佳实践

3.1 索引优化策略

3.1.1 索引设计原则

-- 1. 选择性原则：为选择性高的列创建索引
-- 查看列的选择性
SELECT COUNT(DISTINCT customer_id)/COUNT(*) as selectivity 
FROM orders;

-- 选择性越接近1，索引效果越好
SELECT 
    column_name,
    COUNT(DISTINCT column_name)/COUNT(*) as selectivity
FROM information_schema.columns 
WHERE table_name = 'orders';

-- 2. 最左前缀原则：复合索引的列顺序很重要
CREATE INDEX idx_name_dept_salary ON employees(name, department, salary);

-- 可以使用的查询
EXPLAIN SELECT * FROM employees WHERE name = '张三';                    -- 使用索引
EXPLAIN SELECT * FROM employees WHERE name = '张三' AND department = '技术部'; -- 使用索引
EXPLAIN SELECT * FROM employees WHERE name = '张三' AND salary > 8000;  -- 部分使用索引

-- 不能使用索引的查询
EXPLAIN SELECT * FROM employees WHERE department = '技术部';             -- 不使用索引
EXPLAIN SELECT * FROM employees WHERE salary > 8000;                   -- 不使用索引

-- 3. 覆盖索引：索引包含查询所需的所有列
CREATE INDEX idx_cover_dept_salary ON employees(department, salary, name);

-- 覆盖索引查询（无需回表）
EXPLAIN SELECT department, salary, name 
FROM employees 
WHERE department = '技术部';
-- Extra: Using index

3.1.2 索引使用技巧

-- 1. 前缀索引：为长字符串创建前缀索引
-- 分析前缀长度的选择性
SELECT 
    ROUND(COUNT(DISTINCT LEFT(email, 3))/COUNT(*), 4) as prefix_3,
    ROUND(COUNT(DISTINCT LEFT(email, 5))/COUNT(*), 4) as prefix_5,
    ROUND(COUNT(DISTINCT LEFT(email, 7))/COUNT(*), 4) as prefix_7,
    ROUND(COUNT(DISTINCT LEFT(email, 10))/COUNT(*), 4) as prefix_10,
    ROUND(COUNT(DISTINCT email)/COUNT(*), 4) as full_length
FROM users;

-- 创建前缀索引
CREATE INDEX idx_email_prefix ON users(email(10));

-- 2. 函数索引：为函数表达式创建索引（MySQL 8.0+）
CREATE INDEX idx_upper_name ON employees((UPPER(name)));

-- 可以使用函数索引的查询
EXPLAIN SELECT * FROM employees WHERE UPPER(name) = 'ZHANG SAN';

-- 3. 条件索引：为满足特定条件的行创建索引（部分索引）
-- MySQL不直接支持，但可以通过虚拟列实现
ALTER TABLE orders ADD COLUMN is_large_order BOOLEAN 
GENERATED ALWAYS AS (total_amount > 1000) STORED;

CREATE INDEX idx_large_orders ON orders(is_large_order, order_date);

3.2 查询重写技巧

3.2.1 WHERE条件优化

-- 1. 避免在WHERE子句中使用函数
-- 糟糕的查询
EXPLAIN SELECT * FROM orders WHERE YEAR(order_date) = 2024;

-- 优化后的查询
EXPLAIN SELECT * FROM orders 
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';

-- 2. 避免隐式类型转换
-- 糟糕的查询（如果order_id是字符串类型）
EXPLAIN SELECT * FROM orders WHERE order_id = 12345;

-- 正确的查询
EXPLAIN SELECT * FROM orders WHERE order_id = '12345';

-- 3. 使用EXISTS替代IN（当子查询返回大量数据时）
-- 原查询
SELECT * FROM customers c
WHERE c.customer_id IN (
    SELECT customer_id FROM orders WHERE order_date >= '2024-01-01'
);

-- 优化查询
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.customer_id 
    AND o.order_date >= '2024-01-01'
);

-- 4. 范围查询优化
-- 糟糕的查询
EXPLAIN SELECT * FROM orders WHERE order_id != 1000;

-- 优化查询（如果可能的话）
EXPLAIN SELECT * FROM orders WHERE order_id < 1000 OR order_id > 1000;

3.2.2 JOIN优化技巧

-- 1. 小表驱动大表
-- 分析表大小
SELECT table_name, table_rows 
FROM information_schema.tables 
WHERE table_schema = 'ecommerce';

-- 确保小表作为驱动表
SELECT /*+ USE_INDEX(o, idx_date) */ o.order_id, c.customer_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2024-01-01';

-- 2. 使用适当的JOIN类型
-- INNER JOIN：只返回匹配的行
SELECT o.order_id, c.customer_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

-- LEFT JOIN：返回左表所有行
SELECT c.customer_name, COUNT(o.order_id) as order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

-- 3. 避免笛卡尔积
-- 错误的连接（缺少连接条件）
-- SELECT * FROM orders, customers;  -- 避免这样写

-- 正确的连接
SELECT * FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

3.2.3 子查询优化

-- 1. 将相关子查询改为JOIN
-- 原查询（相关子查询）
SELECT customer_id, customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.customer_id 
    AND o.total_amount > 1000
);

-- 优化查询（改为JOIN）
SELECT DISTINCT c.customer_id, c.customer_name
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.total_amount > 1000;

-- 2. 使用临时表分解复杂查询
-- 复杂查询
SELECT c.customer_name, 
       (SELECT AVG(total_amount) FROM orders WHERE customer_id = c.customer_id) as avg_order,
       (SELECT COUNT(*) FROM orders WHERE customer_id = c.customer_id) as order_count
FROM customers c
WHERE customer_type = 'VIP';

-- 分解为多步查询
-- 步骤1：创建临时表
CREATE TEMPORARY TABLE customer_stats AS
SELECT customer_id, 
       AVG(total_amount) as avg_order,
       COUNT(*) as order_count
FROM orders 
GROUP BY customer_id;

-- 步骤2：连接查询
SELECT c.customer_name, cs.avg_order, cs.order_count
FROM customers c
INNER JOIN customer_stats cs ON c.customer_id = cs.customer_id
WHERE c.customer_type = 'VIP';

-- 清理临时表
DROP TEMPORARY TABLE customer_stats;

3.3 分页查询优化

3.3.1 LIMIT优化技巧

-- 1. 传统分页问题（深度分页性能差）
-- 第1页：性能良好
EXPLAIN SELECT * FROM orders ORDER BY order_id LIMIT 0, 20;

-- 第1000页：性能差
EXPLAIN SELECT * FROM orders ORDER BY order_id LIMIT 20000, 20;

-- 2. 使用子查询优化深度分页
-- 原查询
SELECT * FROM orders ORDER BY order_id LIMIT 20000, 20;

-- 优化查询（先获取ID范围，再查询详细信息）
SELECT * FROM orders o
INNER JOIN (
    SELECT order_id FROM orders 
    ORDER BY order_id 
    LIMIT 20000, 20
) t ON o.order_id = t.order_id
ORDER BY o.order_id;

-- 3. 使用游标分页（推荐）
-- 第一页
SELECT * FROM orders WHERE order_id > 0 ORDER BY order_id LIMIT 20;

-- 下一页（假设上页最后一条记录的order_id是1020）
SELECT * FROM orders WHERE order_id > 1020 ORDER BY order_id LIMIT 20;

-- 4. 延迟关联优化
SELECT o.* FROM orders o
INNER JOIN (
    SELECT order_id FROM orders 
    WHERE order_date >= '2024-01-01'
    ORDER BY order_date DESC
    LIMIT 20000, 20
) t ON o.order_id = t.order_id;

3.3.2 COUNT查询优化

-- 1. COUNT(*)性能问题
-- 避免在大表上使用COUNT(*)
-- SELECT COUNT(*) FROM orders;  -- 可能很慢

-- 2. 使用近似计数
-- 从统计信息表获取近似行数
SELECT table_rows 
FROM information_schema.tables 
WHERE table_schema = 'ecommerce' AND table_name = 'orders';

-- 3. 缓存计数结果
-- 创建计数表
CREATE TABLE table_counts (
    table_name VARCHAR(50) PRIMARY KEY,
    row_count BIGINT,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 定期更新计数
INSERT INTO table_counts (table_name, row_count) 
VALUES ('orders', (SELECT COUNT(*) FROM orders))
ON DUPLICATE KEY UPDATE 
row_count = VALUES(row_count),
last_updated = CURRENT_TIMESTAMP;

-- 4. 分段计数
-- 将大表分段计算
SELECT 
    SUM(CASE WHEN order_date >= '2024-01-01' AND order_date < '2024-02-01' THEN 1 ELSE 0 END) as jan_count,
    SUM(CASE WHEN order_date >= '2024-02-01' AND order_date < '2024-03-01' THEN 1 ELSE 0 END) as feb_count
FROM orders;

3.4 SQL编写最佳实践

3.4.1 SELECT语句优化

-- 1. 避免SELECT *
-- 糟糕的查询
SELECT * FROM orders WHERE order_date >= '2024-01-01';

-- 优化查询（只选择需要的列）
SELECT order_id, customer_id, total_amount 
FROM orders 
WHERE order_date >= '2024-01-01';

-- 2. 使用列别名提高可读性
SELECT 
    o.order_id as 订单号,
    c.customer_name as 客户名称,
    o.total_amount as 订单金额
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

-- 3. 合理使用DISTINCT
-- 避免不必要的DISTINCT
-- SELECT DISTINCT customer_id FROM orders;  -- 如果customer_id有索引，可能不需要DISTINCT

-- 正确使用DISTINCT
SELECT DISTINCT c.customer_name
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

-- 4. 使用UNION ALL替代UNION（如果不需要去重）
-- UNION（会去重，性能较慢）
SELECT customer_id FROM customers WHERE customer_type = 'VIP'
UNION
SELECT customer_id FROM customers WHERE total_spent > 10000;

-- UNION ALL（不去重，性能较快）
SELECT customer_id FROM customers WHERE customer_type = 'VIP'
UNION ALL
SELECT customer_id FROM customers WHERE customer_type = 'PREMIUM';

3.4.2 更新语句优化

-- 1. 批量更新优化
-- 避免逐行更新
-- 单行更新（效率低）
UPDATE orders SET status = 'shipped' WHERE order_id = 1001;
UPDATE orders SET status = 'shipped' WHERE order_id = 1002;
-- ... 更多单行更新

-- 批量更新（效率高）
UPDATE orders 
SET status = 'shipped' 
WHERE order_id IN (1001, 1002, 1003, 1004, 1005);

-- 2. 使用JOIN进行更新
-- 根据其他表的数据更新
UPDATE orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
SET o.discount_rate = 0.1
WHERE c.customer_type = 'VIP';

-- 3. 使用CASE语句进行条件更新
UPDATE orders 
SET shipping_fee = CASE 
    WHEN total_amount > 500 THEN 0
    WHEN total_amount > 200 THEN 10
    ELSE 20
END
WHERE order_date >= '2024-01-01';

-- 4. 分批次更新大量数据
-- 避免长时间锁表
DELIMITER $$
CREATE PROCEDURE BatchUpdate()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE batch_size INT DEFAULT 1000;
    
    REPEAT
        UPDATE orders 
        SET processed = 1 
        WHERE processed = 0 
        LIMIT batch_size;
        
        -- 检查是否还有数据需要更新
        SELECT ROW_COUNT() = 0 INTO done;
        
        -- 短暂休息，避免占用过多资源
        SELECT SLEEP(0.1);
    UNTIL done END REPEAT;
END$$
DELIMITER ;

3.4.3 INSERT语句优化

-- 1. 批量插入
-- 避免逐行插入
-- INSERT INTO orders (customer_id, total_amount) VALUES (1001, 100.00);
-- INSERT INTO orders (customer_id, total_amount) VALUES (1002, 150.00);

-- 批量插入（效率高）
INSERT INTO orders (customer_id, total_amount) VALUES 
(1001, 100.00),
(1002, 150.00),
(1003, 200.00),
(1004, 175.00);

-- 2. 使用INSERT ... SELECT进行批量数据传输
INSERT INTO orders_archive (order_id, customer_id, total_amount, order_date)
SELECT order_id, customer_id, total_amount, order_date
FROM orders 
WHERE order_date < '2023-01-01';

-- 3. 使用INSERT ... ON DUPLICATE KEY UPDATE
INSERT INTO customer_summary (customer_id, total_orders, total_amount)
VALUES (1001, 1, 100.00)
ON DUPLICATE KEY UPDATE 
total_orders = total_orders + VALUES(total_orders),
total_amount = total_amount + VALUES(total_amount);

-- 4. 禁用自动提交进行大批量插入
SET autocommit = 0;
START TRANSACTION;

INSERT INTO large_table (col1, col2, col3) VALUES (...);
-- 插入大量数据

COMMIT;
SET autocommit = 1;

3.5 数据库设计优化

3.5.1 表结构优化

-- 1. 选择合适的数据类型
-- 避免使用过大的数据类型
CREATE TABLE optimized_table (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  -- 使用UNSIGNED节省空间
    status TINYINT NOT NULL DEFAULT 0,           -- 状态字段使用TINYINT
    amount DECIMAL(10,2) NOT NULL,               -- 金额使用DECIMAL
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 时间戳字段
    description VARCHAR(255) NOT NULL            -- 根据实际需要设置长度
);

-- 2. 垂直分表（列分离）
-- 原表（包含大字段）
CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    summary TEXT,
    content LONGTEXT,  -- 大字段
    author_id INT,
    created_at TIMESTAMP
);

-- 分离后的主表
CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    summary TEXT,
    author_id INT,
    created_at TIMESTAMP
);

-- 内容表
CREATE TABLE article_content (
    article_id INT PRIMARY KEY,
    content LONGTEXT,
    FOREIGN KEY (article_id) REFERENCES articles(id)
);

-- 3. 水平分表（行分离）
-- 按时间分表
CREATE TABLE orders_2024_q1 (
    order_id INT PRIMARY KEY,
    customer_id INT,
    total_amount DECIMAL(10,2),
    order_date DATE,
    CHECK (order_date >= '2024-01-01' AND order_date < '2024-04-01')
);

CREATE TABLE orders_2024_q2 (
    order_id INT PRIMARY KEY,
    customer_id INT,
    total_amount DECIMAL(10,2),
    order_date DATE,
    CHECK (order_date >= '2024-04-01' AND order_date < '2024-07-01')
);

3.5.2 范式化与反范式化

-- 1. 范式化设计（减少数据冗余）
-- 客户表
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    email VARCHAR(100),
    phone VARCHAR(20)
);

-- 订单表
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- 2. 反范式化设计（提高查询性能）
-- 在orders表中冗余客户信息，避免频繁JOIN
CREATE TABLE orders_denormalized (
    order_id INT PRIMARY KEY,
    customer_id INT,
    customer_name VARCHAR(100),  -- 冗余字段
    customer_email VARCHAR(100), -- 冗余字段
    order_date DATE,
    total_amount DECIMAL(10,2)
);

-- 3. 维护反范式化数据一致性
-- 使用触发器保持数据同步
DELIMITER $$
CREATE TRIGGER update_customer_info
AFTER UPDATE ON customers
FOR EACH ROW
BEGIN
    UPDATE orders_denormalized 
    SET customer_name = NEW.customer_name,
        customer_email = NEW.email
    WHERE customer_id = NEW.customer_id;
END$$
DELIMITER ;

4. 总结与最佳实践汇总

4.1 性能优化检查清单

🔍 查询分析阶段

• 使用EXPLAIN分析执行计划
• 检查type字段，避免ALL和index
• 确认key字段使用了合适的索引
• 关注rows字段，减少扫描行数
• 分析Extra字段，优化临时表和文件排序

📊 索引优化阶段

• 为WHERE条件中的列创建索引
• 为JOIN连接列创建索引
• 为ORDER BY排序列创建索引
• 使用复合索引覆盖多列查询
• 定期分析索引使用情况，删除无用索引

🔧 SQL重写阶段

• 避免SELECT *，只查询需要的列
• 优化WHERE条件，避免函数和隐式转换
• 使用合适的JOIN类型
• 将子查询改写为JOIN
• 优化分页查询，使用游标分页

📈 监控维护阶段

• 启用慢查询日志
• 定期分析慢查询报告
• 监控数据库性能指标
• 建立性能测试环境
• 制定性能优化流程

4.2 常见性能问题速查表

问题现象	可能原因	解决方案
查询速度慢	缺少索引	分析EXPLAIN，创建合适索引
CPU使用率高	复杂计算、函数调用	优化SQL逻辑，避免不必要计算
内存使用高	大结果集、临时表	限制查询结果，优化GROUP BY
磁盘I/O高	全表扫描、排序	创建索引，优化ORDER BY
并发性能差	长时间锁等待	优化事务，减少锁粒度

4.3 性能优化工具推荐

# 1. 慢查询分析工具
mysqldumpslow         # MySQL自带工具
pt-query-digest       # Percona工具包
mysqlsla             # 第三方分析工具

# 2. 性能监控工具
mysql -e "SHOW PROCESSLIST"              # 查看当前连接
mysql -e "SHOW ENGINE INNODB STATUS"     # InnoDB状态
pt-stalk                                 # 问题诊断工具

# 3. 索引分析工具
pt-index-usage       # 索引使用分析
pt-duplicate-key-checker  # 重复索引检查

通过本文的学习，您已经掌握了MySQL查询性能优化的核心技能。记住，性能优化是一个持续的过程，需要：

1. 定期监控：建立完善的监控体系
2. 及时分析：发现问题后立即分析
3. 持续改进：根据业务发展不断优化
4. 预防为主：在设计阶段就考虑性能

希望这些知识能够帮助您构建高性能的MySQL数据库系统！

---

💡 提示：性能优化需要结合具体业务场景，建议在测试环境中充分验证后再应用到生产环境。