MySQL数据库索引优化实战

目录

一、前言

二、准备工作

2.1 用户表(TB_USER)

2.2 商品表(TB_SKU)

2.3 订单表(TB_ORDER)

三、实例分析

3.1 索引提升查询性能

3.2 多表查询

3.3 索引失效

四、总结


一、前言

在数据库的应用中,性能优化是一个持续不断的过程。其中索引优化是提高查询速度的最有效的方法,本文将通过案例,深入探讨MySQL索引优化,帮助读者理解索引优化、在实际的应用中提升性能。

二、准备工作

在开始优化分析之前,现在准备好三张表TB_USER、TB_SKU、TB_ORDER表。

2.1 用户表(TB_USER)

用户表结构如下

创建表的语句:

CREATE TABLE `tb_user`  (
  `id` int(0) NOT NULL COMMENT 'id',
  `create_time` datetime(0) NOT NULL COMMENT '创建时间',
  `creator` varchar(20) NOT NULL COMMENT '创建人',
  `user_id` int(0) NOT NULL COMMENT '用户id',
  `user_name` varchar(30) NOT NULL COMMENT '用户名',
  `addr` varchar(300) NULL DEFAULT NULL COMMENT '地址',
  `remark` varchar(200)  NULL DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`) USING BTREE
) ;

2.2 商品表(TB_SKU)

表结构如下

创建表的语句

CREATE TABLE `tb_sku`  (
  `id` int(0) NOT NULL COMMENT 'id',
  `create_time` datetime(0) NOT NULL COMMENT '创建时间',
  `creator` varchar(20) NOT NULL COMMENT '创建人',
  `sku_id` int(0) NOT NULL COMMENT '商品id',
  `sku_name` varchar(200) NOT NULL COMMENT '商品名称',
  `remark` varchar(200)  NULL DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`) USING BTREE
);

2.3 订单表(TB_ORDER)

表结构如下:

创建表的语句:

