仓酷云

标题: 公布MySQL查询优化系列讲座之查询优化器 [打印本页]

作者: 因胸联盟 时间: 2015-1-16 20:11
标题: 公布MySQL查询优化系列讲座之查询优化器
到2009年，甲骨文的数据库Oracle已经诞生了30周年，而MySQL却连它的一半时间都没有。微软的SQLServer仅仅比MySQL大两年，但是SQLServer的发布是建立在Sybase的基础上。　　当你提交一个查询的时分，MySQL会剖析它，看是不是能够做一些优化使处置该查询的速率更快。这一部分将先容查询优化器是怎样事情的。假如你想晓得MySQL接纳的优化手腕，能够检察MySQL参考手册。
　　固然，MySQL查询优化器也使用了索引，可是它也利用了别的一些信息。比方，假如你提交以下所示的查询，那末不管数据表有多年夜，MySQL实行它的速率城市十分快：

SELECT*FROMtbl_nameWHERE0;
　　在这个例子中，MySQL检察WHERE子句，熟悉到没有切合查询前提的数据行，因而基本就不思索搜刮数据表。你能够经由过程供应一个EXPLAIN语句看到这类情形，这个语句让MySQL显现本人实行的但实践上没有真正地实行的SELECT查询的一些信息。假如要利用EXPLAIN，只必要在EXPLAIN单词放在SELECT语句的后面：
mysql>EXPLAINSELECT*FROMtbl_nameWHERE0G
***************************1.row***************************
id:1
select_type:SIMPLE
table:NULL
type:NULL
possible_keys:NULL
key:NULL
key_len:NULL
ref:NULL
rows:NULL
Extra:ImpossibleWHERE
　　一般情形下，EXPLAIN前往的信息比下面的信息要多一些，还包含用于扫描数据表的索引、利用的联合范例、每张数据表中估量必要反省的数据行数目等非空（NULL）信息。
　　优化器是怎样事情的
　　MySQL查询优化器有几个方针，可是个中最次要的方针是尽量地利用索引，而且利用最严厉的索引来打消尽量多的数据行。你的终极方针是提交SELECT语句查找数据行，而不是扫除数据行。优化器试图扫除数据行的缘故原由在于它扫除数据行的速率越快，那末找到与前提婚配的数据行也就越快。假如可以起首举行最严厉的测试，查询就能够实行地更快。假定你的查询查验了两个数据列，每一个列上都有索引：
SELECTcol3FROMmytable
WHEREcol1=’somevalue’ANDcol2=’someothervalue’;
　　假定col1上的测试婚配了900个数据行，col2上的测试婚配了300个数据行，而同时举行的测试只失掉了30个数据行。先测试Col1会有900个数据行，必要反省它们找到个中的30个与col2中的值婚配纪录，个中就有870次是失利了。先测试col2会有300个数据行，必要反省它们找到个中的30个与col1中的值婚配的纪录，只要270次是失利的，因而必要的盘算和磁盘I/O更少。其了局是，优化器会先测试col2，由于如许做开支更小。
　　你能够经由过程上面一个引导匡助优化器更好天时用索引：
　　只管对照数据范例不异的数据列。当你在对照操纵中利用索引数据列的时分，请利用数据范例不异的列。不异的数据范例比分歧范例的功能要高一些。比方，INT与BIGINT是分歧的。CHAR(10)被以为是CHAR(10)或VARCHAR(10)，可是与CHAR(12)或VARCHAR(12)分歧。假如你所对照的数据列的范例分歧，那末可使用ALTERTABLE来修正个中一个，使它们的范例相婚配。
　　尽量地让索引列在对照表达式中自力。假如你在函数挪用大概更庞大的算术表达式前提中利用了某个数据列，MySQL就不会利用索引，由于它必需盘算出每一个数据行的表达式值。偶然候这类情形没法制止，可是良多情形下你能够从头编写一个查询让索引列自力地呈现。
　　上面的WHERE子句显现了这类情形。它们的功效不异，可是关于优化方针来讲就有很年夜差别了：
WHEREmycol<4/2
WHEREmycol*2<4
　　关于第一行，优化器把表达式4/2简化为2，接着利用mycol上的索引来疾速地查找小于2的值。关于第二个表达式，MySQL必需检索出每一个数据行的mycol值，乘以2，接着把了局与4举行对照。在这类情形下，不会利用索引。数据列中的每一个值都必需被检索到，如许才干盘算出对照表达式右边的值。
　　我们看别的一个例子。假定你对date_col列举行了索引。假如你提交一条以下所示的查询，就不会利用这个索引：
SELECT*FROMmytblWHEREYEAR(date_col)<1990;
　　这个表达式不会把1990与索引列举行对照；它会把1990与该数据列盘算出来的值对照，而每一个数据行都必需盘算出这个值。其了局是，没有利用date_col上的索引，由于实行如许的查询必要全表扫描。怎样办理这个成绩呢？只必要利用文今天期，接着就能够利用date_col上的索引来查找列中婚配的值了：
WHEREdate_col<’1990-01-01’
　　可是，假定你没有特定的日期。你大概但愿找到一些与明天相隔流动的几天的日期的纪录。表达这类范例的对照有良多种办法--它们的效力其实不同。上面就有三种：
WHERETO_DAYS(date_col)-TO_DAYS(CURDATE())<cutoff
WHERETO_DAYS(date_col)<cutoff+TO_DAYS(CURDATE())
WHEREdate_col<DATE_ADD(CURDATE(),INTERVALcutoffDAY)
　　关于第一行，不会用到索引，由于每一个数据行都必需检索以盘算出TO_DAYS(date_col)的值。第二行要好一些。Cutoff和TO_DAYS(CURDATE())都是常量，因而在处置查询之前，对照表达式的右侧能够被优化器一次性盘算出来，而不必要每一个数据行都盘算一次。可是date_col列仍旧呈现在函数挪用中，它制止了索引的利用。第三行是这几其中最好的。一样，在实行查询之前，对照表达式的右侧能够作为常量一次性盘算出来，可是如今它的值是一个日期。这个值能够间接与date_col值举行对照，不再必要转换整天数了。在这类情形下，会利用索引。
　　在LIKE形式的开首不要利用通配符。有些字符串搜刮利用以下所示的WHERE子句：
WHEREcol_nameLIKE’%string%’
　　假如你但愿找到那些呈现在数据列的任何地位的字符串，这个语句就是对的。可是不要由于习气而复杂地把"%"放在字符串的双方。假如你在查找呈现在数据列开首的字符串，就删失落后面的"%"。假定你要查找那些相似MacGregor或MacDougall等以"Mac"开首的名字。在这类情形下，WHERE子句以下所示：
WHERElast_nameLIKE’Mac%’
　　优化器检察该形式中词首的文本，并利用索引找到那些与上面的表达式婚配的数据行。上面的表达式是利用last_name索引的另外一种情势：
WHERElast_name>=’Mac’ANDlast_name<’Mad’

