索引（也叫做“键（key）”）是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对性能的影响越发重要。其外索引优化也很重要，索引优化应该是对查询性能优化最有效的手段了，索引能轻易将查询性能提高几个数量级。

索引基础

要理解MySQL中索引时如何工作的，最简单的方法就是去看看一本书的“索引”部分：如果想在一本书中找到某个特定主题，一般会先看书的“索引”，找到对应的页码。

在MySQL中，存储引擎用类似的方法使用索引，其先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。也就是说，MySQL先在索引上按值进行查找，然后返回所有包含该值的数据行。

索引可以包含一个或多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为MySQL只能高效的使用索引的最左前缀列。

索引的类型

在MySQL中，索引是在存储引擎层而不是服务层实现的。索引不同存储引擎的索引的工作方式不一样，也不是所有的存储引擎都支持所有类型的索引。下面是MySQL支持的索引类型，以及它们的优点和缺点。

B-Tree索引

如果没有特别说明索引类型，默认是B-Tree索引，它使用B-Tree数据结构来存储数据，大多数MySQL引擎都支持这种索引（Archive除外）。不过底层的存储引擎也可能使用不同的数据结构，例如InnoDB则使用的是B+Tree。

存储引擎以不用的方式使用B-Tree索引，性能也各有不同，各有优势。例如：MyISAM使用前缀压缩技术使得索引更小，但InnoDB则按照原数据格式进行存储。再如MyISAM索引通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行。

B-Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。下图展示B-Tree索引的抽象表示：

B-Tree索引能加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点中值的上限和下限。最终存储引擎要么是找到对应的值，要么该记录不存在。叶子节点比较特别，它们的指针指向的是被索引的数据，而不是其他的节点页。

B-Tree对索引列是顺序组织存储的，所以很适合查找范围数据。例如，在一个基于文本域的索引树上，按字母顺序传递连续的值进行查找是非常合适的，所以像“找出所有以I到K开头的名字”这样的查找效率会非常高。

建立如下表：

CREATE TABLE people(
	last_name varchar(50) not null,
	first_name varchar(50) not null,
	dob date not null,
	gender enum('m','f') not null,
	key(last_name,first_name,dob)
);

索引对多个值进行排序的依据是CREATE TABLE语句中定义索引时列的顺序。例如上图中，最后两个条目中，两个人的姓和名都一样，则根据他们的出生日期来排序。

可以使用B-Tree索引的查询类型。B-Tree索引适用于全键值、键值范围或前缀查询。其中键前缀查找只适用于根据最左前缀的查找。故所述的索引对如下类型的查询有效：

全值匹配：全值匹配指的是和索引中的所有列进行匹配。例如上图中，索引可用于查找姓名为Cuba Allen、同时出生于1960-01-01的人。
匹配最左前缀：索引可用于查询所有姓为Allen的人，即只使用索引的第一列。
匹配列前缀：也可以只匹配某一列的值的开头部分。例如可以查找所有以J开头的性的人，这里也只使用了索引的第一列。
匹配范围值：例如索引可用于查找姓在Allen和Barrymore之间的人，这里也只使用了索引的第一列。
精确匹配某一列并范围匹配另外一列：例如可以查找所有姓为Allen，并且名字是字母K开头的人。即第一列全匹配，第二列范围匹配。
只访问索引的查询：B-Tree通常可以支持“只访问索引的查询”，即查询只需要访问索引，而无须访问数据行。

因为索引树中的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的ORDER BY操作。一般来说，如果B-Tree可以按照这种方式用于排序。

但是B-Tree索引也有一些限制：

如果不是按照索引的最左列开始查找，则无法使用索引。即最左列不能跳过，例如上面例子中的索引无法用于查找名字为Bill的人，也无法查找某个特定生日的人，因为这两列都不是最左数据列。
不能跳过索引中的列。也就是说不能查找姓名为Smith并且某个特定日期出生的人。如果不指定名，则MySQL只能使用索引的第一列。
如果查询中有某个列的范围查找，则其右边所有列都无法使用索引优化查找。

总而言之这些限制都和索引列的顺序有关。在优化性能的时候，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。

哈希索引

哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎哦都会对所有的索引列计算一个哈希码，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

