MySQL優化的步驟詳解

       在開發過程中，雖然感覺優化sql語句很重要，但往往更重視的是功能實現，為了讓自己以後寫Mysql語句效率更高，有必要對Mysql優化做一個小歸納。

步驟一、透過show status 指令了解各種sql執行的效率

show [session|gobal] status

session等級表示統計目前連線的結果。

global等級表示統計自資料上次啟動至今的結果。

如果不寫入級別，預設的是session級別

#eg：SHOW GLOBAL STATUS;

95645#12850660 0#000 #Com_alter_functionCom_alter_procedureCom_alter_serverCom_alter_table#Com_alter_tablespaceCom_alter_userCom_analyzeCom_beginCom_binlog#Com_call_procedureCom_change_dbCom_change_masterCom_checkCom_checksum#Com_commit##0 #Com_create_db0 Com_create_event0Com_create_function#0Com_create_index#0#Com_create_procedure0 #Com_create_server0 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#########1##Com_empty_query2#Com_execute_sql##00000#150##0#Com_kill0Com_load0#Com_lock_tables0Com_optimize#Com_preload_keysCom_prepare_sqlCom_purge#Com_purge_before_dateCom_release_savepointCom_rename_tableCom_rename_userCom_repair#Com_replaceCom_replace_select#Com_resetCom_resignalCom_revokeCom_revoke_all##0Com_savepoint#0Com_select414Com_set_option525Com_signal0###Com_show_binlog_events######0############Com_show_binlogs### ###0############Com_show_charsets######0############Com_show_collat​​ions#######0### ### ######Com_show_create_db######0#############Com_show_create_event######0#############Com_show_create_func ### ###0############Com_show_create_proc###################Com_show_create_table#######260### #####8Com_show_engine_logs8Com_show_engine_logs0Com_show_engine_mutex#0##Com_show_engine_status#Com_show_events#Com_show_errorsCom_show_fieldsCom_show_function_codeCom_show_function_status008600#000Com_show_procedure_status#0Com_show_processlist1Com_show_profile0Com_show_profiles115Com_show_relaylog_events0Com_show_slave_hostsCom_show_slave_hosts##0Com_show_slave_status#024701#1145##5Com_stmt_fetch0#Com_stmt_prepare0Com_stmt_reprepare0Com_stmt_reset0Com_stmt_send_long_data####0####### ######Com_truncate######0############Com_uninstall_plugin######0#############Com_unlock_tables### ###0############Com_update######27############Com_update_multi######0#################0###### ######Com_xa_commit######0#############Com_xa_end######0############Com_xa_prepare#### ###0############Com_xa_recover######0########。壓縮#。壓縮ONConnection_errors_accept0Connection_errors_internal0Connection_errors_max_connections0#Connection_errors_peer_address0Connection_errors_select#Connection_errors_select0Connection_errors_tcpwrap#0連線10#Created_tmp_disk_tables128Created_tmp_files5#Created_tmp_tables#Created_tmp_tablesDelayed_errorsDelayed_insert_threads#Delayed_writesFlush_commandsHandler_commitHandler_deleteHandler_discover##0#Handler_external_lock782Handler_mrr_init0Handler_prepare0Handler_read_first73Handler_read_key##210904201882#9479100##Handler_savepoint_rollback0Handler_update195Handler_write93316#Innodb_buffer_pool_dump_status未啟動Innodb_buffer_pool_load_status##Innodb_buffer_pool_######Innodb_buffer_pool_pages_dirty###########Innodb_buffer_pool_pages_dirty#####。 ########Innodb_buffer_pool_bytes_dirty######0############Innodb_buffer_pool_pages_flushed######193###########」 #####7795############Innodb_buffer_pool_pages_misc######0###########Innodb_buffer_pool_pages_total#############Innodb_buffer_pool_pages_total#######8192##### ########Innodb_buffer_pool_read_ahead_rnd######0########0Innodb_buffer_pool_read_ Innodb_buffer_pool_readsInnodb_buffer_pool_wait_free0872129006033408402281Innodb_data_writing#6534656Innodb_dblwr_pages_writing193Innodb_dblwr_writes14Innodb_have_atomic_builtinsON#Innodb_log_waits#0Innodb_log_write_requests#574Innodb_log_writes46Innodb_os_log_fsyncs61Innodb_os_log_pending_fsyncs0Innodb_os_log_pending_writes0Innodb_os_log_writing#202752#Innodb_pages_writing#Innodb_pages_writingInnodb_row_lock_current_waits##Innodb_row_lock_time00000#Innodb_row_lock_waits##0Innodb_rows_deleted#0Innodb_rows_inserted3#Innodb_rows_read406Innodb_rows_updated2#Innodb_num_open_files#Innodb_num_open_files#Innodb_num_open_files32Innodb_truncated_status_writes0#128######## ######Key_blocks_not_flushed######0############Key_blocks_unused######14344#############Key_blocks_used#####################K ###3############Key_read_requests######381############Key_reads#####1####### ######Key_write_requests######117######Last_query_partial_plans#0Max_used_connections#3##Not_flushed_delayed_rows#Open_filesOpen_streamsOpen_table_definitionsOpen_tablesOpened_files#Opened_table_definitionsOpened_tables#Performance_schema_accounts_lost0Performance_schema_file_instances_lost0Performance_schema_hosts_lost#0##Performance_schema_locker_lost0Performance_schema_mutex_classes_lost0Performance_schema_mutex_instances_lost0Performance_schema_rwlock_classes_lost0Performance_schema_rwlock_instances_lost#Performance_schema_session_connect_attrs_lost#0Performance_schema_socket_classes_lost#Performance_sche_sches_ocket_##Performance_sche_schedinsta_ 0000#00Performance_schema_thread_class_class#lost##1#Qcache_free_memory1039896Qcache_hits#Qcache_inserts#0####Qcache_lowmem_prunes######0############Qcache_not_cached######404#############Qcache_queries_in_cache################Qcache_queries_in_cache### ###0############Qcache_total_blocks#######1########Select_range#Select_range_check#Select_scanSlave_heartbeat_periodSlave_last_heartbeatSlave_open_temp_tables##Slave_received_heartbeats#Slave_retried_transactions#Slave_runningSlow_launch_threadsSlow_queries##Sort_merge_passes0#Sort_range0Sort_rows1964Sort_scan151Ssl_accept_renegotiates#0#Ssl_accepts0Ssl_callback_cache_hits#0Ssl_cipher Ssl_cipher_list Ssl_client_connects#0##Ssl_connect_renegotiates0Ssl_ctx_verify_depth0#Ssl_ctx_verify_modeSsl_default_timeoutSsl_finished_accepts##Ssl_finished_connectsSsl_server_not_after#Ssl_server_not_before#Ssl_server_not_before# # ##Ssl_session_cache_hits#0Ssl_session_cache_misses#0Ssl_session_cache_modeNONE#Ssl_session_cache_overflows0#Ssl_session_cache_size##0 ##Ssl_session_cache_timeoutsSsl_sessions_reusedSsl_used_session_cache_entriesSsl_verify_depth#Ssl_verify_mode##Ssl_verify_mode#0##Ssl_version ####Table_locks_immediate#######386############## Table_locks_waited######0############Table_open_cache_hits######656############？ ####0#Tc_log_page_size0Tc_log_page_waits#0Threads_cached#1Threads_connected2Threads_created3Threads_running#1Uptime286258Uptime_since_flush_status

