MySQL索引类型和索引方法

lyjin 2024-11-20

[TOC]

> MySQL 中的索引类型和索引方法可以帮助提升查询性能。

### 索引类型
#### 1. FULLTEXT 索引
- **用途：** 用于全文检索。

- **支持的数据类型：** CHAR, VARCHAR, TEXT。
- **应用场景：**

- 处理自然语言搜索（如搜索文章内容、博客）。
	
	- 支持布尔模式搜索和自然语言模式搜索。
	
- **特点：**
	- 提供对大文本字段的高效检索。
	
	- 需要通过 MATCH ... AGAINST 查询语句使用。
	
	- 常用于 MyISAM 和 InnoDB（5.6 及更高版本支持 InnoDB 的 FULLTEXT）。
	
- **示例：**

```sql
	CREATE TABLE articles (
		id INT AUTO_INCREMENT PRIMARY KEY,
		title VARCHAR(255),
		content TEXT,
		FULLTEXT(title, content)
	);

SELECT * FROM articles
	WHERE MATCH(title, content) AGAINST('keyword');
	```

#### 2. NORMAL 索引（默认索引）
- **用途：** 最常见的普通索引类型，用于提高查询效率。

- **支持的数据类型：** 几乎所有数据类型。

- **应用场景：**

- 为查询的 WHERE、ORDER BY、GROUP BY 提供加速。
	
	- 用于频繁访问的列（如外键）。
	
- **特点：**

- 不限制唯一性。
	
	- 可单列或多列联合创建。
	
	- MySQL 默认索引类型。

- **示例：**

```sql
	CREATE TABLE employees (
		id INT PRIMARY KEY,
		name VARCHAR(50),
		department_id INT,
		INDEX idx_department_id(department_id)
	);
	```

#### 3. SPATIAL 索引
- **用途：** 用于存储和检索地理空间数据（如点、线、多边形）。

- **支持的数据类型：** 仅支持 MySQL 的 GEOMETRY 数据类型及其子类型（POINT、LINESTRING、POLYGON）。

- **应用场景：**

- 地理信息系统（GIS）相关应用。
	
	- 需要空间数据进行快速查询。
	
- **特点：**

- 必须使用 MyISAM 或 InnoDB（8.0+）。
	
	- 只能用于具有空间数据的列。
	
	- 查询需要配合 MySQL 的 GIS 函数。
	
- **示例：**

```sql
	CREATE TABLE locations (
		id INT PRIMARY KEY,
		coordinates POINT NOT NULL,
		SPATIAL INDEX(coordinates)
	);

SELECT id FROM locations
	WHERE ST_Contains(ST_GeomFromText('POLYGON((...))'), coordinates);
	```

#### 4. UNIQUE 索引
- **用途：** 用于确保列中的值是唯一的。

- **支持的数据类型：** 几乎所有数据类型。

- **应用场景：**

- 确保数据唯一性（如用户名、邮箱等）。
	
	- 常用于业务逻辑中的唯一性约束。
	
- **特点：**

- 如果试图插入重复值，会导致错误。
	
	- 可以为多列创建联合唯一索引。
	
	- 自动为 PRIMARY KEY 创建的索引就是一种 UNIQUE 索引。
	
- **示例：**

```sql
	CREATE TABLE users (
		id INT AUTO_INCREMENT PRIMARY KEY,
		email VARCHAR(255),
		UNIQUE INDEX idx_email(email)
	);
	```

### 索引方法
#### 1. BTREE 索引**
- **用途： 默认索引方法，用于大多数 MySQL 索引类型。

- **支持的存储引擎：** MyISAM、InnoDB。

- **应用场景：**

- 支持范围查询（<, >, BETWEEN）。
	
	- 支持排序查询（ORDER BY）。
	
- **特点：**

- 基于 B+ 树结构，节点有序排列。
	
	- 对等值查询、范围查询、排序等操作效果最好。
	
	- 常用于普通索引和 UNIQUE 索引。
	
- **示例：**

```sql
	CREATE TABLE orders (
		id INT PRIMARY KEY,
		order_date DATE,
		INDEX idx_order_date(order_date) USING BTREE
	);
	```

#### 2. HASH 索引
- **用途：** 提供基于哈希表的快速等值查询。

- **支持的存储引擎：** MEMORY。

- **应用场景：**

- 适合等值查询（=），如主键查找或查询特定值。
	
- **特点：**

- 不支持范围查询（<, >, BETWEEN）。
	
	- 哈希索引无法保持有序，因此不支持 ORDER BY 和部分聚合操作。
	
	- 查询效率极高，但需要额外的内存开销。
	
- **示例：**

```sql
	CREATE TABLE cache_data (
		id INT PRIMARY KEY,
		data VARCHAR(255),
		INDEX idx_id(id) USING HASH
	);
	```

### 索引类型与方法的关系

- **NORMAL 索引** 和 **UNIQUE索引** 通常使用 **BTREE** 方法。

- **FULLTEXT 索引** 使用的是 MySQL 专门的全文检索算法。

- **SPATIAL 索引** 使用 R 树算法（不是 BTREE 或 HASH）。

### 索引选型建议

1. **普通查询（WHERE 条件）**

- 使用 NORMAL 索引（BTREE）。
	
	- 适合等值查询、范围查询、排序。

2. **全文搜索**

- 使用 FULLTEXT 索引，配合 MATCH ... AGAINST 查询。

3. **地理空间数据**

- 使用 SPATIAL 索引，结合 GIS 函数查询。

4. **唯一性要求**

- 使用 UNIQUE 索引，确保数据唯一性。

5. **高并发等值查询**

- 在 MEMORY 表中使用 HASH 索引。

暂无评论

请登录后评论