mysql性能优化的关键

优化方法

通过os诊断，得到os资源短板，资源短板是否需要提高cpu，内核，内存，实例慢的话，是不是一些参数设置不合理，语句慢，是不是没有用到索引。

一、应用访问优化

减少磁盘访问，性能提高1~1000倍，返回更少数据，性能提高1~100倍，减少交互次数，性能提高1~20倍，并且这些优化需要的成本都比较低。

二、硬件选型

三、操作系统优化

centos服务器参数优化,请根据需要修改网络参数(etc/sysctl.conf)

net.core.somaxconn=65535

net.core.netdev_max_backlog=65535

net.ivp4.tcp_max_syn_backlog=65535

 

加快tcp连接的回收

net.ipv4.tcp_fin_timeout=10

net.ipv4.tcp_fin_refuse=1

net.ipv4.tcp_fin_recyle=1

 

tcp连接缓冲区的默认值，最大值

net.core.wmen_default=87380

net.core.wmen_max=166777216

net.core.rmen_default=87380

net.core.rmen_default=166777216

 

内核参数

kernel.shmmax=4294967295

vm.swappiness=10  #不完全关闭交换分区

 

资源限制（etc/security/limit.cong）

* soft nofile 65535

* hard nofile 65535

* 表示对所有用户有效

soft指的是当前系统生效的设置

hard表明当前系统所能设定的最大值

nofile表示所限制的资源是打开文件的最大数目

65535就是限制数量

四、数据库参数优化

（1）innodb_buffer_pool_size设置为物理内存的80%左右

（2）innodb_thread_concurrency设置为系统的核心数

（3）query_cache_type=0和query_cache_size=0推荐设置，高并发情况下建议关闭

（4）max_user_connections要看应用的连接数

以下全是线程缓存的一些参数

read_buffer_size              #顺序读缓冲，提高顺序读效率

read_md_buffer_size        #随机读缓冲，提高随机读效率

sort_buffer_size                  #排序缓冲，提高排序效率

join_buffer_size                  #表连接缓冲，提高表连接效率

binlog_cache_size              #二进制日志缓冲，提高二进制日志写入

tmp_table_size                     #内存临时表，提高临时表存储效率

thread_stack                         #线程堆栈，暂时寄存SQL语句存储过程

thread_cache_size                #线程缓存，降低多次反复打开线程开销

net_buffer_length                     #线程持续连接缓冲以及读取结果缓冲

bulk_insert_buffet_size                          #myisam表批量写入数据缓冲

（5）interactive_timeou   交互连接超时设置为8小时

（6）wait_timeout    等待超时设置位120s

（7）innodb_io_capacity=20000 innodbIO容量设置为75%

（8）innodb_flush_log_at_trx_commit=1和sync_binlog=1     保证数据安全

（9）innodb_log_file_size  根据日志存放的盘大小设置

（10） innodb_log_files_in_group 日志一组有几个

（11）innodb_flush_method      刷脏块的方式，值为O_DIRECT

（12）innodb_max_dirty_pages_pct    设置为50%

（13）innodb_flush_neighbors 设置为off

（14）transaction_isolation     隔离级别，设置为RR

五、SQL优化

（1）禁止多于3表的join，尽量用单表查询

（2）select只取需要的列，禁止select *

（3）innodb表避免使用count（*），可以使用redis缓存实现这样的count

（4）避免多余的排序，不需要排序是可以使用order by null

（5）不建议在DB里面排序，特别是在一千行以下的，可以在app server上排序

（6）避免使用模糊查询，like ‘%string%’

（7）使用union代替or

（8）禁止隐式类型转换，数值类型禁止加引号，字符串必须加引号

（9）禁止使用负向查询，例如，not in，！=，not like

（10）使用union all代替union，因为union主要将多个结果集合并后再进行唯一性过滤操作，这就涉及到排序和去重，当我们不确定是否需要过滤或者不在乎重复结果集的时候，可以用union all

（11）尽量早过滤，尽量用join代替子查询

（12）禁止在主库进行后台管理统计的操作