主頁 >  其他 > 一本徹底搞懂MySQL索引優化EXPLAIN百科全書

一本徹底搞懂MySQL索引優化EXPLAIN百科全書

2020-11-22 16:05:03 其他

1、MySQL邏輯架構

日常在CURD的程序中，都避免不了跟資料庫打交道，大多數業務都離不開資料庫表的設計和SQL的撰寫，那如何讓你撰寫的SQL陳述句性能更優呢？

先來整體看下MySQL邏輯架構圖：

MySQL邏輯架構圖

MySQL整體邏輯架構圖可以分為Server和存盤引擎層，

Server層：

Server層涵蓋了MySQL的大多數核心服務功能，以及所有的內置函式（如日期、時間、數學和加密函式等），以及存盤程序、觸發器、視圖等跨存盤引擎的實作也在這一層來實作，

  • 連接器：負責跟客戶端建立連接、獲取權限、維持和管理連接，
  • 分析器：SQL詞法分析，SQL語法分析
  • 優化器：索引選擇，選擇一個執行效率高的，生成執行計劃
  • 執行器：操作引擎，回傳執行結果
  • ...
  • 查詢快取：執行SQL陳述句之前，先查快取，快取結果可能是以key-value對方式存盤的，key 是查詢的陳述句，value 是查詢的結果，

存盤引擎層：

負責資料的存盤和提取，是一種插件式的架構方式，支持 InnoDB、MyISAM、Memory 等多個存盤引擎，MySQL 5.5.5版本開始默認存盤引擎是 InnoDB，也是目前常用的存盤引擎，

今天我們來看下詳細看下優化器里的執行計劃如何分析，要分析一個 SQL 的執行效率，就要會看執行計劃，根據執行計劃優化 SQL，使其能達到高效查詢的目的，

一條查詢陳述句需要經過 MySQL 查詢優化器的各種基于成本和規則，優化后會生成一個所謂的執行計劃，

那么這個執行計劃主要展示具體執行查詢的方式，比如多表連接的順序是多少，表里包含多個索引，每個表采用什么訪問方法來具體執行查詢等，

而設計 MySQL 的大佬是非常貼心的，知道開發的朋友們都是親自寫 SQL 的，但是寫出 SQL 容易，想寫出性能高的 SQL 可不簡單，

所以，大佬提供了 Explain 陳述句來幫我們查詢某個查詢陳述句的具體執行計劃，

2、SQL 執行計劃決議

本文帶大家看懂 EXPLAIN 陳述句，必須要熟悉各項輸出是做什么的，從而有針對性的提升SQL 查詢陳述句的性能，

列名 用途
id 每一個SELECT關鍵字查詢陳述句都對應一個唯一id
select_type SELECT關鍵字對應的查詢型別
table 表名
partitions 匹配的磁區資訊
types 單表的訪問方法
possible_keys 可能用到的索引
key 實際使用到的索引
key_len 實際使用到的索引長度
ref 當使用索引列等值查詢時，與索引列進行等值匹配的物件資訊
rows 預估需要讀取的記錄條數
filtered 某個表經過條件過濾后剩余的記錄條數百分比
Extra 額外的一些資訊

為了方便解釋上面的執行計劃各項輸出的含義，下面創建三張資料庫表，

資料庫創建三張表：
DROP TABLE IF EXISTS user;
CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO user (`id`, `name`, `update_time`)
  VALUES (1,'a','2017-12-22 15:27:18'), (2,'b','2017-12-22 15:27:18'), (3,'c','2017-12-22 15:27:18');

DROP TABLE IF EXISTS `group`;
CREATE TABLE `group` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `group` (`id`, `name`) VALUES (1,'group1'),(2,'group2'),(3,'group3');

DROP TABLE IF EXISTS user_group;
CREATE TABLE `user_group` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `group_id` int(11) NOT NULL,
  `remark` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_group_id` (`group_id`),
  KEY `idx_user_group_id` (`user_id`,`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO user_group (`id`, `user_id`, `group_id`, `remark`)
  VALUES (1,1,1,'bak1'), (2,2,2,'bak2'), (3,3,3,'bak3');
EXPLAIN 執行計劃引數詳解：

下載了最新的 MySQL8.0+ 版本，直接執行 EXPLAIN ，對比了 MySQL 5.0+ 版本執行的 EXPLAIN EXTENDED 命令同樣都提供了一些查詢優化的資訊，除了執行計劃各項輸出引數外，額外還有 filtered 列，是一個百分比的值，rows * filtered/100 可以估算出將要和 EXPLAIN 中前一個表進行連接的行數 ，

如下所示：

EXPLAIN執行計劃

EXPLAIN 中的列
接下來我們將詳細說明下 EXPLAIN 執行結果每一列的資訊，

1、id 列

設計表時通常會設計 id，一般會作為主鍵，執行計劃的結果也不例外，也有 id 列，id 列編號是 SELECT 的序列號，并且 id 的順序是按 SELECT 出現的順序增長的，id列越大執行優先級越高，id 相同則從上往下執行，id 為 NULL 最后執行，

MySQL將 SELECT 查詢分為簡單查詢 SIMPLE 和復雜查詢 PRIMARY，

復雜查詢包括：簡單子查詢、派生表（ FROM 陳述句中的子查詢）、UNION 和 UNION ALL 查詢，

簡單查詢：

file

復雜查詢：

1）簡單子查詢

EXPLAIN SELECT (SELECT 1 from user LIMIT 1) from user;

file

2）FROM 子句中的子查詢

EXPLAIN SELECT * FROM (SELECT id, count(*) as c from group GROUP BY name) as derived

file

這個查詢執行時有個臨時表別名為 derived，外部 SELECT 查詢參考了這個臨時表

3）UNION 和 UNION ALL 查詢

EXPLAIN SELECT * FROM user UNION SELECT * FROM user;

file

UNION 結果總是放在一個匿名臨時表中，臨時表不在 SQL 中出現，臨時表名為 <union1, 2>，因此它的 id 是 NULL，表明這個臨時表是為了合并兩個查詢結果集而創建的，

