新闻资讯  快讯  焦点  财经  政策  社会
互 联 网   电商  金融  数据  计算  技巧
生活百科  科技  职场  健康  法律  汽车
手机百科  知识  软件  修理  测评  微信
软件技术  应用  系统  图像  视频  经验
硬件技术  知识  技术  测评  选购  维修
网络技术  硬件  软件  设置  安全  技术
程序开发  语言  移动  数据  开源  百科
安全防护  资讯  黑客  木马  病毒  移动
站长技术  搜索  SEO  推广  媒体  移动
财经百科  股票  知识  理财  财务  金融
教育考试  育儿  小学  高考  考研  留学
您当前的位置:首页 > IT > 数据库 > MYSQL

MySQL语句是怎么实现的?需要进行合理的优化分析

时间:2019-06-11 10:10:17  来源:  作者:

想要深入的了解MySQL,首先要了解MySQL语句是怎么实现的,了解了MySQL里语句的执行过程可以更加快速的分析问题的原因,或者进行合理的优化。

MySQL语句是怎么实现的?需要进行合理的优化分析

 

MySQL的架构

MySQL的架构图如下所示,主要由以下几个部分组成:连接器,缓存,分析器,优化器,执行器和存储引擎。

MySQL语句是怎么实现的?需要进行合理的优化分析

 

MySQL可以分为server层和存储引擎层,server层包括连接器、分析器、优化器和执行器,主要负责SQL语法的解析,内置函数的实现,触发器,视图等。存储引擎层负责数据的存储和提取,存储引擎是插件式的,MySQL支持的存储引擎就有InnoDB、MyISAM、Memory等。目前,InnoDB是mysql默认的存储引擎。

连接器

连接器负责与客户端建立网络连接、校验用户名密码、校验用户权限、维持和管理连接等。

网络连接建立后,首先验证用户名和密码,用户名和密码验证通过以后连接器会到权限表里查询该用户的权限。之后,这个连接里的权限判断逻辑,都将依赖于此时读到的权限。这就意味着,一个用户成功连接后,再去修改该用户的权限,也不会影响到已经建立好的连接,只有重新建立连接权限才会生效。

MySQL的网络连接采用的是多线程模型,维护一个线程池,每当有一个新的连接请求时,就从空闲的线程池中选择一个线程进行处理。可以使用 show processlist 命令看到当前所建立的所有连接。

+------------+--------------+--------------------+------------------+---------+-------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------------+--------------+--------------------+------------------+---------+-------+-------+------------------+
| 1801071833 | user_name | 10.1.1.1:49788 | test_db | Sleep | 131 | | NULL | 
| 2309292411 | user_name | 10.1.1.1:57642 | test_db | Query | 0 | NULL | show processlist |
复制代码

ID 表示建立连接的线程 ID 。客户端如果一段时间没有动作, Command 一栏就会显示Sleep,表示该连接处于空闲状态。 多线程的模型必然存在连接数有限的问题,因此客户端如果太长时间没有动静,连接器就会自动断开,回收线程。

缓存

连接建立后,就可以执行查询语句。查询语句首先会查询缓存中是否该语句的缓存结果,因为MySQL查询语句的执行结果可能会已K-V的形式存储在缓存中,SQL语句做KEY,查询的结果做值。

但是MySQL自带的缓存不建议使用,因为MySQL的缓存失效的非常频繁,只要对一个表有更新,那么这个表上所有的缓存都会失效,因此缓存命中率很低。不如在业务层用Redis或者Memcached做缓存来的灵活高效。

MySQL语句是怎么实现的?需要进行合理的优化分析

 

分析器

如果缓存没有命中或者没用使用缓存,查询语句就会到达分析器,分析器就是一个编程语言的解析器,解析的是SQL语言。分析器的工作主要分为两个部分:

1 词法分析:词法分析时分析器会分析SQL语句中每个用空格或者逗号分割的字符串,把SELECT关键词提取出来,把语句里的标识为表名的字符串对应到MySQL的表,把每一个column对应到表里的字段。

2 语法分析:语法分析就是整个SQL语句是否满足语法要求,满足则能执行成功,不满足则报错。

优化器

优化器的功能一句话就能描述,却非常重要,决定了查询的性能。优化器是在表里面有多个索引的时候,决定使用哪个索引;或者在联表查询时决定用哪一张表关联哪一张表。

执行器

执行器的功能就是调用存储引擎的API存入数据或者取出数据。在调用存储引擎的API之前会先进行权限校验,校验该用户是否有对该表相应的操作权限。存储引擎如果索引没有命中,存储引擎就一条条扫表,直到查到指定的数据,然后返回给server层。如果命中了索引,存储引擎就在索引命中的数据中一条条扫描,直到查到指定的数据。如果索引类型为const类型,那么存储引擎会直接命中,然后返回。

查询语句如何实现

说完了MySQL的架构,我们用一个例子来总结一下一条查询语言是如何实现的

select * from t where id = 123 and name = 'tom'
复制代码

