當(dāng)前位置：首頁(yè) > 运维知识 > 数据库 >内容正文

数据库

MySQL- SQL执行计划统计SQL执行每阶段的耗时

發(fā)布時(shí)間：2025/3/21 数据库 18 豆豆

生活随笔收集整理的這篇文章主要介紹了 MySQL- SQL执行计划统计SQL执行每阶段的耗时小編覺(jué)得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

文章目錄

生猛干貨
官方文檔
某些SQL查詢(xún)?yōu)槭裁绰?/li>
- MySQL處理SQL請(qǐng)求的過(guò)程
- 查詢(xún)緩存對(duì)SQL性能的影響
- SQL預(yù)處理及生成執(zhí)行計(jì)劃
- - 造成MySQL生成錯(cuò)誤的執(zhí)行計(jì)劃的原因
如何確定查詢(xún)各個(gè)階段所耗費(fèi)的時(shí)間
- 使用profile
- Performance Schema
- - 開(kāi)啟Performance Schema 記錄功能
搞定MySQL

生猛干貨

帶你搞定MySQL實(shí)戰(zhàn)，輕松對(duì)應(yīng)海量業(yè)務(wù)處理及高并發(fā)需求，從容應(yīng)對(duì)大場(chǎng)面試

官方文檔

https://dev.mysql.com/doc/

如果英文不好的話，可以參考 searchdoc 翻譯的中文版本

http://www.searchdoc.cn/rdbms/mysql/dev.mysql.com/doc/refman/5.7/en/index.com.coder114.cn.html

某些SQL查詢(xún)?yōu)槭裁绰?/h1>
要弄清楚這個(gè)問(wèn)題，需要知道MySQL處理SQL請(qǐng)求的過(guò)程，我們來(lái)看下

MySQL處理SQL請(qǐng)求的過(guò)程

客戶(hù)端將SQL請(qǐng)求發(fā)送給服務(wù)器

服務(wù)器檢查是否在緩存中是否命中該SQL，未命中的話進(jìn)入下一步

服務(wù)器進(jìn)行SQL解析、預(yù)處理，再由優(yōu)化器生成對(duì)應(yīng)的執(zhí)行計(jì)劃

根據(jù)執(zhí)行計(jì)劃來(lái)，調(diào)用存儲(chǔ)引擎API來(lái)查詢(xún)數(shù)據(jù)

將結(jié)果返回給客戶(hù)端

查詢(xún)緩存對(duì)SQL性能的影響

query_cache_type：設(shè)置查詢(xún)緩存是否可用，

可選值 ON OFF DEMAND , DEMAND表示只有在查詢(xún)語(yǔ)句中使用了SQL_CACHE和SQL_NO_CACHE來(lái)控制是否需要緩存
query_cache_size：設(shè)置查詢(xún)緩存的內(nèi)存大小

1024的整數(shù)倍
query_cache_limit：設(shè)置查詢(xún)緩存可用存儲(chǔ)的最大大小
query_cache_wlock_invalidate：設(shè)置數(shù)據(jù)表被鎖后是否返回緩存，默認(rèn)關(guān)閉
query_cache_min_res_unit：設(shè)置查詢(xún)緩存分配的內(nèi)存塊最小單位

對(duì)于一個(gè)讀寫(xiě)很頻發(fā)的的系統(tǒng)，使用查詢(xún)緩存很可能會(huì)降低查詢(xún)處理的效率，建議不是用查詢(xún)緩存，可以將query_cache_type 設(shè)置為OFF,query_cache_size 設(shè)置為0

SQL預(yù)處理及生成執(zhí)行計(jì)劃

接著上一步說(shuō)，查詢(xún)緩存未啟用，或者未命中查詢(xún)緩存，服務(wù)器進(jìn)行SQL解析、預(yù)處理，再由優(yōu)化器生成對(duì)應(yīng)的執(zhí)行計(jì)劃。 MySQL會(huì)依賴(lài)這個(gè)執(zhí)行計(jì)劃和存儲(chǔ)引擎進(jìn)行交互 .

包括以下過(guò)程

語(yǔ)法解析：包含語(yǔ)法等解析校驗(yàn)
預(yù)處理 : 檢查語(yǔ)法是否合法等
執(zhí)行計(jì)劃：上面都通過(guò)了，會(huì)生成執(zhí)行計(jì)劃。

造成MySQL生成錯(cuò)誤的執(zhí)行計(jì)劃的原因

存儲(chǔ)引擎提供的統(tǒng)計(jì)信息不準(zhǔn)確
執(zhí)行計(jì)劃中的估算不等同于實(shí)際的執(zhí)行計(jì)劃的成本
MySQL不考慮并發(fā)的查詢(xún)
MySQL有時(shí)候會(huì)基于一些特定的規(guī)則來(lái)生成執(zhí)行計(jì)劃
…

如何確定查詢(xún)各個(gè)階段所耗費(fèi)的時(shí)間

使用profile

set profiling = 1 ; 啟用profile , session級(jí)別的配置

執(zhí)行查詢(xún)

show profiles ; 查看每一個(gè)查詢(xún)所消耗的總時(shí)間信息

show profiles for query N ：查詢(xún)每個(gè)階段所消耗的時(shí)間（N為 Query_ID）

