MySQL索引优化二

分页查询优化

很多时候我们的业务系统实现分页功能可能会用如下sql实现

select * from employees limit 10000,10;

表示从表employees中取出从10001行开始的10条记录.看似只查询了10条记录,实际这条sql是先读取10010条记录,然后抛弃前10000条记录,然后读到后面10条想要的数据,因此查询一张大表比较靠后的数据,执行效率是非常低的.

常见的分页查询优化

  1. 根据自增且连续的主键排序的分页查询

    首先来看一个根据自增且连续主键排序的分页查询的例子:

    select * from employees limit 90000,5

    在这里插入图片描述

    该SQL表示查询从第90001开始的5条记录,没添加单独order by,表示通过 主键排序.再看表employees,因为主键是自增并且连续的,所以可以改写成按照主键去查询第90001开始的5条记录,如下:

    select * from employees where id > 90000 limit 5

    在这里插入图片描述

    查询结果是一致的.再对比下执行计划

    explain select * from employees limit 90000,5;
explain select * from employees where id > 90000 limit 5

    在这里插入图片描述
    在这里插入图片描述

    显然改写后的sql走了索引,而且扫描行数大大减少,执行效率更高.

    但是,这条改写的sql再很多场景并不实用,因为表中可能某些记录被删后,主键空缺,导致结果不一致.

    先删除一条前面的记录,然后再测试原sql和改写后的sql:
    在这里插入图片描述

    在这里插入图片描述

    两条sql的结果并不一样,因此,如果主键不连续,不能使用上面描述的优化方法.

    另外如果原sql是order by 非主键字段,按照上面说的方法会导致两条sql的结果不一致.所以这种改写得满足一下两个条件:

    • 主键是连续且自增
    • 结果是按照主键排序的

  2. 根据非主键字段排序的分页查询

    select * from employees order by name limit 90000,5;

    在这里插入图片描述

    explain select * from employees order by name limit 90000,5;

    在这里插入图片描述

    发现key字段对应的值为null,并没有使用name字段的索引.扫描整个索引并查找到没有索引的行,需要遍历多个B+树,成本比扫描全表的成本更高,所以优化器放弃使用索引.

    知道不走索引的原因后.可以做出针对性的优化.关键是 让排序时返回的字段尽可能的少,所以可以让排序和分页操作先查出主键,然后根据主键查到对应的记录,sql改写如下

    select e.* from employees e inner join(select id from employees order by name limit 90000,5)ed on e.id = ed.id;

    在这里插入图片描述

    需要的结果与原sql一致,执行时间减少了一半以上,再对比优化前后sql的执行计划:

    explain select e.* from employees e inner join(select id from employees order by name limit 90000,5)ed on e.id = ed.id;

    在这里插入图片描述

    原sql使用的是filesort排序,而优化后的sql使用的是索引排序.

    Join关联查询优化

    drop procedure if exists insert_t1; 
delimiter ;;
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 DEFAULT CHARSET=utf8;

create table t2 like t1;

-- 往t1表插入1万行记录
create procedure insert_t1()        
begin
  declare i int;                    
  set i=1;                          
  while(i<=10000)do                 
    insert into t1(a,b) values(i,i);
    set i=i+1;                       
  end while;
end;;
delimiter ;
call insert_t1();

-- 往t2表插入100行记录
drop procedure if exists insert_t2; 
delimiter ;;
create procedure insert_t2()        
begin
  declare i int;                    
  set i=1;                          
  while(i<=100)do                 
    insert into t2(a,b) values(i,i);
    set i=i+1;                       
  end while;
