EXPLAIN SELECT * FROM users WHERE username = 'alice';

-- 查看执行计划
EXPLAIN SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id, u.name;

-- MySQL 8.0+ 可以查看实际执行统计
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 100;

从最差到最优：
ALL         → 全表扫描（必须优化）
index       → 全索引扫描（比 ALL 稍好）
range       → 索引范围扫描（WHERE id > 100）
ref         → 非唯一索引查找（WHERE username = 'alice'）
eq_ref      → 唯一索引查找（JOIN 主键）
const       → 常量查找（WHERE id = 1，最快）
system      → 系统表（几乎不出现）

-- 1. 为 WHERE、JOIN、ORDER BY 常用字段建索引
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_order_user_created ON orders(user_id, created_at);

-- 2. 覆盖索引：查询字段都在索引中，无需回表
-- 如果经常执行 SELECT id, user_id, created_at FROM orders WHERE user_id = ?
CREATE INDEX idx_order_covering ON orders(user_id, created_at, id);

-- 3. 前缀索引：对长字符串字段只索引前 N 个字符
CREATE INDEX idx_user_name_prefix ON users(username(10));

-- 4. 联合索引遵循最左前缀原则
CREATE INDEX idx_abc ON table_name(a, b, c);
-- 可以命中: WHERE a=1 | WHERE a=1 AND b=2 | WHERE a=1 AND b=2 AND c=3
-- 无法命中: WHERE b=2 | WHERE c=3 | WHERE b=2 AND c=3

-- 5. 查看表的索引使用情况
SHOW INDEX FROM orders;

-- 6. 查看索引基数（基数越高，区分度越好）
SELECT
    INDEX_NAME,
    COLUMN_NAME,
    CARDINALITY
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_NAME = 'orders';

-- ❌ 导致全表扫描的写法

-- 1. 对索引列使用函数
SELECT * FROM orders WHERE YEAR(created_at) = 2024;
-- ✅ 改写为范围查询
SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- 2. 对索引列做运算
SELECT * FROM orders WHERE id + 1 = 100;
-- ✅ 改写为
SELECT * FROM orders WHERE id = 99;

-- 3. 隐式类型转换（phone 是 VARCHAR，传入 INT）
SELECT * FROM users WHERE phone = 13800138000;
-- ✅ 改写为
SELECT * FROM users WHERE phone = '13800138000';

-- 4. LIKE 以通配符开头
SELECT * FROM users WHERE name LIKE '%alice%';
-- ✅ 如果必须模糊搜索，考虑全文索引
ALTER TABLE users ADD FULLTEXT INDEX ft_name(name);
SELECT * FROM users WHERE MATCH(name) AGAINST('alice' IN BOOLEAN MODE);

-- 5. OR 条件可能导致索引失效
SELECT * FROM users WHERE status = 1 OR age > 25;
-- ✅ 改用 UNION
SELECT * FROM users WHERE status = 1
UNION
SELECT * FROM users WHERE age > 25;

-- 6. NOT IN / NOT EXISTS 可能导致全表扫描
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders);
-- ✅ 改用 LEFT JOIN + IS NULL
SELECT u.* FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE o.id IS NULL;

-- 1. 用 EXISTS 替代 IN（大数据量时更高效）
-- 慢
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);
-- 快
SELECT * FROM users u WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 1000
);

-- 2. 用 UNION ALL 替代 UNION（不需要去重时）
-- UNION 会去重排序，UNION ALL 不会
SELECT name FROM users_2023 UNION ALL SELECT name FROM users_2024;

-- 3. 避免 SELECT *，只查需要的列
-- 慢
SELECT * FROM orders WHERE user_id = 1;
-- 快
SELECT id, order_no, total_amount, status FROM orders WHERE user_id = 1;

-- 4. 分页优化（深度分页问题）
-- 慢（OFFSET 100000 需要扫描前 100100 行）
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 100000;
-- 快（游标分页，记住上一页最后一条的 id）
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;