1 客户端与MySQL服务端建立网络连接,连接语句譬如:

mysql -h 127.0.0.1 -P 3306 -u 'name' -p'password!' database_name -A --default-character-set=utf8
复制代码

这条语句指定了MySQL服务器的地址为 127.0.0.1 ,也就是本机,端口号为3306,用户名为name,密码为password。指定库名为database_name,指定默认字符集为utf8。

2 完成连接后,如果开启了MySQL的缓存机制,这时候会先去查询缓存是否命中,如果缓存命中则直接返回缓存中的数据,如果缓存没有命中则继续向下执行。

3 分析器会分析每个词是否是有意义的,比如会解析到 select 是SQL的关键词, t 是表名, id 和 name 是表名中的字段.然后分析SQL的语法是否正常,该条语句可以正常执行。

4 优化器会分析在字段 id 和 name 上是否有索引,应该选择哪个索引。如果表 t 是以 id 为主键,那么分析器就会直接走主键索引了。

5 执行器开始执行前会先校验该用户是否有对该的读权限。通过权限校验后,执行器会调用存储引擎的API查询出这条数据,返回给客户端。

更新语句如何实现

一条更新语句的执行也要经历一条查询语句所要经历的几个阶段,连接器建立连接、分析器分析语法、优化器选择索引,执行器调用存储引擎的API,与查询语句相比,更新语句更为复杂,因为MySQL的InnoDB引擎要保证在数据库机器宕机以后数据不丢失。

同样以一个例子来总结查询语句是如何实现的

update t set name = 'tom' where id = 123
复制代码

1 客户端与MySQL服务端建立网络连接

2 分析器解析出这是一条更新语句

3 优化器选择主键索引,假设以 id 做该表的主键

4 执行器首先查询内存中是否有表 t 中 id 等于123的这一行数据,如果没有则通过存储引擎将这行数据取到内存中

5 执行器修改 name 字段为tom,得到一个新的行

6 存储引擎将新行的数据写入内存,并写redo log日志, 此时 redo log 处于 prepare 状态

7 执行器写bin log日志

8 存储引擎修改redo log日志为commit状态

MySQL语句是怎么实现的?需要进行合理的优化分析

 

以上步骤就是一个完整的更新语句执行过程,细心的读者会发现更新的数据只写入到内存,还没有持久化到磁盘,mysql异步定期将内存中的数据写入到磁盘,这一过程和操作系统的文件系统读写很像,文件系统中有一个page cache,写文件时先写cache然后用一个独立的进程将数据刷到磁盘。mysql使用了redo log日志,因此即使服务器宕机,数据也不会丢失,可以从redo log日志中恢复。

 

redo log日志与bin log日志

1 redo log日志是由server层来写,bin log日志由存储引擎来写的;

2 redo log 是物理日志,记录的是“在某个数据页上做了什么修改",bin log用于记录逻辑操作。在statement模式时,bin log记的就是SQL语句;

3 redo log日志循环写的,空间用完后,要先将数据刷到磁盘,然后清理空间。bin log日志是追加写入的;

4 redo log日志用于数据库崩溃后恢复数据,而bin log日志则用于主备同步,数据备份等;
 



