活动专区

活动公告

上车！GBase 8a开发者联盟发车了 技术福利、人脉资源全都有！

认证培训专区

认证培训资料

GBase 8s GDCA 认证培训课前准备

GBase 8a

其他

物化视图报错：references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

01-GBase 8s 事务型数据库 安装与卸载

GBase 8s

适配迁移

[GBase 8s 教程]GBase 8s 常用函数、表达式

## 前言


在数据库性能优化中，索引设计是至关重要的一环。合理的索引可以大幅提升查询效率，而错误的索引设计不仅无法带来性能提升，还会增加写入开销和存储成本。很多开发者在使用 GBase 8a 时，由于不了解其索引机制，导致 SQL 查询性能不佳。

本文将从 GBase 8a 的索引类型、创建方法、设计原则以及常见误区等方面，全面讲解如何设计和优化索引，帮助你构建高效的数据库查询能力。

## 一、GBase 8a 索引类型概述

GBase 8a 支持多种索引类型，每种索引适用于不同的场景：

### 1.1 B-Tree 索引

B-Tree 索引是 GBase 8a 最常用的索引类型，适用于等值查询和范围查询。

```sql
-- 创建 B-Tree 索引
CREATE INDEX idx_user_id ON user_info(user_id);
CREATE INDEX idx_create_time ON orders(create_time);
```

**适用场景：**

- 等值查询：`WHERE user_id = 12345`
- 范围查询：`WHERE create_time BETWEEN '2026-01-01' AND '2026-03-01'`
- 排序操作：`ORDER BY create_time`
- 多列组合查询

### 1.2 哈希索引

哈希索引适用于等值查询，查询效率非常高，但不支持范围查询和排序。

```sql
-- 创建哈希索引
CREATE INDEX idx_phone USING HASH ON user_info(phone);
```

**适用场景：**

- 等值查询：`WHERE phone = '13800138000'`
- 不支持：`WHERE phone &gt; '13800138000'`
- 不支持：`ORDER BY phone`

### 1.3 复合索引

复合索引是由多个列组成的索引，可以加速多列查询。

```sql
-- 创建复合索引
CREATE INDEX idx_order_user_status ON orders(user_id, status, create_time);
```

**使用原则：**

- 最左前缀匹配：查询条件必须包含索引的最左列
- 索引列顺序：选择性高的列放在前面

### 1.4 唯一索引

唯一索引保证索引列的值唯一，用于确保数据唯一性。

```sql
-- 创建唯一索引
CREATE UNIQUE INDEX idx_user_id ON user_info(user_id);
CREATE UNIQUE INDEX idx_order_no ON orders(order_no);
```

## 二、索引设计核心原则

### 2.1 选择性高的列优先建索引

选择性（Selectivity）是指唯一值占总行数的比例。选择性越高，索引过滤效果越好。

```sql
-- 查看列的选择性
SELECT 
    COUNT(DISTINCT column_name) / COUNT(*) AS selectivity
FROM table_name;

-- 示例：user_id 选择性接近 1（每个用户唯一）
-- 而 status 选择性很低（只有几个固定值）
```

**建议：**

- 选择性 &gt; 0.1 的列适合建索引
- 选择性 &lt; 0.01 的列不建议建索引

### 2.2 避免在频繁更新的列上建索引

索引会增加写入开销，如果在频繁更新的列上建索引，会影响写入性能。

```sql
-- 不推荐：last_update 频繁更新
CREATE INDEX idx_last_update ON orders(last_update);

-- 推荐：更新频率低的列
CREATE INDEX idx_order_no ON orders(order_no);
```

### 2.3 覆盖索引减少回表

覆盖索引（Covering Index）包含查询需要的所有列，可以避免回表查询。

```sql
-- 原始查询：需要回表
SELECT user_id, name, email 
FROM user_info 
WHERE user_id = 123;

-- 优化：创建覆盖索引
CREATE INDEX idx_user_id_cover ON user_info(user_id, name, email);

-- 执行计划显示 Using index，表示使用了覆盖索引
```

