数据库-mysql之索引

目录

1、索引简介

1.1 索引的含义和特点

1.2 索引的分类

1、普通索引

2、唯一性索引

3、全文索引

4、单列索引

5、多列索引

6、空间索引

1.3 索引的设计原则

1．选择唯一性索引

2．为经常需要排序、分组和联合操作的字段建立索引

3．为常作为查询条件的字段建立索引

4．限制索引的数目

5．尽量使用数据量少的索引

6．尽量使用前缀来索引

7．删除不再使用或者很少使用的索引

2、创建索引

2.1 创建表的时候创建索引

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

2.3 用alter table 语句来创建索引

3、删除索引

---

索引是一种特殊的数据库结构，可以用来快速查询数据库表中的特定记录。索引是提高数据库性能的重要方式。MySQL中，所有的数据类型都可以被索引。MySQL 的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。

1、索引简介

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

1.1 索引的含义和特点

索引是创建在表上的，是对数据库表中一列或多列的值进行排序的一种结构。索引可以提高查询的速度。

通过索引，查询数据时可以不必读完记录的所有信息，而只是查询索引列。否则，数据库系统将读取每条记录的所有信息进行匹配。例如，索引相当于新华字典的音序表。如果要查“库”字，如果不使用音序，需要从字典的400页中逐页来找。但是，如果提取拼音出来,构成音序表,就只需要从10多页的音序表中直接查找。这样就可以大大节省时间。因此，使用索引可以很大程度上提高数据库的查询速度。这样有效的提高了数据库系统的性能。

不同的存储引擎定义了每个表的最大索引数和最大索引长度。所有存储引擎对每个表至少支持16个索引，总索引长度至少为256字节。有些存储引擎支持更多的索引数和更大的索引长度。索引有两种存储类型，包括B型树（BTREE）索引和哈希（HASH)索引。InnoDB和 MyISAM存储引擎支持BTREE索引，MEMORY存储引擎支持 HASH索引和BTREE索引，默认为前者。

索引有其明显的优势，也有其不可避免的缺点。

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

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

索引可以提高查询的速度，但是会影响插入记录的速度。因为，向有索引的表中插入记录时，数据库系统会按照索引进行排序。这样就降低了插入记录的速度，插入大量记录时的速度影响更加明显。这种情况下，最好的办法是先删除表中的索引，然后插入数据。插入完成后，再创建索引。

1.2 索引的分类

MySQL 的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。

1、普通索引

在创建普通索引时，不附加任何限制条件。这类索引可以创建在任何数据类型中，其值是否唯一和非空由字段本身的完整性约束条件决定。建立索引以后，查询时可以通过索引进行查询。例如，在 student 表的stu_id字段上建立一个普通索引。查询记录时，就可以根据该索引进行查询。

2、唯一性索引

使用UNIQUE参数可以设置索引为唯一性索引。在创建唯一性索引时，限制该索引的值必须是唯一的。例如，在student 表的 stu_name字段中创建唯一性索引，那么stu_name字段的值就必需是唯一的。通过唯一性索引，可以更快速地确定某条记录。主键就是一种特殊唯一性索引。

3、全文索引

使用FULLTEXT参数可以设置索引为全文索引。全文索引只能创建在CHAR、VARCHAR或TEXT类型的字段上。查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。例如，student 表的information字段是TEXT类型，该字段包含了很多的文字信息。在 information字段上建立全文索引后，可以提高查询information字段的速度。MySQL数据库从3.23.23版开始支持全文索引，但只有MyISAM存储引擎支持全文检索。在默认情况下，全文索引的搜索执行方式不区分大小写。但索引的列使用二进制排序后，可以执行区分大小写的全文索引。

4、单列索引

在表中的单个字段上创建索引。单列索引只根据该字段进行索引。单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。

5、多列索引

多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。但是，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。例如，在表中的id、name和 sex字段上建立一个多列索引，那么，只有查询条件使用了id字段时该索引才会被使用。

6、空间索引

使用SPATIAL参数可以设置索引为空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。MySQL中的空间数据类型包括GEOMETRY和POINT、LINESTRING和POLYGON等。目前只有MyISAM存储引擎支持空间检索，而且索引的字段不能为空值。对于初学者来说，这类索引很少会用到。

1.3 索引的设计原则

为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。

1．选择唯一性索引

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。

2．为经常需要排序、分组和联合操作的字段建立索引

经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。

3．为常作为查询条件的字段建立索引

如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。

4．限制索引的数目

索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

5．尽量使用数据量少的索引

如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。

6．尽量使用前缀来索引

如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和 BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

7．删除不再使用或者很少使用的索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

选择索引的最终目的是为了使查询的速度变快。上面给出的原则是最基本的准则,但不能拘泥于上面的准则。要在以后的学习和工作中进行不断的实践。根据应用的实际情况进行分析和判断，选择最合适的索引方式。

2、创建索引

创建索引是指在某个表的一列或多列上建立一个索引，以便提高对表的访问速度。创建索引有3种方式，这3种方式分别是创建表的时候创建索引、在已经存在的表上创建索引和使用ALTER TABLE语句来创建索引。

2.1 创建表的时候创建索引

创建表时可以直接创建索引，这种方式最简单、方便。其基本形式如下:

create table (属性名 数据类型 [完整性约束条件]
              属性名 数据类型 [完整性约束条件]
              ...
              属性名 数据类型
              [unique | fulltext | spatial] index | key
                    
                    [别名] （属性名1 [(长度)] [asc | desc]）
              );

