MySQL索引有几种类型？分别如何创建？

Bertha 。 2024-02-17 10:09 102阅读 0赞

> MySQL 的索引有两种分类方式：逻辑分类和物理分类。

**按照逻辑分类，索引可分为：**

*  主键索引：一张表只能有一个主键索引，不允许重复、不允许为 NULL；
 *  唯一索引：数据列不允许重复，允许为 NULL 值，一张表可有多个唯一索引，但是一个唯一索引只能包含一列，比如身份证号码、卡号等都可以作为唯一索引；
 *  普通索引：一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入；
 *  全文索引：让搜索关键词更高效的一种索引。

**按照物理分类，索引可分为：**

*  聚集索引：一般是表中的主键索引，如果表中没有显示指定主键，则会选择表中的第一个不允许为 NULL 的唯一索引，如果还是没有的话，就采用 Innodb 存储引擎为每行数据内置的 6 字节 ROWID 作为聚集索引。每张表只有一个聚集索引，因为聚集索引的键值的逻辑顺序决定了表中相应行的物理顺序。聚集索引在精确查找和范围查找方面有良好的性能表现（相比于普通索引和全表扫描），聚集索引就显得弥足珍贵，聚集索引选择还是要慎重的（一般不会让没有语义的自增 id 充当聚集索引）；
 *  非聚集索引：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同（非主键的那一列），一个表中可以拥有多个非聚集索引。

**各种索引的创建脚本如下：**

-- 创建主键索引
    alter table t add primary key add (`id`);
    -- 创建唯一索引
    alter table t add unique (`username`);
    -- 创建普通索引
    alter table t add index index_name (`username`);
    -- 创建全文索引
    alter table t add fulltext (`username`);

## more ##

MySQL中常见的索引类型包括以下几种：

1.  B-Tree索引：B-Tree索引是最常见的索引类型，适用于大多数场景。它可以用于精确匹配和范围查询，并且支持快速的插入和删除操作。
    
    创建B-Tree索引的语法：
    
        CREATE INDEX index_name ON table_name (column_name);
        ```
2.  唯一索引：唯一索引要求索引列的值在整个表中是唯一的，用于确保数据的唯一性。
    
    创建唯一索引的语法：
    
        CREATE UNIQUE INDEX index_name ON table_name (column_name);
        ```
3.  主键索引：主键索引是一种特殊的唯一索引，用于唯一标识表中的每一行数据。
    
    创建主键索引的语法：
    
        ALTER TABLE table_name ADD PRIMARY KEY (column_name);
        ```
4.  全文索引：全文索引用于在文本数据中进行全文搜索，支持关键词匹配和相关性排序。
    
    创建全文索引的语法：
    
        ALTER TABLE table_name ADD FULLTEXT INDEX index_name (column_name);
        ```
5.  哈希索引：哈希索引将索引列的值通过哈希函数转换为哈希码，适用于等值查询，但不支持范围查询。
    
    创建哈希索引的语法：
    
        CREATE INDEX index_name ON table_name (column_name) USING HASH;
        ```

需要注意的是，不同类型的索引适用于不同的查询场景，选择合适的索引类型可以提高查询性能。在创建索引时，需要考虑到数据表的大小、查询频率、数据修改频率等因素，并进行适当的索引设计。

此外，还可以使用组合索引（多列索引）来同时索引多个列，以满足复合查询条件。创建组合索引时，需要按照查询的顺序将相关列包含在索引中。

创建组合索引的语法：

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

请根据实际需求选择适当的索引类型，并在创建索引时考虑查询和数据操作的性能影响。