本文记录了一些mysql性能调优相关知识。
服务器参数调优，有哪些关键点？
操作系统的选择
建议安装在64bit的linux平台（centos\linux\suse）。
关闭SWAP
设置vm.swappiness = 10，如果设置为0当内存不足时会导致OOM。
关闭NUMA
如果是单实例部署，需要关闭NUMA。关闭NUMA有3种方式：BIOS中关闭、OS内核中关闭、启动MYSQL时关闭。
多网卡绑定
一定要做双网卡绑定。关键网络参数调整：
net.ipv4.tcp_keepalive_time = 120 #用于减少失效tcp连接占用的资源，加快资源回收的效率 net.ipv4.tcp_keepalive_intvl = 30 #用于减少失效tcp连接占用的资源，加快资源回收的效率 net.ipv4.tcp_keepalive_probes = 3 #用于减少失效tcp连接占用的资源，加快资源回收的效率 net.core.rmem_max = 4194304 #最大socket读buffer（单位：字节数） net.core.wmem_max = 4194304 #最大socket写buffer（单位：字节数） net.ipv4.tcp_moderate_rcvbuf = 1 #开启recv buffer自动调节机制 net.ipv4.tcp_wmem = 4096 16384 4194304 #为socket发送缓冲区分配的字节数（依次是最小值、默认值、最大值） net.ipv4.tcp_rmem = 4096 87380 4194304 #为socket接受缓冲区分配的字节数（依次是最小值、默认值、最大值）
|
磁盘调度算法
建议采用deadline。磁盘调度设置：
echo deadline > /sys/block/fioa/queue/schedule
|
查看设置：
[root@mysql_42 ~]# cat /sys/block/sda/queue/scheduler noop anticipatory deadline [cfq]
|
文件系统
推荐选择xfs\ext4，格式化时记得添加noatime,nobarrier参数。
[root@mysql_42 ~]# mount /dev/sda3 on / type ext4 (rw) proc on /proc type proc (rw) sysfs on /sys type sysfs (rw) devpts on /dev/pts type devpts (rw,gid=5,mode=620) tmpfs on /dev/shm type tmpfs (rw) /dev/sda1 on /boot type ext4 (rw) /dev/sdb on /data type ext4 (rw) /dev/sdc on /log type ext4 (rw) none on /proc/sys/fs/binfmt_misc type binfmt_misc (rw)
|
调大open files
一定要调大open files，可以通过命令：ulimit -n查看。
修改/etc/hosts绑定IP和主机名。
MySQL性能调优有哪些关键点/经验？
实例参数调优
innodb_buffer_pool_size=物理内存的60%-80% #总IO能力 innodb_thread_concurrency=CPU的核数 #线程的并发数 query_cache_type=0 #禁用结果集缓存，一般通过redis\memcache缓存 query_cache_size=0 #禁用结果集缓存 max_user_connections=应用连接池大小X应用服务器的数量 #连接数设置要考虑每个会话占用的内存 interactive_timeout=8小时 #交互连接空闲超时 wait_timeout=8小时 #非交互的连接空闲超时 innodb_io_capacity=20000（SSD盘） #innodb每秒后台进程处理IO操作的数据页（page）上限 innodb_flush_log_at_trx_commit=1 #每次事务提交都刷盘，可保证数据安全。若设置为0的话是每秒刷盘，和提交没关系，不建议。设置为2的话每次提交会把数据存到操作系统的缓存中，然后定期刷盘。如果操作系统挂掉了数据就丢了。5.6以后该参数没有性能问题了。 sync_binlog=1#1个提交就把日志从缓存写到日志文件 innodb_log_file_size=1-2G（SAS盘）；4-8G（SSD盘）#日志文件的大小，若设置太小日志文件会频繁切换产生新文件，影响IO能力。 innodb_log_files_in_group=4-8 #日志组的数量 innodb_flush_method=O_DIRECT #刷脏块的方式：直接落盘，避免数据多次被cache innodb_max_dirty_pages_pct=50 #innodb buffer cache中的脏块刷到磁盘的比例 innodb_flush_neighbors=0 #合并相邻页数据为1个IO：SAS建议开启，SSD可以关闭 transaction-isolation = READ-COMMITTED #隔离级别
|
SQL调优
1、制定SQL编写规范，约束开发人员从项目开始阶段就编写出高效的SQL：
1、禁止多于3表的join 2、不要select * 3、count(*) 尽量用redis缓存去做 4、避免多作的排序 比如group by 时不用 排序 order by null 5、模糊查询前缀不用要% 如 like 'ss%' 6、使用in 代替 or 7、禁止隐式转换 8、禁止负向查询 如 not in, != ,not like等 9、尽量用union all 代替 union ,union 会排序去重 10、where尽量早过滤 11、不做计算 如select id*10.2 from t1 12、用id条件过滤数据后再分页where id>10000 limit 10，不要用 limit 10000,10
|
2、实时监控慢查询，通过分析执行计划优化SQL语句
3、合理的进行索引设计
覆盖索引 1、查询谓词都能通过index进行扫描 2、排序谓词都能利用index的有序性 3、index包含了查询所需要的所有字段，这样只用索引就不用查表了 不能使用索引 1、不要给选择率低的字段建索引（通过索引扫描记录超30%就全表扫描了） 如 性别 男和女 直接查表快走索引更慢 2、联合索引中，第一个索引不要使用范围查询，第一个查询条件不要是最左索引列 3、like 查询最左 不要是%开始 4、两个独立索引，一个用于检索，一个用于排序，（索引不是越多越好，尽量合并） 5、表关联字段类型不一样（长度不一样）不会走索引 6、索引字段使用函数 7、不要使用外键约束
|
架构调优
实时监控大表，合理制定拆分方案实施分库分表。
