01 执行计划在优化任何查询之前读懂EXPLAIN的输出是你的第一门必修课。它就像SQL的“X光片”能告诉你MySQL究竟打算如何执行你的查询瓶颈在哪里。核心用法与实战执行EXPLAIN后你需要重点关注以下几个关键字段type访问类型从最优到最差大致是systemconsteq_refrefrangeindexALL。看到ALL全表扫描就要警惕了。key实际使用的索引。如果为NULL说明没用上索引。rowsMySQL预估要扫描的行数。这个数字越接近实际需要的数据行数越好。Extra包含非常丰富的信息例如Using filesort需要额外排序、Using temporary使用了临时表这通常是性能杀手。-- 一个需要优化的查询示例 EXPLAIN SELECT * FROM orders WHERE user_id 10086 AND create_time BETWEEN 2024-01-01 AND 2024-01-31 ORDER BY amount DESC;假设这个查询的type是ALLkey是NULL。这意味着它在orders表上进行了全表扫描性能极差。优化方法通常是创建一个复合索引-- 创建覆盖了WHERE和ORDER BY的复合索引 CREATE INDEX idx_user_time_amount ON orders(user_id, create_time, amount); -- 再次使用EXPLAIN你会看到type变成了rangekey显示了新索引性能天差地别。深度剖析EXPLAIN是基于表的统计信息来估算成本的。如果表数据变化很大而统计信息未更新优化器可能会选错索引。这时可以用ANALYZE TABLE table_name;来手动更新统计信息。有些小伙伴在工作中写的SQL本身不复杂但执行很慢第一步就应该祭出EXPLAIN。02 高级索引策略索引是性能的基石但错误的索引比没有索引更糟糕。高级索引策略覆盖索引Covering Index如果索引包含了查询需要的所有字段引擎就无需回表查询数据行速度极快。-- 假设常用查询是获取用户的姓名和邮箱 SELECT name, email FROM users WHERE age 20; -- 为这个查询设计覆盖索引 CREATE INDEX idx_age_name_email ON users(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个字符创建索引 CREATE INDEX idx_content_prefix ON articles (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 (PARTITION BY department ORDER BY salary DESC) as dept_salary_rank, -- 同时计算公司整体排名 RANK() OVER (ORDER BY salary DESC) as company_rank, -- 计算部门内薪水累计占比 SUM(salary) OVER (PARTITION BY department) as dept_total, salary / SUM(salary) OVER (PARTITION BY department) as salary_ratio FROM employees;PARTITION BY类似于GROUP BY但不会将行合并而是定义窗口范围。ORDER BY决定窗口内的排序。深度剖析在MySQL 8.0之前要实现上述查询你需要写复杂的自连接或效率极低的子查询。窗口函数在数据库内部进行了深度优化性能提升可达几个数量级。它特别适用于分析报表、实时排行榜、计算同比环比等OLAP型场景。04 通用表表达式CTECTEWITH子句是另一个MySQL 8.0的重要特性它允许你定义临时的命名结果集在后续查询中像普通表一样引用。优势与示例提升可读性将复杂查询分解成逻辑清晰的步骤。支持递归这是CTE的杀手级功能可以轻松查询树形或图状数据。-- 示例1分解复杂查询非递归 WITH high_value_orders AS ( -- 找出高价值订单 SELECT user_id, SUM(amount) as total_spent FROM orders WHERE status completed GROUP BY user_id HAVING total_spent 10000 ), active_users AS ( -- 找出活跃用户 SELECT DISTINCT user_id FROM user_logs WHERE last_active_date DATE_SUB(NOW(), INTERVAL 30 DAY) ) -- 最终查询既是高价值又是活跃的用户 SELECT u.name, u.email, h.total_spent FROM users u JOIN high_value_orders h ON u.id h.user_id JOIN active_users a ON u.id a.user_id; -- 示例2递归CTE查询部门树 WITH RECURSIVE department_tree AS ( -- 锚点找到根部门 SELECT id, name, parent_id, 1 as level FROM departments WHERE parent_id IS NULL UNION ALL -- 递归成员连接父部门和子部门 SELECT d.id, d.name, d.parent_id, dt.level 1 FROM departments d INNER JOIN department_tree dt ON d.parent_id dt.id ) SELECT * FROM department_tree ORDER BY level, id;深度剖析递归CTE极大地简化了组织架构、分类目录、评论嵌套等层次数据的查询。在旧版本中这通常需要在应用层进行多次查询或在数据库中使用存储过程递归CTE在数据库内核完成遍历效率更高。05 JSON类型与函数MySQL 5.7原生支持JSON数据类型让你能够在关系型数据库中灵活地存储和查询半结构化数据这在处理动态字段、配置信息或第三方API返回的数据时非常有用。核心操作-- 1. 创建包含JSON列的表 CREATE TABLE products ( id INT PRIMARY KEY, name VARCHAR(100), attributes JSON COMMENT 存储颜色、尺寸等动态属性 ); -- 2. 插入JSON数据 INSERT INTO products VALUES (1, T-Shirt, {color: red, size: [M, L], tags: [casual, cotton]}); -- 3. 查询 (使用 - 和 - 操作符) -- - 返回JSON类型 - 返回纯文本字符串 SELECT name, attributes-$.color as color, -- 提取color值 attributes-$.size as size_array -- 提取size数组仍为JSON FROM products WHERE attributes-$.color red OR JSON_CONTAINS(attributes-$.tags, cotton); -- 4. 更新部分JSON UPDATE products SET attributes JSON_SET(attributes, $.color, blue, $.new_field, value) WHERE id 1;深度剖析JSON列同样可以建立索引通过函数索引加速查询。CREATE INDEX idx_color ON products( (attributes-$.color) );这允许你在保持灵活性的同时不丧失对关键字段的查询性能。它完美填补了关系模型在应对多变业务需求时的短板。06 分区表Partitioning当单表数据量巨大如数亿行时分区可以将一张大表在物理上分割为多个更小、更易管理的部分而逻辑上仍是一张表。分区策略与示例-- 按时间范围(RANGE)分区非常适合日志、订单表 CREATE TABLE sales ( id INT, sale_date DATE, amount DECIMAL(10,2) ) PARTITION BY RANGE COLUMNS(sale_date) ( PARTITION p2023q1 VALUES LESS THAN (2023-04-01), PARTITION p2023q2 VALUES LESS THAN (2023-07-01), PARTITION p2023q3 VALUES LESS THAN (2023-10-01), PARTITION p2023q4 VALUES LESS THAN (2024-01-01), PARTITION p_future VALUES LESS THAN MAXVALUE ); -- 查询时优化器会自动定位到特定分区分区裁剪Partition Pruning EXPLAIN SELECT * FROM sales WHERE sale_date 2023-05-15; -- 你会看到partitions: p2023q2意味着只扫描了2023年Q2的分区。除了RANGE还有LIST按列表值、HASH按哈希值均匀分布等分区方式。深度剖析分区的核心优势在于维护性和查询性能。你可以快速删除或归档整个旧分区ALTER TABLE sales DROP PARTITION p2023q1;这比DELETE操作快得多且不产生碎片。对于按分区键过滤的查询性能提升显著。但注意分区键选择不当或跨分区查询性能可能反而下降。07 连接JOIN与子查询多表关联是业务常态但写得不好就是性能灾难。高级技巧控制连接顺序MySQL优化器通常会选择它认为最佳的顺序但你可以在复杂场景下用STRAIGHT_JOIN强制指定顺序。SELECT ... FROM small_table s STRAIGHT_JOIN large_table l ON s.id l.s_id; -- 强制先查小表利用衍生表Derived Table下推条件有时将子查询或过滤条件提前能极大地减少中间结果集。-- 优化前先连接两个大表再过滤 SELECT * FROM A JOIN B ON A.id B.aid WHERE A.create_time ...; -- 优化后先过滤A表再连接 SELECT * FROM (SELECT * FROM A WHERE create_time ...) filtered_A JOIN B ON filtered_A.id B.aid;EXISTSvsIN对于“是否存在”的查询特别是子查询结果集较大时EXISTS关联子查询通常比IN非关联子查询性能更好因为它找到第一个匹配项就会停止。深度剖析所有的JOIN优化其核心思想都是“尽早过滤减少中间数据量”。熟练使用EXPLAIN查看连接类型如eq_ref很好Using join buffer说明可能需要索引是关键。08 用户自定义变量MySQL允许你定义用户变量如rank这在一些需要跨行计算或记录中间状态的分析中非常有用。实战案例计算行间差值-- 计算每日销售额的日环比增长率 SELECT sale_date, daily_amount, -- 使用变量记录前一天的值 prev_amount as prev_day_amount, ROUND( (daily_amount - prev_amount) / prev_amount * 100, 2) as growth_rate, -- 将当前值赋给变量供下一行使用 prev_amount : daily_amount FROM daily_sales_summary, (SELECT prev_amount : 0) init -- 初始化变量 ORDER BY sale_date;深度剖析用户变量提供了过程式编程的能力可以模拟窗口函数的部分功能在MySQL 8.0之前。但它不是SQL标准执行顺序有时反直觉需谨慎使用。在复杂的会话或事务中变量的生命周期和作用域也需要仔细考量。09 在线DDL与无锁变更在业务7x24小时运行的时代给大表加字段、改索引再也不能随意停服务了。MySQL 5.6提供了ALGORITHM和LOCK选项实现在线DDLOnline Data Definition。安全操作指南-- 添加一个可为空且有默认值的新列使用INPLACE算法和尽量低的锁级别 ALTER TABLE huge_table ADD COLUMN new_column VARCHAR(100) DEFAULT NOT NULL, ALGORITHMINPLACE, -- 尽量使用INPLACE原地重建避免COPY锁表复制 LOCKNONE; -- 目标不加锁或共享锁 -- 修改列类型某些情况需要COPY会锁表 ALTER TABLE huge_table MODIFY COLUMN old_column BIGINT, ALGORITHMCOPY, -- 注意这里可能必须用COPY LOCKSHARED;深度剖析ALGORITHMINPLACE意味着大部分工作如重建索引在引擎内部完成允许并发DML操作。而ALGORITHMCOPY会创建新表并复制数据全程锁表。执行前务必用ALGORITHMDEFAULT先测试一下。pt-online-schema-change是Percona提供的第三方工具通过触发器实现真正的全程无锁是更稳妥的选择。10 利用生成列与函数索引生成列的值由表中其他列计算而来可分为虚拟列VIRTUAL不存储读取时计算和存储列STORED持久化存储。这为建立高效的函数索引铺平了道路。应用场景-- 场景经常需要根据 first_name 和 last_name 进行全名搜索 ALTER TABLE users ADD COLUMN full_name VARCHAR(255) GENERATED ALWAYS AS (CONCAT(first_name, , last_name)) STORED, -- 创建存储的生成列 ADD INDEX idx_full_name (full_name); -- 在生成列上建立索引 -- 现在以下查询可以高效使用索引 SELECT * FROM users WHERE full_name John Doe;深度剖析这解决了直接在表达式如CONCAT(first_name, ‘ ‘, last_name)上建立函数索引的难题。虚拟列节省空间但增加CPU计算开销存储列反之。总结从“会用”到“精通”的跃迁好了一口气聊了11个MySQL的高级用法。让我们最后再梳理一下这些技巧并非孤立存在它们构成了一个应对不同场景挑战的工具箱。高级用法核心解决痛点推荐适用场景执行计划(EXPLAIN)性能瓶颈可视化所有慢查询优化前的第一步高级索引策略查询速度慢写操作重高频查询、大表性能优化窗口函数(Window)复杂跨行计算效率低排行榜、数据分析、报表通用表表达式(CTE)复杂SQL难读写树查询难复杂业务逻辑拆分层次数据查询JSON类型动态、半结构化数据存储与查询配置、动态属性、API数据存储分区表超大表维护难历史数据清理慢时间序列数据日志、订单JOIN/子查询优化多表关联性能低下涉及多个业务实体的复杂查询用户自定义变量需行间计算或状态保持8.0前自定义序列、差值计算有窗口函数后优先级降低在线DDL业务不中断变更表结构7x24小时系统表结构变更生成列/函数索引无法直接对表达式建索引基于JSON字段、计算字段的高效查询
推荐MySQL中的这10种高级用法
发布时间：2026/5/17 2:31:38
01 执行计划在优化任何查询之前读懂EXPLAIN的输出是你的第一门必修课。它就像SQL的“X光片”能告诉你MySQL究竟打算如何执行你的查询瓶颈在哪里。核心用法与实战执行EXPLAIN后你需要重点关注以下几个关键字段type访问类型从最优到最差大致是systemconsteq_refrefrangeindexALL。看到ALL全表扫描就要警惕了。key实际使用的索引。如果为NULL说明没用上索引。rowsMySQL预估要扫描的行数。这个数字越接近实际需要的数据行数越好。Extra包含非常丰富的信息例如Using filesort需要额外排序、Using temporary使用了临时表这通常是性能杀手。-- 一个需要优化的查询示例 EXPLAIN SELECT * FROM orders WHERE user_id 10086 AND create_time BETWEEN 2024-01-01 AND 2024-01-31 ORDER BY amount DESC;假设这个查询的type是ALLkey是NULL。这意味着它在orders表上进行了全表扫描性能极差。优化方法通常是创建一个复合索引-- 创建覆盖了WHERE和ORDER BY的复合索引 CREATE INDEX idx_user_time_amount ON orders(user_id, create_time, amount); -- 再次使用EXPLAIN你会看到type变成了rangekey显示了新索引性能天差地别。深度剖析EXPLAIN是基于表的统计信息来估算成本的。如果表数据变化很大而统计信息未更新优化器可能会选错索引。这时可以用ANALYZE TABLE table_name;来手动更新统计信息。有些小伙伴在工作中写的SQL本身不复杂但执行很慢第一步就应该祭出EXPLAIN。02 高级索引策略索引是性能的基石但错误的索引比没有索引更糟糕。高级索引策略覆盖索引Covering Index如果索引包含了查询需要的所有字段引擎就无需回表查询数据行速度极快。-- 假设常用查询是获取用户的姓名和邮箱 SELECT name, email FROM users WHERE age 20; -- 为这个查询设计覆盖索引 CREATE INDEX idx_age_name_email ON users(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个字符创建索引 CREATE INDEX idx_content_prefix ON articles (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 (PARTITION BY department ORDER BY salary DESC) as dept_salary_rank, -- 同时计算公司整体排名 RANK() OVER (ORDER BY salary DESC) as company_rank, -- 计算部门内薪水累计占比 SUM(salary) OVER (PARTITION BY department) as dept_total, salary / SUM(salary) OVER (PARTITION BY department) as salary_ratio FROM employees;PARTITION BY类似于GROUP BY但不会将行合并而是定义窗口范围。ORDER BY决定窗口内的排序。深度剖析在MySQL 8.0之前要实现上述查询你需要写复杂的自连接或效率极低的子查询。窗口函数在数据库内部进行了深度优化性能提升可达几个数量级。它特别适用于分析报表、实时排行榜、计算同比环比等OLAP型场景。04 通用表表达式CTECTEWITH子句是另一个MySQL 8.0的重要特性它允许你定义临时的命名结果集在后续查询中像普通表一样引用。优势与示例提升可读性将复杂查询分解成逻辑清晰的步骤。支持递归这是CTE的杀手级功能可以轻松查询树形或图状数据。-- 示例1分解复杂查询非递归 WITH high_value_orders AS ( -- 找出高价值订单 SELECT user_id, SUM(amount) as total_spent FROM orders WHERE status completed GROUP BY user_id HAVING total_spent 10000 ), active_users AS ( -- 找出活跃用户 SELECT DISTINCT user_id FROM user_logs WHERE last_active_date DATE_SUB(NOW(), INTERVAL 30 DAY) ) -- 最终查询既是高价值又是活跃的用户 SELECT u.name, u.email, h.total_spent FROM users u JOIN high_value_orders h ON u.id h.user_id JOIN active_users a ON u.id a.user_id; -- 示例2递归CTE查询部门树 WITH RECURSIVE department_tree AS ( -- 锚点找到根部门 SELECT id, name, parent_id, 1 as level FROM departments WHERE parent_id IS NULL UNION ALL -- 递归成员连接父部门和子部门 SELECT d.id, d.name, d.parent_id, dt.level 1 FROM departments d INNER JOIN department_tree dt ON d.parent_id dt.id ) SELECT * FROM department_tree ORDER BY level, id;深度剖析递归CTE极大地简化了组织架构、分类目录、评论嵌套等层次数据的查询。在旧版本中这通常需要在应用层进行多次查询或在数据库中使用存储过程递归CTE在数据库内核完成遍历效率更高。05 JSON类型与函数MySQL 5.7原生支持JSON数据类型让你能够在关系型数据库中灵活地存储和查询半结构化数据这在处理动态字段、配置信息或第三方API返回的数据时非常有用。核心操作-- 1. 创建包含JSON列的表 CREATE TABLE products ( id INT PRIMARY KEY, name VARCHAR(100), attributes JSON COMMENT 存储颜色、尺寸等动态属性 ); -- 2. 插入JSON数据 INSERT INTO products VALUES (1, T-Shirt, {color: red, size: [M, L], tags: [casual, cotton]}); -- 3. 查询 (使用 - 和 - 操作符) -- - 返回JSON类型 - 返回纯文本字符串 SELECT name, attributes-$.color as color, -- 提取color值 attributes-$.size as size_array -- 提取size数组仍为JSON FROM products WHERE attributes-$.color red OR JSON_CONTAINS(attributes-$.tags, cotton); -- 4. 更新部分JSON UPDATE products SET attributes JSON_SET(attributes, $.color, blue, $.new_field, value) WHERE id 1;深度剖析JSON列同样可以建立索引通过函数索引加速查询。CREATE INDEX idx_color ON products( (attributes-$.color) );这允许你在保持灵活性的同时不丧失对关键字段的查询性能。它完美填补了关系模型在应对多变业务需求时的短板。06 分区表Partitioning当单表数据量巨大如数亿行时分区可以将一张大表在物理上分割为多个更小、更易管理的部分而逻辑上仍是一张表。分区策略与示例-- 按时间范围(RANGE)分区非常适合日志、订单表 CREATE TABLE sales ( id INT, sale_date DATE, amount DECIMAL(10,2) ) PARTITION BY RANGE COLUMNS(sale_date) ( PARTITION p2023q1 VALUES LESS THAN (2023-04-01), PARTITION p2023q2 VALUES LESS THAN (2023-07-01), PARTITION p2023q3 VALUES LESS THAN (2023-10-01), PARTITION p2023q4 VALUES LESS THAN (2024-01-01), PARTITION p_future VALUES LESS THAN MAXVALUE ); -- 查询时优化器会自动定位到特定分区分区裁剪Partition Pruning EXPLAIN SELECT * FROM sales WHERE sale_date 2023-05-15; -- 你会看到partitions: p2023q2意味着只扫描了2023年Q2的分区。除了RANGE还有LIST按列表值、HASH按哈希值均匀分布等分区方式。深度剖析分区的核心优势在于维护性和查询性能。你可以快速删除或归档整个旧分区ALTER TABLE sales DROP PARTITION p2023q1;这比DELETE操作快得多且不产生碎片。对于按分区键过滤的查询性能提升显著。但注意分区键选择不当或跨分区查询性能可能反而下降。07 连接JOIN与子查询多表关联是业务常态但写得不好就是性能灾难。高级技巧控制连接顺序MySQL优化器通常会选择它认为最佳的顺序但你可以在复杂场景下用STRAIGHT_JOIN强制指定顺序。SELECT ... FROM small_table s STRAIGHT_JOIN large_table l ON s.id l.s_id; -- 强制先查小表利用衍生表Derived Table下推条件有时将子查询或过滤条件提前能极大地减少中间结果集。-- 优化前先连接两个大表再过滤 SELECT * FROM A JOIN B ON A.id B.aid WHERE A.create_time ...; -- 优化后先过滤A表再连接 SELECT * FROM (SELECT * FROM A WHERE create_time ...) filtered_A JOIN B ON filtered_A.id B.aid;EXISTSvsIN对于“是否存在”的查询特别是子查询结果集较大时EXISTS关联子查询通常比IN非关联子查询性能更好因为它找到第一个匹配项就会停止。深度剖析所有的JOIN优化其核心思想都是“尽早过滤减少中间数据量”。熟练使用EXPLAIN查看连接类型如eq_ref很好Using join buffer说明可能需要索引是关键。08 用户自定义变量MySQL允许你定义用户变量如rank这在一些需要跨行计算或记录中间状态的分析中非常有用。实战案例计算行间差值-- 计算每日销售额的日环比增长率 SELECT sale_date, daily_amount, -- 使用变量记录前一天的值 prev_amount as prev_day_amount, ROUND( (daily_amount - prev_amount) / prev_amount * 100, 2) as growth_rate, -- 将当前值赋给变量供下一行使用 prev_amount : daily_amount FROM daily_sales_summary, (SELECT prev_amount : 0) init -- 初始化变量 ORDER BY sale_date;深度剖析用户变量提供了过程式编程的能力可以模拟窗口函数的部分功能在MySQL 8.0之前。但它不是SQL标准执行顺序有时反直觉需谨慎使用。在复杂的会话或事务中变量的生命周期和作用域也需要仔细考量。09 在线DDL与无锁变更在业务7x24小时运行的时代给大表加字段、改索引再也不能随意停服务了。MySQL 5.6提供了ALGORITHM和LOCK选项实现在线DDLOnline Data Definition。安全操作指南-- 添加一个可为空且有默认值的新列使用INPLACE算法和尽量低的锁级别 ALTER TABLE huge_table ADD COLUMN new_column VARCHAR(100) DEFAULT NOT NULL, ALGORITHMINPLACE, -- 尽量使用INPLACE原地重建避免COPY锁表复制 LOCKNONE; -- 目标不加锁或共享锁 -- 修改列类型某些情况需要COPY会锁表 ALTER TABLE huge_table MODIFY COLUMN old_column BIGINT, ALGORITHMCOPY, -- 注意这里可能必须用COPY LOCKSHARED;深度剖析ALGORITHMINPLACE意味着大部分工作如重建索引在引擎内部完成允许并发DML操作。而ALGORITHMCOPY会创建新表并复制数据全程锁表。执行前务必用ALGORITHMDEFAULT先测试一下。pt-online-schema-change是Percona提供的第三方工具通过触发器实现真正的全程无锁是更稳妥的选择。10 利用生成列与函数索引生成列的值由表中其他列计算而来可分为虚拟列VIRTUAL不存储读取时计算和存储列STORED持久化存储。这为建立高效的函数索引铺平了道路。应用场景-- 场景经常需要根据 first_name 和 last_name 进行全名搜索 ALTER TABLE users ADD COLUMN full_name VARCHAR(255) GENERATED ALWAYS AS (CONCAT(first_name, , last_name)) STORED, -- 创建存储的生成列 ADD INDEX idx_full_name (full_name); -- 在生成列上建立索引 -- 现在以下查询可以高效使用索引 SELECT * FROM users WHERE full_name John Doe;深度剖析这解决了直接在表达式如CONCAT(first_name, ‘ ‘, last_name)上建立函数索引的难题。虚拟列节省空间但增加CPU计算开销存储列反之。总结从“会用”到“精通”的跃迁好了一口气聊了11个MySQL的高级用法。让我们最后再梳理一下这些技巧并非孤立存在它们构成了一个应对不同场景挑战的工具箱。高级用法核心解决痛点推荐适用场景执行计划(EXPLAIN)性能瓶颈可视化所有慢查询优化前的第一步高级索引策略查询速度慢写操作重高频查询、大表性能优化窗口函数(Window)复杂跨行计算效率低排行榜、数据分析、报表通用表表达式(CTE)复杂SQL难读写树查询难复杂业务逻辑拆分层次数据查询JSON类型动态、半结构化数据存储与查询配置、动态属性、API数据存储分区表超大表维护难历史数据清理慢时间序列数据日志、订单JOIN/子查询优化多表关联性能低下涉及多个业务实体的复杂查询用户自定义变量需行间计算或状态保持8.0前自定义序列、差值计算有窗口函数后优先级降低在线DDL业务不中断变更表结构7x24小时系统表结构变更生成列/函数索引无法直接对表达式建索引基于JSON字段、计算字段的高效查询
