MySQL高级第六篇：数据库性能分析与优化

  • 一、数据库服务器优化步骤概述
  • 二、慢查询日志：记录执行慢的SQL
  • 1. 开启慢查询日志
  • 2. 设置long_query_time
  • 3. 查看慢查询数与慢查询SQL
  • 三、分析查询语句：EXPLAIN
  • 1. 概述
  • 2.EXPLAIN各列的含义


一、数据库服务器优化步骤概述

  • 1.观察服务器状态，看是否存在周期性波动，是的话可以尝试加缓存或者调整缓存失效策略；
  • 2.否则考虑开启慢查询日志，找出比较慢的SQL语句；
  • 3.分析SQL语句：EXPLAIN，SHOW PROFILE
  • 4.如果是因为SQL等待时间比较长，考虑调整服务器参数；
  • 5.如果是因为SQL执行时间比较长，考虑优化索引、SQL语句本身或者表设计；
  • 6.如果以上都不能解决，考虑是否SQL查询达到瓶颈
  • 7.这时需要考虑读写分离，分库分表等。

二、慢查询日志：记录执行慢的SQL

  • 默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。
  • 如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。
  • 慢查询日志支持将日志记录写入文件。

1. 开启慢查询日志

  • 开启前先查询了一下是关闭状态，然后设置开启，报错要注意有些参数要加 global

2. 设置long_query_time

  • 默认阈值为10s，比较长，自己可以更改，sql语句设置是临时设置，如下：
  • 想要永久修改，需要改配置文件：
[mysqld]
s1ow_query_1og=ON  #开启慢查询日志的开关
slow_query_log_file=/var/lib/mysql/ad-slow.log   #慢查询日志的目录和文件名信息
1ong_query_time=3   #设置慢查询的阀值为3秒,超出此设定值的SQL即被记录到慢查询日志
log_output=FILE

3. 查看慢查询数与慢查询SQL

SHOW GLOBAL STATUS LIKE '%Slow_queries%;

查找慢查询SQL，MySQL提供了慢查询分析工具：mysqldumpslow

三、分析查询语句：EXPLAIN

1. 概述

  • 定位了查询慢的SQL之后，就可以使用EXPLAIN 或 DESCRIBE工具做针对性的分析查询语句。DESCRIBE语句的使用方法与EXPLAIN语句是一样的，并且分析结果也是一样的。
  • MySQL中有专门负责优化SELECT语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提供它认为最优的执行计划，这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来具体执行查询等。
  • MySQL为我们提供了EXPLAIN语句来帮助我们查看某个查询语句的具体执行计划，看懂EXPLAIN语句的各个输出项，可以有针对性的提升我们查询语句的性能。

2.EXPLAIN各列的含义

sql server与mysql性能测试 mysql sql性能分析_慢查询

  • id注意事项
  • id如果相同，可以认为是一组，从上往下顺序执行
  • 在所有组中，id值越大，优先级越高，越先执行
  • id号每个号码，表示一趟独立的查询，一个sq的查询趟数越少越好
  • type注意事项
  • 执行计划的一条记录就代表着MySQL对某个表执行查询时的访间方法，又称“访问类型”，其中 type 列就表明了这个访问方法是啥，是较为重要的一个指标。
  • 完整的访问方法如下：system，const，eq_ref，ref，fulltext，ref_or_null，index_merge，unique_subquery，index_subquery，range，index，ALL（越往前效果越好，越往后越糟糕）
  • 当表中只有一条数据，且使用存储引擎的统计数据是精确的，如MyISAM，访问方式为 system
  • const ：根据主键或者唯一二级索引列与常数进行等值匹配
  • 如果使用索引获取某些范围区间的记录，那么就可能使用到 range 访问方法
  • ALL：最熟悉的全表扫描
  • SQL性能优化的目标：至少要达到range级别，要求是ref级别，最好是consts级别。(阿里巴巴开发手册要求)
  • Extra
  • 通过这些额外信息，可以更准确的理解MySQL到底将如何执行给定的查询语句。

EXPLAIN不考虑各种Cache，并且不能显示执行查询时所作的优化工作等具体信息，只能告诉我们一个大概估算的统计结果。

如果想跟踪优化器做出的各种决策，可以使用 OPTIMIZER_TRACE ，它可以追踪如访问表的方法，各种开销转换等，并记录到一个表中。

以上就是数据库优化分析的步骤概述，接下来的文章将逐个详细分析数据库索引优化的具体情况…