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