​ 在mysql中，所有列类型数据都能被索引，使用索引是改进mysql查询操作性能的最好办法，是数据库优化的重要手段之一。

索引的特点

  • 一个表最多可有16个索引，最大索引长度是256字节，尽管这可以在编译MySQL时被改变。

  • 对于 CHAR 和 VARCHAR 列,你可以索引列的前缀。这更快并且比索引整个列需要较少的磁盘空间。对于 BLOB 和 TEXT 列，你必须索引列的前缀，你不能索引列的全部。

  • MySQL 能在多个列上创建索引，一个索引可以由最多15个列组成。（在CHAR和VARCHAR列上，你也可以使用列的前缀作为一个索引的部分）

    ISAM 表和 MyISAM 表之间的差别：

    索引特点|ISAM 表|MyISAM 表

索引特点 ISAM 表 MyISAM
NULL 值 不允许 允许
BLOB 和 TEXT 值 不能索引 只能索引列的前缀
每个表中的索引数 16 32
每个索引中的列数 16 16
最大索引行尺寸 256字节 500字节

索引有如下几种情况：

  • INDEX 索引：通常意义的索引，某些情况下 KEY 是它的一个同义词，索引的列可以包含重复的值。
  • UNIQUE 索引：唯一索引，保证了列不包含重复的值，对于多列唯一索引，它保证值得组合不重复。
  • PRIMARY KEY 索引：和UNIQUE索引类似，可以理解为特殊的 UNIQUE 索引。一个表中只能包含一个PRIMARY KEY，PRIMARY KEY 索引不允许列为空值。

创建和删除索引

1. 使用 Alter Table 和 Create Index 语句

1
2
3
4
5
ALTER TABLE tb_name ADD INDEX index_name(column_list);

ALTER TABLE tb_name ADD UNIQUE index_name(column_list);

ALTER TABLE tb_name ADD PRIMARY KEY index_name(column_list);

tb_name 是要添加索引的表名，index_name 是创建的索引名，column_list是列名集合,多个列名使用逗号分隔。

同样，也可以使用 ALTER TABLE 语句加 DROP 操作符删除索引：

1
2
3
ALTER TABLE tb_name DROP INDEX index_name;

ALTER TABLE tb_name DROP PRIMARY KEY;

​ 上面第一条语句可以删除各类型索引，第二条语句只在删除 PRIMARY KEY 作用。如果没有明确地创建作为 PRIMARY KEY 的索引，但该表具有一个或多个UNIQUE 索引，则 MySQL将删除 UNIQUE 索引的第一个。

​ 索引依赖于列而存在，如果从表中删除了列，则索引会受到影响。如果所删除的列作为索引的组成部分，则该列会从索引中删除；如果索引的所有列被删除，那么该索引也会被隐式删除。

2. 使用CREATE/DROP INDEX 创建索引

CREATE INDEX 是在 MySQL3.23版引入的。标准语法格式如下：

1
2
3
CREATE UNIQUE INDEX index_name ON tb_name(column_list);

CREATE INDEX index_name ON tb_name(column_list);

注意：不能用 CREATE INDEX 创建PRIMARY KEY 索引。

DROP INDEX 是在 3.22 中引入的，标准语法格式如下：

1
DROP INDEX index_name ON tb_name;

  1. 在创建表时指定索引

    要想创建新表时同时创建索引，指定索引的语句语法如下：

    1
    2
    3
    4
    5
    6
    7
    8
    CREATE table tb_name(
    ...
        INDEX index_name(column_list),
        key index_name(column_list),
        UNIQUE index_name(column_list),
        PRIMARY KEY index_name(column_list),
        ...
    );

    与 ALTER TABLE 一样，索引名对于 INDEX 和 UNIQUE 都是可选的，如果未给出，MySQL将为其选一个。

    当索引的列为单列时，也可以在列定义后指定索引，如下：

    1
    2
    3
    4
    5
    CREATE table tb_name(
       column_name type_name PRIMARY KEY,
       column_name type_name UNIQUE
        ...
    );

    上面 PRIMARY KEY 的指定也可等价于：PRIMARY KEY(column_name)。

3. 对串列的前缀进行索引

在 CREATE TABLE 语句中可以某个串列的前缀进行索引（列值左边的n个字符）。

对某个列的前缀进行索引，上面 colum_list 的指定中，column_name改为column_name(n)。如下面的语句：

1
2
3
4
5
CREATE TABLE tb_name(
    name CHAR(30),
    address CHAR(60),
    INDEX(name(10),adress(20))
)

检查表的索引

1
SHOW INDEX FROM tb_name;

THE END! 本文转自百度文库——MySQL完全手册。