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、建立一个足够容纳数据表索引的独立的缓存。

SET GLOBAL member_cache.key_buffer_size = 1024*1024;

 

2、给数据表指定键缓存。

 

CACHE INDEX member IN member_cache;

 

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

 

LOAD INDEX INTO CACHE member;

 

如果你希望把其它的数据表载入同一个缓存中，或者为其它的数据表建立键缓存，上面的操作就足够了。

 

使用查询缓存

 

MySQL服务器可以使用查询缓存来提高那些重复执行的SELECT语句的处理速度。它对性能的提高通常都是惊人的。查询缓存的工作方式如下所示：

 

· 第一次执行某条SELECT语句的时候，服务器记住该查询的文本内容和它返回的结果。

· 服务器下一次碰到这个语句的时候，它不会再次执行该语句。作为代替，它直接从查询缓存中得到结果并把结果返回给客户端。

· 查询缓存是基于服务器所接收到的查询字符串的文本内容的。如果某些查询的文本完全相同，那些它就认为这些查询是相同的。如果某些查询的字符不同，或者来自那些使用了不同的字符集或通讯协议的客户端，那么它会认为这些查询是不同的。同样，如果某些查询采用其它的功能相当、但是实际上没有指向相同的数据表（例如引用了不同的数据库中的同名数据表），那么它们也是不同的。

· 当数据表被更新了之后，涉及到该数据表的任何缓存查询都变成无效的，并且会被丢弃。这可以防止服务器返回过期的结果。

 

在默认情况下，MySQL对查询缓存的支持是内建的。如果你不希望使用这种缓存，并且想避免它所导致的性能开销，可以使用--without-query-cache选项来运行配置脚本建立服务器。

 

如果需要检测某个服务器是否支持查询缓存，可以检查它的have_query_cache系统变量：

 

SHOW VARIABLES LIKE 'have_query_cache'

 

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

 

· query_cache_type决定查询缓存的操作模式。下表显示了可以使用的模式值：

模式 含义
0  不要缓存查询结果或检索缓存的结果。
1  缓存查询，除非它们以SELECT SQL_NO_CACHE开头。
2  根据需要只缓存那些以SELECT SQL_CACHE开头的查询。

· query_cache_size决定分配给缓存的内存数量，单位是字节。

· query_cache_limit设置被缓存的最大结果集大小；比这个值大的查询结果不会被缓存。

 

例如，为了激活查询缓存并为它分配16MB内存，在配置文件中使用下面的设置：

 

query_cache_type=1
query_cache_size=16M

 

即使query_cache_type的值设置为零，query_cache_size指定内存数量也会被分配。为了避免浪费内存，只有在希望激活缓存的时候才把大小设置成大于零。同时，即使query_cache_type不为零，查询缓存的大小设置为零也会禁用缓存。

 

使用了查询缓存的独立客户端会在服务器的默认缓存模式状态下操作。客户端可以使用下面的语句改变自己的查询的默认缓存模式：

 

SET query_cache_type = value;

 

其中的value可以是0、1或2，它的意义与设置服务器启动时的 query_cache_type变量的意义是相同的。在SET语句中，OFF、ON和DEMAND这些符号值与0、1和2对应。

 

客户端还可以通过在SELECT关键字后添加调节符来控制个别查询的缓存操作。如果缓存模式是ON或DEMAND，那么SELECT SQL_CACHE语句会让查询结果被缓存。SELECT SQL_NO_CACHE语句会使查询结果不被缓存。

如果某些查询从经常改变的数据表中检索信息，那么抑止对这些查询的缓存操作是有用的。在这种情况下，缓存未必有多大用处。假设你把Web服务器请求的日志存储在MySQL数据表中，同时周期性地运行该数据表上的一组统计查询。对于很繁忙的Web服务器来说，会频繁地出现新行插入该数据表的操作，因此该数据表的任何缓存了的查询结果很快就变成无效的了。其含义是，尽管你周期性地提交统计查询，但是查询缓存可能对这些查询没有什么价值。在这种情况下，最好使用SQL_NO_CACHE调节符告诉服务器不要缓存这些查询的结果。

 

硬件问题

 

本文前面的部分中讨论的帮助你提高服务器性能的技术是没有考虑硬件配置的。你当然可以通过使用更好的硬件来让服务器运行得更快。但是并非所有的与硬件相关的改变都有相同的价值。当我们评估哪些硬件提高了性能的时候，最重要的原则与调整服务器参数的原则是相同的：尽可能地把最多的信息放在最快的存储中，并让这些信息尽可能地保持在该存储中。

 

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

 

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

 

重新配置系统，如果你拥有足够的内存，能够在内存文件系统中执行全部的交换操作，那么就删除所有的磁盘交换设备。否则，即使你拥有足够的用于交换操作的RAM，某些系统仍然会跟磁盘进行交换操作。

 

增加更快的磁盘以改善I/O等待时间。在这种情况下，寻道时间是有代表性的主要的性能决定因素。横向移动磁头的速度比较慢，在磁头定位以后，从磁道上读取信息块的速度相对较快。但是，如果需要选择是添加更多的内存还是更快的磁盘，那么最好选择添加更多的内存。内存总是比磁盘快，而且添加内存可以让你使用更大的缓存，从而减少磁盘活动。

 

通过在物理设备上划分磁盘活动来获取并行操作的优势。如果你可以在多个物理设备上划分读操作和写操作，那么其速度就会比从同一个设备读写要快一些。例如，如果你把数据库存储在一个设备上，把日志存储在另一个设备上，那么同时向两个设备写入信息的速度就比数据库和日志共享同一个设备的速度要快。请注意，使用同一个物理设备上的不同分区不算是并行操作。这是没有好处的，因为它们仍然需要争用相同的物理资源（磁头）。

 

在把数据重新部署到另外一个设备之前，你要确保自己知道系统的负载状况。如果在某个特定的物理设备上正在运行一些重要的业务，那么把数据库放在该设备上有可能使性能更差。例如，如果你正在处理大量的Web业务，同时把数据库移动到Web服务器文档目录所在的设备上，就可能感觉不到任何优势。

 

使用RAID设备可以让你获取并行操作的优势。

 

使用多处理器硬件。对于类似MySQL服务器的多线程应用程序来说，多处理器硬件可以同时执行多个线程。