MySQL数据库之索引(2)

ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] index_name (column(length),…) [ASC|DESC]

  a. 创建普通索引

alter table book add index bkName(bookname(30)) ;

  b. 创建唯一索引

alter table book add unique index uniqueIndex(bookId) ;

  c. 创建单列索引

alter table book add index signalIndex2 on(comment(50)) ;

  d. 创建多列索引

alter table book add index mutilIndex (authors(30) , info(50)) ;

  e. 创建全文索引

alter table book add fulltext index fulltextIndex (info) ;

  (3)创建表的时候同时创建索引

  语法结构如下:

CREATE TABLE table_name ( ……(创建字段和约束), [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [INDEX|KEY] [index_name] (col_name [length]) [ASC|DESC] )

  a. 创建普通索引

create table book(

    bookid
int not null ,

    bookname
VARCHAR(255) not null ,

    authors
VARCHAR(255) not null ,

    info
VARCHAR(255) not null ,

    comment
VARCHAR(255) not null ,

    year_publication
YEAR not null ,

   
index pub_index(year_publication)

);

  b. 创建唯一索引

create table book( …… unique index pub_index(bookid) );

  c. 创建单列索引

create table book( …… index single_index(authors) );

  d. 多列索引

create table book( …… index mutil_index(bookid, authors) );

  e. 创建全文索引

create table book( …… fulltext index fulltext_index(info) );

  (注:MySQL5.7中默认存储引擎为InnoDB,在这里创建表时需要修改表的存储引擎为MyISAM,否则会出错)

5 删除索引

  (1)使用ALTER TABLE删除索引

  语法结构:

ALTER TABLE table_name DROP INDEX index_name ;

  (注:有AUTO_INCREMENT约束的字段的唯一索引不能被删除)

  示例:删除book表中名为fulltext_Index的索引

alter table book drop index fulltext_Index ;

  (2)使用DROP INDEX语句删除索引

  语法结构:

DROP INDEX index_name ON table_name ;

  示例:删除book表中名为fulltext_Index的索引

drop index mutil_index on book ;

6 索引设计原则

  索引设计不合理或者缺少索引都会对数据库性能造成不良影响。那么设计索引是该如何考虑呢?

  (1)索引并非越多越好,一个表中如果有大量的索引,不仅占用磁盘空间,而且会影响INSERT、DELETE、UPDATE等语句的性能,因为当表中的数据更改的同时,索引也会进行调整和更新。

  (2)数据量小的表最好不要使用索引,由于数据量较小,查询所花费的时间可能比表里索引的时间还要短,索引可能不会产生优化的效果。

  (3)避免对经常更新的表进行过多的索引,并且索引中的列尽可能少。对经常用于查询的字段应该创建索引,但要避免添加不必要的字段。

  (4)在条件表达式中经常用到的不同值较多的列上建立索引,在不同值很少的列(例如性别字段,只有男女两个取值)不要建立索引。

  (5)当唯一性是数据本身的特征时,指定唯一索引,可确保数据完整性并提高查询速度。

  (6)在频繁进行排序、分组的列上建立索引,如果排序的列有多个,可以在这些列上建立组合索引。

7 总结

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/bca0c68bde3e061ef7d26a051159a541.html