跟 UNION 對比，UNION ALL 無需為最終結果而去重，僅是單純的將多個查詢結果集中的記錄合并成一個并回傳給用戶，所以不會使用到臨時表，故沒有 id 為 NULL 記錄，如下所示：

EXPLAIN SELECT * FROM user UNION ALL SELECT * FROM user;

file

注意點：子查詢優化為連接查詢

查詢優化器可能對子查詢進行重寫，進而轉換為連接查詢，查詢計劃中的兩個id值是相同的，如下所示：

EXPLAIN SELECT * FROM user WHERE id IN (SELECT user_id FROM user_group);

file

2、select_type 列

MySQL中優化器中的概念：

物化:

子查詢陳述句中的子查詢結果集中的記錄保存到臨時表的程序稱之為 物化（英文名：Materialize），簡單理解為存盤子查詢結果集的臨時表稱之為 物化表，

也正因為物化表的記錄都建立了索引（基于記憶體的物化表有哈希索引，基于磁盤的有B+樹索引），因此通過 IN 陳述句判斷某個運算元在不在子查詢的結果集中變得很快，從而提升陳述句的性能，

半連接 semi-join：

也是跟 IN 陳述句子查詢有關，

通用陳述句：

SELECT ... FROM outer_tables
    WHERE expr IN (SELECT ... FROM inner_tables ...) AND ...

outer_tables 表對 inner_tables 半連接的意思：

對于 outer_tables的某條記錄來說，我們僅關心在inner_tables 表中是否存在匹配的記錄，而不用關心具體有多少條記錄與之匹配，最終結果只保留 outer_tables 表的記錄，

每一個 SELECT 關鍵字的查詢都定義了一個 select_type 屬性，知道這個查詢屬性就能知道在整個查詢陳述句中所扮演的角色，

1）SIMPLE：簡單查詢，查詢不包含子查詢 和 UNION，

2）PRIMARY：復雜查詢中最外層的SELECT，可參照上面的 UNION 查詢陳述句，

3）SUBQUERY：包含的子查詢陳述句無法轉換為 semi-join，并且為不相關子查詢，查詢優化器采用物化方案執行該子查詢，該子查詢的第一個 SELECT 就會 SUBQUERY，該查詢由于被物化，只需要執行一次，

4）DERIVED：對于采用物化形式執行的包含派生表的查詢，該派生表的對應的子查詢為 DERIVED，

查詢陳述句如下所示：

EXPLAIN SELECT * FROM (SELECT id, count(*) as c FROM user GROUP BY id) AS derived_u where c>1;

file

5）UNION：在 UNION 查詢陳述句中的第二個和緊隨其后的 SELECT，

6）UNION RESULT：MySQL選擇使用臨時表完成 UNION 查詢的去重作業，

當 select_type 為這個值時，經常可以看到table的值是 <unionN,M>，這說明匹配的 id 行 是這個集合的一部分，請看上面 UNION 查詢示例，

7）MATERIALIZED：當查詢優化器執行包含子查詢的陳述句時，選擇將子查詢物化之后與外層查詢進行連接查詢時，該子查詢型別為 MATERIALIZED，

8）DEPENDENT SUBQUERY：包含的子查詢陳述句無法轉換為 semi-join，并且為相關子查詢，則該子查詢的第一個 SELECT 就會 DEPENDENT SUBQUERY，該查詢可能會被執行多次，

8）DEPENDENT UNION：包含的子查詢陳述句中包含了 UNION 或者 UNION ALL 的大查詢，這些查詢都依賴外層查詢，這些子查詢陳述句型別為 DEPENDENT UNION，

EXPLAIN SELECT * FROM user WHERE id IN (SELECT user_id FROM user_group WHERE name = 'a' UNION SELECT id FROM user WHERE name = 'b');

file

上面這個子查詢陳述句中的 SELECT user_id FROM user_group WHERE name = 'a' 這個小查詢是第一個子查詢，所以它的 select_type 為 DEPENDENT SUBQUERY，而 SELECT id FROM user WHERE name = 'b' 這個查詢在 UNION 后面，所以它的 select_type 為 DEPENDENT UNION，

最常見的值包括：SIMPLE 、PRIMARY、DERIVED、UNION，

3、table 列

table 串列示 EXPLAIN 的單獨行的唯一識別符號，這個值可能是表名、表的別名或者一個未查詢產生臨時表的識別符號，如派生表、子查詢或集合，

當 FROM 子句中有子查詢時，如果優化器采用的物化方式，table 列是 <derivenN> 格式，表示當前查詢依賴 id=N 的查詢，于是先執行 id=N 的查詢，

當使用 UNION 查詢時，UNION RESULT 的 table 列的值為 <UNION1,2>，1和2表示參與 UNION 的 SELECT 的行 id，

4、type 列

這一串列示關聯型別或訪問型別，即MySQL決定如何查找表中的行，查找資料行記錄的大概范圍，
依次從最優到最差分別為：system > const > eq_ref > ref > range > index > ALL
一般來說，得保證查詢達到range級別，最好達到ref

NULL：mysql能夠在優化階段分解查詢陳述句，在執行階段用不著再訪問表或索引，例如：在索引列中選取最小值，可以單獨查找索引來完成，不需要在執行時訪問表，

1）system,const：MySQL 能對查詢的某部分進行優化并將其轉化成一個常量，用于主鍵或唯一二級索引列與常數比較時，所以表最多有一個匹配行，讀取1次，速度比較快，system是 const 的特例，表里只有一條記錄匹配時為 system，

EXPLAIN SELECT * FROM (SELECT * FROM user where id = 1) tmp;

2）eq_ref：在連接查詢時，如果被驅動表是通過主鍵或者唯一二級索引列等值匹配的方式進行訪問的，則對該被驅動表的訪問方法就是 eq_ref，這可能是在 const 之外最好的聯接型別了，

EXPLAIN SELECT * FROM user_group INNER JOIN user ON user_group.user_id = user.id;

