MySQL数据库技术(20)
2007-11-11 15:22:47 来源:WEB开发网 闂傚倸鍊搁崐鎼佸磹閹间礁纾归柟闂寸绾惧綊鏌熼梻瀵割槮缁炬儳缍婇弻鐔兼⒒鐎靛壊妲紒鐐劤缂嶅﹪寮婚悢鍏尖拻閻庨潧澹婂Σ顔剧磼閻愵剙鍔ょ紓宥咃躬瀵鎮㈤崗灏栨嫽闁诲酣娼ф竟濠偽i鍓х<闁绘劦鍓欓崝銈囩磽瀹ュ拑韬€殿喖顭烽幃銏ゅ礂鐏忔牗瀚介梺璇查叄濞佳勭珶婵犲伣锝夘敊閸撗咃紲闂佺粯鍔﹂崜娆撳礉閵堝洨纾界€广儱鎷戦煬顒傗偓娈垮枛椤兘骞冮姀銈呯閻忓繑鐗楃€氫粙姊虹拠鏌ュ弰婵炰匠鍕彾濠电姴浼i敐澶樻晩闁告挆鍜冪床闂備胶绮崝锕傚礈濞嗘挸绀夐柕鍫濇川绾剧晫鈧箍鍎遍幏鎴︾叕椤掑倵鍋撳▓鍨灈妞ゎ厾鍏橀獮鍐閵堝懐顦ч柣蹇撶箲閻楁鈧矮绮欏铏规嫚閺屻儱寮板┑鐐板尃閸曨厾褰炬繝鐢靛Т娴硷綁鏁愭径妯绘櫓闂佸憡鎸嗛崪鍐簥闂傚倷娴囬鏍垂鎼淬劌绀冮柨婵嗘閻﹂亶姊婚崒娆掑厡妞ゃ垹锕ら埢宥夊即閵忕姷顔夐梺鎼炲労閸撴瑩鎮橀幎鑺ョ厸闁告劑鍔庢晶鏇犵磼閳ь剟宕橀埞澶哥盎闂婎偄娲ゅù鐑剿囬敃鈧湁婵犲﹤鐗忛悾娲煛鐏炶濡奸柍瑙勫灴瀹曞崬鈻庤箛鎾寸槗缂傚倸鍊烽梽宥夊礉鎼达絽鍨濇い鏍仜妗呴梺鍛婃处閸ㄦ壆绮婚幎鑺ュ€甸柨婵嗙凹缁ㄨ棄霉閻樿崵鐣烘慨濠冩そ濡啫鈽夊▎鎰€烽梺璇插閻噣宕¢幎鑺ュ仒妞ゆ洍鍋撶€规洖鐖奸、妤佸緞鐎n偅鐝┑鐘愁問閸n垳寰婇崜褉鍋撶粭娑樻搐缁犳煡鏌涢妷顔煎闁藉啰鍠栭弻锝夊棘閹稿孩鍠愰梺鑽ゅ枎缂嶅﹪寮诲☉鈶┾偓锕傚箣濠靛洨浜俊鐐€ら崜娆撴偋閸℃稈鈧棃宕橀鍢壯囧箹缁厜鍋撻懠顒€鍤紓鍌氬€风欢锟犲窗濡ゅ懎绠伴柟闂寸劍閸嬧晠鏌i幋锝嗩棄缁绢厸鍋撻梻浣虹帛閸旀洜绮旈棃娴虫盯宕橀鍏兼К闂侀€炲苯澧柕鍥у楠炴帡骞嬪┑鎰磻闁诲氦顫夐幐椋庣矆娓氣偓閸╃偤骞嬮敂钘変汗闂佸湱绮敮鈺傚閳ь剛绱撴担鐟板姢鐟滄壆鍋熼崚鎺戔枎閹惧疇鎽曞┑鐐村灦閻喖鈻介鍫熺厵閻熸瑥瀚慨鍥ㄣ亜閵夛妇绠炴慨濠冩そ閺屽懘鎮欓懠璺侯伃婵犫拃鍌氬祮闁哄瞼鍠栭幖褰掝敃閿濆懐锛撻梻浣瑰缁诲嫰宕戝☉銏犵厴闁瑰濮崑鎾绘晲鎼存ê浜炬い鎾寸⊕濞呭﹪鏌$仦鐣屝f繛纰变邯楠炲繒浠﹂挊澶婅厫闂傚倷鐒﹂惇褰掑磹閺囥垹绠犻柟閭﹀枟椤洟鏌熼幆褏鎽犲┑顖涙尦閺屾盯骞橀弶鎴犵シ闂佸憡鎸稿畷顒勨€旈崘顔嘉ч柛鈩冾殘娴犳悂姊洪懡銈呮毐闁哄懏鐩幃楣冩倻閽樺)銊ф喐婢舵劕纾婚柟鍓х帛閺呮煡骞栫划鐟板⒉闁诲繐绉瑰铏圭磼濡闉嶅┑鐐插级閿曘垺淇婇悽绋跨妞ゆ牗姘ㄩ悿鈧梻鍌氬€搁悧濠勭矙閹邦喛濮抽柤娴嬫櫇绾捐棄霉閿濆牊顥夐柣鎾村姈閹便劌螣缁嬪灝顬嬪┑鈥冲级閸旀瑩鐛Ο鍏煎珰闁肩⒈鍓﹀Σ浼存⒒娴gǹ鏆遍柟纰卞亰瀹曟劖绻濆В绋挎喘瀵埖鎯旈幘瀛樻澑婵$偑鍊栧濠氬Υ鐎n亶鍟呴柕澶涜礋娴滄粍銇勯幘璺轰粶婵℃彃顭烽弻锝夋晲閸パ冨箣濡ょ姷鍋炵敮锟犵嵁鐎n喖绫嶉柍褜鍓熼幃妤佺節濮橆厸鎷洪柣鐔哥懃鐎氼參宕曞Δ鍛厱婵☆垵銆€閸嬫捇鎮㈤幓鎺戠阀濠电姷鏁告慨鐑藉极閸涘﹥鍙忛柣鎴f閺嬩線鏌涘☉姗堟敾闁告瑥绻戦妵鍕箻閸楃偟浠肩紒鐐劤椤兘寮婚悢鐓庣鐟滃繒鏁☉銏$厓闂佸灝顑呴悘锕傛煏閸パ冾伃妤犵偞甯″畷鍗烆渻閹屾缂傚倸鍊搁崐椋庣矆娓氣偓钘濋梺顒€绉撮弸浣糕攽閻樿櫕鐨戠€规挷绶氶弻娑㈠焺閸愵亖濮囬梺绋匡功閸忔﹢寮诲☉妯锋斀闁糕剝顨忔导鈧俊鐐€栧褰掑礉閺囥垹鐓橀柟杈鹃檮閸婂鏌涢妷銏℃珖閺嶏繝姊绘担鍛婂暈闁圭ǹ顭烽幃鐑芥晜閻e备鏀虫繝鐢靛Т濞诧箓宕甸崘顔界厓闁告繂瀚弳鐔兼煥濞戞瑧鐭掓慨濠囩細閵囨劙骞掗幋婊冩瀳闂備礁鎲¢悷銉︻殽閹间礁鐓濋柟鐐灱閸亪鏌涢銈呮灁闁告ɑ鎮傞弻锝堢疀閺囩偘鎴风紒缁㈠幖閻栫厧鐣烽幋锕€绠婚悹鍥皺閻も偓濠电偠鎻徊浠嬪箟閿熺姴纾规い鏍仦閳锋垹鐥鐐村櫣濞存粌缍婇幃璺衡槈閺嵮冨Е闂佺硶鏂侀崑鎾愁渻閵堝棗绗掗柛鐕佸亰閹啫煤椤忓懐鍘告繛杈剧到濠€杈ㄦ櫠椤忓牊鐓冮悷娆忓閻忔挳鏌熼鐣屾噰鐎殿喖鐖奸獮瀣偐鏉堫煈鏁囬梻鍌氬€风粈浣革耿鏉堛劎浠氶梻浣侯攰婵倗鍒掓惔銊ョ闁圭儤顨呯猾宥夋煕椤愩倕鏋庡ù鐘烘缁辨挻鎷呴崜鎻掑壍濡炪倖娲樻繛濠囧极閸愵喖纾兼繛鎴炶壘楠炲牓姊绘担鍛婃儓婵炲眰鍨藉畷婵嗙暆閸曨剙鈧爼鏌eΟ鑲╁笡闁绘挻娲熼弻鐔兼嚋椤掆偓婵$厧霉濠婂嫬鍔ら柍瑙勫灴閺佸秹宕熼鈩冩線闂備胶枪閿曘儵鎮ч悩鑼殾婵犻潧顑嗛弲婵嬫煃瑜滈崜鐔煎灳閿曞倸閿ゆ俊銈傚亾闁绘帒鐏氶妵鍕箳瀹ュ牆鍘$紓浣哄Т婢т粙鍩€椤掆偓閸樻粓宕戦幘鏂ユ斀闁绘ǹ浜粣鏃堟煕鐎n偒娈旈柍瑙勫灴椤㈡瑧娑甸悜鐣屽弽婵犵數鍋涢幏鎴犲緤閸啣锝夊箛閺夎法顔婇梺鐟板暱绾绢參宕伴幘璇茬闁绘ḿ绮崵鎴︽煠缁嬭法浠涙慨锝嗗姍濮婂宕掑顑藉亾閻戣姤鍤勯柤鍝ユ暩娴犳碍绻濋悽闈涗粶妞ゆ洦鍙冨畷妤€螣娓氼垰娈ㄥ銈嗗姂閸婃牜鈧碍姘ㄩ埀顒傛嚀婢瑰﹪宕伴弽褉鏋旈柕濠忓缁♀偓闂佹眹鍨藉ḿ褎鐗庣紓浣哄亾濠㈡ḿ绮旈悷閭﹀殨闁哄被鍎辩粻鐢告煙閻戞ḿ绠橀柛鐐垫暬閺岋綁鎮╅悜姗嗕哗闁诲繐绻堥崝宀勵敊韫囨稑唯鐟滃宕戦幘鑸靛枂闁告洦鍓欑喊宥呪攽閳藉棗浜濈紒璇插€块敐鐐剁疀濞戞瑦鍎梺闈╁瘜閸橀箖鏁嶅⿰鍐f斀闁宠棄妫楅悘鐘绘煙绾板崬浜伴柨婵堝仜椤撳ジ宕堕埡鍐跨闯濠电偠鎻紞渚€藟閹捐绀夌€广儱顦伴悡娆戠磼鐎n亞浠㈤柡鍡涗憾閺岋綁鏁愰崶褍骞嬪Δ鐘靛仜椤戝寮崘顔肩劦妞ゆ帒鍊绘稉宥呪攽閻樺磭顣查柛瀣剁秮閺屾盯濡烽幋婵嗘殶濡ょ姴娲幃妤冩喆閸曨剙纰嶇紓浣割槹閹告娊鍨鹃弮鍫濈妞ゆ柨妲堣閺屾盯鍩勯崗鐙€浜Λ鍕吋閸モ晝锛濇繛杈剧到婢瑰﹪宕曢幇鐗堢厱闁靛ǹ鍎遍。宕囩磼椤旂⒈鍎忔い鎾冲悑瀵板嫮鈧綆浜栭崑鎾绘煥鐎c劋绨婚梺鐟版惈缁夊爼藝閿旈敮鍋撳▓鍨灈闁诲繑绻堥崺鐐哄箣閿曗偓閻擄繝鏌涢埄鍐炬畼濞寸媭鍨跺娲川婵犲海鍔堕梺鍛婃处閸欏骸煤閸涘﹣绻嗛柕鍫濈箳閸掍即鏌涢悤浣哥仸鐎规洘鍔欏畷褰掝敃閿濆懎浼庢繝纰樻閸ㄦ娊宕㈣缁傚秵銈i崘鈺佲偓鍨叏濡厧浜鹃悗姘炬嫹

