那么这么做的原因相信大家都应该知道：减少数据量的传输。

但我要讲的是另外一个原因：使用select *，就基本不可能使用到覆盖索引（什么是覆盖索引，后面会说）。

而将一个本该可以用覆盖索引的查询变成了不能使用覆盖索引的查询，就会导致随机I/O或回表查询（回表查询在介绍聚簇索引的时候会说）。

一、索引的类型

　　1、B-Tree索引

　　　　大部分的MySQL引擎都支持这种索引，它是使用B-TREE的数据结构来存储数据(INNODB使用的B+TREE)。B+TREE是B-TREE的一个变种，区别是B+TREE为所有叶子结点增加了一个存储指向下个叶子结点的链指针和所有关键字都在叶子结点中出现（关于B+TREE和B-TREE的详细介绍，可以去百度搜下，这里就不详细介绍了）。

　　　　B-TREE通常就意味着里面存储的所有值都是有序的，并且查询的时候，不用全表扫描，而是按照索引结构查找，所以会更快。

　　　　适用查询：

• 全值匹配。是指和索引中的所有列进行匹配。
• 匹配最左前缀。就是多列索引的最左前缀原则。例如一个多列索引为（A,B,C），当你的查询中包括A或A,B或A,B,C都可以用到索引，如果只有B，则无法用到该索引。
• 匹配列前缀。举个栗子就是，像like 'abc%'可以用到索引，而like '%abc%'就无法用到该类索引。
• 匹配范围值。其实就是范围查询，但记住，当多列索引中有一列用到范围查询时，那么该列后面的索引都没法被用到。例如还是又一个索引为（A,B,C），又一个查询为where A=1, B>1, C=1,那么这个查询只会用到（A,B,C）中的A,B列，C是不会被用到。
• 只访问索引的查询。其实就是覆盖索引查询。

　　　　当然如果查询满足以上条件，当然也就可以用这些列进行排序。

　　2、哈希索引

　　　　哈希索引是基于哈希表实现的。只支持精确索引查询。在MySQL中，目前只有Memory引擎支持哈希索引，但我们可以自定义哈希索引。具体思路是这样的：

　　　　在表中创建一列用来存储哈希值，然后还是用B-TREE索引进行查找。下面是一个实例：

　　　　例如一个表中需要存储大量的URL，如果正常使用B-TREE来存储URL，存储的内容就会很大，导致索引会很大。如果我们增加一列(url_hash)存储URL的哈希值，然后在这列上建立B-TREE索引，这样做的性能会高很多。因为当数据量非常大的时候，哈希会存在哈希冲突，所以在查询的时候要用到url和url_hash两列进行筛选。如：

select id from url where url='www.baidu.com' and url_hash='123213512987';

至于哈希算法，可以考虑自己实现一个简单的64位哈希函数。注意这个自定义函数一定要返回整数，而不是字符串。

　　3、全文索引

　　　　这是一种特殊类型的索引。后面会有一篇博客单独讨论全文索引，这里就不详细说了。

二、索引的优点

• 索引大大减少了服务器需要扫描的数据量
• 索引可以帮助服务器避免排序和临时表
• 索引可以将随机I/O变成顺序I/O

　　那么索引就一定是最好的解决方案吗？我们都知道维护索引需要做一些额外的工作，所以简单的说，当使用索引利大于弊时，索引就是有效的。

三、高性能的索引策略

　　1、独立的列

　　　　通常会看到一些不当的查询使得MySQL无法使用已有的索引。例如

select id from users where id+1=5;

这种查询是不会使用到索引的，而且这种查询完全可以写成

select id from users where id=4;

所以我们应该养成简化WHERE条件的习惯，始终将索引列单独放在比较符号的一侧。

　　2、前缀索引和索引选择性

　　　　有时候我们需要索引很长的字符串列，这时候我们就需要使用前缀索引，在MySQL中，对于TEXT，BLOB和很长的字符列，必须使用前缀索引，因为MySQL不允许索引这些列的所有长度。那么相应的，前缀索引必然会降低索引的选择性。索引的选择性是指，不重复的索引列与数据表的总记录数的比值。

　　　　那么怎么才能找到前缀索引和索引选择性间的一个平衡呢？套用《高性能MySQL》中的一个栗子：