file

3）ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前綴，索引要和某個值相比較，可能會找到多個符合條件的行，

a. 簡單 SELECT 查詢，name 是普通索引（非唯一索引)，

EXPLAIN SELECT * FROM user where user.name = 'a';

file

b. 關聯表查詢，idx_user_group_id (user_id,group_id) 為聯合索引，這里使用到了user_group聯合索引最左邊前綴 user_id，

EXPLAIN SELECT user_id FROM user LEFT JOIN user_group ON user.id = user_group.user_id;

file

4）ref_or_null：對普通二級索引進行等值查詢，該索引列也可以為NULL值時，

EXPLAIN SELECT * FROM user where user.name = 'a' OR name IS NULL;

file

5）index_merge：MySQL使用索引合并的方式執行的，

EXPLAIN SELECT * FROM user WHERE user.name = 'a' OR user.id = 1;

file

6）range：使用索引獲取范圍區間的記錄，通常出現在 in, between ,> ,<, >= 等操作中，

EXPLAIN SELECT * FROM user WHERE user.id > 1;

file

7）index：掃描全表索引，這通常比ALL快一些，（index是從索引中讀取的，而 ALL 是從硬碟中讀取）

group 表里的兩個欄位都有索引，

EXPLAIN SELECT * FROM group;

file

8）ALL：即全表掃描，MySQL 需要從頭到尾去查找表中所需要的行，通常情況下這需要增加索引來進行優化了，

EXPLAIN SELECT * FROM user;

file

5、possible_keys 列

possible_keys 串列示查詢可能使用哪些索引來查找，

EXPLAIN 執行計劃結果可能出現 possible_keys 列，而 key 顯示 NULL 的情況，這種情況是因為表中資料不多，MySQL 會認為索引對此查詢幫助不大，選擇了全表查詢，

如果 possible_keys 列為 NULL，則沒有相關的索引，在這種情況下，可以通過檢查 WHERE 子句去分析下，看看是否可以創造一個適當的索引來提高查詢性能，然后用 EXPLAIN 查看效果，

另外注意：不是這一列的值越多越好，使用索引過多，查詢優化器計算時查詢成本高，所以如果可能的話，盡量洗掉那些不用的索引，

6、key 列

key 串列示實際采用哪個索引來優化對該表的訪問，

如果沒有使用索引，則該列是 NULL，如果想強制 MySQL使用或忽視 possible_keys 列中的索引，在查詢中使用 force index、ignore index，

7、key_len 列

key_len 串列示當查詢優化器決定使用某一個索引查詢時，該索引記錄的最大長度，

key_len 列計算規則如下：

  • 字串

char(n)：n位元組長度

varchar(n)：2位元組存盤字串長度，如果是utf-8，則長度 3n + 2

注意：該索引列可以存盤NULL值，則key_len比不可以存盤NULL值時多1個位元組，

比如：varchar(50)，則實際占用的key_len長度是 3 * 50 + 2 = 152，如果該列允許存盤NULL，則key_len長度是153，

  • 數值型別

tinyint：1位元組
smallint：2位元組
int：4位元組
bigint：8位元組　

  • 時間型別　

date：3位元組
timestamp：4位元組
datetime：8位元組

索引最大長度是768位元組，當字串過長時，MySQL 會做一個類似左前綴索引的處理，將前半部分的字符提取出來做索引，

舉例1：

user_group表中的聯合索引 idx_user_group_id 由 user_id 和 group_id 兩個int 列組成，并且每個 int 是 4 位元組，

EXPLAIN SELECT * FROM user_group WHERE user_id = 2;

file

通過結果中的 key_len=4可推斷出查詢使用了第一個列：user_id 列來執行索引查找，

舉例2：

再看 user 表 name 欄位是 varchar(45) 變長字串型別，key_len為138 等于 45 * 3 + 2 (變長位元組) + 1位元組（允許存盤NULL值）

EXPLAIN SELECT * FROM user WHERE name = 'a';

file

所以，以后再看到 key_len 欄位的值，不要在懵逼咯，固定套路~

8、ref 列

ref 列顯示了在 key 列記錄的索引中，表查找值所用到的列或常量，常見的有：const（常量），欄位名（例：user.id），

9、rows 列

rows 列是查詢優化器估計要讀取并檢測的行數，注意這個不是結果集里的行數，

如果查詢優化器使用全表掃描查詢，rows 列代表預計的需要掃碼的行數；
如果查詢優化器使用索引執行查詢，rows 列代表預計掃描的索引記錄行數，

10、filtered 列

對于單表來說意義不大，主要用于連接查詢中，

前文中也已提到 filtered 列，是一個百分比的值，對于連接查詢來說，主要看驅動表的 filtered列的值 ，通過 rows * filtered/100 計算可以估算出被驅動表還需要執行的查詢次數，

EXPLAIN SELECT * FROM user INNER JOIN user_group ON user.id = user_group.user_id WHERE user.update_time = '2019-01-01';

file

可以看到驅動表user執行的rows列為3行，filtered列為 33.33，計算驅動表的扇出值為 3 * 33.33% 約等于1，說明還需要對被驅動表執行大約1次查詢，

11、Extra 列

Extra 列提供了一些額外資訊，這一列在 MySQL中提供的資訊有幾十個，這里僅列舉一些常見的重要值如下：

1）Using index：查詢的列被索引覆寫，并且 WHERE 篩選條件是索引的前導列，使用了索引性能高，一般是使用了覆寫索引(查詢列都是索引列欄位)，對于 INNODB 存盤引擎來說，如果是輔助索引性能會有不少提高，并且也不需要回表查詢，

2）Using where Using index：查詢的列被索引覆寫，并且 WHERE 篩選條件是索引列之一，但并不是索引的前導列，意味著無法直接通過索引查找來查詢到符合條件的資料，

3）NULL：查詢的列未被索引覆寫，并且 WHERE 篩選條件是索引的前導列，意味著用到了索引，但是部分欄位未被索引覆寫，必須通過 回表 來查詢，不是純粹地用到了索引，也不是完全沒用到索引，

