開発プロセスでは、SQL ステートメントの最適化が重要であると感じますが、将来 MySQL ステートメントをより効率的に作成するためには、機能の実装により重点が置かれることがよくあります。 MySQLの最適化。

ステップ 1. show status コマンドを使用して、さまざまな SQL 実行の効率を理解します

show [session|gobal] status

セッションレベルは、現在の接続結果の統計を示します。

グローバルレベルは、データが最後に開始されてからの統計結果を表します。

レベルを記述しない場合、デフォルトはセッションレベルです

例: SHOW GLOBAL STATUS;

prepared_stmt_count1039896Qcache_hits0Qcache_inserts0Qcache_lowmem_prunes0Qcache_not_cached404Qcache_queries_in_cache0Qcache_total_blocks1

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_roll戻る	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_s how_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_mut ex	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_s how_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
圧縮	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	188 2
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	開始されていません
Innodb_buffer_pool_load_status	開始されていません
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
NNODB_BUFFER_POOL_READ_AHEAD	0
INNODB_BUFFER_POOL_READ_EVICTED	0
INNODB_BUFFER_POOL_READ_REQUESTS	4642
INNODB_BUFFER_POOL_READS	364
INNODB_BUFFER_POOL_FEE_FEIT_FREIT_FEIT_FEIT_FEIT_FEIT_FEIT_FEIT_FEIT_FEIT_FEIT_FEIT_FEIT_FEIT_FEIT_FEIT_FEIT_FEIT_FEIT_FREE ests	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_write	6534656
Innodb_dblwr_pages_writing	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_writing	202752
Innodb_page_size	16384
Innodb_pages_created	34
Innodb_pages_read	363
Innodb_pages_write	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_layed_rows	0
Open_files	70
Open_streams	0
Open_table_定義	120
Open_tables	117
Opened_files	1042
Opened_table_settings	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_s chema_file_handles_lost	0
Performance_schema_file_instances_紛失しました	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_lo st	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_instances_lost	0
performance_schema_users_lost	0
0
0






クエリ	1888
質問	1887
Select_full_join	1
Select_full_range_join	0
範囲選択	23
範囲選択チェック	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	オフ
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
S sl_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_ver ify_ 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_待機中	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 の回数更新操作の実行現在のデータベースアプリケーションは主に更新またはクエリの挿入と、実行率です。更新操作に対応するカウントは、実行回数のカウントであり、送信またはロールバックに関係なく累積されます。

ステップ 2: 実行効率の低い SQL ステートメントを特定しますスロークエリログを通じて実行効率の低い SQL ステートメントを特定します。 --log-slow-queries[=file_name] オプションで開始すると、mysqld は、long_query_time 秒を超えて実行されたすべての SQL ステートメントを含むログファイルを書き込みます。遅いクエリを見つける方法については、ここをクリックしてください (http://www.php.cn/)

ステップ 3: EXPLAIN を通じて非効率な SQL の実行計画を分析します

2 では、非効率な SQL ステートメントをクエリすることができ、explain または desc を通じて mysql 実行選択情報を取得できます。

eg:

EXPLAIN 
SELECT 
  t0.* 
FROM
  t3 AS t0
  LEFT JOIN `t2` AS t1 
    ON t0.`id1` = t1.`id1` 
WHERE t0.id1 = 5 ;

ログイン後にコピー

実行結果は次のとおりです:

分析: select_type:選択タイプ。一般的な値には、SIMPLE (単純なテーブル、つまり接続やサブクエリが使用されない)、PRIMARY (メインクエリ、つまり外側のクエリ)、UNION (共用体の 2 番目以降のクエリステートメント)、SUBQUERY (サブクエリ) が含まれます。 ) ) の最初の SELECT など。 talbe: 結果セットを出力するテーブル。

type: テーブルの接続タイプ。パフォーマンスは高いものから低いものまで: system (テーブル内に 1 行のみ)、const (テーブル内に一致する行が 1 つだけ)、eq_ref、ref、ref_null、index_merge、unique_subquery、index_subquery、range、idnex など

possible_keys: クエリ時に使用される可能性のあるインデックス

key: 実際に使用されるインデックス

key_len: インデックスフィールドの長さ

rows: スキャンされた行の数

番外編：解説と実行の説明

ステップ 4: 問題を特定し、対応する最適化措置を講じます

上記のステップの後、問題の原因を特定できます。この時点で、状況に応じて適切な措置を講じることができます。。一般的な対策としては、 1. 対応するインデックスの作成 2. SQL 文の最適化 3. テーブルの分割などがあります。

注: インデックスが機能している場合、handler_read_key の値は、インデックス値によって行が読み取られる回数を表します。値が非常に低い場合、追加することでパフォーマンスが向上します。インデックスは頻繁に使用されないため、テーブル名のインデックスは高くありません。 handler_read_rnd_next の値が高い場合は、クエリが非効率的に実行されており、インデックス修復を確立する必要があることを意味します。多数のテーブルスキャンが実行されており、handler_read_rnd_next 値が高い場合は、通常、以下に示すように、テーブルインデックスが間違っているか、作成されたクエリがインデックスを利用していないことを示します。

テーブルボックスのチェックリストを定期的に分析する必要があります次のコマンドを使用してテーブルを確認します（t3テーブルを確認します）

ANAライズテーブル t3; CHECK TABLE t3 ;
テーブルを定期的に最適化するコマンドは次のとおりです