Mysql索引

索引

索引（Index）是 MySQL 中用于加速数据检索的一种数据结构。它类似于书籍的目录，可以帮助数据库快速定位到所需的数据，而不需要扫描整个表。合理使用索引可以显著提高查询性能，但不当使用索引可能会导致性能下降。

索引的作用

加速数据检索：索引可以帮助数据库快速定位到符合条件的行，避免全表扫描
优化排序和分组：索引可以加速 ORDER BY 和 GROUP BY 操作
保证数据唯一性：唯一索引可以确保某一列的值是唯一的

索引的分类

普通索引（INDEX）

最基本的索引类型，没有任何限制
创建普通索引

CREATE INDEX index_name ON table_name (column_name);

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    INDEX idx_email (email)  -- 在 email 列上创建普通索引
);

CREATE INDEX idx_email ON users (email);

ALTER TABLE users ADD INDEX idx_email (email);

删除普通索引

DROP INDEX idx_email ON users;

ALTER TABLE users DROP INDEX idx_email;

唯一索引（UNIQUE INDEX）

确保索引列的值唯一，允许有空值
创建语法

CREATE UNIQUE INDEX index_name ON table_name (column_name);

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,  -- 在 username 列上创建唯一索引
    email VARCHAR(100)
);

CREATE UNIQUE INDEX idx_username ON users (username);
ALTER TABLE users ADD UNIQUE INDEX idx_username (username);

删除唯一索引

DROP INDEX idx_username ON users;
ALTER TABLE users DROP INDEX idx_username;

主键索引（PRIMARY KEY）

特殊的唯一索引，不允许有空值
每个表只能有一个主键索引。
创建语法：

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

CREATE TABLE users (
    id INT PRIMARY KEY,  -- 在 id 列上创建主键索引
    username VARCHAR(50),
    email VARCHAR(100)
);
ALTER TABLE users ADD PRIMARY KEY (id);

删除主键索引

ALTER TABLE users DROP PRIMARY KEY;

全文索引（FULLTEXT INDEX）

用于全文搜索，适用于 TEXT 或 VARCHAR 类型的列。
一般不在数据库中使用全文索引，使用ES数据等
创建语法：

CREATE FULLTEXT INDEX index_name ON table_name (column_name);

CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT (title, content)  -- 在 title 和 content 列上创建全文索引
);
CREATE FULLTEXT INDEX idx_content ON articles (content);
ALTER TABLE articles ADD FULLTEXT (title, content);

删除全文索引

DROP INDEX idx_content ON articles;
ALTER TABLE articles DROP INDEX idx_content;

组合索引（复合索引）

在多个列上创建的索引。
创建语法：

CREATE INDEX index_name ON table_name (column1, column2, ...);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10, 2),
    INDEX idx_customer_date (customer_id, order_date)  -- 在 customer_id 和 order_date 列上创建组合索引
);

CREATE INDEX idx_customer_date ON orders (customer_id, order_date);
ALTER TABLE orders ADD INDEX idx_customer_date (customer_id, order_date);

删除组合索引

DROP INDEX idx_customer_date ON orders;
ALTER TABLE orders DROP INDEX idx_customer_date;

查看索引

使用 SHOW INDEX 语句查看表的索引信息
```
SHOW INDEX FROM users;
```

创建索引

创建表时定义索引

创建索引三要素

表的名称
索引名称
索引添加的列名称

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100),
    INDEX idx_email (email) -- 在 email 列上创建普通索引
);

使用 CREATE INDEX 语句

CREATE INDEX idx_email ON users (email);

使用 ALTER TABLE 语句

ALTER TABLE users ADD INDEX idx_email (email);

删除索引

删除索引两个要素
- 表的名称
- 索引名称

使用 DROP INDEX 语句
```
DROP INDEX idx_email ON users;
```

使用 ALTER TABLE 语句

ALTER TABLE users DROP INDEX idx_email;

索引的底层原理

索引的底层实现原理是数据库性能优化的核心之一。MySQL 中最常用的索引类型是 B+Tree 索引，它是基于 B+Tree 数据结构实现的

B+Tree 数据结构

B+Tree结构
- 节点：B+Tree 由多个节点组成，每个节点包含多个键值对。
- 内部节点：存储键值和指向子节点的指针。
- 叶子节点：存储键值和指向实际数据行的指针（或直接存储数据）。
- 有序性：所有叶子节点通过指针连接成一个有序链表，便于范围查询。
B+Tree特点
- 平衡树：B+Tree 是一棵平衡树，所有叶子节点位于同一层。
- 多路搜索：每个节点可以包含多个键值，减少树的高度，提高查询效率。
- 范围查询优化：叶子节点通过指针连接，便于范围查询（如 BETWEEN、>、< 等）

索引的存储

索引文件：索引通常存储在一个单独的文件中，与表数据文件分离
索引中的B+Tree树中内部节点，用于指引搜索路径，通常是索引叶子结点值的范围，指针：指向子节点，用于导航到下一层
索引中的B+Tree树中叶子节点：索引列的实际值，数据指针：指向数据行的位置（如主键或行数据）
页存储：B+树节点通常存储在固定大小的页中（如16KB）。
顺序存储：键值在节点内按顺序存储，便于二分查找
索引类型
- 主键索引：叶子节点包含完整数据行。
- 二级索引：叶子节点包含主键值，需回表查询数据。

索引的注意事项

