SQL Glossary

WHERE order_date >= '2024-01-01'

WHERE YEAR(order_date) >= 2024

-- 查看最大允許連線數
SHOW VARIABLES LIKE 'max_connections';

-- 查看當前已使用的連線數
SHOW STATUS LIKE 'Threads_connected';

-- 查看歷史最大連線數
SHOW STATUS LIKE 'Max_used_connections';

-- 計算連線使用率
SELECT
  ROUND(100 * (A.variable_value / B.variable_value), 2) AS connection_usage_percentage
FROM
  (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Threads_connected') A,
  (SELECT variable_value FROM performance_schema.global_variables WHERE variable_name = 'max_connections') B;

-- 查看 InnoDB Buffer Pool 設置
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- 計算緩存命中率
SELECT
  ROUND(100 * (1 - (A.variable_value / B.variable_value)), 2) AS buffer_pool_hit_ratio
FROM
  (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_reads') A,
  (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_read_requests') B;

-- 查看查詢執行計劃
EXPLAIN SELECT * FROM table_name WHERE column_name = 'value';

-- 查看慢查詢日誌是否啟用
SHOW VARIABLES LIKE 'slow_query_log';

-- 查詢執行時間最長的 SQL 語句
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- 查詢所有數據庫的大小
SELECT table_schema AS "Database",
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.tables
GROUP BY table_schema;

-- 查詢單個表的大小
SELECT table_name AS "Table",
       ROUND((data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.tables
WHERE table_schema = 'your_database_name'
ORDER BY (data_length + index_length) DESC
LIMIT 10;

-- 查看當前 InnoDB 事務
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

-- 查看 InnoDB 鎖狀態
SHOW ENGINE INNODB STATUS;

-- 手動關閉某個連線
KILL thread_id;

-- 批量關閉超時連線（生成 KILL 命令）
SELECT CONCAT('KILL ',id,';') FROM information_schema.processlist
WHERE command='Sleep' AND time > 300;