-- 查看慢查詢?nèi)罩菊w開關(guān)
show variables like 'slow_query_log';
-- 查看慢查詢閾值（默認 10秒）
show variables like 'long_query_time';
-- 查看慢日志文件存放路徑
show variables like 'slow_query_log_file';

-- 1. 開啟慢查詢?nèi)罩?
set global slow_query_log = ON;

-- 2. 設(shè)置閾值：執(zhí)行超過 1秒 就記錄
set global long_query_time = 1;

-- 3. 可選：記錄沒有走索引的SQL
set global log_queries_not_using_indexes = ON;

[mysqld]
# 開啟慢查詢?nèi)罩?
slow_query_log = 1
# 慢日志文件路徑
slow_query_log_file = /var/log/mysql/slow.log
# 慢查詢時間閾值 1s
long_query_time = 1
# 記錄未使用索引的SQL
log_queries_not_using_indexes = 1
# 避免日志刷爆，限制每分鐘記錄條數(shù)
log_throttle_queries_not_using_indexes = 10

# CentOS
systemctl restart mysqld

# Ubuntu
systemctl restart mysql

-- `EXPLAIN` 是 MySQL 提供的**診斷命令**，
EXPLAIN SELECT * FROM orders WHERE user_id = 1001 AND create_time > '2025-01-01';

-- 給 user_id + create_time 建【聯(lián)合索引】
CREATE INDEX idx_user_id_create_time ON orders(user_id, create_time);

-- 會導(dǎo)致索引失效 不能對【索引列】做任何函數(shù)運算、四則運算、隱式轉(zhuǎn)換、后置模糊匹配
SELECT * FROM t WHERE YEAR(create_time) = 2025;
SELECT * FROM t WHERE id + 1 = 100;
SELECT * FROM t WHERE name LIKE '%張三';

SELECT * FROM t WHERE create_time >= '2025-01-01' AND create_time < '2026-01-01';
SELECT * FROM t WHERE id = 99;
SELECT * FROM t WHERE name LIKE '張三%';

SELECT * FROM big_table;  -- 無WHERE條件，全表掃描

-- 查看 InnoDB 底層實時狀態(tài)
SHOW ENGINE INNODB STATUS;

SHOW PROCESSLIST;  -- 查看正在執(zhí)行的SQL