Detaillierte Erläuterung der MySQL-Optimierungsschritte

Obwohl ich das Optimieren von SQL-Anweisungen während des Entwicklungsprozesses für sehr wichtig halte, wird häufig mehr Wert auf die funktionale Implementierung gelegt. Um in Zukunft effizienter beim Schreiben von MySQL-Anweisungen zu sein, ist dies erforderlich Machen Sie eine kleine Zusammenfassung der MySQL-Optimierung.

Schritt 1. Verstehen Sie die Effizienz verschiedener SQL-Ausführungen mit dem Befehl „show status“

show [session|global] status

Die Sitzungsebene zeigt die Statistik der aktuellen Verbindungsergebnisse an.

Die globale Ebene stellt die statistischen Ergebnisse seit dem letzten Start der Daten dar.

Wenn Sie keine Ebene schreiben, ist die Standardeinstellung die Sitzungsebene

z. B. SHOW GLOBAL STATUS;

281 Innodb_dblwr_writes14Innodb_have_atomic_builtinsEINInnodb_log_waits0Innodb_log_write_requests574Innodb_log_writes46Innodb_os_log_fsyncs61Innodb_os_log_pending_fsyncs0Innodb_os_log_pending_writes0Innodb_os_log_scribed202752Innodb_page_size16384Innodb_pages_created34Innodb_pages_read363Innodb_pages_geschrieben193Innodb_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_lushed0Key_blocks_unused14344Key_blocks_used3Key_read_requests381Key_reads1Key_write_requests117

Variablenname	Wert
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
Komprimierung	EIN
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
Verbindungen	10
Erstellte_tmp_disk_tables	128
Erstellt_tmp_files	5
Erstellt_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	nicht gestartet
Innodb_buffer_pool_load_status	nicht gestartet
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_bu ffer_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_ Anfragen	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
Key_writes	50
Last_query_cost	0,000000
Last_query_partial_plans	0
Max_used_connections	3
Not_lushed_delayed_rows	0
Open_files	70
Open_streams	0
Open_table_definitions	120
Offene_Tabellen	117
Geöffnete_Dateien	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
Abfragen	1888
Fragen	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	AUS
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
Betriebszeit	286258
Betriebszeit_since_flush_status	286258

Hauptparameterbeschreibung

Verbindungen: Die Häufigkeit, mit der die Ansicht eine Verbindung zum MySQL-Server herstellt

Uptime: Server-Arbeitszeit

Slow_queries: Anzahl langsamer Abfragen

Com_xxx stellt jeweils die Häufigkeit dar xxx-Anweisung wird ausgeführt

Com_select Anzahl der Ausführungen von select

Com_insert Anzahl der Ausführungen von insert Während der Stapeleinfügung nur 1 Zeit wird akkumuliert

Com_update Anzahl der durchgeführten Aktualisierungsvorgänge

Com_delete-Nummer der durchgeführten Löschvorgänge

Innodb_rows_xxx-Typparameter sind nur für die Innodb-Speicher-Engine gültig

Analyse: Anhand der oben genannten Parameter können Sie die Anwendung der aktuellen Datenbank und das Einfügen von Aktualisierungen, ob sie hauptsächlich abfragebasiert oder abfragebasiert sind, sowie das Ausführungsverhältnis leicht verstehen. Die dem Aktualisierungsvorgang entsprechende Anzahl ist die Anzahl der Ausführungszeiten, die unabhängig von der Übermittlung oder dem Rollback akkumuliert werden.

Schritt 2: Suchen Sie SQL-Anweisungen mit geringer Ausführungseffizienz

Verwenden Sie das langsame Abfrageprotokoll, um SQL-Anweisungen mit geringer Ausführungseffizienz zu finden. Gestartet mit der Option --log-slow-queries[=Dateiname] schreibt mysqld eine Protokolldatei, die alle SQL-Anweisungen enthält, die länger als long_query_time Sekunden ausgeführt wurden. Informationen zum Auffinden langsamer Abfragen finden Sie hier (http://www.php.cn/)

Schritt 3: Analysieren Sie den Ausführungsplan von ineffizientem SQL über EXPLAIN

In Schritt 2 können wir die ineffiziente SQL-Anweisung abfragen. Hier können wir die MySQL-Ausführungsauswahlinformationen über EXPLAIN oder DESC abrufen. .

zB:

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

Die Ausführungsergebnisse lauten wie folgt:

Analyse: select_type: repräsentiert den ausgewählten Typ. Zu den allgemeinen Werten gehören SIMPLE (einfache Tabelle, d. h. es wird keine Verbindung oder Unterabfrage verwendet), PRIMARY (Hauptabfrage, d. h. die äußere Abfrage), UNION (die zweite oder nachfolgende Abfrageanweisung in der Union), SUBQUERY (Unterabfrage). ) das erste SELECT in) usw.
Talbe: Die Tabelle, die die Ergebnismenge ausgibt.