end;;
delimiter ;
call insert_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);优化器一般都会优先选择 小表做驱动表,用where条件过滤完驱动表,然后再跟被驱动表做关联查询.所以使用inner join 时,排在前面的并不一定就是驱动表
    • 当使用了left join时,左表是驱动表,右表是被驱动表,当使用right join时,右表是驱动表,左表是被驱动表,当使用join时,mysql会选择数据量较小的表作为驱动表,大表作为被驱动表
    • 使用了NLJ算法.一般join语句中,如果执行计划Extra字段中未出现 Using join buffer则表示使用的join算法是NLJ

    上面sql的大致流程如下:

    1. 从表t2中读取一行数据(如果t2的查询有where条件,先用条件过滤完,再从过滤结果取一行数据)
    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)=10010.并且jion_buffer里的数据是无序的,因此对表中的每一行,都要做100次判断,所以内存中判断的次数是100*10000=100W次.

    示例表t2才100行,如果t2是一个大表,join_buffer放不下怎么处理?

    join_buffer的大小是join_buffer_size设定的,默认值是256k.如果放不下所有数据的话,策略很简单,就是 分段放.

    例如t2表有100行数据,join_buffer一次只能放80行,那么执行过程就是先往join_buffer里放80行记录,然后从t1表里取数据跟join_buffer中数据对比得到部分结果,然后清空join_buffer,再放入t2剩余的20行.再次从t1表里取数据跟join_buffer中数据对比.所以就多扫了一次t1表.

    被驱动表的关联字段没索引为什么使用BNL而不是NLJ?

    如果上面第二条sql使用NLJ,那么扫描行数为100*10000=100W次,这个是磁盘扫描,

    很显然,用BNL磁盘扫描次数少很多,相比于磁盘扫描,BNL的内存计算会快很多

    因此MySQL对于被驱动表的关联字段没索引的关联查询使用的是BNL.如果有索引的情况下一般选择NLJ.有索引的情况下NLJ比BNL快.

    对于关联sql的优化

    • 关联字段加索引:让mysql做join操作时尽量选择NLJ算法,驱动表因为需要全部查询出来,索引过滤条件也尽量走索引,避免全表扫描,总之,能走索引的尽量都走索引
    • 小表驱动大表:写多表连接sql时如果 明确知道那张表是小表可以用straight_join写法固定连接驱动方式,省去mysql优化器自己判断的时间

    **straight_join解释:**straight_join功能同join类似,但能让左边表来驱动右边的表,能改表优化器对于联表查询的执行顺序.

    -- 指定t2作为驱动表
select * from t2 straight_join t1 on t2.a = t1.a
    • straight_join只适用于inner join,并不适用于left join,right join(因为left,right已经指定了表的执行顺序)
    • 尽可能让优化器去判断,因为大部分情况下优化器是比开发人员判断的更准确.使用straight_join一定要慎重,因为部分情况下手动指定的不一定会有优化器选择的靠谱

    对于小表的明确定义

    再决定那个表做驱动表的时候,应该是两个表按照各自的条件过滤,过滤完成之后,计算参与join的各个字段的总数据量,数据量小的那个表,就是"小表",应该作为驱动表.

in和exists优化

原则:小表驱动大表,即小的数据集驱动大的数据集

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 = B.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(*)查询优化

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(*)≈count(1)>count(字段)>count(id),字段有索引,count(字段)走二级索引,二级索引存储数据比主键索引少,所以count(字段)>count(id)

字段无索引:count(*)≈count(1)>count(id)>count(字段),字段无索引,count(字段)走不了索引,count(id)还可以走主键索引,所以count(id)>count(字段)

count(1)跟count(字段)执行过程类似,不过count(1)不需要取出字段统计,就用常量1做统计,count(字段)还需要取出字段,所以理论上count(1)比count(字段)会快一点.

count(*)时例外,mysql并不会把全部字段取出来,而是专门做了优化,不取值按行累加,效率很高,所以不需要用count(字段)或count(1)来代替count( *).

为什么对于count(id),mysql最终选择辅助索引而不是主键聚簇索引?

二级索引相对于主键索引存储数据更少,检索性能更高.mysql内部做了优化

常见优化方法

  1. 查询mysql自己维护的总行数

    对于MyISAM存储引擎的表做不带where条件的count查询性能是很高的,因为MyISAM存储引擎的表会被mysql存储再磁盘上,查询不需要计算

    -- 示例表
