manual_performance.html

MySQL参考手册中文版

</pre>

<p>如果一个多列索引存在于<code>col1</code>和<code>col2</code>上，适当的行可以直接被取出。如果分开的单行列索引存在于<code>col1</code>和<code>col2</code>上，优化器试图通过决定哪个索引将找到更少的行并来找出更具限制性的索引并且使用该索引取行。 
</p>

<p><a NAME="IDX624"></a>如果表有一个多列索引，任何最左面的索引前缀能被优化器使用以找出行。例如，如果你有一个3行列索引<code>(col1,col2,col3)</code>，你已经索引了在<code>(col1)</code>、<code>(col1,col2)</code>和<code>(col1,col2,col3)</code>上的搜索能力。 
</p>

<p>如果列不构成索引的最左面前缀，<strong>MySQL</strong>不能使用一个部分的索引。假定你下面显示的<code>SELECT</code>语句： 
</p>

<pre>mysql&gt; SELECT * FROM tbl_name WHERE col1=val1;
mysql&gt; SELECT * FROM tbl_name WHERE col2=val2;
mysql&gt; SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;
</pre>

<p>如果一个索引存在于<code>(col1、col2、col3)</code>上，只有上面显示的第一个查询使用索引。第二个和第三个查询确实包含索引的列，但是<code>(col2)</code>和<code>(col2、col3)</code>不是<code>(col1、col2、col3)</code>的最左面前缀。 
</p>

<p>如果<code>LIKE</code>参数是一个不以一个通配符字符起始的一个常数字符串，<a NAME="IDX626"></a><strong>MySQL</strong>也为<code>LIKE</code>比较使用索引。例如，下列<code>SELECT</code>语句使用索引： 
</p>

<pre>mysql&gt; select * from tbl_name where key_col LIKE &quot;Patrick%&quot;;
mysql&gt; select * from tbl_name where key_col LIKE &quot;Pat%_ck%&quot;;
</pre>

<p>在第一条语句中，只考虑有<code>&quot;Patrick&quot; &lt;= key_col &lt; 
&quot;Patricl&quot;</code>的行。在第二条语句中，只考虑有<code>&quot;Pat&quot; 
&lt;= key_col &lt; &quot;Pau&quot;</code>的行。 </p>

<p>下列<code>SELECT</code>语句将不使用索引： </p>

<pre>mysql&gt; select * from tbl_name where key_col LIKE &quot;%Patrick%&quot;;
mysql&gt; select * from tbl_name where key_col LIKE other_col;
</pre>

<p>在第一条语句中，<code>LIKE</code>值以一个通配符字符开始。在第二条语句中，<code>LIKE</code>值不是一个常数。 
</p>

<p><a NAME="IDX630"></a><a NAME="IDX631"></a>如果 column_name 是一个索引，使用<code>column_name 
IS NULL</code>的搜索将使用索引。 </p>

<p><strong>MySQL</strong>通常使用找出最少数量的行的索引。一个索引被用于你与下列操作符作比较的列：<code>=</code>、<code>&gt;</code>、<code>&gt;=</code>、<code>&lt;</code>、<code>&lt;=</code>、<code>BETWEEN</code>和一个有一个非通配符前缀象<code>'something%'</code>的<code>LIKE</code>的列。 
</p>

<p>任何不跨越的在<code>WHERE</code>子句的所有<code>AND</code>层次的索引不用来优化询问。 
</p>

<p>下列<code>WHERE</code>子句使用索引：</p>

<pre>... WHERE index_part1=1 AND index_part2=2
... WHERE index=1 OR A=10 AND index=2      /* index = 1 OR index = 2 */
... WHERE index_part1='hello' AND index_part_3=5
          /* optimized like &quot;index_part1='hello'&quot; */
</pre>

<p>这些<code>WHERE</code>子句<strong>不</strong>使用索引： </p>

<pre>... WHERE index_part2=1 AND index_part3=2  /* index_part_1 is not used */
... WHERE index=1 OR A=10                  /* No index */
... WHERE index_part1=1 OR index_part2=10  /* No index spans all rows */
</pre>

<h2><a NAME="Query_Speed" HREF="manual_toc.html#Query_Speed">10.5 
存取或更新数据的查询速度</a></h2>

