索引

一.什么是索引

索引是对数据库表中一列或多列的值进行排序的一种结构。

索引是一个单独的，物理的数据结构，他是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

索引与驻留内存的数据结构（如二叉树，B树和散列表）有些类似，数据库索引区别于驻留内存的数据结构的地方在于，数据库索引包含的数据量比一次能调入内存的数据量大。因此，数据库索引的数据是放在磁盘上的，只有被访问的时候才会被部分的调入内存。当计算机关闭的时候，驻留内存的数据将丢失，但是数据库索引中的数据项表中的记录一样会永久存在。也可以说索引是与表或视图关联的磁盘上的结构，可以加快从表或视图中检索行的速度。索引包含由表或视图中的一列或多列生成的键，这些键存储在一个结构（一般为B树）中。

对于索引的每一项，MySQL在内部为它保存一个数据文件中实际记录所在位置的“指针”。例如，我们创建一个名为people的表：

CREATE TABLE people(peopleid SMALLINT NOT NULL,name CHAR(50) NOT NULL);

假设已经存入1000行数据。如果我们要查找name等于"Mike"记录的peopleid:

SELECT peopleid FROM people WHERE name='Mike';

MySQL能够在name的索引中查找'Mike'的值，然后直接转到数据文件中相应的行，准确的返回改行的peopleid(999)。在这个过程中，MySQL只需处理一个行就可以返回结果。

二.索引的语法

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name    [USING index_type]    ON tbl_name (index_col_name,...)

MySQL提供以下几种索引类型供选择：普通索引（CREATE INDEX），唯一性索引（CREATE UNIQUE INDEX），主键（PRIMARY KEY），全文索引（FULLTEXT）。其中普通索引，唯一性索引和主键也可以在创建或者修改表时指定。

index_type指定语句的语法是USING type_name。不同的储存引擎所支持的type_name值已显示在下表中。如果列有多个索引类型，当没有指定index_type时，第一个类型是默认值。

存储引擎

允许的索引类型

MyISAM

BTREE

InnoDB

BTREE

MEMORY/HEAP

HASH, BTREE

 

 

 

 

 

 

1.普通索引

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

如果是CHAR，VARCHAR类型，length长度可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定length，其他几类相同。也可以在修改表的时候创建：

ALTER mytable ADD INDEX [indexName] ON (username(length));

创建表的时候指定索引，如：

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

2.唯一性索引

和普通索引有一个区别：索引列的所有值都只能出现一次，即必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

直接创建唯一索引语法如下：

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

修改表时创建：

ALTER mytable ADD UNIQUE [indexName] ON (username(length));

创建表时直接指定，例如：

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

3.主键

主键是一种特殊的索引，不允许有空值。一般在建表的同时创建主键索引。例如：

CREATE TABLE mytable(ID INT NOT NULL,username VARCHAR(16) NOT NULL,PRIMARY KEY(ID));

也可以用ALTER命令，但是注意，一个表只能有一个主键。

三.索引的使用

建表：

CREATE TABLE mytable(   id INT,    username VARCHAR(16),city VARCHAR(16),age INT );

创建索引：

-- 创建索引CREATE INDEX indexName ON mytable(username(10));               -- 单列索引-- CREATE INDEX indexName ON mytable(username(10),city(10));   -- 组合索引-- indexName为索引名，mytable表名，username和city为列名，10为前缀长度，即索引在该列从最左字符开始存储的信息长度，单位字节-- 如果是CHAR，VARCHAR类型，前缀长度可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 前缀长度，下同。

-- 调用组合索引SELECT * FROM mytable WHERE username = 'admin' AND city = 'DaLian';