MySQL执行计划

mysql的执行计划：

explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。

使用方法，在select语句前加上explain就可以了：

如：explain select * from test1

EXPLAIN列的解释：

table：显示这一行的数据是关于哪张表的

type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL

possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

key： 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引

key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref：显示索引的哪一列被使用了，如果可能的话，是一个常数

rows：MYSQL认为必须检查的用来返回请求数据的行数

Extra：关于MYSQL如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢

extra列返回的描述的意义

Distinct:一旦MYSQL找到了与行相联合匹配的行，就不再搜索了

Not exists: MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了

Range checked for each Record（index map:#）:没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一 Using filesort: 看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行

Using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候

Using temporary 看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上

Where used 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题不同连接类型的解释（按照效率

高低的顺序排序）

system 表只有一行：system表。这是const连接类型的特殊情况

const:表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待

eq_ref:在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用

ref:这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好

range:这个连接类型使用索引返回一个范围中的行，比如使用>或查找东西时发生的情况

index: 这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一

 

第二篇：MySQL 优化(一)

MySQL 优化（一）

? 内存带宽。当cpu要将更多的数据存放在cpu缓存中时，主内存的带宽就是瓶颈了。在大多数系统中，这不是常见的瓶颈，不过也是要注意的一个因素。

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]/tech-resources/crash-me.php[/url]. 访问

[url]/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查询表现的更好或者更差。

请注意，这个基准是单线程的，因此它度量了操作执行的最少时间。我们未来打算增加多线程测试的基准套件。

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

?

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

模块。并且还需要每个要做测试的服务器上都有特定的bdb驱动程序。例如，为了测试mysql、postgresql和db2，就必须安装dbd::mysql, dbd::pg 及 dbd::db2 模块。详情请看"2.7 perl installation note"。

取得mysql的分发源代码后，就能在 `sql-bench' 目录下看到基准套件。想要运行这些基准测试，请先搭建好服务，然后进入 `sql-bench' 目录，执行 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 字段类型能支持多大

请确定对您的数据库或者应用程序做基准测试，以发现它们的瓶颈所在。解决这个瓶颈（或者使用一个假的模块来代替）之后，就能很容易地找到下一个瓶颈了。即使应用程序当前总体的表现可以接受，不过还是至少要做好找到每个瓶颈的计划，说不定某天您就希望应用程序能有更好的性能。

从mysql的基准套件中就能找到一个便携可移植的基准测试程序了。详情请看"7.1.4 the mysql benchmark suite"。您可以从基准套件中的任何一个程序，做适当的修改以适合您的需要。通过整个方式，您就可以有各种不同的办法来解决问题，知道哪个程序才是最快的。 另一个基准套件是开放源码的数据库基准，可以

在 [url][/url] 上找到。

当系统负载十分繁重的时候，通常就会发生问题。我们就有很多客户联系我们说他们有一个（测试过的）生产系统也遭遇了负载问题。在很多情况下，性能问题归结于数据库的基本设计（例如，在高负载下扫描数据表的表现不好）、操作系统、或者程序库等因素。很多时候，这些问题在还没有正式用于生产前相对更容易解决。

为了避免发生这样的问题，最好让您的应用程序在可能的最差的负载下做基准测试！可以使用super smack，在[url]/mysql/super-smack[/url] 可以找到。从它名字的意思就能想到，只要您愿意，它就能让您的系统死掉，因此确认只在开发系统上做测试。

7.2 优化 select 语句及其他查询

首先，影响所有语句的一个因素是：您的权限设置越复杂，那么开销就越大。

使用比较简单的 grant 语句能让mysql减少在客户端执行语句时权限检查的开销。例如，如果没有设定任何表级或者字段级的权限，那么服务器就无需检

查 tables_priv 和 columns_priv 表的记录了。同样地，如果没有对帐户设定任何资源限制的话，那么服务器也就无需做资源使用统计了。如果有大量查询的话，花点时间来规划简单的授权机制以减少服务器权限检查的开销是值得的。

如果问题处在一些mysql特定的表达式或者函数上，则可以通过 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() 是一个用于检查查询语句中是否存在问题的非常好的工具。