<p>首先，一件事情影响所有的询问。你有的许可系统设置越复杂，你得到更多的开销。</p>

<p>如果你不让任何<code>GRANT</code>语句执行，<strong>MySQL</strong>将稍微优化许可检查。因此如果你有很大量，值得花时间来避免授权，否则更多的许可检查有更大的开销。 
</p>

<p>如果你的问题是与一些明显的<strong>MySQL</strong>函数有关，你总能在<strong>MySQL</strong>客户中计算其时间：</p>

<pre>mysql&gt; select benchmark(1000000,1+1);
+------------------------+
| benchmark(1000000,1+1) |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.32 sec)
</pre>

<p>上面显示<strong>MySQL</strong>能在<code>PentiumII 400MHz</code>上以0.32秒执行1,000,000个<code>+</code>表达式。 
</p>

<p>所有<strong>MySQL</strong>函数应该被高度优化，但是以可能有一些例外并且<code>benchmark(loop_count,expression)</code>是找出是否你的查询有问题的一个极好工具。</p>

<h3><a NAME="Estimating_performance" HREF="manual_toc.html#Estimating_performance">10.5.1 
估计查询性能</a></h3>

<p>在大多数情况下，你能通过计算磁盘寻道估计性能。对小的表，你通常能在1次磁盘寻道中找到行(因为这个索引可能被缓冲)。对更大的表，你能估计它(使用 
B++ 树索引)，你将需要：<code>log(row_count)/log(index_block_length/3*2/(index_length 
+ data_pointer_length))+1</code>次寻道找到行。 </p>

<p>在<strong>MySQL</strong>中，索引块通常是1024个字节且数据指针通常是4个字节，这对一个有一个索引长度为3(中等整数)的 
500,000 行的表给你：<code>log(500,000)/log(1024/3*2/(3+4)) + 1</code>= 4 
次寻道。 </p>

<p>象上面的索引将要求大约 500,000 * 7 * 3/2 = 5.2M，(假设索引缓冲区被充满到2/3(它是典型的))，你将可能在内存中有索引的大部分并且你将可能仅需要1-2调用从OS读数据来找出行。 
</p>

<p>然而对于写，你将需要 4 次寻道请求(如上)来找到在哪儿存放新索引并且通常需2次寻道更新这个索引并且写入行。 
</p>

<p>注意，上述不意味着你的应用程序将缓慢地以 N log N 
退化！当表格变得更大时，只要一切被OS或SQL服务器缓冲，事情将仅仅或多或少地更慢。在数据变得太大不能被缓冲后，事情将开始变得更慢直到你的应用程序仅仅受磁盘寻道限制(它以N 
log N增加)。为了避免这个增加，索引缓冲随数据增加而增加。见<a HREF="manual_Performance.html#Server_parameters">10.2.3 调节服务器参数</a>。</p>

<h3><a NAME="SELECT_speed" HREF="manual_toc.html#SELECT_speed">10.5.2 <code>SELECT</code>查询的速度</a></h3>

<p>总的来说，当你想要使一个较慢的<code>SELECT ... WHERE</code>更快，检查的第一件事情是你是否能增加一个索引。见<a HREF="manual_Performance.html#MySQL_indexes">10.4 MySQL 索引的使用</a>。在不同表之间的所有引用通常应该用索引完成。你可以使用<code>EXPLAIN</code>来确定哪个索引用于一条<code>SELECT</code>语句。见<a HREF="manual_Reference.html#EXPLAIN">7.22<code> EXPLAIN</code>句法(得到关于一条<code>SELECT</code>的信息)</a>。 
</p>

<p>一些一般的建议： 

<ul>
  <li>为了帮助<strong>MySQL</strong>更好地优化查询，在它已经装载了相关数据后，在一个表上运行<code>myisamchk 
    --analyze</code>。这为每一个更新一个值，指出有相同值地平均行数（当然，对唯一索引，这总是1。）</li>
  <li>为了根据一个索引排序一个索引和数据，使用<code>myisamchk --sort-index 
    --sort-records=1</code>（如果你想要在索引1上排序)。如果你有一个唯一索引，你想要根据该索引地次序读取所有的记录，这是使它更快的一个好方法。然而注意，这个排序没有被最佳地编写，并且对一个大表将花很长时间！ 
  </li>
