发布MySQL Internals Optimizer

客户还是可以使用DBaaS系统所能提供的所有能力。数据库云服务消除了组织对专职人员、本地数据库存储设备的需要。他们不必安装、配置和维护任何软硬件。优化器（TheOptimizer）
这篇形貌MySQL查询优化器的事情道理。MySQL查询优化器次要为实行的查询定夺最无效的线路（routine，走向）。

一。源代码和观点

这部分会商优化器关头观点，术语，及在MySQL源代码怎样对应的。

1.界说
广义界说：优化器，就是DBMS为查询时定夺要往哪一种实行路径的一系列线路。
MySQL是常常调剂查询的线路，以是你得把这篇形貌的逻辑和在源代码里的做对照。为了使对照简单些，这里会包括相干文件和路径的注解，比方源代码/sql/sql_select.cc，optimize_cond()函数。
当一个查询被转成另外一种查询时，其了局是一样的，就会产生语句转化。以下这个查询
SELECT...WHERE5=a
就会被转化成为

SELECT...WHEREa=5
最分明的语句转化是少的，有些语句转化，是为了更快的实行。

2.优化器源代码
以下伪代码显现了/sql/sql_select.cc中handle_select()函数的逻辑布局。（源代码/sql/sql_select.cc处置SQL查询）
handle_select()
mysql_select()
JOIN::PRepare()
setup_fields()
JOIN::optimize()/*optimizerisfromhere...*/
optimize_cond()
opt_sum_query()
make_join_statistics()
get_quick_record_count()
choose_plan()
/*Findthebestwayto[url=http://www.ckuyun.com/article.asp?typeid=173]access[/url]tables*/
/*asspecifiedbytheuser.*/
optimize_straight_join()
best_access_path()
/*Finda(sub-)optimalplanamongallorsubset*/
/*ofallpossiblequeryplanswheretheuser*/
/*controlstheexhaustivenessofthesearch.*/
greedy_search()
best_extension_by_limited_search()
best_access_path()
/*Performanexhaustivesearchforanoptimalplan*/
find_best()
make_join_select()/*...tohere*/
JOIN::exec()
缩举行显现了哪一个函数挪用哪一个函数，如handle_select()函数挪用mysql_select()函数，mysql_select()函数会挪用JOIN::prepare()、JOIN::optimize()、JOIN::exec()，和类推。mysql_select()函数的第一部分是挪用JOIN::prepare()，此函数用来高低文剖析、元数据创建和一些语句转化。查询优化器函数JOIN::optimize()和其一切优化处置中的子线路。当实行完JOIN::optimize()函数后，JOIN::exec()接受并完成JOIN::optimize()函数优化定夺后的实行事情。
固然有JOIN字呈现，实在查询优化器的事情会处置一切的查询范例，不但单JOIN连接查询。

二。主要优化

这部分会商服务器实行的最主要优化。


1.优化常数干系
常数等值传送

以下的表达式会产生语句转化：
WHEREcolumn1=column2ANDcolumn2=x
这类表达式，尽人皆知，假如A=B&&B=C=>A=C（可等值传送），上句表达式转化后酿成：

WHEREcolumn1=xANDcolumn2=x

当且仅当，操纵符为以下的任何一个，在column1<操纵符>column2前提中就会产生语句转化：
=,<,>,<=,>=,,<=>,LIKE
注重：等值传送的转化，不合适于BETWEEN。大概也不合适于LIKE，这是后话。


常数等值传送一样产生在轮回中，前一步传送的输入作为后一步传送的输出。

源代码见/sql/sql_select.cc，change_cond_ref_to_const()函数。或/sql/sql_select.cc，propagate_cond_constants()函数。

剔除逝世代码

老是TRUE的前提会产生语句转化，如：
WHERE0=0ANDcolumn1=y
这类情形下，第一个前提会被剔除，最初为：

column1=y
源代码见/sql/sql_select.cc，remove_eq_conds()。

