MySQL查询优化技术使用索引

1、MySQL查询优化技术使用索引添加时间：2007-4-22索引是提高查询速度的最重要的工具。当然还有其它的一些技术可供使用，但是一般来说引起最大性能差异的都是索引的正确使用。在MySQL邮件列表中，人们经常询问那些让查询运行得更快的方法。在大多数情况下，我们应该怀疑数据表上 有没有索引，并且通常在添加索引之后立即解决了问题。当然，并不总是这样简单就 可以解决问题的，因为优化技术本来就并非总是简单的。然而，如果没有使用索引，在 很多情况下，你试图使用其它的方法来提高性能都是在浪费时间。首先使用索引来 获取最大的性能提高，接着再看其它的技术是否有用。这一部分讲述了索引是什么以及索引是怎么样提高查询

2、性能的。它还讨论了在 某些环境中索引可能降低性能，并为你明智地选择数据表的索引提供了一些指导方 针。在下一部分中我们将讨论 MySQL查询优化器，它试图找到执行查询的效率最高 的方法。了解一些优化器的知识，作为对如何建立索引的补充，对我们是有好处的，因 为这样你才能更好地利用自己所建立的索引。某些编写查询的方法实际上让索引不 起作用，在一般情况下你应该避免这种情形的发生。索引的优点让我们开始了解索引是如何工作的，首先有一个不带索引的数据表。不带索引 的表仅仅是一个无序的数据行集合。例如，图1显示的ad表就是不带索引的表，因此 如果需要查找某个特定的公司，就必须检查表中的每个数据行看它是否与目标

3、值相 匹配。这会导致一次完全的数据表扫描，这个过程会很慢，如果这个表很大，但是只包 含少量的符合条件的记录，那么效率会非常低。图1:无索引的ad表图2是同样的一张数据表，但是增加了对ad表的company_num数据列的索引。 这个索引包含了 ad表中的每个数据行的条目，但是索引的条目是按照company_num 值排序的。现在 我们不是逐行查看以搜寻匹配的数据项，而是使用索引。假设我们查找公司13的所有数据行。我们开始扫描索引并找到了该公司的三个值。接着我们碰到了公司14的索引值,它比我们正在搜寻的值大。索引值是排过序的，因此当我 们读取了包含14的索引记录的时候，我们就知道再也不会有更多的

4、匹配记录，可以结 束查询操作了。因此使用索引获得的功效是：我们找到了匹配的数据行在哪儿终止， 并能够忽略其它的数据行。另一个功效来自使用定位算法查找第一条匹配的条目，而不需要从索引头开始执行线性扫描（例如，二分搜索就比线性扫描要快一些。通过 使用这种方法，我们可以快速地定位第一个匹配的值，节省了大量的搜索时间。数据 库使用了多种技术来快速地定位索引值，但是在本文中我们不关心这些技术。重点 是它们能够实现，并且索引是个好东西。图2:索引后的ad表你可能要问我们为什么不对数据行进行排序从而省掉索引 ？这样不是也能实现 同样的搜索速度的改善吗？是的，如果表只有一个索引，这样做也可能达到相同的效 果。

5、但是你可能添加第二个索引，那么就无法一次使用两种不同方法对数据行进行 排序了（例如，你可能希望在顾客名称上建立一个索引，在顾客ID号或电话号码上建立另外一个索 引。把与数据行相分离的条目作为索引解决了这个问题，允许我们创建多个索引。此外，索引中的行一般也比数据行短一些。当你插入或删除新的值的时候，移动较短的索引值比移动较长数据行的排序次序更加容易。不同的MySQL存储引擎的索引实现的具体细节信息是不同的。例如，对于 MylSAM数据表，该表的数据行保存在一个数据文件中，索引信保存在索引文件中。 一个数据表上可能有多个索引，但是它们都被存储在同一个索引文件中。索引文件 中的每个索引都包含一个排序

