MySQL 优化步骤

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

  

上一篇:dayo6_05 总结


下一篇:spa项目开发之tab页实现