MySQL的索引概述

目录

一、索引的描述

二、 如何在一个数据表中创建和删除索引呢？

三、索引的"两面性"

四、索引的适用场景

---

一、索引的描述

         索引是数据库中一种用于提高数据检索速度和加快查询操作的数据结构。它类似于书籍的目录，可以快速定位到包含特定关键字或值的记录。索引的主要作用是加速数据库的数据检索过程，特别是在大型数据集和复杂查询的情况下。

二、 如何在一个数据表中创建和删除索引呢？

        可以通过两个命令来实现：ALTER和CREAT，ALTER语句适用于已存在的表上的索引添加、修改和删除操作，而CREAT语句适用于创建新的表或在已存在的表上创建索引。

-- index_name 是所创建的索引的名称
-- table_name 是要在其上创建索引的表名称
-- column_name 是要在该表上创建索引的列名
 
ALTER TABLE table_name ADD INDEX index_name (column_name);
-- INDEX 关键字用于指定要创建的索引的类型。
CREATE INDEX index_name ON table_name (column_name);

常用的索引类型有：

-- 1.创建 B-树索引（B-Tree Index）：B-树是一种广泛使用的索引结构，
-- 适用于范围查询和精确匹配，常用于大部分的数据库系统。
CREATE INDEX index_name ON table_name (column_name);
 
-- 2.创建唯一索引（Unique Index）：唯一索引要求索引列的值必须是唯一的，
-- 用于保证表中的某一列的数值在表中是唯一的。
CREATE UNIQUE INDEX index_name ON table_name (column_name);
 
-- 3.创建主键索引（Primary Key Index）：主键索引是一种特殊的唯一索引，
-- 它用于标识一张表中的唯一记录，并且不能为空。
ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column_name);
 
-- 4.创建聚集索引（需要根据具体的数据库系统而定）：聚集索引决定了数据在磁盘上的物理存储
-- 顺序，一个表只能有一个聚集索引。在聚集索引的情况下，数据按照索引的顺序存储，所以聚集
-- 索引所在的列值的范围和顺序决定了表中数据存储的物理顺序。
CREATE CLUSTERED INDEX index_name ON table_name (column_name);
 
-- 5.创建非聚集索引（需要根据具体的数据库系统而定）：非聚集索引是在数据行旁边单独存储的
-- 一种数据结构，非聚集索引本身并不决定表中数据行的物理存储顺序。
CREATE NONCLUSTERED INDEX index_name ON table_name (column_name);
 
-- 6.创建全文索引（需要根据具体的数据库系统而定）：全文索引用于对全文内容进行搜索，
-- 如文章、文档或大型文本字段等。
CREATE FULLTEXT INDEX index_name ON table_name (column_name);
 
-- 7.创建空间索引（需要根据具体的数据库系统而定）：空间索引用于优化对包含空间数据
-- （如地理数据）的列进行空间查询的性能。
CREATE SPATIAL INDEX index_name ON table_name (column_name);
 
-- 8.创建多列索引（Composite Index）：多列索引是指在多个列上创建的索引，
-- 用于优化涉及多个列的查询。
CREATE INDEX index_name ON table_name (column1, column2, column3);

三、索引的"两面性"

使用索引有什么好处呢？又会产生什么问题呢？

优点：

1. 提高查询性能：索引可以加速数据库查询的速度。通过在索引列上建立索引，数据库可以更快地定位到存储在表中的特定数据。索引使得数据库可以跳过不需要检查的数据，只关注满足查询条件的数据，从而提高查询效率。

2. 减少数据读取操作：索引可以减少磁盘 I/O 操作的次数。通过使用索引，数据库可以更快地将数据加载到内存中，减少读取磁盘的频率，从而提高整体查询性能。

3. 加速排序和连接操作：索引可以加速排序和连接操作的速度。对于需要按特定顺序排序的查询或需要连接多个表的查询，索引可以有效地减少数据库执行这些操作所需的时间。

4. 提高数据完整性：通过在列上创建唯一索引或主键索引，可以确保表中的数据在指定的列上是唯一的或不能为空，从而维护表的数据完整性。

5. 优化全文搜索：通过创建全文索引，可以提高全文搜索操作的性能。全文索引能够快速定位包含指定关键字的文本，加速搜索过程。

缺点：

1. 增加存储空间：索引需要占用额外的存储空间来存储索引数据结构。当表的数据量很大时，索引的大小可能相当可观，增加了存储的开销。

2. 增加写操作的时间：在插入、更新和删除数据时，索引也需要进行维护以保持数据的一致性。这意味着在执行这些写操作时，数据库不仅要更新实际的数据，还要更新相应的索引结构。当表包含多个索引时，写操作的开销可能会显著增加。

3. 维护成本：创建和维护索引需要一定的成本。当表的数据发生变化时，需要相应地更新索引，以保持索引的有效性和性能。这包括插入、更新和删除数据时的索引维护操作。

4. 索引选择的复杂性：确保选择合适的索引列是一项重要的任务。不正确的索引设计可能会导致索引无效，甚至影响数据库性能。需要仔细评估查询模式和数据访问模式，以选择最适合的索引列。

5. 可能导致查询性能下降：虽然索引可以提高查询性能，但在某些情况下，索引可能会导致查询性能下降。当查询涉及到多个表连接、范围查询或使用与索引不匹配的查询条件时，数据库可能无法充分利用索引的优势，甚至需要额外的索引扫描操作，从而导致性能下降。

四、索引的适用场景

        索引在数据库中可以提高查询性能和数据完整性，但在某些情况下，使用索引可能不是最佳选择。以下是一些情况下适合使用索引的情况，以及一些情况下应该避免使用索引的情况：

适合使用索引的情况：

1. 经常被查询的列：如果某个列经常被用作查询条件，并且数据量庞大，那么在该列上创建索引可以提高查询性能。

2. 多表连接操作：当需要进行多个表之间的连接操作时，使用索引可以加速连接的速度，提高查询性能。

3. 范围查询：对于包含范围查询的操作，如 BETWEEN、大于小于号等，通过在相关列上创建索引，可以加快执行这些范围查询的速度。

4. 排序和分组：当需要对结果进行排序或分组时，使用索引可以改进排序和分组操作的性能。

5. 唯一性约束：为了保持数据的完整性，对于需要保证唯一性的列，可以创建唯一索引或主键索引。

避免使用索引的情况：

1. 数据量较小的表：对于包含较少数据的表，使用索引可能不会带来明显的性能提升。因为索引的维护成本和额外的存储空间可能会带来不必要的开销。

2. 频繁的写操作：当表需要频繁地插入、更新或删除数据时，索引的维护成本可能会导致写操作的性能下降。在这种情况下，需要权衡读写操作的需求，决定是否创建索引。

3. 少数取值的列：对于某些列的取值少且分布均匀的情况，如性别列等，使用索引可能无法带来明显的性能改进。

4. 不经常使用的列：如果某个列很少被用于查询条件，或者很少被用于排序和分组操作，创建索引可能对性能没有实质性影响，且增加了额外的存储和维护开销。