manual_performance.html

详细介绍了MYSQL的主要功能几一些代码

<p>当你运行<code>mysqladmin status</code>时，你将看见象这样的一些东西： 
</p>

<pre>Uptime: 426 Running threads: 1 Questions: 11082 Reloads: 1 Open tables: 12
</pre>

<p>如果你仅有6个表，这可能有点令人困惑。 </p>

<p><strong>MySQL</strong>是多线程的，因此它可以同时在同一个表上有许多询问。为了是2个线程在同一个文件上有不同状态的问题减到最小，表由每个并发进程独立地打开。这为数据文件消耗一些内存和一个额外的文件描述符。索引文件描述符在所有线程之间共享。 
</p>

<h3><a NAME="Memory_use" HREF="manual_toc.html#Memory_use">10.2.7 MySQL怎样使用内存</a></h3>

<p>下表指出<code>mysqld</code>服务器使用存储器的一些方式。在应用的地方，给出与存储器使用相关的服务器变量的名字。 

<ul>
  <li>关键字缓冲区(变量<code>key_buffer_size</code>)由所有线程分享；当需要时，分配服务器使用的其他缓冲区。见<a HREF="manual_Performance.html#Server_parameters">10.2.3 调节服务器参数</a>。</li>
  <li>每个连接使用一些线程特定的空间；一个栈(缺省64K，变量<code>thread_stack</code>)、一个连接缓冲区(变量<code>net_buffer_length</code>)和一个结果缓冲区(变量<code>net_buffer_length</code>)。当需要时，连接缓冲区和结果缓冲区动态地被扩大到<code>max_allowed_packet</code>。当一个查询正在运行当前查询的一个拷贝时，也分配字符串。 
  </li>
  <li>所有线程共享同一基存储器。 </li>
  <li>目前还没有什么是内存映射的(除了压缩表，但是那是另外一个的故事)。这是因为4GB的32位存储器空间对最大的数据库来所不是足够大的。当一个64位寻址空间的系统变得更普遍时，我们可以为内存映射增加全面的支持。 
  </li>
  <li>每个做顺序扫描的请求分配一个读缓冲区(变量<code>record_buffer</code>)。 
  </li>
  <li>所有联结均用一遍完成并且大多数联结可以甚至不用一张临时表来完成。最临时的表是基于内存的(HEAP)表。有较大记录长度(以所有列的长度之和计算)的临时表或包含<code>BLOB</code>列的表在磁盘上存储。在<strong>MySQL</strong>版本3.23.2前一个问题是如果一张HEAP表超过<code>tmp_table_size</code>的大小，你得到错误<code>The 
    table tbl_name is full</code>。在更新的版本中，这通过必要时自动将在内存的(HEAP)表转变为一个基于磁盘(MyISAM)的表来处理。为了解决这个问题，你可以通过设置<code>mysqld</code>的<code>tmp_table_size</code>选项，或通过在客户程序中设置SQL的<code>SQL_BIG_TABLES</code>选项增加临时表的大小。见<a HREF="manual_Reference.html#SET_OPTION">7.25<code> SET OPTION</code>句法</a>。在<strong>MySQL</strong> 
    3.20中，临时表的最大尺寸是<code>record_buffer*16</code>，因此如果你正在使用这个版本，你必须增加<code>record_buffer</code>值。你也可以使用<code>--big-tables</code>选项启动<code>mysqld</code>以总将临时表存储在磁盘上，然而，这将影响许多复杂查询的速度。 
  </li>
  <li>大多数做排序的请求分配一个排序缓冲区和一个或二个临时文件。见<a HREF="manual_Problems.html#Temporary_files">18.5 MySQL在哪儿存储临时文件</a>。 </li>
  <li>几乎所有的语法分析和计算都在一家本地存储器中完成。对小项目没有内存开销并且一般的较慢存储器分配和释放被避免。内存仅为出乎意料的大字符串分配(这用<code>malloc()</code>和<code>free()</code>完成)。 
  </li>
  <li>每个索引文件只被打开一次，并且数据文件为每个并发运行的线程打开一次。对每个并发线程，分配一个表结构、对每列的列结构和大小为<code>3 
    * n</code>的一个缓冲区(这里<code>n</code>是最大的行长度，不算<code>BLOB</code>列)。一个<code>BLOB</code>使用5 
    ～ 8个字节加上<code>BLOB</code>数据。 </li>
  <li>对每个有<code>BLOB</code>列的表，一个缓冲区动态地被扩大以便读入更大的<code>BLOB</code>值。如果你扫描一个表，分配与最大<code>BLOB</code>值一样大的一个缓冲区。 
  </li>
  <li>对所有在用的表的表处理器被保存在一个缓存中并且作为一个FIFO管理。通常缓存有64个入口。如果一个表同时被2个运行的线程使用，缓存为此包含2个入口。见<a HREF="manual_Performance.html#Table_cache">10.2.4 MySQL如何打开和关闭数据库表</a>。 
  </li>
  <li>一个<code>mysqladmin flush-tables</code>命令关闭所有不在用的表并在当前执行的线程结束时，标记所有在用的表准备被关闭。这将有效地释放大多数在用的内存。 
  </li>
