MySQL查询优化讲座之管理员的优化办法[MySQL防范]

　前面的部份中讲授的优化办法都是没有特权的MySQL用户可以履行的.可以掌握MySQL服务器或计算机的系统管理员可以履行额外的优化办法.比方,有些服务器参数从属于查询处理历程,并且是可以调整的,并且某些硬件配置因素对查询处理速度有直接的影响.在很多情形下,这些优化办法提高了整个服务器的性能,因此可以让全部的MySQL用户都受益.

　　普通来说,当你履行管理员优化的时刻,应当紧记以下法则:

　　· 拜候内存中的数据快于拜候磁盘上的数据.

　　· 尽大概把数据保存在内存中可以削减磁盘操作.

　　· 保存索引中的信息比保存数据记录的内容更重要.

　　我们在背面将谈论若何利用这些法则.

　　增添服务器缓存的大小.服务器拥有很多参数（系统变量）,你可以改变这些参数来影响服务器的操作.此中的几个参数直接地影响查询处理的速度.你可以改变的最重要的参数是数据表缓存的大小和存储引擎用于缓冲索引操作信息的缓存大小.假如你拥有可用的内存,就把它分配给服务器的缓存,以答应信息存储在内存中并削减磁盘操作.这会有很好的效果,因为拜候内存中的信息比从磁盘读失信息的速度快得多.

　　· 当服务器翻开表文件的时刻,它试图保持这些文件的翻开状况,以削减翻开文件操作的数目.为了实现这样的功效,它在表缓存中保护翻开文件的信息.table_cache系统变量掌握着这个缓存的大小.假如服务器拜候了大量的表,表缓存就会被填满,并且服务器会关闭那些有一段时间没有利用的表,为翻开新表留出空间.你可以通过查抄Opened_tables状况指导器来拜候表缓存的效果:

SHOW STATUS LIKE ’Opened_tables’;

　　Opened_tables显示了某个数据表必须翻开的次数（因为它还没有翻开）.这个值也显示为mysqladmin状况号令的输出信息中的Opens值.假如这个数字是安定的或迟钝增长,那么它的设置大概是精确的.假如这个数字增长得很快,就意味着这个缓存太小了,必须常常关闭数据表来为翻开别的的数据表留出空间.假如你拥有文件描写信息,增添表缓存大小将削减数据表翻开操作的数目.

　　· MyISAM存储引擎利用键缓冲来保持与索引相关的操作的索引信息块.它的大小是由key_buffer_size系统变量掌握的.这个值越大,MySQL就一次性在内存中保持更多的索引信息块,可以增添在内存中（而不用从磁盘上读取新的信息块）找到键值的大概性.键缓存的默许大小是8MB.假如你拥有很多的内存,这是一个很保守的值,你可以直接增添它的大小,并且会看到基于索引的检索、索引的成立和改正操作的性能有很大改进.

　　在MySQL 4.1以上版本中,你可认为MyISAM数据表成立附加的键缓存,并指定某些表利用它们.这样可以帮忙提高这些数据表上的查询处理速度.

　　· InnoDB和BDB引擎拥有自己的用于缓冲数据和索引值的缓存.它们的大小是由innodb_buffer_pool_size和bdb_cache_size变量掌握的.InnoDB引擎还保护了一个日记缓冲.innodb_log_buffer_size变量可以掌握它的大小.

　　· 另一个专用的缓存是查询缓存,我们在"利用查询缓存"部份中注释.

　　当你改变这些参数值的时刻,应当遵守下面一些原则:

　　· 每次只改变一个参数.假如你一次改变多个彼此独立的变量,那么就很难评价每种改变的效果了.

　　· 渐渐地增添系统变量值.按照理论,数目越多,性能越好,但是假如你使某个变量变得太大了,有大概造成系统资源匮乏,招致逆向效果,降低速度.

　　· 不要在运行业务MySQL数据库的服务器上做调整参数的实行,最好成立一个独立的测试服务器.

