MysqlJoin语法解析与性能分析

MysqlJoin语法解析与性能分析⼀．Join语法概述join⽤于多表中字段之间的联系，语法如下：...FROMtable1INNER|LEFT|RIGHTJOINtable2ONconditionatable1:左表；table2:右表。JOIN按照功能⼤致分为如下三类：INNERJOIN（内连接,或等值连接）：取得两个表中存在连接匹配关系的记录。LEFTJOIN（左连接）：取得左表（table1）完全记录，即是右表（table2）并⽆对应匹配记录。RIGHTJOIN（右连接）：与LEFTJOIN相反，取得右表（table2）完全记录，即是左表（table1）并⽆匹配对应记录。注意：mysql不⽀持Fulljoin,不过可以通过UNION关键字来合并LEFTJOIN与RIGHTJOIN来模拟FULLjoin.接下来给出⼀个列⼦⽤于解释下⾯⼏种分类。如下两个表(A,B)mysql>selectA.id,A.name,B.namefromA,BwhereA.id=B.id;+----+-----------+-------------+|id|name|name|+----+-----------+-------------+|1|Pirate|2|Monkey|Pirate|3|Ninja|DarthVader||Rutabaga|||4|Spaghetti|Ninja+----+-----------+-------------+4rowsinset(0.00sec)⼆.Innerjoin|内连接，也叫等值连接，innerjoin产⽣同时符合A和B的⼀组数据。mysql>select*fromAinnerjoinBonA.name=B.name;+----+--------+----+--------+|id|name|id|name|+----+--------+----+--------+|1|Pirate|2|Pirate||3|Ninja|4|Ninja|+----+--------+----+--------+三.Leftjoinmysql>select*fromAleftjoinBonA.name=B.name;#或者：select*fromAleftouterjoinBonA.name=B.name;+----+-----------+------+--------+|id|name|id|name|+----+-----------+------+--------+|1|Pirate|2|Pirate||2|Monkey|NULL|NULL||3|Ninja|4|Ninja||4|Spaghetti|NULL|NULL|+----+-----------+------+--------+4rowsinset(0.00sec)leftjoin,（或leftouterjoin:在Mysql中两者等价，推荐使⽤leftjoin.）左连接从左表(A)产⽣⼀套完整的记录,与匹配的记录(右表(B)).如果没有匹配,右侧将包含null。如果想只从左表(A)中产⽣⼀套记录，但不包含右表(B)的记录，可以通过设置where语句来执⾏，如下：mysql>select*fromAleftjoinBonA.name=B.namewhereA.idisnullorB.idisnull;+----+-----------+------+------+|id|name|id|name|+----+-----------+------+------+|2|Monkey|NULL|NULL||4|Spaghetti|NULL|NULL|+----+-----------+------+------+2rowsinset(0.00sec)同理，还可以模拟innerjoin.如下：mysql>select*fromAleftjoinBonA.name=B.namewhereA.idisnotnullandB.idisnotnull;+----+--------+------+--------+|id|name|id|name|+----+--------+------+--------+|1|Pirate|2|Pirate||3|Ninja|4|Ninja|+----+--------+------+--------+2rowsinset(0.00sec)求差集：根据上⾯的例⼦可以求差集，如下：SELECT*FROMALEFTJOINBONA.name=B.nameWHEREB.idISNULLunionSELECT*FROMArightJOINBONA.name=B.nameWHEREA.idISNULL;#结果+------+-----------+------+-------------+|id|name|id|name|+------+-----------+------+-------------+|2|Monkey|NULL|NULL|4|Spaghetti|NULL|NULL|||NULL|NULL|1|Rutabaga||NULL|NULL|3|DarthVader|+------+-----------+------+-------------+四.Rightjoinmysql>select*fromArightjoinBonA.name=B.name;+------+--------+----+-------------+|id|name|id|name|+------+--------+----+-------------+|NULL|NULL|1|Rutabaga||1|Pirate|2|Pirate|NULL|NULL|3|DarthVader||3|Ninja|4|Ninja||+------+--------+----+-------------+4rowsinset(0.00sec)同leftjoin。五.Crossjoincrossjoin：交叉连接，得到的结果是两个表的乘积，即笛卡尔（Descartes）乘积⼜叫直积。假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。类似的例⼦有，如果A表⽰某学校学⽣的集合，B表⽰该学校所有课程的集合，则A与B的笛卡尔积表⽰所有可能的选课情况。mysql>select*fromAcrossjoinB;+----+-----------+----+-------------+|id|name|id|name|+----+-----------+----+-------------+|1|Pirate|1|Rutabaga||2|Monkey|1|Rutabaga||3|Ninja|1|Rutabaga||4|Spaghetti|1|Rutabaga||1|Pirate|2|Pirate|2|Monkey|2|Pirate|3|Ninja|2|Pirate|4|Spaghetti|2|Pirate|||||1|Pirate|3|DarthVader||2|Monkey|3|DarthVader||3|Ninja|3|DarthVader||4|Spaghetti|3|DarthVader||1|Pirate|4|Ninja|2|Monkey|4|Ninja|3|Ninja|4|Ninja|4|Spaghetti|4|Ninja||||+----+-----------+----+-------------+16rowsinset(0.00sec)#再执⾏：mysql>select*fromAinnerjoinB;试⼀试#在执⾏mysql>select*fromAcrossjoinBonA.name=B.name;试⼀试实际上，在MySQL中（仅限于MySQL）CROSSJOIN与INNERJOIN的表现是⼀样的，在不指定ON条件得到的结果都是笛卡尔积，反之取得两个表完全匹配的结果。INNERJOIN与CROSSJOIN可以省略INNER或CROSS关键字，因此下⾯的SQL效果是⼀样的：...FROMtable1INNERJOINtable2...FROMtable1CROSSJOINtable2...FROMtable1JOINtable2六.Fulljoinmysql>select*fromAleftjoinBonB.name=A.name->union->select*fromArightjoinBonB.name=A.name;+------+-----------+------+-------------+|id|name|id|name|+------+-----------+------+-------------+|1|Pirate|2|Pirate|2|Monkey|NULL|NULL|3|Ninja|4|Ninja|4|Spaghetti|NULL|NULL|||||NULL|NULL|1|Rutabaga||NULL|NULL|3|DarthVader|+------+-----------+------+-------------+6rowsinset(0.00sec)全连接产⽣的所有记录（双⽅匹配记录）在表A和表B。如果没有匹配,则对⾯将包含null。七.性能优化1.显⽰(explicit)innerjoinVS隐式(implicit)innerjoin如：select*fromtableainnerjointablebona.id=b.id;VSselecta.*,b.*fromtablea,tablebwherea.id=b.id;我在数据库中⽐较(10w数据)得之，它们⽤时⼏乎相同，第⼀个是显⽰的innerjoin，后⼀个是隐式的innerjoin。2.leftjoin/rightjoinVSinnerjoin尽量⽤innerjoin.避免LEFTJOIN和NULL.在使⽤leftjoin（或rightjoin）时，应该清楚的知道以下⼏点：(1).on与where的执⾏顺序ON条件（“ALEFTJOINBON条件表达式”中的ON）⽤来决定如何从B表中检索数据⾏。如果B表中没有任何⼀⾏数据匹配ON的条件,将会额外⽣成⼀⾏所有列为NULL的数据,在匹配阶段WHERE⼦句的条件都不会被使⽤。仅在匹配阶段完成以后，WHERE⼦句条件才会被使⽤。它将从匹配阶段产⽣的数据中检索过滤。所以我们要注意：在使⽤Left(right)join的时候，⼀定要在先给出尽可能多的匹配满⾜条件，减少Where的执⾏。如：PS,这部分有些不妥，感谢wxweven指正：这部分的内容，博主写的有些⽋妥当，不知道博主有没有实际运⾏测试过，下⾯说说我的看法：（1）⾸先关于on和where的⽤法，如果直接把where⾥⾯的条件拿到on⾥⾯去，结果是跟原来的不⼀致的，所以博主说的“在使⽤Left(right)join的时候，⼀定要在先给出尽可能多的匹配满⾜条件，减少Where的执⾏”是不成⽴的，因为筛选条件放在on或者where，产⽣的是不同的结果，不能说为了性能就把where中的条件放到on中。可参考PASSselect*fromAinnerjoinBonB.name=A.nameleftjoinConC.name=B.nameleftjoinDonD.id=C.idwhereC.status>1andD.status=1;Greatselect*fromAinnerjoinBonB.name=A.nameleftjoinConC.name=B.nameandC.status>1leftjoinDonD.id=C.idandD.status=1从上⾯例⼦可以看出，尽可能满⾜ON的条件，⽽少⽤Where的条件。从执⾏性能来看第⼆个显然更加省时。(2).注意ON⼦句和WHERE⼦句的不同如作者举了⼀个列⼦：mysql>SELECT*FROMproductLEFTJOINproduct_detailsON(product.id=product_details.id)ANDproduct_details.id=2;+----+--------+------+--------+-------+|id|amount|id|weight|exist|+----+--------+------+--------+-------+|1|100|NULL|NULL|NULL||2|200|2|22|0||3|300|NULL|NULL|NULL||4|400|NULL|NULL|NULL|+----+--------+------+--------+-------+4rowsinset(0.00sec)mysql>SELECT*FROMproductLEFTJOINproduct_detailsON(product.id=product_details.id)WHEREproduct_details.id=2;+----+--------+----+--------+-------+|id|amount|id|weight|exist|+----+--------+----+--------+-------+|2|200|2|22|0|+----+--------+----+--------+-------+1