MySQL优化之索引的使用.docVIP

MySQL索引的使用 　　我们首先讨论索引，因为它是加快查询的最重要的工具。还有其他加快查询的技术，但是最有效的莫过于恰当地使用索引了。在 MySQL 的邮件清单上，人们通常询问关于使查询更快的问题。在大量的案例中，都是因为表上没有索引，一般只要加上索引就可以立即解决问题。但这样也并非总是有效，因为优化并非总是那样简单。然而，如果不使用索引，在许多情形下，用其他手段改善性能只会是浪费时间。应该首先考虑使用索引取得最大的性能改善，然后再寻求其他可能有帮助的技术。 　　本节介绍索引是什么、它怎样改善查询性能、索引在什么情况下可能会降低性能，以及怎样为表选择索引。下一节，我们将讨论 MySQL 的查询优化程序。除了知道怎样创建索引外，了解一些优化程序的知识也是有好处的，因为这样可以更好地利用所创建的索引。某些编写查询的方法实际上会妨碍索引的效果，应该避免这种情况出现。(虽然并非总会这样。有时也会希望忽略优化程序的作用。我们也将介绍这些情况。) 　　索引对单个表查询的影响 　　索引被用来快速找出在一个列上用一特定值的行。没有索引，MySQL不得不首先以第一条记录开始并然后读完整个表直到它找出相关的行。表越大，花费时间越多。如果表对于查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要考虑所有数据。如果一个表有1000行，这比顺序读取至少快100倍。注意你需要存取几乎所有1000行，它较快的顺序读取，因为此时我们避免磁盘寻道。 　　例如对下面这样的一个student表： 　　mysqlSELECT * 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成绩不及格的学生： 　　mysqlSELECT name,english FROM student WHERE english60; 　　+---------+---------+ 　　| name | english | 　　+---------+---------+ 　　| Marry | 54 | 　　| William | 43 | 　　| Stone | 42 | 　　| Smith | 49 | 　　| Black | 49 | 　　+---------+---------+ 　　其中，WHERE从句不得不匹配每个记录，以检查是否符合条件。对于这个较小的表也许感觉不到太多的影响。但是对于一个较大的表，例如一个非常大的学校，我们可能需要存储成千上万的记录，这样一个检索的所花的时间是十分可观的。 　　如果，我们为english列创建一个索引： 　　mysqlALTER TABLE student ADD INDEX (english) ; 　　+-------------------+ 　　| index for english | 　　+-------------------+ 　　| 42 | 　　| 43 | 　　| 49 | 　　| 49 | 　　| 54 | 　　| 66 | 　　| 78 | 　　| 94 | 　　| 99 | 　　+-------------------+ 　　如上表，此索引存储在索引文件中，包含表中每行的english列值，但此索引是在 english的基础上排序的。现在，不需要逐行搜索全表查找匹配的条款，而是可以利用索引进行查找。假如我们要查找分数小于60的所有行，那么可以扫描索引，结果得出5行。然后到达分数为66的行，及Tom的记录，这是一个比我们正在查找的要大的值。索引值是排序的，