　　· 为了大致理解哪类参数变量大概合适自己的系统,你可以查看MySQL公布文档中包含的my-small.cnf、my-medium.cnf、my-large.cnf和my-huge.cnf选项文件（在Unix系统上,你可以在源公布文件的支持文件目录和二进制公布文件的同享目录总找到这些文件.在Windows上,它们位于基本的安装目录中,其扩大名大概是.ini）.这些文件大概让你知道最好改变服务器上的那些参数以适应差别的利用层次,并且为这些参数供应了一些典型值.

　　用于提高服务器的操作性能的别的一些战略还包含:

　　禁止不需求的存储引擎.服务器不会为禁止的引擎分配任何内存,因此我们可以操纵这一点.假如从源文件成立MySQL,那么在配置的时刻,大大都存储引擎便可以被解除在服务器之外.关于那些包含在服务器中的引擎来说,利用得当的启动选项可以在运行时禁止此中的大大都.
保持受权表答应的简单性.固然服务器在内存中缓存了受权表内容,但是假如你在tables_priv或columns_priv表中有一些数据行的话,服务器就必须为每个查询语句查抄表层次和列层次的权限.假如这些表是空的,那么服务器就可以优化自己的权限查抄历程,略过这些层次.

　　假如你从源文件成立MySQL,那么就把它配置为利用静态类库,而不要利用同享类库.利用同享类库的动态二进制文件节俭磁盘空间,但是静态二进制文件速度更快.但是,假如你利用了用户自定义函数（UDF）机制,那么有些系统要求利用动态链接.在这类系统上,静态二进制文件不能工作.

　　利用MyISAM键缓存

　　当MySQL履行某个操纵了MyISAM数据表索引的语句的时刻,它会利用键缓存来保持索引值.这种缓存削减了磁盘I/O:假如在缓存中找到了某个数据表需求的键值,就不需求再次从磁盘中读取.不幸的是,这种键缓存是有限的,并且在默许情形下,它是全部的MyISAM数据表同享利用的.假如在键缓存中没有找到键值并且键缓存是满的,争用将会招致:必须丢弃缓存中的某些值,为新值留出空间.假以下次需求那些已经被丢弃的值,就必须再次从磁盘上读取.

　　假如你很倚重MyISAM数据表,那么把它的键保存在内存中效果会很好,但是缓存中的争用却会招致相反的效果.从同一张表或差别的表读取数据都大概惹起争用.你可以通过把键缓存设置成足以保存某个特定数据表的全部索引,从而避免同一张数据表的争用,但是别的数据表的键仍旧需求争用缓存空间.

　　MySQL 4.1以上版本为这个问题供应了一种办理筹划:它支持我们成立多个键缓存,并答应我们把某张数据表的索引指定并且预先装入某个缓存.假如你的数据表利用得很频繁,并且你有充足的内存,可以把它的索引载入缓存中,那么这种操作就是有效的.这种本领答应你同时避免同一张表和差别的表的争用:成立一个充足大的缓存,让它保存数据表的全部索引,并且指定该缓存专门用于那张数据表.在键被载入缓存之后,不在需求磁盘I/O操作.同时,键值永久不会被丢弃,对数据表的键的查看操作可以在内存中完成.

　　下面的例子显示了如作甚sampdb数据库的member数据表成立一个键缓存,该缓存的名称是member_cache,大小为1MB.履行这些指令的时刻,你必须有超级（SUPER）权限.

　　1.成立一个充足包容数据表索引的独立的缓存:

mysql> SET GLOBAL member_cache.key_buffer_size = 1024*1024;

　　2.给数据表指定键缓存:

mysql> CACHE INDEX member IN member_cache;
+---------------+--------------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------+--------------------+----------+----------+
| sampdb.member | assign_to_keycache | status | OK |
+---------------+--------------------+----------+----------+

　　3.把数据表索引预先读入它的键缓存中:

mysql> LOAD INDEX INTO CACHE member;
+---------------+--------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------+--------------+----------+----------+
| sampdb.member | preload_keys | status | OK |+---------------+--------------+----------+----------+

　　假如你但愿把别的的数据表载入同一个缓存中,大概为别的的数据表成立键缓存,上面的操作就充足了.

[1] [2] [3] 下一页  

利用查询缓存

