如何在MySQL中使用觸發警報來監控資料庫效能？

CREATE TABLE performance_monitor (
    id INT AUTO_INCREMENT PRIMARY KEY,
    monitor_time DATETIME NOT NULL,
    database_name VARCHAR(50) NOT NULL,
    query_text TEXT,
    execution_time INT,
    row_count INT
);

DELIMITER $$
CREATE TRIGGER monitor_select_query
AFTER SELECT ON your_table
FOR EACH ROW
BEGIN
    DECLARE execution_time INT;
    DECLARE start_time DATETIME;
    DECLARE end_time DATETIME;
    
    SET start_time = NOW();
    
    -- 执行查询语句
    SELECT * FROM your_table;
    
    SET end_time = NOW();
    SET execution_time = TIMESTAMPDIFF(MICROSECOND, start_time, end_time);
    
    -- 将性能监控信息插入表中
    INSERT INTO performance_monitor (monitor_time, database_name, query_text, execution_time, row_count)
    VALUES (NOW(), DATABASE(), 'SELECT * FROM your_table', execution_time, (SELECT COUNT(*) FROM your_table));
END$$
DELIMITER ;

DELIMITER $$
CREATE EVENT check_performance_event
ON SCHEDULE EVERY 1 MINUTE
DO
BEGIN
    DECLARE execution_time INT;
    
    -- 查询性能监控表中的数据
    SELECT execution_time INTO execution_time
    FROM performance_monitor
    WHERE execution_time > 500;
    
    -- 发送邮件报警
    IF execution_time IS NOT NULL THEN
        -- 发送邮件的代码替换成实际的邮件报警逻辑
        SELECT CONCAT('执行时间超过500毫秒的查询数量：', COUNT(*)) INTO @msg
        FROM performance_monitor
        WHERE execution_time > 500;
        
        SET @subject = 'MySQL性能报警';
        SET @recipients = 'admin@example.com';
        SET @body = CONCAT('性能监控发现以下问题：', @msg);
        
        CALL send_email(@subject, @recipients, @body);
    END IF;
END$$
DELIMITER ;