sql 优化

来源：互联网发布：java视频播放器全屏编辑：程序博客网时间：2024/06/03 05:23

SELECT
g.*, p.product_id AS product_id,
p.specs AS product_specs,
b. NAME AS brand_name,
t. NAME AS type_name,
c. NAME AS cat_name
FROM
product p
LEFT JOIN goods g ON p.goods_id = g.goods_id
LEFT JOIN goods_cat c ON g.cat_id = c.cat_id
LEFT JOIN brand b ON g.brand_id = b.brand_id
AND b.disabled = 0
LEFT JOIN es_goods_type t ON g.type_id = t.type_id
WHERE
g.market_enable = 1
AND g.disabled = 0
AND g.sn LIKE 'SAB2107839%'
ORDER BY

goods_id DESC

1，查询条件sn加索引 create index idx_product on product (sn);

2,like 'sn%' 不能出现 like '%sn%' , 否则sn加的索引就不起作用

3，最好不要出现 g.*，需要什么字段，查询出来

4,做更新语句的时候，最好保持sql的原子性，不要查询出来再加减，拼接。考虑到并发情况下会有脏数据出现，

建议写成：

直接加减：

update cart set num=num+? where product_id=? （整型，例如购物车的数据等）

直接拼接：

String sql="update product p set p.name=concat(p.name,'"+spec+"') where p.goods_id>=400108 and p.goods_id<=403855 and p.sn='"+sn+"'";

5，sql替换

UPDATe goods SET `intro`=REPLACE(`intro`,'pl201604/beyotime','pl201605/beyotime') WHERE goods_id >=673951 AND goods_id <= 674885

sql拼接

update es_product set sn=concat(sn,'-',specs) where goods_id >=680138 and goods_id<=691447

0 0