</ul>

<p><code>ps</code>和其他系统状态程序可以报导<code>mysqld</code>使用很多内存。这可以是在不同的内存地址上的线程栈造成的。例如，Solaris版本的<code>ps</code>将栈间未用的内存算作已用的内存。你可以通过用<code>swap 
-s</code>检查可用交换区来验证它。我们用商业内存漏洞探查器测试了<code>mysqld</code>，因此应该有没有内存漏洞。 
</p>

<h3><a NAME="Internal_locking" HREF="manual_toc.html#Internal_locking">10.2.8 MySQL怎样锁定数据库表</a></h3>

<p><strong>MySQL</strong>中所有锁定不会是死锁的。这通过总是在一个查询前立即请求所有必要的锁定并且总是以同样的顺序锁定表来管理。 
</p>

<p>对<code>WRITE</code>，<strong>MySQL</strong>使用的锁定方法原理如下： 

<ul>
  <li>如果在表上没有锁，放一个锁在它上面。 </li>
  <li>否则，把锁定请求放在写锁定队列中。 </li>
</ul>

<p>对<code>READ</code>，<strong>MySQL</strong>使用的锁定方法原理如下： 

<ul>
  <li>如果在表上没有写锁定，把一个读锁定放在它上面。 </li>
  <li>否则，把锁请求放在读锁定队列中。 </li>
</ul>

<p>当一个锁定被释放时，锁定可被写锁定队列中的线程得到，然后是读锁定队列中的线程。 
</p>

<p>这意味着，如果你在一个表上有许多更改，<code>SELECT</code>语句将等待直到有没有更多的更改。 
</p>

<p>为了解决在一个表中进行很多<code>INSERT</code>和<code>SELECT</code>操作的情况，你可在一张临时表中插入行并且偶尔用来自临时表的记录更新真正的表。 
</p>

<p>这可用下列代码做到： </p>

<pre>mysql&gt; LOCK TABLES real_table WRITE, insert_table WRITE;
mysql&gt; insert into real_table select * from insert_table;
mysql&gt; delete from insert_table;
mysql&gt; UNLOCK TABLES;
</pre>

<p>如果你在一些特定的情况字下区分检索的优先次序，你可以使用<code>LOW_PRIORITY</code>选项的<code>INSERT</code>。见<a HREF="manual_Reference.html#INSERT">7.14<code> INSERT</code>句法</a>。 </p>

<p>你也能改变在<tt>“mysys/thr_lock.c”</tt>中的锁代码以使用一个单个队列。在这种情况下，写锁定和读锁定将有同样优先级，它可能帮助一些应用程序。 
</p>

<h3><a NAME="Table_locking" HREF="manual_toc.html#Table_locking">10.2.9 
数据库表级锁定的问题</a></h3>

<p><strong>MySQL</strong>的表锁定代码是不会死锁的。 </p>

<p><strong>MySQL</strong>使用表级锁定(而不是行级锁定或列级锁定)以达到很高的锁定速度。对于大表，表级锁定对大多数应用程序来说比行级锁定好一些，但是当然有一些缺陷。 
</p>

