一些小知识点

（1）使用mysql进行集合运算

1.并集 union 取并集

SELECT product_id, product_name

FROM product

UNION

SELECT product_id, product_name

FROM product2;

2.差集 not in

SELECT *

FROM Product

WHERE product_id NOT IN (SELECT product_id

                        FROM Product2)
1

3.交集 inner join

SELECT p1.product_id, p1.product_name

FROM Product p1

INNER JOIN Product2 p2

ON p1.product_id=p2.product_id

4. 对称差

SELECT *

FROM Product

WHERE product_id NOT IN (SELECT product_id FROM Product2)

UNION

SELECT *

FROM Product2

WHERE product_id NOT IN (SELECT product_id FROM Product)

5.笛卡儿积 cross join

笛卡尔积：

SELECT SP.shop_id

   ,SP.shop_name

   ,SP.product_id

   ,P.product_name

   ,P.sale_price
1
2
3
4
5
6
7

FROM ShopProduct AS SP

CROSS JOIN Product AS P;

（2）mysql 破除死锁的方法

设置事务等待的超时时间

​innodb_lock_wait_timeout

2. 开启主动死锁检测

innodb_deadlock_detect

(3) 查看最近发生的一次死锁的信息

show engine innodb status

(4)mysql执行一条sql语句的流程

首先第一步其实是查询缓存，但是mysql8取消了查询缓存。虽然查询缓存提升系统的性能，但是维护缓存的开销也比较大

解析语句（查询分析）

select title from order where id=1

①词法分析 将关键字提取出来，上面这句sql会被分成8个部分

在elasticsearch引擎中也有一个字典对语句进行分词

②语法分析 根据词法分析的结果，生成语法树（解析树），判断sql语句是否满足语法规则。

如果语句有语法错误的话会在这个时候显示‘You have an error in your SQL syntax’

2. 查询检查（预处理器）

①语义分析

形成关系代数转化成的语义树

②符号名转化

将变量和符号表中的定义绑定

如果字段名和表名不存在会报错 UnKnow column ‘abc’ in ‘field list’

③安全性检查

​- 自主存取控制

​- 强制存取控制
1
2
3

④完整性初步检查

​- 参照完整性

​- 实体完整性

​- 用户定义完整性
1
2
3
4
5

3. 查询优化（优化器）

mysql 使用基于开销的优化器，选择开销较小的计划

mysql通过查询当前会话的 last_query_cost的值来得到计算当前查询的成本

查看last_query_cost的命令:show status like ‘last_query_cost’

考虑的开销可能有：每张表或者索引的页面个数，索引的基数，索引和数据行的长度。索引的分布等等。

优化的策略有：

① 重新定义表的关联顺序，这时候要看表的大小,或者有可能将将外连接转换为内连接，子查询转化为连接

②优化查找最大最小函数

③优化排序

…

4. 查询执行（执行器）

对上述得到的执行策略生成执行计划，让代码生成器执行这个查询计划，从存储引擎取出数据返回给客户端