MySQL优化
参考：http://blog.chinaunix.net/uid-11640640-id-3426908.html
MySQL优化：
慢查询分析mysqldumpslow
我们可以通过打开log文件查看得知哪些SQL执行效率低下
[root@localhost mysql]# more slow-query.log
# Time: 081026 19:46:34
# User@Host: root[root] @ localhost []
# Query_time: 11 Lock_time: 0 Rows_sent: 1 Rows_examined: 6552961
select count(*) from t_user;
从日志中，可以发现查询时间超过5 秒的SQL，而小于5秒的没有出现在此日志中。
如果慢查询日志中记录内容很多，可以使用mysqldumpslow工具（MySQL客户端安装自带）来对慢查询日志进行分类汇总。mysqldumpslow对日志文件进行了分类汇总，显示汇总后摘要结果。
进入log的存放目录，运行
[root@mysql_data]#mysqldumpslow slow-query.log
Reading mysql slow query log from slow-query.log
Count: 2 Time=11.00s (22s) Lock=0.00s (0s) Rows=1.0 (2), root[root]@mysql
select count(N) from t_user;
mysqldumpslow命令
/path/mysqldumpslow -s c -t 10 /database/mysql/slow-query.log
这会输出记录次数最多的10条SQL语句，其中：
-s, 是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙；
-t, 是top n的意思，即为返回前面多少条的数据；
-g, 后边可以写一个正则匹配模式，大小写不敏感的；
例如：
/path/mysqldumpslow -s r -t 10 /database/mysql/slow-log
得到返回记录集最多的10个查询。
/path/mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow-log
得到按照时间排序的前10条里面含有左连接的查询语句。
使用mysqldumpslow命令可以非常明确的得到各种我们需要的查询语句，对MySQL查询语句的监控、分析、优化是MySQL优化非常重要的一步。开启慢查询日志后，由于日志记录操作，在一定程度上会占用CPU资源影响mysql的性能，但是可以阶段性开启来定位性能瓶颈。
explain分析查询使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。通过explain命令可以得到:
– 表的读取顺序
– 数据读取操作的操作类型
– 哪些索引可以使用
– 哪些索引被实际使用
– 表之间的引用
– 每张表有多少行被优化器查询
确认需求，先明确当前的运行状态，是否真的需要进行优化，别没事找事；
常见瓶颈：
绝大多数瓶颈在于I/O子系统；
若CPU很高，90%以上是因为索引不当；
发生swap时，可能因为内存分配太小或过大；
iowait太高时，想办法从索引角度入手优化，以及提高I/O设备性能，增加内存，减少排序，减少SELECT一次性读取数据量。
常用优化策略
瞬间并发很高，采用thread pool；
频繁order by\group by，索引入手；
适当调整内存，不要太大或太小。一般ibp设置为50% ~ 70%为宜；
iowait高，加内存，提高iops，减少数据读写。
制定方案时，重点解决发生频率高的问题（量变更容易引起质变）；回顾反馈，整理文档，回顾总结，从零散资料中总结出规律，预防风险再次出现。
一般采用下面几个瓶颈分析工具：
绝大多数情况下，有经验的工程师靠sysstat工具集中的就足够了，很多问题一看现象大概就能知道瓶颈何在。
在MySQL层面，有哪些确认瓶颈的手段呢？
硬件、系统优化
我们继续MySQL优化之旅。先来看看从硬件以及OS层面，都有哪些可以优化的。首先主要是BIOS中关于CPU和内存的参数调整，其次是RAID方面的优化。
再来看看几个参考配置图：
1、CPU选择最大性能模式，避免节能模式导致性能不足。
2、关闭NUMA，降低swap概率。
3、采用RAID-10，并且选择FORCE WB。
在OS层面，可以有几个优化手段：
调整IO Scheduler
使用XFS
调整其他内核选项备
备注：
vm.swappiness，降低发生swap的几率；
vm.dirty_background_ratio & vm.dirty_ratio，避免瞬间大量I/O请求导致系统卡死。
从这个压测结果可以看到noop/deadline有明显优势。
这个io scheduler还可以在线修改的哦，还等神马？
echo deadline > /sys/block/sdc/queue/scheduler
在用PCIe SSD设备做测试时，XFS的IOPS能跑到ext4的4倍，表现非常好。
还有什么理由不用XFS呢？
xfs挂载参数：
/dev/sdc1 /data xfs defaults,noatime,nodiratime,nobarrier 0 0
格式化参数不用特别指定，默认的即可。
MySQL配置优化
前面讲到，给MySQL分配的内存不要太大或太小，那么多少合适呢。
首先，要搞清楚MySQL的内存都由哪些部分组成：
global buffers和oracle的SGA一个意思，就是全局一次分配，多个线程间共享。
thread buffers和oracle的PGA一个意思，每个线程单独分配，线程间不能相互共享，因此不要分配过大，避免内存不够使用，发生OOM。
原则： 对这些选项调整时，不要照猫画虎随便调整，要先做到心里有数，了解其具体作用才动手。
看看innodb_flush_log_at_trx_commit分别为0、1、2的性能对比如：
如果再启用binlog后的对比：
最后，再加上sync_binlog选项不同设置的对比：
备注： 这3个测试结果图均来自Percona。
结论&建议：
想要保证数据安全，就设置 trx_commit =1 & sync_binlog = 1
在slave上或非关键场景，可以都改成0
SCHEMA设计优化
接下来看看MySQL的模式（SCHEMA）设计优化要点：
要点：
默认地，使用InnoDB引擎，别上MyISAM给自己找事；
InnoDB必须要有自增（或类似自增）属性的主键；
不使用或少使用TEXT/BLOB列；
NOT NULL主要是为了优化索引效率；
若无特殊需求，均可使用latin1字符集，否则用utf8\utf8mb4等大字符集保证通用性。
其他要点：
SQL优化
SQL优化层面有几个要点：
以及 COUNT(*)、大分页 的优化要点：
接下来，我们来看看EXPLAIN的结果中，有哪些关键信息要注意的。首先看下EXPLAIN结果的type列，都可以给我们什么信息：
再看看Extra列有哪些状态要引起重视：
MySQL的慢日志可用下面的工具来进行解析和管理：
pt-query-digest + Box Anemometer的案例，可以对slow log进行便捷管理。
关于JOIN优化有下面的几个关键点：
接下来看看哪些情况下，无法有效使用索引的：
再看看几个杀手级SQL的案例及其优化建议：
在平时，我们登入MySQL服务器后，如果觉得有问题，可以重点关注下面的一些线程状态：
其他优化
关于DBA的利器，常用percona-toolkit工具简介：
附：关于MariaDB及Percona分支版本的简介
Q&A
Q1: 多实例，进程会不会抢占？每个事例都是单独起的。
A：除了OS层面的资源会相互影响外，其他的不会。比如某个实例消耗特别多cpu资源的话，那么其他实例也会跟着受影响，这是必然的，除非用虚拟化等方式做隔离。
Q2: SSD建议单盘还是Raid？
A：如果不担心丢数据，单盘呗。如果怕丢的话，那显然不能单盘了。随机io很高的话，Raid5就不合适了。不过除非采用SSD，用Raid5也不怕了。事实上，Raid卡反而会影响（降低）SSD性能的发挥，但为了数据可靠性，没办法，还好影响不算特别大。
Q3: 能介绍一下哪些业务场景适合哪种RAID吗？
A：1、高随机IO，用Raid10；2、需要大容量，用Raid5。基本就这两种方案，事实上，因为SSD的IOPS性能已经很不错了，很多企业会选择直接用3块盘构建Raid5。毋庸置疑，上了PCIE SSD，可以避免很多问题，或者DBA可以少干很多活，至少可以缓解。
Q4: nnodb_buffer_pool_instances应该如何设置？
A：ibp的instance一般不超过8为宜，超过8的话，可能有反作用，不过多个instance的前提是，平均到每个instance的ibp不能小于2G，否则也没啥意义。
Q5: No text,or in compressed是指如果使用text的话，建议压缩吗？在压缩数据方面，叶老师有什么经验吗？
A：对的，建议不要在InnoDB中存储大量文本。需要的话，事先压缩好再存进去。不需要检索的文本，可以统统压缩后存进去，不是用InnoDB的压缩格式哦，是事先外部压缩后存储，文本内容在存储进去前先压缩好，不是用InnoDB的compressed这种row format，那会被坑惨的，性能损失9层，只有一半压缩比，还不如用TokuDB算了。
Q6: MariaDB和MySQL的优缺点，以及大神怎么看Maria有否取代MySQL的趋势？
A：想要取代还早呢，没那么容易，而且也没必要取代，作为补充就ok。除非哪天MySQL官方版本闭源了，或者支持很差。
Q7: 新的业务系统，是建议继续用MySQL5.5或以上，还是用mariaDB？
A：建议优先Percona 5.6，其次是MySQL 5.6，最末才是MariaDB。
Q8: 你们的数据库备份是用Percona的工具进行吗？每周一全备，每天一增量？用这些工具备份，会不会出现恢复不了的情况？这个有没有办法验证备份是否“正常” ？
A：工具则以xtrabackup为主，mysqldump为辅，数量不是巨大的话，每天一全备，大多有slave做热备，所以就没定期增备了。Mydumper也有些不太爽的，也比较小众就是，备份文件一定要做恢复性测试，千万别只备份不恢复测试，关键时刻会死人的。
Q9:恢复性测试怎么做 有流程方案指导一下吗？
A：简单的：数据恢复，简单查询验证数量，关键数据什么的；复杂的：搭测试环境呗。
Q10: 有没有什么效率较高的验证备份有效性的工具或者方法？还是只好把库恢复出来核对？
A：mysqldump或mydumper备份的文件，可以用grep简单快速验证；xtrabackup的话，只能看文件大小，或者做全量恢复了。
