MYSQL数据库性能调校优化.docxVIP

MYSQL性能调校常常出现在人们的口头禅，”便宜没好货”，让人错认为开源的MySQL数据库，其效能一定也不好。在我之前的经验中就常常发现，这样月晕效应现象让许多人懒得去对MySQL作效能调校，将其效能的慢，视为理因如此。但殊不知，那只是个认知上的错觉罢了。因此，逍遥子科技想在此跟大家介绍一下，MySQL效能调校的方法，但不免俗的要先来个”免责宣言”，以免你又落入了另一个迷失。首先，世上没有所谓的”大还丹”（吃下去可以让你增加一甲子的功力）。要知道，效能调校的心法就像是需要经年累月的苦练才可以练就的”九阳神功”一样，不可能一篇文章就可以让你成为效能调校的专家，也好像在玩在线游戏一般，你一直需要靠打怪来累积经验值。再来是，效能调校的技术中，也没有所谓的万灵药。也就是，今天你用了一个调校的方法达到出乎意料的效能提升，但并不表示在下个案例中你仍可以使用相同的方法，而得到相同的效果。所以，效能调校有输有赢，失败了别气馁，再试一次就好。如果你可以以常理心来面对效能调校，那我们就可以开始言归正传了！心法一：厘清数据库效能调校的瓶颈工程师们常常会低估效能调校的成果，但这还不是最惨的！最惨的是，老板们常常有个错觉，一旦进行数据库效能调校后，系统就可以像飞龙一样飞上天。这样无形的压力，也就是让工程师一直迟迟不敢多跨出一步的凶手。所以，为了自保，我们必须给老板一个正确的引导，让他们知道数据库效能调校的瓶颈在那里。首先，我们必须要先知道，数据库是运行在操作系统上，运算的处理能力是决定于中央处理器的运算能力，数据的快取速度决定在内存的速度及大小，而最终数据是存放在磁盘的档案中。”以上是废话吧”，大部分的人都会这样回答我。但我反问的是，你知道你的CPU每秒可以处理多少个指令集，存放在内存的快取每秒可以处理多少数据量，而每秒钟硬盘的I/O可以都取多少数据量。这些，很多人都答不出来，所以我也不勉强你也可以回答出来。最后，我只问你一个问题，那就是，你知道最能有效支持MySQL的操作系统是什么呢？而你又是用什么操作系统在执行MySQL？我相信，大多数的人没有办法厘清系统本身的效能极限在哪里，也就无法说服老板效能调校的预期结果。所以想着这个吃力不讨好的烫手山芋，还是丢给别人好了。但是，我却不这么认为，我反而觉得这是一个可以自我提升的好机会。在漫画”头文字D”中，藤元拓海加入了高桥凉介的Project D车队后，也因为了解了更多车子构造，引擎性能等，而成为更强的赛车手。所以，如果你真的无法驾驭你的跑车，看不出他的极速在哪里，至少你也要找台跟你一样的车，看看别人可以开多快，想办法追上他的后尾灯吧！心法二：SQL的调校关于SQL的调校，我从三个角度来切入介绍：1. SQL的慢查询：在逍遥子科技的项目经验来说，百分之九十效能的问题，在做完SQL语法的改善之后，便可以充分的解决。所以，如何收集系统中，较慢的SQL语法，便成为一个重要的课题。MySQL的预设设定中，并没有启用”慢查询”日志文件，所以，如果你要纪录系统中较慢的查询，你可以将下面两个设定加入到f中。1. log-slow-queries ##默认是关闭慢查询日志2.?long-qeury-time=5 ##预设是10秒在启用慢查询日志后，默认的日志文件位置及文件名是：[MySQL数据目录]/[主机名]-slow.log。2. SQL语法与储存引擎的选择：SQL语法和储存引擎看似没有太大的关联，但是我想针对全文检索的角度来探索这个问题。现今的系统开始被要求有超炫的显示画面与超强的查询能力，也因此，全文检索的查询，是系统中必备的功能。但在MySQL 5.6之前的版本，InnoDB有支持交易能力（ACID）而没有支持全文检索。所以，如果应用程序需要使用到全文检索的功能，就需要将数据库储存引擎换成MyISAM。这往往会让工程师进入天人交战的抉择。其实，还有另外一个小技巧可以解决这个问题，那就是使用”复合式”数据库储存引擎。也就是在需要全文检索的数据表中，使用MyISAM储存引擎，而需要支持交易的数据表，则使用InnoDB储存引擎。最后，在将两个数据表用一对一的方式关联起来，则你可以同时享用到MyISAM及InnoDB储存引擎的优点。3.?索引数量的控制：索引的目的就是在建立一个数据较小的索引树，当应用程序在查询数据时，可以先利用这颗树取得数据寻址，减少磁盘I/O，进而可以加速数据的存取。但是，用户仍然需要避免使用过多的索引。因为，MySQL优化器中，首先会利用符合条件的索引先试捞一段数据，并决定出读取最快的索引。所以，一旦索引的数目越多，花在优化的时间就越长，反而拉长查询的时间。此外，索引的数目越多，在新增数据或更新数据时，连带要更新的索引也越多，导致数据写入的时间变长，写入锁定（Write Lock）的时间也变长。但千