数据库索引深度解析：从B+树到查询优化器的完整指南

聚簇索引（主键）
           [30|50|80]
          /    |    \
    [10|20|30] [40|50] [60|70|80]
    ↓每个叶节点存完整行数据
    {id:10, name:Alice, age:25, ...}
    {id:20, name:Bob, age:30, ...}

-- 这个查询需要回表（先查name索引得到id，再查聚簇索引得到完整行）
SELECT * FROM users WHERE name = 'Alice';

-- 这个查询不需要回表（覆盖索引，索引中已包含所需字段）
SELECT id, name FROM users WHERE name = 'Alice';

-- 创建联合索引
CREATE INDEX idx_user_search ON users(city, age, gender);

-- 可以使用索引（满足最左前缀）
SELECT * FROM users WHERE city = 'Beijing';                    -- ✅
SELECT * FROM users WHERE city = 'Beijing' AND age = 25;       -- ✅
SELECT * FROM users WHERE city = 'Beijing' AND age > 20;       -- ✅ (city全匹配，age范围)
SELECT * FROM users WHERE city = 'Beijing' AND age = 25 AND gender = 'M'; -- ✅

-- 不能使用索引（跳过了最左列）
SELECT * FROM users WHERE age = 25;                            -- ❌
SELECT * FROM users WHERE age = 25 AND gender = 'M';           -- ❌

-- 只能用到city部分（age跳过后，gender无法用索引）
SELECT * FROM users WHERE city = 'Beijing' AND gender = 'M'; -- 只用city

-- 慢：需要回表（查询列*不在索引中）
SELECT * FROM orders WHERE user_id = 123;

-- 快：覆盖索引（所需列都在索引中）
SELECT id, status, created_at FROM orders WHERE user_id = 123;
-- 前提：有索引 INDEX(user_id, status, created_at) 或 INDEX(user_id) 且只需id

-- 专为覆盖索引优化的查询
ALTER TABLE orders ADD INDEX idx_cover(user_id, status, created_at, id);

-- 以下查询无法使用name列上的索引（函数破坏索引）
SELECT * FROM users WHERE LOWER(name) = 'alice';
SELECT * FROM users WHERE DATE(created_at) = '2024-01-01';

-- 解决方案1：应用层处理
SELECT * FROM users WHERE name = 'Alice';  -- 查询前统一大小写

-- 解决方案2：函数索引（MySQL 8.0+, PostgreSQL）
ALTER TABLE users ADD INDEX idx_name_lower ((LOWER(name)));
SELECT * FROM users WHERE LOWER(name) = 'alice';  -- 现在可以用索引

-- 解决方案3：生成列
ALTER TABLE users ADD COLUMN name_lower VARCHAR(100) 
  GENERATED ALWAYS AS (LOWER(name)) STORED;
ALTER TABLE users ADD INDEX idx_name_lower(name_lower);

-- PostgreSQL：只对未完成的订单建索引（大部分订单是已完成的，只需加速未完成订单查询）
CREATE INDEX idx_pending_orders ON orders(created_at)
WHERE status IN ('PENDING', 'PROCESSING');

-- MySQL的等价方案（用虚拟列）
ALTER TABLE orders ADD COLUMN is_active TINYINT(1) 
  GENERATED ALWAYS AS (status IN ('PENDING', 'PROCESSING'));
ALTER TABLE orders ADD INDEX idx_active(is_active, created_at);

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.city = 'Beijing'
GROUP BY u.id;

-- 更详细的分析（MySQL 8.0+）
EXPLAIN FORMAT=JSON SELECT ...\G

-- 实际执行计划（包含实际行数）
EXPLAIN ANALYZE SELECT ...  -- PostgreSQL

-- 原查询（慢）：全表扫描，filesort
SELECT * FROM orders 
WHERE user_id = 123 
  AND status = 'COMPLETED'
ORDER BY created_at DESC
LIMIT 20;

-- 添加复合索引后（快）：
ALTER TABLE orders 
ADD INDEX idx_user_status_time(user_id, status, created_at);

-- 优化分页（深分页问题）
-- 慢：OFFSET 10000 需要扫描10020行再丢弃前10000行
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 10000;

-- 快：游标分页，只需从cursor_id开始扫描
SELECT * FROM orders WHERE id > :last_id ORDER BY id LIMIT 20;

-- 计算列的选择性
SELECT 
  COUNT(DISTINCT gender) / COUNT(*) AS gender_selectivity,  -- 约0.5，差
  COUNT(DISTINCT city) / COUNT(*) AS city_selectivity,      -- 可能0.01-0.1
  COUNT(DISTINCT email) / COUNT(*) AS email_selectivity     -- 接近1.0，极好
FROM users;