MySQL索引及执行计划

最新推荐文章于 2023-09-06 16:35:45 发布

℡永恒

最新推荐文章于 2023-09-06 16:35:45 发布

阅读量355

点赞数

分类专栏：笔记

本文链接：https://blog.csdn.net/zwj1834787/article/details/107187981

版权

笔记专栏收录该内容

19 篇文章 0 订阅

订阅专栏

什么是索引？

相当于一本书中的目录。优化查询。

MySQL 支持的索引类型（算法）

Btree : 平衡多叉树
Rtree ：空间树所以
Hash ：HASH索引
Fulltext ：全文索引

数据查找算法介绍

二叉树
红黑树
Btree ： Balance Ttree

Btree的查找算法(见图)

B-tree
B+tree
B*tree

MySQL Btree索引的应用

聚簇索引（聚集索引、主键索引）

前提：
0. InnoDB存储引擎的表才会有聚簇索引。
1. 有主键，主键就是聚簇索引
2. 没有主键，选择唯一键作为聚簇索引
3. 生成一个隐藏列(DB_ROW_ID,6字节)，作为聚簇索引
作用：
1. 聚簇（区）索引，组织表（IOT）：所有数据在插入时，都按照ID（主键）属性，在相邻数据页上有序存储数据。
2. 加快存储数据，加快通过索引作为查找条件的查询。

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

聚簇索引构建过程

叶子节点：
由于存储数据时，已经按照ID顺序在各个数据页中有序存储了，所以《原表数据》所在数据页被作为叶子节点。
内部节点（非叶子节点）：
获取叶子节点ID范围+指针。
根节点：
获取非叶子节点 ID范围+指针

辅助索引

构建过程：
1. 叶子节点构建：
提取索引列值+ID ，进行从小到大排序（辅助索引列值），存储到各个数据页，作为Leaf node。
2. 非叶子节点（internel node ）
提取下层的辅助索引列值范围+指针。
3. 根节点：
提取下层节点的范围+指针。
对于查询的优化：
1. 通过辅助索引列，进行条件查询，根据辅助索引BTREE快速锁定条件值对应的ID。
2. 通过得出的ID值，回到聚簇索引继续查询到具体的数据行（回表）。

辅助索引分类

普通单列

联合索引

idx(a,b)
叶子节点：  
	 id+a+b ,按照a和b进行排序，生成叶子节点
枝节点和根节点： 
	 只会包含最左列（a列）的范围+指针
注意： 最左原则
		1. 建索引，最左列重复值少的。
		2. 查询条件中，必须包含最左列。

唯一索引

unique key

前缀索引

idex(test(10))

索引树高度影响因素

列值长度

前缀索引。

数据量

分区表。
定期归档表。
分布式架构：分库、分表。

数据类型

char(20)
varchar(20)

回表问题的探讨？

什么是回表？
辅助索引查找完成----》聚簇索引查询过程。

回表会带来的问题？
IO增多：量、次数

如何减少回表？
使用唯一值索引查询
联合索引
覆盖索引：辅助索引完全覆盖到查询结果

索引应用

压测

mysql> source /root/t100w.sql
mysql> grant all on . to root@‘10.0.0.%’ identified by ‘123’;

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

查询表的索引

desc t100w;

Key

PK --> 主键（聚簇索引）
MUL --> 辅助索引
UK --> 唯一索引

mysql> show index from t100w;

创建索引

单列辅助索引

select * from test.t100w where k2=‘780P’

优化方式：
语法：
alter table 表名 add index 索引名(列名);
alter table t100w add index idx_k2(k2);

联合索引创建

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

前缀索引创建

判断前缀长度多少合适：
select count(distinct(left(name,5))) from city ;
select count(distinct name) from city ;
创建前缀索引
mysql> alter table city add index idx_n(name(5));

删除索引

alter table city drop index idx_n;

执行计划查看和分析

什么是执行计划?

