MySQL数据库优化.docx

MySQL数据库优化.docx

《MySQL数据库优化.docx》由会员分享，可在线阅读，更多相关《MySQL数据库优化.docx（60页珍藏版）》请在冰豆网上搜索。

MySQL数据库优化

MySQL数据库优化

（一）

作者:

叶金荣,　出处:

IT专家网,　责任编辑:

李书琴,　

2008-06-0609:

30

　　数据库优化是一项很复杂的工作，因为这最终需要对系统优化的很好理解才行。

尽管对系统或应用系统的了解不多的情况下优化效果还不错，但是如果想优化的效果更好，那么就需要对它了解更多才行。

　　数据库优化是一项很复杂的工作，因为这最终需要对系统优化的很好理解才行。

尽管对系统或应用系统的了解不多的情况下优化效果还不错，但是如果想优化的效果更好，那么就需要对它了解更多才行。

　　本章主要讲解了几种优化MySQL的方法，并且给出了例子。

记着，总有各种办法能让系统运行的更快，当然了，这需要更多的努力。

　　1优化概述

　　让系统运行得快得最重要因素是数据库基本的设计。

并且还必须清楚您的系统要用来做什么，以及存在的瓶颈。

　　最常见的系统瓶颈有以下几种：

　　磁盘搜索。

它慢慢地在磁盘中搜索数据块。

对现代磁盘来说，平时的搜索时间基本上小于10毫秒，因此理论上每秒钟可以做100次磁盘搜索。

这个时间对于全新的新磁盘来说提高的不多，并且对于只有一个表的情况也是如此。

加快搜索时间的方法是将数据分开存放到多个磁盘中。

　　磁盘读/写。

当磁盘在正确的位置上时，就需要读取数据。

对现代磁盘来说，磁盘吞吐量至少是10-20MB/秒。

这比磁盘搜索的优化更容易，因为可以从多个媒介中并行地读取数据。

　　CPU周期。

数据存储在主内存中（或者它已经在主内存中了），这就需要处理这些数据以得到想要的结果。

存在多个?

硐啾饶诖嫒萘坷此蹈窍拗频囊蛩亍2还孕"砝此担俣韧ǔ2皇俏侍狻?

　　内存带宽。

当CPU要将更多的数据存放在CPU缓存中时，主内存的带宽就是瓶颈了。

在大多数系统中，这不是常见的瓶颈，不过也是要注意的一个因素。

　　1.1MySQL设计的局限性

　　当使用MyISAM存储引擎时，MySQL会使用一个快速数据表锁以允许同时多个读取和一个写入。

这种存储引擎的最大问题是发生在一个单一的表上同时做稳定的更新操作及慢速查询。

如果这种情况在某个表中存在，可以使用另一种表类型。

详情请看"15MySQLStorageEnginesandTableTypes"。

　　MySQL可以同时在事务及非事务表下工作。

为了能够平滑的使用非事务表（发生错误时不能回滚），有以下几条规则：

∙　　所有的字段都有默认值

∙　　如果字段中插入了一个"错误"的值，比如在数字类型字段中插入过大数值，那么MySQL会将该字段值置为"最可能的值"而不是给出一个错误。

数字类型的值是0，最小或者最大的可能值。

字符串类型，不是空字符串就是字段所能存储的最大长度。

∙　　所有的计算表达式都会返回一个值而报告条件错误，例如1/0返回NULL。

　　这些规则隐含的意思是，不能使用MySQL来检查字段内容。

相反地，必须在存储到数据库前在应用程序中来检查。

详情请看"1.8.6HowMySQLDealswithConstraints和"14.1.4INSERTSyntax"。

1.2应用设计的可移植性

　　由于各种不同的数据库实现了各自的SQL标准，这就需要我们尽量使用可移植的SQL应用。

查询和插入操作很容易就能做到可移植，不过由于更多的约束条件的要求就越发困难。

想要让一个应用在各种数据库系统上快速运行，就变得更困难了。

　　为了能让一个复杂的应用做到可移植，就要先看这个应用运行于哪种数据库系统之上，然后看这些数据库系统都支持哪些特性。

　　每个数据库系统都有某些不足。

也就是说，由于设计上的一些妥协，导致了性能上的差异。

