mysql查询索引的正确使用

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

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

3、，但是只包含少量的符合条件的记录，那么效率会非常低。adlablscoupanynumadnunhn3共00123相A0217DOI峙钟682362Q.OZ23aoi23us1323*03i4加1317119呼图 1：无索引的 ad 表图 2 是同样的一张数据表， 但是增加了对 ad 表的 company_nu 做据歹 U 的索引。这个索引包含了 ad 表中的每个数据行的条目，但是索引的条斤是按照 company_nunfi排序的。现在，我们不是逐行查看以搜寻匹配的数据项，而是使用索引。加设我们查找公司 13 的所有数据行。我们开始扫描索引并找到了该公司的三个值。接着我们碰到了公司 14 的索

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

5、行排序从而省掉索引？这样不是也能实现同样的搜索速度的改善吗？是的， 如果表只有一个索引， 这样做也可能达到相同的效果。但是你可能添加第二个索引，那么就无法一次使用两种不同方法对数据行进行排序了(例如，你可能希望在顾客名称上建立一个索引，在顾客 ID 号或电话号码上建立另外一个索引)。把与数据行相分离的条目作为索引解决了这个问题，允许我们创建多个索引。此外，索引中的行一般也比数据行短一些。当你插入或删除新的值的时候，移动较短的索引值比移动较长数据行的排序次序更加容易。不同的 MySQI#储引擎的索引实现的具体细节信息是不同的。例如，对于MylSA 微据表，该表的数据行保存在一个数据文件中，索引信

6、保存在索引文件中。一个数据表上可能有多个索引，但是它们都被存储在同一个索引文件中。索引文件中的每个索引都包含一个排序的键记录(它用于快速地访问数据文件)数组。与此形成对照的是， BDDlnnoDB 存储引擎没有使用这种方法来分离数据行和索引值，尽管它们也把索引作为排序后的值集合进行操作。在默认情况下，BDB 引擎使用单个文件存储数据和索引值。InnoDB 使用单个数据表空间(tablespace),在表空间中管理所有 InnoDB 表的数据和索引存储。我们可以把 InnoDB配置为每个表都在自己的表空间中创建，但是即使是这样，数据表的数据和索引也存储在同一个表空间文件中。前面的讨论描述了单个表

7、查询环境下的索引的优点，在这种情况下，通过减少对整个表的扫描，使用索引明显地提高了搜索的速度。当你运行涉及多表联结(jion)查询的时候，索引的价值就更高了。在单表查询中，你需要在每个数据列上检查的值的数量是表中数据行的数量。在多表查询中，这个数量可能大幅度上升，因为这个数量是这些表中数据行的数量所产生的。假设你拥有三个未索引的表 t1、t2 和 t3,每个表都分别包含数据列 i1、i2 和 i3,并且每个表都包含了 1000 条数据行，其序号从 1 到 1000。查找某些值匹配的数据行组合的查询可能如下所示：SELECTt1.i1,t2.i2,t3.i3FROMt1,t2,t3WHEREt1

8、.i1=t2.i2ANDt2.i1=t3.i3;这个查询的结果应该是 1000 行， 每个数据行包含三个相等的值。 如果在没有索引的情况下处理这个查询，那么如果我们不对这些表进行全部地扫描，我们是没有办法知道哪些数据行含有哪些值的。 因此你必须尝试所有的组合来查找符合WHERE件的记录。可能的组合的数量是 1000 x1000 x1000(10 亿！)，它是匹配记录的数量的一百万倍。这就浪费了大量的工作。这个例子显示，如果没有使用索引，随着表的记录不断增长，处理这些表的联结所花费的时间增长得更快，导致性能很差。我们可以通过索引这些数据表来显著地提高速度，因为索引让查询采用如下所示的方式来处理：

9、1 .选择表 t1 中的第一行并查看该数据行的值。2 .使用表 t2 上的索引，直接定位到与 t1 的值匹配的数据行。类似地，使用表 t3上的索引，直接定位到与表 t2 的值匹配的数据行。3 .处理表 t1 的下一行并重复前面的过程。执行这样的操作直到 t1 中的所有数据行都被检查过。在这种情况下，我们仍然对表 t1 执行了完整的扫描，但是我们可以在 t2 和 t3 上执行索引查找，从这些表中直接地获取数据行。理论上采用这种方式运行上面的查询会快一百万倍。当然这个例子是为了得出结论来人为建立的。然而，它解决的问题却是现实的，给没有索引的表添加索引通常会获得惊人的性能MySQlLT 几种使用索引