CREATE TABLE `tb_order`  (
  `id` int(0) NOT NULL,
  `create_time` datetime(0) NOT NULL,
  `creator` varchar(20)  NOT NULL,
  `user_id` int(0) NOT NULL,
  `sku_id` int(0) NOT NULL,
  `status` varchar(1)  NULL DEFAULT NULL,
  `remark` varchar(200)  NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ;

三、实例分析

在上一篇文章中介绍了索引的优化策略,最后介绍了查看SQL执行技术的关键字explain,并对explain执行计划的各个字段作了介绍,本文将结合实例对各个字段作详细介绍。

3.1 索引提升查询性能

tb_user表总共有6条数据,一个id主键索引,因此用select * from tb_user where user_id=1;去查询的时候,

1. select_type=SIMPLE,简单查询,因为这是一个单表的查询;

2. type=ALL, type的类型有:NULL、system、const、eq_ref、ref、range、index、all;从左到右,性能逐一降低,all的性能最差,属于全部扫描。

3.filtered=16.67,也就是从全部6条记录中筛选出1条,filtered的值是越大越好。

接下来,给tb_user增加一个user_id的索引,然后再来看执行计划

create index idx_tb_user_user_id on tb_user(user_id);

 

从上图的执行计划可以看到,type变成了ref,也就是查询的时候使用了索引; filtered的值变成了100.00,因此性能得到了很大的提升。

下一步,将idex_tb_user_user_id的索引删除,重新创建唯一索引,再来看执行计划

alter table tb_user drop index idx_tb_user_user_id;
create unique index idx_tb_user_user_id on tb_user(user_id);

 type再次从ref变为了const,性能再次得到了提升。

3.2 多表查询

从tb_user、tb_sku、tb_order三张表中查询用户都买了哪些商品

 查看这个SQL的执行计划

对于id是相同的,从上往下执行。

如果id不同的情况,id越大越先执行,假设通过子查询的方式,查询买了铅笔的人名。

查看以下这个SQL的执行计划

这个的执行顺序,跟我们理解的也是一样,应该先去查询tb_sku,查出sku_id,再根据sku_id查询tb_order表,查询user_id,最后再去查询tb_user表;

3.3 索引失效

1. 不要在索引列上运算,否则索引会失效。在user_id上作了运算之后,索引失效,性能降低。

2. 字符串字段,查询时如果没有加单引号,索引失效

首先,我们在tb_order表的status字段上加上索引, 

create index idx_tb_order_status on tb_order(status);

3.  or连接时,一侧有索引、一侧没有索引,索引失效

4. 数据分布影响,这个可能是我们平时在做索引优化时,会被忽略的一点,之前在实际项目中碰到过类似的情况,后来重要明白是因为数据分布导致的。 

我们先将tb_order表中的status字段全更新为9,然后再来查看执行计划。因为当MySQL评估使用索引比全表更慢,则不使用索引。

另外我们将tb_order的数据分布设置为如下:

再进行status>='2' 和 status>='0'的查询,会发现status>='2'的时候,会使用索引,而status>='0'的时候,不会使用索引,而执行全表查询。这是因为数据分布影响的。

四、总结

本文根据实际例子介绍了如何提升查询性能、索引失效等问题,索引对于提升查询性能有很大的帮助,但是也不能滥用索引,因为使用索引本身会占用存储空间,影响数据插入和更新的速度。

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

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

相关文章

SpringBoot从配置文件中获取属性的方法

方式一:Value 基本类型属性注入,直接在字段上添加Value("\${xxx.xxx}")即可.注意这里用的是$,而不是#,Value注入的属性,一般其他属性没有关联关系。 配置文件 user:name: Manaphya…

【搜索引擎】elastic search核心概念

前言 本文不涉及ES的具体安装下载、操作、集群的内容,这部分内容会放在后面一篇文章中。本文只包含ES的核心理论,看完本文再去学ES的细节会事半功倍。 目录 1.由日志存储引出的问题 2.什么是ES? 3.ES的数据结构 4.ES的核心原理 5.联系作…

高效工具汇总,让学习和办公飞起来

目录 1、寻找论文,效率很高2、学习各类编程的地方 1、寻找论文,效率很高 AMiner,由清华大学计算机科学与技术系的唐杰教授团队开发的一个显著的学术搜索和挖掘系统。系统提供了一整套功能以协助学术研究,包括研究人员档案、专家搜…

MySQL8.0安装教程

Mysql安装教程 1.Mysql下载 进入官网https://www.mysql.com/进行下载: 2.Mysql安装 双击文件进行安装,选择默认安装方式: 这里列出了五种安装类型: Developer Default:默认安装类型;Server only&#x…

性能测试浅谈

早期的性能测试更关注后端服务的处理能力。 一个用户去访问一个页面的请求过程,如上图。 数据传输时间 当你从浏览器输入网址,敲下回车,开始... 真实的用户场景请不要忽视数据传输时间,想想你给远方的朋友写信,信件需…

java基础之String的不可变性

目录 概述 String是如何实现不可变的 String为何设计成不可变的 1.缓存和性能优化 2.安全性 3.线程安全性 4.API设计和预测性能 概述 String类的不可变性意味着一旦创建了一个字符串对象,它的值就不能被修改。 String是如何实现不可变的 查看源码 public …

Django 6 后台与便签

1. 什么是后台管理 后台管理是网页管理员利用网页的后台程序管理和更新网站上网页的内容。各网站里网页内容更新就是通过网站管理员通过后台管理更新的。 2. 创建超级用户 1. python .\manage.py createsuperuser 2. 输入账号密码等信息 Username (leave blank to use syl…

Gromacs make_ndx建组问题

1. 选择特定分子或原子: gmx make_ndx -f input.gro -o output.ndx这将打开交互式界面,您可以在其中选择要包含在索引文件中的分子和原子。按照提示进行操作,选择适当的分组。 2. 手动创建索引文件: 您还可以手动创建一个文本文件…

佳能G3800彩色喷墨多功能一体打印机报5B00错误代码处理方法

5B00错误代码的含义 5B00错误代码是指佳能G3800打印机的“废墨仓已满”。这个废墨仓是打印机内部的一个部件,主要用于收集打印过程中产生的废墨。当废墨仓已满时,打印机就会报5B00错误代码。 佳能G3800彩色喷墨多功能一体打印机报5B00错误代码处理办法 …

【软件测试】接口自动化测试面试题及详细答案

📢专注于分享软件测试干货内容,欢迎点赞 👍 收藏 ⭐留言 📝 如有错误敬请指正!📢交流讨论:欢迎加入我们一起学习!📢资源分享:耗时200小时精选的「软件测试」资…

【每天五道题,轻松公务员】Day3:太阳常识

目录 专栏了解 ☞欢迎订阅☜ ★专栏亮点★ ◇专栏作者◇ 太阳常识 题目一 题目二 题目三 题目四 题目五 答案 补充扩展 专栏了解 ☞欢迎订阅☜ 欢迎订阅此专栏:考公务员,必订!https://blog.csdn.net/m0_73787047/category_1254…

ROS 系列学习教程(总目录)

ROSLearning 一、ROS概览 1.1 ROS简介 To be continued… 1.2 ROS安装 Ubuntu 安装 ROS 详细教程(以最后一个ROS1版本Noetic为例) 1.3 ROS Hello World ROS创建工作空间添加包并编译 ROS Hello World 1.4 ROS架构 ROS架构:文件系统 …

人生重开模拟器

前言: 人生重开模拟器是前段时间非常火的一个小游戏,接下来我们将一起学习使用c语言写一个简易版的人生重开模拟器。 网页版游戏: 人生重开模拟器 (ytecn.com) 1.实现一个简化版的人生重开模拟器 (1) 游戏开始的时…

前端开发加速器:十个VSCode插件精选

前端开发是一个不断发展的领域,随着技术的进步,工具也在不断更新。Visual Studio Code(VSCode)是前端开发者广泛使用的编辑器之一,得益于其强大的插件系统,可以帮助开发者提升工作效率。以下是十个对于前端…

【算法每日一练]-动态规划(保姆级教程 篇14) #三倍经验 #散步 #异或和 #抽奖概率

目录 今日知识点: 金字塔的正反dp两种方案,转移方程取决于dp的具体含义 取模实现循环走m步回到原点的方案 在统计上升子序列的时候使用最小结尾元素进行标记,一举两得 将亏本的概率转换各种情况的方案,然后统计亏本的情况的方…

BLE Mesh蓝牙组网技术详细解析之Foundation Model Layer基础模型层(七)

目录 一、什么是BLE Mesh Foundation Model Layer基础模型层? 二、模型 2.1 配置模型 2.2 健康模型 三、状态 3.1 Composition Data 四、资料获取 一、什么是BLE Mesh Foundation Model Layer基础模型层? BLE Mesh Foundation model Layer是蓝牙…

双击shutdown.bat关闭Tomcat报错:未设置关闭端口~

你们好,我是金金金。 场景 当我startup.bat启动tomcat之后,然后双击shutdown.bat关闭,结果报错了~ 排查 看报错信息很明显了,未配置关闭端口,突然想起来了我在安装的时候都选的是默认的配置,我还记得有这…

el-select 多选,选有一个未选择的选项

多选有未选择这个选项后。会出现一个情况,绑定的数据为[‘未选择’,‘cpu1’,‘cpu2’] 进行一个处理,选择(未选择)就清除(其它的选择),选择(cpu)就清除(未选…

NE555学习笔记-2024

实物图片 NE555引脚图 内部时序图 示列1,红外接收电路 红外接收电路的工作原理:在上述电路中,TSOP1738构成了该电路的主要组成部分,旨在检测来自任何来源的红外信号。这用于检测38 KHz范围的信号,因此命名为“TSOP173…

Android Matrix剪切clipPath缩放scale图片postTranslate圆形放大镜,Kotlin(2)

Android Matrix剪切clipPath缩放scale图片postTranslate圆形放大镜,Kotlin(2) 在 Android Matrix剪切clipPath缩放scale图片postTranslate圆形放大镜,Kotlin(1) Android Matrix剪切clipPath缩放scale图片po…