1
SELECT * FROM orders WHERE user_id = 42;

1
-- InnoDB 中，主键就是聚簇索引
2
CREATE TABLE users (
3
    id BIGINT PRIMARY KEY,       -- 聚簇索引
4
    email VARCHAR(255),
5
    name VARCHAR(100),
6
    age INT,
7
    INDEX idx_email (email)      -- 二级索引，叶子存 id 值
8
);
9

10
-- 通过二级索引查找需要回表
11
SELECT * FROM users WHERE email = 'alice@example.com';
12
-- 步骤：idx_email 找到 id=5 → 聚簇索引找 id=5 的完整行

1
-- 联合索引
2
CREATE INDEX idx_status_created ON orders (status, created_at);
3

4
-- 可以使用索引（最左前缀匹配）
5
SELECT * FROM orders WHERE status = 'shipped';
6
SELECT * FROM orders WHERE status = 'shipped' AND created_at > '2026-01-01';
7

8
-- 无法使用索引（跳过了最左列 status）
9
SELECT * FROM orders WHERE created_at > '2026-01-01';
10

11
-- 部分使用索引（仅使用 status 列，created_at 无法用于过滤）
12
SELECT * FROM orders WHERE status = 'shipped' AND amount > 100;

1
选择性 = DISTINCT(col) / COUNT(*)

1
-- 查看列的基数（不同值的数量）
2
-- MySQL
3
SHOW INDEX FROM orders;
4

5
-- PostgreSQL
6
SELECT attname, n_distinct FROM pg_stats WHERE tablename = 'orders';

1
-- PostgreSQL 哈希索引
2
CREATE INDEX idx_user_email_hash ON users USING hash (email);
3

4
-- 哈希索引擅长：精确匹配
5
SELECT * FROM users WHERE email = 'alice@example.com';
6

7
-- 哈希索引不支持：范围查询
8
SELECT * FROM users WHERE email > 'alice@example.com';  -- 无法使用索引

1
-- 查看自适应哈希索引状态
2
SHOW ENGINE INNODB STATUS\G
3

4
-- 开启/关闭自适应哈希索引
5
SET GLOBAL innodb_adaptive_hash_index = ON;

1
-- PG 哈希索引适用于等值查询为主的场景
2
CREATE INDEX idx_session_token ON sessions USING hash (token);
3

4
-- 对比 B-tree 索引，哈希索引在等值查询上更紧凑
5
-- 但 B-tree 仍然是默认选择，因为其通用性更强

1
-- Oracle 位图索引
2
CREATE BITMAP INDEX idx_emp_dept ON employees (department);
3

4
-- 多条件查询：位图 AND 运算
5
-- 查找"研发部门 AND 资深"的员工
6
SELECT /*+ INDEX(employees idx_emp_dept idx_emp_level) */
7
    * FROM employees
8
WHERE department = '研发' AND level = '资深';
9
-- 执行：研发位图 AND 资深位图 → 结果位图 → 行号

1
-- Oracle：为低基数的每个列建位图索引
2
CREATE BITMAP INDEX idx_sales_region ON sales (region);
3
CREATE BITMAP INDEX idx_sales_channel ON sales (channel);
4
CREATE BITMAP INDEX idx_sales_quarter ON sales (quarter);
5

6
-- 星型查询：位图索引的杀手级场景
7
SELECT region, channel, SUM(amount)
8
FROM sales
9
WHERE region = '华东' AND channel = '线上' AND quarter = 'Q1'
10
GROUP BY region, channel;
11
-- Oracle 自动进行位图 AND → 行号 → 事实表查找

1
-- PostgreSQL：只对活跃订单建索引
2
CREATE INDEX idx_orders_active ON orders (user_id)
3
WHERE status IN ('pending', 'processing', 'shipped');
4

5
-- 查询自动使用部分索引
6
SELECT * FROM orders
7
WHERE user_id = 42 AND status = 'pending';

1
-- MySQL 空间索引
2
CREATE TABLE locations (
3
    id INT PRIMARY KEY,
4
    name VARCHAR(100),
5
    point GEOMETRY NOT NULL SRID 4326,
6
    SPATIAL INDEX idx_point (point)
7
);
8

9
-- 空间范围查询
10
SELECT name, ST_Distance_Sphere(point, ST_GeomFromText('POINT(121.47 31.23)', 4326)) AS dist
11
FROM locations
12
WHERE ST_Contains(
13
    ST_Buffer(ST_GeomFromText('POINT(121.47 31.23)', 4326), 0.03),
14
    point
15
);

1
-- PostgreSQL + PostGIS 空间索引
2
CREATE INDEX idx_locations_geom ON locations USING gist (geom);
3

