索引

索引是什么？

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。
拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。
索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。
创建索引时，你需要确保该索引是应用在SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件（索引会自动更新）。

创建索引

1.普通索引:

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

1.1创建索引

CREATE INDEX indexName ON  mytable(username(length)); 

PS:indexName指索引的名字，由自己定义， mytable指表名，即对哪个表创建索引，username即针对表中哪一列创建索引，由自己定义，length：这个length是指使用列的前多少位，例如username（4），指使用username的前四位，这个username的前四位是指实际存储的数据，不是指user，例如administrator，这时索引保存的就是admi，如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。使用列的一部分创建索引可以使索引文件大大减小，从而节省了大量的磁盘空间，有可能提高INSERT操作的速度。

1.2修改表结构(添加索引)

ALTER table tableName ADD INDEX indexName(columnName)

PS:同样的columnName是指列名

1.3创建表的时候直接指定

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
INDEX [indexName] (username(length))  
);  

INDEX [indexName] (username(length))

1.4删除索引的语法

DROP INDEX [indexName] ON mytable;
PS：[]只是为了说明这个要自己定义，实际写索引的时候不需要填加[]。

唯一索引

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

2.1创建索引

直接创建：

CREATE UNIQUE INDEX indexName ON mytable(username(length)) ；

可以看到，这时create和index中间加了个unique。

2.2修改表结构

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

2.3创建表的时候直接指定

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

3.使用ALTER 命令添加和删除索引

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

1.ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

2.ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一（除了NULL外，NULL可能会出现多次）。

3.ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。

4.ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

删除：

drop index index_name on table_name ;
alter table table_name drop index index_name ;
alter table table_name drop primary key ;

其中，在前面的两条语句中，都删除了table_name中的索引index_name。而在最后一条语句中，只在删除PRIMARY KEY索引中使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。

4.显示索引信息

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

SHOW INDEX FROM table_name; \G

优点

1、 索引大大减少了服务器需要扫描的数据量。
2、 索引可以帮助服务器避免排序和临时表。
3、 索引可以将随机I/O变为顺序I/O。

缺点

1.索引会占用空间 - 你的表越大，索引占用的空间越大。
2.性能损失（主要值更新操作），当你在表中添加、删除或者更新行数据的时候， 在索引中也会有相同的操作。记住：建立在某列（或多列）索引需要保存该列最新的数据。
PS：
1、索引需要占用磁盘空间，因此在创建索引时要考虑到磁盘空间是否足够。
2、创建索引时需要对表加锁，因此实际操作中需要在业务空闲期间进行。