優(yōu)化實例

創(chuàng)建一個表格

create table test03(
  a1 int(4) not null,
  a2 int(4) not null,
  a3 int(4) not null,
  a4 int(4) not null

);
alter table test03 add index idx_a1_a2_a3_a4(a1,a2,a3,a4);

寫語句

explain select a1,a2,a3,a4 from test03 where a1=1 and a2=2 and a3=3 and a4=2;

理想情況下

索引和執(zhí)行的順序一樣

where 和符合索引的順序一樣抵卫。

explain select a1,a2,a3,a4 from test03 where a4=1 and a3=2 and a2=3 and a1=2;

不推薦缠诅！運氣好而已

sql優(yōu)化

有一個SQL優(yōu)化器幫你改完之后二者是等價的

//4是無效查詢，所以要using where
//a4需要回表查詢
explain select a1,a2,a3,a4 from test03 where a1=1 and a2=2 and a4=3  order by a3;

沒有filesort

拼起來還是有序的1 2 3


explain select a1,a2,a3,a4 from test03 where a1=1 and a4=3  order by a3;

出現(xiàn)了跨列使用

拼起來不是1 2 3

//理論上不會的
explain select a1,a2,a3,a4 from test03 where a1=1 and a4=3  order by a2, a3;

拼起來了1 2 3

where 和order by

where后面要和索引順序一致，不一致的話，后面部分會失效，用orderby語句和列平起來后還是順序的也是可以的

案例

單表優(yōu)化

1 創(chuàng)建一個表格

create table book(
bid int(4) primary key,
name varchar(20) not null,
authorid int(4) not null,
publicid int(4) not null,
typeid int(4) not null
);
insert into book values(1,'java',1,1,2);
insert into book values(2,'javaScrip',2,3,4);
insert into book values(3,'wx',3,5,7);
insert into book values(4,'C++',4,6,1);
commit;

查詢authorid=1typeid為2或3的bid

explain select bid from book where typeid in(2,3) and authorid=1 order by typeid desc;

待優(yōu)化語句

type=all extra是using filesort

優(yōu)化：加索引

alter table book add index idx_bta(bid,typeid,authorid);

優(yōu)化了一點

根據(jù)sql實際解析的順序航夺，調(diào)整索引的順序

--索引一旦廢棄優(yōu)化，要刪除舊的索引
drop index idx_bta on book;
--雖然可以回表崔涂，但是加入可以使用using index阳掐；
alter table book add index idx_atb(authorid,typeid,bid);

再次優(yōu)化（之前是index）

//范圍查詢in有時候會失效，所以可以調(diào)整范圍查詢在后面
explain select bid from book where authorid=1 and typeid in(2,3) order by typeid desc;

優(yōu)化結(jié)果

--小結(jié)

最佳做前綴冷蚂，索引不能跨列使用缭保，保持索引的定義和使用順序一致
索引需要多部優(yōu)化
將in查詢放到where條件的最后，防止失效
authroid在索引里面蝙茶，不需要回原表艺骂，但是in會使得typeid失效

多表優(yōu)化

創(chuàng)建表格

create table teacher2(
tid int(4) primary key,
cid int(4) not null
);
insert into teacher2 values(1,2);
insert into teacher2 values(2,1);
insert into teacher2 values(3,3);

create table course2(
cid int(4) primary key,
cname varchar(20)
);
insert into course2 values(1,'java');
insert into course2 values(2,'python');
insert into course2 values(3,'cotlin');
commit;

兩表關聯(lián)左連接

select * from teacher2 t left outer join course2 c on t.cid=c.cid where c.cname='java';

小表：10
大表：300
for(int i=0;i<小表.length;i++){
for(int j=0;j<大表.length;j++){
        。隆夯。钳恕。
}
}

for(int i=0;i<大表.length;i++){
for(int j=0;j<小表.length;j++){
        。蹄衷。忧额。
}
}
一般建議外層循環(huán)應該小，內(nèi)存循環(huán)應該大

想加索引where 小表.x = 大表.x;

alter table teacher2 add index index_teacher2_cid(cid);
alter table course2 add index index_course2_cname(cname);

左表加上索引愧口，cname也加上索引

建議

當編寫on t.cid =c.cid的時候宙址，應該是將數(shù)據(jù)量小的表放在左邊
索引應該放在經(jīng)常使用的字段上，因此將x字段加上索引调卑，一般是放在左邊的表加索引

三表優(yōu)化

a. 小表驅(qū)動大表
b. 索引建立在索引經(jīng)常查詢的字段上

避免索引失效的一些原則

符合索引的時候，不要跨列或無序使用

 index:(a,b,c)
where a... and c... order by c

盡量使用全索引匹配

//所有索引都要用
 index:(a,b,c)
where a... and c... order by c
如果a失效了b和c失效

不要再索引上進行任何操作（會失效）
計算大咱，函數(shù)恬涧，類型轉(zhuǎn)換，否則索引失效

select ... where A.x =
//不要
select ... where 3*A.x =

select * from book where authroid=1 and typeid=2;

索引進行操作了碴巾，失效Ｋ堇Α！厦瓢！

復合索引不能使用提揍！= 或者is null，否則自身和右側(cè)索引全部失效
實際使用有沒有SQL優(yōu)化煮仇，但是實際上可能只有一個劳跃，優(yōu)化是一個概率層面的。

體驗一下概率事件

sql優(yōu)化器是一個概率上的事件

有大于號可能也會失效
大部分情況都適用浙垫，但是sql優(yōu)化器等原因刨仑，所以不是百分百的確定郑诺，范圍查詢之后的索引失效（> < in）
補救。盡量使用索引覆蓋 using index杉武，這個條件永遠成立辙诞。
like盡量用常量開頭，不要用%開頭轻抱，否則索引失效飞涂。如果必須使用模糊查詢，可以使用索引覆蓋進行挽救

explain select tname from teacher where tname like '%x%';

索引覆蓋

盡量不要使用類型轉(zhuǎn)換
程序底層可能會將123 -> '123'祈搜，索引失效
盡量不要使用or较店，否則失效

使用or索引失效

具體的優(yōu)化方法

exists和in
如果主查詢的數(shù)據(jù)很大，使用in
如果子查詢的數(shù)據(jù)很大夭问，使用exist泽西，校驗是否有數(shù)據(jù)

(前面大用)
select * from teacher where tname in (select id from teacher);
（后面大用exists）
select tname from teacher where exists (select * from teacher);

order by優(yōu)化

經(jīng)常看到using filesort缰趋，有兩種算法：雙路排序捧杉、單路排序
之前的是雙路排序
（讀取磁盤數(shù)據(jù)，進行排序buffer上）

雙路排序

（只用讀取一次秘血，將所有字段在buffer上面進行排序）
默認的是單路排序
如果數(shù)據(jù)量太大味抖，會進行分片讀取
注意：單路排序非常消耗buffer，如果數(shù)據(jù)量太大灰粮，可以調(diào)大buffer的大小

set max_length_for_sort_data=1024;

如果buffer太低仔涩，就會使得單路變成雙路

選擇單路，調(diào)整buffer大小
select a粘舟，b from 不要用*
復合索引熔脂，不要跨列使用
保證排序的一致性（都是升序或者降序）

sql排序-慢查詢?nèi)罩荆簃ysql提供的日志記錄，用戶記錄響應超過閾值的sql語句柑肴，慢查詢?nèi)罩鞠既啵J是關閉的，開發(fā)的時候可以打開

show variables like '%slow_query_log%';

開啟晰骑，臨時開啟：

set global slow_query_log = 1;

SQL2