欢迎来到学术参考网

一种深入解决MySQL数据库优化方案的体系设计

发布时间:2015-07-16 19:35

 1 MySQL选择意义
  众所周知,MySQL拥有独特的存储引擎架构,其系统核心提供的多线程机制提供完全的多线程运行模式,同时轻巧快速,系统资源消耗小,支持高并发连接,提供了面向C、C++、JAVA、PHP、以及Python等多种编程语言的编程接口,很强的平台的灵活性,完美支持现有的所有平台,作为一个开源的数据库,它毋庸置疑是开源数据库中的佼佼者,现已国内很多大中小企业所用,但是万物皆有瓶颈,MySQL也不例外。针对我们所用的数据库出现了瓶颈,那我们该采取什么措施,这些在各大使用MySQL的企业显得异常重要了。
  2 数据库设计规范
  2.1 数据库设计范式
  为了规范数据库的设计,在数据库理论发展的过程中,逐渐形成了数据库范式的理论。到目前为止,一般认为数据库设计中有五大范式,这五大范式又是层次递进的。
  第一范式:对于表中的每一行,必须且仅仅有唯一的行值。在一行中的每一列仅有唯一的值并且具有原子性。第二范式:要求非主键列是主键的子集,非主键列活动必须完全依赖整个主键。主键必须有唯一性的元素,一个主键可以由一个或更多的组成唯一值的列组成。一旦创建,主键无法改变,外键关联一个表的主键。主外键关联意味着一对多的关系。第三范式:要求非主键列互不依赖。第四范式:禁止主键列和非主键列一对多关系不受约束。第五范式:将表分割成尽可能小的块,为了排除在表中所有的冗余。
  但是现在反范式的一些理论也越来越流行,为什么又要反范式呢。甚至第三范式都要反?数据库范式理论在20世纪70年代提出的,在20世纪80年代基本定型,那个时候的系统存在如下特征:可用的存储器资源极其有限,几百兆字节大小的磁盘就算很大的了,而现在的硬盘动辄几百GB甚至上TB;同时,那个时候的网络还不成熟,能使用网络的人很少,通常只是涉及单机的计算性能。因此,数据库范式理论强调减少依赖、降低冗余是有其历史背景的。而现在,硬盘容量比当时大了几万倍,硬盘廉价,数据存储不再是问题;同时,面临高并发,业务逻辑极度复杂,低延迟要求的情况,此时,还一味的遵循范式设计理论是不当的。适当地降低范式,增加冗余,用空间来换时间是值得的,最低可以把范式降低到第一范式。
  2.2 设计数据库的原则
  1)核心业务使用范式。在类似交易有关的这种敏感和核心业务中,强调数据安全和一致性,需要遵循范式保证机密数据不被破坏,核心业务不出现不一致的情况。
  2)弱一致性需求--反ACID。在一些对数据一致性要求不高的场合,不必完全遵循ACID,出现适当的数据不一致是可以容忍的,如在线人数统计,静态页等。最近几年流行的Nosql技术,就是基于弱一致性,降低数据完整性和一致性换取效率。
  3)空间换时间,冗余换效率。由于一条可见记录被拆分到多个表进行记录,当数据量比较大的时候,联表查询就变得比较费时,SQL语句也变得复杂,难于优化,此时就需要适当的冗余了,在统计报表,视图中就是对这已规则的具体体现。统计表通常会有很多列,有的甚至多到上百列,需要关联几个甚至几十个表进行查询。如果每次查看统计信息都进行关联查询,速度缓慢不说,更严重的情况是使用的人一多,就可能导致数据库服务器宕机。这种情况就需要冗余表了,冗余表一般符合第一或者第二范式。那冗余表怎么处理,一般是定期转储,很少有人回去实时查询3年前某个月的销售数据明细表。
  4)避免不必要的冗余。范式理论是不是想反就能反的,反范式理论不是说不要范式,而是必要的时候创建冗余表或者总结表、不必要的冗余表仍然是要避免的。
  3 MySQL优化措施
  数据库的优化主要包括两个方面:SQL程序语句的优化和数据库服务器和配置的优化。
  3.1 数据库优化基本遵循的原则
  1)使用join时,应该用小的结果集驱动大结果集。复杂的情况下,应把join查询拆分多个query。因为join多个表是,可能导致更多的锁定和堵塞。
  6)limit的基数比较大时使用 between。between限定比limit快
  7)不要使用rand()函数获取多条随机记录。
  8)不要使用 COUNT(id) ,应该是 COUNT(*)
  3.2 关于索引基本遵循的原则
  1)关于索引,并不是越多越好,因为维护索引需要成本,有时索引大小已经大于数据量大小,那这时你就应该坚持数据库架构是否合理了。
  2)差的索引和没有索引效果一样。
  3)每个表的索引应在5个以下,应合理利用部分索引和联合索引。
  4)不要在结果集中的结果单一的列上建索引。比如字段只有0和1两个结果集,在这上面建索引并没有多大的作用。
  5)建索引的字段结果集最好分布均匀,或者符合正态分布。
  3.3 关于MySQL存储引擎的选择
  MySQL中有多种存储引擎,每种存储引擎都有自己的特色,想要好的性能, 第一步就是选择合适的数据库引擎。My SQL中常见的三种引擎特点如表1。
  通常我们认为MyISAM 注重性能,InnoDB注重事务,故一般使用MyISAM类的表做非事务型的业务。这种观点产生于早期InnoDB引擎还不成熟的时候,而现在并不是这样。MySQL在高并发下的性能瓶颈是很明显的,主要原因是锁定机制导致的堵塞。而InnoDB在锁定机制上采用的行级锁,不同于MyISAM的表级锁,行级锁在锁定上带来的消耗大于表级锁,但是在系统并发访问量不就高的时候,InnoDB整体性能远高于MyISAM。同时,InnoDB的索引不仅缓存索引本身,也缓存数据,所以InnoDB则需要更大的内存,不过现在,内存是很廉价的了。选择最合适的存储引擎是优化的第一步。
  3.3.1 存储引擎的选择
  选择存储引擎,首先我们得了解读写比(R/W) 的概念,通过 在数据库中执行 show global status 得到系统当前状态。这些变量中,形容COM_XXX的语句表示XXX语句执行的次数,如 COM_select 表示select语句的执行次数,以此类推。通过计算读类型和写类型语句的比例。即可确定一个粗糙的读写比例。理想的读写比为100:1, 当读写比达到10:1的时候,就认为是已写为主的数据库了,一般这个值30:1左右。
  3.3.2存储引擎的选择遵循基本原则
  1)采用MyISAM引擎:R/W > 100:1且update相对较少;并发不高,不需要事务;表数据量小;硬件资源有限。
  2)采用InnoDB引擎: R/W 比 较小,频繁更新大字段; 表数据超过100万,并发高; 安全性和可用性要求高。
  3)采用Memory引擎:有足够的内存;对数据一致性要求不高,如在线人数和session等应用; 需要定期归档的数据。
  3.4 MySQL服务器调整优化措施
  1)关闭不必要的二进制和慢查询日志,仅在内存足够或者开发调试时打开他们,还可以使用下面语句查询是否打开:show variables like ‘%slow%’;还可以使用下面的语句查看慢查询的条数,定期打开方便优化show global status like ‘%slow%’;但是慢查询也会带来一些cpu的损耗,建议间断性打开满日志来定位性能瓶颈。
  2)适度使用Query Cache。
  3)增加Mysql允许的最大连接数。可用下面的语句查看Mysql循序的最大连接数。show variables like 'max_connections';
  4)对于MyISAM 表适当的增加key_buffer_size。当然这需要根据Key_cache 的命中率进行计算,当key_buffer_size值大于1%时就需要适当增加key_buffer_size了。
  对于MyISAM,还需要注意table_cache的设置,当table_cache 不够用的时候,mysql会采用LRU算法踢掉最长时间没有使用的表;如果table_cache设置过小,mysql会反复打开。关闭FRM文件,早晨一定的性能损失;如果 table_cache设置过大,mysql将会消耗很多cpu资源去处理table_cache的算法。因此table_cache值一定要设置合理,可以参考opened_tables参数的值,如果这个值一直增加,就需要适当增加table_cache的值。对于InnoDB,需要重点注意innodb_buffer_pool_size参数。
  5)从表中删除大量行后,可允许OPTMIZE TABLE TableName 进行碎片整理。
  4 结束语
  MySQL数据库的优化工作是一个长期的、复杂的、循环往复的过程。MySQL数据库优化有时在访问量大的情况下下仅仅只通过服务器参数调整,SQL语句优化也都不一定能解决实际的高并发的的需要的,还需要做数据库的分区、分表,更甚者需要做数据库的分布式架构,做数据库集群,需要做数据库读写分离,异步信息队列来避免高并发情况下所造成数据库击穿等现象,所以我们做优化的时候还需要根据实际的应用做相应的策略。  
  参考文献:
  [1] 唐汉明,翟振兴,关宝军,等.深入浅出MySQL(数据库开发,优化与管理维护[M].2版.北京:人民邮电出版社,2014.
  [2] 王小东,李军,康建勋.高性能MySQL[M].北京:电子工业出版社,2010.
  [3] 宁青,唐李洋,诸云萍.高可用MySQL:构建健壮的数据中心[M].北京:电子工业出版社,2011.
  [4] 姜承尧.MySQL技术内幕: SQL编程[M].北京:机械工业出版社华章公司,2012.
  [5] 李芳,于红芸,邵健.深入理解MySQL核心技术[M].北京:中国电力出版社,2009.
  [6] 贺春旸.MYSQL管理之道:性能调优,高可用与监控[M].北京:机械工业出版社,2013.

上一篇:数据挖掘在进口报关流程优化中的应用技术发展

下一篇:基于四面体网格的地层三维模型的建设方法