### 2.4 控制索引数量

索引并非越多越好，每个索引都会增加存储开销和写入成本。

**建议：**

- 单表索引数量控制在 5 个以内
- 定期分析索引使用情况，删除未使用的索引

```sql
-- 查看索引使用情况
SHOW INDEX FROM table_name;

-- 删除未使用的索引
DROP INDEX idx_unused ON table_name;
```

## 三、索引设计最佳实践

### 3.1 业务查询驱动索引设计

根据实际业务查询来设计索引，而不是为所有列都建索引。

**步骤：**

1. 收集慢查询日志
2. 分析高频查询
3. 为高频查询设计索引
4. 验证索引效果

```sql
-- 假设业务高频查询：
-- 1. 查询某用户的订单
SELECT * FROM orders WHERE user_id = ? AND status = ?;
-- 需要索引：(user_id, status)

-- 2. 按时间范围查询订单
SELECT * FROM orders WHERE create_time &gt;= ? AND create_time &lt;= ?;
-- 需要索引：(create_time)

-- 3. 查询某状态的订单
SELECT * FROM orders WHERE status = ?;
-- 考虑是否需要单独索引
```

### 3.2 复合索引设计示例

```sql
-- 业务查询：WHERE user_id = ? AND status = ? ORDER BY create_time DESC
-- 设计复合索引
CREATE INDEX idx_user_status_time ON orders(user_id, status, create_time);

-- 索引列顺序分析：
-- 1. user_id = ? - 等值查询，放最前面
-- 2. status = ? - 等值查询，放中间
-- 3. create_time - 排序，放最后
```

### 3.3 分区表索引设计

GBase 8a 支持分区表，索引需要考虑分区特性。

```sql
-- 创建分区表
CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    user_id INT,
    status VARCHAR(20),
    create_time DATETIME
) 
PARTITION BY RANGE (YEAR(create_time)) (
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 分区表索引设计
-- 本地索引：每个分区独立的索引
CREATE INDEX idx_user_id ON orders(user_id) LOCAL;

-- 全局索引：跨分区的索引
CREATE INDEX idx_create_time ON orders(create_time) GLOBAL;
```

## 四、索引使用常见误区

### 4.1 误区一：索引越多越好

**错误做法：**

```sql
-- 为每个列都建索引
CREATE INDEX idx_col1 ON table(col1);
CREATE INDEX idx_col2 ON table(col2);
CREATE INDEX idx_col3 ON table(col3);
-- ... 更多索引
```

**问题：**

- 写入性能下降
- 存储空间增加
- 优化器选择困难

### 4.2 误区二：不在选择性低的列上建索引

**错误做法：**

```sql
-- status 只有 3 个值，选择性约为 0.33
CREATE INDEX idx_status ON orders(status);
```

**分析：**

```sql
-- 查看 status 列的值分布
SELECT status, COUNT(*) 
FROM orders 
GROUP BY status;

-- 结果可能是：
-- status    COUNT
-- 待付款    1000
-- 已付款    5000
-- 已完成    90000

-- 使用索引查询"已完成"状态，可能需要扫描 90% 的数据
-- 此时索引效率很低
```

### 4.3 误区三：忽视最左前缀原则

**错误做法：**

```sql
-- 创建复合索引
CREATE INDEX idx_a_b ON table(a, b, c);

-- 以下查询无法使用索引
SELECT * FROM table WHERE b = ?;  -- 跳过最左列 a
SELECT * FROM table WHERE c = ?;  -- 跳过 a 和 b
```

### 4.4 误区四：使用函数或表达式

**错误做法：**

```sql
-- 在列上使用函数
SELECT * FROM orders WHERE YEAR(create_time) = 2026;
CREATE INDEX idx_year ON orders(YEAR(create_time));  -- 错误！
```

**正确做法：**

```sql
-- 使用函数会导致索引失效
SELECT * FROM orders WHERE create_time &gt;= '2026-01-01' 
                      AND create_time &lt; '2027-01-01';

-- 如果必须使用函数，考虑函数索引（GBase 8a 支持）
CREATE INDEX idx_year ON orders((YEAR(create_time)));
```

