hong-red/sql-optimization-agent

1-- 某大型电商系统（高并发、千万级数据预估）工程审计案例2-- 目标：检测索引设计、存储引擎配置、高并发锁竞争等风险3 4CREATE TABLE `oms_order` (5  `order_id` char(36) NOT NULL, -- 风险：UUID作为主键，导致B+树索引分裂和随机IO6  `member_id` bigint NOT NULL,7  `coupon_id` bigint DEFAULT NULL,8  `order_sn` varchar(64) NOT NULL,9  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,10  `member_username` varchar(64) DEFAULT NULL, -- 风险：冗余存储用户名，存在一致性风险11  `total_amount` decimal(10,2) DEFAULT NULL,12  `pay_amount` decimal(10,2) DEFAULT NULL,13  `pay_type` int DEFAULT NULL, -- 1-支付宝，2-微信14  `status` int DEFAULT '0', -- 订单状态：0->待付款；1->待发货；2->已发货；3->已完成；4->已关闭；5->无效订单15  `delivery_company` varchar(64) DEFAULT NULL,16  `delivery_sn` varchar(64) DEFAULT NULL,17  `auto_confirm_day` int DEFAULT NULL,18  `receiver_name` varchar(100) NOT NULL,19  `receiver_phone` varchar(32) NOT NULL,20  `receiver_post_code` varchar(32) DEFAULT NULL,21  `receiver_province` varchar(32) DEFAULT NULL,22  `receiver_city` varchar(32) DEFAULT NULL,23  `receiver_region` varchar(32) DEFAULT NULL,24  `receiver_detail_address` varchar(200) DEFAULT NULL,25  `note` varchar(500) DEFAULT NULL,26  `confirm_status` int DEFAULT '0',27  `delete_status` int DEFAULT '0', -- 风险：软删除标记位，若无索引且数据量大，扫描性能极差28  `use_integration` int DEFAULT NULL,29  `payment_time` datetime DEFAULT NULL,30  `delivery_time` datetime DEFAULT NULL,31  `receive_time` datetime DEFAULT NULL,32  `comment_time` datetime DEFAULT NULL,33  `modify_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,34  `extra_info` json DEFAULT NULL, -- 风险：JSON大字段频繁更新会导致行溢出和Undo Log剧增35  PRIMARY KEY (`order_id`),36  UNIQUE KEY `idx_order_sn` (`order_sn`)37) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';38 39CREATE TABLE `oms_order_item` (40  `id` bigint NOT NULL AUTO_INCREMENT,41  `order_id` char(36) NOT NULL,42  `order_sn` varchar(64) NOT NULL,43  `product_id` bigint NOT NULL,44  `product_pic` varchar(500) DEFAULT NULL,45  `product_name` varchar(200) DEFAULT NULL,46  `product_brand` varchar(200) DEFAULT NULL,47  `product_sn` varchar(64) DEFAULT NULL,48  `product_price` decimal(10,2) DEFAULT NULL,49  `product_quantity` int DEFAULT NULL,50  `product_sku_id` bigint DEFAULT NULL,51  `product_sku_code` varchar(50) DEFAULT NULL,52  `product_category_id` bigint DEFAULT NULL,53  `promotion_name` varchar(200) DEFAULT NULL,54  `promotion_amount` decimal(10,2) DEFAULT NULL,55  `real_amount` decimal(10,2) DEFAULT NULL,56  `gift_integration` int DEFAULT '0',57  `gift_growth` int DEFAULT '0',58  PRIMARY KEY (`id`)59  -- 风险：缺少 order_id 的索引，查询订单明细时会触发全表扫描60) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单中所包含的商品';61