mysql join顺序性能_[mysql]SQL性能下降的原因&SQL执行顺序&JOIN

SQL性能下降的原因

查询语句写的不好

索引失效

关联 查询太多join(设计缺陷或者不得已的需求)。

服务器调优以及各个参数的设置(缓冲、线程数等)。

SQL执行顺序

手写

select distinct

from

...

where

...

group by

...

having

...

order by

...

limit

机器读

JOIN理论

/* 1 */

SELECT FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key;

/* 2 */

SELECT FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key;

/* 3 */

SELECT FROM TableA A INNER JOIN TableB B ON A.Key = B.Key;

/* 4 */

SELECT FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL;

/* 5 */

SELECT FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL;

/* 6 */

SELECT FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key;

/* MySQL不支持FULL OUTER JOIN这种语法 可以改成 1+2 */

SELECT FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key

UNION

SELECT FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key;

/* 7 */

SELECT FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL OR B.Key IS NULL;

/* MySQL不支持FULL OUTER JOIN这种语法 可以改成 4+5 */

SELECT FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL;

UNION

SELECT FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL;

CREATE TABLE `tbl_emp` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(20) DEFAULT NULL,

`deptId` int(11) DEFAULT NULL,

PRIMARY KEY (`id`) ,

KEY `fk_dept_id`(`deptId`)

)ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;

CREATE TABLE `tbl_dept` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`deptName` varchar(30) DEFAULT NULL,

`locAdd` varchar(40) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;

#共有 内连接 只有两者的公有部分

SELECT * FROM tbl_emp a INNER JOIN tbl_dept b ON a.deptid = b.id

#左外

SELECT * FROM tbl_emp a LEFT JOIN tbl_dept b ON a.deptid = b.id

#右外

SELECT * FROM tbl_emp a RIGHT JOIN tbl_dept b ON a.deptid = b.id

#独A，左外去除内连接，则b为null

SELECT * FROM tbl_emp a LEFT JOIN tbl_dept b ON a.deptid = b.id WHERE b.id IS NULL

#独B，有外外去除内连接，则a为null

SELECT * FROM tbl_emp a RIGHT JOIN tbl_dept b ON a.deptid = b.id WHERE a.id IS NULL

#全有

SELECT * FROM tbl_emp a FULL OUT JOIN tbl_dept b ON a.deptid = b.id #mysql不支持

##A左外 + B右外

SELECT * FROM tbl_emp a LEFT JOIN tbl_dept b ON a.deptid = b.id

UNION

SELECT * FROM tbl_emp a RIGHT JOIN tbl_dept b ON a.deptid = b.id

##A左外 + 独B

SELECT * FROM tbl_emp a LEFT JOIN tbl_dept b ON a.deptid = b.id

UNION

SELECT * FROM tbl_emp a RIGHT JOIN tbl_dept b ON a.deptid = b.id WHERE a.id IS NULL

#交集

##独A + 独B

SELECT * FROM tbl_emp a LEFT JOIN tbl_dept b ON a.deptid = b.id WHERE b.id IS NULL

UNION

SELECT * FROM tbl_emp a RIGHT JOIN tbl_dept b ON a.deptid = b.id WHERE a.id IS NULL