　　MySQL服务器可以利用查询缓存来提高那些反复履行的SELECT语句的处理速度.它对性能的提高普通都是惊人的.查询缓存的工作方法以下所示:

　　· 第一次履行某条SELECT语句的时刻,服务器记着该查询的文本内容和它返回的后果.

　　· 服务器下一次碰到这个语句的时刻,它不会再次履行该语句.作为替换,它直接从查询缓存中的得到后果并把后果返回给客户端.

　　· 查询缓存是基于服务器所接纳到的查询字符串的文本内容的.假如某些查询的文本完好相同,那些它就认为这些查询是相同的.假如某些查询的字符差别,大概来自那些利用了差别的字符集或通讯协议的客户端,那么它会认为这些查询是差别的.一样,假如某些查询采取别的的功效相当、但是实际上没有指向相同的数据表（比方引用了差别的数据库中的同名数据表）,那么它们也是差别的.

　　· 当数据表被更新了之后,触及到该数据表的任何缓存查询都变成无效的,并且会被丢弃.这可以避免服务器返回过期的后果.

　　在默许情形下,MySQL对查询缓存的支持是内建的.假如你不但愿利用这种缓存,并且想避免它所招致的性能开销,可以利用--without-query-cache选项来运行配置脚本成立服务器.

　　假如需求检测某个服务器能否支持查询缓存,可以查抄它的have_query_cache系统变量:

mysql> SHOW VARIABLES LIKE ’have_query_cache’;
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+

　　关于那些支持查询缓存的服务器来说,缓存的操作是基于三个系统变量值的:

　　· query_cache_type决意查询缓存的操作情势.下表显示了可以利用的情势值:

上一页  [1] [2] [3] 下一页  

硬件问题

　　本文前面的部份中谈论的帮忙你提高服务器性能的技术是没有考虑硬件配置的.你当然可以通过利用更好的硬件来让服务器运行地更快.但是并非全部的与硬件相关的改变都有相同的代价.当我们评价哪些硬件提高了性能的时刻,最重要的原则与调整服务器参数的原则是相同的:尽大概地把最多的信息放在最快的存储中,并让这些信息尽大概地保持在该存储中.

　　你可以改变几种硬件配置来晋升服务器的性能:

　　在计算机上安装更多的内存.这可以让你把服务器的缓存和缓冲区大小值配置成更大的,从而使数据保存在内存中的时间更长,从磁盘上读失信息的需求更少.

　　重新配置系统,假如你拥有充足的内存,可以在内存文件系统中履行全部的交换操作,那么就删除全部的磁盘交换设备.不然,即便你拥有充足的用于交换操作的RAM,某些系统仍旧会跟磁盘举行交换操作.

　　增添更快的磁盘以改进I/O等候时间.在这种情形下,寻道时间是有代表性的主要的性能决意因素.横向移动磁头的速度对比慢,在磁头定位今后,从磁道上读失信息块的速度相对较快.但是,假如需求挑选是增添更多的内存还是更快的磁盘,那么最好挑选增添更多的内存.内存老是比磁盘快,并且增添内存可以让你利用更大的缓存,从而削减磁盘活动.

　　通过在物理设备上划分磁盘活动来获得并行操作的上风.假如你可以在多个物理设备上划分读操作和写操作,那么其速度就会比从同一个设备读写要快一些.比方,假如你把数据库存储在一个设备上,把日记存储在另一个设备上,那么同时向两个设备写入信息的速度就比数据库和日记同享同一个设备的速度要快.请注意,利用同一个物理设备上的差别分区不算是并行操作.这是没有好处的,因为它们仍旧需求争用相同的物理资源（磁头）.

　　在把数据重新布置到别的一个设备之前,你要确保自己知道系统的负载情况.假如在某个特定的物理设备上正在运行一些重要的业务,那么把数据库放在该设备上有大概使性能更差.比方,假如你正在处理大量的Web业务,同时把数据库移动到Web服务器文档目录所在的设备上,便大概感受不到任何上风.

　　利用RAID设备可以让你获得并行操作的上风.

　　利用多处理器硬件.关于近似MySQL服务器的多线程利用程序来说,多处理器硬件可以同时履行多个线程.

上一页  [1] [2] [3]