Mysql 最佳化

explain中，包含了如下幾個欄位（不同版本可能會有所差異）：

看完是不是很懵，感覺好多要記憶的，彆著急，下邊我們透過實際案例，來加深記憶

id

id 欄位是 select查詢的序列號，是一組數字，表示的是查詢中執行select子句或者是操作表的順序。
id 情況有三種 ：

1. 此處只是單表查詢，id只有一個

1. id一樣，則從上到下

1. id不同，則id值越大，優先順序越高

此處是巢狀子查詢，最內部的子查詢，自然是最先執行的

簡而言之：

• id值越大，優先順序越高；
• id值一樣，則從上到下；

select_type

PRIMARY,SUBQUERY

DERIVED(需要臨時表，自然比上述效率低)

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

一般至少要達到range級別，最好達到ref 。

const

唯一索引，非關聯查詢

eq_ref，ref

eq_ref 跟 const 的區別是：兩者都利用唯一索引，但前者是關聯查詢，後者只是普通查詢？
eq_ref 跟 ref 的區別：後者是非唯一索引

index，all

都是讀全表，區別在於index是遍歷索引樹讀取，而ALL是從硬碟中讀取。

不走索引就會遍歷全表

possible_keys，key

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

key ：實際使用的索引， 如果為NULL， 則沒有使用索引。（可能是沒有走索引，需要分析）

key_len : 表示索引中使用的位元組數， 在不損失精確性的前提下， 長度越短越好 。

• 單列索引，那麼需要將整個索引長度算進去；
• 多列索引，不是所有列都能用到，需要計算查詢中實際用到的列。

ref

顯示索引的哪一列被使用了，如果可能的話，是一個常數。

• 當使用常量等值查詢，顯示const
• 當關聯查詢時，會顯示相應關聯表的關聯欄位
• 如果查詢條件使用了表示式、函式，或者條件列發生內部隱式轉換，可能顯示為func
• 其他情況為null

1. id是索引，而且是id=1，一個常數，故ref = const
2. user_id不是索引，ref直接為null

t1.id是索引，且=號後邊不是常量，故顯示t1.id，即顯示相應關聯表的關聯欄位

rows

掃描行的數量，一般越小越好

• 用索引 rows 就為1，無論是唯一索引還是非唯一索引
• 其他情況一般是全表掃描，rows等於表的行數。

filtered

表裡符合條件的記錄數的所佔的百分比。

extra

其他的額外的執行計劃資訊，在該列展示 ，需要把前兩個最佳化為using index。

具體using index condition中的索引下推是什麼意思，可以參考這篇 索引的原理&&設計原則

using where

不同版本好像不一樣

5.7：表示 MySQL 首先從資料表（儲存引擎）中讀取記錄，返回給 MySQL 的 server 層，然後在 server 層過濾掉不滿足條件的記錄，即無法直接在儲存引擎過濾掉。
簡單來說，就是查詢時where中用的不是索引。

0. SQL準備

create table `tb_seller` (
    `sellerid` varchar (100),
    `name` varchar (100),
    `nickname` varchar (50),
    `password` varchar (60),
    `status` varchar (1),
    `address` varchar (100),
    `createtime` datetime,
    primary key(`sellerid`)
)engine=innodb default charset=utf8mb4; 

insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('alibaba','阿里巴巴','阿里小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('baidu','百度科技有限公司','百度小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('huawei','華為科技有限公司','華為小店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itcast','傳智播客教育科技有限公司','傳智播客','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itheima','黑馬程式設計師','黑馬程式設計師','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('luoji','羅技科技有限公司','羅技小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('oppo','OPPO科技有限公司','OPPO官方旗艦店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('ourpalm','掌趣科技股份有限公司','掌趣小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('qiandu','千度科技','千度小店','e10adc3949ba59abbe56e057f20f883e','2','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('sina','新浪科技有限公司','新浪官方旗艦店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('xiaomi','小米科技','小米官方旗艦店','e10adc3949ba59abbe56e057f20f883e','1','西安市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('yijia','宜家家居','宜家家居旗艦店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');

-- 建立聯合索引
create index idx_seller_name_sta_addr on tb_seller(name,status,address);

1. 不滿足最左字首

所謂最左字首，可以想象成一個爬樓梯的過程，假設我們有一個複合索引：name，status，address，那這個樓梯由低到高依次順序是：name，status，address，最左字首，要求我們不能出現跳躍樓梯的情況，否則會導致我們的索引失效：
​

1. 按樓梯從低到高，無出現跳躍的情況–此時符合最左字首原則，索引不會失效

1. 出現跳躍的情況

• 直接第一層name都不走，當然都失效

• 走了第一層，但是後續直接第三層，只有出現跳躍情況前的不會失效（此處就只有name成功）

• 同時，這個順序並不是由我們where中的排列順序決定，比如：
  • where name=’小米科技’ and status=’1’ and address=’北京市’
  • where status=’1’ and name=’小米科技’ and address=’北京市’

這兩個儘管where中欄位的順序不一樣，第二個看起來越級了，但實際上效果是一樣的

其實是因為我們MySQL有一個Optimizer（查詢最佳化器），查詢最佳化器會將SQL進行最佳化，選擇最優的查詢計劃來執行。

2. 範圍查詢之後

範圍查詢之後的索引欄位，會失效！！！但本身用來範圍查詢的那個索引欄位依然有效，如圖中的status。

• 而圖中address失效了，對比一下長度便可看出來。

3. 索引欄位做運算

對索引欄位做運算，使用函式等都會導致索引失效。

4. 字串不加’ ‘

索引欄位為字串型別，由於在查詢時，沒有對字串加單引號，MySQL的查詢最佳化器，會自動的進行型別轉換，造成索引失效。

5. 避免select *

危害

• 消耗更多的 CPU 和 IO 以網路頻寬資源
• 可減少表結構變更帶來的影響
• 無法使用覆蓋索引

?覆蓋索引

儘量使用覆蓋索引（索引列完全包含查詢列），減少select *
​

當查詢列中包含了非索引項，雖然我們還是能夠利用到索引，但是為了獲取非索引項欄位，我們需要回表去查詢資料，效率會比較低。
​

6. or分割開的條件

用or分割開的條件， 如果or前的條件中的列有索引，而後面的列中沒有索引，那麼涉及的索引都不會被用到。
​

示例，name欄位是索引列 ， 而createtime不是索引列，中間是or進行連線是不走索引的 ：

• 因為有一個不走索引，又是or條件，兩個都要判斷一下，相當於不管如何，都還是得去走全表查詢，沒有利用到索引。

 explain select * from tb_seller where name='黑馬程式設計師' or createtime = '2088-01-01 12:00:00'\G; 

7. 以%開頭的Like模糊查詢

可以聯絡字典樹Trie的匹配吧。

• 比如要找‘abc’，如果是%bc，一開始的根都找不到了，自然沒辦法利用到索引樹

• 而如果是ab%，還能利用到前兩個。

• %開頭的失效，%結尾的還能利用索引（實際上這裡就相當於字串的最左字首原則，可以這麼理解）

解決方法：使用覆蓋索引

當真的需要兩邊都使用%來模糊查詢時，只有當 作為模糊查詢的條件欄位（例子中的name）以及 想要查詢出來的資料欄位（例子中的 name & status & address）都在索引列上時，才能真正使用索引。

關於覆蓋索引，可以參考這篇 -> 索引原理，設計原則

8. MySQL認為全表更快

此處是由於資料的特殊性，‘北京市’所佔的比例很高，還不如全表掃描

8.1 is null 和 is not null

本質上跟上邊是一樣的

MySQL底層會自動判斷，如果全表掃描快，則直接使用全表掃描，不走索引。如果表中該索引列資料絕大多數是非空值，則使用 is not null的時候走索引，使用 is null的時候不走索引（還不如全表掃描快），全表掃描；反之亦然。

如果表中is null的比較多，那自然就直接全表掃描，如果is null的很少，會走索引。

8.2 in 和 not in

為了方便測試，我們單獨建了一個status索引，觀察該表資料，status中2很少，而1很多。

所以in(‘1’)的話，不如走全表，沒有用到索引
in(‘2’)就會走索引

總結

我們建立索引的時候，對於資料分佈均勻且重複的欄位，我們一般不考慮對其新增索引，因為此時MySQL會認為全表更快，會走全表掃描而非索引，導致我們的索引失效。

9. !=或者<>

使用不等式也會導致索引失效

相關習題

說完幾個索引失效的場景，下邊呢，是我們具體的應用場景，在如下幾種特定情況下，我們需要採取不同的SQL最佳化方式，或採用索引，或利用外部條件

環境準備

CREATE TABLE `tb_user_2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(45) NOT NULL,
  `password` varchar(96) NOT NULL,
  `name` varchar(45) NOT NULL,
  `birthday` datetime DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `email` varchar(45) DEFAULT NULL,
  `phone` varchar(45) DEFAULT NULL,
  `qq` varchar(32) DEFAULT NULL,
  `status` varchar(32) NOT NULL COMMENT '使用者狀態',
  `create_time` datetime NOT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_username` (`username`)  -- 唯一性約束
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

load命令

適當的設定可以提高匯入的效率。

對於 InnoDB 型別的表，有以下幾種方式可以提高匯入的效率：

1） 主鍵順序插入

因為InnoDB型別的表是按照主鍵的順序儲存的，所以將匯入的資料按照主鍵的順序排列，可以有效的提高匯入資料的效率。如果InnoDB表沒有主鍵，那麼系統會自動預設建立一個內部列作為主鍵，所以如果可以給表建立一個主鍵，將可以利用這點，來提高匯入資料的效率。

指令碼檔案介紹 :

sql1.log —-> 主鍵有序
sql2.log —-> 主鍵無序
​

插入主鍵順序排列資料：

主鍵無序：

出現了許可權問題

執行：set global local_infile=on;
​

但又出現了另一個問題：

其實我們開啟之後， 需要退出重新連線，再次連線時便可以正常操作了

• 如果還是不行的話，連線的時候可以這樣連線：

mysql --local_infile=1 -u root -ppassword

2）關閉唯一性校驗

在匯入資料前執行 SET UNIQUE_CHECKS=0，關閉唯一性校驗，在匯入結束後執行 SET UNIQUE_CHECKS=1，恢復唯一性校驗，可以提高匯入的效率。

環境準備

CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `age` int(3) NOT NULL,
  `salary` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

insert into `emp` (`id`, `name`, `age`, `salary`) values('1','Tom','25','2300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('2','Jerry','30','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('3','Luci','25','2800');
insert into `emp` (`id`, `name`, `age`, `salary`) values('4','Jay','36','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('5','Tom2','21','2200');
insert into `emp` (`id`, `name`, `age`, `salary`) values('6','Jerry2','31','3300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('7','Luci2','26','2700');
insert into `emp` (`id`, `name`, `age`, `salary`) values('8','Jay2','33','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('9','Tom3','23','2400');
insert into `emp` (`id`, `name`, `age`, `salary`) values('10','Jerry3','32','3100');
insert into `emp` (`id`, `name`, `age`, `salary`) values('11','Luci3','26','2900');
insert into `emp` (`id`, `name`, `age`, `salary`) values('12','Jay3','37','4500');

create index idx_emp_age_salary on emp(age,salary);

兩種排序方式

using index

直接能在索引列完成查詢，無需回表，關於回表查詢，可以參考 這篇文章 ，此時需要保證所查詢的欄位都是索引欄位，才會是using index

但這個不太現實，不可能說我們要查的，都是索引的欄位，所以很多情況下，我們並沒有辦法把using filesort最佳化為using index，只能退而求其次，儘量從filesort的角度去最佳化，透過外部條件。

" class="reference-link">?using filesort

何時會出現：

1. order by的欄位不是索引
2. order by 欄位是索引欄位，但是 select 中沒有使用覆蓋索引
3. order by 中同時存在 ASC 升序排序和 DESC 降序排序

1. order by中用到的是複合索引，但沒有保持複合索引中欄位的先後順序（即違背了最左字首原則）

比如圖中的 select id,age,salary from emp order by salary,age；

為什麼呢？這裡我們得回顧一下複合索引是如何儲存的，比如：我們建立一個複合索引(name，status，address)，索引中也是按這個欄位來儲存的，類似圖中表格這樣：

複合索引樹(只儲存索引列和主鍵用於回表)，而且是先按name排序，name相同了再按status排序，以此類推

所以如果我們不按照索引的先後順序來order by的話，就跟索引樹中的排序規則不一樣了，索引此時排好的序，我們都沒辦法合理利用到，自然MySQL不會去走索引了。

?Filesort的最佳化

兩種掃描演算法

對於Filesort ， MySQL 有兩種排序演算法：
以這條SQL語句為例，我們來看看他是怎麼執行的：

select * from emp where age=1 order by salary；

1） 兩次掃描演算法 ：MySQL4.1 之前，使用該方式排序。

①首先根據where條件，過濾得到相應的滿足age=1的salary，取出排序欄位salary和對應的行指標資訊(用於回表)，然後在排序區 sort buffer 中排序，如果sort buffer不夠，則在臨時表 temporary table 中儲存排序結果。

②完成排序之後，再根據行指標回表讀取所有欄位，而次該操作可能會導致大量隨機I/O操作，是我們需要改進的地方。

這就是所謂的兩次掃描，第一次掃描，我們拿到的只是排序欄位，然後在sort buffer排好序；第二次掃描，才去回表讀取所有欄位，最終返回。

該如何最佳化呢？為什麼要分成兩次，有沒有一種可能是空間不夠呢？那我們如果有足夠的空間，以空間換時間，是不是就可以開闢出一種新的方法，只需要一次掃描即可

2）一次掃描演算法：一次性取出滿足條件的所有欄位，然後在排序區 sort buffer 中排序後直接輸出結果集。排序時記憶體開銷較大，但是排序效率比兩次掃描演算法要高，典型的以空間換時間的思想。

具體使用哪種演算法呢？
MySQL 透過比較系統變數 max_length_for_sort_data 的大小和 Query語句取出的欄位總大小， 來判定使用那種排序演算法，如果max_length_for_sort_data 更大，那麼使用一次掃描演算法；否則使用兩次掃描演算法。

最佳化方案

① 增大前者 max_length_for_sort_data
可以適當 max_length_for_sort_data 系統變數，來增大排序區的大小，提高排序的效率，這是典型的空間換時間的思想。

② 減小後者 Query語句取出的欄位總大小
如果記憶體實在不夠富裕的話，我們可以減少查詢的欄位，避免select *

③ 提高 sort_buffer_size ：
由上文可知，透過增大該引數，可以讓 MySQL 儘量減少在排序過程中對須要排序的資料進行分段，避免需要使用到臨時表 temporary table 來儲存排序結果，再把多次的排序結果串聯起來。

可惜，MySQL無法檢視它用了哪個演算法。如果增加了max_Length_for_sort_data變數的值，磁碟使用率上升了，CPU使用率下降了，並且Sort_merge_passes狀態變數相對於修改之前開始很快地上升，也許是MySQL強制讓很多的排序使用了一次掃描演算法。

具體的實戰修改過程，需要結合MySQL中另一個工具–trace分析最佳化器，來分析執行計劃，後續有機會，我們再來詳細聊一聊！

由於GROUP BY 實際上也同樣會進行排序操作，而且與ORDER BY 相比，GROUP BY 主要只是多了排序之後的分組操作。當然，如果在分組的時候還使用了其他的一些聚合函式，那麼還需要一些聚合函式的計算。所以，在GROUP BY 的實現過程中，與 ORDER BY 一樣也可以利用到索引。

1. 使用索引

先來看看無索引的情況：using temporary；using filesort

建立索引

create index idx_emp_age_salary on emp(age,salary)；

2. 加上order by null 禁止排序

如果查詢包含 group by 但是使用者想要避免排序結果的消耗， 則可以執行order by null 禁止排序。如下 ：

3. 需要排序 (則跟order by的最佳化大體相同)

Mysql4.1版本之後，開始支援SQL的子查詢。這個技術可以使用SELECT語句來建立一個單列的查詢結果，然後把這個結果作為過濾條件用在另一個查詢中。使用子查詢可以一次性的完成很多邏輯上需要多個步驟才能完成的SQL操作，同時也可以避免事務或者表鎖死，並且寫起來也很容易。

但是，有些情況下，子查詢是可以被更高效的連線（JOIN）替代的！！

示例 ，查詢有角色的所有的使用者資訊 :

 explain select * from t_user where id in (select user_id from user_role );

執行計劃為 :

最佳化後 :

explain select * from t_user u , user_role ur where u.id = ur.user_id;

連線(Join)查詢之所以更有效率一些 ，是因為MySQL不需要在記憶體中建立臨時表來完成這個邏輯上需要兩個步驟的查詢工作。

對於包含OR的查詢子句，如果要利用索引，則OR之間的每個條件列都必須用到索引 ， 而且不能使用到複合索引； 如果沒有索引，則應該考慮增加索引。

我們此處有一個id主鍵索引，和一個age，salary複合索引：

單列+複合中的某一個

explain select * from emp where id = 1 or age = 30;

單列+單列（兩個一樣）

實際上等效於range，此處只是提供一個示例

解決：使用union最佳化！！！

最佳化前

• type：index_merge

最佳化後

• type：一個是const，一個是ref，都比index快

SQL提示，是最佳化資料庫的一個重要手段，簡單 來說，就是在SQL語句中加入一些人為的提示來達到最佳化操作的目的。

use index

在查詢語句中表名的後面，新增 use index 來提供希望MySQL去參考的索引列表，就可以讓MySQL不再考慮其他可用的索引。

ingore index

如果使用者只是單純的想讓MySQL忽略一個或者多個索引，則可以使用 ignore index 作為 hint 。

force index

強制走索引，即使MySQL認為全表更快，我們用force也可以強制走索引。

跟use的區別

• use只是提供一個參考，具體用不用還得看MySQL的最佳化器怎麼想的

一個常見又非常頭疼的問題就是 limit 2000000,10 ，此時需要MySQL排序 前2000010 記錄，僅僅返回2000000 - 2000010 的記錄，其他記錄丟棄，查詢排序的代價非常大 。

比如我們有這樣一條語句，select * from tb_item limit 2000000,10 ;

此時預設是根據 id 排序的。

最佳化思路一

在索引上完成排序分頁操作，最後根據主鍵關聯回原表查詢所需要的其他列內容。

最佳化思路二

該方案適用於主鍵自增的表，可以把Limit 查詢轉換成某個位置的查詢 。(侷限性：主鍵不能斷層)

• 如果要根據其他欄位來排序的話，此方法就無法做到了。

一次連線，多次插入

比如我們需要插入三條資料：

insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');

此時需要建立三次連線，每次連線都要消耗資源，為了提高單次連線的執行效率，我們會採取：

insert into tb_test values(1,'Tom'),(2,'Cat')，(3,'Jerry');

同時，insert的時候最好是保持資料的有序插入

1. explain分析SQL中，其中比較重要的主要是type，key，ref以及extra，我們不需要死記硬背，多拿幾條語句去explain比對比對，更有利於我們輔助記憶。

2. 索引失效的幾個場景，借用b站熱評：

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

1. 最佳化基本原則：巧用索引，減少連線次數。

最後，能看到這裡真的是很不容易了，其實這篇文章，相比上篇，更多還是以黑馬課程的思路來整理的，自己只是在其中補充和完善了一下小案例，指正了小錯誤，受眾可能更偏向於b站影片使用者。

當然了，這也只是初級階段，跟著影片學，整理筆記，初級階段過後呢，就需要在此基礎上，去對實際專案中的SQL去最佳化，最佳化過後還需要深入理解如此最佳化的原理，本文的order by，還沒有過多深入其底層原理，只是大概提及到了有 sort_buffer 這麼個東西，以及對應的兩種掃描演算法，order by底層到底是如何實現的，怎麼透過本文的方法，去最佳化諸如以下這種語句：

select * from table order by xxx;

這個xxx，不管是不是索引，按這樣去查詢的話，大機率都是using filesort且不會用到索引的，除非說我們去limit xx，這個xx還要很小，才會使用到索引。

這些，才是我們更進一步的底樑柱，筆記大家都有，只是誰整理得好看一點，多了一些自己的思考罷了。

本作品採用《CC 協議》，轉載必須註明作者和本文連結