一、简介

      索引是一种特殊的数据库结构,可以用来快速查询数据库表中的特定记录。索引是提高数据库性能的重要方式。在MySQL中,所有的数据类型都可以被索引。MySQL的索引包括:普通索引、唯一性索引、全文索引、单列索引、多列索引、空间索引。本文主要讲解以下几个方面:

1、索引的含义和特点

2、索引的分类

3、如何设计索引

4、如何创建索引

5、如何删除索引

1、 索引的含义和特点

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

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

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

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

1.1、索引的优缺点

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

索引的优点是:

        可以提高检索数据的速度,这是创建索引的最主要的原因;

        对于有依赖关系的子表和父表之间的联合查询时,可以提高查询速度;

        使用分组和排序子句进行数据查询时,同样可以显著节省查询中分组和排序的时间。

索引的缺点是:

        创建和维护索引需要耗费时间,耗费时间的数量随着数据量的增加而增加;

        索引需要占用物理空间,每一个索引要占一定的物理空间;

        增加、删除和修改数据时,要动态的维护索引,造成数据的维护速度降低了 。

因此,选择使用索引时,需要综合考虑索引的优点和缺点。

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

2、索引的分类


     2.1 普通索引

     2.2 唯一性索引

     2.3 全文索引

     2.4 单列索引

     2.5 多列索引

     2.6 空间索引

3、索引的设计原则

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

      3.1 选择唯一性索引

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

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

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

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

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

      3.4 限制索引的数目

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

      3.5 尽量使用数据量少的索引

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

      3.6 尽量使用前缀来索引

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

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

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


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


4、创建索引


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


4.1 创建表的时候创建索引


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

1. CREATE TABLE  表名 ( 属性名 数据类型 [完整性约束条件],  
2. 属性名 数据类型 [完整性约束条件],  
3. ......  
4. 属性名 数据类型  
5. [ UNIQUE | FULLTEXT | SPATIAL ]  INDEX | KEY  
6. [ 别名 ]  ( 属性名1  [(长度)]  [ ASC | DESC] )  
7. );

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


1、创建普通索引

【示例1】创建一个表名为user的表,在表中的id字段上建立索引。

 CREATE TABLE USER (    id INT,    NAME VARCHAR (50),    company VARCHAR (50),    INDEX (id));

索引_MySql索引

2、创建唯一性索引

【示例2】下面创建一个表名为user2的表,在表中的id字段上建立名为user2_id的唯一性索引,且以升序的形式排列。

CREATE TABLE user2 (    id INT UNIQUE,    NAME VARCHAR (20),    UNIQUE INDEX user2_id (id ASC)
);

结果可以看到,id字段上已经建立了一个名为index2_id的唯一性索引。这里的id字段可以没有进行唯一性约束,也可以在该字段上成功创建唯一性索引。但是,这样可能达不到提高查询速度的目的。

3、创建全文索引

【示例3】下面创建一个表名为user3的表,在表中的info字段上建立名为user3_ info的全文索引。

CREATE TABLE user3 (    id INT,    INFO VARCHAR (30),    FULLTEXT INDEX user3_info (info)
) ENGINE = MyISAM;

结果可以看到,info字段上已经建立了一个名为index3_info的全文索引。如果表的存储引擎不是MyISAM存储引擎,系统会提示"ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes"。

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

4、创建单列索引

【示例4】 下面创建一个表名为user4的表,在表中的subject字段上建立名为user4_st的单列索引。

CREATE TABLE user4 (    id INT,    SUBJECT VARCHAR (30),    INDEX user4_st (SUBJECT(10))
);

结果可以看到,subject字段上已经建立了一个名为index4_st的单列索引。细心的读者可能会发现,subject字段长度为20,而index4_st索引的长度只有10。这样做的目的还是为了提高查询速度。对于字符型的数据,可以不用查询全部信息,而只查询其前面的若干字符信息。

5、创建多列索引

【示例5】下面创建一个表名为index5的表,在表中的name和sex字段上建立名为index5_ns的多列索引

CREATE  TABLE  index5 (id  INT  ,      name   VARCHAR(20) ,      sex   CHAR(4) ,      INDEX  index5_ns ( name, sex )      );

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

6、创建空间索引

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

【示例6】 下面创建一个表名为index6的表,在表中的space字段上建立名为index6_sp的空间索引。

CREATE  TABLE  index6 (id  INT  ,      space  GEOMETRY  NOT NULL,      SPATIAL  INDEX  index6_sp (space )      )ENGINE=MyISAM; 结果可以看到,space字段上已经建立了一个名为index6_sp的空间索引。值得注意的是,space字段是非空的,而且数据类型是GEOMETRY类型。这个类型是空间数据类型。空间类型包括GEOMETRY、POINT、LINESTRING和POLYGON类型等。这些空间数据类型平时很少用到。

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

1.创建普通索引

【示例7】 下面在example0表中的id字段上建立名为index7_id的索引。

CREATE  INDEX  index7_id  ON  example0 ( id ) ;


2.创建唯一性索引

【示例8】 下面在index8表中的course_id字段上建立名为index8_id的唯一性索引。

CREATE  UNIQUE  INDEX  index8_id  ON  index8( course_id ) ;

3.创建全文索引

【示例9】 下面在index9表中的info字段上建立名为index9_info的全文索引。


CREATE  FULLTEXT  INDEX  index9_info  ON  index9( info ) ;


4.创建单列索引

【示例10】 下面在index10表中的address字段上建立名为index10_addr的单列索引。address字段的数据类型为VARCHAR(20),索引的数据类型为CHAR(4)。


CREATE  INDEX  index10_addr  ON  index10( address(4) ) ;


5.创建多列索引

【示例11】 下面在index11表中的name和address字段上建立名为index11_na的多列索引。


CREATE  INDEX  index11_na  ON  index11( name, address ) ;


6.创建空间索引

【示例12】 下面在index12表中的line字段上建立名为index12_line的多列索引。


4.3、用ALTER TABLE语句来创建索引

1.创建普通索引

【示例13】 下面在example0表中的name字段上建立名为index13_name的索引。


ALTER  TABLE  example0  ADD  INDEX  index13_name ( name(20) ) ;


2.创建唯一性索引


【示例14】 下面在index14表中的course_id字段上,建立名为index14_id的唯一性索引。


ALTER  TABLE  index14  ADD  UNIQUE  INDEX  index14_id ( course_id ) ;


3.创建全文索引

【示例15】 下面在index15表中的info字段上建立名为index15_info的全文索引。


ALTER  TABLE  index15  ADD  FULLTEXT  INDEX  index15_info ( info ) ;


4.创建单列索引

【示例16】 下面在index16表中的address字段上建立名为index16_addr的单列索引。address字段的数据类型为VARCHAR(20),索引的数据类型为CHAR(4)。


ALTER  TABLE  index16 ADD  INDEX  index16_addr( address(4) ) ;


5.创建多列索引

【示例17】 下面在index17表中的name和address字段上建立名为index17_na的多列索引。


ALTER  TABLE  index17  ADD  INDEX  index17_na( name, address ) ;


6.创建空间索引

【示例18】 下面在index18表中的line字段上建立名为index18_line的多列索引。



ALTER  TABLE  index18  ADD  SPATIAL  INDEX  index18_line( line ) ;


5、删除索引

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



DROP  INDEX  索引名  ON  表名 ;


其中,"索引名"参数指要删除的索引的名称;"表名"参数指索引所在的表的名称。