当前位置: 首页 > news >正文

MSQL系列(六) Mysql实战-SQL语句优化

Mysql实战-SQL语句优化

前面我们讲解了索引的存储结构,B+Tree的索引结构,以及索引最左侧匹配原则,Explain的用法,可以看到是否使用了索引,今天我们讲解一下SQL语句的优化及如何优化

文章目录

      • Mysql实战-SQL语句优化
        • 1.表结构
        • 2 where语句及order的列 建立索引
        • 3. where语句不要使用!=,<>
        • 4.where语句不要or进行判断
        • 5.where语句不要使用 like模糊查询
        • 6.where语句 不要 in 和not in, 可能也会导致全表扫描
        • 7.where语句不要使用表达式计算及函数运算

1.表结构

新建表结构 user, user_info

#新建表结构 user
CREATE TABLE `user` (`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',`id_card` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '身份证ID',`user_name` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户名字',`age` int NOT NULL COMMENT '年龄',PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表'
  1. id 主键id列
  2. id_card 身份证id
  3. user_name 用户姓名
  4. age 年龄

先插入测试数据, 插入 5条测试数据

INSERT INTO `test`.`user` (`id`, `id_card`, `user_name`, `age`) VALUES (1, '11', 'aa', 10);
INSERT INTO `test`.`user` (`id`, `id_card`, `user_name`, `age`) VALUES (2, '22', 'bb', 20);
INSERT INTO `test`.`user` (`id`, `id_card`, `user_name`, `age`) VALUES (3, '33', 'cc', 30);
INSERT INTO `test`.`user` (`id`, `id_card`, `user_name`, `age`) VALUES (4, '44', 'dd', 40);
INSERT INTO `test`.`user` (`id`, `id_card`, `user_name`, `age`) VALUES (5, '55', 'ee', 50);
2 where语句及order的列 建立索引

表结构先不创建索引,我们看下执行分析
EXPLAIN SELECT * FROM user WHERE user_name=“AA”;

EXPLAIN SELECT * FROM `user` WHERE user_name="AA";

执行成功, type=ALL表示没有索引,查询效率低下
在这里插入图片描述

我们在 user_name上建立索引后,再看下

#创建索引
alter  table `user` add index `idx_name`(`user_name`);#执行分析
EXPLAIN SELECT * FROM `user` WHERE user_name="AA";

使用了索引,查询效率提升
在这里插入图片描述

3. where语句不要使用!=,<>

where语句中使用!= 或者 <>, 或者使用 between and 都会是引擎放弃索引,进行全表扫描

我们新建 age的索引,然后基于age去做查询分析

#创建age索引
alter  table `user` add index `idx_age`(`age`);
#执行分析
EXPLAIN SELECT * FROM `user` WHERE age=10;

使用age索引进行查询,没有问题
在这里插入图片描述
现在我们使用 != 或者 <> 来进行查询,执行查询分析

EXPLAIN SELECT * FROM `user` WHERE age !=10;
EXPLAIN SELECT * FROM `user` WHERE age <>10;
EXPLAIN SELECT * FROM `user` WHERE age BETWEEN 10 and 20;
EXPLAIN SELECT * FROM `user` WHERE age > 10 and age < 20 ;

执行结果全都是 type=range 表示在索引范围内查找,对索引的扫描开始于某一点,返回匹配值域的行, 已经不是ref类型了,效率已经不高了
Extra 其他信息= using index condition 表示会先条件过滤索引,过滤完索引后找到所有符合索引条件的数据行,随后用 WHERE 子句中的其他条件去过滤这些数据行;
using index condition = using index + 回表 + where 过滤
在这里插入图片描述

4.where语句不要or进行判断

where语句使用or判断,也会导致引擎放弃索引,进而进行全表扫描
使用 or, 也会造成 type=range的情况

EXPLAIN SELECT * FROM `user` WHERE age =10 or age =20;

在这里插入图片描述
这种情况,我们可以采用 union all 来进行优化

EXPLAIN SELECT * FROM `user` WHERE age =10 union all  SELECT * FROM `user` WHERE age =20 ;

在这里插入图片描述

5.where语句不要使用 like模糊查询

like模糊查询,也会导致 全表扫描

#1.左侧开头精确匹配,右侧结果模糊
EXPLAIN SELECT * FROM `user` WHERE user_name like "a%";
#2.左侧开头模糊,右侧结果精确匹配
EXPLAIN SELECT * FROM `user` WHERE user_name like "%a";
#3.左侧开头模糊,右侧结果模糊
EXPLAIN SELECT * FROM `user` WHERE user_name like "%a%";

上面3种情况,我们来逐一分析

  1. 左侧开头精确匹配,右侧结果模糊, 查询会使用左侧索引进行匹配,type=range
EXPLAIN SELECT * FROM `user` WHERE user_name like "a%";

在这里插入图片描述
2. 左侧开头模糊,右侧结果精确匹配, 查询不会使用索引,全表扫描 type=ALL

EXPLAIN SELECT * FROM `user` WHERE user_name like "%a";

在这里插入图片描述
3. 左侧开头模糊,右侧结果模糊, 查询不会使用索引,全表扫描 type=ALL

EXPLAIN SELECT * FROM `user` WHERE user_name like "%a%";

在这里插入图片描述

6.where语句 不要 in 和not in, 可能也会导致全表扫描

where子语句,使用 in,not in 也有可能导致全表扫描

所以使用in 到底走不走索引呢?

  • in通常是走索引的
  • IN 的条件过多,会导致索引失效,走索引扫描
  • 当in后面的数据在数据表中超过一定的数量 (有人说是30%,假如上面的例子的全部数据大约100条,匹配数据超过30条 ),会走全表扫描,即不走索引
  • in走不走索引和后面的数据有关系,这个比例不准

我表中5条数据, 我现在 in(10,20,30,40), in了4条,但是依旧走了索引 type=range, key=idx_age

EXPLAIN SELECT * FROM `user` WHERE age in(10,20,30,40);

在这里插入图片描述

我现在再加一个in条件 in(10,20,30,40,50), 此刻就没有走索引, type=ALL

EXPLAIN SELECT * FROM `user` WHERE age in(10,20,30,40,50);

在这里插入图片描述

但是 not in 是肯定不走索引的,这是我们明确禁止的

EXPLAIN SELECT * FROM `user` WHERE age not in(1,2);

在这里插入图片描述

7.where语句不要使用表达式计算及函数运算

where子句,不要使用表达式计算或者函数运算,这回导致全表扫描

EXPLAIN SELECT * FROM `user` WHERE age / 2 =10;
EXPLAIN SELECT * FROM `user` WHERE SUBSTRING(user_name,1,3)="aa";

执行结果全部都是 type=ALL,使用表达式计算和函数的 都不会使用索引
在这里插入图片描述


至此,我们了解如何去优化查询语句,在平时项目中,也应该多注意这些用法,防止出现线上事故

相关文章:

MSQL系列(六) Mysql实战-SQL语句优化

Mysql实战-SQL语句优化 前面我们讲解了索引的存储结构&#xff0c;BTree的索引结构&#xff0c;以及索引最左侧匹配原则&#xff0c;Explain的用法&#xff0c;可以看到是否使用了索引&#xff0c;今天我们讲解一下SQL语句的优化及如何优化 文章目录 Mysql实战-SQL语句优化1.…...

kaggle新赛:UBC卵巢癌亚型分类和异常检测大赛【图像分类】

赛题名称&#xff1a;UBC Ovarian Cancer Subtype Classification and Outlier Detection (UBC-OCEAN) 赛题链接&#xff1a;https://www.kaggle.com/competitions/UBC-OCEAN 赛题背景 卵巢癌是女性生殖系统最致命的癌症。目前&#xff0c;卵巢癌诊断依赖病理学家评估亚型。…...

基于nodejs+vue云旅青城系统

目 录 摘 要 I ABSTRACT II 目 录 II 第1章 绪论 1 1.1背景及意义 1 1.2 国内外研究概况 1 1.3 研究的内容 1 第2章 相关技术 3 2.1 nodejs简介 4 2.2 express框架介绍 6 2.4 MySQL数据库 4 第3章 系统分析 5 3.1 需求分析 5 3.2 系统可行性分析 5 3.2.1技术可行性&#xff1a;…...

《孙哥说Spring5》笔记汇总

时隔两个多月&#xff0c;终于将《孙哥说Spring5》的笔记文章全部整理完了&#xff0c;在这里做个汇总。孙哥的Spring课讲的非常好&#xff0c;深度和广度都有所兼顾&#xff0c;推荐大家去看 点击学习《孙哥说Spring5》 基础铺垫 1️⃣ Spring5应用之基础扫盲2️⃣ Spring5应…...

在使用了spring-cloud-starter-gateway后,为什么还会发生cors问题

//1.需要配置类 import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.web.cors.CorsConfiguration; import org.springframework.web.cors.reactive.CorsWebFilter; import org.sp…...

CentOS7安装MySQL8.0.28

CentOS7安装MySQL8.0.28 一、下载MySQL安装包二、安装配置mysql 一、下载MySQL安装包 点击以下链接可以自动跳转&#xff1a;MySQL官网 接下来按如图所示依次点击进入。 选择自己所需要版本 此处如需下载历史版本可以点击 二、安装配置mysql 1、登录ssh或其他相关软件上…...

AutoSAR入门:应用背景及简介

1、应用背景 在我们现在的汽车行业里面&#xff0c;汽车电子的发展过程中&#xff0c;我们发现有一些新的趋势汽车电子系统的复杂性不断增长。 我们现在可以看到车辆有越来越多的功能&#xff0c;那么这些功能呢&#xff0c;也在往这个控制器上进行集中&#xff0c;比如说我们现…...

C++初阶(三)

文章目录 一、auto关键字(C11)1、auto简介2、auto使用规则1、 auto与指针和引用结合起来使用2、 在同一行定义多个变量 3、auto不能推导的场景1、 auto不能作为函数的参数2、 auto不能直接用来声明数组3、特性总结 二、基于范围的for循环(C11)1、范围for的语法2、 范围for的使用…...

PHP的学习入门建议

学习入门PHP的步骤如下&#xff1a; 确定学习PHP的目的和需求&#xff0c;例如是为了开发网站还是为了与数据库交互等。学习PHP的基础语法和程序结构&#xff0c;包括变量、数据类型、循环、条件等。学习PHP的面向对象编程&#xff08;OOP&#xff09;概念和技术。学习与MySQL…...

骰子涂色(Cube painting, UVa 253)rust解法

输入两个骰子&#xff0c;判断二者是否等价。每个骰子用6个字母表示&#xff0c;如图4-7所示。 例如rbgggr和rggbgr分别表示如图4-8所示的两个骰子。二者是等价的&#xff0c;因为图4-8&#xff08;a&#xff09;所示的骰子沿着竖直轴旋转90之后就可以得到图4-8&#xff08;b&a…...

elasticsearch的docker安装与使用

安装 docker network create elasticdocker pull docker.elastic.co/elasticsearch/elasticsearch:8.10.4# 增加虚拟内存&#xff0c; 此处适用于linux vim /etc/sysctl.conf # 添加 vm.max_map_count262144 # 重新启动 sysctl vm.max_map_countdocker run --name es01 --net …...

ELK 单机安装

一丶软件下载 elasticsearch: https://www.elastic.co/downloads/past-releases kibana: https://www.elastic.co/downloads/past-releases 选择对应的版本的下载即可 二、es 安装es比较简单 rpm -ivh elasticsearch-2.4.2.rpm 修改配置文件 /etc/elasticsearch/elas…...

优雅而高效的JavaScript——?? 运算符、?. 运算符和 ?. 运算符

&#x1f974;博主&#xff1a;小猫娃来啦 &#x1f974;文章核心&#xff1a;优雅而高效的JavaScript——?? 运算符、?. 运算符和 ?. 运算符 文章目录 引言空值处理的挑战解决方案1&#xff1a;?? 运算符基本用法与 || 运算符的区别实际应用场景举例 解决方案2&#xff…...

Nginx配置负载均衡

Nginx配置负载均衡 使用nginx来配置负载均衡也是比较简单的 首先在http块中配置虚拟域名所对应的地址 # 负载均衡upstream myserver {server 127.0.0.1:8080;server 127.0.0.1:8082;}可以配置的参数有以下选项 #down 不参与负载均衡 #weight5; 权重&#xff0c;越高分配越多 #b…...

Ubuntu 20.04 上安装 neo4j

1. 进入要安装neo4j的ubuntu环境。 2. 添加Debian资源库。 Java 1.8.xx版本对应Neo4j 3.xx版本&#xff1a; &#xff08;1&#xff09;wget -O - https://debian.neo4j.com/neotechnology.gpg.key | sudo apt-key add - &#xff08;2&#xff09;echo deb https://debian.…...

大规模爬虫系统面临的主要挑战及解决思路

在构建大规模爬虫系统时&#xff0c;我们常常面临一系列挑战。这些挑战包括高效爬取、频率限制、分布式处理、存储和数据管理等方面。为了应对这些挑战&#xff0c;我们需要采取一些解决思路和策略。在本文中&#xff0c;我将与大家分享大规模爬虫系统面临的主要挑战以及解决思…...

统计学习方法 感知机

文章目录 统计学习方法 感知机模型定义学习策略学习算法原始算法对偶算法 学习算法的收敛性 统计学习方法 感知机 读李航的《统计机器学习》时&#xff0c;关于感知机的笔记。 感知机&#xff08;perceptron&#xff09;是一种二元分类的线性分类模型&#xff0c;属于判别模型…...

Linux命令(103)之wc

linux命令之wc 1.wc介绍 linux命令wc是用来统计文件的字数、行数和字节数 2.wc用法 wc [参数] [filename] wc参数 参数说明-l统计总行数&#xff0c;备注&#xff1a;常用于查看进程是否启动-L统计最长一行的字符数-c统计字节数-m统计字符数-w统计单词数 3.实例 3.1.统计…...

京东店铺公司名爬虫

内容仅供学习参考&#xff0c;如有侵权联系删除 先通过京东非自营的店铺名拿到的公司名&#xff0c;再通过公司名称去其他平台拿到联系方式&#xff08;代码省略&#xff09; from aioscrapy.spiders import Spider from aioscrapy.http import Request, FormRequest import dd…...

如何解决不同浏览器的样式兼容性问题?

目录 1. 理解浏览器差异&#xff1a; 2. 使用标准CSS属性和值&#xff1a; 3. CSS Reset 或 Normalize&#xff1a; 4. 使用浏览器引擎前缀&#xff1a; 5. 使用CSS兼容性工具&#xff1a; 6. 测试和调试&#xff1a; 7. 使用Polyfill&#xff1a; 8. 条件注释&#xf…...

UE5 学习系列(二)用户操作界面及介绍

这篇博客是 UE5 学习系列博客的第二篇&#xff0c;在第一篇的基础上展开这篇内容。博客参考的 B 站视频资料和第一篇的链接如下&#xff1a; 【Note】&#xff1a;如果你已经完成安装等操作&#xff0c;可以只执行第一篇博客中 2. 新建一个空白游戏项目 章节操作&#xff0c;重…...

简易版抽奖活动的设计技术方案

1.前言 本技术方案旨在设计一套完整且可靠的抽奖活动逻辑,确保抽奖活动能够公平、公正、公开地进行,同时满足高并发访问、数据安全存储与高效处理等需求,为用户提供流畅的抽奖体验,助力业务顺利开展。本方案将涵盖抽奖活动的整体架构设计、核心流程逻辑、关键功能实现以及…...

通过Wrangler CLI在worker中创建数据库和表

官方使用文档&#xff1a;Getting started Cloudflare D1 docs 创建数据库 在命令行中执行完成之后&#xff0c;会在本地和远程创建数据库&#xff1a; npx wranglerlatest d1 create prod-d1-tutorial 在cf中就可以看到数据库&#xff1a; 现在&#xff0c;您的Cloudfla…...

C++ 基础特性深度解析

目录 引言 一、命名空间&#xff08;namespace&#xff09; C 中的命名空间​ 与 C 语言的对比​ 二、缺省参数​ C 中的缺省参数​ 与 C 语言的对比​ 三、引用&#xff08;reference&#xff09;​ C 中的引用​ 与 C 语言的对比​ 四、inline&#xff08;内联函数…...

第一篇:Agent2Agent (A2A) 协议——协作式人工智能的黎明

AI 领域的快速发展正在催生一个新时代&#xff0c;智能代理&#xff08;agents&#xff09;不再是孤立的个体&#xff0c;而是能够像一个数字团队一样协作。然而&#xff0c;当前 AI 生态系统的碎片化阻碍了这一愿景的实现&#xff0c;导致了“AI 巴别塔问题”——不同代理之间…...

基于Docker Compose部署Java微服务项目

一. 创建根项目 根项目&#xff08;父项目&#xff09;主要用于依赖管理 一些需要注意的点&#xff1a; 打包方式需要为 pom<modules>里需要注册子模块不要引入maven的打包插件&#xff0c;否则打包时会出问题 <?xml version"1.0" encoding"UTF-8…...

技术栈RabbitMq的介绍和使用

目录 1. 什么是消息队列&#xff1f;2. 消息队列的优点3. RabbitMQ 消息队列概述4. RabbitMQ 安装5. Exchange 四种类型5.1 direct 精准匹配5.2 fanout 广播5.3 topic 正则匹配 6. RabbitMQ 队列模式6.1 简单队列模式6.2 工作队列模式6.3 发布/订阅模式6.4 路由模式6.5 主题模式…...

推荐 github 项目:GeminiImageApp(图片生成方向,可以做一定的素材)

推荐 github 项目:GeminiImageApp(图片生成方向&#xff0c;可以做一定的素材) 这个项目能干嘛? 使用 gemini 2.0 的 api 和 google 其他的 api 来做衍生处理 简化和优化了文生图和图生图的行为(我的最主要) 并且有一些目标检测和切割(我用不到) 视频和 imagefx 因为没 a…...

群晖NAS如何在虚拟机创建飞牛NAS

套件中心下载安装Virtual Machine Manager 创建虚拟机 配置虚拟机 飞牛官网下载 https://iso.liveupdate.fnnas.com/x86_64/trim/fnos-0.9.2-863.iso 群晖NAS如何在虚拟机创建飞牛NAS - 个人信息分享...

6.9-QT模拟计算器

源码: 头文件: widget.h #ifndef WIDGET_H #define WIDGET_H#include <QWidget> #include <QMouseEvent>QT_BEGIN_NAMESPACE namespace Ui { class Widget; } QT_END_NAMESPACEclass Widget : public QWidget {Q_OBJECTpublic:Widget(QWidget *parent nullptr);…...