MySQL基礎(chǔ)優(yōu)化-索引及執(zhí)行計劃

1.什么是索引？
相當(dāng)于一本書的目錄继控。優(yōu)化查詢
2.MySQL 支持的索引類型（算法）

Btree : 平衡多叉樹
Rtree : 空間樹索引
Hash  : HASH索引
fulltext: 全文索引

3.數(shù)據(jù)查找算法介紹

二叉樹
紅黑樹
Btree: Blance Tree

BTree 的查找算法（見圖）

B-Tree：每次查詢都從根節(jié)點開始砌些，不能從枝節(jié)點或者葉子節(jié)點之間直接轉(zhuǎn)換
B+Tree：實際上是在枝節(jié)點上添加了雙向指針信息吊输，從而減少對根節(jié)點的IO消耗
B*tree  ：實際上是在非根節(jié)點上添加了雙向指針信息环鲤，從而減少對根節(jié)點和枝節(jié)點的IO消耗

B6E99DF40DF7A76F6F1DA9947B62E5AF.jpg

MySQL Btree 索引的應(yīng)用
5.1聚簇索引（聚集索引等脂、主鍵索引）
前提:
0>InnoDB存儲引擎的表才會有聚簇索引
1>有主鍵裁奇，主鍵就是聚簇索引
2>沒有主鍵桐猬，選擇唯一鍵作為聚簇索引
3>生成一個隱藏列（DB_ROW_ID,6字節(jié)），作為聚簇索引

作用：
1. 聚簇（區(qū)）索引刽肠，組織表（IOT）：所有數(shù)據(jù)在插入時溃肪，都按照ID（主鍵）屬性，在相鄰數(shù)據(jù)頁上有序存儲數(shù)據(jù)音五。
2. 加快存儲數(shù)據(jù)惫撰，加快通過索引作為查找條件的查詢。

參考：

    https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html

聚簇索引構(gòu)建過程（見圖）

1.png

葉子節(jié)點：
由于存儲數(shù)據(jù)時躺涝，已經(jīng)按照ID順序在各個數(shù)據(jù)頁中有序存儲了厨钻，所以《原表數(shù)據(jù)》所在數(shù)據(jù)頁被作為葉子節(jié)點。
內(nèi)部節(jié)點（非葉子節(jié)點--->枝節(jié)點）：
獲取葉子節(jié)點ID范圍+指針坚嗜。
根節(jié)點：
獲取非葉子節(jié)點 ID范圍+指針

5.2輔助索引
構(gòu)建過程：
1. 葉子節(jié)點構(gòu)建：
提取索引列值+ID 夯膀，進(jìn)行從小到大排序（輔助索引列值），存儲到各個數(shù)據(jù)頁苍蔬，作為葉子節(jié)點诱建。
2. 非葉子節(jié)點（internel node ）
提取下層的輔助索引列值范圍+指針。
3. 根節(jié)點：
提取下層節(jié)點的范圍+指針碟绑。
對于查詢的優(yōu)化：

    1. 通過輔助索引列俺猿，進(jìn)行條件查詢，根據(jù)輔助索引BTREE快速鎖定條件值對應(yīng)的ID格仲。
    2. 通過得出的ID值押袍，回到聚簇索引繼續(xù)查詢到具體的數(shù)據(jù)行（回表）。

2.png

輔助索引分類
6.1. 普通單列
6.2. 聯(lián)合索引
idx(a,b)提取出來再排序抓狭，先按a列排伯病，如果a列用相同的多個值的話，就按照b列的值進(jìn)行排列（先拿最左列排）
葉子節(jié)點：
id+a+b ,按照a和b進(jìn)行排序否过，生成葉子節(jié)點
枝節(jié)點和根節(jié)點：
只會包含最左列（a列）的范圍+指針（最左原則）
注意：最左原則
1. 建索引午笛，最左列重復(fù)值少的。
2. 查詢條件中苗桂，必須包含最左列药磺。
6.3. 唯一索引
unique key

6.4. 前綴索引
idex(test(10))

