优化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;
