MySQL 索引详细说明以及案例

MySQL 索引详细说明

1. 索引的作用

• 加速查询：索引可以帮助数据库快速定位数据，减少全表扫描的时间。

• 唯一性约束：唯一索引确保列中的值唯一。

• 排序和分组优化：索引可以加速ORDER BY和GROUP BY操作。

2. 索引的类型

• 主键索引（PRIMARY KEY）：唯一且非空，每个表只能有一个主键索引。

• 唯一索引（UNIQUE）：确保列中的值唯一，允许有空值。

• 普通索引（INDEX）：最基本的索引，没有唯一性约束。

• 全文索引（FULLTEXT）：用于全文搜索，适用于大文本字段。

• 组合索引（Composite Index）：多个列组合而成的索引。

3. 索引的创建

• 创建表时创建索引：

  CREATE TABLE users (
      id INT PRIMARY KEY,
      username VARCHAR(50) UNIQUE,
      email VARCHAR(100),
      INDEX idx_email (email)
  );

• 在已有表上创建索引：

  CREATE INDEX idx_username ON users(username);

• 创建组合索引：

  CREATE INDEX idx_name_email ON users(username, email);

4. 索引的删除

• 删除索引：

  DROP INDEX idx_username ON users;

5. 索引的使用场景

• 频繁查询的列：如WHERE条件中的列。

• 排序和分组列：如ORDER BY和GROUP BY中的列。

• 连接列：如JOIN操作中的列。

6. 索引的注意事项

• 索引的维护成本：索引会增加插入、更新和删除操作的开销。

• 选择合适的索引列：避免对低选择性的列（如性别）创建索引。

• 避免过度索引：过多的索引会增加存储空间和维护成本。

案例

案例1：创建索引并查询

-- 创建表
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

-- 插入数据
INSERT INTO employees (id, name, department, salary) VALUES
(1, 'Alice', 'HR', 50000),
(2, 'Bob', 'Engineering', 60000),
(3, 'Charlie', 'HR', 55000);

-- 创建索引
CREATE INDEX idx_department ON employees(department);

-- 查询使用索引
EXPLAIN SELECT * FROM employees WHERE department = 'HR';

案例2：组合索引

-- 创建组合索引
CREATE INDEX idx_department_salary ON employees(department, salary);

-- 查询使用组合索引
EXPLAIN SELECT * FROM employees WHERE department = 'HR' AND salary > 50000;

案例3：删除索引

-- 删除索引
DROP INDEX idx_department ON employees;

通过以上案例，可以更好地理解MySQL索引的创建、使用和删除。