优化MySQL数据库性能的八种方法

1、选取最适用的字段属性
  MySQL可以很好的支持大数据量的存取,但是一般说来,数据库中的表越小,在它上面执行的查询也就会越快。因此,在创建表的时候,为了获得更好的
性能,我们可以将表中字段的宽度设得尽可能小。例如,在定义邮政编码这个字段时,如果将其设置为CHAR(255),显然给数据库增加了不必要的空间,
甚至使用VARCHAR这种类型也是多余的,因为CHAR(6)就可以很好的完成任务了。同样的,如果可以的话,我们应该使用MEDIUMINT而不是
BIGIN来定义整型字段。

  另外一个提高效率的方法是在可能的情况下,应该尽量把字段设置为NOT NULL,这样在将来执行查询的时候,数据库不用去比较NULL值。

  对于某些文本字段,例如“省份”或者“性别”,我们可以将它们定义为ENUM类型。因为在MySQL中,ENUM类型被当作数值型数据来处理,而数
值型数据被处理起来的速度要比文本类型快得多。这样,我们又可以提高数据库的性能。

2、使用连接(JOIN)来代替子查询(Sub-Queries)

  MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果,然后把这个结果作为过滤条件用在另一个查
询中。例如,我们要将客户基本信息表中没有任何订单的客户删除掉,就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来,然后将结果传递给
主查询,如下所示:

  DELETE FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID
FROM salesinfo )

  使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作,同时也可以避免事务或者表锁死,并且写起来也很容易。但是,有些情况下,
子查询可以被更有效率的连接(JOIN).. 替代。例如,假设我们要将所有没有订单记录的用户取出来,可以用下面这个查询完成:

  SELECT * FROM customerinfo WHERE CustomerID NOT in (SELECT
CustomerID FROM salesinfo )

  如果使用连接(JOIN).. 来完成这个查询工作,速度将会快很多。尤其是当salesinfo表中对CustomerID建有索引的话,性能将
会更好,查询如下:

  SELECT * FROM customerinfo LEFT JOIN salesinfoON
customerinfo.CustomerID=salesinfo. CustomerID WHERE
salesinfo.CustomerID IS NULL

  连接(JOIN).. 之所以更有效率一些,是因为 MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

3、使用联合(UNION)来代替手动创建的临时表

  MySQL 从 4.0 的版本开始支持 UNION 查询,它可以把需要使用临时表的两条或更多的 SELECT 查询合并的一个查询中。在客户
端的查询会话结束的时候,临时表会被自动删除,从而保证数据库整齐、高效。使用 UNION 来创建查询的时候,我们只需要用 UNION作为关键字把
多个 SELECT 语句连接起来就可以了,要注意的是所有 SELECT 语句中的字段数目要想同。下面的例子就演示了一个使用 UNION的查
询。

  SELECT Name, Phone FROM client UNION SELECT Name, BirthDate FROM
author
  UNION
  SELECT Name, Supplier FROM product

4、事务

  尽管我们可以使用子查询(Sub-Queries)、连接(JOIN)和联合(UNION)来创建各种各样的查询,但不是所有的数据库操作都可以只
用一条或少数几条SQL语句就可以完成的。更多的时候是需要用到一系列的语句来完成某种工作。但是在这种情况下,当这个语句块中的某一条语句运行出错的
时候,整个语句块的操作就会变得不确定起来。设想一下,要把某个数据同时插入两个相关联的表中,可能会出现这样的情况:第一个表中成功更新后,数据库突
然出现意外状况,造成第二个表中的操作没有完成,这样,就会造成数据的不完整,甚至会破坏数据库中的数据。要避免这种情况,就应该使用事务,它的作用
是:要么语句块中每条语句都操作成功,要么都失败。换句话说,就是可以保持数据库中数据的一致性和完整性。事物以BEGIN 关键字开始,COMMIT
关键字结束。在这之间的一条SQL操作失败,那么,ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。

  BEGIN;

  INSERT INTO salesinfo SET CustomerID=14;

  UPDATE inventory SET Quantity=11

  WHERE item=’book’;

  COMMIT;

  事务的另一个重要作用是当多个用户同时使用相同的数据源时,它可以利用锁定数据库的方法来为用户提供一种安全的访问方式,这样可以保证用户的操作不
被其它的用户所干扰。

5、锁定表
  尽管事务是维护数据库完整性的一个非常好的方法,但却因为它的独占性,有时会影响数据库的性能,尤其是在很大的应用系统中。由于在事务执行的过程
中,数据库将会被锁定,因此其它的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户

  来使用,事务造成的影响不会成为一个太大的问题;但假设有成千上万的用户同时访问一个数据库系统,例如访问一个电子商务网站,就会产生比较严重的响
应延迟。

  其实,有些情况下我们可以通过锁定表的方法来获得更好的性能。下面的例子就用锁定表的方法来完成前面一个例子中事务的功能。

  LOCK TABLE inventory WRITE
  SELECT Quantity FROM inventory
  WHEREItem=’book’;
  …

  UPDATE inventory SET Quantity=11
  WHEREItem=’book’;
  UNLOCK TABLES

  这里,我们用一个 SELECT 语句取出初始数据,通过一些计算,用 UPDATE 语句将新值更新到表中。包含有 WRITE 关键字的
LOCK TABLE 语句可以保证在 UNLOCK TABLES 命令被执行之前,不会有其它的访问来对 inventory 进行插入、更新或者
删除的操作。

6、使用外键

  锁定表的方法可以维护数据的完整性,但是它却不能保证数据的关联性。这个时候我们就可以使用外键。例如,外键可以保证每一条销售记录都指向某一个存
在的客户。在这里,外键可以把customerinfo 表中的CustomerID映射到salesinfo表中CustomerID,任何一条没有
合法CustomerID的记录都不会被更新或插入到salesinfo中。
   CREATE TABLE customerinfo
  (
   CustomerID INT NOT NULL ,
   PRIMARY KEY ( CustomerID )
  ) TYPE = INNODB;
  CREATE TABLE salesinfo
  (
   SalesID INT NOT NULL,
   CustomerID INT NOT NULL,
   PRIMARY KEY(CustomerID, SalesID),
   FOREIGN KEY (CustomerID) REFERENCES customerinfo
   (CustomerID) ON DELETECASCADE
  ) TYPE = INNODB;

  注意例子中的参数“ON DELETE CASCADE”。该参数保证当 customerinfo 表中的一条客户记录被删除的时候,
salesinfo 表中所有与该客户相关的记录也会被自动删除。如果要在 MySQL 中使用外键,一定要记住在创建表的时候将表的类型定义为事务安
全表 InnoDB类型。该类型不是 MySQL 表的默认类型。定义的方法是在 CREATE TABLE 语句中加上 TYPE=INNODB。如
例中所示。

7、使用索引

  索引是提高数据库性能的常用方法,它可以令数据库服务器以比没有索引快得多的速度检索特定的行,尤其是在查询语句当中包含有MAX(), MIN
()和ORDERBY这些命令的时候,性能提高更为明显。那该对哪些字段建立索引呢?一般说来,索引应建立在那些将用于JOIN, WHERE判断和
ORDER BY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。对于一个ENUM类型的字段来说,出现大量重复值是很有可能的
情况,例如customerinfo中的“province”.. 字段,在这样的字段上建立索引将不会有什么帮助;相反,还有可能降低数据库的性能。
我们在创建表的时候可以同时创建合适的索引,也可以使用ALTER TABLE或CREATE INDEX在以后创建索引。此外,MySQL

  从版本3.23.23开始支持全文索引和搜索。全文索引在MySQL 中是一个FULLTEXT类型索引,但仅能用于MyISAM 类型的表。对于
一个大的数据库,将数据装载到一个没有FULLTEXT索引的表中,然后再使用ALTER TABLE或CREATE INDEX创建索引,将是非常快
的。但如果将数据装载到一个已经有FULLTEXT索引的表中,执行过程将会非常慢。

8、优化的查询语句

  绝大多数情况下,使用索引可以提高查询的速度,但如果SQL语句使用不恰当的话,索引将无法发挥它应有的作用。下面是应该注意的几个方面。首先,最
好是在相同类型的字段间进行比较的操作。在MySQL 3.23版之前,这甚至是一个必须的条件。例如不能将一个建有索引的INT字段和BIGINT字
段进行比较;但是作为特殊的情况,在CHAR类型的字段和VARCHAR类型字段的字段大小相同的时候,可以将它们进行比较。其次,在建有索引的字段上
尽量不要使用函数进行操作。

  例如,在一个DATE类型的字段上使用YEAE()函数时,将会使索引不能发挥应有的作用。所以,下面的两个查询虽然返回的结果一样,但后者要比前
者快得多。

  SELECT * FROM order WHERE YEAR(OrderDate)<2001>=”MySQL”and name<"MySQM"   最后,应该注意避免在查询中让MySQL进行自动类型转换,因为转换过程也会使索引变得不起作用。 转自

Alexa Traffic Rank PHP5 Class

This PHP5 class utilizes the web service provided by Alexa/Amazon to get the traffic information about the site. You’ll need to sign up for an account at Alexa to get the pair (access key and secret key). The first 10,000 queries per month are free. view plaincopy to clipboardprint?

