当前位置： 首页 > article >正文

【Mysql】我在广州学Mysql 系列—— 性能优化相关例题

article 2025/2/27 15:11:36

ℹ️大家好，我是练小杰，时间过得真快，还有2天，2025年2月份就结束了！！😆

本文是针对Mysql数据库中有关性能优化的相关示例，通过本文的学习可以深入了解性能优化的各类命令！！！🙏

前情回顾: 【Mysql 性能优化详解】
数据库专栏：👉【数据库专栏】【Mysql练习题】

主页：👉【练小杰的CSDN】

真正的平静是内心的平静~~

在这里插入图片描述

性能优化例题

    • 主页：👉【[练小杰的CSDN](https://blog.csdn.net/weixin_55767624?spm=1011.2415.3001.5343)】
    • 真正的平静是内心的平静~~
  • 前言
  • 使用EXPLAIN命令
    • 使用EXPLAIN语句来分析一个查询语句
  • 对比查询中不使用索引和使用索引
    • 未使用索引时的查询情况
    • 使用索引时的查询情况
  • LIKE关键字
    • 分析"查询以`x`结尾的水果名字" 命令
    • 分析"查询以`x`开头的水果名字" 命令
  • 多列索引
    • 创建多列索引
    • 验证多列索引的使用情况
  • or 关键字查询优化
    • 分析水果名字为apple或id号为101的情况
    • 分析水果名字为apple或id号为l2的情况
  • JOIN 命令查询
    • members表和members_detail表数据
    • 查询结果的几种情况
  • 优化插入命令
    • 新建会员信息表和会员组信息表
  • ANALYZE TABLE 命令分析表

在这里插入图片描述

前言

记得学习本文之前，先了解博客👉【Mysql 性能优化详解】，再进行本博客的各类操作噢！！！😆

使用EXPLAIN命令

为了方便理解EXPLAIN命令，首先在数据库中创建数据表并插入数据，以下是详细命令：

  • 创建数据表fruits
CREATE TABLE fruits
(
f_id    char(10)     NOT NULL,
s_id    INT        	NOT NULL,
f_name  char(255)  	NOT NULL,
f_price decimal(8,2)  	NOT NULL,
PRIMARY KEY(f_id)   
);
  • 在表里插入数据
INSERT INTO fruits (f_id, s_id, f_name, f_price)    
   VALUES('a1', 101,'apple',5.2),
     ('b1',101,'blackberry', 10.2),
     ('bs1',102,'orange', 11.2),
     ('bs2',105,'melon',8.2),
     ('t1',102,'banana', 10.3),
     ('t2',102,'grape', 5.3),
     ('o2',103,'coconut', 9.2),
     ('c0',101,'cherry', 3.2),
     ('a2',103, 'apricot',2.2),
     ('l2',104,'lemon', 6.4),
     ('b2',104,'berry', 7.6),
     ('m1',106,'mango', 15.6),
     ('m2',105,'xbabay', 2.6),
     ('t4',107,'xbababa', 3.6),
     ('m3',105,'xxtt', 11.6),
     ('b5',107,'xxxx', 3.6);

使用EXPLAIN语句来分析一个查询语句

EXPLAIN SELECT * FROM fruits;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | fruits | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

对比查询中不使用索引和使用索引

以下命令是查询语句中不使用索引和使用索引的对比。

未使用索引时的查询情况

EXPLAIN SELECT * FROM fruits WHERE f_name='apple'; 
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | fruits | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   16 |    10.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

使用索引时的查询情况

创建索引并使用EXPLAIN 命令分析使用索引时的查询情况

CREATE INDEX index_name ON fruits(f_name); 
	Query OK, 0 rows affected (0.04 sec)
	Records: 0  Duplicates: 0  Warnings: 0

      
EXPLAIN SELECT * FROM fruits WHERE f_name='apple';  
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | fruits | NULL       | ref  | index_name    | index_name | 1020    | const |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)

LIKE关键字

查询语句中使用LIKE关键字，并且匹配的字符串中含有‘%’符，EXPLAIN语句执行如下：

分析"查询以x结尾的水果名字" 命令

EXPLAIN SELECT * FROM fruits WHERE f_name like '%x';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | fruits | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   16 |    11.11 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

分析"查询以x开头的水果名字" 命令

EXPLAIN SELECT * FROM fruits WHERE f_name like 'x%';
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | fruits | NULL       | range | index_name    | index_name | 1020    | NULL |    4 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)

多列索引

在表fruits中f_id、f_price字段创建多列索引，验证多列索引的使用情况。

创建多列索引

CREATE INDEX index_id_price ON fruits(f_id, f_price);
	Query OK, 0 rows affected (0.03 sec)
	Records: 0  Duplicates: 0  Warnings: 0

验证多列索引的使用情况

EXPLAIN SELECT * FROM fruits WHERE f_id='l2';
+----+-------------+--------+------------+-------+------------------------+---------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys          | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+-------+------------------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | fruits | NULL       | const | PRIMARY,index_id_price | PRIMARY | 40      | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+------------------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)


EXPLAIN SELECT * FROM fruits WHERE f_price=5.2;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | fruits | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   16 |    10.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

or 关键字查询优化

查询语句使用OR关键字的情况

分析水果名字为apple或id号为101的情况

EXPLAIN SELECT * FROM fruits WHERE f_name='apple' or s_id=101 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: fruits
   partitions: NULL
         type: ALL
possible_keys: index_name
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16
     filtered: 15.62
        Extra: Using where
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified

分析水果名字为apple或id号为l2的情况

