高性能mysql之explain实战

看一个sql,  这个sql比较长,多张表使用左外链接链接在一起,还涉及到分库,但是主要的是两张表是orderinfo表和orderdetail表以及临时表demp;orderinfo表与orderdetail表是一对多的关系,这两张表的数据量都是百万级;(sql中关键信息进行了处理,但不影响分析结果)

SELECT

busname,

buscode,

count(demp.orderno),

sum(demp.ticketcount),

sum(demp.pricesum),

sum(demp.servicepricesum),

sum(demp.totalpricesum),

sum(demp.payratsum)

FROM

(

SELECT DISTINCT

b.busname,

b.buscode,

o1.orderno,

o1.ticketcount,

IF (

o1.price IS NULL,

0.00,

o1.price

) pricesum,

IF (

o1.serviceprice IS NULL,

0.00,

o1.serviceprice

) servicepricesum,

IF (

o1.totalprice IS NULL,

0.00,

o1.totalprice

) AS totalpricesum,

ROUND(

(

CASE

WHEN o1.paywayname IS NULL THEN

o1.totalprice * (

(

SELECT

payratepercentage

FROM

nmd_wt.getwaysettlement

WHERE

NAME LIKE '%%'

LIMIT 1

) / 100

)

WHEN o1.gatewayid IS NULL THEN

o1.totalprice * (0.5 / 100)

WHEN o1.totalprice * (gm.payratepercentage / 100) < 0.2 THEN

0.2

ELSE

o1.totalprice * (gm.payratepercentage / 100)

END

),

2

) AS payratsum

FROM

nmd_u1wt.orderinfo o1

LEFT JOIN nmd_wt.businfo b ON b.id = o1.stationorgid

LEFT JOIN nmd_u1wt.orderdetail od1 ON od1.orderno = o1.orderno

LEFT JOIN nmd_wt.getwaysettlement gm ON gm.id = o1.gatewayid

WHERE

1 = 1

AND o1. STATUS = 1

AND TO_DAYS(od1.departdate) - TO_DAYS("2018-02-08") >= 0

AND TO_DAYS(od1.departdate) - TO_DAYS("2018-02-23") <= 0

AND ( SELECT paramvalue FROM nmd_wt.platparameterconfig

WHERE CODE = '052') LIKE CONCAT( '%\'', ifnull(o1.agentcode, ''), '\'%')

UNION ALL

SELECT DISTINCT

b.busname,

b.buscode,

o1.orderno,

o1.ticketcount,

IF (

o1.price IS NULL,

0.00,

o1.price

) pricesum,

IF (

o1.serviceprice IS NULL,

0.00,

o1.serviceprice

) servicepricesum,

IF (

o1.totalprice IS NULL,

0.00,

o1.totalprice

) AS totalpricesum,

ROUND(

(

CASE

WHEN o1.paywayname IS NULL THEN

o1.totalprice * (

(

SELECT

payratepercentage

FROM

nmd_wt.getwaysettlement

WHERE

NAME LIKE '%%'

LIMIT 1

) / 100

)

WHEN o1.gatewayid IS NULL THEN

o1.totalprice * (0.5 / 100)

WHEN o1.totalprice * (gm.payratepercentage / 100) < 0.2 THEN

0.2

ELSE

o1.totalprice * (gm.payratepercentage / 100)

END

),

2

) AS payratsum

FROM

nmd_u2wt.orderinfo o1

LEFT JOIN nmd_wt.businfo b ON b.id = o1.stationorgid

LEFT JOIN nmd_u2wt.orderdetail od1 ON od1.orderno = o1.orderno

LEFT JOIN nmd_wt.getwaysettlement gm ON gm.id = o1.gatewayid

WHERE

1 = 1

AND o1. STATUS = 1

AND od1.departdate BETWEEN "2018-02-08" and "2018-02-23"

AND (

SELECT

paramvalue

FROM

nmd_wt.platparameterconfig

WHERE

CODE = '052'

) LIKE CONCAT(

'%\'',

ifnull(o1.agentcode, ''),

'\'%'

)

) demp

GROUP BY

demp.busname,

demp.buscode

ORDER BY

demp.buscode

通过explain发现两个分库的orderinfo都进行了全表扫描,导致查询速度极慢,严重影响效率,

o1 左外链接 od1 where 条件中 od1的departdate 没有加索引, 导致o1 全表扫描 ;

另外 , 关于日期的条件,全都使用的 TO_DAYS() 函数, 使用这个函数后 即使有索引, 也不会使用;

在本例中,orderinfo表为主表,orderdetail为链接表, orderdetail的索引状态直接影响了主表的查询,值得注意!!

对于数据库中date和datetime类型的数据,推荐使用 between and  写一个例子, date类型:between '2018-04-03' and '2018-04-03';   datetime 类型:between '2018-04-03 00:00:00' and '2018-04-03 23:59:59';

另外where条件中字段的数据类型要与数据库中定义的数据类型一致,否则也不会走索引,例如本例中orderno字段时varchar类型,如果where条件拼接时使用了 1234567 这样的int形式,是不会使用索引的;