MySQL学习之（七）索引

索引 —— 是一种提高查找速度的机制

索引用来快速地寻找那些具有特定值的记录，如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。

索引提供指针以指向存储在表中指定列的数据值，然后根据指定的排序次序排列这些指针。数据库使用索引的方式与使用书的目录很相似：通过搜索索引找到特定的值，然后跟随指针到达包含该值的行。

如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录，通过索引查找记录至少要比顺序扫描记录快100倍。

1.索引机制

学生表student中建立“学号”索引（升序）示意图

没有索引文件时：

 

如果要找位于第10000条的学号”20070201”的记录, 计算机要在表中查找10000次

 

有索引文件时：(二分法查找实例)

   计算机先在索引文件中学号为”20070201”的记录,找到相应的记录号,再到学生表中直接读取相关记录.

索引文件如何加快查找速度？

原因：

 （1）索引后，指针在索引文件中顺序移动。

 （2）索引文件中记录是有序的。

 （3）有序后，可以用各种方法加快查询速度，如折半（二分）查找法，而排序前，只能顺序查找记录。

 

树索引示意图

2.索引的分类

1.  普通索引（INDEX）

这是最基本的索引类型，它没有唯一性之类的限制。创建普通索引的关键字是INDEX。

 

2.  唯一性索引（UNIQUE）

这种索引和前面的普通索引基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须是唯一的。创建唯一性索引的关键字是UNIQUE。

 

3.  主键（PRIMARY KEY）

主键是一种唯一性索引，它必须指定为“PRIMARY KEY”。主键一般在创建表的时候指定，也可以通过修改表的方式加入主键。但是每个表只能有一个主键。

 

4.  全文索引（FULLTEXT）

MySQL支持全文检索和全文索引。全文索引的索引类型为FULLTEXT。全文索引只能在VARCHAR或TEXT类型的列上创建，并且只能在MyISAM表中创建。

3、索引创建

①使用CREATE INDEX语句

使用CREATE INDEX语句可以在一个已有表上创建索引，一个表可以创建多个索引。

语法格式：

CREATE [UNIQUE | FULLTEXT] INDEX 索引名

ON 表名(列名[(length)] [ASC | DESC],...)

说明：

●索引名：索引的名称，索引名在一个表中名称必须是唯一的。

● 列名：表示创建索引的列名。

  length表示使用列的前length个字符创建索引。使用列的一部分创建索引可以使索引文件大大减小，从而节省磁盘空间。BLOB或TEXT列必须用前缀索引。

● UNIQUE:UNIQUE表示创建的是唯一性索引

● FULLTEXT:FULLTEXT表示创建全文索引；

● CREATE INDEX 语句并不能创建主键。

例：

根据Book表的书名列上的前6个字符建立一个升序索引name­_book。

      CREATE INDEX  name­_book

      ON   Book(书名(6)  ASC);

 

可以在一个索引的定义中包含多个列，中间用逗号隔开，但是它们要属于同一个表。这样的索引叫做复合索引。

②使用ALTER TABLE语句

使用ALTER TABLE语句修改表，其中也包括向表中添加索引。

语法格式如下：

   ALTER TABLE 表名

       ADD INDEX [索引名] (列名,...)           /*添加索引*/

      | ADD PRIMARY KEY [索引方式] (列名,...)  /*添加主键*/

      | ADD UNIQUE [索引名] (列名,...)   /*添加唯一性索引*/

      | ADD FULLTEXT [索引名] (列名,...)   /*添加全文索引*/

例：

假设Book表中主键未设定，为Book表创建以图书编号为主键索引，出版社和出版时间为复合索引，以加速表的检索速度。

    ALTER TABLE Book

ADD PRIMARY KEY(图书编号),

ADD INDEX mark(出版社,出版时间);

 

     这个例子中，既包括PRIMARY KEY，也包括复合索引，说明MySQL可以同时创建多个索引。记住，使用PRIMARY KEY的列，必须是一个具有NOT NULL属性的列。

 

如果想要查看表中创建的索引的情况，可以使用SHOW INDEX FROM tbl_name语句，例如：SHOW INDEX FROM book;

③创建表时创建索引

在前面两种情况下，索引都是在表创建之后创建的。索引也可以在创建表时一起创建。在创建表的CREATE TABLE语句中可以包含索引的定义。

语法格式：

     CREATE TABLE 表名 ( 列名, ... | [索引项])

其中，索引项语法格式如下：

       PRIMARY KEY (列名,...)                   /*主键*/

     | {INDEX | KEY} [索引名] (列名,...)       /*索引*/

     | UNIQUE [INDEX] [索引名] (列名,...)     /*唯一性索引*/

     | [FULLTEXT] [INDEX] [索引名] (列名,...)  /*全文索引*/

 

说明：KEY通常是INDEX的同义词。在定义列选项的时候，也可以将某列定义为PRIMARY KEY，但是当主键是由多个列组成的多列索引时，定义列时无法定义此主键，必须在语句最后加上一个PRIMARY KEY列名，…）子句。

 

例：

创建sell_copy表的语句如下，sell_copy表带有身份证号和图书编号的联合主键，并在订购册数列上创建索引。

 

    CREATE TABLE sell_copy (

身份证号   CHAR(18) NOT NULL,

图书编号   CHAR(20) NOT NULL,

订购册数   INT(5),

订购时间   DATETIME

,PRIMARY KEY(身份证号, 图书编号),

INDEX dgcs(订购册数)

     );

4、删除索引

①使用DROP INDEX语句删除索引

语法格式：

           DROP INDEX索引名 ON 表名

 

例：

删除Book表上的sm_book索引。

        DROP INDEX sm_book ON Book;

②使用ALTER TABLE语句删除索引

语法格式：

    ALTER [IGNORE] TABLE 表名

        | DROP PRIMARY KEY        /*删除主键*/

        | DROP INDEX 索引名    /*删除索引*/

 

【例】删除Book表上的主键和mark索引。

     ALTER TABLE Book

 DROP PRIMARY KEY,

 DROP INDEX mark;

 

如果从表中删除了列，则索引可能会受到影响。如果所删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。

5.索引的弊端

首先，索引是以文件的形式存储的，索引文件要占用磁盘空间。如果有大量的索引，索引文件可能会比数据文件更快地达到最大的文件尺寸。

其次，在更新表中索引列上的数据时，对索引也需要更新，这可能需要重新组织一个索引，如果表中的索引很多，这是很浪费时间的。也就是说，这样就降低了添加、删除、修改和其他写入操作的效率。表中的索引越多，则更新表的时间就越长。

但是这些弊端并不妨碍索引的应用，因为索引带来的好处已经基本掩盖了它的缺陷，在表中有很多行数据的时候，索引通常是不可缺少的。