　　这类优化不克不及使用于利用了REGEXP操纵符的形式婚配。REGEXP表达式永久不会被优化。
　　匡助优化器更好的判别索引的效力。在默许情形下，当你把索引列的值与常量举行对照的时分，优化器会假定键值在索引外部是匀称散布的。在决意举行常量对照是不是利用索引的时分，优化器会疾速地反省索引，估量出会用到几个实体（entry）。对应MyISAM、InnoDB和BDB数据表来讲，你可使用ANALYZETABLE让服务器实行对键值的剖析。它会为优化器供应更好的信息。
　　利用EXPLAIN考证优化器的操纵。EXPLAIN语句能够告知你是不是利用了索引。当你试图用别的的体例编写语句或反省增加索引是不是会进步查询实行效力的时分，这些信息对你是有匡助的。
　　在需要的时分给优化器一些提醒。一般情形下，MySQL优化器自在地决意扫描数据表的序次来最快地检索数据行。在有些场所中优化器没有作出最好选择。假如你发觉这类征象产生了，就能够利用STRAIGHT_JOIN关头字来重载优化器的选择。带有STRAIGHT_JOIN的联合相似于交织联合，可是强制数据表依照FROM子句中指定的序次来联合。
　　在SELECT语句中有两个中央能够指定STRAIGHT_JOIN。你能够在SELECT关头字和选择列表之间的地位指定，如许会对语句中一切的交织联合发生影响；你也能够在FROM子句中指定。上面的两个语句功效不异：
SELECTSTRAIGHT_JOIN...FROMt1,t2,t3...;
SELECT...FROMt1STRAIGHT_JOINt2STRAIGHT_JOINt3...;
　　分离在带有STRAIGHT_JOIN和不带STRAIGHT_JOIN的情形下运转这个查询；MySQL大概由于甚么缘故原由没有依照你以为最好的序次利用索引（你可使用EXPLAIN来反省MySQL处置每一个语句的实行企图）。
　　你还可使用FORCEINDEX、USEINDEX或IGNOREINDEX来引导服务器怎样利用索引。
　　使用优化器加倍完美的地区。MySQL能够实行联合和子查询，可是子查询是比来才撑持的，是在MySQL4.1中增加的。因此在良多情形下，优化器春联结操纵的调剂比对子查询的调剂要好一些。当你的子查询实行地很慢的时分，这就是一条实践的提醒。有一些子查询可使用逻辑上相称的联合来从头表达。在可行的情形下，你能够把子查询从头改写为联合，看是不是实行地快一些。
　　测试查询的备用情势，屡次运转。当你测试查询的备用情势的时分（比方，子查询与同等的联合操纵对照），每种体例都应当屡次运转。假如两种情势都只运转了一次，那末你一般会发明第二个查询比第一个快，这是由于第一个查询失掉的信息仍旧保存在缓存中，以致于第二个查询没有真正地从磁盘上读取数据。你还应当在体系负载绝对安稳的时分运转查询，以免体系中别的的事件影响了局。
　　制止过分地利用MySQL主动范例转换。MySQL会实行主动的范例转换，可是假如你可以制止这类转换操纵，你失掉的功能就更好了。比方，假如num_col是整型数据列，那末上面这些查询将前往不异的了局：
SELECT*FROMmytblWHEREnum_col=4;
SELECT*FROMmytblWHEREnum_col=’4’;
　　可是第二个查询触及到了范例转换。转换操纵自己为了把整型和字符串型转换为双精度型举行对照，使功能好转了。更严峻的情形是，假如num_col是索引的，那末触及到范例转换的对照操纵不会利用索引。
　　相反范例的对照操纵（把字符串列与数值对照）也会制止索引的利用。假定你编写了以下所示的查询：
SELECT*FROMmytblWHEREstr_col=4;
　　在这个例子中，不会利用str_col上的索引，由于在把str_col中的字符串值转换成数值的时分，大概有良多值即是4（比方’4’、’4.0’和’4th’）。分辩哪些值切合请求的独一举措是读取每一个数据行并实行对照操纵。
　　利用EXPLAIN来反省优化器的操纵
　　EXPLAIN关于懂得优化器天生的、用于处置语句的实行企图的外部信息是很有匡助的。在这一部分中，我们将注释EXPLAIN的两种用处：
　　・检察接纳分歧的体例编写的查询是不是影响了索引的利用。
　　・检察向数据表增加索引对优化器天生高效力实行企图的才能的影响。
　　这一部分只会商与示例相干的EXPLAIN输出字段。
　　后面，在"优化器是怎样事情的"部分中我们得出的概念是，你编写表达式的体例将决意优化器是不是能利用可用的索引。出格是下面的会商利用了上面三个逻辑相称的WHERE子句的例子，只要第三个同意利用索引：
WHERETO_DAYS(date_col)-TO_DAYS(CURDATE())<cutoff
WHERETO_DAYS(date_col)<cutoff+TO_DAYS(CURDATE())
WHEREdate_col<DATE_ADD(CURDATE(),INTERVALcutoffDAY)
　　EXPLAIN同意你检察编写表达式的某种体例是不是比别的的体例好一些。为了看到了局，让我们分离用这三个WHERE子句搜刮成员表中过时的数据列值，把cutoff值设为30天。为了看到索引的利用和表达式编写体例之间的干系，我们起首对expiration列举行索引：
mysql>ALTERTABLEmemberADDINDEX(expiration);
　　接着在每一个表达式情势上利用EXPLAIN，看优化器天生了甚么样的实行企图：
mysql>EXPLAINSELECT*FROMMEMBER
->WHERETO_DAYS(expiration)-TO_DAYS(CURDATE())<30G
***************************1.row***************************
id:1
select_type:SIMPLE
table:MEMBER
type:ALL
possible_keys:NULL
key:NULL
key_len:NULL
ref:NULL
rows:102
Extra:Usingwhere
mysql>EXPLAINSELECT*FROMMEMBER
->WHERETO_DAYS(expiration)<30+TO_DAYS(CURDATE())G
***************************1.row***************************
id:1
select_type:SIMPLE
table:MEMBER
type:ALL
possible_keys:NULL
key:NULL
key_len:NULL
ref:NULL
rows:102
Extra:Usingwhere
mysql>EXPLAINSELECT*FROMMEMBER
->WHEREexpiration<DATE_ADD(CURDATE(),INTERVAL30DAY)G
***************************1.row***************************
id:1
select_type:SIMPLE
table:MEMBER
type:range
possible_keys:expiration
key:expiration
key_len:4
ref:NULL
rows:6
Extra:Usingwhere
　　下面的了局显现，后面两个语句没有利用索引。范例（type）值标明了将怎样从数据表中读失信息。ALL意味着"将反省一切的纪录"。也就是说，它会实行全表扫描，没有益用索引。每一个与键相干的列都是NULL也标明没有利用索引。
　　与此构成对照的是，第三个语句的了局显现，接纳这类体例编写的WHERE子句，优化器可使用expiration列上的索引：
　　・范例（type）值标明它可使用索引来搜刮特定局限的值（小于右侧表达式给定的值）。
　　・大概键（possible_keys）和键（key）值显现expiration上的索引已被思索作为备选索引，而且它也是真正利用的索引。
　　・行数（rows）值显现优化器估量本人必要反省6个数据行来处置该查询。这比后面两个实行企图的102小良多。
　　EXPLAIN的第二种用处是检察增加索引是不是能匡助优化器更高效力地实行语句。我将利用两个未被索引的数据表。它充足显现创建索引的效力。不异的划定规矩能够使用于触及多表的加倍庞大的联合操纵。
　　假定我们有两个数据表t1和t2，每一个有1000行，包括的值从1到1000。上面的查询查找出两个表中值不异的数据行：
mysql>SELECTt1.i1,t2.i2FROMt1,t2WHEREt1.i1=t2.i2;
+------+------+
|i1|i2|
+------+------+
|1|1|
|2|2|
|3|3|
|4|4|
|5|5|
...
　　两个表都没有索引的时分，EXPLAIN发生上面的了局：
mysql>EXPLAINSELECTt1.i1,t2.i2FROMt1,t2WHEREt1.i1=t2.i2G
***************************1.row***************************
id:1
select_type:SIMPLE
table:t1
type:ALL
possible_keys:NULL
key:NULL
key_len:NULL
ref:NULL
rows:1000
Extra:
***************************2.row***************************
id:1
select_type:SIMPLE
table:t2
type:ALL
possible_keys:NULL
key:NULL
key_len:NULL
ref:NULL
rows:1000
Extra:Usingwhere
　　范例列中的ALL标明要举行反省一切数据行的全表扫描。大概键列中的NULL标明没有找到用于进步查询速率的备选索引（键、键长度和参考列都是NULL也是由于短少符合的索引）。Usingwhere标明利用WHERE子句中的信息来辨认及格的数据行。
　　这段信息告知我们，优化器没无为进步实行查询的效力找就任何有效的信息：
　　・它将对t1表举行全表扫描。
　　・关于t1中的每行，它将实行t2的全表扫描，利用WHERE子句中的信息辨认出及格的行。
　　行数值显现了优化器估量的每一个阶段查询必要反省的行数。T1的估量值是1000，由于1000能够完玉成表扫描。类似地，t2的估量值也是1000，可是这个值是关于t1的每行的。换句话说，优化器所估量的处置该查询所必要反省的数据行组合的数目是1000×1000，也就是一百万。这会形成很年夜的华侈，由于实践上只要1000个组合切合WHERE子句的前提。
　　为了使这个查询的效力更高，给个中一个联合列增加索引偏重新实行EXPLAIN语句：
mysql>ALTERTABLEt2ADDINDEX(i2);
mysql>EXPLAINSELECTt1.i1,t2.i2FROMt1,t2WHEREt1.i1=t2.i2G
***************************1.row***************************
id:1
select_type:SIMPLE
table:t1
type:ALL
possible_keys:NULL
key:NULL
key_len:NULL
ref:NULL
rows:1000
Extra:
***************************2.row***************************
id:1
select_type:SIMPLE
table:t2
type:ref
possible_keys:i2
key:i2
key_len:5
ref:sampdb.t1.i1
rows:10
Extra:Usingwhere;Usingindex
　　我们能够看到功能进步了。T1的输入没有改动（标明仍是必要举行全表扫描），可是优化器处置t2的体例就有所分歧了：
　　・范例从ALL改动为ref，意味着可使用参考值（来自t1的值）来实行索引查找，定位t2中及格的数据行。
　　・参考值在参考（ref）字段中给出了：sampdb.t1.i1。
　　・行数值从1000下降到了10，显现出优化器信任关于t1中的每行，它只必要反省t2中的10行（这是一个失望的估量值。实践上，在t2中只要一行与t1中数据行婚配。我们在前面会看到怎样匡助优化器改良这个估量值）。数据行组合的全体估量值使1000×10＝10000。它比后面的没有索引的时分估量出来的一百万很多多少了。
　　对t1举行索引有代价吗？实践上，关于这个特定的联合操纵，扫描一张表是需要的，因而没有需要对t1创建索引。假如你想看到效果，能够索引t1.i1并再次运转EXPLAIN：
mysql>ALTERTABLEt1ADDINDEX(i1);
mysql>EXPLAINSELECTt1.i1,t2.i2FROMt1,t2WHEREt1.i1=t2.i2G
***************************1.row***************************
id:1
select_type:SIMPLE
table:t1
type:index
possible_keys:i1
key:i1
key_len:5
ref:NULL
rows:1000
Extra:Usingindex
***************************2.row***************************
id:1
select_type:SIMPLE
table:t2
type:ref
possible_keys:i2
key:i2
key_len:5
ref:sampdb.t1.i1
rows:10
Extra:Usingwhere;Usingindex
　　下面的输入与后面的EXPLAIN的输入类似，可是增加索引对t1的输入有一些改动。范例从NULL改成了index，附加（Extra）从空的改成了Usingindex。这些改动标明，只管对索引的值仍旧必要实行全表扫描，可是优化器仍是能够间接从索引文件中读取值，依据不必要利用数据文件。你能够从MyISAM表中看到这类了局，在这类情形下，优化器晓得本人只扣问索引文件就可以够失掉一切必要的信息。关于InnoDB和BDB表也有如许的了局，在这类情形下优化器能够独自利用索引中的信息而不必搜刮数据行。
　　我们能够运转ANALYZETABLE使优化器进一步优化估量值。这会引发服务器天生键值的静态散布。剖析下面的表并再次运转EXPLAIN失掉了更好的估量值：
mysql>ANALYZETABLEt1,t2;
mysql>EXPLAINSELECTt1.i1,t2.i2FROMt1,t2WHEREt1.i1=t2.i2G
***************************1.row***************************
id:1
select_type:SIMPLE
table:t1
type:index
possible_keys:i1
key:i1
key_len:5
ref:NULL
rows:1000
Extra:Usingindex
***************************2.row***************************
id:1
select_type:SIMPLE
table:t2
type:ref
possible_keys:i2
key:i2
key_len:5
ref:sampdb.t1.i1
rows:1
Extra:Usingwhere;Usingindex
　　在这类情形下，优化器估量在t2中与t1的每一个值婚配的数据行只要一个。
　　重载优化历程
　　这个历程听起来过剩，可是偶然候你仍是但愿往失落某些MySQL优化举动的：
　　重载优化器的表联合序次。利用STRAIGHT_JOIN强制优化器依照特定的序次利用数据表。在如许操纵的时分，你必需对数据表举行排序，如许才干包管第一张表是被选择的行数起码的表。假如你不克不及断定被选择行数起码的是哪一张表，那末就把行数最多的放到第一的地位。换句话说，试着对表举行排序，使最有束缚力的选择呈现在最后面。你对大概的备选数据行减少地越早，实行查询的功能就越好。请确保在带有STRAIGHT_JOIN和不带STRAIGHT_JOIN的时分分离实行该查询。偶然候因为某些缘故原由的存在，优化器没有依照你认定的体例联合数据表，STRAIGHT_JOIN也大概没有实践的匡助感化。
　　另外一个大概性是在联合的数据表列表中的某个表的前面利用FORCEINDEX、USEINDEX和IGNOREINDEX调治符来告知MySQL怎样利用索引。这在优化器没有做出准确选择的时分是有效处的。
　　以最小的价值清空一张表。当必要完整地清空一张MyISAM数据表的时分，最快的办法是删除它并使用它的.frm文件中存储的剧本来从头创建它。利用TRUNCATETABLE语句完成：
TRUNCATETABLEtbl_name;
　　经由过程从头创建MyISAM数据表来清空它的这类服务器优化措施使该操纵十分快，由于不必要独自地逐行删除。
　　可是TRUNCATETABLE也带来了一些反作用，在某些情况中是不切合请求的：
　　・TRUNCATETABLE纷歧定可以盘算出被删除的数据列的准确数目。假如你必要这个数值，请利用不带WHERE子句的DELETE语句：
DELETEFROMtbl_name;
　　・可是，经由过程从头创建来清空数据表，它大概会把序号的肇端值设置为1。为了不这类情形，请利用"不优化的"全表DELETE语句，它带有一个恒为真的WHERE子句：
DELETEFROMtbl_nameWHERE1;
　　增加WHERE子句会强制MySQL举行逐行删除，由于它必需盘算出每行的值来判别是不是可以删除它。这个语句实行的速率很慢，可是它却保存了以后的AUTO_INCREMENT序号。
这些过程被存储和运行在数据库服务器上，以减少在客户端的处理过程，从而最大限度地提高了处理能力，因为通常情况下数据库服务器会运行地更快。存储过程并不是MySQL独有的功能，但是这个最近新增加的功能使得这个数据库比以前更具吸引力了。

