剖析如何创建高性能的索引

1.索引基础

1. 在mysql中，存储引擎先在索引中找到对应值，然后根据匹配的索引记录找到对应数据行。
2. 索引可以包含一个或多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为 mysql只能高效地使用索引的最左前缀列。创建一个包含2个列的索引，和创建2个只包含1列的索引时大不相同的。
3. 如果使用的是ORM，是否还需要关心索引？
   ORM工具能够生产符合逻辑的、合法的查询，除非只是生成非常基本的查询，否则它很难生成适合索引的查询。无论多么复杂的ORM工具，在精妙和复杂的索引面前都是浮云。
4. 索引的类型
   1、索引有很多种类型，可以为不同的场景提供更好的性能。在mysql中，索引是在存储引擎层而不是服务器层实现的。所以，并没有统一的标准。
   2、B-Tree索引：
   索引大部分都是使用B-Tree(B+Tree)，即每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历。不过，也可能使用不同的存储结构。例如：InnoDB使用的是B+Tree，NDB集群存储引擎内部实际上使用了T-Tree。
   存储引擎以不同的方式使用B-Tree索引，性能也各有不同，各有优劣。例如：MyISAM使用前缀压缩技术使得索引大小；但InnoDB则按照原数据格式进行存储。 MyISAM索引通过数据的物理位置引用被索引的行；而InnoDB则根据主键引用被索引的行。
   B-Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。
   B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么是找到对应的值，要么不存在。
   叶子节点比较特别，它们的指针指向的是被索引的数据，而不是其他的节点页。
   B-Tree对索引列是顺序组织存储的，所以很适合查找范围数据。
   索引对多个值进行排序的依据是CREATE TABLE语句中定义索引时列的顺序。
   全值匹配：指的是和索引中的所有列进行匹配。
   匹配最左前缀：即只使用索引的第一列。
   匹配列前缀：可以只匹配某一列的值的开头部分。
   匹配范围值
   精确匹配某一列并范围另外一列
   只访问索引的查询：即查询只需要访问索引，而无须访问数据行。
   因为索引树中的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的ORDER BY操作。如果ORDER BY子句满足前面列出的几种查询类型，则这个索引页可以满足对应的排序需求。
   下面是一些关于B-Tree索引的限制：
   1、如果不是按照索引的最左列开始查找，则无法使用索引。
   2、不能跳过索引中的列。
   3、如果查询中有个列的范围查询，则其右边所有列都无法使用索引优化查找。如果范围查询列值的数量有限，那么可以通过使用多个等于条件来代替范围条件。
   而有些限制并不是B-Tree本身导致的，而是mysql优化器和存储引擎使用索引的方式导致的。
5. 哈希索引：
   1、哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针
   2、在mysql中，只有Memory引擎显式支持哈希索引。而且是默认索引类型。Memory引擎是支持非唯一哈希索引的，如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。
   3、哈希索引的每个槽的编号是顺序的，但数据行不是。
   4、因为索引自身只需存储对应的哈希值，所以索引的结构十分紧凑，速度非常快。
   5、哈希索引的限制：
   (1) 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。
   (2) 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
   (3) 哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。
   (4) 哈希索引只支持等值查询，包括=、IN()、<=>，(<=>和=功能相同，用于2个值进行比较。但是<=>可以用于NULL运算，但是=不行)也不支持任何范围查询。
   (5) 访问哈希索引的数据非常快，除非有很多哈希冲突。
   (6) 如果哈希冲突很多的话，一些索引维护操作的代价也会很高。
   因为这些限制，哈希索引只适用于某些特定的场合。而一旦适合哈希索引，则它带来的性能提升将非常显著。
   除了Memory引擎外，NDB集群引擎也支持唯一哈希索引，且在NDB集群引擎中作用非常特殊。
   InnoDB引擎有一个特殊的功能叫做"自适应哈希索引"。当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引，这样就让B-Tree索引页具有哈希索引的一些优点。这是一个完全自动的、内部的行为，用户无法控制或者配置，不过可以关闭。
   创建自定义哈希索引：如果存储引擎不支持哈希索引，则可以模仿像InnoDB一样创建哈希索引，这可以享受一些哈希索引的便利，例如只需要很小的所有就可以为超长的键创建索引。思路很简单：在B-Tree基础上创建一个伪哈希索引。这和真正的哈希索引不是一回事，因为还是使用B-Tree进行查找，但是它使用哈希值而不是键本身进行索引查找。需要做的就是在查询的WHERE子句中手动指定使用哈希函数。 要避免冲突问题，必须在WHERE条件中带入哈希值和对应列值。