假设有如下表：

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

表中包含数据：
假设索引使用假想的哈希函数f()，它返回下面的值（以下为实例数据）：

f('Arjen') = 2323
f('Baron') = 7437
f('Peter') = 8784
f('Vadim') = 2458

则哈希索引的数据结构如下：
注意，每个槽的编号是顺序的，但是数据行不是。

因为索引自身只需存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找到速度非常快。但是哈希索引也有它的限制：

哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不过访问内存中的行的速度很快，所以大部分情况下这一点对性能的影响并不明显。
哈希索引值并不是按照索引值顺序存储的，所以也就无法用于排序。
哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内存容来计算哈希值的。
哈希索引只支持等值比较查询，包括=、IN()、<=>，也不支持任何范围查询。
访问哈希索引的数据非常快，除非有很多哈希冲突。当出现哈希冲突的时候，存储引擎会遍历链表中所有的行指针，逐行进行比较查找。
如果哈希冲突很多的话，一些索引维护操作的代价也会很高，冲突越多，代价越高。

因为这些限制，哈希索引只适用于某些特定的场合，而一旦适合哈希索引，则它带来的性能提升将非常明显。

InnoDB引擎有一个特殊的功能叫做“自适应哈希索引”。当InnoDB注意到某些索引值被使用的非常频繁时，它会在内存中基于B-Tree所以之上再创建一个哈希索引。这是一个完全自动的、内部的行为，用户无法控制或者配置，但是可以关闭。

自定义哈希索引时，记住不要使用SHA1()和MD5()作为哈希函数，因为这两个函数计算出来的哈希值时非常长的字符出阿奴，会浪费大量空间，比较时也会更慢。使用简单哈希函数导致的冲突在一个可以接受的范围，同时又能提供更好的性能。自定义哈希函数要返回整数，而不是字符串，一个简单的方法可以使用MD5()函数返回值的一部分来作为哈希函数。

当使用哈希索引进行查找的时候，要避免冲突问题，必须在WHERE条件中带入哈希值和对应列值。例如SELECT * FROM words WHERE crc = CRC32('gnu') AND word = 'gnu';。如果只是统计记录数（不精确的），则可以不带入列值。

其他索引

空间数据索引（R-Tree）：MyISAM表支持空间索引，可以用作地理数据存储。不同于B-Tree索引，这类索引无须前缀查询。空间索引会从所有维度来索引数据。查询时，可以有效地使用任意维度来组合查询。
全文索引：全文索引是一种特殊类型的索引，它查找的时文本中的关键字，而不是直接比较索引中的值。全文索引更类似于搜索引擎做的事情。在相同的列上同时创建全文索引和基于值的B-Tree索引不会有冲突，全文索引适用于MATCH AGAINST操作，而不是普通的WHERE条件操作。

索引的优点

索引可以让服务器快速地定位到表的指定位置，但这不是索引的唯一作用，因为根据创建索引的数据结构不同，索引也有一些其他的附加作用。

最常见的B-Tree索引，按照顺序存储数据，所以MySQL可以用来做ORDER BY和GROUP BY操作；因为数据是有序的，所以B-Tree也就会将相关的列值都存储在一起；因为索引中存储了实际的列值，所以某些查询只使用索引就能完成全部查询。所以总结下来索引有如下三个优点：

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

有一个简单评价一个索引是否适合某个查询的方法叫做“三星系统”：索引将相关的记录放在一起则获得一星；如果索引中的数据顺序和查找中的排列顺序一致则获得二星；如果索引的列包含了查询中需要的全部列则获得三星。

高性能的索引策略

独立的列

如果查询时不当的使用索引，或使得MySQL无法使用已有的索引。如果查询中的列不是独立的，则MySQL就不会使用索引。“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。
例如：SELECT actor_id FROM sakila.actor WHERE actor_id+1 = 5;和SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(data_col) <= 10;

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

前缀索引和索引选择性

通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但是这样也会降低索引的选择性。索引的选择性是指：不重复的索引值（也称为基数）和数据表的记录总数（#T）的比值，范围从 1/#T 到 1 之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行，唯一索引的选择性是1，即选择性最好，性能也最好。

对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。

诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长，以便节约空间。

