MySQL数据库的索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

索引分为单列索引和组合索引。单列索引，一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，一个索引包含多个列。

创建索引时，需要确保该索引是应用在SQL查询语句的条件（一般作为WHERe子句的条件）。实际上，索引也是一张表，保存了主键与索引字段，并指向实体表的记录。

虽然索引提高了查询速度，同时却降低了更新表的速度，如对表进行insert、update、delete。因为更新表时，不仅要保存数据，还要保存索引文件。

普通索引是最基本的索引，没有任何限制。
有以下几种创建方式：

CREATE INDEX index_name ON table_name (column_name)

如果是char、varchar类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定length。

ALTER table table_name ADD INDEX index_name(column_name)

CREATE TABLE newtable(
	ID INT NOT NULL,
	username VARCHAr(16) NOT NULL,
	INDEX [index_name] (username(length))
);

DROP INDEX [index_name] ON mytable;

唯一索引与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
有以下几种创建方式：

CREATE UNIQUE INDEX index_name ON mytable (username(length))

ALTER table mytable ADD UNIQUE [index_name] (username(length))

CREATE TABLE mytable(
	ID INT NOT NULL,
	username VARCHAr(16) NOT NULL,
	UNIQUE [index_name] (username(length))
);

有四种方式来添加数据表的索引：

1. 添加一个主键，索引值必须唯一，且不能是NULL

ALTER TABLE tb_name ADD PRIMARY KEY(column_list)

2. 创建的索引值必须是唯一的（除了NULL以外，NULL可能会出现多次）

ALTER TABLE tb_name ADD UNIQUE index_name (column_list)

3. 添加普通索引，索引值可以出现多次

ALTER TABLE tb_name ADD INDEX index_name (column_list)

4. 指定索引为FULLTEXT，用于全文索引

ALTER TABLE tb_name ADD FULLTEXT index_name (column_list)

主键作用于列上（可以一个列或多个列的联合主键），添加主键索引时，需要确保该主键默认不为空：

mysql> ALTER TABLE mytable MODIFY id INT NOT NULL;

mysql> ALTER TABLE mytable ADD PRIMARY KEY (id);

使用ALTER命令删除主键，删除主键时只需要指定PRIMARY KEY，但在删除索引时必须知道索引名。

使用SHOW INDEX命令列出表中相关的索引信息，通过添加G来格式化输出信息。

mysql> SHOW INDEX FROM mytableG

create table if not exists `mytable`
(
    `id`              int,
    `title`           varchar(100),
    `author`          varchar(40),
    `submission_date` date
);

show index from mytable;

create index index_id on mytable (id);

alter table mytable
    add index index_title (title);

create table if not exists `newtable`
(
    `id`              int,
    `title`           varchar(100),
    `author`          varchar(40),
    `submission_date` date,
    index index_title (title(100))
);

show index from newtable;

drop index index_title on mytable;

create unique index index_author on mytable (author(40));

alter table mytable
    add unique index_title (title(100));

create table if not exists `newtable2`
(
    `id`              int,
    `title`           varchar(100),
    `author`          varchar(40),
    `submission_date` date,
    unique index_title (title(100))
);

show index from newtable2;

alter table newtable2
    add primary key (id);

alter table newtable2
    add unique index_author (author);

alter table newtable2
    add index index_submission_date (submission_date);

create table if not exists `newtable3`
(
    `id`              int,
    `title`           varchar(100),
    `author`          varchar(40),
    `submission_date` date,
    `content`         text
);

show index from newtable3;

alter table newtable3
    add fulltext index_content (content);

alter table newtable3
    modify id int not null;

alter table newtable3
    add primary key (id);