当前位置:Gxlcms > mysql > MySQL优化的步骤详解

MySQL优化的步骤详解

时间:2021-07-01 10:21:17 帮助过:21人阅读

在开发过程中,虽然感觉优化sql语句很重要,但是往往更加重视的是功能实现,为了使自己以后写Mysql语句效率更高,有必要对Mysql优化做一个小小归纳。

步骤一、通过show status 命令了解各种sql执行的效率

show [session|gobal] status

session级别表示统计当前连接的结果。

global级别表示统计自数据上次启动至今的结果。

如果不写级别,默认的是session级别

eg:SHOW GLOBAL STATUS;

Variable_nameValue
Aborted_clients6
Aborted_connects0
Binlog_cache_disk_use0
Binlog_cache_use0
Binlog_stmt_cache_disk_use0
Binlog_stmt_cache_use0
Bytes_received95645
Bytes_sent1285066
Com_admin_commands0
Com_assign_to_keycache0
Com_alter_db0
Com_alter_db_upgrade0
Com_alter_event0
Com_alter_function0
Com_alter_procedure0
Com_alter_server0
Com_alter_table6
Com_alter_tablespace0
Com_alter_user0
Com_analyze0
Com_begin0
Com_binlog0
Com_call_procedure0
Com_change_db8
Com_change_master0
Com_check0
Com_checksum0
Com_commit0
Com_create_db0
Com_create_event0
Com_create_function0
Com_create_index0
Com_create_procedure0
Com_create_server0
Com_create_table5
Com_create_trigger0
Com_create_udf0
Com_create_user0
Com_create_view6
Com_dealloc_sql0
Com_delete2
Com_delete_multi0
Com_do0
Com_drop_db0
Com_drop_event0
Com_drop_function0
Com_drop_index0
Com_drop_procedure0
Com_drop_server0
Com_drop_table0
Com_drop_trigger0
Com_drop_user0
Com_drop_view1
Com_empty_query2
Com_execute_sql0
Com_flush0
Com_get_diagnostics0
Com_grant0
Com_ha_close0
Com_ha_open0
Com_ha_read0
Com_help0
Com_insert15
Com_insert_select0
Com_install_plugin0
Com_kill0
Com_load0
Com_lock_tables0
Com_optimize0
Com_preload_keys0
Com_prepare_sql0
Com_purge0
Com_purge_before_date0
Com_release_savepoint0
Com_rename_table0
Com_rename_user0
Com_repair0
Com_replace0
Com_replace_select0
Com_reset0
Com_resignal0
Com_revoke0
Com_revoke_all0
Com_rollback0
Com_rollback_to_savepoint0
Com_savepoint0
Com_select414
Com_set_option525
Com_signal0
Com_show_binlog_events0
Com_show_binlogs0
Com_show_charsets0
Com_show_collations0
Com_show_create_db0
Com_show_create_event0
Com_show_create_func0
Com_show_create_proc0
Com_show_create_table260
Com_show_create_trigger0
Com_show_databases8
Com_show_engine_logs0
Com_show_engine_mutex0
Com_show_engine_status0
Com_show_events0
Com_show_errors0
Com_show_fields102
Com_show_function_code0
Com_show_function_status0
Com_show_grants0
Com_show_keys86
Com_show_master_status0
Com_show_open_tables0
Com_show_plugins0
Com_show_privileges0
Com_show_procedure_code0
Com_show_procedure_status0
Com_show_processlist1
Com_show_profile0
Com_show_profiles115
Com_show_relaylog_events0
Com_show_slave_hosts0
Com_show_slave_status0
Com_show_status247
Com_show_storage_engines0
Com_show_table_status1
Com_show_tables14
Com_show_triggers5
Com_show_variables5
Com_show_warnings0
Com_slave_start0
Com_slave_stop0
Com_stmt_close0
Com_stmt_execute0
Com_stmt_fetch0
Com_stmt_prepare0
Com_stmt_reprepare0
Com_stmt_reset0
Com_stmt_send_long_data0
Com_truncate0
Com_uninstall_plugin0
Com_unlock_tables0
Com_update27
Com_update_multi0
Com_xa_commit0
Com_xa_end0
Com_xa_prepare0
Com_xa_recover0
Com_xa_rollback0
Com_xa_start0
CompressionON
Connection_errors_accept0
Connection_errors_internal0
Connection_errors_max_connections0
Connection_errors_peer_address0
Connection_errors_select0
Connection_errors_tcpwrap0
Connections10
Created_tmp_disk_tables128
Created_tmp_files5
Created_tmp_tables910
Delayed_errors0
Delayed_insert_threads0
Delayed_writes0
Flush_commands1
Handler_commit108
Handler_delete2
Handler_discover0
Handler_external_lock782
Handler_mrr_init0
Handler_prepare0
Handler_read_first73
Handler_read_key2109
Handler_read_last0
Handler_read_next42
Handler_read_prev0
Handler_read_rnd1882
Handler_read_rnd_next94791
Handler_rollback0
Handler_savepoint0
Handler_savepoint_rollback0
Handler_update195
Handler_write93316
Innodb_buffer_pool_dump_statusnot started
Innodb_buffer_pool_load_statusnot started
Innodb_buffer_pool_pages_data397
Innodb_buffer_pool_bytes_data6504448
Innodb_buffer_pool_pages_dirty0
Innodb_buffer_pool_bytes_dirty0
Innodb_buffer_pool_pages_flushed193
Innodb_buffer_pool_pages_free7795
Innodb_buffer_pool_pages_misc0
Innodb_buffer_pool_pages_total8192
Innodb_buffer_pool_read_ahead_rnd0
Innodb_buffer_pool_read_ahead0
Innodb_buffer_pool_read_ahead_evicted0
Innodb_buffer_pool_read_requests4642
Innodb_buffer_pool_reads364
Innodb_buffer_pool_wait_free0
Innodb_buffer_pool_write_requests872
Innodb_data_fsyncs129
Innodb_data_pending_fsyncs0
Innodb_data_pending_reads0
Innodb_data_pending_writes0
Innodb_data_read6033408
Innodb_data_reads402
Innodb_data_writes281
Innodb_data_written6534656
Innodb_dblwr_pages_written193
Innodb_dblwr_writes14
Innodb_have_atomic_builtinsON
Innodb_log_waits0
Innodb_log_write_requests574
Innodb_log_writes46
Innodb_os_log_fsyncs61
Innodb_os_log_pending_fsyncs0
Innodb_os_log_pending_writes0
Innodb_os_log_written202752
Innodb_page_size16384
Innodb_pages_created34
Innodb_pages_read363
Innodb_pages_written193
Innodb_row_lock_current_waits0
Innodb_row_lock_time0
Innodb_row_lock_time_avg0
Innodb_row_lock_time_max0
Innodb_row_lock_waits0
Innodb_rows_deleted0
Innodb_rows_inserted3
Innodb_rows_read406
Innodb_rows_updated2
Innodb_num_open_files32
Innodb_truncated_status_writes0
Innodb_available_undo_logs128
Key_blocks_not_flushed0
Key_blocks_unused14344
Key_blocks_used3
Key_read_requests381
Key_reads1
Key_write_requests117
Key_writes50
Last_query_cost0.000000
Last_query_partial_plans0
Max_used_connections3
Not_flushed_delayed_rows0
Open_files70
Open_streams0
Open_table_definitions120
Open_tables117
Opened_files1042
Opened_table_definitions144
Opened_tables147
Performance_schema_accounts_lost0
Performance_schema_cond_classes_lost0
Performance_schema_cond_instances_lost0
Performance_schema_digest_lost0
Performance_schema_file_classes_lost0
Performance_schema_file_handles_lost0
Performance_schema_file_instances_lost0
Performance_schema_hosts_lost0
Performance_schema_locker_lost0
Performance_schema_mutex_classes_lost0
Performance_schema_mutex_instances_lost0
Performance_schema_rwlock_classes_lost0
Performance_schema_rwlock_instances_lost0
Performance_schema_session_connect_attrs_lost0
Performance_schema_socket_classes_lost0
Performance_schema_socket_instances_lost0
Performance_schema_stage_classes_lost0
Performance_schema_statement_classes_lost0
Performance_schema_table_handles_lost0
Performance_schema_table_instances_lost0
Performance_schema_thread_classes_lost0
Performance_schema_thread_instances_lost0
Performance_schema_users_lost0
Prepared_stmt_count0
Qcache_free_blocks1
Qcache_free_memory1039896
Qcache_hits0
Qcache_inserts0
Qcache_lowmem_prunes0
Qcache_not_cached404
Qcache_queries_in_cache0
Qcache_total_blocks1
Queries1888
Questions1887
Select_full_join1
Select_full_range_join0
Select_range23
Select_range_check0
Select_scan727
Slave_heartbeat_period0.000
Slave_last_heartbeat
Slave_open_temp_tables0
Slave_received_heartbeats0
Slave_retried_transactions0
Slave_runningOFF
Slow_launch_threads0
Slow_queries0
Sort_merge_passes0
Sort_range0
Sort_rows1964
Sort_scan151
Ssl_accept_renegotiates0
Ssl_accepts0
Ssl_callback_cache_hits0
Ssl_cipher
Ssl_cipher_list
Ssl_client_connects0
Ssl_connect_renegotiates0
Ssl_ctx_verify_depth0
Ssl_ctx_verify_mode0
Ssl_default_timeout0
Ssl_finished_accepts0
Ssl_finished_connects0
Ssl_server_not_after
Ssl_server_not_before
Ssl_session_cache_hits0
Ssl_session_cache_misses0
Ssl_session_cache_modeNONE
Ssl_session_cache_overflows0
Ssl_session_cache_size0
Ssl_session_cache_timeouts0
Ssl_sessions_reused0
Ssl_used_session_cache_entries0
Ssl_verify_depth0
Ssl_verify_mode0
Ssl_version
Table_locks_immediate386
Table_locks_waited0
Table_open_cache_hits656
Table_open_cache_misses130
Table_open_cache_overflows0
Tc_log_max_pages_used0
Tc_log_page_size0
Tc_log_page_waits0
Threads_cached1
Threads_connected2
Threads_created3
Threads_running1
Uptime286258
Uptime_since_flush_status286258