优化器优化后的“执行方案”。

作用？

a. 语句执行之前，通过执行计划，防患于未然。
b. 对于有性能问题的语句，进行分析。得出优化方案。

获取SQL的执行计划。

Select 、 update 、 delete

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 |
±—±------------±------±-----------±-----±---------------±-----±--------±------------±-----±---------±------+

执行计划介绍

table ：操作的表
type ：查询索引的类型（ALL、index、range、ref 、eq_ref、const(system)）
possible_keys : 可能会走的。
key ：最终选择的索引。
key_len ：联合索引覆盖长度。
rows ：此次查询需要扫描的行数(预估值)。
Extra ：额外信息。

type 详解

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 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 |
±—±------------±------±-----------±-----±--------------±-----±--------±-----±-------±---------±------------+

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 |
±—±------------±------±-----------±------±--------------±-----±--------±-----±-------±---------±------------+

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

注意：
如果重复值过多的话，可能改写的效果不佳。

通过压测：判断改写效果。

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

ref : 辅助索引等值查询

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

eq_ref:

非驱动表的连接条件是主键或唯一键。是多表连接中性能最好的查询方法。

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 |
±—±------------±-

const(system)

主键或唯一键等值查询

NULL

key_len 说明

计算方式

a. 介绍：
索引的应用长度
b. 作用：
判断联合索引的覆盖长度。
idx(a,b,c)

c. 如何计算key_len
总长度的计算：
a+b+c

d. 每个索引列占用多长？
每个列key_len,是这个列的《最大》预留长度。
影响因素：
1. 数据类型
2. not null
3. 字符集(字符串类型)

数字类型：
最大长度 key_len(not null) key_len(not null)
int 4 4 4+1

tinyint 1 1 1+1

字符串类型 (utf8):
最大长度 key_len(not null) key_len(not null)
char(10) 30 30 30+1

varchar(10) 30+2 30+2 30+2+1

字符串类型 (utf8mb4):
最大长度 key_len(not null) key_len(not null)
char(10) 40 40 40+1

varchar(10) 40+2 40+2 40+2+1

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 |
±—±------------±------±-----------±-----±--------------±-----±--------±------------------------------±-----±---------±------------+

联合索引应用细节

a. 联合索引全覆盖
idx(num,k1,k2)

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 ;
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. 完全不覆盖
mysql> desc select * from t100w where k1=‘At’ and k2 like ‘rsE%’;

索引应用规范总结

建立索引的原则（DBA运维规范）

(1) 必须要有主键,无关列。
(2) 经常做为where条件列 order by group by join on, distinct 的条件(业务:产品功能+用户行为)
(3) 最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引
(4) 列值长度较长的索引列,我们建议使用前缀索引.
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)
(6) 索引维护要避开业务繁忙期，建议用pt-osc
(7) 联合索引最左原则

不走索引的情况（开发规范）

没有查询条件，或者查询条件没有建立索引

select * from t1 ;
select * from t1 where id=1001 or 1=1;

查询结果集是原表中的大部分数据，应该是15-25%以上。

查询的结果集，超过了总数行数25%，优化器觉得就没有必要走索引了。
MySQL的预读功能有关。

可以通过精确查找范围，达到优化的效果。
1000000

500000 and

11.2.3 索引本身失效，统计信息不真实（过旧）
索引有自我维护的能力。
对于表内容变化比较频繁的情况下，有可能会出现索引失效。
一般是删除重建

现象:
有一条select语句平常查询时很快,突然有一天很慢,会是什么原因
select? —>索引失效,统计数据不真实

innodb_index_stats
innodb_table_stats

mysql> ANALYZE TABLE world.city;

查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)

例子：
错误的例子：select * from test where id-1=9;
正确的例子：select * from test where id=10;
算术运算
函数运算
子查询

隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.
<> ，not in 不走索引（辅助索引）
like “%_” 百分号在最前面不走

