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：

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：

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语句如下：

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;

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");