如何使用索引优化表

概述:本小结讲述了索引在优化查询中的作用，包括了索引优化查询的原理，索引在各种情况的检索中的益处，也包括索引的的弊端：增加了存储的空间，使装载数据变慢。

索引是优化查询的最常用也是最有效的的方法，一个数据表，尤其是容量很大的表，建立合适的索引，会使查询的速度提高很大.

一:索引对单个表查询的影响

索引被用来快速找出在一个列上用一特定值的行。没有索引，MySQL不得不首先以第一条记录开始并然后读完整个表直到它找出相关的行。表越大，花费时间越多。如果表对于查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要考虑所有数据。如果一个表有1000行，这比顺序读取至少快100倍。注意你需要存取几乎所有1000行，它较快的顺序读取，因为此时我们避免磁盘寻道。

例如对下面这样的一个student表：

mysql>SELECT * FROM student

+------+---------+---------+---------+---------+

| id     | name      | english | chinese | history |

+------+---------+---------+---------+---------+

|     12 | Tom       |        66 |        93 |        67 |

|     56 | Paul      |        78 |        52 |        75 |

|     10 | Marry     |        54 |        89 |        74 |

|      4 | Tina      |        99 |        83 |        48 |

|     39 | William |        43 |        96 |        52 |

|     74 | Stone     |        42 |        40 |        61 |
|     86 | Smith     |        49 |        85 |        78 |
|     37 | Black     |        49 |        63 |        47 |
|     89 | White     |        94 |        31 |        52 |
+------+---------+---------+---------+---------+
这样，我们试图对它进行一个特定查询时，就不得不做一个全表的扫描，速度很慢。例如，我们查找出所有english成绩不及格的学生：
mysql>SELECT name,english FROM student WHERE english<60;
+---------+---------+
| name      | english |
+---------+---------+
| Marry     |        54 |
| William |        43 |
| Stone     |        42 |
| Smith     |        49 |
| Black     |        49 |
+---------+---------+
其中，WHERE从句不得不匹配每个记录，以检查是否符合条件。对于这个较小的表也许感觉不到太多的影响。但是对于一个较大的表，例如一个非常大的学校，我们可能需要存储成千上万的记录，这样一个检索的所花的时间是十分可观的。
如果，我们为english列创建一个索引：
mysql>ALTER TABLE student ADD INDEX (english) ;
+-------------------+
| index for english |
+-------------------+
|                  42 |
|                  43 |
|                  49 |
|                  49 |
|                  54 |
|                  66 |
|                  78 |
|                  94 |
|                  99 |
+-------------------+
如上表，此索引存储在索引文件中，包含表中每行的english列值，但此索引是在 english的基础上排序的。现在，不需要逐行搜索全表查找匹配的条款，而是可以利用索引进行查找。假如我们要查找分数小于60的所有行，那么可以扫描索引，结果得出5

行。然后到达分数为66的行，及Tom的记录，这是一个比我们正在查找的要大的值。索引值是排序的，因此在读到包含Tom的记录时，我们知道不会再有匹配的记录，可以退出了。如果查找一个值，它在索引表中某个中间点以前不会出现，那么也有找到其第一个匹配索引项的定位算法，而不用进行表的顺序扫描（如二分查找法）。这样，可以快速定位到第一个匹配的值，以节省大量搜索时间。数据库利用了各种各样的快速定位索引值的技术，这些技术是什么并不重要，重要的是它们工作正常，索引技术是个好东西。
因此在执行下述查询
mysql>SELECT name,english FROM user WHERE english<60;
其结果为：
+---------+---------+
| name      | english |
+---------+---------+
| Stone     |        42 |
| William |        43 |
| Smith     |        49 |
| Black     |        49 |
| Marry     |        54 |
+---------+---------+
你应该可以发现，这个结果与未索引english列之前的不同，它是排序的，原因正式如上所述。
 
二:索引对多个表查询的影响
 

假如有三个未索引的表 t1、t2、t3，分别只包含列 c1、c2、c3，每个表分别由含有数值 1 到 1000 的 1000 行组成。查找对应值相等的表行组合的查询如下所示：
此查询的结果应该为 1000 行，每个组合包含 3 个相等的值。如果我们在无索引的情况下处理此查询，则不可能知道哪些行包含那些值。因此，必须寻找出所有组合以便得出与 WHERE 子句相配的那些组合。可能的组合数目为 1000×1000×1000（十亿），比匹配数目多一百万倍。很多工作都浪费了，并且这个查询将会非常慢，即使在如像 MySQL 这样快的数据库中执行也会很慢。而这还是每个表中只有 1000 行的情形。如果每个表中有一百万行时，将会怎样？很显然，这样将会产生性能极为低下的结果。如果对每个表进行索引，就能极大地加速查询进程，因为利用索引的查询处理如下：
1) 如下从表 t1 中选择第一行，查看此行所包含的值。
2) 使用表 t2 上的索引，直接跳到 t2 中与来自 t1 的值匹配的行。类似，利用表 t3 上的索引，直接跳到 t3 中与来自 t1 的值匹配的行。
3) 进到表 t1 的下一行并重复前面的过程直到 t1 中所有的行已经查过。
在此情形下，我们仍然对表 t1 执行了一个完全扫描，但能够在表 t2 和 t3 上进行索引查找直接取出这些表中的行。从道理上说，这时的查询比未用索引时要快一百万倍。
如上所述，MySQL 利用索引加速了 WHERE 子句中与条件相配的行的搜索，或者说在执行连接时加快了与其他表中的行匹配的行的搜索。