MySQL优化的步骤详解

       在开发过程中，虽然感觉优化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）！