小小子

为了加快搜索速度，减少查询执行时间，MySQL允许我们为一个表的特定字段设置索引。一个索引就是字段值的一个列表，它用来简化查询中定位特定记录的任务。如果没有索引，MySQL就需要浏览表的每一行来寻找和上匹配的记录。这对于小表来讲，也许是无所谓的，可随着表数据的增加，完全地浏览整个表可能增加查询的时间。

索引可以很大程度上提高速度：利用索引，MySQL可以不用浏览所有的表，而是查看索引之后，跳到表的合适位置。当看到与指定搜索条件相匹配的记录时，读出这个索引要比浏览整个表快得多。因为索引通常比较小(除非您建立多个索引弄的索引文件比表还大)，可以加快搜索。

索引的类型：
MySQL索引一共支持三种类型：普通索引，唯一性索引，全文索引。

1. 普通索引
   这是最基本的索引类型，并且该索引类型不考虑唯一性之类的限制。其创建方式有如下几种
   • 创建表的时候同时创建索引：CREATE TABLE TABLE_NAME ([字段描述省略...], INDEX [INDEXNAME](INDEXCOLS);
   • 直接创建索引：CREATE INDEX[INDEXNAME] ON TABLE_NAME(INDEXCOLS);
   • 修改表的索引：ALTER TABLE TABLE_NAME ADD INDEX [INDEXNAME](INDEXCOLS);
2. 唯一性索引 UNIQUE 索引
   这种索引要求其索引的列必须保持唯一，(主键就是非常有代表性的唯一索引)。其索引列中所有值只能出现一次。其创建方式如下
   • 创建表的时候同时创建索引：CREATE TABLE TABLE_NAME ([字段描述省略...], UNIQUE [INDEXNAME](INDEXCOLS);
   • 直接创建索引：CREATE UNIQUE INDEX[INDEXNAME] ON TABLE_NAME(INDEXCOLS);
   • 修改表的索引：ALTER TABLE TABLE_NAME ADD UNIQUE [INDEXNAME](INDEXCOLS);
3. 全文索引 FULLTEXT 索引
   其是为了在MyISAM(稍候的日志为您介绍表的类型，仅可用在MyISAM表)表中进行全文检索而特意设定的，可以对纯文本字符串中已经经过索引的列进行查询，并且只返回那些包含与搜索字符串相类似的记录。当执行全文检索时，MySQL计算表记录和要搜索字符串之间的相似程度，然后只返回那些相似度较高的记录。其创建方式如下
   • 创建表的时候同时创建索引：CREATE TABLE TABLE_NAME ([字段描述省略...], FULLTEXT [INDEXNAME](INDEXCOLS);
   • 直接创建索引：CREATE FULLTEXT INDEX[INDEX_NAME] ON TABLE_NAME(INDEXCOLS);
   • 修改表的索引：ALTER TABLE TABLE_NAME ADD UNIQUE [INDEXNAME](INDEXCOLS);

INDEXNAME为索引的名字，可以省略 TABLE_NAME为要创建索引的表的名字 INDEXCOLS为创建索引的列的字段名字

单列索引与多列索引：

看我上面创建索引的方法时，其创建索引的列的字段名字我用的是复数INDEXCOLS，这说明创建索引的字段可以不是一个字段，可以是多个字段的组合。其遵循“左侧优先”的原则去利用索引的字段，所以索引又分为单列索引与多列索引。

那么到底是使用单列索引，还是多列索引呢？这不仅仅要看你的心情，事实上千万不要凭感情用事，要看你的业务需要。就像我前两天建了一个表，表中有手机号mobile字段，有ip字段，有status字段。因为要为手机号和ip设置一些规则，比如说同一ip号，一个小时内最多充值多少笔，一个手机号一天最多允许成功充值多少钱，同时一个月最多充值多少钱。我当时的做法是把ip_createtime设为索引，同时把mobile_status_createtime设为索引。

因为当我查询的时候我可以以mobile，并且status为成功的，并且在某个时间段的交易记录去查询。事实上当我执行mobile和status不带上createtime这个时间段去查询的时候，我也会用到该索引，因为多列索引遵循着“左侧优先”的原则。

可是实际上这个索引建立的并不好，因为我这个库不见得很大，同一手机号一天的成交记录最多可以有3笔，这样其实我只把mobile建立为索引就好，因为成功的记录不会很多，这也就是说明这个手机号在库里的数据不会很多，成功的记录一共一天3笔，一年才是千量级的数据，用不上非得建立索引。因为索引并不全是优点，相反的，他的缺点也相当明显，我们一会儿再谈。

一般建立索引的规则是根据在WHERE 子句，和JOIN 子句中出现的列来建立索引。

看一下你的查询是否有用到你建立的索引：

MySQL为我们提供了这样一个命令：EXPLAIN 。

这样检查一下可以知道你的索引建立的是否恰当，你的查询是否真的用到了你建立的索引。在其possile_keys选项，可以看出其是否有用到索引。具体请查看手册。

索引优缺点：

索引有两个非常严重的缺点：它占据了一定的磁盘空间，而且它影响INSERT，UPDATE和DELETE执行的时间，因为当表记录被添加、更新或者删除时，索引每次都必须更新。但是，大多数情况下，这些理由不能阻止我们使用索引：磁盘已经越来越便宜，咱又不差钱，而且MySQL包含了许多优化技术来缩减用于更新索引和搜索索引的时间。

找到一篇很好的介绍MySQL索引的文章：MySQL索引分析和优化，本文也有部分内容摘抄自此，特此声明。

声明: 本文采用 BY-NC-SA 协议进行授权 | 小小子
转载请注明转自《浅谈MySQL索引》