6. 空间数据索引(R-Tree)：
   1、MyISAM表支持空间索引，可以用作地理数据存储。和B-Tree索引不同，这类索引无须前缀查询。空间索引会从所有维度来索引数据。查询时，可以有效地利用任意维度来组合查询。必须使用Mysql的GIS相关函数等来维护数据，但并不完善，所以一般不会使用。
7. 全文索引:
   1、全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文索引和其他几类索引的匹配方式完全不一样。更类似于搜索引擎做的事情，而不是简单的WHERE条件匹配。
   2、在相同的列上同时创建全文索引和基于值的B-Tree索引不会有冲突，全文索引适用于MATCH AGAINTST + 索引(类似于like) 操作，而不是普通的WHERE条件操作。

2.索引的优点

1. 索引可以让服务器快速地定位到表的指定位置。但是这并不是索引的唯一作用，根据创建索引的数据结构不同，索引也有一些其他的附加作用。
2. 最常见的B-TREE索引，按照顺序存储数据，所以Mysql可以用来做ORDER BY和GROUP BY操作。最后，因为索引中存储了实际的列值，所以某些查询只使用索引就能够完成全部查询。据此特性，总结下来索引有如下3个优点：
   1、索引大大减少了服务器需要扫描的数据量。
   2、索引可以帮助服务器避免排序和临时表。
   3、索引可以将随机IO变为顺序IO。
3. 如何评价一个索引是否适合某个查询的三星系统：索引将相关的记录放在一起则获得一星；如果索引中的数据顺序和查找中的排列顺序一致则获得二星；如果索引中的列包含了查询中需要的全部列则获得三星。
4. 但索引并不总是最好的工具。总的来说，只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时，索引才是有效的。对于非常小的表，简单的全表扫描更高效。对于中到大型的表，索引就非常有效。但对于特大型的表，建立和使用索引的代价将随之增长。这种情况下，则需要一种技术可以直接区分出查询需要的一组数据，而不是一条记录一条记录地匹配，例如可以采取分区技术。
   如果表的数量特别多，可以建立一个元数据信息表，用来查询需要用的的某些特性。对于TB级别的数据，定位单条记录的意义不大，所以经常会使用块级别元数据技术来替代索引。

3.高性能的索引策略

1. 独立的列
   1、如果查询中的列不是独立的，则mysql就不会使用索引。独立的列是指索引列不能是表达式的一部分，也不能是函数的参数。我们应该养成简化WHERE条件的习惯，始终将索引列单独放在符号的一侧。

2. 前缀索引和索引选择性
   1、有时候需要索引很长的字符列，这会让索引变得大且慢。一个策略是模拟哈希索引。 另一种策略通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。索引的选择性是指，不重复的索引值(基数)和数据表的记录总数的比值。索引的选择性越高则查询效率越高，因为选择性高的索引可以让mysql在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
   2、一般情况下某个列前缀的选择性也是足够高的，足以满足查询性能。对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为mysql不允许索引这些列的完整长度。
   3、计算合适的前缀长度可以计算完整列的选择性，并使前缀的选择性接近于完整列的选择性。
   只看平均选择性是不够的，也需要考虑最坏情况下的选择性。如果数据分布很不均匀，可能就会有陷阱。
   4、前缀索引时一种能使索引更小、更快的有效办法，但另一方面也有其缺点：mysql无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。
   5、有时候后缀索引也有用途(例如找电子邮件地址)。mysql原生并不支持反向索引，但是可以把字符串反转后存储，并基于此建立前缀索引，可以通过触发器来维护这种索引。

