pt11mysql多表查询优化

多表查询

如果多个表存在一定关联关系，可以多表在一起进行查询操作，其实表的关联整理与外键约束之间并没有必然联系，但是基于外键约束设计的具有关联性的表往往会更多使用关联查询查找数据。

简单多表查询

多个表数据可以联合查询，语法格式如下：

select  字段1,字段2... from 表1,表2... [where 条件]

e.g.
select c.name,c.score,h.hobby from class as c,hobby as h 
where c.name=h.name;

select name,salary,dname from person,dept
where person.dept_id = dept.id;

select name,salary,dname from person,dept 
where person.dept_id = dept.id and salary>=20000; #可有多个条件

如果没有条件，产生笛卡尔积现象。强行匹配AB表的每一条数据，产生A*B条记录。

select * from class,hobby;

内连接 inner join

内连接查询只会查找到符合条件的记录，表前后顺序不影响查询结果，数据量大的表放前面，但查询效率更高。

SELECT 字段列表
    FROM 表1  INNER JOIN  表2
ON 表1.字段 = 表2.字段;

select name,salary,dname from person inner join dept
on person.dept_id = dept.id
where salary>=20000;
#对比上下。查询结果一样，推荐使用上面的查询方法，where做主要条件删选
select name,salary,dname from person,dept 
where person.dept_id = dept.id and salary>=20000; 

分为左连接和右连接

外连接—左连接

左表（表1）全部显示，右表中显示与左表匹配的项，没有的项显示NULL

SELECT 字段列表
    FROM 表1  LEFT JOIN  表2
ON 表1.字段 = 表2.字段;

e.g. 
select name,salary,dname
from person left join dept
on person.dept_id = dept.id
where salary>=20000;

-- 查询每个部门员工人数
select dname,count(name) from dept left join person on dept.id=person.dept_id group by dname;

外连接—右连接

右表全部显示，显示左表中与右表匹配的项

SELECT 字段列表
    FROM 表1  RIGHT JOIN  表2
ON 表1.字段 = 表2.字段;

e.g.
select dname,count(name)
from person right join dept
on person.dept_id = dept.id
group by dname;

注意：当左连接语句，交换表位置，使用右连接查询，可以获得同样的结果。我们尽量使用数据量大的表作为基准表,放在前面，提高查询效率。业界习惯使用左连接，右连接优化查询时使用。

--综合查询练习
--class    1     student 多
--teacher  1     course 多
--score --》 student 多   course 多

create table class(cid int primary key auto_increment,caption char(4) not null);
                  
create table teacher(tid int primary key auto_increment,tname varchar(32) not null);
                    
create table student(sid int primary key auto_increment,
                    sname varchar(32) not null,
                    gender enum('male','female','others') not null default 'male',
                    class_id int,
                    foreign key(class_id) references class(cid) 
                     on update cascade on delete cascade);
                    
create table course(cid int primary key auto_increment,
                   cname varchar(16) not null,
                   teacher_id int,
                   foreign key(teacher_id) references teacher(tid)
                    on update cascade on delete cascade);
                   
create table score(sid int primary key auto_increment,
                  student_id int,
                  course_id int,
                  number int(3) not null,
                  foreign key(student_id) references student(sid)
                   on update cascade on delete cascade,
                   foreign key(course_id) references course(cid)
                   on update cascade on delete cascade);
                   
insert into class(caption) values('三年一班'),('三年二班'),('三年三班');
insert into teacher(tname) values('魏老师'),('祁老师'),('小泽老师');
insert into student(sname,gender,class_id) values('钢蛋','female',1),('铁锤','female',1),('山炮','male',2),('彪哥','male',3),('虎子','male',3),('妞妞','female',2),('建国','male',2);
insert into course(cname,teacher_id) values('生物',1),('体育',1),('物理',2);
insert into score(student_id,course_id,number) values(1,1,60),(1,2,59),(2,2,100),(3,2,78),(4,3,66),(2,3,78),(5,2,77),(6,1,84),(7,1,79),(5,3,80),(3,1,59);


