mysql 建立索引 - mysql index -嗨客网

Mysql建立索引教程

索引在数据库里面是常用的，如果索引使用得当，它在一定程度上能够提高查询效率。索引建立有两种方式，一个是在建表的时候建立，还有一种是修改表结构对建好的表进行修改添加索引。

本章案例用下面的表

-- 嗨客网(www.haicoder.net)
CREATE TABLE `tb_test` (
 `id` int(11),
  `name` varchar(20),
  `age` int(4)
);

Mysql查看索引

语法

SHOW INDEX FROM [表名];

描述

关键字	描述
SHOW	指明要做的操作，show 展示的意思
INDEX	指明需要展示的内容，index 表示的是索引
FROM	从什么地方展示
表名	指明需要被展示的表的名称

案例

-- 嗨客网(www.haicoder.net)
SHOW INDEX FROM tb_test;

因为我们建表的时候，没有操作索引信息，所以查询的时候结果如下：

20_索引展示.png

Mysql建立索引

索引的建立可以在建表的时候确定，也可以在表建立之后，执行 alter 语句来修改表结构，增加索引信息。

建表建索引语法

CREATE TABLE `tablename`(
  `列名1` 数据类型,
  `列名` 数据类型,
  ....,
  [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY
  [索引名](字段名1)
);

描述

字段	描述
UNIQUE	表示索引为唯一性索引，该索引对应的字段不能重复
FULLTEXT	表示索引为全文索引，用于在一篇文章中，检索文本信息的, 针对较大的数据
SPATIAL	表示索引为空间索引，是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建
INDEX和KEY	用于指定字段为索引，两者选择其中之一就可以了，作用是一样的
索引名	给创建的索引取一个新名称
字段名1	指定索引对应的字段的名称，该字段必须是前面定义好的字

案例

-- 嗨客网(www.haicoder.net)
CREATE TABLE db_index (
	`id` INT(11),
	`name` VARCHAR(200),
	`stu_no` VARCHAR(200),
	INDEX `index_stu_no` (stu_no)
);

查询索引结果

21_建表建立索引展示.png

我们看到表 db_index 里面有一个索引的名字叫 index_stu_no，它对应的列名称为 stu_no。

修改表增加索引

在生产环境中，我们经常会遇到那种表已经建立好了，想给某些字段添加索引的情况。这个时候，我们可以通过修改表结构语句来给表添加索引。

语法

ALTER TABLE [tableName] 
ADD 
UNIQUE|INDEX|FULLTEXT 
[INDEXNAME] 
(column1,column2...);

描述

列名	描述
ALTER	添加索引的必要字段，表示对表进行修改
TABLE	表示要修改表
[tableName]	需要添加索引的表名
ADD	表示添加
UNIQUE\|INDEX\|FULLTEXT	唯一索引｜索引｜全局索引
[INDEXNAME]	索引的名称
(column1,column2…)	需要维护的索引对应的列

案例

 -- 嗨客网(www.haicoder.net)
 ALTER TABLE db_index ADD INDEX index_stu_no_and_name (`stu_no`,`name`);

查看表索引情况如下

22_修改表结构添加索引.png

我们可以看到，表里面有两个索引，一个是 index_stu_no 它对应的列名是 stu_no 是我们建表的时候维护的索引，还有一个索引名称是 index_stu_no_and_name 是我们这次修改的时候维护的，它对应多列，分别为 stu_no 和 name 两个字段。

Mysql删除索引

语法

-- 方式1
drop index [index_name] on [table_name] ;
-- 方式2
alter table [table_name] drop index [index_name] ;
-- 删除主键
alter table [table_name] drop primary key ;

描述

方式1 和方式2 都可以生效，这个可以根据个人喜好进行选择，index_name 是索引的名字，table_name 是被操作的表的名称。因为我们每个表的主键只有一个，所以在删除主键的时候，不需要指定主键的名称。

案例

-- 嗨客网(www.haicoder.net)
DROP INDEX index_stu_no on db_index;

运行效果如下

23_删除索引后展示.png

我们可以看到之前表里面有两个索引，被我删除后还剩一个索引。

-- 嗨客网(www.haicoder.net)
alter table db_index drop index index_stu_no_and_name ;

运行效果如下

24_删除索引.png

索引都被删除了，该表没有索引。

总结

索引在数据库里面占有重要的地位，对索引的操作也是比较重要的，本章节，我们了解了查看表索引，建立索引和删除索引。