转自http://kiddymeet.blog.51cto.com/20194/5674
译者:叶金荣(email:),来源:mysql手册版本 5.0.20,转载请注明译者和出处,并且不能用于商业用途,违者必究。
7 mysql 优化
数据库优化是一项很复杂的工作,因为这最终需要对系统优化的很好理解才行。尽管对系统或应用系统的了解不多的情况下优化效果还不错,但是如果想优化的效果更好,那么就需要对它了解更多才行。
本章主要讲解了几种优化mysql的方法,并且给出了例子。记着,总有各种办法能让系统运行的更快,当然了,这需要更多的努力。
7.1 优化概述
让系统运行得快得最重要因素是数据库基本的设计。并且还必须清楚您的系统要用来做什么,以及存在的瓶颈。
最常见的系统瓶颈有以下几种:- 磁盘搜索。它慢慢地在磁盘中搜索数据块。对现代磁盘来说,平时的搜索时间基本上小于10毫秒,因此理论上每秒钟可以做100次磁盘搜索。这个时间对于全新的新磁盘来说提高的不多,并且对于只有一个表的情况也是如此。加快搜索时间的方法是将数据分开存放到多个磁盘中。
- 磁盘读/写。当磁盘在正确的位置上时,就需要读取数据。对现代磁盘来说,磁盘吞吐量至少是10-20mb/秒。这比磁盘搜索的优化更容易,因为可以从多个媒介中并行地读取数据。
- cpu周期。数据存储在主内存中(或者它已经在主内存中了),这就需要处理这些数据以得到想要的结果。存在多个?硐啾饶诖嫒萘坷此蹈窍拗频囊蛩亍2还孕"砝此担俣韧ǔ2皇俏侍狻?
- 内存带宽。当cpu要将更多的数据存放在cpu缓存中时,主内存的带宽就是瓶颈了。在大多数系统中,这不是常见的瓶颈,不过也是要注意的一个因素。
7.1.1 mysql 设计的局限性
当使用myisam
存储引擎时,mysql会使用一个快速数据表锁以允许同时多个读取和一个写入。这种存储引擎的最大问题是发生在一个单一的表上同时做稳定的更新操作及慢速查询。如果这种情况在某个表中存在,可以使用另一种表类型。详情请看"
15 mysql storage engines and table types"。
mysql可以同时在事务及非事务表下工作。为了能够平滑的使用非事务表(发生错误时不能回滚),有以下几条规则:
- 所有的字段都有默认值
- 如果字段中插入了一个"错误"的值,比如在数字类型字段中插入过大数值,那么mysql会将该字段值置为"最可能的值"而不是给出一个错误。数字类型的值是0,最小或者最大的可能值。字符串类型,不是空字符串就是字段所能存储的最大长度。
- 所有的计算表达式都会返回一个值而报告条件错误,例如 1/0 返回
null
。
这些规则隐含的意思是,不能使用mysql来检查字段内容。相反地,必须在存储到数据库前在应用程序中来检查。详情请看"1.8.6 how mysql deals with constraints 和 "14.1.4 insert
syntax"。
7.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
的结果,可以访问:[url]http://dev.mysql.com/tech-resources/crash-me.php[/url]. 访问 [url]http://dev.mysql.com/tech-resources/benchmarks[/url] 可以看到基准的结果。
如果您想努力做到独立于数据库,这就需要对各种sql服务器的瓶颈都有一些很好的想法。例如,mysql对于 myisam
类型的表在检索以及更新记录时非常快,但是在有并发的慢速读取及写入记录时却有一定的问题。作为oracle来说,它在访问刚刚被更新的记录时有很大的问题(直到结果被刷新到磁盘中)。传统的数据库一般地在从日志表中生成摘要表这方面的表现不怎么好,因为在这种情况下,行记录锁几乎没用。
为了能让应用程序真正的做到独立于数据库,就必须把操作数据的接口定义的简单且可扩展。由于c++在很多系统上都可以使用,因此使用c++作为数据库的基类结果很合适。
如果使用了某些数据库独有的特定功能(比如 replace
语句就只在mysql中独有),这就需要通过编写替代方法来在其他数据库中实现这个功能。尽管这些替代方法可能会比较慢,但是它能让其他数据库实现同样的功能。
在mysql中,可以在查询语句中使用 /*! */
语法来增加mysql特有的关键字。然而在很多其他数据库中,/**/
却被当成了注释(并且被忽略)。
如果有时候更高的性能比数据结果的精确更重要,就像在一些web应用中那样,这可以使用一个应用层来缓存结果,这可能会有更高的性能。通过让旧数据在一定时间后过期,来合理的更新缓存。这是处理负载高峰期时的一种方法,这种情况下,可以通过加大缓存容量和过期时间直到负载趋于正常。
这种情况下,建表信息中就要包含了初始化缓存的容量以及正常刷新数据表的频率。
一个实现应用层缓存的可选方案是使用mysql的查询缓存(query cache)。启用查询缓存后,数据库就会根据一些详情来决定哪些结果可以被重用。它大大简化了应用程序,详情请看"5.11 the mysql query cache"。
7.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直接访问累计表,这样的话,那些高级用户就可以自己利用这些数据做试验了。
这个系统工作的很好,并且在适度的sun ultra sparc工作站(2x200mhz)上处理数据没有任何问题。最终这个系统移植到了linux上。
7.1.4 mysql 基准套件
本章本来要包括mysql基准套件(以及 crash-me
)的技术描述的,但是至今还未写。现在,您可以通过查看mysql发布源代码 `sql-bench' 目录下的代码以及结果有一个更好的想法。
基准套件就是想告诉用户执行什么样的sql查询表现的更好或者更差。
请注意,这个基准是单线程的,因此它度量了操作执行的最少时间。我们未来打算增加多线程测试的基准套件。
想要使用基准套件,必备以下几个条件:- 基准套件在mysql的发布源代码中就有。可以去 [url]http://dev.mysql.com/downloads/[/url] 下载发布版或者使用现有开发代码树(详情请看"2.3.3 installing from the development source tree")。
- 基准脚本是用perl写的,它用perl的dbi模块来连接数据库,因此必须安装dbi模块。并且还需要每个要做测试的服务器上都有特定的bdb驱动程序。例如,为了测试mysql、postgresql和db2,就必须安装
dbd::mysql
,dbd::pg
及dbd::db2
模块。详情请看"2.7 perl installation note"。
run-all-tests
脚本:
shell> cd sql-bench shell> perl run-all-tests --server=server_name
server_name 可以是任何一个可用的服务。想要列出所有的可用选项和支持的服务,只要调用以下命令:
shell> perl run-all-tests --help
crash-me
脚本也是放在 `sql-bench' 目录下。crash-me
通过执行真正的查询以试图判断数据库都支持什么特性、性能表现以及限制。例如,它可以判断:
- 都支持什么字段类型
- 支持多少索引
- 支持什么样的函数
- 能支持多大的查询
varchar
字段类型能支持多大
可以从 [url]http://dev.mysql.com/tech-resources/crash-me.php[/url] 上找到各种不同数据库 crash-me
的结果。更多的信息请访问 [url]http://dev.mysql.com/tech-resources/benchmarks[/url]。
7.1.5 使用您自己的基准
请确定对您的数据库或者应用程序做基准测试,以发现它们的瓶颈所在。解决这个瓶颈(或者使用一个假的模块来代替)之后,就能很容易地找到下一个瓶颈了。即使应用程序当前总体的表现可以接受,不过还是至少要做好找到每个瓶颈的计划,说不定某天您就希望应用程序能有更好的性能。
从mysql的基准套件中就能找到一个便携可移植的基准测试程序了。详情请看"7.1.4 the mysql benchmark suite"。您可以从基准套件中的任何一个程序,做适当的修改以适合您的需要。通过整个方式,您就可以有各种不同的办法来解决问题,知道哪个程序才是最快的。
另一个基准套件是开放源码的数据库基准,可以在 [url]http://osdb.sourceforge.net[/url] 上找到。
当系统负载十分繁重的时候,通常就会发生问题。我们就有很多客户联系我们说他们有一个(测试过的)生产系统也遭遇了负载问题。在很多情况下,性能问题归结于数据库的基本设计(例如,在高负载下扫描数据表的表现不好)、操作系统、或者程序库等因素。很多时候,这些问题在还没有正式用于生产前相对更容易解决。
为了避免发生这样的问题,最好让您的应用程序在可能的最差的负载下做基准测试!可以使用super smack,在 [url]http://jeremy.zawodny.com/mysql/super-smack[/url] 可以找到。从它名字的意思就能想到,只要您愿意,它就能让您的系统死掉,因此确认只在开发系统上做测试。
7.2 优化 select
语句及其他查询
首先,影响所有语句的一个因素是:您的权限设置越复杂,那么开销就越大。
使用比较简单的 grant
语句能让mysql减少在客户端执行语句时权限检查的开销。例如,如果没有设定任何表级或者字段级的权限,那么服务器就无需检查 tables_priv
和 columns_priv
表的记录了。同样地,如果没有对帐户设定任何资源限制的话,那么服务器也就无需做资源使用统计了。如果有大量查询的话,花点时间来规划简单的授权机制以减少服务器权限检查的开销是值得的。
mysql
客户端程序使用
benchmark()
函数做一个定时测试。它的语法是:
benchmark(loop_count,expression)
。例如:
mysql> select benchmark(1000000,1+1); +------------------------+ | benchmark(1000000,1+1) | +------------------------+ | 0 | +------------------------+ 1 row in set (0.32 sec)
上述结果是在pentium ii 400mhz的系统上执行得到的。它告诉我们:mysql在这个系统上可以在0.32秒内执行 1,000,000 次简单的加法运算。
所有的mysql函数都应该被最优化,不过仍然有些函数例外。benchmark()
是一个用于检查查询语句中是否存在问题的非常好的工具。
7.2.1 explain
语法(得到select
的相关信息)
explain
tbl_name
explain
语句可以被当作
describe
的同义词来用,也可以用来获取一个mysql要执行的
select
语句的相关信息。
explain tbl_name
语法和describe tbl_name
或show columns from tbl_name
一样。- 当在一个
select
语句前使用关键字explain
时,mysql会解释了即将如何运行该select
语句,它显示了表如何连接、连接的顺序等信息。
explain
用法。
explain
的帮助下,您就知道什么时候该给表添加索引,以使用索引来查找记录从而让
select
运行更快。
analyze table
来更新该表的统计信息,例如键的基数,它能帮您在优化方面做出更好的选择。详情请看"
14.5.2.1 analyze table
syntax"。
select
语句中的表名的顺序做连接,可以在查询的开始使用
select straight_join
而不只是
select
。
explain
返回了一行记录,它包括了
select
语句中用到的各个表的信息。这些表在结果中按照mysql即将执行的查询中读取的顺序列出来。mysql用一次扫描多次连接(single-sweep, multi-join) 的方法来解决连接。这意味着mysql从第一个表中读取一条记录,然后在第二个表中查找到对应的记录,然后在第三个表中查找,依次类推。当所有的表都扫描完了,它输出选择的字段并且回溯所有的表,直到找不到为止,因为有的表中可能有多条匹配的记录下一条记录将从该表读取,再从下一个表开始继续处理。
explain
输出的结果格式改变了,使得它更适合例如
union
语句、子查询以及派生表的结构。更令人注意的是,它新增了2个字段:
id
和
select_type
。当你使用早于mysql 4.1的版本就看不到这些字段了。
explain
结果的每行记录显示了每个表的相关信息,每行记录都包含以下几个字段:
-
本次
select
的标识符。在查询中每个select
都有一个顺序的数值。 -
select
的类型,可能会有以下几种: -
-
简单的
select
(没有使用union
或子查询)
-
最外层的
select
。
-
第二层,在
select
之后使用了union
。
-
union
语句中的第二个select
,依赖于外部子查询
-
子查询中的第一个
select
-
子查询中的第一个
subquery
依赖于外部的子查询
-
派生表
select
(from
子句中的子查询)
simple
primary
union
dependent union
subquery
dependent subquery
derived
-
简单的
- 记录查询引用的表。
-
表连接类型。以下列出了各种不同类型的表连接,依次是从最好的到最差的:
-
表只有一行记录(等于系统表)。这是
const
表连接类型的一个特例。
-
表中最多只有一行匹配的记录,它在查询一开始的时候就会被读取出来。由于只有一行记录,在余下的优化程序里该行记录的字段值可以被当作是一个恒定值。
const
表查询起来非常快,因为只要读取一次!const
用于在和primary key
或unique
索引中有固定值比较的情形。下面的几个查询中, tbl_name 就是 c 表了:select * from tbl_name where primary_key=1; select * from tbl_name where primary_key_part1=1 and primary_key_part2=2; -
从该表中会有一行记录被读取出来以和从前一个表中读取出来的记录做联合。与
const
类型不同的是,这是最好的连接类型。它用在索引所有部分都用于做连接并且这个索引是一个primary key
或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
用于连接程序使用键的最左前缀或者是该键不是primary key
或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
,不同的是mysql会在检索的时候额外的搜索包含null
值的记录。这种连接类型的优化是从mysql 4.1.1开始的,它经常用于子查询。在以下的例子中,mysql使用ref_or_null
类型来处理 ref_table:select * from ref_table where key_column= expr or key_column is null; -
这种连接类型意味着使用了
index merge
优化方法。这种情况下,key
字段包括了所有使用的索引,key_len
包括了使用的键的最长部分。详情请看" 7.2.5 how mysql optimizesor
clauses"。
-
这种类型用例如一下形式的
in
子查询来替换ref
: value in (select primary_key from single_table where some_expr)unique_subquery
只是用来完全替换子查询的索引查找函数效率更高了。 -
这种连接类型类似
unique_subquery
。它用子查询来代替in
,不过它用于在子查询中没有唯一索引的情况下,例如以下形式: value in (select key_column from single_table where some_expr) -
只有在给定范围的记录才会被取出来,利用索引来取得一条记录。
key
字段表示使用了哪个索引。key_len
字段包括了使用的键的最长部分。这种类型时ref
字段值是null
。range
用于将某个字段和一个定植用以下任何操作符比较时=
,<>
,>
,>=
,<
,<=
,is null
,<=>
,between
, 或in
:select * from tbl_name where key_column = 10; select * from tbl_name where key_column between 10 and 20; select * from tbl_name where key_column in (10,20,30); select * from tbl_name where key_part1= 10 and key_part2 in (10,20,30); -
连接类型跟
all
一样,不同的是它只扫描索引树。它通常会比all
快点,因为索引文件通常比数据文件小。mysql在查询的字段知识单独的索引的一部分的情况下使用这种连接类型。
-
将对该表做全部扫描以和从前一个表中取得的记录作联合。这时候如果第一个表没有被标识为
const
的话就不大好了,在其他情况下通常是非常糟糕的。正常地,可以通过增加索引使得能从表中更快的取得记录以避免all
。
system
const
eq_ref
ref
ref_or_null
index_merge
unique_subquery
index_subquery
range
index
all
-
表只有一行记录(等于系统表)。这是
-
possible_keys
字段是指mysql在搜索表记录时可能使用哪个索引。注意,这个字段完全独立于explain
显示的表顺序。这就意味着possible_keys
里面所包含的索引可能在实际的使用中没用到。如果这个字段的值是null
,就表示没有索引被用到。这种情况下,就可以检查where
子句中哪些字段那些字段适合增加索引以提高查询的性能。就这样,创建一下索引,然后再用explain
检查一下。详细的查看章节" 14.2.2alter table
syntax"。想看表都有什么索引,可以通过show index from tbl_name
来看。
-
key
字段显示了mysql实际上要用的索引。当没有任何索引被用到的时候,这个字段的值就是null
。想要让mysql强行使用或者忽略在possible_keys
字段中的索引列表,可以在查询语句中使用关键字force index
,use index
, 或ignore index
。如果是myisam
和bdb
类型表,可以使用analyze table
来帮助分析使用使用哪个索引更好。如果是myisam
类型表,运行命令myisamchk --analyze
也是一样的效果。详细的可以查看章节" 14.5.2.1analyze table
syntax"和" 5.7.2 table maintenance and crash recovery"。 -
key_len
字段显示了mysql使用索引的长度。当key
字段的值为null
时,索引的长度就是null
。注意,key_len
的值可以告诉你在联合索引中mysql会真正使用了哪些索引。 -
ref
字段显示了哪些字段或者常量被用来和key
配合从表中查询记录出来。 -
rows
字段显示了mysql认为在查询中应该检索的记录数。 -
本字段显示了查询中mysql的附加信息。以下是这个字段的几个不同值的解释:
- mysql当找到当前记录的匹配联合结果的第一条记录之后,就不再搜索其他记录了。
-
mysql在查询时做一个
left join
优化时,当它在当前表中找到了和前一条记录符合left join
条件后,就不再搜索更多的记录了。下面是一个这种类型的查询例子:select * from t1 left join t2 on t1.id=t2.id where t2.id is null;假使t2.id
定义为not null
。这种情况下,mysql将会扫描表t1
并且用t1.id
的值在t2
中查找记录。当在t2
中找到一条匹配的记录时,这就意味着t2.id
肯定不会都是null
,就不会再在t2
中查找相同id
值的其他记录了。也可以这么说,对于t1
中的每个记录,mysql只需要在t2
中做一次查找,而不管在t2
中实际有多少匹配的记录。 - mysql没找到合适的可用的索引。取代的办法是,对于前一个表的每一个行连接,它会做一个检验以决定该使用哪个索引(如果有的话),并且使用这个索引来从表里取得记录。这个过程不会很快,但总比没有任何索引时做表连接来得快。
-
mysql需要额外的做一遍从而以排好的顺序取得记录。排序程序根据连接的类型遍历所有的记录,并且将所有符合
where
条件的记录的要排序的键和指向记录的指针存储起来。这些键已经排完序了,对应的记录也会按照排好的顺序取出来。详情请看" 7.2.9 how mysql optimizesorder by
"。 - 字段的信息直接从索引树中的信息取得,而不再去扫描实际的记录。这种策略用于查询时的字段是一个独立索引的一部分。
-
mysql需要创建临时表存储结果以完成查询。这种情况通常发生在查询时包含了
group by
和order by
子句,它以不同的方式列出了各个字段。 -
where
子句将用来限制哪些记录匹配了下一个表或者发送给客户端。除非你特别地想要取得或者检查表种的所有记录,否则的话当查询的extra
字段值不是using where
并且表连接类型是all
或index
时可能表示有问题。
distinct
not exists
range checked for each record (index map: #)
using filesort
using index
using temporary
using where
如果你想要让查询尽可能的快,那么就应该注意extra
字段的值为using filesort
和using temporary
的情况。
id
select_type
table
type
possible_keys
key
key_len
ref
rows
extra
explain
的结果中
rows
字段的值的乘积大概地知道本次连接表现如何。它可以粗略地告诉我们mysql在查询过程中会查询多少条记录。如果是使用系统变量
max_join_size
来取得查询结果,这个乘积还可以用来确定会执行哪些多表
select
语句。详情请看"
7.5.2 tuning server parameters"。
explain
提供的信息来较大程度地优化多表联合查询的性能。
select
语句,正打算用
explain
来检测:explain select tt.ticketnumber, tt.timein, tt.projectreference, tt.estimatedshipdate, tt.actualshipdate, tt.clientid, tt.servicecodes, tt.repetitiveid, tt.currentprocess, tt.currentdppers tt.recordvolume, tt.dpprinted, et.country, et_1.country, do.custname from tt, et, et as et_1, do where tt.submittime is null and tt.actualpc = et.employid and tt.assignedpc = et_1.employid and tt.clientid = do.custnmbr;
- 要比较的字段定义如下:
table column column type tt
actualpc
char(10)
tt
assignedpc
char(10)
tt
clientid
char(10)
et
employid
char(15)
do
custnmbr
char(15)
- 数据表的索引如下:
table index tt
actualpc
tt
assignedpc
tt
clientid
et
employid
(primary key)do
custnmbr
(primary key) tt.actualpc
的值是不均匀分布的。
在任何优化措施未采取之前,经过
explain
分析的结果显示如下:table type possible_keys key key_len ref rows extra et all primary null null null 74 do all primary null null null 2135 et_1 all primary null null null 74 tt all assignedpc, null null null 3872 clientid, actualpc range checked for each record (key map: 35)
type
的对于每个表值都是
all
,这个结果意味着mysql对所有的表做一个迪卡尔积;这就是说,每条记录的组合。这将需要花很长的时间,因为需要扫描每个表总记录数乘积的总和。在这情况下,它的积是
74 * 2135 * 74 * 3872 = 45,268,558,720
条记录。如果数据表更大的话,你可以想象一下需要多长的时间。
isam
类型的表来说,除非字段定义完全一样,否则不会使用索引)。在这个前提下,
varchar
和
char
是一样的除非它们定义的长度不一致。由于
tt.actualpc
定义为
char(10)
,
et.employid
定义为
char(15)
,二者长度不一致。
为了解决这个问题,需要用
alter table
来加大
actualpc
的长度从10到15个字符:
tt.actualpc
和
et.employid
都是
varchar(15)
了。再来执行一次
explain
语句看看结果:
rows
值乘积已经少了74倍。这次查询需要用2秒钟。
第二个改变是消除在比较
tt.assignedpc = et_1.employid
和
tt.clientid = do.custnmbr
中字段的长度不一致问题:
explain
的结果如下:
遗留下来的问题是,mysql默认地认为字段
tt.actualpc
的值是均匀分布的,然而表
tt
并非如此。幸好,我们可以很方便的让mysql分析索引的分布:
explain
的结果如下:
explain
结果中的
rows
字段的值也是mysql的连接优化程序大致猜测的,请检查这个值跟真实值是否基本一致。如果不是,可以通过在
select
语句中使用
straight_join
来取得更好的性能,同时可以试着在
from
分句中用不同的次序列出各个表。
7.2.2 估算查询性能
log(row_count) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1
。
log(500,000)/log(1024/3*2/(3+4)) + 1
=
4
次搜索。
myisam
类型表来说,索引缓存容量是由系统变量
key_buffer_size
控制的。详情请看"
7.5.2 tuning server parameters"。
7.2.3 select
查询的速度
select ... where
查询变得更快的第一件事就是,先检查看看是否可以增加索引。所有对不同表的访问都通常使用索引。可以使用
explain
语句来判断
select
使用了哪些索引。详情请看"
7.4.5 how mysql uses indexes"和"
7.2.1 explain
syntax (get information about a select
)"。
myisam
表查询速度的忠告:
- 想要让mysql将查询优化的速度更快些,可以在数据表已经加载完全部数据后执行行
analyze table
或运行myisamchk --analyze
命令。它更新了每个索引部分的值,这个值意味着相同记录的平均值(对于唯一索引来说,这个值则一直都是 1)。mysql就会在当你使用基于一个非恒量表达式的两表连接时,根据这个值来决定使用哪个索引。想要查看结果,可以在分析完数据表后运行show index from tbl_name
查看cardinality
字段的值。myisamchk --description --verbose
显示了索引的分布信息。 - 想要根据一个索引来排序数据,可以运行
myisamchk --sort-index --sort-records=1
(如果想要在索引 1 上做排序)。这对于有一个唯一索引并且想根据这个索引的顺序依次读取记录的话来说是一个提高查询速度的好办法。不过要注意的是,第一次在一个大表上做排序的话将会耗费很长时间。
where 子句
where
子句。例子中使用了
select
语句,但是在
delete
和
update
语句中对
where
子句的优化是一样的。
- 去除不必要的括号:((a and b) and c or (((a and b) and (c and d)))) -> (a and b and c) or (a and b and c and d)
- 展开常量:(a<b and b=c) and a=5 -> b>5 and b=c and a=5
- 去除常量条件(在展开常量时需要):(b>=5 and b=5) or (b=6 and 5=5) or (b=7 and 5=6) -> b=5 or b=6
- 常量表达示在索引中只计算一次
- 在单独一个表上做
count(*)
而不使用where
时, 对于myisam
和heap
表就会直接从表信息中检索结果。在单独一个表上做任何表not null
达式查询时也是这样做。 - 预先探测无效的常量表达式。mysql会快速探测一些不可能的
select
语句并且不返回任何记录。 - 当没用
group by
或分组函数时,having
和where
合并(count()
,min()
等也是如此)。 - 为表连接中的每个表构造一个简洁的
where
语句,以得到更快的where
计算值并且尽快跳过记录。 - 查询中所有的常量表都会比其他表更早读取。一个常量表符合以下几个条件:
- 空表或者只有一条记录。
- 与在一个
unique
索引、或一个primary key
的where
子句一起使用的表,这里所有的索引部分和常数表达式做比较并且索引部分被定义为not null
。
以下的几个表都会被当成常量表:select * from t where primary_key=1; select * from t1,t2 where t1.primary_key=1 and t2.primary_key=t1.id; - mysql会进各种可能找到表连接最好的连接方法。 如果在
order by
和group by
子句中的所有字段都来自同一个表的话,那么在连接时这个表就会优先处理。 - 如果有
order by
子句和一个不同的group by
子句,或者如果order by
或group by
中的字段都来自其他的表而非连接顺序中的第一个表的话,就会创建一个临时表了。 - 如果使用
sql_small_result
,mysql就会使用内存临时表了。 - 所有的表索引都会查询,最好的情况就是所有的索引都会被用到,除非优化程序认为全表扫描的效率更高。同时,数据表扫描是基于判断最好的索引范围超过数据表的30%。 现在,优化程序复杂多了,它基于对一些附加因素的估计,例如表大小,记录总数,i/o块大小,因此就不能根据一个固定的百分比来决定是选择使用索引还是直接扫描数据表。
- 在某些情况下,mysql可以直接从索引中取得记录而无需查询数据文件。如果所有在索引中使用的字段都是数字类型的话,只需要用索引树就能完成查询。
- 每条记录输出之前,那些没有匹配
having
子句的就会被跳过。
以下几个查询速度非常快:select count(*) from tbl_name; select min( key_part1),max( key_part1) from tbl_name; select max( key_part2) from tbl_name where key_part1= constant; select ... from tbl_name order by key_part1, key_part2,... limit 10; select ... from tbl_name order by key_part1 desc, key_part2desc, ... limit 10;
or 子句
index merge
方法用于使用
ref
,
ref_or_null
, 或
range
扫描取得的记录合并起来放到一起作为结果。这种方法在表条件是或条件
ref
,
ref_or_null
, 或
range
,并且这些条件可以用不同的键时采用。
"join"类型的优化是从 mysql 5.0.0 开始才有的,代表者在索引的性能上有着标志性的改进,因为使用老规则的话,数据库最多只能对每个引用表使用一个索引。
在
explain
的结果中,这种方法在
type
字段中表现为
index_merge
。这种情况下,
key
字段包含了所有使用的索引列表,并且
key_len
字段包含了使用的索引的最长索引部分列表。
例如:select * from tbl_name where key_part1 = 10 or key_part2 = 20; select * from tbl_name where ( key_part1 = 10 or key_part2 = 20) and non_key_part=30; select * from t1,t2 where (t1. key1 in (1,2) or t1. key2 like ' value%') and t2. key1=t1. some_col; select * from t1,t2 where t1. key1=1 and (t2. key1=t1. some_col or t2. key2=t1. some_col2);
is null
mysql在 col_name
is null
时做和
col_name
=
constant_value 一样的优化。例如,mysql使用索引或者范围来根据
is nul l
搜索
null
。select * from
tbl_name where
key_col is null; select * from
tbl_name where
key_col <=> null; select * from
tbl_name where
key_col=
const1 or
key_col=
const2 or
key_col is null;
where
子句包括了一个
col_name
is null
条件,并且这个字段声明为
not null
,那么这个表达式就会被优化。当字段可能无论如何都会产生
null
值时,就不会再做优化了;例如,当它来自一个
left join
中右边的一个表时。
col_name = expr and col_name is null
做额外的优化, 常见的就是子查询。
explain
当优化起作用时会显示
ref_or_null
。
is null
。
a
和 表
t2
中
b
有索引了:select * from t1 where t1.a=
expr or t1.a is null; select * from t1,t2 where t1.a=t2.a or t2.a is null; select * from t1,t2 where (t1.a=t2.a or t2.a is null) and t2.b=t1.b; select * from t1,t2 where t1.a=t2.a and (t2.b=t1.b or t2.b is null); select * from t1,t2 where (t1.a=t2.a and t2.a is null and ...) or (t1.a=t2.a and t2.a is null and ...);
ref_or_null
首先读取引用键,然后独立扫描键值为
null
的记录。
is null
级别。下面的查询中,mysql只会使用键来查询表达式
(t1.a=t2.a and t2.a is null)
而无法使在
b
上使用索引部分:select * from t1,t2 where (t1.a=t2.a and t2.a is null) or (t1.b=t2.b and t2.b is null);
distinct
distinct
和
order by
一起使用时就会创建一个临时表。
distinct
可能需要用到
group by
,就需要明白mysql在
order by
或
having
子句里的字段不在选中的字段列表中时是怎么处理的。详情请看"
13.9.3 group by
with hidden fields"。
limit row_count
和
distinct
一起使用时,mysql在找到
row_count 不同记录后就会立刻停止搜索了。
t1
在
t2
前就使用了(可以通过
explain
分析知道),mysql就会在从
t2
中找到第一条记录后就不再读
t2
了(为了能和中
t1
的任何特定记录匹配):select distinct t1.a from t1,t2 where t1.a=t2.a;
left join 和 right join
a left join b join_condition
在mysql中实现如下:
- 表
b
依赖于表a
以及其依赖的所有表。 - 表
a
依赖于在left join
条件中的所有表(除了b
)。 left join
条件用于决定如何从表b
中读取记录了(换句话说,where
子句中的任何条件都对此不起作用)。- 所有标准的连接优化都会执行,例外的情况是有一个表总是在它依赖的所有表之后被读取。如果这是一个循环的依赖关系,那么mysql会认为这是错误的。
- 所有的标准
where
优化都会执行。 - 如果
a
中有一条记录匹配了where
子句,但是b
中没有任何记录匹配on
条件,那么就会产生一条b
记录,它的字段值全都被置为null
。 - 如果使用
left join
来搜索在一些表中不存在的记录,并且where
部分中有检测条件:col_name is null
,col_name
字段定义成not null
的话,mysql就会在找到一条匹配left join
条件的记录(用于和特定的索引键做联合)后停止搜索了。
right join
的实现和
left join
类似,不过表的角色倒过来了。
连接优化程序计算了表连接的次序。表读取的顺序是由
left join
强行指定的,而且使用
straight_join
能帮助连接优化程序更快地执行,因为这就会有更少的表排队检查了。注意,这里是指如果你执行下面这种类型的查询后,mysql就会对
b
做一次全表扫描,因为
left join
强制要求了必须在读
d
之前这么做:select * from a,b left join c on (c.key=a.key) left join d on (d.key=a.key) where b.key=d.key;
left join
优化:如果对产生的
null
记录
where
条件总是
假
,那么
left join
就会变成一个普通的连接。
例如,下面的查询中如果
t2.column1
的值是
null
的话,
where
子句的结果就是
假
了:
t1
之前就用到
t2
了。想要强行指定表顺序的话,可以使用
straight_join
。
GROUP BY
子句而无需做额外的排序。
ORDER BY
不是和索引的顺序准确匹配,索引还是可以被用到,只要不用的索引部分和所有的额外的
ORDER BY
字段在
WHERE
子句中都被包括了。下列的几个查询都会使用索引来解决
ORDER BY
或
GROUP BY
部分:SELECT * FROM t1 ORDER BY
key_part1,
key_part2,... ; SELECT * FROM t1 WHERE
key_part1=
constant ORDER BY
key_part2; SELECT * FROM t1 WHERE
key_part1=
constant GROUP BY
key_part2; SELECT * FROM t1 ORDER BY
key_part1 DESC,
key_part2 DESC; SELECT * FROM t1 WHERE
key_part1=1 ORDER BY
key_part1 DESC,
key_part2 DESC;
ORDER BY
,尽管它会使用索引来找到记录来匹配
WHERE
子句。这些情况如下:
- 对不同的索引键做
ORDER BY
:SELECT * FROM t1 ORDER BY key1, key2; - 在非连续的索引键部分上做
ORDER BY
:SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2; - 同时使用了
ASC
和DESC
:SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC; - 用于搜索记录的索引键和做
ORDER BY
的不是同一个:SELECT * FROM t1 WHERE key2=constant ORDER BY key1; - 有很多表一起做连接,而且读取的记录中在
ORDER BY
中的字段都不全是来自第一个非常数的表中(也就是说,在EXPLAIN
分析的结果中的第一个表的连接类型不是const
)。 - 使用了不同的
ORDER BY
和GROUP BY
表达式。 - 表索引中的记录不是按序存储。例如,
HASH
和HEAP
表就是这样。
EXPLAIN SELECT ... ORDER BY
,就知道MySQL是否在查询中使用了索引。如果
Extra
字段的值是
Using filesort
,则说明MySQL无法使用索引。详情请看"
7.2.1 EXPLAIN
Syntax (Get Information About a SELECT
)"。
filesort
算法:
- 根据索引键读取记录,或者扫描数据表。那些无法匹配
WHERE
分句的记录都会被略过。 - 在缓冲中每条记录都用一个‘对’存储了2个值(索引键及记录指针)。缓冲的大小依据系统变量
sort_buffer_size
的值而定。 - 当缓冲慢了时,就运行 qsort(快速排序)并将结果存储在临时文件中。将存储的块指针保存起来(如果所有的‘对’值都能保存在缓冲中,就无需创建临时文件了)。
- 执行上面的操作,直到所有的记录都读取出来了。
- 做一次多重合并,将多达
MERGEBUFF
(7)个区域的块保存在另一个临时文件中。重复这个操作,直到所有在第一个文件的块都放到第二个文件了。 - 重复以上操作,直到剩余的块数量小于
MERGEBUFF2
(15)。 - 在最后一次多重合并时,只有记录的指针(排序索引键的最后部分)写到结果文件中去。
- 通过读取结果文件中的记录指针来按序读取记录。想要优化这个操作,MySQL将记录指针读取放到一个大的块里,并且使用它来按序读取记录,将记录放到缓冲中。缓冲的大小由系统变量
read_rnd_buffer_size
的值而定。这个步骤的代码在源文件 `sql/records.cc' 中。
WHERE
分句时,第二次是排序时。尽管第一次都成功读取记录了(例如,做了一次全表扫描),第二次是随机的读取(索引键已经排好序了,但是记录并没有)。
filesort
优化算法用于记录中不只包括索引键值和记录的位置,还包括查询中要求的字段。这么做避免了需要2次读取记录。改进的
filesort
算法做法大致如下:
- 跟以前一样,读取匹配
WHERE
分句的记录。 - 相对于每个记录,都记录了一个对应的;‘元组’信息信息,包括索引键值、记录位置、以及查询中所需要的所有字段。
- 根据索引键对‘元组’信息进行排序。
- 按序读取记录,不过是从已经排序过的‘元组’列表中读取记录,而非从数据表中再读取一次。
filesort
算法相比原来的,‘元组’比‘对’需要占用更长的空间,它们很少正好适合放在排序缓冲中(缓冲的大小是由
sort_buffer_size
的值决定的)。因此,这就可能需要有更多的I/O操作,导致改进的算法更慢。为了避免使之变慢,这种优化方法只用于排序‘元组’中额外的字段的大小总和超过系统变量
max_length_for_sort_data
的情况(这个变量的值设置太高的一个表象就是高磁盘负载低CPU负载)。
ORDER BY
的速度,首先要看MySQL能否使用索引而非额外的排序过程。如果不能使用索引,可以试着遵循以下策略:
- 增加
sort_buffer_size
的值。 - 增加
read_rnd_buffer_size
的值。 - 修改
tmpdir
,让它指向一个有很多剩余空间的专用文件系统。如果使用MySQL 4.1或更新,这个选项允许有多个路径用循环的格式。各个路径之间在 Unix 上用冒号(':')分隔开来,在 Windows,NetWare以及OS/2 上用分号(';')。可以利用这个特性将负载平均分摊给几个目录。注意:这些路径必须是分布在不同物理磁盘上的目录,而非在同一个物理磁盘上的不同目录。
默认情况下,MySQL也会对所有的
GROUP BY col1, col2, ...
查询做排序,跟
ORDER BY col1, col2, ...
查询一样。如果显式地包含一个有同样字段列表的
ORDER BY
分句,MySQL优化它的时候并不会损失速度,因为排序总是会发生。如果一个查询中包括
GROUP BY
,但是想要避免对结果排序的开销,可以通过使用
ORDER BY NULL
来取消排序。例如:INSERT INTO foo SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;
HAVING
的查询时会做不同的处理:
- 如果只是用
LIMIT
来取得很少的一些记录, MySQL 有时会使用索引,但是更通常的情况是做一个全表扫描。 - 如果
LIMIT row_count
和ORDER BY
一起使用,则MySQL在找到 row_count 条记录后就会停止排序了,而非对整个表进行排序。 - 当
LIMIT row_count
和DISTINCT
一起联合起来时,MySQL在找到 row_count 条唯一记录后就不再搜索了。 - 在某些情况下,
GROUP BY
可以通过按照顺序读取索引键来实现(或者在索引键上做排序)并且计算累计信息直到索引键改变了。在这种情况下,LIMIT row_count
不会计算任何非必须的GROUP BY
值。 - 一旦MySQL将请求的记录全数发送给客户端后,它就中止查询除非使用了
SQL_CALC_FOUND_ROWS
。 LIMIT 0
总是返回一个空的结果集。这对于检查查询或者取得结果字段的类型非常有用。- 当服务器使用临时表来处理查询,则
LIMIT row_count
可以用来计算需要多少空间。
7.2.11 如何避免全表扫描
如果MySQL需要做一次全表扫描来处理查询时,在EXPLAIN
的结果中
type
字段的值是
ALL
。在以下几种条件下,MySQL就会做全表扫描:
- 数据表是在太小了,做一次全表扫描比做索引键的查找来得快多了。当表的记录总数小于10且记录长度比较短时通常这么做。
- 没有合适用于
ON
或WHERE
分句的索引字段。 - 让索引字段和常量值比较,MySQL已经计算(基于索引树)到常量覆盖了数据表的很大部分,因此做全表扫描应该会来得更快。详情请看"7.2.4 How MySQL Optimizes
WHERE
Clauses"。 - 通过其他字段使用了一个基数很小(很多记录匹配索引键值)的索引键。这种情况下,MySQL认为使用索引键需要大量查找,还不如全表扫描来得更快。
对于小表来说,全表扫描通常更合适。但是对大表来说,尝试使用以下技术来避免让优化程序错误地选择全表扫描:
- 执行
ANALYZE TABLE tbl_name
更新要扫描的表的索引键分布。详情请看"14.5.2.1ANALYZE TABLE
Syntax"。 - 使用
FORCE INDEX
告诉MySQL,做全表扫描的话会比利用给定的索引更浪费资源。详情请看"14.1.7SELECT
Syntax"。SELECT * FROM t1, t2 FORCE INDEX (index_for_column) WHERE t1.col_name=t2.col_name; - 启动
mysqld
时使用参数--max-seeks-for-key=1000
或者执行SET max_seeks_for_key=1000
来告诉优化程序,所有的索引都不会导致超过1000次的索引搜索。请查看章节"5.2.3 Server System Variables"。
7.2.12 加速 INSERT
插入一条记录花费的时间由以下几个因素决定,后面的数字大致表示影响的比例:
- 连接:(3)
- 发送查询给服务器:(2)
- 解析查询:(2)
- 插入记录:(1 x 记录大小)
- 插入索引:(1 x 索引数量)
- 关闭:(1)
- 如果要在同一个客户端在同一时间内插入很多记录,可以使用
INSERT
语句附带有多个VALUES
值。这种做法比使用单一值的INSERT
语句快多了(在一些情况下比较快)。如果是往一个非空的数据表里增加记录,可以调整变量bulk_insert_buffer_size
的值使之更快。详情请看"5.2.3 Server System Variables"。 - 如果要从不同的客户端中插入大量记录,使用
INSERT DELAYED
语句也可以提高速度。详情请看"14.1.4INSERT
Syntax"。 - 对
MyISAM
而言,可以在SELECT
语句正在运行时插入记录,只要这时候没有正在删除记录。 - 想要将一个文本文件加载到数据表中,可以使用
LOAD DATA INFILE
。这通常是使用大量INSERT
语句的20倍。详情请看"14.1.5LOAD DATA INFILE
Syntax"。 - 通过一些额外的工作,就可能让
LOAD DATA INFILE
在数据表有大量索引的情况下运行的更快。步骤如下:
- 用
CREATE TABLE
随便创建一个表。 - 执行
FLUSH TABLES
语句或mysqladmin flush-tables
命令。 - 执行
myisamchk --keys-used=0 -rq /path/to/db/tbl_name
命令,删掉数据表的所有索引。 - 执行
LOAD DATA INFILE
,数据插入到表中,由于无需更新表索引,因此这将非常快。 - 如果将来只是读取改表,运行
myisampack
让数据表变得更小点。详情查看"15.1.3.3 Compressed Table Characteristics"。 - 运行
myisamchk -r -q /path/to/db/tbl_name
重建索引。创建的索引树在写入磁盘前先保存在内存中,这省去了磁盘搜索,因此速度快多了。重建后的索引树分布非常均衡。 - 执行
FLUSH TABLES
语句或mysqladmin flush-tables
命令。
注意,LOAD DATA INFILE
将数据插入一个空表时,也会做前接优化;主要的不同在于:运行myisamchk
会分配更多的临时内存用于创建索引,而执行LOAD DATA INFILE
命令则是让数据库服务器分配内存用于重建索引。从 MySQL 4.0 起,可以运行ALTER TABLE tbl_name DISABLE KEYS
来代替myisamchk --keys-used=0 -rq /path/to/db/tbl_name
,运行ALTER TABLE tbl_name ENABLE KEYS
代替myisamchk -r -q /path/to/db/tbl_name
。这么做就可以省去FLUSH TABLES
步骤。 - 用
- 可以在锁表后,一起执行几个语句来加速
INSERT
操作:LOCK TABLES a WRITE; INSERT INTO a VALUES (1,23),(2,34),(4,33); INSERT INTO a VALUES (8,26),(6,29); UNLOCK TABLES;这对性能提高的好处在于:直到所有的C 1 does 1000 inserts C 2, 3, and 4 do 1 insert C 5 does 1000 insertsINSERT
语句都完成之后,索引缓存一次性刷新到磁盘中。通常情况是,多有少次INSERT
语句就会有多数次索引缓存刷新到磁盘中的开销。如果能在一个语句中一次性插入多个值的话,显示的锁表操作也就没必要了。对事务表而言,用BEGIN/COMMIT
代替LOCK TABLES
来提高速度。锁表也回降低多次连接测试的总时间,尽管每个独立连接为了等待锁的最大等待时间也会增加。例如:如果没有锁表,则连接2,3,4会在1,5之前就做完了。如果锁表了,则连接2,3,4可能在1,5之后才能完成,但是总时间可能只需要40%。MySQL的INSERT
,UPDATE
,DELETE
操作都非常快,不过在一个语句中如果有超过5个插入或者更新时最好加锁以得到更好的性能。如果要一次性做很多个插入,最好是在每个循环(大约1000次)的前后加上LOCK TABLES
和UNLOCK TABLES
,从而让其他进程也能访问数据表;这么做性能依然不错。INSERT
总是比LOAD DATA INFILE
插入数据来得慢,因为二者的实现策略有着分明的不同。 - 想要让
MyISAM
表更快,在LOAD DATA
和
INFILEINSERT
时都可以增加系统变量key_buffer_size
的值,详情请看"7.5.2 Tuning Server Parameters"。
7.2.13 加速 UPDATE
UPDATE
语句的优化和
SELECT
一样,只不过它多了额外的写入开销。写入的开销取决于要更新的记录数以及索引数。如果索引没有发生变化,则就无需更新。
MyISAM
表中使用了动态的记录格式,那么记录被更新为更长之后就可能会被拆分。如果经常做这个,那么偶尔做一次
OPTIMIZE TABLE
就显得非常重要了。详情请看"
14.5.2.5 OPTIMIZE TABLE Syntax"。
7.2.14 加速 DELETE
7.2.15 其他优化点子
- 使用永久连接到数据库,避免连接的开销。如果需要初始化很多连接,而又不能用永久连接,那么可以修改变量
thread_cache_size
的值,详情请看"7.5.2 Tuning Server Parameters"。 - 总是检查查询是否利用了表中已有的索引。在MySQL中,可以用
EXPLAIN
语句来分析。详情请看"7.2.1 EXPLAIN Syntax (Get Information About a SELECT)"。 - 尽量不要在经常需要更新的
MyISAM
表上用太过复杂的SELECT
语句,这是为了避免在读和写之间争夺锁。 - 在
MyISAM
表中,如果没有正在删除记录,则可以在其他查询正在读取数据的同时插入记录。如果这种情况十分重要,那么就要尽量在表没有删除记录时才使用表。另一个可能的办法就是在删除一大堆记录之后执行OPTIMIZE TABLE
语句。 - 如果总是需要按照
expr1, expr2, ...
的顺序取得记录,那么请使用ALTER TABLE ... ORDER BY expr1, expr2, ...
修改表。通过这种方法扩充修改表之后,就可能获得更高的性能表现。 - 在一些情况下,让一个字段类型是 ``hashed`` ,它基于其他字段信息。如果这个字段比较短而且基本上都是唯一值的话,那么就可能会比在几个字段上使用一个大索引来得更快,很简单的就能使用这样的额外字段,如下:SELECT * FROM tbl_name WHERE hash_col=MD5(CONCAT(col1,col2)) AND col1='c AND col2='constant';
- 如果
MyISAM
表经常大量修改,那么要尽量避免修改所有的变长字段(VARCHAR, BLOB,TEXT
)。尽管表中只有一个变长字段,它也会采用动态记录格式的。详情请看"15 MySQL Storage Engines and Table Types"。 - 通常情况下,当数据表记录变 ``大`` 之后,将表拆分成几个不同的表并没有多大用处。访问一条记录是最大的性能点在于磁盘搜索时找到记录的第一个字节上。只要找到记录的位置后,现在的大部分磁盘对于大部分的应用程序来说都能很快的读取到记录。将
MyISAM
表拆分成多个唯一有关系的情况是,数据表中动态格式的字段(见上)就可以被修改成固定大小的记录,或者需要频繁的扫描表,但是却不需要读取出大部分的字段。详情请看"15 MySQL Storage Engines and Table Types"。 - 如果需要频繁的对一个表做基于很多字段信息的统计信息的话,那么可能新建一个表来存储这些实时更新的统计结果会更好。类似下面的更新就会非常快了:UPDATE tbl_name SET count_col=count_col+1 WHERE key_col=constant;
如果只需要表级锁(多个读/一个写),那么采用
MyISAM
存储引擎就非常重要了,例如MyISAM
和ISAM
表。这在很多的数据库中也会有不错的性能表现,因为行级锁管理程序在这种情况下也基本上没什么用。 - 如果需要从很大的日志表中搜集统计信息的话,可以用摘要表来代替扫描整个日志表。维护摘要表比保持 ``实时`` 的统计信息来得更快。当事情发生变化时(比如商业决策),重新建里摘要表比修改运营中的应用程序快多了。
- 如果可能,最好是分类报告 ``实时`` 还是 ``统计`` 的,报告所需要的数据只需要来自摘要表,摘要表的信息则是周期的从实时数据中产生。
- 应该认识到一个优点就是字段有默认值。当要插入的值和默认值不一致时才需要明确指定。这就省去了MySQL需要来提高插入速度这步了。
- 在一些情况下,将数据组装存储在
BLOB
类型字段中更方便。那么在应用程序中就需要增加额外的命令来组装和拆开BLOB
字段中的值,不过这么做在一些时候就可以节省很多存储开销。这在数据无需遵从记录-和-字段
格式的表结构是很实用。 - 通常地,应该保存所有的冗余数据(在数据库原理中叫做"第三范式")。然而,为了能取得更高的效率复制一些信息或者创建摘要表也是划算的。
- 存储过程或者
UDFs
(用户定义函数) 的方式在执行一些任务时可能性能更高。尽管如此,当数据库不支持这些特性时,还是有其他的替代方法可以达到目的,即使它们有点慢。 - 可以从查询缓存或应答中取得结果,然后将很多次的插入及更新操作放在一起做。如果数据库支持表锁(如MySQL和ORACLE),那么这就可以确保索引缓存在所有的更新操作之后只需要刷新一次。
- 当不需要直到数据什么时候写入表中时,可以用
INSERT DELAYED
。这就会提高速度,因为多条记录同时在一起做一次磁盘写入操作。 - 当想让
SELECT
语句的优先级比插入操作还高时,用INSERT LOW_PRIORITY
。
- 用
SELECT HIGH_PRIORITY
来使检索记录跳过队列,也就是说即使有其他客户端正要写入数据,也会先让SELECT
执行完。
- 在一条
INSERT
语句中采用多重记录插入格式(很多数据库都支持)。
- 用
LOAD DATA INFILE
来导入大量数据,这比INSERT
快。
- 用
AUTO_INCREMENT
字段来生成唯一值。
- 定期执行
OPTIMIZE TABLE
防止使用动态记录格式的MyISAM
表产生碎片。详情请看"15.1.3MyISAM
Table Storage Formats"。
- 采用
HEAP
表,它可能会提高速度。详情请看"15.1.3MyISAM
Table Storage Formats"。
- 正常的WEB服务器配置中,图片文件最好以文件方式存储,只在数据库中保存文件的索引信息。这么做的原因是,通常情况下WEB服务器对于文件的缓存总是做的比数据库来得好,因此使用文件存储会让系统更容易变得更快。
- 对于频繁访问的不是很重要的数据,可以保存在内存表中,例如对那些web客户端不能保存cookies时用于保存最后一次显示的标题等信息。
- 在不同表中值相同的字段应该将它们声明为一样的类型。在 MySQL 3.23 之前,不这么做的话在表连接时就会比较慢。让字段名尽可能简单,例如,在一个叫做
customer
的表中,用name
来代替customer_name
作为字段名。为了让字段名在其他数据库系统中也能移植,应该保持在18个字符长度以内。
- 如果需要真正的高速,建议看看各种数据库服务器支持的底层数据存储接口之间的区别。例如,通过直接访问MySQL的
MyISAM
存储引擎,会比通过其他的SQL接口快2-5倍。这要求数据必须和应用程序在同一个服务器上,并且它通常只被一个进程访问(因为外部文件锁确实慢)。只用一个进程就可以消除在MySQL服务器上引入底层的MyISAM
指令引发的问题了(这容易获得更高性能,如果需要的话)。由于数据库接口设计的比较细心,就很容易支持这种优化方式了。
- 如果使用数字型数据的话,在很多情况下想要访问数据库(使用在线连接)的信息会比采用文本文件来得快。由于数字型信息相比文本文件在数据库中存储的更加紧凑,因此访问时只需要更少的磁盘搜索。而且在应用程序中也可以节省代码,因为无需解析文本文件以找到对应的行和字段。
- 数据库复制对一些操作会有性能上的益处。可以将客户端从多个复制服务器上取得数据,这就能将负载分摊了。为了避免备份数据时会让主服务器变慢,还可以将备份放在从服务器上。详情请看"6 Replication in MySQL"。
- 定义
MyISAM
表时增加选项DELAY_KEY_WRITE=1
,这样的话就会另索引更新更快,因为只有等到数据表关闭了才会刷新磁盘。不过缺点是可能会在数据表还打开时服务器被杀死,可以使用参数--myisam-recover
来保证数据的安全,或者在数据库重启前运行myisamchk
命令(尽管如此,在这种情况下,使用DELAY_KEY_WRITE
的话也不会丢失任何东西,因为索引总是可以从数据中重新生成)。