<p>在<strong>MySQL</strong>3.23.7和更高版本中，一个人能把行插入到<code>MyISAM</code>表同时其他线程正在读该表。注意，目前只有在表中内有删除的行时才工作。 
</p>

<p>表级锁定使很多线程能够同时读一个表，但是如果一个线程想要写一个表，它必须首先得到独占存取权。在更改期间，所有其他想要存取该特定表的线程将等到更改就绪。 
</p>

<p>因为数据库的更改通常被视为比<code>SELECT</code>更重要，更新一个表的所有语句比从一个表中检索信息的语句有更高的优先级。这应该保证更改不被“饿死”，因为一个人针对一个特定表会发出很多繁重的查询。 
</p>

<p>从<strong>MySQL 3.23.7</strong>开始，一个人可以能使用<code>max_write_lock_count</code>变量<strong>强制MySQL</strong>在一个表上一个特定数量的插入后发出一个<code>SELECT</code>。 
</p>

<p>对此一个主要的问题如下： 

<ul>
  <li>一个客户发出一个花很长时间运行的<code>SELECT</code>。 </li>
  <li>然后其他客户在一个使用的表上发出一个<code>UPDATE</code>；这个客户将等待直到<code>SELECT</code>完成。</li>
  <li>另一个客户在同一个表上发出另一个<code>SELECT</code>语句；因为<code>UPDATE</code>比<code>SELECT</code>有更高的优先级，该<code>SELECT</code>将等待<code>UPDATE</code>的完成。它也将等待第一个<code>SELECT</code>完成！ 
  </li>
</ul>

<p>对这个问题的一些可能的解决方案是： 

<ul>
  <li>试着使<code>SELECT</code>语句运行得更快；你可能必须创建一些摘要(summary)表做到这点。 
  </li>
  <li>用<code>--low-priority-updates</code>启动<code>mysqld</code>。这将给所有更新(修改)一个表的语句以比<code>SELECT</code>语句低的优先级。在这种情况下，在先前情形的最后的<code>SELECT</code>语句将在<code>INSERT</code>语句前执行。 
  </li>
  <li>你可以用<code>LOW_PRIORITY</code>属性给与一个特定的<code>INSERT</code>、<code>UPDATE</code>或<code>DELETE</code>语句较低优先级。 
  </li>
  <li>为<strong>max_write_lock_count</strong>指定一个低值来启动<code>mysqld</code>使得在一定数量的<code>WRITE</code>锁定后给出<code>READ</code>锁定。 
  </li>
  <li>通过使用SQL命令：<code>SET SQL_LOW_PRIORITY_UPDATES=1</code>，你可从一个特定线程指定所有的更改应该由用低优先级完成。见<a HREF="manual_Reference.html#SET_OPTION">7.25<code> SET OPTION</code>句法</a>。 </li>
  <li>你可以用<code>HIGH_PRIORITY</code>属性指明一个特定<code>SELECT</code>是很重要的。见<a HREF="manual_Reference.html#SELECT">7.12<code> SELECT</code>句法</a>。 </li>
  <li>如果你有关于<code>INSERT</code>结合<code>SELECT</code>的问题，切换到使用新的<code>MyISAM</code>表，因为它们支持并发的<code>SELECT</code>和<code>INSERT</code>。 
  </li>
  <li>如果你主要混合<code>INSERT</code>和<code>SELECT</code>语句，<code>DELAYED</code>属性<code>的INSERT</code>将可能解决你的问题。见<a HREF="manual_Reference.html#INSERT">7.14<code> INSERT</code>句法</a>。 </li>
  <li>如果你有关于<code>SELECT</code>和<code>DELETE</code>的问题，<code>LIMIT</code>选项的<code>DELETE</code>可以帮助你。见<a HREF="manual_Reference.html#DELETE">7.11<code> DELETE</code>句法</a>。 </li>
</ul>

<h2><a NAME="Data_size" HREF="manual_toc.html#Data_size">10.3 使你的数据尽可能小</a></h2>

<p>最基本的优化之一是使你的数据(和索引)在磁盘上(并且在内存中)占据的空间尽可能小。这能给出巨大的改进，因为磁盘读入较快并且通常也用较少的主存储器。如果在更小的列上做索引，索引也占据较少的资源。 
</p>

