mysql中索引的操作

1 为什么使用索引

        数据库对象索引其实和书的目录类似主要是提高从表中检索的速度。由于数据存储在数据库表中，所以索引是创建在数据库表对象上的，由表中的一个字段或者多个字段生成的键组成，这些键存储在数据结构中（B-树或哈希表），通过mysql可以快速有效的查询与键值相关联的字段。根据索引存储的类型可以将索引分为B型树索引（BTREE）和哈希索引（HASH）.

注：InooDB和MyISAM存储引擎支持BTREE索引类型，MEMORY存储引擎支持HASH类型索引，默认为前者索引。

       数据库索引的出现，除了可以提高数据库管理系统的查询速度，而且还可以保证字段的唯一性，从而实现数据库表的完整性。mysql支持6中索引：普通索引，唯一索引，全文索引 ，单列索引，多列索引，和空间索引。

   以下情况适合创建索引：

         * 经常被查询的字段，即在where字句中出现的字段。

         * 在分组的字段，即在 GROUP BY 字句中出现的字段。

         * 存在依赖关系的字表和父表之间的联合查询，即主键和外键。

         * 设置唯一完整性约束的字段。

  以下情况不适合创建索引：

       * 在查询中很少被使用的字段。

       * 拥有许多重复值的字段。

2 创建和查看索引（三种方式）

      2.1 创建和查看普通索引（第一种）

            语法形式：

            CREATE TABLE table_name(

                 属性名    数据类型，

                 属性名    数据类型，

                  属性名    数据类型，

                   ........

                INDEX(KEY)  【索引名】（属性1 【长度】 【ASC|DESC】）

);

   INDEX或者KEY参数用来指定字段的索引，“索引名”参数用来指定所创建索引名，“属性1”参数指定索引所关联的字符的名称，“长度”参数指定索引的长度，“ASC或DESC”参数指定升序或降序。

例：

创建t_dept表。

CREATE TABLE t_dept(

          deptno INT,

          dname VARCHAR(20),

          loc VARCHAR(40),

          INDEX index_deptno(deptno)

);

校验索引是否创建成功

        SHOW CREATE TABLE t_dept  \G

     2.2 在已经存在的表中创建普通索引（第二种）

         语法形式：CRETAE INDEX 索引名  ON  表名 （属性名 【长度】 【ASC|DESC】）

例：创建索引

      CREATE INDEX index_deptno  ON t_dept (deptno);

校验索引是否创建成功

         SHOW CREATE TABLE t_dept  \G

     2.3 通过sql语句ALTER TABLE创建普通索引（第三种）

          语法形式：ALTER TABLE table_name  ADD INDEX|KEY  索引名 （属性  【长度】  ASC|DESC）;

例：

执行sql语句：

          ALTER TABLE t_dept ADD INDEX index_deptno(deptno);

       校验结果：

          SHOW CREATE TABLE t_dept  \G

3创建和查看唯一索引

所谓唯一索引，就是在创建索引时，限制索引的值必须唯一。通过该类型的索引可以更快速的查询某条记录，可以分为自动索引和手动索引。

  3.1 创建表时创建唯一索引

       

  CREATE TABLE table_name(

                 属性名    数据类型，

                 属性名    数据类型，

                  属性名    数据类型，

                   ........

                 UNIQUE  INDEX(KEY)  【索引名】（属性1 【长度】 【ASC|DESC】）

);

  例;

创建表

         

CREATE TABLE t_dept(

          deptno INT UNIQUE,

          dname VARCHAR(20),

          loc VARCHAR(40),

          UNIQUE  INDEX index_deptno(deptno)

);

         

校验
  SHOW CREATE TABLE t_dept  \G

      

     3.2在已经存在的表上创建唯一索引

          语法形式：CREATE UNIQUE  INDEX 索引名  ON  表名 （属性名 【长度】 【ASC|DESC】）

例：

CREATE UNIQUE  INDEX index_deptno  ON t_dept (deptno);

校验

 SHOW CREATE TABLE t_dept  \G

      3.3 通过sql语句创建唯一索引

             语法形式：ALTER  TABLE table_name ADD UNIQUE INDEX|key  索引名 （属性  【长度】  ASC|DESC）;

例：

 ALTER TABLE t_dept ADD  UNIQUE INDEX index_deptno(deptno);

      校验

 SHOW CREATE TABLE t_dept  \G

4创建和查看全文索引

全文索引主要关联在数据类型为CHAR VARCHAR TEXT 字段上，以便能够快速地查询数据量较大的字符串类型的字段。

MYSQL从3.23.23版本开始支持全文索引。只能在存储引擎为MyISAM的数据库表创建全文索引，默认情况下全文索引的搜索方式不区别大小写，如果为二进制数据类型，则区分。

     4.1创建表时创建全文索引

          语法形式：

CREATE TABLE table_name(

                 属性名    数据类型，

                 属性名    数据类型，

                  属性名    数据类型，

                   ........

                 FULLTEXT  INDEX(KEY)  【索引名】（属性1 【长度】 【ASC|DESC】）

);

  

         4.2 在已经存在的表中创建全文索引

语法形式：CREATE FULLTEXT  INDEX 索引名  ON  表名 （属性名 【长度】 【ASC|DESC】）

        4.3通过sql语句创建全文索引

  语法形式：ALTER  TABLE table_name ADD FULLTEXT INDEX|key  索引名 （属性  【长度】  ASC|DESC）;

 5创建和查看多列索引

     5.1创建表时创建多列索引

        语法形式：

CREATE TABLE table_name(

                 属性名    数据类型，

                 属性名    数据类型，

                 属性名    数据类型，

                   ........

                  INDEX(KEY)  【索引名】（属性1 【长度】 【ASC|DESC】

                                                                                         .......

                                                                                          属性名n 【长度】 【ASC|DESC】）

);

    5.2在已经存在的表中创建多列索引

语法形式：CREATE  INDEX 索引名  ON  表名 （属性名 【长度】 【ASC|DESC】

                                                                                             .......

                                                                                          属性名n 【长度】 【ASC|DESC】

）

  5.3

通过sql语句创建多列索引

  语法形式：ALTER  TABLE table_name ADD  INDEX|key  索引名 （属性名  【长度】  ASC|DESC

                                                                                                                           .......

                                                                                                                         属性名n 【长度】 【ASC|DESC】

）;

6删除索引

  DROP INDEX index_name ON table_name;