记录生活中的点点滴滴

0%

mysql高级2.1:索引前奏

这一篇文章写一下学习索引之前的一些知识,有SQL执行慢的原因和7种join连接。

性能下降、SQL执行慢的原因

  • 查询语句写的烂

  • 索引失效

    • 单值:例如我们如果经常用到 WHERE name = xx 来查询筛选查询user表,那么我们可以考虑为name这一属性创建单值索引,create index idx_user_name on user(name)

    • 符合:如果经常用到name和email,我们可以考虑创建符合索引,create index idx_user_nameEmail on user(name,email)

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

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

JOIN查询

SQL执行加载顺序

我们写的SQL:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT DISTINCT
<select_list>
FROM
<left_table> <join_type>
JOIN <right_table> ON <join_condition>
WHERE
<where_condition>
GROUP BY
<group_by_list>
HAVING
<having_condition>
ORDER BY
<order_by_condition>
LIMIT <limit_number>

机器读的SQL:

1
2
3
4
5
6
7
8
9
10
FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>

总结:

建表SQL

接下来我们要学习7种 JOIN,在这之前,我们先把表建立并插入一些数据,具体的SQL语句如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
insert into tbl_emp values(1,"z3",1);
insert into tbl_emp values(2,"z4",1);
insert into tbl_emp values(3,"z5",1);
insert into tbl_emp values(4,"w5",2);
insert into tbl_emp values(5,"w6",2);
insert into tbl_emp values(6,"s7",3);
insert into tbl_emp values(7,"s8",4);
insert into tbl_emp values(8,"s9",51);

insert into tbl_dept values(1,"RD","11");
insert into tbl_dept values(2,"HR","12");
insert into tbl_dept values(3,"MK","13");
insert into tbl_dept values(4,"MIS","14");
insert into tbl_dept values(5,"FD","15");

执行完SQL,查询结果如下:

7种JOIN

一共7种JOIN,先通过一张图来了解它们:

Inner JOIN

Left JOIN

Right JOIN

Left Excluding JOIN

Right Excluding JOIN

Outer JOIN

因为mysql中没有outer join,我们要用union(连接加去重)和上面学过的几种join,才能实现这一效果

Outer Excluding JOIN

这个和前面一个差不多,只不过改一下 union 前后的语句即可