MySQL之索引

        数据库中的索引与书的目录相似，表中的数据类似于书的内容。书的目录有助于读者快速地找到书中相关的内容，数据库的索引有助于加快数据检索速度。目前大部分MySQL索引都是以B-树（BTREE）方式存储的。BTREE方式构建了包含多个节点的一棵树。顶部的节点构成了索引的开始点，叫做根。每个节点中含有索引列的几个值，节点中的每个值又都指向另一个节点或者指向表中的一行。这样，表中的每一行都会在索引中有一个对应值。查询的时候就可以根据索引值直接找到所在的行。

        索引中的节点存储在文件中，因此索引也要占用物理空间，MySQL将一个表的索引都保存在同一个索引文件中。若更新表中的一个值或者向表中添加或删除一行，MySQL会自动的更新索引，因此索引树总是和表的内容保持一致。

        MySQL主要索引类型：

1、普通索引（INDEX）最基本的索引类型，其没有唯一性之类的限制。创建普通索引的关键字是INDEX。

2、唯一性索引（UNIQUE）该索引和普通索引基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须是唯一的。创建唯一性索引的关键字是UNIQUE

3、主键（PRIMARY KEY）主键是一种唯一性索引，其必须指定为PRIMARY KEY。主键一般在创建表的时候指定，也可以通过修改表的方式加入主键，但每个表只能有一个主键。

4、全文索引（FULLTEXT）MySQL支持全文检索和全文索引。全文索引的索引类型为FULLTEXT。全文索引只能在VARCHAR或TEXT类型的列上创建，并且只能在MyISAM表中创建。

1、索引的创建

1.1、使用CREATE INDEX语句创建索引

使用CREATE INDEX语句可以在一个已有表上创建索引，一个表可以创建多个索引。

语法格式：CREATE [ UNIQUE | FULLTEXT ] INDEX 索引名

ON 表名 （列名 [ （长度）] [ ASC | DESC ],...） 

注：UNIQUE表示创建的是唯一性索引，FULLTEXT表示创建全文索引。CREATE INDEX语句并不能创建主键。

 例：根据Book表的书名列上的前6个字符建立一个升序索引name_book

CREATE INDEX name_book
ON Book(书名（6）,ASC);

 可以在一个索引的定义中包含多个列，中间用逗号隔开，但是他们要属于同一个表，这样的索引叫做复合索引。

例：在Sell表的身份证号列和图书编号列上建立一个复合索引sfz_bh_sell

CREATE INDEX sfz_bh_sell
ON Sell(身份证号,图书编号);

1.2、使用ALTER TABLE语句创建索引

使用ALTER TABLE语句修改表结构，其中也包括向表中添加索引

语法格式：ALTER TABLE 表名

ADD INDEX [ 索引名 ]（列名,...）/*添加索引*/

| ADD PRIMARY KEY（列名,...）/*添加主键*/

| ADD UNIQUE [ 索引名 ]（列名,...）/*添加唯一性索引*/

| ADD FULLTEXT [ 索引名 ]（列名,...）/*添加全文索引*/ 

 例：在Book表的书名列上创建一个普通索引

ALTER TABLE Book
ADD INDEX sm_book(书名);

使用ALTER TABLE语句可以同时添加多个索引

 例：假设Book表中主键未设定，为Book表创建以图书编号为主键索引，出版社和出版时间为复合索引，以加速表的检索速度。

ALTER TABLE Book
ADD PRIMARY KEY(图书编号),
ADD INDEX mark(出版社,出版时间);

 查看表中创建的索引的情况可以用SHOW INDEX FROM tbl_name语句

例：查看Book表中所有索引的情况

SHOW INDEX FROM Book;

 1.3、在创建表时创建索引

在前面两种情况下，索引都是在表创建后创建的。索引也可以在创建表时一起创建。在创建表的CREATE TABLE语句中可以包含索引的定义。

语法格式：CREATE TABLE 表名（列名,...| [ 索引项 ]）

索引项语法格式：

PRIMARY KEY(列名,...)/*主键*/

| {INDEX | KEY}[ 索引名 ]（列名,...）/*索引*/

| UNIQUE [ INDEX ] [ 索引名 ]（列名,...）/*唯一性索引*/

| [ FULLTEXT ] [ INDEX ] [ 索引名 ]（列名,...）/*全文索引*/

 KEY通常是INDEX的同义词。在定义列选项的时候，也可以将某列定义为PRIMARY KEY，但是当主键是由多个列组成的多列索引时，定义列时无法定义此主键，必须在语句最后加上一个PRIMARY KEY（col_name,...）子句。

例： 创建sell_copy表，sell_copy表带有身份证号和图书编号的联合主键，并在订购册数列上创建索引

CREATE TABLE sell_copy(
身份证号 char(18) NOT NULL,
图书编号 char(20) NOT NULL,
订购册数 int(5),
订购时间 datetime,
PRIMARY KEY(身份证号,图书编号),
INDEX dgcs(订购册数)
);

 2、索引的删除

2.1、使用DROP INDEX语句删除索引

语法格式：DROP INDEX 索引名 ON 表名

例：删除Book表上的sm_book索引 

DROP INDEX sm_book ON Book;

 2.2、使用ALTER TABLE语句删除索引

语法格式：ALTER [ IGNORE ] TABLE 表名

| DROP PRIMARY KEY/*删除主键*/

| DROP INDEX 索引名/*删除索引*/

其中，DROP INDEX子句可以删除各种类型的索引。使用DROP PRIMARY KEY子句时不需要提供索引名称，因为一个表中只有一个主键

例：删除Book表上的主键和mark索引 

ALTER TABLE Book
DROP PRIMARY KEY,
DROP INDEX mark;

 若从表中删除了列，则索引可能会受到影响。若所删除的列为索引的组成部分，则该列也会从索引中删除。若组成索引的所有列都被删除，则整个索引将被删除。