阿里云做网站经费,建设工程消防网站进入程序,做电影免费ppt模板下载网站,wordpress 301跳转数据库服务器的优化步骤
当我们遇到数据库调优问题的时候#xff0c;该如何思考呢#xff1f;我把思考的流程整理成了下面这张图。
整个流程划分成了观察#xff08;Show status#xff09;和行动#xff08;Action#xff09;两个部分。字母 S 的部分代表观察#xf…数据库服务器的优化步骤
当我们遇到数据库调优问题的时候该如何思考呢我把思考的流程整理成了下面这张图。
整个流程划分成了观察Show status和行动Action两个部分。字母 S 的部分代表观察会使用相应的分析工具字母 A 代表的部分是行动对应分析可以采取的行动。 我们可以通过观察了解数据库整体的运行状态通过性能分析工具可以让我们了解执行慢的 SQL 都有哪些查看具体的 SQL 执行计划甚至是 SQL 执行中的每一步的成本代价这样才能定位问题所在找到了问题再采取相应的行动。
我来详细解释一下这张图。
首先在 S1 部分我们需要观察服务器的状态是否存在周期性的波动。如果存在周期性波动有可能是周期性节点的原因比如双十一、促销活动等。这样的话我们可以通过 A1 这一步骤解决也就是加缓存或者更改缓存失效策略。
如果缓存策略没有解决或者不是周期性波动的原因我们就需要进一步分析查询延迟和卡顿的原因。接下来进入 S2 这一步我们需要开启慢查询。慢查询可以帮我们定位执行慢的 SQL 语句。我们可以通过设置 long_query_time 参数定义“慢”的阈值如果 SQL 执行时间超过了 long_query_time则会认为是慢查询。当收集上来这些慢查询之后我们就可以通过分析工具对慢查询日志进行分析。
在 S3 这一步骤中我们就知道了执行慢的 SQL这样就可以针对性地用 EXPLAIN 查看对应 SQL 语句的执行计划或者使用 show profile 查看 SQL 中每一个步骤的时间成本。这样我们就可以了解 SQL 查询慢是因为执行时间长还是等待时间长。
如果是 SQL 等待时间长我们进入 A2 步骤。在这一步骤中我们可以调优服务器的参数比如适当增加数据库缓冲池等。如果是 SQL 执行时间长就进入 A3 步骤这一步中我们需要考虑是索引设计的问题还是查询关联的数据表过多还是因为数据表的字段设计问题导致了这一现象。然后在这些维度上进行对应的调整。
如果 A2 和 A3 都不能解决问题我们需要考虑数据库自身的 SQL 查询性能是否已经达到了瓶颈如果确认没有达到性能瓶颈就需要重新检查重复以上的步骤。如果已经达到了性能瓶颈进入 A4 阶段需要考虑增加服务器采用读写分离的架构或者考虑对数据库进行分库分表比如垂直分库、垂直分表和水平分表等。
以上就是数据库调优的流程思路。如果我们发现执行 SQL 时存在不规则延迟或卡顿的时候就可以采用分析工具帮我们定位有问题的 SQL这三种分析工具你可以理解是 SQL 调优的三个步骤慢查询、EXPLAIN 和 SHOW PROFILING。
使用慢查询定位执行慢的 SQL
好慢询可以帮我们找到执行慢的 SQL在使用前我们需要先看下慢查询是否已经开启使用下面这条命令即可
mysql show variables like %slow_query_log;我们能看到 slow_query_logOFF也就是说慢查询日志此时是关上的。我们可以把慢查询日志打开注意设置变量值的时候需要使用 global否则会报错
mysql set global slow_query_logON;然后我们再来查看下慢查询日志是否开启以及慢查询日志文件的位置 你能看到这时慢查询分析已经开启同时文件保存在 DESKTOP-4BK02RP-slow 文件中。
接下来我们来看下慢查询的时间阈值设置使用如下命令
mysql show variables like %long_query_time%;
这里如果我们想把时间缩短比如设置为 3 秒可以这样设置
mysql set global long_query_time 3; 我们可以使用 MySQL 自带的 mysqldumpslow 工具统计慢查询日志这个工具是个 Perl 脚本你需要先安装好 Perl。
mysqldumpslow 命令的具体参数如下
-s采用 order 排序的方式排序方式可以有以下几种。分别是 c访问次数、t查询时间、l锁定时间、r返回记录、ac平均查询次数、al平均锁定时间、ar平均返回记录数和 at平均查询时间。其中 at 为默认排序方式。-t返回前 N 条数据 。-g后面可以是正则表达式对大小写不敏感。
比如我们想要按照查询时间排序查看前两条 SQL 语句这样写即可
perl mysqldumpslow.pl -s t -t 2 C:\ProgramData\MySQL\MySQL Server 8.0\Data\DESKTOP-4BK02RP-slow.log 你能看到开启了慢查询日志并设置了相应的慢查询时间阈值之后只要大于这个阈值的 SQL 语句都会保存在慢查询日志中然后我们就可以通过 mysqldumpslow 工具提取想要查找的 SQL 语句了。
如何使用 EXPLAIN 查看执行计划
定位了查询慢的 SQL 之后我们就可以使用 EXPLAIN 工具做针对性的分析比如我们想要了解 product_comment 和 user 表进行联查的时候所采用的的执行计划可以使用下面这条语句
EXPLAIN SELECT comment_id, product_id, comment_text, product_comment.user_id, user_name FROM product_comment JOIN user on product_comment.user_id user.user_id EXPLAIN 可以帮助我们了解数据表的读取顺序、SELECT 子句的类型、数据表的访问类型、可使用的索引、实际使用的索引、使用的索引长度、上一个表的连接匹配条件、被优化器查询的行的数量以及额外的信息比如是否使用了外部排序是否使用了临时表等等。
SQL 执行的顺序是根据 id 从大到小执行的也就是 id 越大越先执行当 id 相同时从上到下执行。
数据表的访问类型所对应的 type 列是我们比较关注的信息。type 可能有以下几种情况 在这些情况里all 是最坏的情况因为采用了全表扫描的方式。index 和 all 差不多只不过 index 对索引表进行全扫描这样做的好处是不再需要对数据进行排序但是开销依然很大。如果我们在 extra 列中看到 Using index说明采用了索引覆盖也就是索引可以覆盖所需的 SELECT 字段就不需要进行回表这样就减少了数据查找的开销。
比如我们对 product_comment 数据表进行查询设计了联合索引 composite_index (user_id, comment_text)然后对数据表中的 comment_id、comment_text、user_id 这三个字段进行查询最后用 EXPLAIN 看下执行计划
EXPLAIN SELECT comment_id, comment_text, user_id FROM product_comment 你能看到这里的访问方式采用了 index 的方式key 列采用了联合索引进行扫描。Extral 列为 Using index告诉我们索引可以覆盖 SELECT 中的字段也就不需要回表查询了。
range 表示采用了索引范围扫描这里不进行举例从这一级别开始索引的作用会越来越明显因此我们需要尽量让 SQL 查询可以使用到 range 这一级别及以上的 type 访问方式。
index_merge 说明查询同时使用了两个或以上的索引最后取了交集或者并集。比如想要对 comment_id500000 或者 user_id500000 的数据进行查询数据表中 comment_id 为主键user_id 是普通索引我们可以查看下执行计划
EXPLAIN SELECT comment_id, product_id, comment_text, user_id FROM product_comment WHERE comment_id 500000 OR user_id 500000 你能看到这里同时使用到了两个索引分别是主键和 user_id采用的数据表访问类型是 index_merge通过 union 的方式对两个索引检索的数据进行合并。
ref 类型表示采用了非唯一索引或者是唯一索引的非唯一性前缀。比如我们想要对 user_id500000 的评论进行查询使用 EXPLAIN 查看执行计划
EXPLAIN SELECT comment_id, comment_text, user_id FROM product_comment WHERE user_id 500000 这里 user_id 为普通索引因为 user_id 在商品评论表中可能是重复的因此采用的访问类型是 ref同时在 ref 列中显示 const表示连接匹配条件是常量用于索引列的查找。
eq_ref 类型是使用主键或唯一索引时产生的访问方式通常使用在多表联查中。假设我们对 product_comment 表和 usre 表进行联查关联条件是两张表的 user_id 相等使用 EXPLAIN 进行执行计划查看
EXPLAIN SELECT * FROM product_comment JOIN user WHERE product_comment.user_id user.user_id const 类型表示我们使用了主键或者唯一索引所有的部分与常量值进行比较比如我们想要查看 comment_id500000查看执行计划
EXPLAIN SELECT comment_id, comment_text, user_id FROM product_comment WHERE comment_id 500000 需要说明的是 const 类型和 eq_ref 都使用了主键或唯一索引不过这两个类型有所区别const 是与常量进行比较查询效率会更快而 eq_ref 通常用于多表联查中。
system 类型一般用于 MyISAM 或 Memory 表属于 const 类型的特例当表只有一行时连接类型为 system
EXPLAIN SELECT * FROM test_myisam 你能看到除了 all 类型外其他类型都可以使用到索引但是不同的连接方式的效率也会有所不同效率从低到高依次为 all index range index_merge ref eq_ref const/system。我们在查看执行计划的时候通常希望执行计划至少可以使用到 range 级别以上的连接方式如果只使用到了 all 或者 index 连接方式我们可以从 SQL 语句和索引设计的角度上进行改进。
使用 SHOW PROFILE 查看 SQL 的具体执行成本
SHOW PROFILE 相比 EXPLAIN 能看到更进一步的执行解析包括 SQL 都做了什么、所花费的时间等。默认情况下profiling 是关闭的我们可以在会话级别开启这个功能。
mysql show variables like profiling; 通过设置 profilingON’来开启 show profile
mysql set profiling ON; 我们可以看下当前会话都有哪些 profiles使用下面这条命令
mysql show profiles; 你能看到当前会话一共有 2 个查询如果我们想要查看上一个查询的开销可以使用
mysql show profile; 我们也可以查看指定的 Query ID 的开销比如 show profile for query 2 查询结果是一样的。在 SHOW PROFILE 中我们可以查看不同部分的开销比如 cpu、block.io 等 通过上面的结果我们可以弄清楚每一步骤的耗时以及在不同部分比如 CPU、block.io 的执行时间这样我们就可以判断出来 SQL 到底慢在哪里。
不过 SHOW PROFILE 命令将被弃用我们可以从 information_schema 中的 profiling 数据表进行查看。
总结
我今天梳理了 SQL 优化的思路从步骤上看我们需要先进行观察和分析分析工具的使用在日常工作中还是很重要的。今天只介绍了常用的三种分析工具实际上可以使用的分析工具还有很多。
我在这里总结一下今天文章里提到的三种分析工具。我们可以通过慢查询日志定位执行慢的 SQL然后通过 EXPLAIN 分析该 SQL 语句是否使用到了索引以及具体的数据表访问方式是怎样的。我们也可以使用 SHOW PROFILE 进一步了解 SQL 每一步的执行时间包括 I/O 和 CPU 等资源的使用情况。