Tags:MySQL   点击:()  评论:()
声明:本站部分内容来自互联网,如有任何版权侵犯或其他问题请与我们联系,我们将立即删除或处理。
▌相关评论
发表评论 共有条评论
用户名: 密码:
验证码: 匿名发表
▌相关推荐
1、MySQLdbMySQLdb又叫MySQL-python ,是 Python 连接 MySQL 最流行的一个驱动,很多框架都也是基于此库进行开发,遗憾的是它只支持 Python2.x,而且安装的时候有很多前置条件,因为...【详细内容】
2019-06-14 Python  点击:(1)  评论:(0)  加入收藏
在实际的项目开发中有时会有对数据库某字段截取部分的需求,这种场景有时直接通过数据库操作来实现比通过代码实现要更方便快捷些,mysql有很多字符串函数可以用来处理这些需求...【详细内容】
2019-06-14 mysql  点击:(1)  评论:(0)  加入收藏
概述 身份认证模块,即MySQL客户端通过指定用户名,密码,主机名来连接MySQL服务器,主要是认证给定的用户是否有权限连接MySQL服务器,而权限控制模块主要用于控制已经通过身份认证的...【详细内容】
2019-06-12 MySQL  点击:(6)  评论:(0)  加入收藏
想要深入的了解MySQL,首先要了解MySQL语句是怎么实现的,了解了MySQL里语句的执行过程可以更加快速的分析问题的原因,或者进行合理的优化。 MySQL的架构MySQL的架构图如下所示,主...【详细内容】
2019-06-11 MySQL  点击:(3)  评论:(0)  加入收藏
概述在日常工作中,我们会有时会开慢查询去记录一些执行时间比较久的SQL语句,找出这些SQL语句并不意味着完事了,我们还需要用到explain这个命令来查看一个这些SQL语句的执行计划...【详细内容】
2019-06-11 MySQL  点击:(6)  评论:(0)  加入收藏
在MySQL的SQL查询性能分析当中,主要使用explain命令对查询SQL语句的执行情况进行分析,包含查询所涉及的表,查询索引使用情况,排序情况等,用法的使用很简单,示例如下:mysql> EXPLAIN...【详细内容】
2019-06-06 MySQL  点击:(9)  评论:(0)  加入收藏
前言MySQL 数据库最常见的两个瓶颈是CPU和I/O的瓶颈。CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候,磁盘I/O瓶颈发生在装入数据远大于内存容量的时候。MySQ...【详细内容】
2019-05-24 MySQL  点击:(19)  评论:(0)  加入收藏
概述操作系统及MySQL数据库的实时性能状态数据尤为重要,特别是在有性能抖动的时候,这些实时的性能数据可以快速帮助你定位系统或MySQL数据库的性能瓶颈,那么有哪些重要的实时性...【详细内容】
2019-05-16 MySQL,doDBA  点击:(13)  评论:(0)  加入收藏
在程序设计当中,我们很多场景下都会用 group by 关键字。比如在分页读取数据时,为了避免重复扫描记录,这就是必须要使用 group by 了。比如我们使用如下 DDL 创建表:CREATE TAB...【详细内容】
2019-05-15 MySQL  点击:(19)  评论:(0)  加入收藏
方法一cmd 到mysql bin目录下用如下命令:mysqldump --opt -h192.168.0.156 -uusername -ppassword --skip-lock-tables databasename>database.sql把ip改成localhost就可以的...【详细内容】
2019-05-15 mysql,数据库  点击:(15)  评论:(0)  加入收藏
概述Mysql binlog日志有三种格式,分别为Statement,MiXED,以及ROW!这三种格式之间有什么区别呢?下面先介绍下各自的优缺点。ROW日志中会记录成每一行数据被修改的形式,然后在slav...【详细内容】
2019-05-14 mysql,数据库  点击:(15)  评论:(0)  加入收藏
MySQL中没有Rank排名函数当我们需要查询排名时,只能使用MySQL数据库中的基本查询语句来查询普通排名。尽管如此,可不要小瞧基础而简单的查询语句,我们可以利用其来达到Rank函数...【详细内容】
2019-05-14 MySQL  点击:(14)  评论:(0)  加入收藏
查看表是否被锁:直接在mysql命令行执行:show engine innodb statusG。查看造成死锁的sql语句,分析索引情况,然后优化sql.然后show processlist,查看造成死锁占用时间长的sql语...【详细内容】
2019-05-10   点击:(23)  评论:(0)  加入收藏
分片(类似分库)分片是把数据库横向扩展(Scale Out)到多个物理节点上的一种有效的方式,其主要目的是为突破单节点数据库服务器的 I/O 能力限制,解决数据库扩展性问题。Shard这个词...【详细内容】
2019-05-10 mysql  点击:(15)  评论:(0)  加入收藏
mysql高并发的解决方法有:优化SQL语句,优化数据库字段,加缓存,分区表,读写分离以及垂直拆分,解耦模块,水平切分等。高并发大多的瓶颈在后台,在存储mysql的正常的优化方案如下:(1)代码中...【详细内容】
2019-05-10 mysql  点击:(19)  评论:(0)  加入收藏
数据库的索引就像一本书的目录一样,它可以快速定位你所需要的信息。下面来详细说一下MySQL的索引结构。常见索引类型Hash 索引Hash索引的底层实现是由Hash表来实现的,非常适合...【详细内容】
2019-05-07 MySQL  点击:(10)  评论:(0)  加入收藏
在我们开发的过程中,使用全局锁和表锁的场景比较少,接触的也相对少一点,下面主要介绍一下。全局锁FTWRL全局锁就是对整个数据库实例加锁,MySQL 提供了 flush tables with read l...【详细内容】
2019-05-07 MySQL  点击:(9)  评论:(0)  加入收藏
概述有朋友问了Oracle跟mysql的一些区别的地方,所以这里做个简单总结,大的架构就不讲了,这里主要从主键、索引、分页、组函数和单引号来说明下。 1、主键Mysql一般使用自动增长...【详细内容】
2019-05-07 Oracle,Mysql  点击:(14)  评论:(0)  加入收藏
那么什么是编码?什么是 UTF-8? MySQL 简史 为什么这件事情会让人如此抓狂 总结最近我遇到了一个 bug,我试着通过 Rails 在以“utf8”编码的 MariaDB 中保存一个 UTF-8 字符串,...【详细内容】
2019-05-05 MySQL,UTF-8  点击:(11)  评论:(0)  加入收藏
谈到事务最先想到的就是 ACID 属性(Atomicity 原子性、Consistency 一致性、Isolation 隔离性、Durability持久性),今天主要介绍一下MySQL的隔离属性。事务隔离级别SQL标准里定...【详细内容】
2019-05-05 MySQL  点击:(11)  评论:(0)  加入收藏
推荐资讯
相关文章
栏目更新
栏目热门
'); })();