利用explain排查分析慢sql的實戰案例

2022-04-19 16:00:13

1.1.1 全值匹配我最愛
1.1.2 最佳左字首法則，帶頭大哥不能死，中間兄弟不能斷；
1.1.3 索引列上少計算
1.1.4 範圍之後全失效
1.1.5 覆蓋索引不寫 *
1.1.6 使用不等於(!= 或者<>)的時候
1.1.7 不用 is null 或者is not null
1.1.8 LIKE 百分寫最右
1.1.9 字串不加單引號索引失效
1.1.10 少用or，用它來連線時會索引失效。

1.2 案例分析

1.3 建索引總結

二案例分析

總結

一概述

1.0 sql調優的過程

SQL調優過程：

觀察，至少跑1天，看看生產的慢SQL情況。
開啟慢查詢紀錄檔，設定闕值，比如超過5秒鐘的就是慢SQL，並將它抓取出來。
explain + 慢SQL分析。
show profile，查詢SQL在Mysql伺服器裡面的執行細節和生命週期情況。
運維經理 or DBA，進行SQL資料庫伺服器的引數調優。

1.1 優化索引口訣

優化的口訣如下：

全值匹配我最愛，最佳左字首法則 ；

帶頭大哥不能死，中間兄弟不能斷；

索引列上少計算，範圍之後全失效；

LIKE 百分寫最右，覆蓋索引不寫 *；

不等空值還有 OR，索引影響要注意；

VAR 引號不可丟， SQL 優化有訣竅。

注意：以下操作都是在所建索引

CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);

下進行的操作，如下:

1.1.1 全值匹配我最愛

CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);

結論：全職匹配我最愛指的是，查詢的欄位按照順序在索引中都可以匹配到！ ,SQL 中查詢欄位的順序，跟使用索引中欄位的順序，沒有關係。優化器會在不影響 SQL 執行結果的前提下，給你自動地優化。

1.1.2 最佳左字首法則，帶頭大哥不能死，中間兄弟不能斷；

使用複合索引，需要遵循最佳左字首法則，即如果索引了多列，要遵守最左字首法則。指的是查詢從索引的最左前列開始並且不跳過索引中的列

前面兩個sql索引失效的原因：

即使跳過了中間的索引，但是其長度沒變化，跟第一個sql只使用name的索引的長度一樣，那就說明第二個sql值使用了部分索引，只使用了name的索引，後面的age，pos失效。不然的話長度肯定大於74。

結論：過濾條件要使用索引必須按照索引建立時的順序，依次滿足， 一旦跳過某個欄位，索引後面的欄位都無法被使用。

1.1.3 索引列上少計算

不在索引列上做任何操作（計算、函數、（自動or手動）型別轉換），會導致索引失效而轉向全表掃描。

所以字串型別的資料，該加單引號的一定要加！

1.1.4 範圍之後全失效

複合索引：CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);

索引列上不能有範圍查詢，少用>，<，between…and等結構；範圍查詢的列忽略，索引失效，後面的索引列也跟著失效，不起作用。

建議：將可能做範圍查詢的欄位的索引順序放在最後

1.1.5 覆蓋索引不寫 *

即查詢列和索引列一致，不要寫 select *!， 儘量使用覆蓋索引（只存取索引的查詢（索引列和查詢列一致））區別在於extra，索引的不同，速度不一樣

1.1.6 使用不等於(!= 或者<>)的時候

mysql 在使用不等於 (!= 或者 <>) 時，有時會無法使用索引會導致全表掃描。這個得知道這種情況，根據業務情況，得寫這種範圍查詢，還是要寫的。一切滿足業務。

1.1.7 不用 is null 或者is not null

is not null 用不到索引，如果某列欄位中包含null，is null是可以用到索引的如果某列欄位中不包含null，is null是不可以用到索引的

1.1.8 LIKE 百分寫最右

1. 注意看模糊查詢的細節，只有xx%字首查詢才不會失效

2.如果要實現兩邊百分號，不能失效，%xx%，可以使用覆蓋索引來解決

增加一個索引CREATE INDEX idx_user_nameAge ON tbl_user(NAME,age);

如果使用下面這些，都是使用的覆蓋索引，結果都是一樣的， 注意id之所以沒加索引，但是可以加入使用不會失效，是因為他是主鍵

但是但是如果加入了沒有主鍵又不是索引的東西，%xx%就會失效

1.1.9 字串不加單引號索引失效

