執(zhí)行計劃分析

什么是執(zhí)行計劃?

select * from? city where sname='張飛';

分析的是優(yōu)化器按照內(nèi)置的代價計算模型算法，

最終選擇后的執(zhí)行計劃。

cost ? 代價，成本

對于計算機來講，代價是什么?

io,cpu,mem?

查看執(zhí)行計劃?

mysql> explain select * from world.city;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

| id | select_type | table | partitions | type | possible_keys | key? | key_len | ref? | rows | filtered | Extra |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

|? 1 | SIMPLE? ? ? | city? | NULL? ? ? | ALL? | NULL? ? ? ? ? | NULL | NULL? ? | NULL | 4046 |? 100.00 | NULL? |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

1 row in set, 1 warning (0.00 sec)

執(zhí)行計劃顯示結(jié)果的分析

table? :此次查詢涉及到的表

type? :查詢類型：全表掃，索引掃

possible_keys :可能會用到的索引

key :最后選擇的索引

key_len :索引的覆蓋長度?

rows :此次查詢需要掃描的行數(shù)

Extra :額外的信息

詳細分析

table:此次分析所涉及到的表，多對查詢時，定位到有問題的表

type 查詢類型

全表掃描 ： 不會利用到所有的索引 ALL?

例如:select * from city;

select * from city? where sname = like '%y%';

select * from city? where sname not in? '張飛';

索引掃描 : index? < range < ref < eq_ref < const(system)

index :全索引掃描?

desc select contrycode from world ;contrycode有索引

range:索引范圍查詢: > < >= <= like in or between and?

desc select countrycode from world.city where id > 10;

desc select countrycode from world.city where countrycode? like 'F%';

desc select * from city where countrycode in ('USA','CHN');

特殊情況：查詢條件為主鍵是可以走range 范圍查詢

!= 和 not in? 會自動拆分為范圍查詢

ref :? 輔助索引的等值查詢?

desc select * from city where countrycode = ('USA');

eq_ref :多表鏈接中，非驅(qū)動表的鏈接條件是主鍵或唯一鍵.

a join b? on a.xx = b.yy??

desc select c.name,l.name from city c join country l

on c.`CountryCode` = l.`Code` where c.`Name`='Shanghai';

const(system):聚簇索引等值查詢。

desc select * from city where id=1;

possible_keys 可能用到的索引，所有和此次查詢有關(guān)的索引情況

key:當(dāng)次查詢選擇的索引

key_len :聯(lián)合索引的覆蓋長度

例如:index(a,b,c) 我們希望我們的查詢，對聯(lián)合索引，應(yīng)用的越充分越好,

key_len可以幫我判斷，此次查詢走了聯(lián)合索引的幾部分.

假設(shè)，某條查詢可以完全覆蓋三列聯(lián)合索引

select? * from t1 where a=? and b =? and c =?

ken_len = a長度 ? + b長度? + c長度?

長度受到 數(shù)據(jù)類型? 字符集 影響??

長度指的是，列的最大的字節(jié)存儲長度

數(shù)字:

? ? ? ? ?not null? ? ?沒有not null??

tinyint? 1字節(jié)? ? ?1+ 1?

int? 4字節(jié)? ? ? ? ? ?4+ 1?

bigint? 8字節(jié)? ? ? 8 + 1?

key_len:

a int not null --->4

a int? ? ?---->5

字符: utf8------->一個字符最大占3個字節(jié)

? ? ? ? ? ? ? ? ? ? ? ? not null? ? ? ?沒有not null

?char(10)? ? ? ? ? ? 3* 10? ? ? ? ? 3*10 +1?

varchar(10)? ? ? 3* 10 + 2(最多占用2個字節(jié)存儲字符的長度)? ? ? ? ? 3*10 + 2+ 1?

b char(10) not null 30?

b char(10)? ? ? ? ? ? ? ?31?

c varchar(10) not null 32?

c? varchar(10)? ? ?33??

字符: utf8mb4------->一個字符最大占4個字節(jié)

? ? ? ? ? ? ? ? ? ? ? ? not null? ? ? ?沒有not null

?char(10)? ? ? ? ? ? 4* 10? ? ? ? ? 4*10 +1?

varchar(10)? ? ? 4* 10 + 2(最多占用2個字節(jié)存儲字符的長度)? ? ? ? ? 4*10 + 2+ 1?