4）Using index condition：與Using where類似，查詢的列不完全被索引覆寫，WHERE 條件中是一個前導列的范圍，

5）Using temporary：MySQL 中需要創建一張內部臨時表來處理查詢，一般出現這種情況就需要考慮進行優化了，首先是想到用索引來優化，

通常在許多執行包括DISTINCT、GROUP BY、ORDER BY等子句查詢程序中，如果不能有效利用索引來完成查詢，MySQL很有可能會尋求建立內部臨時表來執行查詢，

所以，執行計劃中出現了 Using temporary 并不是個好兆頭，因為建立與維護臨時表要付出很大的成本的，要考慮使用索引來優化改進，

6）Using filesort：MySQL 會對結果使用一個外部索引排序，而不是按索引次序從表里讀取行，此時 MySQL 會根據聯接型別瀏覽所有符合條件的記錄，并保存排序關鍵字和行指標，然后排序關鍵字并按順序檢索行資訊，這種情況下一般也是要考慮使用索引來優化的，

查詢中需要使用 filesort 的方式進行排序的記錄非常多，那么這個過長是很耗時的，想辦法將使用 檔案排序 的執行方式改進為使用索引進行排序，

7）Index merges：通常顯示為Using sort_union(...) 說明準備用 Sort-Union 索引合并方式來查詢；顯示為 Using union(...)，說明準備用Union索引合并方式查詢；顯示為Using intersect(...)，說明準備使用Intersect索引合并方式查詢，

8）LooseScan：在 IN 子查詢轉為 semi-join 時，如果采用的是 LooseScan 執行策略，則會在Extra中提示，

9）FirstMatch(tbl_name)：在 IN 子查詢轉為 semi-join 時，如果采用的是 FirstMatch 執行策略，則會在Extra中提示，

10）Using join buffer：強調了在獲取連接條件時沒有使用索引，并且需要連接緩沖區來存盤中間結果，出現該值，應該注意，根據查詢的具體情況可能需要添加索引來改進性能，

我們所提到的回表操作 ，其實是一種隨機IO，比較耗時，所以盡量避免上面提到的回表操作，當發現Extra提示為 Using filesort、Using temporary 時就需要格外注意了，考慮索引優化，

3、最佳姿勢索引實踐

