SQL 索引

SQL 索引学习笔记

索引是数据库中用于加速数据检索的重要工具。常见的索引类型包括普通索引、唯一索引和位图索引。

1. 普通索引

普通索引用于加速对表中数据的查询。

（1）创建普通索引

-- 创建普通索引
CREATE INDEX IND_TMP_INFO_ID ON TMP_INFO (NAME, AGE);

（2）删除索引

-- 删除索引
DROP INDEX IND_TMP_INFO_ID;

（3）重建索引

-- 重建索引
ALTER INDEX IND_TMP_INFO_ID REBUILD;

2. 唯一索引

唯一索引用于确保列中的值唯一。

（1）创建唯一索引

-- 创建唯一索引
CREATE UNIQUE INDEX IND_UN_ID ON TMP_INFO (ID);

3. 主键索引

主键列会自动创建唯一索引。

（1）创建主键索引

-- 创建表并指定主键
CREATE TABLE TMP_INFO (
    ID INT PRIMARY KEY,  -- 主键自动创建唯一索引
    NAME VARCHAR2(10),
    AGE NUMBER(3)
);

-- 插入数据
INSERT INTO TMP_INFO VALUES (1, 'A', 21);
INSERT INTO TMP_INFO VALUES (2, 'B', 21);
INSERT INTO TMP_INFO VALUES (3, 'C', 21);

-- 查询数据
SELECT * FROM TMP_INFO WHERE NAME = 'B';

4. 位图索引

位图索引适用于列基数较少（列中重复值较多）的情况，常用于加速低基数列的查询。

（1）创建位图索引

-- 创建表
CREATE TABLE TMP_PERSON (
    NAME VARCHAR2(10),
    GENDER VARCHAR2(4),
    CITY VARCHAR2(5)
);

-- 插入数据
INSERT INTO TMP_PERSON VALUES ('张三', '男', '北京');
INSERT INTO TMP_PERSON VALUES ('李四', '男', '上海');
INSERT INTO TMP_PERSON VALUES ('王五', '女', '广州');
INSERT INTO TMP_PERSON VALUES ('赵六', '男', '北京');
INSERT INTO TMP_PERSON VALUES ('钱七', '男', '上海');
INSERT INTO TMP_PERSON VALUES ('孙八', '女', '深圳');

-- 创建位图索引
CREATE BITMAP INDEX IND_TMP_PERSON ON TMP_PERSON (GENDER, CITY);

（2）位图索引原理

位图索引通过位图表示列中的值，适用于低基数列的查询。

• 示例：

  -- 查询性别为 '男' 且城市为 '北京' 的记录
  SELECT * FROM TMP_PERSON WHERE GENDER = '男' AND CITY = '北京';

• 位图表示：

  rownum 1 2 3 4 5 6    
  gender 0 0 1 0 0 1    -- '男' 0, '女' 1
  city   0 1 1 0 1 1    -- '北京' 0, '其他城市' 1

5. 综合示例

（1）创建普通索引

-- 创建普通索引
CREATE INDEX IND_TMP_INFO_ID ON TMP_INFO (NAME, AGE);

-- 查询数据
SELECT * FROM TMP_INFO WHERE NAME = 'B';

（2）创建唯一索引

-- 创建唯一索引
CREATE UNIQUE INDEX IND_UN_ID ON TMP_INFO (ID);

（3）创建位图索引

-- 创建位图索引
CREATE BITMAP INDEX IND_TMP_PERSON ON TMP_PERSON (GENDER, CITY);

-- 查询数据
SELECT * FROM TMP_PERSON WHERE GENDER = '男' AND CITY = '北京';

6. 总结

1. 普通索引：用于加速查询。
2. 唯一索引：用于确保列中的值唯一。
3. 主键索引：主键列自动创建唯一索引。
4. 位图索引：适用于低基数列的查询。