mysql数据库索引

@MYSQL索引

索引的应用场景

索引的应用

为一张表添加索引可以支持其查询时夹带索引字段达到快速查询除想要结果的作用，添加合适的索引可以提升十几倍的查询速度。在添加索引前要对表内数据有预估，是否到达需要添加索引的量级，之后还会讲到当建立索引不合理时也会导致适得其反的效果。

索引的类型

索引的类型分为三种：
1.普通索引：
最基本的索引，它没有任何限制，用于加速查询。
创建方法：
ａ.　建表的时候一起创建
CREATE TABLE mytable (
name VARCHAR(32) ,
INDEX index_mytable_name (name)
);
b. 建表后，直接创建索引
CREATE INDEX index_mytable_name ON mytable(name);
c. 修改表结构
ALTER TABLE mytable ADD INDEX index_mytable_name (name);
注：如果是字符串字段，还可以指定索引的长度，在列命令后面加上索引长度 就可以了（例如:name(11)）
2.唯一索引（字段是唯一的）
索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
创建方法：
ａ.　建表的时候一起创建
CREATE TABLE mytable (
name VARCHAR(32) ,
UNIQUE index_unique_mytable_name (name)
);
b. 建表后，直接创建索引
CREATE UNIQUE INDEX index_mytable_name ON mytable(name);
c. 修改表结构
ALTER TABLE mytable ADD UNIQUE INDEX index_mytable_name (name);
注：如果是字符串字段，还可以指定索引的长度，在列命令后面加上索引长度就可以了（例如:name(11)）
3.主键索引（字段是唯一的，且不为空）
是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。
创建方法：
ａ.　建表的时候一起创建
CREATE TABLE mytable (
id int(11) NOT NULL AUTO_INCREMENT ,
name VARCHAR(32) ,
PRIMARY KEY (id)
);
b. 修改表结构
ALTER TABLE test.t1 ADD CONSTRAINT t1_pk PRIMARY KEY (id);
注：如果是字符串字段，还可以指定索引的长度，在列命令后面加上索引长度就可以了（例如:name(11)）
4.组合索引
指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。
创建方法：
ａ.　建表的时候一起创建
CREATE TABLE mytable (
id int(11) ,
name VARCHAR(32) ,
INDEX index_mytable_id_name (id,name)
);
b. 建表后，直接创建索引
CREATE INDEX index_mytable_id_name ON mytable(id,name);
c. 修改表结构
ALTER TABLE mytable ADD INDEX index_mytable_id_name　(id,name);
5、全文索引
主要用来查找文本中的关键字，而不是直接与索引中的值相比较。
fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。
fulltext索引配合match against操作使用，而不是一般的where语句加like。
它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。
创建方法：
ａ. 建表的时候一起创建
CREATE TABLE article (
id int(11) NOT NULL AUTO_INCREMENT ,
title char(250) NOT NULL ,
contents text NULL ,
create_at int(10) NULL DEFAULT NULL ,
PRIMARY KEY (id),
FULLTEXT (contents)
);
b. 建表后，直接创建索引
CREATE FULLTEXT INDEX index_article_contents ON article(contents);
c. 修改表结构
ALTER TABLE article ADD FULLTEXT INDEX index_article_contents　(contents);

索引的数据类型

索引的数据类型可以这么说，99%的表使用的都是B+TREE作为索引，1%的表使用的是hash索引。
B+TREE是一种经过二叉树、红黑数、BTREE层层优化迭代后的数据类型，主要结构如下图所示：

使用B+TREE可以有效的减少磁盘的IO，上述每一层都是一次磁盘IO，第一层最多可容纳16个存储地址，并且所有的数据或者数据地址都存储在最底层中，并且最底层各组之间有指向下组子集的指针，可以进行范围查询。

那么上述中的存储数据还是数据地址就要再提到一个点，存储引擎。
存储引擎是在创建表时可以选择的，分为聚集索引（innoDB），和非聚集索引(myisam), 理解这个很简单，innoDB就是把索引和其所在行的数据放在一起，myisam就是索引和数据行其他数据分开存储，innoDB就是根据索引的B+TREE建的表，是必须有索引的，如果你不添加，会自动生成一列作为索引。
myisam的最底层的子级中存储的是地址指针，指向存储数据的仓库。innoDB子集中存储的就是数据，所以innoDB比myisam省了一步用指针去找数据的过程。

hash索引不多说了，就是根据将字段进行hash运行作为地址指针，不能进行范围查询，至于存在不存在hash碰撞这个倒是没研究过。

索引的使用的注意事项

推荐建立索引：
1.字段有唯一性，建！
2.字段经常作为where的查询条件，建！
3.需要经常order by 或者group by，建！
4.需要DISTINCT的字段，建！
不建议建立索引：
1.不经常作为where或者order by，group by的字段，不建！
2.表太小，不配拥有索引，不建！
3.所选字段存在大量的重复，建立索引反而减慢查询速度，不建！
4.频繁更新的字段不适合作为索引，需要一直更新索引的结构，不建！