Typ: Der Verbindungstyp der Tabelle. Leistung von hoch nach niedrig: System (nur eine Zeile in der Tabelle), Const (es gibt höchstens eine passende Zeile in der Tabelle), eq_ref, ref, ref_null, index_merge, unique_subquery, index_subquery, range, idnex usw.

mögliche_Schlüssel: Indizes, die während der Abfrage verwendet werden können

Schlüssel: tatsächlich verwendeter Index

key_len: Länge des Indexfeldes

Zeilen: Die Anzahl der gescannten Zeilen

Extra: Erläuterung und Beschreibung der Ausführung

Schritt 4: Bestimmen Sie das Problem und ergreifen Sie entsprechende Optimierungsmaßnahmen

Nach den oben genannten Schritten kann die Ursache des Problems ermittelt werden. Zu diesem Zeitpunkt können wir geeignete Maßnahmen ergreifen Maßnahmen entsprechend der Situation. Gängige Maßnahmen sind 1. Entsprechende Indizes erstellen 2. SQL-Anweisungen optimieren 3. Tabellen aufteilen usw.

Hinweis: Wenn der Index funktioniert, ist der Wert von handler_read_key sehr hoch. Dieser Wert gibt an, wie oft eine Zeile vom Indexwert gelesen wird Name erhöht die vom Index erzielte Leistung. Die Verbesserung ist nicht groß, da der Index nicht sehr oft verwendet wird. Ein hoher handler_read_rnd_next-Wert bedeutet, dass die Abfrage ineffizient ausgeführt wird und eine Indexkorrektur eingerichtet werden sollte. Wenn eine große Anzahl von Tabellenscans durchgeführt werden und der Wert handler_read_rnd_next hoch ist, weist dies normalerweise darauf hin, dass der Tabellenindex falsch ist oder die geschriebene Abfrage den Index nicht verwendet, wie unten gezeigt. Wir sollten die Tabelle regelmäßig analysieren und überprüfen

Überprüfen Sie die Tabelle mit dem folgenden Befehl (überprüfen Sie die t3-Tabelle)

ANALYSE TABLE t3 ;CHECK TABLE t3 ;

Der Befehl zum regelmäßigen Optimieren der Tabelle lautet wie folgt

Tabellennamen optimieren

Gemeinsame Anweisungsoptimierung

1. Optimieren Sie die Insert-Anweisung

(1) Wenn Daten vom selben Client eingefügt werden, versuchen Sie, Insert-Anweisungen und mehrzeilige Einfügungen aus mehreren Worttabellen zu verwenden Um das Einfügen einzelner Zeilen zu reduzieren, reduziert diese Methode den Verbrauch direkter Verbindungen und das Herunterfahren zwischen dem Client und der Datenbank erheblich. Beispiel:

INSERT INTO t3 VALUES(1,2),(8,5),(6,5),(4,3)

(2) Wenn Sie viele Zeilen von verschiedenen Clients einfügen, können Sie durch die Verwendung der Inset-Delay-Anweisung eine höhere Geschwindigkeit erzielen.

(3) Wenn Sie eine Stapeleinfügung durchführen, können Sie die Variablenmethode bulk_insert_buffer_size hinzufügen, um die Geschwindigkeit zu erhöhen.

2. Optimieren Sie die Gruppierung nach Anweisung

Standardmäßig wird Gruppierung nach Spalte1, Spalte2... zum Sortieren der Abfrage verwendet Dementsprechend kann der Benutzer, wenn er den Verbrauch sortierter Ergebnisse vermeiden möchte, order by null angeben, um die Sortierung zu deaktivieren. Dies ist aus dem zusätzlichen Feld in den Abfrageergebnissen ersichtlich:

3. Optimieren Sie die order by-Anweisung

In einigen Fällen kann MySQL einen Index verwenden, um die order by-Klausel ohne zusätzliche Sortierung zu erfüllen. Die where-Bedingung und order by verwenden denselben Index.

4. Optimieren Sie Anweisungen, die oder enthalten

Für Abfrageklauseln, die oder enthalten, muss jede Bedingungsspalte zwischen oder ein Index sein andernfalls sollte über das Hinzufügen eines Index nachgedacht werden.

5. Verwenden Sie SQL-Eingabeaufforderungen

Das Obige ist die detaillierte Erklärung der Schritte der MySQL-Optimierung. Bitte achten Sie auf PHP Chinese Net (www.php.cn)!