hello,大家早上好,中午好,晚上好。今天2020(愛你愛你)第一天祝大家新年快樂。今天也是雞排妹帶大家2020年的第1天。好了,去年的上好雞排妹帶大家瞭解SQL優化,現在繼續開始帶大家繼續努力學習SQL優化。
![優化SQL 經典案例!!!(下)](http://p2.ttnews.xyz/loading.gif)
3.3 explain分析執行計劃
通過以上步驟查詢到效率低的 SQL 語句後,可以通過 EXPLAIN或者 DESC命令獲取 MySQL如何執行 SELECT 語句的信息,包括在 SELECT 語句執行過程中表如何連接和連接的順序。
查詢SQL語句的執行計劃 :
![優化SQL 經典案例!!!(下)](http://p2.ttnews.xyz/loading.gif)
explain select * from tb_item where title ='new2 - 阿爾卡特 (OT-927) 炭黑 聯通3G手機 雙卡雙待';
CREATE TABLE `t_role` (
下面介紹一下上面字段是什麼意思。
3.3.1 環境準備
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;
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',' 系統管理員');
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.3.2 explain 之 id
id 字段是 select查詢的序列號,是一組數字,表示的是查詢中執行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 ;
2) id 不同id值越大,優先級越高,越先被執行。
3) id 有相同,也有不同,同時存在。id相同的可以認為是一組,從上往下順序執行;在所有的組中,id的值越 大,優先級越高,越先執行。
3.3.3 explain 之 select_type
表示 SELECT 的類型,常見的取值,如下表所示:
3.3.4 explain 之 table
展示這一行的數據是關於哪一張表的
3.3.5 explain 之 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
<strong>一般來說, 我們需要保證查詢至少達到 range 級別, 最好達到ref 。
3.3.6 explain 之 key
possible_keys : 顯示可能應用在這張表的索引, 一個或多個。
key : 實際使用的索引, 如果為NULL, 則沒有使用索引。
key_len : 表示索引中使用的字節數, 該值為索引字段最大可能長度,並非實際使用長度,在不損失精確性的前 提下, 長度越短越好 。
3.3.7 explain 之 rows
掃描行的數量。
3.3.8 explain 之 extra
其他的額外的執行計劃信息,在該列展示 。
3.4 show profile分析SQL
Mysql從5.0.37版本開始增加了對 show profiles 和 show profile 語句的支持。show profiles 能夠在做SQL優化時幫助我們瞭解時間都耗費到哪裡去了。
通過 have_profiling 參數,能夠看到當前MySQL是否支持profile:
select @@have_profiling;
默認profiling是關閉的,可以通過set語句在Session級別開啟profiling:
select @@profiling;
set profiling=1; //開啟profiling 開關;
通過profile,我們能夠更清楚地瞭解SQL執行的過程。
首先,我們可以執行一系列的操作,如下圖所示:
show databases;
use day_demo;
show tables;
select * from tb_item where id < 5;
select count(*) from tb_item;
執行完上述命令之後,再執行show profiles 指令, 來查看SQL語句執行的耗時:
通過show profile for query query_id 語句可以查看到該SQL執行過程中每個線程的狀態和消耗的時間:
通過show profile for query query_id 語句可以查看到該SQL執行過程中每個線程的狀態和消耗的時間:
TIP :
Sending data 狀態表示MySQL線程開始訪問數據行並把結果返回給客戶端,而不僅僅是返回個客戶端。由於在Sending data狀態下,MySQL線程往往需要做大量的磁盤讀取操作,所以經常是整各查詢中耗時最長的狀態。
在獲取到最消耗時間的線程狀態後,MySQL支持進一步選擇all、cpu、block io 、context switch、page faults等 明細類型類查看MySQL在使用什麼資源上耗費了過高的時間。例如,選擇查看CPU的耗費時間 :
3.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;
執行SQL語句 :
select * from tb_item where id < 4;
最後, 檢查information_schema.optimizer_trace就可以知道MySQL是如何執行SQL的 :
select * from information_schema.optimizer_trace\\G;
閱讀更多 程序美學 的文章