## 五、索引优化实战案例

### 5.1 案例一：订单查询优化

**原始查询：**

```sql
SELECT order_id, user_id, total_amount, create_time
FROM orders
WHERE user_id = 12345 
  AND status = '已完成'
  AND create_time &gt;= '2026-01-01'
ORDER BY create_time DESC
LIMIT 20;
```

**分析：**

- 条件涉及 user_id、status、create_time
- 需要排序
- 返回限定数量的记录

**索引设计：**

```sql
CREATE INDEX idx_ord_user_status_time ON orders(
    user_id, status, create_time DESC
);
```

**验证：**

```sql
EXPLAIN SELECT order_id, user_id, total_amount, create_time
FROM orders
WHERE user_id = 12345 
  AND status = '已完成'
  AND create_time &gt;= '2026-01-01'
ORDER BY create_time DESC
LIMIT 20;
```

### 5.2 案例二：用户登录优化

**原始查询：**

```sql
SELECT user_id, username, email, mobile
FROM user_info
WHERE (username = 'zhangsan' OR mobile = '13800138000')
  AND status = '正常';
```

**分析：**

- 使用 OR 条件，可能无法有效使用索引
- 需要查询多个字段，考虑覆盖索引

**优化方案：**

```sql
-- 方案 1：创建复合索引
CREATE INDEX idx_user_login ON user_info(username, mobile, status);

-- 方案 2：拆分查询，使用 UNION
(SELECT user_id, username, email, mobile 
 FROM user_info WHERE username = 'zhangsan')
UNION
(SELECT user_id, username, email, mobile 
 FROM user_info WHERE mobile = '13800138000');

-- 创建两个独立的索引
CREATE INDEX idx_username ON user_info(username);
CREATE INDEX idx_mobile ON user_info(mobile);
```

### 5.3 案例三：分页查询优化

**原始查询（深度分页）：**

```sql
SELECT * FROM orders 
ORDER BY create_time DESC 
LIMIT 100000, 20;
```

**问题：**

- 需要扫描前 100020 行，然后丢弃前 100000 行
- 性能随页码增加而下降

**优化方案：**

```sql
-- 方案 1：使用主键进行范围查询
SELECT * FROM orders 
WHERE order_id &lt; #{last_order_id}
ORDER BY order_id DESC 
LIMIT 20;

-- 需要确保 order_id 有索引
CREATE INDEX idx_order_id ON orders(order_id);

-- 方案 2：记录上次的查询位置
-- 适用于需要跳页的场景
```

## 六、索引维护与监控

### 6.1 定期分析索引效率

```sql
-- 查看表的所有索引
SHOW INDEX FROM orders;

-- 分析索引选择性
SELECT 
    INDEX_NAME,
    COLUMN_NAME,
    CARDINALITY
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'your_database'
  AND TABLE_NAME = 'orders';
```

### 6.2 监控慢查询

```sql
-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 1秒

-- 查看慢查询
SELECT * FROM mysql.slow_log 
ORDER BY start_time DESC 
LIMIT 10;
```

### 6.3 重建碎片化的索引

```sql
-- 重建索引（优化）
OPTIMIZE TABLE orders;
```

## 七、总结

索引设计是数据库性能优化的核心技术，需要结合业务查询特点和数据分布情况进行综合考量。核心要点如下：

1. **按需设计**：根据实际查询设计索引，避免过度索引
2. **选择性优先**：优先在选择性高的列上建索引
3. **复合索引**：注意最左前缀原则，合理安排列顺序
4. **覆盖索引**：尽量包含查询所需的所有列
5. **持续优化**：定期分析索引使用情况，删除无效索引

掌握这些索引设计原则和实践技巧，可以让你的 GBase 8a 数据库查询性能得到显著提升。




性能调优

GBase 8a 索引设计与优化实践

GBase 8a 索引设计与优化实践

评论

热门帖子