b char(10) not null 40?

b char(10)? ? ? ? ? ? ? ?41?

c varchar(10) not null 42?

c? varchar(10)? ? ?43??

create table t1 (

a int not null ,

b int,

c char(10) not null,

d varchar(10) ) engine = innodb charset=utf8mb4;

alter table t1 add index idx_abcd(a,b,c,d);

desc select a,b,c,d from t1 where a= 1 and b = 3 and c = 'd' and d = '4';

1 SIMPLE t1 NULL ref idx_abcd idx_abcd 92 const,const,const,const 1 100.00 Using index

Extra?

using filesort:表示此次查詢使用到了文件排序操作，

說明此次查詢的排序操作 order by group by distinct ...（可能有這些語句）

列子;

?desc select * from city? where countrycode='CHN'? order by? ?population? ;

可以看到，extra出現(xiàn)了using filesort ，這時我們通過添加where條件字段和population字段的聯(lián)合索引看是否能解決此問題；

alter table city add index idx_c_p(countrycode,population);

?desc select * from city? where countrycode='CHN'? order by? ?population? ;

可以看到此問題已經(jīng)解決。

索引應(yīng)用規(guī)范

建立索引的原則（dba運維規(guī)范）

1.建表時一定要有主鍵，一般是個無關(guān)列

2.選擇唯一性索引

3.在聯(lián)合索引中，讓唯一索引（重復(fù)值較少的列）作為最左邊的鍵，可以更快速的來查詢。

4.如果字段過長最好使用前綴索引.

5.索引數(shù)據(jù)不是更多更好

可能產(chǎn)生的問題：

1).磁盤空間增大

2).修改表時，對索引的重構(gòu)和更新和麻煩，越多的索引，會使更新表的時間變慢

3).優(yōu)化器的負擔(dān)很重，會影響優(yōu)化器的選擇

6.刪除不使用的或者很少使用的索引

(percona toolkit).py-duplicate-key-checker? 工具可以查詢到重復(fù)的索引

7.大表加索引，要在業(yè)務(wù)不繁忙的期間操作（8.0以前要鎖表）

8.盡量少在經(jīng)常更新值的列上加索引(update的列，這樣容易索引失效)

9.建索引的原則：

1）必須要有主鍵，如果沒有可以作為主鍵條件的列，創(chuàng)建無關(guān)列。

2）經(jīng)常做為where條件列，order by group by join distinct的條件（業(yè)務(wù)：產(chǎn)品功能+用戶行為）

3)最好使用唯一值多的列作為索引條件，如果索引列重復(fù)值較多，可以考慮使用聯(lián)合索引。

4）列值長度較長的索引，我們建議使用前綴索引

5）降低索引條目，一方面不要創(chuàng)建沒有用的索引，不常使用的索引清理，percona toolkit的工具.

6)索引維護要避開業(yè)務(wù)繁忙期