索引樹高度影響因素
7.1 列值長度
前綴索引。
7.2 數(shù)據(jù)量
分區(qū)表煤伟。
定期歸檔表癌佩。
分布式架構(gòu)：分庫木缝、分表。
7.3 數(shù)據(jù)類型
定長：char(20)
變長：varchar(20)

回表問題的探討围辙？
什么是回表我碟？
輔助索引查找完成----> 聚簇索引查詢過程。

回表會帶來的問題姚建？
IO增多：量矫俺、次數(shù)

如何減少回表？
使用唯一值索引查詢
聯(lián)合索引
覆蓋索引：輔助索引完全覆蓋到查詢結(jié)果

索引應(yīng)用
9.1 壓測

mysql> source /root/t100w.sql    #上傳數(shù)據(jù)
mysql> grant all on *.* to root@'10.0.0.%' identified by '123';    #創(chuàng)建一個遠(yuǎn)程登陸用戶

進(jìn)行壓測

shell> mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='780P'" engine=innodb --number-of-queries=2000 -uroot -p123 -h10.0.0.51  -verbose

--concurrency=100  ：  模擬同時100會話連接
--create-schema='test' ： 操作的庫是誰
--query="select * from test.t100w where k2='780P'"  ：做了什么操作
--number-of-queries=2000 ： 一共做了多少次查詢

Average number of seconds to run all queries: 719.431 seconds
Minimum number of seconds to run all queries: 719.431 seconds
Maximum number of seconds to run all queries: 719.431 seconds

9.2 查詢表的索引

#查看表索引的三種方法
desc t100w;  
-----
 Key 
-----
PK     --> 主鍵（聚簇索引）     
MUL    --> 輔助索引   
UK     --> 唯一索引  
     
mysql> show index from t100w;
mysql> show creat table city;

9.3 創(chuàng)建索引
9.3.1 單列輔助索引

select * from test.t100w where k2='780P'
優(yōu)化方式： 
alter table 表名 add index 索引名(列名);   
alter table t100w add index idx_k2(k2);

9.3.2 聯(lián)合索引創(chuàng)建

mysql> alter table t100w add index idx_k1_num(k1,num);

創(chuàng)建聯(lián)合索引的時候要根據(jù)最左原則進(jìn)行創(chuàng)建
對比k2 和 k1 的重復(fù)值掸冤，少的放在最所測厘托，優(yōu)化效果更佳

select count(distinct k1) from t100w;
select count(distinct k2) from t100w;

9.3.3 前綴索引創(chuàng)建

#判斷前綴長度多少合適：
select count(distinct(left(name,5)))  from city ;
select count(distinct name)  from city ;
#創(chuàng)建前綴索引
mysql> alter table city add index idx_n(name(5));

9.4 刪除索引

#語法：alter tabel 表名  drop index 索引名；
alter table city drop index idx_n;

執(zhí)行計劃查看和分析
10.1 什么是執(zhí)行計劃?
優(yōu)化器優(yōu)化后的“執(zhí)行方案”稿湿。

10.2 作用铅匹？
a. 語句執(zhí)行之前，通過執(zhí)行計劃饺藤，防患于未然包斑。
b. 對于有性能問題的語句，進(jìn)行分析策精。得出優(yōu)化方案舰始。

10.3 獲取SQL的執(zhí)行計劃。
Select 咽袜、 update 、 delete

#查看執(zhí)行計劃的兩種方法
mysql> explain select * from test.t100w where num=279106  and k2='VWtu';
mysql> desc select * from test.t100w where num=279106  and k2='VWtu';
+----+-------------+-------+------------+------+----------------+------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys  | key  | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------+------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | t100w | NULL       | ref  | ix_k2,idx,idx1 | idx1 | 22      | const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+----------------+------+---------+-------------+------+----------+-------+

10.4 執(zhí)行計劃介紹

