Explication détaillée des étapes d'optimisation MySQL

Pendant le processus de développement, même si je pense que l'optimisation des instructions SQL est très importante, l'accent est souvent mis sur la mise en œuvre fonctionnelle. Afin de me rendre plus efficace dans l'écriture des instructions MySQL à l'avenir, il est nécessaire de le faire. faites un petit résumé de l'optimisation MySQL.

Étape 1. Comprendre l'efficacité de diverses exécutions SQL via la commande show status

show [session|gobal] status

Le niveau session indique les statistiques des résultats de connexion en cours.

Le niveau global représente les résultats statistiques depuis le dernier démarrage des données.

Si vous n'écrivez pas de niveau, la valeur par défaut est le niveau de session

par exemple : AFFICHER LE STATUT GLOBAL ;

Variable_name	Valeur
Clients_abandonnés	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
Connexions	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_request s	4642
Innodb_buffer_pool_reads	364
Innodb_buffer_pool_wait_free	0
Innodb_buffer_pool_write_requests	872
Innodb_data_fsyncs	129
Innodb_data_ending_fsyncs	0
Innodb_data_ending_reads	0
Innodb_data_ending_writes	0
Innodb_data_read	6033408
Innodb_data_reads	402
Innodb_data_writes	281
Innodb_data_writing	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_ending_fsyncs	0
Innodb_os_log_ending_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_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
Requêtes	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_renégocie	0
Ssl_accepts	0
Ssl_callback_cache_hits	0
Ssl_cipher
Ssl_cipher_list
Ssl_client_connects	0
Ssl_connect_renegoates	0
Ssl_ctx_verify_degree	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	AUCUN
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_degree	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_connectés	2
Threads_created	3
Threads_running	1
Temps de disponibilité	286258
Uptime_since_flush_status	286258

Description du paramètre principal

Connexions : le nombre de fois que la vue se connecte au serveur MySQL

Uptime : Temps de travail du serveur

Slow_queries : Nombre de requêtes lentes

Com_xxx représente le nombre de fois chacune L'instruction xxx est exécutée

Com_select Nombre d'exécutions de select

Com_insert Nombre d'exécutions d'insertion Lors de l'insertion par lots, seulement 1. le temps est accumulé

Com_update Nombre d'opérations de mise à jour effectuées

Com_delete Nombre des opérations de suppression effectuées

Les paramètres de type Innodb_rows_xxx ne sont valables que pour le moteur de stockage innodb

Analyse : Grâce aux paramètres ci-dessus, vous pouvez facilement comprendre l'application de la base de données actuelle et insérer des mises à jour. Est-elle principalement basée sur des requêtes ou sur des requêtes, ainsi que le taux d'exécution. Le décompte correspondant à l'opération de mise à jour est le décompte du nombre d'exécutions, qui seront accumulées indépendamment de la soumission ou de l'annulation.

Étape 2 : Localisez les instructions SQL avec une faible efficacité d'exécution

Utilisez le journal des requêtes lentes pour localiser les instructions SQL avec une faible efficacité d'exécution. Démarré avec l'option --log-slow-queries[=file_name], mysqld écrit un fichier journal contenant toutes les instructions SQL exécutées pendant plus de long_query_time secondes. Pour savoir comment localiser les requêtes lentes, vous pouvez cliquer ici (http://www.php.cn/)

Étape 3 : Analysez le plan d'exécution de SQL inefficace via EXPLAIN

À l'étape 2, nous pouvons interroger l'instruction SQL inefficace. Ici, nous pouvons obtenir les informations de sélection d'exécution MySQL via expliquer ou desc. .

par exemple :

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

Les résultats d'exécution sont les suivants :

Analyse : select_type : représente le type de sélection. Les valeurs courantes incluent SIMPLE (table simple, c'est-à-dire qu'aucune connexion ou sous-requête n'est utilisée), PRIMARY (requête principale, c'est-à-dire la requête externe), UNION (la deuxième instruction de requête ou la suivante dans l'union), SUBQUERY (sous-requête ) le premier SELECT dans) etc.
talbe : La table qui génère l'ensemble de résultats.

type : Le type de connexion de la table. Performances de haut en bas : système (il n'y a qu'une seule ligne dans le tableau), const (il y a au plus une ligne correspondante dans le tableau), eq_ref, ref, ref_null, index_merge, unique_subquery, index_subquery, range, idnex, etc.

possible_keys : index pouvant être utilisés lors de la requête

clé : index réel utilisé

key_len : longueur du champ d'index

lignes : le nombre de lignes scannées

Extra : description et description de l'exécution

Étape 4 : Déterminez le problème et prenez les mesures d'optimisation correspondantes

Après les étapes ci-dessus, la cause du problème peut être déterminée à ce stade. prendre les mesures appropriées selon la situation. Les mesures courantes incluent 1. Créer les index correspondants 2. Optimiser les instructions SQL 3. Diviser les tables, etc.

Remarque : si l'index fonctionne, la valeur de handler_read_key sera très élevée. Cette valeur représente le nombre de fois qu'une ligne est lue par la valeur d'index. name augmente les performances obtenues par l'index. L'amélioration n'est pas élevée car l'index n'est pas utilisé très souvent. Une valeur handler_read_rnd_next élevée signifie que la requête s'exécute de manière inefficace et qu'une correction d'index doit être établie. Si un grand nombre d'analyses de table sont effectuées et que la valeur handler_read_rnd_next est élevée, cela indique généralement que l'index de la table est incorrect ou que la requête écrite n'utilise pas l'index, comme indiqué ci-dessous. Nous devrions analyser régulièrement le tableau et vérifier le tableau

Vérifiez le tableau en utilisant la commande suivante (vérifiez le tableau t3)

ANALYSER TABLE t3 ;CHECK TABLE t3 ;

La commande pour optimiser régulièrement la table est la suivante

optimiser la table nom de la table

Optimisation des instructions communes

1. Optimisez l'instruction Insert

(1) Si les données sont insérées à partir du même client, essayez d'utiliser des instructions d'insertion et des insertions multi-lignes à partir de plusieurs tables de mots. Pour réduire l'insertion d'une seule ligne, cette méthode réduit considérablement la consommation de connexion directe et d'arrêt entre le client et la base de données. par exemple :

INSÉRER DANS LES VALEURS t3(1,2),(8,5),(6,5),(4,3)

(2) Si vous insérez plusieurs lignes provenant de différents clients, vous pouvez obtenir une vitesse plus élevée en utilisant l'instruction retardée en insertion.

(3) Si vous effectuez une insertion par lots, vous pouvez ajouter la méthode variable vrac_insert_buffer_size pour augmenter la vitesse.

2. Optimisez l'instruction group by

Par défaut, group by col1, col2.... sera utilisé pour trier la requête en conséquence, si l'utilisateur souhaite éviter la consommation des résultats triés, il peut spécifier l'ordre par null pour désactiver le tri. Cela peut être vu dans le champ supplémentaire dans les résultats de la requête :

3. Optimisez le instruction order by

Dans certains cas, MySQL peut utiliser un index pour satisfaire la clause order by sans tri supplémentaire. La condition Where et order by utilisent le même index.

4. Optimisez les instructions contenant ou

Pour les clauses de requête contenant ou, si vous souhaitez utiliser un index, chaque colonne de condition entre ou doit Un index est utilisé ; sinon, l’ajout d’un index doit être envisagé.

5. Utilisez les invites SQL

Ce qui précède est l'explication détaillée des étapes d'optimisation de MySQL. veuillez faire attention à PHP Chinese Net (www.php.cn) !