揭秘MySQL高手常用的高级SQL技巧 (三)

发布于 2025-10-02 分类: 数据库
系列文章: Mysql数据库奇技淫巧
第 1 部分: 揭秘MySQL高手常用的高级SQL技巧 (一) 第 2 部分: 揭秘MySQL高手常用的高级SQL技巧 (二) 第 3 部分: 揭秘MySQL高手常用的高级SQL技巧 (三) (当前)

前言

本篇，我们将探讨一些在特定场景下能发挥巨大作用的“终极”技巧。这些内容将涵盖MySQL对半结构化数据（JSON）的支持、存储优化、高级诊断工具，以及一些在运维层面非常有用的窍门。学完这些，你将不仅是一个会写SQL的开发者，更是一个懂得如何最大化利用MySQL特性的数据库专家。

技巧 11: 原生JSON数据类型 - 让MySQL拥抱NoSQL

背景与痛点:

在传统的范式设计中，如果一个实体有动态、不固定属性个数（例如，商品的不同规格、用户配置信息），我们通常需要设计复杂的EAV（实体-属性-值）模型，或者添加大量可能为NULL的列，这两种方式都非常笨拙且难以维护。

很多开发者为了方便，直接用TEXT或VARCHAR字段存储JSON字符串。这样做的问题是：

  1. 无法验证JSON格式：数据库层面无法保证存入的是合法的JSON。
  2. 查询和更新困难：你无法直接查询JSON内部的某个key，也无法原子性地更新某个key的值。通常需要把整个JSON字符串取到应用层，解析、修改，再完整存回去，效率低下且容易产生并发问题。

奇技淫巧:

从MySQL 5.7开始，引入了原生的 JSON 数据类型。

原理:

JSON 类型不仅仅是存储字符串，它会对存入的数据进行格式验证。更重要的是，MySQL提供了一整套函数来高效地创建、查询、提取和修改JSON文档。对JSON字段的更新也是最优化的，只会修改文档中需要变更的部分。

举例:

假设我们有一个产品表 products，其中 properties 字段用于存储动态规格。

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    properties JSON
);

INSERT INTO products VALUES
(1, '笔记本电脑', '{"brand": "Apple", "ram_gb": 16, "ports": ["USB-C", "HDMI"]}'),
(2, '智能手机', '{"brand": "Google", "ram_gb": 8, "colors": {"available": ["black", "white"], "default": "black"}}');

1. 查询JSON内部数据 ( -> 和 ->> )

  • -> (短横线大于号): 提取JSON值，结果带引号（JSON字符串）或为JSON对象/数组。
  • ->> (短横线双大于号): 提取JSON值，并去除引号，返回为SQL字符串。常用于WHERE子句。
-- 查询品牌为 "Apple" 的产品
SELECT name, properties ->> '$.brand' as brand
FROM products
WHERE properties ->> '$.brand' = 'Apple';

-- 查询内存大于8GB的产品
SELECT name
FROM products
WHERE properties ->> '$.ram_gb' > 8; -- 注意：这里是字符串比较，最好转为数值
-- 更严谨的写法
-- WHERE CAST(properties ->> '$.ram_gb' AS UNSIGNED) > 8;

-- 查询嵌套JSON
SELECT properties ->> '$.colors.default' as default_color
FROM products
WHERE id = 2; -- 结果: "black"

-- 查询数组的第一个元素
SELECT properties -> '$.ports[0]' as first_port
FROM products
WHERE id = 1; -- 结果: "USB-C"

$ 代表整个JSON文档。路径语法类似JavaScript。

2. 判断JSON中是否存在某个key或value

  • JSON_CONTAINS(target, candidate[, path]): 判断candidate是否包含在target中。
  • JSON_EXTRACT() / -> 配合 IS NOT NULL
-- 查询所有包含 "HDMI" 接口的产品
SELECT name FROM products
WHERE JSON_CONTAINS(properties -> '$.ports', '"HDMI"'); -- 注意：搜索的值需要是有效的JSON字面量

3. 修改JSON

  • JSON_SET(doc, path, val, ...): 插入或更新值。如果路径存在则更新，不存在则创建。
  • JSON_INSERT(doc, path, val, ...): 只插入值。如果路径已存在，则不做任何操作。
  • JSON_REPLACE(doc, path, val, ...): 只替换值。如果路径不存在，则不做任何操作。
  • JSON_REMOVE(doc, path, ...): 删除指定路径的值。
-- 将id=1的电脑内存升级到32GB，并添加一个新的属性'ssd_gb'
UPDATE products
SET properties = JSON_SET(properties, '$.ram_gb', 32, '$.ssd_gb', 512)
WHERE id = 1;

性能: 在MySQL 8.0中，可以对JSON字段的特定路径创建多值索引（Multi-Valued Indexes），极大地提升了JSON内部数据的查询性能。

