索引

概述

索引(键)是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其当表中的数据量越来越大时，索引对性能的影响愈发重要。索引优化是对查询性能优化最有效的手段，索引能够轻易地将查询性能提高几个数量级，创建最优的索引经常需要重写查询。

在MySQL中，根据索引查询时，存储引擎首先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。索引可以包含一个或多个列的值，如果索引包含多个列，那么列的顺序也十分重要，因为MySQL只能高效的使用索引的最左前缀列。

下面是一些基本的操作方式：

CREATE [UNIQUE] INDEX indexName ON tableName(columnName(length));  //如果是CHAR或VARCHAR类型，length可以小于字段实际长度，如果是BLOB和TEXT类型，必须指定length，加UNIQUE是唯一索引

ALTER TABLE tableName ADD INDEX/UNIQUE indexName(columnName);

/*创建表时直接指定*/
INDEX/UNIQUE [indexName] (columnName(length));

DROP INDEX [indexName] ON tableName;

索引的类型

索引有很多类型，MySQL中，索引是在存储引擎层而不是服务层实现的，所以没有统一的索引标准：不同存储引擎的索引的工作方式不一样，也不是所有的存储引擎都支持所有类型的索引，即使多个存储引擎都支持同一种类型的索引，其底层实现也可能不同。

B-Tree索引

关于B-Tree可以看看这篇：多路查找树(B树)
大多数MySQL使用B-Tree来存储数据，不过，底层的存储引擎可能使用不同的存储结构例如NDB使用T-Tree，InnoDB使用B+-Tree。

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

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

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

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

• 全值匹配：和索引中所有列进行匹配；
• 匹配最左前缀：只使用索引的第一列；
• 匹配列前缀：只匹配某一列的值的开头部分；
• 匹配范围值：查找范围内的数据；
• 精确匹配某一列并范围匹配另外一列：可以使用索引中的几项来查找；
• 只访问索引的查询：B-Tree通常可以支持只访问索引的查询，即查询只访问索引，无需访问数据行。

因为索引树中的结点是有序的，所以除了按值查找外，索引还可以用于查询中的ORDER BY操作(按顺序查找)。如果B-Tree可以按照某种方式查找到值，那么也可以按照这种方式用于排序。所以，如果ORDER BY子句满足前面的几种查询类型，那这个索引也满足对应的排序需求。

下面是一些关于B-Tree索引的限制：

• 如果不是按照索引的最左列开始查找，则无法使用索引；
• 不能跳过索引中的列；
• 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。

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

哈希索引

哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

哈希索引是Memory引擎默认索引类型，支持唯一哈希索引，如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。

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

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

因为这些限制，哈希索引只适用于某些特定的场合，而一旦适合哈希索引，它带来的性能提升将非常显著。如在数据仓库应用中有一种经典的星型schema，需要关联很多查找表，哈希索引就非常适合查找表的需求。

InnoDB有一个特殊的功能叫自适应哈希索引(adaptive hash index)，当InnoDB注意到某些索引值被使用的非常频繁时，他会在内存中基于B-Tree索引之上再创建一个哈希索引，这样就让B-Tree索引也具有哈希索引的一些优点，如快速哈希查找，也可以关闭该功能。

如果存储引擎不支持哈希索引，可以模拟像InnoDB一样创建哈希索引，思路就是在B-Tree基础上创建一个伪哈希索引，这样，还是使用B-Tree进行查找，但他使用哈希值而不是键本身进行索引查找，你需要做的就是在查询的WHERE子句中手动指定使用哈希函数。

为了防止哈希冲突影响查询结果，在查询时不仅要包含哈希索引，还要包含对应列值。

空间数据索引(R-Tree)

MyISAM表支持空间索引，可以用做地理数据存储。和B-Tree索引不同，这类索引无须前缀查询，空间索引会从所有维度来索引数据。查询时可以有效地使用任意维度来组合查询。必须使用MySQL的GIS相关函数如MBRCONTAINS()等来维护数据。MySQL的GIS支持并不完善，对GIS解决方案做的比较好的是PostgreSQL的PostGIS。

全文索引

