MySQL对于很多Linux从业者来说是一个非常棘手的问题。大多数情况下，是因为对数据库问题的情况和处理缺乏清晰的思考。在优化MySQL之前你必须了解的是MySQL的查询过程。很多查询优化工作其实就是遵循一些原则，让MySQL优化器按照预期的方式合理运行。今天，我们特地请来了Linux运维老司机——怜绿青年Linux，为大家带来MySQL优化的实战经验，助你一帆风顺的高薪之路。图-MySQL查询流程1.2优化哲学优化有风险，涉足需谨慎1.2.1优化可能带来的问题优化并不总是在简单的环境下进行，也可能是已经投入生产的复杂系统.优化方法存在很大的风险，只是你没有能力意识到和预见！任何技术都可以解决问题，但一定有带来问题的风险！对于优化来说，解决问题带来的问题必须控制在可接受的范围内才能有成果。现状或更糟是失败的！1.2.2优化需求稳定性和业务可持续性通常比性能更重要！优化必然涉及变化，变化有风险！优化使性能更好，维护和退化是同样可能发生的事件！记住要优化，应该是所有部门的共同工作，没有一个部门可以优化数据库！所以优化工作是由业务需求驱动的！！！1.2.3谁参与优化在进行数据库优化时，数据库管理员、业务部门代表、应用架构师、应用设计人员、应用开发人员、硬件和系统管理员、存储管理员等业务相关人员参与。1.3优化思路1.3.1优化什么数据库优化主要有两个方面：安全和性能。Security--->DataSustainabilityPerformance--->Highperformancedataaccess1.3.2存储、主机、操作系统方面的优化范围有哪些：主机架构稳定性I/O规划和配置SwapswappartitionOSkernel参数和网络问题应用方面：应用稳定性SQL语句性能串口访问资源性能差Session管理这个应用是否适合MySQL数据库优化方面：In-memory数据库结构（物理&逻辑）实例配置说明：无论在，设计系统、定位问题或者优化，都可以按照这个顺序执行。1.3.3优化维度数据库优化有四个维度：硬件、系统配置、数据库表结构、SQL和索引优化选择优化成本：硬件>系统配置>数据库表结构>SQL和索引优化效果：硬件<系统配置<数据库表结构30;从表中显示索引；3．从执行计划、索引问题（是否合理）或语句本身判断4.showstatuslike'%lock%';#查询锁状态killSESSION_ID;#killtheproblematicsession业务周期性卡顿，比如每天10-11点业务很慢，但是还能用。过了这个时间就好了。1、查看slowlog，分析slowlog，分析慢查询语句。2.按照一定的优先级逐一检查所有的慢语句。3、分析topsql，进行explain调试，查看语句执行时间。4.调整索引或语句本身。1.4.3系统级cpuvmstat,sartop,htop,nmon,mpstatmemoryfree,ps-aux,IOdevice(disk,network)iostat,ss,netstat,iptraf,iftop,lsof,vmstat命令说明：Procs:r显示有多少进程在等待CPU时间。b显示处于不间断睡眠中的进程数。WaitingforI/OMemory：swpd显示交换到磁盘的数据块数。Thenumberofunuseddatablocks,userbufferdatablocks,anddatablocksusedfortheoperatingsystemSwap：操作系统每秒从磁盘交换到内存，从内存交换到磁盘的数据块数。s1和s0***为0Io：每秒从设备b1读取的写入设备b0的数据块数。反映磁盘I/OSystem：显示每秒中断（in）和上下文交换（cs）数Cpu：显示用于运行用户代码、系统代码、空闲、等待I/O的CPU时间iostat命令描述示例命令:iostat-dk15 iostat-d-k-x5(查看设备使用情况(%util)和响应时间(await))tps：设备每秒传输的次数。“传输”是指“I/O请求”。多个逻辑请求可以组合成“一个I/O请求”。iops：硬件出厂时，厂商“一次传输”请求大小定义的每秒最大IO次数是未知的。kB_read/s：每秒从设备（驱动表示）读取的数据量；KB_wrtn/s：每秒写入设备（驱动表示）的数据量；kB_read：读取的数据总量；kB_wrtn：写入的数据总量；这些单位是千字节。1.4.4系统级问题的解决方案你认为负载高好还是负载低好？在实际生产中，一般认为只要CPU不超过90%就没有问题。当然也不能排除以下特殊情况：问题一：cpu负载高，IO负载低，内存不足，磁盘性能差SQL问题------>进入数据库层，进一步排查SQL问题IO问题（diskcritical,Poorraiddesign,raiddowngrade,lock,单位时间tps太高）tps太高：大量小数据IO，大量全表扫描问题2：IO负载高，cpu低load大量小IO写操作： autocommit，产生大量小IO IO/PS，磁盘固定值，硬件出厂时，每次最大IO次数第二个由制造商定义。大量大IO写操作 更容易出现SQL问题问题3：IO和cpu??负载很高硬件不够或者SQL有问题->应用-->数据库-->架构（高可用，读写分离，分库分表）处理方向，明确优化目标，性能与安全的折衷，预防措施1.5.2硬件优化host：根据数据库类型，HostCPU选择，内存容量selection,diskselection平衡内存和磁盘资源RandomI/OandsequentialI/OhostRAIDcardBBU(BatteryBackupUnit)closesCPU选择：CPU的两个关键因素：核心数，主频选择根据业务类型不同：cpu密集型：计算量多，OLTPcpu频率高，核数多IO密集型：查询比较，OLAP核数多，主频不一定高内存选择：OLAP类型数据库要求res内存比较大，这跟数据采集的量级有关。OLTP类型数据一般内存是CPU核数的2~4倍，没有最佳实践。存储方面：根据存储数据的不同类型，选择不同的存储设备，配置合理的RAID级别（raid5、raid10、热备盘）。对于操作系统，不需要太特殊的选择，最好做冗余（raid1）（ssd、sas、sata）平衡内存和磁盘资源的随机I/O和顺序I/O待关闭的主机RAID卡（BatteryBackupUnit）BBU。网络设备方面：利用流量支持更高层的网络设备（交换机、路由器、网线、网卡、HBA卡）注：在系统初始设计时应考虑以上规划。1.5.3服务器硬件优化1、物理状态灯：2、自带管理设备：远程控制卡（FENCE设备：ipmiiloidarc）、开关机、硬件监控。3、第三方监控软件和设备（snmp、agent）监控物理设施。4.存储设备：内置监控平台。EMC2（被hp收购）、日立（hds）、IBM低端代工hds，高端存储是自家技术，华为存储1.5.4系统优化Cpu：基本不用调整，在硬件选型上下功夫即可。内存：基本不用调整，在硬件选择上下功夫即可。SWAP：MySQL尽量避免使用交换。阿里云服务器默认swap为0IO：raid，无lvm，ext4或xfs，ssd，IO调度策略Swap调整（不使用swap分区），将/proc/sys/vm/swappiness的内容改为0（临时）,/在etc/sysctl.conf中加入vm.swappiness=0(***)来判断linux是倾向于使用swap还是释放文件系统缓存。在内存紧张的情况下，该值越低，越有可能释放文件系统缓存。当然这个参数只能降低使用swap的概率，并不能阻止linux使用swap。修改MySQL配置参数innodb_flush_method，开启O_DIRECT模式。这种情况下，InnoDB的bufferpool会直接绕过文件系统缓存访问磁盘，但是重做日志还是会使用文件系统缓存。值得注意的是，Redolog是覆盖模式，即使使用了文件系统的缓存，也不会占用太多的IO调度策略#echodeadline>/sys/block/sda/queue/scheduler临时修改todeadline***修改vi/boot/grub/grub.conf为如下内容：kernel/boot/vmlinuz-2.6.18-8.el5roroot=LABEL=/elevator=deadlinerhgbquiet1.5.5系统参数调整Linux系统内核参数优化vim/etc/sysctl.confnet.ipv4.ip_local_port_range=102465535#用户端口范围net.ipv4.tcp_max_syn_backlog=4096net.ipv4.tcp_fin_timeout=30fs.file-max=65535#系统***文件句柄，控制最大数量可以打开文件的用户限制参数（mysql不能设置以下配置）vim/etc/security/limits.conf*softnproc65535*hardnproc65535*softnofile65535*hardnofile655351.5.6应用优化业务应用与数据库应用无关，防火墙：iptables、selinux和其他没用的服务（关闭）：chkconfig--level23456acpidoffchkconfig--level23456anacronoffchkconfig--level23456autofsoffchkconfig--level23456avahi-daemonoffchkconfig--level23456bluetoothoffchkconfig--level23456cupsoffchkconfig--level23456firstbootoffchkconfig--level23456haldaemonoffchkconfig--level23456hplipoffchkconfig--level23456ip6tablesoffchkconfig--level23456iptablesoffchkconfig--level23456isdnoffchkconfig--level23456pcscdoffchkconfig--level23456sendmailoffchkconfig--level23456yum-updatesdoff安装一个图形界面的服务器不要启动图形界面runlevel3另外想想我们的业务以后是不是真的需要MySQL，或者使用其他类型的数据库最好的状态使用数据库就是不使用数据库。1.6数据库优化SQL优化方向：执行计划、索引、SQL重写架构优化方向：高可用架构、高性能架构、分库分表1.6.1数据库参数优化调整：整体实例（高级优化、展开）：thread_concurrency#并发线程数sort_buffer_size#排序缓存read_buffer_size#顺序读缓存read_rnd_buffer_size#随机读缓存key_buffer_size#索引缓存thread_cache_size#(1G—>8,2G—>16,3G—>32,>3G—>64)连接层（基础优化）设置合理的连接客户和连接方式max_connections#***connections，看设置max_connect_errors#***error连接的事务数，如果可以大，connect_timeout#connectiontimeoutmax_user_connections#***userconnectionsskip-name-resolve#跳过域名解析wait_timeout#等待超时back_log#可以入栈的连接数SQL层（基础优化）query_cache_size：查询缓存>>>OLAP类型数据库，需要重点增加这个内存缓存，一般不会超过GB。对于频繁修改的数据，缓存会立即失效。我们可以用内存数据库（redis、memecache）来代替他的功能。1.6.2存储引擎层（innodb基本优化参数）default-storage-engineinnodb_buffer_pool_size#没有固定大小，50%测试值，看情况再微调。但是尽量设置不要超过物理内存的70%innodb_file_per_table=(1,0)innodb_flush_log_at_trx_commit=(0,1,2)#1最安全，0性能最好，2妥协binlog_syncInnodb_flush_method=(O_DIRECT,fdatasync)innodb_log_buffer_size#100M以下innodb_log_file_size#100M以下innodb_log_files_in_group#5个成员以下，一般2-3个就够了(iblogfile0-N)innodb_max_dirty_pages_pct#闪存脏页达到75%时写入磁盘。log_binmax_binlog_cache_size#可以不设置max_binlog_size#可以不设置innodb_additional_mem_pool_size#对于2G内存以下的机器，建议值为20M。32G以上内存100M1.7参考[1]https://www.cnblogs.com/zishengY/p/6892345.html[2]https://www.jianshu.com/p/d7665192aaaf
