MySQL索引创建与索引类型详解

1. 索引概述

1.1 什么是索引

索引（Index）是帮助MySQL高效获取数据的数据结构。索引类似于书籍的目录，可以快速定位到数据所在的位置，而不需要扫描整个表。

1.2 索引的作用

  • 提高查询速度: 通过索引可以快速定位到数据，避免全表扫描
  • 加速排序: 索引可以帮助ORDER BY和GROUP BY操作
  • 保证数据唯一性: 唯一索引可以保证数据的唯一性
  • 加速表连接: 外键索引可以加速JOIN操作

1.3 索引的缺点

  • 占用存储空间: 索引需要额外的存储空间
  • 降低写入性能: INSERT、UPDATE、DELETE操作需要维护索引
  • 增加维护成本: 索引需要定期维护和优化

2. MySQL索引类型

2.1 按存储结构分类

索引类型 说明 适用场景
B-Tree索引 最常用的索引类型，支持范围查询 大多数场景
Hash索引 基于哈希表，只支持等值查询 等值查询场景
Full-Text索引 全文索引，用于文本搜索 文本搜索场景
Spatial索引 空间索引，用于地理数据 GIS应用

2.2 按功能分类

索引类型 说明 特点
普通索引 最基本的索引，没有任何限制 允许重复值
唯一索引 索引列的值必须唯一 不允许重复值
主键索引 特殊的唯一索引，不允许NULL 表的主键
复合索引 多个列组合的索引 支持多列查询
前缀索引 只索引列的前几个字符 节省存储空间

3. B-Tree索引

3.1 B-Tree索引概述

B-Tree（Balanced Tree）索引是MySQL最常用的索引类型，InnoDB和MyISAM存储引擎都支持B-Tree索引。

特点:

  • 支持范围查询（>, <, BETWEEN, LIKE等）
  • 支持排序（ORDER BY）
  • 支持最左前缀匹配
  • 适用于大多数查询场景

3.2 B-Tree索引结构

1
2
3
4
5
6
B-Tree索引结构示例:
                    [50]
                   /    \
              [25]      [75]
             /   \      /   \
          [10] [30]  [60] [90]

3.3 创建B-Tree索引

创建普通索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 方法1: 创建表时指定索引
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    INDEX idx_username (username),
    INDEX idx_email (email)
);

-- 方法2: 使用ALTER TABLE添加索引
ALTER TABLE users ADD INDEX idx_username (username);

-- 方法3: 使用CREATE INDEX创建索引
CREATE INDEX idx_username ON users (username);

创建唯一索引

1
2
3
4
5
-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON users (email);

-- 或使用ALTER TABLE
ALTER TABLE users ADD UNIQUE INDEX idx_email (email);

创建复合索引

1
2
3
4
5
6
7
-- 创建复合索引（多列索引）
CREATE INDEX idx_name_age ON users (username, age);

-- 复合索引支持最左前缀匹配
-- 可以使用: WHERE username = 'xxx'
-- 可以使用: WHERE username = 'xxx' AND age = 20
-- 不能使用: WHERE age = 20 (不满足最左前缀)

3.4 B-Tree索引使用示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 创建测试表
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    category_id INT,
    price DECIMAL(10, 2),
    created_at DATETIME,
    INDEX idx_category (category_id),
    INDEX idx_price (price),
    INDEX idx_category_price (category_id, price)
);

-- 使用索引的查询
-- 1. 单列索引查询
SELECT * FROM products WHERE category_id = 1;

-- 2. 复合索引查询（最左前缀）
SELECT * FROM products WHERE category_id = 1 AND price > 100;

-- 3. 范围查询
SELECT * FROM products WHERE price BETWEEN 100 AND 500;

-- 4. 排序查询
SELECT * FROM products ORDER BY price DESC;

4. Hash索引

4.1 Hash索引概述

Hash索引基于哈希表实现，只支持等值查询（=, IN），不支持范围查询和排序。

特点:

  • 查询速度非常快（O(1)）
  • 只支持等值查询
  • 不支持范围查询
  • 不支持排序
  • 只适用于Memory存储引擎

4.2 创建Hash索引

1
2
3
4
5
6
7
8
9
-- Hash索引只适用于Memory存储引擎
CREATE TABLE hash_table (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    INDEX USING HASH (name)
) ENGINE=MEMORY;

-- 或使用ALTER TABLE
ALTER TABLE hash_table ADD INDEX USING HASH (name);

4.3 Hash索引使用示例

1
2
3
4
5
-- Hash索引只支持等值查询
SELECT * FROM hash_table WHERE name = 'test';

