WEB中的MYSQL应用

前段时间,一直在关心MYSQL性能方面的忧化,所以也想把自己的一些心得体会写出来和大家共同探讨一下,由于我暂时还无直接查看MYSQL源码的能力,所以可能有些方面讲话得不是很透彻,只是把在开发WEB时MYSQL应用所需要注意的一些方面写出来,算是一种方向吧.

 

我想稍微有过大型WEB开发经验的朋友都知道,速度,效率是非常需要注意的一个方面,而WEB开发,这些性能瓶颈却有百分之八九十是存在于数据库的,我是用PHP,所以数据库方面自然也就是MYSQL的搭配,下面我会从MYSQL多个方面来谈一下我对MYSQL应用的一些看法.

 

 

第一:索引

很多时候在我们使用查询时,如果速度慢了,也就会自然而然的想到了数据库中的索引,确实我们在很多时候,可以利用索引来获得比平常快几个数量级的查询速度,但可能又有很多朋友可能不知道,为什么加上索引会使我们的查询速度变快,所以这便是我需要和大家讨论的关于索引的第一个问题

1.1:在MYSQL索引类型中,B-TREE索引是应用于大部分存储引擎之中的,所以我们这里就以这个来讨论.

B-TREE是以B树为扩展而成的一个多路搜索树(并不是二叉),B-树是从根结点开始,对结点内的关键字进行查找,如果命中则结束,否则进入其查询关键字所属范围的儿子结点,如此重复,直至所对应的儿子指针为空,或者已经是叶子结点.而当我们不使用索引时,MYSQL是以一种线性的方式查找的,即对所有的记录进行一次查找,这便成了全表扫描,如果记录少还无所谓,而当记录达到成百上千万,则这种查找显然是不明智的.(B-tree树的存储结构,大家可以上网查一下,有很多讲解的文章)

第2个问题我们讨论在我们的应用中怎样去建立一个高效的索引

这个问题,要得到真正解决,必须是建立在自己针对自己网站系统一个全方位的了解之上的,需要根据哪些条件来进行查找,哪些条件是适合建立索引的,这都是在我们获知业务需求时,才能下的决定,首先,哪些条件适合索引,哪些又不适合呢,这里有几个标准

1)较频繁作为条件出现在查询条件的字段,这个字段是应该被创建索引

2)当很多种条件下,是通过多个字段条件来筛选记录,此时,便应该考虑建立组合索引,其实很多时候,组合索引的查询效率肯定是比单字段索引要好,当然这种选择也是需要根据实际情况来考虑,如果组合索引中的字段更新频率很高,那也许单列索引更好.

3)如果在某一列字段值惟一性太差的则不适宜作为索引,那这个重复值为多少时,则不适合建立索引,大概是百分之十五吧(引用一些有经验的QUERY调优专家的说法,哈哈)

4)最简单一条,也许也是大家都清楚的一条,就是不出现在WHERE条件中的字段不该创建索引

第3个问题就是关于MYSQL关于索引如何的选择问题

MYSQL内部有个查询优化器,他会根据语法分析后,获得一个最优化的执行计划,同时也决定了如何使用索引,MYSQL的查询优化器虽然已经很不错,但有时,他的选择并不一定是我们所需要的,假如,在某种情况明明选择第二种索引要比第一种索引的查询性能要好,而它却选择的是第一种索引,此时我们可以人为的指定MYSQL使用特定索引,在表名后用命令FORCE INDEX(INDEX_NAME)

上面我们说的都是一些最基本的场景应用,当我们真正遇到并发性很大的系统时,又如何优化了呢,拿myisam来说,这就牵涉到了"索引缓存的优化",一般需要经过实际的观察,然后调整MYSQL服务器相应对的一些参数,如key_buffer_size(索引缓存大小),key_buffer_block_size(索引缓存中cache block size)等等,了解这些你必须需要知道它的索引文件的存放格式,我这里也不再啰嗦,纯粹是提个方向,当然,也许很多朋友说,涉及到这个层面上的东西是DBA的事,确实,但如果你多知道一些关于这方面的知识,总是无害的吧.

 

第二:QUERY的优化(explain与profiling的应用)

关于这方面的优化,也许有很多朋友都有自己的一套经验,但既然我已经说到这了,所以我还是得啰嗦一段,说说我的方法,其实要优化QUERY语句,不可能是毫无目的的去优化,一定得去定位哪条QUERY真正影响了系统的性能,关于这个问题,我认为我们可以通过以下两个方面去

1)判断该语句的瓶颈是IO还是CPU,到底是在数据访问上耗费了太多时间,还是在数据的运算(如分组排序)上花费太多功夫.

有了上面的初步判断,我们则可以利用MYSQL的命令工具去真正的定位出现瓶颈的地方,这个命令就是profiling,这个命令我会在后面进行介绍.

 

Explain 与 Profiling的应用

上面曾说过,对于判断一条QUERY的写法是否合理,MYSQL是有自己的一些方法的,而这个方法就是充分的利用EXPLAIN与Profiling这两条命令

