mysql创建索引的三种方式

在MySQL中，索引是优化查询性能的重要工具。它们通过允许数据库引擎快速定位表中的数据行，大幅提高了SELECT查询的执行速度。MySQL提供了多种方式来创建索引，常见的主要有三种：创建索引使用CREATE INDEX语句、在表定义中指定索引、以及使用ALTER TABLE语句添加索引。下面将详细介绍这三种创建索引的方法。

1. 使用CREATE INDEX语句创建索引

CREATE INDEX 语句是显式地为一个或多个列创建索引的标准方法。其基本语法如下：

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

使用这种方法创建索引时，可以选择不同类型的索引以适应特定的查询需求，例如：

• B-Tree索引（默认）：适用于大多数查询场景，尤其是对全匹配、前缀匹配、范围查找及排序操作优化显著。

• Hash索引：主要用于基于精确匹配的查询，但在MySQL中仅支持MEMORY存储引擎。

• Full-text全文索引：用于文本字段的全文搜索，适合查找包含某个单词或短语的文本记录。

• Spatial数据类型索引：针对空间数据类型，如几何数据类型，提升空间查询效率。

例如，为employee表的name列创建一个普通的B-Tree索引：

CREATE INDEX idx_name ON employee (name);

这种方式的好处在于，索引的创建过程与数据定义（表定义）解耦，可以对已经存在的表灵活增加索引。

2. 在表定义中指定索引

在创建表时，可以在表定义内同时指定索引。这通常用于一次性定义完整的数据结构，适合于创建新表时使用。其语法如下：

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    INDEX (column_name),
    UNIQUE (column_name),
    PRIMARY KEY (column_name)
);

在这种方式中，索引信息作为表结构的一部分一起定义。可以定义三种主要类型的索引：

• 普通索引（INDEX）：加快查询速度，对数据容许重复。

• *索引（UNIQUE）：除加快查询，还对列值是否重复进行约束。

• 主键索引（PRIMARY KEY）：每个表仅能有一个，具有*性约束且非空。

例如，在创建employee表时为email字段创建*索引：

CREATE TABLE employee (
    id INT AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(100),
    INDEX idx_name (name),
    UNIQUE KEY unique_email (email),
    PRIMARY KEY (id)
);

在建表时指定索引能够减少日后修改表结构的需要，适合于初始设计时已经充分考虑的场景。

3. 使用ALTER TABLE语句添加索引

ALTER TABLE是用于修改表结构的灵活工具，也支持添加索引。这种方法适合于已经存在的表需要新增或者修改索引的情况。其基本语法如下：

ALTER TABLE table_name
ADD INDEX index_name (column1, column2, ...);

例如，为employee表的salary列添加一个索引：

ALTER TABLE employee
ADD INDEX idx_salary (salary);

这种方式的一个优势在于不需要重新创建表结构。使用ALTER TABLE添加索引的过程可能会锁定表（对于大表可能耗时），但是MySQL在最近的版本中支持在线DDL操作，可以在不锁定表进行读取的情况下添加索引。

索引选择的考虑因素

在选择何种索引及如何创建索引时，需要综合考虑几个因素：

1. 查询模式：不同的查询模式可能需要不同类型的索引。例如，全文搜索、范围查找、精确匹配等。
2. 数据分布：数据分布不均时，某些索引可能比其他更有效。高选择性列适合索引。
3. 存储消耗：索引会额外占用磁盘存储空间，同时维护索引也会增加INSERT, UPDATE, DELETE操作的成本。
4. 维护成本：频繁更新的数据表可能导致索引的维护成本过高，选择性降低或仅在必要时创建和维护索引。
5. 表大小：对于非常大的表，创建索引可能需要注意表的大小以及可能会带来的性能影响。

总之，索引的创建与管理是一项复杂且需要调优的任务，经常需要根据实际查询性能进行调整。通过合理地使用不同的创建方法和类型，可以实现对MySQL数据库的高效管理和优化。