MYSQL培训经典教程第八章.doc

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

MySQL 已经相当快了，但即使是最快的数据库，在人的设计下还能运行得更快优化是一项复杂的任务，因为它最终需要对整个系统的理解当用你的系统/应用的小知识做一些局部优化是可能的时候，你越想让你的系统更优化，你必须知道它也越多 因此，本章将试图解释并给出优化MySQL的不同方法的一些例子但是记住总是有某些(逐渐变难)是系统更快的方法留着去做8.1 索引的使用我们首先讨论索引，因为它是加快查询的最重要的工具还有其他加快查询的技术，但是最有效的莫过于恰当地使用索引了在 MySQL 的邮件清单上，人们通常询问关于使查询更快的问题在大量的案例中，都是因为表上没有索引，一般只要加上索引就可以立即解决问题但这样也并非总是有效，因为优化并非总是那样简单然而，如果不使用索引，在许多情形下，用其他手段改善性能只会是浪费时间应该首先考虑使用索引取得最大的性能改善，然后再寻求其他可能有帮助的技术本节介绍索引是什么、它怎样改善查询性能、索引在什么情况下可能会降低性能，以及怎样为表选择索引下一节，我们将讨论 MySQL 的查询优化程序除了知道怎样创建索引外，了解一些优化程序的知识也是有好处的，因为这样可以更好地利用所创建的索引。

某些编写查询的方法实际上会妨碍索引的效果，应该避免这种情况出现虽然并非总会这样有时也会希望忽略优化程序的作用我们也将介绍这些情况8.1.1索引对单个表查询的影响索引被用来快速找出在一个列上用一特定值的行没有索引，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列之前的不同，它是排序的，原因正式如上所述。

8.1.2索引对多个表查询的影响前面的讨论描述了单表查询中索引的好处，其中使用索引消除了全表扫描，极大地加快了搜索的速度在执行涉及多个表的连接查询时，索引甚至会更有价值在单个表的查询中，每列需要查看的值的数目就是表中行的数目而在多个表的查询中，可能的组合数目极大，因为这个数目为各表中行数之积假如有三个未索引的表 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 子句中与条件相配的行的搜索，或者说在执行连接时加快了与其他表中的行匹配的行的搜索8.1.3多列索引对查询的影响假定你发出下列SELECT语句： mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;如果一个多列索引存在于col1和col2上，适当的行可以直接被取出如果分开的单行列索引存在于col1和col2上，优化器试图通过决定哪个索引将找到更少的行并来找出更具限制性的索引并且使用该索引取行 你可以这样创建一个多列索引：mysql>ALTER TABLE tbl_name ADD INDEX(col1,col2);而你应该这样创建分开的单行列索引：mysql>ALTER TABLE tble_name ADD INDEX(col1);mysql>ALTER TABLE tble_name ADD INDEX(col1);l 如果表有一个多列索引，任何最左面的索引前缀能被优化器使用以找出行。

例如，如果你有一个3行列索引(col1,col2,col3)，你已经索引了在(col1)、(col1,col2)和(col1,col2,col3)上的搜索能力 如果列不构成索引的最左面前缀，MySQ。