在應用的的開發(fā)過程中寓盗，由于初期數(shù)據(jù)量小南吮，開發(fā)人員寫 SQL 語句時更重視功能上的實現(xiàn)蜜猾，但是當應用系統(tǒng)正式上線后，隨著生產(chǎn)數(shù)據(jù)量的急劇增長柿菩，很多 SQL 語句開始逐漸顯露出性能問題戚嗅，對生產(chǎn)的影響也越來越大，此時這些有問題的 SQL 語句就成為整個系統(tǒng)性能的瓶頸枢舶，因此我們必須要對它們進行優(yōu)化懦胞，本章將詳細介紹在 MySQL中優(yōu)化 SQL 語句的方法。

當面對一個有 SQL 性能問題的數(shù)據(jù)庫時凉泄，我們應該從何處入手來進行系統(tǒng)的分析躏尉，使得能夠盡快定位問題 SQL 并盡快解決問題。

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

MySQL 客戶端連接成功后后众，通過 show [session|global] status 命令可以提供服務器狀態(tài)信息胀糜。show[session|global] status 可以根據(jù)需要加上參數(shù)“session”或者“global”來顯示 session 級（當前連接）的計結(jié)果和global 級（自數(shù)據(jù)庫上次啟動至今）的統(tǒng)計結(jié)果。如果不寫蒂誉，默認使用參數(shù)是“session”教藻。
下面的命令顯示了當前 session 中所有統(tǒng)計參數(shù)的值：

show status like 'Com_______';

image.png

show status like 'Innodb_rows_%'; 

image.png

Com_xxx 表示每個 xxx 語句執(zhí)行的次數(shù)，我們通常比較關(guān)心的是以下幾個統(tǒng)計參數(shù)右锨。

Com_*** : 這些參數(shù)對于所有存儲引擎的表操作都會進行累計。
Innodb_*** : 這幾個參數(shù)只是針對InnoDB 存儲引擎的狮斗，累加的算法也略有不同绽乔。

2. 定位低效率執(zhí)行sql

可以通過以下兩種方式定位執(zhí)行效率較低的 SQL 語句。

• 慢查詢?nèi)罩?: 通過慢查詢?nèi)罩径ㄎ荒切﹫?zhí)行效率較低的 SQL 語句碳褒，用--log-slow-queries[=file_name]選項啟動時折砸，mysqld 寫一個包含所有執(zhí)行時間超過 long_query_time 秒的 SQL 語句的日志文件。具體可以查看本書第 26 章中日志管理的相關(guān)部分沙峻。
• show processlist : 慢查詢?nèi)罩驹诓樵兘Y(jié)束以后才紀錄睦授，所以在應用反映執(zhí)行效率出現(xiàn)問題的時候查詢慢查詢?nèi)罩静⒉荒芏ㄎ粏栴}，可以使用show processlist命令查看當前MySQL在進行的線程摔寨，包括線程的狀態(tài)去枷、是否鎖表等，可以實時地查看 SQL 的執(zhí)行情況是复，同時對一些鎖表操作進行優(yōu)化删顶。

image.png

1） id列，用戶登錄mysql時淑廊，系統(tǒng)分配的"connection_id"逗余，可以使用函數(shù)connection_id()查看

2） user列，顯示當前用戶季惩。如果不是root录粱，這個命令就只顯示用戶權(quán)限范圍的sql語句

3） host列，顯示這個語句是從哪個ip的哪個端口上發(fā)的画拾，可以用來跟蹤出現(xiàn)問題語句的用戶

4） db列关摇，顯示這個進程目前連接的是哪個數(shù)據(jù)庫

5） command列，顯示當前連接的執(zhí)行的命令碾阁，一般取值為休眠（sleep）输虱，查詢（query），連接（connect）等

6） time列脂凶，顯示這個狀態(tài)持續(xù)的時間宪睹，單位是秒

7） state列愁茁，顯示使用當前連接的sql語句的狀態(tài)，很重要的列亭病。state描述的是語句執(zhí)行中的某一個狀態(tài)鹅很。一個sql語句，以查詢?yōu)槔锾赡苄枰?jīng)過copying to tmp table促煮、sorting result、sending data等狀態(tài)才可以完成

