你好我是《Redis 高手心法》畅销书作者码哥可以叫我靓仔。今天码哥带你深入 MySQL 的内心世界扒一扒这个每天被你“增删改查”的老伙计到底怎么才能跑得比香港记者还快咱都是实干派不整那些虚头巴脑的理论。直接上硬菜告诉你为啥你的 SQL 写得跟树懒一样慢以及怎么给它装上火箭推进器。“友情提示码哥宣言只讲官网最硬核的实战不搞理论废话。要是看完没收获码哥我当场……给你再讲一遍想象一下这个场景月黑风高夜你和对象如果有的话正在享受甜蜜时光。突然报警短信“哔哔哔”响个不停——线上服务挂了一大片你火急火燎地打开监控一看CPU 100%数据库连接池爆满无数请求在超时的边缘疯狂试探。你脑海中瞬间闪过三个字慢查询别慌码哥教你 MySQL 性能优化的“太极心法”化 I/O 于无形锁争用于无声。下面这套“组合拳”请你接好。数据库性能取决于数据库层面的多个因素例如表结构、查询语句和配置设置。这些软件层面的设计最终会转化为硬件层面的 CPU 和 I/O 操作必须尽可能减少这些操作并提升其效率。在进行数据库性能优化时首先需要掌握软件层面的高级规则与指导原则并通过实际耗时来评估性能。随着经验积累你将深入了解内部运行机制并开始通过测量 CPU 周期和 I/O 操作等指标进行精准优化。表结构设计你想想要是让姚明去住幼儿园的小床他能睡得舒服吗你的数据也是同理。表结构就是数据的家设计得好数据住得舒坦查询速度自然起飞。数据类型能小就别大能瘦就别胖TINYINT 是你的好朋友存状态码0/1用TINYINT别用INT省下来的每一字节都是为未来节省一次磁盘 I/O。用 DATE 代替 DATETIME如果你不关心时分秒就别让它们占着茅坑不拉屎。避免“Null 的诅咒”NULL是个磨人的小妖精它会让索引、计算都变得更复杂。尽量给列设置个默认值比如空字符串0。字符串类型优化IP 地址可以存储为无符号整数UUID 可以存储为 BINARY(16)固定长度字符串使用 CHAR可变长度字符串使用 VARCHAR 并设置合适长度。时间类型优化只需要日期时使用 DATE 类型需要自动更新时间戳时使用 TIMESTAMP。码哥划重点更小的数据类型 → 更少的磁盘空间 → 更多数据能塞进内存 → 更少的 I/O → 飞一样的速度。这叫因果律武器.范式与反范式范式化高范式好比夫妻财政 AA 制账目清晰更新自由写操作快。适合写多读少的场景。反范式化低范式好比夫妻钱放一个兜用起来方便不用来回要读操作快避免 JOIN。适合读多写少的场景。没有绝对的好坏只有适不适合。在需要极致查询速度时适度冗余是智慧的体现。数据库设计是性能优化的基石。合理的数据模型可以减少数据冗余、提高查询效率、确保数据一致性。范式化根据 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-- 反范式化预计算字段 );以下流程图展示了数据库设计的决策过程帮助你平衡范式化与反范式化索引没有索引的查询就像在图书馆里找一本没编号的书——只能“全表扫描”一本一本地翻。索引就是书的目录而且是超级智能的BTree 目录。索引用得好下班回家早索引用不好DBA 两行泪。官方第 10.3 章是索引的“百科全书”。索引是提高查询性能的关键但不恰当的索引反而会降低性能。MySQL 9.5 引入了新的索引类型和优化技术。-- 创建不同类型的索引 -- 1. B-Tree索引默认适用于全值匹配、范围查询、排序 CREATEINDEX idx_order_date ON orders(order_date); -- 2. 哈希索引适用于等值查询Memory引擎支持 CREATEINDEX idx_hash_user ONusers(user_id) USINGHASH; -- 3. 全文索引适用于文本搜索 CREATE FULLTEXT INDEX idx_product_desc ON products(description); -- 4. 空间索引适用于地理空间数据 CREATE SPATIAL INDEX idx_location ON locations(coordinates); -- 5. 多列索引复合索引 CREATEINDEX idx_user_status_date ON orders(user_id, status, order_date); -- 6. 前缀索引为字符串列前N个字符创建索引 CREATEINDEX idx_email_prefix ONusers(email(20)); -- 7. 函数索引MySQL 8.0为表达式结果创建索引 CREATEINDEX idx_lower_username ONusers((LOWER(username)));BTree 索引是如何工作的码哥给你画个“武功秘籍”索引设计最佳实践-- 1. 选择性高的列放在索引前面 -- user_id的选择性高于status因此user_id在前 CREATEINDEX idx_user_status ON orders(user_id, status); -- 2. 考虑覆盖索引 -- 索引包含查询所需的所有列避免回表 CREATEINDEX idx_covering ON orders(order_id, user_id, order_date, total_amount); -- 查询可以使用覆盖索引 SELECT order_id, user_id, order_date FROM orders WHERE user_id 100AND order_date 2024-01-01; -- 3. 避免重复索引 -- 以下索引是冗余的因为idx_a_b可以用于a列的查询 CREATEINDEX idx_a ON table1(a); -- 冗余索引 CREATEINDEX idx_a_b ON table1(a, b); -- 4. 定期分析索引使用情况 SELECT object_schema, object_name, index_name, count_star, count_read, count_fetch FROM performance_schema.table_io_waits_summary_by_index_usage WHERE index_name ISNOTNULL ORDERBY count_star DESC; -- 5. 删除未使用的索引 -- 使用sys schema查看索引使用统计 SELECT * FROM sys.schema_unused_indexes;有几个点总结下最左前缀原则。你建了一个复合索引(last_name, first_name)。这意味著WHERE last_name 码索引有效WHERE last_name 码 AND first_name 哥索引有效WHERE first_name 哥索引失效别在索引列上“搞计算”WHERE YEAR(create_time) 2023❌ 索引列被函数包裹索引卒。要写成WHERE create_time BETWEEN 2023-01-01 AND 2023-12-31✅。高选择性原则别在“性别”这种低区分度的列上建索引。它就像问“你是中国人吗”大部分都是问了也白问。要在“身份证号”这种高区分度的列上建。当你怀疑某个索引是多余的但又不敢删怕删了引发线上事故怎么办在 MySQL 8.0你可以让它“隐身”ALTER TABLE user ADD INDEX idx_email (email); -- 创建 ALTER TABLE user ALTER INDEX idx_email INVISIBLE; -- 隐身索引还在但优化器查询时完全无视它。观察一段时间如果业务无恙就可以放心DROP INDEX了。这是线上索引管理的安全气囊在这里先介绍下我根据 10 年工作经验和面试经验亲手打造的面试专栏 -》《互联网大厂面试高手心法 58 讲》专栏原价 89元现在已经涨价到 20元满 400 人立马涨价不要再错过。我向官方申请了 20 张 8 折优惠券前 20 名还能再优惠下单不要错过8 折优惠码JOIN 优化JOIN 操作是数据库查询中最常见的性能瓶颈之一-- 1. 确保JOIN列上有索引 -- 优化前没有索引的JOIN SELECT * FROM orders o JOINusers u ON o.user_id u.user_id; -- user_id没有索引 -- 优化后为JOIN列添加索引 CREATEINDEX idx_user_id ON orders(user_id); CREATEINDEX idx_user_id ONusers(user_id); -- 2. 小表驱动大表 -- 优化前大表驱动小表 SELECT * FROM large_table l JOIN small_table s ON l.key s.key; -- 优化后小表驱动大表通过STRAIGHT_JOIN提示 SELECTSTRAIGHT_JOIN s.*, l.* FROM small_table s JOIN large_table l ON s.key l.key; -- 3. 避免笛卡尔积 -- 确保所有JOIN都有连接条件 SELECT * FROM table1, table2 WHERE table1.id table2.table1_id; -- 正确 -- 4. 使用合适的JOIN类型 -- 根据数据分布选择INNER JOIN、LEFT JOIN等 -- 5. 分阶段JOIN处理大数据量 -- 第一步获取筛选后的数据 CREATETEMPORARYTABLE filtered_orders SELECT order_id, user_id FROM orders WHERE order_date 2024-01-01; -- 第二步与其他表JOIN SELECT fo.*, u.username FROM filtered_orders fo JOINusers u ON fo.user_id u.user_id;排序和分组优化你可能会遇到这种诡异情况ORDER BY create_time DESC明明有索引但还是慢。因为传统索引是升序的反向扫描效率低。、现在可以创建真正的降序索引了CREATE INDEX idx_time_desc ON article (create_time DESC);这样你的最新文章查询就能直接顺着索引快速返回特别适合新闻流、朋友圈时间线这种场景。常见优化技巧如下-- 1. 使用索引避免排序 -- 优化前没有索引支持排序 SELECT * FROM orders ORDERBY order_date DESC; -- 优化后为排序字段添加索引 CREATEINDEX idx_order_date_desc ON orders(order_date DESC); -- 2. 减少排序数据量 -- 优化前排序所有列 SELECT * FROM orders ORDERBY order_date LIMIT100; -- 优化后只排序和选择必要列 SELECT order_id, order_date, total_amount FROM orders ORDERBY order_date LIMIT100; -- 3. 优化GROUP BY查询 -- 优化前GROUP BY没有索引 SELECT user_id, COUNT(*) FROM orders GROUPBY user_id; -- 优化后为GROUP BY列添加索引 CREATEINDEX idx_user_id ON orders(user_id); -- 4. 使用派生表优化复杂分组 -- 优化前复杂GROUP BY SELECT o.user_id, COUNT(*) as order_count, SUM(oi.quantity) as total_quantity FROM orders o JOIN order_items oi ON o.order_id oi.order_id GROUPBY o.user_id; -- 优化后分阶段处理 WITH order_summary AS ( SELECT o.order_id, o.user_id, SUM(oi.quantity) as order_quantity FROM orders o JOIN order_items oi ON o.order_id oi.order_id GROUPBY o.order_id, o.user_id ) SELECT user_id, COUNT(*) as order_count, SUM(order_quantity) as total_quantity FROM order_summary GROUPBY user_id;分页优化大数据量的分页查询是常见性能问题-- 1. 传统分页的性能问题 -- 偏移量越大性能越差 SELECT * FROM orders ORDERBY order_date LIMIT100000, 20; -- 2. 基于游标的分页Keyset Pagination -- 记住最后一行的值而不是偏移量 SELECT * FROM orders WHERE order_date 2024-01-01 ORDERBY order_date LIMIT20; -- 下一页 SELECT * FROM orders WHERE order_date 最后一行日期 ORDERBY order_date LIMIT20; -- 3. 使用覆盖索引优化 -- 先通过覆盖索引获取ID再获取数据 SELECT o.* FROM orders o JOIN ( SELECT order_id FROM orders WHERE order_date 2024-01-01 ORDERBY order_date LIMIT100000, 20 ) tmp ON o.order_id tmp.order_id; -- 4. 分区表分页优化 -- 使用分区减少扫描范围 SELECT * FROM orders PARTITION (p2024_q1) ORDERBY order_date LIMIT100000, 20;索引失效的 10 大场景索引失效是慢查询的主要原因之一开发中一定要避免以下 10 种场景** 使用 SELECT ***查询所有字段无法使用覆盖索引必须回表查询同时增加数据传输量索引列使用函数 / 运算比如WHERE DATE(create_time) 2025-01-01、WHERE age120MySQL 无法使用索引隐式类型转换比如索引字段是 INT 类型查询时用字符串WHERE age20MySQL 会进行类型转换索引失效使用 LIKE % xxx左模糊查询WHERE name like %张三会导致索引失效右模糊WHERE name like 张三%索引生效使用 OR 连接条件OR 两边的字段如果有一个没有索引整个查询的索引都会失效使用 NOT IN/NOT EXISTS这两个操作会导致 MySQL 放弃索引选择全表扫描联合索引违反最左匹配原则如上文所述跳过左侧字段、范围查询在中间都会导致索引失效数据量太小表中数据量过少时MySQL 优化器会认为全表扫描比使用索引更快主动放弃索引索引列有 NULL 值MySQL 对 NULL 值的处理特殊若索引列大量为 NULL索引效率会大幅降低建议设置默认值过度索引表中索引过多MySQL 优化器在选择索引时会花费大量时间甚至选择错误的索引。查询分析与优化策略任何优化后都要用EXPLAIN看看 SQL 的“体检报告”。关注type字段最好达到ref或range、key字段是否用了你想用的索引、rows字段扫描行数越少越好。很多人以为优化就是调参数大错特错80%的性能问题源于糟糕的 SQL。MySQL 9.5 提供了强大的查询分析工具帮助我们识别和解决性能瓶颈。EXPLAIN 命令深度解析任何不跑EXPLAIN的优化都是耍流氓这玩意儿就是 SQL 的“体检报告”。-- 使用EXPLAIN分析查询 EXPLAINFORMATJSON SELECT o.order_id, o.order_date, u.username, SUM(oi.quantity * p.price) as total_amount FROM orders o JOINusers u ON o.user_id u.user_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 GROUPBY o.order_id HAVING total_amount 1000 ORDERBY o.order_date DESC LIMIT10;你得会看这几个关键指标id查询标识符相同 id 表示是同一查询块.select_type查询类型SIMPLE、PRIMARY、SUBQUERY 等table访问的表partitions匹配的分区type 列这是“访问类型”从好到坏大概是systemconsteq_refrefrangeindexALL。如果看到ALL全表扫描恭喜你索引大概率没用好。key 列告诉你实际用了哪个索引。如果为NULL呵呵索引失踪了。possible_keys可能使用的索引key_len使用的索引长度ref与索引比较的列rows 列表示 MySQL 认为它需要检查多少行。这个数字越大查询越累。filtered按条件过滤的行百分比Extra 列这里的信息极度重要比如出现Using filesort需要额外的排序或Using temporary需要创建临时表都是性能杀手。码哥骚操作用EXPLAIN FORMATJSON获取更详尽的“深度体检报告”里面连成本cost都算给你看子查询的“陷阱”与“救赎”很多靓仔写子查询是这样的SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount 100);看着没问题但 MySQL 可能把它变成一个可怕的“相关子查询”对外层每一行都执行一次子查询慢到怀疑人生。官方推荐救赎方案使用 JOIN 或EXISTSSELECT u.* FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id u.id AND o.amount 100); -- 或者更直接的JOIN SELECT DISTINCT u.* FROM users u INNER JOIN orders o ON u.id o.user_id WHERE o.amount 100;以下是一些常见技巧。-- 1. 避免使用SELECT *只选择需要的列 -- 优化前 SELECT * FROMusersWHERE user_id 100; -- 优化后 SELECT user_id, username, email FROMusersWHERE user_id 100; -- 2. 使用EXISTS代替IN当子查询返回大量数据时 -- 优化前 SELECT * FROM orders WHERE user_id IN (SELECT user_id FROMusersWHEREstatus active); -- 优化后 SELECT * FROM orders o WHEREEXISTS (SELECT1FROMusers u WHERE u.user_id o.user_id AND u.status active); -- 3. 将复杂查询拆分为多个简单查询 -- 优化前一个复杂的多表JOIN查询 -- 优化后拆分为多个查询在应用层组合数据 -- 4. 合理使用LIMIT -- 优化前不必要的大量数据检索 SELECT * FROM orders WHERE order_date 2024-01-01; -- 优化后添加LIMIT限制 SELECT * FROM orders WHERE order_date 2024-01-01LIMIT100; -- 5. 避免在WHERE子句中对列进行函数操作 -- 优化前 SELECT * FROM orders WHEREYEAR(order_date) 2024; -- 优化后 SELECT * FROM orders WHERE order_date 2024-01-01AND order_date 2025-01-01;MySQL 9.5 系统级优化作为默认引擎InnoDB 的调优是重中之重。缓冲池Buffer Pool这是 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这个参数是性能和安全的关键平衡点1默认每次提交都刷盘。最安全但最慢。适用于金融、交易等对数据一致性要求极高的场景。2每次提交只写到操作系统缓存每秒刷一次盘。性能好崩溃可能丢失 1 秒数据。适用于大多数业务场景。0每秒写和刷盘。性能最好但崩溃可能丢失最多 1 秒数据。适用于可容忍少量数据丢失的场景如日志收集。码哥建议如果不是强一致性要求尝试设置为2性能提升会非常明显。最后给出一份常用的配置。MySQL 9.5 引入了新的配置参数优化默认值# InnoDB缓冲池配置通常设置为系统内存的70-80% innodb_buffer_pool_size 16G innodb_buffer_pool_instances 8 # 减少争用通常设为CPU核心数 # 日志文件配置 innodb_log_file_size 2G # 更大的日志文件减少检查点 innodb_log_buffer_size 64M # 日志缓冲区大小 # 连接管理 max_connections 500 # 根据应用需求调整 thread_cache_size 100 # 线程缓存大小 # 查询缓存MySQL 8.0已移除注意替代方案 # 使用应用层缓存或ProxySQL查询缓存 # 临时表和排序优化 tmp_table_size 256M # 内存临时表最大大小 max_heap_table_size 256M # 内存表最大大小 sort_buffer_size 4M # 排序缓冲区大小 # InnoDB I/O优化 innodb_flush_method O_DIRECT # Linux系统推荐 innodb_io_capacity 2000 # 根据磁盘性能调整 innodb_io_capacity_max 4000 # 最大I/O容量 # 事务隔离与锁 transaction_isolation READ-COMMITTED # 大多数应用推荐 innodb_lock_wait_timeout 50 # 锁等待超时时间监控与度量——没有度量就没有优化MySQL 9.5 增强了监控和诊断功能优化不是玄学要靠数据说话。-- 1. 使用Performance Schema监控性能 -- 查看最耗时的SQL语句 SELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT/1000000000as total_sec, AVG_TIMER_WAIT/1000000000as avg_sec, MAX_TIMER_WAIT/1000000000as max_sec FROM performance_schema.events_statements_summary_by_digest ORDERBY SUM_TIMER_WAIT DESC LIMIT10; -- 2. 使用sys schema快速诊断 -- 查看锁等待情况 SELECT * FROM sys.innodb_lock_waits; -- 查看未使用索引 SELECT * FROM sys.schema_unused_indexes; -- 查看表统计信息 SELECT * FROM sys.schema_table_statistics; -- 3. 使用慢查询日志 -- 启用慢查询日志 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 -- 4. 实时监控 -- 查看当前连接和执行语句 SELECT * FROM information_schema.processlist WHERE COMMAND ! Sleep ORDERBYTIMEDESC; -- 查看InnoDB状态 SHOWENGINEINNODBSTATUS; -- 5. 使用MySQL Shell性能报告 -- MySQL Shell提供了高级诊断功能 \sql \performance report慢查询日志Slow Query Log你的“病历本”。开启它记录下所有执行超过long_query_time比如 0.1 秒的 SQL。Performance SchemaMySQL 的“实时监控大屏”。它能深入监控到每个语句的执行阶段、锁等待、I/O 操作是进阶优化的不二法门。sys Schema基于 Performance Schema 的“可视化报表库”。提供人类可读的视图比如直接查询哪些语句全表扫描了SELECT * FROM sys.statements_with_full_table_scans;总结MySQL 优化心法秘籍先诊断后开药EXPLAIN、慢查询日志、Performance Schema 是你的“听诊器”和“CT 机”。SQL 为王90%的性能问题能从 SQL 和索引层面解决或缓解。掌握哈希连接、子查询优化。理解 InnoDB调好 Buffer Pool 和 Redo Log你就掌握了 InnoDB 的命脉。善用新特性不可见索引、降序索引等是线上运维和性能提升的利器。硬件是保障SSD 是解决 I/O 瓶颈的银弹内存是缓解 I/O 压力的黄金。数据驱动决策建立监控基线任何优化前后都要对比数据确保真的有效。优化是一条永无止境的路但跟着 MySQL 官方文档和码哥的这份指南你一定能从“新手村”勇者成长为独当一面的“性能调优大师”好了觉得有用记得给码哥点赞、在看、分享三连下次再见靓仔