table          ： 操作的表
type           ： 查詢索引的類型（ALL枕稀、index询刹、range、ref 萎坷、eq_ref凹联、const(system)）
possible_keys  :  可能會走的。
key            ： 最終選擇的索引哆档。
key_len        ： 聯(lián)合索引覆蓋長度蔽挠。
rows           ： 此次查詢需要掃描的行數(shù)(預(yù)估值)。
Extra          ： 額外信息瓜浸。

10.5 type 詳解
10.5.1 ALL 全表掃描
a. 查詢條件沒有建索引

mysql> desc t100w;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id    | int(11)   | YES  |     | NULL              |                             |
| num   | int(11)   | YES  |     | NULL              |                             |
| k1    | char(2)   | YES  |     | NULL              |                             |
| k2    | char(4)   | YES  |     | NULL              |                             |
| dt    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+

5 rows in set (0.00 sec)

mysql> desc select * from t100w where k2='780P';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t100w | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 986679 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

b. 有索引澳淑，但查詢語句不能走的情況。

mysql> alter table t100w add index idx(k2);
mysql> desc select * from t100w where k2 like '%80P';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t100w | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 986679 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+

mysql> desc select * from t100w where k2 != '780P';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t100w | NULL       | ALL  | idx           | NULL | NULL    | NULL | 986679 |    73.37 | Using where |


mysql> desc select * from t100w where k2 not in ('780P');
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t100w | NULL       | ALL  | idx           | NULL | NULL    | NULL | 986679 |    73.37 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+

10.5.2 index 全索引掃描

mysql> desc select k2 from t100w;
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t100w | NULL       | index | NULL          | idx  | 17      | NULL | 986679 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-------------+

10.5.3 range 索引范圍掃描

mysql> desc select * from city where id<100;
mysql> desc select  * from city where countrycode like 'CH%';
mysql> desc select  * from city where countrycode='CHN' or countrycode='USA';
mysql> desc select  * from world.city where countrycode in ('CHN','USA');

union all 改寫:

desc 
select  * from city where countrycode='CHN' union all  select  * from city where countrycode='USA';

注意： 
    如果重復(fù)值過多的話插佛，可能改寫的效果不佳杠巡。

通過壓測： 判斷改寫效果。

 mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='world' --query="select  * from world.city where countrycode in ('CHN','USA'); " engine=innodb --number-of-queries=2000  -uroot -p123 -h10.0.0.51  -verbose

 mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='world' --query="select  * from city where countrycode='CHN' union all  select  * from city where countrycode='USA'; " engine=innodb --number-of-queries=2000  -uroot -p123 -h10.0.0.51  -verbose

10.5.4 ref : 輔助索引等值查詢

mysql> desc select  * from city where countrycode='CHN';

10.5.5 eq_ref: 多表連接
非驅(qū)動表的連接條件是主鍵或唯一鍵雇寇。是多表連接中性能最好的查詢方法氢拥。
拿結(jié)果集小的作為i驅(qū)動表

mysql> desc select a.name,b.name from city as a  join  country as b on a.countrycode=b.code where a.population<100;
+----+-------------+-------+------------+--------+-----------------+---------+---------+---------------------+------+----------+-----------------------+
| id | select_type | table | partitions | type   | possible_keys   | key     | key_len | ref                 | rows | filtered | Extra                 |
+----+-------------+-------+------------+--------+-----------------+---------+---------+---------------------+------+----------+-----------------------+
|  1 | SIMPLE      | a     | NULL       | range  | CountryCode,idx | idx     | 4       | NULL                |    1 |   100.00 | Using index condition |
|  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY         | PRIMARY | 3       | world.a.CountryCode |    1 |   100.00 | NULL                  |
+----+-------------+--

#left join 強制左表為驅(qū)動表
mysql> desc select a.name,b.name from city as a  left join  country as b on a.countrycode=b.code;
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                 | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------+
|  1 | SIMPLE      | a     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                | 4188 |   100.00 | NULL  |
|  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 3       | world.a.CountryCode |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

10.5.6 const(system)
主鍵或唯一鍵等值查詢

