-- 查询用户最近的订单
SELECT * FROM orders 
WHERE user_id = 12345 
ORDER BY created_at DESC 
LIMIT 10;

-- 执行时间：32.5 秒
-- 扫描行数：50,000,000

CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL
);

-- 5000 万条记录
-- 无索引（除了主键）

-- 常见查询 1：按用户查询订单
SELECT * FROM orders WHERE user_id = ? ORDER BY created_at DESC LIMIT 10;

-- 常见查询 2：按状态查询
SELECT * FROM orders WHERE status = 'pending' AND created_at > DATE_SUB(NOW(), INTERVAL 7 DAY);

-- 常见查询 3：按产品和时间查询
SELECT COUNT(*), SUM(amount) FROM orders 
WHERE product_id = ? AND created_at BETWEEN ? AND ?;

-- 常见查询 4：复合条件查询
SELECT * FROM orders 
WHERE user_id = ? AND status = 'completed' 
ORDER BY amount DESC LIMIT 20;

-- 索引 1：用户 + 时间（覆盖查询 1）
CREATE INDEX idx_user_created ON orders(user_id, created_at DESC);

-- 索引 2：状态 + 时间（覆盖查询 2）
CREATE INDEX idx_status_created ON orders(status, created_at);

-- 索引 3：产品 + 时间（覆盖查询 3）
CREATE INDEX idx_product_created ON orders(product_id, created_at);

-- 索引 4：用户 + 状态 + 金额（覆盖查询 4）
CREATE INDEX idx_user_status_amount ON orders(user_id, status, amount DESC);

-- 查询 1 优化后
EXPLAIN SELECT * FROM orders 
WHERE user_id = 12345 
ORDER BY created_at DESC 
LIMIT 10;

-- 结果：
-- type: ref
-- possible_keys: idx_user_created
-- key: idx_user_created
-- rows: 10（之前是 50,000,000）
-- Extra: Using index condition
-- 执行时间：0.003 秒

-- 索引：(a, b, c)
CREATE INDEX idx_abc ON table(a, b, c);

-- ✅ 可以使用索引
SELECT * FROM table WHERE a = 1;
SELECT * FROM table WHERE a = 1 AND b = 2;
SELECT * FROM table WHERE a = 1 AND b = 2 AND c = 3;

-- ❌ 无法使用索引
SELECT * FROM table WHERE b = 2;
SELECT * FROM table WHERE c = 3;
SELECT * FROM table WHERE b = 2 AND c = 3;

-- 假设：
-- user_id 有 100 万个不同值
-- status 只有 5 个不同值

-- ✅ 好的索引
CREATE INDEX idx_user_status ON orders(user_id, status);

-- ❌ 差的索引
CREATE INDEX idx_status_user ON orders(status, user_id);

-- 如果查询只需要索引中的列，不需要回表

-- 创建覆盖索引
CREATE INDEX idx_user_amount ON orders(user_id, amount, created_at);

-- 这个查询只需要扫描索引
SELECT user_id, amount, created_at 
FROM orders 
WHERE user_id = 12345 
ORDER BY created_at DESC 
LIMIT 10;

-- Extra: Using index（覆盖索引）

-- ❌ 索引失效的情况

-- 1. 函数操作
SELECT * FROM orders WHERE YEAR(created_at) = 2026;
-- 改为：
SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

-- 2. 隐式类型转换
SELECT * FROM orders WHERE user_id = '12345';  -- user_id 是 BIGINT
-- 改为：
SELECT * FROM orders WHERE user_id = 12345;

-- 3. LIKE 以通配符开头
SELECT * FROM orders WHERE product_name LIKE '%phone%';
-- 无法使用索引，考虑全文索引

-- 4. OR 条件
SELECT * FROM orders WHERE user_id = 123 OR status = 'pending';
-- 改为 UNION：
SELECT * FROM orders WHERE user_id = 123
UNION
SELECT * FROM orders WHERE status = 'pending';

-- 只索引活跃用户的订单
CREATE INDEX idx_active_orders ON orders(user_id, created_at)
WHERE status != 'archived';

-- 索引更小，查询更快

-- 索引日期的年月
CREATE INDEX idx_orders_year_month ON orders(EXTRACT(YEAR FROM created_at), EXTRACT(MONTH FROM created_at));

-- 查询时可以使用
SELECT * FROM orders 
WHERE EXTRACT(YEAR FROM created_at) = 2026 
AND EXTRACT(MONTH FROM created_at) = 7;

-- MySQL 全文索引
ALTER TABLE products ADD FULLTEXT INDEX ft_name_desc(name, description);

-- 查询
SELECT * FROM products 
WHERE MATCH(name, description) AGAINST('iPhone 15' IN BOOLEAN MODE);

-- PostgreSQL 全文索引
CREATE INDEX idx_products_search ON products 
USING gin(to_tsvector('english', name || ' ' || description));

-- 查询
SELECT * FROM products 
WHERE to_tsvector('english', name || ' ' || description) @@ to_tsquery('iphone & 15');

-- MySQL JSON 索引
ALTER TABLE orders ADD INDEX idx_metadata 
((CAST(metadata->>'$.category' AS CHAR(50))));

-- 查询
SELECT * FROM orders 
WHERE metadata->>'$.category' = 'electronics';

-- PostgreSQL JSONB 索引
CREATE INDEX idx_orders_metadata ON orders USING gin(metadata);

-- 查询
SELECT * FROM orders 
WHERE metadata @> '{"category": "electronics"}';

-- MySQL：查看索引使用情况
SELECT * FROM sys.schema_unused_indexes 
WHERE object_schema = 'mydb';

-- PostgreSQL：查看索引使用情况
SELECT schemaname, tablename, indexname, idx_scan 
FROM pg_stat_user_indexes 
ORDER BY idx_scan ASC;

-- 索引占用空间但不被使用
DROP INDEX idx_unused ON orders;

-- MySQL：优化表
OPTIMIZE TABLE orders;

-- PostgreSQL：重建索引
REINDEX INDEX idx_user_created;