mysql多条一样的留一条

转载

mob64ca14147fe3 2024-11-04 17:45:47

文章标签 mysql多条一样的留一条 mysql sql 数据库存储引擎 文章分类 MySQL 数据库

Mysql基础架构

MySql主要分为Server层和存储引擎层

server层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog （所有引擎可以共用）。
存储引擎层：主要负责数据的存储和读取，采用可替换的插件式架构，即可以按需替换MySql的存储引擎。支持InnoDB、MylSAM、Memory等多种存储引擎。其中MySql5.5.5版本以后默认存储引擎为InnoDB，它还有自有的日志模块：redolog日志。

mysql多条一样的留一条_存储引擎

Server层组件：
连接器
连接器负责和客户端建立连接，获取权限，管理连接等。
登录命令

mysql -h${主机名或主机IP} -P${端口} -u${用户名} -p ${数据库名};      # 回车之后再输入密码
mysql -h${主机名或主机IP} -P${端口} -u${用户名} -p${密码} ${数据库名};    # 不建议，会显示数据库密码

连接命令中的mysql是客户端工具，用来跟服务端建立连接。在完成TCP握手后，连接器就要开始使用你输入的用户名来密码来验证你的身份，如果用户名密码正确，连接器会到权限表里查询的所拥有的权限，此连接之后的权限判断都会以来于此时读取到的权限信息，也就是说一个用户在成功建立连接之后，即使管理员修改了该用户的权限，在此次连接中也不会生效，如果新建连接才会生效。

连接完成后如果没有后续操作，连接处于空闲状态，使用show processlist可以查看。

mysql多条一样的留一条_mysql_02

Command列为“sleep”，表示空闲连接.

客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数wait_timeout控制的，默认值是8小时。

查询缓存
MySql执行过的语句可能会将其以key-value的形式存入缓存中，key是执行的sql语句，value是执行结果。MySql在拿到一个查询请求后会先到缓存中查找，如果能够找到key，那么将直接把key对应的value返回给客户端，如果没有找到，则执行后面的操作，最后，sql执行完成后，执行结果会以key-value的形式存入缓存中。如果查询能够命中缓存，那将不会再进行后续的操作，此时效率还是挺高的。
但是，MySQL 8.0版本中直接将查询缓存整块功能干掉了，8.0开始就没有这个功能了。因为加入你对一个表做了更新操作，那么这个表上的所有查询缓存都将被清空。对于不经常更新的表，比如系统配置表，使用缓存还是可以的，但是查询缓存失效的场景在实际业务中还是非常频繁的，可能官方也认为该功能的实际应用场景不多，所以删除掉了。
分析器
如果没有命中缓存，就会往下走，这时就要开始真正执行语句了。首先就是进入分析器，分析器有词法分析和语法分析，主要是对sql语句进行解释，让MySql知道这条语句是要做什么。
词法分析： 一条sql是由多个字符串和空格组成的，MySql需要识别出里面的字符串分别是什么，代表什么，比如一条查询语句，提取关键字select，提取查询的表名、字段名，提取查询条件条件等等。做完这些后，就要进行语法分析。
语法分析： 语法分析主要是判断主要是来判断这条sql是否正确，是否符合MySql语法，比如select * from 写成selec * from ，MySQL就会抛出“You have an error in your SQL syntax”异常。
优化器
执行器工作完后，MySql已经知道这条sql是要做什么了，接下来就要执行了，但在开始执行之前，还要经过优化器的处理。
优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。然后选择一个它认为最优的执行方案去执行。
执行器
MySql通过分析器知道了一条sql想要做什么，通过优化器知道了应该怎么做，接下来就是真正执行这条语句了。不过在执行前会先验证该用户对这个表有没有相关权限，如果没有，返回错误，如果有，则调用引擎的读写接口，返回接口执行结果。

SQL是怎么执行的

可以把sql分为两类，一类是读（查询），一类是写（增、删、改）
读

select * from user a where a.name='pp' and a.password='123';

以上面语句为例，执行流程：

首先客户端使用TCP与服务端建立连接。
在8.0以前，会先查缓存，如果在缓存中有已该sql语句为key的数据，直接返回结果（value）。
如果没有，分析器开始对上述语句进行词法分析，提取出关键字select，提取出要查询的表user，要查询的表中的所有列，提取where后面的查询条件是name和password，然后进行语法分析，判断语句有没有写错，之后进入优化器
优化器选择它认为的最优方案，该语句可以有两方案

1.先查询用户表中姓名为“pp”的用户，然后查密码是123的用户。
2.先找出密码是123的用户，然后查询姓名是pp的用户。

优化器会选择上述其中一个，开始执行。

最后进行权限验证，执行器调用引擎接口，返回接口执行结果给客户端。

建立连接->查询缓存->分析器->优化器->权限验证->执行器->调用存储引擎接口

写

update user a set a.password = '321' where a.name = 'pp';

以上面语句为例，执行流程：

首先客户端使用TCP与服务端建立连接,删除表user的索引。
分析器解析sql并判断是否含有语句错误
优化器确定查询方案
执行器调用存储引擎接口查找name='pp’的行返回给server层。
执行器把password改为321，然后调用引擎 API 接口，写入这一行数据，InnoDB 引擎把数据保存在内存中，同时InnoDB 写redo日志，此时 redo日志进入 prepare （准备）状态，然后告诉server层（执行器），执行完成了，随时可以提交。
执行器收到通知后记录binlog，然后调用引擎接口，通知存储引擎
存储引擎提交 redo日志。
最后，更新完成。