manual_performance.html

MySQL参考手册中文版

<p>下面是一些很快的查询例子： </p>

<pre>mysql&gt; SELECT COUNT(*) FROM tbl_name;
mysql&gt; SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;
mysql&gt; SELECT MAX(key_part2) FROM tbl_name
           WHERE key_part_1=constant;
mysql&gt; SELECT ... FROM tbl_name
           ORDER BY key_part1,key_part2,... LIMIT 10;
mysql&gt; SELECT ... FROM tbl_name
           ORDER BY key_part1 DESC,key_part2 DESC,... LIMIT 10;
</pre>

<p>下列查询仅使用索引树就可解决(假设索引列是数字的)： </p>

<pre>mysql&gt; SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;
mysql&gt; SELECT COUNT(*) FROM tbl_name
           WHERE key_part1=val1 AND key_part2=val2;
mysql&gt; SELECT key_part2 FROM tbl_name GROUP BY key_part1;
</pre>

<p>下列查询使用索引以排序顺序检索，不用一次另外的排序： </p>

<pre>mysql&gt; SELECT ... FROM tbl_name ORDER BY key_part1,key_part2,...
mysql&gt; SELECT ... FROM tbl_name ORDER BY key_part1 DESC,key_part2 DESC,...
</pre>

<h3><a NAME="LEFT_JOIN_optimization" HREF="manual_toc.html#LEFT_JOIN_optimization">10.5.4 
MySQL怎样优化<code>LEFT JOIN</code></a></h3>

<p>在<strong>MySQL</strong><code>中，A LEFT JOIN B</code>实现如下： 

<ul>
  <li>表<code>B</code>被设置为依赖于表<code>A</code>。 </li>
  <li>表<code>A</code>被设置为依赖于所有用在<code>LEFT JOIN</code>条件的表(除<code>B</code>外)。 
  </li>
  <li>所有<code>LEFT JOIN</code>条件被移到<code>WHERE</code>子句中。 </li>
  <li>进行所有标准的联结优化，除了一个表总是在所有它依赖的表之后被读取。如果有一个循环依赖，<strong>MySQL</strong>将发出一个错误。 
  </li>
  <li>进行所有标准的<code>WHERE</code>优化。 </li>
  <li>如果在<code>A</code>中有一行匹配<code>WHERE</code>子句，但是在<code>B</code>中没有任何行匹配<code>LEFT 
    JOIN</code>条件，那么在<code>B</code>中生成所有列设置为<code>NULL</code>的一行。 
  </li>
  <li>如果你使用<code>LEFT JOIN</code>来找出在某些表中不存在的行并且在<code>WHERE</code>部分你有下列测试：<code>column_name 
    IS NULL</code>，这里column_name 被声明为<code>NOT NULL</code>的列，那么<code>MySQL</code>在它已经找到了匹配<code>LEFT 
    JOIN</code>条件的一行后，将停止在更多的行后寻找(对一特定的键组合)。 
  </li>
</ul>

<h3><a NAME="LIMIT_optimization" HREF="manual_toc.html#LIMIT_optimization">10.5.5 MySQL怎样优化<code>LIMIT</code></a></h3>

<p>在一些情况中，当你使用<code>LIMIT #</code>而不使用<code>HAVING</code>时，<strong>MySQL</strong>将以不同方式处理查询。 

<ul>
  <li>如果你用<code>LIMIT</code>只选择一些行，当<strong>MySQL</strong>一般比较喜欢做完整的表扫描时，它将在一些情况下使用索引。 
  </li>
  <li>如果你使用<code>LIMIT #</code>与<code>ORDER BY</code>，<strong>MySQL</strong>一旦找到了第一个<code> 
    #</code> 行，将结束排序而不是排序整个表。 </li>
  <li>当结合<code>LIMIT #</code>和<code>DISTINCT</code>时，<strong>MySQL</strong>一旦找到<code>#</code>个唯一的行，它将停止。 
  </li>
  <li>在一些情况下，一个<code>GROUP BY</code>能通过顺序读取键(或在键上做排序)来解决，并然后计算摘要直到键值改变。在这种情况下，<code>LIMIT 
    #</code>将不计算任何不必要的<code>GROUP</code>。 </li>
  <li>只要<code>MySQL</code>已经发送了第一个<code>#</code>行到客户，它将放弃查询。 
  </li>
  <li><code>LIMIT 0</code>将总是快速返回一个空集合。这对检查查询并且得到结果列的列类型是有用的。 
  </li>
  <li>临时表的大小使用<code>LIMIT #</code>计算需要多少空间来解决查询。 </li>
</ul>

