SELECT COUNT(*) FROM users;

EXPLAIN SELECT COUNT(*) FROM users;

SELECT TABLE_ROWS 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'your_database' 
AND TABLE_NAME = 'users';

SHOW TABLE STATUS LIKE 'users';

-- 創(chuàng)建計數(shù)器表
CREATE TABLE table_counts (
    table_name VARCHAR(100) PRIMARY KEY,
    row_count BIGINT NOT NULL,
    last_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 創(chuàng)建觸發(fā)器自動更新計數(shù)
DELIMITER //
CREATE TRIGGER after_users_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO table_counts (table_name, row_count) 
    VALUES ('users', (SELECT COUNT(*) FROM users))
    ON DUPLICATE KEY UPDATE row_count = VALUES(row_count);
END//
DELIMITER ;

-- 類似創(chuàng)建UPDATE和DELETE觸發(fā)器

-- 替代方案：定期批量更新計數(shù)器
-- 例如在應(yīng)用啟動時或通過定時任務(wù)執(zhí)行
UPDATE table_counts SET row_count = (SELECT COUNT(*) FROM users), last_updated = NOW() 
WHERE table_name = 'users';

-- 確保統(tǒng)計信息已收集
ANALYZE TABLE users;

-- 然后查詢信息模式（比之前版本更準確）
SELECT TABLE_ROWS 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'your_database' 
AND TABLE_NAME = 'users';

-- 對于小表（<10萬行），直接COUNT(*)通常足夠快
SELECT COUNT(*) FROM small_table;

-- 快速獲取近似值
EXPLAIN SELECT COUNT(*) FROM large_table;

-- 或
SELECT TABLE_ROWS 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'db' AND TABLE_NAME = 'large_table';

-- 查詢精確計數(shù)器（毫秒級響應(yīng)）
SELECT row_count FROM table_counts WHERE table_name = 'huge_table';

-- 創(chuàng)建存儲過程批量獲取表狀態(tài)
DELIMITER //
CREATE PROCEDURE get_all_table_counts()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE db_name VARCHAR(100);
    DECLARE tbl_name VARCHAR(100);
    DECLARE cur CURSOR FOR 
        SELECT TABLE_SCHEMA, TABLE_NAME 
        FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_SCHEMA = 'your_database';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_table_counts (
        table_schema VARCHAR(100),
        table_name VARCHAR(100),
        row_count BIGINT,
        update_time TIMESTAMP
    );
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO db_name, tbl_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        INSERT INTO temp_table_counts
        SELECT 
            db_name AS table_schema,
            tbl_name AS table_name,
            TABLE_ROWS AS row_count,
            NOW() AS update_time
        FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_SCHEMA = db_name AND TABLE_NAME = tbl_name;
    END LOOP;
    
    CLOSE cur;
    
    SELECT * FROM temp_table_counts;
    DROP TEMPORARY TABLE temp_table_counts;
END//
DELIMITER ;

-- 調(diào)用存儲過程
CALL get_all_table_counts();

-- 測試1: COUNT(*)
SELECT SQL_NO_CACHE COUNT(*) FROM large_table;

-- 測試2: EXPLAIN
EXPLAIN SELECT COUNT(*) FROM large_table;

-- 測試3: INFORMATION_SCHEMA
SELECT TABLE_ROWS 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'test_db' AND TABLE_NAME = 'large_table';

-- 測試4: SHOW TABLE STATUS
SHOW TABLE STATUS LIKE 'large_table';

-- 利用主鍵索引的最小值查詢
SELECT 1 FROM users LIMIT 1;  -- 如果有數(shù)據(jù)返回1，否則空

-- 或者更精確的計數(shù)（如果表有自增ID且無刪除）
SELECT MAX(id) FROM users;  -- 近似行數(shù)（如果有刪除會不準確）

-- 假設(shè)按日期分區(qū)，只查詢最近分區(qū)的計數(shù)
SELECT COUNT(*) FROM users PARTITION (p202301);

-- 創(chuàng)建物化視圖（實際是普通表定期刷新）
CREATE TABLE users_count_mv (
    count_date DATE PRIMARY KEY,
    row_count BIGINT
);

-- 定期刷新數(shù)據(jù)
INSERT INTO users_count_mv (count_date, row_count)
SELECT CURRENT_DATE, COUNT(*) FROM users
ON DUPLICATE KEY UPDATE row_count = VALUES(row_count);

-- 低效：MySQL仍然需要計算所有匹配行
SELECT COUNT(*) FROM users WHERE status = 'active';