MYSQL sql语句分析与实现sql优化操作

╰+哭是因爲堅強的太久メ 2022-03-26 06:44 236阅读 0赞

一，mysql的组成结构和sql的执行顺序  
1，mysql的组成结构  
Mysql是有SQL接口，解析器，优化器，缓存，存储引擎组成的。  
SQL Interface：SQL接口  
接受用户的SQL命令，并且返回用户需要查询的结果。  
Parser：解析器  
SQL命令传递到解析器的时候回被解析器验证和解析。  
Optimizer：查询优化器  
SQL语句在执行之前会用查询优化器对查询进行优化。他使用的是“选取-投影-链接”策略进行查询。  
Select uid,name from user where uid=1;  
chche和buffer：查询缓存  
如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存取数据。  
这个缓存机制是由一系列的小缓存组成的，比如表缓存，记录缓存，key缓存，权限缓存等。  
Engine：存储引擎  
存储引擎是mysql中具体的与文件打交道的子系统。也是mysql最具有特殊的一个地方。  
Mysql的存储引擎是插件式的。他根据mysql ab公司提供的文件访问层的一个抽象接口定制一种文件访问机制（这种访问机制就叫存储引擎）。  
现在有很多种存储引擎，各个存储引擎的优势各不一样，最常用的MyISIAM，InnoDB，BDB。  
MyISIAM，查询速度快，有较好的索引优化和压缩技术，但是它不支持事物。  
InnoDB支持事物，并且提供行级锁定，应用也是相当广泛。  
支持事物，行锁，应用广泛  
2，mysql的执行顺序  
mysql的执行顺序，这个是写SQL的核心  
如何进行多表查询，优化，这个是很重要的部分  
SQL语句的函数，SQL提供的函数方便了很多操作

1）mysql的语法顺序，即当SQL中存在下面的关键字时，他们要保持这样的顺序。

Select \[distinct\]  
from  
join(left join)  
on  
where  
Group by  
Having  
Union  
Order by  
limit  
2）mysql执行顺序，即在执行SQL语句时按照下面的顺序进行执行：  
where  
From  
On  
Join  
Group by  
Having  
Select  
Distinct  
Union  
Order by  
二，mysql的日志文件和数据文件  
1，mysql的日志文件和数据文件的作用  
Mysql有7种日志文件，分别是：重做日志，回滚日志，二进制日志，错误日志，慢查询日志，一般查询日志，中继日志。  
重做日志：  
作用：确保事务的持久性。防止在发生故障的时间点，尚有未写入磁盘的，在重启mysql服务的时候，根据重做日志进行重做，从而达到事物的持久性这一特点。  
回滚日志：  
作用：保存了事务发生之前的一个版本，可以用于回滚，同时可以提供多版本并发下的读，也就是非锁定读。  
二进制日志：  
用于复制，在主从复制中，从库利用主库上的二进制日志进行重播，实现主从同步。用于数据库基于时间点的还原。

数据文件的作用：保存数据  
包含数据文件上的所有索引和索引树  
2，mysql的日志文件和数据文件的存放目录  
Win：  
日志文件和数据文件的存放目录:Mysql/mysql server 5.0/data/  
配置文件存放目录：mysql\\mysql5.7.14\\my.ini  
Linux:  
默认存放地址：/var/lib/mysql/  
三，mysql表连接（外链接，内连接，左链接，右链接，笛卡尔积）  
1，inner join（内连接）  
介绍  
1）inner join子句是将一个表中的行与其他表中的行进行匹配，并且允许从两个表中查询包含列的行记录。  
2）inner join子句是select语句的可选部分，出现在from子句之后。  
3）使用inner join子句之前，必须制定以下条件：  
首先，在from子句中制定主表。  
其次，表中要链接的主表应该出现在inner join子句中。理论上说，可以链接多个其他的表，但是，为了获得更好的性能，应该限制要链接表的数量（不超过三个）。  
第三，链接条件或链接谓词。链接条件出现在inner join子句的on关键字之后，链接条件是将主表中的行与其他表中的行进行匹配的规则。  
使用语法：  
Select uid，name  
From t1  
inner join t2 on [t1.uid=t2.id][t1.uid_t2.id]  
inner join t3 on  
……  
Where t1.struts=1  
原理：对于t1表的每一行，inner join子句将它与t2表的每一行进行比较，以检查他们是否都满足链接条件，当满足链接条件时，inner join将返回由t1和t2表中的列组成的新行。  
2，左连接（left join）  
left join子句允许从匹配的左右表中查询选择行记录，链接左表（t1）中的所有的行，即使在右表（t2）中找不到匹配的行，也显示出来，使用null值代替。  
工作原理  
基本语法：  
Select  
[U.name][],u.sex,[n.name][]  
From user u  
Left join name n  
3，右链接（right join）  
4，交叉连接（cross join）  
简介  
cross join子句从连接的表返回的笛卡尔乘积。  
假设使用cross join链接两个表，结果集将包含两个表中的所有行，其中结果集中的每一行都是第一个表中的行与第二个表中的行的组合。当链接的表之间没有关系时，会使用这种情况。  
Select \* from t1 cross join t2  
cross join子句不具有链接条件。  
如果天剑where子句，如果t1和t2有关系，则cross join的工作方式与inner join子句类似  
Select  
\*  
From  
T1  
Cross join  
T2  
Where  
[T1.id=t2.id][T1.id_t2.id]  
5，笛卡尔积  
1）两个集合相乘，不满足交换律，AxB!=BxA  
2）A集合与B集合相乘，包含了集合A中的元素与集合B中的元素相结合的所有的可能性。就是两个集合相乘得到的新集合的个数是A集合的元素个数X B集合的元素个数。

SELECT \* from student JOIN student\_subject  
以这条SQL为例，看一下他的执行流程  
1，from语句把student表和 student\_subject表从数据库文件加载到内存中  
2，Join语句相当于对两张表做乘法运算，把student表中的每一行与student\_subject表中的记录依次匹配。

针对以上理论，我们提出一个问题，难道表连接的时候都要先形成一张笛卡尔积表吗？如果两张表的数据量都比较大的话，那样就会占用很大的内存空间，这显然是不合理的。所以，我们在进行表连接查询的时候一包都会使用join XXX ON XXX的语法，on语句的执行是在join语句之前的，也就是说两张表数据行之间进行匹配的时候，会先判断数据行是否符合on语句后的条件，在决定是否join。  
因此，有一个显而易见的SQL优化的方案是，当两张表的数据量比较大，又需要连接查询时，应该使用from t1 join t2 on xxx的语法，避免使用from t1，t2 where xxx 的语法，因为后者会在内存中先生成一张数据量比较大的笛卡尔积表，增加了内存的开销。

Sql语句的执行流程  
From  
On  
Join  
Where  
Group by  
Select  
Having  
Order by  
Limit

四，mysql表连接的链接方法以及on、where的作用  
Where查询条件  
On内外链接时候用 链接条件  
As 作为别名  
In 查询某值是否在某条件里

[t1.uid_t2.id]: http://t1.uid=t2.id
[U.name]: http://U.name
[n.name]: http://n.name
[T1.id_t2.id]: http://T1.id=t2.id