</ul>

<h3><a NAME="Where_optimizations" HREF="manual_toc.html#Where_optimizations">10.5.3 MySQL怎样优化<code>WHERE</code>子句</a></h3>

<p>where优化被放在<code>SELECT</code>中，因为他们最主要在那里使用里，但是同样的优化被用于<code>DELETE</code>和<code>UPDATE</code>语句。</p>

<p>也要注意，本节是不完全的。<strong>MySQL</strong>确实作了许多优化而我们没有时间全部记录他们。 
</p>

<p>由<strong>MySQL</strong>实施的一些优化列在下面： 

<ul>
  <li>删除不必要的括号： <pre>   ((a AND b) AND c OR (((a AND b) AND (c AND d))))
-&gt; (a AND b AND c) OR (a AND b AND c AND d)
</pre>
  </li>
  <li>常数调入： <pre>   (a&lt;b AND b=c) AND a=5
-&gt; b&gt;5 AND b=c AND a=5
</pre>
  </li>
  <li>删除常数条件(因常数调入所需)：<pre>   (B&gt;=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
-&gt; B=5 OR B=6
</pre>
  </li>
  <li>索引使用的常数表达式仅计算一次。 </li>
  <li>在一个单个表上的没有一个<code>WHERE</code>的<code>COUNT(*)</code>直接从表中检索信息。当仅使用一个表时，对任何<code>NOT 
    NULL</code>表达式也这样做。 </li>
  <li>无效常数表达式的早期检测。<strong>MySQL</strong>快速检测某些<code>SELECT</code>语句是不可能的并且不返回行。 
  </li>
  <li>如果你不使用<code>GROUP BY</code>或分组函数(<code>COUNT()</code>、<code>MIN()</code>……)，<code>HAVING</code>与<code>WHERE</code>合并。</li>
  <li>为每个子联结(sub join)，构造一个更简单的<code>WHERE</code>以得到一个更快的<code>WHERE</code>计算并且也尽快跳过记录。 
    <a NAME="IDX633"></a> <a NAME="IDX634"></a> </li>
  <li>所有常数的表在查询中的任何其他表前被首先读出。一个常数的表是： 
    <ul>
      <li>一个空表或一个有1行的表。 </li>
      <li>与在一个<code>UNIQUE</code>索引、或一个<code>PRIMARY KEY</code>的<code>WHERE</code>子句一起使用的表，这里所有的索引部分使用一个常数表达式并且索引部分被定义为<code>NOT 
        NULL</code>。 </li>
    </ul>
    <p>所有下列的表用作常数表： </p>
    <pre>mysql&gt; SELECT * FROM t WHERE primary_key=1;
mysql&gt; SELECT * FROM t1,t2
           WHERE t1.primary_key=1 AND t2.primary_key=t1.id;
</pre>
  </li>
  <li>对联结表的最好联结组合是通过尝试所有可能性来找到:(。如果所有在<code>ORDER 
    BY</code>和<code>GROUP BY</code>的列来自同一个表，那么当廉洁时，该表首先被选中。 
  </li>
  <li>如果有一个<code>ORDER BY</code>子句和一个不同的<code>GROUP BY</code>子句，或如果<code>ORDER 
    BY</code>或<code>GROUP BY</code>包含不是来自联结队列中的第一个表的其他表的列，创建一个临时表。 
  </li>
  <li>如果你使用<code>SQL_SMALL_RESULT</code>，<strong>MySQL</strong>将使用一个在内存中的表。 
  </li>
  <li>因为<code>DISTINCT</code>被变换到在所有的列上的一个<code>GROUP BY</code>，<code>DISTINCT</code>与<code>ORDER 
    BY</code>结合也将在许多情况下需要一张临时表。 </li>
  <li>每个表的索引被查询并且使用跨越少于30% 
    的行的索引。如果这样的索引没能找到，使用一个快速的表扫描。 </li>
  <li>在一些情况下，<strong>MySQL</strong>能从索引中读出行，甚至不咨询数据文件。如果索引使用的所有列是数字的，那么只有索引树被用来解答查询。 
  </li>
  <li>在每个记录被输出前，那些不匹配<code>HAVING</code>子句的行被跳过。 
  </li>
</ul>