　　可以用MySQL的crash-me程序来看选定的数据库服务器上可以使用的函数，类型，限制等。

crash-me不会检查各种可能存在的特性，不过这仍然是合乎情理的理解，大约做了450次测试。

　　一个crash-me的信息类型的例子就是，它会告诉您如果想使用Informix或DB2的话，就不能使字段名长度超过18个字符。

　　crash-me程序和MySQL基准使每个准数据库都实现了的。

可以通过阅读这些基准程序是怎么写的，自己就大概有怎样做才能让程序独立于各种数据库这方面的想法了。

这些程序可以在MySQL源代码的`sql-bench'目录下找到。

他们大部分都是用Perl写的，并且使用DBI接口。

由于它提供了独立于数据库的各种访问方式，因此用DBI来解决各种移植性的问题。

　　想要看到crash-me的结果，可以访问：

访问可以看到基准的结果。

　　如果您想努力做到独立于数据库，这就需要对各种SQL服务器的瓶颈都有一些很好的想法。

例如，MySQL对于MyISAM类型的表在检索以及更新记录时非常快，但是在有并发的慢速读取及写入记录时却有一定的问题。

作为Oracle来说，它在访问刚刚被更新的记录时有很大的问题（直到结果被刷新到磁盘中）。

事务数据库一般地在从日志表中生成摘要表这方面的表现不怎么好，因为在这种情况下，行记录锁几乎没用。

　　为了能让应用程序真正的做到独立于数据库，就必须把操作数据的接口定义的简单且可扩展。

由于C++在很多系统上都可以使用，因此使用C++作为数据库的基类结果很合适。

　　如果使用了某些数据库独有的特定功能（比如REPLACE语句就只在MySQL中独有），这就需要通过编写替代方法来在其他数据库中实现这个功能。

尽管这些替代方法可能会比较慢，但是它能让其他数据库实现同样的功能。

　　在MySQL中，可以在查询语句中使用/*!

*/语法来增加MySQL特有的关键字。

然而在很多其他数据库中，/**/却被当成了注释（并且被忽略）。

　　如果有时候更高的性能比数据结果的精确更重要，就像在一些Web应用中那样，这可以使用一个应用层来缓存结果，这可能会有更高的性能。

通过让旧数据在一定时间后过期，来合理的更新缓存。

这是处理负载高峰期时的一种方法，这种情况下，可以通过加大缓存容量和过期时间直到负载趋于正常。

　　这种情况下，建表信息中就要包含了初始化缓存的容量以及正常刷新数据表的频率。

　　一个实现应用层缓存的可选方案是使用MySQL的查询缓存（querycache）。

启用查询缓存后，数据库就会根据一些详情来决定哪些结果可以被重用。

它大大简化了应用程序，详情请看"5.11TheMySQLQueryCache"。

1.3我们都用MySQL来做什么

　　本章描述了一个MySQL的早期应用。

　　在MySQL最开始的开发过程中，MySQL本来是要准备给大客户用的，他们是瑞典的2个最大的零售商，他们用于货物存储数据管理。

　　我们每周从所有的商店中得到交易利润累计结果，以此给商店的老板提供有用的信息，帮助他们分析如果更好的打广告以影响他们的客户。

　　数据量相当的大（每个月的交易累计结果大概有7百万），而且还需要显示4-10年间的数据。

我们每周都得到客户的需求，他们要求能‘瞬间’地得到数据的最新报表。

　　我们把每个月的全部信息存储在一个压缩的‘交易’表中以解决这个问题。

我们有一些简单的宏指令集，它们能根据不同的标准从存储的‘交易’表中根据字段分组（产品组、客户id、商店等等）取得结果。

我们用一个小Perl脚本动态的生成Web页面形式的报表。

这个脚本解析Web页面，执行SQL语句，并且插入结果。

我们还可以用PHP或者mod_perl来做这个工作，不过当时还没有这2个工具。

　　为了得到图形数据，我们还写了一个简单的C语言工具，用于执行SQL查询并且将结果做成GIF图片。

这个工具同样是Perl脚本解析Web页面后动态执行的。

　　很多情况下，只要拷贝现有的脚本简单的修改里面的SQL查询语句就能产生新的报表了。

有时候，就需要在现存的累计表中增加更多的字段或者新建一个。

这个操作十分简单，因为我们在磁盘上存储有所有的交易表（总共大概有50G的交易表以及20G的其他客户资料）。

　　我们还允许客户通过ODBC直接访问累计表，这样的话，那些高级用户就可以自己利用这些数据做试验了。

　　这个系统工作的很好，并且在适度的SunUltraSPARC工作站（2x200MHz）上处理数据没有任何问题。

最终这个系统移植到了Linux上。

　　1.4MySQL基准套件

　　本章本来要包括MySQL基准套件（以及crash-me）的技术描述的，但是至今还未写。

现在，您可以通过查看MySQL发布源代码`sql-bench'目录下的代码以及结果有一个更好的想法。

