MySQL 中SQL 优化

一、 SQL优化步骤

1. 查看SQL 执行频率

通过show [session|global]status 命令可以提供服务器状态信
息，也可以在操作系统上使用mysqladmin extended-status 命令获得这些消息。show
[session|global] status 可以根据需要加上参数“session”或者“global”来显示session 级（当
前连接）的统计结果和global 级（自数据库上次启动至今）的统计结果。如果不写，默认使
用参数是“session”。

1 ) 查询当前连接中SQL执行的次数

SHOW STATUS LIKE 'Com_______';
或
SHOW SESSION STATUS LIKE 'Com_______';

注：_______     ：为7个下划线。

image.png

2) 查询当前数据库中SQL执行的次数

SHOW GLOBAL STATUS LIKE 'Com_______';

image.png

3) 查询搜索引擎Innodb 中SQL执行的次数

SHOW GLOBAL STATUS LIKE 'Innodb_rows_%';

image.png

2. 定位低效率的SQL语句

1）查看实时的SQL执行情况

SHOW PROCESSLIST;

image.png

3. 通过 explain 分析执行计划

EXPLAIN  SELECT * FROM uk_chat_message c WHERE c.channel='weibo';

image.png

字段说明：

image.png

type

type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：

null > system > const > eq_ref  > ref  > fulltext  > ref_or_null  > index_merge  > unique_subquery  > index_subquery  > range  > index  > ALL ;

 system > const >  eq_ref  > ref  >  range  > index  > ALL ;

一般来说，得保证查询至少达到range级别，最好能达到ref。

具体含义参考：MySQL EXPLAIN详解

4. show profile 分析sql

Profiling是从 mysql5.0.3版本以后才开放的。此工具可用来查询SQL执行状态，System lock和Table lock 花多少时间等等，对定位一条语句的I/O消耗和CPU消耗非常重要。(SQL 语句执行所消耗的最大两部分资源就是IO和CPU)

注意：show profile和show Profiles都是不建议使用的，在mysql后期的版本中可能会被删除；官网建议使用Performance Schema

怎么使用

profile默认关闭，生产环境中也建议关闭；查看当前环境的profile设置。

show variables like '%profiling%';

image.png

profiling off : 表示profile关闭，ON :表示开启，profiling_history_size 15表示保存最近15条SQL的资源消耗情况。

开启profile功能，可以使用命令:

set global profiling = 1;

然后就可以使用下面命令:

show profiles;

image.png

查看某一条的具体情况
SQL格式为：

SHOW PROFILE [type [, type] ... ]
    [FOR QUERY n]
    [LIMIT row_count [OFFSET offset]]

type: {
    ALL
  | BLOCK IO
  | CONTEXT SWITCHES
  | CPU
  | IPC
  | MEMORY
  | PAGE FAULTS
  | SOURCE
  | SWAPS
}

例子：

 SHOW PROFILE FOR QUERY 105;

105 ：表示的是：Query_ID .

image.png

查看CPU耗费时间

 SHOW PROFILE CPU FOR QUERY 105;

5. trace 分析优化器执行计划

MySQL5.6提供了对SQL的跟踪trace，通过trace文件能够进一步了解为什么优化器选择A执行计划而不是选择B执行计划，帮助我们更好地理解优化器行为。

使用方式：首先打开trace，设置格式为JSON，设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整显示。

SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;
SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;

image.png

6. MySQL 中SQL语句解析顺序

执行时 SQL 语句的顺序：

SELECT DISTINCT
     < select_list >
 FROM
    < left_table > < join_type >
 JOIN < right_table > ON < join_condition >
 WHERE
     < where_condition >
 GROUP BY
     < group_by_list >
 HAVING
     < having_condition >
 ORDER BY
     < order_by_condition >
 LIMIT < limit_number >

机器读取 SQL 解析的顺序：

1 . FROM <left_table>
2 . ON <join_condition>
3 . <join_type> JOIN <right_table>
4 . WHERE <where_condition>
5 . GROUP BY <group_by_list>
6 . HAVING <having_condition>
7 . SELECT
8 . DISTINCT <select_list>
9 . ORDER BY <order_by_condition>
10 . LIMIT <limit_number>

SQL 解析顺序：

image.png

MySQL 中SQL 优化