<h3><a NAME="Insert_speed" HREF="manual_toc.html#Insert_speed">10.5.6 <code>INSERT</code>查询的速度</a></h3>

<p>插入一个记录的时间由下列组成： 

<ul>
  <li>连接：(3) </li>
  <li>发送查询给服务器：(2) </li>
  <li>分析查询：(2) </li>
  <li>插入记录：（1 x 记录大小）</li>
  <li>插入索引：（1 x 索引）</li>
  <li>关闭：(1) </li>
</ul>

<p>这里的数字有点与总体时间成正比。这不考虑打开表的初始开销(它为每个并发运行的查询做一次)。 
</p>

<p>表的大小以N log N (B 树)的速度减慢索引的插入。 </p>

<p>加快插入的一些方法： 

<ul>
  <li>如果你同时从同一客户插入很多行，使用多个值表的<code>INSERT</code>语句。这比使用分开<code>INSERT</code>语句快(在一些情况中几倍)。</li>
  <li>如果你从不同客户插入很多行，你能通过使用<code>INSERT DELAYED</code>语句得到更高的速度。见<a HREF="manual_Reference.html#INSERT">7.14<code> INSERT</code>句法</a>。 </li>
  <li>注意，用<code>MyISAM</code>，如果在表中没有删除的行，能在<code>SELECT</code>:s正在运行的同时插入行。 
  </li>
  <li>当从一个文本文件装载一个表时，使用<code>LOAD DATA INFILE</code>。这通常比使用很多<code>INSERT</code>语句快20倍。见<a HREF="manual_Reference.html#LOAD_DATA">7.16<code> LOAD DATA INFILE</code>句法</a>。 </li>
  <li>当表有很多索引时，有可能多做些工作使得<code>LOAD DATA INFILE</code>更快些。使用下列过程： 
    <ol>
      <li>有选择地用<code>CREATE TABLE</code>创建表。例如使用<code>mysql</code>或Perl-DBI。 
      </li>
      <li>执行<code>FLUSH TABLES</code>，或外壳命令<code>mysqladmin flush-tables</code>。 
      </li>
      <li>使用<code>myisamchk --keys-used=0 -rq /path/to/db/tbl_name</code>。这将从表中删除所有索引的使用。 
      </li>
      <li>用<code>LOAD DATA INFILE</code>把数据插入到表中，这将不更新任何索引，因此很快。</li>
      <li>如果你有<code>myisampack</code>并且想要压缩表，在它上面运行<code>myisampack</code>。见<a HREF="manual_Performance.html#Compressed_format">10.6.3 压缩表的特征</a>。 </li>
      <li>用<code>myisamchk -r -q /path/to/db/tbl_name</code>再创建索引。这将在将它写入磁盘前在内存中创建索引树，并且它更快，因为避免大量磁盘寻道。结果索引树也被完美地平衡。 
      </li>
      <li>执行<code>FLUSH TABLES</code>，或外壳命令<code>mysqladmin flush-tables</code>。</li>
    </ol>
    <p>这个过程将被构造进在MySQL的某个未来版本的<code>LOAD DATA INFILE</code>。</p>
  </li>
  <li>你可以锁定你的表以加速插入。<pre>mysql&gt; LOCK TABLES a WRITE;
mysql&gt; INSERT INTO a VALUES (1,23),(2,34),(4,33);
mysql&gt; INSERT INTO a VALUES (8,26),(6,29);
mysql&gt; UNLOCK TABLES;
</pre>
    <p>主要的速度差别是索引缓冲区仅被清洗到磁盘上一次，在所有<code>INSERT</code>语句完成后。一般有与有不同的<code>INSERT</code>语句那样夺的索引缓冲区清洗。如果你能用一个单个语句插入所有的行，锁定就不需要。锁定也将降低多连接测试的整体时间，但是对某些线程最大等待时间将上升(因为他们等待锁)。例如：</p>
    <pre>thread 1 does 1000 inserts
thread 2, 3, and 4 does 1 insert
thread 5 does 1000 inserts
</pre>
    <p>如果你不使用锁定，2、3和4将在1和5前完成。如果你使用锁定，2、3和4将可能不在1或5前完成，但是整体时间应该快大约40%。因为<code>INSERT</code>, 
    <code>UPDATE</code>和<code>DELETE</code>操作在<strong>MySQL</strong>中是很快的，通过为多于大约5次连续不断地插入或更新一行的东西加锁，你将获得更好的整体性能。如果你做很多一行的插入，你可以做一个<code>LOCK 
    TABLES</code>，偶尔随后做一个<code>UNLOCK TABLES</code>(大约每1000行)以允许另外的线程存取表。这仍然将导致获得好的性能。当然，<code>LOAD 
    DATA INFILE</code>对装载数据仍然是更快的。 </p>
  </li>
