按 Enter 键跳转到正文

MySQL索引类型

发布于 2024年4月23日，作者 Leanku

MySQL索引类型

索引是帮助MySQL高效获取数据的数据结构，类似于书籍的目录，可以大大加快查询速度。

一、索引类型

1. B-Tree 索引（最常用）

特点

  • 默认的索引类型
  • 适用于全键值、键值范围或键值前缀查找
  • 支持排序和分组

创建语法

 1-- 单列索引
 2CREATE INDEX idx_name ON table_name(column_name);
 3
 4-- 多列复合索引
 5CREATE INDEX idx_name ON table_name(col1, col2, col3);
 6
 7-- 创建表时指定
 8CREATE TABLE users (
 9    id INT PRIMARY KEY,
10    name VARCHAR(50),
11    email VARCHAR(100),
12    age INT,
13    INDEX idx_name (name),
14    INDEX idx_email_age (email, age)
15);

适用查询类型

 1-- 全值匹配
 2SELECT * FROM users WHERE name = 'John';
 3
 4-- 前缀匹配（最左前缀）
 5SELECT * FROM users WHERE name LIKE 'Joh%';
 6
 7-- 范围查询
 8SELECT * FROM users WHERE age BETWEEN 20 AND 30;
 9
10-- 精确匹配左列 + 范围匹配右列
11SELECT * FROM users WHERE email = 'john@example.com' AND age > 25;
12
13-- 排序
14SELECT * FROM users ORDER BY name;
15
16-- 分组
17SELECT COUNT(*), age FROM users GROUP BY age;

最左前缀原则示例

 1-- 复合索引: (last_name, first_name, age)
 2
 3-- 使用索引的情况
 4SELECT * FROM users WHERE last_name = 'Smith';
 5SELECT * FROM users WHERE last_name = 'Smith' AND first_name = 'John';
 6SELECT * FROM users WHERE last_name = 'Smith' AND first_name = 'John' AND age = 30;
 7SELECT * FROM users WHERE last_name = 'Smith' AND age > 25; -- 只使用last_name部分
 8
 9-- 不能使用索引的情况
10SELECT * FROM users WHERE first_name = 'John'; -- 跳过了last_name
11SELECT * FROM users WHERE age = 30; -- 跳过了前两列

2. 哈希索引

特点

  • 基于哈希表实现
  • 只能用于等值比较（=, IN）
  • 不支持范围查询和排序
  • Memory存储引擎默认索引类型

创建语法

1-- 只能在MEMORY表上创建哈希索引
2CREATE TABLE memory_table (
3    id INT,
4    data VARCHAR(100),
5    INDEX USING HASH (id)
6) ENGINE=MEMORY;
7
8-- 或者
9CREATE INDEX idx_hash ON memory_table(id) USING HASH;

适用场景

1-- 等值查询（快速）
2SELECT * FROM memory_table WHERE id = 100;
3
4-- 不支持范围查询
5SELECT * FROM memory_table WHERE id > 100; -- 不会使用哈希索引

自适应哈希索引（InnoDB）

InnoDB会自动在内存中为频繁访问的索引页创建哈希索引，这是自动的，无需手动创建。

3. 全文索引（FULLTEXT）

特点

  • 用于全文搜索
  • 支持自然语言搜索和布尔搜索
  • 只能用于MyISAM和InnoDB存储引擎

创建语法

 1-- 创建全文索引
 2CREATE TABLE articles (
 3    id INT PRIMARY KEY AUTO_INCREMENT,
 4    title VARCHAR(200),
 5    content TEXT,
 6    FULLTEXT(title, content)
 7);
 8
 9-- 或者单独创建
10CREATE FULLTEXT INDEX idx_ft_content ON articles(content);

使用示例

 1-- 自然语言搜索
 2SELECT * FROM articles 
 3WHERE MATCH(title, content) AGAINST('MySQL database' IN NATURAL LANGUAGE MODE);
 4
 5-- 布尔搜索
 6SELECT * FROM articles 
 7WHERE MATCH(title, content) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);
 8
 9-- 相关性排序
10SELECT 
11    id,
12    title,
13    MATCH(title, content) AGAINST('MySQL performance') as relevance
14FROM articles 
15WHERE MATCH(title, content) AGAINST('MySQL performance')
16ORDER BY relevance DESC;

