高性能索引的创建

索引是存储引擎用于快速找到记录的一种数据结构，这是索引的基本功能。

索引印花是对查询性能优化最有效的手段了。“最优”的索引有时比一个“好的”索引性能要好两个数量级。

索引基础

mysql中，存储引擎使用索引，先在索引中找到对应值，然后根据匹配的素银记录找到对应的数据行。索引可以包含一个或多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为Mysql只能高效地使用索引的最左前缀列。创建一个包含两个列的索引，和创建两个只包含一列的索引是大不相同的。

B-Tree索引

不同的存储引擎使用的存储结构不同，InnoDB使用的B+Tree。存储引擎以不同的方式使用B-Tree索引，性能也各有不同。例如：MyISAM使用前缀压缩技术使得索引更小，但InnoDB则按照原数据格式进行存储。

B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行权标扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页值得上限和下限。

叶子节点指针指向的是被索引的数据，而不是其他的节点页。树的深度和表达大小直接相关。

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

B-Tree索引适用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找。

B-Tree索引的限制：

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

所以索引列的书序是非常重要的。在优化性能的时候，需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。

索引的优点

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

如何评价一个索引是否适合某个查询

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

高性能的索引策略

独立的列

索引列不能是表达式的一部分，也不能是函数的参数

例如： select id from test where id + 1 = 5;是不能使用索引的

我们应该始终将索引单独放在比较符号的一侧。

前缀搜因和索引选择性

有时候需要索引很长的字符列，这回让索引变得大且慢。通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。这里有个概念，索引的选择性。索引的选择性是指不重复的索引值和数据表的记录总数的比值，范围从1/T到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，是性能最好的。

多列索引

常见的错误就是，为每个列创建独立的索引，或者按照错误的顺序创建多列索引。

我们先来看为每个列创建独立的索引。

create table t（c1 int,c2 int,c3 int,key(c1),key(c2),key(c3)）;

这样一来最好的情况下也只能是"一星"索引，其性能比起真正最优的索引可能差几个数量级。

Mysql在5.0之后引入一种"索引合并"（index merge）的策略，一定程度上可以使用表上的多个单列来定位指定的行。更早版本mysql之恩给你使用其中一个单列索引。

例如：select id1，id2 from t where id1 = 1 or id2 = 1；

在老的MySQL版本中，mysql对这个查询会使用全表扫描。除非改为携程如下两个查询union的方式：

select id1，id2 form t where id1 =1 union all

select id1, id2 from t where id2 = 1 and id1 != 1;

在mysql5.0之后，查询能够同时使用两个列的单列索引进行扫描，并将结果进行合并。

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

当出现服务器对多个索引做相交操作时（多个and条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
当服务器需要对多个索引做联合操作时（通常有多个or条件），通常需要耗费大量cpu和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。
更重要的是，优化器不会把这些计算到“查询成本”中，优化器只关心随机页面读取。这会使得查询的成本被低估，导致该执行计划还不如直接走全表扫描。

如果在explain看到索引合并，应该好好检查一下查询和表的结构，看是不是已经是最优的。可以通过参数optimizer_switch来关闭索引合并功能。

选择合适的索引列顺序

我们遇到最容易一起困惑的问题就是索引列的顺序。正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好的满足排序和分组的需要。

对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前列。这个建议有用吗？在某些场景可能有帮助，但通常不如避免随机IO和排序那么重要，考虑问题需要更全面。

例如：select * from payment where staff_id =2 and customer_id = 584;

是应该创建一个（staff_id,customer_id）索引还是应该颠倒一下顺序？

我们应该将选择性最高的放在前面，怎么确定呢

select count(distinct staff_id)/count(*) as staff_id_selectivity,

count(distinct customer_id)/count(*) as customer_id_selectivity,

count(*) from payment

如果后者数值高，答案就是讲后者放在索引列的前边。

当使用前缀索引的时候，在某些条件值得基数比正常值高的时候，索引就会失效。

比如说，某些应用中没有登录的用户的id记录为guset。或者一个超级管理员拥有所有用户为好友。在查询这些特殊值得时候应该特殊处理。

聚簇索引

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

InnoDB将通过主键聚集数据，上图中"被索引的列"就是主键列。

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

聚集的数据优点：

可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少数的数据也就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘I/O
数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找的更快。
使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

缺点：

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

覆盖索引

覆盖索引能够极大地提高性功能。只需要扫描索引无须回表。

索引条目通常远小于数据行的大小，所以如果只需要读取索引，那Mysql就会极大地减少数据访问量。
因为索引是按照列值顺序存储的，所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少得多。

使用索引扫描来做排序

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

扫描索引本身是很快，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那就每扫描一条索引记录就都回表查询一次对应的行。这基本上都是随机I/O，因此安索引顺序读取数据的速度通常要比顺序的全表扫描慢。

mysql可以使用同一个索引既满足排序，又用于查找行。因此，如果可能，设计索引时应该尽可能地同时满足。

高性能索引的创建