mysql对json的索引支持，怎么说呢...就像给你的自行车装了个火箭推进器——听起来很酷，但根本不好使。
假设我们有个用户行为表，存的是各种事件数据：
-- MySQL表结构CREATE TABLE user_events ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, event_data JSON NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);-- 典型数据INSERT INTO user_events (user_id, event_data) VALUES (123, '{"type": "purchase", "amount": 299, "product": {"id": 456, "category": "electronics"}}'),(123, '{"type": "view", "page": "homepage", "duration": 15.5}');-- 最常见的查询：找买了电子产品的用户SELECT user_id, event_data FROM user_events WHERE JSON_EXTRACT(event_data, '$.type') = 'purchase' AND JSON_EXTRACT(event_data, '$.product.category') = 'electronics';看起来挺正常的对吧？但当你表里有500万条数据时，这个查询能跑到你怀疑人生。为啥？因为MySQL的JSON函数无法直接使用普通索引。
问题点 |
具体表现 |
伤害指数 |
|---|---|---|
函数索引限制 |
需要创建GENERATED COLUMN + 索引，改动成本极高 |
⭐⭐⭐⭐⭐ |
索引选择性差 |
对整个JSON文档建索引，体积巨大且效果不佳 |
⭐⭐⭐⭐ |
查询优化器识别 |
JSON函数经常导致全表扫描，即使有索引 |
⭐⭐⭐⭐⭐ |
部分更新 |
无法直接更新JSON中某个字段，需要整体替换 |
⭐⭐⭐ |
解决方案（MySQL原生）：
-- 创建虚拟列并加索引（这才是MySQL的正确姿势）ALTER TABLE user_events ADD COLUMN event_type VARCHAR(20) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(event_data, '$.type'))) STORED,ADD COLUMN product_category VARCHAR(50) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(event_data, '$.product.category'))) STORED;CREATE INDEX idx_event_type ON user_events(event_type);CREATE INDEX idx_product_category ON user_events(product_category);-- 改写查询（终于能用上索引了）SELECT user_id, event_data FROM user_events WHERE event_type = 'purchase' AND product_category = 'electronics';
但问题是，业务需求天天变，今天要查$.product.brand，明天要查$.user.location.city，你总不能给每个字段都建虚拟列吧？表结构会爆炸的。
MySQL的JSON函数设计，怎么说呢...就像用叉子喝汤——能喝，但体验极差。
函数名 |
使用体验 |
替代方案思考 |
|---|---|---|
JSON_EXTRACT() |
路径字符串容易写错，无语法检查 |
能否有类型安全的操作？ |
JSON_UNQUOTE() |
经常需要配合EXTRACT使用，忘记就踩坑 |
自动解引用不香吗？ |
JSON_CONTAINS() |
语义模糊，调试困难 |
更直观的包含判断？ |
JSON_MERGE_PATCH() |
5.7和8.0行为不一致，升级就踩雷 |
向前兼容设计？ |
让我吐槽一个真实案例：去年做用户标签系统，需要给JSON数组追加元素：
-- MySQL 5.7UPDATE users SET tags = JSON_MERGE_PATCH(tags, '["vip"]') WHERE id = 1;-- 结果：["old_tag", "vip"] 完美！-- 升级到MySQL 8.0后UPDATE users SET tags = JSON_MERGE_PATCH(tags, '["vip"]') WHERE id = 1;-- 结果：["old_tag", "vip"] 咦？好像没变？-- 查文档发现8.0改了实现，需要改成：UPDATE users SET tags = JSON_ARRAY_APPEND(tags, '$', 'vip') WHERE id = 1;-- 等等，那5.7又不支持这个函数...卒
这种升级就break的API设计，让线上灰度发布变成俄罗斯轮盘赌。
好了，吐槽完MySQL，该聊聊正主PostgreSQL了。第一次接触PostgreSQL的JSONB类型时，我的反应是： "这也行？！"
PostgreSQL的JSONB不是简单的text存储，而是二进制结构化存储，配合GIN索引，性能提升不是几倍，是数量级的差异。
特性维度 |
MySQL JSON |
PostgreSQL JSONB |
实战意义 |
|---|---|---|---|
存储格式 |
文本存储，需解析 |
二进制，预解析 |
查询速度↑↑↑ |
索引支持 |
虚拟列+BTREE |
GIN索引，直接对JSON建 |
灵活性↑↑↑ |
部分更新 |
整文档替换 |
支持部分更新 |
IO↓ |
查询语法 |
JSON_EXTRACT() |
->, ->>, #> 运算符 |
可读性↑ |
路径索引 |
不支持 |
GIN索引支持路径 |
业务适配性↑↑ |
这才是关键。MySQL把JSON当字符串处理，而PostgreSQL把JSON当数据结构处理。
mermaid图表：查询执行流程对比
☞☞☞AI 智能聊天, 问答助手, AI 智能搜索, 免费无限量使用 DeepSeek R1 模型☜☜☜
![[PostgreSQL]避开MySQL JSON查询陷阱：PostgreSQL算法应用指南](https://img.php.cn/upload/article/001/503/042/176475115154174.jpg)
看到区别了吗？MySQL每次都要解析字符串，而PostgreSQL直接二进制查找。这个差异在数据量上规模后，就是分钟级和毫秒级的区别。
光说不练假把式，咱们来个完整的迁移案例。假设有个电商平台的订单系统，原先用MySQL存订单的扩展属性。
先搭个测试环境，我用Docker快速起两个实例：
# MySQL 8.0（模拟现有生产环境）docker run -d --name mysql-source -e MYSQL_ROOT_PASSWORD=root123 -p 3306:3306 mysql:8.0.33# PostgreSQL 15（目标环境）docker run -d --name pg-target -e POSTGRES_PASSWORD=root123 -p 5432:5432 postgres:15.3-alpine
等容器跑起来后，咱们开始建表：
-- MySQL源表（现有业务）CREATE DATABASE order_system;USE order_system;CREATE TABLE orders ( order_id BIGINT PRIMARY KEY, user_id INT NOT NULL, order_data JSON NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_user_id (user_id));-- 模拟500万条订单数据（用存储过程批量生成）DELIMITER $$CREATE PROCEDURE generate_orders()BEGIN DECLARE i INT DEFAULT 1; WHILE i <= 5000000 DO INSERT INTO orders (order_id, user_id, order_data) VALUES ( i, FLOOR(1 + RAND() * 100000), JSON_OBJECT( 'items', JSON_ARRAY( JSON_OBJECT('sku', CONCAT('SKU', FLOOR(RAND()*1000)), 'qty', FLOOR(1 + RAND() * 5), 'price', ROUND(10 + RAND() * 990, 2)), JSON_OBJECT('sku', CONCAT('SKU', FLOOR(RAND()*1000)), 'qty', FLOOR(1 + RAND() * 3), 'price', ROUND(5 + RAND() * 495, 2)) ), 'shipping', JSON_OBJECT('city', ELT(1 + FLOOR(RAND() * 4), '北京', '上海', '深圳', '杭州'), 'fee', ROUND(5 + RAND() * 15, 2)), 'payment', JSON_OBJECT('method', ELT(1 + FLOOR(RAND() * 3), 'alipay', 'wechat', 'card'), 'amount', ROUND(50 + RAND() * 950, 2)) ) ); SET i = i + 1; END WHILE;END$$DELIMITER ;-- 开始生成（这个可能要跑几分钟，去泡杯咖啡吧）CALL generate_orders();-- 验证数据量SELECT COUNT(*) FROM orders;SELECT order_data FROM orders LIMIT 2;数据生成后，咱们测试下MySQL的查询性能：
-- 典型查询：查找北京用户的支付宝订单SELECT order_id, user_id, order_data FROM orders WHERE JSON_EXTRACT(order_data, '$.shipping.city') = '北京' AND JSON_EXTRACT(order_data, '$.payment.method') = 'alipay'LIMIT 10;-- 查看执行计划（重点！）EXPLAIN ANALYZE SELECT COUNT(*) FROM orders WHERE JSON_EXTRACT(order_data, '$.payment.amount') > 500;
不出意外的话，你会看到"Full Table Scan"和可怕的时间消耗。我这边测试500万数据跑了12秒，CPU直接拉满。
现在开始PostgreSQL的部分。记住，设计思维要转变：不要为了迁移而迁移，要利用新特性重构。
-- PostgreSQL目标库CREATE DATABASE order_system;c order_system;-- 订单表（JSONB版本）CREATE TABLE orders ( order_id BIGINT PRIMARY KEY, user_id INT NOT NULL, order_data JSONB NOT NULL, -- 注意是JSONB不是JSON created_at TIMESTAMPTZ DEFAULT NOW());-- 创建GIN索引（这才是灵魂！）CREATE INDEX idx_order_data_gin ON orders USING GIN (order_data);-- 创建特定路径的GIN索引（更精准的优化）CREATE INDEX idx_order_city ON orders USING GIN ((order_data -> 'shipping' -> 'city'));CREATE INDEX idx_payment_method ON orders USING GIN ((order_data -> 'payment' -> 'method'));-- 还可以创建B-tree索引在常用字段上CREATE INDEX idx_user_id ON orders(user_id);CREATE INDEX idx_created_at ON orders(created_at);-- 表结构说明COMMENT ON TABLE orders IS '订单主表，使用JSONB存储扩展属性';COMMENT ON INDEX idx_order_data_gin IS 'GIN索引支持任意JSONB路径查询';
关键设计决策说明：
JSON vs JSONB：永远选JSONB。JSON类型只是带验证的text，JSONB才是二进制高性能版本。GIN索引：这是全文检索和JSON查询的核心。它不像B-tree那样精确匹配，而是创建倒排索引，支持@>, ?等操作符。函数式索引：order_data -> 'shipping' -> 'city'这种索引，专门针对高频查询路径，性能堪比普通列。数据迁移不是简单的INSERT ... SELECT，要考虑字符集、时区、数据类型转换。我写了个Python脚本，带断点续传和错误处理：
#!/usr/bin/env python3"""MySQL到PostgreSQL的JSON数据迁移脚本支持断点续传、进度显示、错误重试"""import mysql.connectorimport psycopg2import jsonfrom datetime import datetimefrom tqdm import tqdm # 进度条库# 配置MYSQL_CONFIG = { 'host': 'localhost', 'port': 3306, 'user': 'root', 'password': 'root123', 'database': 'order_system', 'charset': 'utf8mb4'}PG_CONFIG = { 'host': 'localhost', 'port': 5432, 'user': 'postgres', 'password': 'root123', 'database': 'order_system'}def migrate_orders(batch_size=5000, resume_from=0): """ 迁移订单数据，带进度条和断点续传 参数: batch_size: 每批处理的记录数 resume_from: 从哪个order_id开始（断点续传用） """ # 连接MySQL mysql_conn = mysql.connector.connect(**MYSQL_CONFIG) mysql_cursor = mysql_conn.cursor(dictionary=True) # 连接PostgreSQL pg_conn = psycopg2.connect(**PG_CONFIG) pg_cursor = pg_conn.cursor() try: # 统计总数 mysql_cursor.execute("SELECT COUNT(*) as total FROM orders WHERE order_id >= %s", (resume_from,)) total = mysql_cursor.fetchone()['total'] print(f"[{datetime.now()}] 开始迁移数据，总计 {total} 条...") # 分批查询和插入 offset = 0 pbar = tqdm(total=total, desc="迁移进度") while True: # 查询一批MySQL数据 query = """ SELECT order_id, user_id, order_data, created_at FROM orders WHERE order_id >= %s ORDER BY order_id ASC LIMIT %s """ mysql_cursor.execute(query, (resume_from + offset, batch_size)) rows = mysql_cursor.fetchall() if not rows: break # 转换并插入PostgreSQL for row in rows: # MySQL的JSON字段返回的是字符串，需要确认是否为有效JSON try: # 验证并格式化JSON（确保是标准JSON） order_data = json.loads(row['order_data']) # 解析 order_data_jsonb = json.dumps(order_data, ensure_ascii=False) # 重新序列化 # 插入PostgreSQL pg_cursor.execute( """ INSERT INTO orders (order_id, user_id, order_data, created_at) VALUES (%s, %s, %s::jsonb, %s) ON CONFLICT (order_id) DO NOTHING -- 避免重复 """, (row['order_id'], row['user_id'], order_data_jsonb, row['created_at']) ) except json.JSONDecodeError as e: print(f"警告: order_id={row['order_id']} JSON解析失败: {e}") continue # 提交事务 pg_conn.commit() # 更新进度 pbar.update(len(rows)) offset += batch_size # 打印状态 if offset % 50000 == 0: print(f"[{datetime.now()}] 已迁移 {offset} 条...") pbar.close() print(f"[{datetime.now()}] 迁移完成！") except Exception as e: print(f"迁移出错: {e}") pg_conn.rollback() raise finally: mysql_cursor.close() mysql_conn.close() pg_cursor.close() pg_conn.close()def verify_migration(): """验证数据一致性""" mysql_conn = mysql.connector.connect(**MYSQL_CONFIG) pg_conn = psycopg2.connect(**PG_CONFIG) try: mysql_cursor = mysql_conn.cursor() pg_cursor = pg_conn.cursor() # 统计对比 mysql_cursor.execute("SELECT COUNT(*) FROM orders") mysql_count = mysql_cursor.fetchone()[0] pg_cursor.execute("SELECT COUNT(*) FROM orders") pg_count = pg_cursor.fetchone()[0] print(f"数据验证:") print(f" MySQL源数据: {mysql_count} 条") print(f" PostgreSQL目标数据: {pg_count} 条") print(f" 一致性: {'✓' if mysql_count == pg_count else '✗'}") # 随机抽样验证 pg_cursor.execute(""" SELECT order_id, order_data->>'order_id' as json_order_id FROM orders ORDER BY RANDOM() LIMIT 5 """) print("抽样验证JSON数据解析:") for row in pg_cursor.fetchall(): print(f" order_id={row[0]}, JSON解析order_id={row[1]}") finally: mysql_conn.close() pg_conn.close()if __name__ == '__main__': # 执行迁移 migrate_orders(batch_size=5000, resume_from=0) # 验证数据 verify_migration()代码细节解释：
断点续传机制：通过resume_from参数和ON CONFLICT DO NOTHING，即使迁移中断也能安全重跑。批处理优化：batch_size=5000是经验值，太大内存压力高，太小网络开销大。JSON验证：json.loads()再json.dumps()看似多余，但能确保非法JSON被过滤，避免PostgreSQL导入失败。字符集处理：MySQL用utf8mb4，PostgreSQL用UTF8，中间用Python统一转换。进度显示：tqdm库让漫长的迁移过程可视化，否则你会以为程序卡死了。跑完这个脚本，500万数据大概需要10-15分钟，取决于你的机器性能。
Ghiblio
专业AI吉卜力风格转换平台，将生活照变身吉卜力风格照
157
查看详情
迁移完数据只是开始，真正的挑战是改写业务代码。MySQL和PostgreSQL的JSON查询语法天壤之别。
场景：查询北京用户的订单
数据库 |
查询语句 |
性能 |
可读性 |
|---|---|---|---|
MySQL |
|
全表扫描，慢 |
差 |
PostgreSQL |
|
GIN索引扫描，快 |
好 |
-- PostgreSQL版本，体验飞一般的感觉EXPLAIN (ANALYZE, BUFFERS)SELECT order_id, user_id, order_data FROM orders WHERE order_data @> '{"shipping": {"city": "北京"}}' -- GIN索引神器 AND order_data -> 'payment' ->> 'method' = 'alipay'LIMIT 10;-- 执行计划会显示：-- Bitmap Heap Scan on orders ...-- Recheck Cond: (order_data @> '{"shipping": {"city": "北京"}}'::jsonb)-- Buffers: shared hit=45 read=12-- 只需要扫描57个buffer！运算符解释：
->：获取JSON对象的值，返回JSONB类型->>：获取JSON对象的值，返回TEXT类型（常用）@>：包含操作符，GIN索引的绝配?：检查键是否存在#>：按路径获取，如order_data #> '{shipping, city}'现实业务中的JSON往往是地狱级嵌套。比如要查：购买金额>500且包含SKU123商品的订单。
-- MySQL版本（噩梦）SELECT * FROM orders WHERE JSON_EXTRACT(order_data, '$.payment.amount') > 500 AND JSON_CONTAINS( order_data->'$.items', JSON_OBJECT('sku', 'SKU123') );-- PostgreSQL版本（清晰）SELECT order_id, user_id, order_dataFROM orders WHERE (order_data -> 'payment' ->> 'amount')::DECIMAL > 500 AND order_data @> '{"items": [{"sku": "SKU123"}]}';-- 更复杂的：查找items数组中任意商品价格>1000的订单SELECT order_id, item ->> 'sku' as sku, (item ->> 'price')::DECIMAL as priceFROM orders, jsonb_array_elements(order_data -> 'items') as itemWHERE (item ->> 'price')::DECIMAL > 1000;-- 这个用了jsonb_array_elements函数展开数组，然后对展开后的数据过滤-- 性能依然很棒，因为GIN索引会先过滤候选集做报表时，经常要对JSON字段做聚合。MySQL这里简直是灾难。
-- 统计各城市的订单数和平均金额-- MySQL版本（性能爆炸）SELECT JSON_EXTRACT(order_data, '$.shipping.city') as city, COUNT(*) as order_count, *G(JSON_EXTRACT(order_data, '$.payment.amount')) as *g_amountFROM orders GROUP BY JSON_EXTRACT(order_data, '$.shipping.city');-- PostgreSQL版本（毫秒级响应）SELECT order_data -> 'shipping' ->> 'city' as city, COUNT(*) as order_count, *G((order_data -> 'payment' ->> 'amount')::DECIMAL) as *g_amountFROM orders GROUP BY order_data -> 'shipping' ->> 'city';-- 更高级的：统计每个城市各支付方式占比WITH city_payments AS ( SELECT order_data -> 'shipping' ->> 'city' as city, order_data -> 'payment' ->> 'method' as payment_method, COUNT(*) as cnt FROM orders GROUP BY 1, 2)SELECT city, payment_method, cnt, ROUND(cnt * 100.0 / SUM(cnt) OVER (PARTITION BY city), 2) as percentageFROM city_paymentsORDER BY city, cnt DESC;-- 这个查询在MySQL里基本写不出来，就算写出来也跑不动
性能对比我见过最夸张的案例：MySQL跑了8分钟的报表查询，PostgreSQL 1.2秒搞定。当时老板以为我在吹牛，直到我把执行计划甩他脸上。
光会用不行，还得会用巧。GIN索引不是银弹，设计不好照样慢。
-- 模式1：对整个JSONB字段建GIN索引（最通用）CREATE INDEX idx_order_data_gin ON orders USING GIN (order_data);-- 优点：支持任意路径查询-- 缺点：索引体积大，写入慢-- 模式2：对特定路径建GIN索引（精准优化）CREATE INDEX idx_order_city ON orders USING GIN ((order_data -> 'shipping' -> 'city'));-- 优点：索引小，查询极快-- 缺点：只支持这个路径-- 模式3：多列复合GIN索引CREATE INDEX idx_order_city_payment ON orders USING GIN ( (order_data -> 'shipping' -> 'city'), (order_data -> 'payment' -> 'method'));-- 优点：覆盖常见组合查询-- 缺点：维护成本高-- 模式4：GIN + B-tree混合（终极方案）CREATE INDEX idx_order_city_btree ON orders USING BTREE ((order_data ->> 'shipping.city'));CREATE INDEX idx_order_payment_gin ON orders USING GIN (order_data);-- 优点：精确匹配用BTREE，模糊查询用GIN-- 缺点：需要分析查询模式
![[PostgreSQL]避开MySQL JSON查询陷阱：PostgreSQL算法应用指南](https://img.php.cn/upload/article/001/503/042/176475115259925.jpg)
实战案例：我们有个订单系统，90%的查询都带城市和时间范围。最终索引方案是：
-- 1. 时间范围用BTREE（范围查询最擅长）CREATE INDEX idx_created_at ON orders(created_at);-- 2. 城市用函数式BTREE索引（等值查询）CREATE INDEX idx_shipping_city ON orders USING BTREE ((order_data ->> 'shipping.city'));-- 3. 其他JSON路径用GIN（应对不可预知的需求）CREATE INDEX idx_order_data_gin ON orders USING GIN (order_data);-- 4. 联合索引应对特定报表CREATE INDEX idx_user_city_date ON orders USING BTREE (user_id, (order_data ->> 'shipping.city'), created_at);
这个组合索引策略上线后，我们的P99查询延迟从800ms降到40ms。但代价是写入性能下降了15%，所以索引不是越多越好，得权衡。
索引建完不是一劳永逸，需要持续监控：
-- 查看索引大小SELECT indexname, pg_size_pretty(pg_relation_size(indexrelid)) as index_size, idx_scan as index_scansFROM pg_stat_user_indexes WHERE tablename = 'orders'ORDER BY pg_relation_size(indexrelid) DESC;-- 清理未使用的索引（季度维护）-- 注意：idx_scan=0不代表没用，可能是季度报表才用-- GIN索引膨胀处理（VACUUM不能解决GIN膨胀）-- 需要定期重建REINDEX INDEX idx_order_data_gin;
血泪教训：有次发现数据库磁盘占用疯涨，查了半天是GIN索引膨胀。原因是业务高峰期大量写入，GIN索引的posting list没来得及合并。后来在低峰期加了定时REINDEX任务，问题解决。
既然都迁移到PostgreSQL了，不用点独门绝技怎么行？
这个功能杀疯了。比如订单备注里搜关键词，同时匹配JSON字段：
-- 假设order_data里有个notes字段存用户备注CREATE INDEX idx_notes_fulltext ON orders USING GIN ( to_tsvector('english', order_data ->> 'notes') || to_tsvector('simple', order_data -> 'items' ->> 'sku'));-- 搜索包含"urgent"且SKU包含"phone"的订单SELECT order_id, order_data ->> 'notes' as notes, item ->> 'sku' as skuFROM orders, jsonb_array_elements(order_data -> 'items') as itemWHERE to_tsvector('english', order_data ->> 'notes') @@ to_tsquery('urgent') AND item ->> 'sku' LIKE '%phone%';对于报表类查询，物化视图是神器：
-- 创建物化视图：每日城市销售统计CREATE MATERIALIZED VIEW daily_city_sales ASSELECT DATE(created_at) as sale_date, order_data -> 'shipping' ->> 'city' as city, COUNT(*) as order_count, SUM((order_data -> 'payment' ->> 'amount')::DECIMAL) as total_amountFROM orders GROUP BY 1, 2;-- 创建索引CREATE UNIQUE INDEX ON daily_city_sales(sale_date, city);CREATE INDEX ON daily_city_sales(sale_date);-- 刷新策略-- 手动刷新：REFRESH MATERIALIZED VIEW CONCURRENTLY daily_city_sales;-- 自动刷新：用pg_cron插件定时任务-- 查询飞快SELECT * FROM daily_city_sales WHERE sale_date = CURRENT_DATE - 1ORDER BY total_amount DESC;
数据量过亿后，即使GIN索引也扛不住。这时候需要分区：
-- 按月份分区（PostgreSQL 11+ 支持声明式分区）CREATE TABLE orders_partitioned ( order_id BIGINT NOT NULL, user_id INT NOT NULL, order_data JSONB NOT NULL, created_at TIMESTAMPTZ NOT NULL) PARTITION BY RANGE (created_at);-- 创建分区CREATE TABLE orders_y2025m01 PARTITION OF orders_partitioned FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');CREATE TABLE orders_y2025m02 PARTITION OF orders_partitioned FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');-- ...按需创建后续分区-- 索引也建在每个分区上CREATE INDEX ON orders_y2025m01 USING GIN (order_data);CREATE INDEX ON orders_y2025m01 (created_at);-- 查询自动路由到分区EXPLAIN SELECT * FROM orders_partitioned WHERE created_at >= '2025-01-15' AND created_at < '2025-01-20';-- 会看到只扫描orders_y2025m01分区说点真实的，迁移过程不是一帆风顺，我踩过的坑能写本书。
坑点 |
MySQL表现 |
PostgreSQL差异 |
解决方案 |
|---|---|---|---|
BOOLEAN |
tinyint(1) |
真BOOL类型 |
迁移脚本转换 |
TIMESTAMP |
无无时区 |
timestamptz带时区 |
统一用TIMESTAMPTZ |
自增ID |
AUTO_INCREMENT |
SERIAL/BIGSERIAL |
用identity列 |
JSON NULL |
JSON字段存null字符串 |
区分NULL和'null' |
清洗数据 |
# 数据清洗示例：处理MySQL的JSON null问题def clean_mysql_json(mysql_json_str): """MySQL里经常有{"key": null}存成字符串的情况""" if mysql_json_str is None: return None # 解析JSON try: data = json.loads(mysql_json_str) except
: return None # 递归清理null字符串 def clean_nulls(obj): if isinstance(obj, dict): return {k: clean_nulls(v) for k, v in obj.items() if v != "null"} elif isinstance(obj, list): return [clean_nulls(item) for item in obj if item != "null"] else: return obj cleaned = clean_nulls(data) return json.dumps(cleaned, ensure_ascii=False)这个坑隐藏得深。MySQL的自动提交和PostgreSQL默认不一样：
-- MySQL习惯（隐式提交）INSERT INTO logs VALUES (1, 'test'); -- 自动提交-- PostgreSQL（默认事务）BEGIN;INSERT INTO logs VALUES (1, 'test'); -- 没提交！-- 必须COMMIT; 或者设置自动提交-- 应用层需要调整# Python psycopg2需要conn.autocommit = True # 模拟MySQL行为# 或者conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
迁移后我们发现，高峰期写入性能反而下降了。查了才发现，GIN索引的锁粒度问题：
-- 解决方案1：调整填充因子CREATE INDEX idx_order_data_gin ON orders USING GIN (order_data) WITH (fillfactor = 70);-- 留更多空间减少页分裂-- 解决方案2：异步写入-- 应用层先写入到暂存表（无索引），再用后台任务批量转移到主表CREATE TABLE orders_staging (LIKE orders);-- 暂存表不加任何索引，写入飞快-- 解决方案3：分区表降低锁竞争-- 每个分区独立索引，写入分散
迁移完成后，必须做压测，否则就是裸奔上线。
: return None # 递归清理null字符串 def clean_nulls(obj): if isinstance(obj, dict): return {k: clean_nulls(v) for k, v in obj.items() if v != "null"} elif isinstance(obj, list): return [clean_nulls(item) for item in obj if item != "null"] else: return obj cleaned = clean_nulls(data) return json.dumps(cleaned, ensure_ascii=False)# 安装pgbench（PostgreSQL自带）pgbench -i -s 100 order_system # 初始化测试数据# 自定义脚本测试JSON查询cat > json_test.sql <<EOF\set city random(1, 4)SELECT count(*) FROM orders WHERE order_data @> jsonb_build_object('shipping', jsonb_build_object('city', CASE :city WHEN 1 THEN '北京' WHEN 2 THEN '上海' WHEN 3 THEN '深圳' ELSE '杭州' END));EOF# 执行压测pgbench -c 50 -j 4 -T 60 -f json_test.sql order_system-- postgresql.conf关键参数shared_buffers = 4GB # 设为内存的25%effective_cache_size = 12GB # 设为内存的75%work_mem = 64MB # 复杂JSON查询需要更大maintenance_work_mem = 512MB # 索引构建用random_page_cost = 1.1 # SSD设为1.1，机械盘保持4effective_io_concurrency = 200 # SSD调高max_worker_processes = 8 # 并行查询max_parallel_workers_per_gather = 4 # 单个查询并行度max_parallel_workers = 8 # 总并行工作进程-- JSONB特定优化gin_pending_list_limit = 4MB # GIN索引缓冲，写入密集调大
-- 监控GIN索引扫描效率SELECT schemaname, tablename, indexname, idx_scan as scans, idx_tup_read as tuples_read, idx_tup_fetch as tuples_fetched, ROUND(idx_tup_fetch::NUMERIC / NULLIF(idx_tup_read, 0), 2) as hit_ratioFROM pg_stat_user_indexes WHERE indexname LIKE 'idx_order%'ORDER BY scans DESC;-- 监控JSON函数调用耗时SELECT userid, dbid, queryid, query, mean_exec_time as *g_time_ms, callsFROM pg_stat_statementsWHERE query LIKE '%jsonb%' OR query LIKE '%->>%'ORDER BY mean_exec_time DESC LIMIT 20;
真实案例：压测时发现CPU还是高，查pg_stat_statements发现有个查询用了order_data::text LIKE '%phone%'，导致GIN索引失效。改成order_data @@ '{"items": [{"sku": "phone"}]}'后，QPS从200提升到2800。
MySQL转PostgreSQL函数对照表：
MySQL函数 |
PostgreSQL等价 |
备注 |
|---|---|---|
JSON_EXTRACT() |
->, ->>, #> |
操作符更直观 |
JSON_UNQUOTE() |
->> |
自动解引用 |
JSON_CONTAINS() |
@>, |
包含操作符 |
JSON_LENGTH() |
jsonb_array_length() |
数组长度 |
JSON_KEYS() |
jsonb_object_keys() |
遍历键 |
JSON_SEARCH() |
无直接等价 |
用全文检索替代 |
常用查询模板：
-- 检查键是否存在WHERE order_data ? 'shipping'-- 数组包含WHERE order_data -> 'items' ? 'SKU123'-- 范围查询WHERE (order_data -> 'payment' ->> 'amount')::DECIMAL BETWEEN 100 AND 1000-- 模糊匹配（配合全文检索）WHERE to_tsvector('simple', order_data ->> 'notes') @@ to_tsquery('fast & delivery')以上就是[PostgreSQL]避开MySQL JSON查询陷阱：PostgreSQL算法应用指南的详细内容，更多请关注其它相关文章！
# 敦煌网站建设文案模板
# 断点续传
# 设为
# 数据结构
# 重构
# 的是
# 运算符
# 肇庆全网推广 营销中心
# 铝合金seo优化选哪家
# 分区表
# 非凡教育seo
# 商洛网站优化哪个公司好
# 长宁网站优化选哪家
# 流量运营中的seo
# 网站优化的细节
# 网站推广坚定a金脉科技放心
# 雄县网站优化多少钱
# 工具
# word
# python
# js
# json
# go
# docker
# app
# 电商平台
# mysql
# ai
# 路由
# 数据清洗
# 区
# 关键词
# 北京
# 有个
相关栏目：
【
Google疑问12 】
【
Facebook疑问10 】
【
优化推广96088 】
【
技术知识133117 】
【
IDC资讯59369 】
【
网络运营7196 】
【
IT资讯61894 】
相关推荐：
人工智能“Aria”现身 Opera浏览器100版本更新：新功能“标签岛”
Meta发布音频AI模型，仅需2秒片段模拟真人语音
意大利警察拟用AI预测犯罪 该算法被指种族歧视严重
农业产业升级：AI驱动的“崃·见田”开启农田未来展望
对艺术家拒绝置若罔闻，Stability AI 将推出适应多种画风的开源模型
九号公司主导制定短途交通和送物机器人领域首个国际标准，标志着零的突破发布
智能公司为何纷纷投身机器人领域？
LinkedIn 推出生成式 AI 辅助撰写帖文功能，将向所有用户开放
《自然》杂志拒绝刊登人工智能生成的图片和视频
华为大模型登Nature正刊！审稿人：让人们重新审视预报模型的未来
AI创作广告文案等同2.47年工作经验，且消费者无法区分｜AI营销前沿
再度重仓 AI 赛道，SaaS 巨头 Salesforce 扩大 AIGC 风投基金规模
AI数字人业务频频获点赞，谦寻积极引领示范作用
阿里云全面支持Llama2训练部署，助力企业快速构建自有大型模型
国内首家，360智脑通过中国信通院可信AIGC大语言模型功能评估
微软bing聊天推出AI购物工具 可进行比价并查看历史最低价
谷歌推出 AI 反洗钱工具，可将金融机构内部风险预警准确率提高2至4倍
日媒关注中国推进鸟类识别 AI 普及，除监测保护外还可预防传染性疾病
MetaGPT AI 模型开源：可模拟软件公司开发过程，生成高质量代码
兆讯传媒率先全面拥抱AI 数智广告内容焕发新生机
AI进军债券交易，BondGPT来了！
「电子果蝇」惊动马斯克！背后是13万神经元全脑图谱，可在电脑上运行
吉林首例！机器人辅助下搭桥手术成功实施
OpenAI 向所有付费 API 用户开放 GPT-4
IBM将模拟计算用于人工智能，重塑AI计算
英伟达首席执行官黄仁勋：生成式 AI 时代「人类」会是新的编程语言
对话式论文阅读工具PaperMate上线，综述细节AI告诉你
Meta推出VR订阅服务Quest +：每月免费玩两款游戏，7.99美元/月
昌吉市利用无人机实现全天候河道动态巡检
视觉中国推出AI灵感绘图功能，付费后可在“合法合规前提下使用”
2025世界人工智能大会成功召开
陈根教授：离人形机器人时代还有10年吗？
美的推出 AI 双视精准避障的自动集尘扫拖机器人 V12，售价仅为2999元
特斯拉人形机器人将亮相 预计售价不超过15万元
AI与5G的强强联合：唤醒数字时代的无尽潜能
DeepMind推惊世排序算法，C++库忙更新！
世界上第一个完全由人工智能驱动的图像编辑器！
先进技术在防止全球数据丢失方面的作用
发布最新版本的 PICO OS 5.7.0：支持VR头盔录屏并跨平台分享至微信
Meta 开源 AI 语言模型 MusicGen，可将文本和旋律转化为完整乐曲
国内阅读行业首款对话式AI应用“阅爱聊”封闭内测
AI生成会议纪要 百度如流升级推出超级助手、智能编码等功能
对Hugging Face开源模型精准投毒！LLM切脑后变身PoisonGPT，用虚假事实洗脑60亿人
真全息产品，亮相深圳文博会——dipal数伴拓展元宇宙非沉浸式体验
美图吴欣鸿：希望更多人用上AI时代的影像生产力工具
美图发布国内首个“懂美学的”AI视觉大模型MiracleVision
Nature发AIGC禁令！投稿中视觉内容使用AI的概不接收
上影节直击 | AI技术降低了短片拍摄门槛？金爵奖评委不赞同
如何获得元宇宙的第一个属于自己的空间
人工智能自己玩自己
2025-12-03
运城市盐湖区信雨科技有限公司是一家深耕海外推广领域十年的专业服务商，作为谷歌推广与Facebook广告全球合作伙伴，聚焦外贸企业出海痛点，以数字化营销为核心，提供一站式海外营销解决方案。公司凭借十年行业沉淀与平台官方资源加持，打破传统外贸获客壁垒，助力企业高效开拓全球市场，成为中小企业出海的可靠合作伙伴。