为了决定前缀的合适长度，需要找到最常见的值的列表，然后和最常见的前缀列表进行比较，适当增加前缀长度，直到这个前缀的选择性接近完整列的选择性。另外一个方法就是计算完整列的选择性，并使前缀的选择性接近于完整列的选择性，计算方法如下：SELECT COUNT(DISTINCT city)/COUNT(*) FROM city_demo。通常来说，如果前缀的选择性能够接近0.031，基本上就可用了。

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

多列索引

很多人对多列索引的理解一个常见的错误就是：为每个列创建独立的索引，或者按照错误的顺序创建多列索引。在多个列上建立独立的单独索引大部分情况下并不能提高MySQL的查询性能。

MySQL5.0和最新版本引入了一种叫“索引合并”的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。这种算法有三个变种：OR条件的联合（union），AND条件的相交，组合前两种情况的联合及相交。

索引合并策略有时候是一种优化的结果，但实际上更多时候说明了表上的索引建的很差。如果使用EXPLAIN语句中看到有索引合并，应该好好检查一下查询和表的结构，有无优化的余地。

选择合适的索引列顺序

正确的索引顺序依赖于使用该索引的查询，并且同时需要考虑如何更好地满足排序和分组的需要。

在一个多列的B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。所以索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY、GROUP BY和DISTINCT等子句的查询需求。所以多列索引的列顺序至关重要。

对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放在索引最前列。但不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化WHERE条件的查找。

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式，具体的细节依赖于其实现方式，InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引的数据行。**当表有聚簇索引时，它的数据行实际上存放在索引的叶子页中。“聚簇”表示数据行和相邻的键值紧凑的存储在一起。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。**

下图展示聚簇索引中的记录是如何存放的：注意到，叶子页中包含了行的全部数据，但是节点页只包含了索引列。InnoDB通过主键聚集数据，也就是说上图中的“被索引的列”就是主键列。如果没有定义主键，InnoDB会选择一个唯一非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。下面讨论一些聚簇索引的优缺点。

优点：

可以把相关的数据保存在一起。

数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找更快。

使用覆盖索引扫描的查询可以直接使用页节点的主键值。

缺点：

聚簇索引最大限度的提高了I/O密集型应用的性能，但如果全部数据都放在内存中，那么访问的顺序就没有关系了，因此也就无法体现出什么优势了。

插入速度严重依赖于插入顺序。按照主键的顺序插入是数据加载到InnoDB表中最快速度的方式；但如果不是按照主键顺序加载数据，那么在加载完成后最好使用OPTIMIZE TABLE命令重新组织一下表。

更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。

基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂”的问题，页分裂会导致表占用更多的磁盘空间。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳改行，这就是一次页分裂操作。

聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。

二级索引（非聚簇索引）可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列。

二级索引访问需要两次索引查找，而不是一次。导致这个问题的原因在于二级索引中保存的“行指针”的实质。二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。这意味着通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获得对应的主键值，然后根据这个值去聚簇索引中查找到对应的行。对于InnoDB自适应哈希索引可以减少这样的重复工作。

在InnoDB和MyISAM中，对于聚簇索引和非聚簇索引的实现有区别，所以就以下表为例对比二者的区别：

CREATE TABLE layout_test(
	col1 int NOT NULL,
	col2 int NOT NULL,
	PRIMARY KEY(col1),
	KEY(col2)
);

假设该表的主键取值为1~~10000，并且数据在磁盘上的存储方式已经最优，但行的顺序是随机的；列col2的值是从1~~100之间随机复制，可能有很多重复值。

MyISAM的数据分布

MyISAM的数据分布非常简单，它按照数据插入的顺序存储在磁盘上，如图所示：在行的旁边显示了行号，从0开始递增。因为行是定长的，索引MyISAM可以从表的开头跳过所需的字节知道需要的行。这种分布方式很容易创建索引，如图：

MyISAM中主键索引和其他索引在结构上没什么不同，因此列col2的索引分布和col1一样。主键索引就是一个名为PRIMARY的唯一非空索引。

InnoDB的数据分布