<p>你能用下面的技术使表的性能更好并且使存储空间最小： 

<ul>
  <li>尽可能地使用最有效(最小)的类型。<strong>MySQL</strong>有很多节省磁盘空间和内存的专业化类型。 
  </li>
  <li>如果可能使表更小，使用较小的整数类型。例如，<code>MEDIUMINT</code>经常比<code>INT</code>好一些。 
  </li>
  <li>如果可能，声明列为<code>NOT NULL</code>。它使任何事情更快而且你为每列节省一位。注意如果在你的应用程序中你确实需要<code>NULL</code>，你应该毫无疑问使用它，只是避免缺省地在所有列上有它。</li>
  <li>如果你没有任何变长列(<code>VARCHAR</code>、<code>TEXT</code>或<code>BLOB</code>列)，使用固定尺寸的记录格式。这比较快但是不幸地可能会浪费一些空间。见<a HREF="manual_Performance.html#Choosing_table_type">10.6 选择一种表类型</a>。 </li>
  <li>每张桌子应该有尽可能短的主索引。这使一行的辨认容易而有效。</li>
  <li>对每个表，你必须决定使用哪种存储/索引方法。见<a HREF="manual_Server.html#Table_types">9.4 MySQL表类型</a>。也可参见<a HREF="manual_Performance.html#Choosing_table_type">10.6 选择一种表类型</a>。 </li>
  <li>只创建你确实需要的索引。索引对检索有好处但是当你需要快速存储东西时就变得糟糕。如果你主要通过搜索列的组合来存取一个表，以它们做一个索引。第一个索引部分应该是最常用的列。如果你总是使用许多列，你应该首先以更多的副本使用列以获得更好的列索引压缩。 
  </li>
  <li>如果很可能一个索引在头几个字符上有唯一的前缀，仅仅索引该前缀比较好。<strong>MySQL</strong>支持在一个字符列的一部分上的索引。更短的索引更快，不仅因为他们占较少的磁盘空间而且因为他们将在索引缓存中给你更多的命中率并且因此有更少磁盘寻道。见<a HREF="manual_Performance.html#Server_parameters">10.2.3 调节服务器参数</a>。</li>
  <li>在一些情形下，分割一个经常被扫描进2个表的表是有益的。特别是如果它是一个动态格式的表并且它可能使一个能用来扫描后找出相关行的较小静态格式的表。 
  </li>
</ul>

<h2><a NAME="MySQL_indexes" HREF="manual_toc.html#MySQL_indexes">10.4 MySQL索引的使用</a></h2>

<p>索引被用来快速找出在一个列上用一特定值的行。没有索引，MySQL不得不首先以第一条记录开始并然后读完整个表直到它找出相关的行。表越大，花费时间越多。如果表对于查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要考虑所有数据。如果一个表有1000行，这比顺序读取至少快100倍。注意你需要存取几乎所有1000行，它较快的顺序读取，因为此时我们避免磁盘寻道。 
</p>

<p>所有的MySQL索引(<code>PRIMARY</code>、<code>UNIQUE</code>和<code>INDEX</code>)在B树中存储。字符串是自动地压缩前缀和结尾空间。见<a HREF="manual_Reference.html#CREATE_INDEX">7.27<code> CREATE INDEX</code>句法</a>。 </p>

<p>索引用于： 

<ul>
  <li>快速找出匹配一个<code>WHERE</code>子句的行。 </li>
  <li>当执行联结时，从其他表检索行。 </li>
  <li>对特定的索引列找出<code>MAX()</code>或<code>MIN()</code>值。 </li>
  <li>如果排序或分组在一个可用键的最左面前缀上进行(例如，<code>ORDER 
    BY key_part_1,key_part_2</code>)，排序或分组一个表。如果所有键值部分跟随<code>DESC</code>，键以倒序被读取。 
  </li>
  <li>在一些情况中，一个查询能被优化来检索值，不用咨询数据文件。如果对某些表的所有使用的列是数字型的并且构成某些键的最左面前缀，为了更快，值可以从索引树被检索出来。 
  </li>
</ul>

<p>假定你发出下列<code>SELECT</code>语句： </p>

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