MySQL性能调优与架构设计 .1.2 MySQL与其他数据库的简单比较前面我们简单介绍了 My SQL的发展历程,从中了解了MyS呲L快速崛起的必要的条件。接下米,我们通过wys哑L在功能,性能,以及其易用性方面和其他主流的数据库做一个基本的比较,来了解一下 My SQL成为当下最流行的开源数据厍软件的充分条件。12.1功能比较作为一个成熱的数据库管理系统,要满足冬种各样的商业需求,功能肓定是公被列入重点参考对象的。Mys哑L虽然在早期版本的时候功能非常简单,只能倣一些很基础的结构化数据存取操作,但是经过多年的改进和完善之后,已经基本只备了所有通用数据库管理系统所需要的相关功能。MsQL基本实现了 ANSI SQL92的大部分标准,仅有少部分并不经常被使用的部分没有实现。比如在字段类型支持方面,另一个著名的开源数据库 PostGresQL支持的类型是最完整的,而 Oracle和其他一些萄业数据库,比如DB2、 Sybase等,较 My SQL来说也要相对少些。这一点,我们可以通过TCX的 crash-ne测试套件所得出的测试报告得知。在事务支持方面,虽然MyS呲L自己的存储引擎并没冇提供,但是已经通过第三方插件式存储引擎Innodb实现了SQL92标准所定义的四个事务隔离级别的全部,只是在实现的过程中每一种的实现方式可能有一定的区别,这在当前商用数据库管理系统中都不多见。比如,大家所熟知的大名鼎鼎的0 racle数据库就仅仅实现了其中的两种( Serializable和 Read commited)而 PostGreSQL,同样支持四种隰离级别。不过在可编程支持方面, MYSQL和其他数据库相比还有一定的差距,虽然最新版的 My SQL已经开始提供一些简单的可编程支持,如开始支持 Procedure,Funcτion, Trigger等,但是所支持的功能还比较有限,和其他几大商用数据库管理系统相比,还存在较大的不足。如Oracle有强大的PL/sQL, SQL Server有TSQL, PostGresQL也有功能很完善的PL/ PGSQL的支持。整体来说,虽然在功能方面 My SQL数据库作为个通用的数据库管理系统暂时还无法和PostGreSQL相比,但是其功能完全可以满足我们的通用商业需求,提供足够强人的服务。而且不管是哪一种数据庠在功能方面都不敢声称自己比其他任何一款商用通用数据库管理系统都强,甚至都不敢声称能够自凵拥冇某一数据库产品的所有功能。因为每一款数据库管理系统都有起自身的优势,也有起自身的限制,这只能代表每一款产品所倾向的方向不一样而已。122易用性比较从系统易用性方面来比较,每一个使用过 My SQL的用户都能够明显地感觉出 My SQL在这方面与其他通用数据库管理系统之间的优势所在。尤其是相对于一些大型的商业数据库管理系统如0 racle、DB2以及 Sybase来说,对于普通用户来说,操作的难易程度明显不处于个级别。MySL一直都奉行简单易用的原则,也正是靠这一特性,吸引了大量的初级数据库用户最终选择了 MYSQL。也止是这·批又批的初级用广,在经过了几年时间的成长之后,很多都己经成为了高级数据库用户,而且也一直都在伴随着 MySQL成长。从安装方面来说,MSQL安装包大小仪仅只有100MB左右,这几大商业数据库完全不在个数量级。安装难易程度也要比 Oracle等商业数据厍简单很多,不论是通过已经编译好的二进制分发包还是源码编译安装,都非常简单。从数据库创建来比较, My SQL仅仅只需要一个简单的 CREATE DATABASE命令,即可在瞬间完成建库的动作,而 Oracle数据库与之相比,创建一个数据库简直就是一个非常庞大的工程。当然,二者数据库的概念存在一定差别也不可否认。123性能比较性能方面,一直是 MySQL引以为自豪的一个特点。在权威的第三方评测机构多次测试较量各种数据库TPCC值的过程中, My SQL直都有非常优异的衣现,而且在其他所有商用的通用数据库管理系统中,仅仅只有 Oracle数据厍能够与其一较高下。至于各种数据厍详细的性能数据,我这里就不便记录,大家完全可以通过网上第三方评测机构公布的数据了解具体绀节信息。MySL一直以来奉行一个原则,那就是在保证足够的稳定性的前提下,尽可能的提高自身的处理能力。也就是说,在性能和功能方面, My SQL第一考虑的要素主要还是性能, My SQL希望自己是一个在满足客户9%的功能需求的前提下,花掉剩下的大部分精力来性能努力,而不是希望自己是成为一个比其他任何数据库的功能都要强大的数据库产品。124可靠性关」可靠性的比较,并没有太多详细的评测比较数据,但是从目前业界的交流中可以了解到,几大商业厂商的数据库的可靠性肯定是没有太多值得怀疑的。但是做为开源数据库管理系统的代表,Mys也有非常优异的表现,而并不是像有些人心中所怀疑的那样,因为不是商业厂商所提供,就会不够稳定不够健壮。从当前最火的 Facebook这样大型的网站都是使用 My SQl.数据库,就可以看出,ⅥSQ.在稳定可靠性方面,并不会比我们的商业厂商的产品有太多逊色。而且排在全求前10位的大型网站里面,大部分都有部分业务是运行在 My SQL数据库环境上,如 Yahoo, Google等。总的来说,MySL数据库在发展过程中·直有自己的三个原则:简单、高效、可靠。从上面的简单比较中,我们也可以看出,在 My SQL自己的所有三个原则上面,没有哪一项是做得不好的。而且,虽然功能并不是 My SQL自身所追求的三个原则之一,但是考虑到当前用户量的急剧增长,用户需求越来越越多样化, MYSQL也不得不在功能方面做出大量的努力,来不断满足客户的新需求。比如最近版本中出现的 Eent scheduler(类似于 Oracle的Job功能), Partition功能,自主研发的 Maria存储引擎在功能方面的扩展, Falcon存储引擎对事务的支持等等,都证明了MySQ.在功能方面也开始了不懈的努力。任何一种产品,都不可能是完美的,也不可能适用于所有用户。我们只有衡量了每一和产品的各种特性之后,从中选择出一种最适合于自身的产品1.3 MySQL的主要适用场景据说日前 My SQL用户已经达千万级别了,其中不乏个业级用户。可以说是目前最为流行的开源数据库管理系统软件了。任何产品都不可能是万能的,也不可能适用于所有的应用场景。那么MSQL到底在什么场景卜适用什么场景卜不适用呢?1、Web网站系统Web站点,是 My SQL最大的客户群,也是 My SQL发展史上最为重要的支撑力量,这一点在最开始的 My SQI. Seryer简介部分就已经说明过M√SQL之所以能成为Web站点开发者们最青睐的数据库管理系统,是因为 MYSQL数据库的安装配置都非常简单,使用过程中的绯护也不像很多大型商业数据库管理系统那么复杂而且性能出色。还有个非常重要的原因就是Mys是开放源代码的,完全可以免费使用2、日志记录系统MSQL数据库的插入和査询性能都非常的高效,如果设计地较好,在使用 My Isam存储引擎的时候,两者可以做到互不锁定,达到很高的并发性能。所以,对需要大量的插入和杏询日志记录的系统米说,ySL是非常不错的选择。比如处理用户的登录日志,操作日志等,都是非常适合的应用场景。3、数据仓库系统随着现在数据仓库数据量的飞速増长,我们需要的存储空间越来越大。数据量的不断增长,使数据的统计分析变得越来越低效,也越来越困难。怎么办?这里有几个主要的解决思路,个是采用昂贵的晑性能主机以提高计算性能,用高端存储设备提高L/O性能,效果理想,但是成本非常高;第二个就是通过将数据复制到多台使用大容量硬盘的廉价 pc server上,以提高整体计算性能和I/0能力,效果尚可,存储空间有一定限制,成本低廉;第三个,通过将数据水平拆分,使用多台廉价的 pc server和木地憾盘来存放数据,每台机器上面都只有所有数据的一部分,解决了数据量的问题,所有 pc server一起并行计算,也解决了计算能力问题,通过中间代理程序调配各台机器的运算任务,既可以解决计算性能问题又可以解决I/0性能问题,成木也很低廉。在上面的三个方案中,第二和第三个的实现, My SQL都有较人的优势。通过MySL的简单复制功能,可以很好的将数据从一台主机复制到另外一台,不仅仅在局域网内可以复制,在广域网同样可以。当然,很多人可能会说,其他的数据库同样也可以做到,不是只有MySαL有这样的功能。确实,很多数据库同样能到,但是 My SQL是免费的,其他数据库人多都是按照主机数量或者cpu数量来收费,当我们使用人量的pcserver的时候, License贵用相当惊人。第一个方案,基本上所有数据库系统都能够实现,但是其高昂的成本并不是每一个公司都能够承担的4、嵌入式系统嵌入式环境对软件系统最大的限制是硬件資源非常有限,在嵌入式环境下运行的软件系统,必须是轻量级低消耗的软件。MySQ在资源的使用方面的佴缩性非常大,可以在资源非常充裕的环境下运行,也可以在资源非常少的环境下正常运行。它对于嵌入式环境来说,是一种非常合适的数据库系统,而且MySL有专门针对于嵌入式环境的版本1.4小结从最初的诞生,到发展成为目前最为流行的开源数据库管理软件, My SQL已经走过了较长的一段路,也正是这段不寻常的路,造就了今天 MYSQL的成就通过本章的信息,我想各位读者应该是比较清楚 MYSQL的大部分基本信息了,对于My SQL主要特长,以及适用场景,都有了一个初步的了解。在后续章节中我们将会针对这些内容做更为详细深入的介绍第2章 MySQL架构组成前言麻雀虽小,五脏俱全。MySq.虽然以简单著称,但其内部结构并不简单。本章从 My SQI物理组成、逻辑组成,以及相关工具几个角度米介绍 My SQL的整体架构组成,希望能够让读者对 My SQL有一个更仝面深入的了解。2.1 MySQL物理文件组成2.L1日志文件1、错误日志: Error log错误日志记录了 My QL Server运行过程中所有较为严重的警告和错误信息,以及 My SQLServer每次启动和关闭的详细信息。在默认情况下,系统记录错误日志的功能是关闭的,错误信息被输出到标准错误输出( stderr),如果要开启系统记汞错误日志的功能,需要在启动时开启-1og-eror选项。错淏日志的默认存放位置在数据目录下,以 hostname.cr命名。但是可以使用俞令:--log- error[= file name],修改其仔放目录和文件名为了方便维护需要,有时候会希望将借误日志中的内容做备份并重新开始记录,这时候就可以利用MySQ的 FLUSH LOGS命令来告诉 My SQL各份旧日志文件并生成新的口志文件。备份文件名以“.old”结尾2、二进制日志: Binary log& Binary log index二进制日志,也就是我们常说的 binlog,也是 My SQL Server中最为重要的日志之一。当我们通过“-1og-bin= file name]”打开了记录的功能之后, My SQL会将所有修改数据库数据的 query以二进制形式记录到日志文件中。当然,日志中并不仅限」 query语句这么简单,还包括每一条 query所执行的时间,所消耗的资源,以及相关的事务信息,所以 binlog是事务安全的和错误日志一样, binlog记录功能同样需要“--1og-bin[- file name]”参数的显式指定才能开启,如果未指定 file name,则会在数据目录下记录为mysq1bin.米冰*(*代表09之间的某一个数字,来表示该日志的序号)。binlog还有其他些附加选项参数- -max binlog size”设置 binlog的最大存储上限,当日志达到该上限时, My SQL会重新创建一个日志开始继续记录。不过偶尔也有超出该设置的 binlog产生,一般都是因为在即将达到上限时,产生了个较大的事务,为了保证事务安全,MyS则不会将同个事务分开记录到两个 binlog中。“- binlog-do-db= db name”参数明确告诉 My SQL,需要对某个( db name)数据库记录 binlog,如果有了“- binlog-do-db= db name”参数的显式指定, My SQL会忽略针对其他数据库执行的 query,而仅仅记录针对指定数据库执行的 querybinlog ignore db= db name”与“ binlog do db= db name”完全相反,它显式指定忽略某个( db name)数据库的 binlog记录,当指定了这个参数之后, My SQL会记录指定数据库以外所有的数据库的 binlog“-- binlog- ignore-db= db name”与“- binlog-do-db= db name”两个参数有个共同的概念需要大家理解清楚,参数中的 db name不是指 query语句更新的数据所在的数据库,而是执行 query的时候当前所处的数据库。不论更新哪个数据库的数据, My SQL仅仅比较当前连接所处的数据库(通过 use db name切换后所在的数据)与参数设置的数据库名,而不会分析 query话句所更新数据所在的数据库。mysql-bit. index文件( binary log index)的功能是记录所有 Binary log的绝对路径,保证MyS@L各种线程能够顺利的根据它找到所有需要的 Binary log文件3、更新日志: update log更新日志是MySQ在较老的版本上使用的,其功能和 binlog基本类似,只不过不是以二进制格式来记录而是以简单的文本格式记录内容。自从 My SQL增加了 binlog功能之后就很少使用更新日志了。从版本5.0开始,MSQL已经不再支持更新日志了。4、查询口志: query log查询日志记录MyQL中所有的 query,通过“-- log=fina name”来打开该功能。由于记录了所有的 query,包括所有的 select,体积比较大,开启后对性能也有较大的影响,所以请大家慎用该功能。一般只用」跟踪某些特殊的sql性能问题才会短暂打廾该功能。默认的查询日志文件名为 hostname.log5、慢查询日志:s1 ow query log顾名思义,慢查询日志中记录的是执行时间较长的 query,也就是我们常说的slowquery,通过设-1og-slow- queries[= file name]来打开该功能并设置记录位置和文件名,默认文件名为 hostname-slow.logs,默认目录也是数据目录慢查询口志采用的是简单的文本格式,可以通过各种文本编辑器查看其中的内容。其中记录了语句执行的时刻,执行所消耗的时间,执行用户,连接主机等相关信息。 My SQL还提供了专门用来分析满査询日志的工具程序 mysqlslowdump,用来帮助数据库管理人员解决可能存在的性能问题6、 Innodb的在线redo志: innodb redo1ogInnodb是一个事务安全的存储引擎,其事务安全性主要就是通过在线redo日志和记录在表空间中的undo信息来保证的。redo日志中记录了 Innodb所做的所有物理变更和事务信息,通过redo日志和undo信息, Innodb保证了在任何情况下的事务安全性。 Innodb的redo日志同样默认存放在数据目求下,可以通过 innodb1 og group home dir来更改改置日志的存放位置,迸过 innodb log files in group设置日志的数量2.L2数据文件在WySL中每个数据斥都会在定义好(或者默认)的数据目录下存在个以数据库名字命名的文件夹,用来存放该数据库中各种表数据文件。不同的 My SQL存储引擎有各自不同的数据文件,存放位置也有区别。多数存储引擎的数据文什都存放在和 My IsaN数据文件位置相同的目录下,但是每个数据文件的扩展名却各不一样。如 My ISAv用“.MYD”作为扩展名, Innodb用“.ibd", Archive用“.arc”,CSV用“.csv,等等。1、“.rm”文件与表相关的元数据(meta)信息都存放在“.frm”文件中,包括表结构的定义信息等不论是什么存储引擎,每一个表都会有一个以表名命名的“.frm”文件。所有的“.frm”文件都存放在所属数据库的文件夹下面2、“.MYD”文件“.ⅦYD”文件是 MyISAM存储引擎专用,存放 My ISAM表的数据。每一个MISM表都会有一个“.YD”文件与之对应,同样存放于所属数据库的文件夹下,和“.frm”文件在一起。I”文件“.MYI”文件也是专属于 My IsAM存储引擎的,主要存放 My ISAM表的索引相关信息。对于 My ISAM存储来说,可以被 cache的内容主要就是来源于“.MYI”文件中。每一个 My ISAM衣对应个“.MY”文件,存放于位置和“.frm”以及“.MYD”样4“.ibd”文件和 ibdata文件这两种文件都是存放 Innodb数据的文件,之所以有两和文件来存放Imod的数据(包括索引),是因为 Innodb的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据,还是独享表空间存放存储数据。独享表空间存储方式使用“.ibd”文件来存放数据,且每个表一个“,ibd”文件,文件存放在和 My ISAM数据相同的位置。如耒选用共享存储表空间来存放数据,则会使用 iodate文件来存放,所有表共同使用一个(或者多个,可自行配置) oblata文件。 ibdata文件可以通过 innodb data home dir和 innodb data file path两个参数共同配置组成, innodb data_ home dir配置数据存放的总目录,而nnodb data file path配置每一个文件的名称。当然,也可以不配置innodb data home dir而直接在 innodb data file path参数配置的吋候使用绝对路径来完成配置。 innodb data file path中可以一次配置多个 ibdata文件。文件可以是指定大小,也可以是自动扩展的,但是 Innodb限制了仅仅只有最后一个 ibdata文件能够配置成自动扩展类型。当我们需要添加新的 ibda ta文件的时候,只能添加在 innodb data file path配置的最后,而且必须重启MSQL才能完成 iodate的添加工作。不过如果我们使用独享表空间存储方式的话,就不会有这样的问题,但是如果要使用裸设备的话,每个表一个裸设备,可能造成裸改备数量非常大,而且不太容易控制大小,实现比较困难,而共享表空问却不会有这个问题,容易控制裸设备数量。我个人还是更倾向于使用独享表空间存储方式。当然,两种方式各有利弊,看大家各自应用环境的侧重点在那里了。上面仅仅介绍了两种最常用存储引擎的数据文件,此外其他各种存储引擎都有各自的数据文件,读者朋友可以自行创建某个存储引擎的表做一个简单的测试,做更多的了解。213 Replication相关文件:1、 master.info文件;master.info文件存在于 Slave端的数据目录下,里面存放了该 Slave的 Master端的相关信息,包括 Master的主机地址,连接用户,连接密码,连接端口,当前日志位置,经读取到的日志位置等信息2、 relay log和 relay log indexmysql- relay-bin. XXXXXn文件用于存放 Slave端的I/0线程从 Master端所读取到的 Binary log信息,然后由 Slave端的SαL线程从该 relay log中读取并解析相应的日志信息,转化成 Master所执行的SQL语句,然后在 Slave端应用mysql relay bin. index文件的功能类似于 mysql bin. index,同样是记录日志的存放位置的绝对路径,只不过他所记录的不是 Binary log,而是 Relay log3、 relay-log.info文件:类似于 master.info,它存放通过 Slave的1/0线程写入到本地的 relay log的相关信息。供 Slave端的sQL线程以及某些管理操作随时能够获取当前复制的相关信息。2.1.4其他文件:1、 system config fileMy SQL的系统配置文件一般都是“my.cnf",Unix/ Linux下默认存放在"etc"目录下,Windows境一般存放在“c:/ windows”目录下面。“my.cnf”文件中包含多种参数选项组( group),每一和参数组都通过中括号给定了固定的组名,如“ mysql”组中包括了 mysqld服务启动时候的初始化参数,“[ client]”组中包含着客户端工具程序可以读取的参数,此外还有其他针对于各个客户端软件的特定参数组,如mysq1程序使用的“mysq1]”, mysqlchk使用的“[ mysglchk]”,等等。如果读者朋友自己编写了某个客户端程序,也可以自己设定个参数组名,将相关参数配置在里面,然后调用mysq客户端api程序中的参数读取api读取相关参数。2、 pid filepid file是 mysql应用程序在Unix/ Linux环境下的一个进程文件,和许多其他Unix/inux服务端程序一样,存放着自己的进程id3、 socket filesocket文件也是在inix/ Linux环境下才有的,用户在inix/ Linux环境下客户端连接可以不通过TCP/IP网络而直接使用 Unix socket来连接 My SQL2.2 MySQL Server系统架构22.1逻辑模块组成总的来说,MSQ可以看成是二层架构,第一层我们通常叫做SQ. Layer,在 MySQL数据斥系统处理底层数据之前的所有工作都是在这层完成的,包括权限判断,sq解析,执行计划优化, query cache的处理等等;第二层就是存储引擎层,我们通常叫做 StorageEngine layer,也就是底层数据存取操作实现部分,由多种存储引擎共同组成。所以,可以用如下一张最简单的架构示意图来表示 MYSQL的基木架构,如图2-1所示: