MySQL之索引

一、索引的介绍

  1、优点：提高MySQL的检索速度，使之高效运行。

  2、索引分类：

          1）单列索引：一个索引只包含单个列

          2）组合索引：一个索引包含多个列

  3、索引本质：索引也是一张表，该表保存了主键与索引字段，并且指向实体表的记录，索引应用在SQL查询语句的条件中（where的条件）。

  4、缺点：索引会降低表进行insert、update、delete的速度（需要对数据库中的表和索引都进行对应的操作），索引文件会占用磁盘空间，所以索引不能滥用，应该适当。

二、索引的使用

  1、普通索引

     1）创建索引（三种）

                 （1）最基本的索引（注意：char/varchar类型的字段，length可以小于字段实际长度；blob/text类型字段，必须指定length）

  create index indexName on firstIndex(name(length));  

                 （2）添加索引（修改表结构）

  alter firstIndex add index [indexName]  on(name(length));

                 （3）在创建表的同时创建索引（也称指定索引）

  create table firstIndex (id bigint not null，name varchar(16) not null,index[indexName] (name(length)));

       2）删除索引

  drop index [indexName] on myfirstIndex;

 

  2、唯一索引(索引列的值(或索引组合列的值)必须唯一)

       1）创建索引（三种）

                （1）直接创建索引

   create unique index indexName on firstIndex(name(length));

                （2）修改表结构时添加        

  alter table firstIndex add unique [indexName] (name(length));

                （3）创建表时直接指定

 create table firstIndex(id bigint not null,username varchar(16) not null,unique [indexName] (username(length)));

 

3、添加和删除索引(使用alter进行添加的四种方式)

          1）由于添加了主键，索引值必须是唯一的（NOT NULL）

    alter table tbl_name add primary key(column_list);

          2）创建索引的值必须是唯一的。

   alter table tbl_name add unique index_name(column_list);

          3）添加普通索引(索引值不唯一)

  alter table tbl_name add index index_name(column_list);

          4）索引为fulltext(用于全文索引)

    alter table tbl_name add fulltext index_name(column_list);

          5）使用drop在alter命令中删除索引

  alter table tbl_name drop index lh;

 

4、添加和删除主键（使用alter）

       注意：添加主键索引时，主键只能作用在一个列上，而且该主键必须not null。

          1）修改firstIndex中id符合主键索引条件：

    alter table firstIndex modify id bigint not null;

          2）添加主键

  alter table firstIndex add primary key(id);

          3）删除主键(删除主键只需指定PRIMARY KEY即可,但是删除索引，需要指定索引名)

   alter table firstIndex drop primary key;

 

 

5、显示索引信息（\G：格式化输出信息）

   show index from firstIndex;