MySQL 최적화 단계에 대한 자세한 설명

개발 과정에서 SQL 문을 최적화하는 것이 매우 중요하다고 생각하지만, 앞으로 MySQL 문을 보다 효율적으로 작성하려면 기능적 구현에 더 중점을 두는 경우가 많습니다. MySQL 최적화에 대해 간단히 요약해 보겠습니다.

1단계. show status 명령을 통해 다양한 SQL 실행의 효율성 이해

show [session|gobal] status

세션 레벨은 현재 연결 결과에 대한 통계를 나타냅니다.

글로벌 수준은 데이터가 마지막으로 시작된 이후의 통계 결과를 나타냅니다.

레벨을 작성하지 않으면 기본값은 세션 레벨입니다.

예: SHOW GLOBAL STATUS;

Com_update_multi0Com_xa_commit0Com_xa_end0Com_xa_prepare0Com_xa_recover0

변수_이름	값
중단된_클라이언트	6
Aborted_connects	0
Binlog_cache_disk_use	0
Binlog_cache_use	0
Binlog_stmt_cache_disk_use	0
Binlog_stmt_cache_use	0
수신된 바이트	95645
전송된 바이트	1285066
Com_admin_commands	0
Com_할당_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_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
연결	10
Created_tmp_disk_tables	128
Created_tmp_files	5
Created_tmp_tables	910
지연_오류	0
지연_삽입_스레드	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	시작되지 않음
Innodb_buffer_pool_load_status	시작되지 않음
Innodb_buffer_pool _페이지_데이터	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_requests	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_ fsync	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_writer	6534656
Innodb_dblwr_pages_writer	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_p end_writes	0
Innodb_os_log_writing	202752
Innodb_page_size	16384
Innodb_pages_created	34
Innodb_pages_read	363
Innodb_pages_writer	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
키 블록이 플러시되지 않음	0
키 블록이 사용되지 않음	14344
키 블록이 사용됨	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
최대 사용 연결 수	3
플러시되지 않은_지연_행	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 _잃다	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 _인스턴스_손실	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
성능_ 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
질문	1888
질문	1887
Select_full_join	1
Select_full_range_join	0
Select_range	23
선택_범위_체크	0
선택_스캔	727
슬레이브_하트비트_기간	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
정렬_병합_패스	0
정렬_범위	0
정렬_행	1964
Sort_scan	151
Ssl_accept_재협상	0
Ssl_accepts	0
Ssl_callback_cache_hits	0
Ssl_cipher
Ssl_cipher_list
Ssl_client_connects	0
Ssl_connect_재협상	0
Ssl_ctx_verify_length	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	없음
Ssl_session_cache_overflows	0
Ssl_session_cache_size	0
Ssl_session_cache_timeouts	0
Ssl_sessions_재사용	0
Ssl_used_session_cache_entries	0
Ssl_verify_length	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
연결된 스레드	2
생성된 스레드	3
실행 중인 스레드	1
가동 시간	286258
Uptime_since_flush_status	286258

주요 매개변수 설명

Connections: 뷰가 mysql 서버에 연결되는 횟수

Uptime: 서버 작업 시간

Slow_queries: 느린 쿼리 수

Com_xxx는 각 쿼리 횟수를 나타냅니다. xxx 문 실행

Com_select select 실행 횟수

Com_insert 일괄 삽입 시 insert 실행 횟수 , 1회만 누적됩니다

Com_update 업데이트 작업 수행 횟수

Com_delete 수행된 삭제 작업 수

Innodb_rows_xxx 유형 매개변수는 innodb 스토리지 엔진에만 유효합니다

분석: 위의 매개변수를 통해 현재 데이터베이스의 적용 및 업데이트 삽입이 주로 쿼리 기반인지 쿼리 기반인지, 실행 비율을 쉽게 이해할 수 있습니다. 업데이트 작업에 해당하는 횟수는 실행 횟수이며 제출이나 롤백과 관계없이 누적됩니다.

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 실행 선택 정보를 얻을 수 있습니다. .

예:

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(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: 인덱스 필드 길이

행: 스캔된 행 수

추가: 실행 설명 및 설명

4단계: 문제 파악 및 그에 따른 최적화 조치 취하기

위 단계를 거친 후 문제의 원인을 판단할 수 있습니다. 상황에 따른 조치. 일반적인 조치에는 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) 동일한 클라이언트에서 데이터를 삽입하는 경우 여러 단어 테이블에서 삽입 문 및 다중 행 삽입을 사용해 보세요. 단일 행 삽입을 줄이기 위해 이 방법은 클라이언트와 데이터베이스 간의 직접 연결 및 종료 소비를 크게 줄입니다. 예:

t3 값(1,2),(8,5),(6,5),(4,3)

에 삽입

(2) 서로 다른 클라이언트에서 많은 행을 삽입하는 경우 inset 지연 문을 사용하면 더 빠른 속도를 얻을 수 있습니다.

(3) 일괄 삽입을 수행하는 경우에는bulk_insert_buffer_size 변수 메소드를 추가하여 속도를 높일 수 있습니다.

2. 문별 그룹 최적화

기본적으로 col1, col2....별로 그룹화하여 쿼리를 정렬합니다. 따라서 사용자가 정렬된 결과를 사용하지 않으려면 order by null을 지정하여 정렬을 비활성화할 수 있습니다. 쿼리 결과의 추가 필드에서 확인할 수 있습니다.

3. 최적화 order by 문

경우에 따라 MySQL은 추가 정렬 없이 order by 절을 충족하기 위해 인덱스를 사용할 수 있습니다. where 조건과 order by는 동일한 인덱스를 사용합니다.

4. or를 포함하는 문 최적화

or를 포함하는 쿼리 절의 경우 인덱스를 사용하려면 or must 사이의 각 조건 열 인덱스는 다음과 같습니다. 그렇지 않으면 인덱스 추가를 고려해야 합니다.

5. SQL 프롬프트 사용

이상은 MySQL 최적화 단계에 대한 자세한 설명입니다. PHP 중국어 넷(www.php.cn)을 주목해주세요!