深入浅出mysql优化--一篇博客让你精通mysql优化策略--中
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了深入浅出mysql优化--一篇博客让你精通mysql优化策略--中，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含24913字，纯文字阅读大概需要36分钟。
内容图文
7. mysql 优化case
-
1.Order by与Group by优化
1. explain select * from employees where name = 'LiLei' and position = 'dev' order by age; 利用最左前缀法则：中间字段不能断，因此查询用到了name索引，从key_len=98也能看出，age索引列用在排序过程中， 因为Extra字段里没有using?filesort 2. explain select * from employees where name = 'LiLei' order by position; 从explain的执行结果来看：key_len=98，查询使用了name索引，由于用了position进行排序，跳过了 age，出现了Using?filesort 3. explain select * from employees where name = 'LiLei' order by age,position; 查找只用到索引name，age和position用于排序，无Using?filesort4. explain select * from employees where name = 'LiLei' order by position,age; 和Case?3中explain的执行结果一样，但是出现了Using?filesort， 因为索引的创建顺序为name,age,position，但是排序的时候age和position颠倒位置了。 5. explain select * from employees where name = 'LiLei' order by age asc,position desc; 排序的字段列与索引顺序一样，且 order?by 默认升序，这里position?desc变成了降序， 导致与索引的排序方式不同，从而产生Using?filesort。 Mysql8以上版本有降序索引可以支持该种查询方式 6. explain select * from employees where name in ('LiLei','zhaqngsan') order by age,position; 对于排序来说，多个相等条件也是范围查询 7. explain select * from employees where name > 'a' order by name; 可以用覆盖索引优化 explain select name,age,position from employees where name > 'a' order by name;
总结
1、MySQL支持两种方式的排序filesort和index，Using?index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。
2、order?by满足两种情况会使用Using?index。
1)?order?by语句使用索引最左前列。
2)?使用where子句与order?by子句条件列组合满足索引最左前列。
3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
4、如果order?by的条件不在索引列上，就会产生Using?filesort。
5、能用覆盖索引尽量用覆盖索引
6、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。
对于group by的优化如果不需要排序的可以加上order?by?null禁止排序。注意，where高于having，
能写在where中的限定条件就不要去having限定了。
-
Using filesort文件排序原理详解
filesort文件排序方式 单路排序： 是一次性取出满足条件行的所有字段，然后在sort?buffer中进行排序； 用trace工具可以看到sort_mode信息里显示<?sort_key,?additional_fields?>或者<?sort_key,packed_additional_fields?> 双路排序（又叫回表排序模式）： 是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行?ID，然后在sort?buffer中进行排序， 排序完后需要再次取回其它需要的字段；用trace工具可以看到sort_mode信息里显示<?sort_key,?rowid?> MySQL通过比较系统变量?max_length_for_sort_data(默认1024字节)?的大小和需要查询的字段总大小来判断使用哪种排序模式 如果?max_length_for_sort_data?比查询字段的总长度大，那么使用?单路排序模式； 如果?max_length_for_sort_data?比查询字段的总长度小，那么使用?双路排序模式。 示例验证下各种排序方式： explain select * from employees where name = 'zhangsan' order by position; 单路排序的详细过程： 1.?从索引name找到第一个满足name?=?‘zhuge’条件的主键id 2.?根据主键id取出整行，取出所有字段的值，存入sort_buffer中 3.?从索引name找到下一个满足name?=?‘zhuge’条件的主键id 4.?重复步骤?2、3?直到不满足name?=?‘zhuge’ 5.?对sort_buffer中的数据按照字段position进行排序 6.?返回结果给客户端 双路排序的详细过程 1.?从索引name找到第一个满足name?=?‘zhuge’的主键id 2.?根据主键id取出整行，把排序字段position和主键id这两个字段放到sort?buffer中 3.?从索引name取下一个满足name?=?‘zhuge’记录的主键id 4.?重复3、4直到不满足name?=?‘zhuge’ 5.?对sort_buffer中的字段position和主键id按照字段position进行排序 6.?遍历排序好的id和字段position，按照id的值回到原表中取出所有字段的值返回给客户端 其实对比两个排序模式，单路排序会把所有需要查询的字段都放到sort?buffer中，而双路排序只会把主键和需要排序的字段放到?sort?buffer中进行排序， 然后再通过主键回到原表查询需要的字段。 如果MySQL排序内存配置的比较小并且没有条件继续增加了，可以适当把?max_length_for_sort_data?配置小点， 让优化器选择使用双路排序算法，可以在sort_buffer中一次排序更多的行，只是需要再根据主键回到原表取数据。 如果MySQL排序内存有条件可以配置比较大，可以适当增大max_length_for_sort_data的值，让优化器优先选择全字段排序(单路排序)， 把需要的字段放到sort_buffer中，这样排序后就会直接从内存里返回查询结果了。 所以，MySQL通过?max_length_for_sort_data?这个参数来控制排序，在不同场景使用不同的排序模式，从而提升排序效率。 注意: 如果全部使用sort_buffer内存排序一般情况下效率会高于磁盘文件排序， 但不能因为这个就随便增大sort_buffer(默认1M)，mysql很多参数设置都是做过优化的，不要轻易调整 -
分页查询的优化
create procedure insert_emp() begin declare i int; set i =1; while(i<=100000)do insert into employees(name,age,position) values(CONCAT('zhuge',i),i,'dev'); set i=i+1; end while; end; call insert_emp();很多时候我们业务系统实现分页功能可能会用如下sql实现
select * from employees limit 10000,10; 表示从表?employees中取出从10001行开始的10行记录。看似只查询了10条记录， 实际这条SQL是先读取10010条记录，然后抛弃前10000条记录，然后读到后面10条想要的数据。 因此要查询一张大表比较靠后的数据，执行效率是非常低的。 -
常见的分页场景优化
1、根据自增且连续的主键排序的分页查询 select * from employees limit 90000,5; 该?SQL?表示查询从第?90001开始的五行数据，没添加单独 order by，表示通过主键排序。 再看表employees，因主键是自增并且连续的，所以可以改写成按照主键去查询从第?90001开始的五行数据，如下： select * from employees where id > 90000 limit 5; 改写后的?SQL?走了索引，而且扫描的行数大大减少，执行效率更高。 但是，这条?改写的SQL?在很多场景并不实用，因为表中可能某些记录被删后，主键空缺，导致结果不一致， 如果主键不连续，不能使用上面描述的优化方法。 另外如果原?SQL?是?order?by?非主键的字段，按照上面说的方法改写会导致两条SQL的结果不一致。所 以这种改写得满足以下两个条件： 主键自增且连续 结果是按照主键排序的 2、根据非主键字段排序的分页查询 explain select* from employees order by name limit 90000,5; 发现并没有使用name字段的索引（key字段对应的值为?null）， 具体原因: 扫描整个索引并查找到没索引的行(可能要遍历多个索引树)的成本比扫描全表的成本更高，所以优化器放弃使用索引。 知道不走索引的原因，那么怎么优化呢？ 其实关键是让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录，SQL改写如下 explain select* from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id; 需要的结果与原?SQL?一致，执行时间减少了一半以上，我们再对比优化前后sql的执行计划： 原?SQL?使用的是?filesort?排序，而优化后的?SQL?使用的是索引排序。
-
join 关联查询优化
create table t1( id int(11) not null auto_increment, a int(11) default null, b int(11) default null, primary key(id), key `idx_a` (a) )ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; create table t2 like t1; 往t1表插入1万行记录，往t2表插入100行记录 create procedure insert_emp_t1() begin declare i int; set i =1; while(i<=10000)do insert into t1(a,b) values(i,i+1); set i=i+1; end while; end; call insert_emp_t1(); create procedure insert_emp_t2() begin declare i int; set i =1; while(i<=100)do insert into t2(a,b) values(i,i+1); set i=i+1; end while; end; call insert_emp_t2(); -
mysql的表关联常见有两种算法
Nested-Loop?Join?算法 Block?Nested-Loop?Join?算法 1、嵌套循环连接?Nested-Loop?Join(NLJ)?算法 一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段， 根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集。 explain select * from t1 inner join t2 on t1.a = t2.a; 从执行计划中可以看到这些信息： 驱动表是t2，被驱动表是t1。 先执行的就是驱动表(执行计划结果的id如果一样则按从上到下顺序执行sql)； 优化器一般会优先选择小表做驱动表。所以使用inner?join时，排在前面的表并不一定就是驱动表。 使用了NLJ算法。一般join语句中，如果执行计划Extra中未出现Using?join?buffer则表示使用的join算法是NLJ。 上面sql的大致流程如下： 1.?从表t2中读取一行数据； 2.?从第1步的数据中，取出关联字段a，到表t1中查找； 3.?取出表t1中满足条件的行，跟t2中获取到的结果合并，作为结果返回给客户端； 4.?重复上面3步。 整个过程会读取t2表的所有数据(扫描100行)，然后遍历这每行数据中字段a的值，根据t2表中a的值索引扫描t1表中的对应行 (扫描100次t1表的索引，1次扫描可以认为最终只扫描t1表一行完整数据，也就是总共?t1?表也扫描了100行)。因此整个过程扫描了200行。 如果被驱动表的关联字段没索引，使用NLJ算法性能会比较低(下面有详细解释)，mysql会选择Block?Nested-Loop?Join算法。
2、基于块的嵌套循环连接?Block?Nested-Loop?Join( BNL )算法
把驱动表的数据读入到?join_buffer中，然后扫描被驱动表，把被驱动表每一行取出来跟?join_buffer?中的数据做对比。
explain select * from t1 inner join t2 on t1.b = t2.b
Extra中的Using?join?buffer(Block?Nested?Loop)说明该关联查询使用的是BNL算法。
上面sql的大致流程如下：
1.?把t2的所有数据放入到join_buffer中
2.?把表t1中每一行取出来，跟join_buffer中的数据做对比
3.?返回满足join条件的数据
整个过程对表t1和t2都做了一次全表扫描，
因此扫描的总行数为10000(表t1的数据总量)+100(表t2的数据总量)?=10100。
并且join_buffer里的数据是无序的，因此对表t1中的每一行，都要做100次判断，所以内存中的判断次数是100*10000=100万次。
被驱动表的关联字段没索引为什么要选择使用BNL算法而不使用Nested-Loop Join呢？
假设小表的行数是 N，大表的行数是 M，那么在这个算法里：
1. 两个表都做一次全表扫描，所以总的扫描行数是 M+N；
2. 内存中的判断次数是 M*N。
可以看到，调换这两个算式中的 M 和 N 没差别，因此这时候选择大表还是小表做驱动表，执行耗时是一样的
如果上面第二条sql使用Nested-Loop?Join，那么扫描行数为100?*?10000?=?100万次，这个是磁盘扫描。
很显然，用BNL磁盘扫描次数少很多，相比于磁盘扫描，BNL的内存计算会快得多。
因此MySQL对于被驱动表的关联字段没索引的关联查询，一般都会使用BNL算法。如果有索引一般选择NLJ算法，有索引的情况下NLJ算法比BNL算法性能更高
如果join_buffer放不下怎么办？
join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t2的所有数据话，策略很简单，就是分段放，
假如把join_buffer_size设置大一点再次执行，过程就会变成如下
1. 扫描表 t2，顺序读取数据行放入 join_buffer 中，放完第 88 行 join_buffer 满了，继续第 2 步；
2. 扫描表 t1，把 t1 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回；
3. 清空 join_buffer；
4. 继续扫描表 t2，顺序读取最后的 12行数据放入 join_buffer 中，继续执行第 2 步。
可以看到，这时候由于表t2被分成了两次放入 join_buffer 中，导致表 t1 会被扫描两次。
虽然分成两次放入join_buffer，但是判断等值条件的次数还是不变的，依然是(88+12)*1000=10 万次
对于关联sql的优化
关联字段加索引，让mysql做join操作时尽量选择NLJ算法
小标驱动大表，写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去mysql优化器自己判断的时间
straight_join解释：
straight_join功能同join类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执 行顺序。
比如：
explain select * from t2 straight_join t1 on t2.a = t1.a;
代表制定mysql选着?t2?表作为驱动表。
straight_join只适用于inner?join，并不适用于left?join，right?join。
（因为left?join，right?join已经代表指定了表的执行顺序）
尽可能让优化器去判断，因为大部分情况下mysql优化器是比人要聪明的。
使用straight_join一定要慎重，因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。
问题：
能不能使用 join?
假设不使用 join，那我们就只能用单表查询。我们看看上面这条语句的需求，用单表查询实现
1. 执行select * from t2，查出表 t1 的所有数据，这里有 100 行；
2. 循环遍历这 100 行数据：
可以看到，在这个查询过程，也是扫描了200行，但是总共执行了101条语句，比直接join 多了 100 次交互。
除此之外，客户端还要自己拼接 SQL 语句和结果。显然，这么做还不如直接 join好
1. 如果可以使用 Index Nested-Loop Join 算法，也就是说可以用上被驱动表上的索引，其实是没问题的；
2. 如果使用 Block Nested-Loop Join 算法，扫描行数就会过多。尤其是在大表上的 join操作，
这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种 join 尽量不要用。
在判断要不要使用 join 语句时，就是看 explain 结果里面，Extra 字段里面有没有出现“Block Nested Loop”字样
怎么选择驱动表？
在这个 join语句执行过程中，驱动表是走全表扫描，而被驱动表是走树搜索。
假设被驱动表的行数是 M。每次在被驱动表查一行数据，要先搜索索引 a，再搜索主键索引。
每次搜索一棵树近似复杂度是以 2为底的 M 的对数，记为 log M，所以在被驱动表上查一行的时间复杂度是 2*log M。
假设驱动表的行数是 N，执行过程就要扫描驱动表 N 行，然后对于每一行，到被驱动表上匹配一次。
因此整个执行过程，近似复杂度是 N + N*2*log M。
显然，N对扫描行数的影响更大，因此应该让小表来做驱动表。
如果你没觉得这个影响有那么“显然”， 可以这么理解：
N 扩大 1000 倍的话，扫描行数就会扩大 1000 倍；而 M 扩大 1000 倍，扫描行数扩大不到10 倍。
通过上面的分析得到了两个结论：
1. 使用 join 语句，性能比强行拆成多个单表执行 SQL 语句的性能要好；
2. 如果使用 join 语句的话，需要让小表做驱动表。
但是需要注意，这个结论的前提是“可以使用被驱动表的索引”
-
in和exsits优化
原则：小表驱动大表，即小的数据集驱动大的数据集
in： 当B表的数据集小于A表的数据集时，in优于exists select * from A where id in (select id from B) 可以理解为: for(select?id?from?B){ select?*?from?A?where?A.id?=?id } exists： 当A表的数据集小于B表的数据集时，exists优于in将主查询A的数据， 放到子查询B中做条件验证，根据验证结果（true或false）来决定主查询的数据是否保留 select * from A where exists (select 1 from B where B.id = A.id) 可以理解为: for(select?*?from?A){ select?*?from?B?where?B.id?=?A.id } A表与B表的ID字段应建立索引 1、EXISTS(subquery)只返回TRUE或FALSE,因此子查询中的SELECT?*?也可以用SELECT?1替换,官方说法是实际执行时会忽略SELECT清单,因此没有区别 2、EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比 3、EXISTS子查询往往也可以用JOIN来代替，何种最优需要具体问题具体分析 -
count(*)查询优化
临时关闭mysql查询缓存，为了查看sql多次执行的真实时间 set global query_cache_size=0; set global query_cache_type=0; explain select count(1) from employees; explain select count(id) from employees; explain select count(name) from employees; explain select count(*) from employees;
四个sql的执行计划一样，说明这四个sql执行效率应该差不多，区别在于根据某个字段count不会统计字段为null值的数据行
为什么mysql最终选择辅助索引而不是主键聚集索引？
因为二级索引相对主键索引存储数据更少，检索性能应该更高
优化方法
1、查询mysql自己维护的总行数
对于myisam存储引擎的表做不带where条件的count查询性能是很高的，
因为myisam存储引擎的表的总行数会被mysql存储在磁盘上，查询不需要计算
可自行建表测试
对于innodb存储引擎的表mysql不会存储表的总记录行数，查询count需要实时计算
2、show table status
如果只需要知道表总行数的估计值可以用如下sql查询，性能很高
3、将总数维护到Redis里
插入或删除表数据行的时候同时维护redis里的表总行数key的计数值(用incr或decr命令)，
但是这种方式可能不准，很难保证表操作和redis操作的事务一致性
4、增加计数表
插入或删除表数据行的时候同时维护计数表，让他们在同一个事务里操作
额外分析
select count(*) from t1
首先要明确的是，在不同的 MySQL 引擎中，count(*) 有不同的实现方式。
MyISAM引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高；
而InnoDB引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。
这里需要注意的是，在这里讨论的是没有过滤条件的 count(*)，如果加了where 条件的话，MyISAM 表也是不能返回得这么快的。
为什么InnoDB 不跟MyISAM 一样，也把数字存起来呢？
这是因为即使是在同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，存在事务（不同会话下可能看见的数据不一样等情况），
InnoDB表 “应该返回多少行” 也是不确定的。
这里说的是没有过滤条件的 count(*)，如果加了where 条件的话，MyISAM 表也是不能返回得这么快的
至于分析性能差别的时候，你可以记住这么几个原则：
1. server 层要什么就给什么；
2. InnoDB 只给必要的值；
3. 现在的优化器只优化了 count(*) 的语义为“取行数”，其他“显而易见”的优化并没有做。
对于 count(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id值都取出来，返回给 server 层。server 层拿到 id 后，
判断是不可能为空的，就按行累加。
对于 count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。
单看这两个用法的差别的话，你能对比出来，count(1) 执行得要比 count(主键 id) 快。因从引擎返回id会涉及到解析数据行，以及拷贝字段值的操作。
对于 count(字段) 来说：
1. 如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；
2. 如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。
也就是前面的第一条原则，server层要什么字段，InnoDB就返回什么字段。
但是 count(*)是例外，并不会把全部字段取出来，而是专门做了优化，不取值。count(*)肯定不是 null，按行累加。
看到这里，你一定会说，优化器就不能自己判断一下吗，主键id肯定非空啊，为什么不能按照 count(*)来处理，多么简单的优化啊。
当然，MySQL专门针对这个语句进行优化，也不是不可以。
但是这种需要专门优化的情况太多了，而且 MySQL已经优化过count(*) 了，直接使用这种用法就可以了。
所以结论是：按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(*)
join语句优化深入剖析
清空t2的数据重新插入100条
create procedure insert_emp_t2()
begin
declare i int;
set i =1;
while(i<=1000)do
insert into t2(a,b) values(1001-i,i+1);
set i=i+1;
end while;
end;
call insert_emp_t2();
t1 10000个数据，t2 1000个数据
优化目的：
Multi- 优化 (MRR)。这个优化的主要目的是尽量使用顺序读盘
在这之前需要明白，回表肯定是一行行搜索主键索引的，不能批量会表
-
MRR
以下sql select * from t1 where a>=1 and a<=100 主键索引是一棵 B+ 树，在这棵树上，每次只能根据一个主键 id 查到一行数据。 因此，回表肯定是一行行搜索主键索引的，基本流程如图 30所示
如果随着a的值递增顺序查询的话，id 的值就变成随机的，那么就会出现随机访问，性能相对较差。 虽然“按行查”这个机制不能改，但是调整查询的顺序，还是能够加速的。 因为大多数的数据都是按照主键递增顺序插入得到的，所以我们可以认为， 如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能 这，就是 MRR 优化的设计思路。此时，语句的执行流程变成了这样 1. 根据索引 ，定位到满足条件的记录，将id值放入 read_rnd_buffer 中 ; 2. read_rnd_buffer 中的id进行递增排序； 3. 排序后的id数组，依次到主键 id 索引中查记录，并作为结果返回。 这里，read_rnd_buffer的大小是由 read_rnd_buffer_size 参数控制的。 如果步骤 1 中，read_rnd_buffer放满了，就会先执行完步骤 2 和 3，然后清空 read_rnd_buffer。 之后继续找索引 a 的下个记录，并继续循环。 另外需要说明的是，如果想要稳定地使用MRR 优化的话，需要设置 set optimizer_switch="mrr_cost_based=off" （官方文档的说法，是现在的优化器策略，判断消耗的时候，会更倾向于不使用 MRR，把 mrr_cost_based 设置为 off，就是固定使用 MRR 了。） 使用了MRR 优化后的执行流程和 explain 结果:
从图的explain 结果中，可以看到 Extra 字段多了 Using MRR，表示的是用上了MRR 优化。 而且，由于在 read_rnd_buffer 中按照 id 做了排序，所以最后得到的结果集也是按照主键id递增顺序的，也就是与图30 结果集中行的顺序相反 结论： MRR能够提升性能的核心在于，这条查询语句在索引 a 上做的是一个范围查询（也就是说，这是一个多值查询）， 可以得到足够多的主键 id。这样通过排序以后，再去主键索引查数据，才能体现出“顺序性”的优势 -
Batched Key Access
理解了MRR性能提升的原理，就能理解 MySQL 在 5.6 版本后开始引入的 BatchedKey Access(BKA) 算法了。这个 BKA 算法，其实就是对 NLJ算法的优化 基于Index Nested-Loop Join 流程图，看前面的图 NLJ算法执行的逻辑是： 从驱动表 t2，一行行地取出 a 的值，再到被驱动表 t1 去做join。 也就是说，对于表 t1 来说，每次都是匹配一个值。这时，MRR的优势就用不上了。 那怎么才能一次性地多传些值给表 t1 呢？ 方法就是，从表 t2里一次性地多拿些行出来，一起传给表t1。 既然如此，就把表t2的数据取出来一部分，先放到一个临时内存。这个临时内存不是别人，就是 join_buffer 通过前面的内容可以知道 join_buffer 在BNL算法里的作用，是暂存驱动表的数据。 但是在 NLJ 算法里并没有用。那么，刚好就可以复用 join_buffer到 BKA 算法中。 如下图Batched Key Access 流程所示，是上面的NLJ算法优化后的BKA算法的流程。
图中，在join_buffer中放入的数据是 P1~P100，表示的是只会取查询需要的字段。当然，如果 join buffer 放不下 P1~P100 的所有数据，
就会把这 100 行数据分成多段执行上图的流程
那么，这个 BKA 算法到底要怎么启用呢？
如果要使用BKA 优化算法的话，需要在执行SQL语句之前，先设置
set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
其中，前两个参数的作用是要启用 MRR。这么做的原因是，BKA 算法的优化要依赖于MRR
BNL 算法的性能问题
在此之前有一个问题需要分析
使用 Block Nested-Loop Join(BNL) 算法时，可能会对被驱动表做多次扫描。
如果这个被驱动表是一个大的冷数据表，除了会导致IO 压力大以外，还会对系统有什么影响呢？
由于 InnoDB 对 BuffferPool 的 LRU 算法做了优化，即：
第一次从磁盘读入内存的数据页，会先放在 old 区域。
如果 1 秒之后这个数据页不再被访问了，就不会被移动到 LRU 链表头部，这样对 BufferPool 的命中率影响就不大。
但是，如果一个使用BNL算法的join语句，多次扫描一个冷表，而且这个语句执行时间超过 1 秒，就会在再次扫描冷表的时候，把冷表的数据页移到 LRU 链表头部
这种情况对应的，是冷表的数据量小于整个Buffer Pool的 3/8，能够完全放入 old 区域的情况。
如果这个冷表很大，就会出现另外一种情况：
业务正常访问的数据页，没有机会进入young 区域。
由于优化机制的存在，一个正常访问的数据页，要进入 young 区域，需要隔1秒后再次被访问到。
但是，由于join 语句在循环读磁盘和淘汰内存页，进入 old 区域的数据页，很可能在1秒之内就被淘汰了。
这样，就会导致这个 MySQL 实例的 Buffer Pool 在这段时间内，young区域的数据页没有被合理地淘汰。
也就是说，这两种情况都会影响 Buffer Pool 的正常运作
大表 join操作虽然对IO有影响，但是在语句执行结束后，对 IO 的影响也就结束了。
但是，对 Buffer Pool 的影响就是持续性的，需要依靠后续的查询请求慢慢恢复内存命中率
为了减少这种影响，可以考虑增大 join_buffer_size 的值，减少对被驱动表的扫描次数
也就是说，BNL 算法对系统的影响主要包括三个方面
1. 可能会多次扫描被驱动表，占用磁盘 IO 资源；
2. 判断join条件需要执行 M*N 次对比（M、N 分别是两张表的行数），如果是大表就会占用非常多的 CPU 资源；
3. 可能会导致Buffer Pool的热数据被淘汰，影响内存命中率。
因此在执行语句之前，需要通过理论分析和查看 explain 结果的方式，确认是否要使用 BNL算法。
如果确认优化器会使用 BNL算法，就需要做优化。
优化的常见做法是，给被驱动表的 join 字段加上索引，把 BNL算法转成BKA算法
BNL转BKA优化方式
一些情况下，可以直接在被驱动表上建索引，这时就可以直接转成 BKA 算法了。
但是，有时候确实会碰到一些不适合在被驱动表上建索引的情况。比如下面这个语句:
explain select * from t2 join t1 on (t2.b=t1.b) where t1.b>=1 and t1.b<=2000;
在数据准备的时候，在表 t1中插入了 1万行数据，但是经过 where 条件过滤后，需要参与 join 的只有 2000 行数据。
如果这条语句同时是一个低频的 SQL 语句，那么再为这个语句在表 t2 的字段 b上创建一个索引就很浪费了
但是，如果使用 BNL 算法来 join 的话，这个语句的执行流程是这样的：
1. 把表 t2的所有字段取出来，存入 join_buffer 中。这个表只有 1000 行，join_buffer_size 默认值是 256k，可以完全存入。
2. 扫描表 t1，取出每一行数据跟 join_buffer 中的数据进行对比，
如果不满足 t2.b=t1.b，则跳过；
如果满足 t2.b=t1.b, 再判断其他条件，也就是是否满足 t1.b 处于 [1,2000] 的条件，
如果是，就作为结果集的一部分返回，否则跳过。
对于表t1的每一行，判断 join 是否满足的时候，都需要遍历join_buffer中的所有行。
因此判断等值条件的次数是 1000*1 万=1千万次，这个判断的工作量很大。
这里执行用了0.6s
在表t1的字段b上创建索引会浪费资源，但是不创建索引的话这个语句的等值条件要判断1 千万次，想想也是浪费。那么，有没有两全其美的办法呢？
这时候，我们可以考虑使用临时表。使用临时表的大致思路是:
1. 把表 t1中满足条件的数据放在临时表tmp_t中；
2. 为了让join使用 BKA 算法，给临时表 tmp_t 的字段 b加上索引；
3. 让表t2和tmp_t做join 操作。
此时，对应的 SQL 语句的写法如下:
create temporary table temp_t(id int primary key, a int, b int, index(b))engine=innodb;
insert into temp_t select * from t1 where b>=1 and b<=2000;
select * from t2 join temp_t on (t2.b=temp_t.b);
数据多一点能明显比较出来 性能得到了大幅提升(我这里查看用了0.3S)
分析一下这个过程的消耗
1. 执行insert语句构造 temp_t表并插入数据的过程中，对表 t1 做了全表扫描，这里扫描行数是 1万。
2. 之后的 join 语句，扫描表 t2，这里的扫描行数是 1000；join 比较过程中，做了 1000次带索引的查询。
相比于优化前的 join 语句需要做 1千万次条件判断来说，这个优化效果还是很明显的。
总体来看，不论是在原表上加索引，还是用有索引的临时表，思路都是让 join 语句能够用上被驱动表上的索引，来触发 BKA 算法，提升查询性能
-
扩展 -hash join
看到这里你可能发现了，其实上面计算 1千万次那个操作，看上去有点儿傻。 如果join_buffer里面维护的不是一个无序数组，而是一个哈希表的话，那么就不是 1千万次判 断，而是 1万次 hash 查找。 这样的话，整条语句的执行速度就快多了吧？ 对此MySQL 的优化器和执行器并不支持，但是这可以在业务代码中实现，实现流程大致如下： 1. select * from t2; 取得表 t2 的全部 1000行数据，在业务端存入一个 hash 结构，比如 C++ 里的 set、PHP 的数组这样的数据结构。 2. select * from t1 where b>=1 and b<=2000; 获取表 t1 中满足条件的 2000 行数据。 3. 把这 2000 行数据，一行一行地取到业务端，到 hash 结构的数据表中寻找匹配的数据。满足匹配的条件的这行数据，就作为结果集的一行。 理论上，这个过程会比临时表方案的执行速度还要快一些。这里我就不验证了最后在讲join 语句的这两篇文章中，都只涉及到了两个表的 join。那么，现在有一个三个表join的需求
CREATE TABLE `t1` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; create table t2 like t1; create table t3 like t2; 语句的需求实现如下的 join 逻辑： select * from t1 join t2 on (t1.a=t2.a) join t3 on (t2.b=t3.b) where t1.c>=X and t2.c>=Y and t3.c>=Z; 现在为了得到最快的执行速度，如果让你来设计表 t1、t2、t3 上的索引，来支持这个 join语句，你会加哪些索引呢？ 如果希望你用 straight_join 来重写这个语句，配合你创建的索引，你就需要安排连接顺序，主要考虑的因素是什么呢？ 第一原则是要尽量使用 BKA算法。 需要注意的是，使用BKA算法的时候，并不是“先计算两个表 join的结果，再跟第三个表 join”，而是直接嵌套查询的。 具体实现是： 在 t1.c>=X、t2.c>=Y、t3.c>=Z 这三个条件里，选择一个经过过滤以后，数据最少的那个表，作为第一个驱动表。 此时，可能会出现如下两种情况。 第一种情况，如果选出来是表t1或者 t3，那剩下的部分就固定了。 1. 如果驱动表是 t1，则连接顺序是 t1->t2->t3，要在被驱动表字段创建上索引，也就是t2.a 和 t3.b 上创建索引； 2. 如果驱动表是 t3，则连接顺序是 t3->t2->t1，需要在 t2.b 和 t1.a 上创建索引。 同时，还需要在第一个驱动表的字段c上创建索引。 第二种情况是，如果选出来的第一个驱动表是表 t2 的话，则需要评估另外两个条件的过滤效果。 总之，整体的思路就是，尽量让每一次参与 join 的驱动表的数据集，越小越好，因为这样驱动表就会越小
内容总结
以上是互联网集市为您收集整理的深入浅出mysql优化--一篇博客让你精通mysql优化策略--中全部内容，希望文章能够帮你解决深入浅出mysql优化--一篇博客让你精通mysql优化策略--中所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】