-- 不支持范围查询（不会使用索引）
SELECT * FROM hash_table WHERE name > 'test';

5. Full-Text全文索引

5.1 Full-Text索引概述

Full-Text索引用于全文搜索，支持对文本内容进行搜索。

特点:

  • 支持全文搜索
  • 支持中文分词（MySQL 5.7+）
  • 只适用于CHAR、VARCHAR、TEXT类型
  • 只适用于MyISAM和InnoDB存储引擎（MySQL 5.6+）

5.2 创建Full-Text索引

1
2
3
4
5
6
7
8
9
10
11
-- 创建全文索引
CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT INDEX idx_content (content),
    FULLTEXT INDEX idx_title_content (title, content)
) ENGINE=InnoDB;

-- 或使用ALTER TABLE
ALTER TABLE articles ADD FULLTEXT INDEX idx_content (content);

5.3 Full-Text索引使用示例

1
2
3
4
5
6
7
8
9
10
11
-- 全文搜索（自然语言模式）
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('MySQL索引' IN NATURAL LANGUAGE MODE);

-- 全文搜索（布尔模式）
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);

-- 全文搜索（查询扩展模式）
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('数据库' WITH QUERY EXPANSION);

5.4 Full-Text索引配置

1
2
3
4
5
6
7
-- 查看全文索引最小词长度
SHOW VARIABLES LIKE 'ft_min_word_len';

-- 设置全文索引最小词长度（需要重启MySQL）
-- 在my.cnf中设置
[mysqld]
ft_min_word_len = 2

6. 前缀索引

6.1 前缀索引概述

前缀索引只索引列的前几个字符，可以节省存储空间，适用于较长的字符串列。

6.2 创建前缀索引

1
2
3
4
5
-- 创建前缀索引（只索引前10个字符）
CREATE INDEX idx_email_prefix ON users (email(10));

-- 或使用ALTER TABLE
ALTER TABLE users ADD INDEX idx_email_prefix (email(10));

6.3 前缀索引长度选择

1
2
3
4
5
6
7
8
9
-- 计算前缀索引的最佳长度
SELECT 
    COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS selectivity_5,
    COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS selectivity_10,
    COUNT(DISTINCT LEFT(email, 15)) / COUNT(*) AS selectivity_15,
    COUNT(DISTINCT email) / COUNT(*) AS selectivity_full
FROM users;

-- 选择性越高越好，选择接近完整列选择性的最小长度

7. 复合索引

7.1 复合索引概述

复合索引（联合索引）是在多个列上创建的索引，支持多列查询。

7.2 创建复合索引

1
2
3
4
5
-- 创建复合索引
CREATE INDEX idx_category_price ON products (category_id, price);

-- 复合索引列的顺序很重要
-- 应该将选择性高的列放在前面

7.3 复合索引最左前缀原则

1
2
3
4
5
6
7
8
9
10
-- 复合索引: (category_id, price, created_at)

-- 可以使用索引的查询
SELECT * FROM products WHERE category_id = 1;
SELECT * FROM products WHERE category_id = 1 AND price > 100;
SELECT * FROM products WHERE category_id = 1 AND price > 100 AND created_at > '2024-01-01';

-- 不能使用索引的查询（不满足最左前缀）
SELECT * FROM products WHERE price > 100;
SELECT * FROM products WHERE price > 100 AND created_at > '2024-01-01';

7.4 复合索引列顺序选择

1
2
3
4
5
-- 原则1: 选择性高的列放在前面
-- 选择性 = COUNT(DISTINCT column) / COUNT(*)

-- 原则2: 经常一起查询的列组合
-- 原则3: 考虑排序需求（ORDER BY的列放在后面）

8. 索引创建方法总结

8.1 创建索引的三种方式

方式1: 创建表时指定索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    age INT,
    created_at DATETIME,
    
    -- 普通索引
    INDEX idx_username (username),
    
    -- 唯一索引
    UNIQUE INDEX idx_email (email),
    
    -- 复合索引
    INDEX idx_age_created (age, created_at),
    
    -- 前缀索引
    INDEX idx_email_prefix (email(10))
);

方式2: 使用ALTER TABLE添加索引

1
2
3
4
5
6
7
8
9
10
11
-- 添加普通索引
ALTER TABLE users ADD INDEX idx_username (username);

-- 添加唯一索引
ALTER TABLE users ADD UNIQUE INDEX idx_email (email);

-- 添加复合索引
ALTER TABLE users ADD INDEX idx_age_created (age, created_at);

-- 添加主键索引（如果表没有主键）
ALTER TABLE users ADD PRIMARY KEY (id);

