1.檢測虛擬機環境 1.1 bit檢測命令:getconf LONG_BIT(建議使用64bit做開發) 1.2 gcc環境檢測:gcc -v 如果不具備gcc環境,則使用yum -y install gcc- c++命令進行c++環境的安裝 2.開始安裝 2.1 下載redis: 進入官網找到下載 ...
一、概述
使用mysqldumpslow工具定位到慢查詢語句之後,可以使用explain或describe工具做針對性的分析查詢語句。
MySQL種有專門負責優化SELECT語句的優化器模塊:通過計算分析系統中收集到的統計信息,為客戶端請求的Query提供他認為最優的執行計劃。
這個執行計劃展示了接下來具體執行查詢的方式,比如多表連接的順序是什麼,對於每個表採用什麼訪問方法來具體執行查詢等等。MySQL提供了EXPLAIN語句來幫助我們查看某個查詢語句的具體執行計劃,看懂EXPLAIN語句的各個輸出項,可以有針對性的提升我們查詢語句的性能。
二、基本語法
explain 查詢語句; select、insert、update、delete都可使用
例如:explain select * from user limit 10;
explain語句輸出的各列作用如下:
列名 | 描述 |
id | 在一個大的查詢語句種,每一個select關鍵字都對應一個唯一的id |
select_type | select關鍵字對應的那個查詢的類型 |
table | 表名 |
partitions | 匹配的分區信息 |
type | 針對單表的訪問方法 |
possible_keys | 可能用到的索引 |
key | 實際上用到的索引 |
key_len | 實際用到的索引長度 |
ref | 當使用索引列等值查詢時,與索引列進行等值匹配的對象信息 |
rows | 預估的需要讀取的記錄數 |
filtered | 某個表經過搜索條件過濾後剩餘記錄條數的百分比 |
exea | 一些額外信息 |
三、數據準備
執行以下代碼,為分析explain做準備
#創建表 CREATE TABLE s1 ( id INT AUTO_INCREMENT, key1 VARCHAR(100), key2 INT, key3 VARCHAR(100), key_part1 VARCHAR(100), key_part2 VARCHAR(100), key_part3 VARCHAR(100), common_field VARCHAR(100), PRIMARY KEY (id), INDEX idx_key1 (key1), UNIQUE INDEX idx_key2 (key2), INDEX idx_key3 (key3), INDEX idx_key_part(key_part1, key_part2, key_part3) ) ENGINE=INNODB CHARSET=utf8; CREATE TABLE s2 ( id INT AUTO_INCREMENT, key1 VARCHAR(100), key2 INT, key3 VARCHAR(100), key_part1 VARCHAR(100), key_part2 VARCHAR(100), key_part3 VARCHAR(100), common_field VARCHAR(100), PRIMARY KEY (id), INDEX idx_key1 (key1), UNIQUE INDEX idx_key2 (key2), INDEX idx_key3 (key3), INDEX idx_key_part(key_part1, key_part2, key_part3) ) ENGINE=INNODB CHARSET=utf8; #創建存儲函數: DELIMITER // CREATE FUNCTION rand_string1(n INT) RETURNS VARCHAR(255) #該函數會返回一個字元串 BEGIN DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; DECLARE return_str VARCHAR(255) DEFAULT ''; DECLARE i INT DEFAULT 0; WHILE i < n DO SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1)); SET i = i + 1; END WHILE; RETURN return_str; END // DELIMITER ; SET GLOBAL log_bin_trust_function_creators=1; #創建存儲過程: DELIMITER // CREATE PROCEDURE insert_s1 (IN min_num INT (10),IN max_num INT (10)) BEGIN DECLARE i INT DEFAULT 0; SET autocommit = 0; REPEAT SET i = i + 1; INSERT INTO s1 VALUES( (min_num + i), rand_string1(6), (min_num + 30 * i + 5), rand_string1(6), rand_string1(10), rand_string1(5), rand_string1(10), rand_string1(10)); UNTIL i = max_num END REPEAT; COMMIT; END // DELIMITER ; DELIMITER // CREATE PROCEDURE insert_s2 (IN min_num INT (10),IN max_num INT (10)) BEGIN DECLARE i INT DEFAULT 0; SET autocommit = 0; REPEAT SET i = i + 1; INSERT INTO s2 VALUES( (min_num + i), rand_string1(6), (min_num + 30 * i + 5), rand_string1(6), rand_string1(10), rand_string1(5), rand_string1(10), rand_string1(10)); UNTIL i = max_num END REPEAT; COMMIT; END // DELIMITER ; #調用存儲過程 CALL insert_s1(10001,10000); CALL insert_s2(10001,10000); SELECT COUNT(*) FROM s1; SELECT COUNT(*) FROM s2;
四、explain各列詳解
1、table:表名
查詢的每一個行記錄對應著一個單表
explain select * from s1;
explain select * from s1 inner join s2;
2、id:在一個大的查詢語句中,每一個select關鍵字都對應一個唯一的id
explain select * from s1where key1 = 'a';
總結:
id如果相同,可以認為是一組,從上往下執行
在所有組種,id越大,優先順序越高,越先執行
每一個id號碼表示一趟獨立的查詢,一個SQL查詢的趟數越少越好。
3、select_type:select關鍵字對應的那個查詢的類型,確定小查詢在大查詢種扮演什麼角色
①查詢語句中不包含UNION或者子查詢的查詢都算是SIMPLE類型
explain select * from s1;
②連接查詢也是SIMPLE
explain select * from s1 inner join s2;
③對於包含UNION、UNION ALL的大查詢來說,它是由幾個小查詢組成的,其中最左邊的查詢的select_type值就是PRIMARY,其餘的小查詢的select_type值為UNION
④MySQL選擇使用臨時表來完成UNION查詢的去重工作,針對該臨時表的查詢的select_type的值是UNION RESULT
UNION 具有去重操作,UNION ALL沒有去重操作,所以UNION會有一個臨時表
⑤如果包含子查詢的查詢語句不能轉化為多表聯查的形式,並且該子查詢不是相關子查詢,該子查詢的第一個select關鍵字的查詢的select_type就是SUBQUERY
⑥如果包含子查詢的查詢語句不能轉化為多表聯查的形式,並且該子查詢是相關子查詢,該子查詢的第一個select關鍵字的查詢的select_type就是DEPENDENT SUBQUERY
⑦在包含`UNION`或者`UNION ALL`的大查詢中,如果各個小查詢都依賴於外層查詢的話,那除了最左邊的那個小查詢之外,其餘的小查詢的`select type `的值就是`DEPENDENT UNION
⑧對於包含派生表的查詢,該派生表對應的子查詢的select_type就是DERIVED
⑨當查詢優化器在執行包含子查詢的語句時,選擇將子查詢物化之後與外層查詢進行連接查詢時,該子查詢對應的`select type`屬性就是‘MATERIALIZED~
4、type
執行計劃的一條記錄就代表著MySQL對某個表的執行查詢時的訪問方法,又稱°訪問類型”,其中的type列就表明瞭這個訪問方法是啥,是較為重要的一個指標。比如,看到type列的值是ref,表明MysQL即將使用ref訪問方法來執行對s1表的查詢。
完整的訪問方法如下: system,const,eq_ref,ref, fulltext,ref_or_null , index_merge ,unique_subquery , index_subquery , range , index,ALL。
①system
當表中只有一條記錄並且該表使用的存儲引擎統計的書數據是精確的,比如MyISAM、Memory,那麼對該表的訪問方法就是system
②const
當我們根據主鍵或唯一的二級索引列與常數進行等值匹配時,對單表的訪問方法就是const
explain select * from s1 where id = 10005; explain select * from s1 where key2 = 10066;
③eq_ref
在連接查詢時,如果被驅動表是通過主鍵或者唯一二級索引列等值匹配的方式進行訪問的(如果該主鍵或者唯一二級索引是聯合索引的話,所有的索引列都必須進行等值比較),則對該被驅動表的訪問方法就是`eg ref`
explain select * from s1 inner join s2 on s1.id = s2.id;
④ref
當通過普通的二級索引列與常量進行等值匹配時來查詢某個表,那麼對該表的訪問方法就可能是`ref'
explain select * from s1 where key1 = 'a';
⑤unique_subquery
unique subquery`是針對在一些包含`IN`子查詢的查詢語句中,如果查詢優化器決定將`IN`子查詢
轉換為`ExISTS`子查詢,而且子查詢可以使用到主鍵進行等值匹配的話,那麼該子查詢執行計劃的'type '列的值就是`unique_ subquery
⑥如果使用索引獲取某些範圍區間的記錄,那麼就可能用到range訪問方法
⑦index
當我們可以使用索引覆蓋,但需要掃描全部的索引記錄時,該表的訪問方法就是index
⑧all:全表掃描
5、possiable_keys和key
possiable_keys列表示在某個查詢語句中,對某個表執行單表查詢時可能用到的索引有哪些。
key表示實際用到的索引有哪些
6、key_len
實際使用到的索引的長度(位元組數),檢查是否充分利用了索引,值越大越好(和自己比),主要針對聯合索引。
先更新到這裡,會持續更新~~~~