技巧 12: 生成列(Generated Columns) - 空间换时间的艺术

背景与痛点:

在 上一篇文章 的技巧10中，我们提到为了让函数查询能走索引，可以在MySQL 8.0之前版本通过添加一个新列来存储函数计算结果。这种“手动”维护的冗余列存在数据一致性的风险：如果created_at更新了，你可能会忘记更新created_month，导致数据不一致。

奇技淫巧:

使用生成列（Generated Columns）。

原理:

生成列的值是由其他列计算得出的，它本身不直接接受用户插入的值。生成列分为两种：

  • VIRTUAL: 虚拟生成列。数据不被存储在磁盘上，而是在每次读取时动态计算。它可以被索引（仅限InnoDB）。
  • STORED: 存储生成列。数据在插入或更新时被计算并存储在磁盘上，就像一个普通列。它的行为类似于有触发器的普通列。

选择 VIRTUAL 还是 STORED?

  • VIRTUAL: 节省磁盘空间，但每次读取时有微小的计算开销。
  • STORED: 占用磁盘空间，但读取时没有计算开销，和普通列一样快。

举例:

假设有一个订单表，需要频繁查询订单的总价（quantity * price）。

CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100),
    quantity INT,
    price DECIMAL(10, 2),
    -- 创建一个STORED生成列 total_price
    total_price DECIMAL(10, 2) AS (quantity * price) STORED,
    -- 创建一个VIRTUAL生成列 full_name (用于演示)
    full_name VARCHAR(200) AS (CONCAT(product_name, ' (', id, ')')) VIRTUAL
);

使用:
你不能直接INSERT或UPDATE total_price列。

INSERT INTO order_items (product_name, quantity, price) VALUES ('鼠标', 2, 100.00);

SELECT * FROM order_items;

查询结果会自动包含 total_price (200.00) 和 full_name ('鼠标 (1)')。

与索引结合 (最重要的应用场景):
现在，我们可以为这个生成列创建索引，从而实现对表达式的索引查询。

-- 为 total_price 创建索引
CREATE INDEX idx_total_price ON order_items(total_price);

之后，SELECT * FROM order_items WHERE total_price > 1000; 这样的查询就可以高效地使用 idx_total_price 索引了。这比MySQL 8.0+的表达式索引有更好的兼容性。

技巧 13: INSERT IGNORE vs REPLACE - 更精细地处理主键冲突

背景与痛点:

我们知道ON DUPLICATE KEY UPDATE可以在主键或唯一键冲突时执行更新操作。但有时我们的需求更简单：

  1. 如果记录不存在，就插入；如果存在，就什么都不做。
  2. 如果记录不存在，就插入；如果存在，就先删除旧记录，再插入新记录。

奇技淫巧:

使用 INSERT IGNORE 和 REPLACE。

INSERT IGNORE:

  • 行为: 当插入一行数据，如果因为主键或唯一键冲突而失败时，MySQL会忽略这个错误，不插入该行，然后继续处理下一行数据（如果有的话）。它不会报错，但会生成一个警告。
  • 场景: 批量插入数据时，你希望跳过所有已存在的记录，只插入新的记录。

举例:

CREATE TABLE unique_emails (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);
INSERT INTO unique_emails (email) VALUES ('a@example.com'), ('b@example.com');

-- 尝试插入一条已存在的和一条新的
INSERT IGNORE INTO unique_emails (email) VALUES ('b@example.com'), ('c@example.com');
-- 'b@example.com' 被忽略，'c@example.com' 被成功插入。
-- 查询结果: a@example.com, b@example.com, c@example.com

REPLACE:

  • 行为: REPLACE 的工作机制是：
    1. 尝试插入新行。
    2. 如果因为主键或唯一键冲突导致插入失败，MySQL会先删除导致冲突的旧行。
    3. 然后再插入新行。
      这个过程是原子的。如果表中还有其他AUTO_INCREMENT的ID，旧记录被删除后，新记录会获得一个新的ID。
  • 场景: 你希望用新数据完全覆盖旧数据，而不是在旧数据基础上更新。

举例:

REPLACE INTO unique_emails (id, email) VALUES (2, 'b_new@example.com');
-- 因为id=2冲突 (b@example.com)，MySQL会先删除id=2的行，再插入新行。
-- 查询结果: (1, 'a@example.com'), (2, 'b_new@example.com'), (3, 'c@example.com')

REPLACE vs ON DUPLICATE KEY UPDATE:
REPLACE 是 “删除+插入”，ON DUPLICATE KEY UPDATE 是 “原地更新”。最大的区别在于 AUTO_INCREMENT ID 和对其他列的影响。如果一行数据有很多列，而你只想更新其中几列，ON DUPLICATE KEY UPDATE 更合适，因为它保留了未指定列的旧值。而REPLACE会用新行中未指定的列的默认值替换旧值。

