程序员经验分享-hwhale

MySQL常见的几种优化方案

2023-05-16
注：原始资料来自享学课堂，自己加上整理和思考

目录

思考sql优化的几个地方，我把他做了个分类，方便理解

key_len计算方式简单介绍

一、优化点1：字段优化

覆盖索引尽量用

二、优化点2：where优化

1.尽量全值匹配

2.最佳左前缀法则

3.范围条件放最后

 4.不在索引列上做任何操作

5.不等于要甚用

6.Null/Not null有影响

7、Like 查询要当心

8.字符类型加引号

 三、优化3

1.OR 改 UNION 效率高

思考sql优化的几个地方，我把他做了个分类，方便理解

select [字段 优化1]：主要是覆盖索引
from []
where [条件 优化2]
union [联合查询 优化3]
新建表格 
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(50) DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `phone` varchar(12) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 添加索引，添加索引之后

key_len：根据这个值，就可以判断索引使用情况，特别是在组合索引的时候，判断所有的索引字段是否都被查询用到。

key_len计算方式简单介绍

latin1占用1个字节，gbk占用2个字节，utf8占用3个字节

不允许为空：

varchar(10)：10*3

char(10)：10*3+2

int：4

允许为空：

varchar(10)：10*3+1

char(10)：10*3+2+1

int：4+1

使用完全索引key_len=name(50*3+2+1=153)+age(4+1)+phone(12*3+2+1=39)

alter table studen add index name_age_phone(name, age, phone);

添加数据

insert into student(name,age,phone,create_time) values('赛文',1000,'15717177664',now());
insert into student(name,age,phone,create_time) values('雷欧',1200,'15733337664',now());
insert into student(name,age,phone,create_time) values('泰罗',800,'15714447664',now());

一、优化点1：字段优化

覆盖索引尽量用

简单解释解释，索引是哪几个列，就查询哪几个列：
覆盖索引的原因：索引是高效找到行的一个方法，但是一般数据库也能使用 索引找到一个列的数据，因此它 不必读取整个行。毕竟索引叶子节点存储了它们索引的数据;
当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引 包含了(或 覆盖了)满足查询结果的数据就叫做覆盖索引
注意：有索引尽量不要使用select * 
#未覆盖索引
EXPLAIN SELECT * FROM student WHERE NAME = '泰罗' and age =1000 and phone='15717177664';
#覆盖了索引
EXPLAIN SELECT name,age,phone FROM student WHERE NAME = '泰罗' and age =1000 and phone='15717177664';
#包含了索引
EXPLAIN SELECT name FROM student WHERE NAME = '泰罗' and age =1000 and phone='15717177664';
#加上主键也还是覆盖索引
EXPLAIN SELECT id, name,age,phone FROM student WHERE NAME = '泰罗' and age =1000 and phone='15717177664';

未使用覆盖索引 

 使用完全覆盖索引

使用包含覆盖索引

 加上主键还是覆盖索引

二、优化点2：where优化

1.尽量全值匹配

EXPLAIN SELECT * FROM student WHERE NAME = '赛文';
EXPLAIN SELECT * FROM student WHERE NAME = '雷欧' AND age = 1200;
EXPLAIN SELECT * FROM student WHERE NAME = '泰罗' AND age = 800 AND phone = '15714447664';

执行结果，三个都用到了索引，但是key_len是不同的，key_len=197，表示所有索引都使用到了

当建立了索引列后，能在 wherel 条件中使用索引的尽量所用。

2.最佳左前缀法则

最左前缀法则：指的是查询从索引的最左前列开始并且不跳过索引中的列。
我们定义的索引顺序是 name_age_phone ，所以查询的时候也应该从name开始，然后age，然后phone
情况1：从age、phone开始查询，tpye=All，key = null，没使用索引

情况2：从phone开始查询，type=All,key=null，未使用索引

 情况3：从name开始，type=ref，使用了索引

3.范围条件放最后

没有使用范围查询，key_len=197，使用到了name+age+phone组合索引 
EXPLAIN SELECT * FROM student WHERE NAME = '泰罗' AND age = 1000 AND phone = '15717177664';

 使用了范围查询，key_len从197变为158，即除了name和age，phone索引失效了

