性能分析之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索引实战案例的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---