Spanner索引 #

Spanner索引类型
├── 二级索引 (Secondary Index)
│   ├── 加速非主键查询
│   ├── 自动维护
│   └── 支持唯一约束
│
├── 交错索引 (Interleaved Index)
│   ├── 与交错表配合
│   ├── 优化父子表查询
│   └── 数据物理存储在一起
│
└── 存储索引 (Storing Index)
    ├── 存储额外列
    ├── 避免回表查询
    └── 提高查询性能

-- 示例表
CREATE TABLE users (
    user_id INT64 NOT NULL,
    name STRING(100) NOT NULL,
    email STRING(255),
    status STRING(20),
    created_at TIMESTAMP
) PRIMARY KEY (user_id);

-- 创建单列索引
CREATE INDEX idx_users_email ON users(email);

-- 创建复合索引
CREATE INDEX idx_users_status_created ON users(status, created_at);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- 使用索引查询
SELECT * FROM users WHERE email = 'john@example.com';

-- 复合索引查询
SELECT * FROM users 
WHERE status = 'active' 
  AND created_at > TIMESTAMP '2024-01-01';

-- 强制使用索引
SELECT * FROM users@{FORCE_INDEX=idx_users_email}
WHERE email = 'john@example.com';

索引使用条件:
├── 查询条件匹配索引前缀
├── 使用等值或范围条件
├── 避免在索引列上使用函数
├── 避免使用NOT、!=等条件
└── 使用ORDER BY匹配索引顺序

-- 父表
CREATE TABLE users (
    user_id INT64 NOT NULL,
    name STRING(100)
) PRIMARY KEY (user_id);

-- 子表(交错表)
CREATE TABLE orders (
    user_id INT64 NOT NULL,
    order_id INT64 NOT NULL,
    order_date DATE,
    amount FLOAT64
) PRIMARY KEY (user_id, order_id)
INTERLEAVE IN PARENT users ON DELETE CASCADE;

-- 创建交错索引
CREATE INDEX idx_orders_date ON orders(order_date)
INTERLEAVE IN users;

交错索引优势:
├── 数据物理存储在一起
├── 减少网络传输
├── 提高JOIN性能
├── 优化范围查询
└── 自动级联删除

-- 使用交错索引
SELECT u.name, o.order_id, o.amount
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id
WHERE u.user_id = 1 AND o.order_date >= DATE '2024-01-01';

-- 创建存储索引(STORING)
CREATE INDEX idx_users_status_storing ON users(status)
STORING (name, email, created_at);

-- 存储索引包含额外列，避免回表查询

-- 不使用存储索引: 需要回表
SELECT name, email FROM users WHERE status = 'active';
-- 1. 扫描索引获取主键
-- 2. 回表获取name, email

-- 使用存储索引: 无需回表
SELECT name, email FROM users@{FORCE_INDEX=idx_users_status_storing}
WHERE status = 'active';
-- 直接从索引获取name, email

存储索引选择建议:
├── 经常查询的列
├── 避免回表查询
├── 平衡存储成本
├── 考虑写入开销
└── 监控索引使用情况

-- 查看所有索引
SELECT index_name, table_name
FROM INFORMATION_SCHEMA.INDEXES;

-- 查看索引列
SELECT index_name, column_name, ordinal_position
FROM INFORMATION_SCHEMA.INDEX_COLUMNS
WHERE table_name = 'users'
ORDER BY index_name, ordinal_position;

-- 查看索引统计
SELECT * FROM INFORMATION_SCHEMA.INDEX_STATS;

-- 删除索引
DROP INDEX idx_users_email;

-- 如果存在则删除
DROP INDEX IF EXISTS idx_users_email;

-- 默认索引不包含NULL值
-- 使用NULL_FILTERED关键字
CREATE INDEX idx_users_email_null ON users(email)
WHERE email IS NOT NULL;

-- 查询时需要处理NULL
SELECT * FROM users WHERE email = 'john@example.com' OR email IS NULL;

索引设计原则:
├── 选择高选择性列
├── 考虑查询模式
├── 复合索引注意顺序
├── 避免过多索引
└── 定期评估索引使用

-- 复合索引顺序原则:
-- 1. 等值条件列在前
-- 2. 范围条件列在后
-- 3. 排序列考虑在内

-- 示例: 查询活跃用户按创建时间排序
-- SELECT * FROM users 
-- WHERE status = 'active' 
-- ORDER BY created_at DESC;

-- 创建复合索引
CREATE INDEX idx_users_status_created ON users(status, created_at DESC);

-- 创建覆盖索引
CREATE INDEX idx_users_covering ON users(status)
STORING (name, email, created_at);

-- 查询完全由索引覆盖
SELECT name, email, created_at 
FROM users 
WHERE status = 'active';
-- 无需回表

-- 使用EXPLAIN分析
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';

-- 强制使用索引
SELECT * FROM users@{FORCE_INDEX=idx_users_email}
WHERE email = 'john@example.com';

-- 禁用索引扫描
SELECT * FROM users@{FORCE_INDEX=_BASE_TABLE}
WHERE email = 'john@example.com';

索引对写入的影响:
├── 每个索引增加写入开销
├── 批量写入时更明显
├── 需要权衡读写性能
└── 避免创建不必要的索引

索引存储开销:
├── 索引数据占用存储空间
├── 存储索引额外列增加存储
├── 需要监控索引大小
└── 定期清理无用索引

-- 查看索引统计
SELECT 
    index_name,
    row_count,
    size_bytes
FROM INFORMATION_SCHEMA.INDEX_STATS;

-- 查看查询统计
SELECT 
    text,
    execution_count,
    avg_latency_seconds
FROM INFORMATION_SCHEMA.QUERY_STATS_TOP_HOUR
ORDER BY avg_latency_seconds DESC;

索引创建建议:
├── 根据查询模式创建
├── 高选择性列优先
├── 复合索引注意顺序
├── 考虑存储索引
└── 避免过多索引

索引使用建议:
├── 使用EXPLAIN分析
├── 匹配索引前缀
├── 避免索引列使用函数
├── 使用覆盖索引
└── 监控索引使用情况

索引维护建议:
├── 定期评估索引使用
├── 删除无用索引
├── 监控索引大小
├── 优化复合索引
└── 更新统计信息

1. 根据查询模式设计索引
   └── 分析常用查询

2. 使用复合索引
   └── 注意列顺序

3. 考虑存储索引
   └── 避免回表查询

4. 监控索引性能
   └── 定期评估

5. 平衡读写性能
   └── 避免过多索引