作者: 再见西城 时间: 2015-1-18 18:29
不好!如果出了错;不好调试;不好处理!其实web开发将代码分为3层:web层;业务逻辑层和数据访问层;一般对数据库的操作都在数据访问层来做;这样便于调试和维护!而且将来如果是换了数据库的话;你只需要改数据层的代码;其他层的基本可以不变!要是你在jsp中直接调用sql数据库;那么如果换了数据库呢?岂不都要改?如果报了异常呢?怎么做异常处理?

作者: 若相依 时间: 2015-1-23 07:06
作了些试验，发现使用CLR的存储过程或函数在达到一定的阀值的时候，系统性能会呈指数级下滑！这是非常危险的！只使用几个可能没有问题，当一旦大规模使用会造成严重的系统性能问题！

作者: 乐观 时间: 2015-1-31 15:36
所以你总能得到相应的升级版本，来满足你的需求。

作者: 深爱那片海 时间: 2015-2-6 20:15
原来的计算字段其实和虚拟字段很像。只是管理方面好了而已，性能方面提高不多。但是SQL2005提供了计算字段的持久化，这就提高了查询的性能，但是会加重insert和update的负担。OLTP慎用。OLAP可以大规模使用。

作者: 蒙在股里 时间: 2015-2-18 13:35
可能有的朋友会抱怨集成的orderby，其实如果使用ranking函数,Orderby是少不了的。如果担心Orderby会影响效率，可以为orderby的字段建立聚集索引，查询计划会忽略orderby操作（因为本来就是排序的嘛）。

作者: 变相怪杰 时间: 2015-3-6 07:47
所以你总能得到相应的升级版本，来满足你的需求。

作者: 谁可相欹 时间: 2015-3-12 22:54
从底层原理到表层引用，书籍多的很。个人认为没有什么那本书好？这样的说法。主要看和个人的学习方法是否适合。

作者: 第二个灵魂 时间: 2015-3-20 05:10
作了些试验，发现使用CLR的存储过程或函数在达到一定的阀值的时候，系统性能会呈指数级下滑！这是非常危险的！只使用几个可能没有问题，当一旦大规模使用会造成严重的系统性能问题！

欢迎光临仓酷云 (http://ckuyun.com/)