MySQL 索引
文章目录
前言
MySQL索引是一种数据结构，旨在提高数据库查询的速度它类似于书籍的目录，通过索引可以快速定位到需要的数据，而不必逐行扫描整个表
索引虽然能够提高查询性能，但也需要注意以下几点：
索引会占用额外的磁盘空间
每次插入、更新或删除数据时，相关的索引也需要更新，可能导致写入性能下降
应根据查询的特点选择适合的列建立索引，过多的索引可能会降低性能
一、常见索引类型
- 主键索引：自动创建，唯一标识表中的每一行，不能重复且不能为空
- 唯一索引：确保索引列的值唯一，可以有空值
- 普通索引：没有唯一性要求，可以重复
- 全文索引：用于对文本字段进行复杂查询，支持自然语言的全文搜索
- 复合索引：由多个列组成的索引，可以提高多列查询的性能
二、底层实现 - 使用规则
1.索引的底层实现
MySQL 中常用的存储引擎如 InnoDB 和 MyISAM，其索引的实现方式不同：
B+树索引：
这是 InnoDB 和 MyISAM 的默认索引结构，适用于大多数场景
B+树是一种平衡树结构，能够保证在查询时的较低时间复杂度
主键索引和普通索引都基于B+树
哈希索引：这种索引适用于精确查找，比如 = 或者 IN 查询操作
哈希索引非常快，但不适用于范围查找（如 <、>）
InnoDB 并不直接支持哈希索引，而是基于自适应哈希索引优化某些场景
全文索引（Fulltext Index）：主要用于对文本字段的全文搜索，常用于 TEXT 或 VARCHAR 类型的列上
它对大段文本数据的查找非常有效，支持自然语言查询、布尔模式查询
2.索引的使用规则
MySQL 索引在以下情况下效果最佳：
大表查询：在大量数据表中进行筛选、查找或排序时，索引可以显著提高性能
经常查询的列：如果某个字段经常出现在 WHERE、ORDER BY、GROUP BY 中，可以为其创建索引
外键列：外键关联的列通常需要创建索引以提高查询速度
唯一性需求：当某列必须唯一时，可以通过唯一索引确保数据唯一性
三、使用示例
1.创建普通索引
--- 在 user 表的 name 列上创建一个普通索引
CREATE INDEX idx_name ON user (name);
SELECT * FROM user WHERE name = 'John';
2.唯一索引
--- 在email列创建唯一索引，可以确保该列中的数据是唯一的，避免重复值插入
CREATE UNIQUE INDEX idx_email ON user (email);
INSERT INTO user (name, email) VALUES ('ace', 'ace@163.com');
-- 如果再次插入相同的 email，会报错：
INSERT INTO user (name, email) VALUES ('abc', 'ace@163.com');
3.复合索引
CREATE INDEX idx_name_email ON users (name, email);
--- 复合索引是对多列组合进行索引，适用于多条件查询
SELECT * FROM users WHERE name = 'john' AND email = 'john@163.com';
--- 在这个查询中，MySQL 会使用 idx_name_email 索引来快速查找数据
SELECT * FROM users WHERE name = 'john';
--- 在这个查询中，MySQL 也会利用 idx_name_email 的前缀索引（如果前缀列出现在查询条件中，MySQL 可以部分利用复合索引）
4.全文索引
--- 适用于文本搜索场景
CREATE FULLTEXT INDEX idx_content ON articles (content);
SELECT * FROM articles WHERE MATCH(content) AGAINST('database' IN NATURAL LANGUAGE MODE);
四、优化建议
选择合适的列创建索引：经常出现在 WHERE 子句、JOIN 操作、ORDER BY 或 GROUP BY 子句中的列是索引的最佳选择
避免为小表创建索引：小表（数千行数据）的全表扫描速度可能比索引查询更快
谨慎使用复合索引：如果只查询复合索引中的部分列，确保查询使用的是复合索引的前缀部分
删除不必要的索引：冗余的或几乎从不使用的索引会占用空间并影响写入性能
使用覆盖索引：如果索引包含了查询需要的所有字段，MySQL 不需要访问表中的数据行，查询性能会进一步提高
五、复合索引及覆盖索引
1.复合索引
假设有一个 user 表，包含 first_name、last_name 和 email 列，可以创建一个复合索引
CREATE INDEX idx_name_email ON user (first_name, last_name, email);
--- 以利用 idx_name_email 索引加速查询
SELECT * FROM user WHERE first_name = 'John' AND last_name = 'Doe';
--- 查询 first_name 时可以利用索引
SELECT * FROM user WHERE first_name = 'John';
--- 查询 last_name 时则无法使用该索引
SELECT * FROM user WHERE last_name = 'Doe';
注意事项
1.索引顺序：复合索引中列的顺序很重要，MySQL只能利用索引的前缀部分，即使只查询复合索引的第一个列，MySQL 也能有效利用
比如，在上述索引中，查询 first_name 时可以利用索引，但查询 last_name 时则无法使用该索引
2.列数限制：复合索引的列数不宜过多，通常建议不超过 5 个列，因为索引过大将影响性能和存储空间
3.写入性能：复合索引会增加插入、更新和删除操作的成本，因为每次操作时都需要更新所有相关的索引
4.查询条件：使用复合索引时，最好在查询中包含索引的前缀列
例如，对于 idx_name_email 索引，查询可以是：
WHERE first_name = 'John'
WHERE first_name = 'John' AND last_name = 'Doe'
但不能只使用 last_name 进行查询
2.覆盖索引
覆盖索引是指一个索引包含了查询所需的所有列，使得查询可以直接从索引中获取数据，而不需要访问数据表，这种方式能够显著提高查询效率
还是举例在 user 表上创建一个覆盖索引，只需包含 first_name 和 email 列：
CREATE INDEX idx_name_email ON user (first_name, email);
--- 由于查询所需的所有列都包含在索引中，MySQL 可以直接从索引中获取数据，无需访问数据行，从而提升性能
SELECT first_name, email FROM user WHERE first_name = 'Alice';
注意事项
1.选择合适的列：创建覆盖索引时，应选择查询中频繁使用的列，并确保这些列能满足大部分查询的需求
2.空间开销：虽然覆盖索引提高了查询性能，但会增加存储空间的消耗，尤其在表中列数较多的情况下
3.查询性能提升：覆盖索引适合读取频繁而写入较少的场景，因为它能够显著减少 I/O 操作，提升读取效率
4.索引冗余：避免为同一列创建多个索引，冗余的索引可能导致性能下降和存储空间浪费
六、索引失效场景
索引失效指的是在查询中尽管有索引存在，但 MySQL 并未使用该索引，导致查询性能下降
常见导致索引失效的情况包括：
1. 使用不等式：
--- 当使用 <>、!=、NOT IN 等不等条件时，索引可能失效。
SELECT * FROM user WHERE name <> 'Alice';
2.函数和运算符：
--- 在查询中对索引列使用函数或运算符，会导致索引失效。
SELECT * FROM user WHERE YEAR(birthdate) = 1990; -- 函数调用
SELECT * FROM user WHERE name LIKE '%John'; -- 前缀不匹配
3.类型不匹配：
--- 查询条件中数据类型与索引列的数据类型不一致，会导致索引失效
SELECT * FROM user WHERE age = '25'; -- age 是整数类型
4.复合索引的列顺序：
--- 查询条件没有使用复合索引的前缀列
SELECT * FROM user WHERE email = 'john@example.com'; -- 只能利用 idx_name_email 索引的前缀部分
5.OR 查询：
--- 使用 OR 时，如果其中一个条件不使用索引，整个查询可能会导致索引失效
SELECT * FROM user WHERE name = 'Alice' OR age = 25; -- 如果 age 没有索引，可能导致全表扫描
NULL 值：
6.在索引列中使用 NULL 值时，可能导致索引失效
SELECT * FROM user WHERE name IS NULL; -- 若索引中包含 NULL 值，可能未能有效利用索引
原文地址：https://blog.csdn.net/weixin_42695345/article/details/142462626
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！