CREATE TABLE `test_myisam` (
  `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=MyISAM AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8;

    在这里插入图片描述

    对于 InnoDB存储引擎的表mysql不会存储表的总记录行数(因为涉及MVCC机制),查询count需要实时计算

  2. show table status

    如果只需要知道表总行数的估计值可以用以下sql查询,性能很高

    show table status like 'employees'

    在这里插入图片描述

  3. 将总数维护到Redis里面

    插入或删除表数据时同时维护redis里的表总行数key的计数值(用incr/decr),但这种方式可能不准,很难保证表操作和redis操作的事务一致性

  4. 增加数据库计数表

    插入或删除表数据行的时候同时维护计数表,让它们在同一个事务里操作

以上操作只支持无条件的count统计

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/490216.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

蓝鹏智能测量仪应用于这些方面！助力发展新质生产力！

蓝鹏智能测量仪应用于这些方面！助力发展新质生产力！

新质生产力是未来几年着重发展的方向&#xff0c;关于如何实现产业化升级&#xff0c;各厂家会在自身的基础上进行产业化调整升级&#xff0c;利用新工具、新手段&#xff0c;大幅缩短研发设计周期&#xff0c;从而让产品迭代速度不断加快&#xff1b;提升产品品质&#xff0c;…
阅读更多...
防静电检测设备如何完善PCBA车间的防静电管控？

防静电检测设备如何完善PCBA车间的防静电管控？

在PCBA&#xff08;Printed Circuit Board Assembly&#xff09;车间中&#xff0c;静电是一个极其重要的问题&#xff0c;因为静电可能对电子元器件和PCB板造成损坏&#xff0c;进而影响整个生产流程和产品质量。为了有效防止静电问题&#xff0c;企业通常会引入防静电检测设备…
阅读更多...
UE5学习日记——蓝图节点前缀关键字整理

UE5学习日记——蓝图节点前缀关键字整理

一、起因 节点如海&#xff0c;中英文翻译的时候还是有差别的&#xff0c;比如&#xff1a; 同一个中文&#xff0c;可能在英文里完全不同&#xff0c;连出现位置可能都不一样 附加 Attach Actor To Component&#xff08;将Actor附加到组件&#xff09;Append Array&#xf…
阅读更多...
CTF题型 nodejs(1) 命令执行绕过典型例题

CTF题型 nodejs(1) 命令执行绕过典型例题

CTF题型 nodejs(1) 命令执行绕过 文章目录 CTF题型 nodejs(1) 命令执行绕过一.nodejs中的命令执行二.nodejs中的命令绕过1.编码绕过2.拼接绕过3.模板字符串4.Obejct.keys5.反射6.过滤中括号的情况典型例题1.[GFCTF 2021]ez_calc2.[西湖论剑 2022]Node Magical Login 一.nodejs中…
阅读更多...
SpringBoot可以同时处理多少请求

SpringBoot可以同时处理多少请求

SpringBoot默认的内嵌容器是Tomcat&#xff0c;即看Tomcat可以处理多少请求 默认配置 server:tomcat:threads:min-spare: 10 # 最小工作线程数max: 200 # 最大线程数max-connections: 8192 # 接受和处理的最大连接数&#xff0c;超过8192的请求就会被放入到等待队列中ac…
阅读更多...
【原创教程】关于东方马达的控制方法（上）

【原创教程】关于东方马达的控制方法（上）

1 实现的功能 能够精准定位，快速移动到指定位置 2 硬件配置 东方马达组件一套包含：AZD-CD驱动器，AZM66MC马达电机。 如下图所示： 2.1 东方马达I/O端子分配 2.2 电路图 2.3 硬件接线
阅读更多...
代码随想录算法训练营第二十一天|530. 二叉搜索树的最小绝对差

代码随想录算法训练营第二十一天|530. 二叉搜索树的最小绝对差

530. 二叉搜索树的最小绝对差 已解答 简单 相关标签 相关企业 给你一个二叉搜索树的根节点 root &#xff0c;返回 树中任意两不同节点值之间的最小差值 。 差值是一个正数&#xff0c;其数值等于两值之差的绝对值。 示例 1&#xff1a; 输入&#xff1a;root [4,2,6,1,3] 输出…
阅读更多...
rfc793-page36

rfc793-page36

rfc793原文 If the connection is in any non-synchronized state (LISTEN,SYN-SENT, SYN-RECEIVED), and the incoming segment acknowledgessomething not yet sent (the segment carries an unacceptable ACK), orif an incoming segment has a security level or compart…
阅读更多...
Redis数据类型bitMap以及解决的相关实际需求

Redis数据类型bitMap以及解决的相关实际需求

在Redis数据库中&#xff0c;Bitmap&#xff08;位图&#xff09;是一种特殊的数据结构&#xff0c;它不是一个独立的数据类型&#xff0c;而是基于String类型实现的。Bitmap主要用于存储大量二进制位&#xff08;0或1&#xff09;的数据&#xff0c;这些位可以代表不同的状态或…
阅读更多...
CMU-TARE 探索算法官方社区问答汇总

CMU-TARE 探索算法官方社区问答汇总

参考引用 TARE机器人自主导航系统社区-CSDN社区云TARE平台资源链接汇总CMU团队开源算法点云地面分割 terrainAnalysis 代码解析Local Planner 代码详解以及如何适用于现实移动机器人论文翻译&#xff1a;Autonomous Exploration Development Environment and the Planning Algo…
阅读更多...
3.学习前后端关联

3.学习前后端关联

目录 1.接口类型 2.错误状态码 3.如何定义路由 4.那如何要求前端传入一个JSON数据呢&#xff1f; 4.解决前后端口不同源,跨域问题 1.使用CrossOrigin 2.直接复制代码使用 5.用户登录校验 1.接口类型 POST(新增数据)、PUT(更新更改数据)、GET(查询)、DELET(删除数据) …
阅读更多...
day05 设计计算机硬件

day05 设计计算机硬件

嵌入式学习-04_嵌入式技术之从零搭建计算机 1 添加立即数 现有系统的数据RAM存储方式（操作码+操作数）。 地址指令opcode（操作码）addr（操作数）新代码 /数据000ld_a0b000010b1000b0000100000000100001add0b000100b1010b0001000000000101010sub0b000110b1100b000110000000…
阅读更多...
搭建PHP本地开发环境：看这一篇就够了

搭建PHP本地开发环境：看这一篇就够了

什么是PHP本地开发环境 PHP本地开发环境是指在个人计算机上模拟的服务器环境&#xff0c;这使得开发者能够在没有网络连接的情况下也能开发、测试和调试PHP应用程序。就像在你的电脑里装个小“服务器”&#xff0c;即使没网也能搞定PHP程序的开发和修修补补。这就是PHP本地开发…
阅读更多...
【微服务】接口幂等性常用解决方案

【微服务】接口幂等性常用解决方案

一、前言 在微服务开发中&#xff0c;接口幂等性问题是一个常见却容易被忽视的问题&#xff0c;同时对于微服务架构设计来讲&#xff0c;好的幂等性设计方案可以让程序更好的应对一些高并发场景下的数据一致性问题。 二、幂等性介绍 2.1 什么是幂等性 通常我们说的幂等性&…
阅读更多...
自定义类型

自定义类型

在之前的博客中我们讲到了C语言有三种自定义类型&#xff1a;结构体&#xff08;结构&#xff09;、枚举和联合&#xff0c;在这篇博客中我们将更加深入地探讨这三种自定义类型。 结构体 1.结构体的声明 struct tag {int a;char ch;int arr[3];double d;float f; }t1,t2;如上…
阅读更多...
2022 年甘肃省职业院校技能大赛 高职组 网络系统管理竞赛 网络构建模块试题

2022 年甘肃省职业院校技能大赛 高职组 网络系统管理竞赛 网络构建模块试题

2022 年甘肃省职业院校技能大赛 高职组网络系统管理竞赛 网络构建模块试题 目 录 考试说明… 3 任务描述… 3 任务清单… 3 &#xff08;一&#xff09;基础配置… 3 &#xff08;二&#xff09;有线网络配置… 4 &#xff08;三&#xff09;无线网络配置… 6 &#xff08;四&a…
阅读更多...
【数据结构】双向奔赴的爱恋 --- 双向链表

【数据结构】双向奔赴的爱恋 --- 双向链表

关注小庄 顿顿解馋๑ᵒᯅᵒ๑ 引言&#xff1a;上回我们讲解了单链表(单向不循环不带头链表)&#xff0c;我们可以发现他是存在一定缺陷的&#xff0c;比如尾删的时候需要遍历一遍链表&#xff0c;这会大大降低我们的性能&#xff0c;再比如对于链表中的一个结点我们是无法直接…
阅读更多...
【探究图论中dfs记忆化，搜索，递推，回溯关系】跳棋，奶牛隔间， 小A和uim之大逃离 II

【探究图论中dfs记忆化，搜索，递推，回溯关系】跳棋，奶牛隔间， 小A和uim之大逃离 II

本篇很高能&#xff0c;如有错误欢迎指出&#xff0c;本人能力有限&#xff08;需要前置知识记忆化dfs&#xff0c;树形dp&#xff0c;bfsdp&#xff0c;tarjan&#xff09; 另外&#xff0c;本篇之所以属于图论&#xff0c;也是想让各位明白&#xff0c;dfs就是就是在跑图&am…
阅读更多...
DNS 服务 Unbound 部署最佳实践

DNS 服务 Unbound 部署最佳实践

文章目录 安装unbound-control配置启动服务测试 参考&#xff1a; 官网地址&#xff1a;https://nlnetlabs.nl/projects/unbound/about/ 详细文档&#xff1a;https://unbound.docs.nlnetlabs.nl/en/latest/index.html DNS服务Unbound部署于使用 https://cloud.tencent.com/…
阅读更多...
cryptography，一个神奇的 Python 库！

cryptography，一个神奇的 Python 库！

更多资料获取 &#x1f4da; 个人网站&#xff1a;ipengtao.com 大家好&#xff0c;今天为大家分享一个神奇的 Python 库 - cryptography。 Github地址&#xff1a;https://github.com/pyca/cryptography 在当今数字化时代&#xff0c;信息安全越来越受到重视。数据加密是保护…
阅读更多...
最新文章

Copyright @ 2022~2023