4
-- KNN 查询（最近邻，GiST 原生支持排序）
5
SELECT name, geom <-> ST_MakePoint(121.47, 31.23) AS dist
6
FROM locations
7
ORDER BY dist
8
LIMIT 10;

1
-- MySQL 全文索引
2
CREATE TABLE articles (
3
    id INT PRIMARY KEY,
4
    title VARCHAR(200),
5
    content TEXT,
6
    FULLTEXT INDEX ft_content (title, content) WITH PARSER ngram
7
);
8

9
-- 全文搜索
10
SELECT title,
11
    MATCH(title, content) AGAINST('数据库 索引' IN NATURAL LANGUAGE MODE) AS score
12
FROM articles
13
WHERE MATCH(title, content) AGAINST('数据库 索引' IN NATURAL LANGUAGE MODE)
14
ORDER BY score DESC;

1
-- PostgreSQL GIN 全文索引（推荐）
2
CREATE INDEX idx_articles_fts ON articles USING gin (to_tsvector('simple', title || ' ' || content));
3

4
-- 全文搜索
5
SELECT title, ts_rank(to_tsvector('simple', title || ' ' || content), query) AS rank
6
FROM articles, to_tsquery('simple', '数据库 & 索引') query
7
WHERE to_tsvector('simple', title || ' ' || content) @@ query
8
ORDER BY rank DESC;

1
-- 联合索引
2
CREATE INDEX idx_user_email_name ON users (email, name);
3

4
-- 覆盖索引：查询只需要 email 和 name，索引中都有
5
SELECT email, name FROM users WHERE email = 'alice@example.com';
6

7
-- 非覆盖索引：还需要 age，必须回表
8
SELECT email, name, age FROM users WHERE email = 'alice@example.com';

1
-- MySQL EXPLAIN 验证覆盖索引
2
EXPLAIN SELECT email, name FROM users WHERE email = 'alice@example.com';
3
-- Extra: Using index ← 覆盖索引，无需回表
4

5
-- PostgreSQL EXPLAIN 验证
6
EXPLAIN SELECT email, name FROM users WHERE email = 'alice@example.com';
7
-- Index Only Scan using idx_user_email_name ← 仅索引扫描

1
-- 联合索引 (email, name)
2
-- 查询条件使用了 email（索引第一列）和 name（索引第二列）
3
SELECT * FROM users WHERE email LIKE 'ali%' AND name LIKE '%ce';

1
-- 查看 ICP 是否生效
2
EXPLAIN SELECT * FROM users WHERE email LIKE 'ali%' AND name LIKE '%ce';
3
-- Extra: Using index condition ← 索引下推生效

1
-- 前缀索引：只取 URL 前 20 个字符
2
CREATE INDEX idx_url_prefix ON pages (url(20));
3

4
-- 选择前缀长度的原则：选择性接近完整列即可
5
SELECT
6
    COUNT(DISTINCT url) / COUNT(*) AS full_selectivity,
7
    COUNT(DISTINCT LEFT(url, 10)) / COUNT(*) AS prefix_10,
8
    COUNT(DISTINCT LEFT(url, 20)) / COUNT(*) AS prefix_20,
9
    COUNT(DISTINCT LEFT(url, 30)) / COUNT(*) AS prefix_30
10
FROM pages;
11
-- 选择使选择性接近 full_selectivity 的最短前缀

1
-- 场景：订单表有 status、user_id、created_at 三列
2
-- 查询模式 1：WHERE user_id = ? AND status = ?（高频）
3
-- 查询模式 2：WHERE user_id = ? AND created_at > ?（中频）
4
-- 查询模式 3：WHERE status = ? AND created_at > ?（低频）
5

6
-- 最优索引：user_id 在前（等值+高频），status 在中（等值），created_at 在后（范围）
7
CREATE INDEX idx_uid_status_created ON orders (user_id, status, created_at);
8

9
-- 这个索引可以覆盖查询模式 1 和 2
10
-- 查询模式 3 需要单独的索引
11
CREATE INDEX idx_status_created ON orders (status, created_at);

1
-- 错误：对 created_at 使用函数
2
SELECT * FROM orders WHERE YEAR(created_at) = 2026;
3

4
-- 正确：使用范围查询
5
SELECT * FROM orders
6
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';
7

8
-- 错误：对 user_id 进行算术运算
9
SELECT * FROM orders WHERE user_id + 1 = 43;
10

11
-- 正确：将运算移到等号另一侧
12
SELECT * FROM orders WHERE user_id = 42;

1
-- 假设 user_id 是 VARCHAR 类型
2
-- 错误：传入整数，数据库将 user_id 隐式转为数字
3
SELECT * FROM users WHERE user_id = 123;
4

