我们先来看看索引的定义:
索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。
接下来我们用一个例子来介绍怎么最大程度利用索引来加速我们的查询
1、先来看下要查询的sql
SELECT
wpo.id,
wco.collection_order_no,
wpo.picking_order_no,
wpo.add_time,
wpo.add_admin_name,
wpo.picking_time,
wpo.picking_admin_name,
wpo.status,
wpo.end_time,
wpo.sort AS isTop,
ceil((UNIX_TIMESTAMP(wpo.end_time)-UNIX_TIMESTAMP(wpo.picking_time))/60) useTime,
wpo.picking_type,
wco.warehouse_id,
sla.logic_area_name,
(SELECT count(DISTINCT (source_order_id))
FROM wod
WHERE warehouse_picking_order_id = wpo.id) AS orderNum,
(SELECT count(1)
FROM wod
WHERE warehouse_picking_order_id = wpo.id) AS skuNum
FROM wpo
LEFT JOIN wco ON wpo.warehouse_collection_order_id = wco.id
LEFT JOIN sla ON wpo.logic_area_id = sla.id
ORDER BY wpo.sort desc,wpo.status asc,wpo.add_time asc limit 10;
2、分析原因
联查总count数也就4万左右,并且上面s