MySql之慢Sql定位分析
定位低效率執(zhí)行SQL
可以通過以下兩種方式定位執(zhí)行效率較低的SQL語句。
慢查詢?nèi)罩?通過慢查詢?nèi)罩径ㄎ荒切﹫?zhí)行效率較低的SQL語句,用- log-slow-queries[= file name]選項(xiàng)啟動(dòng)時(shí), mysqld是一個(gè)包含所有執(zhí)行時(shí)間超過 long_query_time秒的sql請(qǐng)句的日志文件。
show processlist:慢查詢?nèi)罩驹诓樵兘Y(jié)束以后才紀(jì)錄,所以在應(yīng)用反映執(zhí)行效率出現(xiàn)問題的時(shí)候查詢慢查詢?nèi)罩静⒉荒芏ㄎ粏栴},可以使用show processlist命令查看當(dāng)前 MySQL在進(jìn)行的線程,包括線程的狀態(tài)、是否鎖表等,可以實(shí)時(shí)地查看SQL的執(zhí)行情況,同時(shí)對(duì)一些鎖表操作進(jìn)行優(yōu)化。(info列，顯示的就是問題sql語句)
explain分析執(zhí)行計(jì)劃
通過以上步驟定位到效率低的SQL語句后，可以通過explain關(guān)鍵字獲取SQL語句的執(zhí)行計(jì)劃，描述的是SQL將以何種方式去執(zhí)行，用法非常簡(jiǎn)單，就是直接加在SQL之前。
查詢sql語句的執(zhí)行計(jì)劃：
explain select * from emp
執(zhí)行結(jié)果
mysql> explain select * from emp;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.01 sec)
explain字段
id
一系列數(shù)字，表示SQL語句執(zhí)行的序列號(hào)，代表了操作的順序。id情況有三種：
id相同，加載表的順序是從上到下
id不同，id值越大，優(yōu)先級(jí)越高，越先被執(zhí)行
id有相同，也有不同，同時(shí)存在。id相同的可以認(rèn)為是一組，從上往下順序執(zhí)行。在所有的組中，id的值越大，優(yōu)先級(jí)越高，越先執(zhí)行
select_type
主要是用來區(qū)分查詢的類型，是普通查詢、連接查詢、還是子查詢。值對(duì)應(yīng)的解釋如下
從上往下，效率越來越低。
table
表示正在訪問哪一張表，是表名或者別名。也有可能是臨時(shí)表或者union的結(jié)果集
type
描述如何聯(lián)接表，表示SQL語句以何種方式去訪問表，找到對(duì)應(yīng)的數(shù)據(jù)。訪問類型有很多，效率從高到低，分別為
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般情況下，得保證查詢至少達(dá)到range級(jí)別，最好能達(dá)到ref
key
possible_keys：顯示可能應(yīng)用在這張表的索引，一個(gè)或者多個(gè)。
key：實(shí)際使用的索引，如果為null，則沒有使用索引。
key_len：表示索引中使用的字節(jié)數(shù)，在滿足需求的情況下，值越小越好。
rows
該SQL語句需要訪問的大致行數(shù)，是一個(gè)估計(jì)值。但是這個(gè)值非常重要，在滿足需求的情況下，越小越好。
extra
其它的額外的執(zhí)行計(jì)劃信息，在該列展示
以上便是explain關(guān)鍵字的使用方式以及含義，這個(gè)關(guān)鍵字的作用主要用來分析索引使用情況。
需要了解的是：使用explain關(guān)鍵字進(jìn)行分析時(shí)，SQL語句并不會(huì)執(zhí)行。只是模擬MySQL優(yōu)化器的執(zhí)行過程，所以用explain查看的結(jié)果是叫執(zhí)行計(jì)劃。
show profile
explain關(guān)鍵字主要用來定性分析索引的使用情況，以及SQL語句的優(yōu)劣，但是無法知道SQL語句的實(shí)際執(zhí)行情況。而show profile命令可以做到定量分析SQL語句的執(zhí)行情況。即使用者可以明確知道一條SQL到底執(zhí)行了多久。
通過have_profiling參數(shù)，能夠看到當(dāng)前MySql是否支持profile：
SELECT @@have_profiling;
默認(rèn)profiling是關(guān)閉的，可以通過set語句在session級(jí)別開啟profiling：
set profiling=1;
接著執(zhí)行多條SQL語句
select * from emp;
select * from dept;
執(zhí)行結(jié)果不重要，主要關(guān)注各個(gè)SQL語句的執(zhí)行時(shí)間
接下來再執(zhí)行如下語句，顯示統(tǒng)計(jì)成功的SQL語句
show profiles;
執(zhí)行結(jié)果
mysql> show profiles;
+----------+------------+--------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------+
| 1 | 0.00065025 | select * from emp |
| 2 | 0.00626150 | select * from dept |
+----------+------------+--------------------+
2 rows in set, 1 warning (0.00 sec)
可以看到MySQL已經(jīng)統(tǒng)計(jì)了上面執(zhí)行的兩條SQL語句
如果想具體查看SQL語句各個(gè)步驟的詳細(xì)耗時(shí)，接著執(zhí)行如下SQL語句 查
看第二條SQL語句執(zhí)行耗時(shí)的詳細(xì)信息
show profile for query 2
執(zhí)行結(jié)果
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000164 |
| checking permissions | 0.000054 |
| Opening tables | 0.004434 |
| init | 0.000037 |
| System lock | 0.000013 |
| optimizing | 0.000007 |
| statistics | 0.000013 |
| preparing | 0.000014 |
| executing | 0.000004 |
| Sending data | 0.001350 |
| end | 0.000013 |
| query end | 0.000007 |
| closing tables | 0.000012 |
| freeing items | 0.000123 |
| cleaning up | 0.000018 |
+----------------------+----------+
15 rows in set, 1 warning (0.03 sec)
執(zhí)行結(jié)果展示個(gè)各個(gè)步驟以及持續(xù)的時(shí)間。
show profile語法
show profile完整的語法如下：
SHOW PROFILE [type [, type] ... ]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]
type: {
ALL
| BLOCK IO
| CONTEXT SWITCHES
| CPU
| IPC
| MEMORY
| PAGE FAULTS
| SOURCE
| SWAPS
}
各個(gè)type對(duì)應(yīng)的信息如下
也就是說除了各個(gè)步驟持續(xù)的時(shí)間，還可以看到BLOCK IO、CPU等信息，具體用法如下：
show profile block io, cpu for query 2
執(zhí)行結(jié)果：
+----------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000164 | 0.000000 | 0.000000 | NULL | NULL |
| checking permissions | 0.000054 | 0.000000 | 0.000000 | NULL | NULL |
| Opening tables | 0.004434 | 0.000000 | 0.000000 | NULL | NULL |
| init | 0.000037 | 0.000000 | 0.000000 | NULL | NULL |
| System lock | 0.000013 | 0.000000 | 0.000000 | NULL | NULL |
| optimizing | 0.000007 | 0.000000 | 0.000000 | NULL | NULL |
| statistics | 0.000013 | 0.000000 | 0.000000 | NULL | NULL |
| preparing | 0.000014 | 0.000000 | 0.000000 | NULL | NULL |
| executing | 0.000004 | 0.000000 | 0.000000 | NULL | NULL |
| Sending data | 0.001350 | 0.000000 | 0.000000 | NULL | NULL |
| end | 0.000013 | 0.000000 | 0.000000 | NULL | NULL |
| query end | 0.000007 | 0.000000 | 0.000000 | NULL | NULL |
| closing tables | 0.000012 | 0.000000 | 0.000000 | NULL | NULL |
| freeing items | 0.000123 | 0.000000 | 0.000000 | NULL | NULL |
| cleaning up | 0.000018 | 0.000000 | 0.000000 | NULL | NULL |
+----------------------+----------+----------+------------+--------------+---------------+
15 rows in set, 1 warning (0.00 sec)
補(bǔ)充
需要注意的是，show profile方式將從5.6.7開始不推薦使用，并且在以后的版本中會(huì)刪除，改用Performance Schema
Trace分析優(yōu)化器執(zhí)行計(jì)劃
MySQL5.6提供了對(duì)SQL的跟蹤trace，通過trace文件能夠進(jìn)一步了解為什么優(yōu)化器選擇A執(zhí)行計(jì)劃而不是選擇B執(zhí)行計(jì)劃，幫助我們更好地理解優(yōu)化器行為。
使用方式：首先打開trace，設(shè)置格式為JSON，設(shè)置trace最大能夠使用的內(nèi)存大小，避免解析過程中因?yàn)槟J(rèn)內(nèi)存過小而不能夠完整顯示。
mysql> SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;
Query OK, 0 rows affected (0.01 sec)
mysql> SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;
Query OK, 0 rows affected (0.02 sec)
執(zhí)行sql語句：
SELECT * from attach_info where id <4
最后，檢查INFORMATION_SCHEMA.OPTIMIZER_TRACE 就可以知道MYSQL是如何執(zhí)行SQL的：文章來源：http://www.zghlxwxcb.cn/news/detail-628039.html
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
最后會(huì)輸出一個(gè)格式如下的跟蹤文件：文章來源地址http://www.zghlxwxcb.cn/news/detail-628039.html
-----------------------------+-----------------------------------+-------------------------+
| select rental_id from rental where 1=1 and rental_date >='2005-05-25 04:00:00'
and rental_date <='2005-05-25 05:00:00'and inventory_id=4466 | {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `rental`.`rental_id` AS `rental_id` from `rental` where ((1 = 1) and (`rental`.`rental_date` >= '2005-05-25 04:00:00') and (`rental`.`rental_date` <= '2005-05-25 05:00:00') and (`rental`.`inventory_id` = 4466))"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((1 = 1) and (`rental`.`rental_date` >= '2005-05-25 04:00:00') and (`rental`.`rental_date` <= '2005-05-25 05:00:00') and (`rental`.`inventory_id` = 4466))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "((1 = 1) and (`rental`.`rental_date` >= '2005-05-25 04:00:00') and (`rental`.`rental_date` <= '2005-05-25 05:00:00') and multiple equal(4466, `rental`.`inventory_id`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "((1 = 1) and (`rental`.`rental_date` >= '2005-05-25 04:00:00') and (`rental`.`rental_date` <= '2005-05-25 05:00:00') and multiple equal(4466, `rental`.`inventory_id`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "((`rental`.`rental_date` >= '2005-05-25 04:00:00') and (`rental`.`rental_date` <= '2005-05-25 05:00:00') and multiple equal(4466, `rental`.`inventory_id`))"
}
] /* steps */
} /* condition_processing */
},
{
"substitute_generated_columns": {
} /* substitute_generated_columns */
},
{
"table_dependencies": [
{
"table": "`rental`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [
{
"table": "`rental`",
"field": "inventory_id",
"equals": "4466",
"null_rejecting": false
}
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [
{
"table": "`rental`",
"range_analysis": {
"table_scan": {
"rows": 16008,
"cost": 1667.4
} /* table_scan */,
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "rental_date",
"usable": true,
"key_parts": [
"rental_date",
"inventory_id",
"customer_id"
] /* key_parts */
},
{
"index": "idx_fk_inventory_id",
"usable": true,
"key_parts": [
"inventory_id",
"rental_id"
] /* key_parts */
},
{
"index": "idx_fk_customer_id",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_fk_staff_id",
"usable": false,
"cause": "not_applicable"
}
] /* potential_range_indexes */,
"best_covering_index_scan": {
"index": "rental_date",
"cost": 1607.9,
"chosen": true
} /* best_covering_index_scan */,
"setup_range_conditions": [
] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
} /* group_index_range */,
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "rental_date",
"ranges": [
"0x9975b24000 <= rental_date <= 0x9975b25000"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": true,
"rows": 10,
"cost": 1.2638,
"chosen": true
},
{
"index": "idx_fk_inventory_id",
"ranges": [
"4466 <= inventory_id <= 4466"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 5,
"cost": 4.4994,
"chosen": false,
"cause": "cost"
}
] /* 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": "rental_date",
"rows": 10,
"ranges": [
"0x9975b24000 <= rental_date <= 0x9975b25000"
] /* ranges */
} /* range_access_plan */,
"rows_for_plan": 10,
"cost_for_plan": 1.2638,
"chosen": true
} /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`rental`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "idx_fk_inventory_id",
"rows": 5,
"cost": 3.8245,
"chosen": true
},
{
"rows_to_scan": 10,
"access_type": "range",
"range_details": {
"used_index": "rental_date"
} /* range_details */,
"resulting_rows": 10,
"cost": 2.2638,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 10,
"cost_for_plan": 2.2638,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`rental`.`inventory_id` = 4466) and (`rental`.`rental_date` >= '2005-05-25 04:00:00') and (`rental`.`rental_date` <= '2005-05-25 05:00:00'))",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`rental`",
"attached": "((`rental`.`inventory_id` = 4466) and (`rental`.`rental_date` >= '2005-05-25 04:00:00') and (`rental`.`rental_date` <= '2005-05-25 05:00:00'))"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"refine_plan": [
{
"table": "`rental`"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": {
"select#": 1,
"steps": [
] /* steps */
} /* join_execution */
}
] /* steps */
} | 0 | 0 |
到了這里，關(guān)于MySql之慢Sql定位分析的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