布尔搜索操作符

  • + 必须包含

  • - 必须不包含

  • > 提高相关性

  • < 降低相关性

  • () 分组

  • ~ 否定相关性

  • * 通配符

  • " 短语搜索

4. 空间索引（SPATIAL）

特点

  • 用于地理空间数据
  • 支持空间数据类型的查询
  • 只能用于MyISAM和InnoDB（5.7+）存储引擎

创建语法

 1-- 创建空间数据表
 2CREATE TABLE locations (
 3    id INT PRIMARY KEY AUTO_INCREMENT,
 4    name VARCHAR(100),
 5    position POINT NOT NULL,
 6    SPATIAL INDEX(position)
 7);
 8
 9-- 插入空间数据
10INSERT INTO locations (name, position) VALUES 
11('Location A', ST_GeomFromText('POINT(116.3974 39.9093)')),
12('Location B', ST_GeomFromText('POINT(121.4737 31.2304)'));

使用示例

 1-- 查找范围内的点
 2SET @bbox = ST_GeomFromText('POLYGON((116.0 39.0, 117.0 39.0, 117.0 40.0, 116.0 40.0, 116.0 39.0))');
 3SELECT name FROM locations 
 4WHERE ST_Within(position, @bbox);
 5
 6-- 计算距离（MySQL 5.7+）
 7SELECT 
 8    name,
 9    ST_Distance_Sphere(position, ST_GeomFromText('POINT(116.3974 39.9093)')) as distance_meters
10FROM locations 
11ORDER BY distance_meters ASC;

5. R-Tree 索引

特点

  • 用于多维数据的索引
  • 主要用于空间索引的底层实现
  • 支持范围查询和多维数据查询

6. 聚簇索引 vs 非聚簇索引

聚簇索引（InnoDB主键索引）

1-- InnoDB表的主键就是聚簇索引
2CREATE TABLE users (
3    id INT PRIMARY KEY,  -- 聚簇索引
4    name VARCHAR(50),
5    email VARCHAR(100)
6);
7
8-- 数据按主键顺序物理存储
9-- 叶子节点包含完整的数据行

非聚簇索引（二级索引）

1-- 非主键索引都是非聚簇索引
2CREATE INDEX idx_email ON users(email);
3
4-- 叶子节点包含主键值，需要回表查询
5-- 查询过程：idx_email -> 主键 -> 数据行

7. 覆盖索引

索引包含所有需要查询的字段，无需回表查询。

示例

1-- 创建复合索引
2CREATE INDEX idx_covering ON users(email, name, age);
3
4-- 覆盖索引查询（Extra: Using index）
5EXPLAIN SELECT email, name FROM users WHERE email = 'test@example.com';
6
7-- 非覆盖索引查询（需要回表）
8EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

8. 前缀索引

适用场景

  • 文本字段很长时
  • 减少索引大小
  • 提高索引效率

创建语法

 1-- 为长文本字段创建前缀索引
 2CREATE TABLE logs (
 3    id INT PRIMARY KEY,
 4    url VARCHAR(500),
 5    INDEX idx_url_prefix (url(100))  -- 前100个字符
 6);
 7
 8-- 选择合适的前缀长度
 9SELECT 
10    COUNT(DISTINCT LEFT(url, 10)) as len_10,
11    COUNT(DISTINCT LEFT(url, 20)) as len_20,
12    COUNT(DISTINCT LEFT(url, 50)) as len_50,
13    COUNT(DISTINCT url) as total
14FROM logs;

9. 唯一索引

特点

  • 保证列值的唯一性
  • 允许NULL值（但只能有一个NULL）
  • 提高查询性能

创建语法

1-- 创建唯一索引
2CREATE UNIQUE INDEX idx_unique_email ON users(email);
3
4-- 创建表时指定
5CREATE TABLE users (
6    id INT PRIMARY KEY,
7    email VARCHAR(100) UNIQUE,
8    username VARCHAR(50) UNIQUE
9);

二、索引管理命令

