http://jackxiang.com/index.php zh-cn http://jackxiang.com/post// jack <xdy108@126.com> Fri, 21 Oct 2011 05:27:51 +0000 http://jackxiang.com/post// “保持Key_reads/Key_read_requests至少1/100以上，越小越好。”
“大的文件提供更高的性能，但数据库恢复时会用更多的时间。”

没做单独测试，， 不过用where id>100000 limit 10 的速度是0.0007秒 直接用 order by id desc limit 100000,10 的速度是162秒
用select id,v_1,v_2 from max_num where id>(select `id` order by desc limit ($page-1)*$pagesize,1) limit 10;的速度 是5-8秒之间
ORDER BY ID(主键)
select id(主键递增),title from table where id in(select id from table order by id desc limit ($page-1)*$pagesize,$pagesize)这个 方法的速度 和WHERE id>(select `id` order by desc limit ($page-1)*$pagesize,1)的速度相 接近 ，，不过 也还是很不乐观

===
MySQL数据库的优化：单机优化
单机MySQL 的优化我分为三个部分，一是服务器物理硬件的优化，二是
MySQL 安装时的编译优化，三是自身配置文件my.cnf 的优化。
一、服务器硬件对MySQL性能的影响
磁盘寻道能力（磁盘I/O），我们现在上的都是SAS15000 转的硬盘。
MySQL 每秒钟都在进行大量、复杂的查询操作，对磁盘的读写量可想而知。
所以，通常认为磁盘I/O 是制约MySQL
性能的最大因素之一，对于日均访问量
在100 万PV 以上的Discuz! 论坛，由于
磁盘I/O 的制约，MySQL 的性能会非常
低下！解决这一制约因素可以考虑以下几种解决方案： 使用RAID1+0 磁
盘阵列，注意不要尝试使用RAID-5，MySQL 在RAID-5 磁盘阵列上的效率
不会像你期待的那样快；
CPU 对于MySQL 应用，推荐使用DELL R710，E5620 @2.40GHz（4
core）* 2 ，我现在比较喜欢DELL R710，也在用其作Linux 虚拟化应用；
物理内存对于一台使用MySQL 的Database Server 来说，服务器内存建
议不要小于2GB，推荐使用4GB 以上的物理内存，不过内存对于现在的服务
器而言可以说是一个可以忽略的问题，工作中遇到高端服务器基本上内存
都超过了32G。我们工作中用得比较多的数据库服务器是HP DL580G5 和
DELL R710，稳定性和性能都不错；特别是DELL R710，我发现许多同行都是
采用它作数据库的服务器，所以重点推荐下。
二、MySQL的线上安装
我建议采取编译安装的方法，这样性能上有较大提升，服务器系统我建
议用64bit 的CentOS 5.5，源码包的编译参数会默认以Debug 模式生成二
进制代码，而Debug 模式给MySQL 带来的性能损失是比较大的，所以当我
们编译准备安装的产品代码时，一定不要忘记使用“--without-debug”参
数禁用Debug 模式。
而如果把--with-mysqld-ldflags
和--with-client-ldflags 二个编译参
数设置为--all-static 的话，可以告诉
编译器以静态方式编译和编译结果代
码得到最高的性能。使用静态编译和使用动态编译的代码相比，性能差距
可能会达到5% 至10% 之多。我参考了简朝阳先生的编译参数，特列如下，
供大家参考
./configure prefix=/usr/local/mysql without-debug withoutbench
enable-thread-safe-client enable-assembler enable-profiling
with-mysqld-ldflags=-all-static with-client-ldflags=-all-static
with-charset=latin1 with-extra-charset=utf8,
gbk with-innodb with-csv-storage-engine with-federatedstorage-
engine with-mysqld-user=mysql without-embedded-server
with-server-suffix=-community with-unix-socket-path=/usr/
local/mysql/sock/mysql.sock

三、my.cnf优化
对 MySQL 自身的优化主要是对其
配置文件my.cnf 中的各项参数进行优
化调整。下面，我们根据以上硬件配置
结合一份已经优化好的my.cnf 进行说
明：
#vim /etc/my.cnf
以下只列出my.cnf 文件中[mysqld] 段落中的内容。（注释部分略）
[mysqld]
port = 3306
serverid = 1
socket = /tmp/mysql.sock
skip-locking
skip-name-resolve
back_log = 384
key_buffer_size = 384M
max_allowed_packet = 4M
thread_stack = 256K
table_cache = 614K
sort_buffer_size = 6M
read_buffer_size = 4M
join_buffer_size = 8M
myisam_sort_buffer_size = 64M
table_cache = 512
thread_cache_size = 64
query_cache_size = 64M
tmp_table_size = 256M
max_connections = 768
max_connect_errors = 1000
wait_timeout = 10
thread_concurrency = 8
skip-networking
table_cache=1024
innodb_additional_mem_pool_size=4M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=2M
innodb_thread_concurrency=8
key_buffer_size=256M
tmp_table_size=64M
read_buffer_size=4M
read_rnd_buffer_size=16M
sort_buffer_size=32M
thread_cache_size=120
query_cache_size=32M
※ 值得注意的是：
一、如果key_reads 太大，则应该把my.cnf 中key_buffer_size 变大，保持
key_reads/key_read_requests 至少1/100 以上，越小越好。
二、如果qcache_lowmem_prunes 很大，就要增加query_cache_size 的值。
很多时候我们发现，通过参数设置进行性能优化所带来的性能提升，可
能并不如许多人想象的那样产生质的飞跃，除非是之前的设置存在严重不
合理的情况。我们不能将性能调优完全依托于通过DBA 在数据库上线后
进行的参数调整，而应该在系统设计和开发阶段就尽可能减少性能问题。
本文有删节，完整内容见原文：
http://database.51cto.com/art/201103/247839.htm

