企业官网建设流程全解析

1. 从EXPLAIN开始：读懂MySQL执行计划的必修课

第一次看到EXPLAIN输出时，我盯着那些"type=ALL"和"rows=10000"的字段发愣——这分明是数据库在向我发出性能警告。作为MySQL调优的起点，EXPLAIN就像数据库的X光片，能透视SQL语句执行的每个细节。

1.1 EXPLAIN基础字段解读实战

在终端执行EXPLAIN SELECT * FROM orders WHERE user_id = 100，你会看到这样的输出：

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | orders | NULL | ALL | NULL | NULL | NULL | NULL | 8924 | 10.00 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

几个关键字段需要特别关注：

• type：这是性能判断的黄金指标。从最优到最差排序为：system > const > eq_ref > ref > range > index > ALL。上例中的ALL表示全表扫描，是性能杀手
• rows：MySQL预估需要检查的行数，8924行对于简单查询来说太高了
• key：实际使用的索引，NULL表示没有使用任何索引

经验：当看到type=ALL且rows超过1000时，就该考虑优化了。我曾处理过一个查询，仅通过添加索引就将执行时间从2.3秒降到了0.02秒。

1.2 高级EXPLAIN技巧：FORMAT=JSON和EXPLAIN ANALYZE

MySQL 8.0带来了更强大的分析工具：

EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE amount > 100;

JSON格式输出包含成本估算等更多细节。而EXPLAIN ANALYZE则会实际执行查询并返回真实耗时：

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 100;

输出示例：

-> Index lookup on orders using idx_user (user_id=100) (cost=5.23 rows=10) (actual time=0.025..0.036 rows=8 loops=1)

这里显示实际只扫描了8行（rows=8），耗时0.036毫秒，与预估的cost=5.23形成对比。我曾用这个功能发现优化器对JOIN顺序的错误估计，通过STRAIGHT_JOIN强制连接顺序后性能提升40%。

2. sys schema：数据库的"仪表盘"

当客户抱怨"数据库变慢了"，sys schema就是我的第一响应工具。这个内置在MySQL 5.7+中的库，把performance_schema的原始数据转换成了人类可读的诊断报告。

2.1 高频使用的sys schema视图

这几个视图是我每天必查的：

-- 查看哪些SQL消耗最多IO SELECT * FROM sys.io_global_by_file_by_bytes LIMIT 10; -- 找出全表扫描的语句 SELECT * FROM sys.statements_with_full_table_scans; -- 检查锁等待情况 SELECT * FROM sys.innodb_lock_waits;

上周通过sys.schema_index_statistics发现一个从未使用过的索引占用了15GB空间，删除后不仅节省了存储，还提升了写入速度20%。

2.2 自定义sys schema查询

结合多个视图可以挖掘更深层次问题：

SELECT esh.query, esh.latency, esc.exec_count, esc.total_latency FROM sys.statement_analysis esc JOIN sys.x$statement_analysis esh USING(digest) WHERE esc.exec_count > 1000 ORDER BY esc.total_latency DESC LIMIT 5;

这个查询帮我找到了一个被调用上万次的小查询，虽然单次很快(0.2ms)，但累计拖慢了系统。通过批量处理改造，整体负载下降了15%。

3. 索引优化的艺术与科学

记得有次调优经历：一个简单的SELECT * FROM users WHERE status=1 AND created_at>'2023-01-01'查询要8秒。EXPLAIN显示用了索引但rows仍有50万，问题出在哪？

3.1 多列索引的陷阱与诀窍

最初添加的索引是(status, created_at)，但效果不佳。因为：

1. status=1过滤性差（70%数据符合）
2. created_at范围查询导致后续索引列失效

解决方案是调整顺序并引入覆盖索引：

ALTER TABLE users ADD INDEX idx_created_status (created_at, status);

同时改写查询只获取必要字段：

SELECT id, name FROM users WHERE created_at>'2023-01-01' AND status=1;

执行时间从8秒→0.05秒。这里的关键认知是：范围查询后的索引列失效。

3.2 索引合并的代价

MySQL有时会使用Index Merge优化：

EXPLAIN SELECT * FROM products WHERE category_id=5 OR price<100;

输出中的type=index_merge表示MySQL合并了多个索引。但要注意：

• 只适合中小表
• 高并发下可能引发性能问题
• 更好的方案是创建复合索引(category_id, price)

4. 实战调优案例：从20秒到0.2秒的蜕变

最近优化过一个电商报表查询，原始SQL：

SELECT o.order_id, u.username, SUM(oi.amount) FROM orders o JOIN users u ON o.user_id = u.user_id JOIN order_items oi ON o.order_id = oi.order_id WHERE o.status = 'completed' AND o.created_at BETWEEN '2023-01-01' AND '2023-12-31' GROUP BY o.order_id HAVING SUM(oi.amount) > 1000 ORDER BY SUM(oi.amount) DESC LIMIT 100;

4.1 问题诊断过程

1. EXPLAIN显示：

   • 对orders表使用idx_status扫描了12万行
   • 对order_items全表扫描
   • 临时表处理GROUP BY

2. sys schema显示：

   • 每次执行需要读取800MB数据
   • 平均耗时18.7秒

4.2 优化方案实施

分阶段优化：

第一阶段：索引改造

ALTER TABLE orders ADD INDEX idx_status_created (status, created_at); ALTER TABLE order_items ADD INDEX idx_order (order_id);

第二阶段：查询重写

SELECT /*+ SEMIJOIN(MATERIALIZATION) */ o.order_id, u.username, oi.total_amount FROM orders o JOIN users u ON o.user_id = u.user_id JOIN ( SELECT order_id, SUM(amount) AS total_amount FROM order_items GROUP BY order_id HAVING SUM(amount) > 1000 ) oi ON o.order_id = oi.order_id WHERE o.status = 'completed' AND o.created_at BETWEEN '2023-01-01' AND '2023-12-31' ORDER BY oi.total_amount DESC LIMIT 100;

第三阶段：持久化中间结果

CREATE TABLE order_totals ( order_id BIGINT PRIMARY KEY, total_amount DECIMAL(12,2), INDEX idx_amount (total_amount) ); -- 定期更新 REPLACE INTO order_totals SELECT order_id, SUM(amount) FROM order_items GROUP BY order_id;

最终方案将查询时间降至0.15秒，关键点在于：

1. 避免在WHERE条件中对字段做计算
2. 使用派生表提前过滤数据
3. 对聚合结果建立专门存储

5. 性能监控体系的建立

调优不是一次性工作，我建议客户建立三层监控：

5.1 实时监控层

-- 每5分钟运行一次 SELECT * FROM sys.metrics WHERE variable_name IN ( 'threads_running', 'innodb_row_lock_waits', 'table_open_cache_overflows' );

5.2 慢查询分析层

-- 每天分析慢查询日志 pt-query-digest /var/lib/mysql/mysql-slow.log

5.3 趋势预测层

-- 每周检查增长趋势 SELECT table_schema, table_name, data_length/1024/1024 AS size_mb, index_length/1024/1024 AS idx_mb FROM information_schema.tables ORDER BY (data_length+index_length) DESC;

这套体系帮助一个客户提前发现了一个每月增长30GB的表，避免了潜在的存储危机。