mysql tips 优化

http://www.ibm.com/developerworks/cn/linux/l-tune-lamp-3.html
这篇文章值得一看。

对TEXT BLOB字段存在的表，会因为删除有无效数据，需要进行优化碎片压缩容量。
optimize table 表名;

修改my.cnf，增加记录执行时间超过long_query_time变量时间的SQL语句。
log-slow-queries       = /var/log/mysql/mysql-slow.log

使用explain显示SQL执行情况，为有问题的sql增加索引：
explain SELECT `ID`, `post_author`, `post_date`, `post_date_gmt`, `post_status`, `post_name`, `post_modified`, `post_modified_gmt`, `post_parent`, `post_type` FROM `wp_posts` WHERE ( (post_status = 'publish' AND (post_type = 'post' OR post_type = '')) OR (post_status = 'publish' AND post_type = 'page') ) AND post_password='' ORDER BY post_modified DESC\G;

使用set设置Mysql全局变量时，它只影响在更改后连接的从该全局变量初始化相应会话变量的客户端。它不会影响已经连接上的客户端的会话变量(甚至是执行SET GLOBAL语句的客户端)。

优化设置变量:
(这些变量在MYSQL的手册都可以找到，我把认为需要调整的都列在这里做记录)。

mysql变量 expire_logs_days 设置Binlog过期删除时间，默认20天。

back_log

MySQL有的主要连接请求的数量。当主MySQL线程在短时间内得到许多连接请求时发挥作用。主线程需要花一些时间(尽管很少)来检查连接并启动一个新线程。back_log值说明MySQL临时停止响应新请求前在短时间内可以堆起多少请求。如果你需要在短时间内允许大量连接，可以增加该数值。

换句话说，该值为“进”TCP/IP连接帧听队列的大小。操作系统有该队列自己的限制值。本手册中Unix listen()系统调用页应有更详细的信息。该变量最大值请查阅OS文档。企图将back_log设置为高于你的操作系统限值是徒劳无益的。

join_buffer_size

用于完全联接的缓冲区的大小(当不使用索引的时候使用联接操作)。一般情况获得快速联接的最好方法是添加索引。当增加索引时不可能通过增加join_buffer_size值来获得快速完全联接。将为两个表之间的每个完全联接分配联接缓冲区。对于多个表之间不使用索引的复杂联接，需要多联接缓冲区。

key_buffer_size

MyISAM表的索引块分配了缓冲区，由所有线程共享。key_buffer_size是索引块缓冲区的大小。键值缓冲区即为键值缓存的最大允许设定值为4GB。有效最大值可以更小，取决于可用物理RAM和操作系统或硬件平台强加的每个进程的RAM限制。增加该值，达到你可以提供的更好的索引处理(所有读和多个写操作)。通常为主要运行MySQL的机器内存的25%。但是，如果你将该值设得过大(例如，大于总内存的50%)，系统将转换为页并变得极慢。MySQL依赖操作系统来执行数据读取时的文件系统缓存，因此你必须为文件系统缓存留一些空间。

sort_buffer_size

每个排序线程分配的缓冲区的大小。增加该值可以加快ORDER BY或GROUP BY操作

使用SHOW STATUS语句并检查Key_read_requests、Key_reads、Key_write_requests和Key_writes状态变量来检查键值缓冲区的性能。Key_reads/Key_read_requests比例一般应小于0.01。如果你使用更新和删除，Key_writes/Key_write_requests比例通常接近1，但如果你更新时会同时影响到多行或如果你正使用DELAY_KEY_WRITE表选项，可能小得多。

interactive_timeout
wait_timeout #服务器关闭非交互连接之前等待活动的秒数。在线程启动时，根据全局wait_timeout值或全局interactive_timeout值初始化会话wait_timeout值，取决于客户端类型(由mysql_real_connect()的连接选项CLIENT_INTERACTIVE定义)。

read_rnd_buffer_size

