用子查询代替游标实现多关联表插入

此程序用于复制房产数据,包括栋座、单元、楼层和房屋信息。通过事务处理确保数据的一致性和完整性,并根据不同的需求选择性地复制单元、楼层及房屋数据。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

 

/**************************************************************************
程序名称: [HM].[pBuildingNosCopyBuildingNo]
功能描述: 插入BNMDAT表一条数据
参数说明: 参数名称     数据类型   输入/输出   字段说明
   bnm_id      bigint         编号
   bnm_com_id     bigint         公司编号
   bnm_bdm_id     bigint         楼盘编号
   bnm_no      nvarchar(16)       栋座
   bnm_unit_check    int          单元
   bnm_floor_check    int          楼层
   bnm_house_check    int          房屋
   bnm_new_usr_id    bigint         新建用户编号         
返回值: ret       int
   0=成功;1=已存在
变更历史: 作者  日期  版本  变更说明
   黄杨  2009-02-01 V1.0  创建
   王红亮 2009-04-02 V1.2  加事务,去游标
**************************************************************************/
ALTER PROCEDURE [HM].[pBuildingNosCopyBuildingNo]
(
 @bnm_id      bigint,
 @bnm_com_id     bigint,
 @bnm_bdm_id     bigint,
 @bnm_no      nvarchar(16),
 @bnm_unit_check    int,
 @bnm_floor_check   int,
 @bnm_house_check   int,
 @bnm_new_usr_id    bigint
)
AS
 DECLARE @system_datetime datetime;  --系统时间
 DECLARE @new_bnm_id   bigint;   --新栋座编号
 DECLARE @bum_id    bigint;   --单元编号
 DECLARE @new_bum_id   bigint;   --新单元编号
 DECLARE @bfm_floor1   nvarchar(16); --标号楼层1
 DECLARE @bfm_floor2   nvarchar(16); --标号楼层2
 DECLARE @bhm_low_bfm_id  bigint;   --楼层编号1
 DECLARE @bhm_high_bfm_id bigint;   --楼层编号2
 DECLARE @bhm_id    bigint;   --房屋编号 
BEGIN
 SET @system_datetime = GETDATE();  --获取系统时间
 SET @new_bnm_id   = 0;    --初始化新栋座编号
 SET @bum_id    = 0;    --初始化单元编号
 SET @new_bum_id   = 0;    --初始化新单元编号
 SET @bfm_floor1   = '';    --初始化标号楼层1
 SET @bfm_floor2   = '';    --初始化标号楼层2
 SET @bhm_low_bfm_id  = 0;    --初始化楼层编号1
 SET @bhm_high_bfm_id = 0;    --初始化楼层编号2
 SET @bhm_id    = 0;    --初始化房屋编号
