读《MySQL性能调优与架构设计》笔记之合理设计并利用索引

        在MySQL 中，主要有四种类型的索引，分别为：B-Tree 索引，Hash 索引，Fulltext 索引和RTree索引，下面针对这四种索引的基本实现方式及存储结构做一个大概的分析。

1.1. B-Tree 索引

MyISAM索引实现

        MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图：

                                                                        图8

        这里设表一共有三列，假设我们以Col1为主键，则图8是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

                                                                        图9

        同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

        MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

InnoDB索引实现

        虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

        第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

                                                                                图10

        图10是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

        第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，图11为定义在Col3上的一个辅助索引：

                                                                                        图11

        这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

        了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

1.2. Hash 索引

        Hash 索引在MySQL 中使用的并不是很多，目前主要是Memory 存储引擎使用，而且在Memory 存储引擎中将Hash 索引作为默认的索引类型。所谓Hash 索引，实际上就是通过一定的Hash 算法，将需要索引的键值进行Hash 运算，然后将得到的Hash 值存入一个Hash 表中。然后每次需要检索的时候，都会将检索条件进行相同算法的Hash 运算，然后再和Hash 表中的Hash 值进行比较并得出相应的信息。

        在Memory 存储引擎中，MySQL 还支持非唯一的Hash 索引。可能很多人会比较惊讶，如果是非唯一的Hash 索引，那相同的值该如何处理呢？在Memory 存储引擎的Hash 索引中，如果遇到非唯一值，存储引擎会将他们链接到同一个hash 键值下以一个链表的形式存在，然后在取得实际键值的时候时候再过滤不符合的键。

        由于Hash 索引结构的特殊性，其检索效率非常的高，索引的检索可以一次定位，而不需要像BTree索引需要从根节点再到枝节点最后才能访问到页节点这样多次IO 访问，所以Hash 索引的效率要远高于B-Tree 索引。

        可能很多人又会有疑问了，既然Hash 索引的效率要比B-Tree 高很多，为什么大家不都用Hash索引而还要使用B-Tree 索引呢？任何事物都是有两面性的，Hash 索引也一样，虽然Hash 索引检索效率非常之高，但是Hash 索引本身由于其实的特殊性也带来了很多限制和弊端，主要有以下这些：

        1. Hash 索引仅仅只能满足“=”,“IN”和“<=>”查询，不能使用范围查询；由于Hash 索引所比较的是进行Hash 运算之后的Hash 值，所以Hash 索引只能用于等值的过滤，而不能用于基于范围的过滤，因为经过相应的Hash 算法处理之后的Hash 值的大小关系，并不能保证还和Hash 运算之前完全一样。

        2. Hash 索引无法被利用来避免数据的排序操作；由于Hash 索引中存放的是经过Hash 计算之后的Hash 值，而且Hash 值的大小关系并不一定和Hash 运算前的键值的完全一样，所以数据库无法利用索引的数据来避免任何和排序运算；

        3. Hash 索引不能利用部分索引键查询；对于组合索引，Hash 索引在计算Hash 值的时候是组合索引键合并之后再一起计算Hash 值，而不是单独计算Hash 值，所以当我们通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用到；

        4. Hash 索引在任何时候都不能避免表扫面；前面我们已经知道，Hash 索引是将索引键通过Hash 运算之后，将Hash 运算结果的Hash 值和所对应的行指针信息存放于一个Hash 表中，而且由于存在不同索引键存在相同Hash 值的

可能，所以即使我们仅仅取满足某个Hash 键值的数据的记录条数，都无法直接从Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较而得到相应的结果。

        5. Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree 索引高；对于选择性比较低的索引键，如果我们创建Hash索引，那么我们将会存在大量记录指针信息存与同一个Hash值相关连。这样要定位某一条记录的时候就会非常的麻烦，可能会浪费非常多次表数据的访问，而造成整体性能的地下。

1.3. Full-text 索引

……

1.4. R-Tree 索引

……

