高效mysql的n个习惯


叶金荣 - 知数堂培训联合创始人 2016.10.15 高效MySQL的N个习惯 MySQL如何保持高效运行? N个好习惯 • 用好硬件 • 让OS跑得快 • DDL、SQL写的好 • 运维习惯好 • 其他好习惯 时延 用更好的硬件 • 用更好的CPU – 主频高,让每个SQL处理时间更快,减少等待 – (L1/L2/L3)cache大,每次CPU计算速率更快 – 线程多,同时支持更多并发SQL,提高TPS同时 – 记得关闭NUMA并设置为最大性能模式 • MySQL 5.6.27后,增加innodb_numa_interleave选项 用更好的硬件 • 用更好的内存 – 主频高,内存读写速率更高,更高吞吐,更低时延 – 内存大,更多数据在内存中,减少直接磁盘读写,提高TPS 用更好的硬件 • 用更好的磁盘 – 通常来说,磁盘I/O是最大的瓶颈 – 如果是机械盘,一定要配阵列卡,以及阵列卡的CACHE & BBU • 并且使用(FORCE)WB策略 – 最好是选用SSD或者PCIe SSD,iops可以提升成千上万倍 用更好的硬件 • 用更好的网卡/网络 – 文件传输速率高,异地文件备份更快 – 主从数据复制数据传输时延更小 – 适合大数据量的分布式存储环境 – 老版本内核中,网络请求太高时会引发中断瓶颈,建议升级内核 – 多个网卡可以进行绑定,提高传输速率并能提高可用性 让OS跑的更快 • 关闭无用服务 – 减少系统开销 – 避免潜在安全隐患 让OS跑的更快 • 尽可能使用本地高速存储 – 坚决不使用nfs – 除非是基于SSD的高速网络分布式存储 – 用于备份场景除外 让OS跑的更快 • 让数据库跑在专用的服务器上,不混搭 – 性能上不相互影响 – 提高安全性 – 必须混撘时要做好权限管理以及安全隔离 让OS跑的更快 • io scheduler – 选择deadline、noop,坚决不能用cfq 让OS跑的更快 • 文件系统选择 – 优先选用xfs 或 ext4(rhel 7及以上,xfs已是默认fs) – zfs/btrfs比较小众 – 坚决不用ext3 灰 色 为 遮 挡 区 域 , 排 版 请 注 意 。 让OS跑的更快 • 其他内核选项 – vm.swappiness ≤ 10 – 降低使用swap的概率 – 内核2.6.32-303及以上版本,慎重设置为0,可能引发OOM 让OS跑的更快 • 其他内核选项 – vm.dirty_ratio ≤ 5 – vm.dirty_background_ratio ≤ 10 – 避免因为io压力瞬间飙升导致内核进程卡死,os hung住 INFO:task jbd/dm-0-8:389 blocked for more than 120 seconds. "echo 0 > /proc/sys/kernel/hung_task_timeout_secs" disable this message. INFO:task flush-253:0:1324 blocked for more than 120 seconds. echo 0 > /proc/sys/kernel/hung_task_timeout_secs" disable this message. DDL、SQL写得好 • 一定要有主键(PRIMARY KEY) • 没有主键会怎样 – 数据多次读写后可能更离散,有更多随机I/O – MySQL复制环境中,如果选择RBR模式,没有主键的update需要读 全表,导致复制延迟 DDL、SQL写得好 • 一定要有主键(PRIMARY KEY) • 好的主键特点 – 没有业务用途 – 数值呈连续增长,最好是自增 – 坚决不能选用CHAR/UUID等类型 DDL、SQL写得好 • 关于数据长度 – 够用前提下,越短越好 – 消耗更少的存储空间 – 需要进行排序时,消耗更少的内存空间 – 例如用INT UNSIGNED存储IPV4地址,不用CHAR(15)类型 – 案例:11个字符长度的数值,bigint vs char(120) vs char(11),1万条 记录,Logical_read:111 vs 1170 vs 224 DDL、SQL写得好 • 适当使用TEXT/BLOB类型 – data page默认16KB – 每行长度超过8KB时,就需要分裂data page – 产生更多离散I/O – 案例:一个100G的表拆分成4个表后,总大小仅25G DDL、SQL写得好 • 每个表增加create_time、update_time两个字段 – 分别表示写入时间以及最后更新时间 – 业务上可能用不到,但是对日常运维管理则非常有用 – 可以用来判断哪些是可以归档的老数据,定期进行归档 – 用来做自定义的差异备份也很方便 DDL、SQL写得好 • 索引很重要 – InnoDB行锁是基于索引实现 – 如果没有索引,将会是灾难性的 • 读取时,全表扫描 • 修改时,全表记录锁 DDL、SQL写得好 • 索引设计 – 基数(cardinality)低的字段一般没必要建立单列索引 – 字符型字段上建索引时优先采用部分索引(prefix index) – 5.6.9之后,optimizer能识别到普通索引同时存储主键值,无需显 式定义加上主键列(Index Extensions) – 优先多列联合索引(multi column index),少用单列索引 DDL、SQL写得好 • 怎么算是好SQL – 所有WHERE条件都加上引号 – 避免潜在的类型隐式转换风险 – 避免个别条件失效时SQL语法错误 DDL、SQL写得好 • 怎么算是好SQL – 不SELECT * – 减少不必要的I/O – 提高可以利用覆盖索引的几率 DDL、SQL写得好 • 怎么算是好SQL – 避免SQL注入风险 – 所有用户输入值都要做过滤 – 利用PREPARE做预处理 – 利用SQL_MODE做限制 DDL、SQL写得好 • 怎么算是好SQL – LIKE查询时,不要用%通配符最左前导(无法使用索引) – 能UNION ALL就不要UNION(UNION需要去重,会产生临时表) – SQL中最好不要有运算 – WHERE子句中,不要有函数 DDL、SQL写得好 • 关于JOIN – 满足业务需求前提下尽量用inner join,让优化器自动选择驱动表 – 有时候优化器选择的驱动表未必是最优的,可以尝试手动调整 – 最后的排序字段如果不在驱动表中,则会有filesort DDL、SQL写得好 糟糕的SQL UPDATE t SET c2 = ? AND c3 = ? WHERE c1 = ‘?’ => UPDATE t SET c2 = ? , c3 = ? WHERE c1 = ‘?’ DDL、SQL写得好 糟糕的SQL UPDATE t SET c2 = ? WHERE c1 = ‘?’ => UPDATE t SET c2 = ? WHERE c1 = ‘?’ DDL、SQL写得好 糟糕的SQL SELECT * FROM t WHERE c1 = ‘?’ => SELECT c2,c3 FROM t WHERE c1 = ‘?’ DDL、SQL写得好 糟糕的SQL SELECT c2,c3 FROM t WHERE c4 like ‘%???%’ => SELECT c2,c3 FROM t WHERE c1 >= ? AND c1 <= ? AND c4 like ‘%???%’ 最好是采用第三方解决方案 DDL、SQL写得好 糟糕的SQL SELECT c2,c3 FROM t WHERE date(c1) = ‘2016-10-15’ => SELECT c2,c3 FROM t WHERE c1 >= ‘2016-10-15’ AND c1 < ‘2016- 10-16’ DDL、SQL写得好 糟糕的SQL SELECT c2,c3 FROM t WHERE char_col = int_value => SELECT c2,c3 FROM t WHERE char_col = ’int_value’ DDL、SQL写得好 • 关于EXPLAIN – 关键业务SQL上线前,都要EXPLAIN确认其执行计划 – 或提前分析slow query log,防患未然 – EXPLAIN中如果有Using temporary、Using filesort、或type=ALL时, 尽量想办法进行优化 运维习惯好 • 存储引擎 – 抛弃MyISAM,InnoDB为王 – 适当的场景下可以采用TokuDB – 误区:MEMORY可不见得就快 运维习惯好 • 关闭QUERY CACHE – 绝大多数情况下鸡肋,最好关闭 – QC锁是全局锁,每次更新QC的内存块锁代价高,出现Waiting for query cache lock状态的频率很高 – 实例启动前设置query_cache_type = 0 & query_cache_size =0 – 参考:http://t.cn/RAF4d7z http://t.cn/RAF4d7Z 运维习惯好 • 使用独立undo表空间 – 避免ibdata1文件存储空间暴涨 – MySQL 5.6开始支持独立表空间 – MySQL 5.7还可以回收已经purge的表空间 – 提高file i/o能力,并适当增加purge线程数 innodb_purge_threads – 事务及时提交,不要积压。并且默认打开autocommit = 1 运维习惯好 • 启用thread pool – 应对突发短连接 – extra port • 没thread pool怎么办 – 想办法启用连接池或其他替代方案 – 适当调低超时阈值,减少空闲连接 运维习惯好 • 几个关键选项 – 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%为宜,过大容易导致全 部卡死 其他好习惯 • 启用辅助监控机制 – 干掉超过N秒的SQL – 干掉疑似注入SQL – 干掉长时间不活跃的sleep连接 其他好习惯 • online ddl – 优先用pt-osc – 但不见得一定要用pt-osc – 尤其是5.6以后对online ddl有了很大提升改善 其他好习惯 • 删除大表 – 不要真的删除,而是先rename – 确认对业务真的没有影响 – 再用硬连接的方法物理删除,效率更高 其他好习惯 • autocommit – 避免某些行锁被长时间持有,影响tps – 更严重时,可能连接数暴涨,导致整个实例挂掉 – 采用gui客户端连接时,记得及时关闭连接,或设置超时阈值以及 自动提交,否则容易发生行锁等待问题
还剩46页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 10 金币 [ 分享pdf获得金币 ] 0 人已下载

下载pdf

pdf贡献者

canfeit

贡献于2017-06-07

下载需要 10 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf