合理设计并利用索引

最新推荐文章于 2024-09-29 08:44:52 发布

weixin_30909575

最新推荐文章于 2024-09-29 08:44:52 发布

阅读量80

点赞数

文章标签：数据库数据结构与算法

原文链接：http://www.cnblogs.com/piwefei/p/5408232.html

版权

索引，可以说是数据库相关优化尤其是在 Query 优化中最常用的优化手段之一了。但是很多人在大
部分时候都只是大概了解索引的用途，知道索引能够让 Query 执行的更快，而并不知道为什么会更快。
尤其是索引的实现原理，存储方式，以及不同索引之间的区别等就更不是太清楚了。正因为索引对我们
的 Query 性能影响很大，所以我们更应该深入理解 MySQL 中索引的基本实现，以及不同索引之间的区
别，才能分析出如何设计出最优的索引来最大幅度的提升 Query 的执行效率。

本人qq群也有许多的技术文档，希望可以为你提供一些帮助(非技术的勿加)。

QQ群： 281442983 (点击链接加入群：http://jq.qq.com/?_wv=1027&k=29LoD19)

在 MySQL 中，主要有四种类型的索引，分别为：B-Tree 索引，Hash 索引，Fulltext 索引和 R-
Tree 索引，下面针对这四种索引的基本实现方式及存储结构做一个大概的分析。
B-Tree 索引
B-Tree 索引是 MySQL 数据库中使用最为频繁的索引类型，除了 Archive 存储引擎之外的其他所有
的存储引擎都支持 B-Tree 索引。不仅仅在 MySQL 中是如此，实际上在其他的很多数据库管理系统中
B-Tree 索引也同样是作为最主要的索引类型，这主要是因为 B-Tree 索引的存储结构在数据库的数据检
索中有非常优异的表现。
一般来说，MySQL 中的 B-Tree 索引的物理文件大多都是以 Balance Tree 的结构来存储的，也就
是所有实际需要的数据都存放于 Tree 的 Leaf Node，而且到任何一个 Leaf Node 的最短路径的长度都
是完全相同的，所以我们大家都称之为 B-Tree 索引当然，可能各种数据库（或 MySQL 的各种存储引
擎）在存放自己的 B-Tree 索引的时候会对存储结构稍作改造。如 Innodb 存储引擎的 B-Tree 索引实
际使用的存储结构实际上是 B+Tree，也就是在 B-Tree 数据结构的基础上做了很小的改造，在每一个
Leaf Node 上面出了存放索引键的相关信息之外，还存储了指向与该 Leaf Node 相邻的后一个 Leaf
Node 的指针信息，这主要是为了加快检索多个相邻 Leaf Node 的效率考虑。
在 Innodb 存储引擎中，存在两种不同形式的索引，一种是 Cluster 形式的主键索引（Primary
Key），另外一种则是和其他存储引擎（如 MyISAM 存储引擎）存放形式基本相同的普通 B-Tree 索引，
这种索引在 Innodb 存储引擎中被称为 Secondary Index。下面我们通过图示来针对这两种索引的存放
形式做一个比较。
图示中左边为 Clustered 形式存放的 Primary Key，右侧则为普通的 B-Tree 索引。两种索引在
Root Node 和 Branch Nodes 方面都还是完全一样的。而 Leaf Nodes 就出现差异了。在 Primary Key
中，Leaf Nodes 存放的是表的实际数据，不仅仅包括主键字段的数据，还包括其他字段的数据，整个数
据以主键值有序的排列。而 Secondary Index 则和其他普通的 B-Tree 索引没有太大的差异，只是在
Leaf Nodes 出了存放索引键的相关信息外，还存放了 Innodb 的主键值。
所以，在 Innodb 中如果通过主键来访问数据效率是非常高的，而如果是通过 Secondary Index 来
访问数据的话，Innodb 首先通过 Secondary Index 的相关信息，通过相应的索引键检索到 Leaf Node
之后，需要再通过 Leaf Node 中存放的主键值再通过主键索引来获取相应的数据行。
MyISAM 存储引擎的主键索引和非主键索引差别很小，只不过是主键索引的索引键是一个唯一且非空
的键而已。而且 MyISAM 存储引擎的索引和 Innodb 的 Secondary Index 的存储结构也基本相同，主要
的区别只是 MyISAM 存储引擎在 Leaf Nodes 上面出了存放索引键信息之外，再存放能直接定位到
MyISAM 数据文件中相应的数据行的信息（如 Row Number），但并不会存放主键的键值信息。
Hash 索引
Hash 索引在 MySQL 中使用的并不是很多，目前主要是 Memory 存储引擎使用，而且在 Memory 存
储引擎中将 Hash 索引作为默认的索引类型。所谓 Hash 索引，实际上就是通过一定的 Hash 算法，将
需要索引的键值进行 Hash 运算，然后将得到的 Hash 值存入一个 Hash 表中。然后每次需要检索的时
候，都会将检索条件进行相同算法的 Hash 运算，然后再和 Hash 表中的 Hash 值进行比较并得出相应
的信息。
在 Memory 存储引擎中，MySQL 还支持非唯一的 Hash 索引。可能很多人会比较惊讶，如果是非唯
一的 Hash 索引，那相同的值该如何处理呢？在 Memory 存储引擎的 Hash 索引中，如果遇到非唯一
值，存储引擎会将他们链接到同一个 hash 键值下以一个链表的形式存在，然后在取得实际键值的时候
时候再过滤不符合的键。
由于 Hash 索引结构的特殊性，其检索效率非常的高，索引的检索可以一次定位，而不需要像 B-
Tree 索引需要从根节点再到枝节点最后才能访问到页节点这样多次 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 值相关连。这样要定位某一条记录的时候就会非常的麻烦，可能会浪费非常
多次表数据的访问，而造成整体性能的地下。
Full-text 索引
Full-text 索引也就是我们常说的全文索引，目前在 MySQL 中仅有 MyISAM 存储引擎支持，而且也
并不是所有的数据类型都支持全文索引。目前来说，仅有 CHAR，VARCHAR 和 TEXT 这三种数据类型的列可
以建 Full-text 索引。
一般来说，Fulltext 索引主要用来替代效率低下的 LIKE '%***%' 操作。实际上，Full-text 索引
并不只是能简单的替代传统的全模糊 LIKE 操作，而且能通过多字段组合的 Full-text 索引一次全模糊
匹配多个字段。
Full-text 索引和普通的 B-Tree 索引的实现区别较大，虽然他同样是以 B-Tree 形式来存放索引
数据，但是他并不是通过字段内容的完整匹配，而是通过特定的算法，将字段数据进行分隔后再进行的
索引。一般来说 MySQL 系统会按照四个字节来分隔。在整个 Full-text 索引中，存储内容被分为两部
分，一部分是分隔前的索引字符串数据集合，另一部分是分隔后的词（或者词组）的索引信息。所以，
Full-text 索引中，真正在 B-Tree 索引细细中的并不是我们表中的原始数据，而是分词之后的索引数
据。在 B-Tree 索引的节点信息中，存放了各个分隔后的词信息，以及指向包含该词的分隔前字符串信
息在索引数据集合中的位置信息。
Full-text 索引不仅仅能实现模糊匹配查找，在实现了基于自然语言的的匹配度查找。当然，这个
匹配读到底有多准确就需要读者朋友去自行验证了。Full-text 通过一些特定的语法信息，针对自然语
言做了各种相应规则的匹配，最后给出非负的匹配值。
此外，有一点是需要大家注意的，MySQL 目前的 Full-text 索引在中文支持方面还不太好，需要借
助第三方的补丁或者插件来完成。而且 Full-text 的创建所消耗的资源也是比较大的，所以在应用于实
际生产环境之前还是尽量做好评估。
关于 Full-text 的实际使用方法由于不是本书的重点，感兴趣的读者朋友可以自行参阅 MySQL 关
于 Full-text 相关的使用手册来了解更为详尽的信息。
R-Tree 索引
R-Tree 索引可能是我们在其他数据库中很少见到的一种索引类型，主要用来解决空间数据检索的问
题。
在 MySQL 中，支持一种用来存放空间信息的数据类型 GEOMETRY，且基于 OpenGIS 规范。在
MySQL5.0.16 之前的版本中，仅仅 MyISAM 存储引擎支持该数据类型，但是从 MySQL5.0.16 版本开始，
BDB，Innodb，NDBCluster 和 Archive 存储引擎也开始支持该数据类型。当然，虽然多种存储引擎都开
始支持 GEOMETRY 数据类型，但是仅仅之后 MyISAM 存储引擎支持 R-Tree 索引。
在 MySQL 中采用了具有二次分裂特性的 R-Tree 来索引空间数据信息，然后通过几何对象（MRB）
信息来创建索引。
虽然仅仅只有 MyISAM 存储引擎支持空间索引（R-Tree Index），但是如果我们是精确的等值匹
配，创建在空间数据上面的 B-Tree 索引同样可以起到优化检索的效果，空间索引的主要优势在于当我
们使用范围查找的时候，可以利用到 R-Tree 索引，而这时候，B-Tree 索引就无能为力了。
对于 R-Tree 索引的详细介绍和使用信息清参阅 MySQL 使用手册。
索引的利弊与如何判定是否需要索引
相信没一位读者朋友都知道索引能够极大的提高我们数据检索的效率，让我们的 Query 执行的更
快，但是可能并不是每一位朋友都清楚索引在极大提高检索效率的同时，也给我们的数据库带来了一些
负面的影响。下面我们就分别对 MySQL 中索引的利与弊做一个简单的分析。
索引的利处
索引能够给我们带来的最大益处可能读者朋友基本上都有一定的了解，但是我相信并不是每一位读
者朋友都能够了解的比较全面。很多朋友对数据库中的索引的认识可能主要还是只限于“能够提高数据
检索的效率，降低数据库的 IO 成本”。
确实，在数据库中个表的某个字段创建索引，所带来的最大益处就是将该字段作为检索条件的时候
可以极大的提高检索效率，加快检索时间，降低检索过程中所需要读取的数据量。但是索引所给我们带
来的收益只是提高表数据的检索效率吗？当然不是，索引还有一个非常重要的用途，那就是降低数据的
排序成本。
我们知道，每个索引中索引数据都是按照索引键键值进行排序后存放的，所以，当我们的 Query 语
句中包含排序分组操作的时候，如果我们的排序字段和索引键字段刚好一致，MySQL Query Optimizer
就会告诉 mysqld 在取得数据之后不用排序了，因为根据索引取得的数据已经是满足客户的排序要求。
那如果是分组操作呢？分组操作没办法直接利用索引完成。但是分组操作是需要先进行排序然后才
分组的，所以当我们的 Query 语句中包含分组操作，而且分组字段也刚好和索引键字段一致，那么
mysqld 同样可以利用到索引已经排好序的这个特性而省略掉分组中的排序操作。
排序分组操作主要消耗的是我们的内存和 CPU 资源，如果我们能够在进行排序分组操作中利用好索
引，将会极大的降低 CPU 资源的消耗。
索引的弊端
索引的益处我们都已经清楚了，但是我们不能光看到索引给我们带来的这么多益处之后就认为索引
是解决 Query 优化的圣经，只要发现 Query 运行不够快就将 WHERE 子句中的条件全部放在索引中。
确实，索引能够极大的提高数据检索效率，也能够改善排序分组操作的性能，但是我们不能忽略的
一个问题就是索引是完全独立于基础数据之外的一部分数据。假设我们在 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 所占用的空间也会
不断增长。所以索引还会带来存储空间资源消耗的增长。
如何判定是否需要创建索引
在了解了索引的利与弊之后，我们知道了索引并不是越多越好，知道了索引也是会带来副作用的。
那我们到底该如何来判断某个索引是否应该创建呢？
实际上，并没有一个非常明确的定律可以清晰的定义出什么字段应该创建索引什么字段不该创建索
引。因为我们的应用场景实在是太复杂，存在太多的差异。当然，我们还是仍然能够找到几点基本的判
定策略来帮助我们分析是否需要创建索引。
◆ 较频繁的作为查询条件的字段应该创建索引；
提高数据查询检索的效率最有效的办法就是减少需要访问的数据量，从上面所了解到的索引的
益处中我们知道了，索引正是我们减少通过索引键字段作为查询条件的 Query 的 IO 量的最有
效手段。所以一般来说我们应该为较为频繁的查询条件字段创建索引。
◆ 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件；
唯一性太差的字段主要是指哪些呢？如状态字段，类型字段等等这些字段中存方的数据可能总
共就是那么几个几十个值重复使用，每个值都会存在于成千上万或是更多的记录中。对于这类
字段，我们完全没有必要创建单独的索引的。因为即使我们创建了索引，MySQL Query
Optimizer 大多数时候也不会去选择使用，如果什么时候 MySQL Query Optimizer 抽了一下风
选择了这种索引，那么非常遗憾的告诉你，这可能会带来极大的性能问题。由于索引字段中每
个值都含有大量的记录，那么存储引擎在根据索引访问数据的时候会带来大量的随机 IO，甚至
有些时候可能还会出现大量的重复 IO。
这主要是由于数据基于索引扫描的特点所引起的。当我们通过索引访问表中的数据的时候，
MySQL 会按照索引键的键值的顺序来依序进行访问。一般来说每个数据页中大都会存放多条记
录，但是这些记录可能大多数都不会是和你所使用的索引键的键值顺序一致。
假如有以下场景，我们通过索引查找键值为 A 和 B 的某些数据。当我们先通过 A 键值找到第一
条满足要求的记录后，我们会读取这条记录所在的 X 数据页，然后我们继续往下查找索引，发
现 A 键值所对应的另外一条记录也满足我们的要求，但是这条记录不在 X 数据页上面，而在
Y 数据页上面，这时候存储引擎就会丢弃 X 数据页，而读取 Y 数据页。如此继续一直到查找
完 A 键值所对应的所有记录。然后轮到 B 键值了，这时候发现正在查找的记录又在 X 数据页
上面，可之前读取的 X 数据页已经被丢弃了，只能再次读取 X 数据页。这时候，实际上已经
出现重复读取 X 数据页两次了。在继续往后的查找中，可能还会出现一次又一次的重复读取。
这无疑极大的给存储引擎增大了 IO 访问量。
不仅如此，如果一个键值对应了太多的数据记录，也就是说通过该键值会返回占整个表比例很
大的记录的时候，由于根据索引扫描产生的都是随机 IO，其效率比进行全表扫描的顺序 IO 的
效率要差很多，即使不会出现重复 IO 的读取，同样会造成整体 IO 性能的下降。
很多比较有经验的 Query 调优专家经常说，当一条 Query 所返回的数据超过了全表的 15% 的
时候，就不应该再使用索引扫描来完成这个 Query 了。对于“15%”这个数字我们并不能判定
是否很准确，但是之少侧面证明了唯一性太差的字段并不适合创建索引。
◆ 更新非常频繁的字段不适合创建索引；
上面在索引的弊端中我们已经分析过了，索引中的字段被更新的时候，不仅仅需要更新表中的
数据，同时还要更新索引数据，以确保索引信息是准确的。这个问题所带来的是 IO 访问量的较大
增加，不仅仅影响更新 Query 的响应时间，还会影响整个存储系统的资源消耗，加大整个存储系统
的负载。
当然，并不是存在更新的字段就比适合创建索引，从上面判定策略的用语上面也可以看出，是
“非常频繁”的字段。到底什么样的更新频率应该算是“非常频繁”呢？每秒，每分钟，还是每小
时呢？说实话，这个还真挺难定义的。很多时候还是通过比较同一时间段内被更新的次数和利用该
字段作为条件的查询次数来判断，如果通过该字段的查询并不是很多，可能几个小时或者是更长才
会执行一次，而更新反而比查询更频繁，那这样的字段肯定不适合创建索引。反之，如果我们通过
该字段的查询比较频繁，而且更新并不是特别多，比如查询十几二十次或是更多才可能会产生一次
更新，那我个人觉得更新所带来的附加成本也是可以接受的。
◆ 不会出现在 WHERE 子句中的字段不该创建索引；
不会还有人会问为什么吧？自己也觉得这是废话了，哈哈！
单键索引还是组合索引
在大概了解了一下 MySQL 各种类型的索引以及索引本身的利弊与判断一个字段是否需要创建索引之
后，我们就需要着手创建索引来优化我们的 Query 了。在很多时候，我们的 WHERE 子句中的过滤条件
并不只是针对于单一的某个字段，而是经常会有多个字段一起作为查询过滤条件存在于 WHERE 子句中。
在这种时候，我们就必须要作出判断，是该仅仅为过滤性最好的字段建立索引还是该在所有字段（过滤
条件中的）上面建立一个组合索引呢？
对于这种问题，很难有一个绝对的定论，我们需要从多方面来分析考虑，平衡两种方案各自的优
劣，然后选择一种最佳的方案来解决。因为从上一节中我们了解到了索引在提高某些查询的性能的同
时，也会让某些更新的效率下降。而组合索引中因为有多个字段的存在，理论上被更新的可能性肯定比
单键索引要大很多，这样可能带来的附加成本也就比单键索引要高。但是，当我们的 WHERE 子句中的查
询条件含有多个字段的时候，通过这多个字段共同组成的组合索引的查询效率肯定比仅仅只用过滤条件
中的某一个字段创建的索引要高。因为通过单键索引所能过滤的数据并不完整，和通过组合索引相比，
存储引擎需要访问更多的记录数，自然就会访问更多的数据量，也就是说需要更高的 IO 成本。
可能有些朋友会说，那我们可以通过创建多个单键索引啊。确实，我们可以将 WHERE 子句中的每一
个字段都创建一个单键索引。但是这样真的有效吗？在这样的情况下，MySQL Query Optimizer 大多数
时候都只会选择其中的一个索引，然后放弃其他的索引。即使他选择了同时利用两个或者更多的索引通
过 INDEX_MERGE 来优化查询，可能所收到的效果并不会比选择其中某一个单键索引更高效。因为如果选
择通过 INDEX_MERGE 来优化查询，就需要访问多个索引，同时还要将通过访问到的几个索引进行 merge
操作，所带来的成本可能反而会比选择其中一个最有效的索引来完成查询更高。
在一般的应用场景中，只要不是其中某个过滤字段在大多数场景下都能过滤出90%以上的数据，而且
其他的过滤字段会存在频繁的更新，我一般更倾向于创建组合索引，尤其是在并发量较高的场景下更是
应该如此。因为当我们的并发量较高的时候，即使我们为每个 Query 节省很少的 IO 消耗，但因为执行
量非常大，所节省的资源总量仍然是非常可观的。
当然，我们创建组合索引并不是说就需要将查询条件中的所有字段都放在一个索引中，我们还应该
尽量让一个索引被多个 Query 语句所利用，尽量减少同一个表上面索引的数量，减少因为数据更新所带
来的索引更新成本，同时还可以减少因为索引所消耗的存储空间。
此外，MySQL 还为我们提供了一个减少优化索引自身的功能，那就是前缀索引。在 MySQL 中，我们
可以仅仅使用某个字段的前面部分内容做为索引键来索引该字段，来达到减小索引占用的存储空间和提
高索引访问的效率。当然，前缀索引的功能仅仅适用于字段前缀比较随机重复性很小的字段。如果我们
需要索引的字段的前缀内容有较多的重复，索引的过滤性自然也会随之降低，通过索引所访问的数据量
就会增加，这时候前缀索引虽然能够减少存储空间消耗，但是可能会造成 Query 访问效率的极大降低，
反而得不偿失。
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));
调整后的索引信息如下（出于篇幅考虑省略了主键索引）：
sky@localhost : example 07:13:38> 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 和两者共同使用时候的执行计划。
sky@localhost : example 07:48:45> 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 这个索引，这是我们自己的
错。
sky@localhost : example 07:48:49> 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 分析也是同样的结果。
sky@localhost : example 07:48:57> 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 这个索引，仍然不是我们期望的结果。
sky@localhost : example 07:51:11> 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 进行的实际执行各条 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 人为控制索引的选择，因为这会使后期的维护成本增加，同时增加维护所带来的潜在风险。
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 索引；
9.
在我们使用索引的时候，需要注意上面的这些限制，尤其是要注意无法使用索引的情况，因为这很
容易让我们因为疏忽而造成极大的性能隐患。