1.5. 索引的利处

        在数据库中个表的某个字段创建索引，所带来的最大益处就是将该字段作为检索条件的时候可以极大的提高检索效率，加快检索时间，降低检索过程中所需要读取的数据量。但是索引所给我们带来的收益只是提高表数据的检索效率吗？当然不是，索引还有一个非常重要的用途，那就是降低数据的排序成本。

        每个索引中索引数据都是按照索引键键值进行排序后存放的，所以，当我们的Query 语句中包含排序分组操作的时候，如果我们的排序字段和索引键字段刚好一致，MySQL Query Optimizer就会告诉mysqld 在取得数据之后不用排序了，因为根据索引取得的数据已经是满足客户的排序要求。

        那如果是分组操作呢？分组操作没办法直接利用索引完成。但是分组操作是需要先进行排序然后才分组的，所以当我们的Query 语句中包含分组操作，而且分组字段也刚好和索引键字段一致，那么mysqld 同样可以利用到索引已经排好序的这个特性而省略掉分组中的排序操作。

        排序分组操作主要消耗的是我们的内存和CPU 资源，如果我们能够在进行排序分组操作中利用好索引，将会极大的降低CPU 资源的消耗。

1.6. 索引的弊端

        索引能够极大的提高数据检索效率，也能够改善排序分组操作的性能，但是我们不能忽略的一个问题就是索引是完全独立于基础数据之外的一部分数据。假设我们在Table ta 中的Column ca 创建了索引idx_ta_ca，那么任何更新Column ca 的操作，MySQL 都需要在更新表中Column ca 的同时，也更新Column ca 的索引数据，调整因为更新所带来键值变化后的索引信息。而如果我们没有对Column ca 进行索引的话,MySQL 所需要做的仅仅只是更新表中Column ca 的信息。这样，所带来的最明显的资源消耗就是增加了更新所带来的IO量和调整索引所致的计算量。此外，Column ca 的索引idx_ta_ca 是需要占用存储空间的，而且随着Table ta 数据量的增长，idx_ta_ca 所占用的空间也会不断增长。所以索引还会带来存储空间资源消耗的增长。

1.7. 如何判定是否需要创建索引

        几点基本的判定策略来帮助我们分析是否需要创建索引。

        ◆ 较频繁的作为查询条件的字段应该创建索引；

        提高数据查询检索的效率最有效的办法就是减少需要访问的数据量，从上面所了解到的索引的益处中我们知道了，索引正是我们减少通过索引键字段作为查询条件的Query 的IO 量的最有效手段。所以一般来说我们应该为较为频繁的查询条件字段创建索引。

        ◆ 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件；

        唯一性太差的字段主要是指哪些呢？如状态字段，类型字段等等这些字段中存方的数据可能总共就是那么几个几十个值重复使用，每个值都会存在于成千上万或是更多的记录中。对于这类字段，我们完全没有必要创建单独的索引的。

        ◆ 更新非常频繁的字段不适合创建索引；

        上面在索引的弊端中我们已经分析过了，索引中的字段被更新的时候，不仅仅需要更新表中的数据，同时还要更新索引数据，以确保索引信息是准确的。这个问题所带来的是IO访问量的较大增加，不仅仅影响更新Query的响应时间，还会影响整个存储系统的资源消耗，加大整个存储系统的负载。

        当然，并不是存在更新的字段就比适合创建索引，从上面判定策略的用语上面也可以看出，是“非常频繁”的字段。到底什么样的更新频率应该算是“非常频繁”呢？每秒，每分钟，还是每小时呢？说实话，这个还真挺难定义的。很多时候还是通过比较同一时间段内被更新的次数和利用该字段作为条件的查询次数来判断，如果通过该字段的查询并不是很多，可能几个小时或者是更长才

        会执行一次，而更新反而比查询更频繁，那这样的字段肯定不适合创建索引。反之，如果我们通过该字段的查询比较频繁，而且更新并不是特别多，比如查询十几二十次或是更多才可能会产生一次更新，那我个人觉得更新所带来的附加成本也是可以接受的。

        ◆ 不会出现在WHERE 子句中的字段不该创建索引

