MySQL中怎么分析性能？
MySQL中主要有4種方式可以分析數(shù)據(jù)庫性能，分別是慢查詢?nèi)罩?，profile，Com_xxx和explain。
慢查詢?nèi)罩?/h3>
先用下面命令查詢慢查詢?nèi)罩臼欠耖_啟，
show variables like 'slow_query_log';
# 一般默認(rèn)都是以下結(jié)果
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | OFF |
+----------------+-------+
- 若結(jié)果為
ON，表示慢查詢?nèi)罩疽验_啟；若為OFF，則需要手動(dòng)開啟。但是一般慢查詢?nèi)罩臼悄J(rèn)不開啟的，需要手動(dòng)開啟，因?yàn)樾枰付ㄖ笜?biāo)，也就是多慢的SQL才算慢SQL。
臨時(shí)開啟（重啟 MySQL 后失效）：
# 開啟慢查詢?nèi)罩?set global slow_query_log = 'ON';
# 設(shè)置一個(gè)時(shí)間，超過這個(gè)時(shí)間的查詢都會(huì)被認(rèn)為是慢查詢，會(huì)記錄到慢查詢?nèi)罩纠?，單位是秒（s）
set global long_query_time = 2;
永久開啟：
linux環(huán)境下只需要改一下/etc/my.cnf配置文件，在里面加入如下兩行配置
# 0:關(guān)閉慢查詢?nèi)罩?1:開啟慢查詢?nèi)罩?slow_query_log = 1
# 指定日志文件路徑（可選，不選則有默認(rèn)路徑）
slow_query_log_file = /var/log/mysql/slow.log
# 設(shè)置一個(gè)時(shí)間，超過這個(gè)時(shí)間的查詢都會(huì)被認(rèn)為是慢查詢，會(huì)記錄到慢查詢?nèi)罩纠?，單位是秒（s）
long_query_time = 2
# 是否記錄未使用索引的查詢（1表示開啟，0表示關(guān)閉，默認(rèn)關(guān)閉）
log_queries_not_using_indexes = 1
關(guān)鍵是參數(shù)【slow_query_log】和【long_query_time】一定要設(shè)置，配置完畢保存后然后使用【systemctl restart mysqld】在Linux命令行重啟MySQL即可。此時(shí)慢查詢的日志會(huì)記錄到文件里，如果沒有配置路徑，使用到了默認(rèn)路徑，可以查詢一下文件位置:
SHOW VARIABLES LIKE 'slow_query_log_file';
# 得到結(jié)果可能如下
+---------------------+-------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------+
| slow_query_log_file | /var/lib/mysql/hostname-slow.log |
+---------------------+-------------------------------+
然后去指定目錄直接查看log文件即可。
profile
使用下列命令查看profiling是否開啟
show variables like 'profiling';
# 默認(rèn)是關(guān)閉的，一般查詢結(jié)果如下
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | OFF |
+---------------+-------+
需要手動(dòng)開啟。
臨時(shí)開啟profiling（重啟 MySQL 后失效）：
在SQL執(zhí)行窗口設(shè)定參數(shù)
set profiling = 1;
永久開啟：
在/etc/my.cnf文件中加入如下配置
profiling = 1
要記得修改過/etc/my.cnf文件以后要重啟mysql。
此時(shí)隨便執(zhí)行幾條sql，然后再來查詢一下profile。
# 此時(shí)為了測試我創(chuàng)建了一個(gè)表
# 執(zhí)行下面幾條查詢
select * from test where id = 2;
select * from test where id = 1;
select * from test;
# 執(zhí)行下行語句，查詢Query記錄
show profiles;
# 得到如下結(jié)果，Query列是查詢語句，Duration是執(zhí)行消耗的時(shí)間，Query_ID是記錄ID
+----------+------------+------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+------------------------------------+
| 1 | 0.00029275 | select * from test where id = 2 |
| 2 | 0.00022375 | select * from test where id = 1 |
| 3 | 0.00020425 | select * from test |
+----------+------------+------------------------------------+
# 如果想要對某一條SQL進(jìn)行分析，比如這里Query_ID為1的記錄消耗時(shí)間最長，想要看一下具體情況，可以使用如下命令
show profile for query 1;
# 得到如下結(jié)果
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000115 |
| Executing hook on transaction | 0.000008 |
| starting | 0.000009 |
| checking permissions | 0.000005 |
| Opening tables | 0.000037 |
| init | 0.000004 |
| System lock | 0.000007 |
| optimizing | 0.000009 |
| statistics | 0.000045 |
| preparing | 0.000011 |
| executing | 0.000009 |
| end | 0.000002 |
| query end | 0.000002 |
| waiting for handler commit | 0.000007 |
| closing tables | 0.000007 |
| freeing items | 0.000010 |
| cleaning up | 0.000007 |
+--------------------------------+----------+
# 可以看到開始時(shí)間，執(zhí)行時(shí)間，打開表的時(shí)間，優(yōu)化時(shí)間，準(zhǔn)備時(shí)間，關(guān)閉表的時(shí)間等參數(shù)
# 如果SQL查詢很慢的話則可以從這里分析原因
Com_%
# 執(zhí)行下列命令
show status like 'Com_%';
# 得到結(jié)果格式如下
+-------------------------------------+-------+
| Variable_name | Value |
+-------------------------------------+-------+
| Com_admin_commands | 0 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Com_alter_event | 0 |
| Com_alter_function | 0 |
| Com_alter_instance | 0 |
| Com_alter_procedure | 0 |
| Com_alter_resource_group | 0 |
| Com_alter_server | 0 |
| Com_alter_table | 0 |
| Com_alter_tablespace | 0 |
| Com_alter_user | 0 |
| Com_alter_user_default_role | 0 |
| Com_analyze | 0 |
| Com_begin | 0 |
| Com_binlog | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 1 |
| Com_change_master | 0 |
| Com_change_repl_filter | 0 |
| Com_change_replication_source | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_clone | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_event | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_procedure | 0 |
| Com_create_role | 0 |
| Com_create_server | 0 |
| Com_create_table | 0 |
| Com_create_resource_group | 0 |
| Com_create_trigger | 0 |
| Com_create_udf | 0 |
| Com_create_user | 0 |
| Com_create_view | 0 |
| Com_create_spatial_reference_system | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 0 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_event | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_procedure | 0 |
| Com_drop_resource_group | 0 |
| Com_drop_role | 0 |
| Com_drop_server | 0 |
| Com_drop_spatial_reference_system | 0 |
| Com_drop_table | 0 |
| Com_drop_trigger | 0 |
| Com_drop_user | 0 |
| Com_drop_view | 0 |
| Com_empty_query | 0 |
| Com_execute_sql | 0 |
| Com_explain_other | 0 |
| Com_flush | 0 |
| Com_get_diagnostics | 0 |
| Com_grant | 0 |
| Com_grant_roles | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_import | 0 |
| Com_insert | 0 |
| Com_insert_select | 0 |
| Com_install_component | 0 |
| Com_install_plugin | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_lock_instance | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_release_savepoint | 0 |
| Com_rename_table | 0 |
| Com_rename_user | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_resignal | 0 |
| Com_restart | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_revoke_roles | 0 |
| Com_rollback | 0 |
| Com_rollback_to_savepoint | 0 |
| Com_savepoint | 0 |
| Com_select | 8 |
| Com_set_option | 1 |
| Com_set_password | 0 |
| Com_set_resource_group | 0 |
| Com_set_role | 0 |
| Com_signal | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_create_db | 0 |
| Com_show_create_event | 0 |
| Com_show_create_func | 0 |
| Com_show_create_proc | 0 |
| Com_show_create_table | 0 |
| Com_show_create_trigger | 0 |
| Com_show_databases | 2 |
| Com_show_engine_logs | 0 |
| Com_show_engine_mutex | 0 |
| Com_show_engine_status | 0 |
| Com_show_events | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 1 |
| Com_show_function_code | 0 |
| Com_show_function_status | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_master_status | 0 |
| Com_show_open_tables | 0 |
| Com_show_plugins | 0 |
| Com_show_privileges | 0 |
| Com_show_procedure_code | 0 |
| Com_show_procedure_status | 0 |
| Com_show_processlist | 0 |
| Com_show_profile | 5 |
| Com_show_profiles | 1 |
| Com_show_relaylog_events | 0 |
| Com_show_replicas | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_replica_status | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 2 |
| Com_show_storage_engines | 0 |
| Com_show_table_status | 0 |
| Com_show_tables | 2 |
| Com_show_triggers | 0 |
| Com_show_variables | 3 |
| Com_show_warnings | 0 |
| Com_show_create_user | 0 |
| Com_shutdown | 0 |
| Com_replica_start | 0 |
| Com_slave_start | 0 |
| Com_replica_stop | 0 |
| Com_slave_stop | 0 |
| Com_group_replication_start | 0 |
| Com_group_replication_stop | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_close | 0 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_uninstall_component | 0 |
| Com_uninstall_plugin | 0 |
| Com_unlock_instance | 0 |
| Com_unlock_tables | 0 |
| Com_update | 0 |
| Com_update_multi | 0 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Com_stmt_reprepare | 0 |
| Compression | OFF |
| Compression_algorithm | |
| Compression_level | 0 |
+-------------------------------------+-------+
# 重點(diǎn)查看4個(gè)參數(shù)的值，Com_insert,Com_delete,Com_update,Com_select的參數(shù)。因?yàn)槲覜]有執(zhí)行增刪改操作，所以都是0，剛剛又查詢了幾次記錄，這邊的Com_select已經(jīng)到8了，代表當(dāng)前已經(jīng)執(zhí)行過8次select操作，0次insert，0次delete，0次update。
在需要分析增刪改查操作到底是增刪改比較多還是查詢比較多的時(shí)候可以使用這個(gè)方式查詢相關(guān)記錄的執(zhí)行情況，分析某個(gè)業(yè)務(wù)到底是查詢比較多呢還是更新比較多，從而可以更好地對系統(tǒng)架構(gòu)進(jìn)行把控。
explain
# 對需要執(zhí)行的sql分析執(zhí)行計(jì)劃，假如要分析下面這條查詢語句
select * from tb_user where id=1;
# 語法如下
explain select * from test where id=1;
# 其實(shí)就是在查詢語句前加上explain關(guān)鍵字，insert，update和delete語句前也可以加上進(jìn)行分析執(zhí)行計(jì)劃
# 得到結(jié)果格式如下
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | tb_user | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
# 需要關(guān)注id列
# 相同的 id 表示同一查詢塊。
# id 越大，執(zhí)行優(yōu)先級越高。
# id 為 NULL 表示是 UNION 結(jié)果的合并操作。
# -----------------------------------------------------
# 需要關(guān)注type列，其中type列的介紹和性能如下（性能從高到低排列）
# NULL：直接查詢，不進(jìn)行表操作，例如select 1 + 1;
# system：表中只有一行數(shù)據(jù)（系統(tǒng)表）。
# const：通過主鍵或唯一索引查找一行數(shù)據(jù)。
# eq_ref：通過唯一索引關(guān)聯(lián)表（多表 JOIN 時(shí)，每行只匹配一行）。
# ref：通過非唯一索引查找數(shù)據(jù)。
# range：使用索引范圍掃描。
# index：全索引掃描（掃描索引樹，不訪問數(shù)據(jù)行）。
# ALL：全表掃描（性能最差）。
# -----------------------------------------------------
# 需要關(guān)注possible_keys列和key列
# possible_keys代表可能用到的索引，key就是實(shí)際用到的索引，從這里可以分析索引是不是沒有用到或者失效了
# 優(yōu)化的時(shí)候要盡量讓沒有使用到索引的語句使用索引
# -----------------------------------------------------
# 需要關(guān)注key_len
# 如果用到了單列索引，則key_len是一個(gè)固定值
# 如果用到了聯(lián)合索引，key_len的值可能會(huì)因?yàn)椴糠炙饕Ф鴮?dǎo)致key_len的值不一樣，可以通過這一列判斷聯(lián)合索引是否全部生效。
# -----------------------------------------------------
# 需要關(guān)注rows列,記錄的是MySQL預(yù)估需要掃描的行數(shù)。
# 行數(shù)越少，性能越好,如果值很大，可能需要優(yōu)化索引或查詢條件。
# -----------------------------------------------------
# 需要關(guān)注filtered列
# filtered= Server層過濾后的行數(shù)/存儲(chǔ)引擎層返回的行數(shù) ×100%
# 值越小，說明存儲(chǔ)引擎層已經(jīng)過濾了更多不滿足條件的數(shù)據(jù)，Server 層只需處理少量數(shù)據(jù)。
# -----------------------------------------------------
# 重點(diǎn)關(guān)注Extra列，其中可能出現(xiàn)的值如下：
# Using where：使用了 WHERE 條件過濾數(shù)據(jù)。
# Using index：使用了覆蓋索引（無需回表）。
# Using temporary：使用了臨時(shí)表（性能較差）。
# Using filesort：使用了文件排序（性能較差）。
# Using join buffer：使用了 JOIN 緩沖區(qū)（多表 JOIN 時(shí)）。
# Impossible WHERE：WHERE 條件永遠(yuǎn)為假（無結(jié)果）。
# 需要注意盡可能避免Using temporary和Using filesort，以及Impossible WHERE。
美的集團(tuán)公司福利 432人發(fā)布
投遞拼多多集團(tuán)-PDD等公司10個(gè)崗位