EXPLAIN SELECT * FROM student WHERE NAME = '泰罗' AND age > 800 AND phone = '15717177664';

 key_len=name(153)+age(5)

 4.不在索引列上做任何操作 

EXPLAIN SELECT * FROM student WHERE NAME = '泰罗';
EXPLAIN SELECT * FROM student WHERE left(NAME,1) = '泰罗';

不做计算,key_len有值，key_len=153，有使用name索引

做了截取结算，type=All,key_len=null，未使用索引

5.不等于要甚用

mysql 在使用不等于 (!= 或者 <>) 的时候无法使用索引会导致全表扫描 
#有使用到索引
EXPLAIN SELECT * FROM student WHERE NAME = '泰罗';
#不等于查询，未使用到索引
EXPLAIN SELECT * FROM student WHERE NAME != '泰罗';
EXPLAIN SELECT * FROM student WHERE NAME <> '泰罗';

#如果定要需要使用不等于,请用覆盖索引
EXPLAIN SELECT name,age,phone FROM student WHERE NAME != '泰罗';
EXPLAIN SELECT name,age,phone FROM student WHERE NAME <> '泰罗';

使用不等于查询，跳过索引

 使用不等于查询，同时使用覆盖索引，此时可以使用到索引

6.Null/Not null有影响

修改为非空

那么为not null，此时导致索引失效

EXPLAIN select * from student where name is null;
EXPLAIN select * from student where name is not null;

 

 改为可以为空

查询为空，索引起作用了

 查询非空索引失效

解决方法：

使用覆盖索引（覆盖索引解千愁） 

7、Like 查询要当心

like 以通配符开头 ('%abc...')mysql 索引失效会变成全表扫描的操作 
#like 以通配符开头('%abc...')mysql 索引失效会变成全表扫描的操作
#索引有效
EXPLAIN select * from student where name ='泰罗';
#索引失效
EXPLAIN select * from student where name like '%泰罗%';
#索引失效
EXPLAIN select * from student where name like '%泰罗';
#索引有效
EXPLAIN select * from student where name like '泰罗%';

解决方式：覆盖索引
EXPLAIN select name,age,phone from student where name like '%泰罗%';

 

 使用覆盖索引能够解决

8.字符类型加引号

字符串不加单引号索引失效（这个看着有点鸡肋了，一般查询字符串都会加上引号） 
#不加引号导致索引失效
EXPLAIN select * from student where name =11;
EXPLAIN select * from student where name ='泰罗';

 使用覆盖索引解决

 三、优化3

1.OR 改 UNION 效率高 

未使用索引
EXPLAIN select * from student where name='泰罗' or name = '雷欧';

使用索引
EXPLAIN
select * from student where name='泰罗'
UNION
select * from student where name = '雷欧';

解决方式：覆盖索引
EXPLAIN select name,age from student where name='泰罗' or name = '雷欧';

使用or未使用到索引

 使用union，使用了索引

解决方式：覆盖索引

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

MySQL常见的几种优化方案 的相关文章

