《高效mysql的N个习惯》
一.用好硬件
1.用更好的cpu
主频高，让每个sql处理时间更快，减少等待
cache大，每次cpu计算速率更快
线程多，同时支持更多并发sql，提高tps
同时记得关闭numa并设置为最大性能模式
2.用更好的内存
主频高，内存读写速度效率更高，更高吞吐，更低时延
内存大，更多数据在内存中，减少直接磁盘读写，提高tps
3.用更好的磁盘
通常来说，磁盘I/O是最大的瓶颈
如果是机械盘，一定要配阵列卡，以及阵列卡的cache&bbu,并且使用wb策略
最好是选用ssd或者pcle ssc，iops可以提升成千上万倍
4.用更好的网络/网卡
文件传输速率高，异地文件备份更快
主从数据复制传输时延更小
适合大数据量的分布式存储环境
老版本内核中，网络请求太高时会引发中断瓶颈，建议升级内核
多个网卡可以绑定，提高传输速率并能提高可用性
二.让os跑得快
1.关闭无用服务
减少系统开销
避免安全隐患
2.尽可能使用本地高速存储
坚决不用nfs
除非是基于ssd的高速网络分布式存储
用于备份场景除外
3.让数据库跑在专用的服务器上，不混搭
性能不相互影响
提高安全性
必须混搭时要做好权限管理和安全隔离
4.io scheduler
选择deadline/noop，坚决不用cfq
5.文件选择系统
优先选用xfs/ext4
zfs/btrfs比较小众
坚决不用ext3
6.其他内核选项
vm.swappiness<=10
降低使用swap的概率
内核2.6.32-303及以上版本，慎重设置为0，可能引发oom
vm.dirty_ratio<=5
vm.dirty_background_ratio<=10
避免因为io压力瞬间飙升导致内核进程卡死，os hung住
三.ddl、sql写的好
1.一定要有主键(primary key)
没有主键会：数据多次读写后可能更离散，有更多随机I/O；mysql复制环境中，如果选择rbr模式，没有主见的update需要读全表，导致复制延迟
好的主键特点：没有业务用途；数值呈连续增长，最好是自增；坚决不能用char/uuid等类型
关于数据长度：够用前提下，越短越好；消耗更少的存储空；需要进行排序时，消耗更少内存空间；
2.适当使用text/blob类型
data page默认16kb
每行长度超过8kb时，就要分裂data page
产生更过离散I/O
3.每个表增加create_time/update_time两个字段
分别表示写入时间及最后更新时间
业务上可能用不到，但是对日常运营管理非常有用
可以用来判断哪些事可以归档的老数据，定期进行归档
用来做自定义的差异备份也很方便
4.索引很重要
innodb的行锁是基于索引实现的，如果没有则：读取时，全表扫描；修改时，全表记录锁；
索引设计：基数低的子弹没必要建立单列索引；字符型字段上建立索引时优先采用部分索引(prefix index);5.6.9后，optimizer能识别普通索引同时存储主键，无需显示定义加上主键列；优先多列联合索引，少用单列索引
5.怎么算好sql
所有where条件都加上引号：避免潜在的类型隐式转换风险；避免个别条件失效时sql语法错误
不select * ：减少不必要的I/O;提高可以利用覆盖索引的几率
避免sql注入风险：用户输入都要做过滤；利用prepare做预处理
利用sql_mode做限制；like查询时，不用%通配符最左前导(无法使用索引)；能union all就不要union(union需要去重，会产生临时表)；sql中最好不要运算；where字句中，不要有函数
6.关于join
满足业务需求前提下，优先用inner join,让优化器自动选择驱动表
有时候优化器选择的驱动表未必是最优的，可以尝试手动调整
最后的排序字段如果不在驱动表中，会有filesort
7.糟糕的sql
update中的set多个字段用,连接
不用select *
先其他再like
引号
8.关于explain
关键业务sql上线前,都要explain确认其执行计划
或提前分析slow query log,防患未然
explain中如果有using temporary/using filesort/type=all时，尽量想办法优化
四.运维习惯好
1.存储引擎
innodb为主
适当场景可用tokuDB
误区：memory不见得就快
2.关闭query cache
绝大多数情况下，最好关闭
QC锁是全局锁，每次更新QC的内存块锁代价高，出现query cache lock状态的频率很高
实例启动前设置query_cache_type=0&query_cache_size=0
3.使用独立undo表空间
避免ibdata1文件存储空间暴涨
mysql5.6开始支持独立表空间
mysql5.7还可以回收已经purge的表空间
提高file i/o能力，并适当增加purge线程数innodb_purge_threads
事务及时提交，不要积压，默认打开autocommit=1
4.启用thread pool
应对突发短连接
extra port
没thread pool怎么办？想办法启用连接池或其他代替方案；适当调低超时阈值，减少空闲链接
5.几个关键选项
innodb_buffer_pool_size:物理内存的50%-70%
innodb_log_file_size:5.5以上2G+,5.5以下不超512M
innodb_flush_log_at_trx_commit:0=》最快，数据不安全；1=》最慢最安全；2=》折中
innodb_max_dirty_pages_pct:25%-50%为宜
max_connections:突发最大连接数的80%为宜，过大容易导致全部卡死
五.其他好习惯
1.启用辅助监控机制
干掉超过Ns的sql
干掉疑似注入sql
干掉长时间不活跃的sleep连接
2.online ddl
优先用pt-osc但不是一定要用
5.6以后对online ddl有很大的提升改善
3.删除大表
不要真的删除，而是先rename
确认对业务真的没有影响
再用硬连接的方法无力删除，效率更高
4.autocommit
避免某些行锁被长时间持有，影响tps
更严重时，可能连接数暴涨，导致整个实例挂掉
改用gui客户端连接时，记得及时关闭连接，或设置超时阈值以及自动提交，否则容易发生行锁等待问题