10.6 key_len 說明
10.6.0 計算方式
a. 介紹：
索引的應(yīng)用長度
b. 作用：
判斷聯(lián)合索引的覆蓋長度蚌铜。
idx(a,b,c)

c. 如何計算key_len
總長度的計算：
a+b+c

d. 每個索引列占用多長？
每個列key_len,是這個列的《最大》預(yù)留長度嫩海。
影響因素：
1. 數(shù)據(jù)類型
2. not null
3. 字符集(字符串類型)

3.png

計算

create table test (
id int  not null primary key auto_increment,
a int not null ,
b char(10) not null ,
c char(5) ,
d varchar(20) not null ,
e varchar(10)
)engine=innodb charset=utf8mb4;

alter table test add index idx(a,b,c,d,e);


4+40+21+82+43=190

計算方法

mysql> desc select * from test where a=1 ;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx           | idx  | 4       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> desc select * from test where a=1 and b='aa';
+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx           | idx  | 44      | const,const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> desc select * from test where a=1 and b='aa' and c='aa' ;
+----+-------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref               | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx           | idx  | 65      | const,const,const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> desc select * from test where a=1 and b='aa' and c='aa' and d='aa' ;
+----+-------------+-------+------------+------+---------------+------+---------+-------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref                     | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------------------------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx           | idx  | 147     | const,const,const,const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> desc select * from test where a=1 and b='aa' and c='aa' and d='aa' and  e='aa';
+----+-------------+-------+------------+------+---------------+------+---------+-------------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref                           | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------------------------------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx           | idx  | 190     | const,const,const,const,const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------------------------------+------+----------+-------------+

10.6.1 聯(lián)合索引應(yīng)用細(xì)節(jié)

a. 聯(lián)合索引全覆蓋
idx(num,k1,k2) 索引相當(dāng)于index(num,k1)冬殃、index(num)

mysql> desc select * from t100w  where num=641631  and k1='At'  and k2='rsEF';
mysql> desc select * from t100w  where k1='At'  and   num=641631  and  k2='rsEF';
mysql> desc select * from t100w  where k1='At'  and   num=641631  and  k2 like 'rsE%';

b. 部分覆蓋

mysql> desc select * from t100w  where num=641631  and k1='At' ;
mysql> desc select * from t100w  where   k1='At'  and num=641631 ;
#中間缺了k1所以只走num,后面就不能走索引了k2列走不到索引
mysql> desc select * from t100w  where  num=641631  and  k2 like 'rsE%';
mysql> desc select * from t100w  where num=641631  and k1 > 'AZ'  and k2='rsEF';
mysql> desc select * from t100w  where num=641631  and k1 != 'AZ'  and k2='rsEF';

c. 完全不覆蓋

#索引遵循最左原則的，這里沒有num列叁怪，所以不會走索引的
mysql> desc select * from t100w  where k1='At'  and     k2 like 'rsE%';

d. 在多子句必須得使用聯(lián)合索引

where a   order by  b 
where a   group by  b   order by   xxx

10.7 Extra
using where : 此次查詢中有部分條件是沒有走索引的造壮。
如果出現(xiàn)以上信息，說明where 條件骂束，索引設(shè)計問題或者語句有問題耳璧。
using filesort : 出現(xiàn)文件排序，order by 展箱、 group by 旨枯、 distinct ...

索引應(yīng)用規(guī)范總結(jié)
11.1 建立索引的原則（DBA運維規(guī)范）

(1) 必須要有主鍵,無關(guān)列。
(2) 經(jīng)常做為where條件列  order by  group by  join on, distinct 的條件(業(yè)務(wù):產(chǎn)品功能+用戶行為)
(3) 最好使用唯一值多的列作為索引,如果索引列重復(fù)值較多,可以考慮使用聯(lián)合索引
(4) 列值長度較長的索引列,我們建議使用前綴索引.
(5) 降低索引條目,一方面不要創(chuàng)建沒用索引,不常使用的索引清理,percona toolkit(xxxxx)
(6) 索引維護(hù)要避開業(yè)務(wù)繁忙期混驰，建議用pt-osc
(7) 聯(lián)合索引最左原則

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

