浅析MySQL索引

字体说明：  红色字体为需要强调的。 蓝色字体为MySQL关键字。 绿色字体为解释性说明文字。

另，图片里面的标注和红色字体解释也很重要。

简介：

      索引是对数据库表中的一列或者多列的值进行排序的一种结构，使用索引可以提高数据库中特定数据的

查询速度。

1.  索引的含义

     索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据库表里所有记录的引用指针。

MySQL中索引的存储类型有两种：BTREE 和 HASH，具体和表的存储引擎有关。MyISAM和InnoDB存储引擎

只支持BTREE索引。

2.  索引的分类

2.1  普通索引和唯一索引

       普通索引是MySQL的基本索引类型，允许在定义索引的列中插入重复值和空值。唯一索引列的值必须唯

一，但允许有空值。主键索引是一种特殊的唯一索引，不允许有空值。

2.2  单列索引和祝组合索引

        组合索引即是在多个列上创建索引。查询时，只有在查询条件中使用了这些字段（创建组合索引的时候

指定的哪些列）的最左边字段时，索引才会被使用。

2.3  全文索引

        全文索引FULLTEXT，在定义索引的列上支持全文查找，允许这些索引列中插入重复值和空值。全文索

引可以在CHAR、VARCHAR、TEXT类型的列上创建。MySQL中只有MyISAM存储引擎支持全文索引。

2.4  空间索引

        略（主要是不熟悉）。

3.  索引的设计原则

      关于索引的创建原则，相信大家也了解很多，废话少说，只是简单的提一两点就好了。1）  索引并非越多

越好，一个表中如果有大量的索引，不仅占用磁盘空间，而且影响*INSERT、DELETE、UPDATE等语句的性

能。  2） 当唯一性是某种数据本身的特征时，指定唯一索引。 3） 在频繁进行排序或者分组（GROUP BY 

或者ORDER BY）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

4.  创建索引

4.1  在创建表的时候创建索引

        语法：

        CREATE   TABLE  表名  [   列名称  数据类型 ]  

        [   UNIQUE  |   FULLTEXT  ]   [   INDEX   |    KEY    ]    [   索引名称  ]   ( 列 名称  [  length  ]  )  [   ASC |  DESC ]

        说明：   UNIQUE  、 FULLTEXT  为可选参数，分别表示唯一索引、全文索引；INDEX 与 KEY为同义词，

两者的作用相同，用来指定索引；参数中列名称为需要创建索引的字段；参数中使用名称为可选参数，不指定

的话，默认名称为对应的列名称；length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引

长度；ACS或者DESC指定升序或者降序的索引值存储。

4.1.1  创建普通索引

          例：在book表中的year_publication字段上创建普通索引。

CREATE  TABLE book

(

bookid   INT  NOT  NULL,

bookname VARCHAR(100)  NOT  NULL,

authors  VARCHAR(100)  NOT  NULL,

info  VARCHAR(500) NULL,

year_publication   YEAR  NOT  NULL,

INDEX(year_publication)

);

如图：

 

用  EXPLAIN  SQL;   来查看执行计划。如下图：

   

4.1.2  创建唯一索引

          例： 创建一个表t1，在表t1中的字段id上使用UNIQUE 关键字创建唯一索引。

CREATE TABLE t1

(

  id  INT  NOT  NULL,

  name  CHAR(50)  NOT  NULL,

   UNIQUE  INDEX   UniqueIdx(id)

);

/*   查看t1  */

SHOW CREATE   table  t1   \G;

如图：

 

4.1.3  创建单列索引

           例： 创建一个表t2，在那么字段上创建单列索引。

CREATE TABLE t2

(

  id  INT  NOT  NULL,

  name  CHAR(50)  NOT  NULL,

   UNIQUE  INDEX   SingleIdx(name(20))

);

SHOW CREATE  table  t2  \G;

如图：

 

4.1.4  创建组合索引

          例：创建表t3，在id、name和age字段上创建组合索引。

CREATE TABLE t3

(

  id  INT  NOT  NULL,

  name  CHAR(50)  NOT  NULL,

  age  INT  NOT  NULL,

  info  VARCHAR(200),

  INDEX   MultiIdx(id,name,age)

);

SHOW CREATE  table  t3  \G;

          如图：

 

比较组合索引在查询记录的区别。如下

EXPLAIN   SELECT  *  FROM t3  WHERE id =1 AND name = 'zyj' \G;

EXPLAIN   SELECT  *  FROM t3  WHERE name = 'zyj' AND age = 28 \G;

 

4.1.5  创建全文索引

          例： 创建表t4，在info字段上创建全文索引。如下：

/*  MySQL只有MyISAM存储引擎支持FULLTEXT索引，并且类类型为CHAR、TEXT、VARCHAR。

    并且需要指定表的存储引擎为MyISAM。

  */

CREATE TABLE t4

(

  id  INT  NOT  NULL,

  name  CHAR(50)  NOT  NULL,

  age  INT  NOT  NULL,

  info  VARCHAR(200),

   FULLTEXT INDEX   FullTextIdx(info)

)  ENGINE = MyISAM ;

SHOW CREATE  table  t4   \G;

          如图：

 

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

4.2.1  使用ALTER TABLE语句创建索引

           语法：

ALTER  TABLE  table_name   ADD    [  UNIQUE  |  FULLTEXT  ]   [  INDEX   |   KEY    ]  

[  inex_name  ]  (col_name [ length ] ,...)  [ASC | DESC ]

            例： 在book表中的book那么字段上建立名为NkNameIdx的普通索引。如下：

ALTER  TABLE  book   ADD  INDEX  NkNameIdx( bookname(30) );

SHOW CREATE  table  book  \G;

            如图：

 

          例：在book表的bookId字段上创建UniqueIdx的唯一索引。如下：

ALTER  TABLE  book  ADD UNIQUE INDEX  UniqueIdx( bookid );

SHOW CREATE  table  book  \G;

          如图：

 

          例： 在book表中的authors和info字段上建立组合索引。如下

ALTER  TABLE  book  ADD  INDEX  BkAndInfoIdx( authors(20), info(50) );

SHOW CREATE  table  book  \G;

          如图：

 

          例： 创建表t6，在t6上用 ALTER   TABLE创建全文索引。如下：

CREATE TABLE t6

(

  id  INT  NOT  NULL,

  info  VARCHAR(200)

) ENGINE = MyISAM ; 

ALTER TABLE t6  ADD   FULLTEXT  INDEX infiIdx(info);

SHOW CREATE  table  t6  \G;

          如图：

 

4.2.2  使用CREATE INDEX 创建索引

           语法：

CREATE     [  UNIQUE  |  FULLTEXT  ]   [  INDEX   |   KEY    ]   INDEX  index_name

ON   table_name ( col_name[ length ] , ... )  [ASC | DESC ]

      例：在表book的bookname字段上建立名为BkNameIdx的索引。如下：

CREATE INDEX   BkNameIdx   ON   book (bookname);

      例： 在book表的bookId字段上建立唯一索引。如下：

CREATE   UNIQUE    INDEX   UniqueIdx    ON   book (bookId);

5.  删除索引

5.1  使用ALTER TABLE撒很难出索引

         语法：

ALTER   TABLE  table_name    DROP   INDEX  index_name;

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

        语法：

DROP    INDEX     index_name    ON  table_name ;

======= MySQL 索引到此结束。又快外1点了。累啊，赶紧休息了。呵呵 =======