Variable_name	Value
#Aborted_clients	#6
Aborted_connects	0
Binlog_cache_disk_use
#Binlog_cache_use	0
Binlog_stmt_cache_disk_use	#0
Binlog_stmt_cache_use	#0
	##收到的位元組數
	發送的位元組數
	Com_admin_commands
	Com_assign_to_keycache
	Com_alter_db
	Com_alter_db_upgrade
	#Com_alter_event
0
0
0
6
#0
0
0
#0
0
#0
8
0
#0
0
Com_drop_server	0
Com_drop_table	0
Com_drop_trigger	0
Com_drop_user	0
#Com_drop_view
	##0
Com_flush	0
Com_get_diagnostics	0
Com_grant
	Com_ha_close
	#Com_ha_open
	Com_ha_read
	Com_help
	#Com_insert
	#Com_insert_select
	#Com_install_plugin
0
0
#0
0
#0
0
0
0
0
#0
0
0
0
0
0
Com_rollback	0
Com_rollback_to_savepoint
Com_show_create_trigger	0
Com_show_databases	#8
Com_show_engine_logs
#0
0
0
102
#0
0	##Com_show_function_status
	#Com_show_grants
	Com_show_keys
	Com_show_master_status
	Com_show_open_tables
	Com_show_plugins
	Com_show_privileges
	Com_show_procedure_code
	Com_show_procedure_status
##Com_show_procedure_status
##0
	##Com_show_status
	Com_show_storage_engines
	Com_show_table_status
	Com_show_tables
	Com_show_tables
	Com_show_triggers
	Com_show_variables
##Com_show_warnings	0
#Com_slave_start	0
Com_slave_stop	0
Com_stmt_close	0
Com_stmt_execute	#0
Com_xa_rollback	0
#Com_xa_start	0
5
910
0
#0
0
1
108
2
	#Handler_read_last
	#Handler_read_next
	Handler_read_prev
	Handler_read_rnd
	Handler_read_rnd_next
	Handler_rollback
	Handler_savepoint
##Innodb_buffer_pool_pages_data	397
Innodb_buffer_pool_bytes_data	6504448
Innodb_buffer_pool_read_ahead	0
#Innodb_buffer_pool_read_ahead_evicted	0
Innodb_buffer_pool_
4642
364
0	#Innodb_buffer_pool_wait_free
	#Innodb_buffer_pool_write_requests
	Innodb_data_fsyncs
	Innodb_data_pending_fsyncs
	Innodb_data_pending_reads
	#Innodb_data_pending_writes
	Innodb_data_read
	Innodb_data_reads
##Innodb_page_size	#16384
##Innodb_pages_created	34
#Innodb_pages_read	363
#Innodb_pages_writing
193
0
	#Innodb_row_lock_time
	Innodb_row_lock_time_avg
	#Innodb_row_lock_time_max
	#Innodb_row_lock_waits
	0
#Innodb_row_lock_waits
	##Innodb_available_undo_logs
Key_writes	#50
Last_query_cost	0.00000
#Last_query_partial_plans
0
70
0
120
117
1042
144
#147
	##Performance_schema_accounts_lost
##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_hand
0
	Performance_schema_stage_classes_lost
	Performance_schema_statement_classes_lost
	#Performance_schema_table_handles_lost
	#Performance_schema_table_instances_lost
	Performance_schema_thread_class
	#0
Performance_schema_thread_instances_lost	0
Performance_schema_users_lost	0
##Prepared_stmt_count	0
Qcache_free_blocks	1
#Qcache_free_memory
查詢	1888
問題	1887
Select_full_join	1
Select_full_range_join	#0
##23
0
727
0.000
#0
0
0
0
OFF
0
0
# 0
0
#0
0
#
#Ssl_session_cache_size
0
#0
0
0
#0
	##Ssl_verify_
Table_open_cache_overflows	0
#Tc_log_max_pages_used	0
#Tc_log_page_size
。

####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）！