ℹ️大家好，我是练小杰，时间过得真快，还有2天，2025年2月份就结束了！！😆
本文是针对Mysql数据库中有关性能优化的相关示例，通过本文的学习可以深入了解性能优化的各类命令！！！🙏
前情回顾: 【Mysql 性能优化详解】
数据库专栏：👉【数据库专栏】【Mysql练习题】主页：👉【练小杰的CSDN】
真正的平静是内心的平静~~
前言
记得学习本文之前，先了解博客👉【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】😆
ℹ️欢迎各位在评论区踊跃讨论，积极提出问题，解决困惑！！！
⚠️若博客里的内容有问题，欢迎指正，我会及时修改！！