可以看到该图显示了整张表，而不只有索引，因为在InnoDB中，聚簇索引就是表，所以不需要像MyISAM那样需要独立的行存储。聚簇索引的每一个叶子节点都包含了主键值、事务ID、用于事务和MVCC的回滚指针以及所有的剩余列。

还有一点与MyISAM不同的是，InnoDB的二级索引和聚簇索引很不相同。InnoDB二级索引的叶子节点中存储的不是“行指针”，而是主键值，并以此作为指向行的“指针”。这样的好处就是减少了当出现行移动或者数据页分裂时二级索引的维护工作，因此InnoDB在移动行时无需更新二级索引的这个“指针”。下图是InnoDB中的二级索引分布：

聚簇和非聚簇对比如图：

在InnoDB表中按主键顺序插入行

如果正在使用的InnoDB表没有什么数据需要聚集，那么可以定义一个与数据无关的代理键作为主键，最简单方法就是使用AUTO_INCREMENT自增列，这样可以保证数据行是按顺序写入，对于根据主键做关联的性能也会更好。最好避免随机的聚簇索引，下面对比以自增的整数ID作为主键和以随机字符串UUID作为主键的情况。可以注意到UUID主键插入不仅花费时间更长，而且索引占用的空间也更大。这一方面是由于主键字段更长，另一方面毫无疑问是因为页分裂和碎片导致的。
如图所示，因为主键的值是顺序的，所以InnoDB把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时（InnoDB默认的最大填充因子是页大小的15/19，留出部分空间用于以后修改），下一条记录就会写入新的页中。一旦数据按照这种顺序的方式加载，主键页就会近似于被顺序的记录填满，这也正是所期望的结果。

但是对比使用UUID聚簇索引的表插入数据，因为新行的主键值不一定比之前的插入大，所以InnoDB无法简单的总是把新行插入到索引的最后，而是需要为新的行寻找合适的位置————通常是已有数据的中间位置————并且分配空间。这会增加很多的额外工作，并导致数据分布不够优化，下面是总结的一些缺点：

写入到目标页可能已经刷到磁盘上并从缓存中移除，或者是还没有被加载到缓存中，InnoDB再插入之前不得不先找到并从磁盘读取目标页到内存中，这将导致大量的随机I/O。

因为写入是乱序的，InnoDB不得不频繁的做分页操作，以便为新的行分配空间。页分裂会导致移动大量数据，一次插入最少需要修改三个页面而不是一个页面。

由于频繁的页分裂，页会变得稀疏并被不规则地填充，所以最终数据会有碎片。

所以总结结论就是，使用InnoDB时应该尽可能的按主键顺序插入数据，并且尽可能地使用单调增加的聚簇键的值来插入新行。

索引覆盖

索引确实是一种查找数据的高效方式，但是MySQL也可以使用索引来直接获取列的数据，这样就不再需要读取数据行。如果一个索引包含（或者说覆盖）所有需要的字段的值，我们就称之为“索引覆盖”。索引覆盖是非常有效的工具，能够极大地提高性能，如果查询只需要扫描索引而无需回表，会带来多少好处：

索引条目通常远小于数据行的大小，所以如果只需要读取索引，那MySQL就会极大的减少数据访问量。这对缓存的负载非常重要，因为这种情况下响应时间大部分花费在数据拷贝上。
因为索引是按照列值的顺序存储的，所以对于I/O密集型的范围查找会比随机从磁盘读取每一行的I/O要少得多。对于MyISAM甚至可以通过OPTIMIZE命令使得索引完全顺序排列，这让简单的范围查询能完全顺序的索引访问。
一些存储引擎存储引擎入MyISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此访问数据需要一次系统调用，所以这里减少了因调用系统导致的开销。
由于InnoDB二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免主键索引的二次查询。

不是所有类型的索引都可为成为覆盖索引，覆盖索引必须存储索引列的值，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引。另外也不是所有的索引都支持覆盖索引。

当发起一个被索引覆盖的查询（也叫做索引覆盖查询）时，在EXPLAIN的Extra列可以看到“Using index”的信息。索引覆盖查询还有很多陷阱可能导致无法实现优化。MySQL查询优化器会在执行查询前判断是否有一个索引能进行覆盖。加入索引覆盖了WHERE条件中的字段，但是不是整个查询涉及的字段。如果条件为假，MySQL5.5及更早总是会回表获取数据行，尽管并不需要这一行且最终会被过滤掉。

