MySQL索引

1. 介绍

索引是提升数据库查询性能的关键工具，它通过创建一种数据结构，显著加快数据表中数据的检索速度。良好的索引设计能够显著提高数据库的响应速度和查询效率。

1.1 索引的作用

加速查询：通过索引，可以更快找到所需的数据，减少全表扫描的开销。
提高排序性能：在ORDER BY和GROUP BY操作中，索引可以加速数据的排序。
实现唯一性：通过唯一索引，可以确保某一列的值在表中唯一，避免重复数据的插入。

1.2 索引的类型

单列索引：对单一列建立的索引，适用于简单查询。
复合索引：对多列组合建立的索引，适用于复杂查询。
全文索引：用于高效处理文本搜索，特别适合char、varchar和text类型的数据。

1.3 索引的成本

存储成本：索引需要额外的存储空间。
维护成本：在插入、更新和删除操作时，索引也需要被更新，因此可能影响性能。

2. 数据结构维度

2.1 BTree索引

特点：
- 采用B+树结构，叶子节点包含数据。
- 支持范围查询，查询效率高。
优势：
- 节点较少，减少I/O次数。
- 快速定位数据，适合大规模数据检索。

2.2 哈希索引

特点：
- 基于哈希表的索引，适用于等值查询。
限制：
- 不支持范围查询，只适用于=和IN操作。

2.3 RTree索引

特点：
- 用于地理数据类型，适合多维数据查询。
替代方案：
- 常用搜索引擎如ElasticSearch来处理复杂的地理查询。

2.4 全文索引

特点：
- 对文本内容进行分词搜索，适合搜索引擎。
替代方案：
- 通常采用ElasticSearch来处理更复杂的全文搜索需求。

3. 底层存储方式

3.1 聚集索引

定义：
- 数据和索引一起存放，表中只能有一个聚集索引。
示例：
- InnoDB引擎的主键索引就是聚集索引。

3.2 非聚集索引

定义：
- 索引结构与数据分开存放，表中可以有多个非聚集索引。
特点：
- 包含指向实际数据的指针。

4. 应用维度

4.1 主键索引

说明：
- 以主键作为索引，自动生成聚集索引。

4.2 唯一索引

说明：
- 确保索引列的值唯一，允许NULL值。

4.3 普通索引

说明：
- 最基本的索引类型，适用于没有唯一性要求的列。

4.4 前缀索引

说明：
- 对字符串类型的列的前几个字符建立索引，适合长文本列。

4.5 覆盖索引

说明：
- 索引中包含查询所需的所有字段，避免回表操作。

4.6 联合索引

说明：
- 由多个列组合而成的索引，适用于多条件查询。

4.7 全文索引

说明：
- 处理文本数据的分词搜索，适用于长文本类型字段。

4.8 空间索引

说明：
- 用于存储和查询地理空间数据。

5. 重要概念

5.1 最左匹配原则

说明：
- 在使用联合索引时，查询条件必须从左到右依次匹配索引列。

5.2 索引下推

说明：
- 在使用索引时，可以在索引层过滤不满足条件的记录，减少回表次数。

5.3 EXPLAIN执行计划

说明：
- 使用EXPLAIN命令查看查询的执行计划，可以分析索引是否生效，主要关注：
- type字段：表示查询的类型，依次为system、const、eq_ref等，越靠前越优。
- key字段：实际使用的索引。
- extra字段：附加信息，如Using index表示使用了覆盖索引。

6. 索引规范建议

数量控制：单张表索引不超过5个。
避免全文索引：除非特殊需求。
避免冗余索引：如index(a,b,c)与index(a,b)重复。
主键存在：每张表必须有主键。
覆盖索引优先：对于频繁查询的字段优先考虑使用覆盖索引。
更新字段注意：频繁更新的字段不适合建立索引。
定期维护：定期删除未使用的索引，保持索引的有效性。

7. 代码案例

-- 创建一个带有注释的 'employees' 表
CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '员工唯一标识符，主键索引（聚集索引）',
    first_name VARCHAR(50) COMMENT '员工的名',
    last_name VARCHAR(50) COMMENT '员工的姓',
    email VARCHAR(100) UNIQUE COMMENT '员工的电子邮件地址，唯一索引',
    department VARCHAR(50) COMMENT '员工所在的部门',
    hire_date DATE COMMENT '员工的雇佣日期'
) COMMENT = '员工信息表';

-- 插入一些测试数据
INSERT INTO employees (first_name, last_name, email, department, hire_date)
VALUES
('John', 'Doe', 'john.doe@example.com', 'HR', '2024-01-15'),  -- 插入员工John Doe，部门HR
('Jane', 'Smith', 'jane.smith@example.com', 'IT', '2024-02-20'),  -- 插入员工Jane Smith，部门IT
('Alice', 'Johnson', 'alice.johnson@example.com', 'Finance', '2024-03-10'),  -- 插入员工Alice Johnson，部门Finance
('Bob', 'Brown', 'bob.brown@example.com', 'IT', '2024-04-22');  -- 插入员工Bob Brown，部门IT

-- 创建普通索引
CREATE INDEX idx_department ON employees (department);
-- 解释：为 'department' 列创建一个非聚集索引，用于加速基于部门的查询。

-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON employees (email);
-- 解释：为 'email' 列创建一个唯一索引，确保电子邮件地址的唯一性。

-- 创建联合索引
CREATE INDEX idx_name_hire_date ON employees (last_name, hire_date);
-- 解释：为 'last_name' 和 'hire_date' 列创建一个联合索引，用于加速基于姓氏和雇佣日期的查询。

-- 创建前缀索引
CREATE INDEX idx_name_prefix ON employees (last_name(10));
-- 解释：为 'last_name' 列的前10个字符创建索引，用于加速基于姓氏前缀的查询。

-- 查询使用覆盖索引
SELECT first_name, last_name FROM employees WHERE department = 'IT';
-- 解释：由于 'idx_department' 索引包含了查询所需的所有列（'department', 'first_name', 'last_name'），因此使用了覆盖索引。

-- 查询使用联合索引
SELECT * FROM employees WHERE last_name = 'Doe' AND hire_date = '2024-01-15';
-- 解释：查询同时涉及 'last_name' 和 'hire_date' 列，利用了 'idx_name_hire_date' 联合索引。

-- 使用 EXPLAIN 分析查询
EXPLAIN SELECT * FROM employees WHERE department = 'IT';
-- 解释：使用 EXPLAIN 查看查询的执行计划，分析索引是否生效。

-- 使用 EXPLAIN 分析联合索引查询
EXPLAIN SELECT * FROM employees WHERE last_name = 'Doe' AND hire_date > '2024-01-01';
-- 解释：通过下推条件直接在索引中进行过滤，减少回表次数。

-- 哈希索引示例（Memory 存储引擎特有，Mysql一般用InnoDB）
CREATE TABLE hash_example (
    id INT PRIMARY KEY,  -- 唯一标识符
   

 value VARCHAR(100)  -- 存储值
) ENGINE=MEMORY;  -- 使用Memory存储引擎

CREATE INDEX idx_value ON hash_example (value) USING HASH;
-- 解释：哈希索引适用于等值查询，不适用于范围查询。

-- 创建全文索引（适用于 MyISAM 引擎，一般使用ElasticSearch）
CREATE FULLTEXT INDEX idx_fulltext ON employees (first_name, last_name);
-- 解释：全文索引用于处理文本数据的分词搜索，MyISAM 引擎支持。