MySQL数据库基础之索引相关知识点整理

数据库对象索引的出现，除了可以提高数据库管理系统的查找速度，而且还可以保证字段的唯一性，从而实现数据库表的完整性。

MySQL支持6种索引：普通索引、唯一索引、全文索引、单列索引、多列索引和空间索引。

以下情况适合创建索引：

经常被查询的字段，即在WHERE子句种出现的字段
在分组的字段，即在GROUP BY子句中出现的字段
存在依赖关系的子表和父表之间的联合查询，即主键或外键字段
设置唯一完整性约束的字段

以下情况，不适合创建索引

在查询中很少被使用的字段
拥有许多重复值的字段

索引的操作包括创建索引、查看索引和删除索引。

创建和查看普通索引

所谓普通索引，就是在创建索引时，不附加任何限制条件（唯一、非空等限制）。该类型的索引可以创建在任何数据类型的字段上。

语法形式如下：

创建表时创建普通索引

CREATE TABLE table_name (
    属性名 数据类型,
    属性名 数据类型,
    ...
    属性名 数据类型
    INDEX|KEY 【索引名】(属性名【索引所关联的字段的名称】 (长度【可选】) ASC|DESC【可选,排序方式，升|降】 )
)

在已经存在的表上创建普通索引

CREATE INDEX 索引名 ON 表名 (属性名【索引所关联的字段的名称】 (长度【可选】) ASC|DESC【可选,排序方式，升|降】)

通过SQL语句ALTER TABLE创建普通索引

ALTER TABLE table_name ADD INDEX|KEY 索引名 (属性名【索引所关联的字段的名称】 (长度【可选】) ASC|DESC【可选,排序方式，升|降】)

创建和查看唯一索引

所谓唯一索引，就是在创建索引时，限制索引的值必须时唯一的。通过该类型的索引可以更快速地查询某条记录。

根据创建索引方式，可以分为自动索引和手动索引。

当表中的某个字段被设置成主键或唯一完整性约束时，系统就会自动创建关联该字段的唯一索引。

语法形式如下：

创建表时创建唯一索引

CREATE TABLE table_name (
    属性名 数据类型,
    属性名 数据类型,
    ...
    属性名 数据类型,
    UNIQUE INDEX|KEY 索引名 (属性名【索引所关联的字段的名称】 (长度【可选】) ASC|DESC【可选,排序方式，升|降】)
)

在已经存在的表上创建唯一索引

CREATE UNIQUE INDEX 索引名 ON 表名 (属性名【索引所关联的字段的名称】 (长度【可选】) ASC|DESC【可选,排序方式，升|降】)

通过SQL语句ALTER TABLE创建唯一索引

ALTER TABLE table_name ADD UNIQUE INDEX|KEY 索引名 (属性名【索引所关联的字段的名称】 (长度【可选】) ASC|DESC【可选,排序方式，升|降】)

创建和查看全文索引

全文索引主要关联在数据类型为CHAR、VARCHAR和TEXT的字段上，以便能够更加快速地查询数据量较大的字符串类型的字段。

在默认情况下，全文索引得搜索执行方式为不区分大小写，如果全文索引所关联得字段为二进制数据类型，则以区分大小写得搜索方式执行。

语法形式如下

创建表时创建全文索引

CREATE TABLE table_name (
    属性名 数据类型,
    属性名 数据类型,
    ...
    属性名 数据类型,
    FULLTEXT INDEX|KEY 索引名 (属性名【索引所关联的字段的名称】 (长度【可选】) ASC|DESC【可选,排序方式，升|降】)
)

在已经存在得表上创建全文索引

CREATE FULLTEXT INDEX 索引名 ON 表名 (属性名【索引所关联的字段的名称】 (长度【可选】) ASC|DESC【可选,排序方式，升|降】)

通过SQL语句ALTER TABLE创建全文索引

ALTER TABLE table_name ADD FULLTEXT INDEX|KEY 索引名 (属性名【索引所关联的字段的名称】 (长度【可选】) ASC|DESC【可选,排序方式，升|降】)

创建和查看多列索引

所谓多列索引，是指在创建索引时，所关联得字段不是一个字段，而是多个字段。虽然可以通过所关联得字段进行查询，但是只有查询条件中使用了所关联字段中得第一个字段，多列索引才会被使用。

语法形式如下

创建表时创建多列索引

CREATE TABLE table_name (
    属性名 数据类型,
    属性名 数据类型,
    ...
    属性名 数据类型，
    INDEX|KEY 索引名  (
        属性名【索引所关联的字段的名称】 (长度【可选】) ASC|DESC【可选,排序方式，升|降】,
        属性名【索引所关联的字段的名称】 (长度【可选】) ASC|DESC【可选,排序方式，升|降】,
        ...
        属性名【索引所关联的字段的名称】 (长度【可选】) ASC|DESC【可选,排序方式，升|降】,
        )
)

在已经存在得表上创建多列索引

CREATE INDEX 索引名
    ON 表名 (
            属性名【索引所关联的字段的名称】 (长度【可选】) ASC|DESC【可选,排序方式，升|降】,
            属性名【索引所关联的字段的名称】 (长度【可选】) ASC|DESC【可选,排序方式，升|降】,
            ...
            属性名【索引所关联的字段的名称】 (长度【可选】) ASC|DESC【可选,排序方式，升|降】,
        )

通过SQL语句ALTER TABLE创建多列索引

ALTER TABLE table_name 
    ADD INDEX|KEY 索引名 (
            属性名【索引所关联的字段的名称】 (长度【可选】) ASC|DESC【可选,排序方式，升|降】,
            属性名【索引所关联的字段的名称】 (长度【可选】) ASC|DESC【可选,排序方式，升|降】,
            ...
            属性名【索引所关联的字段的名称】 (长度【可选】) ASC|DESC【可选,排序方式，升|降】,
        )