一 概述

1.0 sql調(diào)優(yōu)的過程

SQL調(diào)優(yōu)過程：

1. 觀察，至少跑1天，看看生產(chǎn)的慢SQL情況。
2. 開啟慢查詢?nèi)罩?，設(shè)置闕值，比如超過5秒鐘的就是慢SQL，并將它抓取出來。
3. explain + 慢SQL分析。
4. show profile，查詢SQL在Mysql服務(wù)器里面的執(zhí)行細節(jié)和生命周期情況。
5. 運維經(jīng)理 or DBA，進行SQL數(shù)據(jù)庫服務(wù)器的參數(shù)調(diào)優(yōu)。

1.1 優(yōu)化索引口訣

優(yōu)化的口訣如下：

全值匹配我最愛， 最佳左前綴法則 ；

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

索引列上少計算， 范圍之后全失效；

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

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

VAR 引號不可丟， SQL 優(yōu)化有訣竅。

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

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);

結(jié)論：全職匹配我最愛指的是，查詢的字段按照順序在索引中都可以匹配到！ ,SQL 中查詢字段的順序，跟使用索引中字段的順序，沒有關(guān)系。優(yōu)化器會在不影響 SQL 執(zhí)行結(jié)果的前提下，給你自動地優(yōu)化。

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

使用復(fù)合索引，需要遵循最佳左前綴法則，即如果索引了多列，要遵守最左前綴法則。指的是查詢從索引的最左前列開始并且不跳過索引中的列

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

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

結(jié)論： 過濾條件要使用索引必須按照索引建立時的順序，依次滿足， 一旦跳過某個字段，索引后面的字段都無 法被使用。  

1.1.3  索引列上少計算

 不在索引列上做任何操作（計算、函數(shù)、（自動or手動）類型轉(zhuǎn)換），會導(dǎo)致索引失效而轉(zhuǎn)向全表掃描。

 所以字符串類型的數(shù)據(jù)，該加單引號的一定要加！

1.1.4  范圍之后全失效

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

索引列上不能有范圍查詢，少用>，<，between…and等結(jié)構(gòu)；范圍查詢的列忽略，索引失效，后面的索引列也跟著失效，不起作用。

 建議：將可能做范圍查詢的字段的索引順序放在最后

1.1.5  覆蓋索引不寫 *

即查詢列和索引列一致，不要寫 select *!， 盡量使用覆蓋索引（只訪問索引的查詢（索引列和查詢列一致））區(qū)別在于extra，索引的不同，速度不一樣

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

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

1.1.7 不用 is null 或者is not null 

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

1.1.8 LIKE 百分寫最右

1. 注意看模糊查詢的細節(jié)，只有xx%前綴查詢才不會失效

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

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

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

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

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

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

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

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

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

 1.2  案例分析 

1.3 建索引總結(jié)

1.對于單鍵索引，盡量選擇針對當(dāng)前query過濾性更好的索引。

2.在選擇組合索引的時候，當(dāng)前Query中過濾性最好的字段在索引字段順序中，位置越靠前越好。

3.在選擇組合索引的時候，盡量選擇可以能夠包含當(dāng)前query中的where字句中更多字段的索引。

4.盡可能通過分析統(tǒng)計信息和調(diào)整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;

通過執(zhí)行查看發(fā)現(xiàn)：此sql進行了全表查詢，而且在extra還出現(xiàn)了Using filesort等問題 。

2解決辦法：建立其復(fù)合索引

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

或者

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

 通過觀察發(fā)現(xiàn)： 掃描的范圍發(fā)生改變，變?yōu)閞ange，但是extra還是using filetext。

3.原因在于：

當(dāng)comments字段在聯(lián)合索引里處于中間位置時，因comments > 1條件是一個范圍值(所謂range)，MySQL無法利用索引再對后面的views部分進行檢索，即range類型查詢字段后面的索引無效。

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

到此 優(yōu)化完成！

2.2.2 原因結(jié)論

索引失效違反的規(guī)則為：

聯(lián)合索引中，范圍索引字段之后全失效。

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.假設(shè)在class 表中對card字段創(chuàng)建索引

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

 3.假設(shè)在book表中對card字段創(chuàng)建索引

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

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

2.2.2 結(jié)論

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

優(yōu)化關(guān)聯(lián)查詢時，只有在被驅(qū)動表上建立索引才有效！

索引兩表優(yōu)化，左連接右表建索引，右連接左表建索引

2.3 3表表分析

2.3.1 案例

現(xiàn)在再建一張新表：

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 結(jié)論

leftjoin時：永遠用小表結(jié)果集驅(qū)動大表結(jié)果集，保證join語句中被驅(qū)動的表的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. 直接關(guān)聯(lián)

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 結(jié)論

第一個查詢效率較高，且有優(yōu)化的余地。第二個案例中， 子查詢作為被驅(qū)動表，由于子查詢是虛表， 無法建立索引，因此不能優(yōu)化 1子查詢盡量不要放在被驅(qū)動表，有可能使用不到索引；

2left join時，盡量讓實體表作為被驅(qū)動表。