　　一张表中的一个字段存储的各个城市的名字。首先，我们找到最常见的城市列表：

然后尝试下从3个前缀开始：

可以看出这个与原来的差距还是挺大的。经过实现后，我们发现，当前缀索引长度为7时，比较合适：

我们还可以利用另外一种算法计算下：计算选择性

这是完整列的选择性。然后我们看下当前缀索引分别为3，4，5，6，7时的选择性为多少：

这里可能有一个误区，会让我们感觉在索引前缀长度为4或5的时候，就已经足够了。那么我们在用之前的方法验证一下：

可以看到最常出现的前缀次数要比最常出现的城市次数大很多。即使它们的选择性比较低。找到前缀索引长度后，我们就可以创建前缀索引了。

mysql> ALTER TABLE city ADD KEY (city(7));

前缀索引是一种能使索引更小、更快的有效办法。它的缺点是：MySQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。

　　3、多列索引

　　看到这里，你可以打开自己的数据库表，然后看看结构，是不是为每个列建立的单独的索引，我们公司现在就是这么做的。

这是一个常见的错误。在MySQL5.0之前，下面的查询将无法用到索引，需要全表扫描：

select id from `shops_orders` where user_id=11 and shop_id=1;

在MySQL5.0之后，引入了‘索引合并’的概念。这种算法包括：OR条件的联合(union)，AND条件的相交(intersect)，组合前两种情况的联合和相交。

• 首先看下OR条件的联合(union)：

会看到在EXTRA列中， 有一个Using union()。而AND条件的相交(intersect)会有一个Using intersect()。

这种索引合并策略是一种优化结果。但也间接说明了你的表上的索引建的很糟糕：

• 当服务器对多个索引做相交操作时（通常是多个AND条件），通常意味着需要一个包含相关列的多列索引，而不是多个独立的单独索引。
• 当服务器对多个索引做联合操作时（通常是多个OR条件），通常需要消耗大量的CPU和内存资源在算法的缓存、排序和合并操作上。特别是其中有些索引选择性不高，需要合并扫描返回的大量数据。
• 更重要的是，这种索引合并策略不会被优化器计算到"查询成本"(cost)中去，优化器只关心随机页面的读取。

　　4、选择合适的索引列顺序

　　既然要建立多列索引，那么选择合适的顺序就相当重要了。对于如何选择合适的索引顺序有一个经验法则：将选择性最高的列放到索引最前列。

　　最开始，你可以按照这个法则建立多列索引，因为这可以在使用第一个索引列的时候就筛选出最少的数据量。随着经验的积累，你会有自己的索引列排序的经验。

　　5、聚簇索引

　　聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。聚簇索引总是把数据行存储在叶子页中，因此一个表中只能有一个聚簇索引。并不是所有的存储引擎都支持聚簇索引，这里我们主要讨论InnoDB，在InnoDB中，聚簇索引其实就是主键索引。如果表中没有定义主键，InnoDB会选择一个唯一的非空索引作为主键。如果没有这样的索引，InnoDB会隐式的定义一个主键来作为聚簇索引。聚簇索引的优点如下：

• 可以把相关数据保存在一起。
• 数据访问更快。
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

如果表在设计和查询的时候能充分利用以上特点，将会极大提高性能。当然，聚簇索引也有它的缺点：

• 聚簇索引最大限度提高了I/O密集型应用的性能，但如果所有的数据都存放在内存中，聚簇索引就没有优势了。
• 插入速度严重依赖插入顺序。这也是为什么InnoDB一般都会设置一个自增的int列作为主键。
• 更新聚簇索引的代价很高，因为会强制InnoDB将每个被更新的行移到新的位置。
• 如果不安顺序插入新数据时，可能会导致"页分裂"。
• 二级索引可能会比想象的更大。因为在二级索引的页子节点中包含了引用行的主键列。
• 二级索引访问可能会需要进行回表查询。

有人可能会有疑问。什么是回表查询呢？二级索引为什么要回表查询？答案在二级索引中保存的“行指针”的实质。因为二级索引在页子节点中保存的并不是指向行的物理位置的指针，而是行的主键值。那么如果此次查询不是覆盖查询，就会利用二级索引页子节点中保存的行主键值再去表里进行二次查询。这是才会得到我们真正想要的数据，这样就是导致使用两次B