Explain的主要作用是用于告诉我们,MYSQL在执行QUERY时,选择了一个什么样的执行计划,所以我们的优化过程中利用这个命令可以很方便查找我们当前QUERY语句在被MYSQL执行时,是否以一个最优化的一个方案去执行的,这篇文章,我只是作为一个抛砖引玉的部分,所以至于这条命令怎么使用,以及具体实例的判断,我在这里就不讲了,有机会再写一篇专门的文章来介绍.

Profiling命令与EXPLAIN不同,它给我们的信息是反映我们的QUERY在执行过程中,所以花费的CPU与IO等等,同时也显示它在MYSQL执行QUERY过程中,各个步骤所花费的时间.同样,在这里我也只提供方向,告诉大家有这么一个命令可以帮助大家来定位哪条QUERY语句耗费了太多的资源,从而进行优化.

 

第三:MYSQL常用日志介绍

MYSQL常用日志,包括,二进日制,查询日志,慢查询日志,错误日志

二进制日志(binary log),是MYSQL中最重要的日志之一,常用log-bin[=file_name]来打开,它以二进制的方式记录所有修改数据库数据的QUERY语句,它并不仅仅是只记录QUERY语句,它还包含了该条QUERY所执行的时间,消耗的资源,它常用来对数据的恢复,以及主从数据库之间的数据同步.

查询日志(query log),听到这个名字,我想大家应该很明白它的作用了,它最主要的作用是记录MYSQL执行的所有select语句,也许很多朋友认为它很有用,因为可以通过他,查看当前MYSQL系统中所有的查询记录,其实不然,如果开启他,必定造成对性能的很大影响,因为SELECT语句的体积很大,而且是很频繁,只有在某一段时间需要查找问题时,才会短暂的开启,开启他的方法是在my.ini配置文件中加入--log[file_name]

慢查询日志(slow query log):这个日志跟上面那个文件有什么区别呢,其实就是在这个slow上即慢,正如其名,该日志记录的内容是只记录慢于"多少秒"的查询语句,而至于记录慢多少秒,则为我们自行设定,设置这个时间的是log-query-time="**",当然前提是得开启该开日志,开启该日志的方式是--log-slow-queries=[file_name]

错误日志:该日志的作用也是用于记录MYSQL运行过程中所有较为严重的错误信息和严重警告以及关于每次MYSQL SERVER启动的详细信息,它默认也是不开启的,需要开启需要在MY.INI中以--log-err[=file_name]的方式来开启.

关于日志,其实大家看了这个名字也知道大概是知道什么意思,每个日志有每个日志的作用,而我们怎么利用这些日志才是关键,我这里只是提供了日志种类,并没说,怎么去分析这些日志,最实际的一个问题就是怎样去利用二进制日志来恢复一些数据库的数据,关于这个问题,我这也不详解了,网上有很多相关的说明,我这只是方向,方向,方向.

 

第四:数据库的模型设计

相信很多朋友在学校学习数据库设计时,都会提到该领域的一柄"尚方宝剑"即规范化范式理论,分别第一,二,三,四范式,一直到了第六范式设计,难道按照这种指导方式来设计数据库,就一定是最好的吗,就一定符合我们的业务需求吗,我看并不见得.

为什么这样说,大家先想想这套范式设计是产生于什么年代,对,是上世纪的七十年代,在那个年代,存储介质极其昂贵,为了节省那点空间,不得不用最小的数据来发挥最大的效用,而现在,硬件存储设备高速发展,而非那时所比.自然大小的限制已不在是问题.

范式设计,告诉我们,数据不能有一点的冗余,一定得有它的原子性,也就是一定得保证数据的一致性,而为了保证它的一致性,必须添加各种约束,而各种约束对于数据库本身来说就是一种非常消耗资源的事情.

所以,现在我们设计数据库,我们更应该从实际出发,更应该为业务需求而服务,保证数据库的查询速度,并发处理速度,所以在这里我提一下以下几个方向

1:适当的冗余

在很多时候,我们写程序,会发现一个问题,在页面上显示一条完整信息,其中百分之九十可以从一个表当中取出,而那百分之十的信息却必须从另外一个表取出,所以最后,为了显示完整的信息,要不就是不显示那百分之十的信息,要不就是JOIN表来取得那百分之十的信息,第一种情况,我估计你从你老板那里说不过去,所以考虑第二种吧,第二种方法,固然可行,但如果在你并发访问相当突出的时候,这样频繁的访问,锁表肯定会造成查询速度的降低.所以为了解决这个问题,我们是否可以考虑另外一个方法呢,即把那百分之十的信息也同时保存在那百分之九十所在表中呢,我觉得这样是可行的,只要这些数据不是经常改动,便可以这样做,这样就达到了,在显示信息时,可以只需要查询一个表即可.速度也自然也得到提升