如下例子：
这里索引无法覆盖该查询，有两个原因：

没有任何索引能够覆盖这个查询，因为查询从表中选择了所有的列，而每一任何索引覆盖了所有列。

MySQL不能在索引中执行LIKE操作。这是底层存储引擎API的限制，MySQL5.5及更早版本只允许在索引中做简单比较操作。MySQL能在索引中做最左前缀匹配的LIKE比较，因为该操作可以转换为简单的比较操作，但是如果时通配符开头的LIKE查询，存储器引擎就无法做比较匹配。

也有办法解决上面说的问题，需重写查询并巧妙的设计索引。先将索引拓展至覆盖三个数据列，然后按如下方式重写查询：

这种方式叫做“延迟关联”，因为延迟了对列的访问。在查询的第一阶段MySQL可以使用覆盖索引，在FROM子句的子查询中找到匹配的prod_id，然后根据这些prod_id值在外层查询匹配获取需要的所有列值。虽无法完全使用覆盖查询，但总比不使用覆盖查询好。

这样的优化效果取决于WHERE条件匹配返回的数据行。
在示例3中，因为索引过滤时符合第一个条件的结果集已经很小，所以子查询带来的成本反而比从表中直接提取完整行更高。

可以考虑更进一步优化InnoDB：InnoDB的二级索引的叶子节点都包含了主键的值，这意味着InnoDB的二级索引可以有效地利用这些“额外”的主键列来覆盖查询。

使用索引扫描扫描来做排序

MySQL有两种方式可以生成有序的结果：通过排序操作或者按索引顺序扫描。如果EXPLAIN出来的type列的值为“index”，则说明MySQL使用了索引扫描来做排序。扫描索引本身是很快的，但是如果索引不能覆盖所需的全部列，那就不得不每扫描一条索引记录都回表查询一次对应的行，这基本上都是随机I/O，因此按索引顺序读取数据的速度通常要比顺序地全表扫描。

MySQL可以使用同一个索引既满足排序又用于查找行，因此，如果可能，设计索引时应该尽可能地满足这两种任务。只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向都一样时，MySQL才能使用索引来对结果做排序。**如果查询时需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引来做排序，，ORDER BY子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求；否则MySQL都需要执行排序操作，而无法利用索引排序。有一种情况下ORDER BY子句可以不满足索引的最左前缀的要求：前导列为常量的时候。**如果WHERE子句或者JOIN子句种对这些列指定了常量，就可以弥补索引的不足。例如有如下表：

(retal_date,inventory_id,customer_id):
CREATE TABLE retal(
	...
	PRIMARY KEY (retal_id),
	UNIQUE KEY retal_date(retal_date,inventory_id,customer_id),
	KEY idx_fk_inverntory_id(inventory_id),
	KEY idx_fk_customer_id(customer_id),
	KEY idx_fk_staff_id(staff_id),
	...
);

MySQL可以在使用retal_date索引为下面的查询做排序，从EXPLAIN中可以看到没有出现文件排序（filesort）操作：

1
2
3

EXPLAIN SELECT retal_id,staff_id FROM sakila.rental
	WHERE retal_date = '2005-05-25'
	ORDER BY inventory_id,customer_id\G

即使ORDER BY子句不满足索引的最左前缀要求，也可以用于查询查询排序，这是因为索引的第一列被指定为一个常数。还有更多可以使用索引做排序的查询示例。下面这个查询可以利用查询排序，是因为查询为索引的第一列提供了常量条件，而使用第二列进行排序，将两列组合在一起，就形成了索引的最左前缀：

1	...WHERE retal_date = '2005-05-25' ORDER BY inventory_id DESC;

下面这个查询也可以，因为ORDER BY使用的两列就是索引的最左前缀：

1	...WHERE retal_date > '2005-05-25' ORDER BY retal_date,inventory_id;

下面是一些不能使用索引做排序的查询：

查询使用两种不同的排序方向，但是索引列都是正序排序的。
查询的ORDER BY子句中引用了一个不在索引中的列。
查询的WHERE 和ORDER BY中的列无法组合成索引的最左前缀。
查询在索引列的第一列上使范围条件，MySQL无法使用索引的其余列。
在某一列上有多个等于条件，对于排序来说，这也是一种范围查询。

