一罐可乐

8.1 MySQL 与 JSON 数据处理#

MySQL 8.0 提供了原生的 JSON 数据类型和丰富的处理函数，将非结构化文档与关系型表格融合。

• JSON 常用函数：
  • JSON_OBJECT(key, val, ...)：将键值对转化为 JSON 对象。
  • JSON_ARRAY(val, ...)：创建 JSON 数组。
  • JSON_EXTRACT(json_doc, path)：提取指定 JSON 路径的值。可以使用缩写操作符 ->。
  • JSON_UNQUOTE(json_val)：去掉提取出的 JSON 字符串的引号。缩写操作符为 ->>。

1
-- 查询 myJsonOrders 表中 items 数组内第一个商品的商品名称
2
SELECT OrderID, items->'$.items[0].ProductName' AS FirstProduct
3
FROM myJsonOrders;
4

5
-- 提取并去除双引号
6
SELECT OrderID, items->>'$[0].ProductName' AS FirstProductClean
7
FROM myJsonOrders;

8.2 关系数据库中的层次树状模型存储与处理#

处理树状结构（如组织架构、商品多级分类）在关系数据库中有几种典型设计：

1
CREATE TABLE CategoryTree (
2
  CategoryID CHAR(14) PRIMARY KEY,
3
  CategoryName VARCHAR(100) NOT NULL,
4
  ParentNodeID CHAR(14),
5
  IsParentFlag TINYINT, -- 1 表示父节点，0 表示叶子节点
6
  Level TINYINT,        -- 树的层级
7
  Ancestor VARCHAR(150) -- 所有祖先节点路径，如 A#A1#
8
);

8.3 数据库查询性能优化#

• 深度分页优化（LIMIT OFFSET）：
  • 问题：LIMIT 1000000, 10 会导致 MySQL 扫描 $1000010$ 行并抛弃前面的 $1000000$ 行，IO 开销巨大。
  • 优化方案：延迟关联（子查询优化）。先利用覆盖索引（仅检索主键）快速找出分页的主键 ID，然后再与原表关联获取整行数据：

    1
    -- 优化前
    2
    SELECT * FROM Orders ORDER BY OrderDate DESC LIMIT 100000, 10;
    3
    
    4
    -- 优化后
    5
    SELECT a.* FROM Orders a
    6
    JOIN (SELECT OrderID FROM Orders ORDER BY OrderDate DESC LIMIT 100000, 10) b
    7
    USING(OrderID);

• 索引优化建议：
  • 对 WHERE 子句和 ORDER BY 子句中频繁出现的列创建索引。
  • 创建联合索引时，必须遵循 最左匹配原则（如建立 (A, B, C) 索引，只有查询条件包含 A 时索引才生效）。
  • 避免在索引列上使用函数或进行表达式计算（如 WHERE YEAR(OrderDate) = 2018 会使索引失效，应改为 WHERE OrderDate BETWEEN '2018-01-01' AND '2018-12-31'）。