今天，笔者带你深入 MySQL 的内心世界，扒一扒这个每天被你“增删改查”的老伙计，到底怎么才能跑得比香港记者还快！
咱都是实干派，不整那些虚头巴脑的理论。直接上硬菜，告诉你为啥你的 SQL 写得跟树懒一样慢，以及怎么给它装上火箭推进器。
友情提示：只讲官网最硬核的实战，不搞理论废话。要是看完没收获，我当场……给你再讲一遍！
想象一下这个场景：月黑风高夜，你和对象（如果有的话）正在享受甜蜜时光。突然，报警短信“哔哔哔”响个不停——线上服务挂了一大片！
你火急火燎地打开监控一看，CPU 100%，数据库连接池爆满，无数请求在超时的边缘疯狂试探。你脑海中瞬间闪过三个字：慢查询！
别慌，笔者教你 MySQL 性能优化的“太极心法”：化 I/O 于无形，锁争用于无声。下面这套“组合拳”，请你接好。
数据库性能取决于数据库层面的多个因素，例如表结构、查询语句和配置设置。
这些软件层面的设计最终会转化为硬件层面的 CPU 和 I/O 操作，必须尽可能减少这些操作并提升其效率。
在进行数据库性能优化时，首先需要掌握软件层面的高级规则与指导原则，并通过实际耗时来评估性能。
随着经验积累，你将深入了解内部运行机制，并开始通过测量 CPU 周期和 I/O 操作等指标进行精准优化。
你想想，要是让姚明去住幼儿园的小床，他能睡得舒服吗？你的数据也是同理。
表结构就是数据的家，设计得好，数据住得舒坦，查询速度自然起飞。
划重点：更小的数据类型 → 更少的磁盘空间 → 更多数据能塞进内存 → 更少的 I/O → 飞一样的速度。这叫因果律武器。
没有绝对的好坏，只有适不适合。在需要极致查询速度时，适度冗余，是智慧的体现。
数据库设计是性能优化的基石。
合理的数据模型可以减少数据冗余、提高查询效率、确保数据一致性。
根据 MySQL 官方文档，优化的数据库设计需要平衡范式化与反范式化。
范式化设计遵循数据库设计的规范形式，通常到第三范式（3NF）：
CREATETABLEusers ( user_id INT PRIMARY KEY, username VARCHAR(50), email VARCHAR(100));
CREATETABLE orders ( order_id INT PRIMARY KEY, user_id INT, order_date DATE,FOREIGNKEY (user_id) REFERENCESusers(user_id));
CREATETABLE order_items ( item_id INT PRIMARY KEY, order_id INT, product_id INT, quantity INT,FOREIGNKEY (order_id) REFERENCES orders(order_id));
范式化的优势在于：
数据冗余最小化，更新操作只需要修改一处
数据一致性高，减少数据异常
存储空间效率高
然而，过度范式化会导致查询需要频繁的 JOIN 操作，影响查询性能。
特别是在需要跨多个表检索数据时，JOIN 操作可能成为性能瓶颈。
当读操作远多于写操作时，适当反范式化可以显著提升查询性能：
CREATETABLE orders_denormalized ( order_id INT PRIMARY KEY, user_id INT, username VARCHAR(50), email VARCHAR(100), order_date DATE, total_amount DECIMAL(10, 2));
CREATETABLE products ( product_id INT PRIMARY KEY, product_name VARCHAR(100), price DECIMAL(10, 2), stock_quantity INT, total_sold INTDEFAULT0);
以下流程图展示了数据库设计的决策过程，帮助你平衡范式化与反范式化：
没有索引的查询，就像在图书馆里找一本没编号的书——只能“全表扫描”，一本一本地翻。
索引就是书的目录，而且是超级智能的B+Tree 目录。
索引用得好，下班回家早；索引用不好，DBA 两行泪。官方第 10.3 章是索引的“百科全书”。
索引是提高查询性能的关键，但不恰当的索引反而会降低性能。MySQL 9.5 引入了新的索引类型和优化技术。
CREATEINDEX idx_order_date ON orders(order_date);
CREATEINDEX idx_hash_user ONusers(user_id) USINGHASH;
CREATE FULLTEXT INDEX idx_product_desc ON products(description);
CREATE SPATIAL INDEX idx_location ON locations(coordinates);
CREATEINDEX idx_user_status_date ON orders(user_id, status, order_date);
CREATEINDEX idx_email_prefix ONusers(email(20));
CREATEINDEX idx_lower_username ONusers((LOWER(username)));
B+Tree 索引是如何工作的？ 笔者给你画个“武功秘籍”：
CREATEINDEX idx_user_status ON orders(user_id, status);
CREATEINDEX idx_covering ON orders(order_id, user_id, order_date, total_amount);SELECT order_id, user_id, order_dateFROM ordersWHERE user_id = 100AND order_date > '2024-01-01';
CREATEINDEX idx_a ON table1(a); CREATEINDEX idx_a_b ON table1(a, b);
SELECT object_schema, object_name, index_name, count_star, count_read, count_fetchFROM performance_schema.table_io_waits_summary_by_index_usageWHERE index_name ISNOTNULLORDERBY count_star DESC;
SELECT * FROM sys.schema_unused_indexes;
有几个点总结下：
1）最左前缀原则
你建了一个复合索引 (last_name, first_name)。这意味著：
WHERE last_name = '码'索引有效
WHERE last_name = '码' AND first_name = '哥' 索引有效
WHERE first_name = '哥'（索引失效！）
2）别在索引列上“搞计算”
3）高选择性原则
别在“性别”这种低区分度的列上建索引。它就像问“你是中国人吗？”，大部分都是，问了也白问。要在“身份证号”这种高区分度的列上建。
当你怀疑某个索引是多余的，但又不敢删，怕删了引发线上事故怎么办？
在 MySQL 8.0+，你可以让它“隐身”：
ALTER TABLE user ADD INDEX idx_email (email); ALTER TABLE user ALTER INDEX idx_email INVISIBLE;
索引还在，但优化器查询时完全无视它。观察一段时间，如果业务无恙，就可以放心DROP INDEX了。
这是线上索引管理的安全气囊！
JOIN 操作是数据库查询中最常见的性能瓶颈之一：
SELECT * FROM orders oJOINusers u ON o.user_id = u.user_id;
CREATEINDEX idx_user_id ON orders(user_id);CREATEINDEX idx_user_id ONusers(user_id);
SELECT * FROM large_table lJOIN small_table s ON l.key = s.key;
SELECTSTRAIGHT_JOIN s.*, l.*FROM small_table sJOIN large_table l ON s.key = l.key;
SELECT * FROM table1, table2 WHERE table1.id = table2.table1_id;
CREATETEMPORARYTABLE filtered_ordersSELECT order_id, user_idFROM ordersWHERE order_date > '2024-01-01';
SELECT fo.*, u.usernameFROM filtered_orders foJOINusers u ON fo.user_id = u.user_id;
你可能会遇到这种诡异情况：ORDER BY create_time DESC明明有索引，但还是慢。
因为传统索引是升序的，反向扫描效率低。
现在可以创建真正的降序索引了：
CREATE INDEX idx_time_desc ON article (create_time DESC);
这样，你的最新文章查询就能直接顺着索引快速返回，特别适合新闻流、朋友圈时间线这种场景。
常见优化技巧如下：
SELECT * FROM orders ORDERBY order_date DESC;
CREATEINDEX idx_order_date_desc ON orders(order_date DESC);
SELECT * FROM orders ORDERBY order_date LIMIT100;
SELECT order_id, order_date, total_amountFROM ordersORDERBY order_dateLIMIT100;
SELECT user_id, COUNT(*)FROM ordersGROUPBY user_id;
CREATEINDEX idx_user_id ON orders(user_id);
SELECT o.user_id,COUNT(*) as order_count,SUM(oi.quantity) as total_quantityFROM orders oJOIN order_items oi ON o.order_id = oi.order_idGROUPBY o.user_id;
WITH order_summary AS (SELECT o.order_id, o.user_id, SUM(oi.quantity) as order_quantityFROM orders oJOIN order_items oi ON o.order_id = oi.order_idGROUPBY o.order_id, o.user_id)SELECT user_id,COUNT(*) as order_count,SUM(order_quantity) as total_quantityFROM order_summaryGROUPBY user_id;
大数据量的分页查询是常见性能问题：
SELECT * FROM orders ORDERBY order_date LIMIT100000, 20;
SELECT * FROM ordersWHERE order_date > '2024-01-01'ORDERBY order_dateLIMIT20;SELECT * FROM ordersWHERE order_date > '最后一行日期'ORDERBY order_dateLIMIT20;
SELECT o.*FROM orders oJOIN (SELECT order_id
FROM ordersWHERE order_date > '2024-01-01'ORDERBY order_dateLIMIT100000, 20) tmp ON o.order_id = tmp.order_id;
SELECT * FROM orders PARTITION (p2024_q1)ORDERBY order_dateLIMIT100000, 20;
索引失效是慢查询的主要原因之一，开发中一定要避免以下 10 种场景：
1）使用 SELECT ：查询所有字段，无法使用覆盖索引，必须回表查询，同时增加数据传输量；
2）索引列使用函数 / 运算：比如WHERE DATE(create_time) = '2025-01-01'、WHERE age+1=20，MySQL 无法使用索引；
3）隐式类型转换：比如索引字段是 INT 类型，查询时用字符串WHERE age='20'，MySQL 会进行类型转换，索引失效；
4）使用 LIKE % xxx：左模糊查询WHERE name like '%张三'会导致索引失效，右模糊WHERE name like '张三%'索引生效；
5）使用 OR 连接条件：OR 两边的字段如果有一个没有索引，整个查询的索引都会失效；
6）使用 NOT IN/NOT EXISTS：这两个操作会导致 MySQL 放弃索引，选择全表扫描；
7）联合索引违反最左匹配原则：如上文所述，跳过左侧字段、范围查询在中间都会导致索引失效；
8）数据量太小：表中数据量过少时，MySQL 优化器会认为全表扫描比使用索引更快，主动放弃索引；
9）索引列有 NULL 值：MySQL 对 NULL 值的处理特殊，若索引列大量为 NULL，索引效率会大幅降低，建议设置默认值；
10）过度索引：表中索引过多，MySQL 优化器在选择索引时会花费大量时间，甚至选择错误的索引。
任何优化后，都要用EXPLAIN看看 SQL 的“体检报告”。关注type字段（最好达到ref或range）、key字段（是否用了你想用的索引）、rows字段（扫描行数越少越好）。
很多人以为优化就是调参数，大错特错！80%的性能问题源于糟糕的 SQL。
MySQL 9.5 提供了强大的查询分析工具，帮助我们识别和解决性能瓶颈。
任何不跑EXPLAIN的优化都是耍流氓！这玩意儿就是 SQL 的“体检报告”。
EXPLAINFORMAT=JSONSELECT o.order_id, o.order_date, u.username, SUM(oi.quantity * p.price) as total_amountFROM orders oJOINusers u ON o.user_id = u.user_idJOIN order_items oi ON o.order_id = oi.order_idJOIN products p ON oi.product_id = p.product_idWHERE o.order_date >= '2024-01-01'GROUPBY o.order_idHAVING total_amount > 1000ORDERBY o.order_date DESCLIMIT10;
你得会看这几个关键指标：
骚操作：用 EXPLAIN FORMAT=JSON获取更详尽的“深度体检报告”，里面连成本（cost）都算给你看！
很多靓仔写子查询是这样的：
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);
看着没问题？但 MySQL 可能把它变成一个可怕的“相关子查询”，对外层每一行都执行一次子查询，慢到怀疑人生。
官方推荐救赎方案：使用 JOIN 或 EXISTS
SELECT u.* FROM users uWHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 100);SELECT DISTINCT u.*
FROM users uINNER JOIN orders o ON u.id = o.user_idWHERE o.amount > 100;
以下是一些常见技巧。
SELECT * FROMusersWHERE user_id = 100;
SELECT user_id, username, email FROMusersWHERE user_id = 100;
SELECT * FROM ordersWHERE user_id IN (SELECT user_id FROMusersWHEREstatus = 'active');
SELECT * FROM orders oWHEREEXISTS (SELECT1FROMusers u WHERE u.user_id = o.user_id AND u.status = 'active');
SELECT * FROM orders WHERE order_date > '2024-01-01';
SELECT * FROM orders WHERE order_date > '2024-01-01'LIMIT100;
SELECT * FROM orders WHEREYEAR(order_date) = 2024;
SELECT * FROM orders WHERE order_date >= '2024-01-01'AND order_date < '2025-01-01';
作为默认引擎，InnoDB 的调优是重中之重。
这是 InnoDB 的灵魂所在。innodb_buffer_pool_size必须调大，通常是**系统总内存的 70%-80%**。
一个足够大的 BP 能让热数据常驻内存，告别磁盘 I/O。
但怎么知道 BP 是否够用？看状态：
SHOW STATUS LIKE ‘innodb_buffer_pool_read%’;
如果 Innodb_buffer_pool_reads（从磁盘读取的页数）远大于 Innodb_buffer_pool_read_requests（总的读请求），说明 BP 太小了，很多数据不得不去磁盘读。
Redo Log 是 InnoDB 的“日记本”，为保证事务持久性而存在。innodb_flush_log_at_trx_commit这个参数是性能和安全的关键平衡点：
建议：如果不是强一致性要求，尝试设置为2，性能提升会非常明显。
最后给出一份常用的配置。
MySQL 9.5 引入了新的配置参数，优化默认值：
innodb_buffer_pool_size = 16Ginnodb_buffer_pool_instances = 8
innodb_log_file_size = 2G innodb_log_buffer_size = 64M
max_connections = 500 thread_cache_size = 100
tmp_table_size = 256M max_heap_table_size = 256M sort_buffer_size = 4M
innodb_flush_method = O_DIRECT innodb_io_capacity = 2000 innodb_io_capacity_max = 4000
transaction_isolation = READ-COMMITTED innodb_lock_wait_timeout = 50
MySQL 9.5 增强了监控和诊断功能，优化不是玄学，要靠数据说话。
SELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT/1000000000
as total_sec, AVG_TIMER_WAIT/1000000000as avg_sec, MAX_TIMER_WAIT/1000000000as max_secFROM performance_schema.events_statements_summary_by_digestORDERBY SUM_TIMER_WAIT DESCLIMIT10;
SELECT * FROM sys.innodb_lock_waits;
SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_table_statistics;
SETGLOBAL slow_query_log = ON;SETGLOBAL long_query_time = 1; # 超过1秒的查询
# 使用mysqldumpslow工具mysqldumpslow -s t /var/log/mysql/mysql-slow.log
# 使用pt-query-digest工具pt-query-digest /var/log/mysql/mysql-slow.log
SELECT * FROM information_schema.processlistWHERE COMMAND != 'Sleep'ORDERBYTIMEDESC;
SHOWENGINEINNODBSTATUS;
\sql\performance report
1）慢查询日志（Slow Query Log）：你的“病历本”。开启它，记录下所有执行超过long_query_time（比如 0.1 秒）的 SQL。
2）Performance Schema：MySQL 的“实时监控大屏”。它能深入监控到每个语句的执行阶段、锁等待、I/O 操作，是进阶优化的不二法门。
3）sys Schema：基于 Performance Schema 的“可视化报表库”。提供人类可读的视图，比如直接查询哪些语句全表扫描了：SELECT * FROM sys.statements_with_full_table_scans;
1）先诊断，后开药：EXPLAIN、慢查询日志、Performance Schema 是你的“听诊器”和“CT 机”。
2）SQL 为王：90%的性能问题能从 SQL 和索引层面解决或缓解。掌握哈希连接、子查询优化。
3）理解 InnoDB：调好 Buffer Pool 和 Redo Log，你就掌握了 InnoDB 的命脉。
4）善用新特性：不可见索引、降序索引等是线上运维和性能提升的利器。
5）硬件是保障：SSD 是解决 I/O 瓶颈的银弹，内存是缓解 I/O 压力的黄金。
6）数据驱动决策：建立监控基线，任何优化前后都要对比数据，确保真的有效。
优化是一条永无止境的路，但跟着 MySQL 官方文档和码哥的这份指南，你一定能从“新手村”勇者，成长为独当一面的“性能调优大师”！
