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