如varchar型別，自己寫成int型，雖然型別不正確也可以查詢，但是底層會幫你轉換型別，索引直接失效，變成了全表查詢。字串不加單引號索引失效。

1.1.10 少用or，用它來連線時會索引失效。

少用or，用它來連線時會索引失效。

用使用 union all 或者 union 來替代：

1.2 案例分析

1.3 建索引總結

1.對於單鍵索引，儘量選擇針對當前query過濾性更好的索引。

2.在選擇組合索引的時候，當前Query中過濾性最好的欄位在索引欄位順序中，位置越靠前越好。

3.在選擇組合索引的時候，儘量選擇可以能夠包含當前query中的where字句中更多欄位的索引。

4.儘可能通過分析統計資訊和調整query的寫法來達到選擇合適索引的目的。

二案例分析

2.1 單表分析

2.2.1 分析過程

1.sql語句

explain SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

通過執行檢視發現：此sql進行了全表查詢，而且在extra還出現了Using filesort等問題。

2解決辦法：建立其複合索引

create index idx_article_ccv on article(category_id,comments,views);

或者

ALTER TABLE 'article' ADD INDEX idx_article_ccv ( 'category_id , 'comments', 'views' );

通過觀察發現：掃描的範圍發生改變，變為range，但是extra還是using filetext。

3.原因在於：

當comments欄位在聯合索引裡處於中間位置時，因comments > 1條件是一個範圍值(所謂range)，MySQL無法利用索引再對後面的views部分進行檢索，即range型別查詢欄位後面的索引無效。

4.解決辦法：建立複合索引是對的，但是其思路要避開中間那個範圍的索引進去。只加入另外兩個索引即可create index idx_article_cv on article(category_id, views);

到此優化完成！

2.2.2 原因結論

索引失效違反的規則為：

聯合索引中，範圍索引欄位之後全失效。

2.2 兩表表分析

2.2.1 案例準備

1.calss表

CREATE TABLE IF NOT EXISTS `class` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
);

2.book表

CREATE TABLE IF NOT EXISTS `book` (
bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`bookid`)
);

1.不建索引

2.假設在class 表中對card欄位建立索引

在 class 表上建立索引：alter table class add index idx_card(card);

3.假設在book表中對card欄位建立索引

ALTER TABLE `book` ADD INDEX idx_card( `card`);

通過，1，2，3情況的比較，2，3使用到了索引，要比1的情況好，3要比2的好，查詢效率高，通過過book表，這個大端表實現rows為1，etra為using index 相比情況2中book使用到索引，效率高。

2.2.2 結論

left join 時候，小表放到左邊，小表叫驅動表，大表放到右邊，大表叫被驅動表。

優化關聯查詢時，只有在被驅動表上建立索引才有效！

索引兩表優化，左連線右表建索引，右連線左表建索引

2.3 3表表分析

2.3.1 案例

現在再建一張新表：

CREATE TABLE IF NOT EXISTS phone(
    phoneid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    card INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY(phoneid)
)ENGINE=INNODB;

新增必要的索引

通過後兩張表

分別是alter tablephoneadd index z(card);，以及alter tablebookadd index y(card);

2.3.2 結論

leftjoin時：永遠用小表結果集驅動大表結果集，保證join語句中被驅動的表的join條件的欄位新增了索引

2.4 4表表分析

2.4.1 實驗比較

1.虛表作為左表，實表作為右表

EXPLAIN SELECT ed.name ' 人物 ',c.name ' 掌門 ' FROM (SELECT e.name,d.ceo from t_emp e LEFT JOIN t_dept d on e.deptid=d.id) ed LEFT JOIN t_emp c on ed.ceo= c.id;

2.實體表作為左表，虛表作為實體表

EXPLAIN SELECT e.name ' 人物 ',tmp.name ' 掌門 ' FROM t_emp e LEFT JOIN (SELECT d.id did,e.name FROM t_dept d LEFT JOIN t_emp e ON d.ceo=e.id)tmp ON e.deptId=tmp.did;

3. 直接關聯

EXPLAIN SELECT e1.name ' 人物 ',e2.name ' 掌門 ' FROM t_emp e1 LEFT JOIN t_dept d on e1.deptid = d.id LEFT JOIN t_emp e2 on d.ceo = e2.id ;

2.4.2 結論

第一個查詢效率較高，且有優化的餘地。第二個案例中， 子查詢作為被驅動表，由於子查詢是虛表，無法建立索引，因此不能優化 1子查詢儘量不要放在被驅動表，有可能使用不到索引；