3. 多列索引
   1、在多个列建立独立的单列索引大部分情况下并不能提高mysql的查询性能。5.0以上版本引入了一种叫索引合并的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。
   2、有一个查询的or语句，在5.0以前，mysql对这个查询会使用全表扫描。除非改写为2个查询UNION的方式。 但在5.0及以上，查询能够同时使用这2个单列索引进行扫描，并将结果进行合并。这种算法有3个变种：OR条件的联合，AND条件的相交，组合前两种情况的联合及相交。
   mysql会使用这类技术优化复杂查询，所以在某些语句的Extra列中还可以看到嵌套操作。
   索引合并策略有时候是一种优化的结果，但实际上更多时候说明了表上的所有建得很糟糕：
   (1) 当出现服务器对多个索引做相交操作时，意味着需要一个包含所有相关列的多列索引。
   (2) 当服务器需要对多个索引做联合操作时，需要耗费大量的cpu和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。
   (3) 更重要的是，优化器不会把这些计算到查询成本中，优化器只关心随机页面读取。这会使得查询的成本被低估，导致该执行计划还不如直接走全表扫描。这样做不但会消耗更多的cpu和内存资源，还可能会影响查询的并发性。还不如将查询改写成UNION的方式往往更好。
   3、如果在EXPLAIN中看到有索引合并，应该好好检查一下查询和表的结构，看是不是已经是最优的。也可以通过参数optimizer_switch来关闭索引合并功能，也可以用IGNORE INDEX提示让优化器忽略掉某些索引。

4. 选择合适的索引列顺序
   1、正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好地满足排序和分组的需要。
   2、在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二次。所以，索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY、GROUP BY和DISTINCT等子句的查询需求。 所以多列索引的列顺序至关重要。
   3、对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前列，这个建议在某些场景可能有帮助，但通常不如避免随机IO和排序那么重要，
   当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化WHERE条件的查找。在这种情况下，这样设计的索引确实能够最快地过滤出需要的行，对于在WHERE子句中只使用了索引部分前缀列的查询来说选择性也更高。然而，性能不只是依赖于所有索引列的选择性(整体基数)，也和查询条件的具体值有关，也就是和值的分布有关。 可能需要根据哪些运行频率最高的查询来调整索引列的顺序，让这种情况下索引的选择性最高。
   4、最后，尽管关于选择性和基数的经验法则值得去研究和分析，但一定要记住别忘了WHERE子句中的排序、分组和范围条件等其他因素，这些因素可能对查询的性能造成非常大的影响。