1.8. 单键索引还是组合索引

        在很多时候，我们的WHERE 子句中的过滤条件并不只是针对于单一的某个字段，而是经常会有多个字段一起作为查询过滤条件存在于WHERE 子句中。在这种时候，我们就必须要作出判断，是该仅仅为过滤性最好的字段建立索引还是该在所有字段（过滤条件中的）上面建立一个组合索引呢？

        从上一节中我们了解到了索引在提高某些查询的性能的同时，也会让某些更新的效率下降。而组合索引中因为有多个字段的存在，理论上被更新的可能性肯定比单键索引要大很多，这样可能带来的附加成本也就比单键索引要高。但是，当我们的WHERE 子句中的查询条件含有多个字段的时候，通过这多个字段共同组成的组合索引的查询效率肯定比仅仅只用过滤条件中的某一个字段创建的索引要高。因为通过单键索引所能过滤的数据并不完整，和通过组合索引相比，

        存储引擎需要访问更多的记录数，自然就会访问更多的数据量，也就是说需要更高的IO 成本。

        可能有些朋友会说，那我们可以通过创建多个单键索引啊。确实，我们可以将WHERE 子句中的每一个字段都创建一个单键索引。但是这样真的有效吗？在这样的情况下，MySQL Query Optimizer 大多数时候都只会选择其中的一个索引，然后放弃其他的索引。即使他选择了同时利用两个或者更多的索引通

        过INDEX_MERGE 来优化查询，可能所收到的效果并不会比选择其中某一个单键索引更高效。因为如果选择通过INDEX_MERGE 来优化查询，就需要访问多个索引，同时还要将通过访问到的几个索引进行merge操作，所带来的成本可能反而会比选择其中一个最有效的索引来完成查询更高。

        在一般的应用场景中，只要不是其中某个过滤字段在大多数场景下都能过滤出90%以上的数据，而且其他的过滤字段会存在频繁的更新，我一般更倾向于创建组合索引，尤其是在并发量较高的场景下更是应该如此。因为当我们的并发量较高的时候，即使我们为每个Query 节省很少的IO 消耗，但因为执行量非常大，所节省的资源总量仍然是非常可观的。

        当然，我们创建组合索引并不是说就需要将查询条件中的所有字段都放在一个索引中，我们还应该尽量让一个索引被多个Query 语句所利用，尽量减少同一个表上面索引的数量，减少因为数据更新所带来的索引更新成本，同时还可以减少因为索引所消耗的存储空间。

        此外，MySQL 还为我们提供了一个减少优化索引自身的功能，那就是前缀索引。在MySQL 中，我们可以仅仅使用某个字段的前面部分内容做为索引键来索引该字段，来达到减小索引占用的存储空间和提高索引访问的效率。当然，前缀索引的功能仅仅适用于字段前缀比较随机重复性很小的字段。如果我们需要索引的字段的前缀内容有较多的重复，索引的过滤性自然也会随之降低，通过索引所访问的数据量就会增加，这时候前缀索引虽然能够减少存储空间消耗，但是可能会造成Query访问效率的极大降低，反而得不偿失。

1.9. Query 的索引选择

        在有些场景下，我们的Query 由于存在多个过滤条件，而这多个过滤条件可能会存在于两个或者更多的索引中。在这种场景下，MySQL Query Optimizer 一般情况下都能够根据系统的统计信息选择出一个针对该Query 最优的索引完成查询，但是在有些情况下，可能是由于我们的系统统计信息的不够准确完整，也可能是MySQL Query Optimizer 自身功能的缺陷，会造成他并没有选择一个真正最优的索引而选择了其他查询效率较低的索引。在这种时候，我们就不得不通过认为干预，在Query 中增加Hint 提示MySQL Query Optimizer 告诉他该使用哪个索引而不该使用哪个索引，或者通过调整查询条件来达到相同的目的。

        我们这里再次通过在本章第2 节“Query 语句优化基本思路和原则”的“仅仅使用最有效的过滤条件”中示例的基础上将group_message表的索引做部分调整，然后再进行分析。

        在group_message 上增加如下索引：Create index group_message_author_subject on group_message(author,subject(16));

        调整后的索引信息如下（出于篇幅考虑省略了主键索引）：

> show indexes from group_message\G

......

************************ 2. row *************************

Table: group_message

Non_unique: 1

Key_name: group_message_author_subject

Seq_in_index: 1

Column_name: author

Collation: A

Cardinality: NULL

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

*********************** 3. row *************************

Table: group_message

Non_unique: 1

Key_name: group_message_author_subject