　　基准套件就是想告诉用户执行什么样的SQL查询表现的更好或者更差。

　　请注意，这个基准是单线程的，因此它度量了操作执行的最少时间。

我们未来打算增加多线程测试的基准套件。

　　想要使用基准套件，必备以下几个条件：

　　基准套件在MySQL的发布源代码中就有。

可以去下载发布版或者使用现有开发代码树（详情请看"2.3.3InstallingfromtheDevelopmentSourceTree"）。

　　基准脚本是用Perl写的，它用Perl的DBI模块来连接数据库，因此必须安装DBI模块。

并且还需要每个要做测试的服务器上都有特定的BDB驱动程序。

例如，为了测试MySQL、PostgreSQL和DB2，就必须安装DBD:

:

mysql,DBD:

:

Pg及DBD:

:

DB2模块。

详情请看"2.7PerlInstallationNote"。

取得MySQL的分发源代码后，就能在`sql-bench'目录下看到基准套件。

想要运行这些基准测试，请先搭建好服务，然后进入`sql-bench'目录，执行run-all-tests脚本：

shell>cdsql-bench

shell>perlrun-all-tests--server=server_name

　　server_name可以是任何一个可用的服务。

想要列出所有的可用选项和支持的服务，只要调用以下命令：

shell>perlrun-all-tests--help

