数据库基础(5)--MySQL索引

前言:

mysql索引的建立对于MySQL高效运行是非常重要的,合适的索引可以大大提高MySQL的检索速度.

索引分为单例索引和组合索引.

单列索引:一个索引只包含单个列,一个表可以有多个单列索引.

组合索引:一个索引包含多个列.

实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录. 创建索引时,应该确保索引是应用在sql查询语句的条件.

缺点:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对标进行INSERT,UPDATE和DELETE.因为更新新表时,MySQL不仅要保存数据,还要保存索引文件.同时,建立索引为占用磁盘空间的索引文件.

1.普通索引

1.1 创建索引

语法:CREATE INDEX indexname ON tablename(字段名称(length));

1.2通过修改表的结构添加索引

语法:ALTER table tableName ADD INDEX indexname(columnName);

1.3创建表的时候直接指定

例如:CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
INDEX

indexName (username(length))  
 
); 

1.4删除索引语法

DROP INDEX indexName ON tableName;

2.唯一索引:索引列的值必须唯一,但允许有空值

2.1 创建索引

语法:CREATE UNIQUE INDEX indexname ON tablename(字段名称(length));

2.2通过修改表的结构添加索引

语法:ALTER table tableName ADD UNIQUE indexname(columnName);

2.3创建表的时候确定

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

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

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

• ALTER TABLE tableName ADD PRIMARY KEY(column_list);          添加一个主键,即索引值唯一且不为null
• ALTER TABLE tableName ADD UNIQUE indexName(column_list)  添加一个唯一索引,索引的值唯一但可以为null
• ALTER TABLE tableName ADD INDEX indexNmae(column-list)      添加一个普通索引
• ALTER TABLE tableName ADD FULLTEXT indexName(column_list)   指定所有为FULLTEXT,用于全文索引.

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

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

mysql> ALTER TABLE testalter_tbl MODIFY itcast INT NOT NULL;

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

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

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

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

5.显示索引信息

语法: show index from tableName; \G

添加 \G 来格式化输出信息。