性能分析之MySQL索引实战案例

2024-09-09 17:44

本文主要是介绍性能分析之MySQL索引实战案例,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

文章目录

  • 一、前言
  • 二、准备
  • 三、MySQL索引优化
  • 四、MySQL 索引知识回顾
  • 五、总结

一、前言

在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题,本文将一起从代码层去分析为什么没有建立索引?

  • 开源ERP项目地址:https://gitee.com/jishenghua/JSH_ERP

二、准备

打开IDEA找到登录请求资源路径位置,代码如:

@PostMapping(value = "/login")
public BaseResponseInfo login(@RequestParam(value = "loginName", required = false) String loginName,@RequestParam(value = "password", required = false) String password,HttpServletRequest request)throws Exception {

**步骤1:**找到登录 Controller 调 service 位置
在这里插入图片描述

**步骤2:**找到方法实现层:
在这里插入图片描述
解释:
从上面代码可以看出,用户登录传入用户名密码,代码根据用户去DAO层去查询是否有该用户;
在这里插入图片描述
说明:Dao层就是常规写法,没有什么特别地方,再跳转xml文件查看SQL是怎么写的:
在这里插入图片描述

说明:根据条件查询全部数据,既然这是登录接口传入的是用户名,那么应该在用户名处增加用户索引,这样查询能加快速度;

索引类似于字典目录,通过索引能快速找到响应数据;

在这里插入图片描述
解释:
如果查询为空或者查询结果为0表示数据库么有数据直接返回用户不存在,如果存在在往下走走;
在这里插入图片描述
解释:
如果上面都通过,这里又根据用户名密码查询数据库,这里作者为什么要查询两次数据库,既然上面已经查询完全可以在内存做判断;假如数据库有1000千用户数,每个用户登录都需要查询两次数据,也是一笔不小的开支;

三、MySQL索引优化

上面已经发现索引有问题,但是发现用户表数据很少,第一步先增加用户数据,再通过JMeter进行压测,造数据在性能测试中是常见的事件,这次造数据直接通过 java for 循环造数据,代码参考如下:


/*** @description: 注册用户* @author: 李文* @create: 2021-03-19 21:03**/
@RunWith(SpringRunner.class)
@SpringBootTest
public class LoginRegTest {@Resourceprivate UserMapper userMapper;@Testpublic void contextLoads() {try {for (int j = 0; j < 100; j++) {for (int i = 0; i < 1000; i++) {UserEx userEx = new UserEx();userEx.setLoginName(RandomUtil.randomString(10));userEx.setUsername(RandomUtil.randomString(8));userEx.setEmail(RandomUtil.randomInt(1, 1100) + "@7DGroup.com");userEx.setPassword(Tools.md5Encryp(BusinessConstants.USER_DEFAULT_PASSWORD));userEx.setIsystem(BusinessConstants.USER_NOT_SYSTEM);userEx.setIsmanager(BusinessConstants.USER_NOT_MANAGER);userEx.setStatus(BusinessConstants.USER_STATUS_NORMAL);userMapper.insert(userEx);}}} catch (NoSuchAlgorithmException e) {e.printStackTrace();}}
}

再次打开MySQL客户端输入如下SQL语句:

mysql> SELECT count(*) from `jsh_user`;
+----------+
| count(*) |
+----------+
| 333724   |
+----------+
1 行于数据集 (0.07)mysql> SELECT count(*) from `jsh_user`;EXPLAIN SELECT id,username,login_name, PASSWORD,position, department, email, phonenum, ismanager, isystem, STATUS, description, remark, tenant_id
FROMjsh_user
WHERE(login_name = "admin"AND PASSWORD = "e10adc3949ba59abbe56e057f20f883e"AND STATUS = 0);
+----------+
| count(*) |
+----------+
| 333724   |
+----------+
1 行于数据集 (0.05)+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1  | SIMPLE      | jsh_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 331551 | 0.10     | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 行于数据集 (0.06)mysql> 

截图如下:
在这里插入图片描述
为了减少性能消耗,这次都采用后台运行,把项目跑起来显示如下:
在这里插入图片描述
JMeter运行结果如下:
在这里插入图片描述

liwen@liwen123 hunhe % jmeter -n -t he1.jmx
Creating summariser <summary>
Created the tree successfully using he1.jmx
Starting standalone test @ Fri Mar 19 22:01:53 CST 2021 (1616162513949)
Waiting for possible Shutdown/StopTestNow/HeapDump/ThreadDump message on port 4445
summary +     44 in 00:00:06 =    7.8/s Avg:   534 Min:   472 Max:   910 Err:     0 (0.00%) Active: 9 Started: 9 Finished: 0
summary +    336 in 00:00:30 =   11.2/s Avg:  2129 Min:   537 Max:  3626 Err:     0 (0.00%) Active: 30 Started: 30 Finished: 0
summary =    380 in 00:00:36 =   10.7/s Avg:  1944 Min:   472 Max:  3626 Err:     0 (0.00%)

运行几分钟结果如下:
在这里插入图片描述
MySQL增加索引语句:

ALTER TABLE `jsh_user` ADD INDEX index_name ( `login_name` )

增加索引结果如下:

在这里插入图片描述
调优结果:

在这里插入图片描述
JMeter后台数据如下:

在这里插入图片描述
说明:
通过直接增加索引TPS明显增加;

四、MySQL 索引知识回顾

MySQL索引分为:
(1)主键索引 PRIMARY KEY:它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候同时创建主键索引。
(2) 唯一索引 UNIQUE:

ALTER TABLE table_name ADD UNIQUE (column)

(3) 普通索引 INDEX

ALTER TABLE table_name ADD INDEX index_name (column)

(4) 组合索引 INDEX

ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3)

