mysql优化案例

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

从以上结果可以看出，大表进行分区后，查询效率有所降低，但是随着查询次数的增多，所用时间的差距不断减小。

[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/

以且应该优化什么？

硬件

操作系统/软件库

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://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