MySQL调优（一）

最新推荐文章于 2024-09-06 08:47:20 发布

冯居竹

最新推荐文章于 2024-09-06 08:47:20 发布

阅读量194

点赞数 1

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/qq_41098689/article/details/106505572

版权

前置

首先我们需要明白一点，那就是mysql是模块化的，相当于一个完整的机器其内部零件是可替换的。

在这里插入图片描述
其分为四层，从上到下分为连接层、服务层、引擎层
以及存储层，接下来大致介绍一下各层的主要功能。

1.连接层
最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供先成功，同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限
2.服务层

Management Serveices & Utilities：

系统管理和控制工具

SQL Interface: SQL接口

  接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface

  Parser: 解析器

   SQL命令传递到解析器的时候会被解析器验证和解析。

Optimizer: 查询优化器。

 SQL语句在查询之前会使用查询优化器对查询进行优化。 
 用一个例子就可以理解： select uid,name from user where  gender= 1;
 优化器来决定先投影还是先过滤。

Cache和Buffer：查询缓存。

  如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。
  这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等
   缓存是负责读，缓冲负责写。

3.引擎层
存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信，不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。
4.存储层
数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互

在这里插入图片描述
查询流程大致是：
mysql客户端通过mysql通过关键字将SQL语句进行解析，并生成一颗对应的“解析树”。mysql解析器将使用mysql语法规则验证和解析查询；预处理器则根据一些mysql规则进一步检查解析数是否何发。
查询优化器在当解析数被认为是合法的了，并且由优化器将其转化成执行计划。一条查询可以有多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。
然后，mysql默认使用BTREE索引，并且一个大致方向是:无论怎么折腾sql，至少在目前来说，mysql最多只用到表中的一个索引。协议与mysql服务器建立连接，发送查询语句，先检查查询缓存，如果命中(一摸一样的sql才能命中)，直接返回结果，否则进行语句解析，也就是说，在解析查询之前，服务器会先访问查询缓存-它存储select语句以及相应的查询结果集。如果某个查询结果已经位于缓存中，服务器就不会再对查询进行解析，优化，以及执行。它仅仅将缓存中的结果返回给用户即可，这将大大提高系统的性能。

一、SQL语句性能下降的原因
1.查询数据过多
2.关联了太多的表，太多的join
3.没有利用到索引（单值索引、复合索引）
4。服务器调优及各个参数设置(缓冲、线程数等)

二、SQL顺序
我们首先需要明确的一点是输入的sql语句与执行的顺序是不一样的，这是我们输入的顺序
select distinct <select_list> from <left_table><join_type>
joiin <right_table> on <join_condition> where <where_condition> group by <group_by_list> having <having_condition> order by <order_by_condition>
limit<limit_number>
这是执行的顺序：
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>
在这里插入图片描述

Join图理解

在这里插入图片描述
共有：满足 a.deptid = b.id 的叫共有
A独有: A 表中所有不满足 a.deptid = b.id 连接关系的数据
同时参考 join 图
1 A、B两表共有
select * from t_emp a inner join t_dept b on a.deptId = b.id;

2 A、B两表共有+A的独有
select * from t_emp a left join t_dept b on a.deptId = b.id;

3 A、B两表共有+B的独有
select * from t_emp a right join t_dept b on a.deptId = b.id;

4 A的独有
select * from t_emp a left join t_dept b on a.deptId = b.id where b.id is null;

5 B的独有
select * from t_emp a right join t_dept b on a.deptId = b.id where a.deptId is null;

6 AB全有
#MySQL Full Join的实现因为MySQL不支持FULL JOIN,下面是替代方法
#left join + union(可去除重复数据)+ right join
SELECT * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id
UNION
SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id
这里因为要联合的缘故，不能考虑到小表驱动大表的情况。只能用right join。要保证查询出来的数字要一致。
7 A的独有+B的独有
select * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id WHERE B.id IS NULL
UNION
SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id WHERE A.deptId IS NULL;

至此，熟悉了知识以后，我们将进入索引的复习。