频繁查询的列：为经常用于 WHERE、JOIN、ORDER BY 和 GROUP BY 的列创建索引
高选择性的列：选择性高的列（即唯一值较多的列）更适合创建索引。例如，用户 ID 比性别列更适合创建索引
遵循最左前缀原则，对于组合索引，查询条件必须包含索引的最左列，否则索引可能不会被使用
避免在索引列上使用函数或表达式，在索引列上使用函数或表达式会导致索引失效
```
SELECT * FROM users WHERE YEAR(create_time) = 2023;  -- 索引失效
SELECT * FROM users WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';  -- 使用索引
```
- 索引是基于列的值构建的数据结构，MySQL 通过索引快速定位到符合条件的行。如果在查询中对索引列使用了函数或表达式，MySQL 无法直接使用索引的值，而是需要对每一行的列值进行计算，然后再进行比较。这会导致全表扫描，而不是使用索引
- 索引失效的状况
  - 在索引列上使用函数、在索引列上使用表达式都会导致索引失效
```
SELECT * FROM users WHERE YEAR(age) = 2023;


SELECT * FROM users WHERE age + 1 = 26;
MySQL 无法直接使用 idx_age 索引，因为需要对每一行的 age 值进行计算（age + 1），然后再进行比较
```
  - 如果需要频繁对某列进行函数或表达式计算，可以考虑使用计算列（Generated Column），并为计算列创建索引
避免在长文本列上创建普通索引，对于长文本列（如 TEXT 或 VARCHAR(255)），创建普通索引可能会导致索引过大，影响性能。可以考虑使用前缀索引或全文索引
- 对于字符串列，可以指定索引的前缀长度以减少索引大小
```
CREATE INDEX idx_email ON users (email(10));  -- 只索引 email 列的前 10 个字符
```
定期维护索引
- 删除未使用的索引：定期检查并删除未使用的索引，以减少存储空间和写操作的开销。
- 重建索引：如果索引碎片化严重，可以使用 OPTIMIZE TABLE 或 ALTER TABLE 重建索引
- ```
OPTIMIZE TABLE users;
```
避免在更新频繁的列上创建索引，索引会增加写操作（INSERT、UPDATE、DELETE）的开销。对于更新频繁的列，创建索引可能会降低性能
使用覆盖索引，覆盖索引是指查询只需要索引列中的数据，而不需要回表查询数据行。覆盖索引可以显著提高查询性能
- 覆盖索引是指一个索引包含了查询所需的所有列，因此查询可以直接从索引中获取数据，而不需要回表（即不需要访问数据行）
- 回表：当查询需要的数据不在索引中时，MySQL 需要通过索引找到对应的主键，然后再根据主键去数据行中查找所需的数据。这个过程称为回表
- 要实现覆盖索引，需要确保查询的所有列都包含在索引中
避免在小表上创建索引，对于数据量较小的表，全表扫描可能比使用索引更快。在这种情况下，创建索引反而会增加开销
使用 EXPLAIN 命令分析查询的执行计划，确保查询正确使用了索引
```
EXPLAIN SELECT * FROM users WHERE name = 'Alice';
```
使用索引提示优化查询
- 在某些情况下，MySQL 可能没有选择最优的索引。可以使用索引提示（Index Hint）强制使用特定的索引
```
SELECT * FROM users USE INDEX (idx_name) WHERE name = 'Alice';
```

索引与原始数据的有序性

索引是一种数据结构（如 B+Tree），它按照索引列的值进行排序，并存储了指向实际数据行的指针
索引的有序性：索引的叶子节点按照索引列的值有序排列，但这并不意味着表中的数据行在物理存储上也是有序的
表中的数据行是按照插入顺序存储在磁盘上的
- 如果 ORDER BY 的列上没有索引，MySQL 需要对结果集进行排序
  - MySQL 从磁盘读取数据行。
  - 将数据加载到内存中。
  - 在内存中对数据进行排序。
  - 返回排序后的结果
- 如果数据量太大，无法完全加载到内存中，MySQL 会使用磁盘临时文件进行外部排序
- ORDER BY 只是对查询结果的排序：它不会修改数据在磁盘上的物理存储顺序
- 数据存储的持久性：数据行的物理存储顺序是由表的存储引擎决定的，除非显式地对表进行重建或优化（如 OPTIMIZE TABLE），否则数据行的物理存储顺序不会改变
即使创建了索引，索引只是对索引列的值进行了排序，而不会改变表中数据行的物理存储顺序
当执行 SELECT * FROM table_name 时，MySQL 会按照数据行的物理存储顺序返回数据，而不是按照索引的顺序。
如果没有显式指定 ORDER BY，MySQL 不会对结果进行排序，因此返回的数据看起来是“未排序”的

删除列后的删除索引行为

单列索引
- 单列索引会被自动删除
- 如果该列单独创建了索引（例如唯一索引、普通索引等），删除列时 MySQL 会自动删除与其关联的单列索引
复合索引
- 如果删除的列是复合索引的一部分，MySQL 会调整索引
  - 如果复合索引中还有其他列，则索引会被更新，移除该列。
  - 如果复合索引中只剩下一个列，则该索引可能会被优化为单列索引或删除
主键或外键关联的索引
- 主键索引：如果删除的列是主键的一部分，需先删除主键约束（需谨慎操作）
- 外键索引：若列被外键引用，需先解除外键约束才能删除列，否则操作会失败

posted @ 2025-03-17 21:35 QAQ001 阅读(13) 评论(0) 收藏举报

刷新页面返回顶部

Learn-Review-blog