graph TD
    A[INSERT] --> B{主键/唯一键冲突?};
    B -- No --> C[插入成功];
    B -- Yes --> D{使用什么语句?};

    D -- "INSERT IGNORE" --> E[忽略错误, 不做任何事];
    D -- "ON DUPLICATE KEY UPDATE" --> F[原地更新旧行];
    D -- "REPLACE" --> G[删除旧行] --> H[插入新行];

技巧 14: SHOW PROFILE - 解剖你的SQL执行细节

背景与痛点:

EXPLAIN 告诉我们SQL的执行计划，但它不告诉我们SQL实际执行时，每个阶段花费了多少时间。例如，EXPLAIN可能会显示Using filesort，但这个排序到底花了0.01秒还是10秒？EXPLAIN无法回答。

奇技淫巧:

使用 SHOW PROFILE（或更高版本中的 performance_schema）来获取SQL执行的详细计时信息。

原理:

当开启 profiling 后，MySQL会记录当前会话中执行的每一条语句的详细性能剖析数据，包括每个执行阶段（如starting, checking permissions, Opening tables, Executing, Sending data, end等）的耗时。

举至:

  1. 开启 profiling:
    SET profiling = 1;
  2. 执行你的慢查询:
    SELECT a.id, GROUP_CONCAT(t.name)
FROM articles a
JOIN tags t ON a.tag_id = t.id
GROUP BY a.id
ORDER BY a.publish_date DESC;
  3. 查看 profiles 列表:
    SHOW PROFILES;
    这会列出你刚才执行的查询及其Query_ID和总耗时。
    +----------+------------+--------------------------------+
| Query_ID | Duration   | Query                          |
+----------+------------+--------------------------------+
| 1        | 0.12345600 | SELECT ... (你的查询)          |
+----------+------------+--------------------------------+
  4. 查看特定查询的详细 profile:
    SHOW PROFILE FOR QUERY 1;
    你会得到一个详细的列表，显示每个阶段的耗时：
    Status Duration
    starting 0.000050
    Opening tables 0.000100
    System lock 0.000030
    init 0.000080
    optimizing 0.000070
    executing 0.000020
    Creating sort index 0.110000
    Sending data 0.012000
    end 0.000010
    ... ...

通过这个输出，你可以清晰地看到Creating sort index（对应EXPLAIN中的Using filesort）占用了绝大部分时间。这为你提供了精确的优化方向：设法消除或优化这个排序。

注意:
SHOW PROFILE 在高版本MySQL中已被废弃，并推荐使用功能更强大但更复杂的performance_schema。但对于快速、临时的单条SQL诊断，SHOW PROFILE 仍然是一个极其方便的工具。

技巧 15: mysqldump --master-data - 备份与主从复制的黄金搭档

背景与痛点:

在搭建MySQL主从复制环境时，最关键的一步是确保从库的数据与主库在某个时间点上完全一致。这个时间点被称为“日志点”（binlog file and position）。手动获取这个日志点（通过SHOW MASTER STATUS;）然后执行备份，再恢复到从库，这个过程容易出错：

  1. 如果在 SHOW MASTER STATUS; 和 mysqldump 开始之间，主库又有新的写入，你记录的日志点就不是备份文件对应的准确位置了。
  2. 为了保证一致性，你可能需要锁住整个数据库（FLUSH TABLES WITH READ LOCK;），但这会阻塞所有写入，对线上业务影响巨大。

奇技淫巧:

使用mysqldump的 --master-data 选项。

原理:

mysqldump --master-data=2 (通常用2) 会在备份开始时，自动执行以下操作：

  1. 在一个事务中（如果使用--single-transaction）或加全局读锁。
  2. 执行 SHOW MASTER STATUS; 获取当前的二进制日志文件名和位置。
  3. 将获取到的信息以 CHANGE MASTER TO ... 命令的形式，注释在备份文件的头部。
  4. 执行数据备份。
  5. 释放锁。

整个过程是原子性的，确保了备份文件和日志点信息的完美对应。

举例:

mysqldump -u root -p --all-databases --single-transaction --master-data=2 > full_backup.sql

打开 full_backup.sql 文件，你会在文件开头看到类似下面的一行：

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000123', MASTER_LOG_POS=456;

恢复与配置从库:

  1. 将 full_backup.sql 导入到你的从库服务器。
  2. 在从库上，直接复制、取消注释并执行备份文件头部的 CHANGE MASTER TO 命令。
  3. START SLAVE;

这样，从库就能准确地从备份结束的那个精确时刻，开始向主库请求后续的binlog进行同步，主从复制就完美建立起来了。这个技巧是每个DBA和需要处理数据库运维的后端工程师的必备技能。

-- 感谢阅读 --