8） info列整袁，顯示這個sql語句菠齿，是判斷問題語句的一個重要依據(jù)

3. explain分析執(zhí)行計劃

通過以上步驟查詢到效率低的 SQL 語句后，可以通過 EXPLAIN或者 DESC命令獲取 MySQL如何執(zhí)行 SELECT 語句的信息坐昙，包括在 SELECT 語句執(zhí)行過程中表如何連接和連接的順序绳匀。

查詢SQL語句的執(zhí)行計劃 ：

explain  select * from tb_item where id = 1;

image.png

explain  select * from tb_item where title = '阿爾卡特 (OT-979) 冰川白 聯(lián)通3G手機3'; 

image.png

3.1 環(huán)境準備

image.png

創(chuàng)建表sql

CREATE TABLE `t_role`
(
    `id`          varchar(32) NOT NULL,
    `role_name`   varchar(255) DEFAULT NULL,
    `role_code`   varchar(255) DEFAULT NULL,
    `description` varchar(255) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `unique_role_name` (`role_name`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;
CREATE TABLE `t_user`
(
    `id`       varchar(32) NOT NULL,
    `username` varchar(45) NOT NULL,
    `password` varchar(96) NOT NULL,
    `name`     varchar(45) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `unique_user_username` (`username`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;
CREATE TABLE `user_role`
(
    `id`      int(11) NOT NULL auto_increment,
    `user_id` varchar(32) DEFAULT NULL,
    `role_id` varchar(32) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `fk_ur_user_id` (`user_id`),
    KEY `fk_ur_role_id` (`role_id`),
    CONSTRAINT `fk_ur_role_id` FOREIGN KEY (`role_id`) REFERENCES `t_role` (`id`) ON
        DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT `fk_ur_user_id` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`) ON
        DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

插入sql

insert into `t_user` (`id`, `username`, `password`, `name`)
values ('1', 'super', '$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe', '
超級管理員');
insert into `t_user` (`id`, `username`, `password`, `name`)
values ('2', 'admin', '$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe', '
系統(tǒng)管理員');
insert into `t_user` (`id`, `username`, `password`, `name`)
values ('3', 'itcast', '$2a$10$8qmaHgUFUAmPR5pOuWhYWOr291WJYjHelUlYn07k5ELF8ZCrW0Cui',
        'test02');
insert into `t_user` (`id`, `username`, `password`, `name`)
values ('4', 'stu1', '$2a$10$pLtt2KDAFpwTWLjNsmTEi.oU1yOZyIn9XkziK/y/spH5rftCpUMZa', '學
生1');
insert into `t_user` (`id`, `username`, `password`, `name`)
values ('5', 'stu2', '$2a$10$nxPKkYSez7uz2YQYUnwhR.z57km3yqKn3Hr/p1FR6ZKgc18u.Tvqm', '學
生2');
insert into `t_user` (`id`, `username`, `password`, `name`)
values ('6', 't1', '$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe', '老師
1');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`)
VALUES ('5', '學
生', 'student', '學生');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`)
VALUES ('7', '老
師', 'teacher', '老師');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`)
VALUES ('8', '教
學管理員', 'teachmanager', '教學管理員');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`)
VALUES ('9', '管
理員', 'admin', '管理員');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`)
VALUES ('10', '超
級管理員', 'super', '超級管理員');

INSERT INTO user_role(id, user_id, role_id)
VALUES (NULL, '1', '5'),
       (NULL, '1', '7'),
       (NULL, '2', '8'),
       (NULL, '3', '9'),
       (NULL, '4', '8'),
       (NULL, '5', '10');

3.2 explain 之 id

id 字段是 select查詢的序列號梨州，是一組數(shù)字，表示的是查詢中執(zhí)行select子句或者是操作表的順序田轧。id 情況有三種：
1） id 相同表示加載表的順序是從上到下暴匠。

explain select * from t_role r, t_user u, user_role ur where r.id = ur.role_id and u.id = ur.user_id ;

image.png

2） id 不同id值越大，優(yōu)先級越高傻粘，越先被執(zhí)行每窖。

EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = 'stu1'))

image.png

3） id 有相同，也有不同弦悉，同時存在窒典。id相同的可以認為是一組，從上往下順序執(zhí)行稽莉；在所有的組中瀑志，id的值越大，優(yōu)先級越高，越先執(zhí)行劈猪。

EXPLAIN SELECT * FROM t_role r , (SELECT * FROM user_role ur WHERE ur.`user_id` = '2') a WHERE r.id = a.role_id ;

image.png

3.3 explain 之 select_type

表示 SELECT 的類型昧甘，常見的取值，如下表所示：

3.4 explain 之 table

展示這一行的數(shù)據(jù)是關(guān)于哪一張表的

3.5 explain 之 type

type 顯示的是訪問類型肘习，是較為重要的一個指標，可取值為：

結(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

一般來說馅闽， 我們需要保證查詢至少達到 range 級別， 最好達到ref 。

3.6 explain 之 key

possible_keys : 顯示可能應用在這張表的索引福也， 一個或多個孝冒。

key ： 實際使用的索引， 如果為NULL拟杉， 則沒有使用索引庄涡。

key_len : 表示索引中使用的字節(jié)數(shù)， 該值為索引字段最大可能長度搬设，并非實際使用長度穴店，在不損失精確性的前提下， 長度越短越好 拿穴。

3.7 explain 之 rows

掃描行的數(shù)量

3.8 explain 之 extra

其他的額外的執(zhí)行計劃信息泣洞，在該列展示 。

4. show profile分析SQL

Mysql從5.0.37版本開始增加了對 show profiles 和 show profile 語句的支持椿每。show profiles 能夠在做SQL優(yōu)化時幫助我們了解時間都耗費到哪里去了伊者。
通過 have_profiling 參數(shù)，能夠看到當前MySQL是否支持profile：

image.png

默認profiling是關(guān)閉的间护，可以通過set語句在Session級別開啟profiling：

image.png

set profiling=1; //開啟profiling 開關(guān)亦渗；

通過profile，我們能夠更清楚地了解SQL執(zhí)行的過程汁尺。
首先法精，我們可以執(zhí)行一系列的操作，如下圖所示：

show databases;
use db01;
show tables;
select * from tb_item where id < 5;
select count(*) from tb_item;

執(zhí)行完上述命令之后均函，再執(zhí)行show profiles 指令亿虽， 來查看SQL語句執(zhí)行的耗時：

image.png

通過show profile for query query_id 語句可以查看到該SQL執(zhí)行過程中每個線程的狀態(tài)和消耗的時間：

image.png

TIP ：
Sending data 狀態(tài)表示MySQL線程開始訪問數(shù)據(jù)行并把結(jié)果返回給客戶端，而不僅僅是返回個客戶端苞也。由于在Sending data狀態(tài)下洛勉，MySQL線程往往需要做大量的磁盤讀取操作，所以經(jīng)常是整各查詢中耗時最長的狀態(tài)如迟。

在獲取到最消耗時間的線程狀態(tài)后收毫，MySQL支持進一步選擇all攻走、cpu、block io 此再、context switch昔搂、page faults等明細類型類查看MySQL在使用什么資源上耗費了過高的時間。例如输拇，選擇查看CPU的耗費時間 ：

image.png

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

MySQL5.6提供了對SQL的跟蹤trace, 通過trace文件能夠進一步了解為什么優(yōu)化器選擇A計劃, 而不是選擇B計劃摘符。

打開trace ， 設(shè)置格式為 JSON策吠，并設(shè)置trace最大能夠使用的內(nèi)存大小逛裤，避免解析過程中因為默認內(nèi)存過小而不能
夠完整展示。

SET optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;

執(zhí)行SQL語句 ：

select * from tb_item where id < 4;

最后猴抹， 檢查information_schema.optimizer_trace就可以知道MySQL是如何執(zhí)行SQL的 ：

select * from information_schema.optimizer_trace\G; 

*************************** 1. row ***************************
QUERY: select * from tb_item where id < 4
TRACE: {
 "steps": [
 {
   "join_preparation": {
    "select#": 1,
    "steps": [
    {
      "expanded_query": "/* select#1 */ select `tb_item`.`id` AS
`id`,`tb_item`.`title` AS `title`,`tb_item`.`price` AS `price`,`tb_item`.`num` AS
`num`,`tb_item`.`categoryid` AS `categoryid`,`tb_item`.`status` AS
`status`,`tb_item`.`sellerid` AS `sellerid`,`tb_item`.`createtime` AS
`createtime`,`tb_item`.`updatetime` AS `updatetime` from `tb_item` where
(`tb_item`.`id` < 4)"
    }
   ] /* steps */
  } /* join_preparation */
 },
 {
   "join_optimization": {
    "select#": 1,
    "steps": [
    {
      "condition_processing": {
       "condition": "WHERE",
       "original_condition": "(`tb_item`.`id` < 4)",
       "steps": [
       {
         "transformation": "equality_propagation",
         "resulting_condition": "(`tb_item`.`id` < 4)"
       },
       {
         "transformation": "constant_propagation",
         "resulting_condition": "(`tb_item`.`id` < 4)"
       },
       {
       "transformation": "trivial_condition_removal",
         "resulting_condition": "(`tb_item`.`id` < 4)"
       }
      ] /* steps */
     } /* condition_processing */
    },
    {
      "table_dependencies": [
      {
        "table": "`tb_item`",
        "row_may_be_null": false,
        "map_bit": 0,
        "depends_on_map_bits": [
       ] /* depends_on_map_bits */
      }
     ] /* table_dependencies */
    },
    {
      "ref_optimizer_key_uses": [
     ] /* ref_optimizer_key_uses */
    },
    {
      "rows_estimation": [
      {
        "table": "`tb_item`",
        "range_analysis": {
         "table_scan": {
          "rows": 9816098,
          "cost": 2.04e6
        } /* table_scan */,
         "potential_range_indices": [
         {
           "index": "PRIMARY",
           "usable": true,
           "key_parts": [
            "id"
          ] /* key_parts */
         }
        ] /* potential_range_indices */,
         "setup_range_conditions": [
        ] /* setup_range_conditions */,
         "group_index_range": {
          "chosen": false,
          "cause": "not_group_by_or_distinct"
        } /* group_index_range */,
         "analyzing_range_alternatives": {
          "range_scan_alternatives": [
          {
            "index": "PRIMARY",
            "ranges": [
             "id < 4"
           ] /* ranges */,
            "index_dives_for_eq_ranges": true,
            "rowid_ordered": true,
            "using_mrr": false,
            "index_only": false,
            "rows": 3,
            "cost": 1.6154,
            "chosen": true
          }
         ] /* range_scan_alternatives */,
          "analyzing_roworder_intersect": {
           "usable": false,
           "cause": "too_few_roworder_scans"
         } /* analyzing_roworder_intersect */
        } /* analyzing_range_alternatives */,
         "chosen_range_access_summary": {
          "range_access_plan": {
           "type": "range_scan",
           "index": "PRIMARY",
           "rows": 3,
           "ranges": [
            "id < 4"
          ] /* ranges */
         } /* range_access_plan */,
          "rows_for_plan": 3,
          "cost_for_plan": 1.6154,
          "chosen": true
        } /* chosen_range_access_summary */
       } /* range_analysis */
      }
     ] /* rows_estimation */
    },
    {
      "considered_execution_plans": [
      {
        "plan_prefix": [
       ] /* plan_prefix */,
        "table": "`tb_item`",
        "best_access_path": {
         "considered_access_paths": [
         {
           "access_type": "range",
           "rows": 3,
           "cost": 2.2154,
           "chosen": true
         }
        ] /* considered_access_paths */
       } /* best_access_path */,
        "cost_for_plan": 2.2154,
        "rows_for_plan": 3,
        "chosen": true
      }
     ] /* considered_execution_plans */
    },
    {
      "attaching_conditions_to_tables": {
       "original_condition": "(`tb_item`.`id` < 4)",
       "attached_conditions_computation": [
      ] /* attached_conditions_computation */,
       "attached_conditions_summary": [
       {
         "table": "`tb_item`",
         "attached": "(`tb_item`.`id` < 4)"
       }
      ] /* attached_conditions_summary */
     } /* attaching_conditions_to_tables */
    },
    {
      "refine_plan": [
      {
        "table": "`tb_item`",
        "access_type": "range"
      }
     ] /* refine_plan */
    }
   ] /* steps */
  } /* join_optimization */
 },
 {
   "join_execution": {
    "select#": 1,
    "steps": [
   ] /* steps */
  } /* join_execution */
 }
] /* steps */
}