11.2.1 沒有查詢條件攀隔，或者查詢條件沒有建立索引
select * from t1 ;
select * from t1 where id=1001 or 1=1;

11.2.2 查詢結(jié)果集是原表中的大部分?jǐn)?shù)據(jù)，應(yīng)該是15-25%以上栖榨。
查詢的結(jié)果集昆汹，超過了總數(shù)行數(shù)25%，優(yōu)化器覺得就沒有必要走索引了婴栽。
MySQL的預(yù)讀功能有關(guān)满粗。

可以通過精確查找范圍，達(dá)到優(yōu)化的效果愚争。
1000000

大于 > 500000 and

11.2.3 索引本身失效映皆，統(tǒng)計信息不真實（過舊）
索引有自我維護(hù)的能力。
對于表內(nèi)容變化比較頻繁的情況下轰枝，有可能會出現(xiàn)索引失效捅彻。
一般是刪除重建

現(xiàn)象:

有一條select語句平常查詢時很快,突然有一天很慢,會是什么原因
select?  --->索引失效,統(tǒng)計數(shù)據(jù)不真實

innodb_index_stats  
innodb_table_stats  
結(jié)局方法
#1.立即更新統(tǒng)計信息為最新的
# mysql> ANALYZE TABLE  表名
mysql> ANALYZE TABLE world.city;  
#2.重建索引

11.2.4 查詢條件使用函數(shù)在索引列上，或者對索引列進(jìn)行運算鞍陨，運算包括(+步淹，-，*诚撵，/缭裆，! 等)
例子：
錯誤的例子：select * from test where id-1=9;
正確的例子：select * from test where id=10;

算術(shù)運算  函數(shù)運算  子查詢 都會導(dǎo)致索引失效或者不走索引

11.2.5 隱式轉(zhuǎn)換導(dǎo)致索引失效.這一點應(yīng)當(dāng)引起重視.也是開發(fā)中經(jīng)常會犯的錯誤.
11.2.6 <> ，not in 不走索引（輔助索引）
11.2.7 like "%_" 百分號在最前面不走

12. 彩蛋(擴(kuò)展)：優(yōu)化器針對索引的算法

12.1 自優(yōu)化能力：
12.1.1 MySQL索引的自優(yōu)化-AHI（自適應(yīng)HASH索引）
a. 限制
MySQL的InnoDB引擎砾脑，能夠手工創(chuàng)建只有Btree幼驶。
AHI 只有InnoDB表會有，MySQL自動維護(hù)的韧衣。

AHI作用： 
自動評估"熱"的內(nèi)存索引page,生成HASH索引表盅藻。
幫助InnoDB快速讀取索引頁氏淑。加快索引讀取的效果。
相當(dāng)與索引的索引假残。

4.png

參考文章：

https://dev.mysql.com/doc/refman/5.7/en/innodb-adaptive-hash.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html

12.1.2 MySQL索引的自優(yōu)化-Change buffer
限制：
比如insert缭贡，update辉懒，delete 操作時會使用change buffer。
對于聚簇索引會直接更新葉子節(jié)點眶俩。
對于輔助索引莹汤，不是實時更新的。
insert into t1 (id,name,age) values(33,'d',18)

在InnoDB 內(nèi)存結(jié)構(gòu)中纲岭，加入了insert buffer（會話）线罕，現(xiàn)在版本叫change buffer钞楼。
Change buffer 功能是臨時緩沖輔助索引需要的數(shù)據(jù)更新。
當(dāng)我們需要查詢新insert 的數(shù)據(jù)窿凤，會在內(nèi)存中進(jìn)行merge(合并)操作雳殊，此時輔助索引就是最新的窗轩。

參考：
https://dev.mysql.com/doc/refman/5.7/en/innodb-change-buffer.html

12.1.3 8.0 版本索引的新特性