方式3: 使用CREATE INDEX创建索引

1
2
3
4
5
6
7
8
9
10
11
-- 创建普通索引
CREATE INDEX idx_username ON users (username);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON users (email);

-- 创建复合索引
CREATE INDEX idx_age_created ON users (age, created_at);

-- 创建全文索引
CREATE FULLTEXT INDEX idx_content ON articles (content);

8.2 删除索引

1
2
3
4
5
6
7
8
-- 方法1: 使用DROP INDEX
DROP INDEX idx_username ON users;

-- 方法2: 使用ALTER TABLE
ALTER TABLE users DROP INDEX idx_username;

-- 删除主键索引
ALTER TABLE users DROP PRIMARY KEY;

8.3 查看索引

1
2
3
4
5
6
7
8
-- 查看表的所有索引
SHOW INDEX FROM users;

-- 查看表的创建语句（包含索引信息）
SHOW CREATE TABLE users;

-- 使用EXPLAIN查看查询是否使用索引
EXPLAIN SELECT * FROM users WHERE username = 'test';

9. 索引最佳实践

9.1 索引设计原则

  1. 选择性高的列: 选择区分度高的列创建索引
  2. 经常查询的列: 为WHERE、JOIN、ORDER BY中的列创建索引
  3. 避免过多索引: 索引过多会影响写入性能
  4. 考虑复合索引: 多个列经常一起查询时使用复合索引
  5. 最左前缀原则: 复合索引要遵循最左前缀原则

9.2 索引使用建议

应该创建索引的情况

  • 主键和外键
  • 经常出现在WHERE子句中的列
  • 经常用于JOIN的列
  • 经常用于ORDER BY和GROUP BY的列
  • 选择性高的列

不应该创建索引的情况

  • 选择性很低的列（如性别、状态等）
  • 很少用于查询的列
  • 经常更新的列（影响写入性能）
  • 数据量很小的表（全表扫描可能更快）

9.3 索引优化技巧

技巧1: 使用覆盖索引

1
2
3
4
5
6
-- 覆盖索引：索引包含查询所需的所有列
-- 创建覆盖索引
CREATE INDEX idx_covering ON products (category_id, name, price);

-- 查询只需要索引中的列，不需要回表
SELECT category_id, name, price FROM products WHERE category_id = 1;

技巧2: 使用索引提示

1
2
3
4
5
6
7
8
-- 强制使用索引
SELECT * FROM users USE INDEX (idx_username) WHERE username = 'test';

-- 忽略索引
SELECT * FROM users IGNORE INDEX (idx_username) WHERE username = 'test';

-- 强制使用索引（如果可用）
SELECT * FROM users FORCE INDEX (idx_username) WHERE username = 'test';

技巧3: 分析索引使用情况

1
2
3
4
5
6
7
8
9
10
11
12
-- 查看索引使用统计
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    COUNT_FETCH,
    COUNT_INSERT,
    COUNT_UPDATE,
    COUNT_DELETE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_database'
ORDER BY COUNT_FETCH DESC;

10. 索引性能分析

10.1 使用EXPLAIN分析索引

1
2
3
4
5
6
7
8
-- 分析查询执行计划
EXPLAIN SELECT * FROM users WHERE username = 'test';

-- 关键字段说明
-- type: 访问类型（ALL=全表扫描, index=索引扫描, ref=索引查找）
-- key: 使用的索引
-- rows: 扫描的行数
-- Extra: 额外信息（Using index=覆盖索引）

10.2 索引效果评估

1
2
3
4
5
6
7
-- 查看索引选择性
SELECT 
    COUNT(DISTINCT username) / COUNT(*) AS username_selectivity,
    COUNT(DISTINCT email) / COUNT(*) AS email_selectivity
FROM users;

-- 选择性越高，索引效果越好

10.3 索引维护

1
2
3
4
5
6
7
8
-- 分析表（更新索引统计信息）
ANALYZE TABLE users;

-- 优化表（重建索引，回收空间）
OPTIMIZE TABLE users;

-- 检查表
CHECK TABLE users;

11. 实战案例

11.1 案例1: 电商商品表索引设计

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
-- 商品表结构
CREATE TABLE products (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    category_id INT NOT NULL,
    brand_id INT,
    price DECIMAL(10, 2) NOT NULL,
    stock INT DEFAULT 0,
    status TINYINT DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    
    -- 索引设计
    INDEX idx_category (category_id),
    INDEX idx_brand (brand_id),
    INDEX idx_status (status),
    INDEX idx_category_price (category_id, price),
    INDEX idx_created (created_at),
    INDEX idx_name (name(50))  -- 前缀索引
) ENGINE=InnoDB;