EXPLAIN SELECT * FROM fruits WHERE f_name='apple' or f_id='l2' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: fruits
   partitions: NULL
         type: index_merge
possible_keys: PRIMARY,index_name,index_id_price
          key: index_name,PRIMARY
      key_len: 1020,40
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using union(index_name,PRIMARY); Using where
1 row in set, 1 warning (0.01 sec)

ERROR:
No query specified

JOIN 命令查询

为了方便观察，建立会员表（members），主要用来存储会员登录认证信息。
以下命令会返回members表中的所有记录，并将每个members记录与members_detail表中对应的记录通过LEFT JOIN进行连接。

CREATE TABLE members (
  Id int(11) NOT NULL AUTO_INCREMENT,
  username varchar(255) DEFAULT NULL ,
  password varchar(255) DEFAULT NULL ,
  last_login_time datetime DEFAULT NULL ,
  last_login_ip varchar(255) DEFAULT NULL ,
  PRIMARY KEY (Id)
) ;


CREATE TABLE members_detail (
  member_id int(11) NOT NULL DEFAULT 0,
  address varchar(255) DEFAULT NULL ,
  telephone varchar(16) DEFAULT NULL ,
  description text
) ;

SELECT * FROM members LEFT JOIN members_detail ON members.id=members_detail.member_id;

members表和members_detail表数据

  • members表在这里插入图片描述
  • members_detail表在这里插入图片描述
  • 查询结果： 在这里插入图片描述

查询结果的几种情况

  • 一对一关系：

每个members记录对应一个members_detail记录，结果中包含所有members字段和对应的members_detail字段。

  • 无对应关系：

members记录在members_detail中没有对应记录时，members_detail字段为NULL。

  • 一对多关系：

members_detail中有多条对应记录时，members记录会重复显示，每条对应一个members_detail记录。

优化插入命令

新建会员信息表和会员组信息表

CREATE TABLE vip(
  Id int(11) NOT NULL AUTO_INCREMENT,
  username varchar(255) DEFAULT NULL,
  password varchar(255) DEFAULT NULL,
  groupId INT(11) DEFAULT 0,
  PRIMARY KEY (Id)
) ;


CREATE TABLE vip_group (
  Id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(255) DEFAULT NULL,
  remark varchar(255) DEFAULT NULL,
  PRIMARY KEY (Id)
) ;

CREATE TABLE temp_vip (
  Id int(11) NOT NULL AUTO_INCREMENT,
  user_name varchar(255) DEFAULT NULL,
  group_name varchar(255) DEFAULT NULL,
  group_remark varchar(255) DEFAULT NULL,
  PRIMARY KEY (Id)
);

INSERT INTO temp_vip (user_name, group_name, group_remark) VALUES 
('lxj', 'lian', 'This is my remark');

SELECT v.username,g.name,g.remark 
 FROM vip as v ,vip_group as g
 WHERE v.groupId=g.Id;

执行INSERT INTO命令后，temp_vip表中会插入一条新记录。
通过select语句查询的是获取vip表中每个用户的用户名，以及他们所属的组的名称和备注，前提是vip表的groupId与vip_group表的Id匹配。

ANALYZE TABLE 命令分析表

该命令用于分析 fruits 表的结构和统计信息，以优化查询性能。具体来说，ANALYZE TABLE 会更新表的统计信息，比如索引的使用情况、行数等，帮助 MySQL 查询优化器做出更好的决策。
使用ANALYZE TABLE来分析fruits表，执行的语句及结果如下：

ANALYZE TABLE fruits;
+------------+---------+----------+----------+
| Table      | Op      | Msg_type | Msg_text |
+------------+---------+----------+----------+
| lxj.fruits | analyze | status   | OK       |
+------------+---------+----------+----------+
1 row in set (0.01 sec)
  • Table: 被分析的表名。在这里是 lxj.fruits，其中 lxj 是数据库名，fruits 是表名。
  • Op: 操作类型。这里是 analyze，表示执行的是分析操作。
  • Msg_type: 消息类型。这里是 status，表示返回的是状态信息。
  • Msg_text: 消息文本。这里是 OK，表示分析操作成功完成。

本文有关Mysql数据库性能优化例题已经讲完了， 明天再见啦👋
主页：【练小杰的CSDN】😆
ℹ️欢迎各位在评论区踊跃讨论，积极提出问题，解决困惑！！！
⚠️若博客里的内容有问题，欢迎指正，我会及时修改！！


http://www.kler.cn/a/563291.html

相关文章：

  • seacmsv9注入管理员账号密码+orderby+limi
  • 贪心算法精品题
  • MySQL--聚集索引、辅助索引、回表查询和覆盖索引的原理
  • 在 macOS 系统上安装 kubectl
  • MATLAB基础应用精讲-【数模应用】牛顿迭代法（附MATLAB、C++、R语言和python代码实现）
  • 【环境配置】maven,mysql,node.js,vue的快速配置与上手
  • 解锁养生密码，拥抱健康生活
  • 【代码解读】阿里最新开源视频生成模型 Wan 2.1 实现解析
  • 锂电池保护板测试仪：电池安全的守护者与创新驱动力
  • JUC并发—14.Future模式和异步编程分析二
  • go-zero中定时任务的用法
  • 神经网络参数量计算
  • 云图库平台（五）——后端图片模块开发
  • 2025/2/25，字节跳动后端开发一面面经
  • 3D格式转换工具HOOPS Exchange在PMI处理中的关键作用与优势解析
  • 互联网核心技术概念笔记
  • NLP学习记录十：多头注意力
  • 【react】react Native
  • [免单统计]
  • 使用前端 html css 和js 开发一个AI智能平台官网模板-前端静态页面项目