MySQL问题定位-性能优化之我见
前言
首先任何一个数据库不是独立存在的，也不是凭空想象决定出来的。
数据库的架构离不开应用的场景。所以，为了解决某些深入的问题，首先你得掌握数据库的原理与架构。原理掌握得越深入，越能帮助你定位复杂与隐藏的问题。
其次，DBA不能只局限于数据库本身。因为问题的来源，很多时候都来自用户表象（比如说用户反映查询某个东西很慢）。这个表象 问题，可能来自从应用到数据库，到OS，存储等方面。或者是网络链路的任一环节等。
最后，DBA常需要关注的层面，除了应用，更重要的还有OS硬件相关的层面。如内存，CPU，磁盘等。
背景知识：数据库经典三层架构图
背景知识：MySQL体系架构
图1即为MySQL的逻辑架构图，可以简单地归结为四层结构：
第一层为客户端连接层，主要是做一些连接处理、权限认证、安全连接等处理。
第二层为服务管理层，实现了诸如SQL接口、解析、优化、缓存以及备份恢复、复制等核心功能。
第三层为插件存储引擎层，这是MySQL区别于其它数据库系统如ORACLE、MSSQL SERVER最重要的一点，MySQL中数据的存储和提取最终是由存储引擎来实现的，不同的存储引擎存取数据的方式不一样，它们通过统一的API与服务层进行通信。
第四层为数据存储层，确切地说它不属于MySQL系统，只是MySQL生成的数据、日志等文件最终是要保存在磁盘文件系统中的。
背景知识：MySQL体系架构
背景知识：MySQL内核架构
MySQL定位问题关注方面
数据库层面
- MySQL Slow log （80%的问题，都来自SQL应用的问题）
- Mysql error log
- MySQL统计状态信息
- Show global status;
- Show engine innodb status\G
- Show full processlist;
- Show master status
- Show slave status
OS层面
- 内存，磁盘，IO，网络等
- free, vmstat, iostat, top, sar -n DEV, demesg, perf等
有图表的情况下，尽量多通过图表方式来查看指标变化趋势。所谓一图顶千言。
MySQL定位问题思路与方法
排除法
- 排除应用问题（查看slow log）
- 排除OS问题（查看OS各类输出，图表等）
- 排除数据库本身问题（查看数据库，状态，延时等）
搜索路径（知识爆炸的时代，谁也不可能记住所有的问题）
- 百度
- 谷歌
- Oracle Support知识库 https://support.oracle.com/portal/
慢SQL问题定位方法
- Explain
- Show Profile
- mysqldumpslow
MySQL性能优化关注点
SQL及索引优化
- 根据需求写出良好的SQL，并创建有效的索引，实现某一种需求可以多种写法，这时候我们就要选择一种效率最高的写法。这个时候就要了解sql优化
- 数据库表结构优化
- 根据数据库的范式，设计表结构，表结构设计的好直接关系到写SQL语句。
系统配置优化
- 大多数运行在Linux机器上，如tcp连接数的限制、打开文件数的限制、安全性的限制，因此我们要对这些配置进行相应的优化。
硬件配置优化
- 选择适合数据库服务的cpu，更快的IO，更高的内存。
- 但不意味着越强越好。因为我们总是在成本与收益之间权衡。配置过低，性能无法满足要求。配置太高，造成浪费。
- 我们应该在选择合理的配置，并预留部分资源以应对突发流量。
注：通过下图可以看出，该金字塔中，优化的成本从下而上逐渐增高，而优化的效果会逐渐降低。
性能优化的原则
- 优化永远不会结束（也即不需要做无畏的优化）
- 2/8理论，短板理论（改善20%的短板，提升80%的性能）
所谓性能优化，在大部分情况而言，就是找到导致性能的瓶颈所在，并加以解决。
案例分享
某部门系统从ORACLE数据库迁移到MySQL，碰到了严重的性能问题。实测TPS不足ORACLE DB的30%。
我的性能优化过程步骤：
- 前期现场查看数据表定义，配置文件等。
- 发现问题表没有使用主键，insert values一条一条插入导致速度很慢，配置文件参数不合适(innodb buffer pool, redo log, io_capacity等）等问题。
- 现场测试，性能还是有问题（表象为Mysql服务器IO，CPU负载都很小）
采用排除法
-
- 用sysbench压测没有问题，排除硬件OS问题
- 数据库查询状态正常，没有问题
- 剩下，只能是应用问题。
查看网络流量，抓取数据包，发现应用流量很小。
代码排查，oracle以前用sequence。MySQL不支持，应用代码实现生成ID。
这段代码有问题，出现锁争用，导致应用流量一直很小。