第六章练习答案

01-index-types

练习 1：创建 B-Tree 索引

CREATE INDEX idx_email ON users(email);

练习 2：复合索引

CREATE INDEX idx_name ON users(last_name, first_name);

-- 测试最左前缀
EXPLAIN SELECT * FROM users WHERE last_name = 'Smith';  -- 使用索引
EXPLAIN SELECT * FROM users WHERE first_name = 'John';  -- 不使用索引

练习 3：Hash 索引（PostgreSQL）

CREATE INDEX idx_email_hash ON users USING HASH (email);
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';

练习 4：全文搜索

-- PostgreSQL
CREATE INDEX idx_content ON articles USING GIN (to_tsvector('english', content));
SELECT * FROM articles WHERE to_tsvector('english', content) @@ to_tsquery('database');

-- MySQL
CREATE FULLTEXT INDEX idx_content ON articles(content);
SELECT * FROM articles WHERE MATCH(content) AGAINST('database');

---

02-index-management

练习 1：创建索引

CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_name ON users(last_name, first_name);

练习 2：唯一索引

CREATE UNIQUE INDEX idx_email_unique ON users(email);
INSERT INTO users (email) VALUES ('test@example.com');
INSERT INTO users (email) VALUES ('test@example.com');  -- 错误

练习 3：部分索引

CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

练习 4：并发创建

CREATE INDEX CONCURRENTLY idx_email ON users(email);

练习 5：查看和删除

-- PostgreSQL
\d users
DROP INDEX idx_email;

-- MySQL
SHOW INDEX FROM users;
DROP INDEX idx_email ON users;

---

03-explain

练习 1：分析简单查询

EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';

练习 2：对比执行计划

-- 无索引
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';  -- Seq Scan

-- 有索引
CREATE INDEX idx_email ON users(email);
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';  -- Index Scan

练习 3：EXPLAIN ANALYZE

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1;

---

04-optimization

练习 1：优化函数查询

-- 优化前
SELECT * FROM orders WHERE YEAR(order_date) = 2026;

-- 优化后
SELECT * FROM orders WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01';

练习 2：覆盖索引

CREATE INDEX idx_email_name ON users(email, name);
EXPLAIN SELECT email, name FROM users WHERE email = 'alice@example.com';

练习 3：EXISTS 代替 IN

-- 优化前
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

-- 优化后
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

练习 4：分页优化

-- 优化前
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 10000;

-- 优化后
SELECT * FROM orders WHERE id > 10000 ORDER BY id LIMIT 10;

练习 5：批量插入

BEGIN;
INSERT INTO orders (user_id, product, amount) VALUES
    (1, 'A', 10),
    (2, 'B', 20),
    (3, 'C', 30);
COMMIT;

---

复习要点

索引类型

• B-Tree：通用，支持范围查询
• Hash：仅等值查询
• GiST/GIN：复杂数据类型

索引管理

• CREATE INDEX、DROP INDEX
• 唯一索引、部分索引
• 并发创建、监控使用

EXPLAIN

• cost、rows、actual time
• Seq Scan vs Index Scan
• 识别性能瓶颈

优化技巧

• 避免函数包裹列
• 使用覆盖索引
• EXISTS 代替 IN
• 批量操作
• 合适的分页

返回第六章概览 · 返回SQL 学习路线