大浪做网站公司,青岛科友网站建设网络公司,郑州网站建设讠汉狮网络,定制网站的优势文章目录 1 大表查询无条件优化原理(入门)2 大表查询带 条件 优化原理(进阶)2.1 where 后面的查询字段只有一个时#xff0c;要求该字段是索引字段2.2 where 后面的查询字段有多个时#xff0c;尽量让查询字段为索引字段且字段值基数大 3 大表查询带 排序 优化原理(入门)2 大表查询带 条件 优化原理(进阶)2.1 where 后面的查询字段只有一个时要求该字段是索引字段2.2 where 后面的查询字段有多个时尽量让查询字段为索引字段且字段值基数大 3 大表查询带 排序 优化原理(进阶)3.1 没有where 条件那么推荐使用 order by create_time desc。3.2 where 条件查询字段中如果存在索引字段 且当前查询不需要回表那么推荐使用 order by id desc 3.查询字段加了索引一定会用上吗最左匹配原则-模糊搜索案例~ 会颠覆你的三观 1 大表查询无条件优化原理(入门)
优化前( 查询耗时 114.1s)
select * from link_exec_task limit 80000, 10 # 查询耗时 114.1s优化后( 查询耗时 0.121s)
select * from link_exec_task a
INNER JOIN (select id from link_exec_task limit 80000, 10) b on a.id b.id #0.121s原理(索引覆盖非聚簇索引) 观察以下sql 查询计划
查询优化原理 主键ID形成的索引是聚簇索引(B树)叶子节点存的是记录(数据)而普通索引字段形成的索引是非聚簇索引非聚簇索引的叶子节点里面存的是记录的ID。
查找第8w条记录聚簇索引和非聚簇索引 查找对比
聚簇索引方式由于聚簇索引-数据页里面的数据项存的是完整的数据记录故而一个数据页里面的数据项占用空间大即一个数据页只能存少数的数据项即一次磁盘IO查询出来的数据页只包含少数的数据项。因此当limit 偏移量很大时就得通过多次的磁盘IO来查找对应的偏移量的记录很慢 非聚簇索引方式由于非聚簇索引-数据页里面的数据项存的是记录的ID故而一个数据页里面的数据项占用空间很小即一个数据页可以存更多的数据项即一次磁盘IO查询出来的数据页只包含很多个数据项。因此当limit 偏移量很大时只select id的情况下只需通过少次数的磁盘IO就能快速找到对应偏移量的记录ID很快~。
故而上面就算我查询哪怕没有用到 status字段进行查询Innodb执行引擎也会借助status索引字段使用的非聚簇索引来快使找到第8w条记录的ID。
问1为什么借助的是status索引字段而不使用其他非聚簇索引字段 答我的理解是 会挑一个索引字段空间占用最小的字段在该表的索引字段中就status字段的空间占用最小。因为这里借助非聚簇索引的目的是找出数据页里面的id值而不关心目录页里面的索引字段值由于目录页存的内容是索引字段值和数据页的地址故而索引字段越小那么一个目录页能存放更多的目录项这些目录页又可以指向数据页的地址故而可以通过更少次数的IO来找到更多的数据页。
问2子查询里面可能select id吗我还能select 其他吗 答正常情况下不行例如你select * 那么此时你的查询耗时会和优化前查询耗时一样因为此时你的查询不是覆盖索引查询由于非聚簇索引里面没有你要select的内容故而innodb直接就不借助非聚簇索引找第8w条记录的ID而是直接从聚簇索引里面找出第8w条记录故而速度会变得很慢。 当然如果你select 的字段均包含在某个非聚簇索引树里面那么此时还是会使用覆盖索引还是走非聚簇索引查询速度也会快。
问3上文的业务逻辑是查询第8W条数据之后的10条记录假设使用聚簇索引进行扫描那么只需扫描数据页下的8w条数据项那么innodb借助了status非聚簇索引进行查询第8w条记录之后的10个ID是否也只需要扫描8w条数据项 答一般不止。因为在聚簇索引中数据页里面的数据项(完整数据记录)的ID是升序的。但是在非聚簇索引中索引结构是根据索引字段值进行排序所以数据页里面的数据项的ID是无序的。所以我的理解是innodb需要把非聚簇索引所有数据页里面所有的数据项的ID都给查询出来然后再根据ID进行排序最终返回ID第8w大的10条ID记录。
2 大表查询带 条件 优化原理(进阶)
经过上文的学习我们找到大表主要可以利用 非聚簇索引覆盖索引 的方式进行目标记录ID的快速查找。
select * from link_exec_task a
INNER JOIN (select id from link_exec_task limit 80000, 10) b on a.id b.id 这一节主要讲 “select id from link_exec_task limit 80000, 10” 这部分 加条件和排序 的优化。因此下面的示例也是针对该部分的sql进行改造。
2.1 where 后面的查询字段只有一个时要求该字段是索引字段
以下文SQL为例 假设status字段不是索引字段那么innodb就无法找到 包含status字段的非聚簇索引树故而只能利用聚簇索引树进行数据过滤而聚簇索引的一次磁盘IO只能扫描到少数量的数据项(上一节的知识)故而该SQL查询非常耗时 假设status字段是索引字段那么innodb就可以找到 status字段对应的非聚簇索引树根据该非聚簇索引快速找到符合查询条件的数据页由于非聚簇索引的数据页里面的数据项只有记录ID(上一节的知识)即一个数据页可以包含大量的数据项。故而只需要少量的数据页就能表示符合条件的数据项故而通过少次数的磁盘IO就能把对应的数据页给查找出来故而该SQL查询非常快
select id from link_exec_task where status3 limit 80000, 102.2 where 后面的查询字段有多个时尽量让查询字段为索引字段且字段值基数大
注字段值基数大是指 字段值重复率低越低越好。例如ID的基数大因为ID不能重复。基数越大越有利于查找树的快速查找。
1以下文SQL为例假设status字段是索引字段而scene_type字段不是索引字段。status字段值基数大、小 时的查询耗时: 经过status过滤后此时会根据符合status条件的这些ID进行回表然后根据ID对应的所有记录然后再根据 scene_type 1 进行过滤筛选和合适的ID。如果status字段基数很大那么回表次数会很少此时总体查询耗时短反之回表次数会很多从而造成查询耗时急剧加长。
select id from link_exec_task where status3 and edge_id 04001 limit 80000, 102以下文SQL为例假设status字段是索引字段而edge_id 字段也是索引字段。 此时innodb会进行优化分析有可能将这两个索引进行合并即索引合并index_merge也有可能只使用其中一个索引。 如果使用了索引合并index_merge那么查询速度会很快因为此时innodb会使用status和edge_id 这两个非聚簇索引树分别根据查询条件进行过滤过滤出两组ID集合后再对ID集合取交集(因为条件是and方式)此过程无需回表就可以找出符合条件的ID集合。 如果只使用其中一个索引那么查询耗时就取决于作为索引的那个字段值基数大不大如果基数大那么耗时短否则耗时就很长具体原因见上文1。
select id from link_exec_task where status3 and edge_id 04001 limit 80000, 10问什么时候会使用索引合并什么时候只会使用其中一个索引。 答和你字段范围、字段的存储内容、条件查询内容有关。理论上表数据量大 limit offert偏移量大 情况下索引合并 会比 “只使用其中一个索引” 的查询速度快很多。但有时候innodb就是会选择“只使用一个索引”导致优化后查询速度反而变慢这玩意儿感觉也有点捉摸不透让人蛋疼。 相同查询条件查询内容不一样实际使用的索引可能也不一样。 条件里面有多个索引只实际使用一个聚簇索引可能导致的查询急剧下降 见下图 3 大表查询带 排序 优化原理(进阶)
假设当前表中有id主键字段也有creat_time索引字段前端需要展示最新记录那么此时就必须使用id或者create_time 进行倒序排序具体使用哪个字段呢选择合适排序字段能让你的查询速度天差地别。
3.1 没有where 条件那么推荐使用 order by create_time desc。
结合下图的explain结果 见下图无where条件时使用id和create_time进行倒序排序时间相差甚大这是为什么 如果没有where条件且使用id进行倒序排序时由于id没有专门的非聚簇索引树所以innodb使用的是聚簇索引查找从聚簇索引里面的最后一个数据页的数据项从后晚前找直到找到第8w条数据项之后的10条记录id。上文已经说过聚簇索引的数据页里面的数据项是完整的数据记录即一个数据项占用空间很大一个数据页只能包含少量的数据项故而需要查找大量的数据页即通过大量的磁盘IO 才能把这8w条数据项所在数据页给扫描出来速度很慢。
如果没有where条件且使用ceate_time进行倒序排序时由于create_time有专门的非聚簇索引树此时innodb使用的是非聚簇索引查找从ceate_time非聚簇索引里面的最后一个数据页的数据项从后晚前找直到找到第8w条数据项之后的10条记录id。上文已经说过非聚簇索引的数据页里面的数据项是记录ID即一个数据项占用空间很小一个数据页可以包含大量的数据项故而只需查找少量的数据页即通过少量的磁盘IO 就能把这8w条数据项所在数据页给扫描出来速度很慢。 以下SQL查询耗时27.166s
select id from link_exec_task ORDER BY id desc limit 100000, 10 # 查询耗时27.166s以下SQL查询耗时0.143s
select id from link_exec_task ORDER BY id desc limit 100000, 10 # 查询耗时0.143s3.2 where 条件查询字段中如果存在索引字段 且当前查询不需要回表那么推荐使用 order by id desc
结合下图的explain结果
看explain分析结果再结合上文那些说明其实你也应该懂了。 答where 条件查询字段中存在status索引查询字段 根据条件status0 innodb使用status非聚簇索引树过滤掉数据后拿到符合条件的记录ID集合此时分以下两种情况 假设当前是根据id进行排序排序 将过滤后ID集合进行快速倒序排序然后找出第10w条记录后面的10条记录ID不需要回表故而速度很快。 假设当前是根据create_time进行排序排序 由于当前是create_time字段进行倒序排序而且上一步status0过滤后只有记录的ID集合innodb执行引擎没办法对这些ID集合进行create_time倒序排序故而只能拿着过滤后的ID集合到聚簇索引树进行查找记录即回表根据ID对应的create_time值后再根据ID所对应的Create_time值对ID集合进行排序。由于我这张表中status字段基数值不大也就是status0过滤后的ID集合还是很大就会导致后面出现频繁回表从而造成查询速度急剧下降。 以下SQL执行耗时29.082s
select id from link_exec_task where status 0 ORDER BY create_time desc limit 100000, 10以下SQL执行耗时0.057s
select id from link_exec_task where status 0 ORDER BY id desc limit 100000, 103.查询字段加了索引一定会用上吗最左匹配原则-模糊搜索案例~ 会颠覆你的三观
答案是不一定innodb会根据 你当前 是否有使用limit分页、分页的偏移量大小、查询字段存储值基数大小、条件查询内容、排序字段 等 来决定当前使不使用 该查询字段索引。‘’
下面以 分页的偏移量大小、以及是否有使用limit分页 影响innodb查询方式进行演示 下这里我的name字段配置了普通索引理论来说使用最左匹配原则的模糊搜索就能使用到索引但事实真的是这样吗见下图。 下面两个图中我对name使用最左匹配原则的模糊搜索但实际上却没有使用到name索引也就是没有走非聚簇索引。而是走了主键索引从而导致我的查询速度极速下降。 这innodb的优化还不如不优化
把limit偏移量调小
查询耗时:22.153s
select id from link_exec_task where name like 定时% order by id desc limit 0, 10 # 查询耗时 22.153s把limit偏移量调大
我把上面的sql limit偏移量调到5000结果查询速度反而快了几百倍见下图 查询耗时:0.095s
select id from link_exec_task where name like 定时% order by id desc limit 5000, 10 # 查询耗时 0.095s上面说了使用limit 偏移量小慢偏移量大反而块。如果我直接把分页limit 给去掉是怎么样子的呢 直接把limit偏移量去掉:
查询耗时:0.075s
select id from link_exec_task where name like 定时% order by id desc # 查询耗时 0.075s总结至此我们明白了有时候我们使用 索引字段进行搜索但实际不一定会使用到该索引影响使用哪种索引的因素很多具体使用哪种索引还是由innodb决定。要注意的是innodb优化 有时候不一定会加快查询反而导致查询速度变慢几十、甚至几百倍。例如像上文的 最左匹配原则的模糊搜索 理论上走普通字段的非聚簇索引更快但是它有时候却使用了主键索引有例如有时候 查询使用到了多索引字段查询但实际没有使用索引合并而是只使用了其中一个索引造成查询速度变慢。