5. 聚簇索引
   1、聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。
   2、当表有聚簇索引时，它的数据行实际上存放在索引的叶子页中。聚簇表示数据行和相邻的键值紧凑地存储在一起。因为无法同时把数据行存放在2个不同的地方，所以一个表只能有一个聚簇索引。(不过，覆盖索引可以模拟多个聚簇索引的情况)
   3、因为是存储引擎负责实现索引，因此不是所有的存储引擎都支持聚簇索引。InnoDB支持。
   4、聚簇索引的叶子页包含了行的全部数据，但是节点页只包含了索引列。InnoDB将通过主键聚集数据。如果没有定义主键，会选择一个唯一的非空索引(唯一键索引)代替。如果没有这样的索引，会隐式定义一个主键(自动增加一个6字节的整数列，被叫做行ID)来作为聚簇索引。InnoDB只聚集在同一个页面的记录，包括相邻键值的页面可能会相距甚远。
   5、优点：
   (1) 可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘I/O。
   (2) 数据访问更快。
   (3) 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。
   6、缺点：
   (1) 聚簇数据最大限度地提高了IO密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没那么重要了，聚簇索引也就没什么优势了。
   (2) 插入速度严重依赖于插入顺序。根据主键的顺序插入是加载数据到InnoDB表中速度最快的方式。但如果不是按照主键顺序加载数据，那么在加载完成后最好使用OPTIMIZE TABLE命令重新组织一下表。
   (3) 更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。’
   (4) 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候可能面临页分裂的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成2个页面来容纳该行，这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间。
   (5) 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏(页中的数据不连续)，或者由于页分裂导致数据存储不连续的时候。
   (6) 二级索引(非聚簇索引)可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列。
   (7) 二级索引访问需要2次索引查找，而不是1次。 因为二级索引中实际保存的是行指针。二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。
   这意味着通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获得对应的主键值，然后根据这个值去聚簇索引中查找到对应的行。对于InnoDB，自适应哈希索引能够减少这样的重复工作。
   7、InnoDB和MyISAM的数据分布对比：
   (1)MyISAM： 它按照数据插入的顺序存储在磁盘上。使用的是非聚簇索引。
   它的主键索引和其他索引在结构上没有什么不同
   (2)InnoDB:支持聚簇索引。InnoDB二级索引的叶子节点中存储的不是行指针，而是主键值，并以此作为指向行的指针。这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。使用主键值当作指针会让二级索引占用更多的空间，但换来的好处是：InnoDB在移动行时无须更新二级索引中的这个指针。
   8、在InnoDB表中按主键顺序插入行
   (1) 如果正在使用InnoDB表并且没有什么数据需要聚集，那么可以定义一个代理键作为主键，这种主键的数据应该和应用无关。最简单的方法时使用自增列，这样可以保证数据行时按顺序写入，对于根据主键做关联操作的性能也会更好。
   (2) 最好避免随机的聚簇索引，特别是对于IO密集型的应用。例如，从性能的角度考虑，使用UUID来作为聚簇索引会很糟糕：它使得聚簇索引的插入变得完全随机，这是最坏的情况，使得数据没有任何聚集特性。
   (3)如果主键的值是顺序的，InnoDB会把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时(默认的最大填充因子是页大小的15/16，留出部分空间用于以后修改)，下一条记录就会写入新的页中。
   (4) 如果主键的值是随机的，那么InnoDB需要为新的行寻找合适的位置，这会增加很多的额外工作，并导致数据分布不够优化。下面是总结的一些缺点：
   1、写入的目标页可能已经刷到磁盘上并从缓存中移除(内存页被写满会被刷到磁盘上)，或者是还没有被加载到缓存中，InnoDB在插入之前不得不先找到并从磁盘读取目标页到内存中。这将导致大量的随机IO。
   2、因为写入是乱序的，InnoDB不得不频繁地做页分裂操作，以便为新的行分配空间。页分裂会导致移动大量数据，一次插入最少需要修改3个页而不是一个页。
   3、由于频繁的页分裂，页会变得稀疏并被不规则地填充，所以最终数据会有碎片。
   在把这些随机值载入到聚簇索引以后，也许需要做一次OPTIMIZE TABLE来重建表并优化页的填充。
   9、顺序的主键什么会造成更坏的结果？
   答；对于高并发工作负载，在InnoDB中按主键顺序插入可能会造成明显的争用。主键的上界会成为热点。因为所有的插入都发生在这里，所以并发插入可能导致间隙锁竞争。另一个可能是自增锁机制。如果遇到这个问题，则可能需要考虑重新设计表或应用，或更改innodb_autoinc_lock_mode配置。

6. 稠密索引和稀疏索引：
   1、在密集索引中，数据库中的每个搜索值都有一个索引记录，这样可以加快搜索速度，但需要更多空间来存储索引记录本身。索引记录包含搜索键值和指向磁盘上实际记录的指针。
   2、在稀疏索引中，不会为每个搜索关键字创建索引记录。
   聚簇索引(主键索引)是稠密索引，因为聚簇索引所有的值都不为空，每一个搜索码都会有对应的记录
   非聚簇索引时稀疏索引，非聚集索引有唯一索引，普通索引，复合索引。他们的特征就是不会为表的每个值创建搜索码，而是为单个或单个字段创建，且行记录的某些值可以为null。当where条件不止单个条件的时候我们会首先通过索引查找出来一批数据，然后进行顺序查找筛选。

