八股文mysql

最新推荐文章于 2024-02-28 21:20:52 发布

扣扣糖qqt

最新推荐文章于 2024-02-28 21:20:52 发布

阅读量296

点赞数 1

分类专栏：八股总结文章标签： mysql pandas 数据库

本文链接：https://blog.csdn.net/enheihei/article/details/129844749

版权

系列文章目录

提示：这里可以添加系列文章的所有文章的目录，目录需要自己手动添加
例如：第一章 Python 机器学习入门之pandas的使用

文章目录

系列文章目录
前言
一、基础
二、索引
3、事务
4、锁
5、日志
6、内存
7 番外
- 7.1 mysql分库分表
总结

前言

mysql这段时间八股文总结，后面有待补充

一、基础

1.1连接器

连接命令： mysql -h$ip -u$user -p$pwd
顾名思义，$ip是连接的IP地址，如果是连接本地的mysql服务，则无需该参数；$是指定的用户名，管理员角色为root；$pwd指定的密码，自己电脑上试验一次该命令：
在这里插入图片描述

连接时先经过TCP三次握手mysql是基于TCP协议传输的，如果一切没有问题，连接器会获得该用户的权限，然后保存起来，后续该用户在此连接里的任何操作，都会基于连接开始时读取到的权限来进行权限逻辑的判断。所以如果一个用户已经建立了连接，即使管理员中途修改了该用户的权限，也不会影响已经存在连接的权限。修改完成后，只有再建新的连接才会使用新的权限设置。

如何查看mysql服务被多少个客户端连接了？
执行show processlist命令来进行查看，查看截图如下所示：
在这里插入图片描述
如图所示，command分别为daemon守护进程和query操作进程（crud），sleep（休眠进程），休眠的连接。
休眠的连接会一直占用着嘛，mysql定义了空闲连接的最大空闲时长，由wait_timeout参数控制的，空闲连接超过这个时间，连接器就会自动将该连接断开，在自己的mysql执行一下如下图所示：
在这里插入图片描述
默认值是8小时（28800s），也可以使用kill connection+id的命令来手动断开空闲的连接。

mysql的连接数有限制嘛
mysql服务支持的最大连接数由max_connections参数控制，在自己的电脑上执行查看mysql最大连接数的命令，如下图所示：
在这里插入图片描述
我的mysql上默认是200个，超过这个值，系统会拒绝接下来的连接请求too many connections
长连接和短链接

//短链接
连接mysql服务（TCP三次握手）
执行mysql
断开mysql服务（TCP四次挥手）
//长连接
连接mysql服务（TCP三次握手）
执行sql
执行sql
执行sql
,…
断开mysql服务（TCP四次挥手）

可以看到使用长连接的好处就是可以减少建立连接和断开连接的过程，所以一般推荐使用长连接
但是使用长连接后可能占用内存增多，因为mysql在执行查询过程中临时使用内存管理连接对象，这些连接对象会一直保存在内存中只有在连接断开时才会释放。如果长连接累计很多，将导致mysql服务占用内存太大，有可能被系统强制杀掉，这样会发生mysql服务异常重启的现象。

怎么解决长连接占用内存的问题

定期断开长连接。
客户端主动重置连接。mysql5.7版本实现了mysql_reset_connection()函数的接口。当客户端执行了很大的操作后，在代码里调用该函数来重置连接，达到释放内存的效果。

连接器总结

与客户端进行三次握手建立连接
校验客户端的用户名和密码，如果用户名或密码不对，则会报错
如果用户名和密码都对了，就会读取该用户的权限，然后后面的权限逻辑判断都是基于此时读取到的权限。

1.2查询缓存

如果sql是查询语句（select 语句），mysql就会先去查询缓存（query cache）里查找缓存数据，看看之前有没有执行过这一条命令，这个查询缓存是以key-value形式保存在内存中的，key为sql查询语句，value为sql语句查询的结果。同一般的缓存一样，如果命中缓存，则直接返回value给客户端；如果未命中，则继续往下执行，等执行完后，再将查询的结果存入查询缓存中。
mysql查询缓存很鸡肋，对于更新频繁的表，查询缓存的命中率是很低的，mysql8.0之后将查询缓存删掉了注意是server层的查询缓存，存储引擎中的查询缓存仍然保留

1.3 解析sql

在正式执行sql查询语句前，mysql会先对sql语句做解析，这个工作交给解析器来完成。
解析器会做如下两件事：

词法分析，mysql会根据你输入的字符串识别出关键字，构建出sql语法树，这样方便后面模块获取sql类型、表名、字段名、where条件等。
语法分析。根据词法分析的结果，语法解析器会根据语法规则，判断你输入的这个sql语句是否满足mysql语法。
如果输入的sql语句语法不对，就会在解析器这个阶段报错。如下面语句from写成form，此时mysql解析器报错，错误截图如下所示：

表不存在或者字段不存在，不是在解析器做的，是在接下来的阶段做的。

1.4 执行sql

经过解析器后，接着要进入执行sql查询语句的流程了，每条select查询语句流程主要可以分为下面三个阶段：

prepare阶段，也就是预处理阶段
optimize阶段，也就是优化阶段
execute阶段，也就是执行阶段

预处理器

检查sql查询语句中的表或者字段是否存在
将select *中的*符号，扩展为表上的所有列

优化器
经过预处理阶段后，还需要为sql查询语句先制定一个执行计划，这个工作交给优化器来完成。
优化器主要负责将sql查询语句的执行方案确定下来，比如表里面有多个索引的时候，优化器会基于查询成本的考虑，来决定选择使用哪个索引，简单的查询语句select * from goverment where id = 27，就使用主键索引，想知道优化器使用了哪个索引，可以在查询语句最前面加个explain命令，即会输出这条sql语句的执行计划，执行计划中的key就表示执行过程中使用了哪个索引，比如下图的key为primary就是使用了主键索引。
在这里插入图片描述
如果查询语句的执行计划中的key为null说明没有使用索引，那就会全表扫描(type = ALL),这种扫描的方式是效率最低档次的，如下图：

现在将这张表中的name设置为普通索引（二级索引）

这时users表中就有主键索引（id）和普通索引（name）。假设执行查询语句
select id from users where id > 28 and name like 's%';
这条查询语句既可以用主键索引也可以用普通索引，但执行的效率会不同。这时就需要优化器来决定使用哪个索引。
这条语句采用了覆盖索引,直接在二级索引就能查到结果（因为二级索引的B+树的叶子节点的数据存储的是主键值），就没必要在主键索引查找了，因为查询主键索引的B+树的成本会比查询二级索引的B+成本大，优化器基于查询成本的考虑，会选择查询代价小的普通索引。
执行语句后，输出如下图所示：
在这里插入图片描述
如图所示：possible_keys代表可以选择的索引，key代表优化器选择的索引，可以看到优化器选择了普通索引