时间:2021-07-01 10:21:17 帮助过:12人阅读
每产生一个连接或者一个会话,在服务端就会创建一个线程来处理。反过来,如果要杀死会话,就是 Kill 线程。可以使用 SHOW PROCESSLIST; (root 用户)查看 SQL 的执行状态。一些常见的状态:
- show global status <span style="color: #808080;">like</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Thread%</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">;
- Threads_cached:缓存中的线程连接数。
- Threads_connected:当前打开的连接数。
- Threads_created:为处理连接创建的线程数。
- Threads_running:非睡眠状态的连接数,通常指并发连接数。</span>
show 的参数说明: 1、级别:会话 session 级别(默认);全局 global 级别 2、动态修改:set,重启后失效;永久生效,修改配置文件/etc/my.cnf
- show variables <span style="color: #808080;">like</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">max_connections</span><span style="color: #ff0000;">‘</span>;
- <span style="color: #0000ff;">set</span> global max_connections <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">1000</span>;
我们的编程语言的连接模块都是用 TCP 协议连接到 MySQL 服务器的,比如mysql-connector-java-x.x.xx.jar。
- mysql <span style="color: #808080;">-</span>h192.<span style="color: #800000; font-weight: bold;">168.8</span>.<span style="color: #800000; font-weight: bold;">211</span> <span style="color: #808080;">-</span>uroot <span style="color: #808080;">-</span>p123456
另一方面,对于服务端来说,也是一次性发送所有的数据,不能因为你已经取到了想要的数据就中断操作,这个时候会对网络和内存产生大量消耗。所以,我们一定要在程序里面避免不带 limit 的这种操作,比如一次把所有满足条件的数据全部查出来,一定要先 count 一下。如果数据量的话,可以分批查询。
一直很好奇为什么我的一条 SQL 语句能够被识别呢?假如我随便执行一个字符串 penyuyan,服务器报了一个 1064 的错,它是怎么知道我输入的内容是错误的?这个就是 MySQL 的 Parser 解析器和 Preprocessor 预处理模块。这一步主要做的事情是对语句基于 SQL 语法进行词法和语法分析和语义的解析。
它会打碎成 8 个符号,每个符号是什么类型,从哪里开始到哪里结束。
- <span style="color: #0000ff;">select</span> name <span style="color: #0000ff;">from</span> <span style="color: #ff00ff;">user</span> <span style="color: #0000ff;">where</span> id <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">1</span>;
解析器可以分析语法,但是它怎么知道数据库里面有什么表,表里面有什么字段呢?实际上还是在解析的时候报错,解析 SQL 的环节里面有个预处理器。它会检查生 成的解析树,解决解析器无法解析的语义。比如,它会检查表和列名是否存在,检查名字和别名,保证没有歧义。预处理之后得到一个新的解析树。
- <span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> penyuyan;
- show status <span style="color: #808080;">like</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Last_query_cost</span><span style="color: #ff0000;">‘</span>;
- EXPLAIN <span style="color: #0000ff;">select</span> name <span style="color: #0000ff;">from</span> <span style="color: #ff00ff;">user</span> <span style="color: #0000ff;">where</span> id<span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">1</span>;
- show variables <span style="color: #808080;">like</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">datadir</span><span style="color: #ff0000;">‘</span>;
默认情况下,每个数据库有一个自己文件夹,任何一个存储引擎都有一个 frm 文件,这个是表结构定义文件。
不同的存储引擎存放数据的方式不一样,产生的文件也不一样,innodb 是 1 个,memory 没有,myisam 是两个。 主要介绍一下InnoDB:
mysql 5.7 中的默认存储引擎。InnoDB 是一个事务安全(与 ACID 兼容)的 MySQL存储引擎,它具有提交、回滚和崩溃恢复功能来保护用户数据。InnoDB 行级锁和 Oracle 风格的一致非锁读提高了多用户并发性和性能。InnoDB 将用户数据存储在聚集索引中,以减少基于主键的常见查询的 I/O。为了保持数据 完整性,InnoDB 还支持外键引用完整性约束。1.6 执行引擎,返回结果
执行引擎利用存储引擎提供的相应的 API 来完成操作。为什么我们修改了表的存储引擎,操作方式不需要做任何改变?因为不同功能的存储引擎实现的 API 是相同的。
最后把数据返回给客户端,即使没有结果也要返回。基于上面分析的流程,我们一起来梳理一下 MySQL 的内部模块。
1、 Connector:用来支持各种语言和 SQL 的交互,比如 PHP,Python,Java 的JDBC; 2、 Management Serveices & Utilities:系统管理和控制工具,包括备份恢复、MySQL 复制、集群等等; 3、 Connection Pool:连接池,管理需要缓冲的资源,包括用户密码权限线程等等; 4、 SQL Interface:用来接收用户的 SQL 命令,返回用户需要的查询结果; 5、 Parser:用来解析 SQL 语句; 6、 Optimizer:查询优化器; 7、 Cache and Buffer:查询缓存,除了行记录的缓存之外,还有表缓存,Key 缓存,权限缓存等等; 8、 Pluggable Storage Engines:插件式存储引擎,它提供 API 给服务层使用,跟具体的文件打交道。
我们的客户端要连接到 MySQL 服务器 3306 端口,必须要跟服务端建立连接,那么管理所有的连接,验证客户端的身份和权限,这些功能就在连接层完成。
下一次读取相同的页,先判断是不是在缓冲池里面,如果是,就直接读取,不用再次访问磁盘。
修改数据的时候,先修改缓冲池里面的页。内存的数据页和磁盘数据不一致的时候,我们把它叫做脏页。InnoDB 里面有专门的后台线程把 Buffer Pool 的数据写入到磁盘, 每隔一段时间就一次性地把多个修改写入磁盘,这个动作就叫做刷脏。 Buffer Pool 是 InnoDB 里面非常重要的一个结构,它的内部又分成几块区域。这里我们趁机到官网来认识一下 InnoDB 的内存结构和磁盘结构。Buffer Pool 主要分为 3 个部分: Buffer Pool、Change Buffer、Adaptive HashIndex,另外还有一个(redo)log buffer。
1、Buffer Pool Buffer Pool 缓存的是页面信息,包括数据页、索引页。 2、Change Buffer 写缓冲 如果这个数据页不是唯一索引,不存在数据重复的情况,也就不需要从磁盘加载索引页判断数据是不是重复(唯一性检查)。这种情况下可以先把修改记录在内存的缓冲 池中,从而提升更新语句(Insert、Delete、Update)的执行速度。这一块区域就是 Change Buffer。5.5 之前叫 Insert Buffer 插入缓冲,现在也能支持 delete 和 update。 最后把 Change Buffer 记录到数据页的操作叫做 merge。什么时候发生 merge?有几种情况:在访问这个数据页的时候,或者通过后台线程、或者数据库 shut down、redo log 写满时触发。如果数据库大部分索引都是非唯一索引,并且业务是写多读少,不会在写数据后立刻读取,就可以使用 Change Buffer(写缓冲)。写多读少的业务,调大这个值:3、Adaptive Hash Index 哈希的索引的内存块。 4、(redo)Log Buffer 思考一个问题:如果 Buffer Pool 里面的脏页还没有刷入磁盘时,数据库宕机或者重启,这些数据丢失。如果写操作写到一半,甚至可能会破坏数据文件导致数据库不可用。为了避免这个问题,InnoDB 把所有对页面的修改操作专门写入一个日志文件,并且在数据库启动时从这个文件进行恢复操作(实现 crash-safe)——用它来实现事务的持 久性。
- SHOW VARIABLES <span style="color: #808080;">LIKE</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">innodb_change_buffer_max_size</span><span style="color: #ff0000;">‘</span>;
如果存储引擎正在写入页的数据到磁盘时发生了宕机,可能出现页只写了一部分的情况,比如只写了 4K,就宕机了,这种情况叫做部分写失效(partial page write),可
能会导致数据丢失。 我们不是有 redo log 吗?但是有个问题,如果这个页本身已经损坏了,用它来做崩溃恢复是没有意义的。所以在对于应用 redo log 之前,需要一个页的副本。如果出现了 写入失效,就用页的副本来还原这个页,然后再应用 redo log。这个页的副本就是 doublewrite,InnoDB 的双写技术。通过它实现了数据页的可靠性。跟 redo log 一样,double write 由两部分组成,一部分是内存的 double write,一个部分是磁盘上的 double write。因为 double write 是顺序写入的,不会带来很大的开销。在默认情况下,所有的表共享一个系统表空间,这个文件会越来越大,而且它的空间不会收缩。开启后,则每张表会开辟一个表空间,这个文件就是数据目录下的 ibd 文件(例如/var/lib/mysql/gupao/user_innodb.ibd),存放表的索引和数据。但是其他类的数据,如回滚(undo)信息,插入缓冲索引页、系统事务信息,二次写缓冲(Double write buffer)等还是存放在原来的共享表空间内。
- SHOW VARIABLES <span style="color: #808080;">LIKE</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">innodb_file_per_table</span><span style="color: #ff0000;">‘</span>;
在创建表的时候可以指定表空间,用 ALTER 修改表空间可以转移表空间。
- <span style="color: #0000ff;">create</span> tablespace ts2673 <span style="color: #0000ff;">add</span> datafile <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">/var/lib/mysql/ts2673.ibd</span><span style="color: #ff0000;">‘</span> file_block_size<span style="color: #808080;">=</span>16K engine<span style="color: #808080;">=</span>innodb;
不同表空间的数据是可以移动的。删除表空间需要先删除里面的所有表:
- <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span> t2673(id <span style="color: #0000ff;">integer</span>) tablespace ts2673;
- <span style="color: #0000ff;">drop</span> <span style="color: #0000ff;">table</span> t2673; <br><br><span style="color: #0000ff;">drop</span> tablespace ts2673;
1、事务开始,从内存或磁盘取到这条数据,返回给 Server 的执行器; 2、执行器修改这一行数据的值为 penyuyan; 3、记录 name=qingshan 到 undo log; 4、记录 name=penyuyan 到 redo log; 5、调用存储引擎接口,在内存(Buffer Pool)中修改 name=penyuyan; 6、事务提交。 内存和磁盘之间,工作着很多后台线程。
- <span style="color: #0000ff;">update</span> <span style="color: #ff00ff;">user</span> <span style="color: #0000ff;">set</span> name <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">penyuyan</span><span style="color: #ff0000;">‘</span> <span style="color: #0000ff;">where</span> id<span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">1</span>;
例如一条语句:update teacher set name=‘盆鱼宴‘ where id=1;
1、先查询到这条数据,如果有缓存,也会用到缓存。 2、把 name 改成盆鱼宴,然后调用引擎的 API 接口,写入这一行数据到内存,同时记录 redo log。这时 redo log 进入 prepare 状态,然后告诉执行器,执行完成了,可 以随时提交。 3、执行器收到通知后记录 binlog,然后调用存储引擎接口,设置 redo log为 commit状态。 4、更新完成。MySQL架构与执行流程
标签:操作系统 cpu excel 通用 rop 获取 因此 中断 mysql数据库