一千萬個為什麽

搜索

在恒定時間內查找表的最小值和最大值

我有一個包含相對大數據的表,
所以下面的陳述需要太長時間:

SELECT MIN(column) FROM table WHERE ...
SELECT MAX(column) FROM table WHERE ...

我嘗試索引列,但性能仍然不足以滿足我的需要。

我還想過使用觸發器或事件在另一個表中緩存最小值和最大值 但是我的MySQL版本是5.0.51a,它需要SUPER權限才能觸發,並且不支持事件。

擁有SUPER權限或升級MySQL對我來說是不可能的 (如果可能的話,不需要問!)

如何在MySQL內部解決這個問題?
也就是說,沒有OS的幫助。

最佳答案

如果您的被編入索引,您應該立即找到 min(column),因為這是MySQL將找到的第一個值。
索引列上的 max(column)也是如此。

如果由於某種原因無法添加索引,則以下觸發器會將MIN和MAX值緩存在單獨的表中。

請註意,TRUE = 1且FALSE = 0。

DELIMITER $$

CREATE TRIGGER ai_table1_each AFTER INSERT ON table1 FOR EACH ROW
BEGIN
  UPDATE db_info i 
    SET i.minimum = LEAST(i.minimum, NEW.col) 
       ,i.maximum = GREATEST(i.maximum, NEW.col)
       ,i.min_count = (i.min_count * (new.col < i.minumum)) 
                      + (i.minimum = new.col) + (i.minimum < new.col)
       ,i.max_count = (i.max_count * (new.col > i.maximum))
                      + (i.maximum = new.col) + (new.col > i.maximum)
    WHERE i.tablename = 'table1'; 
END $$

CREATE TRIGGER ad_table1_each AFTER DELETE ON table1 FOR EACH ROW
BEGIN
  DECLARE new_min_count INTEGER;
  DECLARE new_max_count INTEGER;

  UPDATE db_info i 
    SET i.min_count = i.min_count - (i.minimum = old.col)
       ,i.max_count = i.max_count - (i.maximum = old.col)
    WHERE i.tablename = 'table1';
  SELECT i.min_count INTO new_min_count, i.max_count INTO new_max_count
    FROM db_info i
    WHERE i.tablename = 'table1';

  IF new_max_count = 0 THEN 
    UPDATE db_info i
    CROSS JOIN (SELECT MAX(col) as new_max FROM table1) m
      SET i.max_count = 1  
         ,i.maximum = m.new_max;
  END IF;
  IF new_min_count = 0 THEN
    UPDATE db_info i
    CROSS JOIN (SELECT MIN(col) as new_min FROM table1) m
      SET i.min_count = 1  
         ,i.minimum = m.new_min;
  END IF;
END $$

DELIMITER ;

更新後的 觸發器將是插入和刪除觸發器的混合。

轉載註明原文: 在恒定時間內查找表的最小值和最大值