mParams = array(   
      'Service'=>'AlexaWebInfoService',   
      'AWSAccessKeyId'=>'YOUR ACCESS KEY', // access key id   
      'Operation'=>'UrlInfo',   
      'ResponseGroup'=>'Rank',   
      'Url'=>NULL,   
      'Timestamp'=>gmdate('Y-m-d\TH:i:s.000\Z'),   
      'Signature'=>NULL,   
    );   
    $this->prepareSignature();   
  }   
  
  /** ref: http://developer.amazonwebservices.com/connect/thread.jspa?threadID=9675 */  
  private function calculate_RFC2104HMAC ($data, $key) {   
    return base64_encode(   
      pack("H*", sha1((str_pad($key, 64, chr(0x00))   
      ^(str_repeat(chr(0x5c), 64))) .   
      pack("H*", sha1((str_pad($key, 64, chr(0x00))   
      ^(str_repeat(chr(0x36), 64))) . $data))))   
    );   
  }   
  
  
  /** have the signature ready */  
  private function prepareSignature() {   
    $vData = $this->mParams['Service'].$this->mParams['Operation'].$this->mParams['Timestamp'];   
    $this->mParams['Signature'] = $this->calculate_RFC2104HMAC($vData,$this->mSecretKey);   
  }   
     
  
  /** make the rest call and return result as XML object */  
  function call() {   
    $vURL = 'http://awis.amazonaws.com/onca/xml?';   
    $vTmp = array();   
    foreach ($this->mParams AS $vKey=>$vValue) {   
      $vTmp[] = $vKey.'='.$vValue;   
    } // rof   
    $vURL .= implode('&',$vTmp);   
    $vFile = file_get_contents($vURL);   
    $vXML = simplexml_load_string($vFile);   
    return $vXML;   
  }   
  
  /** get the rank for a domain */  
  function getRank($pDomain) {   
    $this->mParams['Url'] = $pDomain;   
    $vResult = $this->call();   
    $vResult = $vResult->UrlInfoResult->Alexa->TrafficData->Rank+0;   
    return $vResult;   
  }   
}   
?>  
mParams = array(
      'Service'=>'AlexaWebInfoService',
      'AWSAccessKeyId'=>'YOUR ACCESS KEY', // access key id
      'Operation'=>'UrlInfo',
      'ResponseGroup'=>'Rank',
      'Url'=>NULL,
      'Timestamp'=>gmdate('Y-m-d\TH:i:s.000\Z'),
      'Signature'=>NULL,
    );
    $this->prepareSignature();
  }

  /** ref: http://developer.amazonwebservices.com/connect/thread.jspa?threadID=9675 */
  private function calculate_RFC2104HMAC ($data, $key) {
    return base64_encode(
      pack("H*", sha1((str_pad($key, 64, chr(0x00))
      ^(str_repeat(chr(0x5c), 64))) .
      pack("H*", sha1((str_pad($key, 64, chr(0x00))
      ^(str_repeat(chr(0x36), 64))) . $data))))
    );
  }


  /** have the signature ready */
  private function prepareSignature() {
    $vData = $this->mParams['Service'].$this->mParams['Operation'].$this->mParams['Timestamp'];
    $this->mParams['Signature'] = $this->calculate_RFC2104HMAC($vData,$this->mSecretKey);
  }
  

  /** make the rest call and return result as XML object */
  function call() {
    $vURL = 'http://awis.amazonaws.com/onca/xml?';
    $vTmp = array();
    foreach ($this->mParams AS $vKey=>$vValue) {
      $vTmp[] = $vKey.'='.$vValue;
    } // rof
    $vURL .= implode('&',$vTmp);
    $vFile = file_get_contents($vURL);
    $vXML = simplexml_load_string($vFile);
    return $vXML;
  }

  /** get the rank for a domain */
  function getRank($pDomain) {
    $this->mParams['Url'] = $pDomain;
    $vResult = $this->call();
    $vResult = $vResult->UrlInfoResult->Alexa->TrafficData->Rank+0;
    return $vResult;
  }
}
?>

Sample Usage: view plaincopy to clipboardprint?

$vAlexa = new CAlexaAPI();   
$vResult = $vAlexa->getRank('www.adspeed.com');   
var_dump($vResult); // return int 23974  
$vAlexa = new CAlexaAPI();
$vResult = $vAlexa->getRank('www.adspeed.com');
var_dump($vResult); // return int 23974

原文http://www.adspeed.org/2006/03/alexa-traffic-rank-php5-class.html

[原创] MySQL Proxy 学习笔记

作者:heiyeluren
时间:2008-1-28
博客:
http://blog.csdn.net/heiyeshuwu

 

【 测试平台 】

服务器端:
OS:FreeBSD 6.2
Lua: 5.1.1
MySQL Server:4.1.22-log
MySQL Proxy: 0.6.0
客户端:
OS:Windows XP Pro SP2
PHP: PHP 5.2.4 (cli)
MySQL Client: Ver 14.13 Distrib 5.1.19-beta for Win32

注意:本文使用的是最新版本 MySQL Proxy alpha 0.6.0 ,如果是0.5.0版本可能部分选项和操作不一致,同样的,以后如果除了新版本,请参照新版本文档。
【 MySQL Proxy 安装 】

$ fetch http://mirror.mysql-partners-jp.biz/Downloads/MySQL-Proxy/mysql-proxy-0.6.0-freebsd6-x86.tar.gz
$ tar zxvf mysql-proxy-
0.6.0-freebsd6-x86.tar.gz
$ cd mysql-proxy-
0.6.0-freebsd6-x86
$ cp sbin/mysql-proxy /usr/local/sbin/

如果无法正常使用,请检查相应的其他组件是否安装了,mysql-proxy 需要依赖 lua、libtool、glib、libevent 等库。
【 初步使用 】

执行 mysql-proxy –help-all 查看所有的设置选项,比较重要的选项大致描述一下功能:

管理功能选项

–admin-address=host:port — 指定一个mysqo-proxy的管理端口,缺省是 4041

代理功能选项

–proxy-address=host:port — 这个是mysql-proxy 服务器端的监听端口,缺省是 4040
–proxy-read-only-backend-addresses
=<host:port> — 远程只读Slave服务器的地址和端口,缺省为不设置(本选项在mysql-proxy0.5.0版本中没有)
–proxy-backend-addresses
=host:port — 指定远程MySQL服务器地址和端口,可以设置多个,缺省是 127.0.0.1:3306
–proxy-skip-profiling — 关闭查询分析功能,缺省是打开的
–proxy-fix-bug-
25371 — 修正 mysql的libmysql版本大于5.1.12的一个#25371号bug
–proxy-lua-script
=file — 指定一个Lua脚本程序来控制mysql-proxy的运行和设置,这个脚本在每次新建连接和脚本发生修改的的时候将重新调用

其他选项

–daemon — mysql-proxy以守护进程方式运行
–pid-file
=file — 设置mysql-proxy的存储PID文件的路径

我们执行试试,监听本地MySQL是3000端口:
/usr/local/sbin/mysql-proxy –proxy-backend-addresses=127.0.0.1:3000

然后从我的windows机器使用mysql客户端连接过去:
mysql -uroot -h 192.168.0.2 -P 4040

 

Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 3
Server version: 
4.1.22-log
Type ‘help
;‘ or ‘h’ for help. Type ‘c’ to clear the buffer.
mysql>

远程的mysql-proxy缺省对外监听的是4040端口,它会对连接4040端口的连接做处理后丢给后端3000端口的mysql服务器处理,把结果返回。
我们执行一条sql试试:

mysql> select version();
+————+
| version()  |
+————+
4.1.22-log |
+————+
1 row in set (0.01 sec)

 

【 对两台/多台MySQL的负载均衡 】

首先我们目前MySQL Proxy 是支持多台后端MySQL数据库的,它可以支持同时设置多个 –proxy-backend-addresses 选项来使用多个MySQL,比如:
/usr/local/sbin/mysql-proxy –proxy-address=192.168.0.2:3306 –proxy-backend-addresses=127.0.0.1:3000 –proxy-backend-addresses=192.168.0.2:4000
就分别连接了两台MySQL,一台是3000端口,一台是4000端口。

假设有两台Slave的读数据服务器,我们使用mysql_proxy多个数据库的轮询技术来进行两台Slave数据库的负载均衡分散访问措施,mysql-proxy可以做到,缺省就具有了简单的均衡功能,它自带的均衡功能是当有多台MySQL服务器的时候,进行逐个访问的原则,比如有A、B两台MySQL服务器,那么第一个连接就访问A,第二个连接访问B,第三个连接访问A,一次分布,达到A、B两台MySQL访问均衡。

对于mysql-proxy这个功能,我们进行检测一下。增加一个Lua检测脚本 test-conn.lua,就一个函数:

function read_query( packet )
    
print(read_query: connection.backend_ndx: , proxy.connection.backend_ndx)
end

执行:
/usr/local/sbin/mysql-proxy –proxy-address=192.168.0.2:3306 –proxy-backend-addresses=127.0.0.1:3000 –proxy-backend-addresses=192.168.0.2:4000 –proxy-lua-script=./test-conn.lua

在windows上调用mysql客户端连接过去,打开多个cmd窗口进行连接:mysql -u root -h 192.168.0.2 -P 3306

看到mysql-proxy服务器端输出结果:

read_query: connection.backend_ndx:     1
read_query: connection.backend_ndx:     
2
read_query: connection.backend_ndx:     
1
read_query: connection.backend_ndx:     
2
read_query: connection.backend_ndx:     
1
read_query: connection.backend_ndx:     
2

很明显是按照逐个来轮询,这样能够达到平均分配连接的目的。

但是一个问题就是如果一台机器硬件设备比另外一台设备差,那么对数据库的连接就不能使用平均的方式了,那么完全使用Lua脚本构建自己的轮询连接算法,mysql-proxy的强大之处就在于能够支持Lua脚本进行各种控制,包括 连接服务器(connect_server)、连接握手(read_handshake)、提交验证(read_auth)、验证结果(read_auth_result)、提交查询(read_query)、查询结果(read_query_result) 等控制都是通过简单的在 –proxy-lua-script 选项中指定脚本来进行控制。

现在我们来简单改进一下连接服务器连接分配的算法,就在Lua脚本中增加一个 connect_server 函数就行。
比如按照当前时间进行分布连接到两台MySQL上:

function connect_server()
    
