MySQL优化的步骤详解
程序员文章站
2022-05-15 19:39:02
...
在开发过程中，虽然感觉优化sql语句很重要，但是往往更加重视的是功能实现，为了使自己以后写Mysql语句效率更高，有必要对Mysql优化做一个小小归纳。
步骤一、通过show status 命令了解各种sql执行的效率
show [session|gobal] status
session级别表示统计当前连接的结果。
global级别表示统计自数据上次启动至今的结果。
如果不写级别，默认的是session级别
eg：SHOW GLOBAL STATUS;
| Variable_name | Value |
| Aborted_clients | 6 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 0 |
| Bytes_received | 95645 |
| Bytes_sent | 1285066 |
| Com_admin_commands | 0 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Com_alter_db_upgrade | 0 |
| Com_alter_event | 0 |
| Com_alter_function | 0 |
| Com_alter_procedure | 0 |
| Com_alter_server | 0 |
| Com_alter_table | 6 |
| Com_alter_tablespace | 0 |
| Com_alter_user | 0 |
| Com_analyze | 0 |
| Com_begin | 0 |
| Com_binlog | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 8 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 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_server | 0 |
| Com_create_table | 5 |
| Com_create_trigger | 0 |
| Com_create_udf | 0 |
| Com_create_user | 0 |
| Com_create_view | 6 |
| Com_dealloc_sql | 0 |
| Com_delete | 2 |
| 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_server | 0 |
| Com_drop_table | 0 |
| Com_drop_trigger | 0 |
| Com_drop_user | 0 |
| Com_drop_view | 1 |
| Com_empty_query | 2 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_get_diagnostics | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 15 |
| Com_insert_select | 0 |
| Com_install_plugin | 0 |
| Com_kill | 0 |
| Com_load | 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_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_rollback_to_savepoint | 0 |
| Com_savepoint | 0 |
| Com_select | 414 |
| Com_set_option | 525 |
| 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 | 260 |
| Com_show_create_trigger | 0 |
| Com_show_databases | 8 |
| 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 | 102 |
| Com_show_function_code | 0 |
| Com_show_function_status | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 86 |
| 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 | 1 |
| Com_show_profile | 0 |
| Com_show_profiles | 115 |
| Com_show_relaylog_events | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 247 |
| Com_show_storage_engines | 0 |
| Com_show_table_status | 1 |
| Com_show_tables | 14 |
| Com_show_triggers | 5 |
| Com_show_variables | 5 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reprepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_uninstall_plugin | 0 |
| Com_unlock_tables | 0 |
| Com_update | 27 |
| 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 |
| Compression | ON |
| Connection_errors_accept | 0 |
| Connection_errors_internal | 0 |
| Connection_errors_max_connections | 0 |
| Connection_errors_peer_address | 0 |
| Connection_errors_select | 0 |
| Connection_errors_tcpwrap | 0 |
| Connections | 10 |
| Created_tmp_disk_tables | 128 |
| Created_tmp_files | 5 |
| Created_tmp_tables | 910 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 108 |
| Handler_delete | 2 |
| Handler_discover | 0 |
| Handler_external_lock | 782 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 73 |
| Handler_read_key | 2109 |
| Handler_read_last | 0 |
| Handler_read_next | 42 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 1882 |
| Handler_read_rnd_next | 94791 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 195 |
| Handler_write | 93316 |
| Innodb_buffer_pool_dump_status | not started |
| Innodb_buffer_pool_load_status | not started |
| Innodb_buffer_pool_pages_data | 397 |
| Innodb_buffer_pool_bytes_data | 6504448 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_bytes_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 193 |
| Innodb_buffer_pool_pages_free | 7795 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_total | 8192 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 0 |
| Innodb_buffer_pool_read_ahead_evicted | 0 |
| Innodb_buffer_pool_read_requests | 4642 |
| Innodb_buffer_pool_reads | 364 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 872 |
| Innodb_data_fsyncs | 129 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 6033408 |
| Innodb_data_reads | 402 |
| Innodb_data_writes | 281 |
| Innodb_data_written | 6534656 |
| Innodb_dblwr_pages_written | 193 |
| Innodb_dblwr_writes | 14 |
| Innodb_have_atomic_builtins | ON |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 574 |
| Innodb_log_writes | 46 |
| Innodb_os_log_fsyncs | 61 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 202752 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 34 |
| Innodb_pages_read | 363 |
| Innodb_pages_written | 193 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Innodb_rows_deleted | 0 |
| Innodb_rows_inserted | 3 |
| Innodb_rows_read | 406 |
| Innodb_rows_updated | 2 |
| Innodb_num_open_files | 32 |
| Innodb_truncated_status_writes | 0 |
| Innodb_available_undo_logs | 128 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 14344 |
| Key_blocks_used | 3 |
| Key_read_requests | 381 |
| Key_reads | 1 |
| Key_write_requests | 117 |
| Key_writes | 50 |
| Last_query_cost | 0.000000 |
| Last_query_partial_plans | 0 |
| Max_used_connections | 3 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 70 |
| Open_streams | 0 |
| Open_table_definitions | 120 |
| Open_tables | 117 |
| Opened_files | 1042 |
| Opened_table_definitions | 144 |
| Opened_tables | 147 |
| Performance_schema_accounts_lost | 0 |
| Performance_schema_cond_classes_lost | 0 |
| Performance_schema_cond_instances_lost | 0 |
| Performance_schema_digest_lost | 0 |
| Performance_schema_file_classes_lost | 0 |
| Performance_schema_file_handles_lost | 0 |
| Performance_schema_file_instances_lost | 0 |
| Performance_schema_hosts_lost | 0 |
| Performance_schema_locker_lost | 0 |
| Performance_schema_mutex_classes_lost | 0 |
| Performance_schema_mutex_instances_lost | 0 |
| Performance_schema_rwlock_classes_lost | 0 |
| Performance_schema_rwlock_instances_lost | 0 |
| Performance_schema_session_connect_attrs_lost | 0 |
| Performance_schema_socket_classes_lost | 0 |
| Performance_schema_socket_instances_lost | 0 |
| Performance_schema_stage_classes_lost | 0 |
| Performance_schema_statement_classes_lost | 0 |
| Performance_schema_table_handles_lost | 0 |
| Performance_schema_table_instances_lost | 0 |
| Performance_schema_thread_classes_lost | 0 |
| Performance_schema_thread_instances_lost | 0 |
| Performance_schema_users_lost | 0 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1039896 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 404 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
| Queries | 1888 |
| Questions | 1887 |
| Select_full_join | 1 |
| Select_full_range_join | 0 |
| Select_range | 23 |
| Select_range_check | 0 |
| Select_scan | 727 |
| Slave_heartbeat_period | 0.000 |
| Slave_last_heartbeat | |
| Slave_open_temp_tables | 0 |
| Slave_received_heartbeats | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 1964 |
| Sort_scan | 151 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_server_not_after | |
| Ssl_server_not_before | |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 386 |
| Table_locks_waited | 0 |
| Table_open_cache_hits | 656 |
| Table_open_cache_misses | 130 |
| Table_open_cache_overflows | 0 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 1 |
| Threads_connected | 2 |
| Threads_created | 3 |
| Threads_running | 1 |
| Uptime | 286258 |
| Uptime_since_flush_status | 286258 |
主要参数描述
Connections：视图连接mysql服务器的次数
Uptime：服务器工作时间
Slow_queries:慢查询的次数
Com_xxx表示每个xxx语句执行的次数
Com_select 执行select次数
Com_insert 执行insert次数，批量插入时候，只累加1次
Com_update 执行Update操作次数
Com_delete 执行删除操作次数
innodb_rows_xxx类型的参数只对innodb存储引擎有效
解析：通过上面一些参数，可以容易了解当前数据库的应用时插入更新为主还是查询为主，以及执行比例。对应更新操作的计数，是对执行次数的计数，不论提交还是回滚都会累加。
步骤二：定位执行效率较低的sql语句
通过慢查询日志定位哪些是执行效率的sql语句。用--log-slow-queries[=file_name]选项启动，mysqld写一个包含所有执行超过long_query_time秒的sql语句的日志文件。关于如何定位慢查询可以点解这里（http://www.php.cn/）
步骤三：通过EXPLAIN分析低效率SQL的执行计划
在步骤二中，我们可以查询到低效率的sql语句，在此我们通过explain或desc可以获取mysql执行select信息。
eg:
EXPLAIN
SELECT
t0.*
FROM
t3 AS t0
LEFT JOIN `t2` AS t1
ON t0.`id1` = t1.`id1`
WHERE t0.id1 = 5 ;
执行结果如下：
解析：select_type:表示select类型。常见的取值有SIMPLE（简单表，即不使用连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（union中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个SELECT）等。
talbe：输出结果集的表。
type:表的连接类型。性能由高到底：system（表中仅有一行）、const（表中最多有一个匹配行）、eq_ref、ref、ref_null、index_merge、unique_subquery、index_subquery、range、idnex等
possible_keys:查询时，可能使用的索引
key:实际使用的索引
key_len:索引字段的长度
rows：扫描行的数量
Extra：执行情况的说明和描述
步骤四：确定问题并采取相应的优化措施
经过上面的步骤，可以确定问题出现的原因，此时我们可以根据情况，采取相应的措施。常见的措施有1.建立相应的索引2.优化sql语句3.分表等。
备注：如果索引正在工作，handler_read_key的值将很高，这个值代表了一个行被索引值读的次数，很低的值表名增加索引得到的性能改善不高，因为索引并不经常使用。handler_read_rnd_next值高则意味着查询运行低效，并且应该建立索引补救。如果正在进行大量的表扫描，handler_read_rnd_next值较高，则通常说明表索引不正确或写入的查询没有利用索引，如下图。
我们应当定期分析表盒检查表
检查表使用如下命令（检查t3表）
ANALYZE TABLE t3;
CHECK TABLE t3;
定期优化表使用命令如下
optimize table 表名
常见语句优化
1.优化Insert语句
（1）如果从同一个客户端插入数据，尽力使用多个字表的insert语句和多行插入，减少单行插入，这种方式大大减少客户端与数据库直接的连接、关闭等消耗。eg：
INSERT INTO t3 VALUES(1,2),(8,5),(6,5),(4,3)
（2）如果从不同的客户插入很多行，能通过使用inset delayed语句得到更高的速度。
（3）如果进行批量插入，可以增加bulk_insert_buffer_size变量方法，提高速度。
2.优化group by语句
默认情况下使用group by col1,col2....会对查询进行相应的排序，如果用户想要避免排序结果的消耗，可以指定order by null 禁止排序。通过查询结果中extra字段可以看出：
3.优化order by语句
在某些情况中，mysql可以使用一个索引来满足order by子句，而不需要额外的排序，where条件和order by使用相同的索引。
4.优化含有or语句
对于含有or的查询子句，如果要使用索引，则or之间的每个条件列必须用到索引；否则，应该考虑添加索引。
5.使用sql提示
以上就是MySQL优化的步骤详解的内容，更多相关内容请关注PHP中文网（www.php.cn）！