</ul>

<p>为了对<code>LOAD DATA INFILE</code>和<code>INSERT</code>得到一些更快的速度，扩大关键字缓冲区。见<a HREF="manual_Performance.html#Server_parameters">10.2.3 调节服务器参数</a>。</p>

<h3><a NAME="Update_speed" HREF="manual_toc.html#Update_speed">10.5.7 <code>UPDATE</code>查询的速度</a></h3>

<p>更改查询被优化为有一个写开销的一个<code>SELECT</code>查询。写速度依赖于被更新数据大小和被更新索引的数量。 
</p>

<p>使更改更快的另一个方法是推迟更改并且然后一行一行地做很多更改。如果你锁定表，做一行一行地很多更改比一次做一个快。 
</p>

<p>注意，动态记录格式的更改一个较长总长的记录，可能切开记录。因此如果你经常这样做，时不时地<code>OPTIMIZE 
TABLE</code>是非常重要的。见<a HREF="manual_Reference.html#OPTIMIZE_TABLE">7.9<code> 
OPTIMIZE TABLE</code>句法</a>。 </p>

<h3><a NAME="Delete_speed" HREF="manual_toc.html#Delete_speed">10.5.8 <code>DELETE</code>查询的速度</a></h3>

<p>删除一个记录的时间精确地与索引数量成正比。为了更快速地删除记录，你可以增加索引缓存的大小。见<a HREF="manual_Performance.html#Server_parameters">10.2.3 调节服务器参数</a>。 </p>

<p>从一个表删除所有行比删除行的一大部分也要得多。 </p>

<p><a NAME="IDX635"></a> <a NAME="IDX636"></a> </p>

<h2><a NAME="Choosing_table_type" HREF="manual_toc.html#Choosing_table_type">10.6 
选择一种表类型</a></h2>

<p>用MySQL，当前(版本 3.23.5)你能从一个速度观点在4可用表的格式之间选择。 

<dl COMPACT="Choosing_table_type">
  <dt><strong>静态MyISAM</strong> </dt>
  <dd>这种格式是最简单且最安全的格式，它也是在磁盘格式最快的。速度来自于数据能在磁盘上被找到的难易方式。当所定有一个索引和静态格式的东西时，它很简单，只是行长度乘以行数量。而且在扫描一张表时，用每次磁盘读取来读入常数个记录是很容易的。安全性来自于如果当写入一个静态MyISAM文件时，你的计算机崩溃，<code>myisamchk</code>能很容易指出每行在哪儿开始和结束，因此它通常能回收所有记录，除了部分被写入的那个。注意，在MySQL中，所有索引总能被重建。</dd>
  <dt><strong>动态MyISAM</strong> </dt>
  <dd>这种格式有点复杂，因为每一行必须有一个头说明它有多长。当一个记录在更改时变长时，它也可以在多于一个位置上结束。你能使用<code>OPTIMIZE 
    table</code>或<code>myisamchk</code>整理一张表。如果你在同一个表中有象某些<code>VARCHAR</code>或<code>BLOB</code>列那样存取/改变的静态数据，将动态列移入另外一个表以避免碎片可能是一个好主意。 
  </dd>
  <dt><strong>压缩MyISAM</strong> </dt>
  <dd>这是一个只读类型，用可选的<code>myisampack</code>工具生成。 </dd>
  <dt><strong>内存(HEAP 堆）</strong> </dt>
  <dd>这种表格式对小型/中型查找表十分有用。对拷贝/创建一个常用的查找表(用联结)到一个(也许临时)HEAP表有可能加快多个表联结。假定我们想要做下列联结，用同样数据可能要几倍时间。 
    <pre>SELECT tab1.a, tab3.a FROM tab1, tab2, tab3
        WHERE tab1.a = tab2.a and tab2.a = tab3.a and tab2.c != 0;
</pre>
    <p>为了加速它，我们可用tab2和tab3的联结创建一张临时表，因为用相同列( 
    tab1.a )查找。这里是创建该表和结果选择的命令。 </p>
    <pre>CREATE TEMPORARY TABLE test TYPE=HEAP
        SELECT
                tab2.a as a2, tab3.a as a3
        FROM
                tab2, tab3
        WHERE
                tab2.a = tab3.a and c = 0;
SELECT tab1.a, test.a3 from tab1, test where tab1.a = test.a1;
SELECT tab1.b, test.a3 from tab1, test where tab1.a = test.a1 and something;
</pr