随机推荐

  • AI在情人节，限定数藏等你领！

    这个情人节 xff0c 百度超级链为您准备了一份不一样的礼物 xff01 我们选择了全球以 浪漫 闻名的城市 xff0c 参考梵高 高更 毕加索 克里姆特 蒙德里安等知名艺术家的风格 xff0c 借助百度文心大模型创作一系列 情人节 主题艺

  • nvm 切换node 版本显示成功，但是查看还一直是之前的

    这个问题纠结了好久 xff0c 应该是先装的node xff0c 后面再装nvm引起的 xff0c 网上试了各种方法都不行 xff0c 后面发现直接在环境变量里面改可以切换了

  • 树莓派3B+ 安装系统

    树莓派3B 43 安装系统 对于树莓派3B 43 系统安装方法有很多 xff0c 我就介绍比较普通的一种 适合小白操作 xff01 安装概要步骤 xff1a 官网下载系统 刷入TF卡 设置开启显示器和SSH 通电 进入系统 1 进入官方网站

  • SSIS之“查找” 组件（功能解释 细节 图文 数据讲解）

    对比源数据和目标数据 xff0c 对设定查找条件进行查找 xff0c 相同查找条件的数据放一堆 xff0c 不同的放另一堆 今天我通过一个简单的例子来讲解 xff1a 从一个新的逗号分隔符格式的TXT文件里的数据 xff0c 通过查找组件

  • VNCserver服务配置

    VNCserver服务配置 xff08 麒麟版 xff09 vncserver是linux系统进行终端登录较为好用的一款软件 xff0c 但在某些情况下 xff0c 我们登录到vnc终端时 xff0c 发现出现界面不显示 xff0c 只有命

  • 云服务器 VNC 远程连接

    此服务器买来是为了搭建IC EDA云的 xff0c 因此选用的是centOS 6的环境 xff0c 对各EDA软件兼容较好 本人手头拮据 xff0c 因此买的是腾讯云活动期间的云服务器 xff0c 只能说够用吧 一 桌面安装 在云服务器控制

  • 解决VNC中Linux系统与Win系统中复制粘贴问题！（亲测有效！）

    解决VNC中Linux系统与Win系统中复制粘贴问题 1 这是我的VNC viewer的版本号 然后我想实现VNC与win系统的复制粘贴 2 解决方式 第一步 我在VNC中的Linux桌面打开终端 输入 vncconfig 然后 enter

  • 常见非关系型数据库(NoSQL)推荐介绍

    目录 非关系型数据库的特点 1 关系型数据库 2 关系型数据库瓶颈 3 NoSQL

  • 各种好用的在线时钟、网页时钟汇总推荐集合

    电脑全屏时钟网页版 https www sioe cn rili quanpingshizhong php 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61

  • Caused by: libcore.io.ErrnoException: open failed: ENOENT (No such file or d.......

    problem reading network stats Caused by libcore io ErrnoException open failed ENOENT No such file or d 我出现此错误是由于activity

  • MapReduce实现分词和倒排索引(算法TF-IDF)

    MapReduce实现分词和倒排索引 算法TF IDF 介绍IFDF IF xff1a 词频 xff08 单词在文档中出现的次数 文档中的总词数 xff09 DF xff1a 逆向文件频率 xff08 log 文档总数 出现该单词的文件数量

  • 节点共建，来了！

    节点是分布式区块链网络公信力的重要来源 百度超级链开放网络 xff08 XuperOS xff09 自2020年上线以来 xff0c 一直秉承透明可信 开放共享的理念对外招募超级节点 XuperOS最终有25个超级节点 xff0c 这些分布

  • 线程池简单案例

    线程池使用案例 一 Executors1 newFixedThreadPool2 newCachedThreadPool3 newSingleThreadExecutor4 newScheduledThreadPool二 ThreadPoo

  • 入门级springboot整合kafka

    文章目录 前言一 本文使用环境 xff1a 二 安装zookeeper二 安装kafka 三 整合到springboot总结 前言 在springboot和消息中间件愈发广泛的使用的年代 xff0c 掌握他们其中之一的整合有备无患 一 本文

  • springboot整合cas

    1 创建springboot项目后在pom中添加 span class token tag span class token tag span class token punctuation lt span dependency span

  • springboot 关闭CSRF 过滤

  • tencentSonic框架

    这是Soinc 项目的地址https github com Tencent VasSonic 这个框架做的事情是加速网页的加载速度 传统的WebView加载方式是WebView 初始化后 xff0c 然后去请求数据 xff0c 是串行的操作

  • 关闭虚拟机中的防火墙

    本人系统是centos 7 64位 因在使用虚拟机搭建zookeeper集群时 xff0c 无法成功 xff0c 发现问题是没有路由 xff0c 核查为虚拟机防火墙造成的 操作下面两条命令即可 xff1a systemctl stop fi

  • oracle 索引（index）原理、使用、分类、注意事项

    注意点 索引在大表中才有意义 在经常出现在条件中的字段上建立索引 索引层次不要超过4层 索引要占用大约为表的1 2倍的硬盘和内存空间 更新数据时 xff0c 系统必须要花费时间来更新索引 xff0c 不恰当的索引将降低系统性能 如 xff1

  • MySQL常见的几种优化方案

    注 xff1a 原始资料来自享学课堂 xff0c 自己加上整理和思考 目录 思考sql优化的几个地方 xff0c 我把他做了个分类 xff0c 方便理解 key len计算方式简单介绍 一 优化点1 xff1a 字段优化 覆盖索引尽量用 二

热门标签