MYSQL大数据量下的，SQL练习，SQL常用高级关键字讲解

如果你没有数据练习，请看我这篇博客

https://blog.youkuaiyun.com/weixin_44012722/article/details/107974662

现在开始练习(以下练习都是以上面的数据集作为练习数据)

关键字	作用
JOIN	连接查询
UNION	合并表结果
EXPLAIN	查看SQL语句的执行计划
EXISTS / NOT EXISTS	EXIST的内查询语句
VARIABLES	mysql系统变量及其值
PROFILES / PROFILE	检查当前会话SQL的消耗性能分析描述
FUNCTION	函数
PROCEDURE	存储过程
更多的关键字

1.JOIN

1. left join

返回左表拥有的emp_no，

select e.emp_no,birth_date,first_name,last_name,gender,salary from employees e left join salaries s on e.emp_no = s.emp_no 

//返回左表有的emp_no，且右表没有的emp_no
select e.emp_no,birth_date,first_name,last_name,gender,salary from employees e left join salaries s on e.emp_no = s.emp_no where s.emp_no is null 

2. right join

返回右表拥有的emp_no

select e.emp_no,birth_date,first_name,last_name,gender,salary from employees e right join salaries s on e.emp_no = s.emp_no 

//返回右表有的emp_no，且左表没有的emp_no
select e.emp_no,birth_date,first_name,last_name,gender,salary from employees e right join salaries s on e.emp_no = s.emp_no where s.emp_no is null 

3. inner join

返回左右表都有的emp_no

select e.emp_no,birth_date,first_name,last_name,gender,salary from employees e inner join salaries s on e.emp_no = s.emp_no 

4. full join / full outer join (Oracle数据库支持full join，mysql是不支持full join的，但仍然可以同过左外连接+ union+右外连接实现)

返回左表所有有的emp_no和右表所有的emp_no

//Oracle数据库支持full join
select e.emp_no,m.emp_no,first_name,last_name,motto from employees e full join motto m on e.emp_no = m.emp_no 

2.UNION

select e.emp_no,birth_date,first_name,last_name,gender,salary from employees e left join salaries s on e.emp_no = s.emp_no where s.emp_no is null 
union
select e.emp_no,birth_date,first_name,last_name,gender,salary from employees e right join salaries s on e.emp_no = s.emp_no 

3.EXPLAIN

在日常工作中，我们会有时会开慢查询去记录一些执行时间比较久的SQL语句，找出这些SQL语句并不意味着完事了，些时我们常常用到explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。所以我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。

执行EXPLAIN可以查询 到以下字段