主要参数描述

Connections:视图连接mysql服务器的次数

Uptime:服务器工作时间

Slow_queries:慢查询的次数

Com_xxx表示每个xxx语句执行的次数

Com_select 执行select次数

Com_insert 执行insert次数,批量插入时候,只累加1次

Com_update 执行Update操作次数

Com_delete 执行删除操作次数

innodb_rows_xxx类型的参数只对innodb存储引擎有效

解析:通过上面一些参数,可以容易了解当前数据库的应用时插入更新为主还是查询为主,以及执行比例。对应更新操作的计数,是对执行次数的计数,不论提交还是回滚都会累加。

步骤二:定位执行效率较低的sql语句

通过慢查询日志定位哪些是执行效率的sql语句。用--log-slow-queries[=file_name]选项启动,mysqld写一个包含所有执行超过long_query_time秒的sql语句的日志文件。关于如何定位慢查询可以点解这里(http://www.gxlcms.com/)


步骤三:通过EXPLAIN分析低效率SQL的执行计划

在步骤二中,我们可以查询到低效率的sql语句,在此我们通过explain或desc可以获取mysql执行select信息。

eg:

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


执行结果如下:


解析:select_type:表示select类型。常见的取值有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:索引字段的长度

rows:扫描行的数量

Extra:执行情况的说明和描述

步骤四:确定问题并采取相应的优化措施

经过上面的步骤,可以确定问题出现的原因,此时我们可以根据情况,采取相应的措施。常见的措施有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)如果从同一个客户端插入数据,尽力使用多个字表的insert语句和多行插入,减少单行插入,这种方式大大减少客户端与数据库直接的连接、关闭等消耗。eg:

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

(2)如果从不同的客户插入很多行,能通过使用inset delayed语句得到更高的速度。

(3)如果进行批量插入,可以增加bulk_insert_buffer_size变量方法,提高速度。

2.优化group by语句

默认情况下使用group by col1,col2....会对查询进行相应的排序,如果用户想要避免排序结果的消耗,可以指定order by null 禁止排序。通过查询结果中extra字段可以看出:



3.优化order by语句

在某些情况中,mysql可以使用一个索引来满足order by子句,而不需要额外的排序,where条件和order by使用相同的索引。

4.优化含有or语句

对于含有or的查询子句,如果要使用索引,则or之间的每个条件列必须用到索引;否则,应该考虑添加索引。

5.使用sql提示

以上就是MySQL优化的步骤详解的内容,更多相关内容请关注PHP中文网(www.gxlcms.com)!

人气教程排行