老是FLASE的前提也会产生语句转化，如：
WHERE(0=ANDs1=ORs1=7
小括号和前两个前提老是FLASE，最初为：

WHEREs1=7

另有一些情形下，当WHERE语句中代表不成能的前提，查询优化器大概会全体剔除语句，以下：
WHERE(0=ANDs1=5)
由于这前提永久不为TRUE，在EXPLAIN剖析中会显现ImpossibleWHERE。复杂地说，MySQL会说WHERE前提被优化过。


假如一个字段不克不及为NULL，优化器会剔除一切不相干的ISNULL的前提，如许
WHEREnot_null_columnISNULL
这类前提总为FLASE情形；且

WHEREnot_null_columnISNOTNULL
这类前提总为TRUE情形，以是这类字段查询的前提也被剔除。这类判别是很奇妙的。举个例：在一个OUTJOIN外连接，被界说成NOTNULL字段仍旧含有NULL值，优化器就会独自扫除ISNULL前提在这类特别情形中。

优化器不会反省一切的ImpossibleWHERE的前提，由于这方面大概性太多了。比方：

CREATETABLETable1(column1CHAR(1));
...
SELECT*FROMTable1WHEREcolumn1=Popgo;
优化器不会剔除这类查询的前提，即便在CREATETABLE界说中使之成为不成能的前提。


可兼并的常数值

以下表达式会产生语句转化：
WHEREcolumn1=1+2
最初为：

WHEREcolumn1=3
在之前说的常数等值传送，优化器很简单将这类查询语句兼并在一同。这操纵就简化了却果。

常数值和常数表
MySQL常数值，偶然不但单指在查询的SQL语句的字面意义上，也可在常数表（constanttables）的内容里。常数表（constanttables）被界说为：
1。无纪录或一笔记录的表

2。表的表达式被WHERE前提束缚，并且包括的表达式情势column="constant"，大概表的主键的一切字段，大概任何独一键的一切字段（独一键的字段界说为NOTNULL）

比方，Table0表的界说包括：
...PRIMARYKEY(column1,column2)
然后，查询表达式：

FROMTable0...WHEREcolumn1=5ANDcolumn2=7...
会前往常数表（constanttables）。更多复杂地，假如Table1表的界说包括：

...unique_not_null_columnINTNOTNULLUNIQUE
然后，查询表达式：

FROMTable1...WHEREunique_not_null_column=5
也会前往常数表（constanttables）。


这个划定规矩指一个常数表（constanttables）最多有一笔记录值。MySQL就会优先评价是不是为常数表（constanttables），并找出谁人值。如许，MySQL会将这值拔出查询语句。如这个例子：
SELECTTable1.unique_not_null_column,Table2.any_column
FROMTable1,Table2
WHERETable1.unique_not_null_column=Table2.any_column
ANDTable1.unique_not_null_column=5;
MySQL评价这语句时，起首就会发明，依照常数表（constanttables）的第二点界说，查询前提为unique_not_null_column的表Table1是一个常数表（constanttables），它就会获得这个值。

假如取值失利，也就是在表Table1里unique_not_null_column=没值，EXPLAIN后了局：

ImpossibleWHEREnoticedafterreadingconsttables
相反，假如取值乐成，也就是在表Table1里unique_not_null_column=为一笔记录值，MySQL会转化为以下语句：

SELECT5,Table2.any_column
FROMTable1,Table2
WHERE5=Table2.any_column
AND5=5;

现实上，这是一个很好的例子。优化器因后面提到的常数等值传送举行一些语句转化。别的，为何要先形貌常数等值传送，由于它在MySQL确认甚么是常数表（constanttables）前就先辈行了。优化器步骤的按次，偶然是有不同。

固然良多查询都没常数表（constanttables）参考。应当切记，今后不管甚么时分，常数constant字被说起，它是指任何一个字面上的值大概一个常数表（constanttables）的内容。

2.优化JOIN连接

这部分会商优化JOIN连接的分歧办法。注重：JOIN连接不但单指JOIN范例，而是一切前提查询的范例。有些人更喜好叫accesstype。

断定JOIN连接范例

当评价查询前提表达式时，MySQL会断定它是属于哪一个JOIN连接范例。
以下有纪录在档的JOIN范例，从最好到最坏的排序上去：
system：常数表（constanttables）的system范例
const：常数表（constanttables）
eq_ref：相称干系的独一或主键索引
ref：相称干系的索引，此索引的值不克不及为NULL
ref_or_null：相称干系的索引，此索引的值大概为NULL
range：有联系关系的索引，如BETWEEN，IN，>=，LIKE等
index：按次扫描索引
ALL：按次扫描全部表数据
源代码见/sql/sql_select.h，enumjoin_type{}。别的，另有一小部分没纪录在档，为了子查询的JOIN连接范例。

优化器使用JOIN连接范例选择一个驱动表达式，以下：
SELECT*
FROMTable1
WHEREindexed_column=ANDunindexed_column=6
假如indexed_column有对照好的JOIN连接范例，它更大概成为驱动表达式。对它来讲，你也会碰到各类分歧的破例，但对这句形貌，是第一个复杂的优化法例。


对驱动来讲，甚么是最成心义的呢？以下查询时的两条实行路径：

最坏实行企图：扫描读表的一切行。这也叫Table1的按次扫描或复杂表扫描。查询每行，反省indexed_column和unindexed_column两列的值是不是婚配查询的前提。

最好实行企图：经由过程索引，检索哪些有indexed_column=值的纪录。这也叫被索引的搜刮。查询每行，反省unindexed_column列的值是不是婚配查询的前提。


被索引的搜刮一般比按次扫描挪用更少的会见，并且假如会见的表是伟大的，索引又是独一的，如许表的会见长短常少的。这也是为何有好实行企图的会见表是更好的，而且这也是为何经常把indexed_column做为驱动。

连接和会见的办法
在单表搜刮中，坏的JOIN连接实行选择比坏的实行选择形成更多的功能伤害。以是MySQL开辟者发了更多的工夫确保查询中的表以一种最好按次被连接和此最好会见办法（经常被称会见路径）被选择作为反省表数据。表连接的流动按次和响应的一切表的表会见办法的组合，叫做查询实行企图（QEP）。查询优化器的目标就是在一切大概的企图中找出一个最好的QEP。JOIN连接优前后有一些惯例的观点。
每一个企图或企图的部分，被界说成COST本钱。企图的本钱大略地反应了盘算依照企图的查询所必要的资本，个中次要的要素是当盘算查询时以是会见的纪录总数。一旦我们有举措分派到分歧的本钱QEPs，我们有举措对它们举行对照。如许，优化器的目标就是在一切大概的企图中找到一个本钱最低的QEP。

在MySQL中，完成了最好QEP搜刮是自下而上的体例。优化器起首确认一张表的一切企图，接着两张表的一切企图，以此类推，直到创建一个完全的最好QEP。查询企图包含在查询中只要部分表和限制（predicates），被称为部分企图（partialplans）。优化器依附着一点现实：越多表被加到部分企图（partialplans），本钱就越高（注：本钱高，实行效力就低）。这使得优化器可扩大更多的表只用较低本钱的部分企图（partialplans）类比以后最好的完全企图。
完成搜刮一条最好QEP的关头线路见sql/sql_select.cc，find_best()。它实行了一切大概企图的细致搜刮，从而包管它终极将找到一个最好的一条。


以下我们形貌find_best()办法的伪代码。这是递回的，以是一些输出变量被标志了，以标明到今朝为止，他们夙昔一个的迭代来的。
remaining_tables={t1,...,tn};/*alltablesreferencedinaquery*/
procedurefind_best(
partial_planin,/*in,partialplanoftables-joined-so-far*/
partial_plan_cost,/*in,costofpartial_plan*/
remaining_tables,/*in,setoftablesnotreferencedinpartial_plan*/
best_plan_so_far,/*in/out,bestplanfoundsofar*/
best_plan_so_far_cost)/*in/out,costofbest_plan_so_far*/
{
foreachtableTfromremaining_tables
{
/*CalculatethecostofusingtableT.Factorsthatthe
optimizertakesintoaccountmayinclude:
Manyrowsintable(bad)
Manykeypartsincommonwithtablessofar(verygood)
RestrictionmentionedintheWHEREclause(good)
Longkey(good)
Uniqueorprimarykey(good)
Full-textkey(bad)
Otherfactorsthatmayatsometimebeworthconsidering:
Manycolumnsinkey
Shortaverage/maximumkeylength
Smalltablefile
Fewlevelsinindex
AllORDERBY/GROUPcolumnscomefromthistable*/
cost=complex-series-of-calculations;
/*Addthecosttothecostsofar.*/
partial_plan_cost+=cost;
if(partial_plan_cost>=best_plan_so_far_cost)
/*partial_plan_costalreadytoogreat,stopsearch*/
continue;
partial_plan=expandpartial_planbybest_access_method;
remaining_tables=remaining_tables-tableT;
if(remaining_tablesisnotanemptyset)
{
find_best(partial_plan,partial_plan_cost,
remaining_tables,
best_plan_so_far,best_plan_so_far_cost);
}
else
{
best_plan_so_far_cost=partial_plan_cost;
best_plan_so_far=partial_plan;
}
}
}
这里优化器使用了一种深度优先搜刮算法。它完成了在FROM语句中评价一切的表。假如评价比起今朝为止最好的评价，变得更差，它将中断搜刮。扫描的按次依附于呈现FROM语句中的表的按次。

源代码见：/sql/table.h，structst_table。

剖析表（ANALYZETABLE）大概会影响到一些优化器定夺的要素。源代码见：/sql/sql_sqlect.cc，make_join_statistics()。

find_best()和greedy_search()的刀切斧砍（straightforward）利用将不会用于LEFTJOIN或RIGHTJOIN。比方，从MySQL4.0.14起，在一些情形下，优化器大概变化LEFTJOIN为STRAIHTJOIN，并互换表的按次。别的见：LEFTJOINandRIGHTJOINOptimization。


RANGE连接范例
有些前提可使用索引，可是在一个键的局限（range）或宽度内。这些称为局限前提，最常看到的是带>，>=，<，<=，IN，LIKE，BETWEEN的查询表达式。


对优化器来讲，以下表达式：
column1IN(1,2,3)
和这个是一样的：

column1=ORcolumn1=ORcolumn1=3
MySQL一样看待这类语句，无需对查询前提的IN到OR或OR到IN做变化。


以下语句，优化器也会用到索引（局限查询rangesearch）
column1LIKEx%
但这类就不可：

column1LIKE%x%
也就是说，假如婚配前提的第一个字符是通配符，那就没局限查询。

一样，以下两个语句也是一样的
column1BETWEEN5AND7
和

column1>=ANDcolumn1<=7

假如查询的前提，检索了太多的索引键，优化器大概变化RANGE连接范例为ALLJOIN连接范例。像这类变化，出格大概在<和>前提和多级第二索引（secondaryindexes）中。源代码见：/myisam/mi_range.c，mi_records_in_range()（MyISAM索引）。


INDEX连接范例
思索这个查询
SELECTcolumn1FROMTable1;
假如column1有加索引，那优化器大概选择从加的索引取值，而不是从表（全表扫描）。像这类体例的索引，一样平常称为掩盖索引（COVERINGINDEX）。在EXPLAINExtra形貌中，MySQL会复杂用INDEX单词来暗示掩盖索引（COVERINGINDEX）。

语句：
SELECTcolumn1,column2FROMTable1;只要当索引被界说成以下，优化器会利用JOIN连接范例为INDEX：jointype=indexCREATEINDEX...ONTable1(column1,column2);
换句话说，被查询的字段（如：column1,column2）都必须被加索引的，被加索引的多个字段是无按次之分的。因而，更成心义的严厉界说一个多列索引（multiple-columnindex）作为一个掩盖索引（COVERINGINDEX）来利用，不管搜刮的思索。


INDEXMERGE连接范例

概述
利用索引兼并（INDEXMERGE），当表的前提可转化成以下：

cond_1ORcond_2...ORcond_N
转化的前提是：每一个cond_i（cond_1，cond_2。。。）前提可用于局限扫描，且没有一对前提（cond_i，cond_j）用不异的索引。假如cond_i和cond_j前提利用不异的索引，那末cond_i大概cond_j前提能分离一个单一局限扫描，也就没兼并的需要了。


以下查询就用了索引兼并（INDEXMERGE）：
SELECT*FROMtWHEREkey1=c1ORkey2<c2ORkey3IN(c3,c4);
SELECT*FROMtWHERE(key1=c1ORkey2<c2)ANDnonkey=c3;
索引兼并（INDEXMERGE），是完成成一种局限键（rangekey）并以cond_i（cond_1，cond_2。。。）前提机关成的容器。在做索引兼并（INDEXMERGE）时，MySQL检索每一个键扫描（keyscans）的行，然后经由过程一个打消反复的历程来运转它们。今朝类Unique用于打消反复的。

INDEXMERGE优化器

单一SEL_TREE工具不克不及被机关成在OR语句中有分歧成员的键的前提，相似这前提：
key1<c1ORkey2<c2


从MySQL5.0入手下手，这些前提被索引兼并（INDEXMERGE）办法，和局限优化器（rangeoptimizer）布局的类SEL_IMERGE处置。SEL_IMERGE代表了多少SEL_TREE工具的析取，这类被暗示为：
sel_imerge_cond=(t_1ORt_1OR...ORt_n)
每一个t_i（t_1，t_2。。。）代表一个SEL_TREE，没有一对（t_i，t_j）分歧的SEL_TREE工具能被兼并成单一的SEL_TREE工具。

今朝的完成办法在构建SEL_IMERGE时，只要当没有单一的SEL_TREE工具能被构建成被剖析过的查询的一部分；假如发明单一SEL_TREE工具能被机关，就会即刻抛弃SEL_TREE。这实践是一个限定，而且大概招致最坏行检索战略的利用。以下查询：

SELECT*FROMtWHERE(goodkey1=c1ORgoodkey1=c2)ANDbadkey=c3
在badkey的扫描会被选择，即便在（goodkey1，goodkey1）上的索引兼并（INDEXMERGE）会更快。


索引兼并（INDEXMERGE）优化器会搜集索引兼并（INDEXMERGE）会见行的一切大概的线路列表。这类SEL_IMERGE布局列表暗示以下的前提：

(t_11ORt_12OR...ORt_1k)AND
(t_21ORt_22OR...ORt_2l)AND
...AND
(t_M1ORt_M2OR...ORt_mp)
当t_ij是一个SEL_IMERGE且一个前提就是一个SEL_IMERGE工具。

最小本钱的SEL_IMERGE工具用来检索行。
索引兼并（INDEXMERGE）机关函数的具体信息见：源代码sql/opt_range.cc，imerge_list_and_list()，imerge_list_or_list()，和SEL_IMERGE类的成员函数。

RANGE优化器
为了局限RANGE查询，MySQL优化器构建一个SEL_TREE工具，以下这类情势：
range_cond=(cond_key_1ANDcond_key_2AND...ANDcond_key_N)
每个cond_key_i都是一个键的构成部分的前提。MySQL为每一个有效的键创立一个cond_key_i前提。然后这类本钱最廉价的前提cond_key_i用来做局限RANGE扫描。

单一的cond_key_i前提是用SEL_ARG工具中的一个相联指针网（apointer-linkednetworkofSEL_ARGobjects）来暗示。每一个SEL_ARG工具参考键的特定部分和暗示以下的前提：

sel_arg_cond=(inf_val<key_part_nANDkey_part_n<sup_val)(1)
ANDnext_key_part_sel_arg_cond(2)
ORleft_sel_arg_cond(3)
ORright_sel_arg_cond(4)

1。完成距离，大概没有高低临界，也或包含或没包含临界值。
2。完成SEL_ARG工具以下一个键组件作为前提（isforaSEL_ARGobjectwithconditiononnextkeycomponent）。

3。完成有距离的SEL_ARG工具，在一样地区作为这个SEL_ARG工具（isforaSEL_ARGobjectwithanintervalonthesamefieldasthisSEL_ARGobject）。在以后工具和右边工具中的距离，是不订交的。left_sel_arg_cond.sup_val<=inf_val。

4。完成有距离的SEL_ARG工具，在一样地区作为这个SEL_ARG工具。在以后工具和右侧工具中的距离，是不订交的。left_sel_arg_cond.min_val>=max_val。
MySQL会变化恣意深度的嵌套AND-OR前提为上述相毗连的情势。

行检索算法

索引兼并（INDEXMERGE）有以下两个步骤：
筹办阶段：
activateindexonly;
foreachkey_iin(key_scansclustered_pk_scan)
{
while(retrievenext(key,rowid)pairfromkey_i)
{
if(noclusteredPKscan||
rowdoesntmatchclusteredPKscancondition)
putrowidintoUnique;
}
}
deactivateindexonly;

行检索阶段：
foreachrowidinUnique
{
retrieverowandpassittooutput;
}
if(clustered_pk_scan)
{
while(retrievenextrowforclustered_pk_scan)
passrowtooutput;
}
源代码见：sql/opt_range.cc，QUICK_INDEX_MERGE_SELECT类函数的索引兼并（INDEXMERGE）行检索代码。


3.换位（Transpositions）
MySQL撑持复杂语句表达式的换位（反转干系操纵符的操纵数的按次）。换句话说：
WHERE-5=column1
此语句可转化成：

WHEREcolumn1=-5

但是，MySQL不撑持有运算存在的换位，如：
WHERE5=-column1
而这句不克不及一律看待：

WHEREcolumn1=-5

像这情势column=constant表达式的换位是为了更好的索引检索。假如这类情势的语句有加了索引的字段，不管表的巨细，MySQL一直利用上索引的。（破例：假如表无纪录或只要一行纪录，它就是常数表，需出格处置。见常数值和常数表）。

AND干系
一个AND的查询情势如condition1ANDcondition2，以下：
WHEREcolumn1=xANDcolumn2=y
这步骤形貌了优化器定夺的历程：
1。假如两个前提都没被索引，利用按次扫描（全表扫描）。
2。除前一点以外，假如个中一个前提有更好的JOIN连接范例，则以JOIN连接范例选择一个驱动。（见断定JOIN连接范例）
3。除前两点以外，假如两个前提都有加索引且同等的JOIN连接范例（注：JON连接范例效果有优劣之分），则以第一个创立的索引选择一个驱动。
优化器也会依据索引交织选择索引兼并（INDEXMERGE），见INDEXMERGE连接范例。例子以下：

CREATETABLETable1(s1INT,s2INT);
CREATEINDEXIndex1ONTable1(s2);
CREATEINDEXIndex2ONTable1(s1);
...
SELECT*FROMTable1WHEREs1=ANDs2=5;
中选择一种战略来办理这个查询，优化器会选择s2=5作为驱动，因为s2上的索引起首被创立。视为一个偶尔的效果，而不是一种划定规矩，在任什么时候刻都有大概会改动的。


OR干系
一个OR的查询情势如condition1ORcondition2，以下：
WHEREcolumn1=xORcolumn2=y
这类查询优化器的定夺是利用按次全表扫描。

另有一种选择在特定的情况下会利用索引兼并（INDEXMERGE），更多信息见INDEXMERGE优化器和IndexMergeOptimization。

上述的特定情形不克不及用于假如两前提的字段是一样。以下：
WHEREcolumn1=xORcolumn1=y
这类情形，因为语句是RANG查询，以是会走索引的。这个话题会在IN限制（predicate）的会商中再次看到。


UNION查询
一切含有UNION的SELECT查询语句会被各自优化。因而，这个查询：
SELECT*FROMTable1WHEREcolumn1=x
UNIONALL
SELECT*FROMTABLE1WHEREcolumn2=y
假如column1和column2都有索引的，每一个SELECT城市利用索引扫描，各自的了局会议被兼并。注重：此查询大概发生不异的了局集，假如查询利用了按次扫描OR的例子。


NOT（）干系
一个逻辑前提以下：

column15
等价于：

column1<5ORcolumn1>5
但是，MySQL不会对这类前提举行转化语句。假如你以为用RANG查询效果会更好，你必须本人手动做语句转化。

另有一个逻辑前提以下：
WHERENOT(column1!=5)
等价于：

WHEREcolumn1=5
对这类情形，MySQL也不会做语句转化的。

我们等候能针对上述两个情形到场新的优化办法。

ORDERBY语句

一般，假如优化器发明行纪录不论怎样都是有序的，在ORDERBY语句中它也会跳过SORT历程。可是仍是考证几个破例的情形。

例：
SELECTcolumn1FROMTable1ORDERBYx;
优化器会抛弃ORDERBY语句，这也是逝世代码删除一个例子。


例：

SELECTcolumn1FROMTable1ORDERBYcolumn1;
优化器会利用column1的索引，假如存在的话。


例：
SELECTcolumn1FROMTable1ORDERBYcolumn1+1;
优化器会利用column1的索引，假如存在的话。可是不要被弄混了，索引只用来查找纪录值。别的：按次扫描索引的本钱比按次扫描全表的本钱是更廉价的（一样平常索引的巨细会比数据值的巨细小的），这也是为何INDEXJOIN连接范例会比ALL范例更优化。见断定JOIN连接范例。


另有一种了局集的全体排序SORT，例：

SELECT*FROMTable1
WHEREcolumn1>xANDcolumn2>x
ORDERBYcolumn2;
假如column1和column2都有索引的，优化器会走在column1上的索引。在这个查询语句，对column2值的排序不会影响驱动的选择。

源代码见：/sql/sql_select.cc，test_if_order_by_key()和/sql/sql_select.cc，test_if_skip_sort_order()。


ORDERBYOptimization，形貌了SORT排序历程的内容机制，在这里不反复注释。但恳请你必定要浏览，由于它形貌了缓冲和疾速排序机制的操纵。

GROUPBY和相干的前提

这里形貌了GROUPBY和相干前提（HAVING，COUNT()，MAX()，MIN()，SUM()，AVG()，DISTINCT()）的次要优化。

GROUPBY会利用索，假如一个索引存在的话。
GROUPBY会用排序，假如没有索引存在。优化器大概选择利用HASH表排序。
GROUPBYxORDERBYx的情形，优化器会由于GROUPBY会以x的排序，而以为ORDERBY是不必要的。
优化器包括了为转移特定HAVING前提的WHERE语句中的代码。但是，此代码在编写时是不失效的。源代码见：/sql/sql_select.cc，JOIN::optimize()，在#ifdefHAVE_REF_TO_FIELDS以后。
假如表句柄（handler）有个无效的疾速行总数（row-count），那末这个查询：
SELECTCOUNT(*)FROMTable1;
不用扫描一切行，就可以失掉行总数值。这只对MyISAM表是准确的，但不合适InnoDB表。别的这个查询

SELECTCOUNT(column1)FROMTable1;
不会有一样的优化，除非column1被界说为NOTNULL。

MAX()和MIN()新的优化办法。例：
SELECTMAX(column1)
FROMTable1
WHEREcolumn1<a;
假如column1被索引了，就很简单找到最年夜值经由过程查询索引中的a值而且在这之前前往索引键。

优化对以下情势的查询，举行语句转化：
SELECTDISTINCTcolumn1FROMTable1;
成：

SELECTcolumn1FROMTable1GROUPBYcolumn1;
当且仅当这两个前提都是准确：

*GROUPBY能经由过程索引来未完成。这表示了只要一个表在FROM语句中且没有WHERE语句。
*没有LIMIT语句。

由于DISTINCT语句其实不老是被转化成GROUPBY，不要希冀含有DISTINCT查询语句总会有被排序的了局集。但是，你能依附GROUPBY优化划定规矩，除非查询包含ORDERBYNULL。


三。别的优化

这部分，会商别的更出格的优化办法。

1.ref和eq_ref的NULLs值过滤会见

这部分会商ref和eq_ref连接范例的NULLs值过滤优化办法。

后期（early）NULLs值过滤
假定我们有个连接按次以下：
...,tblX,...,tblY,...
更深切假定，表tblY经由过程ref或eq_ref团结范例被会见：

tblY.key_column=tblX.column
大概，利用多个键部分的ref范例会见：

...ANDtblY.key_partN=tblX.columnAND...
tblX.column能够为NULL。ref（或eq_ref）范例会见时，后期会使用NULLs过滤。我们做以下的揣度：

(tblY.key_partN=tblX.column)=>(tblX.columnISNOTNULL)
原等式的反省只要在读了表tblX和tblY确当前行纪录后。ISNOTNULL限制（predicate）的反省，只要在读了表tblX确当前行纪录后。假如在表tblX和tblY的团结排序中有任何

别的表，ISNOTNULL限制（predicate）的反省就同意我们跳过会见这些表。

这个特征的完成代码以下：

ref剖析器（包括办法update_ref_and_keys()）经由过程设置KEY_FIELD::null_rejecting=TRUE反省和标志像上述这类范例的查询等式。
选择JOIN连接排序今后，add_not_null_conds()会增添得当的ISNOTNULL限制（predicate）到得当表的相干前提中。

对一切等式加了ISNOTNULL限制（predicate）是有大概被ref会见范例利用（而不是那些有实践利用的）。但是，今朝没如许做。

前期（Late）NULLs过滤
假定我们有一个表tblX查询企图，是经由过程ref会见范例被会见：
tblX.key_part1=expr1ANDtblX.key_part2=expr2AND...
在挪用索引检索前，我们断定任何expri（expr1，expr2，expr3。。。）值是不是为NULL。假如是，我们不会挪用检索，而是会即刻前往没找到婚配数组。

这个优化办法重用了由后期（early）NULLs过滤发生的null_rejecting属性。这个反省的源代码见：函数join_read_always_key()。

2.分区相干的优化
这部分会商MySQL分区相干的优化。MySQL5.1分区相干观点和完成见：Partitioning。

分区裁剪（pruning）
分区裁剪（partitionpruning）的操纵，以下界说：
“供应一个分区表的查询，比对此分区表的DDL语句和查询中的任何WHERE或ON语句，且找出这查询会见的最小分区集。”


如许失掉的分区会议比表一切分区的汇合小良多，这个分区集也是以后查询语句要用到的。没被到场这个分区集的别的分区，就不会被会见的，也就是说被裁剪失落的分区。正由于如许，查询的实行速率变得更快。

Non-TransactionalTableEngines.??如MyISAM无事件存储引擎，锁会被加在全部分区表。实际上讲，利用分区裁剪（partitionpruning）是有大概进步并发，只把锁加在被利用的分区上。可是今朝还没完成这功效。
分区裁剪（partitionpruning）不依附表的存储引擎，以是这功效是MySQL查询优化器的一部分。接上去章节形貌分区裁剪（partitionpruning）的细节。

分区裁剪概述
分区裁剪（partitionpruning）的完成步骤以下：
1。剖析WHERE语句前提并机关区间图intervalgraph，用来形貌剖析的了局情形。
2。经由过程区间图，为每一个区间找出被会见的分区集（包含子分区）。
3。机关查询所必要的分区集。

区间图intervalgraph是自下而上的体例机关成，并来暗示上述步骤的形貌。接着会商，我们会起首界说术语区间图intervalgraph，接着形貌如何用分戋戋间来构成一个区间图intervalgraph，最初形貌区间图intervalgraph的事情流程。

分戋戋间（PartitioningIntervals）
单点区间（Single-PointIntervals）
从最复杂的情形入手下手，假定一个有N个列的分区表，经由过程分区范例p_type和分区函数p_func，暗示以下：
CREATETABLEt(columns)
PARTITIONBYp_type(p_func(col1,col2,...colN)...);
再假定查询的WHERE前提情势以下：

WHEREt.col1=const1ANDt.col2=const2AND...t.colN=constN
我们能盘算出p_func(const1,const2...constN)，并发掘出哪一个分区包括的纪录和WHERE前提一样。注重：这个流程会在一切的分区范例和一切的分区函数上操纵。


注重：此流程只事情在，假如WHERE前提的情势像上述那样，表的每一个列必须被考证是不是等与一些恣意常数（不必要不异的常数为每列）。比方，假如上述例子的WHERE语句中没有col1=const1，那末我们不管帐算p_func分区函数的值，也就不会束缚实践被用的分区集。


区间游历（Walking）
假定一个分区表t被界说成columns列集，分区范例p_type，分区函数p_func利用integer范例字段int_col，以下：
CREATETABLEt(columns)
PARTITIONBY
p_type(p_func(int_col))
...
假定我们有以下情势的WHERE前提查询：

WHEREconst1<=int_col<=const2
我们能减少此情形的前提成一系列单点区间（Single-PointIntervals），以下，经由过程转化此WHERE语句为以下干系：

int_field=const1OR
int_field=const1+1OR
int_field=const1+2OR
...OR
int_field=const2

在源代码里，这类转化被称作区间游历（Walking）。游历短的区间本钱是不贵的，如许我们能减少分区数来扫描小的分区。然尔，游历长的区间不是那末十分无效的，必要反省大批的分区，如许的话，大概一切分区城市被扫描的。
以下参数决意区间游历（Walking）的值：
#defineMAX_RANGE_TO_WALK=10

注重：以下前提干系也会使用上述区间游历（Walking）的逻辑：
const1>=int_col>=const2

区间映照（mapping）
假定以下的分区表界说：
CREATETABLEt(columns)
PARTITIONBYRANGE|LIST(unary_ascending_function(column))
假定我们对表t的查询的WHERE语句，是以下情势中的一种：

const1<=t.column<=const2
t.column<=const2
const1<=t.column
自分区函数是升序，看以下的干系：
const1<=t.col<=const2
=>p_func(const1)<=
p_func(t.column)<=p_func(const2)
用A和B暗示这干系的最左和最右部分，我们能重写干系为：

A<=p_func(t.column)<=B
注重：在这实例中，区间是封闭的且有两个界值。可是，相似的推论能够类推到别的范例的区间。


如局限分区（RANGEpartitioning），每一个分区占有一个区间于分区函数值的轴线上，每一个区间是不相连的，以下：
p0p1p2
tablepartitions------x------x--------x-------->
searchinterval----x==============x----------->
AB
一个分区必要被会见，当且仅当假如它的区间和搜刮区间[A,B]没有空的交织点。


如枚举分区（LISTpartitioning），每一个分区包含点集于分区函数值的轴线上，各分区会发生分歧的交织点，以下：
p0p1p2p1p1p0
tablepartitions--+---+----+----+----+----+---->
searchinterval----x===================x------>
AB
一个分区必要被会见，最少一个交织点在搜刮区间[A,B]里。所用的分区集可断定运转从A到B，并搜集它们的点在这个搜刮局限内的分区。


子分戋戋间（subpartitioningintervals）

在后面部分我们形貌几种从基础的WHERE前提揣度出在用分区集。统统都标明，这些分区的揣度办法都合适于子分区，除局限分区（RANGEpartitioning）和枚举分区（LISTpartitioning）的子分区外。

自每一个分区以一样的体例被份子分区，我们会找出在每一个分区内的哪一个子分区会被会见。


从WHERE语句到区间（FromWHEREClausestoIntervals）
之前的章节报告了，从暗示分区和子分戋戋间的WHERE语句揣度出分区集。如今我们看看怎样从恣意WHERE语句抽出区间。

抽取的流程利用局限剖析器（RANGEAnalyzer），属于MySQL优化器的一部分，它发生局限RANGE会见的企图。这是由于这个义务是类似的。两种WHERE语句的情势：RANGE会见范例利用索引局限（区间）扫描；分区裁剪（partitionpruning）模块利用分戋戋间，用来决意哪一个分区被利用。

为了分区裁剪（partitionpruning），局限剖析器（RANGEAnalyzer）与WHERE语句被挪用，一个由分区和子分区函数利用的表的列清单：
(part_col1,part_col2,...part_colN,
subpart_col1,subpart_col2,...subpart_colM)
局限剖析器（RANGEAnalyzer）事情的了局被称为SEL_ARG图。这是一个很庞大的布局，我们不盘算在这里形貌它。今朝这个文明会商的重点是我们能游历一切分区，并搜集分区和子分区的区间。


以下例子分析布局和游历流程。假定表t按以下的分区：
CREATETABLEt(...,pfINT,sp1CHAR(5),sp2INT,...)
PARTITIONBYLIST(pf)
SUBPARTITIONBYHASH(sp1,sp2)(
PARTITIONp0VALUESIN(1),
PARTITIONp1VALUESIN(2),
PARTITIONp2VALUESIN(3),
PARTITIONp3VALUESIN(4),
PARTITIONp4VALUESIN(5),
);
现假定对表t的一个很庞大的WHERE语句查询：

pf=1AND(sp1=fooANDsp2IN(40,50))
OR
(pf1=3ORpf1=4)ANDsp1=barANDsp2=33
OR
((pf=3ORpf=4)ANDsp1=5)
OR
p=8

SEL_ARG图以下：
(root)
|:
|Partitioning:Sub-partitioning
|:
|:
|:
|+------+:+-----------++--------+
---|pf=1|----:-----|sp1=foo|---|sp2=40|
+------+:+-----------++--------+
|:|
|:+--------+
|:|sp2=50|
|:+--------+
|:
|:
+------+:+-----------++--------+
|pf=3|----:--+--|sp1=bar|---|sp2=33|
+------+:|+-----------++--------+
|:|
+------+:|
|pf=4|----:--+
+------+:
|:
|:
+------+:+-----------+
|pf=8|----:-----|sp1=baz|
+------+:+-----------+
上述图表，竖的界限（|）代表OR，横的（-）代表AND，横的和竖的线也代表AND。


分区裁剪（partitionpruning）代码游历从图上方到下方，从右边到右侧，并做了以下的推论
1。在最上和最左的区间，从利用分区的空汇合入手下手游历：
2。
实行pf=1的区间剖析，找到分区P0的响应汇合，右移到sp1=foo
再次右移，到sp2=40
剖析sp1=fooANDsp2=40区间，在某SP1子分区找到行。推论一：在每一个分区构成汇合P0，标识子分区SP1“被利用”
下移到sp2=50
剖析sp1=foo区间和sp2=50区间，在某SP2子分区找到行。推论二：在每一个分区构成汇合P0，标识子分区SP2“被利用”
移回到pf=1，然后下称到pf=3
3。
实行pf=3的区间剖析，找到分区P1的响应汇合，右移到sp1=bar
再次右移，到sp2=33
剖析sp1=barANDsp2=33区间，在某SP3子分区找到行。推论三：在每一个分区构成汇合P1，标识子分区SP3“被利用”
移回到pf=3，然后下移到pf=4
4。

实行pf=4的区间剖析，找到分区P2的响应汇合，右移到sp2=bar
实行挪动和相似的推论已在pf=3考证准确。如许的效果是对照差的，由于我们将再次剖析sp1=barANDsp2=33区间，但这个操纵不会很年夜影响到全体功能。
移回到pf=3，然后下称到pf=8
5。

实行pf=8的区间剖析，找到分区P3的响应汇合，右移到sp1=baz
如今到了sp1=baz，发明不克不及再向右挪动，也不克不及构建子分戋戋间。我们纪录下，并前往pf=8
从之前的历程，我们不克不及再限定子分戋戋间了，以是推论：在P3分区集里的每一个分区，假定一切的子分区都是无效在用的。
6。实验从pf=9下移，发明到尾，以是游历图也就完成。

注重：在特定的情形下，局限剖析器（RANGEAnalyzer）的了局会有几种的SEL_ARG图，这图是由OR或AND操纵符构成的。呈现这类情形关于WHERE语句，要末长短常庞大的要末不同意一个单一的区间列表构建。对这类情形，分区裁剪（partitionpruning）代码接纳符合的操纵，例：

SELECT*FROMt1WHEREpartition_id=10ORsubpartition_id=20
在这个实例中，没有单一的区间被构建，但分区裁剪（partitionpruning）代码准确地揣度了利用的分区集是团结：

一切在分区里的子分区包括了partition_id=10的行，在每一个分区里一个子分区包括subpartition_id=20的行。


源代码平分区裁剪（partitionpruning）完成
源代码的复杂讲授：
sql/opt_range.cc：
这代码包括了从WHERE语句到区间（FromWHEREClausestoIntervals）的完成，办法prune_partitions()。关于分区裁剪（partitionpruning）的都有具体的行行代码正文，从PartitionPruningModule代码入手下手：
sql/partition_info.h：
classpartition_info{
...
/*
Bitmapofused(i.e.notprunedaway)partitions.Thisiswhereresult
ofpartitionpruningisstored.
*/
MY_BITMAPused_partitions;
/*
"virtualfunction"pointerstofunctionsthatperformintervalanalysis
onthispartitionedtable(usedbythecodeinopt_range.cc)
*/
get_partitions_in_range_iterget_part_iter_for_interval;
get_partitions_in_range_iterget_subpart_iter_for_interval;
};
sql/sql_partition.cc：
这代码包括了完成一切分戋戋间剖析范例的办法。

分区检索
假如分区表被一系列索引检索（即ref，eq_ref，ref_or_null连接会见体例）会见，MySQL会反省是不是必要一切分区做索引检索大概限定会见到一个特定的分区。例：
CREATETABLEt1(aINT,bINT);
INSERTINTOt1VALUES(1,1),(2,2),(3,3);
CREATETABLEt2(
keypart1INT,
keypart2INT,
KEY(keypart1,keypart2)
)
PARTITIONBYHASH(keypart2);
INSERTINTOt2VALUES(1,1),(2,2),(3,3);

查询前提以下：
SELECT*FROMt1,t2
WHEREt2.keypart1=t1.a
ANDt2.keypart2=t1.b;
使用以下算法实行：

(foreachrecordint1:)
{
t2->index_read({current-value-of(t1.a),current-value-of(t1.b)});
while(t2->index_next_same())
passrowcombinationtoqueryoutput;
}
在index_read()挪用中，分区表句柄会发掘出被断定一切分区列的值，在这个例子中，是单一列b，接着找出一个分区会见。假如这个分区被裁剪过，就没别的的分区可会见。


-EOF-

[email=Popgo@XM]Popgo@XM[/email]SeniorDBA
Full-timelinux/AIXSA&&MySQL/OracleDBA
GTK/Email/MSN:unidba@Gmail.com
QQGroupUnixDBA:10098435
如果你在一个遵循GPL的自由(开源)项目中使用MySQL，那么你可以遵循GPL协议使用MySQL。然而，如果你的项目不是在GPL协议下的话，你必须为使用MySQL来支付许可费用，或者你可能因为这个因素而将你的项目改为遵循GPL。

学习SQL语言的话如果要学会去做网站就不是很难!但是要做数据库管理的话就有难度了!

相信各位对数据库和怎么样学习数据库都有一些经验和看法，也会有人走了一些弯路总结出自己的经验来，希望大家能把各自的看法和经验拿出来分享，给别人一份帮助，给自己一份快乐

也可谈一下你是怎么优化存储过程的?

财务软件要用SQL也只是后台的数据库而已,软件都是成品的,当然多学东西肯定是有好处的..

对于数据库来说，查询是数据库的灵魂，那么SQL查询效率究竟效率如何呢？下文将带对SQL查询的相关问题进行讨论，供您参考。

数据库物理框架没有变动undo和redo都放在数据库得transaction中，个人感觉是个败笔。如果说我们在设计数据库的时候考虑分多个数据库，可能能在一定程度上避免I/O效率问题。

同样会为索引视图等应用带来麻烦。看看行级和事务级的快照数据放在tempdb中，就能感觉到目前架构的尴尬。