《高性能MySQL》之创建高性能的索引

1.什么是索引

    索引是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，当数据量很大时，不适当的索引对性能的影响很大。索引优化是对查询性能优化最有效的手段，最优的索引能将查询性能提高几个数量级，前提是需要重写查询命中索引。

2.索引的类型

    索引有很多种类型，不同的场景不用合适的索引可以提供更好的性能。在MySQL中，索引是在存储引擎层而不是服务器层的。MySQL支持的索引类型：B-Tree索引、哈希索引、空间数据索引、全文索引。

1）B-Tree索引

    通常没有特别指明类型的索引，说的应该是B-Tree索引，它使用B-Tree数据结构来存储数据，不同的存储引擎以不同的方式使用B-Tree索引，性能会各有不同，各有优劣。
    比如，MyISAM使用前缀压缩技术使得索引更小，单InnoDB按照原数据格式进行存储。再如：MyISAM通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行。
    B-Tree索引能够加快访问数据的速度，它使存储引擎不在进行全表扫描来获取需要的数据，而是从索引的根节点(B-Tree树的根节点)开始进行搜索，根节点存储了指向子节点的指针，存储引擎根据指针向下层查找，通过比较节点值和要查找的值，逐层向下查找，要么找到对应的值，要么该记录不存在。
    可以使用B-Tree索引的查询类型：
        ①全值匹配：
              指的是和索引中的所有列进行匹配。比如索引包含name,age，sex三列，可以查炒姓名为张三，年龄18，性别男的人。
        ②匹配最左前缀
              上面提到的索引也可用于查找姓名为李四的人，即只是用索引的第一列。
        ③匹配列前缀
              也是使用索引的第一列，例如可以查找姓李的人。
        ④匹配范围值
              通用使用索引的第一列，例如查找姓名在张三到李四之间的人，前提是字符排序张三在前、李四在后。
        ⑤精确匹配索引的前列并范围匹配索引的后列
              查询姓名为张三，年龄在18-20之间的人。
        ⑥只访问索引的查询
              即查询只需要访问索引，不需要访问数据行。

    B-Tree索引的限制：
        查询列的顺序和索引列的顺序一致且不是范围查询，才可使用此索引优化查找。
        ①如果不是按索引的最左列开始查找，则无法使用索引 。
        ②不能跳过索引的列
        ③如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引优化查询。

2）哈希索引

    基于哈希表实现，只有精确匹配索引的所有列的查询才有效。
    对于每一行的数据，存储引擎都会对所有的索引列计算一个哈希码，哈希索引将所有的哈希码存储在索引中，同时哈希表中保存指向每一行数据的指针。
    应该尽可能保证不同键值的行计算出来的哈希码不一样，减少哈希冲突。
    查找逻辑：

select last_name from user where first_name='peter';

MySQL先计算peter的哈希值，比如f_hash(‘peter’)=999，然后在索引中查找999,找到指向第3行的指针，最后比较第三行的值是否为‘peter’，以确保是要查找的行。
    限制：
        ①哈希索引只包含哈希值和行指针，不包含字段值，需要读取行来获取数据。
        ②数据并不是按索引值顺序存储的，无法用于排序。
        ③不支持部分索索引列匹配查找。在数据列(A,B)上创建哈希索引，只查询数据列A，则无法使用该索引。因为哈希索引是使用索引列的全部列内容来计算哈希值的。
        ④哈希索引只支持等值比较查询，包括=、IN()、<=>;不支持任何范围查询，例如age>18
        ⑤当有很多哈希冲突是，存储引擎需要遍历链表中的所有行指针，逐行比较，直至找到所有符合条件的行。
        ⑥哈希冲突很多时，一些索引维护操作的代价会很高。

3）空间数据索引

    MyISAM支持空间索引，可以用作地理数据存储。

4）全文索引

    全文索引是一种特殊的索引，他查找的是文本中的关键词，全文索引类似于搜索引擎做的事情，不是简单的WHERE条件匹配。

3.高性能的索引策略

    正确地创建和使用索引是实现高性能查询的基础。首先引入前辈的“三星索引”概念，用于评价一个索引是否适合某个查询。索引将相关的记录放到一起则获得异形；如果索引中的数据顺序和查找中的排列顺序一致则获得二星；如果过索引中的列包含了查询中需要的全部列则获得【三星】。

1）独立的列

    是指索引列不能是表达式的一部分，也不能是函数的参数。我们应该养成简化WHERE条件的习惯，始终将索引列单独放在比较符号的一侧。
    例如，下面的查询无法使用user_id列的索引：

select user_id from user where user_id + 1 = 10;

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

    当需要在很长的字符列上建索引是，可以使用前缀索引，即只索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率，但这样会降低索引的选择性。
    索引的选择性：是指不重复的索引值和数据表的记录总数的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高，因为可以在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，因此性能也是最好的。
    对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不循序索引这些列的完整长度。

3）多列索引

    当有多个AND条件时，在多个列上建立独立的单列索引大部分情况下并不能提高查询性能，应该创建一个包含所有相关列的多列索引。

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

    正确的顺序依赖于使用该索引的查询，并且同时考虑如何更好的满足排序和分组的需要。在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。此条策略适用B-Tree索引。
    当不需要考虑排序和分组时，将选择性最高的列放在索引最前列，这样设计的索引能够最快的过滤出需要的行。此外，还可以根据那些运行频率最高的查询来调整索引列的顺序。

