大家好，我是melo，一名大三后臺練習生

專欄回顧

索引的原理&&設計原則
歡迎關注本專欄：MySQL高級篇

本篇速覽

在我們上一篇文章中，講到了索引的原理&&設計原則，知道了索引如何使用，
emm？那具體什么場景需要用到索引，我們要怎么分析SQL陳述句，并對其進行優化呢，這篇將從以下幾點帶你攻破ta：

詳解explain分析SQL
索引失效的幾個場景
- ......
SQL優化的幾個場景
- 大批量插入
- order by
- group by
- limit分頁
- insert操作
- 嵌套查詢
- or條件

注意，本文MySQL版本為5.6.43，部分結論在其他版本可能不適用！！！

本篇篇幅較長，全文近8500字，可以收藏下來慢慢啃，沒事就掏出來翻閱翻閱，

建議通過側邊欄目錄檢索對您有幫助的部分，其中有emoji表情前綴屬于重點部分，覺得對您有幫助的話，melo還會持續更進完善本篇文章和MySQL專欄，

好，現在我們已經掌握了索引的基本原理和使用方法了，要來大干一場優化SQL了！等等，我們要優化什么SQL來著，褲子都脫了，結果沒物件可以.....

別著急，這篇既然掛著MySQL高級篇，自然MySQL還是很高級的，給我們提供了幾種方法，來為我們找到SQL，并分析SQL，
本篇，我們先著重講解如何分析，具體如何找到SQL，后續的實戰篇，我們再來詳細談一談，

??【一、explain】分析SQL

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

欄位	含義
id	select查詢的序列號，是一組數字，表示的是查詢中執行select子句或者是操作表的順序，
select_type	表示 SELECT 的型別，常見的取值有 SIMPLE（簡單表，即不使用表連接或者子查詢）、PRIMARY（主查詢，即外層的查詢）、UNION（UNION 中的第二個或者后面的查詢陳述句）、SUBQUERY（子查詢中的第一個 SELECT）等
table	輸出結果集的表
partitions	查詢時匹配到的磁區資訊，對于非磁區表值為NULL，當查詢的是磁區表時，partitions顯示磁區表命中的磁區情況，
type	表示表的連接型別，性能由好到差的連接型別為( system ---> const -----> eq_ref ------> ref -------> ref_or_null----> index_merge ---> index_subquery -----> range -----> index ------> all )
possible_keys	表示查詢時，可能使用的索引
key	表示查詢時，實際使用的索引
key_len	索引欄位的長度，可用來區分長短索引
rows	掃描行的數量
filtered	表里符合條件的記錄數所占的百分比
extra	執行情況的說明和描述

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

id

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

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

id一樣，則從上到下

id不同，則id值越大，優先級越高

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

簡而言之：

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

select_type

SELECT_TYPE	含義
SIMPLE	簡單的select查詢，查詢中不包含子查詢或者UNION
PRIMARY	查詢中若包含任何復雜的子查詢，最外層查詢標記為該標識
SUBQUERY	在SELECT 或 WHERE 串列中包含了子查詢
DERIVED	在FROM 串列中包含的子查詢，被標記為 DERIVED（衍生） MYSQL會遞回執行這些子查詢，把結果放在臨時表中
UNION	若第二個SELECT出現在UNION之后，則標記為UNION ；若UNION包含在FROM子句的子查詢中，外層SELECT將被標記為： DERIVED
UNION RESULT	從UNION表獲取結果的SELECT

PRIMARY,SUBQUERY

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

type

TYPE	含義
NULL	MySQL不訪問任何表，索引，直接回傳結果
system	表只有一行記錄(等于系統表)，這是const型別的特例，一般不會出現
const	表示通過索引一次就找到了，const 常用于primary key 或者 unique 索引(本質上都是唯一索引)，因為只匹配一行資料，所以很快，如將主鍵置于where串列中，MySQL 就能將該查詢轉換為一個常量，const于將 "主鍵" 或 "唯一" 索引的所有部分與常量值進行比較
eq_ref	類似ref，區別在于使用的是唯一索引，使用主鍵的關聯查詢，關聯查詢出的記錄只有一條，常見于主鍵或唯一索引掃描
ref	非唯一性索引掃描，回傳匹配某個單獨值的所有行，本質上也是一種索引訪問，回傳所有匹配某個單獨值的所有行（多個）
range	只檢索給定回傳的行，使用一個索引來選擇行， where 之后出現 between ， < , > , in 等操作，
index	index 與 ALL的區別為 index 型別只是遍歷了索引樹，通常比ALL 快， ALL 是遍歷資料檔案，
all	將遍歷全表以找到匹配的行

結果值從最好到最壞以此是：

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

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

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

rows

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

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

filtered

表里符合條件的記錄數的所占的百分比，

extra

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

EXTRA	含義
using filesort	說明mysql會對資料使用一個外部的索引排序，而不是按照表內的索引順序進行讀取，表示無法利用索引完成的排序操作，稱為 “檔案排序”, 效率低，
using temporary	使用了臨時表保存中間結果，MySQL在對查詢結果排序時使用臨時表，常見于 order by 和 group by；效率低
using index	表示相應的select操作使用了覆寫索引，直接從索引中過濾掉不需要的結果，無需回表，效率不錯，
using index condition	索引下推！！查找使用了索引，但是需要回表查詢資料，此時就是因為索引列沒有完全包含查詢列

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