當(dāng)然了還有查詢(xún)CPU等信息的命令

比如 show profile cpu for query 1

演示

mysql> set profiling = 1; Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> select * from t_order; +------+-----------------+ | id | product | +------+-----------------+ | 1 | artisan-prod-01 | +------+-----------------+ 1 row in set (0.00 sec)mysql> show profiles; # 每條SQL的執(zhí)行匯總信息 +----------+------------+-----------------------+ | Query_ID | Duration | Query | +----------+------------+-----------------------+ | 1 | 0.00067725 | select * from t_order | +----------+------------+-----------------------+ 1 row in set, 1 warning (0.00 sec)mysql> show profile for query 1; # m每個(gè)階段的耗時(shí) +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000169 | | checking permissions | 0.000054 | | Opening tables | 0.000041 | | init | 0.000028 | | System lock | 0.000034 | | optimizing | 0.000009 | | statistics | 0.000023 | | preparing | 0.000020 | | executing | 0.000009 | | Sending data | 0.000218 | | end | 0.000010 | | query end | 0.000013 | | closing tables | 0.000013 | | freeing items | 0.000021 | | cleaning up | 0.000018 | +----------------------+----------+ 15 rows in set, 1 warning (0.00 sec)mysql>

查詢(xún)CPU的使用情況

mysql> show profile cpu for query 1; +----------------------+----------+----------+------------+ | Status | Duration | CPU_user | CPU_system | +----------------------+----------+----------+------------+ | starting | 0.000169 | 0.000066 | 0.000103 | | checking permissions | 0.000054 | 0.000021 | 0.000033 | | Opening tables | 0.000041 | 0.000015 | 0.000025 | | init | 0.000028 | 0.000011 | 0.000016 | | System lock | 0.000034 | 0.000013 | 0.000021 | | optimizing | 0.000009 | 0.000003 | 0.000005 | | statistics | 0.000023 | 0.000009 | 0.000014 | | preparing | 0.000020 | 0.000008 | 0.000012 | | executing | 0.000009 | 0.000003 | 0.000005 | | Sending data | 0.000218 | 0.002785 | 0.000000 | | end | 0.000010 | 0.000000 | 0.000000 | | query end | 0.000013 | 0.000000 | 0.000000 | | closing tables | 0.000013 | 0.000000 | 0.000000 | | freeing items | 0.000021 | 0.000000 | 0.000000 | | cleaning up | 0.000018 | 0.000000 | 0.000000 | +----------------------+----------+----------+------------+ 15 rows in set, 1 warning (0.00 sec)mysql>

看到有一個(gè) 1 warning ，看看是啥

mysql> show warnings; +---------+------+-------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------------------------------------------------------------+ | Warning | 1287 | 'SHOW PROFILE' is deprecated and will be removed in a future release. Please use Performance Schema instead | +---------+------+-------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)mysql>

‘SHOW PROFILE’ is deprecated and will be removed in a future release. Please use Performance Schema instead ，很明白了，看看官方推薦的 Performance Schema 吧

Performance Schema

5.5引入的 . performance_schema在5.7.x及其以上版本中默認(rèn)啟用(5.6.x及其以下版本默認(rèn)關(guān)閉)，如果要顯式啟用或關(guān)閉時(shí)，我們需要使用參數(shù)performance_schema=ON|OFF設(shè)置

performance_schema可以記錄數(shù)據(jù)庫(kù)所有線程執(zhí)行過(guò)的SQL，而上面的profile是session級(jí)別的，僅能記錄當(dāng)前session 的。

開(kāi)啟Performance Schema 記錄功能

mysql> use performance_schema ; # 使用performance_schema Database changed mysql> update setup_instruments set enabled = 'YES', timed = 'YES' where name like 'stage%'; # Step1 Query OK, 120 rows affected (0.00 sec) Rows matched: 129 Changed: 120 Warnings: 0mysql> update setup_consumers set enabled = 'YES' where name like 'events%'; # Step2 Query OK, 10 rows affected (0.31 sec) Rows matched: 12 Changed: 10 Warnings: 0mysql> select * from artisan.t_order; # 隨便執(zhí)行點(diǎn)啥以便觀察效果 +------+-----------------+ | id | product | +------+-----------------+ | 1 | artisan-prod-01 | +------+-----------------+ 1 row in set (0.00 sec)mysql>

查看耗時(shí)的SQL

SELECTa.thread_id,sql_text,c.event_name,(c.timer_end - c.timer_start) / 1000000000 AS 'duration (ms)' FROM`performance_schema`.events_statements_history_long a JOIN `performance_schema`.threads b ON a.thread_id = b.thread_id JOIN `performance_schema`.events_stages_history_long c ON c.thread_id = b.thread_id AND c.event_id BETWEEN a.event_id AND a.end_event_id WHEREb.processlist_id = connection_id() AND a.event_name = 'statement/sql/select' ORDER BYa.thread_id,c.event_id;

搞定MySQL

總結(jié)

以上是生活随笔為你收集整理的MySQL- SQL执行计划统计SQL执行每阶段的耗时的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。

上一篇： MySQL-获取有性能问题SQL的方法_
下一篇： MySQL-在线处理大表数据在线修改