if (tonumber(os.date(%M)) % 2 == 0then
        proxy.connection.backend_ndx 
= 2
    
else
        proxy.connection.backend_ndx 
= 1
    
end
end

或者我们采取随机方式,在两台MySQL之间随机挑选一台:

function connect_server()
    local server_index 
= math.random(12)
    proxy.connection.backend_ndx
end

我们客户端继续连接多次,监测连接的服务器,发现连接的机器是随机展开的:

read_query: connection.backend_ndx:     1
read_query: connection.backend_ndx:     
2
read_query: connection.backend_ndx:     
2
read_query: connection.backend_ndx:     
1
read_query: connection.backend_ndx:     
1
read_query: connection.backend_ndx:     
2
read_query: connection.backend_ndx:     
2
read_query: connection.backend_ndx:     
1
read_query: connection.backend_ndx:     
1
read_query: connection.backend_ndx:     
1
read_query: connection.backend_ndx:     
2

【 压力测试 】

1. 连接数据库压力测试

测试代码使用PHP5.2,测试文件 php_mysql_proxy.php 代码:

<?php
//连接函数
function mysql_proxy_connect($is_close=true){
    
$link = mysql_connect(192.168.0.2:3306, root, “”, true);
    
if (!$link){
        
die(Could not connect:  . mysql_error());
    }
    
if ($is_close){
        
echo  close connection … ;
        
mysql_close($link);
    }
    
return true;
}

//测试函数
function test($sum, $is_close=true){
    
for($i=0$i<$sum$i++){
        
echo Connect mysql-proxy . $i ... ;
        
if (mysql_proxy_connect($is_close)){
            
echo Succeed ;
        } 
else {
            
echo Failed ;
        }
        
    }
}
?>

//测试1 (连续连接1w次,每次连接后不关闭连接)
test(10000);

Server: mysql-proxy 的CPU占用是从0.1%一直升到 12.4% 左右,然后结束后慢慢减下来,mysqld 的cpu占用率很低,%0.2左右
Client: 客户端在连接后马上关闭连接的状态下,一直持续到3948个连接的时候无法再连接到mysql-proxy
客户端输出:


Connect mysql-proxy 
3945.. close connection … Succeed
Connect mysql-proxy 
3946.. close connection … Succeed
Connect mysql-proxy 
3947.. close connection … Succeed
Connect mysql-proxy 
3948..
Warning: mysql_connect(): Can’t connect to MySQL server on ‘
192.168.0.2‘ (10048)
 in C:php_mysql_proxy.php on line 
4
Could not connect: Can’t connect to MySQL server on ‘
192.168.0.2‘ (10048)

//测试2(连续连接1w次,每次连接后不关闭连接)
test(10000, false);

Server: mysql-proxy 的CPU占用是从0.1%一直升到 14.5% 左右,然后结束后慢慢减下来, mysqld 的cpu占用率也很低,%0.2左右
Client: 客户端在连接后马上不关闭连接的状态下,一直持续到3946个连接的时候无法再连接到mysql-proxy,基本跟关闭连接的最终处理数量差不多
输出信息:

Connect mysql-proxy 3944.. Succeed
Connect mysql-proxy 
3945.. Succeed
Connect mysql-proxy 
3946..
Warning: mysql_connect(): Can’t connect to MySQL server on ‘
192.168.0.2‘ (10048)
 in C:php_mysql_proxy.php on line 
4
Could not connect:

2. 执行查询测试

测试脚本,php_mysql_proxy.php,代码如下:

<?php
//时间函数
function microtime_float(){
    
list($usec, $sec= explode( , microtime());
    
return ((float)$usec + (float)$sec);
}

//测试函数1(简单查询)
function test1(){
    
$link = mysql_connect(192.168.0.2:3306, root, “”, true);
    
if (!$link){
        
die(Could not connect:  . mysql_error());
    }
    
for($i=0$i<10000$i++){
        
$res = mysql_query(select now(),$link);
        
echo Query .$i. … ;
        
if (!$res){
            
echo(Could not query:  . mysql_error());
            
return false;
        }
        
$row = mysql_fetch_row($res);
        
echo  Succeed ;
    }
}

//测试函数2(复杂查询)
function test2(){
    
$link = mysql_connect(192.168.0.2:3306, root, “”, true);
    
if (!$link){
        
die(Could not connect:  . mysql_error());
    }
    
mysql_select_db(replaction, $link);
    
for($i=0$i<10000$i++){
        
$res = mysql_query(select * from t1 where id in (select id from t1),$link);
        
echo Query .$i. … ;
        
if (!$res){
            
echo(Could not query:  . mysql_error());
            
return false;
        }
        
while($row = mysql_fetch_row($res)){}
        
echo  Succeed ;
    }    
}
?>

//执行测试1
$start_time = microtime_float();
test1();
$end_time = microtime_float();
echo “\nExecute Time: “. ($end_time – $start_time) .”\n”;

测试结果:PHP执行时间和服务器端cpu占用率
Execute Time: 39.4633390903
mysql-proxy: 17.56%
mysqld-1: 5.26%
mysqld-2: 2.12%

//执行测试2
$start_time = microtime_float();
test2();
$end_time = microtime_float();
echo “\nExecute Time: “. ($end_time – $start_time) .”\n”;

测试结果:PHP执行时间和服务器端cpu占用率
Execute Time: 62.6189789772
mysql-proxy: 19.09%
mysqld-1: 6.25%
mysqld-2: 3.08%

可以基本看出两者差别不是非常大,也可能是因为数据太少的缘故。
【 管理MySQL Proxy 】

在启动 mysql-proxy 之后,就会打开两个端口,一个是4040,这个是缺省用于提供给MySQL客户端连接使用的端口,可以通过 –proxy-address 选项修改为其他的,比如设置为 –proxy-address=192.168.0.2:3306,就完全模拟成了一台MySQL数据库了。另外默认开放 4041 的端口,这个是用来提供给管理 mysql-proxy 使用的,可以使用标准的mysql客户端来连接,同样能够通过 –admin-address 选项来设置为其他管理端口。

目前版本的管理功能还比较弱,只能够查看两个貌似是数据表的关于 mysql-proxy 的一些信息:
 mysql -u root -h 192.168.0.2 -P 4041

Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1
Server version: 
5.1.20-agent MySQL Enterprise Agent

 

说明这是一个版本为5.1.20的MySQL的代理程序,查看目前mysql-proxy的运行信息:


mysql> select * from proxy_connections;
+——+——–+——-+————+
| id   | type   | state | db         |
+——+——–+——-+————+
|    0 | server | 0     |            |
|    1 | proxy  | 0     |            |
|    2 | proxy  | 10    | replaction |
|    3 | server | 10    |            |
+——+——–+——-+————+
4 rows in set (0.02 sec)

mysql> select * from proxy_config;
+—————————-+——————+
| option                     | value            |
+—————————-+——————+
| admin.address              | :4041            |
| proxy.address              | 192.168.0.2:3306 |
| proxy.lua_script           | NULL             |
| proxy.backend_addresses[0] | 127.0.0.1:3000   |
| proxy.fix_bug_25371        | 0                |
| proxy.profiling            | 1                |
+—————————-+——————+
6 rows in set (0.02 sec)

 除了这两个状态信息,我貌似没有发现别的,不过基本的运行信息都包含了。
【 总结 】

先说说优点,使用简单,安装方便,各种平台的二进制版都有(这个是MySQL的传统优点),同时性能不错。
再说说缺点,缺点同样很明显,说两个典型的缺点:

1. 无法 Master/Slave 的区分
我一开始兴冲冲的以为 –proxy-read-only-backend-addresses 选项来构建一个支持Master/Slave的环境进行测试,但是我反复测试,发现无法实现,每次都是连接到Master,或者是做到每次都是按照连接算法来进行,mysql-proxy无法区分本次操作是写入操作还是读取操作而且划分到不同的MySQL机器。我后来又尝试也力求通过Lua脚本的方式来解决问题,自己定义连接情况,不过目前来看,定义的基本Lua接口,connect_server 是连接服务器,read_query 是整理SQL语句,我发现不论如何都是先执行了 connect_server 才去执行 read_query 的, 就是是先连接MySQL,然后执行的查询,这样就无法实现先拿到SQL语句,然后进行分析,发现是 insert/update/delete 全部拿到Master去执行,剩余的拿到Slave去执行,但是目前貌似无法实现,可能是我文档读的不够,或者是操作方法不对,非常希望在日后新版本中有比较方便简单的方式实现,因为一般使用 mysql-proxy 的人来说,都是因为负载高,数据连接太多或者是需要管理多台Slave机器,目前让 mysql-proxy 管理多台服务器是没有问题的,负载均衡也可以通过自行写Lua脚本控制。

2. mysql-proxy admin的管理功能太弱
目前发现除了能够查看两项信息之外,mysql-proxy admin 功能没有别的,比如重启服务呀,或者是查看目前执行连接和执行的SQL语句等等都没有,稍微弱了一点,希望在后期版本中加强。

大致功能都比较清楚了,测试速度也比较快,基本上目前来说,用于一些应用问题是不大的,稳定性也还好,毕竟目前是alpha版本,我们完全有热情去期待在正式版中会有很多新功能和对目前功能的改进,我无比的期待着。


【 相关URL 】

[ MySQL Proxy 相关URL ]
下载地址:http://dev.mysql.com/downloads/mysql-proxy/index.html
官方手册:http://dev.mysql.com/doc/refman/5.1/en/mysql-proxy.html
官方wiki:http://forge.mysql.com/wiki/MySQL_Proxy

[ Lua 相关URL ]
Lua官方网站:http://www.lua.org/
Lua英文手册:http://www.lua.org/manual/5.1/
Lua中文手册:http://www.codingnow.com/2000/download/lua_manual.html
(转载请著名来源:http://blog.csdn.net/heiyeshuwu,本文如有纰漏或者不正确的地方,欢迎指正,谢谢!)
【补充】

added 2008-01-29: 今天在InfoQ上面看到一篇文章《用MySQL-Proxy实现读写分离》文章使用了一个Lua脚本实现了读写分离的实现,大家可以参考。

[转]MySQL与事务

2006年08月05日 星期六 下午 02:23
作者:老王
MySQL5.X都已经发布好久了,但是还有很多人认为MySQL是不支持事务处理的,这不得不怪他们是孤陋寡闻的,其实,只要你的MySQL版本支持BDB或InnoDB表类型,那么你的MySQL就具有事务处理的能力。这里面,又以InnoDB表类型用的最多,虽然后来发生了诸如Oracle收购InnoDB等令MySQL不爽的事情,但那些商业上的斗争与技术无关,下面以InnoDB表类型为例简单说一下MySQL中的事务。
先来明确一下事务涉及的相关知识:
事务都应该具备ACID特征。所谓ACID是Atomic(原子性),Consistent(一致性),Isolated(隔离性),Durable(持续性)四个词的首字母所写,下面以“银行转帐”为例来分别说明一下它们的含义:
原子性:组成事务处理的语句形成了一个逻辑单元,不能只执行其中的一部分。换句话说,事务是不可分割的最小单元。比如:银行转帐过程中,必须同时从一个帐户减去转帐金额,并加到另一个帐户中,只改变一个帐户是不合理的。
一致性:在事务处理执行前后,数据库是一致的。也就是说,事务应该正确的转换系统状态。比如:银行转帐过程中,要么转帐金额从一个帐户转入另一个帐户,要么两个帐户都不变,没有其他的情况。
隔离性:一个事务处理对另一个事务处理没有影响。就是说任何事务都不可能看到一个处在不完整状态下的事务。比如说,银行转帐过程中,在转帐事务没有提交之前,另一个转帐事务只能处于等待状态。
持续性:事务处理的效果能够被永久保存下来。反过来说,事务应当能够承受所有的失败,包括服务器、进程、通信以及媒体失败等等。比如:银行转帐过程中,转帐后帐户的状态要能被保存下来。
再来看看哪些问题会用到事务处理:

这里不说“银行转帐”的例子了,说一个大家实际更容易遇到的“网上购书”的例子。先假设一下问题的背景:网上购书,某书(数据库编号为123)只剩最后一本,而这个时候,两个用户对这本书几乎同时发出了购买请求,让我们看看整个过程:
在具体分析之前,先来看看数据表的定义:
——————————————————————————-

create table book
(
    book_id unsigned int(10) not null auto_increment,
    book_name varchar(100) not null,
    book_price float(5, 2) not null, #我假设每本书的价格不会超过999.99元
    book_number int(10) not null,
    primary key (book_id)
)
type = innodb; #engine = innodb也行

——————————————————————————-
对于用户甲来说,他的动作稍微比乙快一点点,其购买过程所触发的动作大致是这样的:
——————————————————————————-

1. SELECT book_number FROM book WHERE  book_id = 123;
book_number大于零,确认购买行为并更新book_number
2. UPDATE book SET book_number = book_number - 1 WHERE  book_id = 123;

购书成功
——————————————————————————-
而对于用户乙来说,他的动作稍微比甲慢一点点,其购买过程所触发的动作和甲相同:
——————————————————————————-

1. SELECT book_number FROM book WHERE  book_id = 123;
这个时候,甲刚刚进行完第一步的操作,还没来得及做第二步操作,所以book_number一定大于零
2. UPDATE book SET book_number = book_number - 1 WHERE  book_id = 123;

购书成功
——————————————————————————-
表面上看甲乙的操作都成功了,他们都买到了书,但是库存只有一本,他们怎么可能都成功呢?再看看数据表里book_number的内容,已经变成“-1”了,这当然是不能允许的(实际上,声明这样的列类型应该加上unsigned的属性,以保证其不能为负,这里是为了说明问题所以没有这样设置)
好了,问题陈述清楚了,再来看看怎么利用事务来解决这个问题,打开MySQL手册,可以看到想用事务来保护你的SQL正确执行其实很简单,基本就是三个语句:开始,提交,回滚。
——————————————————————————-

开始:START TRANSACTION或BEGIN语句可以开始一项新的事务
提交:COMMIT可以提交当前事务,是变更成为永久变更
回滚:ROLLBACK可以回滚当前事务,取消其变更
此外,SET AUTOCOMMIT = {0 | 1}可以禁用或启用默认的autocommit模式,用于当前连接。

——————————————————————————-
那是不是只要用事务语句包一下我们的SQL语句就能保证正确了呢?比如下面代码:
——————————————————————————-

BEGIN;
SELECT book_number FROM book WHERE  book_id = 123;
// ...
UPDATE book SET book_number = book_number - 1 WHERE  book_id = 123;
COMMIT;

——————————————————————————-
答案是否定了,这样依然不能避免问题的发生,如果想避免这样的情况,实际应该如下:
——————————————————————————-

BEGIN;
SELECT book_number FROM book WHERE  book_id = 123 FOR UPDATE;
// ...
UPDATE book SET book_number = book_number - 1 WHERE  book_id = 123;
COMMIT;

——————————————————————————-
由于加入了FOR UPDATE,所以会在此条记录上加上一个行锁,如果此事务没有完全结束,那么其他的事务在使用SELECT … FOR UPDATE请求的时候就会处于等待状态,直到上一个事务结束,它才能继续,从而避免了问题的发生,需要注意的是,如果你其他的事务使用的是不带FOR UPDATE的SELECT语句,将得不到这种保护。
最后看看PHP + MySQL事务操作的代码演示:
实际LAMP应用中,一般PHP使用AdoDB操作MySQL,下面给出AdoDB相应的代码方便大家查阅:
——————————————————————————-

// ... 
$adodb->startTrans(); 
//实际,getOne所调用的查询也可以直接放到rowLock来进行,这里只是为了演示效果能更明显些。
$adodb->rowLock('book', 'book_id = 123'); 
$bookNumber = $adodb->getOne("SELECT book_number FROM book WHERE  book_id = 123"); 
$adodb->execute("UPDATE book SET book_number = book_number - 1 WHERE  book_id = 123"); 
$adodb->completeTrans(); 
// ... 
?>

——————————————————————————-
其中,rowLock的方法就是调用的FOR UPDATE来实现的行锁,你可能会想把“FOR UPDATE”直接写到$adodb->getOne()调用的那条SQL语句里面去实现行锁的功能,不错,那样确实可以,但是并不是所有的数据库都使用“FOR UPDATE”语法来实现行锁功能,比如Sybase使用“HOLDLOCK”的语法来实现行锁功能,所以为了你的数据库抽象层保持可移植性,我还是劝你用rowLock来实现行锁功能,至于可移植性就交给AdoDB好了,嗯,有点扯远了,今儿就说到这里了。
——————————————————————————-
附:

AdoDB中存在一个setTransactionMode()方法,能够设置事务的隔离级别,如下:
SetTransactionMode allows you to pass in the transaction mode to use for all subsequent transactions for that connection session. Note: if you have persistent connections and using mysql or mssql, you might have to explicitly reset your transaction mode at the beginning of each page request. This is only supported in postgresql, mssql, mysql with InnoDB and oci8 currently. For example:
$db->SetTransactionMode("SERIALIZABLE");
$db->BeginTrans();
$db->Execute(...); $db->Execute(...);
$db->CommiTrans();
$db->SetTransactionMode(""); // restore to default
$db->StartTrans();
$db->Execute(...); $db->Execute(...);
$db->CompleteTrans();
Supported values to pass in:
    * READ UNCOMMITTED (allows dirty reads, but fastest)
    * READ COMMITTED (default postgres, mssql and oci8)
    * REPEATABLE READ (default mysql)
    * SERIALIZABLE (slowest and most restrictive) 
You can also pass in database specific values such as 'SNAPSHOT' for mssql or 'READ ONLY' for oci8/postgres. 

本文来自ChinaUnix博客,如果查看原文请点:http://blog.chinaunix.net/u1/38200/showart_1864830.html

freebsd vmstat结果内容的解释

top是给Linux设计的。在FreeBSD VM里面的Free概念和其他OS完全不同,使用top查看Free内存对于FreeBSD来说可以说没什么意义。正确的方法是看vmstat。

作者:南非蜘蛛

procs memory page disk faults cpu
r b w swap free re mf mi po fr de sr f0 s0 s1 s2 in sy cs us sy id
0 0 0 14888 19120 0 4 2 11 10 0 0 0 0 0 8 198 2158 98 11 19 69

SWAP的单位应该是K,不是M。还有两个比较重要的参数是PI、PO,表示内存的调入、调出页面,单位也是K,但是多大值作为一个衡量标准,我也不清楚,不知道是否有经验值。

还有,最好使用vmstat t [n]命令,例如 vmstat 5 5,表示在T(5)秒时间内进行N(5)次采样。如果只使用vmstat,无法反映真正的系统情况,试一下,看看结果就知道了。

procs:
r–>在运行队列中等待的进程数
b–>在等待io的进程数
w–>可以进入运行队列但被替换的进程

memoy
swap–>现时可用的交换内存(k表示)
free–>空闲的内存(k表示)

pages
re--》回收的页面
mf--》非严重错误的页面
pi--》进入页面数(k表示)
po--》出页面数(k表示)
fr--》空余的页面数(k表示)
de--》提前读入的页面中的未命中数
sr--》通过时钟算法扫描的页面

disk 显示每秒的磁盘操作。 s表示scsi盘,0表示盘号

fault 显示每秒的中断数
in--》设备中断
sy--》系统中断
cy--》cpu交换

cpu 表示cpu的使用状态
cs--》用户进程使用的时间
sy--》系统进程使用的时间
id--》cpu空闲的时间

解释:
如果 r经常大于 4 ,且id经常少于40,表示cpu的负荷很重。
如果pi,po 长期不等于0,表示内存不足。
如果disk 经常不等于0, 且在 b中的队列 大于3, 表示 io性能不好。

 

 原文地址 http://www.atoi.cn/blog/archives/79-freebsd-vmstatauAEAEa.html

Mysql安装&同步

mysql压缩文件格式的MySQL二进制分发版

一.   安装的软件

1.  Red Hat Enterprise Linux 4.0 update5

2.  mysql-5.0.45-linux-i686-icc-glibc23.tar.gz

二、装步骤如下:

首先检查是否安装了Red Hat Enterprise Linux 4.0中自带的mysql服务,检查系统是否安装了mysql软件包,在终端执行如下命令:

Shell> rpm –aq | grep mysql –ri

显示如下:

mysql-4.1.12-3.RHEL4.1

Mod_auth_mysql-2.6.1-2.2   等软件包

在安装mysql二进制分发版之前请先卸载mysql  rpm软件包

 

Shell> rpm e mysql-4.1.12-3.RHEL4.1 将相关的rpm包逐一掉。

 

shell> groupadd mysql

shell> useradd -g mysql mysql    mysql增加一个登录用户和组

shell> cd /usr/local

shell> tar –zxvf mysql-5.0.45-linux-i686-icc-glibc23.tar.gz

shell> ln -s mysql-5.0.45-linux-i686-icc-glibc23  mysql

shell> cd mysql

shell> scripts/mysql_install_db –user=mysql

 /*如果你用root运行命令,应当使用–user选项。选项的值应与你在第一步为运行服务器所创建的登录账户相同。如果你用该用户登录来运行命令,可以省略–user选项。*/

shell> chown -R root  .

shell> chown -R mysql data

shell> chgrp -R mysql .

 /* 第一个命令将文件的所有属性改为root用户。第二个命令将数据目录的所有属性改为mysql用户。第三个命令将组属性改为mysql  */

shell> cp support-files/my-medium.cnf  /etc/my.cnf

shell> cp support-files/mysql.server    /etc/init.d/mysql

shell> chmod +x    /etc/init.d/mysql

shell> chkconfig –add mysql

shell> chkconfig  – -level 345 mysql on  /* mysql添加为服务*/

shell> chkconfig  – -list mysql          /* mysql level服务状态*/

shell> bin/mysqld_safe –user=mysql &   /* 命令启动MySQL服务器  */

 或者shell> service mysql start  /* 启动mysql */

shell> service mysql stop /* 停止mysql*/

至此,mysql的安装基本完成!

 

三、优化:对以下参数进行调整

show variables like ‘key_buffer_size’;

show variables like ‘max_allowed_packet’;

show variables like ‘table_cache’;

show variables like ‘sort_buffer_size’;

show variables like ‘read_buffer_size’;

show variables like ‘read_rnd_buffer_size’;

show variables like ‘myisam_sort_buffer_size’;

show variables like ‘thread_cache_size’;

show variables like ‘query_cache_size’;

 

set global key_buffer_size=384000000;

set global max_allowed_packet = 1000000;

set global table_cache = 512;

set global sort_buffer_size = 2000000;

set global read_buffer_size = 2000000;

set global read_rnd_buffer_size = 8000000;

set global myisam_sort_buffer_size = 64000000;

set global thread_cache_size = 8;

set global query_cache_size = 32000000;

 

low_priority_updates

show variables like ‘%low_priority_u%’;

SET LOW_PRIORITY_UPDATES=1

Start mysqld with –low-priority-updates

 

添加:/etc/my.cnf/ 

long_query_time = 1

log_slow_queries=mydatabase-slwqry

 

修改:max_connection=1500  默认:max_connection=100

三、Mysql数据库数据同步实现

    两台安装一样的系统,都是linux as4Mysql的版本是5.0.45,都是目前最新的版本。

A服务器: 192.168.1.13主服务器master
B
服务器: 192.168.1.14 副服务器slave
Master
服务器设置:
shell>to_path/ bin/mysql  –uroot  –p
mysql>grant replication slave on *.* to ‘slaver’@‘192.168.1.14’ identified by ‘password’;

/* 在主服务器上为服务器设置一个连接账slaver,连接密码是password该账户必须授予REPLICATION SLAVE权限。如果账户仅用于复制(推荐这样做),则不需要再授予任何其它权限。*/

mysql>flush tables with read lock;    //停止数据库的写状态


导出Master的数据库到slave的机器,可以选择导出需要同步的数据库或者打包需要同步的数据库

Shell>tar zcvf  mydatabase.slave.tar.gz  /usr/local/mysql/data/mydatabase

movivi1.slave.tar.gz传输到slave /usr/local/mysql/data/并且释放
Master机器上修改/etc/my.cnf
[mysqld]区段内加入参数
log-bin=mysql-bin
server-id=1
binlog_do_db = mydatabase   //
要同步的数据库
binlog_ignore_db = mysql,test,information_schema
recommendation                           // 不需要同步的库,5.0 information_schema这个库
sql-bin-update-same  //同步形式 该参数在很多方案中都有用到, 我在测试过程中使用该参数,发现数据库启动时会报错,故没有使用该参数,具体不会影响数据库主从同步。
重启Master服务器的mysql服务或者

Shell> unlock tables   //解除数据库表的写状态

Shell>show master status//查看主数据库的状态

 

B服务器设置
设定/etc/my.cnf
[mysqld]区段加入

server-id = 2 (如果还有slave用户,他们的ID也随之增加,如server-id=3
master-host = 192.168.13      //
同步的master IP
master-user = slaver          //
同步用的用户名
master-password =password   //
同步用的密码

replicate-do-db =mydatabase     //设置同步的数据库,如果多个库,再重新添加一行

log_err =movivi-err.log      //设置错误日志文件名
log-slave-updates           //
同步的形式
master-port=3306           //
同步端口
master-connect-retry = 60    //
同步时间

–slave-skip-errors=1062,1053 //跳过出错的信息

–slave-skip-errors=all


//在从服务器上执行下面的语句,用你的系统的实际值替换选项值:

mysql> CHANGE MASTER TO

 ->     MASTER_HOST=’192.168.1.13′,

     ->     MASTER_USER=’slaver’,

     ->     MASTER_PASSWORD=’password’,

    ->     MASTER_LOG_FILE=’recorded_log_file_name’,//mysql-bin.0002

     ->     MASTER_LOG_POS=recorded_log_position;//show master status;

最后两行参数可以不选。或者show master status;获取主数据库binlog日志在当前时间点的状态。
重启B服务器的MYSQL
在数据库的文件夹里有master.info文件,用来查看同步信息。如果同步参数设置不正确,导致数据库不能同步,在修改了/etc/my.cnf后重启mysqlshell>stop slave Shell>start slave;它不会覆盖master.info的内容,必须先手动删除master.info再重新启动slave服务。

mysql> show processlist;   //看到两个 system user 就是正常的

Mysql> stop slave  //停止slave服务器

Mysql>start slave  //启动slave服务器,开启了slave就可以同步了

Mysql> show slave status;  //看看同步的状态

 

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.13

Master_User: slaver

Master_Port: 3306

Connect_Retry: 60

 Master_Log_File: mysql-bin.000021

Read_Master_Log_Pos: 67406

Relay_Log_File: master-relay-bin.000117

Relay_Log_Pos: 2195

Relay_Master_Log_File: mysql-bin.000021

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: mydatabase

Replicate_Ignore_DB:

Replicate_Do_Table:

eplicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 99654

Exec_Master_Log_Pos: 67406

Relay_Log_Space: 2195

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

 Seconds_Behind_Master: 0

四.测试
mastermydatabase库里建立test表,并且插入数据,然后检查slave有没有同步这些数据,就能够检查出是否设置成功。

 

五、其它

1.  mysql 并发连接问题,最大多少?同时多少用户在线, 平均每个用户多少时间访问一次页面,平均一个页面有多少个SQL查询. 平均一个SQL查询需要花费多少时间(单线程情况下).

当然这些都是平均数据, 肯定有高峰和极端情况的.此外,你的数据库SQL查询和数据库规模关系大不大, 100W条下的执行时间和1000W条下的执行时间也可能不同.基本上这样你就可以大约知道你的数据库服务器可以支持多少用户同时在线.

单线程情况下,1个查询执行10ms,那么100个查询同时执行,每个查询基本上就是等待1s(因为是单线程,也就不用考虑锁定,线程切换等开销)实际上一个Mysql做的好的话可以达到2000多Query的。这里的最大连接数并不是并发数目有不少人经常出现 超过连接数而被拒绝连接的情况,这时候可以适当增加max connetions

如果自己不是很清楚,可以设为1024

2. .查看mysql设置参数

$mysqladmin -u root -p variables;

.开启服务器

$/etc/rc.d/init.d/mysqld start

$mysqld_safe –user=mysql &

$/etc/rc.d/init.d/mysqld stop

$mysqladmin shutdown -u root -p

.优化表

mysql>optimize table tblA,tblB;

[进行优化并检查修复任务]

$mysqlcheck -o dbname tblA tblB tblC -u root -p

.对表进行分析

mysql>analyze table tblA;

$mysqlcheck -a dbname [tblA tblB tblC ] -u root -p

.对表进行检查

mysql>check table tblA,tblB,tblC;

.赋权

mysql>grant all on dbname.* to username@ identified by ‘localhostpassword’

刷新权限表

mysql>flush privileges;

 

原文 http://blog.chinaunix.net/u/19637/showart_464668.html

my.cnf 注释

从 hi!admin 抄来的一份配置.注释得非常好.精

#BEGIN CONFIG INFO
#DESCR: 4GB RAM, 只使用InnoDB, ACID, 少量的连接, 队列负载大
#TYPE: SYSTEM
#END CONFIG INFO

#
# 此mysql配置文件例子针对4G内存
# 主要使用INNODB
#处理复杂队列并且连接数量较少的mysql服务器
#
# 将此文件复制到/etc/my.cnf 作为全局设置,
# mysql-data-dir/my.cnf 作为服务器指定设置
# (@localstatedir@ for this installation) 或者放入
# ~/.my.cnf 作为用户设置.
#
# 在此配置文件中, 你可以使用所有程序支持的长选项.
# 如果想获悉程序支持的所有选项
# 请在程序后加上”–help”参数运行程序.
#
# 关于独立选项更多的细节信息可以在手册内找到
#

#
# 以下选项会被MySQL客户端应用读取.
# 注意只有MySQL附带的客户端应用程序保证可以读取这段内容.
# 如果你想你自己的MySQL应用程序获取这些值
# 需要在MySQL客户端库初始化的时候指定这些选项

#
[client]
#password = [your_password]
port = @MYSQL_TCP_PORT@
socket = @MYSQL_UNIX_ADDR@

# *** 应用定制选项 ***

#
# MySQL 服务端
#
[mysqld]

# 一般配置选项
port = @MYSQL_TCP_PORT@
socket = @MYSQL_UNIX_ADDR@

# back_log 是操作系统在监听队列中所能保持的连接数,
# 队列保存了在MySQL连接管理器线程处理之前的连接.
# 如果你有非常高的连接率并且出现”connection refused” 报错,
# 你就应该增加此处的值.
# 检查你的操作系统文档来获取这个变量的最大值.
# 如果将back_log设定到比你操作系统限制更高的值,将会没有效果
back_log = 50

# 不在TCP/IP端口上进行监听.
# 如果所有的进程都是在同一台服务器连接到本地的mysqld,
# 这样设置将是增强安全的方法
# 所有mysqld的连接都是通过Unix sockets 或者命名管道进行的.
# 注意在windows下如果没有打开命名管道选项而只是用此项
# (通过 “enable-named-pipe” 选项) 将会导致mysql服务没有任何作用!
#skip-networking

# MySQL 服务所允许的同时会话数的上限
# 其中一个连接将被SUPER权限保留作为管理员登录.
# 即便已经达到了连接数的上限.
max_connections = 100

# 每个客户端连接最大的错误允许数量,如果达到了此限制.
# 这个客户端将会被MySQL服务阻止直到执行了”FLUSH HOSTS” 或者服务重启
# 非法的密码以及其他在链接时的错误会增加此值.
# 查看 “Aborted_connects” 状态来获取全局计数器.
max_connect_errors = 10

# 所有线程所打开表的数量.
# 增加此值就增加了mysqld所需要的文件描述符的数量
# 这样你需要确认在[mysqld_safe]中 “open-files-limit” 变量设置打开文件数量允许至少4096
table_cache = 2048

# 允许外部文件级别的锁. 打开文件锁会对性能造成负面影响
# 所以只有在你在同样的文件上运行多个数据库实例时才使用此选项(注意仍会有其他约束!)
# 或者你在文件层面上使用了其他一些软件依赖来锁定MyISAM表
#external-locking

# 服务所能处理的请求包的最大大小以及服务所能处理的最大的请求大小(当与大的BLOB字段一起工作时相当必要)
# 每个连接独立的大小.大小动态增加
max_allowed_packet = 16M

# 在一个事务中binlog为了记录SQL状态所持有的cache大小
# 如果你经常使用大的,多声明的事务,你可以增加此值来获取更大的性能.
# 所有从事务来的状态都将被缓冲在binlog缓冲中然后在提交后一次性写入到binlog中
# 如果事务比此值大, 会使用磁盘上的临时文件来替代.
# 此缓冲在每个连接的事务第一次更新状态时被创建
binlog_cache_size = 1M

# 独立的内存表所允许的最大容量.
# 此选项为了防止意外创建一个超大的内存表导致永尽所有的内存资源.
max_heap_table_size = 64M

# 排序缓冲被用来处理类似ORDER BY以及GROUP BY队列所引起的排序
# 如果排序后的数据无法放入排序缓冲,
# 一个用来替代的基于磁盘的合并分类会被使用
# 查看 “Sort_merge_passes” 状态变量.
# 在排序发生时由每个线程分配
sort_buffer_size = 8M

# 此缓冲被使用来优化全联合(full JOINs 不带索引的联合).
# 类似的联合在极大多数情况下有非常糟糕的性能表现,
# 但是将此值设大能够减轻性能影响.
# 通过 “Select_full_join” 状态变量查看全联合的数量
# 当全联合发生时,在每个线程中分配
join_buffer_size = 8M

# 我们在cache中保留多少线程用于重用
# 当一个客户端断开连接后,如果cache中的线程还少于thread_cache_size,
# 则客户端线程被放入cache中.
# 这可以在你需要大量新连接的时候极大的减少线程创建的开销
# (一般来说如果你有好的线程模型的话,这不会有明显的性能提升.)
thread_cache_size = 8

# 此允许应用程序给予线程系统一个提示在同一时间给予渴望被运行的线程的数量.
# 此值只对于支持 thread_concurrency() 函数的系统有意义( 例如Sun Solaris).
# 你可可以尝试使用 [CPU数量]*(2..4) 来作为thread_concurrency的值
thread_concurrency = 8

# 查询缓冲常被用来缓冲 SELECT 的结果并且在下一次同样查询的时候不再执行直接返回结果.
# 打开查询缓冲可以极大的提高服务器速度, 如果你有大量的相同的查询并且很少修改表.
# 查看 “Qcache_lowmem_prunes” 状态变量来检查是否当前值对于你的负载来说是否足够高.
# 注意: 在你表经常变化的情况下或者如果你的查询原文每次都不同,
# 查询缓冲也许引起性能下降而不是性能提升.
query_cache_size = 64M

# 只有小于此设定值的结果才会被缓冲
# 此设置用来保护查询缓冲,防止一个极大的结果集将其他所有的查询结果都覆盖.
query_cache_limit = 2M

# 被全文检索索引的最小的字长.
# 你也许希望减少它,如果你需要搜索更短字的时候.
# 注意在你修改此值之后,
# 你需要重建你的 FULLTEXT 索引
ft_min_word_len = 4

# 如果你的系统支持 memlock() 函数,你也许希望打开此选项用以让运行中的mysql在在内存高度紧张的时候,数据在内存中保持锁定并且防止可能被swapping out
# 此选项对于性能有益
#memlock

# 当创建新表时作为默认使用的表类型,
# 如果在创建表示没有特别执行表类型,将会使用此值
default_table_type = MYISAM

# 线程使用的堆大小. 此容量的内存在每次连接时被预留.
# MySQL 本身常不会需要超过64K的内存
# 如果你使用你自己的需要大量堆的UDF函数
# 或者你的操作系统对于某些操作需要更多的堆,
# 你也许需要将其设置的更高一点.
thread_stack = 192K

# 设定默认的事务隔离级别.可用的级别如下:
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
transaction_isolation = REPEATABLE-READ

# 内部(内存中)临时表的最大大小
# 如果一个表增长到比此值更大,将会自动转换为基于磁盘的表.
# 此限制是针对单个表的,而不是总和.
tmp_table_size = 64M

# 打开二进制日志功能.
# 在复制(replication)配置中,作为MASTER主服务器必须打开此项
# 如果你需要从你最后的备份中做基于时间点的恢复,你也同样需要二进制日志.
log-bin=mysql-bin

# 如果你在使用链式从服务器结构的复制模式 (A->B->C),
# 你需要在服务器B上打开此项.
# 此选项打开在从线程上重做过的更新的日志,
# 并将其写入从服务器的二进制日志.
#log_slave_updates

# 打开全查询日志. 所有的由服务器接收到的查询 (甚至对于一个错误语法的查询)
# 都会被记录下来. 这对于调试非常有用, 在生产环境中常常关闭此项.
#log

# 将警告打印输出到错误log文件. 如果你对于MySQL有任何问题
# 你应该打开警告log并且仔细审查错误日志,查出可能的原因.
#log_warnings

# 记录慢速查询. 慢速查询是指消耗了比 “long_query_time” 定义的更多时间的查询.
# 如果 log_long_format 被打开,那些没有使用索引的查询也会被记录.
# 如果你经常增加新查询到已有的系统内的话. 一般来说这是一个好主意,
log_slow_queries

# 所有的使用了比这个时间(以秒为单位)更多的查询会被认为是慢速查询.
# 不要在这里使用”1″, 否则会导致所有的查询,甚至非常快的查询页被记录下来(由于MySQL 目前时间的精确度只能达到秒的级别).
long_query_time = 2

# 在慢速日志中记录更多的信息.
# 一般此项最好打开.
# 打开此项会记录使得那些没有使用索引的查询也被作为到慢速查询附加到慢速日志里
log_long_format

# 此目录被MySQL用来保存临时文件.例如,
# 它被用来处理基于磁盘的大型排序,和内部排序一样.
# 以及简单的临时表.
# 如果你不创建非常大的临时文件,将其放置到 swapfs/tmpfs 文件系统上也许比较好
# 另一种选择是你也可以将其放置在独立的磁盘上.
# 你可以使用”;”来放置多个路径
# 他们会按照roud-robin方法被轮询使用.
#tmpdir = /tmp

# *** 复制有关的设置

# 唯一的服务辨识号,数值位于 1 到 2^32-1之间.
# 此值在master和slave上都需要设置.
# 如果 “master-host” 没有被设置,则默认为1, 但是如果忽略此选项,MySQL不会作为master生效.
server-id = 1

# 复制的Slave (去掉master段的注释来使其生效)
#
# 为了配置此主机作为复制的slave服务器,你可以选择两种方法:
#
# 1) 使用 CHANGE MASTER TO 命令 (在我们的手册中有完整描述) –
# 语法如下:
#
# CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
# MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
# 你需要替换掉 <host>, <user>, <password> 等被尖括号包围的字段以及使用master的端口号替换<port> (默认3306).
#
# 例子:
#
# CHANGE MASTER TO MASTER_HOST=’125.564.12.1′, MASTER_PORT=3306,
# MASTER_USER=’joe’, MASTER_PASSWORD=’secret’;
#
# 或者
#
# 2) 设置以下的变量. 不论如何, 在你选择这种方法的情况下, 然后第一次启动复制(甚至不成功的情况下,
# 例如如果你输入错密码在master-password字段并且slave无法连接),
# slave会创建一个 master.info 文件,并且之后任何对于包含在此文件内的参数的变化都会被忽略
# 并且由 master.info 文件内的内容覆盖, 除非你关闭slave服务, 删除 master.info 并且重启slave 服务.
# 由于这个原因,你也许不想碰一下的配置(注释掉的) 并且使用 CHANGE MASTER TO (查看上面) 来代替
#
# 所需要的唯一id号位于 2 和 2^32 – 1之间
# (并且和master不同)
# 如果master-host被设置了.则默认值是2
# 但是如果省略,则不会生效
#server-id = 2
#
# 复制结构中的master – 必须
#master-host = <hostname>
#
# 当连接到master上时slave所用来认证的用户名 – 必须
#master-user = <username>
#
# 当连接到master上时slave所用来认证的密码 – 必须
#master-password = <password>
#
# master监听的端口.
# 可选 – 默认是3306
#master-port = <port>

# 使得slave只读.只有用户拥有SUPER权限和在上面的slave线程能够修改数据.
# 你可以使用此项去保证没有应用程序会意外的修改slave而不是master上的数据
#read_only

#*** MyISAM 相关选项

# 关键词缓冲的大小, 一般用来缓冲MyISAM表的索引块.
# 不要将其设置大于你可用内存的30%,
# 因为一部分内存同样被OS用来缓冲行数据
# 甚至在你并不使用MyISAM 表的情况下, 你也需要仍旧设置起 8-64M 内存由于它同样会被内部临时磁盘表使用.
key_buffer_size = 32M

# 用来做MyISAM表全表扫描的缓冲大小.
# 当全表扫描需要时,在对应线程中分配.
read_buffer_size = 2M

# 当在排序之后,从一个已经排序好的序列中读取行时,行数据将从这个缓冲中读取来防止磁盘寻道.
# 如果你增高此值,可以提高很多ORDER BY的性能.
# 当需要时由每个线程分配
read_rnd_buffer_size = 16M

# MyISAM 使用特殊的类似树的cache来使得突发插入
# (这些插入是,INSERT … SELECT, INSERT … VALUES (…), (…), …, 以及 LOAD DATA
# INFILE) 更快. 此变量限制每个进程中缓冲树的字节数.
# 设置为 0 会关闭此优化.
# 为了最优化不要将此值设置大于 “key_buffer_size”.
# 当突发插入被检测到时此缓冲将被分配.
bulk_insert_buffer_size = 64M

# 此缓冲当MySQL需要在 REPAIR, OPTIMIZE, ALTER 以及 LOAD DATA INFILE 到一个空表中引起重建索引时被分配.
# 这在每个线程中被分配.所以在设置大值时需要小心.
myisam_sort_buffer_size = 128M

# MySQL重建索引时所允许的最大临时文件的大小 (当 REPAIR, ALTER TABLE 或者 LOAD DATA INFILE).
# 如果文件大小比此值更大,索引会通过键值缓冲创建(更慢)
myisam_max_sort_file_size = 10G

# 如果被用来更快的索引创建索引所使用临时文件大于制定的值,那就使用键值缓冲方法.
# 这主要用来强制在大表中长字串键去使用慢速的键值缓冲方法来创建索引.
myisam_max_extra_sort_file_size = 10G

# 如果一个表拥有超过一个索引, MyISAM 可以通过并行排序使用超过一个线程去修复他们.
# 这对于拥有多个CPU以及大量内存情况的用户,是一个很好的选择.
myisam_repair_threads = 1

# 自动检查和修复没有适当关闭的 MyISAM 表.
myisam_recover

# 默认关闭 Federated
skip-federated

# *** BDB 相关选项 ***

# 如果你运行的MySQL服务有BDB支持但是你不准备使用的时候使用此选项. 这会节省内存并且可能加速一些事.
skip-bdb

# *** INNODB 相关选项 ***

# 如果你的MySQL服务包含InnoDB支持但是并不打算使用的话,
# 使用此选项会节省内存以及磁盘空间,并且加速某些部分
#skip-innodb

# 附加的内存池被InnoDB用来保存 metadata 信息
# 如果InnoDB为此目的需要更多的内存,它会开始从OS这里申请内存.
# 由于这个操作在大多数现代操作系统上已经足够快, 你一般不需要修改此值.
# SHOW INNODB STATUS 命令会显示当先使用的数量.
innodb_additional_mem_pool_size = 16M

# InnoDB使用一个缓冲池来保存索引和原始数据, 不像 MyISAM.
# 这里你设置越大,你在存取表里面数据时所需要的磁盘I/O越少.
# 在一个独立使用的数据库服务器上,你可以设置这个变量到服务器物理内存大小的80%
# 不要设置过大,否则,由于物理内存的竞争可能导致操作系统的换页颠簸.
# 注意在32位系统上你每个进程可能被限制在 2-3.5G 用户层面内存限制,
# 所以不要设置的太高.
innodb_buffer_pool_size = 2G

# InnoDB 将数据保存在一个或者多个数据文件中成为表空间.
# 如果你只有单个逻辑驱动保存你的数据,一个单个的自增文件就足够好了.
# 其他情况下.每个设备一个文件一般都是个好的选择.
# 你也可以配置InnoDB来使用裸盘分区 – 请参考手册来获取更多相关内容
innodb_data_file_path = ibdata1:10M:autoextend

# 设置此选项如果你希望InnoDB表空间文件被保存在其他分区.
# 默认保存在MySQL的datadir中.
#innodb_data_home_dir = <directory>

# 用来同步IO操作的IO线程的数量. This value is
# 此值在Unix下被硬编码为4,但是在Windows磁盘I/O可能在一个大数值下表现的更好.
innodb_file_io_threads = 4

# 如果你发现InnoDB表空间损坏, 设置此值为一个非零值可能帮助你导出你的表.
# 从1开始并且增加此值知道你能够成功的导出表.
#innodb_force_recovery=1

# 在InnoDb核心内的允许线程数量.
# 最优值依赖于应用程序,硬件以及操作系统的调度方式.
# 过高的值可能导致线程的互斥颠簸.
innodb_thread_concurrency = 16

# 如果设置为1 ,InnoDB会在每次提交后刷新(fsync)事务日志到磁盘上,
# 这提供了完整的ACID行为.
# 如果你愿意对事务安全折衷, 并且你正在运行一个小的食物, 你可以设置此值到0或者2来减少由事务日志引起的磁盘I/O
# 0代表日志只大约每秒写入日志文件并且日志文件刷新到磁盘.
# 2代表日志写入日志文件在每次提交后,但是日志文件只有大约每秒才会刷新到磁盘上.
innodb_flush_log_at_trx_commit = 1

# 加速InnoDB的关闭. 这会阻止InnoDB在关闭时做全清除以及插入缓冲合并.
# 这可能极大增加关机时间, 但是取而代之的是InnoDB可能在下次启动时做这些操作.
#innodb_fast_shutdown

# 用来缓冲日志数据的缓冲区的大小.
# 当此值快满时, InnoDB将必须刷新数据到磁盘上.
# 由于基本上每秒都会刷新一次,所以没有必要将此值设置的太大(甚至对于长事务而言)

innodb_log_buffer_size = 8M

# 在日志组中每个日志文件的大小.
# 你应该设置日志文件总合大小到你缓冲池大小的25%~100%
# 来避免在日志文件覆写上不必要的缓冲池刷新行为.
# 不论如何, 请注意一个大的日志文件大小会增加恢复进程所需要的时间.
innodb_log_file_size = 256M

# 在日志组中的文件总数.
# 通常来说2~3是比较好的.
innodb_log_files_in_group = 3

# InnoDB的日志文件所在位置. 默认是MySQL的datadir.
# 你可以将其指定到一个独立的硬盘上或者一个RAID1卷上来提高其性能
#innodb_log_group_home_dir

# 在InnoDB缓冲池中最大允许的脏页面的比例.
# 如果达到限额, InnoDB会开始刷新他们防止他们妨碍到干净数据页面.
# 这是一个软限制,不被保证绝对执行.
innodb_max_dirty_pages_pct = 90

# InnoDB用来刷新日志的方法.
# 表空间总是使用双重写入刷新方法
# 默认值是 “fdatasync”, 另一个是 “O_DSYNC”.
#innodb_flush_method=O_DSYNC

# 在被回滚前,一个InnoDB的事务应该等待一个锁被批准多久.
# InnoDB在其拥有的锁表中自动检测事务死锁并且回滚事务.
# 如果你使用 LOCK TABLES 指令, 或者在同样事务中使用除了InnoDB以外的其他事务安全的存储引擎
# 那么一个死锁可能发生而InnoDB无法注意到.
# 这种情况下这个timeout值对于解决这种问题就非常有帮助.
innodb_lock_wait_timeout = 120

[mysqldump]
# 不要在将内存中的整个结果写入磁盘之前缓存. 在导出非常巨大的表时需要此项
quick

max_allowed_packet = 16M

[mysql]
no-auto-rehash

# 仅仅允许使用键值的 UPDATEs 和 DELETEs .
#safe-updates

[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
# 增加每个进程的可打开文件数量.
# 警告: 确认你已经将全系统限制设定的足够高!
# 打开大量表需要将此值设高
open-files-limit = 8192 

 

 

http://hi.baidu.com/liangzhongbo/blog/item/627a5c2e22eeb0584ec226ee.html

InnoDB行锁特点

InnoDB行锁是通过给索引上的索引项加锁来实现的,这一点MySQL与ORACLE不同,后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁!

在实际应用中,要特别注意InnoDB行锁的这一特性,不然的话,可能导致大量的锁冲突,从而影响并发性能。下面我们通过一些实际例子,来加以说明。

(1)在不通过索引条件查询的时候,InnoDB确实使用的是表锁,而不是行锁。

下面的例子中,开始tab_no_index表没有索引:

mysql> create table tab_no_index(id int,name varchar(10)) engine=innodb; 


Query OK, 0 rows affected (0.15 sec) 




mysql> insert into tab_no_index values(1,'1'),(2,'2'),(3,'3'),(4,'4'); 


Query OK, 4 rows affected (0.00 sec) 


Records: 4 Duplicates: 0 Warnings: 0 


                     InnoDB存储引擎的表在不使用索引时使用表锁例子                    

session_1
 session_2
 
mysql> set autocommit=0; 


Query OK, 0 rows affected (0.00 sec) 


mysql> select * from tab_no_index where id = 1 ; 


+------+------+ 


| id   | name | 


+------+------+ 


| 1    | 1    | 


+------+------+ 


1 row in set (0.00 sec) 










 mysql> set autocommit=0; 


Query OK, 0 rows affected (0.00 sec) 


mysql> select * from tab_no_index where id = 2 ; 


+------+------+ 


| id   | name | 


+------+------+ 


| 2    | 2    | 


+------+------+ 


1 row in set (0.00 sec) 










 
mysql> select * from tab_no_index where id = 1 for update; 


+------+------+ 


| id   | name | 


+------+------+ 


| 1    | 1    | 


+------+------+ 


1 row in set (0.00 sec) 








 



 




 mysql> select * from tab_no_index where id = 2 for update; 


等待 



 

上面的例子中,看起来session_1只给一行加了排他锁,但session_2在请求其他行的排他锁时,却出现了锁等待!原因就是在没有索引的情况下,InnoDB只能使用表锁。当我们给其增加一个索引后,InnoDB就只锁定了符合条件的行,如下例所示:

创建tab_with_index表,id字段有普通索引

mysql> create table tab_with_index(id int,name varchar(10)) engine=innodb; 


Query OK, 0 rows affected (0.15 sec)

mysql> alter table tab_with_index add index id(id); 


Query OK, 4 rows affected (0.24 sec) 


Records: 4 Duplicates: 0 Warnings: 0 


                                   InnoDB存储引擎的表在使用索引时使用行锁例子

session_1
 session_2
 
mysql> set autocommit=0; 


Query OK, 0 rows affected (0.00 sec) 


mysql> select * from tab_with_index where id = 1 ; 


+------+------+ 


| id   | name | 


+------+------+ 


| 1    | 1    | 


+------+------+ 


1 row in set (0.00 sec) 










 mysql> set autocommit=0; 


Query OK, 0 rows affected (0.00 sec) 


mysql> select * from tab_with_index where id = 2 ; 


+------+------+ 


| id   | name | 


+------+------+ 


| 2    | 2    | 


+------+------+ 


1 row in set (0.00 sec) 










 
mysql> select * from tab_with_index where id = 1 for update; 


+------+------+ 


| id   | name | 


+------+------+ 


| 1    | 1    | 


+------+------+ 


1 row in set (0.00 sec) 








 



 




 mysql> select * from tab_with_index where id = 2 for update; 


+------+------+ 


| id   | name | 


+------+------+ 


| 2    | 2    | 


+------+------+ 


1 row in set (0.00 sec) 
 

原文请点:http://blog.chinaunix.net/u2/69385/showart_1852286.html

InnoDB和MyISAM的差别

InnoDB和MyISAM是在使用MySQL最常用的两个表类型,各有优缺点,视具体应用而定。基本的差别为:MyISAM类型不支持事务处理等
高级处理,而InnoDB类型支持。MyISAM类型的表强调的是性能,其执行数度比InnoDB类型更快,但是不提供事务支持,而InnoDB提供事务
支持已经外部键等高级数据库功能。
MyIASM是IASM表的新版本,有如下扩展:
二进制层次的可移植性。
NULL列索引。
对变长行比ISAM表有更少的碎片。
支持大文件。
更好的索引压缩。
更好的键吗统计分布。
更好和更快的auto_increment处理。
每个MyISAM表最大索引数是64。
这可以通过重新编译来改变。每个索引最大的列数是16个。

以下是一些细节和具体实现的差别:
1.InnoDB不支持FULLTEXT类型的索引。
2.InnoDB
中不保存表的具体行数,也就是说,执行select count(*) from
table时,InnoDB要扫描一遍整个表来计算有多少行,但是MyISAM只要简单的读出保存好的行数即可。注意的是,当count(*)语句包含
where条件时,两种表的操作是一样的。
3.对于AUTO_INCREMENT类型的字段,InnoDB中必须包含只有该字段的索引,但是在MyISAM表中,可以和其他字段一起建立联合索引。
4.DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的删除。
5.LOAD TABLE FROM MASTER操作对InnoDB是不起作用的,解决方法是首先把InnoDB表改成MyISAM表,导入数据后再改成InnoDB表,但是对于使用的额外的InnoDB特性(例如外键)的表不适用。

另外,InnoDB表的行锁也不是绝对的,如果在执行一个SQL语句时MySQL不能确定要扫描的范围,InnoDB表同样会锁全表,例如update table set num=1 where name like “%aaa%”
任何一种表都不是万能的,只用恰当的针对业务类型来选择合适的表类型,才能最大的发挥MySQL的性能优势。
http://dev.mysql.com/doc/refman/5.1/zh/storage-engines.html

MySQL 語法 詳細執行時間 與 CPU/記憶體使用

查看 MySQL 語法 詳細執行時間 與 CPU/記憶體使用量: MySQL Query Profiler
MySQL 的 SQL 語法調整主要都是使用 EXPLAIN, 但是這個並沒辦法知道詳細的 Ram(Memory)/CPU 等使用量.

於 MySQL 5.0.37 以上開始支援 MySQL Query Profiler, 可以查詢到此 SQL 會執行多少時間, 並看出 CPU/Memory 使用量, 執行過程中 System lock, Table lock 花多少時間等等.

MySQL Query Profile 詳細介紹可見: Using the New MySQL Query Profiler (2007.04.05 發表)

效能分析主要分下述三種(轉載自上篇):

Bottleneck analysis – focuses on answering the questions: What is my database server waiting on; what is a user connection waiting on; what is a piece of SQL code waiting on?
Workload analysis – examines the server and who is logged on to determine the resource usage and activity of each.
Ratio-based analysis – utilizes a number of rule-of-thumb ratios to gauge performance of a database, user connection, or piece of code.
MySQL Query Profile 使用方法
啟動

mysql> set profiling=1; # 此命令於 MySQL 會於 information_schema 的 database 建立一個 PROFILING 的 table 來紀錄. 

SQL profiles show

mysql> show profiles; # 從啟動之後所有語法及使用時間, 含錯誤語法都會紀錄. 

ex: (root@localhost) [test]> show profiles; # 注意 Query_ID, 下面執行時間統計等, 都是依 Query_ID 在紀錄

+———-+————+—————————+
| Query_ID | Duration | Query |
+———-+————+—————————+
| 1 | 0.00090400 | show profile for query 1 |
| 2 | 0.00008700 | select * from users |
| 3 | 0.00183800 | show tables |
| 4 | 0.00027600 | mysql> show profiles |
+———-+————+—————————+
查詢所有花費時間加總

mysql> select sum(duration) from information_schema.profiling where query_id=1; # Query ID = 1

+—————+
| sum(duration) |
+—————+
| 0.000447 |
+—————+
查詢各執行階段花費多少時間

mysql> show profile for query 1; # Query ID = 1

	+--------------------+------------+
	| Status             | Duration   |
	+--------------------+------------+
	| (initialization)   | 0.00006300 |
	| Opening tables     | 0.00001400 |
	| System lock        | 0.00000600 |
	| Table lock         | 0.00001000 |
	| init               | 0.00002200 |
	| optimizing         | 0.00001100 |
	| statistics         | 0.00009300 |
	| preparing          | 0.00001700 |
	| executing          | 0.00000700 |
	| Sending data       | 0.00016800 |
	| end                | 0.00000700 |
	| query end          | 0.00000500 |
	| freeing items      | 0.00001200 |
	| closing tables     | 0.00000800 |
	| logging slow query | 0.00000400 |
	+--------------------+------------+

查詢各執行階段花費的各種資源列表

mysql> show profile cpu for query 1; # Query ID = 1

	+--------------------------------+----------+----------+------------+
	| Status                         | Duration | CPU_user | CPU_system |
	+--------------------------------+----------+----------+------------+
	| (initialization)               | 0.000007 | 0        | 0          |
	| checking query cache for query | 0.000071 | 0        | 0          |
	| Opening tables                 | 0.000024 | 0        | 0          |
	| System lock                    | 0.000014 | 0        | 0          |
	| Table lock                     | 0.000055 | 0.001    | 0          |
	| init                           | 0.000036 | 0        | 0          |
	| optimizing                     | 0.000013 | 0        | 0          |
	| statistics                     | 0.000021 | 0        | 0          |
	| preparing                      | 0.00002  | 0        | 0          |
	| executing                      | 0.00001  | 0        | 0          |
	| Sending data                   | 0.015072 | 0.011998 | 0          |
	| end                            | 0.000021 | 0        | 0          |
	| query end                      | 0.000011 | 0        | 0          |
	| storing result in query cache  | 0.00001  | 0        | 0          |
	| freeing items                  | 0.000018 | 0        | 0          |
	| closing tables                 | 0.000019 | 0        | 0          |
	| logging slow query             | 0.000009 | 0        | 0          |
	+--------------------------------+----------+----------+------------+
mysql> show profile IPC for query 1;

	+--------------------------------+----------+---------------+-------------------+
	| Status                         | Duration | Messages_sent | Messages_received |
	+--------------------------------+----------+---------------+-------------------+
	| (initialization)               | 0.000007 |             0 |                 0 |
	| checking query cache for query | 0.000071 |             0 |                 0 |
	| Opening tables                 | 0.000024 |             0 |                 0 |
	| System lock                    | 0.000014 |             0 |                 0 |
	| Table lock                     | 0.000055 |             0 |                 0 |
	| init                           | 0.000036 |             0 |                 0 |
	| optimizing                     | 0.000013 |             0 |                 0 |
	| statistics                     | 0.000021 |             0 |                 0 |
	| preparing                      | 0.00002  |             0 |                 0 |
	| executing                      | 0.00001  |             0 |                 0 |
	| Sending data                   | 0.015072 |             0 |                 0 |
	| end                            | 0.000021 |             0 |                 0 |
	| query end                      | 0.000011 |             0 |                 0 |
	| storing result in query cache  | 0.00001  |             0 |                 0 |
	| freeing items                  | 0.000018 |             0 |                 0 |
	| closing tables                 | 0.000019 |             0 |                 0 |
	| logging slow query             | 0.000009 |             0 |                 0 |
	+--------------------------------+----------+---------------+-------------------+

其它屬性列表
ALL – displays all information
BLOCK IO – displays counts for block input and output operations
CONTEXT SWITCHES – displays counts for voluntary and involuntary context switches
IPC – displays counts for messages sent and received
MEMORY – is not currently implemented
PAGE FAULTS – displays counts for major and minor page faults
SOURCE – displays the names of functions from the source code, together with the name and line number of the file in which the function occurs
SWAPS – displays swap counts
設定 Profiling 存的 Size

mysql> show variables where variable_name='profiling_history_size'; # 預設是 15筆 

關閉

mysql> set profiling=0; 

原文 http://plog.longwin.com.tw/my_note-unix/2008/10/03/mysql-query-profiler-cpu-ram-time-2008