11级_Java_曹建波6.05 索引

索引

索引的分类

目前大部分MySQL索引都是以B-树（BTREE）方式存储的。BTREE方式构建为包含了多个节点的一棵树。顶部的节点构成了索引的开始点，叫做根。每个节点中含有索引列的几个值，节点中的每个值又都指向另一个节点或者指向表中的一行，一个节点中的值必须是有序排列的。指向一行的节点叫做叶子页。叶子页本身也是相互连接的，一个叶子页有一个指针指向下一组。这样，表中的每一行都会在索引中有一个对应值。查询的时候就可以根据索引值直接找到所在的行。

索引中的节点是存储在文件中的，所以索引也是要占用物理空间的，MySQL将一个表的索引都保存在同一个索引文件中。

如果更新表中的一个值或者向表中添加或删除一行，MySQL会自动地更新索引，因此索引树总是和表的内容保持一致。

按BTREE形式存储的主要索引类型介绍如下。

1.  普通索引（INDEX）

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

2.  唯一性索引（UNIQUE）

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

3.  主键（PRIMARY KEY）

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

4.  全文索引（FULLTEXT）

      MySQL支持全文检索和全文索引。在MySQL中，全文索引的索引类型为FULLTEXT。全文索引只能在VARCHAR或TEXT类型的列上创建，并且只能在MyISAM表中创建。它可以通过CREATE TABLE命令创建，1.  使用CREATE INDEX语句

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

语法格式：

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name

    [USING index_type]

    ON tbl_name (index_col_name,...)

其中，index_col_name格式为：

col_name [(length)] [ASC | DESC]

说明：

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

●  USING index_type：部分存储引擎允许在创建索引时指定索引的类型。index_type为存储引擎支持的索引类型的名称，MySQL支持的索引类型有BTREE和HASH。如果不指定USING子句，MySQL自动创建一个BTREE索引。

●  index_col_name：col_name表示创建索引的列名。length表示使用列的前length个字符创建索引。使用列的一部分创建索引可以使索引文件大大减小，从而节省磁盘空间。在某些情况下，只能对列的前缀进行索引。例如，索引列的长度有一个最大上限，因此，如果索引列的长度超过了这个上限，那么就可能需要利用前缀进行索引。BLOB或TEXT列必须用前缀索引。前缀最长为255字节，但对于MyISAM和InnoDB表，前缀最长为1000字节。

也可以通过ALTER TABLE或CREATE INDEX命令创建。对于大规模的数据集，通过ALTER TABLE（或CREATE INDEX）命令创建全文索引要比把记录插入带有全文索引的空表更快。

      另外，当表类型为MEMORY或HEAP时，除了BTREE索引，MySQL还支持哈希索引（HASH）。使用哈希索引，不需要建立树结构，但是所有的值都保存在一个列表中，这个列表指向相关页和行。当根据一个值获取一个特定的行时，哈希索引非常快。

另外还可以规定索引按升序（ASC）还是降序（DESC）排列，默认为ASC。如果一条SELECT语句中的某列按照降序排列，那么在该列上定义一个降序索引可以加快处理速度。

●   UNIQUE | FULLTEXT | SPATIAL：UNIQUE表示创建的是唯一性索引；FULLTEXT表示创建全文索引；SPATIAL表示为空间索引，可以用来索引几何数据类型的列。本书不讨论SPATIAL索引。

可以看出，CREATE INDEX语句并不能创建主键。

根据XS表的学号列上的前5个字符建立一个升序索引XH­_XS。

CREATE INDEX XH_XS

    ON   XS(学号(5)  ASC);

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

在XS_KC表的学号列和课程号列上建立一个复合索引XSKC­_IN。

CREATE INDEX  XSKC_IN

    ON   XS_KC(学号,课程号);

2.  使用ALTER TABLE语句

第2章介绍了如何使用ALTER TABLE语句修改表，其中也包括向表中添加索引。

