安徽省六安市建设局网站,网站建设适合的企业,wordpress原创培训主题,平面设计师作品网站MySQL的发展历史和版本分支#xff1a;
时间里程碑1996 年MySQL1.0 发布。它的历史可以追溯到 1979 年#xff0c;作者 Monty 用 BASIC 设计的一个报表工具。1996 年 10 月3.11.1 发布。MySQL 没有 2.x 版本。2000 年ISAM 升级成 MyISAM 引擎。MySQL 开源。2003 年MySQL4.0 …MySQL的发展历史和版本分支
时间里程碑1996 年MySQL1.0 发布。它的历史可以追溯到 1979 年作者 Monty 用 BASIC 设计的一个报表工具。1996 年 10 月3.11.1 发布。MySQL 没有 2.x 版本。2000 年ISAM 升级成 MyISAM 引擎。MySQL 开源。2003 年MySQL4.0 发布集成 InnoDB 存储引擎2005 年MySQL5.0 版本发布提供了视图、存储过程等功能。2008 年MySQLAB 公司被 Sun 公司收购进入 SunMySQL 时代。2009 年Oracle 收购 Sun 公司进入 OracleMySQL 时代。2010 年MySQL5.5 发布InnoDB 成为默认的存储引擎。2016 年MySQL 发布 8.0.0 版本。为什么没有 6、75.6 可以当成 6.x5.7 可以当 成 7.x。
因为MySQL是开源的也有收费版本所以在MySQL稳定版本的基础上也发展出来了很多的分支就像Linux一样有Ubuntu、RedHat、CentOS、 Fedora 、Debian等等。大家最熟悉的应该是MariaDB因为CentOS7里面自带了一个MariaDB。它是怎么来的呢Oracle收购MySQL之后MySQL创始人之一Monty担心MySQL数据库的未来开发缓慢封闭可能会被闭源就创建了一个分支MariaDB默认使用全新的Maria存储引擎它是原MyISAM存储引擎的升级版本。
其他流行分支
Percona Server 是MySQL重要的分支之一它基于InnoDB存储引擎的基础上提升了性能和易管理性最后形成了增强版的XtraDB引擎可以用来更好地发挥服务器硬件上的性能。国内也有一些MySQL的分支或者自研的存储引擎比如网易的InnoSQL极数云舟的ArkDB。
我们操作数据库有各种各样的方式比如Linux 系统中的命令行比如数据库工具Navicat比如程序例如Java语言的JDBC API或者ORM框架。但大家有没有思考过当我们的工具或者程序连接到数据库之后实际上发生了什么事情它的内部是怎么工作的 下面以数据查询为例来看下MySQL的工作流程是什么样的 我们的程序或者工具要操作数据库第一步要做什么事情跟数据库建立连接。
1.与数据库建立连接
首先MySQL必须要运行一个服务监听默认的3306端口。在我们开发系统跟第三方对接的时候必须要弄清楚的有两件事。
第一个就是通信协议比如我们是用TCP/UDP还是HTTP还是WebService第二个是消息格式比如我们用XML格式还是JSON格式还是定长格式报文头长度多少包含什么内容每个字段的详细含义
1.1 通信类型和连接方式
MySQL是支持多种通信协议的可以使用同步/异步的方式支持长连接/短连接。这里我们拆分来看。
通信类型同步或者异步 同步通信的特点 同步通信依赖于被调用方受限于被调用方的性能。也就是说应用操作数据库线程会阻塞等待数据库的返回一般只能做到一对一很难做到一对多的通信 异步跟同步相反 异步可以避免应用阻塞等待但是不能节省SQL执行的时间如果异步存在并发每一个SQL的执行都要单独建立一个连接避免数据混乱。但是这样会给服务端带来巨大的压力一个连接就会创建一个线程线程间切换会占用大量CPU资源。另外异步通信还带来了编码的复杂度所以一般不建议使用。如果要异步必须使用连接池排队从连接池获取连接而不是创建新连接一般来说我们连接数据库都是同步连接。
连接方式长连接或者短连接
MySQL既支持短连接也支持长连接。短连接就是操作完毕以后马上close 掉。长连接可以保持打开减少服务端创建和释放连接的消耗后面的程序访问的时候还可以使用这个连接。一般我们会在连接池中使用长连接。
保持长连接会消耗内存。长时间不活动的连接MySQL服务器会断开。那这个超时时间怎么查看呢
show global variables like wait_timeout; -- 非交互式超时时间如 JDBC 程序
show global variables like interactive_timeout; -- 交互式超时时间如数据库工具执行结果如下图。默认都是28800秒8小时 我们怎么查看MySQL当前有多少个连接可以用show status命令
show global status like Thread%;Threads_cached缓存中的线程连接数Threads_connected当前打开的连接数Threads_created为处理连接创建的线程数Threads_running非睡眠状态的连接数通常指并发连接数有了连接数怎么知道当前连接的状态可以使用show processlist命令root用户查看SQL的执行状态。
SHOW PROCESSLIST; 从上面的Threads_connected可以看到当前有4个连接所以这里的显示了4个连接状态。那Command这一列是什么意思呢一些常见的状态
状态含义Sleep线程正在等待客户端以向它发送一个新语句Query线程正在执行查询或往客户端发送数据Locked该查询被其它查询锁定Copying to tmp tableondisk临时结果集合大于 tmp_table_size。线程把临时表从存储器内部格式改 变为磁盘模式以节约存储器Sendingdata线程正在为 SELECT 语句处理行同时正在向客户端发送数据Sortingforgroup线程正在进行分类以满足 GROUPBY 要求Sortingfororder线程正在进行分类以满足 ORDERBY 要求还有个问题MySQL服务允许的最大连接数是多少呢
show variables like max_connections;在5.7版本中默认是151个最大可以设置成163842^14。
set global max_connections1000;1.2 MySQL支持哪些通信协议
第一种是Unix Socket。比如我们在Linux服务器上如果没有指定-h参数它就用socket方式登录。如下图省略 了-S /var/lib/mysql/mysql.sock 它不用通过网络协议也可以连接到MySQL的服务器它需要用到服务器上的一个物理文件/var/lib/mysql/mysql.sock
select socket;如果有参数-h指定主机就会用第二种方式TCP/IP协议。
mysql -h192.168.8.211 -uroot -p123456我们的编程语言的连接模块都是用 TCP 协议连接到 MySQL 服务器的比如 mysql-connector-java-x.x.xx.jar。 1.3 MySQL采用什么通信方式
通信方式分为以下三种 单工在两台计算机通信的时候数据的传输是单向的。比如遥控器半双工在两台计算机之间数据传输是双向的你可以给我发送我也可以给你发送但是在这个通讯连接里面同一时间只能有一台服务器在发送数据也就是你要给我发的话也必须等我发给你完了之后才能给我发。比如对讲机全双工数据的传输是双向的并且可以同时传输。比如打电话
那MySQL应该采用哪种通信方式呢半双工的通信方式因为客户端与服务端肯定是双向通信的而且要么是客户端向服务端发送数据要么是服务端向客户端发送数据这两个动作不能同时发生。
客户端发送SQL语句给服务端的时候在一次连接里面数据是不能分成小块发送的不管你的SQL语句有多大都是一次性发送。比如我们用MyBatis动态SQL生成了一个批量插入的语句 插入10万条数据 values后面跟了一长串的内容或者where条件in里面的值太多会出现问题。这个时候我们必须要调整MySQL服务器配置 max_allowed_packet 参数的值默认是4M把它调大否则就会报错。 另一方面对于服务端来说也是一次性发送所有的数据不能因为你已经取到了想要的数据就中断操作这个时候会对网络和内存产生大量消耗。所以我们一定要在程序里面避免不带limit 的这种操作比如一次把所有满足条件的数据全部查出来一定要先count一下。如果数据量大的话可以分批查询。
执行一条查询语句客户端跟服务端建立连接之后呢下一步要做什么
2.查询缓存
MySQL内部自带了一个缓存模块。缓存的作用我们应该很清楚了把数据以KV的形式放到内存里面可以加快数据的读取速度也可以减少服务器处理的时间。但是MySQL的缓存我们好像比较陌生从来没有去配置过也不知道它什么时候生效假如 t_user 表有500万行数据没有索引。我们在没有索引的字段上执行同样的查询大家觉得第二次会快吗 可以看到MySQL缓存是默认关闭的。默认关闭的意思就是不推荐使用为什么MySQL不推荐使用它自带的缓存呢
主要是因为MySQL自带的缓存的应用场景有限需要满足以下两个要求
它要求SQL语句必须一模一样中间多一个空格字母大小写不同都被认为是不同的的SQL。表里面任何一条数据发生变化的时候这张表所有缓存都会失效所以对于有大量数据更新的应用也不适合。
所以缓存这一块我们还是交给ORM框架比如MyBatis默认开启了一级缓存或者独立的缓存服务比如Redis来处理更合适。在MySQL 8.0中查询缓存已经被移除了。
我们没有使用缓存的话就会跳过缓存的模块下一步我们要做什么呢
3. 语法解析和预处理
这里我会有一个疑问为什么我的一条SQL语句能够被识别呢假如我随便执行一个字符串penyuyan服务器报了一个1064的错 它是怎么知道我输入的内容是错误的这个就是MySQL的Parser解析器和Preprocessor预处理模块。这一步主要做的事情是对语句基于SQL语法进行词法和语法分析和语义的解析。
3.1 词法分析
词法分析就是把一个完整的SQL语句打碎成一个个的单词。比如一个简单的SQL语句
select username from t_user where id 1;它会打碎成8个符号每个符号是什么类型从哪里开始到哪里结束。
3.2 语法分析
第二步就是语法分析语法分析会对SQL做一些语法检查比如单引号有没有闭合然后根据MySQL定义的语法规则根据SQL语句生成一个数据结构。这个数据结构我们把它叫做解析树select_lex。 任何数据库的中间件比如 MycatSharding-JDBC用到了Druid Parser都必须要有词法和语法分析功能在市面上也有很多的开源的词法解析的工具比如LEXYacc。
3.3 预处理器
问题如果我写了一个词法和语法都正确的SQL但是表名或者字段不存在会在哪里报错是在数据库的执行层还是解析器比如
select * from penyuyan;解析器可以分析语法但是它怎么知道数据库里面有什么表表里面有什么字段呢实际上还是在解析的时候报错解析SQL的环节里面有个预处理器。它会检查生成的解析树解决解析器无法解析的语义。比如它会检查表和列名是否存在检查名字和别名保证没有歧义。预处理之后得到一个新的解析树。
4.查询优化得到执行计划
得到解析树之后是不是执行SQL语句了呢这里我们有一个问题一条SQL语句是不是只有一种执行方式或者说数据库最终执行的SQL是不是就是我们发送的SQL
这个答案是否定的。一条SQL语句是可以有很多种执行方式的最终返回相同的结果他们是等价的。但是如果有这么多种执行方式这些执行方式怎么得到的最终选择哪一种去执行根据什么判断标准去选择
这个就是MySQL的查询优化器的模块Query Optimizer。
4.1 什么是优化器
查询优化器的目的就是根据解析树生成不同的执行计划ExecutionPlan然后选择一种最优的执行计划。MySQL里面使用的是基于开销cost的优化器哪种执行计划开销最小就用哪种。可以使用这个命令查看查询的开销
show status like Last_query_cost;mysql官网关于这里的参考想了解更多的同学可以看看
4.2 优化器可以做什么
MySQL的优化器能处理哪些优化类型呢举两个简单的例子
当我们对多张表进行关联查询的时候以哪个表的数据作为基准表有多个索引可以使用的时候选择哪个索引
实际上对于每一种数据库来说优化器的模块都是必不可少的他们通过复杂的算法实现尽可能优化查询效率的目标。如果对于优化器的细节感兴趣可以看看《数据库查询优化器的艺术-原理解析与SQL性能优化》。 但是优化器也不是万能的并不是再垃圾的SQL语句都能自动优化也不是每次都能选择到最优的执行计划大家在编写SQL语句的时候还是要注意。
如果我们想知道优化器是怎么工作的它生成了几种执行计划每种执行计划的cost是多少应该怎么做
4.3 优化器得到执行计划的过程
首先我们要启用优化器的追踪默认是关闭的
SHOW VARIABLES LIKE optimizer_trace;
set optimizer_trace enabledon;注意开启这开关是会消耗性能的因为它要把优化分析的结果写到表里面所以不要轻易开启或者查看完之后关闭它改成off。接着我们执行一个SQL语句优化器会生成执行计划下面是一个两表联查
SELECT d.username,i.phone from user_info i,user_detail d WHERE i.idd.user_id;这个时候优化器分析的过程已经记录到系统表里面了我们可以查询
select * from information_schema.optimizer_trace\G;如果是直接在Navicat中执行那么得到结果只是短短一行数据并没有完整的执行计划 所以此处应该是在命令行中执行 得到的优化器分析的过程是一个JSON类型的数据主要分成三部分准备阶段、优化阶段和执行阶段。 那具体的优化计划在哪呢在优化阶段“join_optimization”中的 considered_execution_plans 最后分析完记得关掉它
set optimizer_traceenabledoff;
SHOWVARIABLESLIKEoptimizer_trace;优化完之后得到一个什么东西呢
4.4 如何查看最终执行计划
优化器最终会把解析树变成一个查询执行计划查询执行计划是一个数据结构。
当然这个执行计划是不是一定是最优的执行计划呢不一定因为MySQL也有可能覆盖不到所有的执行计划。我们怎么查看MySQL的执行计划呢比如多张表关联查询先查询哪张表在执行查询的时候可能用到哪些索引实际上用到了什么索引
MySQL提供了一个执行计划的工具。我们在SQL语句前面加上EXPLAIN就可以看到执行计划的信息。
5.存储引擎存储数据
得到执行计划以后SQL语句是不是终于可以执行了问题又来了
从逻辑的角度来说我们的数据是放在哪里的或者说放在一个什么结构里面执行计划在哪里执行是谁去执行
5.1 存储引擎基本介绍
我们先回答第一个问题在关系型数据库里面数据是放在什么结构里面的放在表Table里面的我们可以把这个表理解成Excel电子表格的形式。所以我们的表在存储数据的同时还要组织数据的存储结构这个存储结构就是由我们的存储引擎决定的所以我们也可以把存储引擎叫做表类型。
在MySQL里面支持多种存储引擎他们是可以替换的所以叫做插件式的存储引擎。为什么要搞这么多存储引擎呢一种还不够用吗这个问题先留着。
5.2 查看存储引擎
比如我们数据库里面已经存在的表我们怎么查看它们的存储引擎呢
show table status from forum; --forum是指定数据库名另外还通过DDL建表语句来查看。
在MySQL里面我们创建的每一张表都可以指定它的存储引擎而不是一个数据库只能使用一个存储引擎。存储引擎的使用是以表为单位的。而且创建表之后还可以修改存储引擎。
我们说一张表使用的存储引擎决定我们存储数据的结构那在服务器上它们是怎么存储的呢我们先要找到数据库存放数据的路径
show variables like datadir;默认情况下每个数据库有一个自己文件夹以forum数据库为例。 任何一个存储引擎都有一个frm文件这个是表结构定义文件。不同的存储引擎存放数据的方式不一样产生的文件也不一样innodb 是 1 个memory没有myisam是两个。
这些存储引擎的差别在哪呢
5.3 存储引擎比较
MyISAM 和InnoDB 是我们用得最多的两个存储引擎在 MySQL 5.5 版本之前默认的存储引擎是MyISAM它是MySQL自带的。我们创建表的时候不指定存储引擎它就会使用MyISAM作为存储引擎。MyISAM的前身是ISAMIndexedSequentialAccessMethod利用索引顺序存取数据的方法。
5.5版本之后默认的存储引擎改成了InnoDB它是第三方公司为MySQL开发的。为什么要改呢最主要的原因还是InnoDB 支持事务支持行级别的锁对于业务一致性要求高的场景来说更适合。 这个里面又有Oracle和MySQL公司的一段恩怨情仇。 InnoDB本来是InnobaseOy公司开发的 它和MySQLAB公司合作开源了InnoDB的代码。但是没想到MySQL的竞争对手Oracle把InnobaseOy收购了。后来08年Sun公司开发Java语言的Sun收购了MySQLAB09年Sun公司又被 Oracle 收购了所以 MySQLInnoDB 又是一家了。有人觉得 MySQL 越来越像Oracle其实也是这个原因。 那么除了这两个我们最熟悉的存储引擎数据库还支持其他哪些常用的存储引擎呢我们可以用这个命令查看数据库对存储引擎的支持情况
show engines;其中有存储引擎的描述和对事务、XA协议和Savepoints的支持。
XA协议用来实现分布式事务分为本地资源管理器事务管理器。Savepoints用来实现子事务嵌套事务。创建了一个Savepoints之后事务就可以回滚到这个点不会影响到创建Savepoints之前的操作。这些数据库支持的存储引擎分别有什么特性呢 MyISAM3 个文件 应用范围比较小。表级锁定限制了读/写的性能因此在Web 和数据仓库配置中它通常用于只读或以读为主的工作。 支持表级别的锁插入和更新会锁表。不支持事务 拥有较高的插入insert和查询select速度 存储了表的行数count速度更快。怎么快速向数据库插入100万条数据我们有一种先用MyISAM插入数据然后修改存储引擎为InnoDB的操作 适用只读之类的数据分析的项目 InnoDB2 个文件 mysql5.7中的默认存储引擎。 InnoDB是一个事务安全与ACID兼容的MySQL存储引擎它具有提交、回滚和崩溃恢复功能来保护用户数据。InnoDB行级锁不升级为更粗粒度的锁和Oracle风格的一致非锁读提高了多用户并发性和性能。InnoDB将用户数据存储在聚集索引中以减少基于主键的常见查询的I/O。为了保持数据完整性InnoDB还支持外键引用完整性约束。 支持事务支持外键因此数据的完整性、一致性更高 支持行级别的锁和表级别的锁 支持读写并发写不阻塞读MVCC 特殊的索引存放方式可以减少IO提升查询效率 适用经常更新的表存在并发读写或者有事务处理的业务系统 Memory1 个文件 将所有数据存储在RAM中以便在需要快速查找非关键数据的环境中快速访问。这个引擎以前被称为堆引擎。其使用案例正在减少 InnoDB及其缓冲池内存区域提供了一种通用、持久的方法来将大部分或所有数据保存在内存中而ndbcluster为大型分布式数据集提供了快速的键值查找。 把数据放在内存里面读写的速度很快但是数据库重启或者崩溃数据会全部消失。只适合做临时表。将表中的数据存储到内存中 CSV3 个文件 它的表实际上是带有逗号分隔值的文本文件。 csv表允许以csv格式导入或转储数据以便与读写相同格式的脚本和应用程序交换数据。因为csv 表没有索引所以通常在正常操作期间将数据保存在innodb表中并且只在导入或导出阶段使用csv表。 不允许空行不支持索引。格式通用可以直接编辑适合在不同数据库之间导入导出。 Archive2 个文件 这些紧凑的未索引的表用于存储和检索大量很少引用的历史、存档或安全审计信息。 不支持索引不支持update delete.
这是MySQL里面常见的一些存储引擎我们看到了不同的存储引擎提供的特性都不一样它们有不同的存储机制、索引方式、锁定水平等功能。我们在不同的业务场景中对数据操作的要求不同就可以选择不同的存储引擎来满足我们的需求这个就是MySQL支持这么多存储引擎的原因。
5.4 如何选择存储引擎
如果对数据一致性要求比较高需要事务支持可以选择InnoDB。如果数据查询多更新少对查询性能要求比较高可以选择MyISAM。如果需要一个用于查询的临时表可以选择Memory。如果所有的存储引擎都不能满足你的需求并且技术能力足够可以根据官网内部手册用C语言开发一个存储引擎
6.执行引擎返回结果
OK存储引擎分析完了它是我们存储数据的形式继续第二个问题是谁使用执行计划去操作存储引擎呢这就是我们的执行引擎它利用存储引擎提供的相应的API来完成操作。为什么我们修改了表的存储引擎操作方式不需要做任何改变因为不同功能的存储引擎实现的API是相同的。
最后把数据返回给客户端即使没有结果也要返回。 MySQL架构
基于上面分析的流程我们一起来梳理一下MySQL的内部模块。
1.模块详解 Connector用来支持各种语言和SQL的交互比如PHPPythonJava的 JDBCManagement Serveices Utilities系统管理和控制工具包括备份恢复、MySQL复制、集群等等Connection Pool连接池管理需要缓冲的资源包括用户密码权限线程等等SQL Interface用来接收用户的SQL命令返回用户需要的查询结果Parser用来解析SQL语句Optimizer查询优化器CacheandBuffer查询缓存除了行记录的缓存之外还有表缓存Key缓存权限缓存等等Pluggable Storage Engines插件式存储引擎它提供API给服务层使用跟具体的文件打交道
2.架构分层
总体上我们可以把MySQL分成三层跟客户端对接的连接层真正执行操作的服务层和跟硬件打交道的存储引擎层参考MyBatis接口、核心、基础。 连接层 我们的客户端要连接到MySQL服务器3306端口必须要跟服务端建立连接那么管理所有的连接验证客户端的身份和权限这些功能就在连接层完成。 服务层 连接层会把SQL语句交给服务层这里面又包含一系列的流程比如查询缓存的判断、根据SQL调用相应的接口对我们的SQL语句进行词法和语法的解析比如关键字怎么识别别名怎么识别语法有没有错误等等。 然后就是优化器MySQL底层会根据一定的规则对我们的 SQL语句进行优化最后再交给执行器去执行。 存储引擎 存储引擎就是我们的数据真正存放的地方在MySQL里面支持不同的存储引擎。再往下就是内存或者磁盘。