-- 常见查询场景
-- 1. 按分类查询
SELECT * FROM products WHERE category_id = 1;

-- 2. 按分类和价格范围查询
SELECT * FROM products 
WHERE category_id = 1 AND price BETWEEN 100 AND 500;

-- 3. 按状态和创建时间排序
SELECT * FROM products 
WHERE status = 1 
ORDER BY created_at DESC 
LIMIT 20;

11.2 案例2: 用户表索引设计

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- 用户表结构
CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    password VARCHAR(255) NOT NULL,
    status TINYINT DEFAULT 1,
    created_at DATETIME NOT NULL,
    last_login_at DATETIME,
    
    -- 索引设计
    UNIQUE INDEX idx_username (username),
    UNIQUE INDEX idx_email (email),
    UNIQUE INDEX idx_phone (phone),
    INDEX idx_status (status),
    INDEX idx_created (created_at),
    INDEX idx_status_created (status, created_at)
) ENGINE=InnoDB;

-- 常见查询场景
-- 1. 登录查询
SELECT * FROM users WHERE username = 'test' AND password = 'xxx';

-- 2. 按状态和时间查询活跃用户
SELECT * FROM users 
WHERE status = 1 AND created_at > '2024-01-01';

11.3 案例3: 订单表索引设计

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- 订单表结构
CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    order_no VARCHAR(50) NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status TINYINT NOT NULL,
    created_at DATETIME NOT NULL,
    paid_at DATETIME,
    
    -- 索引设计
    UNIQUE INDEX idx_order_no (order_no),
    INDEX idx_user (user_id),
    INDEX idx_status (status),
    INDEX idx_created (created_at),
    INDEX idx_user_status (user_id, status),
    INDEX idx_status_created (status, created_at)
) ENGINE=InnoDB;

-- 常见查询场景
-- 1. 查询用户订单
SELECT * FROM orders WHERE user_id = 1;

-- 2. 查询用户特定状态的订单
SELECT * FROM orders WHERE user_id = 1 AND status = 2;

-- 3. 查询最近订单
SELECT * FROM orders 
WHERE status = 2 
ORDER BY created_at DESC 
LIMIT 20;

12. 索引常见问题

12.1 索引不生效的原因

  1. 不满足最左前缀原则: 复合索引查询不满足最左前缀
  2. 函数操作: 对索引列使用函数（如WHERE YEAR(created_at) = 2024）
  3. 类型不匹配: 查询条件类型与索引列类型不匹配
  4. OR条件: 多个OR条件可能导致索引失效
  5. NULL值: 索引列包含大量NULL值

12.2 索引优化建议

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 问题1: 函数操作导致索引失效
-- 错误写法
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- 正确写法
SELECT * FROM orders 
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- 问题2: 类型不匹配
-- 错误写法（如果id是INT类型）
SELECT * FROM users WHERE id = '123';

-- 正确写法
SELECT * FROM users WHERE id = 123;

-- 问题3: LIKE查询
-- 前导%导致索引失效
SELECT * FROM users WHERE username LIKE '%test%';

-- 非前导%可以使用索引
SELECT * FROM users WHERE username LIKE 'test%';

13. 命令总结

13.1 索引创建命令

命令 功能 示例
CREATE INDEX 创建索引 CREATE INDEX idx_name ON table (col)
CREATE UNIQUE INDEX 创建唯一索引 CREATE UNIQUE INDEX idx_email ON users (email)
CREATE FULLTEXT INDEX 创建全文索引 CREATE FULLTEXT INDEX idx_content ON articles (content)
ALTER TABLE ADD INDEX 添加索引 ALTER TABLE users ADD INDEX idx_name (name)
ALTER TABLE ADD UNIQUE 添加唯一索引 ALTER TABLE users ADD UNIQUE idx_email (email)

13.2 索引管理命令

命令 功能 示例
DROP INDEX 删除索引 DROP INDEX idx_name ON table
ALTER TABLE DROP INDEX 删除索引 ALTER TABLE users DROP INDEX idx_name
SHOW INDEX 查看索引 SHOW INDEX FROM users
ANALYZE TABLE 分析表 ANALYZE TABLE users
OPTIMIZE TABLE 优化表 OPTIMIZE TABLE users

13.3 索引查询命令

命令 功能 示例
EXPLAIN 分析查询计划 EXPLAIN SELECT * FROM users WHERE id = 1
SHOW CREATE TABLE 查看表结构 SHOW CREATE TABLE users
SHOW INDEX 查看索引信息 SHOW INDEX FROM users