-- 5. 批量操作替代循环操作
-- 慢（循环插入）
INSERT INTO logs (msg) VALUES ('a');
INSERT INTO logs (msg) VALUES ('b');
INSERT INTO logs (msg) VALUES ('c');
-- 快（批量插入）
INSERT INTO logs (msg) VALUES ('a'), ('b'), ('c');

-- 6. 避免在 WHERE 子句中对索引列使用 <> 或 !=
SELECT * FROM users WHERE status != 0;
-- ✅ 如果只有少量状态值，改写为
SELECT * FROM users WHERE status IN (1, 2, 3);

-- 查看慢查询日志配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 超过1秒记录
SET GLOBAL log_queries_not_using_indexes = 'ON';  -- 记录未使用索引的查询

-- 分析慢查询日志
-- 使用 mysqldumpslow 工具
-- mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

-- 使用 pt-query-digest（Percona Toolkit）
-- pt-query-digest /var/log/mysql/slow.log

-- EXPLAIN 基本用法
EXPLAIN SELECT ...;
EXPLAIN ANALYZE SELECT ...;  -- MySQL 8.0+

-- 查看查询执行时间
SET profiling = 1;
SELECT ...;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

-- 查看索引
SHOW INDEX FROM table_name;

-- 强制使用指定索引
SELECT * FROM orders FORCE INDEX (idx_user_id) WHERE user_id = 100;

-- 忽略索引（用于对比测试）
SELECT * FROM orders IGNORE INDEX (idx_user_id) WHERE user_id = 100;

-- MySQL / PostgreSQL / SQLite
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 20;

-- SQL Server
SELECT * FROM orders ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
-- SQL Server 2012 之前
SELECT TOP 10 * FROM orders WHERE id NOT IN (SELECT TOP 20 id FROM orders ORDER BY id);

-- Oracle 12c+
SELECT * FROM orders ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
-- Oracle 12c 之前
SELECT * FROM (SELECT o.*, ROWNUM rn FROM orders o WHERE ROWNUM <= 30) WHERE rn > 20;

-- MySQL
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50));
-- 或使用默认值
INSERT INTO users (name) VALUES ('Alice');  -- id 自动生成

-- PostgreSQL
CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(50));
-- PostgreSQL 10+
CREATE TABLE users (id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR(50));

-- SQLite
CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);
-- SQLite 也可用 ROWID
INSERT INTO users (name) VALUES ('Alice');

-- SQL Server
CREATE TABLE users (id INT IDENTITY(1,1) PRIMARY KEY, name NVARCHAR(50));

-- Oracle
CREATE TABLE users (id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR2(50));

-- 字符串拼接
SELECT CONCAT('Hello', ' ', 'World');           -- MySQL, PostgreSQL
SELECT 'Hello' || ' ' || 'World';                -- PostgreSQL, SQLite, Oracle
SELECT name + ' ' + email FROM users;            -- SQL Server

-- 截取子串
SELECT SUBSTRING('Hello World', 1, 5);           -- MySQL, SQL Server
SELECT SUBSTR('Hello World', 1, 5);              -- PostgreSQL, SQLite, Oracle

-- 字符串长度
SELECT LENGTH('Hello');                           -- MySQL, PostgreSQL, SQLite
SELECT LEN('Hello');                              -- SQL Server

-- 大小写转换
SELECT UPPER('hello'), LOWER('HELLO');           -- 所有数据库通用
SELECT UCASE('hello'), LCASE('HELLO');           -- MySQL 额外支持

-- 去除空格
SELECT TRIM('  Hello  ');                         -- 所有数据库通用
SELECT LTRIM('  Hello'), RTRIM('Hello  ');       -- MySQL, SQL Server, PostgreSQL

-- 替换
SELECT REPLACE('Hello World', 'World', 'SQL');   -- 所有数据库通用

-- 当前时间
SELECT NOW();                                     -- MySQL, PostgreSQL
SELECT CURRENT_TIMESTAMP;                         -- 所有数据库通用
SELECT GETDATE();                                 -- SQL Server
SELECT datetime('now');                           -- SQLite

-- 日期加减
SELECT DATE_ADD('2024-01-01', INTERVAL 30 DAY);  -- MySQL
SELECT '2024-01-01'::DATE + INTERVAL '30 days';  -- PostgreSQL
SELECT DATEADD(DAY, 30, '2024-01-01');            -- SQL Server
SELECT date('2024-01-01', '+30 days');             -- SQLite

