Mysql索引介绍、应用场景、索引类型、索引失效等问题

原创已于 2025-08-01 11:39:25 修改 · 1.2k 阅读

12 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

于 2025-01-22 18:16:00 首次发布

数据库相关技术栈专栏收录该内容

12 篇文章

订阅专栏

一、索引的概念

索引是数据库表中一列或多列的值的集合，它类似于书籍的目录，能够快速定位到数据行，避免全表扫描，从而提高查询效率。

二、索引的类型

B-Tree索引
- 描述：MySQL的默认索引类型，适用于大多数场景，特别是范围查询。
- 应用：适用于等值查询、范围查询（如 BETWEEN、>, <）、排序等。
哈希索引
- 描述：基于哈希表实现，只能用于等值查询。
- 应用：适用于频繁的等值查找，通常与内存表（如Memory存储引擎）配合使用。
全文索引
- 描述：用于对文本数据的全文搜索。
- 应用：适用于大文本字段（如TEXT、VARCHAR），例如文章内容搜索。
空间索引
- 描述：用于地理信息系统（GIS），支持空间数据类型。
- 应用：适用于地理数据的范围查询，如查找某个点附近的所有地点。

三、索引的创建与管理

创建索引：使用CREATE INDEX语句，可以在表创建时定义索引，也可以在表创建后添加索引。

CREATE INDEX idx_name ON table_name(column_name);

查看索引：使用SHOW INDEX FROM table_name;可以查看表的所有索引信息。
删除索引：使用DROP INDEX语句可以删除索引。

DROP INDEX idx_name ON table_name;

四、索引的优缺点

优点

提高查询性能：加快数据检索速度。
加速排序操作：通过索引可以避免全表扫描，提高排序性能。
唯一性约束：通过索引可以确保列值的唯一性。

缺点

占用存储空间：索引会增加数据库的存储需求。
降低写入性能：每次插入、更新或删除操作时，索引也需要更新，可能影响性能。
维护成本：随着数据的增加，索引的维护和重建可能会变得复杂。

五、应用场景

高频查询：对于频繁执行的查询，特别是使用WHERE条件的查询，建议创建索引。
排序和分组：在ORDER BY和GROUP BY子句中使用的列，应考虑创建索引。
联合查询：在连接多个表时，连接条件中的列应考虑添加索引。
大数据表：对于数据量较大的表，索引的作用更为明显，可以显著提高查询性能。
唯一性约束：需要确保某列的值唯一时，应创建唯一索引。

六、创建索引类型

1. 主键索引（Primary Key）

描述：主键索引是一种特殊的唯一索引，用于唯一标识表中的每一行。每个表只能有一个主键。
特点：
- 主键列不允许有NULL值。
- 自动创建唯一性约束。
应用：适用于需要唯一标识数据行的场景。

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

2. 唯一索引（Unique Index）

描述：唯一索引确保索引列中的所有值都是唯一的，但可以包含NULL值（多个NULL值是允许的）。
特点：
- 可以有多个唯一索引。
- 可以用于需要唯一性约束的列。
应用：适用于如电子邮件地址、用户名等需要唯一性的场景。

CREATE TABLE users (
    id INT,
    email VARCHAR(100) UNIQUE,
    name VARCHAR(100)
);

3. 普通索引（Index）

描述：最基本的索引类型，用于提高查询速度，但不保证唯一性。
特点：
- 可以包含重复值。
- 没有唯一性约束。
应用：适用于查询频繁的列。

CREATE INDEX idx_name ON users(name);

4. 组合索引（Composite Index）

描述：由多个列组成的索引，可以提高多列查询的性能。
特点：
- 可以是主键索引或唯一索引。
- 适合在WHERE子句中包含多个条件的查询。
应用：适用于需要同时基于多个列进行查询的场景。

CREATE INDEX idx_name_email ON users(name, email);

5. 全文索引（Fulltext Index）

描述：专门用于全文搜索的索引，支持对大文本字段进行高效查询。
特点：
- 适用于TEXT和VARCHAR类型的列。
- 使用MATCH和AGAINST进行查询。
应用：适用于文章内容、评论等文本搜索。

CREATE FULLTEXT INDEX idx_content ON articles(content);

6. 空间索引（Spatial Index）

描述：用于存储地理信息数据，支持空间数据类型。
应用：适用于GIS应用中对空间数据的查询。

CREATE SPATIAL INDEX idx_location ON places(location);

六、索引在失效情况

1. 使用不等于运算符

当查询条件使用不等于（<>）运算符时，索引将不会被使用。

SELECT * FROM users WHERE age <> 30;  -- 索引失效

2. 范围查询

如果在查询中同时包含范围条件（如<, >, BETWEEN）和其他条件，通常只有范围条件之前的列索引会被使用，后续的条件可能会导致索引失效。

SELECT * FROM users WHERE age > 30 AND name = 'Alice';  -- 可能只使用age索引

3. 使用函数或运算

在索引列上应用函数或进行运算，可能导致索引失效，因为数据库无法使用索引直接访问数据。

SELECT * FROM users WHERE YEAR(birthdate) = 1990;  -- 索引失效

4. 类型不匹配

如果查询条件的类型与索引列的类型不匹配，索引可能不会被使用。例如，使用字符串比较数字。

SELECT * FROM users WHERE id = '100';  -- 如果id是整数，索引可能失效

5. NULL值比较

对于包含NULL值的列，如果在查询中使用NULL比较，索引可能会失效。

SELECT * FROM users WHERE email IS NULL;  -- 索引失效

6. 使用LIKE通配符

如果LIKE查询以通配符（%）开头，索引将失效。

SELECT * FROM users WHERE name LIKE '%Alice';  -- 索引失效

7. 使用OR条件

当使用OR连接多个条件时，MySQL可能不会使用索引，尤其是在其中某个条件不利用索引时。

SELECT * FROM users WHERE age < 30 OR name = 'Alice';  -- 可能索引失效