　　crash-me脚本也是放在`sql-bench'目录下。

crash-me通过执行真正的查询以试图判断数据库都支持什么特性、性能表现以及限制。

例如，它可以判断：

∙　　都支持什么字段类型

∙　　支持多少索引

∙　　支持什么样的函数

∙　　能支持多大的查询

∙　　VARCHAR字段类型能支持多大

　　可以从上找到各种不同数据库crash-me的结果。

更多的信息请访问

　　1.5使用您自己的基准

　　请确定对您的数据库或者应用程序做基准测试，以发现它们的瓶颈所在。

解决这个瓶颈（或者使用一个假的模块来代替）之后，就能很容易地找到下一个瓶颈了。

即使应用程序当前总体的表现可以接受，不过还是至少要做好找到每个瓶颈的计划，说不定某天您就希望应用程序能有更好的性能。

　　从MySQL的基准套件中就能找到一个便携可移植的基准测试程序了。

详情请看"7.1.4TheMySQLBenchmarkSuite"。

您可以从基准套件中的任何一个程序，做适当的修改以适合您的需要。

通过整个方式，您就可以有各种不同的办法来解决问题，知道哪个程序才是最快的。

　　另一个基准套件是开放源码的数据库基准，可以在上找到。

　　当系统负载十分繁重的时候，通常就会发生问题。

我们就有很多客户联系我们说他们有一个（测试过的）生产系统也遭遇了负载问题。

在很多情况下，性能问题归结于数据库的基本设计（例如，在高负载下扫描数据表的表现不好）、操作系统、或者程序库等因素。

很多时候，这些问题在还没有正式用于生产前相对更容易解决。

　　为了避免发生这样的问题，最好让您的应用程序在可能的最差的负载下做基准测试!

可以使用SuperSmack，在可以找到。

从它名字的意思就能想到，只要您愿意，它就能让您的系统死掉，因此确认只在开发系统上做测试。

2优化SELECT语句及其他查询

　　首先，影响所有语句的一个因素是：

您的权限设置越复杂，那么开销就越大。

　　使用比较简单的GRANT语句能让MySQL减少在客户端执行语句时权限检查的开销。

例如，如果没有设定任何表级或者字段级的权限，那么服务器就无需检查tables_priv和columns_priv表的记录了。

同样地，如果没有对帐户设定任何资源限制的话，那么服务器也就无需做资源使用统计了。

如果有大量查询的话，花点时间来规划简单的授权机制以减少服务器权限检查的开销是值得的。

　　如果问题处在一些MySQL特定的表达式或者函数上，则可以通过mysql客户端程序使用BENCHMARK（）函数做一个定时测试。

它的语法是：

BENCHMARK（loop_count,expression）。

例如：

mysql>SELECTBENCHMARK（1000000,1+1）;

+------------------------+

|BENCHMARK（1000000,1+1）|

+------------------------+

|                     0|

+------------------------+

1rowinset（0.32sec）

　　上述结果是在PentiumII400MHz的系统上执行得到的。

它告诉我们：

MySQL在这个系统上可以在0.32秒内执行1,000,000次简单的加法运算。

　　所有的MySQL函数都应该被最优化，不过仍然有些函数例外。

BENCHMARK（）是一个用于检查查询语句中是否存在问题的非常好的工具。

MySQL数据库优化

（二）

作者:

叶金荣,　出处:

IT专家网,　责任编辑:

李书琴,　

2008-06-1109:

55

　　EXPLAIN语句可以被当作DESCRIBE的同义词来用，也可以用来获取一个MySQL要执行的SELECT语句的相关信息。

EXPLAINtbl_name语法和DESCRIBEtbl_name或SHOWCOLUMNSFROMtbl_name一样。

　　MySQL数据库优化

（一）　

　　1.EXPLAIN语法（得到SELECT的相关信息）　

EXPLAIN tbl_name

　　或者:

EXPLAIN SELECT select_options

　　tableIN语句可以被当作DESCRIBE的同义词来用，也可以用来获取一个MySQL要执行的SELECT语句的相关信息。

EXPLAINtbl_name语法和DESCRIBEtbl_name或SHOWCOLUMNSFROMtbl_name一样。

当在一个SELECT语句前使用关键字EXPLAIN时，MYSQL会解释了即将如何运行该SELECT语句，它显示了表如何连接、连接的顺序等信息。

本章节主要讲述了第二种EXPLAIN用法。

　　在EXPLAIN的帮助下，您就知道什么时候该给表添加索引，以使用索引来查找记录从而让SELECT运行更快。

　　如果由于不恰当使用索引而引起一些问题的话，可以运行ANALYZETABLE来更新该表的统计信息，例如键的基数，它能帮您在优化方面做出更好的选择。

　　您还可以查看优化程序是否以最佳的顺序来连接数据表。

为了让优化程序按照SELECT语句中的表名的顺序做连接，可以在查询的开始使用SELECTSTRAIGHT_JOIN而不只是SELECT。

　　EXPLAIN返回了一行记录，它包括了SELECT语句中用到的各个表的信息。

这些表在结果中按照MySQL即将执行的查询中读取的顺序列出来。

MySQL用一次扫描多次连接（single-sweep,multi-join）的方法来解决连接。

这意味着MySQL从第一个表中读取一条记录，然后在第二个表中查找到对应的记录，然后在第三个表中查找，依次类推。

当所有的表都扫描完了，它输出选择的字段并且回溯所有的表，直到找不到为止，因为有的表中可能有多条匹配的记录下一条记录将从该表读取，再从下一个表开始继续处理。

　　在MySQLversion4.1中，EXPLAIN输出的结果格式改变了，使得它更适合例如UNION语句、子查询以及派生表的结构。

更令人注意的是，它新增了2个字段：

id和select_type。

当你使用早于MySQL4.1的版本就看不到这些字段了。

　　EXPLAIN结果的每行记录显示了每个表的相关信息，每行记录都包含以下几个字段：

　　id

　　本次SELECT的标识符。

在查询中每个SELECT都有一个顺序的数值。

select_type

　　SELECT的类型，可能会有以下几种：

∙　　SIMPLE

　　简单的SELECT（没有使用UNION或子查询）

∙　　PRIMARY

　　最外层的SELECT。

∙　　UNION

　　第二层，在SELECT之后使用了UNION。

∙　　DEPENDENTUNION

　　UNION语句中的第二个SELECT，依赖于外部子查询

∙　　SUBQUERY

　　子查询中的第一个SELECT

∙　　DEPENDENTSUBQUERY

　　子查询中的第一个SUBQUERY依赖于外部的子查询

∙　　DERIVED

　　派生表SELECT（FROM子句中的子查询）

　　table

　　记录查询引用的表。

　　type

　　表连接类型。

以下列出了各种不同类型的表连接，依次是从最好的到最差的：

∙　　system

　　表只有一行记录（等于系统表）。

这是const表连接类型的一个特例。

∙　const

　　表中最多只有一行匹配的记录，它在查询一开始的时候就会被读取出来。

由于只有一行记录，在余下的优化程序里该行记录的字段值可以被当作是一个恒定值。

const表查询起来非常快，因为只要读取一次!

const用于在和PRIMARYKEY或UNIQUE索引中有固定值比较的情形。

下面的几个查询中，tbl_name就是const表了：

　　SELECT * FROM tbl_name WHERE primary_key=1;

　　SELECT * FROM tbl_name

　　WHERE primary_key_part1=1 AND primary_key_part2=2;

∙　　eq_ref

　　从该表中会有一行记录被读取出来以和从前一个表中读取出来的记录做联合。

与const类型不同的是，这是最好的连接类型。

它用在索引所有部分都用于做连接并且这个索引是一个PRIMARYKEY或UNIQUE类型。

eq_ref可以用于在进行"="做比较时检索字段。

比较的值可以是固定值或者是表达式，表达式中可以使用表里的字段，它们在读表之前已经准备好了。

以下的几个例子中，MySQL使用了eq_ref连接来处理ref_table：

　　SELECT * FROM ref_table,other_table

　　WHERE ref_table.key_column=other_table.column;

　　SELECT * FROM ref_table,other_table

　　WHERE ref_table.key_column_part1=other_table.column

　　AND ref_table.key_column_part2=1;

∙　　ref

　　该表中所有符合检索值的记录都会被取出来和从上一个表中取出来的记录作联合。

ref用于连接程序使用键的最左前缀或者是该键不是PRIMARYKEY或UNIQUE索引（换句话说，就是连接程序无法根据键值只取得一条记录）的情况。

当根据键值只查询到少数几条匹配的记录时，这就是一个不错的连接类型。

ref还可以用于检索字段使用=操作符来比较的时候。

以下的几个例子中，MySQL将使用ref来处理ref_table：

　　SELECT * FROM ref_table WHERE key_column=expr;

　　SELECT * FROM ref_table,other_table

　　WHERE ref_table.key_column=other_table.column;

　　SELECT * FROM ref_table,other_table

　　WHERE ref_table.key_column_part1=other_table.column

　　AND ref_table.key_column_part2=1;

∙　　ref_or_null

　　这种连接类型类似ref，不同的是MySQL会在检索的时候额外的搜索包含NULL值的记录。

这种连接类型的优化是从MySQL4.1.1开始的，它经常用于子查询。

在以下的例子中，MySQL使用ref_or_null类型来处理ref_table：

　　SELECT * FROM ref_table

　　WHERE key_column=expr OR key_column IS NULL;

∙　　index_merge

　　这种连接类型意味着使用了IndexMerge优化方法。

这种情况下，key字段包括了所有使用的索引，key_len包括了使用的键的最长部分。

详情请看"7.2.5HowMySQLOptimizesORClauses"。

∙　　unique_subquery

　　这种类型用例如一下形式的IN子查询来替换ref：

　　valueIN（SELECTprimary_keyFROMsingle_tableWHEREsome_expr）

　　unique_subquery只是用来完全替换子查询的索引查找函数效率更高了。

∙　　index_subquery

　　这种连接类型类似unique_subquery。

它用子查询来代替IN，不过它用于在子查询中没有唯一索引的情况下，例如以下形式：

　　valueIN（SELECTkey_columnFROMsingle_tableWHEREsome_expr）

∙　　range

　　只有在给定范围的记录才会被取出来，利用索引来取得一条记录。

key字段表示使用了哪个索引。

key_len字段包括了使用的键的最长部分。

这种类型时ref字段值是NULL。

range用于将某个字段和一个定植用以下任何操作符比较时=,<>,>,>=,<,<=,ISNULL,<=>,BETWEEN,或IN：