一 、 SQL優(yōu)化步驟

1. 查看SQL 執(zhí)行頻率

通過show [session|global]status 命令可以提供服務(wù)器狀態(tài)信
息轮傍，也可以在操作系統(tǒng)上使用mysqladmin extended-status 命令獲得這些消息暂雹。show
[session|global] status 可以根據(jù)需要加上參數(shù)“session”或者“global”來顯示session 級(jí)（當(dāng)
前連接）的統(tǒng)計(jì)結(jié)果和global 級(jí)（自數(shù)據(jù)庫上次啟動(dòng)至今）的統(tǒng)計(jì)結(jié)果。如果不寫创夜，默認(rèn)使
用參數(shù)是“session”杭跪。

1 ) 查詢當(dāng)前連接中SQL執(zhí)行的次數(shù)

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

注：_______     ：為7個(gè)下劃線。

image.png

2) 查詢當(dāng)前數(shù)據(jù)庫中SQL執(zhí)行的次數(shù)

SHOW GLOBAL STATUS LIKE 'Com_______'; 

image.png

3) 查詢搜索引擎Innodb 中SQL執(zhí)行的次數(shù)

SHOW GLOBAL STATUS LIKE 'Innodb_rows_%';

image.png

2. 定位低效率的SQL語句

1）查看實(shí)時(shí)的SQL執(zhí)行情況

SHOW PROCESSLIST;

image.png

3. 通過 explain 分析執(zhí)行計(jì)劃

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

image.png

字段說明：

image.png

type

type顯示的是訪問類型驰吓，是較為重要的一個(gè)指標(biāo)涧尿，結(jié)果值從好到壞依次是：

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 ;

一般來說，得保證查詢至少達(dá)到range級(jí)別檬贰，最好能達(dá)到ref姑廉。

具體含義參考：MySQL EXPLAIN詳解

4. show profile 分析sql

Profiling是從 mysql5.0.3版本以后才開放的。此工具可用來查詢SQL執(zhí)行狀態(tài)翁涤，System lock和Table lock 花多少時(shí)間等等桥言，對(duì)定位一條語句的I/O消耗和CPU消耗 非常重要。(SQL 語句執(zhí)行所消耗的最大兩部分資源就是IO和CPU)

注意：show profile和show Profiles都是不建議使用的迷雪，在mysql后期的版本中可能會(huì)被刪除；官網(wǎng)建議使用Performance Schema

怎么使用

profile默認(rèn)關(guān)閉虫蝶，生產(chǎn)環(huán)境中也建議關(guān)閉章咧；查看當(dāng)前環(huán)境的profile設(shè)置。

show variables like '%profiling%';

image.png

profiling off : 表示profile關(guān)閉能真，ON :表示 開啟 赁严，profiling_history_size 15表示保存最近15條SQL的資源消耗情況。

1. 開啟profile功能粉铐，可以使用命令:

set global profiling = 1;

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

show profiles;

image.png

2. 查看某一條的具體情況
   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

3. 查看CPU耗費(fèi)時(shí)間

 SHOW PROFILE CPU FOR QUERY 105; 

5. trace 分析優(yōu)化器執(zhí)行計(jì)劃

MySQL5.6提供了對(duì)SQL的跟蹤trace疼约，通過trace文件能夠進(jìn)一步了解為什么優(yōu)化器選擇A執(zhí)行計(jì)劃而不是選擇B執(zhí)行計(jì)劃，幫助我們更好地理解優(yōu)化器行為蝙泼。

使用方式：首先打開trace程剥，設(shè)置格式為JSON，設(shè)置trace最大能夠使用的內(nèi)存大小汤踏，避免解析過程中因?yàn)槟J(rèn)內(nèi)存過小而不能夠完整顯示织鲸。

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

image.png

6. MySQL 中SQL語句解析順序

執(zhí)行時(shí) 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 >

機(jī)器讀取 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