5）聚簇索引

    聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式。InnoDB的聚簇索引实际上是在同一个结构中保存了B-Tree索引和数据行，InnoDB通过主键聚集数据。一个表只能有一个聚簇索引。“聚簇”表示数据行和相邻的键值紧凑的存储在一起。
    优点：
        ①可以把相关数据保存在一起，减少磁盘I/O。比如根据用户id来聚集数据，就可以从磁盘读取少数的数据也就能获取某个用户的全部邮件。
        ②数据访问更快。
        ③使用覆盖索引扫描的查询可以直接使用页节点的主键值。
    缺点：
        ①更新聚簇索引的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置
        ②基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂”的问题。页分裂会导致表需要更多的磁盘空间。
        ③聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏的时候，或者由于页分裂导致数据存储不连续的时候

6）覆盖索引

    如果一个索引包含或者说覆盖所有需要查询的字段的值，我们就称之为“覆盖索引”。即使用使用覆盖索引，查询只需要扫描索引而无需访问行数据，这将会提高很大的性能。

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

    MySQL可以使用同一个索引既满足排序，又用于查找行，因此，如果可能，设计索引时应该尽可能地同时满足这两种任务。
    只有当索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方向(正序或倒序)一致是，才能使用索引来对结果排序。如果查询需要关联多张表，则只有当order by子句引用的字段全部为第一张表时，才能使用索引做排序。order by子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求，才能利用索引排序。

8）冗余和重复索引

    MySQL允许在相同列上创建多个索引，这些索引需要单独维护。
    重复索引：是指在相同的列上按相同的顺序创建相同类型的索引。应当避免创建这样的索引，发现后应该立即删除。
    冗余索引：是指如果创建了索引（A,B),在创建索引(A)就是冗余索引。冗余索引通常发生在为表添加新索引的时候。例如，有人可能增加一个新的索引（A,B)，而不是扩展已有的索引(A)。但是当扩展已有的索引导致使用原有索引的查询性能变差是，可以增加单独的一个索引。

9）未使用的索引

除了冗余索引和重复索引，可能还会存在一些服务器永远不会使用的索引，这样的索引完全是累赘的，建议考虑删除。但有些索引的功能相当于唯一约束，虽然该索引一直未被查询使用，却可能是用于避免产生重复数据的。

4.维护索引和表

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

1）找到并修复损坏的表

    损坏的索引会导致查询返回错误的结果或者莫须有的主键冲突等问题。CHECK TABLE命令通常能够找出大多数的表和索引的错误。有些存储引擎不支持该命令，会存在其他选项来控制检查表的方式。可以使用REPAIR TABLE命令来修复损坏的表；也可以通过ALTER TABLE操作来重建表。如果不是索引损坏，而是系统区域或者表的行数据区域损坏，可以从备份中恢复表，或者从损坏的数据文件中尽可能的恢复数据，当遇到此种损坏导致的数据损坏，最重要的是找出损坏的原因，而不是简单的修复，否则可能还会不断地损坏。

2）维护准确的索引统计信息

    InnoDB在打开某些INFORMATION_SCHEMA表，或者使用SHOW TABLE STATUS 和SHOW INDEX，或者在MySQL客户端开启自动补全功能的时候都会触发索引统计信息的更新，如果服务器上有大量的数据，或者当I/O比较慢的时候，可能会导致大量的锁，给服务器带来额外的压力。可以关闭innodb_stats_on_metadata参数来避免这个问题。
    一旦关闭索引统计信息的自动更新，那么就需要周期性的使用ANALYZE TABLE命令来手动更新。

3）减少碎片

    B-Tree索引可能会碎片化，这样会降低查询效率。有三种类型的数据碎片：行碎片、行间碎片、剩余空间碎片。
    可以通过OPTIMIZE TABLE或者导出再导入的方式来重新整理数据，或者通过一个不做任何操作的ALTER TABLE操作来重建表，只需要把表的存储引擎修改为当前引擎即可：

ALTER TABLE <table> ENGINE=<engine>;

5.总结

    如何判断创建的索引是合理的呢，一般我们应该用响应时间来对查询进行分析，找出消耗事件最长的查询或者给服务器带来最大压力的查询，然后检查这些查询的schema、SQL和索引结构，判断是否扫描了太多的行，是否做了很多额外的排序或者使用了临时表，是否使用随机I/O访问数据，或者有太多回表查询那些不在索引中的列的操作。
    如果在合适的场景中使用索引，将大大提高查询的相应事件。在选择索引和编写利用这些索引的查询时，有三个原则：尽可能选择合适的索引来避免单行查找、尽可能的使用数据原始顺序从而避免额外的排序操作、尽可能使用索引覆盖查询减少数据行的访问。
    如果一个查询无法从现有的索引中获益，那么可以考虑创建一个更合适的索引。或者重写该查询，使其转化为能够利用现有索引或新创建索引的查询。这就涉及到SQL优化的主题了，暂不叙述。