Seq_in_index: 2

Column_name: subject

Collation: A

Cardinality: NULL

Sub_part: 16

Packed: NULL

Null:

Index_type: BTREE

Comment:

************************ 4. row *************************

Table: group_message

Non_unique: 1

Key_name: idx_group_message_uid

Seq_in_index: 1

Column_name: user_id

Collation: A

Cardinality: NULL

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

************************ 5. row *************************

Table: group_message

Non_unique: 1

Key_name: idx_group_message_author

Seq_in_index: 1

Column_name: author

Collation: A

Cardinality: NULL

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

        从索引的Sub_part 中，我们可以看到subject 字段是取前16 个字符的前缀作为索引键。下面假设我们知道某个用户的user_id ，nick_name 和subject 字段的部分前缀信息（weiurazs），希望通过这些条件查询出所有满足上面存在于group_message 中的信息。我们知道存在三个索引可以被利用：idx_group_message_author ， idx_group_message_uid 和group_message_author_subject，而且也知道每个user_id 实际上都是和一个author 分别唯一对应的。所以实际上，无论是使用user_id 和author（nick_name）中的某一个来作为条件或者两个条件都使用，所得到的数据都是完全一样的。当然，我们还需要subject LIKE 'weiurazs%' 这个条件来过滤subject 相关的信息。

        根据三个索引的组成，和我们的查询条件，我们知道group_message_author_subject 索引可以让我们得到最高的检索效率，因为只有他索引了subject 相关的信息，subject 是我们的查询必须包含的过滤条件。下面我们分别看看使用user_id ，author 和两者共同使用时候的执行计划。

> EXPLAIN SELECT * FROM group_message WHERE user_id = 3 AND subject LIKE 'weiurazs%'\G

************************ 1. row ***************************

id: 1

select_type: SIMPLE

table: group_message

type: ref

possible_keys: idx_group_message_uid

key: idx_group_message_uid

key_len: 4

ref: const

rows: 8

Extra: Using where

1 row in set (0.00 sec)

        很明显，这不是我们所期望的执行计划，当然我们并不能责怪MySQL，因为我们都没有使用author来进行过滤，Optimizer 当然不会选择group_message_author_subject 这个索引，这是我们自己的错。

> EXPLAIN SELECT * FROM group_message WHERE author = '3' AND subject LIKE 'weiurazs%'\G

************************ 1. row ***************************

id: 1

select_type: SIMPLE

table: group_message

type: range

possible_keys: group_message_author_subject,idx_group_message_author

key: idx_group_message_author

key_len: 98

ref: NULL

rows: 8

Extra: Using where

1 row in set (0.00 sec)

        这次我们改为使用author 作为查询条件了，可MySQL Query Optimizer 仍然没有选择group_message_author_subject 这个索引，即使我们通过analyze 分析也是同样的结果。

> EXPLAIN SELECT * FROM group_message WHERE user_id = 3 AND author = '3' AND subject LIKE 'weiurazs%'\G

************************ 1. row ***************************

id: 1

select_type: SIMPLE

table: group_message

type: range

possible_keys: group_message_author_subject,idx_group_message_uid,

idx_group_message_author

key: idx_group_message_uid

key_len: 98

ref: NULL

rows: 8

Extra: Using where

1 row in set (0.00 sec)

        同时使用user_id 和author 两者的时候，MySQL Query Optimizer 又再次选择了idx_group_message_uid 这个索引，仍然不是我们期望的结果。

> EXPLAIN SELECT * FROM group_message FORCE INDEX(idx_group_message_author_subject) WHERE user_id = 3 AND author = '3' AND subject LIKE 'weiurazs%'\G

************************ 1. row ***************************

id: 1

select_type: SIMPLE

table: group_message

type: range

possible_keys: group_message_author_subject

key: group_message_author_subject

key_len: 148

ref: NULL

rows: 8

Extra: Using where

        在最后，我们不得不利用MySQL 为我们提供的在优化Query 时候所使用的高级功能，通过显式告诉MySQL Query Optimizer 我们要使用哪个索引的Hint功能。强制MySQL 使用group_message_author_subject 这个索引来完成查询，才达到我们所需要的效果。或许有些读者会想，会不会是因为选择group_message_author_subject 这个索引本身就不是一个

        最优的选择呢？大家请看下面通过mysqlslap（工具说明http://blog.csdn.net/mr_mablevi/article/details/5881491） 进行的实际执行各条Query 的测试结果：

