MySQL基本操作-索引

   索引是一种特殊的数据库结构，用来快速查询数据库表中的特定记录，是提高数据库性能的重要方式。所有数据类型都可被索引。它创建在表上，对表中的一列或多列的值进行排序的一种结构。InnoDB和MyISAM支持B型树(BTREE)索引,MEMORY存储引擎支持HASH索引和BTREE索引。

  优点是提高查询速度，缺点是创建和维护索引需要耗费时间。在插入大量记录时为了降低插入速度的影响(插入的同时数据库系统会按照索引进行排序)应该先删除表中的索引，然后插入数据，插入完成后再创建索引。

1.创建表的时候创建索引

  create table 表名(属性名  数据类型 [完整性约束条件],

　　　　　　　　    属性名  数据类型 [完整性约束条件]

　　　　　　　　　[UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY 

　　　　　　　　　　[别名] (属性名1 [(长度)]) [ASC|DESC]

　　　　　　　　　　);

　　(1).创建普通索引

         create table index1(id int,

　　　　　　　　　　　　　name varchar(20),

　　　　　　　　　　　　　index(id));

       explain select * from index1 where id=1 \G;//若possible_keys和key值都为id说明id索引已经存在并作用

      (2).创建唯一性索引

          create table index2(id int unique,name varchar(20),unique index index2_id(id ASC));

　　(3).创建全文索引，只能在char,varchar,text类型的字段。

          create table index3(id int,info varchar(20),fulltext index index3_info(info));

      (4).创建单列索引

          create table index4(id int,subject varchar(30),index index4_sub(subject(10)));

　　　对应字符型数据，可以不查询全部信息，以提高查询速度。

     (5).创建多列索引

          create table index6(id int,name varchar(30),sex char(4),index index_ns(name,sex));

　　　  多列索引只有查询条件中使用了这些字段的第一个字段时，索引才会被使用。在优化查询速度时，可以考虑优化多列索引。

          explain select * from index6 where name='ms'\G;//possible_keys和key值都为index_ns

　　    explain select * from index6 where sex='n' \G; //值为null

  2.在已存在的表上创建索引

     create [unique|fulltext|spatial] index 索引名  on 表名 (属性名 [(长度)] [ASC|DESC]);

     (1).创建普通索引

         create index index7_id on index6(id);

     (2).创建唯一性索引

        create unique index index8_id on index7(course_id);

     (3).创建全文索引

         create fulltext index index9_id on index8(info);

     (4).创建单列索引

         create index index10_addr on index10(address(4));

     (5).创建多列索引

         create index index11_na on index11(name,address));

 3. 用alter table创建索引

    alter table 表名  add [unique|fulltext|spaital] index 索引名 (属性名 [(长度)] [ASC|DESC)];

    eg: alter table index12 add unique index index12_id(course_id);

 4.删除索引

    drop index 索引名 on 表名;