使用索引做排序的一个最重要的用法是当查询同时有ORDER BY和LIMIT子句的时候。

压缩（前缀压缩）索引

MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中，这在某些情况下能极大的提高性能。默认只压缩字符串，也可以设置对整数压缩。MyISAM压缩每个索引块的方法是：先完全保存索引块中的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分，把这部分存储起来即可。例如索引块中的第一个值是“perform”，第二个值是“performance”，那么这个值的前缀压缩后存储的是类似“7.ance”这样的形式，同样MyISAM对行指针也采用类似的前缀压缩方式。

压缩块使用更少的空间，代价是某些操作可能更慢。因为每个值的压缩前缀都依赖前面的值，所以MyISAM查找时无法在索引块使用二分查找而只能从头开始扫描。正序的扫描速度还不错，但是倒序就不是很好了。对于CPU密集型应用，因为扫描需要随机查找，压缩索引使得MyISAM在索引查找上要慢好几倍；但如果是I/O密集型应用，对某些查询带来的好处会比成本多很多。

冗余和重复索引

MySQL允许在相同列上创建多个索引，MySQL需要单独与维护重复的索引，并且优化器在优化查询的时候也需要逐个地进行考虑，这会影响性能。

重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免这样创建重复索引。

冗余索引和重复索引有一些不同。如果创建了索引（A，B），在创建索引（A）就是冗余索引，因为这是前一个索引的前缀索引。因此索引（A，B）也可以当作索引（A）来使用。但是如果再创建索引（B，A）则不是冗余索引，因为B不是索引（A，B）的最左前缀列。

冗余索引通常发生在为表添加新索引的时候。例如，可能会增加一个新的索引（A,B）而不是扩展已有的索引（A），还有一种情况是将一个索引扩展为（A，ID），其中ID是主键，对于InnoDB来说主键列已经包含在二级索引中了，所以这也是冗余的。大多数情况下都不需要冗余索引，应该尽量扩展已有的索引而不是创建新索引。但也有时候出于性能方面考虑需要冗余索引，因为扩展已有的索引会太大导致影响性能。

有多个索引的缺点是索引成本更高，表中的索引越多插入速度会越慢，一般来说，增加新索引将导致INSERT、UPDATE、DELETE等操作速度变慢。解决冗余索引和重复索引的方法很简单，删除这些索引就可以。

未使用的索引

除了冗余索引和重复索引，可能还会有一些服务器永远不用的索引，这样的索引完全是累赘，建议考虑删除。

索引和锁

索引可以让查询锁定更少的行。InnoDB只有在访问行的时候才会对其进行加锁，而索引能够减少InnoDB访问的行数，从而减少锁的行数，但这只有当InnoDB在存储引擎层能够过滤掉索引不需要的行时才有效。

关于InnoDB、索引和锁有一些细节很少有人知道：InnoDB在二级索引上使用共享（读）锁，但访问主键索引需要排他（写）锁。这消除使用覆盖索引的可能性，并且使得SELECT FOR UPDATE比LOCK IN SHARE MODE或非锁定查询要慢的多。

总结

在MySQL中，大多数情况下都会使用B-Tree索引。其他类型的索引大多只适用于特殊的目的。如果在合适的场景中，将大大提高查询的响应时间。在选择索引和编写利用这些索引的查询时有如下三个原则始终需要记住：

单行访问是很慢的。特别是在机械硬盘存储中，如果服务器从存储中读取一个数据块只是为了获取其中一行，那么就浪费了很多工作。最好读取的块中能包含尽可能多所需要的行。使用索引可以创建位置引用以提升效率。
按顺序访问范围数据是很快的，这有两个原因：第一，I/O不需要多次磁盘寻道，所以比随机I/O要快很多；第二，如果服务器能够按需要顺序读取数据，那么就不再需要额外的排序操作，并且GROUP BY查询也无须再做排序和将行按组进行聚合计算了。
索引覆盖查询是很快的。如果一个索引包含了查询的所有列，那么存储引擎就不需要再回表查找行，这避免了大量的单行访问。