MySQL 优化步骤

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
优化SQL步骤
 
1)查看SQL执行频率
2)定位低效率的执行SQL
3)explain分析执行计划
4)show profile分析SQL
5)trace分析优化器执行计划
 
 
 
1)查看SQL执行频率: 判断数据库是以插入为主  还是以查询为主
 
show status like 'Com_______';          // __ 站位7个字符
show global status like 'Com_______';
show global status like 'Innodb_rows_%';
 
show session status;
show global status;
 
use demo_02;
update goods_innodb set name = 'Meta30';
show global status like 'Innodb_rows_%';
 
2)定位低效率的执行SQL
 1、慢查询日志:
 2、show processlist;
  
3)explain分析执行计划
explain --select 语句;
 
 
CREATE TABLE `t_role` (
`id` varchar(32) NOT NULL,
`role_name` varchar(255) DEFAULT NULL,
`role_code` varchar(255) DEFAULT NULL,
`description` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_role_name` (`role_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE `t_user` (
`id` varchar(32) NOT NULL,
`username` varchar(45) NOT NULL,
`password` varchar(96) NOT NULL,
`name` varchar(45) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_user_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE `user_role` (
`id` int(11) NOT NULL auto_increment ,
`user_id` varchar(32) DEFAULT NULL,
`role_id` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_ur_user_id` (`user_id`),
KEY `fk_ur_role_id` (`role_id`),
CONSTRAINT `fk_ur_role_id` FOREIGN KEY (`role_id`) REFERENCES `t_role` (`id`) ON
DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_ur_user_id` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`) ON
DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
insert into `t_user` (`id`, `username`, `password`, `name`)
values('1','super','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','
超级管理员');
insert into `t_user` (`id`, `username`, `password`, `name`)
values('2','admin','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','
系统管理员');
insert into `t_user` (`id`, `username`, `password`, `name`)
values('3','itcast','$2a$10$8qmaHgUFUAmPR5pOuWhYWOr291WJYjHelUlYn07k5ELF8ZCrW0Cui',
'test02');
insert into `t_user` (`id`, `username`, `password`, `name`)
values('4','stu1','$2a$10$pLtt2KDAFpwTWLjNsmTEi.oU1yOZyIn9XkziK/y/spH5rftCpUMZa','学
生1');
insert into `t_user` (`id`, `username`, `password`, `name`)
values('5','stu2','$2a$10$nxPKkYSez7uz2YQYUnwhR.z57km3yqKn3Hr/p1FR6ZKgc18u.Tvqm','学
生2');
insert into `t_user` (`id`, `username`, `password`, `name`)
values('6','t1','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','老师
1');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('5','学
生','student','学生');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('7','老
师','teacher','老师');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('8','教
学管理员','teachmanager','教学管理员');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('9','管
理员','admin','管理员');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('10','超
级管理员','super','超级管理员');
 
 
INSERT INTO user_role(id,user_id,role_id) VALUES(NULL, '1', '5'),(NULL, '1', '7'),
(NULL, '2', '8'),(NULL, '3', '9'),(NULL, '4', '8'),(NULL, '5', '10') ;
 
 
explain 之 id
id 相同表示加载表的顺序是从上到下。
id 不同id值越大,优先级越高,越先被执行
id 有相同,也有不同,同时存在。id相同的可以认为是一组,从上往下顺序执行;在所有的组中,id的值越
大,优先级越高,越先执行。
 
explain 之 select_type
 
select_type 含义
SIMPLE      简单的select查询,查询中不包含子查询或者UNION
PRIMARY     查询中若包含任何复杂的子查询,最外层查询标记为该标识
SUBQUERY    在SELECT WHERE 列表中包含了子查询
DERIVED
FROM 列表中包含的子查询,被标记为 DERIVED(衍生) MYSQL会递归执行这些子查
询,把结果放在临时表中
UNION
若第二个SELECT出现在UNION之后,则标记为UNION ; 若UNION包含在FROM子句的子
查询中,外层SELECT将被标记为 : DERIVED
UNION RESULT    从UNION表获取结果的SELECT
 
 
 
4)show profile分析SQL
show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了
 
select @@have_profiling;
select @@profiling;
set profiling=1;     //开启 profiling
 
 
CREATE TABLE `tb_item` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '商品id',
`title` varchar(100) NOT NULL COMMENT '商品标题',
`price` decimal(20,2) NOT NULL COMMENT '商品价格,单位为:元',
`num` int(10) NOT NULL COMMENT '库存数量',
`categoryid` bigint(10) NOT NULL COMMENT '所属类目,叶子类目',
`status` varchar(1) DEFAULT NULL COMMENT '商品状态,1-正常,2-下架,3-删除',
`sellerid` varchar(50) DEFAULT NULL COMMENT '商家ID',
`createtime` datetime DEFAULT NULL COMMENT '创建时间',
`updatetime` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='商品表';
 
 
执行命令:
show databases;
use db01;
show tables;
select * from tb_item where id < 5;
select count(*) from tb_item;
 
然后:  show profiles;
 
通过show profile for query query_id 语句可以查看到该SQL执行过程中每个线程的状态和消耗的时间:
show profile for query 6;
show profile all for query 6;
show profile cpu for query 6;
 
 
 
5)trace分析优化器执行计划
 
SET optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;
 
select * from tb_item where id < 4;
select * from information_schema.optimizer_trace\G;

  

posted @   walkersss  阅读(73)  评论(0编辑  收藏  举报
编辑推荐:
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· DeepSeek “源神”启动!「GitHub 热点速览」
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· NetPad:一个.NET开源、跨平台的C#编辑器
点击右上角即可分享
微信分享提示