MySQL学习笔记之十一 索引

    在我们学习数据库的无数历程当中，我们会发现，对于查询的性能的优化，索引都表现的非常出色。

    所以，这里，在学习MySQL索引过程中，我还是不厌其烦的把有关索引的一些理论知识在重新回顾一遍，虽然它们的使用与oracle是惊人的相似。

    一、索引的特点

    所有的MySQL列类型能被索引。在相关的列上的使用索引是改进SELECT操作性能的最好方法。

•      一个表最多可有16个索引。最大索引长度是256个字节，尽管这可以在编译MySQL时被改变。
•      对于CHAR和VARCHAR列，你可以索引列的前缀。这更快并且比索引整个列需要较少的磁盘空间。对于BLOB和TEXT列，你必须索引列的前缀，你不能索引列的全部。
•      MySQL能在多个列上创建索引。一个索引可以由最多15个列组成。（在CHAR和VARCHAR列上，你也可以使用列的前缀作为一个索引的部分）。

    虽然随着 MySQL 的进一步开发创建索引的约束将会越来越少，但现在还是存在一些约束的。下面的表根据索引的特性，给出了ISAM 表和MyISAM 表之间的差别：

   

  
    从此表中可以看到，对于 ISAM 表来说，其索引列必须定义为NOT NULL，并且不能对BLOB 和TEXT 列进行索引。MyISAM 表类型去掉了这些限制，而且减缓了其他的一些限制。两种表类型的索引特性的差异表明，根据所使用的MySQL 版本的不同，有可能对某些列不能进行索引。例如，如果使用3.23 版以前的版本，则不能对包含NULL 值的列进行索引。
     索引有如下的几种情况：

•      INDEX索引：通常意义的索引，某些情况下KEY是它的一个同义词。索引的列可以包括重复的值。
•      UNIQUE索引：唯一索引，保证了列不包含重复的值，对于多列唯一索引，它保证值的组合不重复。
•      PRIMARY KEY索引：也UNIQUE索引非常类似。事实上，PRIMARY KEY索引仅是一个具有PRIMARY名称的UNIQUE索引。这表示一个表只能包含一个     PRIMARY KEY。

    二、用Alter Table语句创建与删除索引

    为了给现有的表增加一个索引，可使用 ALTER TABLE 或CREATE INDEX 语句。ALTER TABLE 最常用，因为可用它来创建普通索引、UNIQUE 索引PRIMARY KEY 索引，如：
    ALTER TABLE tbl_name ADD INDEX index_name (column_list)
    ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)
    ALTER TABLE tbl_name ADD PRIMARY KEY index_name (column_list)
    其中 tbl_name 是要增加索引的表名，而column_list 指出对哪些列进行索引。一个(col1,col2,...)形式的列表创造一个多列索引。索引值有给定列的值串联而成。如果索引由不止一列组成，各列名之间用逗号分隔。索引名index_name 是可选的，因此可以不写它，MySQL 将根据第一个索引列赋给它一个名称。ALTER TABLE 允许在单个语句中指定多个表的更改，因此可以在同时创建多个索引。
    同样，也可以用ALTER TABLE语句产出列的索引：
    ALTER TABLE tbl_name DROP INDEX index_name
    ALTER TABLE tbl_name DROP PRIMARY KEY
    注意上面第一条语句可以用来删除各种类型的索引，而第二条语句只在删除PRIMARY KEY 索引时使用；在此情形中，不需要索引名，因为一个表只可能具有一个这样的索引。如果没有明确地创建作为PRIMARY KEY 的索引，但该表具有一个或多个UNIQUE 索引，则MySQL 将删除这些UNIQUE 索引中的第一个。
    如果从表中删除了列，则索引可能会受到影响。如果所删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。
    例如，对于上面所使用的student为例，你可能想为之创建这样的索引，以加速表的检索速度：
    mysql> ALTER TABLE student
    -> ADD PRIMARY KEY(id),
    -> ADD INDEX mark(english,Chinese,history);
    这个例子，既包括PRIMARY索引，也包括多列索引。记住，使用PRIMARY索引的列，必须是一个具有NOT NULL属性的列，如果你愿意产看创建的索引的情况，可以使用SHOW INDEX语句：
    mysql> SHOW INDEX FROM student;
    其结果为：
    +---------+------------+----------+--------------+-------------+-
    | Table | Non_unique | Key_name | Seq_in_index | Column_name |
    +---------+------------+----------+--------------+-------------+-
    | student | 0 | PRIMARY | 1 | id |
    | student | 1 | mark | 1 | english |
    | student | 1 | mark | 2 | chinese |
    | student | 1 | mark | 3 | history |
    +---------+------------+----------+--------------+-------------+-
    由于列数太多，上表并没有包括所有的输出，读者可以试着自己查看。
    再使用ALTER TABLE语句删除索引，删除索引需要知道索引的名字，你可以通过SHOW INDEX语句得到：
    mysql> ALTER TABLE student DROP PRIMARY KEY,
    -> DROP INDEX mark;
    再产看表中的索引，其语句和输出为：
    mysql> SHOW INDEX FROM student;
    Empty set (0.01 sec)

    三、用CREATE\DROP INDEX创建索引

    还可以用CREATE INDEX语句来创建索引.CREATE INDEX 是在MySQL 3.23版中引入的，但如果使用3.23 版以前的版本，可利用ALTER TABLE 语句创建索引（MySQL 通常在内部将CREATE INDEX 映射到ALTER TABLE）。该语句创建索引的语法如下：
    CREATE UNIQUE INDEX index_name ON tbl_name (column_list)
    CREATE INDEX index_name ON tbl_name (column_list)
    tbl_name、index_name 和column_list 具有与ALTER TABLE 语句中相同的含义。这里索引名不可选。很明显，CREATE INDEX 可对表增加普通索引或UNIQUE 索引，不能用CREATE INDEX 语句创建PRIMARY KEY 索引。
    可利用DROP INDEX语句来删除索引。类似于CREATE INDEX 语句，DROP INDEX 通常在内部作为一条ALTER TABLE 语句处理，并且DROP INDEX是在MySQL 3.22 中引入的。
    删除索引语句的语法如下：
    DROP INDEX index_name ON tbl_name
    还是上一节的例子，由于CREATE INDEX不能创建PRIMARY索引，所以这里我们值创建一个多列索引：
    mysql> CREATE INDEX mark ON student(english,chinese,history);
    同样的检查student表，可知：
    mysql> SHOW INDEX FROM student;
    +---------+------------+----------+--------------+-------------+
    | Table | Non_unique | Key_name | Seq_in_index | Column_name |
    +---------+------------+----------+--------------+-------------+
    | student | 1 | mark | 1 | english |
    | student | 1 | mark | 2 | chinese |
    | student | 1 | mark | 3 | history |
    +---------+------------+----------+--------------+-------------+
    然后使用下面的语句删除索引：
    mysql> DROP INDEX mark ON student;

    四、在创建表时指定索引

    要想在发布 CREATE TABLE 语句时为新表创建索引，所使用的语法类似于ALTER TABLE 语句的语法，但是应该在您定义表列的语句部分指定索引创建子句，如下所示：

    CREATE TABLE tbl_name
    (
    …
    INDEX index_name (column_list),
    KEY index_name (column_list),
    UNIQUE index_name (column_list),
    PRIMARY KEY index_name (column_list),
    …
    )

    与ALTER TABLE 一样，索引名对于INDEX 和UNIQUE 都是可选的，如果未给出，MySQL 将为其选一个。另外，这里KEY时INDEX的一个别名，具有相同的意义。
