SQL语句优化（雷人代码）
维护组项目出了点问题，打开网站越慢，部门几个老员工在找原因，发现一条SQL语句，我看到没把我雷死。
原代码：
代码
1 select top 16 goods_id,goods_name,goods_thumb,integral,cat_id from
2 goods where goods_id>0 and cat_id=40 and is_real=1 and area in('000','0514')
3 AND goods_name not like '%B30（2G）纽曼MP3%' and goods_name not like '%IPOD音箱%'
4 and goods_name not like '%SP-8210 史努比旋风吹风机%' and goods_name not like '%zippo打火机150ZL%'
5 and goods_name not like '%ZIPPO打火机205%' and goods_name not like '%zippo打火机280AW陆军空战银章%'
6 and goods_name not like '%爱国者音箱SP-F021%' and goods_name not like '%爱国者音箱SP-F027%'
7 and goods_name not like '%铂金多用汤锅DFS-T012%' and goods_name not like '%不锈钢快速水壶OL-209%'
8 and goods_name not like '%不锈钢快速水壶OL-215%' and goods_name not like '%不锈钢密封碗三件套%'
9 and goods_name not like '%彩棉浴巾%' and goods_name not like '%超人电吹风SB80%'
10 and goods_name not like '%超人电须刀SA35%' and goods_name not like '%超人电须刀SA850%'
11 and goods_name not like '%超人电熨斗SY570%' and goods_name not like '%超人毛球修剪器SR2855%'
12 and goods_name not like '%超人剃须刀SA2701%' and goods_name not like '%超人拓威干鞋机TH151%'
13 and goods_name not like '%厨具十八件套CQG1518%' and goods_name not like '%春笑牌USB蓝鲸电暖手鼠标垫%'
14 and goods_name not like '%春笑牌USB维尼熊电暖手鼠标垫%' and goods_name not like '%春笑牌USB喜洋洋电暖手鼠标垫%'
15 and goods_name not like '%德世朗多功能蒸煮锅%' and goods_name not like '%迪士尼保温泡菜壶%'
16 and goods_name not like '%迪士尼抽真空保温杯%' and goods_name not like '%迪士尼幸运草随手杯%'
17 and goods_name not like '%冬暖复合被%' and goods_name not like '%都彭牛皮皮带8818C289%'
18 and goods_name not like '%都彭牛皮皮带8881C055%' and goods_name not like '%都彭牛皮钥匙包8855B020-4%'
19 and goods_name not like '%哆啦A梦便利收纳箱%' and goods_name not like '%哆啦A梦大容量真空保温杯%'
20 and goods_name not like '%哆啦A梦红酒瓶雨伞(黄)%' and goods_name not like '%哆啦A梦时尚套装礼盒DM-2319%'
21 and goods_name not like '%果缤纷（刀具套装）%' and goods_name not like '%韩式调料缸三件套（22*24*26）%'
22 and goods_name not like '%好帮手淘米器%' and goods_name not like '%红厨铝合金煎盘YN0351%'
23 and goods_name not like '%吉旺1厘无磁钢柄五件套装7162%'
24 and goods_name not like '%吉旺3厘木纹6件套7191%' and goods_name not like '%康戈 蒸好（微波炉专用小蒸煲）CQG2004%'
25 and goods_name not like '%康戈SURE不锈钢保温杯%' and goods_name not like '%康戈秤心如意厨房秤%'
26 and goods_name not like '%康戈情侣密封罐CQG5001%' and goods_name not like '%康戈钛真空保温杯CQG4000%'
27 and goods_name not like '%康戈蒸好（微波炉专用小蒸煲）CQG2004%'
28 and goods_name not like '%康戈智能卡路里电子跳绳%' and goods_name not like '%乐扣茶杯HPL934M%'
29 and goods_name not like '%乐扣乐扣四方型保鲜盒HPL809F%' and goods_name not like '%乐扣乐扣圆型保鲜盒HPL933%'
30 and goods_name not like '%乐仕菲斯贝贝乐保温提盒RSC1000K%'
31 and goods_name not like '%乐仕菲斯单柄奶锅RS1600K%' and goods_name not like '%乐仕菲斯优乐套装锅RS201T%'
32 and goods_name not like '%绿波仙子（补铁绿彩套锅）%'
33 and goods_name not like '%欧莱斯特304不锈钢无缝内胆水壶OL-818橙色%'
34 and goods_name not like '%欧莱斯特304不锈钢无缝内胆水壶QL-818%'
35 and goods_name not like '%欧然电水壶CR-1130%' and goods_name not like '%日式环保包袋组合（折叠环保包+爱心便当包）%'
36 and goods_name not like '%三洋SANYO 煮蛋器 JH702A%'
37 and goods_name not like '%三洋SANYO煮蛋器JH702A%' and goods_name not like '%三洋电吹风DH-JH104%'
38 and goods_name not like '%三洋电吹风HD-JH101A%'
39 and goods_name not like '%三洋电吹风HD-JH104%' and goods_name not like '%三洋电熨斗A-JH102M%'
40 and goods_name not like '%三洋加湿器CFK-JH6013%'
41 and goods_name not like '%膳之厨多功能锅CJ-096%' and goods_name not like '%膳之厨锅具两件套CJ-960%'
42 and goods_name not like '%膳之厨时尚三件套CJ-3683%' and goods_name not like '%圣德保罗工具套装SD-007-C%'
43 and goods_name not like '%十八子作高级不锈钢家用剪WJJ-01%' and goods_name not like '%十八子作开瓶器SC-02%'
44 and goods_name not like '%十八子作雅刃六件套刀S2902%' and goods_name not like '%十八子作银盈快刃斩切刀S2504-A%'
45 and goods_name not like '%时尚折叠凳%' and goods_name not like '%史努比故事保鲜盒组SP-A123%'
46 and goods_name not like '%史努比故事水杯SP-A317%' and goods_name not like '%史努比酷狗健康称SP-H201%'
47 and goods_name not like '%史努比酷炫多功能杯SP-A105%' and goods_name not like '%史努比亲亲四入冷水壶SP-A203%'
48 and goods_name not like '%史努比清凉小冰杯SP-A112%'
49 and goods_name not like '%史努比提手杯%' and goods_name not like '%史努比幸福双层饭盒SP-C103%'
50 and goods_name not like '%史努比紫洋保温杯SP-A602%' and goods_name not like '%天飞伞3007%'
51 and goods_name not like '%天堂10302E国色天香伞%' and goods_name not like '%天堂苏印丝伞%'
52 and goods_name not like '%西铁城电子体温计CT513W%' and goods_name not like '%香山牌电子健康秤EB9003L%'
53 and goods_name not like '%香山牌机械健康秤BR2017B%' and goods_name not like '%小天子钢柄八件套刀A-019%'
54 and goods_name not like '%伊莱特3L电饭煲CFXB30-J31A%' and goods_name not like '%宜剪美%'
55 and goods_name not like '%羽梦情缘被套(简装)%' and goods_name not like '%羽梦情缘床单(简装)%'
56 and goods_name not like '%羽梦情缘枕套(简装)%' and goods_name not like '%雨花丝绒毯%'
57 and goods_name not like '%蒸功夫蒸汤两用锅%' and goods_name not like '%竹纤维被%' and goods_name not like '%竹纤维浴巾%'
58 and goods_name not like '%红厨 32cm炫雅雪花不沾炒锅YN0304%' and goods_name not like '%春笑牌烘鞋器（大）%'
59 and goods_name not like '%欧然不锈钢快速电水壶CR-1181 1.2L%' and goods_name not like '%春笑牌烘鞋器（小）%'
60 and goods_name not like '%史努比紫洋保温杯 SP-A602%' and goods_name not like '%欧然不锈钢快速电水壶CR-1120 0.8L%'
61 and goods_id not in
62 (select top 16 goods_id from goods where goods_id>0 and cat_id=40 and is_real=1 and area in('000','0514')
63 and goods_name not like '%B30（2G）纽曼MP3%' and goods_name not like '%IPOD音箱%'
64 and goods_name not like '%SP-8210 史努比旋风吹风机%' and goods_name not like '%zippo打火机150ZL%'
65 and goods_name not like '%ZIPPO打火机205%' and goods_name not like '%zippo打火机280AW陆军空战银章%'
66 and goods_name not like '%爱国者音箱SP-F021%' and goods_name not like '%爱国者音箱SP-F027%'
67 and goods_name not like '%铂金多用汤锅DFS-T012%' and goods_name not like '%不锈钢快速水壶OL-209%'
68 and goods_name not like '%不锈钢快速水壶OL-215%' and goods_name not like '%不锈钢密封碗三件套%'
69 and goods_name not like '%彩棉浴巾%'and goods_name not like '%超人电吹风SB80%'
70 and goods_name not like '%超人电须刀SA35%' and goods_name not like '%超人电须刀SA850%'
71 and goods_name not like '%超人电熨斗SY570%' and goods_name not like '%超人毛球修剪器SR2855%'
72 and goods_name not like '%超人剃须刀SA2701%' and goods_name not like '%超人拓威干鞋机TH151%'
73 and goods_name not like '%厨具十八件套CQG1518%' and goods_name not like '%春笑牌USB蓝鲸电暖手鼠标垫%'
74 and goods_name not like '%春笑牌USB维尼熊电暖手鼠标垫%' and goods_name not like '%春笑牌USB喜洋洋电暖手鼠标垫%'
75 and goods_name not like '%德世朗多功能蒸煮锅%' and goods_name not like '%迪士尼保温泡菜壶%'
76 and goods_name not like '%迪士尼抽真空保温杯%' and goods_name not like '%迪士尼幸运草随手杯%'
77 and goods_name not like '%冬暖复合被%' and goods_name not like'%都彭牛皮皮带8818C289%'
78 and goods_name not like '%都彭牛皮皮带8881C055%' and goods_name not like'%都彭牛皮钥匙包8855B020-4%'
79 and goods_name not like '%哆啦A梦便利收纳箱%' and goods_name not like'%哆啦A梦大容量真空保温杯%'
80 and goods_name not like '%哆啦A梦红酒瓶雨伞(黄)%' and goods_name not like '%哆啦A梦时尚套装礼盒DM-2319%'
81 and goods_name not like '%果缤纷（刀具套装）%' and goods_name not like '%韩式调料缸三件套（22*24*26）%'
82 and goods_name not like '%好帮手淘米器%' and goods_name not like '%红厨铝合金煎盘YN0351%'
83 and goods_name not like '%吉旺1厘无磁钢柄五件套装7162%' and goods_name not like '%吉旺3厘木纹6件套7191%'
84 and goods_name not like '%康戈 蒸好（微波炉专用小蒸煲）CQG2004%' and goods_name not like '%康戈SURE不锈钢保温杯%'
85 and goods_name not like '%康戈秤心如意厨房秤%'and goods_name not like '%康戈情侣密封罐CQG5001%'
86 and goods_name not like '%康戈钛真空保温杯CQG4000%'
87 and goods_name not like '%康戈蒸好（微波炉专用小蒸煲）CQG2004%' and goods_name not like '%康戈智能卡路里电子跳绳%'
88 and goods_name not like '%乐扣茶杯HPL934M%' and goods_name not like '%乐扣乐扣四方型保鲜盒HPL809F%'
89 and goods_name not like '%乐扣乐扣圆型保鲜盒HPL933%' and goods_name not like '%乐仕菲斯贝贝乐保温提盒RSC1000K%'
90 and goods_name not like '%乐仕菲斯单柄奶锅RS1600K%' and goods_name not like '%乐仕菲斯优乐套装锅RS201T%'
91 and goods_name not like '%绿波仙子（补铁绿彩套锅）%' and goods_name not like '%欧莱斯特304不锈钢无缝内胆水壶OL-818橙色%'
92 and goods_name not like '%欧莱斯特304不锈钢无缝内胆水壶QL-818%' and goods_name not like '%欧然电水壶CR-1130%'
93 and goods_name not like '%日式环保包袋组合（折叠环保包+爱心便当包）%'
94 and goods_name not like '%三洋SANYO 煮蛋器 JH702A%' and goods_name not like '%三洋SANYO煮蛋器JH702A%'
95 and goods_name not like '%三洋电吹风DH-JH104%' and goods_name not like '%三洋电吹风HD-JH101A%'
96 and goods_name not like '%三洋电吹风HD-JH104%' and goods_name not like '%三洋电熨斗A-JH102M%'
97 and goods_name not like '%三洋加湿器CFK-JH6013%' and goods_name not like '%膳之厨多功能锅CJ-096%'
98 and goods_name not like '%膳之厨锅具两件套CJ-960%' and goods_name not like '%膳之厨时尚三件套CJ-3683%'
99 and goods_name not like '%圣德保罗工具套装SD-007-C%' and goods_name not like '%十八子作高级不锈钢家用剪WJJ-01%'
100 and goods_name not like '%十八子作开瓶器SC-02%' and goods_name not like '%十八子作雅刃六件套刀S2902%'
101 and goods_name not like '%十八子作银盈快刃斩切刀S2504-A%' and goods_name not like '%时尚折叠凳%'
102 and goods_name not like '%史努比故事保鲜盒组SP-A123%' and goods_name not like '%史努比故事水杯SP-A317%'
103 and goods_name not like '%史努比酷狗健康称SP-H201%' and goods_name not like '%史努比酷炫多功能杯SP-A105%'
104 and goods_name not like '%史努比亲亲四入冷水壶SP-A203%' and goods_name not like '%史努比清凉小冰杯SP-A112%'
105 and goods_name not like '%史努比提手杯%' and goods_name not like '%史努比幸福双层饭盒SP-C103%'
106 and goods_name not like '%史努比紫洋保温杯SP-A602%' and goods_name not like '%天飞伞3007%'
107 and goods_name not like '%天堂10302E国色天香伞%' and goods_name not like '%天堂苏印丝伞%'
108 and goods_name not like '%西铁城电子体温计CT513W%' and goods_name not like '%香山牌电子健康秤EB9003L%'
109 and goods_name not like '%香山牌机械健康秤BR2017B%' and goods_name not like '%小天子钢柄八件套刀A-019%'
110 and goods_name not like '%伊莱特3L电饭煲CFXB30-J31A%' and goods_name not like '%宜剪美%'
111 and goods_name not like '%羽梦情缘被套(简装)%' and goods_name not like '%羽梦情缘床单(简装)%'
112 and goods_name not like '%羽梦情缘枕套(简装)%' and goods_name not like '%雨花丝绒毯%'
113 and goods_name not like '%蒸功夫蒸汤两用锅%' and goods_name not like '%竹纤维被%' and goods_name not like '%竹纤维浴巾%'
114 and goods_name not like '%红厨 32cm炫雅雪花不沾炒锅YN0304%'
115 and goods_name not like '%春笑牌烘鞋器（大）%' and goods_name not like '%欧然不锈钢快速电水壶CR-1181 1.2L%'
116 and goods_name not like '%春笑牌烘鞋器（小）%' and goods_name not like '%史努比紫洋保温杯 SP-A602%'
117 and goods_name not like '%欧然不锈钢快速电水壶CR-1120 0.8L%' order by suppliertabid)order by suppliertabid
2 goods where goods_id>0 and cat_id=40 and is_real=1 and area in('000','0514')
3 AND goods_name not like '%B30（2G）纽曼MP3%' and goods_name not like '%IPOD音箱%'
4 and goods_name not like '%SP-8210 史努比旋风吹风机%' and goods_name not like '%zippo打火机150ZL%'
5 and goods_name not like '%ZIPPO打火机205%' and goods_name not like '%zippo打火机280AW陆军空战银章%'
6 and goods_name not like '%爱国者音箱SP-F021%' and goods_name not like '%爱国者音箱SP-F027%'
7 and goods_name not like '%铂金多用汤锅DFS-T012%' and goods_name not like '%不锈钢快速水壶OL-209%'
8 and goods_name not like '%不锈钢快速水壶OL-215%' and goods_name not like '%不锈钢密封碗三件套%'
9 and goods_name not like '%彩棉浴巾%' and goods_name not like '%超人电吹风SB80%'
10 and goods_name not like '%超人电须刀SA35%' and goods_name not like '%超人电须刀SA850%'
11 and goods_name not like '%超人电熨斗SY570%' and goods_name not like '%超人毛球修剪器SR2855%'
12 and goods_name not like '%超人剃须刀SA2701%' and goods_name not like '%超人拓威干鞋机TH151%'
13 and goods_name not like '%厨具十八件套CQG1518%' and goods_name not like '%春笑牌USB蓝鲸电暖手鼠标垫%'
14 and goods_name not like '%春笑牌USB维尼熊电暖手鼠标垫%' and goods_name not like '%春笑牌USB喜洋洋电暖手鼠标垫%'
15 and goods_name not like '%德世朗多功能蒸煮锅%' and goods_name not like '%迪士尼保温泡菜壶%'
16 and goods_name not like '%迪士尼抽真空保温杯%' and goods_name not like '%迪士尼幸运草随手杯%'
17 and goods_name not like '%冬暖复合被%' and goods_name not like '%都彭牛皮皮带8818C289%'
18 and goods_name not like '%都彭牛皮皮带8881C055%' and goods_name not like '%都彭牛皮钥匙包8855B020-4%'
19 and goods_name not like '%哆啦A梦便利收纳箱%' and goods_name not like '%哆啦A梦大容量真空保温杯%'
20 and goods_name not like '%哆啦A梦红酒瓶雨伞(黄)%' and goods_name not like '%哆啦A梦时尚套装礼盒DM-2319%'
21 and goods_name not like '%果缤纷（刀具套装）%' and goods_name not like '%韩式调料缸三件套（22*24*26）%'
22 and goods_name not like '%好帮手淘米器%' and goods_name not like '%红厨铝合金煎盘YN0351%'
23 and goods_name not like '%吉旺1厘无磁钢柄五件套装7162%'
24 and goods_name not like '%吉旺3厘木纹6件套7191%' and goods_name not like '%康戈 蒸好（微波炉专用小蒸煲）CQG2004%'
25 and goods_name not like '%康戈SURE不锈钢保温杯%' and goods_name not like '%康戈秤心如意厨房秤%'
26 and goods_name not like '%康戈情侣密封罐CQG5001%' and goods_name not like '%康戈钛真空保温杯CQG4000%'
27 and goods_name not like '%康戈蒸好（微波炉专用小蒸煲）CQG2004%'
28 and goods_name not like '%康戈智能卡路里电子跳绳%' and goods_name not like '%乐扣茶杯HPL934M%'
29 and goods_name not like '%乐扣乐扣四方型保鲜盒HPL809F%' and goods_name not like '%乐扣乐扣圆型保鲜盒HPL933%'
30 and goods_name not like '%乐仕菲斯贝贝乐保温提盒RSC1000K%'
31 and goods_name not like '%乐仕菲斯单柄奶锅RS1600K%' and goods_name not like '%乐仕菲斯优乐套装锅RS201T%'
32 and goods_name not like '%绿波仙子（补铁绿彩套锅）%'
33 and goods_name not like '%欧莱斯特304不锈钢无缝内胆水壶OL-818橙色%'
34 and goods_name not like '%欧莱斯特304不锈钢无缝内胆水壶QL-818%'
35 and goods_name not like '%欧然电水壶CR-1130%' and goods_name not like '%日式环保包袋组合（折叠环保包+爱心便当包）%'
36 and goods_name not like '%三洋SANYO 煮蛋器 JH702A%'
37 and goods_name not like '%三洋SANYO煮蛋器JH702A%' and goods_name not like '%三洋电吹风DH-JH104%'
38 and goods_name not like '%三洋电吹风HD-JH101A%'
39 and goods_name not like '%三洋电吹风HD-JH104%' and goods_name not like '%三洋电熨斗A-JH102M%'
40 and goods_name not like '%三洋加湿器CFK-JH6013%'
41 and goods_name not like '%膳之厨多功能锅CJ-096%' and goods_name not like '%膳之厨锅具两件套CJ-960%'
42 and goods_name not like '%膳之厨时尚三件套CJ-3683%' and goods_name not like '%圣德保罗工具套装SD-007-C%'
43 and goods_name not like '%十八子作高级不锈钢家用剪WJJ-01%' and goods_name not like '%十八子作开瓶器SC-02%'
44 and goods_name not like '%十八子作雅刃六件套刀S2902%' and goods_name not like '%十八子作银盈快刃斩切刀S2504-A%'
45 and goods_name not like '%时尚折叠凳%' and goods_name not like '%史努比故事保鲜盒组SP-A123%'
46 and goods_name not like '%史努比故事水杯SP-A317%' and goods_name not like '%史努比酷狗健康称SP-H201%'
47 and goods_name not like '%史努比酷炫多功能杯SP-A105%' and goods_name not like '%史努比亲亲四入冷水壶SP-A203%'
48 and goods_name not like '%史努比清凉小冰杯SP-A112%'
49 and goods_name not like '%史努比提手杯%' and goods_name not like '%史努比幸福双层饭盒SP-C103%'
50 and goods_name not like '%史努比紫洋保温杯SP-A602%' and goods_name not like '%天飞伞3007%'
51 and goods_name not like '%天堂10302E国色天香伞%' and goods_name not like '%天堂苏印丝伞%'
52 and goods_name not like '%西铁城电子体温计CT513W%' and goods_name not like '%香山牌电子健康秤EB9003L%'
53 and goods_name not like '%香山牌机械健康秤BR2017B%' and goods_name not like '%小天子钢柄八件套刀A-019%'
54 and goods_name not like '%伊莱特3L电饭煲CFXB30-J31A%' and goods_name not like '%宜剪美%'
55 and goods_name not like '%羽梦情缘被套(简装)%' and goods_name not like '%羽梦情缘床单(简装)%'
56 and goods_name not like '%羽梦情缘枕套(简装)%' and goods_name not like '%雨花丝绒毯%'
57 and goods_name not like '%蒸功夫蒸汤两用锅%' and goods_name not like '%竹纤维被%' and goods_name not like '%竹纤维浴巾%'
58 and goods_name not like '%红厨 32cm炫雅雪花不沾炒锅YN0304%' and goods_name not like '%春笑牌烘鞋器（大）%'
59 and goods_name not like '%欧然不锈钢快速电水壶CR-1181 1.2L%' and goods_name not like '%春笑牌烘鞋器（小）%'
60 and goods_name not like '%史努比紫洋保温杯 SP-A602%' and goods_name not like '%欧然不锈钢快速电水壶CR-1120 0.8L%'
61 and goods_id not in
62 (select top 16 goods_id from goods where goods_id>0 and cat_id=40 and is_real=1 and area in('000','0514')
63 and goods_name not like '%B30（2G）纽曼MP3%' and goods_name not like '%IPOD音箱%'
64 and goods_name not like '%SP-8210 史努比旋风吹风机%' and goods_name not like '%zippo打火机150ZL%'
65 and goods_name not like '%ZIPPO打火机205%' and goods_name not like '%zippo打火机280AW陆军空战银章%'
66 and goods_name not like '%爱国者音箱SP-F021%' and goods_name not like '%爱国者音箱SP-F027%'
67 and goods_name not like '%铂金多用汤锅DFS-T012%' and goods_name not like '%不锈钢快速水壶OL-209%'
68 and goods_name not like '%不锈钢快速水壶OL-215%' and goods_name not like '%不锈钢密封碗三件套%'
69 and goods_name not like '%彩棉浴巾%'and goods_name not like '%超人电吹风SB80%'
70 and goods_name not like '%超人电须刀SA35%' and goods_name not like '%超人电须刀SA850%'
71 and goods_name not like '%超人电熨斗SY570%' and goods_name not like '%超人毛球修剪器SR2855%'
72 and goods_name not like '%超人剃须刀SA2701%' and goods_name not like '%超人拓威干鞋机TH151%'
73 and goods_name not like '%厨具十八件套CQG1518%' and goods_name not like '%春笑牌USB蓝鲸电暖手鼠标垫%'
74 and goods_name not like '%春笑牌USB维尼熊电暖手鼠标垫%' and goods_name not like '%春笑牌USB喜洋洋电暖手鼠标垫%'
75 and goods_name not like '%德世朗多功能蒸煮锅%' and goods_name not like '%迪士尼保温泡菜壶%'
76 and goods_name not like '%迪士尼抽真空保温杯%' and goods_name not like '%迪士尼幸运草随手杯%'
77 and goods_name not like '%冬暖复合被%' and goods_name not like'%都彭牛皮皮带8818C289%'
78 and goods_name not like '%都彭牛皮皮带8881C055%' and goods_name not like'%都彭牛皮钥匙包8855B020-4%'
79 and goods_name not like '%哆啦A梦便利收纳箱%' and goods_name not like'%哆啦A梦大容量真空保温杯%'
80 and goods_name not like '%哆啦A梦红酒瓶雨伞(黄)%' and goods_name not like '%哆啦A梦时尚套装礼盒DM-2319%'
81 and goods_name not like '%果缤纷（刀具套装）%' and goods_name not like '%韩式调料缸三件套（22*24*26）%'
82 and goods_name not like '%好帮手淘米器%' and goods_name not like '%红厨铝合金煎盘YN0351%'
83 and goods_name not like '%吉旺1厘无磁钢柄五件套装7162%' and goods_name not like '%吉旺3厘木纹6件套7191%'
84 and goods_name not like '%康戈 蒸好（微波炉专用小蒸煲）CQG2004%' and goods_name not like '%康戈SURE不锈钢保温杯%'
85 and goods_name not like '%康戈秤心如意厨房秤%'and goods_name not like '%康戈情侣密封罐CQG5001%'
86 and goods_name not like '%康戈钛真空保温杯CQG4000%'
87 and goods_name not like '%康戈蒸好（微波炉专用小蒸煲）CQG2004%' and goods_name not like '%康戈智能卡路里电子跳绳%'
88 and goods_name not like '%乐扣茶杯HPL934M%' and goods_name not like '%乐扣乐扣四方型保鲜盒HPL809F%'
89 and goods_name not like '%乐扣乐扣圆型保鲜盒HPL933%' and goods_name not like '%乐仕菲斯贝贝乐保温提盒RSC1000K%'
90 and goods_name not like '%乐仕菲斯单柄奶锅RS1600K%' and goods_name not like '%乐仕菲斯优乐套装锅RS201T%'
91 and goods_name not like '%绿波仙子（补铁绿彩套锅）%' and goods_name not like '%欧莱斯特304不锈钢无缝内胆水壶OL-818橙色%'
92 and goods_name not like '%欧莱斯特304不锈钢无缝内胆水壶QL-818%' and goods_name not like '%欧然电水壶CR-1130%'
93 and goods_name not like '%日式环保包袋组合（折叠环保包+爱心便当包）%'
94 and goods_name not like '%三洋SANYO 煮蛋器 JH702A%' and goods_name not like '%三洋SANYO煮蛋器JH702A%'
95 and goods_name not like '%三洋电吹风DH-JH104%' and goods_name not like '%三洋电吹风HD-JH101A%'
96 and goods_name not like '%三洋电吹风HD-JH104%' and goods_name not like '%三洋电熨斗A-JH102M%'
97 and goods_name not like '%三洋加湿器CFK-JH6013%' and goods_name not like '%膳之厨多功能锅CJ-096%'
98 and goods_name not like '%膳之厨锅具两件套CJ-960%' and goods_name not like '%膳之厨时尚三件套CJ-3683%'
99 and goods_name not like '%圣德保罗工具套装SD-007-C%' and goods_name not like '%十八子作高级不锈钢家用剪WJJ-01%'
100 and goods_name not like '%十八子作开瓶器SC-02%' and goods_name not like '%十八子作雅刃六件套刀S2902%'
101 and goods_name not like '%十八子作银盈快刃斩切刀S2504-A%' and goods_name not like '%时尚折叠凳%'
102 and goods_name not like '%史努比故事保鲜盒组SP-A123%' and goods_name not like '%史努比故事水杯SP-A317%'
103 and goods_name not like '%史努比酷狗健康称SP-H201%' and goods_name not like '%史努比酷炫多功能杯SP-A105%'
104 and goods_name not like '%史努比亲亲四入冷水壶SP-A203%' and goods_name not like '%史努比清凉小冰杯SP-A112%'
105 and goods_name not like '%史努比提手杯%' and goods_name not like '%史努比幸福双层饭盒SP-C103%'
106 and goods_name not like '%史努比紫洋保温杯SP-A602%' and goods_name not like '%天飞伞3007%'
107 and goods_name not like '%天堂10302E国色天香伞%' and goods_name not like '%天堂苏印丝伞%'
108 and goods_name not like '%西铁城电子体温计CT513W%' and goods_name not like '%香山牌电子健康秤EB9003L%'
109 and goods_name not like '%香山牌机械健康秤BR2017B%' and goods_name not like '%小天子钢柄八件套刀A-019%'
110 and goods_name not like '%伊莱特3L电饭煲CFXB30-J31A%' and goods_name not like '%宜剪美%'
111 and goods_name not like '%羽梦情缘被套(简装)%' and goods_name not like '%羽梦情缘床单(简装)%'
112 and goods_name not like '%羽梦情缘枕套(简装)%' and goods_name not like '%雨花丝绒毯%'
113 and goods_name not like '%蒸功夫蒸汤两用锅%' and goods_name not like '%竹纤维被%' and goods_name not like '%竹纤维浴巾%'
114 and goods_name not like '%红厨 32cm炫雅雪花不沾炒锅YN0304%'
115 and goods_name not like '%春笑牌烘鞋器（大）%' and goods_name not like '%欧然不锈钢快速电水壶CR-1181 1.2L%'
116 and goods_name not like '%春笑牌烘鞋器（小）%' and goods_name not like '%史努比紫洋保温杯 SP-A602%'
117 and goods_name not like '%欧然不锈钢快速电水壶CR-1120 0.8L%' order by suppliertabid)order by suppliertabid
全是not like,排序写了两次（order by suppliertabid），还有一个条件，重复写了两次（goods_id>0 and cat_id=40 and is_real=1 and area in('000','0514')）。
这种看到头都大了，然后执行查询6秒种，OMG，不到三千的数据，查询要6秒。这是任何一个程序员都不允许发生的事情。
然后又仔细看了下 OMG ，LDGAGA，这个 nd goods_id not in (条件)，重复上面where条件了，这个这个，当我没说，一个程序能干出来这样的事，我就不发表意见了。
然后我就想给优化下，排序重复了去掉一个，现在暂时不先考虑NOT LIKE，还有 goods_id>0 and cat_id=40 and is_real=1 and area in('000','0514')也重复了，去掉一个。
去掉子条件里面的。现在执行在毫秒级别。and goods_id not in ()这样写没有什么意义，去掉。这样少了一半的代码啊。继续改，where 条件改成where goods_id>0 and cat_id=40 and is_real=1 and area in('000','0514') and
goods_id not in(
select top 16 goods_id from goods where 后面是NOT LIKE数据)这样代码也好看些，运行，OMG 才30多毫秒,这就对了，这样才OK。
修改后的代码：
代码select top 16 goods_id,goods_name,goods_thumb,integral,cat_id from
goods where goods_id>0 and cat_id=40 and is_real=1 and area in('000','0514') and
goods_id not in(
select top 16 goods_id from goods where
goods_name not like '%SP-8210 史努比旋风吹风机%' and goods_name not like '%zippo打火机150ZL%'
and goods_name not like '%ZIPPO打火机205%' and goods_name not like '%zippo打火机280AW陆军空战银章%'
and goods_name not like '%爱国者音箱SP-F021%' and goods_name not like '%爱国者音箱SP-F027%'
and goods_name not like '%铂金多用汤锅DFS-T012%' and goods_name not like '%不锈钢快速水壶OL-209%'
and goods_name not like '%不锈钢快速水壶OL-215%' and goods_name not like '%不锈钢密封碗三件套%'
and goods_name not like '%彩棉浴巾%'and goods_name not like '%超人电吹风SB80%'
and goods_name not like '%超人电须刀SA35%' and goods_name not like '%超人电须刀SA850%'
and goods_name not like '%超人电熨斗SY570%' and goods_name not like '%超人毛球修剪器SR2855%'
and goods_name not like '%超人剃须刀SA2701%' and goods_name not like '%超人拓威干鞋机TH151%'
and goods_name not like '%厨具十八件套CQG1518%' and goods_name not like '%春笑牌USB蓝鲸电暖手鼠标垫%'
and goods_name not like '%春笑牌USB维尼熊电暖手鼠标垫%' and goods_name not like '%春笑牌USB喜洋洋电暖手鼠标垫%'
and goods_name not like '%德世朗多功能蒸煮锅%' and goods_name not like '%迪士尼保温泡菜壶%'
and goods_name not like '%迪士尼抽真空保温杯%' and goods_name not like '%迪士尼幸运草随手杯%'
and goods_name not like '%冬暖复合被%' and goods_name not like'%都彭牛皮皮带8818C289%'
and goods_name not like '%都彭牛皮皮带8881C055%' and goods_name not like'%都彭牛皮钥匙包8855B020-4%'
and goods_name not like '%哆啦A梦便利收纳箱%' and goods_name not like'%哆啦A梦大容量真空保温杯%'
and goods_name not like '%哆啦A梦红酒瓶雨伞(黄)%' and goods_name not like '%哆啦A梦时尚套装礼盒DM-2319%'
and goods_name not like '%果缤纷（刀具套装）%' and goods_name not like '%韩式调料缸三件套（22*24*26）%'
and goods_name not like '%好帮手淘米器%' and goods_name not like '%红厨铝合金煎盘YN0351%'
and goods_name not like '%吉旺1厘无磁钢柄五件套装7162%' and goods_name not like '%吉旺3厘木纹6件套7191%'
and goods_name not like '%康戈 蒸好（微波炉专用小蒸煲）CQG2004%' and goods_name not like '%康戈SURE不锈钢保温杯%'
and goods_name not like '%康戈秤心如意厨房秤%'and goods_name not like '%康戈情侣密封罐CQG5001%'
and goods_name not like '%康戈钛真空保温杯CQG4000%'
and goods_name not like '%康戈蒸好（微波炉专用小蒸煲）CQG2004%' and goods_name not like '%康戈智能卡路里电子跳绳%'
and goods_name not like '%乐扣茶杯HPL934M%' and goods_name not like '%乐扣乐扣四方型保鲜盒HPL809F%'
and goods_name not like '%乐扣乐扣圆型保鲜盒HPL933%' and goods_name not like '%乐仕菲斯贝贝乐保温提盒RSC1000K%'
and goods_name not like '%乐仕菲斯单柄奶锅RS1600K%' and goods_name not like '%乐仕菲斯优乐套装锅RS201T%'
and goods_name not like '%绿波仙子（补铁绿彩套锅）%' and goods_name not like '%欧莱斯特304不锈钢无缝内胆水壶OL-818橙色%'
and goods_name not like '%欧莱斯特304不锈钢无缝内胆水壶QL-818%' and goods_name not like '%欧然电水壶CR-1130%'
and goods_name not like '%日式环保包袋组合（折叠环保包+爱心便当包）%'
and goods_name not like '%三洋SANYO 煮蛋器 JH702A%' and goods_name not like '%三洋SANYO煮蛋器JH702A%'
and goods_name not like '%三洋电吹风DH-JH104%' and goods_name not like '%三洋电吹风HD-JH101A%'
and goods_name not like '%三洋电吹风HD-JH104%' and goods_name not like '%三洋电熨斗A-JH102M%'
and goods_name not like '%三洋加湿器CFK-JH6013%' and goods_name not like '%膳之厨多功能锅CJ-096%'
and goods_name not like '%膳之厨锅具两件套CJ-960%' and goods_name not like '%膳之厨时尚三件套CJ-3683%'
and goods_name not like '%圣德保罗工具套装SD-007-C%' and goods_name not like '%十八子作高级不锈钢家用剪WJJ-01%'
and goods_name not like '%十八子作开瓶器SC-02%' and goods_name not like '%十八子作雅刃六件套刀S2902%'
and goods_name not like '%十八子作银盈快刃斩切刀S2504-A%' and goods_name not like '%时尚折叠凳%'
and goods_name not like '%史努比故事保鲜盒组SP-A123%' and goods_name not like '%史努比故事水杯SP-A317%'
and goods_name not like '%史努比酷狗健康称SP-H201%' and goods_name not like '%史努比酷炫多功能杯SP-A105%'
and goods_name not like '%史努比亲亲四入冷水壶SP-A203%' and goods_name not like '%史努比清凉小冰杯SP-A112%'
and goods_name not like '%史努比提手杯%' and goods_name not like '%史努比幸福双层饭盒SP-C103%'
and goods_name not like '%史努比紫洋保温杯SP-A602%' and goods_name not like '%天飞伞3007%'
and goods_name not like '%天堂10302E国色天香伞%' and goods_name not like '%天堂苏印丝伞%'
and goods_name not like '%西铁城电子体温计CT513W%' and goods_name not like '%香山牌电子健康秤EB9003L%'
and goods_name not like '%香山牌机械健康秤BR2017B%' and goods_name not like '%小天子钢柄八件套刀A-019%'
and goods_name not like '%伊莱特3L电饭煲CFXB30-J31A%' and goods_name not like '%宜剪美%'
and goods_name not like '%羽梦情缘被套(简装)%' and goods_name not like '%羽梦情缘床单(简装)%'
and goods_name not like '%羽梦情缘枕套(简装)%' and goods_name not like '%雨花丝绒毯%'
and goods_name not like '%蒸功夫蒸汤两用锅%' and goods_name not like '%竹纤维被%' and goods_name not like '%竹纤维浴巾%'
and goods_name not like '%红厨 32cm炫雅雪花不沾炒锅YN0304%'
and goods_name not like '%春笑牌烘鞋器（大）%' and goods_name not like '%欧然不锈钢快速电水壶CR-1181 1.2L%'
and goods_name not like '%春笑牌烘鞋器（小）%' and goods_name not like '%史努比紫洋保温杯 SP-A602%'
and goods_name not like '%欧然不锈钢快速电水壶CR-1120 0.8L%')order by suppliertabid
goods where goods_id>0 and cat_id=40 and is_real=1 and area in('000','0514') and
goods_id not in(
select top 16 goods_id from goods where
goods_name not like '%SP-8210 史努比旋风吹风机%' and goods_name not like '%zippo打火机150ZL%'
and goods_name not like '%ZIPPO打火机205%' and goods_name not like '%zippo打火机280AW陆军空战银章%'
and goods_name not like '%爱国者音箱SP-F021%' and goods_name not like '%爱国者音箱SP-F027%'
and goods_name not like '%铂金多用汤锅DFS-T012%' and goods_name not like '%不锈钢快速水壶OL-209%'
and goods_name not like '%不锈钢快速水壶OL-215%' and goods_name not like '%不锈钢密封碗三件套%'
and goods_name not like '%彩棉浴巾%'and goods_name not like '%超人电吹风SB80%'
and goods_name not like '%超人电须刀SA35%' and goods_name not like '%超人电须刀SA850%'
and goods_name not like '%超人电熨斗SY570%' and goods_name not like '%超人毛球修剪器SR2855%'
and goods_name not like '%超人剃须刀SA2701%' and goods_name not like '%超人拓威干鞋机TH151%'
and goods_name not like '%厨具十八件套CQG1518%' and goods_name not like '%春笑牌USB蓝鲸电暖手鼠标垫%'
and goods_name not like '%春笑牌USB维尼熊电暖手鼠标垫%' and goods_name not like '%春笑牌USB喜洋洋电暖手鼠标垫%'
and goods_name not like '%德世朗多功能蒸煮锅%' and goods_name not like '%迪士尼保温泡菜壶%'
and goods_name not like '%迪士尼抽真空保温杯%' and goods_name not like '%迪士尼幸运草随手杯%'
and goods_name not like '%冬暖复合被%' and goods_name not like'%都彭牛皮皮带8818C289%'
and goods_name not like '%都彭牛皮皮带8881C055%' and goods_name not like'%都彭牛皮钥匙包8855B020-4%'
and goods_name not like '%哆啦A梦便利收纳箱%' and goods_name not like'%哆啦A梦大容量真空保温杯%'
and goods_name not like '%哆啦A梦红酒瓶雨伞(黄)%' and goods_name not like '%哆啦A梦时尚套装礼盒DM-2319%'
and goods_name not like '%果缤纷（刀具套装）%' and goods_name not like '%韩式调料缸三件套（22*24*26）%'
and goods_name not like '%好帮手淘米器%' and goods_name not like '%红厨铝合金煎盘YN0351%'
and goods_name not like '%吉旺1厘无磁钢柄五件套装7162%' and goods_name not like '%吉旺3厘木纹6件套7191%'
and goods_name not like '%康戈 蒸好（微波炉专用小蒸煲）CQG2004%' and goods_name not like '%康戈SURE不锈钢保温杯%'
and goods_name not like '%康戈秤心如意厨房秤%'and goods_name not like '%康戈情侣密封罐CQG5001%'
and goods_name not like '%康戈钛真空保温杯CQG4000%'
and goods_name not like '%康戈蒸好（微波炉专用小蒸煲）CQG2004%' and goods_name not like '%康戈智能卡路里电子跳绳%'
and goods_name not like '%乐扣茶杯HPL934M%' and goods_name not like '%乐扣乐扣四方型保鲜盒HPL809F%'
and goods_name not like '%乐扣乐扣圆型保鲜盒HPL933%' and goods_name not like '%乐仕菲斯贝贝乐保温提盒RSC1000K%'
and goods_name not like '%乐仕菲斯单柄奶锅RS1600K%' and goods_name not like '%乐仕菲斯优乐套装锅RS201T%'
and goods_name not like '%绿波仙子（补铁绿彩套锅）%' and goods_name not like '%欧莱斯特304不锈钢无缝内胆水壶OL-818橙色%'
and goods_name not like '%欧莱斯特304不锈钢无缝内胆水壶QL-818%' and goods_name not like '%欧然电水壶CR-1130%'
and goods_name not like '%日式环保包袋组合（折叠环保包+爱心便当包）%'
and goods_name not like '%三洋SANYO 煮蛋器 JH702A%' and goods_name not like '%三洋SANYO煮蛋器JH702A%'
and goods_name not like '%三洋电吹风DH-JH104%' and goods_name not like '%三洋电吹风HD-JH101A%'
and goods_name not like '%三洋电吹风HD-JH104%' and goods_name not like '%三洋电熨斗A-JH102M%'
and goods_name not like '%三洋加湿器CFK-JH6013%' and goods_name not like '%膳之厨多功能锅CJ-096%'
and goods_name not like '%膳之厨锅具两件套CJ-960%' and goods_name not like '%膳之厨时尚三件套CJ-3683%'
and goods_name not like '%圣德保罗工具套装SD-007-C%' and goods_name not like '%十八子作高级不锈钢家用剪WJJ-01%'
and goods_name not like '%十八子作开瓶器SC-02%' and goods_name not like '%十八子作雅刃六件套刀S2902%'
and goods_name not like '%十八子作银盈快刃斩切刀S2504-A%' and goods_name not like '%时尚折叠凳%'
and goods_name not like '%史努比故事保鲜盒组SP-A123%' and goods_name not like '%史努比故事水杯SP-A317%'
and goods_name not like '%史努比酷狗健康称SP-H201%' and goods_name not like '%史努比酷炫多功能杯SP-A105%'
and goods_name not like '%史努比亲亲四入冷水壶SP-A203%' and goods_name not like '%史努比清凉小冰杯SP-A112%'
and goods_name not like '%史努比提手杯%' and goods_name not like '%史努比幸福双层饭盒SP-C103%'
and goods_name not like '%史努比紫洋保温杯SP-A602%' and goods_name not like '%天飞伞3007%'
and goods_name not like '%天堂10302E国色天香伞%' and goods_name not like '%天堂苏印丝伞%'
and goods_name not like '%西铁城电子体温计CT513W%' and goods_name not like '%香山牌电子健康秤EB9003L%'
and goods_name not like '%香山牌机械健康秤BR2017B%' and goods_name not like '%小天子钢柄八件套刀A-019%'
and goods_name not like '%伊莱特3L电饭煲CFXB30-J31A%' and goods_name not like '%宜剪美%'
and goods_name not like '%羽梦情缘被套(简装)%' and goods_name not like '%羽梦情缘床单(简装)%'
and goods_name not like '%羽梦情缘枕套(简装)%' and goods_name not like '%雨花丝绒毯%'
and goods_name not like '%蒸功夫蒸汤两用锅%' and goods_name not like '%竹纤维被%' and goods_name not like '%竹纤维浴巾%'
and goods_name not like '%红厨 32cm炫雅雪花不沾炒锅YN0304%'
and goods_name not like '%春笑牌烘鞋器（大）%' and goods_name not like '%欧然不锈钢快速电水壶CR-1181 1.2L%'
and goods_name not like '%春笑牌烘鞋器（小）%' and goods_name not like '%史努比紫洋保温杯 SP-A602%'
and goods_name not like '%欧然不锈钢快速电水壶CR-1120 0.8L%')order by suppliertabid
这样就完整了吗？NO NO NO。
继续优化，这还怎么优化，当然能了，现在查的是商品名字啊，改啊商品ID，不使用NOT LIKE 性能太差了。创建唯一索引，OMG，更加快了。这样是最终效果。任务完成。代码真雷死我了。
作者：Mr S.R Lee
出处：http://www.cnblogs.com/LeeYongze
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利.