不走索引的情況(開發(fā)規(guī)范）

1 沒有查詢條件，或者查詢條件沒有建立索引

select * from tab; 全表掃描

select * from tab where 1=1 ;

在業(yè)務(wù)庫中，特別是數(shù)據(jù)量比較大的表，

是沒有全表掃描的需求。

對用戶查看是非常痛苦的。

對服務(wù)器來講是毀滅性的。

2.查詢結(jié)果集是原表中的大部分數(shù)據(jù)，應(yīng)該是15-30%,

查詢的結(jié)果集，超過了總行數(shù)的25%，優(yōu)化器就覺得沒有必要

走索引。

原因是？？？ 跟數(shù)據(jù)庫的預(yù)讀能力有關(guān)，以及一些參數(shù)有關(guān)。彩蛋？？

解決辦法，如果業(yè)務(wù)允許，進行分批次查詢，或者進行分頁查詢。

limit 查詢。

如果不允許，盡量不要把這部分數(shù)據(jù)存儲在mysql，可以放到redis這種類似的

內(nèi)存數(shù)據(jù)庫中.

3。索引本身失效，統(tǒng)計數(shù)據(jù)不真實.

索引和表都有自我維護的能力。

對于表內(nèi)容的變化比較頻繁的情況下，統(tǒng)計信息不準(zhǔn)確，過舊，有可能出現(xiàn)索引失效的情況。

解決辦法：重建索引.

現(xiàn)象：有一條sql語句平時查詢很快，突然很慢，會是什么原因。

表跟新頻繁，統(tǒng)計信息過舊，導(dǎo)致索引失效.

查看表和索引的統(tǒng)計信息:

select * from innodb_table_stats; 表的統(tǒng)計信息

select * from innodb_index_stats; 索引的統(tǒng)計信息

不是實時跟新。

表的實時跟新解決辦法:多刪除點才可以跟新。

所以解決辦法是，重建索引。

4.如果函數(shù)作用在索引上，或者對索引列進行運算，運算包括(+,-,* ,/,!等)

5.隱式轉(zhuǎn)換導(dǎo)致索引失效，這一點應(yīng)當(dāng)引起重視

這里我測試字符串轉(zhuǎn)數(shù)字不會失效，只有數(shù)字轉(zhuǎn)字符串就會失效。

6.<>,not in? 不走索引（輔助索引）(聚簇會走)

單獨的> < in 又可能走索引，也可能不走.

7.like '%_' 百分號在前面不走索引。

如有這種大量的需求可以用mongodb數(shù)據(jù)庫

擴展：優(yōu)化器針對索引的算法

1mysql索引的自優(yōu)化-AHI（索引頁的索引,自適應(yīng)hash索引）

mysql的innodb引擎，能夠創(chuàng)建的只有btree。

ahi作用：自動評估熱的內(nèi)存索引page，生成一個hash索引表，加速

索引讀取的速度。

2.mysql索引的自優(yōu)化 -Change buffer?

insert update delete? 數(shù)據(jù)

對于聚簇索引會立即更新

對于輔助索引，不是實時更新

在innodb內(nèi)存結(jié)構(gòu)中，加入了insert buffer（會話）,現(xiàn)版本叫change buffer

change buffer的功能是臨時緩沖輔助索引需要的數(shù)據(jù)更新。

當(dāng)我們需要查詢新insert 的數(shù)據(jù)，會在內(nèi)存中進行mege 合并，此時輔助索引就是最新的。

3 ICP 索引下推?

在引擎層再次根據(jù)索引進行過濾，減少磁盤的io。

例如 index(a,b,c) select * from t1 where a= and c=?

?在sql層只走部分索引a，到引擎層再次走索引過濾掉部分c的數(shù)據(jù)，

在進行磁盤數(shù)據(jù)讀取，減少了磁盤的io.

所涉及的算法調(diào)優(yōu)的參數(shù):

?select@@optimizer_switch;

|index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on |

如何修改算法參數(shù)？

1）.my.cnf?

2）.set global??optimizer_switch='batched_key_access=on'

3）.hints 在語句里加入? /*+? ?*/? 也就是oracle里面的hint?

4.MRR? muti range read?

mrr = on? mrr_cost_based = on(開啟mrr需要關(guān)閉 mrr_cost_based)

set global??optimizer_switch='mrr_cost_based=off';

默認的輔助索引查詢，會多次回表，產(chǎn)生隨機io。

mrr 緩存一部輔助索引的id，然后進行排序，利用rowid buffer，然后進行一次或者

少次數(shù)的回表，減少回表次數(shù)以及隨機IO；提高服務(wù)器的性能。

輔助索引 回表 >> 聚簇索引?

轉(zhuǎn)換為

輔助索引 > sort id 回表? >聚簇索引?

5.SNLJ? 多表關(guān)聯(lián)優(yōu)化的算法?

a join b on a.xx = b.y y where a.c = b.c?

for each row? in b {

for each row in b{

?a.xx = b.xx send to? client?}

以上例子，可以通過left join 強制驅(qū)動表

6.BNLJ? 多表關(guān)聯(lián)優(yōu)化算法?

a join b on a.xx = b.y y where a.c = b.c

將a表滿足條件的數(shù)據(jù)一次性（使用塊）跟b表比對，并返回，減少了

循環(huán)的次數(shù)。提高了效率。a為驅(qū)動表.

when

7 BKA?

BNLJ的基礎(chǔ)上，也就是把a表的數(shù)據(jù)放到一個塊中，然后在進行排序，然后在跟

b表進行關(guān)聯(lián)，可以減少io次數(shù)，和隨機io次數(shù)。優(yōu)化非驅(qū)動表的關(guān)聯(lián)輔助索引。

開啟的方式? 開啟：MRR ，在開啟BKA。