--插入栋座
 IF EXISTS ( SELECT 1
    FROM HM.BNMDAT
    WHERE bnm_bdm_id  = @bnm_bdm_id
    AND  bnm_com_id  = @bnm_com_id
    AND  bnm_no   = @bnm_no
    )
  RETURN 1 --Key Duplicate

 IF NOT EXISTS ( SELECT 1
      FROM HM.BDMMAS
      WHERE bdm_id = @bnm_bdm_id
     )
  RETURN 9 --楼盘编号不存在
  
 BEGIN TRAN  --事务开始

  INSERT
  INTO HM.BNMDAT 
    (
     bnm_com_id,
     bnm_bdm_id,
     bnm_bdm_name,
     bnm_no,
     bnm_build_year,
     bnm_build_area,
     bnm_towards,
     bnm_up_floor_cnt,
     bnm_down_floor_cnt,
     bnm_pro_sol_id,
     bnm_pro_sol_name,
     bnm_unit_define_type,
     bnm_fact_unit_cnt,
     bnm_memo,
     bnm_status,
     bnm_new_usr_id,
     bnm_unit_cnt,
     bnm_new_time
    )
  SELECT  A.bnm_com_id,
     A.bnm_bdm_id,
     A.bnm_bdm_name,
     @bnm_no,
     A.bnm_build_year,
     A.bnm_build_area,
     A.bnm_towards,
     A.bnm_up_floor_cnt,
     A.bnm_down_floor_cnt,
     A.bnm_pro_sol_id,
     A.bnm_pro_sol_name,
     A.bnm_unit_define_type,
     A.bnm_fact_unit_cnt,
     A.bnm_memo,
     A.bnm_status,
     @bnm_new_usr_id,
     A.bnm_unit_cnt,
     @system_datetime
  FROM  HM.BNMDAT A   
  WHERE  A.bnm_id = @bnm_id

  IF @@ERROR <> 0
  BEGIN
   ROLLBACK TRAN --事务回滚
   COMMIT           --事务结束
   RETURN -11 --返回错误
  END

  SET @new_bnm_id = @@IDENTITY;  --获取新栋座编号
  --新增成功后修改楼盘字典表中的总栋数
  UPDATE HM.BDMMAS
  SET  bdm_no_cnt = bdm_no_cnt +1
  WHERE bdm_id = @bnm_bdm_id;

  IF @@ERROR <> 0
  BEGIN
   ROLLBACK TRAN --事务回滚
   COMMIT           --事务结束
   RETURN -10 --返回错误
  END
  
  --插入单元
  IF @bnm_unit_check = 1
  BEGIN

    INSERT 
    INTO HM.BUMDAT
      (
       bum_com_id,
       bum_bdm_id,
       bum_bnm_id,
       bum_unit,
       bum_up_floor_cnt,
       bum_down_floor_cnt,
       bum_floor_define_type,
       bum_fact_floor_cnt,
       bum_house_cnt,
       bum_memo,
       bum_status,
       bum_new_usr_id,
       bum_floor_cnt,
       bum_new_time   
      )
    SELECT  B.bum_com_id,
       B.bum_bdm_id,
       @new_bnm_id,
       B.bum_unit,
       B.bum_up_floor_cnt,
       B.bum_down_floor_cnt,
       B.bum_floor_define_type,
       B.bum_fact_floor_cnt,
       B.bum_house_cnt,
       B.bum_memo,
       B.bum_status,
       @bnm_new_usr_id,
       B.bum_floor_cnt,
       @system_datetime 
    FROM  HM.BUMDAT B
    WHERE  B.bum_bnm_id  = @bnm_id

    IF @@ERROR <> 0
    BEGIN
     ROLLBACK TRAN --事务回滚
     COMMIT           --事务结束
     RETURN -12 --返回错误
    END


    --插入楼层
    IF @bnm_floor_check = 1
    BEGIN
     INSERT 
     INTO HM.BFMDAT
       (
        bfm_com_id,
        bfm_bdm_id,
        bfm_bnm_id,
        bfm_bum_id,
        bfm_floor,
        bfm_physics_floor,
        bfm_memo,
        bfm_exist,
        bfm_house_cnt,
        bfm_height,
        bfm_status,
        bfm_new_usr_id,
        bfm_new_time   
       )
     SELECT  C.bfm_com_id,
        C.bfm_bdm_id,
        @new_bnm_id,
        (SELECT bum_id FROM HM.BUMDAT WHERE bum_unit=(SELECT bum_unit FROM HM.BUMDAT WHERE bum_id=C.bfm_bum_id) and bum_bnm_id=@new_bnm_id) AS bfm_bum_id,       
        C.bfm_floor,
        C.bfm_physics_floor,
        C.bfm_memo,
        C.bfm_exist,
        C.bfm_house_cnt,
        C.bfm_height,
        C.bfm_status,
        @bnm_new_usr_id,
        @system_datetime
     FROM  HM.BFMDAT C
     WHERE  C.bfm_bnm_id = @bnm_id

     IF @@ERROR <> 0
     BEGIN
      ROLLBACK TRAN --事务回滚
      COMMIT           --事务结束
      RETURN -13 --返回错误
     END

     --插入房屋
     IF @bnm_house_check = 1
     BEGIN
       INSERT 
       INTO HM.BHMDAT 
         (
          bhm_bum_id,
          bhm_bdm_id,
          bhm_bnm_id,
          bhm_com_id,
          bhm_low_bfm_id,
          bhm_high_bfm_id,
          bhm_layer,
          bhm_door_no,
          bhm_rooms,
          bhm_halls,
          bhm_toilets,
          bhm_kitchens,
          bhm_fitment_sol_id,
          bhm_build_area,
          bhm_use_area,
          bhm_towards,
          bhm_pro_sol_id,
          bhm_usage,
          bhm_structure_sol_id,
          bhm_memo,
          bhm_status,
          bhm_new_usr_id,
          bhm_new_time   
         )
       SELECT  
          (SELECT bum_id FROM HM.BUMDAT WHERE bum_unit=(SELECT bum_unit FROM HM.BUMDAT WHERE bum_id=F.bhm_bum_id) and bum_bnm_id=@new_bnm_id) AS bhm_bum_id, 
          F.bhm_bdm_id,
          @new_bnm_id,
          F.bhm_com_id,
          (SELECT bfm_id FROM HM.BFMDAT WHERE bfm_floor=(SELECT bfm_floor FROM HM.BFMDAT WHERE bfm_id=F.bhm_low_bfm_id ) and bfm_bnm_id=@new_bnm_id and bfm_bum_id=(SELECT bum_id FROM HM.BUMDAT WHERE bum_unit=(SELECT bum_unit FROM HM.BUMDAT WHERE bum_id=F.bhm_bum_id) and bum_bnm_id=@new_bnm_id)) AS bhm_low_bfm_id,
          (SELECT bfm_id FROM HM.BFMDAT WHERE bfm_floor=(SELECT bfm_floor FROM HM.BFMDAT WHERE bfm_id=F.bhm_high_bfm_id ) and bfm_bnm_id=@new_bnm_id and bfm_bum_id=(SELECT bum_id FROM HM.BUMDAT WHERE bum_unit=(SELECT bum_unit FROM HM.BUMDAT WHERE bum_id=F.bhm_bum_id) and bum_bnm_id=@new_bnm_id)) AS bhm_high_bfm_id,         
          F.bhm_layer,
          F.bhm_door_no,
          F.bhm_rooms,
          F.bhm_halls,
          F.bhm_toilets,
          F.bhm_kitchens,
          F.bhm_fitment_sol_id,
          F.bhm_build_area,
          F.bhm_use_area,
          F.bhm_towards,
          F.bhm_pro_sol_id,
          F.bhm_usage,
          F.bhm_structure_sol_id,
          F.bhm_memo,
          F.bhm_status,
          @bnm_new_usr_id,
          @system_datetime
       FROM  HM.BHMDAT F
       WHERE  F.bhm_bnm_id = @bnm_id

       IF @@ERROR <> 0
       BEGIN
        ROLLBACK TRAN --事务回滚
        COMMIT           --事务结束
        RETURN -14 --返回错误
       END
     END
    END
    ELSE
    BEGIN
     --如果不复制楼层,则采集楼层数置0
     UPDATE HM.BUMDAT
     SET  bum_floor_cnt = 0
     WHERE bum_id   = @new_bum_id

     IF @@ERROR <> 0
     BEGIN
      ROLLBACK TRAN --事务回滚
      COMMIT           --事务结束
      RETURN -12 --返回错误
     END
    END    
  END
  ELSE
  BEGIN
   --如果不复制单元,则采集单元数置0
   UPDATE HM.BNMDAT
   SET  bnm_unit_cnt = 0
   WHERE bnm_id   = @new_bnm_id

   IF @@ERROR <> 0
   BEGIN
    ROLLBACK TRAN --事务回滚
    COMMIT           --事务结束
    RETURN -11 --返回错误
   END
  END
  
 COMMIT  --事务结束 

 RETURN 0
