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