7. 覆盖索引
   1、如果一个索引包含(覆盖)所有需要查询的字段的值，就称之为覆盖索引。
   2、覆盖索引能够极大地提高性能，如果查询只需要扫描索引而无须回表，会带来的好处：
   (1) 索引条目通常远小于数据行大小，所以如果只需要读取索引，那mysql就会极大地减少数据访问量。这对缓存的负载非常重要，因为这种情况下响应时间大部分花在数据拷贝上。覆盖索引对于IO密集型的应用也有帮助，因为索引比数据更小，更容易全部放入内存中(这对于MyISAM尤其正确，因为MyISAM能压缩索引以变得更小)
   (2) 因为索引是按照列值顺序存储的，所以对于IO密集型的范围查询会比随机从磁盘读取每一行数据的IO要少得多。
   (3) 一些存储引擎如MyISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用。这可能会导致严重的性能问题，尤其是那些系统调用占了数据访问中的最大开销场景。
   (4) 由于InnoDB的聚簇索引，覆盖索引对InnoDB特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。
   在所有这些场景中，在索引中满足查询的成本一般比查询行要小得多。
   3、不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以mysql只能使用B-Tree索引做覆盖索引。
   4、当发起一个被索引覆盖的查询时，在EXPLAIN的Extra可以看到Using index的信息。
   5、索引覆盖查询还有很多陷阱可能会导致无法实现优化。mysql查询优化器会在执行查询前判断是否有一个索引能进行覆盖。假设索引覆盖了WHERE条件中的字段，但不是整个查询涉及的字段。如果条件为假，5.5和之前的版本也总是会回表获取数据行，尽管并不需要这一行且最终会被过滤掉。
   6、mysql不能在索引中执行LIKE操作。这是底层存储引擎API的限制，mysql能在索引中做最左前缀匹配的LIKE查询，因为该操作可以转换为简单的比较操作，但是如果是通配符开头的LIKE查询，存储引擎就无法做比较匹配。这种情况下，只能提取数据行的值而不是索引值来做比较。
   7、上面提到的很多限制都是由于存储引擎API设计所导致的，目前的API设计不允许mysql将过滤条件传到存储引擎层。如果mysql在后续版本能够做到这一点，则可以把查询发送到数据上，而不是像限制这样只能把数据从存储引擎拉到服务器层，再根据查询条件过滤。

8. 使用索引扫描来排序
   1、mysql有2种方式可以生产有序的结果：通过排序操作，或者按索引顺序扫描。如果EXPLAIN出来的type列的值为index，则说明使用了索引扫描来做排序。
   2、扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条索引记录就都回表查询一次对应的行。这基本上都是随机IO，因此按索引顺序读取数据的数据通常要比顺序地全表扫描慢，尤其是在IO密集型的工作负载时。
   3、mysql可以使用同一个索引既满足排序，又用于查找行。因此，设计索引时应该尽可能地可能满足这2种任务，这样是最好的。
   4、只有当索引的列顺序的ORDER BY子句的顺序完全一致，并且所有列的排序方向都一样时，mysql才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。ORDER BY子句和查找性查询的限制是一样的：需要满足索引的最左前缀的要求，否则，都需要执行排序操作，而无法利用索引排序。
   5、有一种情况下ORDER BY子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候。如果WHERE子句或者JOIN子句中对这些列指定了常量，就可以弥补索引的不足。
   6、使用索引做排序的一个最重要的用法是当查询同时有ORDER BY和LIMIT子句的时候。

