索引的一些知识

mysql中：

首先要明白索引（index）是在存储引擎（storage engine）层面实现的，而不是server层面。不是所有的存储引擎都支持所有的索引类型。即使多个存储引擎支持某一索引类型，它们的实现和行为也可能有所差别。

MySQL里的索引类型主要有以下几种。

1. B-Tree索引

最常见的索引类型，基于B-Tree数据结构。B-Tree的基本思想是，所有值（被索引的列）都是排过序的，每个叶节点到跟节点距离相等。所以B-Tree适合用来查找某一范围内的数据，而且可以直接支持数据排序（ORDER BY）。但是当索引多列时，列的顺序特别重要，需要格外注意。InnoDB和MyISAM都支持B-Tree索引。InnoDB用的是一个变种B+Tree，而MyISAM为了节省空间对索引进行了压缩，从而牺牲了性能。

2. Hash索引

基于hash表。所以这种索引只支持精确查找，不支持范围查找，不支持排序。这意味着范围查找或ORDER BY都要依赖server层的额外工作。目前只有Memory引擎支持显式的hash索引（但是它的hash是nonunique的，冲突太多时也会影响查找性能）。Memory引擎默认的索引类型即是Hash索引，虽然它也支持B-Tree索引。

例子：

CREATE TABLE testhash (
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    KEY USING HASH(fname)
) ENGINE =MEMORY;

3. Spatial (R-Tree)（空间）索引

只有MyISAM引擎支持，并且支持的不好。可以忽略。

4. Full-text索引

主要用来查找文本中的关键字，而不是直接与索引中的值相比较。Full-text索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的WHERE语句的参数匹配。你可以对某列分别进行full-text索引和B-Tree索引，两者互不冲突。Full-text索引配合MATCH AGAINST操作使用，而不是一般的WHERE语句加LIKE。

以上内容基本取自High Performance MySQL一书，对MySQL性能调优感兴趣的话，此书值得一读再读。

索引用来快速地寻找那些具有特定值的记录，所有的MySQL索引都以B-树的形式保存。如果没有索引，执行查询的时候mysql必须从第一个记录开始扫描整个表中的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录既可迅速得到目标记录所有在位置。如果表有1000个记录，通过索引查找记录至少比顺序扫描记录快100倍。

索引的类型

MySQL提供多种索引类型供选择：

普通索引 
这是最基本的索引类型，而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建： 
创建索引，例如:

create index <indexname> on tablename (列名)
 
 
1
 
 
1

修改表

alter table tablename add index [indexname] (列的列表)
 
 
1
 
 
1

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

create table tablename([...], index [indexname] (列的列表))
 
 
1
 
 
1

唯一性索引 
这种索引和前面的“普通索引”基本相同，但又一个区别：索引列的所有值都只能出现一次，即必须唯一。唯一性索引可以用以下几种方式创建： 
创建索引，例如：

create unique index <indexname> on tablename (columnname)
 
 
1
 
 
1

修改表：

alter table tablename add unique [indexname] (columnname)
 
 
1
 
 
1

创建表的时候指定索引

create table tablename([...], unique [indexname] (columnname))
 
 
1
 
 
1

主键主键是一种唯一性索引，但它必须指定为“primary key”。如果你曾经用过auto_increment类型的列，你可能已经熟悉主键之类的概念了。主键一般在创建表的时候，指定，例如

create table tablename ([...], primary key(columnname))
 
 
1
 
 
1

但是，我们也可以通过修表的方式加入主键，例如：

alter table tablename add primary key (columnname)
 
 
1
 
 
1

每个表只能有一个主键 
全文索引 
MySQL从3.23.23开始支持全文索引和全文检索。在MySQL中，全文索引的索引类型为FULLTEXT。全文索引可以在varchar或者text类型的列上创建。它可以通过create table命令创建，也可以通过alter table 或者create index命令创建。对于大规模的数据集，通过alter table（或者create index)命令创建全文索引要比把记录插入带有全文索引的空表更快。本文下面的讨论不再设计全文索引，要了解更多的信息，请参见MySQL documentation。

单列索引与多列索引

索引可以是单列索引，也可以是多列索引。下面我们通过具体的例子来说明这两种索引的区别。假设有这样的一个people表：

create table people (peopleid smallnt not null auto_increment, firstname char(50) not null, lastname char(50) not null, age smallint not null, townid smallint not null, primary key (peopleid));
 
 
1
 
 
1

简单来说，索引就好比一本书的目录，你只要浏览标题就可以快速的找到具体内容是放在哪一页。也就好似说select查找的时候不需要直接取搜索表，只需要查找索引，就可以直接定位到你想要查找的内容位置。索引带来的方便不是免费的，是以每次插入或者更新（相当于删除并插入）时要维护索引为代价的。索引如果一张表更多的是用户查询而很少插入那么就可以建立尽量多的索引以优化查询性能。相反如果一张表要经常插入后者更新，则尽可能少用索引，有的时候，甚至连主键都不建。

下半部分转载自：

http://blog.csdn.net/sinat_21793691/article/details/60776135