6、的键记录（它用于快速地访问数据文件数组。与此形成对照的是，BDB和InnoDB存储引擎没有使用这种方法来分离数据行 和索引值，尽管它们也把索引作为排序后的值集合进行操作。在默认情况下,BDB引擎使用单个文件存储数据和索引值。InnoDB使用单个数据表空间（tablespace在表 空间中管理所有InnoDB表的数据和索引存储。我们可以把InnoDB配置为每个表都在自己的表空间中创建，但是即使是这样，数据表的数据和索引也存储在同一个表 空间文件中前面的讨论描述了单个表查询环境下的索引的优点，在这种情况下，通过减少对 整个表的扫描，使用索引明显地提高了搜索的速度。当你运行涉及多表联结(jion查询

7、的时候 索引的价值就更高了。在单表查询中，你需要在每个数据列上检查的值的 数量是表中数据行的数量。在多表查询中，这个数量可能大幅度上升，因为这个数量 是这些表中数据行的数量所产生的。假设你拥有三个未索引的表t1、t2和t3,每个表都分别包含数据列i1、i2和i3, 并且每个表都包含了 1000条数据行，其序号从1到1000。查找某些值匹配的数据行 组合的查询可能如下所示：SELECT t1.i1, t2.i2, t3.i3FROM t1, t2, t3WHERE t1.i1 = t2.i2 AND t2.i1 = t3.i3;这个查询的结果应该是1000行，每个数据行包含三个相等的值。如果在没

8、有索 引的情况下处理这个查询，那么如果我们不对这些表进行全部地扫描，我们是没有办 法知道哪些数据行含有哪些值的。因此你必须尝试所有的组合来查找符合WHERE条件的记录。可能的组合的数量是 1000 x 1000 x 1000(10亿!,它是匹配记录的数量 的一百万倍。这就浪费了大量的工作。这个例子显示，如果没有使用索引，随着表的记录不断增长，处理这些表的联结所花费的时间增长得更快，导致性能很差。我们可 以通过索引这些数据表来显著地提高速度，因为索引让查询采用如下所示的方式来 处理：.选择表t1中的第一行并查看该数据行的值。.使用表t2上的索引，直接定位到与t1的值匹配的数据行。类似地，使用表t

9、3上 的索引，直接定位到与表t2的值匹配的数据行。.处理表t1的下一行并重复前面的过程。执行这样的操作直到t1中的所有数据行都被检查过。在这种#况下我们仍然对表t1执行了完整的扫描，但是我们可以在t2和t3上 执行索引查找，从这些表中直接地获取数据行。理论上采用这种方式运行上面的查 询会快一百万倍。当然这个例子是为了得出结论来人为建立的。然而，它解决的问题却是现实的，给没有索引的表添加索引通常会获得惊人的性能提高。MySQL有几种使用索引的方式：如上所述，索引被用于提高 WHERE条件的数据行匹配或者执行联结操作时匹 配其它表的数据行的搜索速度。对于使用了 MIN（或MAX（函数的查询，索引数

10、据列中最小或最大值可以很快 地找到，不用检查每个数据行。MySQL利用索引来快速地执行 ORDER BY和GROUP BY语句的排序和分 组操作。有时候MySQL会利用索引来读取查询得到的所有信息。假设你选择了 MylSAM表中的被索引的数值列，那么就不需要从该数据表中选择其它的数据列。 在这种情况下，MySQL从索引文件中读取索引值，它所得到的值与读取数据文件得到 的值是相同的。没有必要两次读取相同的值，因此没有必要考虑数据文件。索引的代价一般来说，如果MySQL能够找到方法，利用索引来更快地处理查询，它就会这样 做。这意味着，对于大多数情况，如果你没有对表进行索引，就会使性能受到损害。这

11、就是我所描绘的索引优点的美景。但是它有缺点吗 ？有的，它在时间和空间上都有开 销。在实践中 索引的优点的价值一般会超过这些缺点，但是你也应该知道到底有一 些什么缺点。首先，索引加快了检索的速度，但是减慢了插入和删除的速度，同时还减慢了更新 被索引的数据列中的值的速度。也就是说，索引减慢了大多数涉及写操作的速度。 发生这种现象的原因在于写入一条记录的时候不但需要写入数据行，还需要改变所有的索引。数据表带有的索引越多，需要做出的修改就越多，平均性能的降低程度也 就越大。在本文的“高效率载入数据部分中，我们将更细致地了解这些现象并找出 处理方法。其次,索引会花费磁盘空间，多个索引相应地花费更多的磁盘

12、空间。这可能导致 更快地到达数据表的大小限制：对于MylSAM表，频繁地索引可能引起索引文件比数据文件更快地达到最大限 制。对于BDB表，它把数据和索引值一起存储在同一个文件中，添加索引引起这种 表更快地达到最大文件限制。在InnoDB的共享表空间中分配的所有表都竞争使用相同的公共空间池 ，因此 添加索引会更快地耗尽表空间中的存储。但是 ，与MylSAM和BDB表使用的文件 不同,InnoDB共享表空间并不受操作系统的文件大小限制，因为我们可以把它配置成 使用多个文件。只要有额外的磁盘空间，你就可以通过添加新组件来扩展表空问。使用单独表空间的InnoDB表与BDB表受到的约束是一样的，因为它的

13、数据和 索引值都存储在单个文件中。这些要素的实际含义是：如果你不需要使用特殊的索引帮助查询执行得更快，就 不要建立索引。选择索引假设你已经知道了建立索引的语法，但是语法不会告诉你数据表应该如何索 引。这要求我们考虑数据表的使用方式。这一部分指导你如何识别出用于索引的备 选数据列，以及如何最好地建立索引：用于搜索、排序和分组的索引数据列并不仅仅是用于输出显示的。换句话说，用于索引的最好的备选数据列是那些出现在WHERE子句、join子句、ORDER BY或GROUP BY子句中的列。仅仅出现在 SELECT关键字后面的输出数据列列表中 的数据列不是很好的备选列：SELECTcol_a -不是备选

14、列FROMtbll LEFT JOIN tbl2ON tbl1.col_b = tbl2.col_c -备选列WHEREcol_d = expr; -备选歹 U当然，显示的数据列与 WHERE子句中使用的数据列也可能相同。我们的观点 是输出列表中的数据列本质上不是用于索引的很好的备选列。Join子句或 WHERE子句中类似col1 = col2形式的表达式中的数据列都是特别 好的索引备选列。前面显示的查询中的col_b和col_c就是这样的例子。如果MySQL能够利用联结列来优化查询，它一定会通过减少整表扫描来大幅度减少潜在 的表-行组合。考虑数据列的基数（cardinality。基数是数据列

15、所包含的不同值的数量。例如 ，某 个数据列包含值1、3、7、4、7、3,那么它的基数就是4。索引的基数相对于数据 表行数较高（也就是说，列中包含很多不同的值，重复的值很少的时候，它的工作效果 最好。如果某数据列含有很多不同的年龄，索引会很快地分辨数据行。如果某个数 据列用于记录性别（只有M和F两种值，那么索引的用处就不大。如果值出现的几 率几乎相等，那么无论搜索哪个值都可能得到一半的数据行。在这些情况下，最好根 本不要使用索引，因为查询优化器发现某个值出现在表的数据行中的百分比很高的 时候,它一般会忽略索引，进行全表扫描。惯用的百分比界线是“30%”。现在查询优化器更加复杂，把其它一些因素也考

16、虑进去了 ，因此这个百分比并不是MySQL决定 选择使用扫描还是索引的唯一因素。索引较短的值。尽可能地使用较小的数据类型。例如 ，如果MEDIUMINT足够 保存你需要存储的值，就不要使用BIGINT数据列。如果你的值不会长于25个字符, 就不要使用CHAR（100。较小的值通过几个方面改善了索引的处理速度 ：较短的值可以更快地进行比较，因此索引的查找速度更快了。较小的值导致较小的索引，需要更少的磁盘I/O。使用较短的键值的时候，键缓存中白索引块（block可以保存更多的键值。 MySQL可以在内存中一次保持更多的键，在不需要从磁盘读取额外的索引块的情况 下，提高键值定位的可能性。对于Inno

17、DB和BDB等使用聚簇索引（clustered index的存储引擎来说 保持主 键（primary key短小的优势更突出。聚簇索引中数据行和主键值存储在一起（聚簇在一起。其它的索引都是次级索引；它们存储主键值和次级索引值。次级索引屈从主 键值，它们被用于定位数据行。这暗示主键值都被复制到每个次级索引中，因此如果主键值很长，每个次级索引就需要更多的额外空间。索引字符串值的前缀（prefixe。如果你需要索引一个字符串数据列，那么最好在 任何适当的情况下都应该指定前缀长度。例如，如果有CHAR（200数据列，如果前面 10个或20个字符都不同，就不要索引整个数据列。索引前面10个或20个字符会

18、节 省大量的空间，并且可能使你的查询速度更快。通过索引较短的值，你可以获得那些与比较速度和磁盘I/O节省相关的好处。当然你也需要利用常识。仅仅索引某个数 据列的第一个字符串可能用处不大，因为如果这样操作，那么在索引中不会有太多的 唯一值。你可以索弓 I CHAR、VARCHAR、BINARY、VARBINARY、BLOB 和 TEXT数据列的前缀使用最左（leftmost前缀。建立多列复合索引的时候，你实际上建立了 MySQL可 以使用的多个索引。复合索引可以作为多个索引使用，因为索引中最左边的列集合都可以用于匹配数据行。这种列集合被称为“最左前缀”（它与索引某个列的前缀不 同，那种索引把某个

19、列的前面几个字符作为索引值。假设你在表的state city和zip数据列上建立了复合索引。索引中的数据行按 照state/city/zip次序排列，因此它们也会自动地按照state/city和state次序排列。这 意味着，即使你在查询中只指定了 state值，或者指定state和city值,MySQL也可以使 用这个索引。因此，这个索引可以被用于搜索如下所示的数据列组合 ：state, city, zipstate, citystateMySQL不能利用这个索引来搜索没有包含在最左前缀的内容。例如，如果你按照city或zip来搜索，就不会使用到这个索引。如果你搜索给定的state和具体的Z

20、IP代码（索引的1和3列,该索引也是不能用于这种组合值的，尽管MySQL可以利用索 引来查找匹配的state从而缩小搜索的范围。不要过多地索引。不要认为索引越多，性能越高”，不要对每个数据列都进行索 引。我们在前面提到过，每个额外的索引都会花费更多的磁盘空间，并降低写操作的 性能。当你修改表的内容的时候，索引就必须被更新，甚至可能重新整理。如果你的索引很少使用或永不 使用，你就没有必要减小表的修改操作的速度。止匕外，为检索操作生成执行计划的时候，MySQL会考虑索引。建立额外的索引会给查询优化器增 加更多的工作量。如果索引太多，有可能（未必）出现MySQL选择最优索引失 败的情况。维护自己必须

21、的索引可以帮助查询优化器来避免这类错误。如果你考虑给已经索引过的表添加索引，那么就要考虑你将增加的索引是否是已有的多列索引的最左前缀。如果是这样的，不用增加索引，因为已经有了（例如，如果你在state city和zip上建立了索引，那么没有必要再增加 state的索引）。让索引类 型与你所执行的比较的类型相匹配。在你建立索引的时候，大多数存储引擎会选择它们将使用的索引实现。例如，InnoDB通常使用B树索引。MySQL也使用B 树索引，它只在三维数据类型上使用 R树索引。但是，MEMORY存储引擎支持 散列索引和B树索引，并允 许你选择使用哪种索引。为了选择索引类型，需要考 虑在索引数据列上将执行的比较操作类 型：对于散列（hash）索引，会在每个数据列值上应用散列函数。生成的结果散列值存储 在索引中，并用于执行查询。散 列函数实现的算法类似于为不同的输入值生成不同的散列值。使用散列值的好处是散列值比原始值的比较效率更高。散列索引用于执行=或=操作等精确匹配的 时候速度非常快。但是对于查询一个值的范围效果就非常差了：id 30 weightBETWEEN