全文索引是一种特殊类型的索引，他查找的是文本中的关键词，而不是直接比较索引中的值。全文搜索和其他几类索引的匹配方式完全不一样。他有许多需要注意的细节，如停用词、词干和复数、布尔搜索等。在相同的列上同时创建全文索引和基于值的B-Tree索引不会有冲突，全文索引适用于MATCH AGAINST操作，而不是普通的WHERE条件操作。

还有一些第三方存储引擎使用不同的数据结构来存储索引，如TokuDB使用分形树索引(fractal tree index)，这是一类较新开发的数据结构，既有B-Tree的很多优点，也避免了B-Tree的一些缺点。ScaleBD使用Patricia tries。

索引的优点

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

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

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

索引的三星系统：索引将相关记录放到一起则获得一星；索引中的数据顺序和查找中的排列顺序一致则获得二星；索引中的列包含了查询中需要的全部列则获得三星。

高性能的索引策略

独立的列

我们经常会看到一些查询不当的使用索引，或者使得MySQL无法使用已有的索引。如果查询中的列不是独立的，则MySQL就不会使用索引。独立的列是指索引列不能是表达式的一部分，也不能是函数的参数。例如，下面这个查询无法使用actor_id列的索引：

SELECT actor_id FROM actor WHERE actor_id+1=5

MySQL无法自动解析上面的方程，我们应该养成简化WHERE条件的习惯，始终将索引列单独放在比较符号的一侧。

前缀索引和索引选择性

