MySQL从入门到精通-11第八章

本章要点：如何使用索引优化表如何选用合适的列类型如何优化SQL查询如何设定服务器参数关系数据库的世界是一个表与集合、表与集合上的运算占统治地位的世界。数据库是一个表的集合，而表又是行和列的集合。在发布一条SELECT查询从表中进行检索行时，得到另一个行和列的集合。这些都是一些抽象的概念，对于数据库系统用来操纵表中数据的基本表示没有多少参考价值。另一个抽象概念是，表上的运算都同时进行；查询是一种概念性的集合运算，并且集合论中没有时间概念。当然，现实世界是相当不同的。数据库管理系统实现了抽象的概念，但是在实际的硬件范围内要受到实际的物理约束。结果是，查询要花时间，有时要花很长的时间。而人类很容易不耐烦，不喜欢等待，因此我们丢下了集合上的那些瞬间的数学运算的抽象世界去寻求加速查询的方法。幸运的是，有几种加速运算的技术，可对表进行索引使数据库服务器查找行更快。可考虑怎样充分利用这些索引来编写查询。可编写影响服务器调度机制的查询，使来自多个客户机的查询协作得更好。我们思考基本硬件怎样运行，以便想出怎样克服其物理约束对性能进行改善的方法。数数数据据据库库库优优优化化化第第88章章MySQL金典培训教程178这些正是本章所要讨论的问题，其目标是优化数据库系统的性能，使其尽可能快地处理各种查询。MySQL已经相当快了，但即使是最快的数据库，在人的设计下还能运行得更快。优化是一项复杂的任务，因为它最终需要对整个系统的理解。当用你的系统/应用的小知识做一些局部优化是可能的时候，你越想让你的系统更优化，你必须知道它也越多。因此，本章将试图解释并给出优化MySQL的不同方法的一些例子。但是记住总是有某些(逐渐变难)是系统更快的方法留着去做。8.1索引的使用我们首先讨论索引，因为它是加快查询的最重要的工具。还有其他加快查询的技术，但是最有效的莫过于恰当地使用索引了。在MySQL的邮件清单上，人们通常询问关于使查询更快的问题。在大量的案例中，都是因为表上没有索引，一般只要加上索引就可以立即解决问题。但这样也并非总是有效，因为优化并非总是那样简单。然而，如果不使用索引，在许多情形下，用其他手段改善性能只会是浪费时间。应该首先考虑使用索引取得最大的性能改善，然后再寻求其他可能有帮助的技术。本节介绍索引是什么、它怎样改善查询性能、索引在什么情况下可能会降低性能，以及怎样为表选择索引。下一节，我们将讨论MySQL的查询优化程序。除了知道怎样创建索引外，了解一些优化程序的知识也是有好处的，因为这样可以更好地利用所创建的索引。某些编写查询的方法实际上会妨碍索引的效果，应该避免这种情况出现。（虽然并非总会这样。有时也会希望忽略优化程序的作用。我们也将介绍这些情况。）8.1.1索引对单个表查询的影响索引被用来快速找出在一个列上用一特定值的行。没有索引，MySQL不得不首先以第一条记录开始并然后读完整个表直到它找出相关的行。表越大，花费时间越多。如果表对于查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要考虑所有数据。如果一个表有1000行，这比顺序读取至少快100倍。注意你需要存取几乎所有1000行，它较快的顺序读取，因为此时我们避免磁盘寻道。例如对下面这样的一个student表：mysqlSELECT*FROMstudent+------+---------+---------+---------+---------+|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|8数据库优化|74|Stone|42|40|61||86|Smith|49|85|78||37|Black|49|63|47||89|White|94|31|52|+------+---------+---------+---------+---------+这样，我们试图对它进行一个特定查询时，就不得不做一个全表的扫描，速度很慢。例如，我们查找出所有english成绩不及格的学生：mysqlSELECTname,englishFROMstudentWHEREenglish60;+---------+---------+|name|english|+---------+---------+|Marry|54||William|43||Stone|42||Smith|49||Black|49|+---------+---------+其中，WHERE从句不得不匹配每个记录，以检查是否符合条件。对于这个较小的表也许感觉不到太多的影响。但是对于一个较大的表，例如一个非常大的学校，我们可能需要存储成千上万的记录，这样一个检索的所花的时间是十分可观的。如果，我们为english列创建一个索引：mysqlALTERTABLEstudentADDINDEX(english);+-------------------+|indexforenglish|+-------------------+|42||43||49||49||54||66||78||94||99|+-------------------+如上表，此索引存储在索引文件中，包含表中每行的english列值，但此索引是在english的基础上排序的。现在，不需要逐行搜索全表查找匹配的条款，而是可以利用索引进行查找。假如我们要查找分数小于60的所有行，那么可以扫描索引，结果得出5MySQL金典培训教程180行。然后到达分数为66的行，及Tom的记录，这是一个比我们正在查找的要大的值。索引值是排序的，因此在读到包含Tom的记录时，我们知道不会再有匹配的记录，可以退出了。如果查找一个值，它在索引表中某个中间点以前不会出现，那么也有找到其第一个匹配索引项的定位算法，而不用进行表的顺序扫描（如二分查找法）。这样，可以快速定位到第一个匹配的值，以节省大量搜索时间。数据库利用了各种各样的快速定位索引值的技术，这些技术是什么并不重要，重要的是它们工作正常，索引技术是个好东西。因此在执行下述查询mysqlSELECTname,englishFROMuserWHEREenglish60;其结果为：+---------+---------+|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的值匹配的行。8数据库优化)进到表t1的下一行并重复前面的过程直到t1中所有的行已经查过。在此情形下，我们仍然对表t1执行了一个完全扫描，但能够在表t2和t3上进行索引查找直接取出这些表中的行。从道理上说，这时的查询比未用索引时要快一百万倍。如上所述，MySQL利用索引加速了WHERE子句中与条件相配的行的搜索，或者说在执行连接时加快了与其他表中的行匹配的行的搜索。8.1.3多列索引对查询的影响假定你发出下列SELECT语句：mysqlSELECT*FROMtbl_nameWHEREcol1=val1ANDcol2=val2;如果一个多列索引存在于col1和col2上，适当的行可以直接被取出。如果分开的单行列索引存在于col1和col2上，优化器试图通过决定哪个索引将找到更少的行并来找出更具限制性的索引并且使用该索引取行。你可以这样创建一个多列索引：mysqlALTERTABLEtbl_nameADDINDEX(col1,col2);而你应该这样创建分开的单行列索引：mysqlALTERTABLEtble_nameADDINDEX(col1);mysqlALTERTABLEtble_nameADDINDEX(col1);如果表有一个多列索引，任何最左面的索引前缀能被优化器使用以找出行。例如，如果你有一个3行列索引(col1,col2,col3)，你已经索引了在(col1)、(col1,col2)和(col1,col2,col3)上的搜索能力。如果列不构成索引的最左面前缀，MySQL不能使用一个部分的索引。假定你下面显示的SELECT语句：mysqlSELECT*FROMtbl_nameWHEREcol1=val1;mysqlSELECT*FROMtbl_nameWHEREcol2=val2;mysqlSELECT*FROMtbl_nameWHEREcol2=val2ANDcol3=val3;如果一个索引存在于(col1、col2、col3)上，只有上面显示的第一个查询使用索引。第二个和第三个查询确实包含索引的列，但是(col2)和(col2、col3)不是(col1、col2、col3)的最左面前缀。如果LIKE参数是一个不以一个通配符字符起始的一个常数字符串，MySQL也为LIKE比较使用索引。例如，下列SELECT语句使用索引：mysqlselect*fromtbl_namewherekey_colLIKEPatrick%;mysqlselect*fromtbl_namewherekey_colLIKEPat%_ck%;在第一条语句中，只考虑有Patrick=key_colPatricl的行。在第二条语句中，只考虑有Pat=key_colPau的行。下列SELECT语句将不使用索引：MySQL金典培训教程182*fromtbl_namewherekey_colLIKE%Patrick%;mysqlselect*fromtbl_namewherekey_colLIKEother_col;在第一条语句中，LIKE值以一个通配符字符开始。在第二条语句中，LIKE值不是一个常数。如果column_name是一个索引，使用column_nameISNULL的搜索将使用索引。MySQL通常使用找出最少数量的行的索引。一个索引被用于你与下列操作符作比较的列：=、