MySQL优化的步骤详解

       在开发过程中，虽然感觉优化sql语句很重要，但是往往更加重视的是功能实现，为了使自己以后写Mysql语句效率更高，有必要对Mysql优化做一个小小归纳。

步骤一、通过show status 命令了解各种sql执行的效率

show [session|gobal] status

session级别表示统计当前连接的结果。

global级别表示统计自数据上次启动至今的结果。

如果不写级别，默认的是session级别

eg：SHOW GLOBAL STATUS;

Variable_nameValueAborted_clients6Aborted_connects0Binlog_cache_disk_use0Binlog_cache_use0Binlog_stmt_cache_disk_use0Binlog_stmt_cache_use0Bytes_received95645Bytes_sent1285066Com_admin_commands0Com_assign_to_keycache0Com_alter_db0Com_alter_db_upgrade0Com_alter_event0Com_alter_function0Com_alter_procedure0Com_alter_server0Com_alter_table6Com_alter_tablespace0Com_alter_user0Com_analyze0Com_begin0Com_binlog0Com_call_procedure0Com_change_db8Com_change_master0Com_check0Com_checksum0Com_commit0Com_create_db0Com_create_event0Com_create_function0Com_create_index0Com_create_procedure0Com_create_server0Com_create_table5Com_create_trigger0Com_create_udf0Com_create_user0Com_create_view6Com_dealloc_sql0Com_delete2Com_delete_multi0Com_do0Com_drop_db0Com_drop_event0Com_drop_function0Com_drop_index0Com_drop_procedure0Com_drop_server0Com_drop_table0Com_drop_trigger0Com_drop_user0Com_drop_view1Com_empty_query2Com_execute_sql0Com_flush0Com_get_diagnostics0Com_grant0Com_ha_close0Com_ha_open0Com_ha_read0Com_help0Com_insert15Com_insert_select0Com_install_plugin0Com_kill0Com_load0Com_lock_tables0Com_optimize0Com_preload_keys0Com_prepare_sql0Com_purge0Com_purge_before_date0Com_release_savepoint0Com_rename_table0Com_rename_user0Com_repair0Com_replace0Com_replace_select0Com_reset0Com_resignal0Com_revoke0Com_revoke_all0Com_rollback0Com_rollback_to_savepoint0Com_savepoint0Com_select414Com_set_option525Com_signal0Com_show_binlog_events0Com_show_binlogs0Com_show_charsets0Com_show_collations0Com_show_create_db0Com_show_create_event0Com_show_create_func0Com_show_create_proc0Com_show_create_table260Com_show_create_trigger0Com_show_databases8Com_show_engine_logs0Com_show_engine_mutex0Com_show_engine_status0Com_show_events0Com_show_errors0Com_show_fields102Com_show_function_code0Com_show_function_status0Com_show_grants0Com_show_keys86Com_show_master_status0Com_show_open_tables0Com_show_plugins0Com_show_privileges0Com_show_procedure_code0Com_show_procedure_status0Com_show_processlist1Com_show_profile0Com_show_profiles115Com_show_relaylog_events0Com_show_slave_hosts0Com_show_slave_status0Com_show_status247Com_show_storage_engines0Com_show_table_status1Com_show_tables14Com_show_triggers5Com_show_variables5Com_show_warnings0Com_slave_start0Com_slave_stop0Com_stmt_close0Com_stmt_execute0Com_stmt_fetch0Com_stmt_prepare0Com_stmt_reprepare0Com_stmt_reset0Com_stmt_send_long_data0Com_truncate0Com_uninstall_plugin0Com_unlock_tables0Com_update27Com_update_multi0Com_xa_commit0Com_xa_end0Com_xa_prepare0Com_xa_recover0Com_xa_rollback0Com_xa_start0CompressionONConnection_errors_accept0Connection_errors_internal0Connection_errors_max_connections0Connection_errors_peer_address0Connection_errors_select0Connection_errors_tcpwrap0Connections10Created_tmp_disk_tables128Created_tmp_files5Created_tmp_tables910Delayed_errors0Delayed_insert_threads0Delayed_writes0Flush_commands1Handler_commit108Handler_delete2Handler_discover0Handler_external_lock782Handler_mrr_init0Handler_prepare0Handler_read_first73Handler_read_key2109Handler_read_last0Handler_read_next42Handler_read_prev0Handler_read_rnd1882Handler_read_rnd_next94791Handler_rollback0Handler_savepoint0Handler_savepoint_rollback0Handler_update195Handler_write93316Innodb_buffer_pool_dump_statusnot startedInnodb_buffer_pool_load_statusnot startedInnodb_buffer_pool_pages_data397Innodb_buffer_pool_bytes_data6504448Innodb_buffer_pool_pages_dirty0Innodb_buffer_pool_bytes_dirty0Innodb_buffer_pool_pages_flushed193Innodb_buffer_pool_pages_free7795Innodb_buffer_pool_pages_misc0Innodb_buffer_pool_pages_total8192Innodb_buffer_pool_read_ahead_rnd0Innodb_buffer_pool_read_ahead0Innodb_buffer_pool_read_ahead_evicted0Innodb_buffer_pool_read_requests4642Innodb_buffer_pool_reads364Innodb_buffer_pool_wait_free0Innodb_buffer_pool_write_requests872Innodb_data_fsyncs129Innodb_data_pending_fsyncs0Innodb_data_pending_reads0Innodb_data_pending_writes0Innodb_data_read6033408Innodb_data_reads402Innodb_data_writes281Innodb_data_written6534656Innodb_dblwr_pages_written193Innodb_dblwr_writes14Innodb_have_atomic_builtinsONInnodb_log_waits0Innodb_log_write_requests574Innodb_log_writes46Innodb_os_log_fsyncs61Innodb_os_log_pending_fsyncs0Innodb_os_log_pending_writes0Innodb_os_log_written202752Innodb_page_size16384Innodb_pages_created34Innodb_pages_read363Innodb_pages_written193Innodb_row_lock_current_waits0Innodb_row_lock_time0Innodb_row_lock_time_avg0Innodb_row_lock_time_max0Innodb_row_lock_waits0Innodb_rows_deleted0Innodb_rows_inserted3Innodb_rows_read406Innodb_rows_updated2Innodb_num_open_files32Innodb_truncated_status_writes0Innodb_available_undo_logs128Key_blocks_not_flushed0Key_blocks_unused14344Key_blocks_used3Key_read_requests381Key_reads1Key_write_requests117Key_writes50Last_query_cost0.000000Last_query_partial_plans0Max_used_connections3Not_flushed_delayed_rows0Open_files70Open_streams0Open_table_definitions120Open_tables117Opened_files1042Opened_table_definitions144Opened_tables147Performance_schema_accounts_lost0Performance_schema_cond_classes_lost0Performance_schema_cond_instances_lost0Performance_schema_digest_lost0Performance_schema_file_classes_lost0Performance_schema_file_handles_lost0Performance_schema_file_instances_lost0Performance_schema_hosts_lost0Performance_schema_locker_lost0Performance_schema_mutex_classes_lost0Performance_schema_mutex_instances_lost0Performance_schema_rwlock_classes_lost0Performance_schema_rwlock_instances_lost0Performance_schema_session_connect_attrs_lost0Performance_schema_socket_classes_lost0Performance_schema_socket_instances_lost0Performance_schema_stage_classes_lost0Performance_schema_statement_classes_lost0Performance_schema_table_handles_lost0Performance_schema_table_instances_lost0Performance_schema_thread_classes_lost0Performance_schema_thread_instances_lost0Performance_schema_users_lost0Prepared_stmt_count0Qcache_free_blocks1Qcache_free_memory1039896Qcache_hits0Qcache_inserts0Qcache_lowmem_prunes0Qcache_not_cached404Qcache_queries_in_cache0Qcache_total_blocks1Queries1888Questions1887Select_full_join1Select_full_range_join0Select_range23Select_range_check0Select_scan727Slave_heartbeat_period0.000Slave_last_heartbeat Slave_open_temp_tables0Slave_received_heartbeats0Slave_retried_transactions0Slave_runningOFFSlow_launch_threads0Slow_queries0Sort_merge_passes0Sort_range0Sort_rows1964Sort_scan151Ssl_accept_renegotiates0Ssl_accepts0Ssl_callback_cache_hits0Ssl_cipher Ssl_cipher_list Ssl_client_connects0Ssl_connect_renegotiates0Ssl_ctx_verify_depth0Ssl_ctx_verify_mode0Ssl_default_timeout0Ssl_finished_accepts0Ssl_finished_connects0Ssl_server_not_after Ssl_server_not_before Ssl_session_cache_hits0Ssl_session_cache_misses0Ssl_session_cache_modeNONESsl_session_cache_overflows0Ssl_session_cache_size0Ssl_session_cache_timeouts0Ssl_sessions_reused0Ssl_used_session_cache_entries0Ssl_verify_depth0Ssl_verify_mode0Ssl_version Table_locks_immediate386Table_locks_waited0Table_open_cache_hits656Table_open_cache_misses130Table_open_cache_overflows0Tc_log_max_pages_used0Tc_log_page_size0Tc_log_page_waits0Threads_cached1Threads_connected2Threads_created3Threads_running1Uptime286258Uptime_since_flush_status286258

主要参数描述

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://blog.csdn.net/hsd2012/article/details/51141325）

步骤三：通过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提示