mysql优化案例

灰太狼 2021-11-10 03:40 343阅读 0赞

## [MySQL优化案例][MySQL] ##

Mysql5.1大表分区效率测试

Mysql5.1大表分区效率测试  
MySQL | add at 2009-03-27 12:29:31 by PConline | view:60, comment:0  
mysql5.1开始支持数据表分区了，原来的分表可以不用了，分表的不足在于多表查询不方便。呵呵，下面来简单测试下表分区的查询效率。  
  
1、用来测试的数据为discuz论坛的数据库，表为cdb\_posts表，数据量为1500多万条  
mysql> select count(\*) from cdb\_posts;  
\+-------------+  
| count(\*) |  
\+-------------+  
| 15276429 |  
\+-------------+  
1 row in set (0.04 sec)  
  
2、为了增强表的扩展性，将cdb\_posts表分为10个分区，新建一个表cdb\_posts1,建表语句为  
  
CREATE TABLE \`cdb\_posts1\` (  
\`pid\` int(10) unsigned NOT NULL auto\_increment,  
\`fid\` smallint(6) unsigned NOT NULL default '0',  
\`tid\` mediumint(8) unsigned NOT NULL default '0',  
\`first\` tinyint(1) NOT NULL default '0',  
\`author\` varchar(15) NOT NULL default '',  
\`authorid\` mediumint(8) unsigned NOT NULL default '0',  
\`subject\` varchar(80) NOT NULL default '',  
\`dateline\` int(10) unsigned NOT NULL default '0',  
\`message\` mediumtext NOT NULL,  
\`useip\` varchar(15) NOT NULL default '',  
\`invisible\` tinyint(1) NOT NULL default '0',  
\`anonymous\` tinyint(1) NOT NULL default '0',  
\`usesig\` tinyint(1) NOT NULL default '0',  
\`htmlon\` tinyint(1) NOT NULL default '0',  
\`bbcodeoff\` tinyint(1) NOT NULL default '0',  
\`smileyoff\` tinyint(1) NOT NULL default '0',  
\`parseurloff\` tinyint(1) NOT NULL default '0',  
\`attachment\` tinyint(1) NOT NULL default '0',  
\`rate\` smallint(6) NOT NULL default '0',  
\`ratetimes\` tinyint(3) unsigned NOT NULL default '0',  
\`status\` tinyint(1) NOT NULL default '0',  
PRIMARY KEY (\`pid\`),  
KEY \`fid\` (\`fid\`),  
KEY \`dateline\` (\`dateline\`),  
KEY \`authorid\` (\`authorid\`),  
KEY \`invisible\` (\`invisible\`),  
KEY \`displayorder\` (\`tid\`,\`invisible\`,\`dateline\`),  
KEY \`first\` (\`tid\`,\`first\`)  
) ENGINE=MyISAM AUTO\_INCREMENT=17715224 DEFAULT CHARSET=gbk PARTITION BY RANGE (pid) (  
PARTITION p0 VALUES LESS THAN (3000000),  
PARTITION p1 VALUES LESS THAN (6000000),  
PARTITION p2 VALUES LESS THAN (9000000),  
PARTITION p3 VALUES LESS THAN (12000000),  
PARTITION p4 VALUES LESS THAN (15000000),  
PARTITION p5 VALUES LESS THAN (18000000),  
PARTITION p6 VALUES LESS THAN (21000000),  
PARTITION p7 VALUES LESS THAN (24000000),  
PARTITION p8 VALUES LESS THAN (27000000),  
PARTITION p9 VALUES LESS THAN (30000000),  
PARTITION p10 VALUES LESS THAN MAXVALUE  
);  
  
3、接下来将cdb\_post表的数据导入到cdb\_posts1  
  
mysql>insert into cdb\_posts1 select \* from cdb\_posts;  
  
由于数据量非常大，所用的时间当然也就很长，在双路双核1.6G 4M L2cache CPU，2G内存的机器上这个过程花费了1小时3秒。  
  
接下来测试分区后的表和未分区的表的查询速度比较。  
  
测试程序如下：  
  
  
代码如下  
<?php  
mysql\_connect("localhost","root","");  
mysql\_select\_db("discuz"); )  
//cdb\_posts  
$start = microtime\_float(); //开始时间  
  
for($i=0;$i<100;$i++) \{ //查询100次  
  
$rand = rand(100,15000000);  
$sql = "select \* from cdb\_posts where pid= ".$rand;  
mysql\_query($sql);  
$i++;  
\}  
$end = microtime\_float(); //结束时间  
  
//cdb\_posts1  
$posts = "select cdb\_posts has total spend ".($end-$start)." secondsn";  
  
$start = microtime\_float(); //开始时间  
  
for($i=0;$i<100;$i++) \{ //查询100次  
$rand = rand(100,15000000);  
$sql = "select \* from cdb\_posts1 where pid = ".$rand;  
mysql\_query($sql);  
$i++;  
\}  
$end = microtime\_float(); //结束时间  
$posts1 = "select cdb\_posts1 has total spend ".($end-$start)." secondsn";  
echo "nnn";  
echo $posts."n";  
echo $posts1."n";  
mysql\_query("flush table cdb\_posts"); //清除表缓存  
mysql\_query("flush table cdb\_posts1"); //清除表缓存  
function microtime\_float()  
\{  
list($usec, $sec) = explode(" ", microtime());  
return ((float)$usec + (float)$sec);  
\}  
?>  
  
  
  
为了尽可能的避免Mysql查询使用缓存，将pid采用rand函数取随机数。  
执行这个php  
select cdb\_posts has total spend 0.48239207267761 seconds  
select cdb\_posts1 has total spend 1.4392290115356 seconds  
接下来将查询次数改到1000  
select cdb\_posts has total spend 6.6411547660828 seconds  
select cdb\_posts1 has total spend 13.684276103973 seconds  
接下来我把查询次数增加到10000，执行结果为  
  
select cdb\_posts has total spend 42.948129892349 seconds  
select cdb\_posts1 has total spend 68.492646932602 seconds  
  
从以上结果可以看出，大表进行分区后，查询效率有所降低，但是随着查询次数的增多，所用时间的差距不断减小。

.Net架构网站遇到大表该怎么办？  
最近做的web2.0网站本身遇到一个大表(千万rows左右)，因为对于performance，web本身可用性的考虑，必须想办法boost perf.  
  
这种情况应该都用partition来搞定了，这也符合分治等算法的思想，想办法降低问题本身的复杂度，然后在一个一个解决。  
  
mysql中一般到100万操作就有点麻烦了，index要好好的做。这里还遇到了一个文本检索问题，MyIASM storage engine里面有个full-text index,但是不知道  
  
它对于中文支持如何，而且不清楚它是怎么分词的，不大清楚后台逻辑，Mysql这种index limitation很多，很难scalable,所以基本上直接考虑用search engine那一套。直接上了lucene+solr+solrsharp.小表like还可以忽悠忽悠，大点就慢的如老牛....  
  
Partition通过了解发现解决方案倒是不少，结合了以前做过一点这方面知识储备。  
  
对hivedb,hscale等都没想过要尝试，发现.net在使用open source很多都不是很舒服。  
  
最开始尝试了mysql partition，一开始听起来方法这种方案很Perfect!是mysql解决horizontal partioning的很好方案，等document看完了，发现  
  
5.1版本的partion limitation太多了，只能适合某些特性的场景，例如按照用户id做split;普通那种非unique key,primary key是很难搞定的，简单方法是给表本身  
  
不添加任何主键，自己来实现主键生成机制。这样仿佛可以了，但是通过explain partitions来做下analysis,发现结果定位具体parition不好，这就很难降低IO本身的高成本了。没有通过具体测试不知道可能是explain partition本身不准确原因还是。。。  
  
mysql partition还有一个很大的弊病，就是很难跨机器，当然如何能够把Mysql存储做成分布式，也还好，但是这个技术代价都上了不少档次，risk过高了，只能算是下下策了，备用好了。这些不爽的地方导致偶们直接抛弃了这种方案，直接用手工切分来搞定这种问题，我想这也是大部分这种需求的常见solution把。  
  
手工切分本身技术还比较简单，就是要考虑表的编码，管理等多个方面，以及如何快速定位到可能的partition,这些在设计方面都应该注意了。  
  
而且对于多partitions的结果，应该使用多线程等并发，同步技术来提高perf.  
  
这里的partition还做到支持对某一个partition做进一步切分，这样切分到每一个partition块尽量表中数据在50万以下，这样加上db index,速度应该能够满足一定的需求的，手工切分本身很容易scale out,可以把表放在不同的机器上等等load balance方法来scale.  
  
回想感觉最有意思还是表编码自身的考虑有点意思，我很大程度的灵感来源于IP地址的划分，因为这个表自身增长速度会很慢，所以采用unsigned int来搞定，43亿来表示2000万还是小意思嘛。我主要是通过前缀+长度来定义表的标识。1，2，3前缀可以让给数据比较密集的表，因为它们可以支持10位，其它就用9位来表示，可能有些不再切分范围内的就让他们从0开始增长把。这里partition list本身维护可以序列化到filesystem中，每次Load class时候deserialize一下，然后就是本身partition如何快速定位就需要用点复杂点的data stuctures了。

\[MySQL优化案例\]系列 -- 在5.1的分区功能中混用InnoDB和MyISAM  
周二, 2009/03/17 - 16:42 — yejr  
MySQL 5.1中增加了分区(partition)功能，有了这个功能，以前很头疼的分表方案，现在就变得不再那么麻烦了。不过，如果采用了MyISAM引擎，而且在数据量较大的情境下，并发读写仍然是个问题，尤其是对索引的更新。为此，可以在分区表中采用MyISAM和InnoDB引擎混用的方法，大致如下：  
  
mysql>  
mysql> CREATE TABLE test\_part(  
\-> date DATE NOT NULL DEFAULT '0000-00-00',  
\-> comment VARCHAR(20) DEFAULT NULL  
\-> )ENGINE=MyISAM  
\-> PARTITION BY RANGE (to\_days(date))  
\-> (  
\-> PARTITION nov08 VALUES LESS THAN(TO\_DAYS('2008-12-01')),  
\-> PARTITION dec08 VALUES LESS THAN(TO\_DAYS('2009-01-01')),  
\-> PARTITION jan09 VALUES LESS THAN(TO\_DAYS('2009-02-01')),  
\-> PARTITION feb09 VALUES LESS THAN(TO\_DAYS('2009-03-01')),  
\-> PARTITION mar09 VALUES LESS THAN(TO\_DAYS('2009-04-01')) ENGINE=InnoDB,  
\-> PARTITION unpart VALUES LESS THAN MAXVALUE  
\-> );  
这样的话，就可以利用InnoDB的行锁以及buffer pool实现了对索引以及行记录的并发读写，大大提高效率。不幸的是，目前5.1还不支持这样的混合引擎特性，所以，上面的想法暂时只是美好的愿望了，哈哈。  
  
上面的创意来自：Venu Anuganti，原文出自：[http://venublog.com/2009/03/16/m ... lers-in-partitions/][http_venublog.com_2009_03_16_m ... lers-in-partitions]

【求助】MySQL的InnoDB引擎的优化问题  
  
  
\--------------------------------------------------------------------------------  
  
magiclx  
单表记录数量级别是1000w级，将来可能是在5000w级。现在有四个问题：  
  
1，对关联表建立外键后，如何优化插入或更新的速度？如何配置MySQL？  
2，除了添加索引、优化查询语句，还有什么办法能提高MySQL查询的性能？  
3，分表分库的技术资料哪里可找到？请熟悉的人推荐书籍或讲讲经验。  
4，MySQL有没有缓存技术？除了在架构上，有什么办法让MySQL支持大数据量表的关联查询？（压力到达1000万记录的表自关联10次）  
  
\--------------------------------------------------------------------------------  
  
绿茵汗将  
我随便说说几点  
一、像楼主这么大的数据量的话，通过优化sql语句提升performance的空间我觉得比较小。数据量大了要分表或分区。  
1）如果表里大部分数据是以前的数据，现在不用了的，像论坛，2年前的帖子就很少打开了，这种可以分区；  
2）经常的数据还可以用分表的方式，把单表分成多个表。像用户信息表，就可以按用户名的md5值的第一个字母切分，那就可以把一个User表切出User\_0 、User\_1、User\_2 …… User\_a……User\_f共16个表来，如果是按md5的前两位字母切分表的话，那将可以分出256个表出来，这样一来每个表的数据就少的多了。怎么查询呢？举个用户登录的例子来说：用户通过表单提交用户名为“a"，程序计算出a的md5为“0cc175b9c0f1b6a831c399e269772661”，取第一位：0，那么显然，这个用户的资料是存在了 User\_0 这张表，然后 SELECT \* FROM User\_0 where user\_name = 'a' 。如果不存在？那当然就说明这个用户根本没注册嘛。分库的原理是一样的。  
  
二、上面讲的是存储，还有架构上。如果有多台数据库服务器的话，把数据库的读写分开。楼主可以找找资料看 mysql replication。  
  
三、mysql也有query cache，我印象中不是经常用的，一般单台服务器跑PHP的话会用APC。多台服务器之间共享缓存可以用memchached，这东西其它语言也有API。像很多不需要实时更新的数据都可以塞到APC/memcached里边  
  
\--------------------------------------------------------------------------------  
  
YinH  
分区方面，我记得mysql 5.1开始支持分区表了吧，不过似乎引擎也不是innodb了，毕竟innodb被oracle收购了嘛。  
  
感觉一般处理大数据量的方案还是分成多个表，然后就是不断的生成中间表  
  
\--------------------------------------------------------------------------------  
  
hshh  
1. innodb使用每个表使用独立的表空间: innodb\_file\_per\_table  
2. innodb\_buffer\_pool\_size为物理内存25%~50%  
3. innodb\_additional\_mem\_pool\_size >= 8M  
4. innodb\_log\_file\_size 建议>=50M  
  
5. 如果系统很稳定, innodb\_flush\_log\_at\_trx\_commit=0  
这说明innodb需要大量的disk io, 因此你的硬盘要足够快, 包括了传输快, 和寻道快  
  
6. key\_buffer与innodb\_buffer\_pool\_size一样大小  
  
7. 如果cpu性能很不错, 建议关闭query\_cache, 或者query\_cache\_type=2, 在大量同一查询而且更新很少量才强制使用query cache. cpu性能越好query cache越影响mysql的性能, 但是不建议使用8M以下的query cache  
关于query cache的性能影响:  
我在测试用E5310\*2 (Quad Core 1.6G), 那么就是8CPU情况下面,关闭query cache可以比打开时候提高30%+的纯的随机查询性能, 40%+的随机select+update性能  
  
8. 还有不少关于order, join等,建议参看mysql手册  
9. 目前最新版本mysql 5.1的innodb还不稳定, 我在测试100并发select+update操作的时候会死锁, 不知道mysqld在干什么.  
  
\--------------------------------------------------------------------------------  
  
magiclx  
谢谢绿茵汗将，YinH和老大的经验分享，MySQL的潜力还是有的，需要优化出来。  
  
现在正在做多线程的性能测试，SuSE的服务器，现在有50多个MySQL连接查询和更新，基本服务器接近挂了，可能是MySQL还要配置。  
  
可能后一步会用到依据大表的某列来分表的方案，在开发的复杂度上也不会增加太多，数量级直接可以下降一个数量级。  
  
据说douban的后台只用了一台AMD服务器和MySQL就支撑下来了，好牛。  
  
\--------------------------------------------------------------------------------  
  
hshh  
另外说句: innodb表越大性能下降越大, select count(\*) 这样的查询是要做全表检索的.  
当表很大的时候,query cache又有一点效果了.  
  
\--------------------------------------------------------------------------------  
  
magiclx  
另外说句: innodb表越大性能下降越大, select count(\*) 这样的查询是要做全表检索的.  
当表很大的时候,query cache又有一点效果了.  
  
是的，MyISAM的select count(\*)只需读一行记录。  
一般select count(\*)的时候可能是导出时会用到，一般都会有Where语句的，只要Where的是一列有索引的，还是不慢的

以且应该优化什么？  
  
硬件  
  
操作系统/软件库  
  
SQL服务器(设置和查询)  
  
应用编程接口(API)  
  
应用程序  
  
\--------------------------------------------------------------------------------  
  
二、优化硬件  
  
如果你需要庞大的数据库表(>2G)，你应该考虑使用64位的硬件结构，像Alpha、Sparc或即将推出的IA64。因为MySQL内部使用大量64位的整数，64位的CPU将提供更好的性能。  
  
对大数据库，优化的次序一般是RAM、快速硬盘、CPU能力。  
  
更多的内存通过将最常用的键码页面存放在内存中可以加速键码的更新。  
  
如果不使用事务安全(transaction-safe)的表或有大表并且想避免长文件检查，一台UPS就能够在电源故障时让系统安全关闭。  
  
对于数据库存放在一个专用服务器的系统，应该考虑1G的以太网。延迟与吞吐量同样重要。  
  
\--------------------------------------------------------------------------------  
  
三、优化磁盘  
  
为系统、程序和临时文件配备一个专用磁盘，如果确是进行很多修改工作，将更新日志和事务日志放在专用磁盘上。  
低寻道时间对数据库磁盘非常重要。对与大表，你可以估计你将需要log(行数)/log(索引块长度/3\*2/(键码长度 + 数据指针长度))+1次寻到才能找到一行。对于有500000行的表，索引Mediun int类型的列，需要log(500000) / log(1024/3\*2/(3 + 2))+1=4次寻道。上述索引需要500000\*7\*3/2=5.2M的空间。实际上，大多数块将被缓存，所以大概只需要1-2次寻道。  
然而对于写入（如上），你将需要4次寻道请求来找到在哪里存放新键码，而且一般要2次寻道来更新索引并写入一行。  
对于非常大的数据库，你的应用将受到磁盘寻道速度的限制，随着数据量的增加呈N log N数据级递增。  
将数据库和表分在不同的磁盘上。在MySQL中，你可以为此而使用符号链接。  
条列磁盘(RAID 0)将提高读和写的吞吐量。  
带镜像的条列(RAID 0+1)将更安全并提高读取的吞吐量。写入的吞吐量将有所降低。  
不要对临时文件或可以很容易地重建的数据所在的磁盘使用镜像或RAID(除了RAID 0)。  
在Linux上，在引导时对磁盘使用命令hdparm -m16 -d1以启用同时读写多个扇区和DMA功能。这可以将响应时间提高5~50%。  
在Linux上，用async (默认)和noatime挂载磁盘(mount)。  
对于某些特定应用，可以对某些特定表使用内存磁盘，但通常不需要。  
  
\--------------------------------------------------------------------------------  
  
四、优化操作系统  
  
不要交换区。如果内存不足，增加更多的内存或配置你的系统使用较少内存。  
不要使用NFS磁盘(会有NFS锁定的问题)。  
增加系统和MySQL服务器的打开文件数量。(在safe\_mysqld脚本中加入ulimit -n \#)。  
增加系统的进程和线程数量。  
如果你有相对较少的大表，告诉文件系统不要将文件打碎在不同的磁道上(Solaris)。  
使用支持大文件的文件系统(Solaris)。  
选择使用哪种文件系统。在Linux上的Reiserfs对于打开、读写都非常快。文件检查只需几秒种。  
  
\--------------------------------------------------------------------------------  
  
五、选择应用编程接口  
  
PERL  
可在不同的操作系统和数据库之间移植。  
适宜快速原型。  
应该使用DBI/DBD接口。  
PHP  
比PERL易学。  
使用比PERL少的资源。  
通过升级到PHP4可以获得更快的速度。  
C  
MySQL的原生接口。  
较快并赋予更多的控制。  
低层，所以必须付出更多。  
C++  
较高层次，给你更多的时间来编写应用。  
仍在开发中  
ODBC  
运行在Windows和Unix上。  
几乎可在不同的SQL服务器间移植。  
较慢。MyODBC只是简单的直通驱动程序，比用原生接口慢19%。  
有很多方法做同样的事。很难像很多ODBC驱动程序那样运行，在不同的领域还有不同的错误。  
问题成堆。Microsoft偶尔还会改变接口。  
不明朗的未来。(Microsoft更推崇OLE而非ODBC)  
ODBC  
运行在Windows和Unix上。  
几乎可在不同的SQL服务器间移植。  
较慢。MyODBC只是简单的直通驱动程序，比用原生接口慢19%。  
有很多方法做同样的事。很难像很多ODBC驱动程序那样运行，在不同的领域还有不同的错误。  
问题成堆。Microsoft偶尔还会改变接口。  
不明朗的未来。(Microsoft更推崇OLE而非ODBC)  
JDBC  
理论上可在不同的操作系统何时据库间移植。  
可以运行在web客户端。  
Python和其他  
可能不错，可我们不用它们。  
  
\--------------------------------------------------------------------------------  
  
六、优化应用  
  
应该集中精力解决问题。  
在编写应用时，应该决定什么是最重要的：  
速度  
操作系统间的可移植性  
SQL服务器间的可移植性  
使用持续的连接。.  
缓存应用中的数据以减少SQL服务器的负载。  
不要查询应用中不需要的列。  
不要使用SELECT \* FROM table\_name...  
测试应用的所有部分，但将大部分精力放在在可能最坏的合理的负载下的测试整体应用。通过以一种模块化的方式进行，你应该能用一个快速“哑模块”替代找到的瓶颈，然后很容易地标出下一个瓶颈。  
如果在一个批处理中进行大量修改，使用LOCK TABLES。例如将多个UPDATES或DELETES集中在一起。  
  
\--------------------------------------------------------------------------------  
  
七、应该使用可移植的应用  
  
Perl DBI/DBD  
ODBC  
JDBC  
Python(或其他有普遍SQL接口的语言)  
你应该只使用存在于所有目的SQL服务器中或可以很容易地用其他构造模拟的SQL构造。[www.mysql.com][]上的Crash-me页可以帮助你。  
为操作系统/SQL服务器编写包装程序来提供缺少的功能。  
  
\--------------------------------------------------------------------------------  
  
八、如果你需要更快的速度，你应该：  
  
找出瓶颈(CPU、磁盘、内存、SQL服务器、操作系统、API或应用)并集中全力解决。  
使用给予你更快速度/灵活性的扩展。  
逐渐了解SQL服务器以便能为你的问题使用可能最快的SQL构造并避免瓶颈。  
优化表布局和查询。  
使用复制以获得更快的选择(select)速度。  
如果你有一个慢速的网络连接数据库，使用压缩客户/服务器协议。  
不要害怕时应用的第一个版本不能完美地移植，在你解决问题时，你总是可以在以后优化它。  
  
\--------------------------------------------------------------------------------  
  
九、优化MySQL  
  
挑选编译器和编译选项。  
位你的系统寻找最好的启动选项。  
通读MySQL参考手册并阅读Paul DuBios的《MySQL》一书。(已有中文版-译注)  
多用EXPLAIN SELECT、SHOW VARIABLES、SHOW STATUS和SHOW PROCESSLIST。  
了解查询优化器的工作原理。  
优化表的格式。  
维护你的表(myisamchk、CHECK TABLE、 OPTIMIZE TABLE)  
使用MySQL的扩展功能以让一切快速完成。  
如果你注意到了你将在很多场合需要某些函数，编写MySQL UDF函数。  
不要使用表级或列级的GRANT，除非你确实需要。  
购买MySQL技术支持以帮助你解决问题憨笑  
  
\--------------------------------------------------------------------------------  
  
十、编译和安装MySQL  
  
通过位你的系统挑选可能最好的编译器，你通常可以获得10-30%的性能提高。  
在Linux/Intel平台上，用pgcc(gcc的奔腾芯片优化版)编译MySQL。然而，二进制代码将只能运行在Intel奔腾CPU上。  
对于一种特定的平台，使用MySQL参考手册上推荐的优化选项。  
一般地，对特定CPU的原生编译器(如Sparc的Sun Workshop)应该比gcc提供更好的性能，但不总是这样。  
用你将使用的字符集编译MySQL。  
静态编译生成mysqld的执行文件(用--with-mysqld-ldflags=all-static)并用strip sql/mysqld整理最终的执行文件。  
注意，既然MySQL不使用C++扩展，不带扩展支持编译MySQL将赢得巨大的性能提高。  
如果操作系统支持原生线程，使用原生线程(而不用mit-pthreads)。  
用MySQL基准测试来测试最终的二进制代码。  
  
\--------------------------------------------------------------------------------  
  
十一、维护  
  
如果可能，偶尔运行一下OPTIMIZE table，这对大量更新的变长行非常重要。  
偶尔用myisamchk -a更新一下表中的键码分布统计。记住在做之前关掉MySQL。  
如果有碎片文件，可能值得将所有文件复制到另一个磁盘上，清除原来的磁盘并拷回文件。  
如果遇到问题，用myisamchk或CHECK table检查表。  
用mysqladmin -i10 precesslist extended-status监控MySQL的状态。  
用MySQL GUI客户程序，你可以在不同的窗口内监控进程列表和状态。  
使用mysqladmin debug获得有关锁定和性能的信息。  
  
\--------------------------------------------------------------------------------  
  
十二、优化SQL  
  
扬SQL之长，其它事情交由应用去做。使用SQL服务器来做：  
  
找出基于WHERE子句的行。  
JOIN表  
GROUP BY  
ORDER BY  
DISTINCT  
  
不要使用SQL来做：  
  
检验数据(如日期)  
成为一只计算器  
  
技巧：  
  
明智地使用键码。  
键码适合搜索，但不适合索引列的插入/更新。  
保持数据为数据库第三范式，但不要担心冗余信息或这如果你需要更快的速度，创建总结表。  
在大表上不做GROUP BY，相反创建大表的总结表并查询它。  
UPDATE table set count=count+1 where key\_column=constant非常快。  
对于大表，或许最好偶尔生成总结表而不是一直保持总结表。  
充分利用INSERT的默认值。  
  
\--------------------------------------------------------------------------------  
  
十三、不同SQL服务器的速度差别（以秒计）  
  
\+--------------------------+--------+---------+  
|通过键码读取2000000行： | NT | Linux |  
\+--------------------------+--------+---------+  
|mysql | 367 | 249 |  
\+--------------------------+--------+---------+  
|mysql\_odbc | 464 | |  
\+--------------------------+--------+---------+　  
|db2\_odbc | 1206 | |  
\+--------------------------+--------+---------+　  
|informix\_odbc | 121126 | |  
\+--------------------------+--------+---------+　  
|ms-sql\_odbc 　 | 1634 | |  
\+--------------------------+--------+---------+  
|oracle\_odbc | 20800 | |  
\+--------------------------+--------+---------+　  
|solid\_odbc | 877 　 | |  
\+--------------------------+--------+---------+  
|sybase\_odbc | 17614 | |  
\+--------------------------+--------+---------+　  
  
\+--------------------------+--------+---------+　  
|插入350768行： | NT | Linux |  
\+--------------------------+--------+---------+  
|mysql | 381 | 206 |  
\+--------------------------+--------+---------+  
|mysql\_odbc | 619 　 | |  
\+--------------------------+--------+---------+  
|db2\_odbc | 3460 　| |  
\+--------------------------+--------+---------+  
|informix\_odbc | 2692 　| |  
\+--------------------------+--------+---------+  
|ms-sql\_odbc | 4012 　| |  
\+--------------------------+--------+---------+  
|oracle\_odbc | 11291 | |  
\+--------------------------+--------+---------+　  
|solid\_odbc | 1801 　| |  
\+--------------------------+--------+---------+  
|sybase\_odbc | 4802 　| |  
\+--------------------------+--------+---------+  
  
在上述测试中，MySQL配置8M高速缓存运行，其他数据库以默认安装运行。  
  
本文来自: ([www.91linux.com][]) 详细出处参考：[http://www.91linux.com/html/arti ... /20071213/9050.html][http_www.91linux.com_html_arti ... _20071213_9050.html]

网站整体优化其一：数据库优化[http://news.jx163.com][http_news.jx163.com]　发布时间：2009-05-05　来源：站长网 作者：　点击： 8  
　　目前web2.0的程序，很大瓶颈是数据库的吞度量。不过，如何才能确定系统的瓶颈是数据库呢，因为只有确定数据库是整个系统的瓶颈，我们才有必要去优化他，毕竟，还有这么多需求等待我们去做。  
  
　　如何确定数据库是瓶颈?  
  
　　1 如果程序设计良好，有一个数据库操作逻辑层，可以从这个层的统计数据看到每个请求花费的时间，如果平均时间已经不能让你容忍的话，数据库已经是瓶颈了。  
  
　　2 在数据库的服务器上使用top命令，看看mysql服务器占用资源的情况，看看机子的平均负载。  
  
　　如果服务器的平均负载已经很高，mysql占用了块100%的cpu资源，说明mysql服务器很忙了。  
  
　　3 在数据库服务器上使用iostat命令，看看磁盘IO，如果block住的操作比较多的话，说明数据库操作还是过于频繁了，磁盘都响应不急了。  
  
　　4 建议打开mysql的慢查询日志，这样grep select看一下日志中的慢查询的数量，如果数量较多，说明慢查询的数量很多，需要进行调整了。  
  
　　5 如果有一天数据库无法插入了，需要检查一下数据库表是不是过大了。32位的操作系统上一个表最大的容量是2^32这么大。不过还是建议增加一个数据库操作的逻辑层，在数据库操作的前后记录下操作的时间，进行统计上报，利用监控程序来报警相关负责人，这样可以及早的知道数据库是瓶颈，提前做出优化。  
  
　　知道数据库是瓶颈了，如何来进行优化呢?  
  
　　1 我们第一个想到是看看数据库的容量是不是太大了，如果数据库表太大的话，索引文件也会比较大，每次的更新操作就会更加的费时。需要考虑进行分库和分表了。  
  
　　分库分表按照一定的规则来对数据库中的记录进行分区来存储，一方面可以做到一定的负载均衡，将请求平分下来，每个区段去独自承受;另一方面，分库分表可以使我们存储和操作更多的数据。  
  
　　不过分库分表需要多之前基于单库的程序进行修改，存在一定的风险，因此，在程序设计之初就应该考虑到分库分表的需要，最好是将数据库操作层独立出来，便于扩展和更改。  
  
　　2 如果数据库表不是很大，但是查询慢的话，我们需要检查一下我们的sql查询语句，利用mysql的explain语句看看是不是使用了索引，如果没有使用索引，那我们需要在相应的字段上建上索引，反复的使用explain，寻找到个一个合适的索引。  
  
　　在建索引时需要考虑：  
  
　　1)数据库的索引要做到越少越好。  
  
　　因为每次更新都需要更新索引，索引过多就会降低写入的速度。  
  
　　2)最窄的字段放在键的左边。  
  
　　这样提高了索引中每一个点的基数，带来更好的索引读写性能。  
  
　　3)尽量避免file sort排序、临时表和表扫描。  
  
　　对于大表，全表扫描会导致大量的磁盘IO的操作，会导致操作非常的缓慢。  
  
　　4)对于大表，尽量不要将索引建在字符串类型的列上，字符串的匹配是很费时的，需要付出很高的性能代价，如果一定有必要，建议对字符串列进行hash后取一个整形的值来进行索引。  
  
　　3 如果更新操作有点慢，而读操作的响应要求不需要很及时的话，可以考虑利用mysql的主从热备来分担读写的压力。  
  
　　毕竟对数据库的操作，写少读多。因此，我们将对数据库的写操作放到mysql的主服务器上，利用mysql的热备，我们在备份的数据库服务器上进行读操作，由于可以有多个热备mysql，于是可以将读操作分布在多个热备上面，从而将读操作均衡开来，提高读操作的性能。  
  
　　4 缓存的使用  
  
　　缓存是一切后台程序的根本，因为80%的请求是对应20%的数据，我们只需要少量的内存将20%的数据缓存起来，就可以大大的满足我们系统需求，何乐而不为呢。  
  
　　1)mysql设置中尽量增加key cache，thread cache、查询的cache。  
  
　　2)在应用程序层增加一个memcached这样的通用cache。  
  
　　3)对于少量数据，但是操作频繁的表使用mysql提供的内存heap表，可以获得极高的写入和读取速度。  
  
　　5 数据库的设计上进行优化  
  
　　对于传统的数据库设计我们讲究建模范式，避免数据的冗余从而导致脏数据。然而在我们实际的应用中需要根据情况来使用第三范式的一些规则，对于一些频繁需要在多个地方出现的数据，如同一个论坛这种用户和主题以及回复等有关联的应用中，如果我们将用户同主题和回复分开来存储，每次查询一下一篇文章或者一个回复的情况都需要对用户表和主题表或者回复表进行联查，如果数据量小的话，这样联查的性能还是可以接受的，如果表大一点，上了3、4十万以上的数据，联查的速度就会比较慢了。  
  
　　该范式化的地方需要进行范式化，但是还是需要根据情况来设计我们的表，从而达到性能和良好设计的折中。  
  
　　其它的话：  
  
　　1 对于数据库的操作建议分层处理，至少分为两层，一层是数据库操作的逻辑层，一层是数据库的cache层。  
  
　　从一开始就考虑如此，可以很方便在未来对数据库进行划分部署、分库分表扩展。  
  
　　2 增加mysql的监控，监控mysql的慢查询日志，监控mysql的请求情况。  
  
　　3 根据自己的需要来选择mysql的存储引擎。  
  
　　myisam有较高的读写速度，但是由于表锁定，不能同时进行快速的读和写。  
  
　　innodb支持事务，提供了行级的锁，但是为了使用事务，表空间会比较大，而且不支持全文索引。  
  
　　heap将表放到内存中，适合与表小而需要频繁操作的情况，如用户信息，其读写很快，但是不是持久的，需要自己来写工具让其持久。  
  
　　4 mysql服务器的一些状态检测的命令。  
  
　　show slave status：可以看到主从同步的情况。  
  
　　show \[full\] processlist：可以看到mysql服务器的请求情况，如果发现lock情况很多，需要注意了。  
  
　　show status：可以看到mysql服务器的各种请求情况。  
  
　　我的小站 [http://www.qin3.com][http_www.qin3.com]

详细介绍优化mysql性能的十个参数,数据库教程,数据库设计1)、back\_log：  
要求 MySQL 能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。  
  
back\_log 值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的TCP/IP连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。试图设定back\_log高于你的操作系统的限制将是无效的。  
  
当你观察你的主机进程列表，发现大量 264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时，就要加大 back\_log 的值了。默认数值是50，我把它改为500。  
  
(2)、interactive\_timeout：  
服务器在关闭它前在一个交互连接上等待行动的秒数。一个交互的客户被定义为对 mysql\_real\_connect()使用 CLIENT\_INTERACTIVE 选项的客户。默认数值是28800，我把它改为7200。  
  
(3)、key\_buffer\_size：  
索引块是缓冲的并且被所有的线程共享。key\_buffer\_size是用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，到你能负担得起那样多。如果你使它太大，系统将开始换页并且真的变慢了。默认数值是8388600(8M)，我的MySQL主机有2GB内存，所以我把它改为 402649088(400MB)。  
  
(4)、max\_connections：  
允许的同时客户的数量。增加该值增加 mysqld 要求的文件描述符的数量。这个数字应该增加，否则，你将经常看到 Too many connections 错误。 默认数值是100，我把它改为1024 。  
  
(5)、record\_buffer：  
每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，你可能想要增加该值。默认数值是131072(128K)，我把它改为16773120 (16M)  
  
(6)、sort\_buffer：  
每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作。默认数值是2097144(2M)，我把它改为 16777208 (16M)。  
  
(7)、table\_cache：  
为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。MySQL对每个唯一打开的表需要2个文件描述符。默认数值是64，我把它改为512。  
  
(8)、thread\_cache\_size：  
可以复用的保存在中的线程的数量。如果有，新的线程从缓存中取得，当断开连接的时候如果有空间，客户的线置在缓存中。如果有很多新的线程，为了提高性能可以这个变量值。通过比较 Connections 和 Threads\_created 状态的变量，可以看到这个变量的作用。我把它设置为 80。  
  
(9)mysql的搜索功能  
用mysql进行搜索，目的是能不分大小写，又能用中文进行搜索  
只需起动mysqld时指定 --default-character-set=gb2312  
  
(10)、wait\_timeout：  
服务器在关闭它之前在一个连接上等待行动的秒数。 默认数值是28800，我把它改为7200。  
  
注：参数的调整可以通过修改 /etc/my.cnf 文件并重启 MySQL 实现。这是一个比较谨慎的工作，上面的结果也仅仅是我的一些看法，你可以根据你自己主机的硬件情况（特别是内存大小）进一步修改。

转载于:https://www.cnblogs.com/DjangoBlog/p/3992467.html

[MySQL]: http://www.cppblog.com/woaidongmao/archive/2009/06/10/87310.html
[http_venublog.com_2009_03_16_m ... lers-in-partitions]: http://venublog.com/2009/03/16/mysql-51-mix-of-handlers-in-partitions/
[www.mysql.com]: http://www.mysql.com/
[www.91linux.com]: http://www.91linux.com/
[http_www.91linux.com_html_arti ... _20071213_9050.html]: http://www.91linux.com/html/article/database/mysql/20071213/9050.html
[http_news.jx163.com]: http://news.jx163.com/
[http_www.qin3.com]: http://www.qin3.com/