1. 查询每位老师教授的课程数量
--  select  *  from teacher left ... 一步一步查
select tname,count(cname)
from teacher left join course
on teacher.tid = course.teacher_id
group by tname;

2. 查询各科成绩最高和最低的分数,形式 : 课程ID  课程名称 最高分  最低分
select cid as 课程ID,cname as 课程名称,
max(number) as 最高分,min(number) as 最低分
from course left join score
on course.cid = score.course_id
group by cid,cname;

3. 查询平均成绩大于85分的所有学生学号,姓名和平均成绩
select student.sid,sname,avg(number)
from student left join score
on student.sid = score.student_id
group by student.sid,sname
having avg(number) > 85;

4. 查询课程编号为2且课程成绩在80以上的学生学号和姓名
select student.sid,sname,number
from student left join score
on student.sid = score.student_id
where course_id=2 and number>80;

5. 查询各个课程及相应的选修人数
select cname,count(course_id)
from course left join score
on course.cid = score.course_id
group by cname;

6. 查询每位学生的姓名，所在班级和平均成绩
select sname,caption,avg(number)
from student left join class
on student.class_id = class.cid
left join score
on student.sid = score.student_id
group by sname,caption;

视图

视图概念

视图是存储的查询语句,当调用的时候,产生结果集,视图充当的是虚拟表的角色。其实视图可以理解为一个表或多个表中导出来的表，作用和真实表一样，包含一系列带有行和列的数据 视图中，用户可以使用SELECT语句查询数据，也可以使用INSERT，UPDATE，DELETE修改记录，视图可以使用户操作方便，并保障数据库系统安全，如果原表改名或者删除则视图也失效。

创建视图语法结构：

CREATE [OR REPLACE] VIEW [view_name] AS [SELECT_STATEMENT];
释义：
CREATE VIEW： 创建视图
OR REPLACE : 可选，如果添加原来有同名视图的情况下会覆盖掉原有视图
view_name ： 视图名称
SELECT_STATEMENT ：SELECT语句

-- e.g.
create view good_student_view
as select name,age,score from class
where score>=80;
show tables; #可以查看到，虚拟表，不真实存在，可以认为是查询的快捷方式

-- 如果视图是针对多张表创建，是不允许写操作
create view student_hobby_view
as select class.name,score,hobby,price
from class left join hobby
on class.name=hobby.name;

视图表的增删改查操作

视图的增删改查操作与一般表的操作相同，使用insert update delete select即可，但是原数据表的约束条件仍然对视图产生作用。

查看现有视图

show full tables in stu

删除视图

drop view if exists hobby_stu;

修改视图

alter view good_student_view
as select name,age,sex,score from class
where score>=85;

视图作用

1. 是对数据的一种重构，不影响原数据表的使用。
2. 简化高频复杂操作的过程，就像一种对复杂操作的封装。
3. 提高安全性，可以给不同用户提供不同的视图。
4. 让数据更加清晰。
缺点：视图的性能相对较差，从数据库视图查询数据可能会很慢。

函数和存储过程

存储过程和函数是事先经过编译并存储在数据库中的一段sql语句集合，调用存储过程和函数可以简化应用开发工作，提高数据处理的效率。

函数创建

delimiter $$      -- 自定义语句结束符号　　

create function 函数名(形参列表) returns 返回类型　　-- 注意是retruns
begin
　　函数体   -- 若干sql语句，但是不能直接写查询
　　return val;
end  $$  -- 自定义符号，跟上面的保持一致

delimiter ;   --恢复默认的结束符号

释义：
delimiter 自定义符号 是为了在函数内些语句方便，制定除了;之外的符号作为函数书写结束标志,一般用$$或者//
形参列表 ： 形参名 类型   类型为mysql支持类型
返回类型:  函数返回的数据类型,mysql支持类型即可
函数体： 若干sql语句组成
return: 返回指定类型返回值

