Mysql索引查询优化二
2023-05-26 17:57:56 7 举报mysql学习
作者其他创作
大纲/内容
<b>根据自增且连续的主键排序的分页查询</b>
自增且连续的主键排序的分页查询
可以这么优化，但是局限性太多不适用大部分场景，只适用主键自增连续的并且结果集按照主键排序
<b>根据非主键字段排序的分页查询</b>
根据name排序,name为连续索引第一个字段，因为查询结果集多大且 需要回表所以不如直接全表扫描来得快
可以适用<b><font color="#b71c1c">覆盖索引</font></b>优化，<b>查询出id关联主键</b>查询主键索引，优化之后使用<b><font color="#ff0000">索引排序</font></b>，原sql使用filesort
<b>Join关联查询优化</b>
<b>mysql的表关联常见有两种算法</b><br>
<b>Nested-Loop Join</b> 算法
<b>Block Nested-Loop Join</b> 算法
<b>嵌套循环连接 Nested-Loop Join(<font color="#ff0000">NLJ</font>) 算法</b>
一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动<br>表）里取出满足条件的行，然后取出两张表的结果合集。<b><font color="#000000">不知道为什么我自己测试时是</font><font color="#ff0000">大表驱动小表，把a变成唯一索引，或者使用主键id做关联，就是小表驱动大表</font></b>
驱动表是 t2，被驱动表是 t1。先执行的就是驱动表(执行计划结果的id如果一样则按从上到下顺序执行sql)；优<br>化器一般会优先选择小表做驱动表，<b><font color="#ff0000">不是一定的</font></b>。所以使用 inner join 时，排在前面的表并不一定就是驱动表。
使用了 NLJ算法。一般 join 语句中，如果<b>执行计划 Extra</b> 中未出现 <b><font color="#ff0000">Using join buffer </font></b>则表示使用的 join 算<br>法是 NLJ。
<b>基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法</b>
把驱动表的数据读入到 <b><font color="#b71c1c">join_buffer</font></b> 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。
<font color="#b71c1c"><b>Extra</b></font> 中 的Using join buffer (<b>Block Nested Loop</b>)说明该关联查询使用的是 BNL 算法。
上面sql流程如下：t1是大表，t2是<b><font color="#ff0000">小表</font></b>
把 t2 的所有数据放入到 join_buffer 中
把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比
返回满足 join 条件的数据
要是小表的数据量过大，大于256k放不下小表所有的数据，则分段放，会多扫一次t1表
被驱动表 的关联字段没索引为什么要选择使用BNL算法而不使用Nested-Loop join呢？
0 条评论
下一页