9. 压缩(前缀压缩)索引
   1、MyISAM使用前缀压缩来减少索引的带下，从而让更多的索引可以放入内存中。默认只压缩字符串，但通过参数设置也可以对整数做压缩。
   2、压缩每个索引块的方法是，先完全保存索引块中的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分，把这部分存储起来即可。例如第一个值是perform，第二个值是performance，那么第二个值的前缀压缩后存储的是类似’7,ance’这样的形式。对行指针也采用类似的前缀压缩方式。
   3、`压缩块使用更少的空间，代价是某些操作可能更慢。因为每个值的压缩前缀都依赖前面的值，所以MyISAM查找时无法在索引块使用二分查找而只能从头开始扫描。正序的扫描速度还不错，但是如果是倒序扫描就不是很好了。所有在块中查找某一行的操作平均都需要扫描半个索引块。
   4、对于CPU密集型应用，因为扫描需要随机查找，压缩索引使得MyISAM在索引查找上要慢好几倍。压缩索引需要在CPU内存资源和磁盘之间做权衡。压缩索引可能只需要十分之一大小的磁盘空间，如果是IO密集型应用，对某些查询带来的好处会比成本多很多。
   5、可以在CREATE TABLE语句中指定PACK_KEYS参数来控制索引压缩的

10. 冗余和重复索引
    1、mysql允许在相同列上创建多个索引，无论是有意的还是无意的。需要单独维护重复的索引，并且优化器在优化查询的时候也需要逐个地进行考虑，这会影响性能。
    2、重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免这样创建重复索引，发现以后也应该立即移除。
    3、mysql的唯一限制和主键限制都是通过索引实现的。
    4、冗余索引和重复索引有一些不同。如果创建了索引(A,B),再创建索引(A)就是冗余索引，因为这只是前一个索引的前缀索引。因此索引(A,B)也可以当做索引(A)来使用(这种冗余只是对B-Tree索引来说的)。但是如果再创建索引(B,A),则不是冗余索引，索引(B)也不是，因为B不是索引(A,B)的最左前缀列。另外，其他不同类型的索引也不会是B-Tree索引的冗余索引，而无论覆盖的索引列是什么。
    5、冗余索引通常发生在为表添加新索引的时候。例如，有人可能会增加一个新的索引(A,B)，而不是扩展已有的索引(A)。还有一种情况是将一个索引扩展为(A,ID)，其中ID是主键，对于InnoDB来说主键列已经包含在二级索引中了，所以这也是冗余的。
    6、大多数情况下都不需要冗余索引，应该尽量扩展已有的索引而不是创建新索引。但也有时候出于性能方面的考虑需要冗余索引，因为扩展已有的索引会导致其变得太大，从而影响其他使用该索引的查询的性能。

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

12. 索引和锁
    1、索引可以让查询锁定更少的行。虽然InnoDB的行锁效率很高，内存使用也很少，但是锁定行的时候仍然会带来额外开销。其次，锁定超过需要的行会增加锁争用并减少并发性。
    2、InnoDB只有在访问行的时候才会对其加锁，而索引能够减少InnoDB访问的次数，从而减少锁的数量。但这只有当InnoDB在存储引擎层能够过滤掉所有不需要的行时才有效。如果索引无法过滤掉无效的行，那么在InnoDB检索到数据并返回给服务器层以后，mysql服务器才能应用WHERE子句。这时已经无法避免锁定行了：InnoDB已经锁住了这些行，到适当的时候才释放。在5.1以上，InnoDB可以在服务器端过滤掉行后就释放锁，但是之前只有事务提交后才能释放锁。
    3、即使使用了索引，InnoDB也可能锁住一些不需要的数据。如果不能使用索引查找，锁定行的话问题可能会更糟糕，mysql会做全表扫描并锁住所有的行，不管是需要不需要。
    4、关于InnoDB、索引和锁有一些很少有人知道的细节：InnoDB在二级索引上使用共享锁，但访问主键索引需要排他锁。这消除了使用覆盖索引的可能性，并且使得SELECT FOR UPDATE 比LOCK IN SHARE MODE或非锁定查询要慢得多。

4.索引案例

1. 支持多种过滤条件
   1、基本原则：考虑表上所有的选项。当设计索引时，不要只为现有的查询考虑需要哪些索引，还需要考虑对查询进行优化。如果发现某些查询需要创建新索引，但是这个索引又会降低另一些查询的效率，那么应该想一下是否能优化原来的查询。应该同时优化查询和索引以找到最佳的平衡。
   2、尽可能将需要做范围查询的列放到索引的后面，以便优化器能使用尽可能多的索引列。
2. 避免多个范围条件，因为当使用范围条件列作为索引，后面的列就会失效。
3. 优化排序
   1、延迟关联：通过使用覆盖索引查询返回需要的主键，再根据这些主键关联原表获得需要的行。这可以减少mysql扫描那些需要丢弃的行数。

5.维护索引和表

即使使用正确的类型创建了表并加上了合适的索引，工作也没有结束：还需要维护表和索引来确保它们都正常工作。维护表有三个主要的目的：找到并修复损坏的表，维护准确的索引统计信息，减少碎片。

1. 找到并修复损坏的表
   1、对于MyISAM存储引擎，表损坏通常是系统崩溃导致的。其他的引擎也会由于硬件问题、mysql本身的缺陷或者操作系统的问题导致索引损坏。
   2、损坏的索引会导致查询返回错误的结果或者莫须有的主键冲突等问题，严重时还会导致数据库的崩溃。CHECK TABLE通常能够找出大多数的表和索引的错误。
   3、可以使用REPAIR TABLE命令来修复损坏的表，如果存储引擎不支持，也可通过一个不做任何操作的ALTER操作来重建表。
   4、如果遇到数据损坏，最重要的是找出是什么导致了损坏，而不只是简单地修复，否则很有可能还会不断地损坏。可以通过设置innodb_force_recovery参数进入InnoDB的强制恢复模式来修复数据。
2. 更新索引统计信息
   1、Mysql的查询优化器会通过2个API来了解存储引擎的索引值的分布信息，以决定如何使用索引。第一个API是records_in_range()，通过向存储引擎传入两个边界值获取在这个范围大概有多少条记录。对于MyISAM，该接口返回精确值；对于InnoDB，则是估算值。
   第二个API是info()，该接口返回各种类型的数据，包括索引的基数(不重复的索引值)
   2、如果存储引擎向优化器提供的扫描行数信息是不准确的数据，或者执行计划本身太复杂以致无法准确地获取各个阶段匹配的行数，那么优化器会使用索引统计信息来估算扫描行数。mysql优化器使用的是基于成本的模型，而衡量成本的主要指标就是一个查询需要扫描多少行。如果表没有统计信息，或者不准确，优化器就有可能做出错误的决定。可以通过ANALYZE TABLE来重新生成统计信息解决这个问题。
   3、每种存储引擎实现索引统计信息的方式不同，所以需要进行ANALYZE TABLE的频率也因不同的引擎而不同，每次运行的成本也不同：
   Memory引擎根本不存储索引统计信息。
   MyISAM将索引统计信息存储在磁盘中，ANALYZE TABLE需要进行一次全索引扫描来计算索引基数。在整个过程中需要锁表。
   直到5.5版本，InnoDB也不在磁盘存储索引统计信息，而是通过随机的索引访问进行评估并将其存储在内存中。
   4、可以使用SHOW INDEX FROM命令来查看所有的基数(Cardinality)
   5、InnoDB引擎通过抽样的方式来计算统计信息，首先随机地读取少量的索引页面，然后以此为样本计算索引的统计信息。在老的版本中，样本页面数是8，新版本的InnoDB可以通过参数innodb_stats_sample_pages来设置样本页的数量。设置更大的值，可以帮助生成更准确的索引信息
   6、InnoDB会在表首次打开，或者执行ANALYZE TABLE，抑或表的大小发生非常大的变化的时候计算索引的统计信息。
   7、如果服务器上有大量的数据，触发索引统计信息的更新，就可能是个很严重的问题。因为当IO比较慢的时候，会导致大量的锁，并给服务器带来很多额外的压力。
3. 减少索引和数据的碎片
   1、B-Tree索引可能会碎片化，这会降低查询的效率。碎片化的岁月可能会以很差或者无序的方式存储在磁盘上。
   2、表的数据存储也可能碎片化。然而，数据存储的碎片化比索引更加复杂。有3种：
   (1) 行碎片：数据行被存储为多个地方的多个片段中。即使查询只从索引中访问一行记录，行碎片也会导致性能下降。
   (2) 行间碎片：指的是逻辑上顺序的页，或者行在磁盘上不是顺序存储的。行间碎片对诸如全表扫描和聚簇索引扫描之类的操作有很大的影响，因为这些操作原本能从磁盘上顺序存储的数据获益。
   (3) 剩余空间碎片：数据页中有大量的空余空间。这会导致服务器读取大量不需要的数据，从而造成浪费。
   对于MyISAM表，这三类碎片化都可能发生。但InnoDB不会出现短小的行碎片，会移动短小的行并重写到一个片段中。
   3、可以通过执行OPTIMIZE TABLE或者导出再导入的方式来重新整理数据。
   MyISAM可以通过排序算法重建索引的方式来消除碎片。
   InnoDB可以通过添加和删除索引的功能来消除。
   4、另外也要考虑数据是否已经达到稳定状态，如果进行碎片整理将数据压缩到一起，可能反而会导致后续的更新操作触发一系列的页分裂和重组，这会对性能造成不良的影响。