关于Mysql索引那点事_MySQL

bitsCN.com

 关系数据库的世界是一个表与集合、表与集合上的运算占统治地位的世界。数据库是一个表的集合，而表又是行和列的集合。在发布一条SELECT 查询从表中进行检索行时，得到另一个行和列的集合。这些都是一些抽象的概念，对于数据库系统用来操纵表中数据的基本表示没有多少参考价值。另一个抽象概念是，表上的运算都同时进行；查询是一种概念性的集合运算，并且集合论中没有时间概念。当然，现实世界是相当不同的。数据库管理系统实现了抽象的概念，但是在实际的硬件  范围内要受到实际的物理约束。结果是，查询要花时间，有时要花很长的时间。而人类很容易不耐烦，不喜欢等待，因此我们丢下了集合上的那些瞬间的数学运算的抽象世界去寻求加速查询的方法。幸运的是，有几种加速运算的技术，可对表进行索引使数据库服务器查找行更快。可考虑怎样充分利用这些索引来编写查询。可编写影响服务器调度机制的查询，使来自多个客户机的查询协作得更好。我们思考基本硬件怎样运行，以便想出怎样克服其物理约束对性能进行改善的方法。

 这些正是本文所要讨论的问题，其目标是优化数据库系统的性能，使其尽可能快地处理各种查询。MySQL已经相当快了，但即使是最快的数据库，在人的设计下还能运行得更快。

1 使用索引

我们首先讨论索引，因为它是加快查询的最重要的工具。还有其他加快查询的技术，但是最有效的莫过于恰当地使用索引了。在MySQL的邮件清单上，人们通常询问关于使查询更快的问题。在大量的案例中，都是因为表上没有索引，一般只要加上索引就可以立即解决问题。但这样也并非总是有效，因为优化并非总是那样简单。然而，如果不使用索引，在许多情形下，用其他手段改善性能只会是浪费时间。应该首先考虑使用索引取得最大的性能改善，然后再寻求其他可能有帮助的技术。

本文介绍索引是什么、它怎样改善查询性能、索引在什么情况下可能会降低性能，以及怎样为表选择索引。下一节，我们将讨论MySQL的查询优化程序。除了知道怎样创建索引外，了解一些优化程序的知识也是有好处的，因为这样可以更好地利用所创建的索引。某些编写查询的方法实际上会妨碍索引的效果，应该避免这种情况出现。（虽然并非总会这样。有时也会希望忽略优化程序的作用。我们也将介绍这些情况。）

1.1 索引的益处

让我们从一个无索引的表着手来考察索引是怎样起作用的。无索引的表就是一个无序的行集。例如，图4 - 1给出了我们在第1章“MySQL与SQL 介绍” 中首先看到的ad 表。这个表上没有索引，因此如果我们查找某个特定公司的行时，必须查看表中的每一行，看它是否与所需的值匹配。这是一个全表扫描，很慢，如果表中只有少数几个记录与搜索条件相匹配，则其效率是相当低的。

 图4 - 2给出了相同的表，但在表的company_num 列上增加了一个索引。此索引包含表中每行的一项，但此索引是在company_num 上排序的。现在，不需要逐行搜索全表查找匹配的条款，而是可以利用索引进行查找。假如我们要查找公司13的所有行，那么可以扫描索引，结果得出3行。然后到达公司14的行，这是一个比我们正在查找的要大的号码。索引值是排序的，因此在读到包含14的记录时，我们知道不会再有匹配的记录，可以退出了。如果查找一个值，它在索引表中某个中间点以前不会出现，那么也有找到其第一个匹配索引项的定位算法，而不用进行表的顺序扫描（如二分查找法）。这样，可以快速定位到第一个匹配的值，以节省大量搜索时间。数据库利用了各种各样的快速定位索引值的技术，这些技术是什么并不重要，重要的是它们工作正常，索引技术是个好东西。

有人会问，为什么不只对数据文件进行排序，省掉索引文件？这样不也在搜索时产生相同的效果吗？问得好，如果只有单个索引时，是这样的。不过有可能会用到第二个索引，但同时以两种不同的方法对同一个数据文件进行排序是不可能的。（如，想要一个顾客名的索引，同时又要一个顾客ID 号或电话号码的索引。）将索引文件作为一个与数据文件独立的实体就解决了这个问题，而且允许创建多个索引。此外，索引中的行一般要比数据文件中的行短。在插入或删除值时，为保持排序顺序而移动较短的索引值与移动较长的数据行相比更为容易。

这个例子与MySQL索引表的方法相符。表的数据行保存在数据文件中，而索引值保存在索引文件中。一个表上可有不止一个索引；如果确实有不止一个索引，它们都保存在同一个索引文件中。索引文件中的每个索引由排过序的用来快速访问数据文件的键记录数组构成。

前面的讨论描述了单表查询中索引的好处，其中使用索引消除了全表扫描，极大地加快了搜索的速度。在执行涉及多个表的连接查询时，索引甚至会更有价值。在单个表的查询中，每列需要查看的值的数目就是表中行的数目。而在多个表的查询中，可能的组合数目极大，因为这个数目为各表中行数之积。

假如有三个未索引的表t 1、t 2、t 3，分别只包含列c 1、c 2、c 3，每个表分别由含有数值1到1000 的1000 行组成。查找对应值相等的表行组合的查询如下所示：
    SELECT c1,c2,c3
    FROM t1,t2,t3
    WHERE c1=c2 AND c1=c3

此查询的结果应该为1000 行，每个组合包含3 个相等的值。如果我们在无索引的情况下处理此查询，则不可能知道哪些行包含那些值。因此，必须寻找出所有组合以便得出与WHERE 子句相配的那些组合。可能的组合数目为10 0 0×10 0 0×10 0 0（十亿），比匹配数目多一百万倍。很多工作都浪费了，并且这个查询将会非常慢，即使在如像MySQL这样快的数据库中执行也会很慢。而这还是每个表中只有1000 行的情形。如果每个表中有一百万行时，将会怎样？很显然，这样将会产生性能极为低下的结果。如果对每个表进行索引，就能极大地加速查询进程，因为利用索引的查询处理如下：

    1) 如下从表t1中选择第一行，查看此行所包含的值。
    2) 使用表t2 上的索引，直接跳到t2 中与来自t1的值匹配的行。类似，利用表t3 上的索引，直接跳到t3 中与来自t1的值匹配的行。
    3) 进到表t1的下一行并重复前面的过程直到t1中所有的行已经查过。在此情形下，我们仍然对表t1执行了一个完全扫描，但能够在表t2 和t3 上进行索引查找直接取出这些表中的行。从道理上说，这时的查询比未用索引时要快一百万倍。如上所述，MySQL利用索引加速了WHERE 子句中与条件相配的行的搜索，或者说在执行连接时加快了与其他表中的行匹配的行的搜索。它也利用索引来改进其他操作的性能：

    ■ 在使用MIN( ) 和MAX( ) 函数时，能够快速找到索引列的最小或最大值。
    ■ MySQL常常能够利用索引来完成ORDER BY 子句的排序操作。
    ■ 有时，MySQL可避免对整个数据文件的读取。假如从一个索引数值列中选择值，而且不选择表中其他列。这时，通过对索引值的读取，就已经得到了读取数据文件所要得到的值。没有对相同的值进行两次读取的必要，因此，甚至无需涉及数据文件。 bitsCN.com