using where

不同版本好像不一樣

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

現在，我們知道怎么用explain來分析SQL陳述句了，自然可以來剖析我們的SQL陳述句的性能，不過早有先人給我們總結了幾個需要優化的場景-->索引失效?

??【二、索引失效】的幾個場景

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，最左前綴，要求我們不能出現跳躍樓梯的情況，否則會導致我們的索引失效：
?

按樓梯從低到高，無出現跳躍的情況--此時符合最左前綴原則，索引不會失效

出現跳躍的情況

直接第一層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. !=或者<>

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

??【三、優化場景】1. 大批量插入資料

環境準備

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，恢復唯一性校驗，可以提高匯入的效率，

??2. order by 排序

環境準備

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的角度去優化，通過外部條件，

??using filesort

何時會出現：

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

order by中用到的是復合索引，但沒有保持復合索引中欄位的先后順序（即違背了最左前綴原則）

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

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

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

name	status	address	id(主鍵)
小米1	0	1	1
小米2	1	1	2

所以如果我們不按照索引的先后順序來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分析優化器，來分析執行計劃，后續有機會，我們再來詳細聊一聊！

3. group by 分組

由于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的優化大體相同)

4. 優化子查詢

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不需要在記憶體中創建臨時表來完成這個邏輯上需要兩個步驟的查詢作業，

5. 優化OR條件

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

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

單列+復合中的某一個

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

單列+單列（兩個一樣）

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

解決：使用union優化！！！

優化前

type：index_merge

優化后

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

6. 使用SQL提示

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

use index

在查詢陳述句中表名的后面，添加 use index 來提供希望MySQL去參考的索引串列，就可以讓MySQL不再考慮其他可用的索引，

ingore index

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

force index

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

跟use的區別

use只是提供一個參考，具體用不用還得看MySQL的優化器怎么想的

?7. 優化limit分頁

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

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

此時默認是根據 id 排序的，

優化思路一

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

優化思路二

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

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

8. 優化insert操作

一次連接，多次插入

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

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的時候最好是保持資料的有序插入

??總結

explain分析SQL中，其中比較重要的主要是type，key，ref以及extra，我們不需要死記硬背，多拿幾條陳述句去explain比對比對，更有利于我們輔助記憶，
索引失效的幾個場景，借用b站熱評：

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

優化基本原則：巧用索引，減少連接次數，

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

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

select * from table order by xxx;

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

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

??下篇預告

這兩篇，我們了解了索引的理論知識，并知道了如何避免索引失效，分析并優化SQL，SQL陳述句優化方面也暫時告一段落了，當然了，SQL優化也可以通過MySQL其他方面來進行，工具，日志，并發引數等，這些我們等到后邊再詳細了解，

下一篇呢，我們先把目光放到MySQL本體上，了解了解MySQL的邏輯架構與存盤引擎

??參考教程

黑馬MySQL高級篇

收藏=白嫖，點贊+關注才是真愛！！！本篇文章如有不對之處，還請在評論區指出，歡迎添加我的微信一起交流：Melo__Jun

??友鏈

??我的一年后臺練習生涯
聊聊Java
分布式開發實戰
Redis入門與實戰
資料結構與演算法
MySQL高級篇

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

標籤：其他

上一篇：教你處理數倉慢SQL常見定位問題

下一篇：京東云開發者｜京東云RDS資料遷移常見場景攻略

「MySQL高級篇」explain分析SQL，索引失效&&常見優化場景

專欄回顧

本篇速覽

??【一、explain】分析SQL

id

簡而言之：

select_type

PRIMARY,SUBQUERY

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

type

const

eq_ref，ref

index，all

possible_keys，key

ref

rows

filtered

extra

using where

??【二、索引失效】的幾個場景

0. SQL準備

1. 不滿足最左前綴

2. 范圍查詢之后

3. 索引欄位做運算

4. 字串不加' '

5. 避免select *

危害

??覆寫索引

6. or分割開的條件

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

解決方法：使用覆寫索引

8. MySQL認為全表更快

8.1 is null 和 is not null

8.2 in 和 not in

總結

9. !=或者<>

相關習題

??【三、優化場景】1. 大批量插入資料

環境準備

load命令

1） 主鍵順序插入

出現了權限問題

但又出現了另一個問題：

2）關閉唯一性校驗

??2. order by 排序

環境準備

兩種排序方式

using index

??using filesort

何時會出現：

??Filesort的優化

兩種掃描演算法

優化方案

3. group by 分組

1. 使用索引

創建索引

2. 加上order by null 禁止排序

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

4. 優化子查詢

5. 優化OR條件

單列+復合中的某一個

單列+單列（兩個一樣）

解決：使用union優化！！！

優化前

優化后

6. 使用SQL提示

use index

ingore index

force index

跟use的區別

?7. 優化limit分頁

優化思路一

優化思路二

8. 優化insert操作

一次連接，多次插入

??總結

??下篇預告

??參考教程

??友鏈

1）主鍵順序插入