2left join時，儘量讓實體表作為被驅動表。

3.第3種情況，相對於前面2種，進行直接關聯。 能夠直接多表關聯的儘量直接關聯，不用子查詢。

2.5 inner join關聯

2.5.1 例子

1.EXPLAIN SELECT * FROM book inner join class on class.card=book.card;

2.EXPLAIN SELECT * FROM class inner join book on class.card=book.card;

2.5.2 結論

兩個查詢欄位調換順序，發現結果也是一樣的！ inner join 時，mysql 會自己幫你把小結果集的表選為驅動表。

2.6 子查詢優化

2.6.1 情況描述

1.查詢所有不為掌門人的員工，按年齡分組！ select age as ' 年齡 ', count(*) as ' 人數' from t_emp where id not in (select ceo from t_dept where ceo is not null) group by age;

可以看到 dept表是全表掃描。沒有使用上索引

2.解決 dept 表的全表掃描，建立 ceo 欄位的索引：

3.再次查詢

4.修改sql寫法：替換 not in

select age as ' 年齡 ',count(*) as ' 人數 ' from emp e left join dept d on e.id=d.ceo where d.id is null group by age;

2.6.2 結論

在範圍判斷時，儘量不要使用 not in 和 not exists，使用 left join on xxx is null 代替。

2.7 小表驅動大表（in 或者exits的使用）

2.7.1 情況舉例

select * from A where id in (select id from B)

1.當B表的資料集必須小於A表的資料集時，用in優於exists。

等價於:

for select id from B

for select * from A where A.id = B.id

2.當A表的資料集系小於B表的資料集時，用exists優於in

等價於：

for select * from A

for select * from B where B.id = A.id

2.7.2 結論

1.小表驅動大表

2.EXSTS(subquey)只返回TRUE或FALSE，因此子查詢中的SELECT * 也可以是 SELECT 1 或select ‘X’，官方說法是實際執行時會忽略SELECT清單，因此沒有區別。

3.EXISTS子查詢往往也可以用條件表示式，其他子查詢或者JOIN來替代，何種最優需要具體問題具體分析

2.8 order by

2.8.1 案例說明

1.建立一張表

