MySQL对于很多Linux从业者来说是一个非常棘手的问题。大多数情况下，是因为对数据库问题的情况和处理缺乏清晰的思考。在优化MySQL之前你必须了解的是MySQL的查询过程。很多查询优化工作其实就是遵循一些原则，让MySQL优化器按照预期的方式合理运行。MySQL查询过程优化哲学注意：优化有风险，修改需谨慎。优化可能出现的问题：优化并不总是在纯净的环境下进行，也可能是一个复杂的生产系统。优化方法本身就存在很大的风险，只是你无法意识到和预见。任何技术都可以解决问题，但必然存在带来问题的风险。对于优化来说，解决问题带来的问题一定要控制在可接受的范围内才能有成果。维持现状或更糟是失败的。优化要求：稳定性和业务可持续性通常比性能更重要。优化必然涉及变化，变化有风险。优化使性能更好，维护和退化是同样可能发生的事件。记住优化，应该是所有部门的共同工作，没有一个部门可以优化数据库。因此，优化工作是由业务需求驱动的！谁参与优化？在进行数据库优化时，数据库管理员、业务部门代表、应用架构师、应用设计人员、应用开发人员、硬件和系统管理员、存储管理员等，以及业务相关人员共同参与。优化思路优化什么数据库优化主要有两个方面：安全：数据的可持续性。性能：对数据的高性能访问。优化范围有哪些存储、主机和操作系统方面：主机架构稳定性I/O规划和配置Swap交换分区OS内核参数和网络问题应用方面：应用稳定性SQL语句性能串行访问资源性能差是应用适合MySQL数据库优化的session管理？In-memorydatabasestructure(physical&logical)实例配置说明：无论是设计系统、定位问题还是优化，都可以按照这个顺序执行。优化维度数据库优化维度有以下四个：硬件系统配置数据库表结构SQL和索引优化选择：优化成本：硬件>系统配置>数据库表结构>SQL和索引。优化效果：硬件<系统配置<数据库表结构优化工具数据库层面排查问题常用的12个工具有哪些：MySQLmysqladmin：MySQL客户端，可进行管理操作mysqlshow：强大的查看shell命令SHOW[SESSION|GLOBAL]变量：查看数据库参数信息SHOW[SESSION|GLOBAL]STATUS：查看数据库状态信息information_schema：获取元数据的方法SHOWENGINEINNODBSTATUS：Innodb引擎的所有状态SHOWPROCESSLIST：查看当前所有连接的session状态explain：获取query语句的执行计划showindex：查看索引slow-log表的信息：记录慢查询语句mysqldumpslow：分析slowlog文件的工具不常用但7个有用的工具：Zabbix：监控主机、系统、数据库（部署Zabbix监控平台）pt-query-digest:分析慢日志MySQLslap:分析慢日志sysbench:压力测试工具MySQLprofiling:统计数据库整体状态工具PerformanceSchema:MySQL性能状态统计dataworkbench:管理、备份、监控、分析、优化工具（比较成本资源）参考一下Zabbix：http://www.cnblogs.com/clsn/p/7885990.html数据库级问题解决思路通用应急调优思路：针对突发总线业务卡顿，无法正常操作业务处理，需要立即解决的场景。showprocesslistexplainselectid,namefromstuwherename='clsn';#ALLidnameagesexselectid,namefromstuwhereid=2-1函数结果集>30; showindexfromtable;从执行计划、索引问题（是否合理）或语句本身判断比如每天10-11点业务很慢，但是还是可以用的，之后这个时间就好了。查看slowlog，分析slowlog，分析慢查询语句；按照一定的优先级逐一检查所有慢语句；分析topSQL，进行explain调试，查看语句执行时间；调整索引或语句本身。系统级别的CPU：vmstat、sartop、htop、nmon、mpstat。内存：免费，ps-aux。IO设备（磁盘、网络）：iostat、ss、netstat、iptraf、iftop、lsof。vmstat命令说明：procs:r显示有多少进程在等待CPU时间。b显示处于不可中断睡眠状态的进程数。等待I/O。内存：swpd显示交换到磁盘的数据块数。未使用的数据块、用户缓冲区数据块和操作系统使用的数据块的数量。Swap：操作系统每秒从磁盘交换到内存和从内存交换到磁盘的数据块数。s1和s0绝对为0。IO：每秒从设备b1读取的写入设备b0的数据块数。反映磁盘I/O。系统：显示每秒中断(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：写入的数据总量；这些单位是千字节。系统级问题的解决方案你认为负载高好还是负载低好？在实际生产中，一般认为只要CPU不超过90%就没有问题。当然，也不能排除以下特殊情况。CPU负载高，IO负载低：内存不足，磁盘性能差SQL问题：到数据库层进一步排查SQL问题IO问题（磁盘critical，raid设计不好，raid降级，锁，TPS超过单位时间High)TPS过高：大量小数据IO，大量全表扫描IO负载高，CPU负载低：大量小IO写操作autocommit，导致大量小IO；IO/PS，磁盘的一个固定值，硬件出厂时，厂商定义的每秒最大IO数。大量大IO写操作：SQL出问题的概率比较高。IO和CPU负载高：硬件不够或者SQL有问题。读写分离，分库分表）。处理方向：明确优化目标，在性能和安全之间妥协，防患于未然。硬件优化①主机依赖于数据库类型、主机CPU选择、内存容量选择、磁盘选择：平衡内存和磁盘资源随机I/O和顺序I/O主机RAID卡BBU（BatteryBackupUnit）关闭②CPU选择CPU的两个关键因素：核心数和主频。根据不同业务类型选择：CPU密集型：计算量大，OLTPCPU频率高，核数多。IO密集型：与查询相比，OLAP核数更多，主频不一定高。③内存选择OLAP类型的数据库需要较多的内存，这与数据采集的量级有关。对于OLTP类型的数据，一般内存是CPU核数的2~4倍，没有最佳实践。④存储方面，根据存储数据类型的不同，选择不同的存储设备，配置合理的RAID级别（raid5、raid10、热备盘）。对于操作系统，不需要太特殊的选择，最好做冗余（raid1）（ssd、sas、sata）。主机raid卡选择：实现操作系统磁盘冗余（raid1）平衡内存和磁盘资源随机I/O和顺序I/O主机raid卡的BBU（BatteryBackupUnit）要关闭⑤对网络设备的流量支持更高的网络设备（交换机、路由器、网线、网卡、HBA卡）。注：以上方案应在系统初始设计时考虑。服务器硬件优化服务器硬件优化要点：物理状态灯、内置管理设备：远程控制卡（FENCE设备：ipmiiloidarc）、开关机、硬件监控。第三方监控软件和设备（snmp、agent）监控物理设施。存储设备：内置监控平台。EMC2（被惠普收购）、日立（HDS）、IBM低端代工HDS，高端存储是自家技术，华为存储。系统优化CPU：基本不用调整，在硬件选择上下功夫即可。内存：基本不用调整，在硬件选择上下功夫即可。SWAP：MySQL尽量避免使用Swap。阿里云服务器默认swap值为0。IO：raid，无lvm，ext4或xfs，ssd，IO调度策略。Swap调整（不使用swap分区）：将/proc/sys/vm/swappiness的内容改为0（临时），在/etc/sysctl中增加参数vm.swappiness=0(***)。conf来判断Linux是优先使用Swap，还是优先释放文件系统Cache。在内存紧张的情况下，该值越低，越有可能释放文件系统缓存。当然，这个参数只能降低使用Swap的概率，并不能阻止Linux使用Swap。修改mysql配置参数innodb_flush_方法开启O_DIRECT模式。这种情况下，InnoDB的bufferpool会直接绕过文件系统缓存访问磁盘，但是重做日志还是会使用文件系统缓存。值得注意的是，Redolog是覆盖模式，即使使用了文件系统的缓存，也不会占用太多。IO调度策略：#echodeadline>/sys/block/sda/queue/scheduler暂时改为deadline***修改：vi/boot/grub/grub.conf改为如下：kernel/boot/vmlinuz-2.6.18-8.el5roroot=LABEL=/elevator=deadlinerhgbquiet系统参数调整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*hardnofile65535应用优化业务应用和数据库应用是独立的。防火墙：iptables、selinux等其他无用服务(关闭)：chkconfig--level23456acpidoffchkconfig--level23456anacronoffchkconfig--level23456autofsoffchkconfig--level23456avahi-daemonoffchkconfig--level23456bluetoothoffchkconfig--level23456cupsoffchkconfig--level23456firstbootoffchkconfig--level23456haldaemonoffchkconfig--level23456hplipoffchkconfig--level23456ip6tablesoffchkconfig--level23456iptablesoffchkconfig--level23456isdnoffchkconfig--level23456pcscdoffchkconfig--level23456sendmailoffchkconfig--level23456yum-updatesdoff安装图形界面的服务器不启动图形界面runlevel3。另外想想我们的业务以后是不是真的需要MySQL，或者使用其他种类的数据库。使用数据库的最终状态是不使用数据库。数据库优化SQL优化方向：执行计划索引SQL重写架构优化方向：高可用架构高性能架构分库分表数据库参数优化①调整整体实例（高级优化、扩展）：thread_concurrency：#并发数threadssort_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#可以被i的连接数nthestack③SQL层（基础优化）query_cache_size：querycache>>>OLAP类型的数据库，需要重点增加这个内存缓存，但是一般不会超过GB。对于频繁修改的数据，缓存会立即失效。我们可以使用内存数据库（redis、memecache）来代替它的功能。存储引擎层优化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参考文章：https://www.cnblogs.com/zishengY/p/6892345.htmlhttps://www.jianshu.com/p/d7665192aaaf