(5) 全文索引 FULLTEXT

ALTER TABLE table_name ADD FULLTEXT (column)

查看索引:

mysql> show index from  jsh_user;
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table    | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| jsh_user | 0          | PRIMARY    | 1            | id          | A         | 3           | NULL     | NULL   |      | BTREE      |         |               | YES     |
| jsh_user | 1          | index_name | 1            | login_name  | A         | 331551      | NULL     | NULL   |      | BTREE      |         |               | YES     |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
2 行于数据集 (0.02)mysql>

删除索引:

ALTER TABLE  jsh_user DROP INDEX  index_name;

更多MySQL性能分析请参考《性能测试实战30讲》中的:

  • 《22丨MySQL:数据库级监控及常用计数器解析(上)》
  • 《23丨MySQL:数据库级监控及常用计数器解析(下)》

五、总结

性能优化是一个反复验证尝试的过程,但调优步骤是有逻辑。在这一节中通过观察代码步骤来跟踪并理解为什么在用户名上面增加索引,通过边压测边增加索引看到调优结果。

相关系列:

  • 性能工具之 JMeter ajax 简单登录案例实战
  • 性能工具之 JProfiler 简单登录案例分析实战

这篇关于性能分析之MySQL索引实战案例的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1151846

相关文章

Python通用唯一标识符模块uuid使用案例详解

《Python通用唯一标识符模块uuid使用案例详解》Pythonuuid模块用于生成128位全局唯一标识符,支持UUID1-5版本,适用于分布式系统、数据库主键等场景,需注意隐私、碰撞概率及存储优... 目录简介核心功能1. UUID版本2. UUID属性3. 命名空间使用场景1. 生成唯一标识符2. 数

浅谈mysql的not exists走不走索引

《浅谈mysql的notexists走不走索引》在MySQL中,​NOTEXISTS子句是否使用索引取决于子查询中关联字段是否建立了合适的索引,下面就来介绍一下mysql的notexists走不走索... 在mysql中,​NOT EXISTS子句是否使用索引取决于子查询中关联字段是否建立了合适的索引。以下

Linux系统性能检测命令详解

《Linux系统性能检测命令详解》本文介绍了Linux系统常用的监控命令(如top、vmstat、iostat、htop等)及其参数功能,涵盖进程状态、内存使用、磁盘I/O、系统负载等多维度资源监控,... 目录toppsuptimevmstatIOStatiotopslabtophtopdstatnmon

Python办公自动化实战之打造智能邮件发送工具

《Python办公自动化实战之打造智能邮件发送工具》在数字化办公场景中,邮件自动化是提升工作效率的关键技能,本文将演示如何使用Python的smtplib和email库构建一个支持图文混排,多附件,多... 目录前言一、基础配置:搭建邮件发送框架1.1 邮箱服务准备1.2 核心库导入1.3 基础发送函数二、

Android kotlin中 Channel 和 Flow 的区别和选择使用场景分析

《Androidkotlin中Channel和Flow的区别和选择使用场景分析》Kotlin协程中,Flow是冷数据流,按需触发,适合响应式数据处理;Channel是热数据流,持续发送,支持... 目录一、基本概念界定FlowChannel二、核心特性对比数据生产触发条件生产与消费的关系背压处理机制生命周期

PowerShell中15个提升运维效率关键命令实战指南

《PowerShell中15个提升运维效率关键命令实战指南》作为网络安全专业人员的必备技能,PowerShell在系统管理、日志分析、威胁检测和自动化响应方面展现出强大能力,下面我们就来看看15个提升... 目录一、PowerShell在网络安全中的战略价值二、网络安全关键场景命令实战1. 系统安全基线核查

Java通过驱动包(jar包)连接MySQL数据库的步骤总结及验证方式

《Java通过驱动包(jar包)连接MySQL数据库的步骤总结及验证方式》本文详细介绍如何使用Java通过JDBC连接MySQL数据库,包括下载驱动、配置Eclipse环境、检测数据库连接等关键步骤,... 目录一、下载驱动包二、放jar包三、检测数据库连接JavaJava 如何使用 JDBC 连接 mys

SQL中如何添加数据(常见方法及示例)

《SQL中如何添加数据(常见方法及示例)》SQL全称为StructuredQueryLanguage,是一种用于管理关系数据库的标准编程语言,下面给大家介绍SQL中如何添加数据,感兴趣的朋友一起看看吧... 目录在mysql中,有多种方法可以添加数据。以下是一些常见的方法及其示例。1. 使用INSERT I

Qt使用QSqlDatabase连接MySQL实现增删改查功能

《Qt使用QSqlDatabase连接MySQL实现增删改查功能》这篇文章主要为大家详细介绍了Qt如何使用QSqlDatabase连接MySQL实现增删改查功能,文中的示例代码讲解详细,感兴趣的小伙伴... 目录一、创建数据表二、连接mysql数据库三、封装成一个完整的轻量级 ORM 风格类3.1 表结构

PostgreSQL的扩展dict_int应用案例解析

《PostgreSQL的扩展dict_int应用案例解析》dict_int扩展为PostgreSQL提供了专业的整数文本处理能力,特别适合需要精确处理数字内容的搜索场景,本文给大家介绍PostgreS... 目录PostgreSQL的扩展dict_int一、扩展概述二、核心功能三、安装与启用四、字典配置方法