-- 提取年/月/日
SELECT YEAR(created_at), MONTH(created_at), DAY(created_at) FROM orders;  -- MySQL, SQL Server
SELECT EXTRACT(YEAR FROM created_at) FROM orders;                          -- PostgreSQL, MySQL 8.0+
SELECT strftime('%Y', created_at) FROM orders;                             -- SQLite

-- 日期格式化
SELECT DATE_FORMAT(created_at, '%Y-%m-%d') FROM orders;                    -- MySQL
SELECT TO_CHAR(created_at, 'YYYY-MM-DD') FROM orders;                      -- PostgreSQL, Oracle
SELECT FORMAT(created_at, 'yyyy-MM-dd') FROM orders;                       -- SQL Server
SELECT strftime('%Y-%m-%d', created_at) FROM orders;                       -- SQLite

-- IF 表达式
SELECT IF(score >= 60, '及格', '不及格') FROM exams;                       -- MySQL
SELECT IIF(score >= 60, '及格', '不及格') FROM exams;                      -- SQL Server
SELECT CASE WHEN score >= 60 THEN '及格' ELSE '不及格' END FROM exams;    -- 所有数据库通用

-- COALESCE（返回第一个非 NULL 值）
SELECT COALESCE(nickname, username, '匿名') FROM users;                    -- 所有数据库通用

-- NULLIF（相等时返回 NULL）
SELECT NULLIF(a, b);                                                        -- 所有数据库通用

-- MySQL: 无原生 BOOLEAN，TINYINT(1) 代替
CREATE TABLE users (is_active TINYINT(1) DEFAULT 1);
SELECT * FROM users WHERE is_active = TRUE;  -- TRUE 等于 1

-- PostgreSQL: 原生 BOOLEAN
CREATE TABLE users (is_active BOOLEAN DEFAULT TRUE);
SELECT * FROM users WHERE is_active = TRUE;

-- SQLite: 无原生 BOOLEAN，用 INTEGER
CREATE TABLE users (is_active INTEGER DEFAULT 1);
SELECT * FROM users WHERE is_active = 1;

-- SQL Server: 原生 BIT
CREATE TABLE users (is_active BIT DEFAULT 1);
SELECT * FROM users WHERE is_active = 1;

-- MySQL
INSERT INTO stats (article_id, view_count) VALUES (1, 1)
ON DUPLICATE KEY UPDATE view_count = view_count + 1;

-- PostgreSQL
INSERT INTO stats (article_id, view_count) VALUES (1, 1)
ON CONFLICT (article_id) DO UPDATE SET view_count = stats.view_count + 1;

-- SQLite
INSERT INTO stats (article_id, view_count) VALUES (1, 1)
ON CONFLICT(article_id) DO UPDATE SET view_count = view_count + 1;

-- SQL Server
MERGE INTO stats AS target
USING (SELECT 1 AS article_id, 1 AS view_count) AS source
ON target.article_id = source.article_id
WHEN MATCHED THEN UPDATE SET view_count = target.view_count + 1
WHEN NOT MATCHED THEN INSERT (article_id, view_count) VALUES (source.article_id, source.view_count);

-- 所有主流数据库都支持（MySQL 8.0+, PostgreSQL, SQL Server, SQLite 3.25+）
SELECT
    name,
    score,
    RANK() OVER (ORDER BY score DESC) AS ranking,
    ROW_NUMBER() OVER (PARTITION BY class_id ORDER BY score DESC) AS class_rank
FROM students;

-- MySQL 5.7 及以下不支持窗口函数，需用变量模拟
-- GROUP_CONCAT / STRING_AGG
SELECT category_id, GROUP_CONCAT(name SEPARATOR ',') FROM products GROUP BY category_id;  -- MySQL
SELECT category_id, STRING_AGG(name, ',') FROM products GROUP BY category_id;             -- PostgreSQL