有时候需要索引很长的字符列，这会让索引变得大且慢，一个策略是模拟哈希索引。此外，通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。索引的选择性是指，不重复的索引值(基数，cardinality)和数据表的记录总数(#T)的比值范围从1/T到1之间，索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

一般情况下某个列前缀的选择性也是足够高的，足以满足查询性能，对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长，前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。为了决定合适的前缀长度，需要找到常见的值的列表，然后和最常见的前缀列表进行比较。另一种方法是计算完整列的选择性，并使前缀的选择性接近于完整列的选择性。

创建前缀索引的方式(假设前缀长度为7)：ALTER TABLE city ADD KEY(city(7));

前缀索引也有一个缺点：MySQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。

多列索引

在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能，MySQL5.0和更新版本引入了一种叫“索引合并(index merge)”的策略，一定程度上可以使用表上的多个单列索引来定位指定的行，查询能够同时使用这两个单列索引进行扫描，并将结果合并。这种算法有三个变种：OR条件的联合(union)，AND条件的相交(intersection)，组合前两种情况的联合及相交。下面是使用了两个索引扫描的联合：EXPLAIN SELECT film_id，actor_id FROM film_actor WHERE actor_id= 1 OR film_id=1\G

MySQL会使用索引合并策略优化复杂查询。

索引合并策略有时候是一种优化的结果，但实际上更多时候说明了表上的索引建得很糟糕：

• 当出现服务器对多个索引做相交操作时(通常有多个AND条件)，通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引；
• 当服务器需要对多个索引做联合操作时(通常有多个OR条件)，通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候；
• 更重要的是，优化器不会把这些计算到查询成本中，优化器只关心随机页面读取，这会使得查询的成本被低估，导致该执行计划还不如直接走全表扫描。这样做不但会消耗更多的CPU和内存资源，还可能会影响查询的并发性，但如果是单独运行这样的查询则往往会忽略对并发行的影响。通常来说，不如将查询改写成UNION的方式。

如果在EXPLAIN中看到有索引合并，应该好好检查一些查询和表的结构，看是不是已经是最优的，也可以通过参数optimizer_switch来关闭索引合并功能，也可以使用IGNORE INDEX提示让优化器忽略掉某些索引。

选择合适的索引列顺序

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

当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的，这时候索引的作用只是用于优化WHERE条件的查找。在这种情况下，这样设计的索引确实能够最快的过滤出需要的行，对于在WHERE子句中只使用了索引部分前缀列的查询来说选择性也更高。然而，性能不只是依赖与所有索引列的选择性(整体基数)，也和查询条件的具体值有关，也就是和值的分布有关。可能需要根据那些运行频率最高的查询来调整索引列的顺序，让这种情况下索引的选择性最高。

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式，具体的细节依赖于其实现方式，但InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree和数据行。

当表有聚簇索引时，它的数据行实际上存放在索引的叶子页中，术语“聚簇”表示数据行和相邻的键值紧凑的存储在一起，因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

InnoDB通过主键聚集数据，如果没有主键，InnoDB会选择一个唯一的非空索引代替，如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚集索引。InnoDB只聚集在同一个页面中的记录，包含相邻键值的页面可能会相距甚远。

聚集的数据有一些优点：

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

同时，也有一些缺点：

• 聚簇数据最大限度的提高了I/O密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没那么重要了，聚簇索引也就没什么优势了；
• 插入速度严重依赖于插入顺序，按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。但如果不是按照主键顺序加载数据，那么在加载完成后最好使用OPTIMIZE TABLE命令重新组织一下表；
• 更新聚簇索引的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置；
• 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂(page split)的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页面分裂成两个页面来容纳改行，这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间；
• 聚簇素银可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候；
• 二级索引(非聚簇索引)可能比想象的要更大，因为在二级索引的叶子结点包含了引用行的主键列；
• 二级索引访问需要两次索引查找，而不是一次。

二级索引叶子结点保存的不是指向行的物理位置的指针，而是行的主键值，这意味着通过二级索引查找行，存储引擎需要找到二级索引的叶子结点获得对应的主键值，然后根据这个值去聚簇索引中查找到对应的行，这样就进行了两次B-Tree查找。

InnoDB和MyISAM的数据分布对比

聚簇索引和非聚簇索引的数据分布有区别，以及对应的主键索引和二级索引的数据分布也有区别。

MyISAM按照数据插入的顺序存储在磁盘上，在行的旁边加了行号，从0开始递增。因为行是定长的，所以MyISAM可以从表的开头跳过所需的字节找到需要的行，这种分布方式很容易创建索引。

InnoDB支持聚簇索引，所以使用非常不同的方式存储同样的数据。聚簇索引的每一个叶子结点都包含了主键值、事务ID、用于事务和MVCC的回滚指针以及所有的剩余列，如果主键是一个列前缀索引，InnoDB也会包含完整的主键列和剩下的其他列。InnoDB不需要独立的行存储。

还有一点和MyISAM的不同是，InnoDB的二级索引和聚簇索引很不相同。InnoDB二级索引的叶子结点中存储的不是行指针，而是主键值，并以此作为指向行的指针。这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。使用主键值当工作指针会让二级索引占用更多的空间，换来的好处是，InnoDB在移动行时无需更新二级索引中的这个指针。

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

如果正在使用InnoDB表并且没有什么数据需要聚集，那么可以定义一个代理键(surrogate key)作为主键，这种主键的数据应该和应用无关，最简单的方法是使用AUTO_INCREMENT自增列。这样可以保证数据行是按顺序写入，对于根据主键做关联操作的性能也会更好。最好避免随机的(不连续且值的分布范围非常大)聚簇索引，特别是对于I/O密集型的应用。

随机的聚簇索引有以下缺点：

• 写入的目标页可能已经刷到磁盘上并从缓存中移除，或者是还没有被加载到缓存中，InnoDB在插入之前不得不先找到并从磁盘读取目标页到内存中，这将导致大量的随机I/O；
• 因为写入是乱序的，InnoDB不得不频繁的做页分裂操作，以便为新的行分配空间。页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个页；
• 由于频繁的页分裂，页会变得稀疏并被不规则的填充，所以最终数据会有碎片。

把这些随机值载入到聚簇索引以后，也许需要做一次OPTIMIZE TABLE来重建表并优化页的填充。

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

覆盖索引

如果一个索引包含(或者说覆盖)所有需要查询的字段的值，我们就称之为覆盖索引。覆盖索引能极大地提高性能，有如下好处：

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

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

在做索引覆盖查询时，在EXPLAIN的Extra列可以看到Using index 的信息。

索引覆盖查询还有很多陷阱可能会导致无法实现优化，MySQL查询优化器会在执行查询前判断是否有一个索引能进行覆盖。假设索引覆盖了WHERE中的字段，但不是整个查询涉及的字段，如果条件为假，MySQL5.5和更早的版本也总是会回表获取数据行，尽管不需要这一行且最终会被过滤掉。

索引无法覆盖查询可能有以下原因：

• 索引不能覆盖所有列的查询；
• MySQL不能在索引中执行LIKE操作。

使用索引扫描来做排序

MySQL有两种方式可以生成有序的结果：通过排序操作或者按索引顺序扫描。如果EXPLAIN出来的type列的值为index，则说明MySQL使用了索引扫描来做排序。

扫描索引本身是和很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条记录就都回表查询一次对应的行，这基本上都是随机I/O，因此按索引顺序读取数据的速度通常要比顺序的全表扫描慢，尤其是在I/O密集型的工作负载时。MySQL可以使用同一个索引既满足排序，又用于查找行，因此，如果可能，设计索引时应该尽可能地同时满足这两种任务。

只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向都一样时，MySQL才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。ORDER BY子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求，否则，MySQL都需要执行排序操作，而无法利用这些索引。

有一种情况下ORDER BY子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候，如果WHERE子句或者JOIN子句中对这些列指定了常量，就可以弥补索引的不足。如这样一句：SELECT * FROM rental WHERE rental_data = ‘2005’ ORDER BY inventory_id DESC (索引排列为rental_data，inventory_id)因为常量可以和后面的列形成最左前缀。

下面的一些情况即使前导列为常量也不能用索引排序：

• 使用了两种不同的排序方向；
• 引用不在索引中的列；
• 列无法组成索引的最左前缀；
• 在索引的第一列上是范围条件；
• 优化器改变了表关联的顺序。

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

压缩(前缀压缩)索引

MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中，这在某些情况下能极大地提高性能。默认只压缩字符串，但通过参数设置也可以对整数做压缩。

MyISAM压缩每个索引块的方法是，先完全保存索引块的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分，把这部分存储起来即可，如索引块第一个值是perform，第二个是performance，那么第二个字的前缀压缩后存储的是类似“7，ance”这样的形式。MyISAM对行指针也采用类似的前缀压缩方式。

压缩块使用更少的空间，代价是某些操作可能更慢，因为每个值的压缩前缀都依赖前面的值，所以MyISAM查找时无法在索引块使用二分查找而只能从头开始扫描，正序扫描速度还不错，但倒序扫描就不是很好了。

对于CPU密集型应用，因为扫描需要随机查找，压缩索引使得MyISAM在索引查找上要慢好几倍，倒序扫描就更慢了。压缩索引需要在CPU内存资源与磁盘之间做权衡，压缩索引可能只需要十分之一大小的磁盘空间，如果是I/O密集型应用，对某些查询带来的好处会比成本多很多。

可在CREATE TABLE语句中指定PACK_KEYS参数来控制索引压缩的方式。

冗余和重复索引

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

重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引，应该避免这样创建重复索引，发现以后也应该立即移除。

索引类型不同，并不算是重复索引，通常有很好地理由创建KEY和FULLTEXT KEY两种索引。

冗余索引和重复索引有一些不同，如果创建了索引(A，B)，再创建索引(A)就是冗余索引，因为这只是前一个索引的前缀索引。因此索引(A，B)也可以当做索引(A)来使用(针对B-Tree索引)。但是如果再创建索引(B,A)，则不是冗余索引，索引(B)也不是。另外，其他不同类型的索引(例如哈希索引或全文索引)也不会是B-Tree索引的冗余索引，而不论覆盖的索引列是什么。

冗余索引通常发生在为表添加新索引的时候，扩展主键也是冗余的，因为主键已经包含在二级索引了。

大多数情况下都不需要冗余索引，应该尽量扩展已有的索引而不是创建新索引。但也有时候出于性能方面的考虑需要冗余索引，因为扩展已有的索引会导致其变得太大，从而影响其它使用该索引的查询的性能。

添加了冗余索引和重复索引可能使搜索更快，但缺点是成本更高，且会导致增、删、更新操作变慢。

解决冗余索引和重复索引的方法是删除这些索引，可以用Shlomi Noach的common_schema中的一些视图来定位。另外也可以使用Percona Toolkit中的pt-duplicate-key-checker，该工具通过分析表结构来找出冗余和重复的索引。

未使用的索引

除了冗余索引和重复索引，可能还会有一些服务器永远不用的索引，这样的索引完全是累赘，应该删除。有两个工具可以帮助定位未使用的索引：在Percona Server或MariaDB打开userstates服务器变量，查询INFORMATION_SCHEMA.INDEX_STATISTICS就能查到每个索引的使用频率，也可以使用Percona Toolkit的pt-index-usage读取查询日志，对日志中的每条查询进行EXPLAIN操作，打印关于索引和查询的报告。

索引和锁

索引可以让查询锁定更少的行，如果你的查询从不访问那些不需要的行，就会锁定更少的行，这对性能很有好处。首先，可以减少锁定行带来的额外开销，其次，锁定超过需要的行会增加锁争用并减少并发性。InnoDB只有在访问行的时候才会对其加锁，而索引能够减少InnoDB访问的行数，从而减少锁的数量。但这只有当InnoDB在存储引擎层能够过滤掉所有不需要的行时才有效。如果索引无法过滤掉无效的行，那么在InnoDB检索到数据并返回给服务层以后，MySQL服务器才能应用WHERE子句，这时已经无法避免锁定行了。5.1和更新的版本中，InnoDB可以在服务器端过滤掉行后就释放锁，但在早期版本，InnoDB只有在事务提交后才能释放锁。

InnoDB在二级索引上使用共享锁，但访问主键索引需要排它锁，消除了使用覆盖索引的可能性，并使得SELECT FOR UPDATE比LOCK IN SHARE MODE或非查询索引要慢很多。

维护索引和表

维护表有三个主要的目的：找到并修复损坏的表、维护准确的索引统计信息、减少碎片。

找到并修复损坏的表

表损坏是很糟糕的事情，对于MyISAM存储引擎，表损坏通常是系统崩溃导致的，其他的引擎也会由于硬件问题、MySQL本身的缺陷或者操作系统的问题导致索引损坏。损坏的索引会导致查询返回错误的结果或者莫须有的主键冲突等问题，严重时还会导致数据库的崩溃。如果遇到了古怪的问题，可以尝试运行CHECK TABLE来检查是否发生了表损坏。CHECK TABLE通常能够找出大多数的表和索引的错误。可以使用REPAIR TABLE命令来修复损坏的表，但不是所有的引擎都支持CHECK TABLE和REPAIR TABLE，可以通过一个不作任何操作(no-op)的ALTER操作来重建表，例如修改表的存储引擎为当前的引擎：ALTER TABLE innodb_tbl ENGINE=INNODB；也可以使用myisamchk等工具，或者将数据导出一份，然后再重新导入。如果损坏的是系统区域，或者是表的行数据区域，而不是索引，可以从备份中回复表，或者尝试从损坏的数据文件中尽可能的恢复数据。

InnoDB的表出现损坏，一定是发生了严重的错误，一般要么是数据库的硬件问题例如内存或者磁盘问题，要么是由于数据库管理员的错误例如在MySQL外部操作了数据文件，抑或是InnoDB本身的缺陷(不太可能)。常见的类似错误通常是由于尝试使用rsync备份InnoDB导致的，不存在什么查询能让InnoDB表损坏。

如果遇到数据损坏，最重要的是找出是什么导致了损坏，而不只是简单地修复，否则很有可能还会不断的损坏。可以通过设置innodb_force_recovery参数进入InnoDB的强制恢复模式来修复数据。另外，还可以使用InnoDB数据恢复工具箱(InnoDB Data Recovery Toolkit)直接从InnoDB数据文件恢复出数据。

更新索引统计信息

MySQL的查询优化器会通过两个API来了解存储引擎的所引致的分部信息，以决定如何使用索引。第一个API是record_in_range()，通过向存储引擎传入两个边界值获取在这个范围大概有多少条记录。第二个API是info()，该接口返回各种类型的数据，包括索引的基数(每个键值有多少条记录)。

如果存储引擎向优化器提供的扫描行数信息是不准确的数据，或者执行计划本身太复杂以致无法准确的获取各个阶段匹配的行数，那么优化器会使用索引统计信息来估算扫描行数。MySQL优化器使用的是基于成本的模型，而衡量成本的主要指标就是一个查询需要扫描多少行。如果没有统计信息或者统计信息不准确，优化器就可能做出错误的决定。可以通过运行ANALYZE TABLE来重新生成统计信息解决这个问题。

每种引擎实现索引统计信息方式不同：

• Memory引擎根本不存储索引统计信息；
• MyISAM将索引统计信息存储在磁盘中，ANALYZE TABLE需要进行一次全索引扫描来计算索引基数，在整个过程中需要锁表；
• 直到5.5版本，InnoDB也不在磁盘存储索引统计信息，而是通过随机的索引访问进行评估并将其存储在内存中。

可以用SHOW INDEX FROM tableName查看索引基数。

• InnoDB会在表首次打开，或者执行ANALYZE TABLE，抑或表的大小发生很大变化的时候计算索引的统计信息。
• InnoDB在打开某些INFORMATION_SCHEMA表，或者使用SHOW TABLE STATUS和SHOW INDEX，抑或在MySQL客户端开启自动补全功能的时候都会触发索引统计信息的更新，这可能给服务器带来很大压力，可通过关闭innodb_stats_on_metadata参数来避免上述问题。
• 如果想要更稳定的执行计划，并在系统重启后更快地生成这些统计信息，可以使用系统表来持久化这些索引统计信息。甚至还可以在不同的机器间迁移索引统计信息，这样新环境启动时就无需再收集这些数据。Percona版本可通过innodb_use_sys_stats_table参数启用该特性，5.6版本通过innodb_analyze_is_persistent参数控制。
• 一旦关闭索引统计信息的自动更新，就需要周期性的使用ANALYZE TABLE来手动更新，否则，统计信息永远不变。

减少索引和数据的碎片

B-Tree索引可能会碎片化，这会降低查询的效率，碎片化的索引可能会以很差或者无序的方式存储在磁盘上。根据设计，B-Tree需要随机磁盘访问才能定位到叶子页，所以随机访问是不可避免的。如果叶子页在物理分布上是顺序且紧密的，那么查询的性能就会更好，否则，对应范围查询、索引覆盖扫描等操作来说，速度可能会降低很多倍，对于索引覆盖扫描这一点更加明显。

表的数据存储也可能碎片化，然而，数据存储的碎片化比索引更加复杂，有三种类型的数据碎片：

• 行碎片(Row fragmentation)：数据行被存储为多个地方的多个片段中，即使查询只从索引中访问这一行记录，行碎片也会导致性能下降；
• 行间碎片(Intra-row fragmentation)：行间碎片是指逻辑上顺序的页，或者行在磁盘上不上顺序存储的。行间碎片对诸如全表扫描和聚簇索引扫描之类的操作有很大影响，因为这些操作原本能够从磁盘上顺序存储的数据中获益；
• 剩余空间碎片(Free space fragmentation)：数据页中有大量的空余空间，这会导致服务器读取大量不需要的数据，从而造成浪费。

对于MyISAM表，这三类碎片化都可能发生，但InnoDB不会出现短小的行碎片，InnoDB会移动短小的行并重写到一个片段中。

可通过OPTIMIZE TABLE或者导出再导入的方式来重新整理数据，对于MyISAM，可通过排序算法重建索引的方式来消除碎片。新版本InnoDB新增了“在线”添加和删除索引的功能，可以通过先删除，然后再重新创建索引的方式来消除索引的碎片化。

总结

在选择索引和编写利用这些索引的查询时，有如下三个原则始终需要记住：

• 单行访问是很慢的；
• 按顺序访问范围数据是很快的；
• 索引覆盖查询是很快的。

总的来说，编写查询语句时应该尽可能地使用数据原生顺序从而避免额外的排序操作，并尽可能使用索引覆盖查询。