Techyou labs
文章RSS
评论RSS
登录
真正的爱应该超越生命的长度,心灵的宽度,灵魂的深度
搜索
关于作者
文章分类
Default
Linux/Unix
Database
Cloud
Networking
Security
Programming
最新文章
deepin-wine6-stable下TIM悄悄崩溃问题
openwrt 设置ipv6地址分配
Redis 实战篇:巧用数据类型实现亿级数据统计
Cilium 容器网络的落地实践
Below:Facebook 开源的 Linux 系统资源监视器
How to do nat based on port number in stateless nat
LetsEncrypt根证书到期导致系统更新失败
转:Lima:Docker Desktop for Mac 的免费开源且自由的替代品
[转]Qunar 容器平台网络之道:Calico
deepin-wine-qq在ubuntu/linuxmint下常见问题
最新评论
renothing: 二次反向代理跟你应用程序得处理时间有关系吧?尤其是...
二次反向代理性能很差,怎么优化的?: 我也用nginx 做了个二次反向代理,但是并发连3...
hostyep: 交换链接么?目前每天保持30个左右对口IP,每月都...
yzhkpli: error while loading share...
美肤宝: 感谢分享。。。
lq: 嗯 喜欢弄得点单点
按月归档
March 2022
January 2022
November 2021
October 2021
September 2021
August 2021
July 2021
June 2021
February 2021
September 2020
May 2020
September 2019
August 2019
July 2019
June 2019
May 2019
January 2019
December 2018
November 2018
October 2018
September 2018
August 2018
July 2018
June 2018
April 2018
March 2018
December 2017
October 2017
September 2017
August 2017
April 2017
March 2017
February 2017
August 2016
July 2015
November 2014
September 2014
August 2014
July 2014
June 2014
July 2013
April 2013
September 2012
July 2012
May 2012
April 2012
February 2012
January 2012
December 2011
November 2011
October 2011
September 2011
August 2011
December 2010
November 2010
October 2010
September 2010
August 2010
July 2010
June 2010
May 2010
April 2010
March 2010
February 2010
January 2010
December 2009
November 2009
October 2009
September 2009
June 2009
May 2009
April 2009
March 2009
February 2009
December 2008
November 2008
September 2008
August 2008
July 2008
June 2008
常用标签
Mysql
nginx
mysql优化
linux
debian
Powered by
Typecho)))
Optimized by
EAimTY
左链接查询优化一例
August 16, 2016
大部分时候,两个大表做join的时候,where条件前置不但可以节省扫描行数,还能避免创建磁盘临时文件: 优化前: ``` SELECT g.goods_id, g.add_time, g.goods_name, g.market_price, g.is_new , g.is_best, g.is_hot, g.shop_price AS org_price , IFNULL(mp.user_price, g.shop_price * '1.00') AS shop_price , g.promote_price, g.promote_start_date, g.promote_end_date, g.goods_thumb, g.goods_img , g.goods_brief, g.goods_type FROM `ecshop`.`fp_goods` g LEFT JOIN `ecshop`.`fp_member_price` mp ON mp.goods_id = g.goods_id AND mp.user_rank = '1' WHERE (g.is_delete = 0 AND g.is_on_sale = 1 AND g.is_alone_sale = 1 AND 1) ORDER BY goods_id DESC LIMIT 200, 40 ``` 优化后 ``` SELECT g.goods_id, g.add_time, g.goods_name, g.market_price, g.is_new , g.is_best, g.is_hot, g.shop_price AS org_price , IFNULL(mp.user_price, g.shop_price * '1.00') AS shop_price , g.promote_price, g.promote_start_date, g.promote_end_date, g.goods_thumb, g.goods_img , g.goods_brief, g.goods_type FROM ( SELECT * FROM `ecshop`.`fp_goods` ORDER BY goods_id DESC LIMIT 200, 40 ) g LEFT JOIN `ecshop`.`fp_member_price` mp ON mp.goods_id = g.goods_id AND mp.user_rank = '1' ``` 优化前: ``` SELECT g.goods_id, g.goods_name, g.market_price, g.is_new, g.is_best , g.is_hot, g.shop_price AS org_price , IFNULL(mp.user_price, g.shop_price * '1') AS shop_price , g.promote_price, g.promote_start_date, g.promote_end_date, g.goods_thumb, g.goods_img , g.goods_brief, g.goods_type FROM `ecshop`.`fp_goods` g LEFT JOIN `ecshop`.`ecs_member_price` mp ON mp.goods_id = g.goods_id AND mp.user_rank = '0' LEFT JOIN `ecshop`.`fp_brand` b ON b.brand_id = g.brand_id WHERE (b.brand_id NOT IN ('406', '13', '7') AND g.is_delete = 0 AND g.is_on_sale = 1 AND g.is_alone_sale = 1 AND (1 AND (((goods_name LIKE '%Phillip%' OR goods_sn LIKE '%Phillip%' OR keywords LIKE '%Phillip%')) OR ((goods_name LIKE '%plein%' OR goods_sn LIKE '%plein%' OR keywords LIKE '%plein%')) OR ((goods_name LIKE '%womens%' OR goods_sn LIKE '%womens%' OR keywords LIKE '%womens%')) OR ((goods_name LIKE '%tshirt%' OR goods_sn LIKE '%tshirt%' OR keywords LIKE '%tshirt%'))))) ORDER BY goods_number ASC LIMIT 32, 32 ``` 优化后 ``` SELECT g.goods_id, g.goods_name, g.market_price, g.is_new, g.is_best , g.is_hot, g.shop_price AS org_price , IFNULL(mp.user_price, g.shop_price * '1') AS shop_price , g.promote_price, g.promote_start_date, g.promote_end_date, g.goods_thumb, g.goods_img , g.goods_brief, g.goods_type FROM ( SELECT * FROM `ecshop`.`fp_goods` WHERE MATCH (goods_sn, goods_name, keywords) AGAINST ('Phillip plein womens tshirt') AND (is_delete = 0 AND is_on_sale = 1 AND is_alone_sale = 1) ORDER BY goods_number ASC LIMIT 32, 32 ) g LEFT JOIN `ecshop`.`ecs_member_price` mp ON mp.goods_id = g.goods_id AND mp.user_rank = 0 LEFT JOIN `ecshop`.`fp_brand` b ON b.brand_id = g.brand_id WHERE b.brand_id NOT IN (406, 13, 7) ```
暂无评论
添加新评论
称呼
Email
网站
取消回复
内容
发表评论