关系数据库的世界是一个表与集合、表与集合上的运算占统治地位的世界。数据库是一个表的集合,而表又是行和列的集合。在发布一条SELECT 查询从表中进行检索行时,得到另一个行和列的集合。这些都是一些抽象的概念,对于数据库系统用来操纵表中数据的基本
表示没有多少参考价值。另一个抽象概念是,表上的运算都同时进行;查询是一种概念性的集合运算,并且集合论中没有时间概念。当然,现实世界是相当不同的。数据库管理系统实现了抽象的概念,但是在实际的硬件
范围内要受到实际的物理约束。结果是,查询要花时间,有时要花很长的时间。而人类很容易不耐烦,不喜欢等待,因此我们丢下了集合上的那些瞬间的数学运算的抽象世界去寻求加速查询的方法。幸运的是,有几种加速运算的技术,可对表进行索引使数据库服务器查找行更快。可考虑怎样充分利用这些索引来编写查询。可编写影响服务器调度机制的查询,使来自多个客户机的查询协作得更好。我们思考基本硬件怎样运行,以便想出怎样克服其物理约束对性能进行改善的方法。
这些正是本章所要讨论的问题,其目标是优化数据库系统的性能,使其尽可能快地处理各种查询。MySQL(和PHP搭配之最佳组合) 已经相当快了,但即使是最快的数据库,在人的设计下还能运行得更快。
4.1 使用索引
我们首先讨论索引,因为它是加快查询的最重要的工具。还有其他加快查询的技术,但是最有效的莫过于恰当地使用索引了。在MySQL(和PHP搭配之最佳组合) 的邮件清单上,人们通常询问关于使查询更快的问题。在大量的案例中,都是因为表上没有索引,一般只要加上索引就可以立即解决问题。但这样也并非总是有效,因为优化并非总是那样简单。然而,如果不使用索引,在许多情形下,用其他手段改善性能只会是浪费时间。应该首先考虑使用索引取得最大的性能改善,然后再寻求其他可能有帮助的技术。
本节介绍索引是什么、它怎样改善查询性能、索引在什么情况下可能会降低性能,以及怎样为表选择索引。下一节,我们将讨论MySQL(和PHP搭配之最佳组合) 的查询优化程序。除了知道怎样创建索引外,了解一些优化程序的知识也是有好处的,因为这样可以更好地利用所创建的索引。某些编写查询的方法实际上会妨碍索引的效果,应该避免这种情况出现。(虽然并非总会这样。有时也会希望忽略优化程序的作用。我们也将介绍这些情况。)
4.1.1 索引的益处
让我们从一个无索引的表着手来考察索引是怎样起作用的。无索引的表就是一个无序的行集。例如,图4 - 1给出了我们在第1章“MySQL(和PHP搭配之最佳组合) 与SQL 介绍” 中首先看到的ad 表。这个表上没有索引,因此如果我们查找某个特定公司的行时,必须查看表中的每一行,看它是否与所需的值匹配。这是一个全表扫描,很慢,如果表中只有少数几个记录与搜索条件相匹配,则其效率是相当低的。(和PHP搭配之最佳组合)数据库技术(20)(图一)" />
图4 - 2给出了相同的表,但在表的company_num 列上增加了一个索引。此索引包含表中每行的一项,但此索引是在company_num 上排序的。现在,不需要逐行搜索全表查找匹配的条款,而是可以利用索引进行查找。假如我们要查找公司1 3的所有行,那么可以扫描索引,结果得出3行。然后到达公司1 4的行,这是一个比我们正在查找的要大的号码。索引值是排序的,因此在读到包含1 4的记录时,我们知道不会再有匹配的记录,可以退出了。如果查找一个值,它在索引表中某个中间点以前不会出现,那么也有找到其第一个匹配索引项的定位算法,而不用进行表的顺序扫描(如二分查找法)。这样,可以快速定位到第一个匹配的值,以节省大量搜索时间。数据库利用了各种各样的快速定位索引值的技术,这些技术是什么并不重要,重要的是它们工作正常,索引技术是个好东西。
有人会问,为什么不只对数据文件进行排序,省掉索引文件?这样不也在搜索时产生相同的效果吗?问得好,如果只有单个索引时,
是这样的。不过有可能会用到第二个索引,但同时以两种不同的方法对同一个数据文件进行排序是不可能的。(如,想要一个顾客名的索
引,同时又要一个顾客ID 号或电话号码的索引。)将索引文件作为一个与数据文件独立的实体就解决了这个问题,而且允许创建多个索
引。此外,索引中的行一般要比数据文件中的行短。在插入或删除值时,为保持排序顺序而移动较短的索引值与移动较长的数据行相比更
为容易。(和PHP搭配之最佳组合)数据库技术(20)(图二)" />
这个例子与MySQL(和PHP搭配之最佳组合) 索引表的方法相符。表的数据行保存在数据文件中,而索引值保存在索引文件中。一个表上可有不止一个索引;如果确实有不止一个索引,它们都保存在同一个索引文件中。索引文件中的每个索引由排过序的用来快速访问数据文件的键记录数组构成。
前面的讨论描述了单表查询中索引的好处,其中使用索引消除了全表扫描,极大地加快了搜索的速度。在执行涉及多个表的连接查询时,索引甚至会更有价值。在单个表的查询中,每列需要查看的值的数目就是表中行的数目。而在多个表的查询中,可能的组合数目极大,因为这个数目为各表中行数之积。
假如有三个未索引的表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 子句相配的那些组合。可能的组合数目为1 0 0 0×1 0 0 0×1 0 0 0(十亿),比匹配数目多一百万倍。很多工作都浪费了,并且这个查询将会非常慢,即使在如像MySQL(和PHP搭配之最佳组合) 这样快的数据库中执行也会很慢。而这还是每个表中只有1000 行的情形。如果每个表中有一百万行时,将会怎样?很显然,这样将会产生性能极为低下的结果。如果对每个表进行索引,就能极大地加速查询进程,因为利用索引的查询处理如下:
1) 如下从表t1 中选择第一行,查看此行所包含的值。
2) 使用表t2 上的索引,直接跳到t2 中与来自t1 的值匹配的行。类似,利用表t3 上的索引,直接跳到t3 中与来自t1 的值匹配的行。
3) 进到表t1 的下一行并重复前面的过程直到t1 中所有的行已经查过。在此情形下,我们仍然对表t1 执行了一个完全扫描,但能够在表t2 和t3 上进行索引查找直接取出这些表中的行。从道理上说,这时的查询比未用索引时要快一百万倍。如上所述,MySQL(和PHP搭配之最佳组合) 利用索引加速了WHERE 子句中与条件相配的行的搜索,或者说在执行连接时加快了与其他表中的行匹配的行的搜索。它也利用索引来改进其他操作的性能:
■ 在使用MIN( ) 和MAX( ) 函数时,能够快速找到索引列的最小或最大值。
■ MySQL(和PHP搭配之最佳组合) 常常能够利用索引来完成ORDER BY 子句的排序操作。
■ 有时,MySQL(和PHP搭配之最佳组合) 可避免对整个数据文件的读取。假如从一个索引数值列中选择值,而且不选择表中其他列。这时,通过对索引值的读取,就已经得到了读取数据文件所要得到的值。没有对相同的值进行两次读取的必要,因此,甚至无需涉及数据文件。
4.1.2 索引的弊端
一般情况下,如果MySQL(和PHP搭配之最佳组合) 能够知道怎样用索引来更快地处理查询,它就会这样做。这表示,在大多数情况下,如果您不对表进行索引,则损害的是您自己的利益。可以看出,作者描绘了索引的诸多好处。但有不利之处吗?是的,有。实际上,这些缺点被优点所掩盖了,
但应该对它们有所了解。
首先,索引文件要占磁盘空间。如果有大量的索引,索引文件可能会比数据文件更快地达到最大的文件尺寸。其次,索引文件加快了检索,但增加了插入和删除,以及更新索引列中的值的时间(即,降低了大多数涉及写入的操作的时间),因为写操作不仅涉及数据行,而且还常常涉及索引。一个表拥有的索引越多,则写操作的平均性能下降就越大。在4 . 4节“有效地装载数据”中,我们将更为详细地介绍这些性能问题,并讨论怎样解决。
4.1.3 选择索引
创建索引的语法已经在3 . 4 . 3节“创建和删除索引”中进行了介绍。这里,我们假定您已经阅读过该节。但是知道语法并不能帮助确定表怎样进行索引。要决定表怎样进行索引需要考虑表的使用方式。本节介绍一些关于怎样确定和挑选索引列的准则:
■ 搜索的索引列,不一定是所要选择的列。换句话说,最适合索引的列是出现在WHERE 子句中的列,或连接子句中指定的列,而不是出现在SELECT 关键字后的选择列表中的列:(和PHP搭配之最佳组合)数据库技术(20)(图三)" />
当然,所选择的列和用于WHERE 子句的列也可能是相同的。关键是,列出现在选择列表中不是该列应该索引的标志。出现在连接子句中的列或出现在形如col1 = col2 的表达式中的列是很适合索引的列。查询中的col_b 和col_c 就是这样的例子。如果MySQL(和PHP搭配之最佳组合) 能利用连接列来优化一个查询,表示它通过消除全表扫描相当可观地减少了表行的组合。
■ 使用惟一索引。考虑某列中值的分布。对于惟一值的列,索引的效果最好,而具有多个重复值的列,其索引效果最差。例如,存放年龄的列具有不同值,很容易区分各行。而用来记录性别的列,只含有“ M”和“F”,则对此列进行索引没有多大用处(不管搜索哪个值,都会得出大约一半的行)。
■ 使用短索引。如果对串列进行索引,应该指定一个前缀长度,只要有可能就应该这样做。例如,如果有一个CHAR(200) 列,如果在前10 个或20 个字符内,多数值是惟一的,那么就不要对整个列进行索引。对前10 个或20 个字符进行索引能够节省大量索引空间,也可能会使查询更快。较小的索引涉及的磁盘I/O 较少,较短的值比较起来更快。更为重要的是,对于较短的键值,索引高速缓存中的块能容纳更多的键值,因此,MySQL(和PHP搭配之最佳组合) 也可以在内存中容纳更多的值。这增加了找到行而不用读取索引中较多块的可能性。(当然,应该利用一些常识。如仅用列值的第一个字符进行索引是不可能有多大好处的,因为这个索引中不会有许多不同的值。)
■ 利用最左前缀。在创建一个n 列的索引时,实际是创建了MySQL(和PHP搭配之最佳组合) 可利用的n 个索引。多列索引可起几个索引的作用,因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。(这与索引一个列的前缀不同,索引一个列的前缀是利用该的前n 个字符作为索引值。)
假如一个表在分别名为s t a t e、city 和zip 的三个列上有一个索引。索引中的行是按state/city/zip 的次序存放的,因此,索引中的行也会自动按state/city 的顺序和state 的顺序存放。这表示,即使在查询中只指定state 值或只指定state 和city 的值,M y S Q L也可以利用索引。因此,此索引可用来搜索下列的列组合:
state,city,zip
state,city
sate
MySQL(和PHP搭配之最佳组合) 不能使用不涉及左前缀的搜索。例如,如果按city 或zip 进行搜索,则不能使用该索引。如果要搜索某个州以及某个zip 代码(索引中的列1和列3),则此索引不能用于相应值的组合。但是,可利用索引来寻找与该州相符的行,以减少搜索范围。
■ 不要过度索引。不要以为索引“越多越好”,什么东西都用索引是错的。每个额外的索引都要占用额外的磁盘空间,并降低写操作的性能,这一点我们前面已经介绍过。在修改表的内容时,索引必须进行更新,有时可能需要重构,因此,索引越多,所花的时间越长。如果有一个索引很少利用或从不使用,那么会不必要地减缓表的修改速度。此外,MySQL(和PHP搭配之最佳组合) 在生成一个执行计划时,要考虑各个索引,这也要费时间。创建多余的索引给查询优化带来了更多的工作。索引太多,也可能会使MySQL(和PHP搭配之最佳组合) 选择不到所要使用的最好索引。只保持所需的索引有利于查询优化。如果想给已索引的表增加索引,应该考虑所要增加的索引是否是现有多列索引的最左索引。如果是,则就不要费力去增加这个索引了,因为已经有了。
■ 考虑在列上进行的比较类型。索引可用于“ <”、“ < = ”、“ = ”、“ > =”、“ > ”和BETWEEN 运算。在模式具有一个直接量前缀时,索引也用于LIKE 运算。如果只将某个列用于其他类型的运算时(如STRCMP( )),对其进行索引没有价值。
赞助商链接