MySql_添加索引

9.1 索引简介

      索引是一个单独的存在磁盘上的数据结构,他们包含着对数据表列的所有记录的引用的指针,使用索引可以快速的查找出某个或多个列中有特定值的行

      索引是在存储引擎上实现的,每种存储引擎的索引不一定完全相同,包括索引的类型及长度

      MySql:MyISAM和InnoDB支持BTREE索引,MEMORY和HEAP支持HASH和BTREE索引

索引优点:

      1通过创建唯一索引可以包装表中的每一行数据的唯一性

      2可以大大提高查询效率

      3在实现数据的参考的完整性方面,可以加速表与表之间的连接

      4使用分组和排序子句进行查询时可以查询中分组和排序的时间

索引缺点:

      1创建和维护索引需要耗费时间,并且数据两越大,耗费时间越长

      2索引需要占用磁盘空间,如果有大量的索引,可能索引文件比数据文件先达到最大文件尺寸

      3当对表中的数据进行增删改时,索引需要动态维护,降低了数据的维护速度

索引分类:

      1普通索引和唯一索引

      普通索引:允许有空值,重复

      唯一索引:索引列的值(组合索引组合的值)唯一,可空值,但主键索引不允许有空值

      2单列索引和组合索引

      3全文索引

      FULLTEXT,在定义索引的列上支持全文查找,可空值可重复,可用于char varchartext类型的列,mysql中      只有MyiSAM存储引擎支持全文索引

      4空间索引

      创建空间索引的列必须指明not null, mysql中   只有MyiSAM存储引擎支持空间索引

索引设计原则:

      1并非索引越多越好,因为增删改时需要动态维护,且占磁盘空间

      2避免多经常更新的列添加索引

      3数据量小时不要创建索引

      4条件表达式中经常用到的不同值较多的列上创建索引

      5当唯一性是某种数据本身特征时创建唯一索引

      6在频繁进行排序和分组的列上创建索引

9.2 创建索引

9.2.1创建表的时候创建索引

基本语法格式:

create table table_name [col_name data_type]

[unique | fulltext | spatial] [index | key][index_name] (col_name[length]) [asc | desc]

1 创建普通索引

CREATE     TABLEbook

(

book_id     INT NOT NULL,

book_name VARCHAR(255) NOT NULL,

year_publication YEAR NOT NULL,

INDEX(year_publication)

);

查看索引:

SHOW INDEX FROM book

2 创建唯一索引

CREATE     TABLEbook2

(

book_id INT NOT NULL,

book_name VARCHAR(255) NOT NULL,

year_publication YEAR NOT NULL,

UNIQUE INDEX uniqindextext(book_id)

);

3 创建单列索引

CREATE     TABLEbook3

(

book_id INT NOT NULL,

book_name VARCHAR(255) NOT NULL,

year_publication YEAR NOT NULL,

INDEX singleIdx(book_name(20))

);

4 创建组合索引

CREATE     TABLEbook4

(

book_id INT NOT NULL,

book_name VARCHAR(255) NOT NULL,

year_publication YEAR NOT NULL,

INDEXmultiIdxTest(book_id,book_name(20),year_publication)

);

5 创建全文索引

只可以为char varchar text列创建全文索引,且要修改存储引擎,mysql引擎可以修改为MyISAM

CREATE     TABLEbook5

(

book_id INT NOT NULL,

book_name VARCHAR(255) NOT NULL,

year_publication YEAR NOT NULL,

FULLTEXT INDEX fullTextIndex(book_name)

)ENGINE=MYISAM;

6 创建空间索引

只可以为非空列创建空间索引,且要修改存储引擎,mysql引擎可以修改为MyISAM

注意:A SPATIAL index may only contain a geometrical type column

CREATE     TABLEbook6

(

book_id INT NOT NULL,

book_name GEOMETRYNOT NULL,

year_publication YEAR NOT NULL,

SPATIAL INDEX spatialIndexTest(book_name)

)ENGINE=MYISAM;

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

1使用alter table语句创建

基本语法:

alter table table_name add [unique |fulltext | spatial] [index | key] [index_name] (col_name[length,..]) [asc |desc]

1.1添加普通索引

#创建表:

CREATE     TABLEbook7

(

book_id INT NOT NULL,

book_name VARCHAR(200) NOT NULL,

year_publication YEAR NOT NULL

);

#然后执行下面创建索引语句:

ALTER TABLE book7 ADD INDEXbknameInd(book_name(30));

1.2添加唯一索引

#创建表:

CREATE     TABLEbook8

(

book_id INT NOT NULL,

book_name VARCHAR(200) NOT NULL,

year_publication YEAR NOT NULL

);

#然后执行下面创建语句:

ALTER TABLE book8 ADD UNIQUE INDEXbknameInd(book_name(30));

1.3添加组合索引

#创建表:

CREATE     TABLEbook9

(

book_id INT NOT NULL,

book_name VARCHAR(200) NOT NULL,

year_publication YEAR NOT NULL

);

#然后执行下面创建语句:

ALTER TABLE book9 ADD INDEXbknameAndPubInd(book_name(30),year_publication);

1.4添加全文索引

#创建表:

CREATE     TABLEbook10

(

book_id INT NOT NULL,

book_name VARCHAR(200) NOT NULL,

year_publication YEAR NOT NULL

)ENGINE=MYISAM;

#然后执行下面创建语句:

ALTER TABLE book10 ADD FULLTEXT INDEXnameFTIdx(book_name);

1.5添加空间索引

#创建表:

CREATE     TABLEbook11

(

book_id INT NOT NULL,

book_name GEOMETRY NOT NULL,

year_publication YEAR NOT NULL

)ENGINE=MYISAM;

#然后执行下面创建语句:

ALTER TABLE book11 ADD SPATIAL INDEXnameSPIdx(book_name);

2使用create index 创建索引

基本语法结构:

create [unique | fulltext | spatial] indexindex_name on table_name (col_name[length…]) [asc | desc]

2.1添加普通,单列,组合,唯一索引

#创建表:

CREATE     TABLEbook13

(

book_id INT NOT NULL,

book_name VARCHAR(200) NOT NULL,

book_authors VARCHAR(200) NOT NULL,

book_info VARCHAR(200) NOT NULL,

book_coment VARCHAR(200) NOT NULL,

year_publication YEAR NOT NULL

);

#添加普通索引

CREATE INDEX bkName ON book13(book_name);

#添加唯一索引

CREATE UNIQUE INDEX uniqueInd ONbook13(book_id);

#添加多列索引

CREATE INDEX infoAndComIndex ONbook13(book_info(20),book_coment(50));

2.2添加全文索引

#创建表:

CREATE     TABLEbook14

(

book_id INT NOT NULL,

book_name GEOMETRY NOT NULL,

book_authors VARCHAR(200) NOT NULL,

book_info VARCHAR(200) NOT NULL,

book_coment VARCHAR(200) NOT NULL,

year_publication YEAR NOT NULL

)ENGINE=MYISAM;

#添加全文索引

CREATE FULLTEXT INDEX fullTextIndex ONbook14(book_info);

#添加空间索引

CREATE     SPATIALINDEX spatIndex ON book14(book_name);

9.3 删除索引

1使用altertable语句删除索引

格式:

alter table table_name drop indexindex_name;

2 使用dropindex语句删除索引

drop index index_name on table_name;