优化器针对索引的算法

自优化能力：

MySQL索引的自优化-AHI（自适应HASH索引）

a. 限制
MySQL的InnoDB引擎，能够手工创建只有Btree。
AHI 只有InnoDB表会有，MySQL自动维护的。

AHI作用：
自动评估"热"的内存索引page,生成HASH索引表。
帮助InnoDB快速读取索引页。加快索引读取的效果。
相当与索引的索引。

参考：
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

MySQL索引的自优化-Change buffer

限制：
比如insert，update，delete 操作时会使用change buffer。
对于聚簇索引会直接更新叶子节点。
对于辅助索引，不是实时更新的。
insert into t1 (id,name,age) values(33,‘d’,18)

在InnoDB 内存结构中，加入了insert buffer（会话），现在版本叫change buffer。
Change buffer 功能是临时缓冲辅助索引需要的数据更新。
当我们需要查询新insert 的数据，会在内存中进行merge(合并)操作，此时辅助索引就是最新的。

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

8.0 版本索引的新特性

a. 不可见索引。invisable/visable index
针对优化器不可见。但是索引还在磁盘存在，还会自动维护。
对于索引维护时，不确定索引是否还有用。这时可以临时设定为invisable。

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

可选的优化器算法-索引

优化器算法查询

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

如何修改?

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

index_condition_pushdown （ICP）

介绍：索引下推，5.6+ 加入的特性
idx(a,b,c)
where a = and b 不等值 and c =

作用： SQL层做完过滤后，只能用a,b的部分辅助索引，将c列条件的过滤下推到engine层，进行再次过滤。排除无用的数据页。
最终去磁盘上拿数据页。
大大减少无用IO的访问。

测试1: ICP开启时
idx(k1,k2)

mysql> SET global optimizer_switch=‘index_condition_pushdown=ON’
[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关闭时：
idx(k1,k2)
mysql> SET global optimizer_switch=‘index_condition_pushdown=OFF’

[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/

MRR : Multi Range Read

作用

理论上减少回表。

辅助索引扫描后，得到聚簇索引值，统一缓存到read_rnd_buffer，进行排序，再次回表。

开关方法：

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

区别

具体参考：
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

优化没有最佳实践。

SNLJ 普通嵌套循环连接

例子：
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;

优化器默认优化规则：

选择驱动表
默认选择方式（非驱动表）：
1. 结果集小的表作为驱动表
  按照on的条件列，是否有索引，索引的类型选择。
2. 在on条件中，优化器优先选择有索引的列为非驱动表。
3. 如果两个列都有索引，优化器会按照执行的代价去选择驱动表和非驱动表。

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

}

关于驱动表选择的优化思路：
理论支撑：
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 ;

查询语句执行代价：
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 强制驱动表。

BNLJ

在 A和B关联条件匹配时，不再一次一次进行循环。
而是采用一次性将驱动表的关联值和非驱动表匹配.一次性返回结果
主要优化了， CPU消耗，减少了IO次数

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

BKA

主要作用，使用来优化非驱动表的关联列有辅助索引。
BNL+ MRR的功能。
开启方式：
mysql> set global optimizer_switch=‘mrr=on,mrr_cost_based=off’;
mysql> set global optimizer_switch=‘batched_key_access=on’;
重新登陆生效。

℡永恒

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL索引及执行计划

什么是索引？相当于一本书中的目录。优化查询。MySQL 支持的索引类型（算法）Btree : 平衡多叉树Rtree ：空间树所以Hash ：HASH索引Fulltext ：全文索引数据查找算法介绍二叉树红黑树Btree ： Balance TtreeBtree的查找算法(见图)B-treeB+treeB*treeMySQL Btree索引的应用聚簇索引（聚集索引、主键索引）前提：0. InnoDB存储引擎的表才会有聚簇索引。1. 有主键，主键就是聚簇
复制链接

扫一扫