索引的分类
1.普通索引(INDEX)
这是最基本的索引类型,它没有唯一性之类的限制。创建普通索引的关键字是INDEX。
2.唯一性索引(UNIQUE)
这种索引和普通索引基本相同。唯一区别就是索引列队所有值都只能出现一次,即必须是唯一的。创建唯一性索引的关键字是UNIQUE。
3.主键(PRIMARY KEY)
主键是一种唯一性索引,不同之处在于每个表只能有一个主键索引,它必须指定为“PRIMARY KEY”。一般在创建表的时候指定主键,也可以通过修改表的方式加入主键。
4.全文索引(FULLTEXT)
MySQL支持全文检索和全文索引。全文索引的索引类型为 FULLTEXT,只能在varchar或text类型的列上创建,查询数据量较大的字符串类型的字段时,使用全文索引可以提高查询速度,但只能在MyISAM表中创建全文索引。
创建索引
一、使用 CREATE INDEX 语句
使用CREATE INDEX语句可以在一个已有表上创建索引,一个表可以创建多个索引。语法格式如下:
CREATE [UNIQUE | FULLTEXT] INDEX 索引名
ON 表名 (列名 [(长度)] [ASC | DESC]...)
语法说明:
索引名:索引的名称,索引在一个表中的名称必须是唯一的。
列名:表示创建索引的列名。长度表示使用列的前面多少个字符创建索引。
例子:
1.1 根据book表,“书名”列上的前6个字符建立一个升序索引name_book。
CREATE INDEX name_book
ON book(书名(6) ASC);
一个索引的定义中可以包含多个列,中间用逗号隔开,但是它们要属于同一个表。这样的索引叫做复合索引。
1.2 在sell表的“用户表”列和“图书编号”列上建立一个复合索引user_bh_sell。
CREATE INDEX user_bh_sell
ON sell(用户号,图书编号);
二、使用 ALTER TABLE 语句
对于已有的表,如果需要添加索引,也可以使用ALTER TABLE 语句向表中添加索引。
语法格式如下。
ALTER TABLE 表名
ADD INDEX [索引名] (列名,...) /*添加普通索引*/
| ADD PRIMARY KEY [索引方式] (列名,...) /*添加主键*/
| ADD UNIQUE [索引名] (列名,…) /*添加唯一性索引*/
| ADD FULLTEXT [索引名] (列名,…) /*添加全文索引*/
索引方式:语法格式为USING {BTREE | HASH},指定索引类型
语法说明:
索引方式:语法格式为 USING {BTREE | HASH},指定索引类型。
例子:
1.3在book表的“书名”列上创建一个普通索引。
ALTER TABLE book
ADD INDEX (书名);
使用ALTER TABLR语句可以同时添加多个索引。
1.4假设book表中主键未设定,为book表创建以“图书编号”为主键的索引,“出版社”“出版时间”为复合索引,以加快表的检索速度。
ALTER TABLE book
ADD PRIMARY KEY (图书编号),
ADD INDEX (出版社,出版时间);
这个例子中,既包括PRIMARYKEY,也包括复合索引,说明MySQL可以同时创建多个索引。需要注意,使用PRIMARY KEY的列,必须是一个具有NOT NULL 属性的列。
如果想要查看表中创建的索引的情况,可以使用SHOW INDEX FROM表名语句,示例如下。
SHOW INDEX FROM book;
三、在创建表时创建索引
在前面两种情况下,索引都是在表创建之后创建的。索引也可以在创建表时一起创建,在创建表的CREATE TABLE语句中可以包含索引的定义。
语法格式如下。
CREATE TABLE表名(列名,… | [索引项])
其中,索引项语法格式如下:
PRIMARY KEY (列名,…) /*主键索引*/
| {INDEX|KEY} [索引名] (列名,…) /*普通索引*/
| UNIQUE [INDEX] | [索引名] (列名,…) /*唯一性索引*/
| [FULLTEXT] [INDEX] [索引名] (列名,…) /*全文索引*/
KEY通常是INDEX的同义词。
例子:
1.5创建sell_copy表,设置“用户号”“图书编号”为联合主键,并在“订购册数”列上创建索引。
CREATE TABLE sell_copy (
用户号 char(18) NOT NULL,
图书编号 char(20) NOT NULL,
订购册数 int(5),
订购时间 datetime,
PRIMARY KEY(用户号,图书编号),
INDEX(订购册数)
);
四、删除索引
1.使用 DROP INDEX语句删除索引
语法格式如下:
DROP INDEX 索引名 ON 表名
这个语句的语法非常简单,索引名为要删除的索引的名称,表名为索引所在的表的名称。
例子:
1.6删除book表上索引名为“书名”的索引。
DROP INDEX 书名 ON book;
2.使用 ALTER TABLE 语句删除索引
语法格式如下:
ALTER [IGNORE] TABLE 表名
| DROP PRIMARY KEY /*删除主键*/
| DROP INDEX 索引名 /*删除索引*/
其中,使用DROP INDEX子句可以删除各种类型的索引。使用DROP PRIMARY KEY子句时不需要提供索引名称,因为一个表中只有一个主键。
例子:
1.7删除book 表上的主键和name_book索弓引。
ALTER TABLE book
DROP PRIMARY KEY,
DROP INDEX name_book;
如果从表中删除了列,则索引可能会受到影响。如果所删除的列为索引的组成部分,则该列也会从索引中删除。如果组成索子的所都被删除,则整个索引将被删除。