其实,这种设计方式,我想在你们确定业务需求的时候,数据库的设计一定得同步起来,这样才会做到不减弱业务需求的情况下,同时又保证了数据库的性能.

2:表字段的垂直拆分

什么是表字段的垂直拆分呢:其实很简单,就是把相对应的字段移到另一个表,或者新建一个表,保存进去.

大家在设计数据库与写程序时,会发现在显示信息时,只需要取某个表中非常一部分的字段信息即可满足业务需求,那另一部分不需要的数据,只需要在很少的地方才需要用到,而且这些数据,在很多时候,往往都是大字段,如文章的内容,个人简介,等等,这时我们往往可以把它移动到另一个单独的表当中去.现在可能就有很多朋友就会问,我为什么需要把这些数据移出去,答案很简单,就是因为它大.

我之前就提过,数据库其实牵涉到最底层,即文件的操作,由于大字段存放的内容比较多,大部分情况下都会占整条记录的百分之八十甚至更多,而数据库在数据文件中一般都是以记录为单位进行保存的,也就是说,在查询某几个字段时,数据库并不会像你希望的那样只访问那几个字段,而是需要取出整条记录的内容,自然也会把这个大字段的内容取出,这样文件IO资源的浪费就相当大了

上面只提到那种大的字段,其实也还有一些字段可以一并移出去,就是那些不是经常使用的字段也可以一起和大字段同时移出,具体什么样的字段该移出,这个就得靠你自己的观察以及你们的业务需求了

3:大表的水平拆分
       其实这个与上面的垂直拆分是对应的,上面是把某些字段移出去,而这个便是把表里面特定的一些记录移出去

有时,在查询时,需要根据某个状态字段来区分不同的记录,而其中百分之九十里面的数据是归于一类,只有百分之十的数据归一类,这可能就出现问题了,当数据表记录上千万条的时候,去获得这百分之十的记录,其查询速度便会是很大的问题,而当获得这种百分之十的数据的操作又非常频繁的时候,问题就更明显了.所以此时,如果我们考虑把这些数据放到某一个单独的表中去,这时候对那百分之十数据的查询,速度就会达到量的提升,其实这个概念和分表差不多,通过某种规则把每条记录分散存放于不同的表当中,各表的数据量也就会相对的减少,这时查询速度也就会达到质的提升.

第四:就是准实时数据的保存

关于这个,很多网站都会有这样一种情况,在网站上显示统计性的数据,即根据一些条件来统计某一些资源的访问情况,如果每一次显示(也就是每一次刷新页面)都要根据某些条件来统计数据,这会带来大量的重复资源浪费,而做成准实时,每次只需要访问特定的访问数据,这样就不需要频繁的统计计算工作了(当然这建立在你们的项目经理允许的情况下).那哪些数据适合时实时呢,通过以下几点,可以粗略判断

1:统计信息的准确性不是特别严格

2:统计信息对时间并不是太敏感

再次就是统计信息的访问特别频繁

最后就是参与统计量较大.

其实最明显的一个事例就是论坛里面的贴子总数,我们在论坛里面发了一份贴子后,我想一般网站都不会非常及时的显示出这个正确的贴子数,因为统计这类数据需要经过全表扫描,对数据库的压力特别大,一般都会通过特定程序后台运行,更新总数记录,这样也就做到了数据的统计,同时也不会让用户体验变差.

 

上面说了几点关于数据库设计时需要注意的问题,其实在设计数据库时,还有很多其它需要注意的,如还有最明显的一条就是数据类型合理的选用,这个选用得好,可以节省很多的文件IO资源.其余具体的还得靠各位在自行实验中不断积累了.

 

第五:MYSQL表的读写分离

表的读写分离,也许很多朋友已经在不同场合听说这个说法了,其实这也是在我们开发中为了提高访问速度而提供的一种解决方案,为什么需要读写分离,我这里只粗略的总结一下

1:提高访问速度,我们知道,在数据记录被更新的时候,一般都会有表锁的操作(以myisam引擎为例),如果在高并发时,读写相当频繁,相对应的表锁也会相当的频繁,这样查询性能也就会因为表锁的问题造成性能的丢失.当我们把读写分开,这样分工明确,就不会造成因为表锁而查询表受到影响的问题了

2:数据库的备份,在读写分离时,我们必须有一个问题就是,主从机的搭建,当主库更新,从库也可以同步更新,这样也就会保证在主机数据丢失,起码从库数据还可以基本应对

 

 

上面提到了五个大的方面,其实对于MYSQL远远不止这些,还有很多,如MYSQL的权限设计,MYSQL的存储引擎之间的区别及优化,MYSQL的锁定机制,以及MYSQL的可扩展性设计,所以我这里算是一个抛砖引玉,提供一个方向,这篇文章可能对于那些有几年经验的朋友来说没有起不到什么作用,因为我这里面讲的东西还是有点泛,只是提供了一个方向,只是希望能让那些刚接触PHP不久,对于MYSQL应用有个大概的认识,当然同时也欢迎大家热烈讨论.