mixvideo-v2/apps/desktop/DATABASE_OPTIMIZATION_GUIDE.md

数据库访问优化指南

🚨 问题描述

在多线程环境中，数据库连接锁竞争是常见问题，表现为：

• 程序卡在 conn.lock().unwrap()
• 死锁导致程序无响应
• 性能下降

🎯 优化方案

1. 使用 Database 辅助方法（推荐）

// ❌ 旧方式 - 容易造成锁竞争
pub fn get_photos(&self, model_id: &str) -> Result<Vec<ModelPhoto>> {
    let conn = self.database.get_connection();
    let conn = conn.lock().unwrap(); // 可能无限等待
    
    // 长时间持有锁进行数据库操作
    let mut stmt = conn.prepare("SELECT ...")?;
    // ...
}

// ✅ 新方式 - 使用 with_connection 辅助方法
pub fn get_photos(&self, model_id: &str) -> Result<Vec<ModelPhoto>> {
    self.database.with_connection(|conn| {
        let mut stmt = conn.prepare("SELECT ...")?;
        // 数据库操作
        Ok(photos)
    })
}

2. Database 辅助方法实现

impl Database {
    /// 执行数据库操作的辅助方法，自动处理锁的获取和释放
    pub fn with_connection<T, F>(&self, operation: F) -> Result<T, rusqlite::Error>
    where
        F: FnOnce(&Connection) -> Result<T, rusqlite::Error>,
    {
        match self.connection.try_lock() {
            Ok(conn) => {
                // 成功获取锁，执行操作
                operation(&*conn)
            },
            Err(_) => {
                // 锁被占用，等待一小段时间后重试
                std::thread::sleep(std::time::Duration::from_millis(10));
                
                // 使用阻塞方式获取锁，但有错误处理
                let conn = self.connection.lock().map_err(|e| {
                    eprintln!("数据库连接锁获取失败: {}", e);
                    rusqlite::Error::SqliteFailure(
                        rusqlite::ffi::Error::new(rusqlite::ffi::SQLITE_BUSY),
                        Some("数据库连接被锁定".to_string()),
                    )
                })?;
                
                operation(&*conn)
            }
        }
    }

    /// 执行只读查询
    pub fn query<T, F>(&self, operation: F) -> Result<T, rusqlite::Error>
    where
        F: FnOnce(&Connection) -> Result<T, rusqlite::Error>,
    {
        self.with_connection(operation)
    }

    /// 执行写入操作
    pub fn execute<T, F>(&self, operation: F) -> Result<T, rusqlite::Error>
    where
        F: FnOnce(&Connection) -> Result<T, rusqlite::Error>,
    {
        self.with_connection(operation)
    }
}

🔧 最佳实践

1. 缩小锁的作用域

// ❌ 锁持有时间过长
pub fn bad_example(&self) -> Result<()> {
    let conn = self.database.get_connection();
    let conn = conn.lock().unwrap();
    
    // 大量计算和处理
    let processed_data = expensive_computation();
    
    // 数据库操作
    conn.execute("INSERT ...", params)?;
    Ok(())
}

// ✅ 最小化锁持有时间
pub fn good_example(&self) -> Result<()> {
    // 在锁外进行计算
    let processed_data = expensive_computation();
    
    // 只在需要时持有锁
    self.database.execute(|conn| {
        conn.execute("INSERT ...", params)?;
        Ok(())
    })
}

2. 避免嵌套锁

// ❌ 可能导致死锁
pub fn bad_nested_locks(&self) -> Result<()> {
    let conn1 = self.database.get_connection();
    let _guard1 = conn1.lock().unwrap();
    
    // 在持有锁时调用其他可能需要锁的方法
    self.other_method_that_needs_lock()?;
    
    Ok(())
}

// ✅ 避免嵌套锁
pub fn good_no_nested_locks(&self) -> Result<()> {
    // 分别处理，避免嵌套
    let data = self.prepare_data()?;
    
    self.database.execute(|conn| {
        // 只在这里持有锁
        conn.execute("INSERT ...", params)?;
        Ok(())
    })
}

3. 使用事务优化批量操作

// ✅ 批量操作使用事务
pub fn batch_insert(&self, items: &[Item]) -> Result<()> {
    self.database.execute(|conn| {
        let tx = conn.transaction()?;
        
        for item in items {
            tx.execute("INSERT ...", params)?;
        }
        
        tx.commit()?;
        Ok(())
    })
}

4. 错误处理和超时

// ✅ 带超时的锁获取
pub fn with_timeout(&self) -> Result<()> {
    use std::time::{Duration, Instant};
    
    let start = Instant::now();
    let timeout = Duration::from_secs(5);
    
    loop {
        match self.database.connection.try_lock() {
            Ok(conn) => {
                // 成功获取锁
                return self.do_operation(&*conn);
            },
            Err(_) if start.elapsed() < timeout => {
                // 继续重试
                std::thread::sleep(Duration::from_millis(10));
                continue;
            },
            Err(_) => {
                // 超时
                return Err(rusqlite::Error::SqliteFailure(
                    rusqlite::ffi::Error::new(rusqlite::ffi::SQLITE_BUSY),
                    Some("数据库操作超时".to_string()),
                ));
            }
        }
    }
}

📊 性能监控

// 添加性能监控
pub fn monitored_operation(&self) -> Result<()> {
    let start = std::time::Instant::now();
    
    let result = self.database.with_connection(|conn| {
        // 数据库操作
        Ok(())
    });
    
    let duration = start.elapsed();
    if duration > std::time::Duration::from_millis(100) {
        eprintln!("慢查询警告: 操作耗时 {:?}", duration);
    }
    
    result
}

🎯 总结

1. 使用 with_connection 方法：自动处理锁的获取和释放
2. 最小化锁持有时间：只在必要时持有锁
3. 避免嵌套锁：防止死锁
4. 使用 try_lock：避免无限等待
5. 添加错误处理：优雅处理锁竞争
6. 使用事务：优化批量操作
7. 性能监控：识别慢查询

这些优化可以显著提高数据库访问的性能和稳定性，避免锁竞争问题。