前言很多小伙伴在工作中可能只把MySQL当作一个简单的“数据存储箱”用了它80%的基础功能却不知道它还有另外20%的、能解决90%复杂问题的“高级用法”。今天我不谈基础的增删改查就和你深入聊聊在实际高性能、高并发、大数据量的场景下那些真正能让你和团队生产力倍增、性能飞升的10种MySQL高级实战技巧。希望对你会有所帮助。01 执行计划在优化任何查询之前读懂EXPLAIN的输出是你的第一门必修课。它就像SQL的“X光片”能告诉你MySQL究竟打算如何执行你的查询瓶颈在哪里。核心用法与实战执行EXPLAIN后你需要重点关注以下几个关键字段type访问类型从最优到最差大致是systemconsteq_refrefrangeindexALL。看到ALL全表扫描就要警惕了。key实际使用的索引。如果为NULL说明没用上索引。rowsMySQL预估要扫描的行数。这个数字越接近实际需要的数据行数越好。Extra包含非常丰富的信息例如Using filesort需要额外排序、Using temporary使用了临时表这通常是性能杀手。-- 一个需要优化的查询示例 EXPLAIN SELECT*FROMorders WHEREuser_id 10086 ANDcreate_timeBETWEEN2024-01-01AND2024-01-31 ORDERBYamountDESC;假设这个查询的type是ALLkey是NULL。这意味着它在orders表上进行了全表扫描性能极差。优化方法通常是创建一个复合索引-- 创建覆盖了WHERE和ORDER BY的复合索引 CREATEINDEXidx_user_time_amountONorders(user_id, create_time, amount); -- 再次使用EXPLAIN你会看到type变成了rangekey显示了新索引性能天差地别。深度剖析EXPLAIN是基于表的统计信息来估算成本的。如果表数据变化很大而统计信息未更新优化器可能会选错索引。这时可以用ANALYZE TABLE table_name;来手动更新统计信息。有些小伙伴在工作中写的SQL本身不复杂但执行很慢第一步就应该祭出EXPLAIN。欢迎加入小哈的星球你将获得:专属的项目实战多个项目 / 1v1 提问 /Java 学习路线 /学习打卡 / 每月赠书 / 社群讨论新项目《Spring AI 项目实战》正在更新中..., 基于 Spring AI Spring Boot 3.x JDK 21;《从零手撸前后端分离博客项目全栈开发》2期已完结,演示链接http://116.62.199.48/;截止目前累计输出 100w 字讲解图 4013 张还在持续爆肝中..后续还会上新更多项目目标是将 Java 领域典型的项目都整一波如秒杀系统, 在线商城, IM 即时通讯Spring Cloud Alibaba 等等戳我加入学习解锁全部项目已有4200小伙伴加入02 高级索引策略索引是性能的基石但错误的索引比没有索引更糟糕。高级索引策略覆盖索引Covering Index如果索引包含了查询需要的所有字段引擎就无需回表查询数据行速度极快。-- 假设常用查询是获取用户的姓名和邮箱 SELECTname, emailFROMusersWHEREage 20; -- 为这个查询设计覆盖索引 CREATEINDEXidx_age_name_emailONusers(age,name, email); -- age用于查询name和email本身就在索引页中无需查找数据行。索引下推Index Condition Pushdown ICP这是MySQL 5.6引入的重大优化。对于复合索引(a, b)查询WHERE a ? AND b LIKE ‘%xxx’。在旧版本中即使a命中了索引引擎也会将所有a?的记录回表再去过滤b。而ICP允许将b LIKE ‘%xxx’这个条件下推到存储引擎层在索引扫描时就过滤大大减少回表次数。前缀索引Prefix Index对于超长文本字段如VARCHAR(500)为整个字段建索引非常臃肿。可以只对前N个字符建立索引在空间和效率间取得平衡。-- 为content字段前100个字符创建索引 CREATEINDEXidx_content_prefixONarticles (content(100)); -- 缺点是前缀索引无法用于GROUP BY和ORDER BY操作。深度剖析索引是一把双刃剑加速查询的同时会降低写操作INSERT/UPDATE/DELETE的速度因为索引树也需要维护。一个表上创建十几个索引是常见的设计误区。你需要定期使用SHOW INDEX FROM table_name;审查索引的基数Cardinality唯一值数量删除使用率极低的冗余索引。03 窗口函数这是MySQL 8.0带来的“神兵利器”用于进行跨行计算完美解决复杂排名、累加、移动平均等问题。核心场景与语法-- 经典场景计算每个部门内员工的薪水排名 SELECT name, department, salary, RANK()OVER(PARTITIONBYdepartmentORDERBYsalaryDESC)asdept_salary_rank, -- 同时计算公司整体排名 RANK()OVER(ORDERBYsalaryDESC)ascompany_rank, -- 计算部门内薪水累计占比 SUM(salary)OVER(PARTITIONBYdepartment)asdept_total, salary /SUM(salary)OVER(PARTITIONBYdepartment)assalary_ratio FROMemployees;PARTITION BY类似于GROUP BY但不会将行合并而是定义窗口范围。ORDER BY决定窗口内的排序。深度剖析在MySQL 8.0之前要实现上述查询你需要写复杂的自连接或效率极低的子查询。窗口函数在数据库内部进行了深度优化性能提升可达几个数量级。它特别适用于分析报表、实时排行榜、计算同比环比等OLAP型场景。04 通用表表达式CTECTEWITH子句是另一个MySQL 8.0的重要特性它允许你定义临时的命名结果集在后续查询中像普通表一样引用。优势与示例提升可读性将复杂查询分解成逻辑清晰的步骤。支持递归这是CTE的杀手级功能可以轻松查询树形或图状数据。-- 示例1分解复杂查询非递归 WITH high_value_ordersAS(-- 找出高价值订单 SELECTuser_id,SUM(amount)astotal_spent FROMorders WHEREstatuscompleted GROUPBYuser_id HAVINGtotal_spent 10000 ), active_usersAS(-- 找出活跃用户 SELECTDISTINCTuser_id FROMuser_logs WHERElast_active_date DATE_SUB(NOW(),INTERVAL30DAY) ) -- 最终查询既是高价值又是活跃的用户 SELECTu.name, u.email, h.total_spent FROMusersu JOINhigh_value_orders hONu.id h.user_id JOINactive_users aONu.id a.user_id; -- 示例2递归CTE查询部门树 WITHRECURSIVEdepartment_treeAS( -- 锚点找到根部门 SELECTid,name, parent_id,1aslevel FROMdepartments WHEREparent_idISNULL UNIONALL -- 递归成员连接父部门和子部门 SELECTd.id, d.name, d.parent_id, dt.level 1 FROMdepartments d INNERJOINdepartment_tree dtONd.parent_id dt.id ) SELECT*FROMdepartment_treeORDERBYlevel,id;深度剖析递归CTE极大地简化了组织架构、分类目录、评论嵌套等层次数据的查询。在旧版本中这通常需要在应用层进行多次查询或在数据库中使用存储过程递归CTE在数据库内核完成遍历效率更高。05 JSON类型与函数MySQL 5.7原生支持JSON数据类型让你能够在关系型数据库中灵活地存储和查询半结构化数据这在处理动态字段、配置信息或第三方API返回的数据时非常有用。核心操作-- 1. 创建包含JSON列的表 CREATETABLEproducts ( idINTPRIMARYKEY, nameVARCHAR(100), attributesJSONCOMMENT存储颜色、尺寸等动态属性 ); -- 2. 插入JSON数据 INSERTINTOproductsVALUES(1,T-Shirt,{color: red, size: [M, L], tags: [casual, cotton]}); -- 3. 查询 (使用 - 和 - 操作符) -- - 返回JSON类型 - 返回纯文本字符串 SELECT name, attributes-$.colorascolor,-- 提取color值 attributes-$.sizeassize_array-- 提取size数组仍为JSON FROMproducts WHEREattributes-$.colorred ORJSON_CONTAINS(attributes-$.tags,cotton); -- 4. 更新部分JSON UPDATEproducts SETattributes JSON_SET(attributes,$.color,blue,$.new_field,value) WHEREid1;深度剖析JSON列同样可以建立索引通过函数索引加速查询。CREATEINDEXidx_colorONproducts( (attributes-$.color) );这允许你在保持灵活性的同时不丧失对关键字段的查询性能。它完美填补了关系模型在应对多变业务需求时的短板。06 分区表Partitioning当单表数据量巨大如数亿行时分区可以将一张大表在物理上分割为多个更小、更易管理的部分而逻辑上仍是一张表。分区策略与示例-- 按时间范围(RANGE)分区非常适合日志、订单表 CREATETABLEsales ( idINT, sale_dateDATE, amountDECIMAL(10,2) ) PARTITIONBYRANGECOLUMNS(sale_date) ( PARTITIONp2023q1VALUESLESSTHAN(2023-04-01), PARTITIONp2023q2VALUESLESSTHAN(2023-07-01), PARTITIONp2023q3VALUESLESSTHAN(2023-10-01), PARTITIONp2023q4VALUESLESSTHAN(2024-01-01), PARTITIONp_futureVALUESLESSTHANMAXVALUE ); -- 查询时优化器会自动定位到特定分区分区裁剪Partition Pruning EXPLAINSELECT*FROMsalesWHEREsale_date 2023-05-15; -- 你会看到partitions: p2023q2意味着只扫描了2023年Q2的分区。除了RANGE还有LIST按列表值、HASH按哈希值均匀分布等分区方式。深度剖析分区的核心优势在于维护性和查询性能。你可以快速删除或归档整个旧分区ALTER TABLE sales DROP PARTITION p2023q1;这比DELETE操作快得多且不产生碎片。对于按分区键过滤的查询性能提升显著。但注意分区键选择不当或跨分区查询性能可能反而下降。07 连接JOIN与子查询多表关联是业务常态但写得不好就是性能灾难。高级技巧控制连接顺序MySQL优化器通常会选择它认为最佳的顺序但你可以在复杂场景下用STRAIGHT_JOIN强制指定顺序。SELECT... FROMsmall_table s STRAIGHT_JOINlarge_table lONs.id l.s_id;-- 强制先查小表利用衍生表Derived Table下推条件有时将子查询或过滤条件提前能极大地减少中间结果集。-- 优化前先连接两个大表再过滤 SELECT*FROMAJOINBONA.id B.aidWHEREA.create_time ...; -- 优化后先过滤A表再连接 SELECT*FROM(SELECT*FROMAWHEREcreate_time ...) filtered_A JOINBONfiltered_A.id B.aid;**EXISTSvsIN**对于“是否存在”的查询特别是子查询结果集较大时EXISTS关联子查询通常比IN非关联子查询性能更好因为它找到第一个匹配项就会停止。深度剖析所有的JOIN优化其核心思想都是“尽早过滤减少中间数据量”。熟练使用EXPLAIN查看连接类型如eq_ref很好Using join buffer说明可能需要索引是关键。08 用户自定义变量MySQL允许你定义用户变量如rank这在一些需要跨行计算或记录中间状态的分析中非常有用。实战案例计算行间差值-- 计算每日销售额的日环比增长率 SELECT sale_date, daily_amount, -- 使用变量记录前一天的值 prev_amountasprev_day_amount, ROUND( (daily_amount - prev_amount) / prev_amount *100,2)asgrowth_rate, -- 将当前值赋给变量供下一行使用 prev_amount : daily_amount FROM daily_sales_summary, (SELECTprev_amount :0) init-- 初始化变量 ORDERBYsale_date;深度剖析用户变量提供了过程式编程的能力可以模拟窗口函数的部分功能在MySQL 8.0之前。但它不是SQL标准执行顺序有时反直觉需谨慎使用。在复杂的会话或事务中变量的生命周期和作用域也需要仔细考量。09 在线DDL与无锁变更在业务7x24小时运行的时代给大表加字段、改索引再也不能随意停服务了。MySQL 5.6提供了ALGORITHM和LOCK选项实现在线DDLOnline Data Definition。安全操作指南-- 添加一个可为空且有默认值的新列使用INPLACE算法和尽量低的锁级别 ALTERTABLEhuge_table ADDCOLUMNnew_columnVARCHAR(100)DEFAULTNOTNULL, ALGORITHMINPLACE,-- 尽量使用INPLACE原地重建避免COPY锁表复制 LOCKNONE;-- 目标不加锁或共享锁 -- 修改列类型某些情况需要COPY会锁表 ALTERTABLEhuge_table MODIFYCOLUMNold_columnBIGINT, ALGORITHMCOPY,-- 注意这里可能必须用COPY LOCKSHARED;深度剖析ALGORITHMINPLACE意味着大部分工作如重建索引在引擎内部完成允许并发DML操作。而ALGORITHMCOPY会创建新表并复制数据全程锁表。执行前务必用ALGORITHMDEFAULT先测试一下。pt-online-schema-change是Percona提供的第三方工具通过触发器实现真正的全程无锁是更稳妥的选择。10 利用生成列与函数索引生成列的值由表中其他列计算而来可分为虚拟列VIRTUAL不存储读取时计算和存储列STORED持久化存储。这为建立高效的函数索引铺平了道路。应用场景-- 场景经常需要根据 first_name 和 last_name 进行全名搜索 ALTERTABLEusers ADDCOLUMNfull_nameVARCHAR(255) GENERATEDALWAYSAS(CONCAT(first_name, , last_name))STORED,-- 创建存储的生成列 ADDINDEXidx_full_name (full_name);-- 在生成列上建立索引 -- 现在以下查询可以高效使用索引 SELECT*FROMusersWHEREfull_name John Doe;深度剖析这解决了直接在表达式如CONCAT(first_name, ‘ ‘, last_name)上建立函数索引的难题。虚拟列节省空间但增加CPU计算开销存储列反之。总结从“会用”到“精通”的跃迁好了一口气聊了11个MySQL的高级用法。让我们最后再梳理一下这些技巧并非孤立存在它们构成了一个应对不同场景挑战的工具箱。有些小伙伴在工作中可能会有这样的疑问“我知道它们好但该从哪里开始学起呢”我的建议是从EXPLAIN和索引优化开始。这是性能问题的根本。然后根据你的业务需求引入窗口函数或CTE来简化复杂查询。当数据量上来后考虑分区。对于动态数据结构尝试JSON类型。