有一种特殊情形：可在列定义之后增加 PRIMARY KEY 创建一个单列的PRIMARY KEY 索引，如下所示：

    CREATE TABLE tbl_name
    (
    i INT NOT NULL PRIMARY KEY
    )

    该语句等价于以下的语句:

    CREATE TABLE tbl_name
    (
     i INT NOT NULL,
     PRIMARY KEY (i)
     )

    前面所有表创建样例都对索引列指定了 NOT NULL。如果是ISAM 表，这是必须的，因为不能对可能包含NULL 值的列进行索引。如果是MyISAM 表，索引列可以为NULL，只要该索引不是PRIMARY KEY 索引即可。
    在CREATE TBALE语句中可以某个串列的前缀进行索引（列值的最左边n 个字符）。
    如果对某个串列的前缀进行索引，应用column_list 说明符表示该列的语法为col_name(n) 而不用col_name。例如，下面第一条语句创建了一个具有两个CHAR 列的表和一个由这两列组成的索引。第二条语句类似，但只对每个列的前缀进行索引：

    CREATE TABLE tbl_name
   (
   name CHAR(30),
   address CHAR(60),
   INDEX (name,address)
   )
   CREATE TABLE tbl_name
   (
   name CHAR(30),
   address CHAR(60),
   INDEX (name(10),address(20))
   )

    你可以检查所创建表的索引：
    mysql> SHOW INDEX FROM tbl_name;
    +----------+------------+----------+--------------+-------------+-
    | Table | Non_unique | Key_name | Seq_in_index | Column_name |
    +----------+------------+----------+--------------+-------------+-
    | tbl_name | 1 | name | 1 | name |
    | tbl_name | 1 | name | 2 | address |
    +----------+------------+----------+--------------+-------------+-
    在某些情况下，可能会发现必须对列的前缀进行索引。例如，索引行的长度有一个最大上限，因此， 如果索引列的长度超过了这个上限，那么就可能需要利用前缀进行索引。在MyISAM 表索引中，对BLOB 或TEXT 列也需要前缀索引。
    对一个列的前缀进行索引限制了以后对该列的更改；不能在不删除该索引并使用较短前缀的情况下，将该列缩短为一个长度小于索引所用前缀的长度的列。