10、的方式： 如上所述，索引被用于提高 WHERE 件的数据行匹配或者执行联结操作时匹配其它表的数据行的搜索速度。 对于使用了 MIN()或 MAX()函数的查询，索引数据列中最小或最大值可以很快地找到，不用检查每个数据行。 MySQL 利用索引来快速地执行 ORDERBY 口 GROUPBY 句的排序和分组操作。有时候 MySQL 会利用索引来读取查询得到的所有信息。假设你选择了MyISAMft 中的被索引的数值列，那么就不需要从该数据表中选择其它的数据列。在这种情况下， MySQLM 索引文件中读取索引值， 它所得到的值与读取数据文件得到的值是相同的。没有必要两次读取相同的值，因此没有必要考虑

11、数据文件。索引的代价一般来说， 如果 MySQ 能够找到方法， 利用索引来更快地处理查询， 它就会这样做。这意味着，对于大多数情况，如果你没有对表进行索引，就会使性能受到损害。这就是我所描绘的索引优点的美景。但是它有缺点吗？有的，它在时间和空间上都有开销。在实践中，索引的优点的价值一般会超过这些缺点，但是你也应该知道到底有一些什么缺点。首先，索引加快了检索的速度，但是减慢了插入和删除的速度，同时还减慢了更新被索引的数据列中的值的速度。也就是说，索引减慢了大多数涉及写操作的速度。发生这种现象的原因在于写入一条记录的时候不但需要写入数据行,还需要改变所有的索引。数据表带有的索引越多，需要做出的修改

12、就越多，平均性能的降低程度也就越大。在本文的“高效率载入数据部分中，我们将更细致地了解这些现象并找出处理方法。其次，索引会花费磁盘空间，多个索引相应地花费更多的磁盘空间。这可能导致更快地到达数据表的大小限制：对于 MylSAMft,频繁地索引可能引起索引文件比数据文件更快地达到最大限制。对于 BDB8,它把数据和索引值一起存储在同一个文件中， 添加索引引起这种表更快地达到最大文件限制。在 InnoDB 的共享表空间中分配的所有表都竞争使用相同的公共空间池，因此添加索引会更快地耗尽表空间中的存储。但是，与 MylSAMf 口 BDBg 使用的文件不同，InnoDB 共享表空间并不受操作系统的文件

13、大小限制，因为我们可以把它配置成使用多个文件。只要有额外的磁盘空间，你就可以通过添加新组件来扩展表空间。使用单独表空间的 InnoDB 表与 BDBg 受到的约束是一样的，因为它的数据和索引值都存储在单个文件中。这些要素的实际含义是：如果你不需要使用特殊的索引帮助查询执行得更快，就不要建立索引。选择索引假设你已经知道了建立索引的语法，但是语法不会告诉你数据表应该如何索引。这要求我们考虑数据表的使用方式。这一部分指导你如何识别出用于索引的备选数据列，以及如何最好地建立索引：用于搜索、排序和分组的索引数据列并不仅仅是用于输出显示的。换句话说，用于索引的最好的备选数据列是那些出现在 WHER 田旬、

14、join 子句、ORDERBY 或 GROUPB 乎句中的列。仅仅出现在 SELEC 正键字后面的输出数据列列表中的数据列不是很好的备选列：SELECTcol_a-不是备选列FROMtbllLEFTJOINtbl2ONtbl1.col_b=tbl2.col_c-备选列WHEREcol_d=expr;-备选歹 U当然,显示的数据列与 WHERE 句中使用的数据列也可能相同。 我们的观点是输出列表中的数据列本质上不是用于索引的很好的备选列。Join 子句或 WHERE 句中类似 col1=col2 形式的表达式中的数据列都是特别好的索引备选列。前面显示的查询中的 col_b 和 col_c 就是这

