mysql中的查询语句执行过程

Scroll Down

mysql中的查询语句执行过程

select * from table where id = 1;

流程图

mysql查询流程图

整体流程如上图,首先用户通过客户端访问数据库server服务的时候,先校验账号密码是否正确,密码正确后,连接器会到数据库中查询具体用户的权限,并且后面该次连接的请求权限控制都在此次查询结果中,如果有修改新的权限,必须要重新建立连接才能重新读取.

客户端建立长连接和短连接

客户端向服务端发起请求,其中有长连接和短连接的区别,使用show processlist;命令可以看到现有数据库中连接的活动情况

showprocesslist

  1. id列,用户登录mysql时,系统分配的"connection_id",可以使用函数connection_id()查看
  2. user列,显示当前用户。如果不是root,这个命令就只显示用户权限范围的sql语句
  3. host列,显示这个语句是从哪个ip的哪个端口上发的,可以用来跟踪出现问题语句的用户
  4. db列,显示这个进程目前连接的是哪个数据库
  5. command列,显示当前连接的执行的命令,一般取值为休眠(sleep),查询(query),连接(connect)等,commond为sleep的情况下为空闲的连接
  6. time列,显示这个状态持续的时间,单位是秒
  7. state列,显示使用当前连接的sql语句的状态,很重要的列。state描述的是语句执行中的某一个状态。一个sql语句,以查询为例,可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成
  8. info列,显示这个sql语句

一个连接长期有操作或发起心跳包给mysql,就被认为是长连接,而查几次就断开,重新查询又重新连接的则称为短连接

如果一个连接长期没有动作,就会被断开请求,mysql默认时间是8小时(wait_timeout参数控制),如果断开连接后,还想进行请求则会报错,Lost connection to MySQL server during query,需要重新发起连接后才可以进行查询,因为客户端和服务端建立连接的过程比较复杂,较为耗费资源,一般使用长连接进行查询,但是如果mysql中的长连接过多,也会导致mysql数据库的压力变大,内存压力变大,压力太大会导致mysql内存溢出,即被系统杀掉进程,导致mysql挂掉的症状产生.

如果想使用长连接又不想导致对应的后果的话,可以采取在一个耗费内存资源大的语句执行过后,执行mysql_reset_connection 来重新初始化连接资源,这样既不用重新连接,也会将连接重置为初始状态,不会使内存迟迟不能释放

查询缓存

mysql在连接器中会首先判断缓存中是否有该sql的缓存,在服务端缓存起来是key-value的形势,匹配到相应语句后就会直接返回,如果没有匹配到才会进行分析器,优化器,执行器的动作去查询引擎中查询数据.但是查询缓存的功能比较笨重,每次更新对应表的数据,会把该张表关联的缓存全部清空,其实也变相影响了数据库的效率,除非是一些长期不用的参数表,确实可以使用缓存功能,因为该功能的不便利性,mysql8.0已经去除该查询缓存功能,不过现在还是有很多使用5.7的mysql,如果想去除该功能,可以修改数据库对应my.cnf文件,加入

query_cache_type = 2 (显示指定 sql_cache,例如:select sql_cache * from table_name; ) 或者 query_cache_type = 0(关闭)

query_cache_type = 1 (开启)

分析器

mysql真正开始执行sql语句的时候,就是分析器首先进行语法解析,判断sql语句语法是否正确,这里会对你的别名等进行处理,如果语法错误,会报出如下错误

select * from

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

优化器

分析器之后,就证明语法已经没有问题了,mysql已经知道了你想要查询什么数据,下一步就是对这条语句进行优化,会对多连接的情况下优化表的连接顺序,表里面有多个索引的时候才去更优方案,这里才是我们平常处理sql效率最需要关注的地方

执行器

优化器之后,相当于优化后的sql语句也确定下来,下一步就是由执行器来向查询引擎调用读内容的接口了,执行语句前,首先会对用户是否有改表的查询权限进行判断,如果没有权限则直接返回报错

java.sql.SQLException: Access denied for user '***'@'localhost' (using pass)

如果已有权限,则可继续查询数据库里的数据,判断每一行的数据id是否等于1.有索引的话,效率会大大提升,没有索引情况下是全表扫描,每一行都要判断是否等于1,如果有了索引,只需要将id = 1的数据行查询出来加到结果集返回给客户端即可.