一 概述

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

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

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

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

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

全值匹配我最愛(ài)， 最佳左前綴法則 ；

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

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

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

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

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

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

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

下進(jìn)行的操作，如下:

1.1.1 全值匹配我最愛(ài)

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

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

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

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

 前面兩個(gè)sql索引失效的原因：

即使跳過(guò)了中間的索引，但是其長(zhǎng)度沒(méi)變化，跟第一個(gè)sql只使用name的索引的長(zhǎng)度一樣，那就說(shuō)明第二個(gè)sql值使用了部分索引，只使用了name的索引，后面的age，pos失效。不然的話長(zhǎng)度肯定大于74。

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

1.1.3  索引列上少計(jì)算

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

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

1.1.4  范圍之后全失效

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

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

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

1.1.5  覆蓋索引不寫 *

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

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

mysql 在使用不等于 (!= 或者 <>) 時(shí)，有時(shí)會(huì)無(wú)法使用索引會(huì)導(dǎo)致全表掃描。這個(gè)得知道這種情況，根據(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. 注意看模糊查詢的細(xì)節(jié)，只有xx%前綴查詢才不會(huì)失效

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

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

如果使用下面這些，都是使用的覆蓋索引，結(jié)果都是一樣的， 注意id之所以沒(méi)加索引，但是可以加入使用不會(huì)失效，是因?yàn)樗侵麈I

但是但是如果加入了沒(méi)有主鍵又不是索引的東西，%xx%就會(huì)失效

1.1.9 字符串不加單引號(hào)索引失效

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

1.1.10  少用or，用它來(lái)連接時(shí)會(huì)索引失效。

少用or，用它來(lái)連接時(shí)會(huì)索引失效。

用使用 union all 或者 union 來(lái)替代：

 1.2  案例分析 

1.3 建索引總結(jié)

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

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

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

4.盡可能通過(guò)分析統(tǒng)計(jì)信息和調(diào)整query的寫法來(lái)達(dá)到選擇合適索引的目的。

二 案例分析

2.1 單表分析

2.2.1 分析過(guò)程

1.sql語(yǔ)句

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

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

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

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

3.原因在于：

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

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

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

2.2.2 原因結(jié)論

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

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

2.2 兩表表分析

2.2.1 案例準(zhǔn)備

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 表中對(duì)card字段創(chuàng)建索引

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

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

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

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

2.2.2 結(jié)論

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

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

索引兩表優(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;

添加必要的索引

通過(guò)后兩張表

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

 2.3.2 結(jié)論

leftjoin時(shí)：永遠(yuǎn)用小表結(jié)果集驅(qū)動(dòng)大表結(jié)果集，保證join語(yǔ)句中被驅(qū)動(dòng)的表的join條件的字段添加了索引

2.4 4表表分析  

2.4.1 實(shí)驗(yàn)比較

1.虛表作為左表，實(shí)表作為右表

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.實(shí)體表作為左表，虛表作為實(shí)體表

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é)論

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

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

3.第3種情況，相對(duì)于前面2種，進(jìn)行直接關(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é)論

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

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表是全表掃描。沒(méi)有使用上索引

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é)論

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

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

2.7.1 情況舉例

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

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

等價(jià)于:

for select id from B

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

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

等價(jià)于：

for select * from A

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

2.7.2  結(jié)論

1.小表驅(qū)動(dòng)大表

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

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

2.8 order by 

2.8.1 案例說(shuō)明

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.如果索引的時(shí)候也是按照順序

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

 2.8.2 結(jié)論

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

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

     FileSort方式效率較低。

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

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

沒(méi)有where條件過(guò)濾的情況，ORDER BY語(yǔ)句使用索引最左前綴原則。

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

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

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

4.常見(jiàn)案例

2.9  group by

2.9.1 結(jié)論 

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

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

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

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

group by 使用索引的原則幾乎跟 order by 一致 ，唯一區(qū)別是 groupby 即使沒(méi)有過(guò)濾條件用到索引，也可以直接使用索引

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é)論：使用到了索引，且索引級(jí)別為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：使用到了索引，但是沒(méi)有用到覆蓋索引，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 條件過(guò)濾

     #中間兄弟不能斷
             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,' 老鳥(niǎo) ',' 菜鳥(niǎo) ') from dept d inner join emp e
on d.id=e.deptid
group by d.deptName,d.id

 查看：

優(yōu)化思路：

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

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

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

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.進(jìn)行查詢

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

 結(jié)論：當(dāng)設(shè)置first_name為普通索引(單列索引)，按照f(shuō)irst_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);
-- 按照f(shuō)irst_name查找
 desc select first_name,last_name from student where first_name='a';

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

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

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

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

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

總結(jié)

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

最新評(píng)論