--函数中有写操作语句则只能在select后面使用
delimiter $$
create function func() returns int
begin
 update class set score=66 where name="Abby";
 delete from class where name="Eva";
 return (select score from class where id=1);
end $$
delimiter ;


--如果函数没有写操作语句则可以作为一个值提供者在where中
delimiter $$
create function func2() returns int
begin
 declare max_score int;
 declare min_score int;
 set max_score=(select score
 from class order by score desc limit 1);
 select score from class
 order by score limit 1 into min_score;
 return max_score-min_score;
end $$
delimiter ;

 
-- 局部变量 declare ： 获得最高分和最低分分数差
create function st2() returns int
begin
    declare num_1 int;
    declare num_2 int;
    set num_1=(select score from class order by score desc limit 1);
    select score from class order by score limit 1 into num_2;
    return num_1-num_2;
end $$

-- 含有参数的函数调用
delimiter $$
create function queryNameById(uid int) returns varchar(20)
begin
return  (select name from class where id=uid);
end $$
delimiter ;

select queryNameById(80)     

-- 函数练习：books表完成 ，编写一个函数，传入两本书的名字，得到两本书的价格之差

create function get_price(book1 varchar(30),book2 varchar(30))
returns float
begin
    declare price1 float;
    declare price2 float;
    select price from books where bname=book1 into price1;
    select price from books where bname=book2 into price2;
    return price1-price2;
end $$

存储过程创建

创建存储过程语法与创建函数基本相同，但是没有返回值。

delimiter 自定义符号　

create procedure 存储过程名(形参列表)
begin
　	存储过程　　　　-- sql语句构成存储过程语句集
end  自定义符号

delimiter ;

释义：
delimiter 自定义符号 是为了在函数内些语句方便，制定除了;之外的符号作为函数书写结束标志
形参列表 ：[ IN | OUT | INOUT ] 形参名 类型
          in 输入，out  输出，inout 可以输入也可以输出
存储过程： 若干sql语句组成，如果只有一条语句也可以不写delimiter和begin,end

e.g. 存储过程创建和调用
create procedure st()
begin
    delete from class where name="James";
    update class set score=71 where name="Emma";
    select * from class;
end $$

call st();   -- call调用

存储过程三个参数的区别

• IN 类型参数可以接收变量也可以接收常量，传入的参数在存储过程内部使用即可，但是在存储过程内部的修改无法传递到外部。

• OUT 类型参数只能接收一个变量，接收的变量不能够在存储过程内部使用（内部为NULL），但是可以在存储过程内对这个变量进行修改。因为定义的变量是全局的，所以外部可以获取这个修改后的值。

• INOUT类型参数同样只能接收一个变量，但是这个变量可以在存储过程内部使用。在存储过程内部的修改也会传递到外部。

e.g. : 分别将参数类型改为IN OUT INOUT 看一下结果区别
delimiter $$
create procedure p_out ( OUT num int )
begin
    select num;
    set num=100;
    select num;
end $$
delimiter ;

set @num=10;  -- 变量
call p_out(@num)  

--homework
写一个函数，传入一本数的名字，返回书的价格
delimiter $$
create function get_price(name varchar(30))
returns float
begin
    return (select price from books where bname=name);
end $$
delimiter ;

写一个存储过程，传入一个作者名字
将该作者所有图书涨价5元，将比该作者最高价格
的书还高的图书删除掉

delimiter $$
create procedure handle_book(in name varchar(30))
begin
    declare max_price float;
    update books set price=price+5 where author=name;
    select price from books where author=name order by price desc limit 1 into max_price;
    delete from books where price>max_price;
end $$
delimiter ;


