优化sql语句的一般步骤
- 通过show status 命令了解各种命令执行的频率
mysql> show status like "%com%";
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| 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_instance | 0 |
| Com_alter_procedure | 0 |
| Com_alter_server | 0 |
| Com_alter_table | 2 |
| Com_alter_tablespace | 0 |
| Com_alter_user | 0 |
| Com_analyze | 0 |
| Com_begin | 0 |
| Com_binlog | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 3 |
| Com_change_master | 0 |
| Com_change_repl_filter | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 15 |
| Com_create_db | 2 |
| Com_create_event | 0 |
| Com_create_function | 3 |
| Com_create_index | 0 |
| Com_create_procedure | 3 |
| Com_create_server | 0 |
| Com_create_table | 16 |
| Com_create_trigger | 6 |
| Com_create_udf | 0 |
| Com_create_user | 0 |
| Com_create_view | 7 |
| Com_dealloc_sql | 0 |
| Com_delete | 0 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 1 |
| 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 | 0 |
| Com_empty_query | 0 |
| Com_execute_sql | 0 |
| Com_explain_other | 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 | 1017 |
| Com_insert_select | 0 |
| Com_install_plugin | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_lock_tables | 1 |
| 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 | 13 |
| Com_set_option | 28 |
| 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 | 1 |
| Com_show_create_trigger | 0 |
| Com_show_databases | 2 |
| 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 | 0 |
| Com_show_function_code | 0 |
| Com_show_function_status | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| 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 | 0 |
| Com_show_profile | 1 |
| Com_show_profiles | 2 |
| Com_show_relaylog_events | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 2 |
| Com_show_storage_engines | 0 |
| Com_show_table_status | 0 |
| Com_show_tables | 5 |
| Com_show_triggers | 0 |
| Com_show_variables | 0 |
| Com_show_warnings | 2 |
| Com_show_create_user | 0 |
| Com_shutdown | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_group_replication_start | 0 |
| Com_group_replication_stop | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_close | 0 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_uninstall_plugin | 0 |
| Com_unlock_tables | 1 |
| Com_update | 0 |
| 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 |
| Com_stmt_reprepare | 0 |
| Compression | OFF |
| Flush_commands | 1 |
| Handler_commit | 53 |
+-----------------------------+-------+
151 rows in set (0.01 sec)
//通过该命令可以知道每种命令执行的次数- 定位执行效率较低的sql语句
1.通过慢查询日志定位那些执行效率较低的SQL语句， 用--log-slow-queries[=file_name]选启动项时，mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件，具体的可以查看以后章节
2.慢查询日志在查询结束后才记录， 所以在应用反映执行效率出现问题的时候查慢查询并不能定位问题， 可以使用show processlist 命令查看当前mysql在进行的线程， 包含线程的状态， 是否锁表等， 可以实时地查看sql的执行情况， 同时对一些锁表操作进行优化3.通过Explain 分析低效SQL的执行计划
通过以上步骤查询低效sql语句后， 可以通过explain或者desc命令查询mysql如何执行select 语句的信息，包括在select语句执行过程中表如果还连接和连接的顺序
mysql> explain select sum(amount) from customer a, payment b where 1=1 and a.customer_id=b.customer_id and email='JANE.BENNETT@sakilacustomer.org';
+----+-------------+-------+------------+------+--------------------+--------------------+---------+----------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+--------------------+--------------------+---------+----------------------+------+----------+-------------+
| 1 | SIMPLE | a | NULL | ALL | PRIMARY | NULL | NULL | NULL | 599 | 10.00 | Using where |
| 1 | SIMPLE | b | NULL | ref | idx_fk_customer_id | idx_fk_customer_id | 2 | sakila.a.customer_id | 26 | 100.00 | NULL |
+----+-------------+-------+------------+------+--------------------+--------------------+---------+----------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
select_type:表示 SELECT 的类型，常见的取值有Simple(简单表， 即是不使用任何表连接或子查询)， Primary(主查询， 即外层的查询)， UNION(UNION中的第二个或后面的查询语句)， SUBQUERY(子查询中的第一个SELECT)等
table：输出结果集的表
type：表示mysql在表中找到需要行的方式， 或者叫访问类型，常见类型如下
ALL, INDEX, RANGE, REF, EQ_REF, const/system, NULL
ALL：全部扫描
INDEX:索引扫描， mysql遍历整个索引来查询匹配的行
RANGE:索引范围扫描， 常见于<、<=、>、>=、between
REF:使用非唯一索引扫描或唯一索引的前缀扫描， 返回匹配某个单独值的记录行
EQ_REF:类似ref, 区别就在使用索引是唯一索引 对每个索引键值， 表中只有一条记录匹配， 简单来说， 就是多表连接使用primary key 或者unique index作为关联条件。
const/system:单表中最多有一个匹配行， 查询起来非常迅速，所以这个匹配行中的其他列的值可以被优化在当前查询中当作常量来处理，例如， 根据主键primary key 或者唯一索引unique index进行查询
NULL:mysql不用访问表或索引， 直接就能得到结果
4.使用show profile 分析sql
msyql从5.0.37版本开始增加对show profiles 和 show profile语句的支持。通过have_profiling参数，能查看当前是否支持profile
mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES |
+------------------+
1 row in set, 1 warning (0.00 sec)
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 1 |
+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show profile;
+----------------+----------+
| Status | Duration |
+----------------+----------+
| starting | 0.000051 |
| Opening tables | 0.000017 |
| query end | 0.000005 |
| closing tables | 0.000004 |
| freeing items | 0.000019 |
| cleaning up | 0.000017 |
+----------------+----------+
6 rows in set, 1 warning (0.00 sec)
mysql> show profiles;
+----------+------------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | 0.00038100 | show tables |
| 2 | 0.00022800 | select * from store |
| 3 | 0.00348500 | show status like "%com%" |
| 4 | 0.00207100 | show processList |
| 5 | 0.00496600 | explain select sum(amount) from customer a, payment b where 1=1 and a.customer_id=b.customer_id and email='JANE.BENNETT@sakilacustomer.org' |
| 6 | 0.00014700 | select @@have_profiling |
| 7 | 0.00013700 | select @@profiling |
| 8 | 0.00011300 | set profiling=1 |
+----------+------------+---------------------------------------------------------------------------------------------------------------------------------------------+
8 rows in set, 1 warning (0.00 sec)
//通过该命令可以查看之前每一条执行命令所花费的时间
mysql> show profile for query 4;
+----------------+----------+
| Status | Duration |
+----------------+----------+
| starting | 0.001119 |
| query end | 0.000017 |
| closing tables | 0.000005 |
| freeing items | 0.000029 |
| cleaning up | 0.000901 |
+----------------+----------+
5 rows in set, 1 warning (0.00 sec)
//通过该命令可以查看指定命令的执行时间分配
mysql> show profile cpu for query 4;
+----------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+----------------+----------+----------+------------+
| starting | 0.001119 | 0.000065 | 0.000161 |
| query end | 0.000017 | 0.000007 | 0.000009 |
| closing tables | 0.000005 | 0.000004 | 0.000001 |
| freeing items | 0.000029 | 0.000010 | 0.000020 |
| cleaning up | 0.000901 | 0.000033 | 0.000103 |
+----------------+----------+----------+------------+
mysql> select * from INFORMATION_SCHEMA.PROFILING where QUERY_ID=4;
+----------+-----+----------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+--------------+-------------+
| QUERY_ID | SEQ | STATE | DURATION | CPU_USER | CPU_SYSTEM | CONTEXT_VOLUNTARY | CONTEXT_INVOLUNTARY | BLOCK_OPS_IN | BLOCK_OPS_OUT | MESSAGES_SENT | MESSAGES_RECEIVED | PAGE_FAULTS_MAJOR | PAGE_FAULTS_MINOR | SWAPS | SOURCE_FUNCTION | SOURCE_FILE | SOURCE_LINE |
+----------+-----+----------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+--------------+-------------+
| 4 | 2 | starting | 0.001119 | 0.000065 | 0.000161 | 0 | 2 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | NULL | NULL | NULL |
| 4 | 3 | query end | 0.000017 | 0.000007 | 0.000009 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 4952 |
| 4 | 4 | closing tables | 0.000005 | 0.000004 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 5004 |
| 4 | 5 | freeing items | 0.000029 | 0.000010 | 0.000020 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | mysql_parse | sql_parse.cc | 5578 |
| 4 | 6 | cleaning up | 0.000901 | 0.000033 | 0.000103 | 0 | 16 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | dispatch_command | sql_parse.cc | 1864 |
+----------+-----+----------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+--------------+-------------+
5 rows in set, 1 warning (0.00 sec)
//所有的信息可以到INFORMATION_SCHEMA.PROFILING表中查询- 通过trace分析优化器如何选择执行计划
使用方式：首先打开trace，设置格式为json， 设置trace最大能够使用的内存大小， 避免分析过程中因为内存过小而不能完整显示
set OPTIMIZER_TRACE="enabled=on", END_MARKERS_IN_JSON=on;
set OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;
最后通过SELECT * FROM IMFORMATION_SCHEMA.OPTIMIZER_TRACE;6.确定问题， 进行优化sql
扫一扫
470
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
