1.1前言MySQL对于很多Linux从业者来说是一个非常棘手的问题。大多数情况下，是因为对数据库问题的情况和处理没有弄清楚。在优化MySQL之前你必须了解的是MySQL的查询过程。很多查询优化工作其实就是遵循一些原则，让MySQL优化器按照预期的方式合理运行。今天就带大家看看MySQL的实战优化，助你顺利拿高薪。图-MySQL查询流程1.2优化哲学优化有风险，涉足需谨慎1.2.1优化可能出现的问题优化并不总是在简单的环境下进行，也可能是一个复杂的系统，具有已投入生产。优化方法存在很大的风险，只是你没有能力意识到和预见！任何技术都可以解决问题，但一定有带来问题的风险！对于优化来说，解决问题带来的问题必须控制在可接受的范围内才能有成果。现状或更糟是失败的！1.2.2优化要求稳定性和业务可持续性通常比性能更重要！优化必然涉及变化，变化有风险！优化使性能更好，维护和退化是同样可能发生的事件！记住要优化，应该是所有部门的共同工作，没有一个部门可以优化数据库！所以优化工作是由业务需求驱动的！！！1.2.3谁参与优化在进行数据库优化时，数据库管理员、业务部门代表、应用架构师、应用设计人员、应用开发人员、硬件和系统管理员、存储管理员等业务相关人员参与。1.3优化思路1.3.1优化什么数据库优化主要有两个方面：安全和性能。Full--->DataSustainabilityPerformance--->Highperformancedataaccess1.3.2存储、主机、操作系统方面的优化范围有哪些：主机架构稳定性I/O规划和配置Swap交换分区OS内核参数和网络问题应用方面：应用稳定性SQL语句性能串口访问资源性能差Session管理这个应用是否适合MySQL数据库优化方面：In-memory数据库结构（物理&逻辑）实例配置说明：无论在，设计系统、定位问题或者优化，都可以按照这个顺序执行。1.3.3优化维度数据库优化有四个维度：硬件、系统配置、数据库表结构、SQL和索引优化选择优化成本：硬件>系统配置>数据库表结构>SQL和索引优化效果：硬件<系统配置<数据库表结构30;显示表中的索引；3、从执行计划、索引问题（是否合理）或语句本身判断4、showstatuslike'%lock%';#查询锁状态杀死SESSION_ID；#杀死有问题的session一般调优思路：针对周期性的业务卡顿，比如每天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显示交换到磁盘的数据块数。Unuseddatablocks，userbuffer数据块，操作系统使用的数据块数Swap：操作系统每秒从磁盘交换到内存，从内存交换到磁盘的数据块数。s1和s0***都为0io：从设备b1读取每秒写入设备b0的数据块数。反映磁盘I/O系统：显示每秒中断(in)和上下文切换(cs)的次数Cpu：显示用于运行用户代码、系统代码、空闲、等待I/O的CPU时间iostat命令说明示例命令:iostat-dk15iostat-d-k-x5(检查设备使用率(%util)和响应时间(await))tps：设备每秒传输的次数。“传输”是指“I/O请求”。多个逻辑请求可以组合成“一个I/O请求”。iops：硬件出厂时，厂商定义的每秒最大IO次数，“onetransfer”请求的大小未知。kB_read/s：每秒从设备（驱动表示）读取的数据量；KB_wrtn/s：每秒写入设备（驱动表示）的数据量；kB_read：读取的数据总量；kB_wrtn：写入的数据总量；这些单位是千字节。1.4.4系统级问题的解决方案你认为负载高好还是负载低好？在实际生产中，一般认为只要CPU不超过90%就没有问题。当然也不能排除以下特殊情况：问题一：cpu负载高，IO负载低，内存不足，磁盘性能差SQL问题------>进入数据库层，进一步排查SQL问题IOproblems(diskcritical,Badraiddesign,raiddowngrade,locks,andtpsistoohighunittime)tps过高：大量小数据IO，大量全表扫描问题2：高IO负载,低CPU负载,大量小IO写操作:autocommit,产生大量小IOIO/PS,磁盘固定值,硬件出厂时定义的最大每秒IO次数由制造商。大量的大IO写操作出现SQL问题的几率比较高。问题三：IO和CPU负载高。硬件不够或者SQL有问题。-->数据库-->架构（高可用、读写分离、分库分表）处理方向明确优化目标，在性能和安全之间折衷，防患于未然1.5.2硬件优化主机：根据数据库类型、主机CPU选择、内存容量选择、磁盘选择平衡内存和磁盘资源随机I/O和顺序I/O主机RAID卡的BBU（BatteryBackupUnit）关闭cpu选择：两个键cpu的因素：核心数和主频根据不同的业务类型选择：cpu密集型：计算量大，OLTPCPU频率高，核心数多IO密集型：查询比较，OLAP核心数多，内存多a不一定高频选择：OLAP类型的数据库需要较多的内存，这与数据采集的量级有关。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添加vm。swappiness=0(***)到/sysctl.conf来确定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等无用服务s(关闭)：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#***连接数，看事务数设置max_connect_errors#***错误连接数，如果可以大的话connect_timeout#connectiontimeoutmax_user_connections#***用户连接数skip-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内存和100M以上为什么总有人比你好，那是因为他自己很好，一直在努力变得更好，而你是否还安于现状，心里暗自高兴呢！关注我，私信回复我“666”或“Java架构”获取免费Java架构学习资料（含高可用、高并发、高性能与分布式、Jvm性能调优、Spring源码、MyBatis、Netty、Redis、Kafka,Mysql,Zookeeper,Tomcat,Docker,Dubbo,Nginx等知识点架构资料）合理利用每一分每一秒的时间去学习提升自己，不要用“没时间”来掩盖自己的思想懒惰！趁着年轻，好好努力，给未来的自己一个交代！
