MySQL 索引

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

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

   创建索引时，你需要确保该索引是应用在SQL查询语句的条件(一般作为WHERE子句的条件)。

   实际上，索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录。

   索然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT，UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

   建立索引会占用磁盘空间的索引文件。

创建索引

 

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

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

 

修改表结构(添加索引)

 

ALTER table tableName ADD INDEX indexName(columnName)(columnName)

创建表的时候直接指定

 

 

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

删除索引的语法

 

 

 

DROP INDEX [indexName] ON mytable; [indexName] ON mytable; 

 

 

 

唯一索引

索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

 

CREATE UNIQUE INDEX indexName ON mytable(username(length)) //创建索引

ALTER table mytable ADD UNIQUE [indexName] (username(length))//修改表结构

CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
UNIQUE [indexName] (username(length))  
);  //创建表的时候直接指定

使用ALTER 命令添加和删除索引
有四种方式来添加数据表的索引：

 

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

·ALTER TABLE tbl_name ADD UNIQUE index_name(column_list):这条语句创建索引的值必须是唯一的。

·ALTER TABLE tbl_name ADD INDEX index_name(column_list）：添加普通索引，索引值可以出现多次。

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

实例

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);> ALTER TABLE testalter_tbl ADD INDEX (c);

mysql> ALTER TABLE testalter_tbl DROP INDEX c;> ALTER TABLE testalter_tbl DROP INDEX c;

 

使用ALTER命令添加和删除主键

    主键只能作用于一个列上，添加主键索引时，需要确保该主键默认不为空(NOT NULL)。实例如下：

 

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

    也可以使用ALTER命令删除主键：

 

 

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

    删除主键时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。

 

显示索引信息

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

 

mysql> SHOW INDEX FROM table_name; \G
........> SHOW INDEX FROM table_name; \G
........

参考：

https://www.yuque.com/docs/share/d8dcc3e9-b2a8-426f-99fb-7a9f4d235ed3