DBA笔记-第四部分

-----函数部分练习

-- 创建一个函数来计算 1 到 100 的和
DELIMITER //

CREATE FUNCTION Sum1To100WithLoop() 
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE sum INT DEFAULT 0;
    DECLARE counter INT DEFAULT 1;

    -- 使用循环计算和
    WHILE counter <= 100 DO
        SET sum = sum + counter;
        SET counter = counter + 1;
    END WHILE;

    RETURN sum;
END //

DELIMITER ;

select Sum1To100WithLoop();

只能使用select 来查询函数 不是用call  ！

1.函数性能

 函数性能损耗举例，每行数据都需要执行一次函数计算，性能开销大，假设有一百万行数据，那么函数需要执行一百万次。

SELECT func(val);
SELECT * FROM tbl where col=func(val);·

函数优化建议

• 简化逻辑：尽量简化函数中的逻辑，避免复杂的计算或大量的循环。
• 减少查询：减少函数中对数据库的查询次数。如果可能，将查询操作集中到函数外部，或者使用缓存机制。
• 使用索引：确保函数中涉及到的表有适当的索引，以提高查询性能。
• 优化 SQL 语句：确保 SQL 语句的优化，避免不必要的复杂连接、子查询等。
• 函数体小：尽量使函数体保持小且高效，将复杂的逻辑拆分到外部存储过程或应用程序中处理。
• 避免非必要的计算：在函数中避免进行非必要的计算或处理，减少资源消耗。

-----触发器高级应用--物化视图

触发器是事务性的，具体表现在触发器执行失败时候会导致事务回滚，以保持数据库的一致性和完整性，为了保存数据且不想每次实时计算数据的情况可以使用触发器来将计算结果保存到创建好的表中。

-- 原始销售数据表
CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    salesperson_id INT,
    amount DECIMAL(10, 2),
    sale_date DATE
);

-- 物化视图表，存储每个销售员的总销售额
CREATE TABLE sales_summary (
    salesperson_id INT PRIMARY KEY,
    total_sales DECIMAL(10, 2)
);

插入触发器举例

DELIMITER //

CREATE TRIGGER after_sales_insert
AFTER INSERT ON sales
FOR EACH ROW
BEGIN
    -- 更新物化视图表
    INSERT INTO sales_summary (salesperson_id, total_sales)
    VALUES (NEW.salesperson_id, NEW.amount)
    ON DUPLICATE KEY UPDATE total_sales = total_sales + NEW.amount;
END //

DELIMITER ;

小知识：mysql视图不占任何空间，仅是一个定义