(慢查询优化)如何排查和优化慢SQL?
在 MySQL 中排查和优化慢 SQL 主要分为以下三步:
- 开启 MySQL 慢日志:记录 MySQL 中执行比较慢的 SQL 语句。
- 查看 SQL 执行计划:根据慢日志中的 SQL 查看执行计划,定位问题。
- 分析原因优化执行:根据 SQL 执行计划分析导致慢 SQL 的原因进行优化。例如我们通过explain来判断查询是否走索引,如果可以的话我们添加索引来提高我们的查询效率
① 开启 SQL 慢日志
慢查询是 MySQL 中提供的一种慢查询日志,它用来记录在 MySQL 中响应时间超过阈值的语句,具体指运行时间超过 long_query_time 值的 SQL,则会被记录到慢查询日志中。
慢日志参数 long_query_time 的默认值为 10,意思是运行 10 秒以上的语句。默认情况下,MySQL 数据库并不启动慢查询日志,需要我们手动来设置这个参数,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会给 MySQL 服务器带来一定的性能影响。慢查询日志支持将日志记录写入文件,也支持将日志记录写入数据库表。
使用 show variables like '%slow_query_log%' 来查询慢查询日志是否开启,执行效果如下图所示:
slow_query_log 的值为 OFF 时,表示未开启慢查询日志。
②开启慢查询(explain)
explain 查询计划如下图所示:
查询结果中的字段有很多,我们关注的字段主要有以下两个:
- type:表示查询时使用的访问方法或策略,描述了 MySQL 在执行查询时如何访问数据。常见的 type 值包括:
- ALL:全表扫描,表示 MySQL 将扫描整个表来找到匹配的行。
- index:索引扫描,表示 MySQL 将通过索引进行扫描,但可能需要回表访问数据行。
- range:范围扫描,表示 MySQL 将使用索引的范围条件来定位匹配的行。
- ref:使用非唯一索引进行查找,返回匹配某个值的所有行。
- eq_ref:使用唯一索引进行查找,返回匹配某个值的单个行。
- const:使用常量值进行查找,通常是通过主键或唯一索引进行精确匹配。
- NULL:无效或未知的访问类型。
- key:表示查询时使用的索引。如果查询使用了索引,key 字段将显示使用的索引名称;如果查询没有使用索引,key 字段将显示 NULL。
也就说,当 explain 查询计划中的 key 不等于 NULL,并且 type 等于 index、range、ref、eq_ref、const 等时都表示此语句执行了索引查询,也就是索引并未失效。
③ 根据原因优化 SQL
根据上一步查询计划的结果,分析和优化 SQL,通常的优化手段有以下几个:
- 正确使用索引:如果查询字段为创建索引,可以新建索引;如创建了索引但未正确使用,通过优化 SQL 正确使用索引来提高查询效率。
- 添加缓存:通过多级缓存,例如分布式缓存 + 本地缓存来优化查询效率。
- 分库分表:单表行数超过 500 万行或者单表容量超过 2GB,推荐进行分库分表。
- 分布式数据库:使用大数据下性能更好的分布式数据库,例如 TiDB
SQL优化
1.避免使用select *
SELECT * 会消耗更多的 CPU。
SELECT * 无用字段增加网络带宽资源消耗,增加数据传输时间,尤其是大字段(如 varchar、blob、text)。
SELECT * 无法使用 MySQL 优化器覆盖索引的优化(基于 MySQL 优化器的“覆盖索引”策略又是速
度极快,效率极高,业界极为推荐的查询优化方式)
SELECT <字段列表> 可减少表结构变更带来的影响。
2.分页优化
3.子查询优化
4.使用inner join和left join(隐式连接和显式连接)
多表查询的时候,我们有时候可以使用inner join和left join来避免走全表扫描
因为隐式连接的语法问题例如没有明确指定连接的类型和条件,导致优化器在生成执行计划时可能无法充分利用索引
5.批量操作减少打入数据库请求
对于更新操作,能使用批量操作就尽量使用,减少打入数据库请求提高性能
6.优化慢SQL
通过explain,查看当前SQL语句是否走索引
修改sql语句,如改变where的匹配条件,让sql语句走索引
常见方法还有:
1.固定不变的数据我们放缓存,走redisor本地缓存不走数据库
2.分库分表
3.使用NOSQL非关系型数据库