百万数据Sql优化流程

百万数据Mysql优化流程

引言

Mysql优化是一件非常繁琐且考验程序员综合能力的事儿，下图是针对Mysql优化制作的一个流程图，可以帮助新手有效快速的进行Mysql问题的排查和解决。整个流程划分成了观察（Show status）和行动（Action）两个部分。字母 S 的部分代表观察（会使用相应的分析工具），字母 A 代表的部分是行动（对应分析可以采取的行动）。

SQL优化的一般实战流程

1. 发现问题SQL

   1. 通过监控系统查看服务器状态

      

   2. 查看慢查询日志

2. 分析问题SQL

   1. 查看SQl的执行计划

   2. 如果有异常信息或者警告，查看相关信息【了解】

   3. 查看SQL涉及的表结构和索引信息【了解】

      https://zerolee1993.github.io/mysql-guide/#

      1. SQL规范性检查
      2. 表结构索引检查

   4. 根据执行计划，思考可能的优化点

3. 按照可能的优化点执行表结构变更、增加索引、SQL改写等操作

4. 查看优化后的执行时间和执行计划

分析问题SQL—执行计划

SQL的执行计划能够反应SQL的执行效率，使用方式非常的简单，只需要在要执行的SQL前添加 Explain 关键字即可

字段	解释
id	每个被独立执行的操作标识，标识对象被操作的顺序，id值越大，先被执行，如果相同，执行顺序从上到下
select_type	查询中每个select 字句的类型
table	通常是表名，显示数据来自哪张表
partitions	匹配的分区信息(对于非分区表值为NULL)
type	连接操作的类型
possible_keys	可能用到的索引
key	优化器实际使用的索引(最重要的列) 从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL。当出现ALL时表示当前SQL出现了“坏味道”
key_len	被优化器选定的索引键长度，单位是字节
ref	表示本行被操作对象的参照对象，无参照对象为NULL
rows	查询执行所扫描的元组个数（对于innodb，此值为估计值）
filtered	条件表上数据被过滤的元组个数百分比
extra	执行计划的重要补充信息，当此列出现Using filesort , Using temporary 字样时就要小心了，很可能SQL语句需要优化

type

• 依次从好到差：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL，除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个

extra

• Using filesort（文件排序）:看到这个的时候,查询就需要优化了。 MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
• Using temporary（临时表）：看到这个的时候,查询需要优化了。这里, MYSQL需要创建一个临时表来存储结果,这通常发生在对不同的列集进行 ORDER BY上,而不是 GROUP BY上