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

SQL性能下降的原因

• 查询语句写的不好
• 索引失效
• 关联 查询太多join（设计缺陷或者不得已的需求）。
• 服务器调优以及各个参数的设置（缓冲、线程数等）。

SQL执行顺序

• 手写

select distinct
      <select_list>
from
      ...
where
      ...
group by
      ...
having
      ...
order by
      ...
limit <limit number>

• 机器读
  

JOIN理论

/* 1 */
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key;

/* 2 */
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key;

/* 3 */
SELECT <select_list> FROM TableA A INNER JOIN TableB B ON A.Key = B.Key;

/* 4 */
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL;

/* 5 */
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL;

/* 6 */
SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key;
/* MySQL不支持FULL OUTER JOIN这种语法 可以改成 1+2 */
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key
UNION
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key;

/* 7 */
SELECT <select_list> 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 <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL;
UNION
SELECT <select_list> 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