当排序后按排序后的顺序读取行时，则通过该缓冲区读取行，避免搜索硬盘。将该变量设置为较大的值可以大大改进ORDER BY的性能。但是，这是为每个客户端分配的缓冲区，因此你不应将全局变量设置为较大的值。相反，只为需要运行大查询的客户端更改会话变量。

join_buffer_size

用于完全联接的缓冲区的大小(当不使用索引的时候使用联接操作)。一般情况获得快速联接的最好方法是添加索引。当增加索引时不可能通过增加join_buffer_size值来获得快速完全联接。将为两个表之间的每个完全联接分配联接缓冲区。对于多个表之间不使用索引的复杂联接，需要多联接缓冲区。

max_allowed_packet

包或任何生成的/中间字符串的最大大小。
包消息缓冲区初始化为net_buffer_length字节，但需要时可以增长到max_allowed_packet字节。该值默认很小，以捕获大的(可能是错误的)数据包.如果你使用大的BLOB 列或长字符串，你必须增加该值。应同你想要使用的最大的BLOB一样大。max_allowed_packet的协议限制为1GB。

query_cache_size #当设置query_cache_size变量为非零值时，应记住查询缓存至少大约需要40KB来分配其数据结构。
query_cache_limit#不要缓存大于该值的结果。默认值是1048576(1MB)。

have_query_cache#有query cache
SHOW STATUS LIKE 'Qcache%';#显示query cache情况
FLUSH QUERY CACHE#语句来清理查询缓存碎片以提高内存使用性能。该语句不从缓存中移出任何查询。
RESET QUERY CACHE语句从查询缓存中移出所有查询。FLUSH TABLES语句也执行同样的工作。

Key_reads 是读取本地磁盘索引,Key_read_request是读取cache。

table_cache是打开表的缓存，max_connection是最大连接，table_cache=max_connection*N,N是一个连接最大使用的表缓存。这个可以在启动时设置my.cnf。

索引放入cache中:

在mysql5.1后可以自己建立每个表的内存cache，将索引放入cache

set global wp_posts_cache =20*1024;#建立一块cache,可以在my.cnf设置

cache index wp_posts in wp_posts_cache;#将wp_posts表的索引放入cache

load index into cache wp_posts#预装wp_posts表的索引进入cache

关闭磁盘atime

atime是读取数据时回写时间，可以关闭减少IO。

vi /etc/fstab    

对mysql数据库存放的挂载点修改为noatime

/dev/sda6       /var            ext3    noatime        0       2

mount -oremount /var

Qcache_free_blocks:当一个表被更新之后，和它相关的cache blocks将被free。但是这个block依然可能存在队列中，除非是在队列的尾部。这些blocks将会被统计到这个值来。可以用FLUSH QUERY CACHE语句来清空free blocks。
Qcache_free_memory:可用内存，如果很小，考虑增加query_cache_size
Qcache_hits:自mysql进程启动起，cache的命中数量
Qcache_inserts:自mysql进程启动起，被增加进QC的数量
Qcache_lowmem_prunes:由于内存过少而导致QC被删除的条数。加大query_cache_size，尽可能保持这个值0增长。
Qcache_not_cached:自mysql进程启动起，没有被cache的只读查询数量（包括select,show,use,desc等）
Qcache_queries_in_cache:当前被cache的SQL数量
Qcache_total_blocks:在QC中的blocks数。一个query可能被多个blocks存储，而这几个blocks中的最后一个，未 用满的内存将会被释放掉。例如一个QC结果要占6KB内存，如果query_cache_min_res_unit是4KB，则最后将会生成3个 blocks，第一个block用来存储sql语句文本，这个不会被统计到query+cache_size里，第二个block为4KB，第三个 block为2KB（先allocate4KB，然后释放多余的2KB）。每个表，当第一个和它有关的SQL查询被CACHE的时候，会使用一个 block来存储表信息。也就是说，block会被用在三处地方：表信息，SQL文本，查询结果。

innodb表的几个参数

innodb_buffer_pool_size
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
默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。特别是使用电池供电缓存（Battery backed up cache）时。设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬盘，所以你一般不会丢失超过1-2秒的更新。设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统挂了时才可能丢数据。