注意

本文原始碼為 **SQL Server (T-SQL)** 語法，非 MySQL。MySQL 5.0+ 可用 PROFILING 或 PERFORMANCE_SCHEMA 替代。

原始 T-SQL 程式碼

USE NBDXMIS;
GO

CREATE PROCEDURE TestTimeAnySentence
    @sql_where VARCHAR(8000)
AS
BEGIN
    DECLARE @ct DATETIME;
    SET @ct = GETDATE();

    DECLARE @newsql_where VARCHAR(8000);
    SET @newsql_where = @sql_where;

    EXEC(@newsql_where);

    SELECT DATEDIFF(MS, @ct, GETDATE()) AS '查詢時間(毫秒)';
END;
GO

-- 執行範例
EXEC TestTimeAnySentence 'SELECT * FROM 水費表 WHERE 費用ID < 200000';

MySQL 替代方案

方法一：使用 PROFILING (MySQL 5.0.37 - 8.0 移除)

SET profiling = 1;
SELECT * FROM large_table WHERE id < 200000;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

方法二：PERFORMANCE_SCHEMA (MySQL 5.6+ 推薦)

-- 確保啟用
UPDATE performance_schema.setup_instruments SET ENABLED='YES', TIMED='YES' WHERE NAME LIKE 'statement/%';
UPDATE performance_schema.setup_consumers SET ENABLED='YES' WHERE NAME LIKE 'events_statements%';

-- 執行查詢
SELECT * FROM large_table WHERE id < 200000;

-- 查看最近查詢耗時 (皮秒)
SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT/1000000000 AS avg_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY LAST_SEEN DESC LIMIT 5;

方法三：簡易計時 (應用層)

-- 在應用程式 (PHP/Python/Java) 中記錄微秒時間差
$start = microtime(true);
$pdo->query($sql);
$elapsed_ms = (microtime(true) - $start) * 1000;

方法四：資訊庫查詢最近慢查詢

SELECT sql_text, timer_wait/1000000000 AS elapsed_ms
FROM sys.statements_with_runtimes_in_95th_percentile
LIMIT 10;