MySQL写入优化
innodb_buffer_pool_size
如果用Innodb，那么这是一个重要变量。相对于MyISAM 来说，Innodb
对于buffer size 更敏感。MySIAM 可能对于大数据量使用默认的key_
buffer_size 也还好，但Innodb 在大数据量时用默认值就感觉在爬了。
Innodb 的缓冲池会缓存数据和索引，所以不需要给系统的缓存留空间，如果
只用Innodb，可以把这个值设为内存的
70%-80%。和 key_buffer 相同，如果
数据量比较小也不怎么增加，那么不要
把这个值设太高也可以提高内存的使
用率。
innodb_additional_pool_size
这个的效果不是很明显，至少是当操作系统能合理分配内存时。但你可
能仍需要设成20M 或更多一点以看Innodb 会分配多少内存做其他用途。
innodb_log_file_size
对于写很多尤其是大数据量时非常重要。要注意，大的文件提供更高的
性能，但数据库恢复时会用更多的时间。我一般用64M-512M，具体取决于
服务器的空间。
innodb_log_buffer_size
默认值对于多数中等写操作和事务短的运用都是可以的。如果经常做
更新或者使用了很多blob 数据，应该增大这个值。但太大了也是浪费内存，
因为1 秒钟总会 flush（这个词的中文怎么说呢？）一次，所以不需要设到
超过1 秒的需求。8M-16M 一般应该够了。小的运用可以设更小一点。
innodb_flush_log_at_trx_commit （这个很管用）
抱怨Innodb 比MyISAM 慢 100 倍？那么你大概是忘了调整这个值。默
认值1 的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）
硬盘，这是很费时的。特别是使用电
池供电缓存（Battery backed up cache）
时。设成2 对于很多运用，特别是从
MyISAM 表转过来的是可以的，它的意
思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush 到硬盘，所以你
一般不会丢失超过1-2 秒的更新。设成0 会更快一点，但安全方面比较差，
即使MySQL 挂了也可能会丢失事务的数据。而值2 只会在整个操作系统
挂了时才可能丢数据。
上面是网上看的，我发现慢查询日志内有很多update 和insert 的查询，
就把innodb_flush_log_at_trx_commit 改成了2，效果很明显，改成0 会更明
显，但安全性比较差。做下面的操作启动mysqld 就生效：
vim /etc/my.cnf
innodb_flush_log_at_trx_commit=2
也可以在mysqld 运行时执行：
set GLOBAL innodb_flush_log_at_trx_commit = 2
下面是mysql 手册上innodb_flush_log_at_trx_commit 的解释：
如果innodb_flush_log_at_trx_commit 设置为0，log buffer 将每秒一次地
写入log file 中，并且log file 的flush( 刷到磁盘) 操作同时进行；但是，这
种模式下，在事务提交的时候，不会有任何动作。如果 innodb_flush_log_
at_trx_commit 设置为1( 默认值)，log buffer 每次事务提交都会写入log
file，并且，flush 刷到磁盘中去。如果innodb_flush_log_at_trx_commit 设置
为2，log buffer 在每次事务提交的时候都会写入log file，但是，flush( 刷
到磁盘) 操作并不会同时进行。这种模式下，MySQL 会每秒一次地去做
flush( 刷到磁盘) 操作。注意：由于进程调度策 略问题，这个“每秒一次的
flush( 刷到磁盘) 操作”并不是保证100% 的“每秒”。
默认值1 是为了ACID (atomicity, consistency, isolation, durability)
原子性，一致性，隔离性和持久化的考虑。如果你不把innodb_flush_log_at_
trx_commit 设置为1，你将获得更好的性能，但是，你在系统崩溃的情况，可
能会丢失最多一秒钟的事务数据。当你把innodb_flush_log_at_trx_commit
设置 为0，mysqld 进程的崩溃会导致上一秒钟所有事务数据的丢失。如
果你把innodb_flush_log_at_trx_commit 设置为2，只有在操作系统崩溃或
者系统掉电的情况下，上一秒钟所有事务数据才可能丢失。InnoDB 的crash
recovery 崩溃恢复机制并不受这个值的影响，不管这个值设置为多少，crash
recovery 崩溃恢复机制都会工作。
另外innodb_flush_method 参数也值得关注，对写操作有影响：
innodb_flush_method ： 设置InnoDB 同步IO 的方式：
1) Default 使用fsync（）。
2) O_SYNC 以sync 模式打开文件，通常比较慢。
3) O_DIRECT，在Linux 上使用Direct IO。可以显著提高速度，特别是
在RAID 系统上。避免额外的数据复制和double buffering（mysql buffering
和OS buffering）。
原文：
http://fendou.org/2011/08/30/mysql 写入优化/ ]]> http://jackxiang.com/post//#blogcomment <user@domain.com> Thu, 01 Jan 1970 00:00:00 +0000 http://jackxiang.com/post//#blogcomment