a. 不可見索引痢艺。invisable/visable index
針對優(yōu)化器不可見。但是索引還在磁盤存在色建，還會自動維護(hù)箕戳。
對于索引維護(hù)時，不確定索引是否還有用陵吸。這時可以臨時設(shè)定為invisable壮虫。

b. 倒序索引。
select * from t1 where c = order by a ASC , b desc
idx(c,a, b desc)

12.2 可選的優(yōu)化器算法-索引

12.2.1 優(yōu)化器算法查詢

select @@optimizer_switch;        #查詢默認(rèn)優(yōu)化器算法=show variables like '%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

12.2.2 如何修改?

my.cnf

#修改配置文件
optimizer_switch='batched_key_access=on'

#在線修改
 set global optimizer_switch='batched_key_access=on';

hints 了解一下
SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1
FROM t3 WHERE f1 > 30 AND f1 < 33;

SELECT /*+ BKA(t1) NO_BKA(t2) */ * FROM t1 INNER JOIN t2 WHERE ...;

SELECT /*+ NO_ICP(t1, t2) */ * FROM t1 INNER JOIN t2 WHERE ...;

SELECT /*+ SEMIJOIN(FIRSTMATCH, LOOSESCAN) / * FROM t1 ...;
EXPLAIN SELECT /+ NO_ICP(t1) */ * FROM t1 WHERE ...;

https://dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html

12.2.3 index_condition_pushdown （ICP）
介紹：索引下推，5.6+ 加入的特性
idx(a,b,c)
where a = and b 不等值 and c =

作用： SQL層做完過濾后徐伐，只能用a,b的部分輔助索引搬素，將c列條件的過濾下推到engine層熬尺，進(jìn)行再次過濾。排除無用的數(shù)據(jù)頁季二。
最終去磁盤上拿數(shù)據(jù)頁揭措。
大大減少無用IO的訪問绊含。

測試1: ICP開啟時
idx(k1,k2)

#開啟ICP參數(shù)
mysql> SET global optimizer_switch='index_condition_pushdown=ON' 
#進(jìn)行壓測測試
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where  k1 = 'Za' and k2 like '%sE%'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose

Benchmark
    Running for engine rbose
    Average number of seconds to run all queries: 1.114 seconds
    Minimum number of seconds to run all queries: 1.114 seconds
    Maximum number of seconds to run all queries: 1.114 seconds
    Number of clients running queries: 100
    Average number of queries per client: 20


Benchmark
    Running for engine rbose
    Average number of seconds to run all queries: 6.945 seconds
    Minimum number of seconds to run all queries: 6.945 seconds
    Maximum number of seconds to run all queries: 6.945 seconds
    Number of clients running queries: 100
    Average number of queries per client: 200

測試2:ICP關(guān)閉時：
idx(k1,k2)

#開啟ICP參數(shù)
mysql> SET global optimizer_switch='index_condition_pushdown=OFF'
#進(jìn)行壓測測試
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where  k1='Za' and  k2 like '%sE%'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose

Benchmark
    Running for engine rbose
    Average number of seconds to run all queries: 3.125 seconds
    Minimum number of seconds to run all queries: 3.125 seconds
    Maximum number of seconds to run all queries: 3.125 seconds
    Number of clients running queries: 100
    Average number of queries per client: 20


Benchmark
    Running for engine rbose
    Average number of seconds to run all queries: 31.102 seconds
    Minimum number of seconds to run all queries: 31.102 seconds
    Maximum number of seconds to run all queries: 31.102 seconds
    Number of clients running queries: 100
    Average number of queries per client: 200

具體參考：
https://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html
https://mariadb.com/kb/en/index-condition-pushdown/

12.4 MRR : Multi Range Read

12.4.1 作用：理論上減少回表躬充。

輔助索引掃描后充甚，得到聚簇索引值，統(tǒng)一緩存到read_rnd_buffer盈蛮，進(jìn)行排序抖誉，再次回表。

12.4.2 開關(guān)方法：

mysql> set global optimizer_switch='mrr=on,mrr_cost_based=off';