字段名	意义
id	表示查询中执行select子句或操作表的顺序序列号( id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行)
select_type	查询类型
table	显示 这行数据关于哪一张表
type	访问类型
possible_keys	显示可能应用在这张表中索引，一个或多个，，但不一定被查询实际用到
key	实际用到的索引
key_len	表示索引中使用的字节数，查询中使用的索引长度，越短越好
ref	哪些列字段或常量被用于查找索引列上的值
rows	找出数据所需要读取的行数
extra(#extra)	额外的信息描述

- id

//id相同  id如果相同，可以认为是一组，从上往下顺序执行；
explain select t2.* from t1,t2,t3 where t1.id = t2.id and t1.id = t3.id and t1.col_1 = ''

//id不同 d值越大，优先级越高，越先执行
explain select t2.* from t2 where id = (select t1.id from t1 where id = (select t3.id from t3 where t3.col_1 = ''))

- select_type

查询类型只要区别，普通查询，联合查询，子查询的复杂查询

名字	介绍
SIMPLE	简单的select查询，查询中不包含子查询或者UNION
PRIMARY	查询中若包含任何复杂 的子部分，最外层查询则标记为PRIMARY
SUBQUERY	在SELECT或WHERE列表中包含子查询
DERIVED	在FROM列表中包含子查询，MYSQL会递归执行这些子查询把结果放在临时表
UNION	若在第二个SELECT出现UNION就标记为 UNION,若UNION包含在FROM子句的子查询中，外层SELECT将被标记为DERIVED

- type

$全部访问类型$

system > const > eq_ref > ref > fulltext > ref_or_null > index-merge > unique_subquery > index_subquery > range > index > ALL

$常用访问类型$

system > const > eq_ref > ref > range > index > ALL

$一般来说至少要达到range级别为好，最好到达ref级别$

级别	描述
system	表只有一行数据（等于系统表），平时不会出现，可以忽略不计
const	表示通过一次索引就找到了，const常用于比较primary和unique索引，因为只匹配一行数据
eq_ref	常用于比较primary和unique索引,对于每个索引键，表中都只有 一条记录与之匹配
ref	用于非primary和unique索引，返回匹配某个单独的所有行
range	给定范围的索引查询，一般出现 关键字 between，>，<
index	读取全表，因为index只读取索引树，通常比all快，因为索引文件比数据文件小
all	读取全表，从硬盘中读取数据文件

const和eq_ref的区别 (刚开始我也分不清，后面知道了，分享一下)

两者相同都是用于比较primary和unique索引，不同地方是，const是通过一次索引比较，找出一条记录，而eq_ref是对于每个索引键，表中都只有 一条记录与之匹配，找出1条记录，看以下sql，品一下

const

explain select * from t1 where id = 1

eq_ref

explain select * from t1,t2  where t1.id = t2.id

- extra

名称	描述	SQL性能
Using filesort	MYSQL无法利用索引完成的排序称作为“文件排序”	差
Using temporary	使用了临时表保存中间结果，MYSQL在对查询结果排序时使用了临时表	极差
Using index	表示索引被作为读取字段进行查找	优
Using where	表示索引被作为条件进行查找	优
Using join buffer	使用了join连接查询缓冲区	一般
impossible where	不可能的条件	无
select tables optimized away	MySQL根本没有遍历表或索引就返回数据了	优
distinct	优化distinct操作，在找到第一个匹配的数据后停止查找	优

以上就是EXPLAIN关键字的介绍，想进行优化SQL练习的请看我这篇博客

4.EXISTS / NOT EXISTS

EXISTS(包括 NOT EXISTS )子句的返回值是一个BOOL值。 EXISTS内部有一个子查询语句(SELECT … FROM…)， 我将其称为EXIST的内查询语句。其内查询语句返回一个结果集。 EXISTS子句根据其内查询语句的结果集空或者非空，返回一个布尔值。将外查询表的每一行，代入内查询作为检验，如果内查询返回的结果取非空值，则EXISTS子句返回TRUE，这一行行可作为外查询的结果行，否则不能作为结果,反而反之

例子

select * from salaries s where emp_no in ( select emp_no from employees )

select * from salaries where exists ( select emp_no from employees )

可以看到查询结果都一样，但是执行时间不一样，一个用in嵌套子查询，一个用exists内查询，因为salaries是数据比employees的数据要多，所以in查询相当于大表驱动小表，相当于他要去salaries表循环N次，每次要去employees表查找是否有相同emp_n，而exists是小表驱动大表，遍历employees表中数据，如果满足salaries表中emp_no数据相同则返回emp_no作为外查询作为结果集。小表驱动大表是推荐，效率高

5.VARIABLES

mysqld服务器维护两种变量。——GLOBAL变量 / SESSION变量

当mysql启动他就会按照 my.ini 里的配置信息 设置到 GLOBAL变量中， 当有一个客户端来连接，就会将GLOBAL的配置信息变量 复制到 新创建的这个SESSION变量中维护。所以当MYSQL重启就会重新把my.ini中配置信息放入GLOBAL中。

1.想要永久该配置信息就要改 my.ini文件

2.想要临时改变配置信息，选择可以改变GLOBAL或SESSION

SET GLOBAL var_name = var_val
SET SESSION var_name = var_val
#默认改变SESSION中的变量
SET var_name = var_val

GLOBAL 和 SESSION 的区别就是，改变GLOBAL变量，后续连接的客户端也会改变，但不影响已连接的客户端，如果SESSION变量改变了，只影响执行该SQL的当前客户端，不影响其他 ，当客户端断开连接，重新连接他又是从GLOBAL中初始化配置信息

如果想知道配置参数变量的意思可以看这篇博客https://blog.youkuaiyun.com/qq_30450439/article/details/80089892（变量值1为ON,0为OFF）

6.PROFILES / PROFILE

用于分析当前连接客户端会话中语句执行的资源消耗情况，可用于SQL调优测

默认参数处于关闭状态，开启后 保存最近15次的运行结果

SHOW VARIABLES like 'profiling'
# 如果关闭了请开启
SET profiling=on
#随便执行一条查询SQL
#查看当前会话所有执行的SQL的消耗信息
SHOW PROFILES

#展示指定 查询ID 查看此SQL的消耗性能的过程情况
SHOW PROFILE FOR QUERY 查询ID

SHOW PROFILE 还可以显示其他详细的性能消耗信息

名称	作用
ALL	显示所有的开销信息
BLOCK IO	显示块IO相关开销
CONTEXT SWITCHES	上下文切换相关开销
CPU	显示CPU相关开销信息
IPC	显示发送和接收相关开销信息
MEMORY	显示内存相关的开销信息
PAEG FAULTS	显示页面错误相关开心信息
SOURCE	显示和Source_function,Source_file,Source_line相关的开销信息
SWAPS	显示交换次数想相关开销的信息

#使用例子
SHOW PROFILE CPU,SOURCE FOR QUERY 查询ID

当你的SQL性能消耗信息STATUS中出现了以下，你要注意

SATATUS	描述	危险等级
converting HEAP to MyISAM	查询结果太大，内存都不够使用，搬去磁盘了	危险
Creating tmp table	拷贝查询结果到临时表，用完再删除	危险
Copying to tmp table on disk	把内存中临时表复制到磁盘	极危险

因为 SHOW PROFILES 是显示15条，不能显示全部，如果要查询全局就要开启 全局日志查询（他会记录所有查询SQL）

此功能最好在测试环境中开启，因为生产环境中会占用性能，使性能下降

#开启全局日志
SET general_log = 1
#显示当前会话配置参数 general_log_file全局日志查询文件路径 log_output日志文件格式
SHOW VARIABLES LIKE 'general_log_file'
SHOW VARIABLES LIKE 'log_output'
# 如果改变log_output日志文件格式为TABLE,他就会记录在mysql库中general_log表中
SET GLOBAL log_output = 'TABLE'
#客户端重新连接

7.FUNCTION

mysql的自定义函数，用于封装自定义方法

7.1. 创建函数

DELIMITER $$  #定界符（函数定义开始标志）
DROP FUNCTION IF EXISTS 方法名$$
CREATE FUNCTION 方法名(形参变量名 形参数据类型) RETURN 返回参数类型
BEGIN
  .....;
  .....;
  RETURN 返回参数;
END $$ #函数定义结束标志

8.PROCEDURE

存储过程，用于批量执行sql语句逻辑，与 函数 区别是没有返回值

8.1. 创建存储过程

DELIMITER $$  #定界符（存储过程定义开始标志）
DROP FUNCTION IF EXISTS 存储过程名$$
CREATE FUNCTION 存储过程名(形参变量名 形参数据类型)
BEGIN
  .....;
  .....;
END $$ #存储过程定义结束标志

8.2. 调用存储过程

DELIMITER $  #定义定界符
CALL 存储过程名(参数变量..)$

9.更多关键字正在持续的更新中。。。

一键查询淘宝/拼多多内部优惠券，每日大额外卖红包，购物省钱的宝藏工具