5
-- 正确：传入字符串
6
SELECT * FROM users WHERE user_id = '123';
7

8
-- 假设 phone 是 VARCHAR 类型
9
-- 错误：传入数字
10
SELECT * FROM users WHERE phone = 13800138000;
11

12
-- 正确：传入字符串
13
SELECT * FROM users WHERE phone = '13800138000';

1
-- 错误：前缀通配符，无法使用索引
2
SELECT * FROM users WHERE name LIKE '%lice';
3

4
-- 正确：前缀匹配，可以使用索引
5
SELECT * FROM users WHERE name LIKE 'ali%';
6

7
-- 如需全文搜索，使用全文索引
8
SELECT * FROM users WHERE MATCH(name) AGAINST('lice');

1
-- 假设 email 有索引，name 没有索引
2
-- 错误：name 无索引导致整个 OR 无法使用索引
3
SELECT * FROM users WHERE email = 'alice@example.com' OR name = 'Alice';
4

5
-- 正确方案 1：为 name 也建索引
6
CREATE INDEX idx_name ON users (name);
7
SELECT * FROM users WHERE email = 'alice@example.com' OR name = 'Alice';
8
-- 优化器可能使用索引合并（Index Merge）
9

10
-- 正确方案 2：用 UNION 替代 OR
11
SELECT * FROM users WHERE email = 'alice@example.com'
12
UNION
13
SELECT * FROM users WHERE name = 'Alice';

1
-- 索引：idx_status_created (status, created_at)
2

3
-- 错误：跳过 status，直接查 created_at
4
SELECT * FROM orders WHERE created_at > '2026-01-01';
5

6
-- 正确：包含最左列 status
7
SELECT * FROM orders WHERE status = 'shipped' AND created_at > '2026-01-01';
8

9
-- 错误：status 用范围查询，created_at 无法利用索引排序
10
SELECT * FROM orders WHERE status > 'pending' ORDER BY created_at;
11

12
-- 正确：status 用等值查询，created_at 可利用索引排序
13
SELECT * FROM orders WHERE status = 'shipped' ORDER BY created_at;

1
# Redis Geo 索引示例
2
GEOADD locations 121.47 31.23 "上海站" 121.48 31.24 "南京路"
3
GEORADIUS locations 121.47 31.23 3 km COUNT 10

1
CREATE TABLE products (
2
    id SERIAL PRIMARY KEY,
3
    name TEXT,
4
    price NUMERIC,
5
    tags TEXT[],
6
    description TEXT
7
);
8

9
INSERT INTO products (name, price, tags, description) VALUES
10
    ('Laptop', 999.99, ARRAY['electronics', 'computer'], 'A powerful laptop'),
11
    ('Phone', 699.99, ARRAY['electronics', 'mobile'], 'A smart phone'),
12
    ('Tablet', 399.99, ARRAY['electronics', 'mobile'], 'A tablet device'),
13
    ('Book', 29.99, ARRAY['education', 'reading'], 'A good book'),
14
    ('Pen', 1.99, ARRAY['education', 'writing'], 'A simple pen');
15

16
-- B-tree 索引（默认，适合范围查询和排序）
17
CREATE INDEX idx_products_price ON products USING btree (price);
18

19
-- Hash 索引（只适合等值查询）
20
CREATE INDEX idx_products_name ON products USING hash (name);
21

22
-- GIN 索引（适合数组/全文搜索）
23
CREATE INDEX idx_products_tags ON products USING gin (tags);
24

25
-- BRIN 索引（适合大表的范围块索引）
26
CREATE INDEX idx_products_price_brin ON products USING brin (price);

1
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'products';

1
        indexname        |                                  indexdef
2
-------------------------+----------------------------------------------------------------------------
3
 products_pkey           | CREATE UNIQUE INDEX products_pkey ON public.products USING btree (id)
4
 idx_products_price      | CREATE INDEX idx_products_price ON public.products USING btree (price)
5
 idx_products_name       | CREATE INDEX idx_products_name ON public.products USING hash (name)
6
 idx_products_tags       | CREATE INDEX idx_products_tags ON public.products USING gin (tags)
7
 idx_products_price_brin | CREATE INDEX idx_products_price_brin ON public.products USING brin (price)

1
SELECT indexname, pg_size_pretty(pg_relation_size(indexname::regclass)) as size
2
FROM pg_indexes WHERE tablename = 'products';

1
        indexname        | size
2
-------------------------+-------
3
 products_pkey           | 16 kB
4
 idx_products_price      | 16 kB
5
 idx_products_name       | 32 kB
6
 idx_products_tags       | 16 kB
7
 idx_products_price_brin | 24 kB