【IT老齐044】慢SQL优化

【IT老齐044】慢SQL优化

表结构

CREATE TABLE `a`(
`id` int(11) NOT NULL AUTO_INCREMENT,
`seller_id` bigint(20) DEFAULT NULL,
`seller_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`gmt_create` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
)ENGINE=InnoDB AUTO_INCREMENT=32744 DEFAULT CHARSET=utf8;

CREATE TABLE `b`(
`id` int(11) NOT NULL AUTO_INCREMENT,
`seller_name` varchar(100) DEFAULT NULL.
`user_id` varchar(50) DEFAULT NULL,
`user_name` varchar(100) DEFAULT NULL,
`sales` bigint(20) DEFAULT NULL,
`gmt_create` varchar(3) DEFAULT NULL,
PRIMARY KEY (`id`)
)ENGINE=InnoDB AUTO INCREMENT=32744 DEFAULT CHARSET=utf8;

CREATE TABLE `c`(
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` varchar(50) DEFAULT NULL,
`order` id varchar(1ee) DEFAULT NULL
`state` bigint(20) DEFAULT NULL,
`gmt_create` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
)ENGINE=InnoDB AUTO INCREMENT=458731 DEFAULT CHARSET=utf8;

待优化SQL

select a.seller_id,a.seller_name,b.user_name,c.state
from a,b,c
where
a.seller_name=b.seller_name
and b.user id=c.user_id and cuser_id=17
and a.gmt_create BETWEEN DATE_ADD(NOW(),INTERVAL - 600 MINUTE)
AND DATE_ADD(NOW(),INTERVAL 600 MINUTE)
order by a.gmt_create;

优化过程

首次执行计划

选取驱动表

创建索引

二次执行计划

详细执行计划

分析

修改表结构

alter table a modify column gmt_create datetime;
alter table a modify column seller_name varchar(100)
alter table c modify column user_id bigint;

修改结果