查看索引

 1-- 查看表索引
 2SHOW INDEX FROM users;
 3
 4-- 查看索引信息
 5SELECT 
 6    TABLE_NAME,
 7    INDEX_NAME,
 8    SEQ_IN_INDEX,
 9    COLUMN_NAME,
10    INDEX_TYPE,
11    CARDINALITY
12FROM INFORMATION_SCHEMA.STATISTICS 
13WHERE TABLE_NAME = 'users';

维护索引

 1-- 重建索引（InnoDB）
 2ALTER TABLE users ENGINE=InnoDB;
 3
 4-- 优化表（重建索引并整理碎片）
 5OPTIMIZE TABLE users;
 6
 7-- 分析索引使用情况
 8ANALYZE TABLE users;
 9
10-- 强制使用/忽略索引
11SELECT * FROM users USE INDEX (idx_email) WHERE email = 'test@example.com';
12SELECT * FROM users IGNORE INDEX (idx_email) WHERE email = 'test@example.com';

三、索引设计最佳实践

1. 选择合适索引列

 1-- 适合索引的列
 2- WHERE子句中的列
 3- JOIN关联的列  
 4- ORDER BY/GROUP BY的列
 5- 选择性高的列（不同值多的列）
 6
 7-- 计算选择性
 8SELECT 
 9    COUNT(DISTINCT column_name) * 1.0 / COUNT(*) as selectivity
10FROM table_name;

2. 复合索引设计

1-- 好的复合索引设计
2CREATE INDEX idx_optimized ON orders(user_id, status, create_date);
3
4-- 支持的查询
5SELECT * FROM orders WHERE user_id = 123 AND status = 'completed';
6SELECT * FROM orders WHERE  status = 'completed' AND user_id = 123; --注意，这个也会走到索引，where顺序会自动调整
7SELECT * FROM orders WHERE user_id = 123 ORDER BY create_date DESC;
8SELECT * FROM orders WHERE user_id = 123 AND status IN ('pending', 'processing');

3. 避免索引失效

1-- 会导致索引失效的操作
2SELECT * FROM users WHERE YEAR(create_time) = 2023;  -- 对索引列使用函数
3SELECT * FROM users WHERE amount * 2 > 1000;         -- 对索引列进行运算
4SELECT * FROM users WHERE name LIKE '%john%';        -- 前导通配符
5SELECT * FROM users WHERE email != 'test@example.com'; -- 不等于操作

性能测试示例

创建测试表

 1CREATE TABLE index_test (
 2    id INT PRIMARY KEY AUTO_INCREMENT,
 3    name VARCHAR(100),
 4    email VARCHAR(100),
 5    age INT,
 6    created_at DATETIME,
 7    INDEX idx_name (name),
 8    INDEX idx_email_age (email, age),
 9    INDEX idx_created (created_at)
10);
11
12-- 插入测试数据
13INSERT INTO index_test (name, email, age, created_at)
14SELECT 
15    CONCAT('User', n),
16    CONCAT('user', n, '@example.com'),
17    FLOOR(RAND() * 80) + 18,
18    DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 365) DAY)
19FROM (SELECT @n := @n + 1 as n FROM 
20    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t1,
21    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t2,
22    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t3,
23    (SELECT @n := 0) t4
24) numbers;

测试不同查询

 1-- 使用索引的查询
 2EXPLAIN SELECT * FROM index_test WHERE name = 'User100';
 3
 4-- 使用复合索引
 5EXPLAIN SELECT * FROM index_test WHERE email = 'user100@example.com' AND age > 25;
 6
 7-- 范围查询使用索引
 8EXPLAIN SELECT * FROM index_test WHERE created_at > '2023-01-01';
 9
10-- 索引失效的查询
11EXPLAIN SELECT * FROM index_test WHERE LEFT(name, 4) = 'User';

四、总结

索引类型适用场景优点缺点
B-Tree大多数场景支持范围查询、排序索引大小较大
哈希索引等值查询查询速度快不支持范围查询
全文索引文本搜索支持全文搜索维护成本高
空间索引地理数据支持空间查询使用复杂
聚簇索引主键数据访问快插入速度可能慢

选择合适的索引类型和设计良好的索引策略是数据库性能优化的关键。需要根据具体的业务场景、数据特性和查询模式来设计索引。

发布在 Database

发表评论