优雅的关闭mysql_mysql优化,优雅的sql

CREATE TABLE `orders` (

`order_no` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '',

`uuid` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT 'id',

`status` tinyint(4) NOT NULL,

`money` decimal(9,2) unsigned zerofill NOT NULL DEFAULT '0000000.00' COMMENT '金额',

`remark` varchar(1000) COLLATE utf8_bin DEFAULT NULL COMMENT '备注',

`type` tinyint(4) NOT NULL COMMENT '类型',

`end_time` timestamp NULL DEFAULT NULL COMMENT '结束时间',

`create_time` timestamp NULL DEFAULT NULL COMMENT '创建时间',

`update_time` timestamp NULL DEFAULT NULL COMMENT '更新时间',

PRIMARY KEY (`order_no`),

KEY `orderid` (`order_no`,`uuid`,`create_time`) USING BTREE

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

需求:

第一次写:

SELECT

COUNT( 1 ) allOrder,

t1.allInSuccess ,

t1.allInSuccess/count(1) as successRate,

IFNULL(t2.allInAppeal,0)/count(1) as appealRate

FROM

orders AS o

LEFT JOIN (

SELECT

COUNT( 1 ) AS allInSuccess,

orders.uuid

FROM

orders

WHERE

orders.`status` = 1

AND orders.uuid = 'w033yinct82lq5rmlfary5dozz0yjy9k'

AND orders.create_time BETWEEN '2019-08-01'

AND '2019-08-30'

) AS t1 ON o.uuid = t1.uuid

LEFT JOIN (

SELECT

COUNT( 1 ) AS allInAppeal,

orders.uuid

FROM

orders

WHERE

orders.`status` = 3

AND orders.uuid = 'w033yinct82lq5rmlfary5dozz0yjy9k'

AND orders.create_time BETWEEN '2019-08-01'

AND '2019-08-30'

) AS t2 ON o.helper_uuid = t2.helper_uuid

WHERE

o.uuid = 'w033yinct82lq5rmlfary5dozz0yjy9k'

AND o.create_time BETWEEN '2019-08-01'

AND '2019-08-30'

分析如下:

结果如下:

优化sql:

SELECT

SUM( o.`status` IN ( '1', '2', '3', '4', '5' ) ) AS allOrder,

SUM( o.`status` = 1 ) AS allInSuccess,

SUM( o.`status` = 3 ) AS allInAppeal

FROM

zzsw_orders AS o

WHERE

o.helper_uuid = 'w033yinct82lq5rmlfary5dozz0yjy9k'

AND o.create_time BETWEEN '2019-08-01'

AND '2019-08-30'

分析:

结果如下:

优化完成???

本文章为原创,未经允许不得转载