-- 查询最近30天每个用户的订单总金额（假设 users 表 10万行，orders 表 100万行）
EXPLAIN
SELECT u.name, u.email, SUM(o.total_amount) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY u.id, u.name, u.email
ORDER BY total_spent DESC
LIMIT 20;

-- 1. 为 orders 表添加联合索引
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);

-- 2. 优化查询：先过滤再关联
SELECT u.name, u.email, sub.total_spent
FROM users u
INNER JOIN (
    SELECT user_id, SUM(total_amount) AS total_spent
    FROM orders
    WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
    GROUP BY user_id
    ORDER BY total_spent DESC
    LIMIT 20
) sub ON u.id = sub.user_id
ORDER BY sub.total_spent DESC;

-- 3. 再次 EXPLAIN 确认优化效果
EXPLAIN SELECT u.name, u.email, sub.total_spent ...
-- type: ref, rows: 大幅减少

-- 查询第10000页（每页20条），OFFSET 200000
SELECT id, title, created_at
FROM articles
WHERE status = 1
ORDER BY created_at DESC
LIMIT 20 OFFSET 200000;
-- 即使有索引，也需要扫描 200020 行，非常慢

-- 记住上一页最后一条的 created_at 和 id
-- 假设上一页最后一条: created_at='2024-03-15 10:30:00', id=50001
SELECT id, title, created_at
FROM articles
WHERE status = 1
  AND (created_at < '2024-03-15 10:30:00'
       OR (created_at = '2024-03-15 10:30:00' AND id < 50001))
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- 只需扫描20行，极快

-- 先查主键，再关联取完整数据
SELECT a.id, a.title, a.created_at
FROM articles a
INNER JOIN (
    SELECT id FROM articles
    WHERE status = 1
    ORDER BY created_at DESC
    LIMIT 20 OFFSET 200000
) b ON a.id = b.id;
-- 子查询走覆盖索引，主查询按主键取数据

-- 如果允许，限制最大翻页深度
-- 只允许查看前1000条结果，提示用户使用搜索缩小范围
SELECT id, title, created_at
FROM articles
WHERE status = 1
  AND category_id = 5  -- 增加筛选条件
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;

-- 原始查询：多条件筛选 + 排序 + 分页
SELECT p.id, p.name, p.price, p.sales_count, c.name AS category_name
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
WHERE p.status = 1
  AND p.category_id IN (10, 11, 12, 13)
  AND p.price BETWEEN 50 AND 500
ORDER BY p.sales_count DESC
LIMIT 20;

-- 优化：
-- 1. 创建覆盖主要筛选条件的联合索引
CREATE INDEX idx_product_filter ON products(status, category_id, price, sales_count);

-- 2. 如果 EXPLAIN 显示 filesort，调整索引顺序
CREATE INDEX idx_product_sort ON products(status, category_id, sales_count DESC, price);

-- 3. 如果分类表很小，可以冗余分类名到商品表
ALTER TABLE products ADD COLUMN category_name VARCHAR(50);
-- INSERT/UPDATE 时同步维护

-- 原始查询：按月统计订单数据（数据量大，每次查询都很慢）
SELECT
    DATE_FORMAT(created_at, '%Y-%m') AS month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue
FROM orders
WHERE created_at >= '2023-01-01'
GROUP BY month
ORDER BY month;

-- 优化方案一：预计算汇总表
CREATE TABLE monthly_order_stats (
    month_key VARCHAR(7) PRIMARY KEY COMMENT '格式: 2024-01',
    order_count INT NOT NULL DEFAULT 0,
    revenue DECIMAL(15,2) NOT NULL DEFAULT 0,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 定时任务每天更新（增量计算）
INSERT INTO monthly_order_stats (month_key, order_count, revenue)
SELECT
    DATE_FORMAT(created_at, '%Y-%m'),
    COUNT(*),
    SUM(total_amount)
FROM orders
WHERE created_at >= CURDATE() - INTERVAL 1 DAY
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
ON DUPLICATE KEY UPDATE
    order_count = VALUES(order_count),
    revenue = VALUES(revenue),
    updated_at = NOW();

-- 查询汇总表（毫秒级响应）
SELECT * FROM monthly_order_stats ORDER BY month_key;