#修改:ALTERTABLE book ADDINDEX BkNameIdx ( bookname(30));#在book表中的year_publication字段上建立普通索引,SQL语句如下:CREATETABLE book
(
bookid INTNOTNULL,
bookname VARCHAR(255)NOTNULL,
authors VARCHAR(255)NOTNULL,
info VARCHAR(255)NULL,commentVARCHAR(255)NULL,
year_publication YEARNOTNULL,INDEX(year_publication));#表结构:SHOWCREATEtable book \G
***************************1.row***************************Table: book
CREATETable: CREATETABLE`book`(`bookid`int(11)NOTNULL,`bookname`varchar(255)NOTNULL,`authors`varchar(255)NOTNULL,`info`varchar(255)DEFAULTNULL,`comment`varchar(255)DEFAULTNULL,`year_publication`year(4)NOTNULL,KEY`year_publication`(`year_publication`))ENGINE=InnoDBDEFAULTCHARSET=utf8
#索引名称year_publication为MySQL自动添加。#使用EXPLAIN语句查看索引是否正在使用:explainselect*from book where year_publication=1990 \G
***************************1.row***************************
id: 1
select_type: SIMPLEtable: book
type: ref
possible_keys: year_publication
key: year_publication
key_len: 1
ref: const
rows: 1
Extra:
1rowinset(0.05 sec)
唯一索引
除主键索引外,允许有空值,但遵循唯一性
创建时关键字:UNIQUE INDEX
查看时关键字:UNIQUE KEY
#修改:ALTERTABLE book ADDUNIQUEINDEX UniqidIdx ( bookId );#创建一个表t1,在表中的id字段上使用UNIQUE关键字创建唯一索引。CREATETABLE t1
(
id INTNOTNULL,
name CHAR(30)NOTNULL,UNIQUEINDEX UniqIdx(id));#表结构:SHOWCREATEtable t1 \G
***************************1.row***************************Table: t1
CREATETable: CREATETABLE`t1`(`id`int(11)NOTNULL,`name`char(30)NOTNULL,UNIQUEKEY`UniqIdx`(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8
1rowinset(0.00 sec)#id字段上已经成功建立了一个名为UniqIdx的唯一索引。
单列索引
创建时关键字:INDEX SingleIdx(name(20))
查看时关键字:KEY SingleIdx (name(20))
#修改ALTERTABLE book ADDINDEX BkcmtIdx (comment(50));#创建一个表t2,在表中的name字段上创建单列索引:CREATETABLE t2
(
id INTNOTNULL,
name CHAR(50)NULL,INDEX SingleIdx(name(20)));#查看表结构:SHOWCREATEtable t2 \G
***************************1.row***************************Table: t2
CREATETable: CREATETABLE`t2`(`id`int(11)NOTNULL,`name`char(50)DEFAULTNULL,KEY`SingleIdx`(`name`(20)))ENGINE=InnoDBDEFAULTCHARSET=utf8
#建立了一个名为SingleIdx的单列索引,索引长度为20。
组合索引
创建时关键字:INDEX MultiIdx(id, name, age(100))
查看时关键字: KEY MultiIdx (id,name,age)
#修改ALTERTABLE book ADDINDEX BkAuAndInfoIdx ( authors(20),info(50));#创建表t3,在表中的id、name和age字段上建立组合索引,SQL语句如下:CREATETABLE t3
(
id INTNOTNULL,
name CHAR(30)NOTNULL,
age INTNOTNULL,
info VARCHAR(255),INDEX MultiIdx(id, name, age(100)));#查看表结构:SHOWCREATEtable t3 \G
***************************1.row***************************Table: t3
CREATETable: CREATETABLE`t3`(`id`int(11)NOTNULL,`name`char(30)NOTNULL,`age`int(11)NOTNULL,`info`varchar(255)DEFAULTNULL,KEY`MultiIdx`(`id`,`name`,`age`))ENGINE=InnoDBDEFAULTCHARSET=utf8
#建立了一个名为MultiIdx的组合索引。#“最左前缀”:利用索引中最左边的列集来匹配行,这样的列集称为最左前缀。#例如这里由id、name和age 3个字段构成的索引,索引行中按id/name/age的顺序存放,索引可以搜索下面字段组合:(id, name, age)、(id, name)或者id。#如果列不构成索引最左面的前缀,MySQL不能使用局部索引,如(age)或者(name,age)组合则不能使用索引查询。#在t3表中,查询id和name字段,使用EXPLAIN语句查看索引的使用情况:explainselect*from t3 where id=1AND name='joe' \G
***************************1.row***************************
id: 1
select_type: SIMPLEtable: t3
type: ref
possible_keys: MultiIdx
key: MultiIdx
key_len: 94
ref: const,const
rows: 1
Extra: Usingwhere1rowinset(0.00 sec)#查询id和name字段时,使用了名称MultiIdx的索引,如果查询(name,age)组合或者单独查询name和age字段,结果如下:***************************1.row***************************
id: 1
select_type: SIMPLEtable: t3
type: ALL
possible_keys: NULLkey: NULL
key_len: NULL
ref: NULLrows: 1
Extra: Usingwhere#possible_keys和key值为NULL,并没有使用在t3表中创建的索引进行查询。
全文索引
允许重复和空。
创建时关键字:FULLTEXT INDEX FullTxtIdx(info)
查看时关键字:KEY MultiIdx (id,name,age)
#修改ALTERTABLE t6 ADD FULLTEXT INDEX infoFTIdx ( info );#FULLTEXT全文索引可以用于全文搜索。只有MyISAM存储引擎支持FULLTEXT索引,并且只为CHAR、VARCHAR和TEXT列。索引总是对整个列进行,不支持局部(前缀)索引。
【例9.5】创建表t4,在表中的info字段上建立全文索引,SQL语句如下:
CREATETABLE t4
(
id INTNOTNULL,
name CHAR(30)NOTNULL,
age INTNOTNULL,
info VARCHAR(255),
FULLTEXT INDEX FullTxtIdx(info))ENGINE=MyISAM;#存储引擎为MyISAM,不然创建索引会出错。#查看表结构:SHOWCREATEtable t4 \G
***************************1.row***************************Table: t4
CREATETable: CREATETABLE`t4`(`id`int(11)NOTNULL,`name`char(30)NOTNULL,`age`int(11)NOTNULL,`info`varchar(255)DEFAULTNULL,
FULLTEXT KEY`FullTxtIdx`(`info`))ENGINE=MyISAM DEFAULTCHARSET=utf8
#FullTxtIdx的FULLTEXT索引。#全文索引非常适合于大型数据集.
空间索引
创建时关键字:( g GEOMETRY NOT NULL, SPATIAL INDEX spatIdx(g) )
查看时关键字:SPATIAL KEY spatIdx (g)
#修改ALTERTABLE t7 ADD SPATIAL INDEX spatIdx(g);#空间索引必须在MyISAM类型的表中创建,且空间类型的字段必须为非空。#创建表t5,在空间类型为GEOMETRY的字段上创建空间索引,SQL语句如下:CREATETABLE t5
( g GEOMETRYNOTNULL, SPATIAL INDEX spatIdx(g))ENGINE=MyISAM;#结构:CREATETABLE`t5`(`g`geometryNOTNULL,
SPATIAL KEY`spatIdx`(`g`))ENGINE=MyISAM DEFAULTCHARSET=utf8;