3.第3種情況，相對于前面2種，進行直接關(guān)聯(lián)。 能夠直接多表關(guān)聯(lián)的盡量直接關(guān)聯(lián)，不用子查詢。

2.5  inner join關(guān)聯(lián)

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 結(jié)論

兩個查詢字段調(diào)換順序，發(fā)現(xiàn)結(jié)果也是一樣的！ inner join 時，mysql 會自己幫你把小結(jié)果集的表選為驅(qū)動表。

2.6 子查詢優(yōu)化

2.6.1 情況描述

1.查詢所有不為掌門人的員工，按年齡分組！ select age as ' 年齡 ', count(*) as ' 人數(shù)' 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 ' 人數(shù) ' from emp e left join dept d on e.id=d.ceo where d.id is null group by age;

2.6.2 結(jié)論

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

2.7 小表驅(qū)動大表（in 或者exits的使用）

2.7.1 情況舉例

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

1.當(dāng)B表的數(shù)據(jù)集必須小于A表的數(shù)據(jù)集時，用in優(yōu)于exists。

等價于:

for select id from B

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

2.當(dāng)A表的數(shù)據(jù)集系小于B表的數(shù)據(jù)集時，用exists優(yōu)于in

等價于：

for select * from A

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

2.7.2  結(jié)論

1.小表驅(qū)動大表

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

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

2.8 order by 

2.8.1 案例說明

1.創(chuàng)建一張表

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

2.創(chuàng)建索引

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

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

2.索引的時候不按順序就會出現(xiàn)這樣的情況

 2.8.2 結(jié)論

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 * 影響排序速度。影響情況：

• 當(dāng)Query的字段大小總和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB類型時，會用改進后的算法——單路排序，否則用老算法——多路排序。
• 兩種算法的數(shù)據(jù)都有可能超出sort_buffer的容量，超出之后，會創(chuàng)建tmp文件進行合并排序，導(dǎo)致多次IO，但是用單路排序算法的風(fēng)險會更大一些，所以要提高sort_buffer__size。
• 嘗試提高sort_buffer_size，不管用哪種算法，提高這個參數(shù)都會提高效率，當(dāng)然，要根據(jù)系統(tǒng)的能力去提高，因為這個參數(shù)是針對每個進程的。
• 嘗試提高max_length_for_sort_data，提高這個參數(shù)，會增加用改進算法的概率。但是如果設(shè)的太高，數(shù)據(jù)總?cè)萘砍鰏ort_buffer_size的概率就增大，明顯癥狀是高的磁盤I/O活動和低的處理器使用率。

4.常見案例

2.9  group by

2.9.1 結(jié)論 

GroupBy優(yōu)化（和order by差不多）

group by實質(zhì)是先排序后進行分組，遵照索引建的最佳左前綴。

當(dāng)無法使用索引列，增大max_length_for_sort_data參數(shù)的設(shè)置 + 增大sort_buffer_size參數(shù)的設(shè)置。

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

group by 使用索引的原則幾乎跟 order by 一致 ，唯一區(qū)別是 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

 結(jié)論：可以看到是全表掃描

2.10.3  創(chuàng)建單列索引

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

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

2.10.4  創(chuàng)建復(fù)合索引

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

 查看：

優(yōu)化思路：

1.在emp表中對deptid字段創(chuàng)建索引；create index index_deptid on emp(deptid);

2.在 deprt表中對depatname，id兩個字段創(chuàng)建索引：create index idx_deptName_id on dept(deptName,id);

3.dept為驅(qū)動表，emp為被驅(qū)動表

2.12 綜合

1.首先創(chuàng)建表

 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.進行查詢

 -- 插入數(shù)據(jù)
insert into student values (1,'a','b');
 -- 按照first_name查找
 desc select first_name,last_name from student where first_name='a';

 結(jié)論：當(dāng)設(shè)置first_name為普通索引(單列索引)，按照first_name查詢；type:ref、possible_keys:indes_first、key:indes_first、extra:null，用到索引；

2.使用復(fù)合索引

-- 設(shè)置first_name,last_name復(fù)合索引
 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';

結(jié)論： 當(dāng)設(shè)置first_name，last_name為復(fù)合索引(聯(lián)合索引)，按照first_name查詢；type:ref、possible_keys:indes_name、key:indes_name、extra:Using index；type:ref用到索引，因為是復(fù)合索引不需要回表掃描，extra:Using index索引覆蓋；注意此時key_len為33=10*3(utf8)+2(變長)+1(null標志位)，用到了復(fù)合索引的一部分即first_name

3.使用復(fù)合索引

 結(jié)論：當(dāng)設(shè)置first_name，last_name為復(fù)合索引(聯(lián)合索引)，按照last_name查詢；type:index、possible_keys:null、key:indes_name、extra:Using where，Using index；type:index而不是ref，掃描索引樹，復(fù)合索引的最左原則；此時key_len為66=10*3(utf8)+2(變長)+1(null)+10*3(utf8)+2(變長)+1(null標志位)；Using where應(yīng)where子句進行限制

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

MySQL——執(zhí)行計劃 - ClassicalRain

總結(jié)

到此這篇關(guān)于利用explain排查分析慢sql的文章就介紹到這了,更多相關(guān)explain排查分析慢sql內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論