--update delete操作使用子查询时，修改或者删除的表不能跟子查询是同一个表
--在子查询中，对数据采用查询的方法重命名一个表可以解决
delimiter $$
create procedure handle_book1(in name varchar(30))
begin
    update books set price=price+5 where author=name;
    delete from books where price>(select new.price from (select * from books) as new where new.author=name order by new.price desc limit 1);
end $$
delimiter ;

存储过程和存储函数操作

1. 调用存储过程

call 存储过程名字（[存储过程的参数[,……]])

2. 调用存储函数

select 存储函数名字（[函数的参数[,……]])

3. 使用show create语句查看存储过程和函数的定义

show create  {procedure|function}  存储过程或存储函数的名称

4. 查看所有函数或者存储过程

select name,type from mysql.proc where db='stu';
show procedure|function status  where db="stu";

5. 删除存储过程或存储函数

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

函数和存储过程区别

1. 函数有且只有一个返回值，而存储过程不能有返回值。
2. 函数只能有普通参数，而存储过程可以有in,out,inout多个类型参数。
3. 存储过程中的语句功能更丰富，实现更复杂的业务逻辑，可以理解为一个按照预定步骤调用的执行过程，而函数中不能展示查询结果集语句，只是完成查询的工作后返回一个结果，功能针对性比较强。
4. 存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用。

事务控制

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，既需要删除人员的基本资料，也要删除和该人员相关的信息，如果操作就必须同时操作成功，如果有一个不成功则所有数据都不动。这时候数据库操作语句就构成一个事务。事务主要处理数据的增删改操作。

事务是一件事从开始发生到结束的过程，为确保数据操作过程中的数据完整和使用安全。

事务操作

1. 开启事务

mysql>begin;

2. 开始执行事务中的若干条SQL命令（增删改）
3. 终止事务，若begin之后使用commit提交事务或者使用rollback进行事务回滚。

mysql>commit; # 事务中SQL命令都执行成功,提交到数据库,结束!
mysql>rollback; # 有SQL命令执行失败,回滚到初始状态,结束!commit的sql不能回滚

注意：事务操作只针对数据操作。rollback不能对数据库，数据表结构操作恢复。

事务四大特性ACID mvcc原理了解下

1. 原子性（atomicity）

一个事务必须视为一个不可分割的最小工作单元，对于一个事务来说，不可能只执行其中的一部分操作,整个事务中的所有操作要么全部提交成功，要么全部失败回滚

2. 一致性（consistency）

事务完成时，数据必须处于一致状态，数据的完整性约束没有被破坏。

3. 隔离性（isolation）

