mysql优化详解.docx

mysql优化详解.docx

《mysql优化详解.docx》由会员分享，可在线阅读，更多相关《mysql优化详解.docx（25页珍藏版）》请在冰豆网上搜索。

mysql优化详解

我最近在网上查了一些有关优化MySql的资料，并对照MySql手册对一些调优设置进行了细结，但是由于时间比较勿忙，没来得及对这些设置进行实验/测试，你可以把这些方法应用到实际中，得出具体结论。

调优方法大致如下：

MySql服务器的后台管理程序，要想使用客户端程序，该程序必须运行，因为客户端通过连接服务器来访问数据库。

下面让我们以服务器的系统变量和状态变量为根据，优化我们的MySql数据库服务。

在这之前，我们需要掌握以下方法：

查看MySql状态及变量的方法：

Mysql>showstatus——显示状态信息（扩展showstatuslike'XXX'）

Mysql>showvariables——显示系统变量（扩展showvariableslike'XXX'）

Mysql>showinnodbstatus——显示InnoDB存储引擎的状态

Shell>mysqladminvariables-uusername-ppassword——显示系统变量

Shell>mysqladminextended-status-uusername-ppassword——显示状态信息

查看状态变量及帮助：

Shell>mysqld--verbose--help[|more#逐行显示]

首先，让我们看看有关请求连接的变量：

为了能适应更多数据库应用用户，MySql提供了连接（客户端）变量，以对不同性质的用户群体提供不同的解决方案，笔者就max_connections，back_log做了一些细结，如下：

max_connections是指MySql的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySql会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。

可以过'conn%'通配符查看当前状态的连接数量，以定夺该值的大小。

back_log是要求MySQL能有的连接数量。

当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间（尽管很短）检查连接并且启动一个新线程。

back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。

如果期望在一个短时间内有很多连接，你需要增加它。

也就是说，如果MySql的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。

另外，这值（back_log）限于您的操作系统对到来的TCP/IP连接的侦听队列的大小。

你的操作系统在这个队列大小上有它自己的限制（可以检查你的OS文档找出这个变量的最大值），试图设定back_log高于你的操作系统的限制将是无效的。

优化了MySql的连接后属性后，我们需要看看缓冲区变量：

使用MySql数据库存储大量数据（或使用复杂查询）时，我们应该考虑MySql的内存配置。

如果配置MySQL服务器使用太少的内存会导致性能不是最优的；如果配置了太多的内存则会导致崩溃，无法执行查询或者导致交换操作严重变慢。

在现在的32位平台下，仍有可能把所有的地址空间都用完，因此需要审视。

计算内存使用的秘诀公式就能相对地解决这一部分问题。

不过，如今这个公式已经很复杂了，更重要的是，通过它计算得到的值只是“理论可能”并不是真正消耗的值。

事实上，有8GB内存的常规服务器经常能运行到最大的理论值（100GB甚至更高）。

此外，你轻易不会使用到“超额因素”（它实际上依赖于应用以及配置）。

一些应用可能需要理论内存的10%而有些仅需1%。

那么，我们可以做什么呢？

来看看那些在启动时就需要分配并且总是存在的全局缓冲吧！

全局缓冲：

key_buffer_size,innodb_buffer_pool_size,innodb_additional_mem_pool_size，innodb_log_buffer_size,query_cache_size

注：

如果你大量地使用MyISAM表，那么你也可以增加操作系统的缓存空间使得MySQL也能用得着。

把这些也都加到操作系统和应用程序所需的内存值之中，可能需要增加32MB甚至更多的内存给MySQL服务器代码以及各种不同的小静态缓冲。

这些就是你需要考虑的在MySQL服务器启动时所需的内存。

其他剩下的内存用于连接。

key_buffer_size决定索引处理的速度，尤其是索引读的速度。

一般我们设为16M，通过检查状态值Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。

比例key_reads/key_read_requests应该尽可能的低，至少是1:

100，1:

1000更好（上述状态值可以使用'key_read%'获得用来显示状态数据）。

key_buffer_size只对MyISAM表起作用。

即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。

可以使用检查状态值'created_tmp_disk_tables'得知详情。

innodb_buffer_pool_size对于InnoDB表来说，作用就相当于key_buffer_size对于MyISAM表的作用一样。

InnoDB使用该参数指定大小的内存来缓冲数据和索引。

对于单独的MySQL数据库服务器，最大可以把该值设置成物理内存的80%。

innodb_additional_mem_pool_size指定InnoDB用来存储数据字典和其他内部数据结构的内存池大小。

缺省值是1M。

通常不用太大，只要够用就行，应该与表结构的复杂度有关系。

如果不够用，MySQL会在错误日志中写入一条警告信息。

innodb_log_buffer_size指定InnoDB用来存储日志数据的缓存大小，如果您的表操作中包含大量并发事务（或大规模事务），并且在事务提交前要求记录日志文件，请尽量调高此项值，以提高日志效率。

query_cache_size是MySql的查询缓冲大小。

（从4.0.1开始，MySQL提供了查询缓冲机制）使用查询缓冲，MySQL将SELECT语句和查询结果存放在缓冲区中，今后对于同样的SELECT语句（区分大小写），将直接从缓冲区中读取结果。

根据MySQL用户手册，使用查询缓冲最多可以达到238%的效率。

通过检查状态值’Qcache_%’，可以知道query_cache_size设置是否合理：

如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况，如果Qcache_hits的值也非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小；如果Qcache_hits的值不大，则表明你的查询重复率很低，这种情况下使用查询缓冲反而会影响效率，那么可以考虑不用查询缓冲。

此外，在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲。

除了全局缓冲，MySql还会为每个连接发放连接缓冲。

连接缓冲：

每个连接到MySQL服务器的线程都需要有自己的缓冲。

大概需要立刻分配256K，甚至在线程空闲时，它们使用默认的线程堆栈，网络缓存等。

事务开始之后，则需要增加更多的空间。

运行较小的查询可能仅给指定的线程增加少量的内存消耗，然而如果对数据表做复杂的操作例如扫描、排序或者需要临时表，则需分配大约read_buffer_size，sort_buffer_size，read_rnd_buffer_size，tmp_table_size大小的内存空间。

不过它们只是在需要的时候才分配，并且在那些操作做完之后就释放了。

有的是立刻分配成单独的组块。

tmp_table_size可能高达MySQL所能分配给这个操作的最大内存空间了。

注意，这里需要考虑的不只有一点——可能会分配多个同一种类型的缓存，例如用来处理子查询。

一些特殊的查询的内存使用量可能更大——如果在MyISAM表上做成批的插入时需要分配bulk_insert_buffer_size大小的内存；执行ALTERTABLE，OPTIMIZETABLE，REPAIRTABLE命令时需要分配myisam_sort_buffer_size大小的内存。

read_buffer_size是MySql读入缓冲区大小。

对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。

read_buffer_size变量控制这一缓冲区的大小。

如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。

sort_buffer_size是MySql执行排序使用的缓冲大小。

如果想要增加ORDERBY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。

如果不能，可以尝试增加sort_buffer_size变量的大小。

read_rnd_buffer_size是MySql的随机读缓冲区大小。

当按任意顺序读取行时（例如，按照排序顺序），将分配一个随机读缓存区。

进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。

但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。

tmp_table_size是MySql的heap（堆积）表缓冲大小。

所有联合在一个DML指令内完成，并且大多数联合甚至可以不用临时表即可以完成。

大多数临时表是基于内存的（HEAP）表。

具有大的记录长度的临时表（所有列的长度的和）或包含BLOB列的表存储在硬盘上。

如果某个内部heap（堆积）表大小超过tmp_table_size，MySQL可以根据需要自动将内存中的heap表改为基于硬盘的MyISAM表。

还可以通过设置tmp_table_size选项来增加临时表的大小。

也就是说，如果调高该值，MySql同时将增加heap表的大小，可达到提高联接查询速度的效果。

当我们设置好了缓冲区大小之后，再来看看：

table_cache所有线程打开的表的数目，增大该值可以增加mysqld需要的文件描述符的数量。

每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。

通过检查峰值时间的状态值’Open_tables’和’Opened_tables’，可以决定是否需要增加table_cache的值。

如果你发现open_tables等于table_cache，并且opened_tables在不断增长，那么你就需要增加table_cache的值了（上述状态值可以使用’Open%tables’获得）。

注意，不能盲目地把table_cache设置成很大的值。

如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。

做了以上方面的调优设置之后，MySql应该基本能满足您需求（当然是建立在调优设置适当的情况下），我们还应该了解并注意：

只有简单查询OLTP（联机事务处理）应用的内存消耗经常是使用默认缓冲的每个线程小于1MB，除非需要使用复杂的查询否则无需增加每个线程的缓冲大小。

使用1MB的缓冲来对10行记录进行排序和用16MB的缓冲基本是一样快的（实际上16MB可能会更慢，不过这是其他方面的事了）。

找出MySQL服务器内存消耗的峰值。

这很容易就能计算出操作系统所需的内存、文件缓存以及其他应用。

在32位环境下，还需要考虑到32位的限制，限制“mysqld”的值大约为2.5G（实际上还要考虑到很多其他因素）。

现在运行“psaux”命令来查看“VSZ”的值（MySQL进程分配的虚拟内存）。

监视着内存变化的值，就能知道是需要增加或减少当前的内存值了。

一、我们可以且应该优化什么？

硬件

操作系统/软件库

SQL服务器（设置和查询）

应用编程接口（API）

应用程序

--------------------------------------------------------------------------------

二、优化硬件

如果你需要庞大的数据库表（>2G），你应该考虑使用64位的硬件结构，像Alpha、Sparc或即将推出的IA64。

因为MySQL内部使用大量64位的整数，64位的CPU将提供更好的性能。

对大数据库，优化的次序一般是RAM、快速硬盘、CPU能力。

更多的内存通过将最常用的键码页面存放在内存中可以加速键码的更新。

如果不使用事务安全（transaction-safe）的表或有大表并且想避免长文件检查，一台UPS就能够在电源故障时让系统安全关闭。

对于数据库存放在一个专用服务器的系统，应该考虑1G的以太网。

延迟与吞吐量同样重要。

--------------------------------------------------------------------------------

三、优化磁盘

为系统、程序和临时文件配备一个专用磁盘，如果确是进行很多修改工作，将更新日志和事务日志放在专用磁盘上。

低寻道时间对数据库磁盘非常重要。

对与大表，你可以估计你将需要log（行数）/log（索引块长度/3*2/（键码长度+数据指针长度））+1次寻到才能找到一行。

对于有500000行的表，索引Mediunint类型的列，需要log（500000）/log（1024/3*2/（3+2））+1=4次寻道。

上述索引需要500000*7*3/2=5.2M的空间。

实际上，大多数块将被缓存，所以大概只需要1-2次寻道。

然而对于写入（如上），你将需要4次寻道请求来找到在哪里存放新键码，而且一般要2次寻道来更新索引并写入一行。

对于非常大的数据库，你的应用将受到磁盘寻道速度的限制，随着数据量的增加呈NlogN数据级递增。

将数据库和表分在不同的磁盘上。

在MySQL中，你可以为此而使用符号链接。

条列磁盘（RAID0）将提高读和写的吞吐量。

带镜像的条列（RAID0+1）将更安全并提高读取的吞吐量。

写入的吞吐量将有所降低。

不要对临时文件或可以很容易地重建的数据所在的磁盘使用镜像或RAID（除了RAID0）。

在Linux上，在引导时对磁盘使用命令hdparm-m16-d1以启用同时读写多个扇区和DMA功能。

这可以将响应时间提高5~50%。

在Linux上，用async（默认）和noatime挂载磁盘（mount）。

对于某些特定应用，可以对某些特定表使用内存磁盘，但通常不需要。

--------------------------------------------------------------------------------

四、优化操作系统

不要交换区。

如果内存不足，增加更多的内存或配置你的系统使用较少内存。

不要使用NFS磁盘（会有NFS锁定的问题）。

增加系统和MySQL服务器的打开文件数量。

（在safe_mysqld脚本中加入ulimit-n#）。

增加系统的进程和线程数量。

如果你有相对较少的大表，告诉文件系统不要将文件打碎在不同的磁道上（Solaris）。

使用支持大文件的文件系统（Solaris）。

选择使用哪种文件系统。

在Linux上的Reiserfs对于打开、读写都非常快。

文件检查只需几秒种。

--------------------------------------------------------------------------------

五、选择应用编程接口

PERL

可在不同的操作系统和数据库之间移植。

适宜快速原型。

应该使用DBI/DBD接口。

PHP

比PERL易学。

使用比PERL少的资源。

通过升级到PHP4可以获得更快的速度。

C

MySQL的原生接口。

较快并赋予更多的控制。

低层，所以必须付出更多。

C++

较高层次，给你更多的时间来编写应用。

仍在开发中

ODBC

运行在Windows和Unix上。

几乎可在不同的SQL服务器间移植。

较慢。

MyODBC只是简单的直通驱动程序，比用原生接口慢19%。

有很多方法做同样的事。

很难像很多ODBC驱动程序那样运行，在不同的领域还有不同的错误。

问题成堆。

Microsoft偶尔还会改变接口。

不明朗的未来。

（Microsoft更推崇OLE而非ODBC）

ODBC

运行在Windows和Unix上。

几乎可在不同的SQL服务器间移植。

较慢。

MyODBC只是简单的直通驱动程序，比用原生接口慢19%。

有很多方法做同样的事。

很难像很多ODBC驱动程序那样运行，在不同的领域还有不同的错误。

问题成堆。

Microsoft偶尔还会改变接口。

不明朗的未来。

（Microsoft更推崇OLE而非ODBC）

JDBC

理论上可在不同的操作系统何时据库间移植。

可以运行在web客户端。

Python和其他

可能不错，可我们不用它们。

--------------------------------------------------------------------------------

六、优化应用

应该集中精力解决问题。

在编写应用时，应该决定什么是最重要的：

    速度

    操作系统间的可移植性

    SQL服务器间的可移植性

使用持续的连接。

.

缓存应用中的数据以减少SQL服务器的负载。

不要查询应用中不需要的列。

不要使用SELECT*FROMtable_name...

测试应用的所有部分，但将大部分精力放在在可能最坏的合理的负载下的测试整体应用。

通过以一种模块化的方式进行，你应该能用一个快速“哑模块”替代找到的瓶颈，然后很容易地标出下一个瓶颈。

如果在一个批处理中进行大量修改，使用LOCKTABLES。

例如将多个UPDATES或DELETES集中在一起。

--------------------------------------------------------------------------------

七、应该使用可移植的应用

PerlDBI/DBD

ODBC

JDBC

Python（或其他有普遍SQL接口的语言）

你应该只使用存在于所有目的SQL服务器中或可以很容易地用其他构造模拟的SQL构造。

上的Crash-me页可以帮助你。

为操作系统/SQL服务器编写包装程序来提供缺少的功能。

--------------------------------------------------------------------------------

八、如果你需要更快的速度，你应该：

找出瓶颈（CPU、磁盘、内存、SQL服务器、操作系统、API或应用）并集中全力解决。

使用给予你更快速度/灵活性的扩展。

逐渐了解SQL服务器以便能为你的问题使用可能最快的SQL构造并避免瓶颈。

优化表布局和查询。

使用复制以获得更快的选择（select）速度。

如果你有一个慢速的网络连接数据库，使用压缩客户/服务器协议。

不要害怕时应用的第一个版本不能完美地移植，在你解决问题时，你总是可以在以后优化它。

--------------------------------------------------------------------------------

九、优化MySQL

挑选编译器和编译选项。

位你的系统寻找最好的启动选项。

通读MySQL参考手册并阅读PaulDuBios的《MySQL》一书。

（已有中文版-译注）

多用EXPLAINSELECT、SHOWVARIABLES、SHOWSTATUS和SHOWPROCESSLIST。

了解查询优化器的工作原理。

优化表的格式。

维护你的表（myisamchk、CHECKTABLE、OPTIMIZETABLE）

使用MySQL的扩展功能以让一切快速完成。

如果你注意到了你将在很多场合需要某些函数，编写MySQLUDF函数。

不要使用表级或列级的GRANT，除非你确实需要。

购买MySQL技术支持以帮助你解决问题:

）

--------------------------------------------------------------------------------

十、编译和安装MySQL

通过位你的系统挑选可能最好的编译器，你通常可以获得10-30%的性能提高。

在Linux/Intel平台上，用pgcc（gcc的奔腾芯片优化版）编译MySQL。

然而，二进制代码将只能运行在Intel奔腾CPU上。

对于一种特定的平台，使用MySQL参考手册上推荐的优化选项。

一般地，对特定CPU的原生编译器（如Sparc的SunWorkshop）应该比gcc提供更好的性能，但不总是这样。

用你将使用的字符集编译MySQL。

静态编译生成mysqld的执行文件（用--with-mysqld-ldflags=all-static）并用stripsql/mysqld整理最终的执行文件。

注意，既然MySQL不使用C++扩展，不带扩展支持编译MySQL将赢得巨大的性能提高。

如果操作系统支持原生线程，使用原生线程（而不用mit-pthreads）。

用MySQL基准测试来测试最终的二进制代码。

--------------------------------------------------------------------------------

十一、维护

如果可能，偶尔运行一下OPTIMIZEtable，这对大量更新的变长行非常重要。

偶尔用myisamchk-a更新一下表中的键码分布统计。

记住在做之前关掉MySQL。

如果有碎片文件，可能值得将所有文件复制到另一个磁盘上，清除原来的磁盘并拷回文件。

如果遇到问题，用myisamchk或CHECKtable检查表。

用mysqladmin-i10precesslistextended-status监控MySQL的状态。

用MySQLGUI客户程序，你可以在不同的窗口内监控进程列表和状态。

使用mysqladmindebug获得有关锁定和性能的信息。

--------------------------------------------------------------------------------

十二、优化SQL

扬SQL之长，其它事情交由应用去做。

使用SQL服务器来做：

    找出基于WHERE子句的行。

    JOIN表

    GROUPBY

    ORDERBY

    DISTINCT

不要使用SQL来做：

    检验数据（如日期）

    成为一只计算器

技巧：