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

黄舟
풀어 주다: 2017-03-01 13:51:47
원래의
1838명이 탐색했습니다.

개발 과정에서 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)을 주목해주세요!


관련 라벨:
원천:php.cn
본 웹사이트의 성명
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.
인기 튜토리얼
더>
최신 다운로드
더>
웹 효과
웹사이트 소스 코드
웹사이트 자료
프론트엔드 템플릿