浅谈MySQL之索引

1.什么是索引

        索引是一种数据结构，用于提高数据库的查询性能。它类似于书籍的目录，通过预先排序和存储一定列（或多列）的值，使数据库引擎能够更快速地定位和访问特定行的数据。索引的作用是加速数据检索的速度，降低查询的成本。

        在关系型数据库中，索引的实现通常是通过B树（或B+树）等数据结构来完成。这样的结构使得数据库引擎能够在进行查询时快速地定位到满足条件的数据，而不必遍历整个表。

        索引可以基于单个列或多个列，并且可以包括不同的类型，如唯一索引、主键索引、全文索引等。不同类型的索引适用于不同的查询需求。在MySQL中，索引是一种优化数据库查询性能的重要机制。索引可以加快数据检索的速度，降低查询的成本。

2.索引类型

MySQL支持多种类型的索引，其中最常见的包括:

• 主键索(Primary Key) 

        主键索引是一种特殊的唯一索引，用于唯一标识表中的每一行。每个表只能有一个主键索引。主键索引可以加速数据检索，并确保表中的每行都有唯一标识。

• 唯一索引(Unique Index):

        每个表只能有一个主键索引。主键索引可以加速数据检索，并确保表中的每行都有唯一标识。

• 普通索引(Normal Index)

        普通索引是最基本的索引类型，没有唯一性或主键限制，可以在一个表的多个列上创建普通索引。

• 全文索引(Full-Text Index) 

        全文索引用于在文本数据上执行全文搜索，而不是简单的匹配。主要用于对文本字段进行高效的搜索操作。

3.创建索引

3.1建表时创建索引

CREATE TABLEmember (id INT UNSIGNED PRIMARY KEY,name VARCHAR(20),email VARCHAR(36) UNIQUE KEY,)
或者
CREATE TABLEmember (id,name,email,PRIMARY KEY (id),UNIQUE KEY (email),)

3.2使用Create Index创建索引

• 添加普通索引

语法：create index 索引名 on 表名(字段)

CREATE INDEX  索引名 ON 表名(col1, col2, ..., )

• 添加唯一索引

CREATE UNIQUE INDEX  索引名 ON 表名(col1, col2, ..., )

例如：创建一个名为idx_name的索引：

CREATE INDEX idx_name ON your_table(column_name);

•  复合索引

 可以在多个列上创建索引，形成复合索引。复合索引可以提高某些查询的性能，但也需要注意避免创建过于庞大的复合索引。

CREATE INDEX idx_multiple_columns ON your_table(column1, column2);

3.3使用Alter table创建索引

添加主键索引

数据列不允许重复，不能为null,一张表只能有一个主键；Mysql主动将该字段进行排序

ALTER TABLE 表名 ADD Primary key (col);

添加唯一索引

索引列是唯一的，可以null；Mysql主动将该字段进行排序

ALTER TABLE 表名 ADD unique <索引名> (col1, col2, ...col3);

添加普通索引

添加普通索引， 索引值不唯一，可为null

ALTER TABLE 表名 ADD index <索引名> (col1, col2, ...,);

添加全文索引

只能在文本类型CHAR，VARCHAR, TEXT类型字段上创建全文索引；

ALTER TABLE 表名 ADD Fulltext <索引名> (col)

添加多列索引

多列是唯一的

ALTER TABLE 表名 ADD UNIQUE (col1, col2, ..., );

3.4alter table与create index创建索引的区别

（1）使用create index时需要提供索引的名称，如果是alter方式会自动生成名字。

（2）create index一次只能创建一个索引，alter方式可以创建多个。

4.查看索引信息

可以使用SHOW INDEX语句查看表的索引信息。

SHOW INDEX FROM your_table;

5.删除索引

5.1删除普通索引

通常 使用DROP INDEX语句删除索引。例如，删除名为idx_name的索引：

DROP INDEX index_name ON table_name;

index_name 是要删除的索引的名称。

table_name 是包含要删除索引的表的名称。

5.2删除主键或唯一索引

如果要删除主键或唯一索引，可以使用以下语法：

ALTER TABLE table_name DROP PRIMARY KEY; -- 删除主键 
或 ALTER TABLE table_name DROP INDEX index_name; -- 删除唯一索引

5.3注意事项

(1)删除索引可能会影响数据库性能，因此在删除索引之前，确保了解索引的使用情况和对查询性能的影响。

(2)在执行删除索引的操作之前，最好先备份相关的数据或表，以防出现意外情况。

(3)删除主键索引时，要确保删除操作不会导致数据完整性问题，例如，删除主键索引之前要确保有其他合适的唯一标识。

6.索引的优缺点

6.1优点

(1)提高查询速度： 通过使用索引，数据库引擎可以更快速地定位和检索符合查询条件的数据，减少了数据访问的成本。

(2)加速排序和聚合操作： 索引对排序和聚合操作也有积极的影响，使这些操作更加高效。

6.2缺点

(1)占用存储空间： 索引需要额外的存储空间，因为它是一种额外的数据结构。

(2)影响写操作性能： 插入、更新和删除操作可能变得更慢，因为每次这些操作时，数据库引擎需要更新索引。

(3)维护成本： 随着数据的变化，索引需要进行维护。因此，过多或不必要的索引可能导致维护成本增加。

7.写在最后

        以上是MySQL中索引的基本介绍。在设计数据库时，正确使用索引是提高查询性能的关键因素之一。在设计数据库时，需要权衡索引的使用，根据实际的查询需求和数据操作特性来合理选择和创建索引，以达到优化数据库性能的目的。