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; |
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· DeepSeek “源神”启动!「GitHub 热点速览」
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· NetPad:一个.NET开源、跨平台的C#编辑器