建一个网站都需要什么,网站如何防止恶意注册,35岁了还能学平面设计吗,网站建设备案需要什么想象一下#xff0c;你正在图书馆寻找一本关于 MySQL 索引的书。图书馆里有成千上万本书#xff0c;但没有目录。你只能一排一排、一本一本地找#xff0c;直到找到你想要的书。这将会花费大量的时间#xff01;数据库索引就像图书馆的目录一样#xff0c;可以帮助数据库系…想象一下你正在图书馆寻找一本关于 MySQL 索引的书。图书馆里有成千上万本书但没有目录。你只能一排一排、一本一本地找直到找到你想要的书。这将会花费大量的时间数据库索引就像图书馆的目录一样可以帮助数据库系统快速定位到所需数据从而大大提高查询速度。
1. 索引基础概念与类型
1.1 什么是索引
索引是一种特殊的数据结构它存储了表中一列或多列的值以及对应行的物理地址。当数据库执行查询时会首先在索引中查找符合条件的记录地址然后再根据地址直接访问数据行从而避免了全表扫描提高了查询效率。
示例
假设我们有一个名为 users 的表包含以下数据
idnameemail1张三zhangsanexample.com2李四lisiexample.com3王五wangwuexample.com
如果我们在 name 列上创建索引数据库就会创建一个索引结构其中包含 name 列的值和对应行的 id
nameid张三1李四2王五3
当我们执行查询 SELECT * FROM users WHERE name 李四 时数据库会首先在索引中找到 name 为 李四 的记录然后直接访问 id 为 2 的行而不需要扫描整个 users 表。
1.2 常见的索引类型
MySQL 支持多种类型的索引常见的包括 主键索引 (PRIMARY KEY): 唯一标识表中每一行的索引一个表只能有一个主键索引主键索引的值不能为空。 唯一索引 (UNIQUE): 唯一索引保证索引列的值是唯一的可以有多个唯一索引允许为空值但只允许一个空值。 普通索引 (INDEX): 最基本的索引类型没有任何限制用于加速查询速度。 全文索引 (FULLTEXT): 用于在文本字段中进行全文搜索主要用于 MyISAM 引擎。
2. 索引的利与弊
优点 大大加快数据的检索速度这是创建索引的最主要原因。 加速表之间的连接特别是在实现数据的参考完整性方面特别有用。 在使用分组和排序子句进行数据检索时可以显著减少查询中分组和排序的时间。
缺点 创建和维护索引需要耗费时间而且随着数据量的增加而增加。 索引需要占用物理空间如果要建立索引的列数据量很大那么需要的存储空间也会很大。 当对表中的数据进行修改时比如添加、删除和修改索引也需要动态地维护降低了数据的维护速度。
3. 索引操作创建与删除
创建索引
可以使用 CREATE INDEX 或 ALTER TABLE 语句来创建索引 CREATE INDEX:
CREATE INDEX index_name ON table_name (column_name);
示例
CREATE INDEX idx_name ON users (name); ALTER TABLE:
ALTER TABLE table_name ADD INDEX index_name (column_name);
示例
ALTER TABLE users ADD INDEX idx_email (email);
删除索引
可以使用 DROP INDEX 或 ALTER TABLE 语句来删除索引 DROP INDEX:
DROP INDEX index_name ON table_name;
示例
DROP INDEX idx_name ON users; ALTER TABLE:
ALTER TABLE table_name DROP INDEX index_name;
示例
ALTER TABLE users DROP INDEX idx_email;
4. 深入底层数据结构与性能对比
前面我们已经了解了索引的基本概念现在让我们更深入地探讨 MySQL 索引的底层实现原理以及使用索引和不使用索引在性能上的巨大差异。
4.1 索引的数据结构
MySQL 索引的底层数据结构主要有两种BTree多路平衡搜索树 和 哈希表。我们平常所说的索引如果没有特别指明都是指默认的 BTree 结构组织的索引。 B 树 是一种多路平衡查找树它将所有数据存储在叶子节点非叶子节点只存储索引值和指向子节点的指针。所有叶子节点通过链表连接方便范围查询并且每个节点可以存储多个索引值降低树的高度减少 I/O 次数 使其成为 MySQL 索引最常用的数据结构。 哈希表 是一种键值对存储结构它通过哈希函数将索引值映射到哈希表中的一个位置从而实现快速查找。哈希表适用于等值查询例如 WHERE name 张三但不适用于范围查询。MySQL 中Memory 存储引擎默认使用哈希索引而 InnoDB 存储引擎默认使用 B 树索引。 B Tree多路平衡搜索树结构介绍如图所示 BTree结构 每一个节点可以存储多个key有n个key就有n个指针 节点分为叶子节点、非叶子节点 叶子节点就是最后一层子节点所有的数据都存储在叶子节点上 非叶子节点不是树结构最下面的节点用于索引数据存储的的是key指针 为了提高范围查询效率叶子节点形成了一个双向链表便于数据的排序及区间范围查询 4.2 使用索引和不使用索引的性能差异
为了更好地理解使用索引带来的性能提升我们来看一个具体的例子。
假设我们有一个包含 100 万条数据的 users 表其中 name 列没有创建索引。
场景一不使用索引
SELECT * FROM users WHERE name 张三;
当执行这条 SQL 语句时MySQL 数据库需要遍历整个 users 表逐行比较 name 列的值是否等于 张三直到找到匹配的行。这种方式被称为全表扫描效率非常低下尤其是在数据量非常大的情况下。
场景二使用索引
CREATE INDEX idx_name ON users (name);SELECT * FROM users WHERE name 张三;
当我们在 name 列上创建了索引之后再次执行相同的查询语句MySQL 数据库会直接使用索引进行查找。由于 B 树的特性查找速度非常快只需要很少的 I/O 操作就可以定位到目标数据。
总结
使用索引可以避免全表扫描大大提高查询效率尤其是在数据量非常大的情况下。
5. 索引失效问题与解决
虽然索引可以提高查询效率但在某些情况下索引可能会失效导致 MySQL 数据库无法使用索引进行查询从而进行全表扫描。
常见的索引失效的情况包括 未使用索引列进行查询 比如在 WHERE 子句中使用了非索引列进行过滤。 对索引列进行了函数操作 比如在 WHERE 子句中对索引列使用了函数操作如 SUBSTR、DATE 等。 使用了 LIKE 模糊查询且通配符 % 位于开头 比如 WHERE name LIKE %三。 使用了 OR 连接条件且其中一个条件没有使用索引 比如 WHERE name 张三 OR age 18如果 age 列没有创建索引那么整个查询将无法使用索引。 数据分布不均 如果索引列的数据分布非常不均匀比如大部分数据的索引列值都相同那么索引的效率也会降低。
6. 总结
索引是 MySQL 数据库中非常重要的一个概念合理地使用索引可以大大提高数据库的查询效率。在设计和使用索引时需要根据实际情况选择合适的索引类型并尽量避免索引失效的情况。 以上就是关于数据库中索引的相关知识希望对各位看官有所帮助下期见谢谢~