END

 

 

 

 

<think>嗯,用户之前问了关于MySQL覆盖索引的使用,现在又接着问如果PHP不使用覆盖索引的话,如何优化查询。这看起来他们可能在开发中遇到了性能问题,特别是数据库查询方面的优化。用户可能已经尝试过覆盖索引,但可能在某些情况下无法使用覆盖索引,或者想知道其他优化方法。 首先,我需要确认用户对覆盖索引的理解是否正确。覆盖索引确实能显著提升查询性能,但有时候可能因为结构或查询需求的原因,无法创建合适的覆盖索引。比如,当查询需要返回很多列,或者索引的大小变得不经济时,用户可能不得不寻找其他优化手段。 接下来,用户的问题是如何在不使用覆盖索引的情况下优化PHP中的查询。这里需要从多个方面来考虑,包括数据库设计、查询语句优化、服务器配置、缓存机制以及代码层面的优化。 可能的优化方法包括: 1. **数据库索引优化**:即使不使用覆盖索引,确保查询用到了合适的索引还是很重要的。比如,确保WHERE条件中的列有索引,避免全扫描。 2. **查询语句优化**:减少不必要的字段查询,避免SELECT *,使用JOIN代替子查询,分页优化等。 3. **数据库结构优化**:比如分区、垂直分,减少单数据量。 4. **缓存策略**:使用内存缓存如Redis或Memcached,减少重复查询数据库的次数。 5. **批量操作和延迟写入**:合并多个写操作,减少数据库的写入压力。 6. **服务器和配置优化**:调整MySQL的配置参数,比如缓冲池大小,连接数等。 7. **代码逻辑优化**:减少循环中的查询次数,使用预处理语句,避免N+1查询问题。 此外,用户可能在PHP代码中执行了低效的数据库操作,例如在循环中执行查询,或者没有使用预处理语句导致SQL解析开销增加。需要检查代码是否存在这些情况,并给出相应的优化建议。 还需要考虑是否可以通过数据库本身的特性来优化,比如使用查询缓存(虽然MySQL 8.0已经移除了查询缓存),或者利用EXPLAIN分析查询计划,找出慢查询的原因。 用户可能没有提到具体的问题场景,所以需要给出一般性的建议,同时提醒他们在实际应用中需要根据具体情况进行分析和调整。比如,分页查询时使用LIMIT加偏移量可能导致性能问题,可以改用基于游标的分页;或者大查询时使用分区来分散数据存储。 另外,用户可能对PHP和MySQL的交互不太熟悉,比如使用持久连接还是普通连接,是否正确地关闭数据库连接,或者是否使用了ORM工具导致生成的SQL不够高效。这些都是潜在的优化点。 最后,需要强调优化是一个综合性的工作,需要结合监控工具(如慢查询日志、Performance Schema)来定位问题,逐步优化,而不是单一方法的解决。</think>在 PHP 中优化数据库查询时,即使无法使用覆盖索引,也可以通过以下方法提升性能。这些策略涵盖了 **数据库设计**、**查询优化**、**缓存机制**和 **代码逻辑** 等多个层面: --- ### **1. 数据库索引优化** 即使不使用覆盖索引,合理设计普通索引仍能显著提升查询效率: - **确保 WHERE 条件列有索引**: ```sql -- 例如查询 WHERE user_id = 100,需为 user_id 创建索引 CREATE INDEX idx_user_id ON orders(user_id); ``` - **利用最左前缀原则**: 对复合查询(如 `WHERE a = 1 AND b = 2`),优先为高频条件组合创建复合索引 `(a, b)`。 - **避免索引失效**: 如:避免对索引列使用函数(`WHERE YEAR(create_time) = 2023`)、范围查询后的列失效等。 --- ### **2. 查询语句优化** - **减少查询字段**: 避免 `SELECT *`,只选择必要字段,减少数据传输和内存占用。 ```php // 不推荐 $stmt = $pdo->query("SELECT * FROM users WHERE id = 100"); // 推荐 $stmt = $pdo->query("SELECT name, email FROM users WHERE id = 100"); ``` - **分页优化**: 避免 `LIMIT 100000, 10` 式的大偏移量分页,改用 **游标分页**(基于上一页的最大 ID): ```sql SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10; ``` - **避免复杂子查询**: 用 `JOIN` 替代子查询,尤其是关联大时: ```sql -- 不推荐 SELECT * FROM users WHERE id IN (SELECT user_id FROM orders); -- 推荐 SELECT users.* FROM users JOIN orders ON users.id = orders.user_id; ``` --- ### **3. 数据库结构优化** - **垂直分**: 将大的低频字段拆分到副,减少主体积。例如: ```sql -- 主存储核心字段 CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50), email VARCHAR(100) ); -- 副存储详情字段 CREATE TABLE user_profiles ( user_id INT, bio TEXT, address TEXT ); ``` - **分区(Partitioning)**: 对时间序列数据(如日志),按时间分区,缩小查询范围: ```sql CREATE TABLE logs ( id INT, log_time DATETIME, content TEXT ) PARTITION BY RANGE (YEAR(log_time)) ( PARTITION p2022 VALUES LESS THAN (2023), PARTITION p2023 VALUES LESS THAN (2024) ); ``` --- ### **4. 缓存机制** - **内存缓存(如 Redis/Memcached)**: 缓存高频查询结果,减少数据库压力: ```php $cacheKey = 'user_100_profile'; $profile = $redis->get($cacheKey); if (!$profile) { $profile = $db->query("SELECT * FROM user_profiles WHERE user_id = 100")->fetch(); $redis->setex($cacheKey, 3600, json_encode($profile)); // 缓存1小时 } ``` - **MySQL 查询缓存(MySQL 5.7 及以下)**: 启用 `query_cache_type`,但注意该特性在 MySQL 8.0 已移除。 --- ### **5. 批量操作延迟写入** - **批量插入(INSERT Batch)**: 合并多次插入为单条语句,减少网络和事务开销: ```php // 不推荐:循环插入 foreach ($data as $row) { $db->query("INSERT INTO logs (content) VALUES ('$row')"); } // 推荐:批量插入 $values = implode(",", array_map(fn($row) => "('$row')", $data)); $db->query("INSERT INTO logs (content) VALUES $values"); ``` - **异步写入**: 对非实时性数据(如日志),使用消息队列(如 RabbitMQ)延迟写入数据库。 --- ### **6. 服务器配置优化** - **调整 MySQL 配置**: 增加 `innodb_buffer_pool_size`(缓存池大小)、优化 `max_connections`(连接数限制)。 - **启用慢查询日志**: 定位并优化执行缓慢的 SQL: ```ini # my.cnf 配置 slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow.log long_query_time = 2 ``` --- ### **7. 代码逻辑优化** - **避免 N+1 查询**: 使用 `JOIN` 或 `WHERE IN` 合并关联查询: ```php // 不推荐:循环中查询 $users = $db->query("SELECT * FROM users LIMIT 10"); foreach ($users as $user) { $orders = $db->query("SELECT * FROM orders WHERE user_id = {$user['id']}"); } // 推荐:一次性查询 $userIds = implode(",", array_column($users, 'id')); $orders = $db->query("SELECT * FROM orders WHERE user_id IN ($userIds)"); ``` - **预处理语句(Prepared Statements)**: 防止 SQL 注入,同时提升重复查询性能: ```php $stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?"); $stmt->execute([100]); ``` --- ### **总结** 即使无法使用覆盖索引,仍可通过 **合理设计索引**、**精简查询语句**、**分库分**、**缓存加速** 和 **代码逻辑优化** 等手段提升性能。核心原则是:**减少不必要的数据传输、降低磁盘 I/O、利用缓存和批处理**。实际应用中需结合 `EXPLAIN` 分析查询计划,针对性优化瓶颈。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值