数据库允许多个并发事务同时对其数据进行读写和修改的能力，而多个事务相互独立。隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。(a终端开启事务，执行写操作，不提交，b终端对同一数据写操作将不被允许，表级锁、行级锁、页级锁）

4. 持久性（durability）

一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。

事务隔离级别

事务四大特性中的隔离性是在使用事务时最为需要注意的特性，因为隔离级别不同带来的操作现象也有区别

隔离级别

读未提交：read uncommitted

事物A和事物B，事物A未提交的数据，事物B可以读取到,这里读取到的数据叫做“脏数据”产生脏读，
这种隔离级别最低，这种级别一般是在理论上存在，数据库隔离级别一般都高于该级别

读已提交rc：read committed

事物A和事物B，事物A提交的数据，事物B才能读取到，这种隔离级别高于读未提交，
换句话说，对方事物提交之后的数据，当前事物才能读取到，这种级别可以避免“脏数据”，
这种隔离级别会导致“不可重复读取”，B事务只能读取修改前后的数据

可重复读rr：repeatable read

事务A和事务B，事务A提交之后的数据，事务B读取不到，事务B是可重复读取数据，这种隔离级别高于读已提交
MySQL默认级别，虽然可以达到可重复读取，但是会导致“幻读”，B事务读到了修改前的数据，实际数据已变化

串行化：serializable

事务A和事务B，事务A在操作数据库时，事务B只能排队等待，这种隔离级别很少使用，吞吐量太低，用户体验差
这种级别可以避免“幻像读”，每一次读取的都是数据库中真实存在数据，事务A与事务B串行，而不并发

mysql优化了解

数据库设计范式

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式。

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

各种范式呈递次规范，越高的范式数据库冗余越小。但是范式越高也意味着表的划分更细，一个数据库中需要的表也就越多，此时多个表联接在一起的花费是巨大的，尤其是当需要连接的两张或者多张表数据非常庞大的时候，表连接操作几乎是一个噩梦，这严重地降低了系统运行性能。所以通常数据库设计遵循第一第二第三范式，以避免数据操作异常,又不至于表关系过于复杂。

范式简介：

• 第一范式： 数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等组合的数据项。简单来说要求数据库中的表示二维表，每个数据元素不可再分。

  例如： 在国内的话通常理解都是姓名是一个不可再拆分的单位，这时候就符合第一范式；但是在国外的话还要分为FIRST NAME和LAST NAME，这时候姓名这个字段就是还可以拆分为更小的单位的字段，就不符合第一范式了。

• 第二范式： 第二范式（2NF）要求数据库表中的每个实例或记录必须可以被唯一地区分，所有属性依赖于主属性。即选取一个能区分每个实体的属性或属性组，作为实体的唯一标识，每个属性都能被主属性筛选。其实简单理解要设置一个区分各个记录的主键就好了。

• 第三范式： 在第二范式的基础上属性不传递依赖，即每个属性不依赖其他非主属性。要求一个表中不包含已在其它表中包含的非主关键字信息。其实简单来说就是合理使用外键，使不同的表中不要有重复的字段就好了。

MySQL存储引擎

即mysql数据库管理系统中用来处理表的处理器

1、查看所有存储引擎
   mysql> show engines;
2、查看已有表的存储引擎
   mysql> show create table 表名;
3、创建表指定
   create table 表名(...)engine=MyISAM;
4、已有表指定
   alter table 表名 engine=InnoDB;

常用存储引擎特点

InnoDB

1. 支持行级锁,仅对指定的记录进行加锁，这样其它进程还是可以对同一个表中的其它记录进	行操作。
2. 支持外键、事务、事务回滚
3. 表字段和索引同存储在一个文件中
		 1. 表名.frm ：表结构
 		 2. 表名.ibd : 表记录及索引文件

MyISAM

1. 支持表级锁,在锁定期间，其它进程无法对该表进行写操作。如果你是写锁，则其它进程则		读也不允许
2.  表字段和索引分开存储
      	 1. 表名.frm ：表结构
         2. 表名.MYI : 索引文件(my index)
         3. 表名.MYD : 表记录(my data)

如何选择存储引擎

1. 执行查操作多的表用 MyISAM(使用InnoDB浪费资源)
2. 执行写操作多的表用 InnoDB

CREATE TABLE user(
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(30) DEFAULT NULL,
sex varchar(2) DEFAULT NULL,
PRIMARY KEY (id)
)ENGINE=MyISAM;

alter table hobby engine=myisam;

字段数据类型和键的选择

数据类型优先程度   数字类型 -->  时间日期类型  --> 字符串类型
同一级别   占用空间小的 --> 占用空间大的

字符串在查询比较排序时数据处理慢
占用空间少，数据库占磁盘页少，读写处理就更快

Innodb如果不设置主键也会自己设置隐含的主键，所以最好自己设置
尽量设置占用空间小的字段为主键
建立外键会自动建立索引，在表关联查询时建议使用外键子段作为关联条件
外键虽然可以保持数据完整性，但是会降低数据导入和操作效率，增加维护成本，不要大量使用

explain语句

使用 EXPLAIN 模拟查询语句，从而知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。通过explain命令可以得到:

- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询

explain select * from class where id <5;

EXPLAIN主要字段解析：

table：显示这一行的数据是关于哪张表的

type：查询使用的类型。一般来说，得保证查询至少达到range级别，最好能达到ref

type中包含的值(最好到最差)：
- system、const： 可以将查询的变量转为常量. 如id=1; id为 主键或唯一键.
- eq_ref： 访问索引,返回某单一行的数据.(通常在联接时出现，查询使用的索引为主键或唯一键)
- ref： 访问索引,返回某个值的数据.(可以返回多行) 通常使用=时发生 
- range： 这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西，并且该字段上建有索引时发生的情况
- index： 以索引的顺序进行全表扫描，优点是不用排序,缺点是还要全表扫描 
- ALL： 全表扫描，应该尽量避免

possible_keys：显示可能应用在这张表中的索引。如果为空，表示没有可能应用的索引。
key：实际使用的索引。如果为NULL，则没有使用索引。
key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
rows：MySQL认为必须检索的用来返回请求数据的行数

SQL优化

尽量选择数据类型占空间少，在where ，group by，order by中出现的频率高的字段建立索引
尽量避免使用 select * ...; 用具体字段代替 * ,不要返回用不到的任何字段 
尽量控制使用自定义函数
查询最后添加 LIMIT 会停止全表扫描
尽量避免 NULL 值判断,否则会进行全表扫描，默认值为空时可以用默认0代替
  优化前：select number from t1 where number is null;
  优化后：select number from t1 where number=0;

尽量避免 or 连接条件,否则会放弃索引进行全表扫描，可以用union代替
  优化前：select id from t1 where id=10 or id=20;
  优化后： select id from t1 where id=10 union all  select id from t1 where id=20;

尽量避免使用 in 和 not in,否则会全表扫描
  优化前：select id from t1 where id in (1,2,3,4);
  优化后：select id from t1 where id between 1 and 4;

表的拆分

垂直拆分 ： 表中列太多，分为多个表，每个表是其中的几个列。将常查询的放到一起，blob或者text类型字段放到另一个表

水平拆分 ： 减少每个表的数据量，通过关键字进行划分然后拆成多个表

数据库安全和管理

表的复制

#表能根据实际需求复制数据，复制表时不会把KEY属性复制过来
create table 表名 select 查询命令;
create table student select name,age,score from class where score>80

数据库备份

#备份命令格式 
mysqldump -u root -p stu > stu.sql
#恢复命令格式
mysql -u root -p student < stu.sql

MySQL远程连接

cd /etc/mysql/mysql.conf.d
sudo vi +43mysqld.cnf  
# bind-address = 127.0.0.1   # 注释
sudo service mysql restart

update mysql.user set host='%' where user='root';
flush privileges;

添加用户和授权

1. 用root用户登录mysql
   mysql -u root -p
   
2. 添加用户 % 表示自动选择可用IP
   CREATE USER 'username'@'host' IDENTIFIED BY 'password';
   
3. 权限管理
   # 增加权限
   grant 权限列表 on 库.表 to "用户名"@"%" identified by "密码" with grant option;
   # 删除权限
   revoke insert,update,select on 库.表 from 'user'@'%';
   
4. 刷新权限
   flush privileges;
   
5. 删除用户
   drop user "用户名"@"%"

权限列表

all privileges ，select ，insert ，update，delete，alter，create，drop等。
库.表 ： *.* 代表所有库的所有表

示例

1. 创建用户
  mysql>create user  'work'@'%'  identified by '123';
  
2. 添加授权用户work,密码123,对所有库的所有表有所有权限
  mysql>grant all privileges on *.* to 'work'@'%' identified by '123' with grant option;
  mysql>flush privileges;
  
3. 添加用户duty，密码123,对books库中所有表有查看，插入权限
  mysql>grant select,insert on books.* to 'duty'@'%' identified by '123' with grant option;
  mysql>flush privileges;
  
4. 删除work用户的删除权限
  mysql>revoke delete on *.* from "work"@"%";
  
5. 删除用户duty
  drop user "duty"@"%";