20.索引

索引简介

索引是一种特殊的数据库结构，可以用来快速查询数据库表中的特定记录。索引是提高数据库性能的重要方式。MySQL中，所有的数据类型都可以被索引。

索引由数据库表中一列或多列组合而成，其作用是提高对表中数据的【查询】速度。

索引是创建在表上的，是对数据库表中一列或多列的值进行【排序】的一种结构。

索引可以提高查询的速度。通过索引，查询数据时可以【不必读完记录的所有信息】，而只是查询索引列。

索引的优点是可以提高检索数据的速度，这是创建索引的最主要的原因；对于有依赖关系的子表和父表之间的联合查询时，可以提高查询速度；使用分组和排序子句进行数据查询时，同样可以显著节省查询中分组和排序的时间。

索引的缺点是创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加；索引需要占用物理空间，每一个索引要占一定的物理空间；增加、删除和修改数据时，要动态的维护索引，造成数据的维护速度【降低】了。

索引分类

普通索引
惟一性索引
全文索引
单列索引
多列索引
空间索引

创建索引

创建索引是指在某个表的一列或多列上建立一个索引，以便提高对表的访问速度。创建索引有三种方式，这三种方式分别是：

创建表的时候创建索引
在已经存在的表上创建索引
使用ALTER TABLE语句来创建索引

创建表的时候创建索引

创建表的时候可以直接创建索引，这种方式最简单、方便。其基本形式如下： CREATE TABLE 表名 ( 属性名数据类型 [完整性约束条件], 属性名数据类型 [完整性约束条件], … 属性名数据类型 [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY [别名](属性名1 [(长度)] [ASC | DESC]) );

sql

--普通索引
Create table index1(
 Id int,
 Name varchar(20),
 Sex boolean,
 Index(id)
);

sql

创建唯一性索引
Create table index2(
 Id int unique,
 Name varchar(20),
 Unique index index2_id(id asc)
);

创建全文索引只能创建在char,varchar或text类型的字段上，而且，现在只有MyISAM存储引擎支持全文索引。

sql

Create table index3(
  Id int,
  Info varchar(20),
  Fulltext index index3_info(info)
)engine=myisam;

创建单列索引

sql

Create table index4(
 Id int,
 Subject varchar(30),
 Index index4_st(subject(10))
);

创建多列索引

sql

Create table index5(
  Id int,
  Name varchar(20),
  Sex char(4),
  Index index5_ns(name,sex)
);

使用多列索引时一定要特别注意，只有使用了索引中的第一个字段时才会触发索引。如果没有使用索引中的第一个字段，那么这个多列索引就不会起作用。

创建空间索引

sql

Create table index6(
 Id int,
 Space geometry not null,
 Spatial index index6_sp(space)
)engine=myisam;

创建空间索引时，表的存储引擎必须是myisam类型，而且索引字段必须有非空约束。空间数据类型包括geometry,point,linestring和polygon类型等。平时很少用到。

在已经存在的表上创建索引

在已经存在的表上，可以直接为表上的一个或几个字段创建索引。基本形式如下： CREATE [ UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 ON 表名 (属性名 [ (长度) ] [ ASC | DESC] ); 创建普通索引创建惟一性索引创建全文索引创建单列索引创建多列索引创建空间索引

sql

CREATE TABLE t_index7(
  Id INT PRIMARY KEY,
  NAME VARCHAR(20)
);

CREATE UNIQUE INDEX  uname_constant ON t_index7(`NAME`)

用ALTER TABLE语句来创建索引

在已经存在的表上，可以通过ALTER TABLE语句直接为表上的一个或几个字段创建索引。基本形式如下： ALTER TABLE 表名 ADD [ UNIQUE | FULLTEXT | SPATIAL ] INDEX
索引名（属性名 [ (长度) ] [ ASC | DESC]）; 其中的参数与上面的两种方式的参数是一样的创建普通索引创建惟一性索引创建全文索引创建单列索引创建多列索引创建空间索引

删除索引

删除索引是指将表中已经存在的索引删除掉。一些不再使用的索引会降低表的更新速度，影响数据库的性能。对于这样的索引，应该将其删除。对应已经存在的索引，可以通过DROP语句来删除索引。基本形式如下： DROP INDEX 索引名 ON 表名 ;

索引的设计原则

为了使索引的使用效率更高，在创建索引的时候必须考虑在哪些字段上创建索引和创建什么类型的索引。索引的设计原则如下：选择惟一性索引为经常需要排序、分组和联合操作的字段建立索引为常作为查询条件的字段建立索引限制索引的数目尽量使用数据量少的索引尽量使用前缀来索引删除不再使用或者很少使用的索引

20.索引 ​

索引简介 ​

索引分类 ​

创建索引 ​

创建表的时候创建索引 ​

在已经存在的表上创建索引 ​

用ALTER TABLE语句来创建索引 ​

删除索引 ​

索引的设计原则 ​