本文详细介绍了MySQL数据库SQL优化的一般步骤，包括通过`SHOW STATUS`了解SQL执行频率，使用慢查询日志定位低效语句，利用`EXPLAIN`分析执行计划，借助`SHOW PROFILE`理解SQL执行过程，以及在MySQL 5.6中通过`TRACE`深入分析优化器选择。通过这些方法，可以有效地识别和解决SQL性能问题，提高数据库运行效率。
优化sql一般步骤
文章目录
sql提供：http://downloads.mysql.com/docs/sakila-db.zip
原文：《深入浅出MySQL++数据库开发、优化与管理维护》
压缩文件中3个文件：sakila.schema.sql是表结构创建，sakila-data是表数据，sakila.mwb是数据模型
1.通过shou status 命令了解各种sql执行频率
SHOW SESSION STATUS
或者
SHOW GLOBAL STATUS
session级: 当前连接的统计结果
global级: 自数据库上次启动至今的统计结果
两个级别可以省略不写，默认是session级
执行以下命令，like ‘Com_%’ 表示匹配Com_xxx的属性，因为这里面通常有我们关心的参数
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 | 0 |
| Com_alter_tablespace | 0 |
| Com_alter_user | 0 |
| Com_analyze | 0 |
| Com_begin | 0 |
| Com_binlog | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 0 |
| Com_change_master | 0 |
| Com_change_repl_filter | 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 | 0 |
| Com_create_trigger | 0 |
| Com_create_udf | 0 |
| Com_create_user | 0 |
| Com_create_view | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 0 |
| 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 | 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 | 0 |
| 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 | 1 |
| Com_set_option | 0 |
| 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 | 0 |
| Com_show_create_trigger | 0 |
| Com_show_databases | 0 |
| 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 | 0 |
| Com_show_profiles | 0 |
| Com_show_relaylog_events | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_storage_engines | 0 |
| Com_show_table_status | 0 |
| Com_show_tables | 0 |
| Com_show_triggers | 0 |
| Com_show_variables | 0 |
| Com_show_warnings | 0 |
| 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 | 0 |
| 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 |
+-----------------------------+-------+
可以看到这些类型语句的执行数量，通常关心以下参数:
-
Com_select:执行SELECT操作次数，一次查询只累加1。
-
Com_insert: 执行INSERT操作次数，对于批量插入是的INSERTCA操作，只累计一次。
-
Com_update: 执行UPDATE操作的次数。
-
Com_ delete:执行DELETE操作的次数。
上面这些参数对于所有存储引擎的表操作都会进行累计。下面这几个参数只是针对InnoDB存储引擎的，累加的算法也略有不同。
-
Innodb_ rows_read: SELECT 查询返回的行数。
-
Innodb_ rows_inserted: 执行INSERT操作插入的行数。
-
Innodb_ rows_ updated: 执行UPDATE操作更新的行数。
-
Innodb_ rows_ deleted: 执行DELETE操作删除的行数。
通过以上几个参数,可以很容易地了解当前数据库的应用是以插入更新为主还是以查询操作为主，以及各种类型的SQL大致的执行比例是多少。对于更新操作的计数，是对执行次数的计数，不论提交还是回滚都会进行累加。
对于事务型的应用，通过Com commit 和Com rollback 可以了解事务提交和回滚的情况，对于回滚操作非常频繁的数据库，可能意味着应用编写存在问题。
此外，以下几个参数便于用户了解数据库的基本情况。
- Connections:试图连接MySQL服务器的次数。
- Uptime:服务器工作时间。
- Slow_ queries: 慢查询的次数。
2.定位执行效率较低的语句
通过慢查询日志定位那些执行效率较低的SQL语句,用–log-slow-queries[= file_ name]选项启动时, mysqld写一个包含所有执行时间超过long query time 秒的SQL语句的日志文件。
慢查询日志在查询结束以后才记录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看SQL的执行情况，同时对一些锁表操作进行优化。
3.通过EXPLAIN分析低效sql的执行计划
对每个列简单的进行一下说明
- select_ type:表示SELECT的类型，常见的取值有SIMPLE (简单表，即不使用表连接或者子查询)、PRIMARY (主查询，即外层的查询)、UNION ( UNION中的第二个或者后面的查询语句)、SUBQUERY (子查询中的第一个SELECT)等。
- table:输出结果集的表。
- type:表示MySQL在表中找到所需行的方式，或者叫访问类型，常见类型如图18-1
所示。
从左至右，性能最差到最好。
-
type=ALL，全表扫描，Mysql遍历全表来找到匹配的行。
-
type=index, 索引全扫描，Mysql遍历整个索引来查询匹配的行。
-
type=range,索引范围扫描，常见于<、<=、>、>=、between等操作符。
-
type=ref,使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值的记录行，例如：
索引idx_ fk_ customer id是非唯一索引, 查询条件为等值查询条件customer id=35,所以扫描索引的类型为ref。ref还经常出现在join 操作中 -
type=eq__ref, 类似ref,区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配;简单来说,就是多表连接中使用primarykey或者uniqueindex作为关联条件。
-
type-const/system,单表中最多有一个匹配行，查询起来非常迅速，所以这个匹配行中的其他列的值可以被优化器在当前查询中当作常量来处理，例如，根据主键primary key 或者唯一索引unique index进行的查询。
-
type=NULL，MySQL不用访问表或者索引，直接就能够得到结果。
类型type还有其他值,如ref_ or_ null( 与ref类似，区别在于条件中包含对NULL的查询)index_ merge (索引合并优化) unique_subquery ( in的后面是一个查询主键字段的子查询)、index_ subquery (与unique_ subquery 类似，区别在于in的后面是查询非唯一索引字段的子查询)等。
- possible_ keys: 表示查询时可能使用的索引。
- key: 表示实际使用的索引。
- key_ len:使用到索引字段的长度。
- rows:扫描行的数量。
- Extra:执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息。
MySQL 4.1开始引入了explain extended命令，通过explain extended加上show warnings,我们能够看到在SQL真正被执行之前优化器做了哪些SQL改写:
explain EXTENDED SELECT sum(amount) FROM customer a, payment b WHERE 1=1 AND a.customer_id = b.customer_id AND email = 'JAVE.BENNETT@sakilacustomer.org'\G
注意：如果使用软件链接的Mysql,不用加\G,不然可能会报错
mysql> explain EXTENDED SELECT sum(amount) FROM customer a, payment b WHERE 1=1 AND a.customer_id = b.customer_id AND email = 'JAVE.BENNETT@sakilacustomer.org'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
partitions: NULL
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 599
filtered: 10.00
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
partitions: NULL
type: ref
possible_keys: idx_fk_customer_id
key: idx_fk_customer_id
key_len: 2
ref: sakila.a.customer_id
rows: 26
filtered: 100.00
Extra: NULL
2 rows in set, 2 warnings (0.00 sec)
mysql> show warnings\G
*************************** 1. row ***************************
Level: Warning
Code: 1681
Message: 'EXTENDED' is deprecated and will be removed in a future release.
*************************** 2. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select sum(`sakila`.`b`.`amount`) AS `sum(amount)` from `sakila`.`customer` `a` join `sakila`.`payment` `b` where ((`sakila`.`b`.`customer_id` = `sakila`.`a`.`customer_id`) and (`sakila`.`a`.`email` = 'JAVE.BENNETT@sakilacustomer.org'))
2 rows in set (0.00 sec)
explain extended输出结果中多了filtered 字段,同时从wamning的message字段能够看到优化器自动去除了1=1 恒成立的条件,也就是说优化器在改写SQL时会自动去掉恒成立的条件。在遇到复杂的SQL时，我们可以利用explain extended的结果来迅速地获取一个更清晰易读的SQL。
MySQL 5.1开始支持分区功能，同时explain 命令也增加了对分区的支持。可以通过explainpartitions命令查看SQL所访问的分区。例如，创建-一个 Hash分区的customer part 表，根据分区键查询的时候，能够看到explain partitions 的输出结果中有-列partitions,其中显示了SQL所需要访问的分区名字p2:
有的时候，仅仅通过explain分析执行计划并不能很快地定位SQL的问题，这个时候我们还可以选择profile联合分析。
4.通过 show profile 分析SQL
MySQL从5.0.37版本开始增加了对show profiles 和show profile 语句的支持。通过have_ profiling 参数，能够看到当前MySQL是否支持profile:
select @@have_profiling;
mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES |
+------------------+
1 row in set, 1 warning (0.00 sec)
默认profiling是关闭的，可以通过set语句在Session级别开profiling:
select @@profiling;
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set, 1 warning (0.00 sec)
通过profile,我们能够更清楚地了解SQL执行的过程。例如，我们知道MyISAM表有表元数据的缓存(例如行数，即COUNT()值),那么对一个MyISAM表的COUNT()是不需要
消耗太多资源的，而对于InnoDB 来说，就没有这种元数据缓存，COUNT(*)执行得较慢。
show profile能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。而MySQL5.6则通过trace文件进一步向我们展示 了优化器是如何选择执行计划的。
5.通过trace分析优化器如何选择执行计划
MySQL 5.6提供了对SQL的跟踪trace,通过trace文件能够进一步了解为什么优化器选择A执行计划而不选择B执行计划，帮助我们更好地理解优化器的行为。
使用方式:首先打开trace,设置格式为JSON,设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整显示。
接下来执行想做trace的SQL语句，例如想了解租赁表rental 中库存编号inventory_ id为4466的电影拷贝在出租日期rental_date 为2005-05-25 4:00:00~ 5:00:00之间出租的记录:
最后，检查INFORMATION_SCHEMA.OPTIMIZER_TRACE就可以知道MySQL是如何执行SQL的:
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G
最后会输出一个跟踪文件
6.确定问题并采取相应的优化措施
经过以上步骤,基本就可以确认问题出现的原因。此时用户可以根据情况采取相应的措施,进行优化以提高执行的效率。
例如在第3小节的例子中，已经可以确认是对客户表customer的全表扫描导致效率的不理想，那么对客户表customer的email字段创建索引，具体如下：
create index idx_email on customer(email)
创建索引后，再看一下这条语句的执行计划，具体如下：
mysql> create index idx_email on customer(email);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain EXTENDED SELECT sum(amount) FROM customer a, payment b WHERE 1=1 AND a.customer_id = b.customer_id AND email = 'JAVE.BENNETT@sakilacustomer.org'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
partitions: NULL
type: ref
possible_keys: PRIMARY,idx_email
key: idx_email
key_len: 203
ref: const
rows: 1
filtered: 100.00
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
partitions: NULL
type: ref
possible_keys: idx_fk_customer_id
key: idx_fk_customer_id
key_len: 2
ref: sakila.a.customer_id
rows: 26
filtered: 100.00
Extra: NULL
2 rows in set, 2 warnings (0.00 sec)
可以发现，建立索引后对客户表customer需要扫描的行数明显减少（rows变成了1）,可见索引的使用可以大大提高数据库的访问速度，尤其在表很庞大的时候这种优势更为明显。
扫一扫
1122
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
