MySQL的索引详解

一. 索引基础

1.1 简介

在Mysql中，索引（index）也叫做“键（key）”，它是存储引擎用于快速找到记录的一种数据结构。

索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对性能的影响就愈发重要。

索引优化应该是对查询性能优化最有效的手段，创建一个真正最优的索引经常需要重写sql查询语句。

1.2 索引的工作原理

要理解mysql中索引的工作原理，最简单的方法就是去看一看一本书的索引部分：比如你想在一本书中寻找某个主题，一般会先看书的索引目录，找到对应的章节、对应的页码后就可以快速找到你想看的内容。

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

1.3 索引的类型

在MySQL中，通常我们所指的索引类型，有以下几种：

• 常规索引

  常规索引，也叫普通索引（index或key），它可以常规地提高查询效率。一张数据表中可以有多个常规索引。常规索引是使用最普遍的索引类型，如果没有明确指明索引的类型，我们所说的索引都是指常规索引。

• 主键索引

  主键索引（Primary Key），也简称主键。它可以提高查询效率，并提供唯一性约束。一张表中只能有一个主键。被标志为自动增长的字段一定是主键，但主键不一定是自动增长。一般把主键定义在无意义的字段上（如：编号），主键的数据类型最好是数值。

• 唯一索引

  唯一索引（Unique Key），可以提高查询效率，并提供唯一性约束。一张表中可以有多个唯一索引。

• 全文索引

  全文索引（Full Text），可以提高全文搜索的查询效率，一般使用Sphinx替代。但Sphinx不支持中文检索，Coreseek是支持中文的全文检索引擎，也称作具有中文分词功能的Sphinx。实际项目中，我们用到的是Coreseek。

• 外键索引

  外键索引（Foreign Key），简称外键，它可以提高查询效率，外键会自动和对应的其他表的主键关联。外键的主要作用是保证记录的一致性和完整性。

  注意：只有InnoDB存储引擎的表才支持外键。外键字段如果没有指定索引名称，会自动生成。如果要删除父表（如分类表）中的记录，必须先删除子表（带外键的表，如文章表）中的相应记录，否则会出错。 创建表的时候，可以给字段设置外键，如 foreign key(cate_id) references cms_cate(id)，由于外键的效率并不是很好，因此并不推荐使用外键，但我们要使用外键的思想来保证数据的一致性和完整性。

1.4 索引的方法

在MySQL中，索引是在存储引擎层实现的，而不是在服务器层。MySQL支持的索引方法，也可以说成是索引的类型（这是广义层面上的），主要有以下几种：

B-Tree 索引

如果没有特别指明类型，那多半说的就是B-Tree 索引。不同的存储引擎以不同的方式使用B-Tree索引，性能也各不相同。例如：MyISAM使用前缀压缩技术使得索引更小，但InnoDB则按照原始的数据格式存储索引。再如MyISAM通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行。

B-Tree 对索引列是顺序存储的，因此很适合查找范围数据。它能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据。

如果一个索引中包括多个字段（列）的值，那它就是一个复合索引。复合索引对多个字段值进行排序的依据是创建索引时列的顺序。如下：

create table people (
 id int unsigned not null auto_increment primary key comment '主键id',
 last_name varchar(20) not null default '' comment '姓',
 first_name varchar(20) not null default '' comment '名',
 birthday date not null default '1970-01-01' comment '出生日期',
 gender tinyint unsigned not null default 3 comment '性别：1男，2女，3未知',
 key(last_name, first_name, birthday)
) engine=innodb default charset=utf8;