语法格式如下：

ALTER [IGNORE] TABLE tbl_name

    ADD INDEX [index_name] [index_type] (index_col_name,...)                /*添加索引*/

  | ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)/*添加主键*/

  | ADD [CONSTRAINT [symbol]]UNIQUE [index_name] [index_type] (index_col_name,...)

                                                                                    /*添加唯一性索引*/

  | ADD [FULLTEXT | SPATIAL] [index_name] (index_col_name,...)            /*添加全文索引*/

  | ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...)

                                   [reference_definition]                         /*添加外键*/

  | DISABLE KEYS

  | ENABLE KEYS

说明：

●   index_type：语法格式为USING {BTREE | HASH}。

当定义索引时默认索引名，则一个主键的索引叫做“PRIMARY”，其他索引使用索引的第一个列名作为索引名。如果存在多个索引的名字以某一个列的名字开头，就在列名后面放置一个顺序号码。

●  CONSTRAINT [symbol]：为主键、UNIQUE键、外键定义一个名字。这个将在命名完整性约束一节中介绍。

●   DISABLE KEYS | ENABLE KEYS：只在MyISAM表中有用，使用ALTER TABLE...DISABLE KEYS可以让MySQL在更新表时停止更新MyISAM表中的非唯一索引，然后使用ALTER TABLE ... ENABLE KEYS重新创建丢失的索引，这样可以大大地加快查询的速度。

在XS表的姓名列上创建一个非唯一的索引。

ALTER TABLE XS

       ADD INDEX XS_XM USING BTREE (姓名);

以XS表为例（假设XS表中主键未定），创建这样的索引，以加速表的检索速度：

ALTER TABLE XS

       ADD PRIMARY KEY(学号),

       ADD INDEX mark(出生日期,性别);

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

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

SHOW INDEX FROM XS;

3.  在创建表时创建索引

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

语法格式：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

    [ ( [column_definition] , ... | [index_definition] ) ]

    [table_option] [select_statement];

其中，index_definition为索引项：

  [CONSTRAINT [symbol]]PRIMARY KEY [index_type] (index_col_name,...)            /*主键*/

| {INDEX | KEY} [index_name] [index_type] (index_col_name,...)                       /*索引*/

| [CONSTRAINT [symbol]] UNIQUE [INDEX] [index_name] [index_type] (index_col_name,...)

                                                                                                                                                   /*唯一性索引*/

| [FULLTEXT|SPATIAL] [INDEX] [index_name] (index_col_name,...)                  /*全文索引*/

| [CONSTRAINT [symbol]] FOREIGN KEY  [index_name] (index_col_name,...) [reference_definition]    

                                                                                                                                                   /*外键*/

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

创建XS_KC表的语句如下，XS_KC表带有学号和课程号的联合主键，并在成绩列上创建索引。

CREATE TABLE XS_KC

(

       学号   CHAR(6) NOT NULL,

       课程号 CHAR(3) NOT NULL,

       成绩   TINYINT(1),

       学分    TINYINT(1),

       PRIMARY KEY(学号,课程号),

       INDEX CJ(成绩)

);

1.  使用DROP INDEX语句删除索引

语法格式：

DROP INDEX index_name ON tbl_name

这个语句语法非常简单，index_name为要删除的索引名，tb1_name为索引所在的表。

删除XS表上的XS_XH索引。

DROP INDEX XS_XH ON XS;

2.  使用ALTER TABLE语句删除索引

语法格式：

ALTER [IGNORE] TABLE tbl_name

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

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

  | DROP FOREIGN KEY fk_symbol                                            /*删除外键*/

其中，DROP INDEX子句可以删除各种类型的索引。使用DROP PRIMARY KEY子句时不需要提供索引名称，因为一个表中只有一个主键。

删除XS表上的主键和mark索引。

ALTER TABLE XS

       DROP PRIMARY KEY,

       DROP INDEX mark;

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