12.4.3 區(qū)別
具體參考：
https://dev.mysql.com/doc/refman/5.7/en/mrr-optimization.html
https://mariadb.com/kb/en/multi-range-read-optimization/

壓力測試： 
alter table world.city add index idx_n(name);
mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='world' --query="select * from world.city where name in ('Aachen','Aalborg','Aba','Abadan','Abaetetuba')" engine=innodb --number-of-queries=20000 -uroot -p123 -verbose

no-mrr: 
Benchmark
    Running for engine rbose
    Average number of seconds to run all queries: 2.909 seconds
    Minimum number of seconds to run all queries: 2.909 seconds
    Maximum number of seconds to run all queries: 2.909 seconds
    Number of clients running queries: 100
    Average number of queries per client: 200


mrr: 
Benchmark
    Running for engine rbose
    Average number of seconds to run all queries: 3.384 seconds
    Minimum number of seconds to run all queries: 3.384 seconds
    Maximum number of seconds to run all queries: 3.384 seconds
    Number of clients running queries: 100
    Average number of queries per client: 200

優(yōu)化沒有最佳實踐。

12.5 SNLJ 普通嵌套循環(huán)連接
例子：
A join B
on A.xx = B.yy
where

偽代碼：
for each row in A matching range {
block
for each row in B {
A.xx = B.yy 梳杏，send to client
}

}

例子：
mysql> desc select * from teacher join course on teacher.tno=course.tno;

優(yōu)化器默認(rèn)優(yōu)化規(guī)則：

選擇驅(qū)動表

默認(rèn)選擇方式（非驅(qū)動表）： 
    0. 結(jié)果集小的表作為驅(qū)動表
    按照on的條件列十性，是否有索引，索引的類型選擇楷掉。
    1. 在on條件中烹植，優(yōu)化器優(yōu)先選擇有索引的列為非驅(qū)動表愕贡。
    2. 如果兩個列都有索引，優(yōu)化器會按照執(zhí)行的代價去選擇驅(qū)動表和非驅(qū)動表墩虹。

for each row in course matching range {
block
for each row in teacher {
course.tno = tracher.tno 诫钓，send to client
}

}

關(guān)于驅(qū)動表選擇的優(yōu)化思路：
理論支撐：
mysql> desc select * from city join country on city.countrycode=country.code ;
mysql> desc select * from city left join country on city.countrycode=country.code ;

查詢語句執(zhí)行代價：
mysql> desc format=json select * from city join country on city.countrycode=country.code ;
mysql> desc format=json select * from city left join country on city.countrycode=country.code ;

實踐檢驗：
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='world' --query="select * from city left join country on city.countrycode=country.code ;" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose

[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='world' --query="select * from city join country on city.countrycode=country.code ;" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose

注：可以通過 left join 強制驅(qū)動表菌湃。

12.6 BNLJ
在 A和B關(guān)聯(lián)條件匹配時遍略，不再一次一次進(jìn)行循環(huán)墅冷。
而是采用一次性將驅(qū)動表的關(guān)聯(lián)值和非驅(qū)動表匹配.一次性返回結(jié)果
主要優(yōu)化了或油， CPU消耗顶岸，減少了IO次數(shù)

In EXPLAIN output,
use of BNL for a table is signified
when the Extra value contains Using join buffer (Block Nested Loop)

12.7 BKA
主要作用叫编，使用來優(yōu)化非驅(qū)動表的關(guān)聯(lián)列有輔助索引搓逾。
BNL+ MRR的功能杯拐。
開啟方式：
mysql> set global optimizer_switch='mrr=on,mrr_cost_based=off';
mysql> set global optimizer_switch='batched_key_access=on';
重新登陸生效端逼。

MySQL基礎(chǔ)優(yōu)化-索引及執(zhí)行計劃

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

12. 彩蛋(擴(kuò)展)：優(yōu)化器針對索引的算法

12.4.1 作用： 理論上減少回表躬充。

優(yōu)化沒有最佳實踐。

12.4.1 作用：理論上減少回表躬充。