MySQL的性能调优

优化目的与目标：

1.优化的目的：

• 提高资源利用率
• 避免短板效应
• 提高系统吞吐量
• 同时满足更多用户在线需求

2.优化的目标：

• 减少磁盘IO：全表扫描，磁盘临时表，日志和数据块fsync
• 减少网络带宽：返回太多数据，交互次数过多
• 降低CPU消耗：排序分组（order by，group by），聚合函数（max，min，sum），逻辑读

需要关注的性能指标：

1.QPS/TPS：

• TPS：平均每秒的事务数
• QPS：平均每秒SQL语句的执行数
• QPS（TPS）= 并发请求数/平均响应时间
• QPS/TPS反映了一个系统的吞吐量
• QPS/TPS低的几种可能：并发量不够，平均响应时间长（慢SQL导致资源争用），某个资源出现性能瓶颈。

2.InnoDB逻辑读：

逻辑读指从内存中读取block的数量，一次逻辑读即读取一个内存块，指标单位为blocks。逻辑读体现了数据库整体的SQL执行性能。
Logical Reads Per Query=InnoDB Logical Reads/QPS
逻辑读过高意味着读取了过量的非需要的block。

3.临时表：

MySQL用于存储中间结果集的表，查询完成后自动删除。当临时表被大量创建时，往往意味着复杂SQL过多。内存临时表使用MEMORY存储引擎，硬盘临时表使用MyISAM存储引擎。
大多数情况，参数tmp_table_size决定MySQL创建临时表的行为，应该尽量避免mysql创建临时表的行为，尤其是磁盘临时表。
创建临时表的场景：
union查询，子查询，semi-join（IN,exists），高代价的order by/group by
创建磁盘临时表的场景：
表包含TEXT或者BLOB列；GROUP BY或者DISTINCT子句中包含长度大于512字节的列；使用UNION或UNION ALL时，SELECT 子句中包含大于512字节的列。

4.CPU使用率：

• CPU是如何被消耗的？
  处理逻辑读：1） 维护从存储系统到内存中的数据一致性问题 2） filter 过滤条件（where a=?）
  排序分组查询。order by，group by
  聚合运算。sum，max，avg
• CPU资源与逻辑读的关系
  avg_lgc_io:每条查询需要的平均逻辑IO
  total_lgc_io:实例CPU资源单位时间能够处理的逻辑IO总量
  计算公式：total_lgc_io=avg_lgc_io×qps
  单位时间CPU资源=查询执行的平均成本×单位时间执行的查询数量
• CPU使用率高的几种可能：
  1） QPS高。单条查询简单，执行成本低，QPS和CPU使用率曲线吻合，SQL优化余地小
  2） 查询执行成本高。执行效率低，资源消耗大，一般表现为慢SQL或较差的执行计划，SQL优化空间大。

5.连接数/会话数：

一定程度上体现了系统的业务压力，RDS最大连接数由实例规格决定。活跃连接数指同时存在SQL‘请求的链接数量。
连接数过多可能的原因：程序未使用或合理配置连接池，查询效率低下，事务未及时提交，等待事件。

6.IOPS：

每秒发生磁盘IO的次数，一旦磁盘IO成为数据库的瓶颈，性能会急剧下降，磁盘的性能瓶颈一般在于iops而不是吞吐量，RDS上默认分配SSD，具有优秀的随机读写能力。
IOPS高的集中可能：
1） 热平衡被破坏（频繁的全表扫描） 2） 事务提交频繁（fsync） 3） 磁盘临时表 4） 不合理的系统设计（日志入库。。。）

优化思路：

1.优化流程：

完整的监控体系：细致合理的告警；多维度图形化指标；暴露性能缺陷，掌握大规模资源
V
分析日志定位问题：Exceptional Time Range；DB/System error log；slow log；SQL执行统计
V
分析业务逻辑：读写需求；业务精简；资源调用关系
V
SQL优化：explain；SQL改写；索引调整；参数调整

2.MySQL的执行流程：

1） 客户提交一条语句
2） 现在查询缓存查看是否有对应的缓存数据，如果有直接返回（有的可能性很小，因此一般建议关闭查询缓存）
3） 交给解析器处理，解析器会将提交的语句生成一个解析树
4） 预处理器会处理解析树，形成新的解析树。此阶段存在一些SQL改写的过程
5） 改写后的解析树提交给查询优化器。查询优化器生成执行计划
6） 执行计划交由执行引擎调用存储引擎接口，完成执行过程，这里要注意MySQL的server层和Engine层是分离的
7） 最终结果由执行引擎返回给客户端，如果开启查询缓存的话则会缓存

执行过程：

SQL执行顺序：
(8) SELECT (9) DISTINCT
(1) FROM
(3) JOIN
(2) ON
(4) WHERE
(5) GROUP
(6) WITH {CUBE|ROLLUP}
(7) HAVING
(10) ORDER BY
(11) LIMIT

3.MySQL优化器和执行计划：

查询优化器：

• 负责生成SQL语句的有效执行计划的数据库组件
• 优化器是数据库核心价值所在，它是数据库的’大脑‘
• 优化SQL，某种意义上就是理解优化器的行为
• 优化的依据是执行成本（CBO）
• 优化器工作的前提是了解数据，工作的目的是解析SQL，生成执行计划

查询优化器工作过程：

4.查看执行计划：

explain Synax

• 查看一个SQL语句的执行计划：explain SQL_Statement
• 结合show warning查看优化器的改写：explain extended SQL_Statement
• 用于分区表查看执行计划：explain partition SQL_Statement
  

解读执行计划：

id ：包含一组数字，表示查询中执行select子句或操作表的顺序（id若相同，执行顺序由上而下；如果是子查询，id序号会递增，id越大优先级越高，越先被执行）
select_type ：表示查询中每个select子句的类型：

• SIMPLE：查询中不包含子查询或者UNION
• PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY
• SUBQUERY：在select或者where列表中包含子查询，该子查询被标记为SUBQUERY
• DERIVED：在from列表中包含的子查询被标记为DERIVED
• UNION：位于UNION关键字之后的查询；UNION语句的第一个查询根据情况被标记PRIMARY/SUBQUERY/DERIVED
• UNION RESULT：UNION语句的结果集
  type：表示MySQL在表中找到所需行的方式，又称为访问类型：
• ALL：Full Table Scan，全表扫描
• index：Full Index Scan，索引全扫描
• range：索引范围扫描
• ref：非唯一性索引扫描
• eq_ref：唯一性索引扫描
• const、system：常量匹配，如PRIMARY KEY/UNIQUE KEY
• NULL：MySQL不用访问表或者索引可以直接得到结果
  （Null性能最好，依次向上递减，ALL性能最差）

possible_keys：指出MySQL从where条件上找到的可用索引，过滤条件中的字段上若存在索引，则会在此列出，不能决定哪个索引被引用
key：显示MySQL在查询中实际使用的索引，若没有使用索引则显示为NULL
key_len：表示索引中使用的字节数，可通过该列计算查询中使用索引的长度
ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
rows：表示MySQL根据表的统计信息及索引选用情况，估算的找到所需的记录需要读取的行数
Extra：包含不适合在其他列中显示但是十分重要的额外信息

• Using index： 使用覆盖索引标志（Covering Index）
• Using where： 使用where条件进行过滤
• Using temporary： 查询中使用到了临时表或临时文件，通常会造成性能的下降
• Using filesort： MySQL不得不进行额外的排序操作，通常会造成性能下降
• Using index condition： 使用到ICP特性进行查询优化（index condition push down）

来自为知笔记(Wiz)