15、样的例子。如果 MySQLft 够利用联结列来优化查询，它一定会通过减少整表扫描来大幅度减少潜在的表-行组合。考虑数据列的基数(cardinality)。基数是数据列所包含的不同值的数量。例如，某个数据列包含值 1、3、7、4、7、3,那么它的基数就是 4。索引的基数相对于数据表行数较高(也就是说，列中包含很多不同的值，重复的值很少)的时候，它的工作效果最好。如果某数据列含有很多不同的年龄，索引会很快地分辨数据行。如果某个数据列用于记录性别(只有M和F两种值)，那么索引的用处就不大。如果值出现的几率几乎相等，那么无论搜索哪个值都可能得到一半的数据行。在这些情况下，最好根本不要使用索引，因为查询

16、优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。惯用的百分比界线是“30%”。现在查询优化器更加复杂，把其它一些因素也考虑进去了，因此这个百分比并不是 MySQ 映定选择使用扫描还是索引的唯一因素。索引较短的值。尽可能地使用较小的数据类型。例如，如果 MEDIUMIN 足够保存你需要存储的值，就不要使用 BIGINT 数据列。如果你的值不会长于 25 个字符，就不要使用 CHAR(100)较小的值通过几个方面改善了索引的处理速度： 较短的值可以更快地进行比较，因此索引的查找速度更快了。 较小的值导致较小的索引，需要更少的磁盘 I/O。 使用较短的键值的时候

17、，键缓存中的索引块(block)可以保存更多的键值。MySQlM 以在内存中一次保持更多的键，在不需要从磁盘读取额外的索引块的情况下，提高键值定位的可能性。对于InnoDB和BDB等使用聚簇索弓I(clusteredindex)的存储引擎来说,保持主键（primarykey）短小的优势更突出。聚簇索引中数据行和主键值存储在一起（聚簇在一起）。其它的索引都是次级索引；它们存储主键值和次级索引值。次级索引屈从主键值，它们被用于定位数据行。这暗示主键值都被复制到每个次级索引中，因此如果主键值很长，每个次级索引就需要更多的额外空间。索引字符串值的前缀（prefixe）。如果你需要索引一个字符串数据列，

18、那么最好在任何适当的情况下都应该指定前缀长度。例如，如果有 CHAR（20 吸据列，如果前面 10 个或 20 个字符都不同，就不要索引整个数据列。索引前面 10个或 20 个字符会节省大量的空间，并且可能使你的查询速度更快。通过索引较短的值，你可以获得那些与比较速度和磁盘 I/O 节省相关的好处。当然你也需要利用常识。仅仅索引某个数据列的第一个字符串可能用处不大，因为如果这样操作，那么在索引中不会有太多的唯一值。你可以索弓 ICHARVARCHARBINARYVARBINARYBLOEJ 口 TEX 微据歹用勺前缀。使用最左（leftmost）前缀。建立多列复合索引的时候，你实际上建立了 M

19、ySQlM以使用的多个索引。复合索引可以作为多个索引使用，因为索引中最左边的列集合都可以用于匹配数据行。这种列集合被称为“最左前缀”（它与索引某个列的前缀不同，那种索引把某个列的前面几个字符作为索引值）。假设你在表的 state、city 和 zip 数据列上建立了复合索引。索引中的数据行按照state/city/zip 次序排列，因此它们也会自动地按照 state/city 和 state 次序排列。这意味着，即使你在查询中只指定了 state 值，或者指定 state 和 city 值，MySQ 也可以使用这个索引。因此，这个索引可以被用于搜索如下所示的数据列组合：state,city,z

20、ipstate,citystateMySQ 印能利用这个索引来搜索没有包含在最左前缀的内容。例如，如果你按照city 或 zip 来搜索，就不会使用到这个索引。如果你搜索给定的 state和具体的 ZIP 代码（索引的 1 和 3 歹 I），该索引也是不能用于这种组合值的，尽管MySQLM 以利用索引来查找匹配的 state 从而缩小搜索的范围。不要过多地索引。不要认为索引越多，性能越高”，不要对每个数据列都进行索引。我们在前面提到过，每个额外的索引都会花费更多的磁盘空间，并降低写操作的性能。当你修改表的内容的时候，索引就必须被更新，甚至可能重新整理。如果你的索引很少使用或永不使用，你就没有必

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