sky@sky:~$ mysqlslap --create-schema=example --query="SELECT * FROM group_message WHERE

user_id = 3 AND subject LIKE 'weiurazs%'" --iterations=10000

Benchmark

Average number of seconds to run all queries: 0.021 seconds

Minimum number of seconds to run all queries: 0.010 seconds

Maximum number of seconds to run all queries: 0.030 seconds

Number of clients running queries: 1

Average number of queries per client: 1

 

sky@sky:~$ mysqlslap --create-schema=example --query="SELECT * FROM group_message WHERE

author = '3' AND subject LIKE 'weiurazs%'" --iterations=10000

Benchmark

Average number of seconds to run all queries: 0.025 seconds

Minimum number of seconds to run all queries: 0.012 seconds

Maximum number of seconds to run all queries: 0.031 seconds

Number of clients running queries: 1

Average number of queries per client: 1

 

sky@sky:~$ mysqlslap --create-schema=example --query="SELECT * FROM group_message WHERE

user_id = 3 AND author = '3' AND subject LIKE 'weiurazs%'" --iterations=10000

Benchmark

Average number of seconds to run all queries: 0.026 seconds

Minimum number of seconds to run all queries: 0.013 seconds

Maximum number of seconds to run all queries: 0.030 seconds

Number of clients running queries: 1

Average number of queries per client: 1

 

sky@sky:~$ mysqlslap --create-schema=example --query="SELECT * FROM group_message force

index(group_message_author_subject) WHERE author = '3' subject LIKE 'weiurazs%'" --

iterations=10000

Benchmark

Average number of seconds to run all queries: 0.017 seconds

Minimum number of seconds to run all queries: 0.010 seconds

Maximum number of seconds to run all queries: 0.027 seconds

Number of clients running queries: 1

Average number of queries per client: 1

        我们可以清晰的看出，通过我们添加Hint之后选择group_message_author_subject 这个索引的Query 确实比其他的三条要快很多。通过这个示例，我们可以看出在优化Query 的时候，选择合适的索引是非常重要的，而且我们也同时实例证明了MySQL Query Optimizer 并不是任何时候都能够选择出最佳的执行计划，在有些时候，我们不得不通过人为的手工干预来让MySQL Query Optimizer 改变他的“想法”，而按照我们的思路走。

        当然，这个示例仅仅只是告诉了我们选择合适索引的重要性，并且不能任何时候都完全相信MySQL Query Optimizer，但并没有告诉我们到底该如何来选择一个更合适的索引。下面是我对于选择合适索引的几点建议，并不一定在任何场景下都合适，但在大多数场景下还是比较适用的。

        1. 对于单键索引，尽量选择针对当前Query过滤性更好的索引；

        2. 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中排列越靠前越好；

        3. 在选择组合索引的时候，尽量选择可以能够包含当前Query的WHERE 子句中更多字段的索引；

        4. 尽可能通过分析统计信息和调整Query的写法来达到选择合适索引的目的而减少通过使用Hint人为控制索引的选择，因为这会使后期的维护成本增加，同时增加维护所带来的潜在风险。

1.10. MySQL 中索引的限制

        在使用索引的同时，我们还应该了解在MySQL 中索引存在的限制，以便在索引应用中尽可能的避开限制所带来的问题。下面列出了目前MySQL 中索引使用相关的限制。

        1. MyISAM 存储引擎索引键长度总和不能超过1000 字节；

        2. BLOB 和TEXT 类型的列只能创建前缀索引；

        3. MySQL 目前不支持函数索引；

        4. 使用不等于（!= 或者<>）的时候MySQL 无法使用索引；

        5. 过滤字段使用了函数运算后（如abs(column)），MySQL 无法使用索引；

        6. Join 语句中Join 条件字段类型不一致的时候MySQL 无法使用索引；

        7. 使用LIKE 操作的时候如果条件以通配符开始（ '%abc...'）MySQL 无法使用索引；

        8. 使用非等值查询的时候MySQL无法使用Hash 索引；