新建 staff 表表演使用：
# 重建 `staff` 表
DROP TABLE `staff`;
CREATE TABLE `staff` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `s_name` VARCHAR(24) NOT NULL DEFAULT '' COMMENT  '花名',
  `s_no` INT(4) NOT NULL DEFAULT 0 COMMENT  '工號',
  `work_age` int(11) NOT NULL DEFAULT '0' COMMENT '工齡',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '職位',
  `arrival_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入職時間',
  `remark` VARCHAR(500) DEFAULT NULL COMMENT '備注', # 允許 NULL
  PRIMARY KEY (`id`), # 主鍵
  UNIQUE KEY idx_s_name (s_name), # 唯一索引
  KEY idx_s_no (s_no), # 普通索引
  KEY `idx_name_age_position` (`name`,`work_age`,`position`) USING BTREE # 聯合索引
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='員工記錄表';
# 初始化 `staff` 表資料
INSERT INTO staff(name,s_name,s_no,work_age,position,arrival_time) VALUES('zhangsan','zs',10,2,'manager',NOW());
INSERT INTO staff(name,s_name,s_no,work_age,position,arrival_time) VALUES('lisi','ls',11,3,'dev',NOW());
INSERT INTO staff(name,s_name,s_no,work_age,position,arrival_time) VALUES('wangwu','ww',12,8,'dev',NOW());
INSERT INTO staff(name,s_name,s_no,work_age,position,arrival_time) VALUES('zhangliu','zl',110,5,'dev',NOW());
INSERT INTO staff(name,s_name,s_no,work_age,position,arrival_time) VALUES('xiaosun','xs',111,5,'dev',NOW());
INSERT INTO staff(name,s_name,s_no,work_age,position,arrival_time) VALUES('donggua','dg',200,3,'dev',NOW());
資料庫索引最佳實踐

1、全值匹配：

EXPLAIN SELECT * FROM staff WHERE name= 'zhangsan';

file

EXPLAIN SELECT * FROM staff WHERE name= 'zhangsan' AND work_age = 2;

file

EXPLAIN SELECT * FROM staff where name = 'zhangsan' AND work_age = 2 AND position = 'dev';

file

EXPLAIN SELECT * FROM staff where position = 'dev' AND name = 'zhangsan' AND work_age = 2;

file

最后一條，我們將 position 放到了 WHERE 條件后面，盡管沒有按照聯合索引的順序撰寫條件，MySQL 優化器會自動優化，將 name 排到最前面去，所以還是會正確使用聯合索引的，

聯合索引創建后，你必須嚴格按照最左前綴的原理進行使用，否則會無法使用到索引，盡量按照這個順序去寫，這樣避免 MySQL 優化器再次優化了，

2、最佳左前綴法則：

如果索引了多列，要遵守最左前綴法則，指的是查詢從索引的最左前列開始并且不跳過索引中的列，

以下 SQL 符合最左前綴匹配法則：

EXPLAIN SELECT * FROM staff WHERE name = 'zhangsan' AND work_age = 3 AND position = 'manager';

file

EXPLAIN SELECT * FROM staff WHERE name = 'zhangsan' AND position = 'manager';

file

以下執行都是全表掃描，type 為 ALL，都不符合最左前綴法則：

EXPLAIN SELECT * FROM staff WHERE work_age = 2 AND position ='dev';

file

EXPLAIN SELECT * FROM staff WHERE position = 'dev';

file

3、索引列上避免做計算操作

索引上盡量避免做函式計算等操作，會導致索引失效而轉向全表掃描，

WHERE 條件后面索引列使用函式:

EXPLAIN SELECT * FROM staff WHERE LEFT(name, 5) = 'zhang';
EXPLAIN SELECT * FROM staff WHERE LOWER(name) = 'zhangsan';

file

EXPLAIN SELECT * FROM staff WHERE staff.s_no * 2 > 3;

file

查詢的結果 type 列為 ALL，key 是空的，索引失效，全表掃描，

計算邏輯盡量放到業務層去處理，最大限度的命中索引，同時還能節省資料庫資源開銷，

4、存盤引擎無法使用索引中范圍條件右邊的列

EXPLAIN SELECT * FROM staff WHERE name= 'zhangsan' AND work_age > 2 AND position ='dev';

file

我們看到了執行結果中 type 為 range 級別，使用了范圍查找，而 position 欄位并沒有用到索引（沒有使用到BTree的索引去查詢），只是從 name = 'zhangsan' AND work_age > 2 條件回傳的結果集中，再過濾符合 position 欄位條件的資料，

5、盡量使用覆寫索引

覆寫索引：簡單理解，只訪問建了索引的列，減少使用 SELECT * 陳述句查詢列，

使用了覆寫索引：

EXPLAIN SELECT name,work_age FROM staff WHERE name= 'zhangsan' AND work_age = 3;

file
使用了 SELECT * 查詢：

EXPLAIN SELECT * FROM staff WHERE name= 'zhangsan' AND work_age = 3;

file

我們重點看下使用了 覆寫索引 方式查詢，會在結果中 Extra 列顯示 Using index ，這說明在查詢列包含了索引列，不需要再次回表查詢了，而如果使用 SELECT * 方式查詢，查詢列包含非索引的列，Extra 顯示為 NULL，所以還會進行回表查詢，

附一個曾經線上SQL的優化記錄：

file

artist 表有幾十萬條的資料量，第一條執行的SQL沒有索引直接查詢，查詢耗時 0.557 毫秒；第一次優化新建 founded 欄位作為普通索引，查詢耗時 0.0224 毫秒；第二次優化再次重建聯合索引 founded_name，優化后查詢耗時：0.0051 毫秒，因為使用了覆寫索引查詢方式，基于此優化，SQL查詢效率提升非常明顯，

6、范圍條件查找能夠命中索引

范圍條件主要包括 <、<=、>、>=、between 等，

若條件中范圍列有普通索引和主鍵索引同時存在， 優先使用主鍵索引:

EXPLAIN SELECT * FROM staff WHERE staff.s_no > 10 AND staff.id > 2;

file

范圍列可以用到索引，注意聯合索引必須符合最左前綴法則，如果查詢條件中有兩個范圍列則無法全用到索引，優化器會去選擇：

EXPLAIN SELECT * FROM staff WHERE staff.name != 'zl' AND staff.s_no > 1;

file

若條件中范圍查詢和等值查詢同時存在，優先匹配等值查詢列的索引：

EXPLAIN SELECT * FROM staff WHERE staff.s_no > 10 AND staff.s_name = 'zl';

file

7、索引列不為 NULL，IS NOT NULL無法使用索引

索引列建議都使用 NOT NULL 約束 及默認值，單列索引不存 NULL 值，聯合索引不存全部為 NULL 的值，如果列允許為 NULL，查詢結果可能不符合預期，

staff 表中為 remark 欄位新建普通索引：

ALTER TABLE staff ADD INDEX idx_remark (remark);

IS NULL 查詢命中索引：

EXPLAIN SELECT * FROM staff WHERE staff.remark IS NULL;

file

IS NOT NULL 查詢不會命中索引：

EXPLAIN SELECT * FROM staff WHERE staff.name IS NOT NULL;

file

8、模糊條件查詢以通配符開頭索引失效

like '%xx' 或 like '%xx%' 前導模糊查詢不能命中索引：

EXPLAIN SELECT * from staff where name like '%zhang%';

file

如何使用模擬查詢才能命中索引？

a）like 'xx%' 非前導模糊查詢可以命中索引：

EXPLAIN SELECT * FROM staff WHERE name LIKE 'zhang%';

file

b）使用覆寫索引，查詢欄位必須要建立覆寫索引欄位

EXPLAIN SELECT name,work_age FROM staff WHERE name LIKE '%zhang%';

聯合索引是 idx_name_work_age_position

file

9、字串型別不加單引號索引失效

字串的資料型別一定要將常量值使用單引號，這個在日常開發中要特別注意的，資料型別出現隱式轉換的時候不會命中索引，

不加單引號索引失效

EXPLAIN SELECT * FROM staff WHERE name = 1;

file

name=1 類似于在該欄位上做了一個函式運算，因此不會走索引的，

加單引號會命中索引：

EXPLAIN SELECT * FROM staff WHERE name = 'zhangsan';

file

10、OR 使用多數情況下索引會失效

EXPLAIN SELECT * FROM staff WHERE name='zhangsan' OR work_age = 2;

file

盡管 name 和 work_age 是聯合索引，但是 work_age 列上并沒有建索引，所以使用了 OR 不會走索引，

如果 OR 前后都是聯合索引帶頭大哥 name 欄位，那么就會用到索引，如下所示：

file

因 OR 后面的條件列中沒有索引，會走全表掃描，存在全表掃描的情況下，就沒有必要多一次索引掃描增加IO訪問，

可使用覆寫索引查詢：

EXPLAIN SELECT name,work_age FROM staff WHERE name='zhangsan' OR work_age = 2;

file

** OR 后面也使用索引列：**

EXPLAIN SELECT * FROM staff WHERE name='zhangsan' OR s_name='wangwu';

file

s_name 是唯一索引，name是聯合索引第一個欄位，兩者使用 OR 查詢結果 Extra 顯示 Using sort_union(idx_name_age_position,idx_s_name); Using where 解釋一下，

如果執行計劃 Extra 列出現了 Using sort_union(...) 的提示，說明準備使用 Sort-Union 索引合并的方式執行查詢，如果出現了 Using intersect(...) 的提示，說明準備使用 Intersect 索引合并方式執行查詢，如果出現了 Using union(...) 的提示 ，說明準備使用 Union 索引合并方式執行查詢， 括號中 ... 表示需要進行索引合并的索引名稱，

使用UNION優化改進：

EXPLAIN SELECT * FROM staff WHERE name='zhangsan' UNION SELECT * FROM staff WHERE s_name = 'zs';

file

使用 UNION 執行計劃中出現了第三條記錄，Extra 中出現 Using temporary，說明 MySQL因為不能有效利用索引，建立了內部臨時表來執行查詢，當你在使用 DISTINCT 、GROUP BY、UNION 等子句中的查詢程序中，都有可能會出現該擴展資訊，

使用UNION ALL進一步優化：

EXPLAIN SELECT * FROM staff WHERE name='zhangsan' UNION ALL SELECT * FROM staff WHERE s_name = 'zs';

file

執行結果中不再出現內部臨時表，具體用的時候結合實際需求來定是否使用，

11、負向查詢條件不能使用索引，可以優化為 IN 查詢

負向查詢條件包括：!=、<>、NOT IN、NOT EXISTS、NOT LIKE 等，

不會命中索引：

EXPLAIN SELECT * FROM staff WHERE s_no !=1 AND s_no != 2;
EXPLAIN SELECT * FROM staff WHERE s_no NOT IN (1,2);
file

使用IN優化，命中索引：

EXPLAIN SELECT * FROM staff WHERE s_no IN (11,12);

file

但是使用 IN 命中索引有個前提，是查詢條件欄位資料區分度要高，通常如：狀態、型別、性別之類的欄位，

** 12、排序對索引的影響**

ORDER BY是經常用的陳述句，排序也遵循最左前綴列的原則，

查詢所有列未命中索引：

EXPLAIN SELECT * FROM staff ORDER BY name,work_age;

file

覆寫索引查詢可命中索引：

file

覆寫索引能夠利用聯合索引查詢，但是 ORDER BY 后的條件查詢不符合最左前綴原則，執行結果 Extra 中出現了 Using filesort 的提示，一般看到這個就要想辦法優化了，

調整排序的兩個欄位順序之后，Extra 會提示為 Using index，使用了索引，避免了排序的資源開銷：

EXPLAIN SELECT name,work_age FROM staff ORDER BY name,work_age;

file

** 13、區域索引的使用**

區域索引，區別于最左列索引（順序取索引中靠左的列的查詢），它只取某列的一部分作為索引，

INNODB存盤引擎下，一般是字串型別，很長，全部作為索引大大增加存盤空間，索引也需要維護，對于長字串，又想作為索引列，可取的辦法就是取前一部分（區域），代表一整列作為索引串，

如何確保這個前綴能代表或大致代表這一列？MySQL中有個概念是 索引選擇性，是指索引中不重復的值的數目（也稱基數X）與整個表該列記錄總數（T）的比值，基數可以通過SHOW INDEX FROM 表名 查看，

比如一個串列 [1,2,2,3,5,6]，總數是 6，不重復值數目為 5，選擇性為 5/6，因此選擇性范圍是[X/T, 1]，這個值越大，表示列中不重復值越多，越適合作為區域索引，而唯一索引（UNIQUE KEY）的選擇性是1，

`SELECT COUNT(DISTINCT(CONCAT(LEFT(remark, N))/COUNT(*) FROM t; 測驗出接近 1 的索引選擇性，其中N是索引的長度，窮舉法去找出N的值，然后再建索引，

創建 區域索引 ，使用 remark 欄位舉個例子

EXPLAIN SELECT * FROM staff where remark LIKE 'xxx%';

file

對 remark 欄位重建區域索引：

ALTER TABLE staff DROP INDEX idx_remark_part, ADD INDEX idx_remark_part(remark(5));

再次執行查詢：

EXPLAIN SELECT * FROM staff where remark LIKE 'xxx%';

file

索引優化總結

上面列了大部分場景索引最佳實戰，除此之外，不宜建索引的幾點小總結：

1）更新非常頻繁欄位不宜建索引

因為欄位更新臺頻繁，會導致B+樹的頻繁的變更，重建索引，所以這個程序是十分消耗資料庫性能的，

2）區分度不大的欄位不宜建索引

比如類似性別這類的欄位，區分度不大，建立索引的意義不大，因為不能有效過濾資料，性能和全表掃描相當，另外注意一點，回傳資料的比例在 30% 之外的，優化器不會選擇使用索引，

3）業務中有唯一特性的欄位，建議建成唯一索引

業務中如果有唯一特性的欄位，即使是多個欄位的組合，也盡量都建成唯一索引，盡管唯一索引會影響插入效率，但是對于查詢的速度提升是非常明顯的，此外，還能夠提供校驗機制，如果沒有唯一索引，高并發場景下，可能還會產生臟資料，

4）多表關聯時，要確保關聯欄位上必須有索引

5）創建索引時避免建立錯誤的認識

索引越多越好，認為一個查詢就需要建一個索引，
寧缺勿濫，認為索引會消耗空間、嚴重拖慢更新和新增速度，
抵制唯一索引，認為業務的唯一性一律需要在應用層通過“先查后插”方式解決，
過早優化，在不了解系統的情況下就開始優化，

6）最佳索引實踐口訣

如果你覺得上面哪些太啰嗦，有朋友已總結為一套優化口訣，優化SQL時也能提個醒吧，

全值匹配我最愛，最左前綴要遵守；
帶頭大哥不能死，中間兄弟不能斷；
索引列上少計算，范圍之后全失效；
Like百分寫最右，覆寫索引不寫星；
不等空值還有or，索引失效要少用；
VAR引號不可丟，SQL高級也不難！

7）EXPLAIN 執行計劃實踐總結

如果還是覺得 EXPLAIN 執行計劃列太多了，也記不住呀，那么請重點關注以下幾列：

第1列：ID越大，執行的優先級越高；ID相等，從上往下優先順序執行，

第2列：select_type 查詢陳述句的型別，SIMPLE簡單查詢，PRIMARY復雜查詢，DERIVED衍生查詢(from子查詢的臨時表)，派生表，

第4列：請重點掌握，type型別，查詢效率優先級：system->const->eq_ref->ref->range->index->ALL

ALL 是最差的，system 是最好的，性能最佳，阿里巴巴開發規約中要求最差也得到 range 級別，而不能有 index、ALL，

最后，對于后端工程師而言，盡力都能掌握 EXPLAIN 的使用，寫完SQL請習慣性的用它幫助你分析一下，做一個對SQL性能有追求的程式員，因為SQL也是程式員必備技能，將慢查詢問題拍死在專案上線前夕，

如果覺得本文有所識訓，歡迎轉發分享，

參考資料：

MySQL官網
https://www.cnblogs.com/songwenjie/p/9402295.html
https://www.cnblogs.com/phpdragon/p/8231533.html

歡迎關注我的公眾號，掃二維碼關注獲得更多精彩原創文章，與你一同成長~

Java愛好者社區

轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/226393.html

標籤：其他

上一篇：達夢資料庫-RAC-DMDSC部署的關鍵點

下一篇：0804. Unique Morse Code Words (E)

標籤雲
其他(157675) Python(38076) JavaScript(25376) Java(17977) C(15215) 區塊鏈(8255) C＃(7972) AI(7469) 爪哇(7425) MySQL(7132) html(6777) 基礎類(6313) sql(6102) 熊猫(6058) PHP(5869) 数组(5741) R(5409) Linux(5327) 反应(5209) 腳本語言(PerlPython)(5129) 非技術區(4971) Android(4554) 数据框(4311) css(4259) 节点.js(4032) C語言(3288) json(3245) 列表(3129) 扑(3119) C++語言(3117) 安卓(2998) 打字稿(2995) VBA(2789) Java相關(2746) 疑難問題(2699) 细绳(2522) 單片機工控(2479) iOS(2429) ASP.NET(2402) MongoDB(2323) 麻木的(2285) 正则表达式(2254) 字典(2211) 循环(2198) 迅速(2185) 擅长(2169) 镖(2155) 功能(1967) .NET技术(1958) Web開發(1951) python-3.x(1918) HtmlCss(1915) 弹簧靴(1913) C++(1909) xml(1889) PostgreSQL(1872) .NETCore(1853) 谷歌表格(1846) Unity3D(1843) for循环(1842)

熱門瀏覽
  • 網閘典型架構簡述

    網閘架構一般分為兩種：三主機的三系統架構網閘和雙主機的2+1架構網閘。 三主機架構分別為內端機、外端機和仲裁機。三機無論從軟體和硬體上均各自獨立。首先從硬體上來看，三機都用各自獨立的主板、記憶體及存盤設備。從軟體上來看，三機有各自獨立的作業系統。這樣能達到完全的三機獨立。對于“2+1”系統，“2”分為 ......

    uj5u.com 2020-09-10 02:00:44 more
  • 如何從xshell上傳檔案到centos linux虛擬機里

    如何從xshell上傳檔案到centos linux虛擬機里及：虛擬機CentOs下執行 yum -y install lrzsz命令，出現錯誤：鏡像無法找到軟體包 前言 一、安裝lrzsz步驟 二、上傳檔案 三、遇到的問題及解決方案 總結 前言 提示：其實很簡單，往虛擬機上安裝一個上傳檔案的工具 ......

    uj5u.com 2020-09-10 02:00:47 more
  • 一、SQLMAP入門

    一、SQLMAP入門 1、判斷是否存在注入 sqlmap.py -u 網址/id=1 id=1不可缺少。當注入點后面的引數大于兩個時。需要加雙引號， sqlmap.py -u "網址/id=1&uid=1" 2、判斷文本中的請求是否存在注入 從文本中加載http請求，SQLMAP可以從一個文本檔案中 ......

    uj5u.com 2020-09-10 02:00:50 more
  • Metasploit 簡單使用教程

    metasploit 簡單使用教程 浩先生， 2020-08-28 16:18:25 分類專欄： kail 網路安全 linux 文章標簽： linux資訊安全 編輯 著作權 metasploit 使用教程 前言 一、Metasploit是什么？ 二、準備作業 三、具體步驟 前言 Msfconsole ......

    uj5u.com 2020-09-10 02:00:53 more
  • 游戲逆向之驅動層與用戶層通訊

    驅動層代碼： #pragma once #include <ntifs.h> #define add_code CTL_CODE(FILE_DEVICE_UNKNOWN,0x800,METHOD_BUFFERED,FILE_ANY_ACCESS) /* 更多游戲逆向視頻www.yxfzedu.com ......

    uj5u.com 2020-09-10 02:00:56 more
  • 北斗電力時鐘（北斗授時服務器）讓網路資料更精準

    北斗電力時鐘（北斗授時服務器）讓網路資料更精準 北斗電力時鐘（北斗授時服務器）讓網路資料更精準 京準電子科技官微——ahjzsz 近幾年，資訊技術的得了快速發展，互聯網在逐漸普及，其在人們生活和生產中都得到了廣泛應用，并且取得了不錯的應用效果。計算機網路資訊在電力系統中的應用，一方面使電力系統的運行 ......

    uj5u.com 2020-09-10 02:01:03 more
  • 【CTF】CTFHub 技能樹 彩蛋 writeup

    ?碎碎念 CTFHub：https://www.ctfhub.com/ 筆者入門CTF時時剛開始刷的是bugku的舊平臺，后來才有了CTFHub。 感覺不論是網頁UI設計，還是題目質量，賽事跟蹤，工具軟體都做得很不錯。 而且因為獨到的金幣制度的確讓人有一種想去刷題賺金幣的感覺。 個人還是非常喜歡這個 ......

    uj5u.com 2020-09-10 02:04:05 more
  • 02windows基礎操作

    我學到了一下幾點 Windows系統目錄結構與滲透的作用 常見Windows的服務詳解 Windows埠詳解 常用的Windows注冊表詳解 hacker DOS命令詳解（net user / type /md /rd/ dir /cd /net use copy、批處理 等） 利用dos命令制作 ......

    uj5u.com 2020-09-10 02:04:18 more
  • 03.Linux基礎操作

    我學到了以下幾點 01Linux系統介紹02系統安裝，密碼啊破解03Linux常用命令04LAMP 01LINUX windows： win03 8 12 16 19 配置不繁瑣 Linux：redhat,centos(紅帽社區版)，Ubuntu server,suse unix:金融機構，證券，銀 ......

    uj5u.com 2020-09-10 02:04:30 more
  • 05HTML

    01HTML介紹 02頭部標簽講解03基礎標簽講解04表單標簽講解 HTML前段語言 js1.了解代碼2.根據代碼 懂得挖掘漏洞 （POST注入/XSS漏洞上傳）3.黑帽seo 白帽seo 客戶網站被黑帽植入劫持代碼如何處理4.熟悉html表單 <html><head><title>TDK標題，描述 ......

    uj5u.com 2020-09-10 02:04:36 more
最新发布
  • 2023年最新微信小程式抓包教程

    01 開門見山 隔一個月發一篇文章，不過分。 首先回顧一下《微信系結手機號資料庫被脫庫事件》，我也是第一時間得知了這個訊息，然后跟蹤了整件事情的經過。下面是這起事件的相關截圖以及近日流出的一萬條資料樣本： 個人認為這件事也沒什么，還不如關注一下之前45億快遞資料查詢渠道疑似在近日復活的訊息。 訊息是 ......

    uj5u.com 2023-04-20 08:48:24 more
  • web3 產品介紹：metamask 錢包 使用最多的瀏覽器插件錢包

    Metamask錢包是一種基于區塊鏈技術的數字貨幣錢包，它允許用戶在安全、便捷的環境下管理自己的加密資產。Metamask錢包是以太坊生態系統中最流行的錢包之一，它具有易于使用、安全性高和功能強大等優點。 本文將詳細介紹Metamask錢包的功能和使用方法。 一、 Metamask錢包的功能 數字資 ......

    uj5u.com 2023-04-20 08:47:46 more
  • vulnhub_Earth

    前言 靶機地址->>>vulnhub_Earth 攻擊機ip：192.168.20.121 靶機ip：192.168.20.122 參考文章 https://www.cnblogs.com/Jing-X/archive/2022/04/03/16097695.html https://www.cnb ......

    uj5u.com 2023-04-20 07:46:20 more
  • 從4k到42k，軟體測驗工程師的漲薪史，給我看哭了

    清明節一過，盲猜大家已經無心上班，在數著日子準備過五一，但一想到銀行卡里的余額……瞬間心情就不美麗了。最近，2023年高校畢業生就業調查顯示，本科畢業月平均起薪為5825元。調查一出，便有很多同學表示自己又被平均了。看著這一資料，不免讓人想到前不久中國青年報的一項調查：近六成大學生認為畢業10年內會 ......

    uj5u.com 2023-04-20 07:44:00 more
  • 最新版本 Stable Diffusion 開源 AI 繪畫工具之中文自動提詞篇

    🎈 標簽生成器 由于輸入正向提示詞 prompt 和反向提示詞 negative prompt 都是使用英文，所以對學習母語的我們非常不友好 使用網址：https://tinygeeker.github.io/p/ai-prompt-generator 這個網址是為了讓大家在使用 AI 繪畫的時候 ......

    uj5u.com 2023-04-20 07:43:36 more
  • 漫談前端自動化測驗演進之路及測驗工具分析

    隨著前端技術的不斷發展和應用程式的日益復雜，前端自動化測驗也在不斷演進。隨著 Web 應用程式變得越來越復雜，自動化測驗的需求也越來越高。如今，自動化測驗已經成為 Web 應用程式開發程序中不可或缺的一部分，它們可以幫助開發人員更快地發現和修復錯誤，提高應用程式的性能和可靠性。 ......

    uj5u.com 2023-04-20 07:43:16 more
  • CANN開發實踐：4個DVPP記憶體問題的典型案例解讀

    摘要：由于DVPP媒體資料處理功能對存放輸入、輸出資料的記憶體有更高的要求（例如，記憶體首地址128位元組對齊），因此需呼叫專用的記憶體申請介面，那么本期就分享幾個關于DVPP記憶體問題的典型案例，并給出原因分析及解決方法。 本文分享自華為云社區《FAQ_DVPP記憶體問題案例》，作者：昇騰CANN。 DVPP ......

    uj5u.com 2023-04-20 07:43:03 more
  • msf學習

    msf學習 以kali自帶的msf為例 一、msf核心模塊與功能 msf模塊都放在/usr/share/metasploit-framework/modules目錄下 1、auxiliary 輔助模塊，輔助滲透（埠掃描、登錄密碼爆破、漏洞驗證等） 2、encoders 編碼器模塊，主要包含各種編碼 ......

    uj5u.com 2023-04-20 07:42:59 more
  • Halcon軟體安裝與界面簡介

    1. 下載Halcon17版本到到本地 2. 雙擊安裝包后 3. 步驟如下 1.2 Halcon軟體安裝 界面分為四大塊 1. Halcon的五個助手 1) 影像采集助手：與相機連接，設定相機引數，采集影像 2) 標定助手：九點標定或是其它的標定，生成標定檔案及內參外參，可以將像素單位轉換為長度單位 ......

    uj5u.com 2023-04-20 07:42:17 more
  • 在MacOS下使用Unity3D開發游戲

    第一次發博客，先發一下我的游戲開發環境吧。 去年2月份買了一臺MacBookPro2021 M1pro(以下簡稱mbp)，這一年來一直在用mbp開發游戲。我大致分享一下我的開發工具以及使用體驗。 1、Unity 官網鏈接： https://unity.cn/releases 我一般使用的Apple ......

    uj5u.com 2023-04-20 07:40:19 more

Copyright © 2010-2020 有解無憂