SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'slow_query_log_file';

SET GLOBAL slow_query_log = ON; 
SET GLOBAL long_query_time = 1;

# 按執(zhí)行次數(shù)排序前10條
  mysqldumpslow -s c -t 10 /var/log/mysql/slow.log
  # 按總耗時排序前10條
  mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

EXPLAIN SELECT id, order_no 
FROM orders 
WHERE user_id = 100 AND create_time >= '2024-01-01' 
ORDER BY create_time DESC;

-- ? 不推薦 
SELECT * FROM users; 
-- ? 推薦 
SELECT id, name, email FROM users;

-- ? 索引失效 
SELECT * 
FROM orders 
WHERE YEAR(create_time) = 2024; 
-- ? 使用范圍查詢，可走索引 
SELECT * 
FROM orders 
WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';

EXPLAIN SELECT * FROM products WHERE category_id = 10;

-- ? 子查詢（可能低效） 
SELECT * 
FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE amount > 100); 
-- ? 改寫為 
JOIN SELECT DISTINCT u.* 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE o.amount > 100;

?
SELECT * FROM messages 
WHERE id > 100000 
ORDER BY id 
LIMIT 20;
?

-- 方法1：CREATE INDEX（推薦用于已有表） 
CREATE INDEX index_name ON table_name (column_name); 
-- 示例：在 users 表的 email 字段上創(chuàng)建索引 
CREATE INDEX idx_email ON users (email);

CREATE UNIQUE INDEX idx_username ON users (username);

-- 按順序：先按 category_id，再按 created_at 排序 
CREATE INDEX idx_category_created ON products (category_id, created_at);

CREATE TABLE orders (
	id BIGINT PRIMARY KEY AUTO_INCREMENT, 
	user_id INT NOT NULL, 
	status VARCHAR(20), 
	created_at DATETIME, 
	-- 主鍵自動創(chuàng)建聚簇索引（InnoDB） 
	INDEX idx_user_status (user_id, status), -- 普通復合索引 
	UNIQUE INDEX uk_order_no (order_no) -- 唯一索引 
	);

ALTER TABLE table_name ADD PRIMARY KEY (id);