實(shí)際的日常開發(fā)工作中可能會遇到某個(gè)新功能在測試時(shí)需要很久才返回結(jié)果，這時(shí)就應(yīng)該分析是不是慢查詢導(dǎo)致的，如果確實(shí)有慢查詢，就需要來學(xué)習(xí)怎么找到慢查詢和怎么分析 SQL 執(zhí)行效率？
定位慢 SQL 有如下兩種解決方案：
- 查看慢查詢?nèi)罩敬_定已經(jīng)執(zhí)行完的慢查詢
- show processlist 查看正在執(zhí)行的慢查詢
定位到慢查詢語句后，可以通過 explain、show profile 和 trace 等診斷工具來分析慢查詢
一、如何開啟并找到慢查詢?nèi)罩荆?/h4>
如果需要使用慢查詢?nèi)罩?，一般分為四步?/strong>
開啟慢查詢?nèi)罩?、設(shè)置慢查詢閥值、確定慢查詢?nèi)罩韭窂?、確定慢查詢?nèi)罩镜奈募?/strong>
?涉及到的命令如下：
set global slow_query_log = on;
set global long_query_time = 1;
show global variables like "datadir";
show global variables like "slow_query_log_file";二、mysqldumpslow工具分析慢查詢
如果覺得系統(tǒng)自帶的慢查詢?nèi)罩静环奖悴榭?，也可以使用mysqldumpslow 等工具對慢查詢?nèi)罩具M(jìn)行分析；
mysqldumpslow經(jīng)常使用的參數(shù)：
-s，是order的順序
----- al 平均鎖定時(shí)間
-----ar 平均返回記錄時(shí)間
-----at 平均查詢時(shí)間（默認(rèn)）
-----c 計(jì)數(shù)
-----l 鎖定時(shí)間
-----r 返回記錄
-----t 查詢時(shí)間
-t，是top n的意思，即為返回前面多少條的數(shù)據(jù)
-g，后邊可以寫一個(gè)正則匹配模式，大小寫不敏感的
基本命令如下：
mysqldumpslow -t 10 -s t -g “l(fā)eft join” slow.log三、show processlist
show processlist 命令判斷正在執(zhí)行的慢查詢。show processlist 顯示哪些線程正在運(yùn)行。如果有 PROCESS 權(quán)限，則可以看到所有線程。否則，只能看到當(dāng)前會話的線程；
?四、使用 explain 分析慢查詢
定位到慢查詢語句后，我們就要開始分析 SQL 執(zhí)行效率了;
簡單的數(shù)據(jù)準(zhǔn)備工作：
CREATE DATABASE test;
use test;
drop table if exists t1;
CREATE TABLE `t1` (
`id` int NOT NULL auto_increment,
`a` int DEFAULT NULL,
`b` int DEFAULT NULL,
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '記錄創(chuàng)建時(shí)間',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '記錄更新時(shí)間',
PRIMARY KEY (`id`),
KEY `idx_a` (`a`),
KEY `idx_b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
drop procedure if exists insert_t1;
delimiter ;;
create procedure insert_t1()
begin
declare i int;
set i=1;
while(i<=1000)do
insert into t1(a,b) values(i, i);
set i=i+1;
end while;
end;;
delimiter ;
call insert_t1();
drop table if exists t2;
create table t2 like t1;
insert into t2 select * from t1;
?explain分析結(jié)果：
?explain返回結(jié)果各字段說明：
| 列名 | 解釋 |
|---|---|
| id | 查詢編號 |
| select_type | 查詢類型：顯示本行是簡單還是復(fù)雜查詢 |
| table | 涉及到的表 |
| partitions | 匹配的分區(qū)：查詢將匹配記錄所在的分區(qū)。僅當(dāng)使用 partition 關(guān)鍵字時(shí)才顯示該列。對于非分區(qū)表，該值為 NULL。 |
| type | 本次查詢的表連接類型 |
| possible_keys | 可能選擇的索引 |
| key | 實(shí)際選擇的索引 |
| key_len | 被選擇的索引長度：一般用于判斷聯(lián)合索引有多少列被選擇了 |
| ref | 與索引比較的列 |
| rows | 預(yù)計(jì)需要掃描的行數(shù)，對 InnoDB 來說，這個(gè)值是估值，并不一定準(zhǔn)確 |
| filtered | 按條件篩選的行的百分比 |
| Extra | 附加信息 |
?其中 select_type、type、key、rows、Extra 是重點(diǎn)關(guān)注項(xiàng)；
五、show profile 分析慢查詢
在 MySQL 數(shù)據(jù)庫中，通過 profile，能夠更清楚地了解 SQL 執(zhí)行過程的資源使用情況，如何使用 profile 分析慢查詢，大致步驟是：確定這個(gè) MySQL 版本是否支持 profile；確定 profile 是否關(guān)閉；開啟 profile；執(zhí)行 SQL(查看執(zhí)行完 SQL 的 query id；通過 query id 查看 SQL 的每個(gè)狀態(tài)及耗時(shí)時(shí)間)
具體操作如下：
相關(guān)命令：
select @@have_profiling;
select @@profiling;
set profiling=1;
相關(guān)命令：
select * from t1 where b=1000;
show profiles;
show profile for query 1;?六、trace 分析 SQL 優(yōu)化器
如果需要使用，先開啟 trace，設(shè)置格式為 JSON，再執(zhí)行需要分析的 SQL，最后查看 trace 分析結(jié)果（在 information_schema.OPTIMIZER_TRACE 中）
開啟該功能，會對 MySQL 性能有所影響，因此只建議分析問題時(shí)臨時(shí)開啟；
該語句使用的是 b 字段的索引 idx_b。實(shí)際表 t1 中，a、b 兩個(gè)字段都有索引，為什么條件中有這兩個(gè)索引字段卻偏偏選了 b 字段的索引呢？這時(shí)就可以使用 trace 進(jìn)行分析
1 手動開啟trace
set session optimizer_trace="enabled=on",end_markers_in_json=on;2 執(zhí)行sql查詢
select * from t1 where a >900 and b > 910 order by a;執(zhí)行結(jié)果如下：
------+------+------+---------------------+---------------------+
| id | a | b | create_time | update_time |
+------+------+------+---------------------+---------------------+
| 911 | 911 | 911 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 912 | 912 | 912 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 913 | 913 | 913 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 914 | 914 | 914 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 915 | 915 | 915 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 916 | 916 | 916 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 917 | 917 | 917 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 918 | 918 | 918 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 919 | 919 | 919 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 920 | 920 | 920 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 921 | 921 | 921 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 922 | 922 | 922 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 923 | 923 | 923 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 924 | 924 | 924 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 925 | 925 | 925 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 926 | 926 | 926 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 927 | 927 | 927 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 928 | 928 | 928 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 929 | 929 | 929 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 930 | 930 | 930 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 931 | 931 | 931 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 932 | 932 | 932 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 933 | 933 | 933 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 934 | 934 | 934 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 935 | 935 | 935 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 936 | 936 | 936 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 937 | 937 | 937 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 938 | 938 | 938 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 939 | 939 | 939 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 940 | 940 | 940 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 941 | 941 | 941 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 942 | 942 | 942 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 943 | 943 | 943 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 944 | 944 | 944 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 945 | 945 | 945 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 946 | 946 | 946 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 947 | 947 | 947 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 948 | 948 | 948 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 949 | 949 | 949 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 950 | 950 | 950 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 951 | 951 | 951 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 952 | 952 | 952 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 953 | 953 | 953 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 954 | 954 | 954 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 955 | 955 | 955 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 956 | 956 | 956 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 957 | 957 | 957 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 958 | 958 | 958 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 959 | 959 | 959 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 960 | 960 | 960 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 961 | 961 | 961 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 962 | 962 | 962 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 963 | 963 | 963 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 964 | 964 | 964 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 965 | 965 | 965 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 966 | 966 | 966 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 967 | 967 | 967 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 968 | 968 | 968 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 969 | 969 | 969 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 970 | 970 | 970 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 971 | 971 | 971 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 972 | 972 | 972 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 973 | 973 | 973 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 974 | 974 | 974 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 975 | 975 | 975 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 976 | 976 | 976 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 977 | 977 | 977 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 978 | 978 | 978 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 979 | 979 | 979 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 980 | 980 | 980 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 981 | 981 | 981 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 982 | 982 | 982 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 983 | 983 | 983 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 984 | 984 | 984 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 985 | 985 | 985 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 986 | 986 | 986 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 987 | 987 | 987 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 988 | 988 | 988 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 989 | 989 | 989 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 990 | 990 | 990 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 991 | 991 | 991 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 992 | 992 | 992 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 993 | 993 | 993 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 994 | 994 | 994 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 995 | 995 | 995 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 996 | 996 | 996 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 997 | 997 | 997 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 998 | 998 | 998 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 999 | 999 | 999 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
| 1000 | 1000 | 1000 | 2023-06-26 02:16:08 | 2023-06-26 02:16:08 |
+------+------+------+---------------------+---------------------+3 查看 trace 分析結(jié)果
SELECT * FROM information_schema.OPTIMIZER_TRACE\G查詢結(jié)果如下圖所示：文章來源：http://www.zghlxwxcb.cn/news/detail-514399.html
*************************** 1. row ***************************
QUERY: select * from t1 where a >900 and b > 910 order by a
TRACE: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `t1`.`id` AS `id`,`t1`.`a` AS `a`,`t1`.`b` AS `b`,`t1`.`create_time` AS `create_time`,`t1`.`update_time` AS `update_time` from `t1` where ((`t1`.`a` > 900) and (`t1`.`b` > 910)) order by `t1`.`a`"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))"
}
] /* steps */
} /* condition_processing */
},
{
"substitute_generated_columns": {
} /* substitute_generated_columns */
},
{
"table_dependencies": [
{
"table": "`t1`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [
{
"table": "`t1`",
"range_analysis": {
"table_scan": {
"rows": 1000,
"cost": 103.35
} /* table_scan */,
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_a",
"usable": true,
"key_parts": [
"a",
"id"
] /* key_parts */
},
{
"index": "idx_b",
"usable": true,
"key_parts": [
"b",
"id"
] /* key_parts */
}
] /* potential_range_indexes */,
"setup_range_conditions": [
] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
} /* group_index_range */,
"skip_scan_range": {
"potential_skip_scan_indexes": [
{
"index": "idx_a",
"usable": false,
"cause": "query_references_nonkey_column"
},
{
"index": "idx_b",
"usable": false,
"cause": "query_references_nonkey_column"
}
] /* potential_skip_scan_indexes */
} /* skip_scan_range */,
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idx_a",
"ranges": [
"900 < a"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"in_memory": 1,
"rows": 100,
"cost": 35.26,
"chosen": true
},
{
"index": "idx_b",
"ranges": [
"910 < b"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"in_memory": 1,
"rows": 90,
"cost": 31.76,
"chosen": true
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */,
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "idx_b",
"rows": 90,
"ranges": [
"910 < b"
] /* ranges */
} /* range_access_plan */,
"rows_for_plan": 90,
"cost_for_plan": 31.76,
"chosen": true
} /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`t1`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 90,
"access_type": "range",
"range_details": {
"used_index": "idx_b"
} /* range_details */,
"resulting_rows": 90,
"cost": 40.76,
"chosen": true,
"use_tmp_table": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 90,
"cost_for_plan": 40.76,
"sort_cost": 90,
"new_cost_for_plan": 130.76,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`t1`",
"attached": "((`t1`.`a` > 900) and (`t1`.`b` > 910))"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"optimizing_distinct_group_by_order_by": {
"simplifying_order_by": {
"original_clause": "`t1`.`a`",
"items": [
{
"item": "`t1`.`a`"
}
] /* items */,
"resulting_clause_is_simple": true,
"resulting_clause": "`t1`.`a`"
} /* simplifying_order_by */
} /* optimizing_distinct_group_by_order_by */
},
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "ORDER BY",
"steps": [
] /* steps */,
"index_order_summary": {
"table": "`t1`",
"index_provides_order": false,
"order_direction": "undefined",
"index": "idx_b",
"plan_changed": false
} /* index_order_summary */
} /* reconsidering_access_paths_for_index_ordering */
},
{
"finalizing_table_conditions": [
{
"table": "`t1`",
"original_table_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))",
"final_table_condition ": "((`t1`.`a` > 900) and (`t1`.`b` > 910))"
}
] /* finalizing_table_conditions */
},
{
"refine_plan": [
{
"table": "`t1`",
"pushed_index_condition": "(`t1`.`b` > 910)",
"table_condition_attached": "(`t1`.`a` > 900)"
}
] /* refine_plan */
},
{
"considering_tmp_tables": [
{
"adding_sort_to_table": "t1"
} /* filesort */
] /* considering_tmp_tables */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": {
"select#": 1,
"steps": [
{
"sorting_table": "t1",
"filesort_information": [
{
"direction": "asc",
"expression": "`t1`.`a`"
}
] /* filesort_information */,
"filesort_priority_queue_optimization": {
"usable": false,
"cause": "not applicable (no LIMIT)"
} /* filesort_priority_queue_optimization */,
"filesort_execution": [
] /* filesort_execution */,
"filesort_summary": {
"memory_available": 262144,
"key_size": 9,
"row_size": 32,
"max_rows_per_buffer": 90,
"num_rows_estimate": 90,
"num_rows_found": 90,
"num_initial_chunks_spilled_to_disk": 0,
"peak_memory_used": 33792,
"sort_algorithm": "std::sort",
"unpacked_addon_fields": "skip_heuristic",
"sort_mode": "<fixed_sort_key, additional_fields>"
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
}
] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 04 關(guān)閉trace功能文章來源地址http://www.zghlxwxcb.cn/news/detail-514399.html
set session optimizer_trace="enabled=off";到了這里，關(guān)于mysql 簡單定位慢查詢并分析SQL執(zhí)行效率的文章就介紹完了。如果您還想了解更多內(nèi)容，請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