其中，UNIQUE是可选参数，表示索引为唯一性索引;FULLTEXT是可选参数，表示索引为全文索引;SPATIAL也是可选参数，表示索引为空间索引;INDEX和KEY 参数用来指定字段为索引的，两者选择其中之一就可以了，作用是一样的;“别名”是可选参数，用来给创建的索引取的新名称;“属性1”参数指定索引对应的字段的名称，该字段必须为前面定义好的字段;“长度”是可选参数，其指索引的长度，必须是字符串类型才可以使用;“ASC”和“DESC”都是可选参数，“ASC”参数表示升序排列，“DESC”参数表示降序排列。

1、创建普通索引

创建一个普通索引时，不需要加任何UNIQUE、FULLTEXT 或者SPATIAL参数。

下面创建一个表名为index1的表，在表中的id字段上建立索引。SQL代码如下:

使用EXPLAIN语句可以查看索引是否被使用，SQL代码如下:

 

结果显示，possible_keys和 key 处的值都为id。说明id索引已经存在，而且已经开始起作用。

2、创建唯一性索引

创建唯一性索引时，需要使用UNIQUE参数进行约束。

下面创建一个表名为index2的表，在表中的id字段上建立名为index2_id的唯一性索引，且以升序的形式排列。SQL代码如下:

运行结果显示创建成功，使用SHOW CREATE TABLE语句查看表的结构。显示如下:

3、创建全文索引

全文索引只能创建在CHAR、VARCHAR或 TEXT类型的字段上。而且，现在只有MyISAM存储引擎支持全文索引。

下面创建一个表名为index3的表，在表中的info字段上建立名为index3info的全文索引。SQL代码如下:

目前只有MyISAM存储引擎支持全文索引，InnoDB存储引擎还不支持全文索引。因此，在创建全文索引时一定注意表的存储引擎的类型。对于经常需要索引的字符串、文字数据等信息，可以考虑存储到MyISAM存储引擎的表中。

4、创建单列索引

单列索引是在表的单个字段上创建索引。

下面创建一个表名为index4 的表，在表中的 subject字段上建立名为index4_st的单列索引。SQL代码如下:

可能会发现，subject字段长度为20，而 index4_st索引的长度只有10。这样做的目的还是为了提高查询速度。对于字符型的数据，可以不用查询全部信息，而只查询其前面的若干字符信息。

5、创建多列索引

创建多列索引是在表的多个字段上创建一个索引。

下面创建一个表名为index5的表，在表中的name和 sex字段上建立名为index5 ns的多列索引。SOL代码如下:

用EXPLAIN语句可以查看索引的使用情况，显示结果如下:

【只使用name 字段作为查询条件】

 【只使用sex 字段作为查询条件】

 

！！！使用多列索引时一定要特别注意，只有使用了索引中的第一个字段时才会触发索引。如果没有使用索引中的第一个字段，那么这个多列索引就不会起作用。因此，在优化查询速度时，可以考虑优化多列索引。

6、创建空间索引

创建空间索引时必须使用SPATIAL参数来设置。创建空间索引时，表的存储引擎必须是MyISAM类型。而且，索引字段必须有非空约束。

下面创建一个表名为index6的表，在表中的space字段上建立名为index6_sp的空间索引。SQL代码如下:

结果可以看到，space字段上已经建立了一个名为 index6_sp的空间索引。值得注意的是，space字段是非空的，而且数据类型是GEOMETRY类型。这个类型是空间数据类型。空间类型包括GEOMETRY、POINT、LINESTRING和POLYGON类型等。这些空间数据类型平时很少用。

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

在已经存在的表中，可以直接为表上的一个或几个字段创建索引。基本形式如下:

create [unique | fulltext | spatial] index 索引名
  on 表名 （属性名 [(长度)] [asc | desc]）

其中，UNIQUE是可选参数，表示索引为唯一性索引;FULLTEXT是可选参数，表示索引为全文索引;SPATIAL也是可选参数，表示索引为空间索引;“INDEX”参数用来指.定字段为索引的;“索引名”参数是给创建的索引取的新名称;“表名”参数是指需要创建索引的表的名称，该表必须是已经存在的，如果不存在，需要先创建;“属性名”参数指定索引对应的字段的名称，该字段必须为前面定义好的字段;“长度”是可选参数，其指索引的长度,必须是字符串类型才可以使用;ASC和DESC都是可选参数，ASC参数表示升序排列，DESC参数表示降序排列。

比如：在example1表中的stu_id字段上建立名为index7_id的索引。

2.3 用alter table 语句来创建索引

在已经存在的表上，可以通过ALTER TABLE语句直接为表上的一个或几个字段创建索引。基本形式如下:

alter table 表名 add [ unique| filltext | spatial] index
索引名(属性名[(长度)] [asc | desc);

比如：在example1表中的stu_name字段上建立名为index8_name的索引。

3、删除索引

删除索引是指将表中已经存在的索引删除掉。一些不再使用的索引会降低表的更新速度，影响数据库的性能。对于这样的索引，应该将其删除。

对应已经存在的索引，可以通过DROP语句来删除索引。基本形式如下:

DROP INDEX 索引名 ON 表名;

其中，“索引名”参数指要删除的索引的名称;“表名”参数指索引所在的表的名称。

下面删除 index1表的索引: