MySQL优化SQL语句步骤
前言
在应用的的开发过程中，由于初期数据量小，开发人员写 SQL 语句时更重视功能上的实现，但是当应用系统正式上线后，随着生产数据量的急剧增长，很多 SQL 语句开始逐渐显露出性能问题，对生产的影响也越来越大，此时这些有问题的SQL 语句就成为整个系统性能的瓶颈，因此我们必须要对它们进行优化。一段程序出了bug我们能够通过调试工具进行查找bug并优化程序，那么对于SQL语句有哪些“调试”工具，又如何分析这些“调试”结果，然后对SQL语句进行优化呢？本文讲解对SQL语句的性能进行跟踪分析和优化的方法（工具）。
一、查看SQL语句执行频率的方法
show[session|global] status 可以根据需要加上参数“session”或者“global”来显示 session 级（当前连接）的统计结果和global 级（自数据库上次启动至今）的统计结果。如果不写，默认使用参数是“session”。
对于 show[session|global] status 语句查询出的结果，我们通常比较关注以下的统计参数。
如查询上表中相关统计次数的SQL语句如下：
show status like 'Com_______';
show status like 'Innodb_rows_%';
show status like 'Connections';
show status like 'Uptime';
show status like 'Slow_%';二、定位低效率执行的SQL语句的方法
1. 方式一：慢查询日志
通过慢查询日志定位那些执行效率较低的 SQL 语句，用 --log-slow-queries[=file_name] 选项启动时，mysqld 会写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件。关于慢查询后期会专门更一篇文章。
2. 方式二：show processlist
慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。
其中各个字段含义如下：
- Id：用户登录mysql时，系统分配的"connection_id"，可以使用函数connection_id()查看
- User：显示当前用户。
- Host：显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户。
- db：显示这个进程目前连接的是哪个数据库。
- Command：显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接
（connect）等。 - Time：显示这个状态持续的时间，单位是秒。
- State：显示使用当前连接的sql语句的状态，一个sql语句，以查询为例，可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成。
- Info：显示这个sql语句，是判断问题语句的一个重要依据。
三、explain分析执行计划
通过以上步骤查询到效率低的 SQL 语句后，可以通过explain或者desc命令获取 MySQL如何执行 select语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。分析该SQL语句低效的具体原因，进而去优化该低效的SQL语句。
通过explain 或者 desc 命令得到的具体每个字段的意义如下：
- id：id 字段是 select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。
id 情况有三种：1） id 相同：表示加载表的顺序是从上到下。2)）id 不同：id值越大，优先级越高，越先被执行。3）id 有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行。 - select_type：表示select 的类型，常见的取值，如下表所示，其中从上到下效率递减。
- table：展示这一行的数据是关于哪一张表的
- type：显示的是访问类型，是较为重要的一个指标，可取值在下表列出。
对于查询效率而言，结果值从最好到最坏依次是：system > const > eq_ref > ref > range > index > ALL；一般来说， 我们需要保证查询至少达到 range 级别， 最好达到ref 。 - possible_keys：显示可能应用在这张表的索引， 一个或多个。
- key：实际使用的索引， 如果为NULL， 则没有使用索引。
- key_len：表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好 。
- rows：扫描行的数量。
- extra：其他的额外的执行计划信息。几个重要的标识如下表
通过对每条SQL语句的以上各个字段的分析，得出该SQL语句的问题及优化方法。
四、show profile分析SQL
- show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。
- 通过 have_profiling 参数，能够看到当前MySQL是否支持profile
- 默认profiling是关闭的，可以通过set语句在Session级别开启profiling
- 通过profile，我们能够更清楚地了解SQL执行的过程。
- 通过show profile for query query_id 语句可以查看到该SQL执行过程中每个线程的状态和消耗的时间，query_id 是通过show profiles得到每条SQL的Query_ID
- 在获取到最消耗时间的线程状态后，MySQL支持进一步选择all、cpu、block io 、context switch、page faults等明细类型类查看MySQL在使用什么资源上耗费了过高的时间。例如，选择查看CPU的耗费时间 ：
五、trace分析优化器执行计划
通过trace文件能够看到优化后的查询语句是什么样的，进一步了解为什么优化器选择A计划, 而不是选择B计划。
- 打开trace方法：设置格式为 JSON，并设置trace最大能够使用的内存大小。避免解析过程中因为默认内存过小而不能够完整展示。
SET optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;
- 查看trace文件内容
select * from information_schema.optimizer_trace\G;- 举例：首先对city进行查询，然后查看trace文件，了解经优化器优化后的SQL语句
select * from city;mysql> select * from information_schema.optimizer_trace\G;
*************************** 1. row ***************************
QUERY: select * from city
TRACE: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `city`.`city_id` AS `city_id`,`city`.`city_name` AS `city_name`,`city`.`country_id` AS `country_id` from `city`"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"table_dependencies": [
{
"table": "`city`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"rows_estimation": [
{
"table": "`city`",
"table_scan": {
"rows": 4,
"cost": 0.25
} /* table_scan */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`city`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 4,
"access_type": "scan",
"resulting_rows": 4,
"cost": 0.65,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 4,
"cost_for_plan": 0.65,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": null,
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`city`",
"attached": null
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"finalizing_table_conditions": [
] /* finalizing_table_conditions */
},
{
"refine_plan": [
{
"table": "`city`"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": {
"select#": 1,
"steps": [
] /* steps */
} /* join_execution */
}
] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)
