从代码看就是查数据库很慢，于是从 sql 开始经过索引，到代码结束
storage_record：一百万条数据，pv_release_base：五百万条数据
- 通过 explain 发现该 sql 对 storage_record 进行了全表扫描
添加索引后（goods_id,user_id）的索引后: 空值查询[4s -> 0.054s], 有值查询[分钟级别 -> 5s]- 对 sql 进行拆解，充分利用主键索引，通过先查 id 再 in 的方式: 空值查询[0.054s], 有值查询[5s -> 0.115s]
参考
参考书籍 -《数据库索引设计与优化》
参考博客
原始 sql
-- 空行：4s，有数据：分钟级别
SELECT
sr.order_no AS orderNo,
sr.updated_time AS updatedTime,
sr.cert,
sr.real_name AS realName,
sr.remark,
sr.delivery_time AS deliveryTime,
sr.id,
sr.goods_name AS goodsName,
sr.address_code AS addressCode,
sr.created_time AS createTime,
sr.receipt_address AS receiptAddress,
sr.receiver,
sr.count,
sr.goods_id AS goodsId,
sr.shop_name AS shopName,
sr.record_type AS recordType,
sr.third_storage_id AS thirdStorageId,
sr.shop_id AS shopId,
sr.in_time AS inTime,
sr.storage_name AS storageName,
sr.out_type AS outType,
sr.user_id AS userId,
sr.receiver_phone AS receiverPhone,
sr.order_id AS orderId,
sr.account,
IF
(
TIMESTAMPDIFF( DAY, DATE_FORMAT( DATE_SUB( IFNULL( prb.new_c_time, prb.c_time ), INTERVAL 0 DAY ), '%Y-%m-%d' ), now( ) ) > 14,
14,
TIMESTAMPDIFF( DAY, DATE_FORMAT( DATE_SUB( IFNULL( prb.new_c_time, prb.c_time ), INTERVAL 0 DAY ), '%Y-%m-%d' ), now( ) )) AS storageDays
FROM
storage_record AS sr
LEFT JOIN ( SELECT c_time, new_c_time, order_id FROM pv_release_base WHERE user_id = '42023' ) AS prb ON sr.order_id = prb.order_id
WHERE
sr.deleted = 0
AND sr.record_type = '1'
AND sr.goods_id = '24401329'
AND sr.user_id = '118837'
ORDER BY
sr.created_time DESCSELECT
sr.order_no AS orderNo,
sr.updated_time AS updatedTime,
sr.cert,
sr.real_name AS realName,
sr.remark,
sr.delivery_time AS deliveryTime,
sr.id,
sr.goods_name AS goodsName,
sr.address_code AS addressCode,
sr.created_time AS createTime,
sr.receipt_address AS receiptAddress,
sr.receiver,
sr.count,
sr.goods_id AS goodsId,
sr.shop_name AS shopName,
sr.record_type AS recordType,
sr.third_storage_id AS thirdStorageId,
sr.shop_id AS shopId,
sr.in_time AS inTime,
sr.storage_name AS storageName,
sr.out_type AS outType,
sr.user_id AS userId,
sr.receiver_phone AS receiverPhone,
sr.order_id AS orderId,
sr.account,
IF
(
TIMESTAMPDIFF( DAY, DATE_FORMAT( DATE_SUB( IFNULL( prb.new_c_time, prb.c_time ), INTERVAL 0 DAY ), '%Y-%m-%d' ), now( ) ) > 14,
14,
TIMESTAMPDIFF( DAY, DATE_FORMAT( DATE_SUB( IFNULL( prb.new_c_time, prb.c_time ), INTERVAL 0 DAY ), '%Y-%m-%d' ), now( ) )) AS storageDays
FROM
storage_record AS sr
LEFT JOIN ( SELECT c_time, new_c_time, order_id FROM pv_release_base WHERE user_id = '42023' ) AS prb ON sr.order_id = prb.order_id
WHERE
sr.deleted = 0
AND sr.record_type = '1'
AND sr.goods_id = '24401329'
AND sr.user_id = '118837'
ORDER BY
sr.created_time DESC
查看执行计划、建立索引
explain sql
一眼问题：sr type = ALL ==> storage_record 表 没索引
-- 查看where表达式根据值离散程度建立索引（大大降低了查不到数据的速度）
ALTER TABLE storage_record ADD INDEX group_warehousing(user_id,goods_id) COMMENT '入仓记录索引';
拆分 sql
-- 拆分sql前：空行：0.054s，有数据：5.5s
SELECT
sr.order_no AS orderNo,
sr.updated_time AS updatedTime,
sr.cert,
sr.real_name AS realName,
sr.remark,
sr.delivery_time AS deliveryTime,
sr.id,
sr.goods_name AS goodsName,
sr.address_code AS addressCode,
sr.created_time AS createTime,
sr.receipt_address AS receiptAddress,
sr.receiver,
sr.count,
sr.goods_id AS goodsId,
sr.shop_name AS shopName,
sr.record_type AS recordType,
sr.third_storage_id AS thirdStorageId,
sr.shop_id AS shopId,
sr.in_time AS inTime,
sr.storage_name AS storageName,
sr.out_type AS outType,
sr.user_id AS userId,
sr.receiver_phone AS receiverPhone,
sr.order_id AS orderId,
sr.account
FROM
storage_record AS sr
WHERE
sr.goods_id = '24401329'
AND sr.user_id = '118837'
AND sr.record_type = '1'
AND sr.deleted = 0
ORDER BY
sr.created_time DESClimit 0,10;
-- 对sql进行拆解，充分利用主键索引，通过先查id再in的方式: 空值查询[0.054s], 有值查询[5s -> 0.115s ]
-- 对连表数据进行拆分 0.041s + 0.039s + 0.035s = 0.115s （稳定）
-- 0.041
SELECT
idFROM
storage_record
WHERE
goods_id = '24401329'
AND user_id = '118837'
AND record_type = '1'
AND deleted = 0
ORDER BY created_time DESClimit 0,10;
-- 0.039s
SELECT
sr.order_id,
sr.order_no AS orderNo,
sr.updated_time AS updatedTime,
sr.cert,
sr.real_name AS realName,
sr.remark,
sr.delivery_time AS deliveryTime,
sr.id,
sr.goods_name AS goodsName,
sr.address_code AS addressCode,
sr.created_time AS createTime,
sr.receipt_address AS receiptAddress,
sr.receiver,
sr.count,
sr.goods_id AS goodsId,
sr.shop_name AS shopName,
sr.record_type AS recordType,
sr.third_storage_id AS thirdStorageId,
sr.shop_id AS shopId,
sr.in_time AS inTime,
sr.storage_name AS storageName,
sr.out_type AS outType,
sr.user_id AS userId,
sr.receiver_phone AS receiverPhone,
sr.order_id AS orderId,
sr.account
FROM
storage_record AS sr
where sr.id in (
'983713',
'983715',
'983721',
'983788',
'983793',
'983795',
'984100',
'984101',
'984102',
'984103'
);
-- 0.035s
SELECT order_id,IF
(
TIMESTAMPDIFF( DAY, DATE_FORMAT( DATE_SUB( IFNULL( new_c_time, c_time ), INTERVAL 0 DAY ), '%Y-%m-%d' ), now( ) ) > 14,
14,
TIMESTAMPDIFF( DAY, DATE_FORMAT( DATE_SUB( IFNULL( new_c_time, c_time ), INTERVAL 0 DAY ), '%Y-%m-%d' ), now( ) )) AS storageDays FROM pv_release_base WHERE order_id in (
'14288049',
'14288051',
'14288062',
'14288174',
'14288179',
'14288181',
'14322725',
'14322726',
'14322727',
'14322728'
) and user_id in (
'118837',
'118837',
'118837',
'118837',
'118837',
'118837',
'118837',
'118837',
'118837',
'118837'
);
代码修改
......略
欢迎来到这里！
我们正在构建一个小众社区，大家在这里相互信任，以平等 • 自由 • 奔放的价值观进行分享交流。最终，希望大家能够找到与自己志同道合的伙伴，共同成长。
注册 关于