There are four main ways to analyze database performance in MySQL, namely slow query log, profile, Com_xxx and explain.
Slow query log
First use the following command to check whether the slow query log is enabled.
show variables like 'slow_query_log';
# Generally, the following results are the default
+---------------------+
| Variable_name | Value |
+---------------------+
| slow_query_log | OFF |
+---------------------+- If the result is
ON, means that the slow query log has been enabled; ifOFF, it needs to be turned on manually. However, generally, the slow query log is not enabled by default and needs to be enabled manually because the indicator needs to be specified, that is, how slow SQL is considered slow SQL.
Temporarily enabled (invalid after restarting MySQL):
# Enable slow query log
set global slow_query_log = 'ON';
# Set a time. Queries that exceed this time will be considered slow queries and will be recorded in the slow query log, with the unit being seconds (s)
set global long_query_time = 2;Permanently open:
In the Linux environment, you only need to change the /etc/ configuration file and add the following two lines of configuration to it.
# 0: Turn off the slow query log 1: Turn on the slow query log
slow_query_log = 1
# Specify the log file path (optional, if not selected, there will be a default path)
slow_query_log_file = /var/log/mysql/
# Set a time. Queries that exceed this time will be considered slow queries and will be recorded in the slow query log, with the unit being seconds (s)
long_query_time = 2
# Whether to record queries that have not used indexes (1 means on, 0 means off, and default is off)
log_queries_not_using_indexes = 1The key is that the parameters [slow_query_log] and [long_query_time] must be set. After the configuration is completed and saved, then use [systemctl restart mysqld] to restart MySQL on the Linux command line. At this time, the slow query log will be recorded in the file. If there is no configuration path and the default path is used, you can query the file location:
SHOW VARIABLES LIKE 'slow_query_log_file';
# The result may be as follows
+---------------------+-------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------+
| slow_query_log_file | /var/lib/mysql/ |
+---------------------+-------------------------------+Then go to the specified directory and view the log file directly.
profile
Use the following command to check whether the profile is enabled
show variables like 'profiling';
# The default is turned off, the general query results are as follows
+--------------------+
| Variable_name | Value |
+--------------------+
| profiling | OFF |
+--------------------+Need to be turned on manually.
Temporarily enable profiling (if it is invalid after restarting MySQL):
Set parameters in SQL execution window
set profiling = 1;
Permanently open:
Add the following configuration to the /etc/ file
profiling = 1
Remember to restart mysql after modifying the /etc/ file.
At this time, execute a few sqls and then query the profile.
# At this time, I created a table for testing
# Execute the following queries
select * from test where id = 2;
select * from test where id = 1;
select * from test;
# Execute the downlink statement and query the Query record
show profiles;
# Get the following result, the Query column is a query statement, Duration is the time consumed for execution, and Query_ID is the record 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 |
+----------+------------+------------------------------------+
# If you want to analyze a certain SQL, for example, the record with Query_ID 1 takes the longest time, if you want to see the specific situation, you can use the following command
show profile for query 1;
# Get the following results
+----------------------------------------+
| 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 |
| optimization | 0.000009 |
| statistics | 0.000045 |
| preparation | 0.000011 |
| execution | 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 |
+----------------------------------------+
# You can see parameters such as start time, execution time, opening table, optimization time, preparation time, closing table, etc.
# If SQL query is very slow, you can analyze the reasons from hereCom_%
# Execute the following command
show status like 'Com_%';
# The result format is as follows
+----------------------------------------+
| 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 |
+----------------------------------------+
# Focus on the values of 4 parameters, Com_insert, Com_delete, Com_update, Com_select parameters. Because I did not perform the addition, deletion and modification operations, it was all 0. I just checked the records several times. The Com_select here has reached 8, which means that 8 select operations have been performed, 0 insert, 0 delete, and 0 updates have been performed.When it is necessary to analyze whether the addition, deletion, modification and query operations are more added, deletion, modification or query, you can use this method to query the execution of relevant records, and analyze whether a certain business has more queries or updates, so that the system architecture can be better controlled.
explain
# Analyze the execution plan for the SQL that needs to be executed. If you want to analyze the following query statement
select * from tb_user where id=1;
# The syntax is as follows
explain select * from test where id=1;
# In fact, it is to add the explanation keyword, insert, update and delete statement before the query statement to perform analysis and execution plan.
# The result format is as follows
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 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 |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
# Need to pay attention to the id column
# The same id means the same query block.
# The larger the id, the higher the execution priority.
# id is NULL indicates that it is a merge operation of UNION results.
# -----------------------------------------------------
# You need to pay attention to the type column, where the introduction and performance of the type column are as follows (performance is arranged from high to low)
# NULL: Direct query, no table operations, such as select 1 + 1;
# system: There is only one row of data in the table (system table).
# const: Find a row of data by primary key or unique index.
# eq_ref: Associate tables through unique indexes (When multi-table JOIN, each row only matches one row).
# ref: Find data through non-unique index.
# range: Use index range scan.
# index: Full index scan (scan the index tree, no data rows are accessed).
# ALL: Full table scan (worst performance).
# -----------------------------------------------------
# You need to pay attention to possible_keys columns and key columns
# possible_keys represents the index that may be used, and key is the index that is actually used. From here, you can analyze whether the index is useless or invalid.
# When optimizing, try to make the statements that do not use the index to use the index.
# -----------------------------------------------------
# Need to follow key_len
# If a single column index is used, key_len is a fixed value
# If a joint index is used, the value of key_len may cause the value of key_len to be different due to partial index failure. This column can be used to determine whether the joint index is fully valid.
# -----------------------------------------------------
# You need to pay attention to the rows column, which records the number of rows that MySQL estimates to scan.
# The fewer rows, the better the performance. If the value is large, you may need to optimize the index or query conditions.
# -----------------------------------------------------
# Need to pay attention to filtered column
# filtered= Number of rows filtered by the Server layer/number of rows returned by the storage engine layer ×100%
# The smaller the value, the more data that does not meet the conditions is filtered, and the Server layer only needs to process a small amount of data.
# -----------------------------------------------------
# Focus on Extra columns, where possible values are as follows:
# Using where: The WHERE condition is used to filter the data.
# Using index: Overwrite index is used (no table return required).
# Using temporary tables (poor performance).
# Using filesort: Used file sorting (poor performance).
# Using join buffer: The JOIN buffer is used (when multi-table JOIN).
# Impossible WHERE: The WHERE condition is always false (no result).
# Pay attention to avoid using temporary and Using filesort as much as possible, as well as Impossible WHERE.