create table tblA( #id int primary key not null auto_increment, age int, birth timestamp not null );

2.建立索引

索引 create index idx_A_ageBirth on tblA(age, birth);

1.如果索引的時候也是按照順序

2.索引的時候不按順序就會出現這樣的情況

2.8.2 結論

1.MySQL支援二種方式的排序：FileSort和lIndex

Index效率高，它指MySQL掃描索引本身完成排序

FileSort方式效率較低。

2 ORDER BY子句，儘量使用Index方式排序，避免使用FileSort方式排序。

3. ORDER BY滿足兩情況，會使用Index方式排序：

沒有where條件過濾的情況，ORDER BY語句使用索引最左字首原則。

使用where子句與Order BY子句條件列組合滿足索引最左字首原則。

4.order by 時候，select * 是一個大忌，select * 影響排序速度。影響情況：

當Query的欄位大小總和小於max_length_for_sort_data而且排序欄位不是TEXT|BLOB型別時，會用改進後的演演算法——單路排序，否則用老演演算法——多路排序。
兩種演演算法的資料都有可能超出sort_buffer的容量，超出之後，會建立tmp檔案進行合併排序，導致多次IO，但是用單路排序演演算法的風險會更大一些，所以要提高sort_buffer__size。
嘗試提高sort_buffer_size，不管用哪種演演算法，提高這個引數都會提高效率，當然，要根據系統的能力去提高，因為這個引數是針對每個程序的。
嘗試提高max_length_for_sort_data，提高這個引數，會增加用改進演演算法的概率。但是如果設的太高，資料總容量超出sort_buffer_size的概率就增大，明顯症狀是高的磁碟I/O活動和低的處理器使用率。

4.常見案例

2.9 group by

2.9.1 結論

GroupBy優化（和order by差不多）

group by實質是先排序後進行分組，遵照索引建的最佳左字首。

當無法使用索引列，增大max_length_for_sort_data引數的設定 + 增大sort_buffer_size引數的設定。

where高於having，能寫在where限定的條件就不要去having限定了。

group by 使用索引的原則幾乎跟 order by 一致，唯一區別是 groupby 即使沒有過濾條件用到索引，也可以直接使用索引

2.10 最左字首原則

2.10.1 描述

1.建表

CREATE TABLE `tb_student` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(10) DEFAULT NULL,
  `pos` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

2.10.2 不建索引的情況

explain   select * from tb_student

結論：可以看到是全表掃描

2.10.3 建立單列索引

alter table tb_student add index name_index(name);
desc select * from tb_student where  name='ljf'

結論：使用到了索引，且索引級別為ref；

2.10.4 建立複合索引

alter table tb_student drop index name_index;
 alter table tb_student add index name_age_pos_index(name,age,pos);
  show index from tb_student

情況1：使用到了索引，但是沒有用到覆蓋索引，select * (id,name,age,pos,address)查詢的列大於所建的索引列(name,age,pos),etra 為null
desc select name,age,pos,address from tb_student where name='ljf' and age=22 and pos='dd'

    desc select * from tb_student where  name='ljf'

    desc select address from tb_student where  name='ljf'

情況2：使用到了索引，走覆蓋索引，所建的索引列，正好能覆蓋住 select 要查詢的列，走覆蓋索引，etra 為using index

    desc select name from tb_student where  name='ljf'
        desc select age from tb_student where  name='ljf'
        desc select pos from tb_student where  name='ljf'
      desc select name,pos from tb_student where  name='ljf'
        desc select name,pos from tb_student where  name='ljf' and age=22 and pos='dd'
                desc select name,age,pos from tb_student where  name='ljf' 
                             desc select name,age,pos from tb_student where  name='ljf' and age=23
        #（255*3+2+1）+（4+1）+（255*3+2+1）=1541
        desc select name,age,pos from tb_student where  name='ljf' and age=22 and pos='dd'

情況3：使用到了索引，使用部分索引，部分索引欄位失效，有using index，和using where 條件過濾

     #中間兄弟不能斷
             desc select name,age,pos from tb_student where  name='ljf' and pos='dd'
              desc select age,pos from tb_student where  name='ljf' and pos='dd'

desc select * from tb_student where  name='ljf' and pos='dd'

                #帶頭大哥不能斷
                desc select name,pos from tb_student where     pos='dd'

2.11 綜合where+group by

explain select d.deptName,if(avg(age)>40,' 老鳥 ',' 菜鳥 ') from dept d inner join emp e
on d.id=e.deptid
group by d.deptName,d.id

檢視：

優化思路：

1.在emp表中對deptid欄位建立索引；create index index_deptid on emp(deptid);

2.在 deprt表中對depatname，id兩個欄位建立索引：create index idx_deptName_id on dept(deptName,id);

3.dept為驅動表，emp為被驅動表

2.12 綜合

1.首先建立表

 create  table student(
    id int,
      first_name varchar(10),
     last_name varchar(10),
     primary key(id),
    key index_first(first_name)
)engine=innodb default charset=utf8;

1.進行查詢

 -- 插入資料
insert into student values (1,'a','b');
 -- 按照first_name查詢
 desc select first_name,last_name from student where first_name='a';

結論：當設定first_name為普通索引(單列索引)，按照first_name查詢；type:ref、possible_keys:indes_first、key:indes_first、extra:null，用到索引；

2.使用複合索引

-- 設定first_name,last_name複合索引
 alter table student drop index index_first;
 alter table student add index index_name(first_name,last_name);
-- 按照first_name查詢
 desc select first_name,last_name from student where first_name='a';

結論：當設定first_name，last_name為複合索引(聯合索引)，按照first_name查詢；type:ref、possible_keys:indes_name、key:indes_name、extra:Using index；type:ref用到索引，因為是複合索引不需要回表掃描，extra:Using index索引覆蓋；注意此時key_len為33=10*3(utf8)+2(變長)+1(null標誌位)，用到了複合索引的一部分即first_name

3.使用複合索引

結論：當設定first_name，last_name為複合索引(聯合索引)，按照last_name查詢；type:index、possible_keys:null、key:indes_name、extra:Using where，Using index；type:index而不是ref，掃描索引樹，複合索引的最左原則；此時key_len為66=10*3(utf8)+2(變長)+1(null)+10*3(utf8)+2(變長)+1(null標誌位)；Using where應where子句進行限制

違反最左原則，索引級別從req 變成了index。

MySQL——執行計劃 - ClassicalRain

總結

到此這篇關於利用explain排查分析慢sql的文章就介紹到這了,更多相關explain排查分析慢sql內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！