索引（基础）

1、索引介绍

1.1、前言

在数据库中，执行如下语句时:

                  
                    select * from emp where id=1000;

                  
                

mysql 是从第一条记录开始遍历，直至找到 id = 1000 的数据，然而这样查询的效率低，所以 mysql 允许通过建立索引来加快数据表的查询和排序.

1.2、索引概念

数据库的索引类似字典中的拼音， 是对数据库表中一列或多列的值进行排序后的一种结构.

作用： 就是提高表中数据的查询速度.

1.3、索引分类

1. 普通索引:

   不加任何参数，直接通过 key 或 index 来创建的索引。是 mysql 中的基本索引类型，可创建在任意数据类型中。其值是否唯1、非空，则由字段本身的约束决定.

2. 唯一索引:

   在定义索引时，加上 unique 即可，该索引所在字段的值必须唯一。但允许有空值。在一张数据表里可以有多个 唯一索引 .

3. 全文索引 :

   在定义索引时，加上 fulltext 即可，该索引只能创建在 char、varchar 、text 类型的字段上.

4. 单列索引 :

   是在表中单个字段上创建索引，可以为普通索引、唯一索引等，只要保证该索引只对应表中一个字段即可.

5. 多列索引 :

   是在表中多个字段上创建索引，但是只有在查询条件中使用了这些字段中的第一个字段时，该索引才会被使用.

6. 空间索引 ： 在定义索引时，加上 spatial 即可，该索引只能创建在空间数据类型的字段上.

   mysql 中空间类型索引有 4 中： GEOMETRY、POINT、LINESTRING 和 POLYGON 。但是当使用空间索引时，该字段的约束必须为 not null 且数据库的存储引擎为 MyISAM 中使用.

注：主键：primary key 也可作为索引 。

但本质上看其是一种约束，而索引是一种数据结构，用来提升查询效率 。

2、创建索引

2.1、创建表时

语法:

                  
                    create table 表名(
	字段 数据类型 约束
    [unique|fulltext|spatial] index|key [别名] (字段) [ASC|DESC]
 
);

                  
                

[]：中的值表示可选项 。

[unique|fulltext|spatial]：分别表示：唯一索引、全文索引、空间索引 。

[ASC|DESC]：升序、降序 。

2.1.1、创建普通索引

语法:

                  
                    create table 表名(
	字段 数据类型 约束
     index|key [别名] (字段) [ASC|DESC]
);

                  
                

例如这里需要在 t1 表中的 id 创建索引:

                  
                    create table t1(
	id int,
    name varchar(10),
    age int,
    index(id)
);

                  
                

通过下述命令查看是否创建成功:

                  
                    show create table t1\g

                  
                

通过上图可知， t1 表中成功创建索引.

可再通过下述命令查看是否使用:

                  
                    explain select * from t1 where id=1\g

                  
                

通过图中的 possible_keys 与 key 知 其值都为 id ，表明 id 索引已经存在并开始使用.

2.1.2、创建唯一索引

语法:

                  
                    create table 表名(
	字段 数据类型 约束
     unique index|key [别名] (字段) [ASC|DESC]
);

                  
                

例如这里需要在 t2 表中在 id 上创建 unique_id 索引:

                  
                    create table t2(
	id int,
    name varchar(7),
    score float,
    unique index unique_id (id ASC)
);

                  
                

2.1.3、创建全文索引

语法:

                  
                    create table 表名(
	字段 数据类型 约束
     fulltext index|key [别名] (字段) [ASC|DESC]
);

                  
                

例如这里需要在 t3 表上根据 name 字段来创建全文索引:

                  
                    create table t3(
	id int,
    name varchar(7),
    score float,
    fulltext index fulltext_name (name)
);

                  
                

2.1.4、创建单列索引

语法:

                  
                    create table 表名(
	字段 数据类型 约束
     index|key [别名] (字段1,字段2......) [ASC|DESC]
);

                  
                

例如需要在 t4 表上根据 name 创建索引 。

                  
                    create table t4(
	id int,
    name varchar(7),
    score float,
    index single_name (name(7))
);

                  
                

2.1.5、创建多列索引

语法:

                  
                    create table 表名(
	字段 数据类型 约束
     index|key [别名] (字段1,字段2......) [ASC|DESC]
);

                  
                

例如需要在 t5 表上创建多列索引:

                  
                    create table t5(
	id int,
    name varchar(7),
    score float,
    index multi (id,name(7))
);

                  
                

注意：只有当在查询条件中使用索引字段的第一个字段时，才会有效。验证如下:

这里通过 id 来查询:

                  
                    explain select * from t5 where id=1\g

                  
                

当通过 name 来查询时

                  
                    explain select * from t5 where name='张三'\g

                  
                

这时，发现通过 name 查询的结果中： possible_keys | key 皆为 null,表明索引并为使用.

2.1.6、创建空间索引

语法:

                  
                    create table 表名(
	字段 数据类型 约束
     spatial index|key [别名] (字段1,字段2......) [ASC|DESC]
);

                  
                

例如需要在 t6 表上创建空间索引:

                  
                    create table t6(
	id int,
    space geometry not null,
    spatial index sp (space)
);

                  
                

2.2、创表后

1、通过 create 创建

语法:

                  
                    create [unique|fulltext|spatial] index 索引名 on 表明 (字段名[长度]) [asc|desc];

                  
                

这里以创建唯一索引为例:

需要在 t7 表上根据 bookid 创建唯一索引 。

                  
                    create unique index unique_id on book(bookid);

                  
                

其余类似.

2、通过 alter 创建

语法:

                  
                    alter table 表名 add [unique|fulltext|spatial] index 索引名 (字段名[长度]) [asc|desc];

                  
                

这里以创建唯一索引为例:

需要在 t7 表上根据 bookid 创建唯一索引 。

                  
                    alter table book add unique index unique_id (bokid);

                  
                

其余类似.

3、删除索引

由于索引会占用一定的磁盘空间，所以为了避免影响数据库性能，需删除不使用的索引.

3.1、alter

语法:

                  
                    alter table 表名 drop index 索引名

                  
                

这里以删除唯一索引为例:

需要在 t7 表上根据 bookid 删除唯一索引:

                  
                    alter table book drop index unique_id;

                  
                

其余类似.

3.2、drop

语法:

                  
                    drop index 索引名 on 表名;

                  
                

这里以删除唯一索引为例:

需要在 t7 表上根据 bookid 删除唯一索引:

                  
                    drop index unique_id on book;

                  
                

其余类似.

最后此篇关于索引初识的文章就讲到这里了,如果你想了解更多关于索引初识的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！ 。