MySQL中的索引

一 索引基础

1、定义：

　　在MySQL中，索引（index）也叫做“键（key）”，他是存储引擎用于快速找到记录的一种数据结构，可以提高查询效率。

　　对查询性能优化的最有效手段就是索引优化。

2、工作原理：

　　在MySQL中，索引是在存储引擎层实现的，而不是在服务器层。

　　在MySQL中，存储引擎用类似的方法使用索引，其先在索引中查找对应的值，然后根据匹配的索引记录来找到对应的数据行，最后将数据结果集返回给客户端。

3、索引类型：

（1）常规索引：

　　也叫普通索引（index或key），一张数据表中可以有多个常规索引。

　　一般没有指明索引的类型，都是指常规索引。

（2）主键索引 - primary key

　　简称主键，提供唯一性约束。一张表中只能有一个主键。

　　被标志为自动增长的字段一定是主键，但是主键不一定是自动增长。

　　一般把主键定义在例如编号之类的字段上，其数据类型最好是数值。

（3）唯一索引 - unique key

　　提供唯一性约束。一张表中可以有多个唯一索引。

（4）全文索引 - Full Text

　　可以提高全文搜索的查询效率，一般使用Sphinx替代，但是Sphinx不支持中文检索。

　　Coreseek是支持中文的全文检索引擎，也称作具有中文分词功能的Sphinx。

　　实际项目中，用到的是Coreseek。

（5）外键索引 - Foreign key

　　简称外键，外键会自动和对应的其他表的主键关联。

　　外键的主要作用是保证记录的一致性和完整性，但是由于外键的效率不是很高，所以并不推荐使用外键。

　　注意：只有InnoDB存储引擎的表才支持外键。如果要删除父类中的记录，必须先删除子表中的额相应记录，否则会出错。

延伸：

　　B-Tree索引：

　　如果没有特别指明类型，那一般说的就是B-Tree索引。B-Tree对索引是顺序存储的，因此和适合查找范围数据。

　　因为存储引擎不在需要进行全表扫描来获取需要的数据，故能够加快访问数据的速度。

　　注意：不同的存储引擎以不同的方式使用B-Tree索引，性能也各不相同。

　　　　　例如：MyISAM使用前缀压缩技术使得索引更小，但InnoDB则按照原始的数据格式存储索引。

　　　　  再如：MyISAM通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行。

4、索引的方法

在创建表的时候直接创建索引：

　　基本的语法格式：

CREATE TABLE 表名( 属性名 数据类型[完整性约束条件], 
属性名 数据类型[完整性约束条件], 
...... 
属性名 数据类型 
[ UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY 
[ 别名] ( 属性名1 [(长度)] [ ASC | DESC] ) 
);

　　参数说明：　　

• UNIQUE：可选。表示索引为唯一性索引。
• FULLTEXT；可选。表示索引为全文索引。
• SPATIAL：可选。表示索引为空间索引。
• INDEX和KEY：用于指定字段为索引，两者选择其中之一就可以了，作用是一样的。
• 索引名：可选。给创建的索引取一个新名称。
• 字段名1：指定索引对应的字段的名称，该字段必须是前面定义好的字段。
• 长度：可选。指索引的长度，必须是字符串类型才可以使用。
• ASC：可选。表示升序排列。
• DESC：可选。表示降序排列。

例子：

（1）普通索引：

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
INDEX [indexName] (username(length))  
 
); 

CREATE INDEX indexName ON mytable(username(length)); 

注意：
如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

ALTER table tableName ADD INDEX indexName(columnName)

DROP INDEX [indexName] ON mytable; 

(2) 唯一索引：

CREATE UNIQUE INDEX indexName ON mytable(username(length));

ALTER table mytable ADD UNIQUE [indexName] (username(length))

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
UNIQUE [indexName] (username(length))  
 
);

使用ALTER 命令添加和删除索引

有四种方式来添加数据表的索引：

• ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
• ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
• ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

以下实例为在表中添加索引。

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

你还可以在 ALTER 命令中使用 DROP 子句来删除索引。尝试以下实例删除索引:

mysql> ALTER TABLE testalter_tbl DROP INDEX c;

---

使用 ALTER 命令添加和删除主键

主键只能作用于一个列上，添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。实例如下：

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

你也可以使用 ALTER 命令删除主键：

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

删除主键时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。

---

显示索引信息

你可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 G 来格式化输出信息。

尝试以下实例:

mysql> SHOW INDEX FROM table_name; G

资料来源：MySQL索引详情